Convert nested dictionary into a multiple-column indexes dataframe

user_10328430 0
Viewed 244

I have the following dataframe:

| Week | info |
| ---- | ---- |
| 1    |{'Info': 'text', 'value': 94615}, {'Info': 'text_2', 'value': 111968}|
| 2    |{'Info': 'text', 'value': 234}, {'Info': 'text_2', 'InfoB': 236}|
| 3    |{'Info': 'text', 'value': 524523}, {'Info': 'text_2', 'InfoB': 5555}|

I would like to open up the dictionaries under info and am thinking that I should maybe re-shape the dataframe into a multiple-column index... perhaps by converting it into a multiple-column indexes dataframe as follows:

| Week | info             |
|      | text   | text_2  |
| ---- | ------ | ------- |
| 1    | 94615  | 111968  |  
| 2    | 234    | 236     |  
| 3    | 524523 | 5555    |

Any suggestions how can I do this? Also what's the best way to perform aggregation to this data, (e.g. mean of text, sum of week 1) pivot table perhaps?

pandas python
1 Answers

I think first is necessary change format of dictionaries and then convert to DataFrame like:

L =  [dict([tuple(y.values()) for y in x]) for x in df.pop('info')]

df = df.join(pd.DataFrame(L, index=df.index))
print (df)
   Week    text  text_2
0     1   94615  111968
1     2     234     236
2     3  524523    5555

Another idea:

f = lambda x: dict([tuple(y.values()) for y in x])
df = df.join(pd.json_normalize(df.pop('info').apply(f)))
user_29010020
Related