TransWikia.com

Pandas Dataframe split columns list simultaneously

Stack Overflow Asked by colla on January 25, 2021

I have a dataframe like this:

data = {'period':[
                  [{'periods': [{'From': '19', 'To': '21'}]}, {'periods': [{'From': '13', 'To': '30'}]}, {'periods': [{'From': '02', 'To': '26'}]}],  
                  [{'periods': [{'From': '21', 'To': '26'}]}, {'periods': [{'From': '22', 'To': '27'}]}, {'periods': [{'From': '05', 'To': '07'}]}]
                 ], 
        'Age':[
                  ['9820', '5480', '8535'], 
                  ['1524', '6555', '4555']
              ], 
        'Address':[
                    '2', 
                    '3'
                   ]
        }

df = pd.DataFrame(data) 

![enter image description here

I would like to transform my dataset in this way:

data = {'Name':[
{'periods': [{'From': '19', 'To': '21'}]}, 
{'periods': [{'From': '13', 'To': '30'}]}, 
{'periods': [{'From': '02', 'To': '26'}]}
,  
{'periods': [{'From': '21', 'To': '26'}]}, 
{'periods': [{'From': '22', 'To': '27'}]}, 
{'periods': [{'From': '05', 'To': '07'}]}
], 
        'Age':['9820', '5480', '8535','1524', '6555', '4555'], 
        'Address':['2', '2', '2', '3', '3', '3']}

df = pd.DataFrame(data) 

![enter image description here

I am struggling to find a way to do explode() on two columns simultaneously so that the first element of the list in the column period belongs to the same row of the first element of the list in the column Age.

period list and age list are always of equal size.

I tried doing this:

df = df.reset_index()
uid = df.set_index('index')['period'].explode().reset_index()
df = uid.join(df['Age'].explode())

but it does not show the Address column anymore

I would appreciate any help. Thank you

2 Answers

You can use apply + explode:

df.apply(pd.Series.explode).reset_index(drop=True)

                                      period   Age Address
0  {'periods': [{'From': '19', 'To': '21'}]}  9820       2
1  {'periods': [{'From': '13', 'To': '30'}]}  5480       2
2  {'periods': [{'From': '02', 'To': '26'}]}  8535       2
3  {'periods': [{'From': '21', 'To': '26'}]}  1524       3
4  {'periods': [{'From': '22', 'To': '27'}]}  6555       3
5  {'periods': [{'From': '05', 'To': '07'}]}  4555       3

Answered by Shubham Sharma on January 25, 2021

Let's try explode them manually:

names = df.period.explode()
ages = df.Age.explode()

pd.DataFrame({'Name':names, 'Age':ages, 'Address':df.Address.reindex(ages.index)})

Output:

                                        Name   Age Address
0  {'periods': [{'From': '19', 'To': '21'}]}  9820       2
0  {'periods': [{'From': '13', 'To': '30'}]}  5480       2
0  {'periods': [{'From': '02', 'To': '26'}]}  8535       2
1  {'periods': [{'From': '21', 'To': '26'}]}  1524       3
1  {'periods': [{'From': '22', 'To': '27'}]}  6555       3
1  {'periods': [{'From': '05', 'To': '07'}]}  4555       3

Answered by Quang Hoang on January 25, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP