Pandas: How to explode data frame with json arrays

Question

How to explode pandas data frame?

Input df:

Required output df:

+----------------+------+-----+------+
|level_2         | date | val | num  | 
+----------------+------+-----+------+
| name_1a        | 2020 |  1  | null |
| name_1b        | 2019 |  2  | null |
| name_1b        | 2020 |  3  | null |
| name_10000_xyz | 2018 |  4  | str  |
| name_10000_xyz | 2019 |  5  | null |
| name_10000_xyz | 2020 |  6  | str  |
+------------------------------------+

To reproduce input df:

import pandas as pd
pd.set_option('display.max_colwidth', None)
data={'level_2':{1:'name_1a',3:'name_1b',5:'name_10000_xyz'},'value':{1:[{'date':'2020','val':1}],3:[{'date':'2019','val':2},{'date':'2020','val':3}],5:[{'date':'2018','val':4,'num':'str'},{'date':'2019','val':5},{'date':'2020','val':6,'num':'str'}]}}
df = pd.DataFrame(data)

Shubham Sharma · Accepted Answer · 2021-08-26 15:36:55Z

7

Explode the dataframe on value column, then pop the value column and create a new dataframe from it then join the new frame with the exploded frame.

s = df.explode('value', ignore_index=True)
s.join(pd.DataFrame([*s.pop('value')], index=s.index))

          level_2  date  val  num
0         name_1a  2020    1  NaN
1         name_1b  2019    2  NaN
2         name_1b  2020    3  NaN
3  name_10000_xyz  2018    4  str
4  name_10000_xyz  2019    5  NaN
5  name_10000_xyz  2020    6  str

answered Aug 26, 2021 at 15:36

Shubham Sharma

71.8k6 gold badges26 silver badges58 bronze badges

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

Comments

Scott Boston · Accepted Answer · 2021-08-26 15:36:21Z

2

Try, explode then use pd.DataFrame constructor build dataframe from dictionaries:

dfe = df.explode('value')
df_out = pd.DataFrame([i for i in dfe['value'].to_numpy()], index=dfe['level_2']).reset_index()
print(df_out)

Output:

          level_2  date  val  num
0         name_1a  2020    1  NaN
1         name_1b  2019    2  NaN
2         name_1b  2020    3  NaN
3  name_10000_xyz  2018    4  str
4  name_10000_xyz  2019    5  NaN
5  name_10000_xyz  2020    6  str

answered Aug 26, 2021 at 15:36

Scott Boston

154k15 gold badges160 silver badges207 bronze badges

1 Comment

Scott Boston Over a year ago

Use @ShubnamSharma solution below. dfe.join(pd.DataFrame([*dfe.pop('value')]))

Collectives™ on Stack Overflow

Pandas: How to explode data frame with json arrays

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related