I want to flatten JSON column in a Pandas DataFrame
Here is a way to use pandas.io.json.json_normalize():
from pandas.io.json import json_normalizedf = df.join(json_normalize(df["e"].tolist()).add_prefix("e.")).drop(["e"], axis=1)print(df)# e.k1 e.k2#0 v1 v2#1 v3 v4#2 v5 v6However, if you're column is actually a str and not a dict, then you'd first have to map it using json.loads():
import jsondf = df.join(json_normalize(df['e'].map(json.loads).tolist()).add_prefix('e.'))\ .drop(['e'], axis=1)
If your column is not already a dictionary, you could use map(json.loads) and apply pd.Series:
s = df['e'].map(json.loads).apply(pd.Series).add_prefix('e.')Or if it is already a dictionary, you can apply pd.Series directly:
s = df['e'].apply(pd.Series).add_prefix('e.')Finally use pd.concat to join back the other columns:
>>> pd.concat([df.drop(['e'], axis=1), s], axis=1).set_index('id') id e.k1 e.k21 v1 v22 v3 v43 v5 v6