How to flatten a pandas dataframe with some columns as json?
Here's a solution using json_normalize()
again by using a custom function to get the data in the correct format understood by json_normalize
function.
import ast
from pandas.io.json import json_normalize
def only_dict(d):
'''
Convert json string representation of dictionary to a python dict
'''
return ast.literal_eval(d)
def list_of_dicts(ld):
'''
Create a mapping of the tuples formed after
converting json strings of list to a python list
'''
return dict([(list(d.values())[1], list(d.values())[0]) for d in ast.literal_eval(ld)])
A = json_normalize(df['columnA'].apply(only_dict).tolist()).add_prefix('columnA.')
B = json_normalize(df['columnB'].apply(list_of_dicts).tolist()).add_prefix('columnB.pos.')
Finally, join the DFs
on the common index to get:
df[['id', 'name']].join([A, B])
EDIT:- As per the comment by @MartijnPieters, the recommended way of decoding the json strings would be to use json.loads()
which is much faster when compared to using ast.literal_eval()
if you know that the data source is JSON.
The quickest seems to be:
import pandas as pd
import json
json_struct = json.loads(df.to_json(orient="records"))
df_flat = pd.io.json.json_normalize(json_struct) #use pd.io.json