Create pandas columns from json pandas columns

Question

I have this dataframe

d = {'parameters': [{'Year': '2018',
 'Median Age': 'nan',
 'Total Non-Family Household Income': 289.0,
 'Total Household Income': 719.0,
 'Gini Index of Income Inequality': 0.4121}]}
df_sample = pd.DataFrame(data=d)
df_sample.head()

I want to convert that json into pandas columns. How do I do this? Assume I only have the dataframe not the parameter d

I saw this example

#which columns have json
#device
json_cols = ['device', 'geoNetwork', 'totals',  'trafficSource']

for column in json_cols:

    c_load = test[column].apply(json.loads)
    c_list = list(c_load)
    c_dat = json.dumps(c_list)

    test = test.join(pd.read_json(c_dat))
    test = test.drop(column , axis=1)

But this does not seem too pythonic...

Guru Stron · Accepted Answer · 2023-01-25 20:13:19Z

1

Use json_normalize:

df_sample = pd.json_normalize(data=d, record_path=['parameters'])

Resulting dataframe:

Year	Median Age	Total Non-Family Household Income	Total Household Income	Gini Index of Income Inequality
2018	nan	289.0	719.0	0.4121

UPD:

If you already have dataframe loaded, then applying pd.Series should work:

df_sample = df_sample['parameters'].apply(pd.Series) # or df_sample['parameters'].map(json.loads).apply(pd.Series) if data is not already dict

edited Jan 25, 2023 at 20:13

answered Jan 25, 2023 at 19:59

Guru Stron

150k11 gold badges186 silver badges231 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

justanewb Over a year ago

Assume, I do not have the parameter d, what do I do then?

Collectives™ on Stack Overflow

Create pandas columns from json pandas columns

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related