I have a dataframe in pandas with two columns. One is an ID and the other is a long JSON object, which is the same object for each object in the dataframe. My goal here is to create columns for each key in the JSON object.
Here is an example of the input
ID request_json
175431467 {"Rate":"50","Groups":"7 months - 3 years"
I'd like to expand this into a dataframe with three columns: ID, Rate, and Groups.
What's the best way to do this?
You can use DataFrame constructor with join or concat:
import json
df = df[['ID']].join(pd.DataFrame(df['request_json'].apply(json.loads).values.tolist()))
print (df)
ID Groups Rate
0 175431467 7 months - 3 years 50
Or:
df = pd.concat([df['ID'],
pd.DataFrame(df['request_json'].apply(json.loads).values.tolist())], axis=1)
print (df)
ID Groups Rate
0 175431467 7 months - 3 years 50
In [38]: pd.io.json.json_normalize(df.to_dict('r'))
Out[38]:
ID request_json.Groups request_json.Rate
0 175431467 7 months - 3 years 50
