I need to help with normalize columns in pandas DataFrame. This is input
df = {0:[{'Code' : 1,'Category' : 'X'},
{'Code' : 2,'Category' : 'Y','snapshots' : [{'Address': {'City': 'City B'}}] },
{'Code' : 3,'Category' : 'Z','snapshots' : [{'Address': {'City': 'City C'}}] }
]
}
df = pd.DataFrame(df)
My code:
df_1 = pd_json.json_normalize(df[0],
meta=["Code"],
record_path=["snapshots"],
record_prefix="snapshots.",
errors="ignore"
)
df_2 = (df_1.drop('snapshots.Address', 1)
.assign(**df_1["snapshots.Address"].dropna()
.apply(pd.Series).add_prefix('snapshots.Address.')))
Error:
Traceback (most recent call last):
File "", line 5, in errors="ignore"
File "C:\Users\my_user\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\io\json\normalize.py", line 267, in json_normalize _recursive_extract(data, record_path, {}, level=0)
File "C:\Users\my_user\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\io\json\normalize.py", line 244, in _recursive_extract recs = _pull_field(obj, path[0])
File "C:\Users\my_user\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\io\json\normalize.py", line 189, in _pull_field result = result[spec]
KeyError: 'snapshots'
There is missing "snapshots" in DataFrame for "Code=1". My idea is add "snapshots" to places in DataFrame, where it is missing. But I do not know how to do it.
Expected result:
df_2
Out[617]:
Code snapshots.Address.City
0 1 NaN
1 2 City B
2 3 City C