3
votes

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
1

1 Answers

0
votes

It's not working since you have one value without snapshots key, so you'd need to use:

df = pd.DataFrame(df[0]).iloc[:, 1:]
df['snapshots.Address.City'] = df.pop('snapshots').apply(lambda x: x[0]['Address']['City'] if x is not np.nan else x)
print(df)

Output:

   Code snapshots.Address.City
0     1                    NaN
1     2                 City B
2     3                 City C