Pandas: Configurable json_normalize with respect to number of levels and Keys to be flattened

[bhavaniravi]

Code Sample, a copy-pastable example if possible

data = [{"CreatedBy":{"Name": "User001"}, 
        "Lookup":{"TextField":"Some text", "UserField":{"Id":"ID001", "Name": "Name001"}},
        "Image":{"a":"b"}}]
json_normalize(data).to_json(orient="records")

Current Output

[{"CreatedBy.Name":"User001","Image.a":"b",
"Lookup.TextField":"Some text",
"Lookup.UserField.Id":"ID001","Lookup.UserField.Name":"Name001"}]

Problem description

I want to flatten only specific keys and up to a specific a specific level.
For eg., I want to flatten until level 1 and skip key image.

Expected Output

[{"CreatedBy.Name":"User001",
"Image:{a":"b}",
"Lookup.TextField":"Some text",
"Lookup.UserField":{Id":"ID001",
"Name":"Name001"}]

Output of pd.show_versions()

INSTALLED VERSIONS

commit: None
pandas: 0.23.4
pytest: None
pip: 18.1
setuptools: 39.1.0
Cython: None
numpy: 1.15.1
scipy: None
pyarrow: None
xarray: None
IPython: None
sphinx: None
patsy: None
dateutil: 2.7.3
pytz: 2018.4
blosc: None
bottleneck: None
tables: None
numexpr: None
feather: None
matplotlib: 3.0.0
openpyxl: None
xlrd: None
xlwt: None
xlsxwriter: None
lxml: None
bs4: None
html5lib: None
sqlalchemy: None
pymysql: None
psycopg2: None
jinja2: 2.10
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None

[gfyoung]

I thought for a moment that the meta parameter to json_normalize might help, but that was not to be. Any kind of enhancement would need to be done to json_normalize. It's "too late" once you get to the .to_json-DataFrame stage.

Interesting proposal. My only concern is the extent of use cases for partial flattening.

cc @WillAyd @jreback

[bhavaniravi]

I have rewritten the nested_to_records method for my use.

Would love to contribute it back and extend it to json_normalize as well.

My use case is for exporting data for report generation.

[gfyoung]

That's great! Before we proceed, can you run tests on your machine to confirm that things don't break?

[bhavaniravi]

Definitely. Will do that.

[WillAyd]

Yea I think it would be possible to add a level parameter here. There's a recursive function nested within which uses that as well, so may be able to wire those all together to control how deep the un-nesting of records goes.

Investigation and PRs certainly welcome

[bhavaniravi]

I have a query. If the values of the keys in record path are nested dictionaries itself, is it supposed . to get flattened? I am going through the code and got stuck here

[bhavaniravi]

@WillAyd I am a bit confused with the behavior ofjson_normalize method with records_path. While just passing a list of records flattens the dictionary, it doesn't flattens the same on sending records_path param. Is it the excepted behavior?

data = [{'CreatedBy': {'Name': 'User001'},
             'Lookup': [{'TextField': 'Some text',
                                'UserField': {'Id': 'ID001', 'Name': 'Name001'}},
                              {'TextField': 'Some text',
                               'UserField': {'Id': 'ID001', 'Name': 'Name001'}}
                        ],
            'Image': {'a': 'b'}
        }]

json_normalize(data, record_path=["Lookup"])

Current Output

   TextField                           UserField
0  Some text  {'Id': 'ID001', 'Name': 'Name001'}
1  Some text  {'Id': 'ID001', 'Name': 'Name001'}

Expected Output

   TextField              UserField.Id       Name
0  Some text               ID001             Name001
1  Some text               ID001             Name001

[WillAyd]

@bhavaniravi that seems like a bug to me. I'd suggest opening that as a separate issue and working that as a pre-cursor to this. Would serve the purpose of not only fixing the bug but helping grow familiarity with the various parameters so we can see how this should fit into the context of them

[WillAyd]

Actually there's already an issue open about this #22706

[bhavaniravi]

@WillAyd I think someone already gave a PR for the same. Should I be working on it too?

[WillAyd]

I just pinged the PR author on that one to see if they can make some updates. Let's give it a few days to see if they are interested in doing that first

[jreback]

@bhavaniravi your pr will close this the max_levels, but I don't think keys; pls open a new issue if that is needed / makes sense.

[bhavaniravi]

okay, will create another one.