explode a pandas dataframe using two comma separated columns

Question

I have a data which is packed a lot of values into row, but i would like to have to converted to row wise data to make it usable. below is how my input looks like. i would like to explode this dataset. i am able to do if its for single column, but i am unable to achieve same result for combination of two column. is the below achievable using pandas?

import pandas as pd
data = [{'col1':'val1','col2':'val2','col3':'aaa,bbb,ccc','col4':'ddd,eee,fff'}]
df = pd.DataFrame(data)

Input

col1  col2         col3         col4
val1  val2  aaa,bbb,ccc  ddd,eee,fff

Desired Output

col1  col2         col3         col4
val1  val2         aaa          ddd
val1  val2         aaa          eee
val1  val2         aaa          fff
val1  val2         bbb          ddd
val1  val2         bbb          eee
val1  val2         bbb          fff
val1  val2         ccc          ddd
val1  val2         ccc          eee
val1  val2         ccc          fff

related to this.

Quang Hoang
– Quang Hoang

2021-02-23 16:32:01 +00:00
Commented Feb 23, 2021 at 16:32 — Quang Hoang
– Quang Hoang, Commented Feb 23, 2021 at 16:32

Pygirl · Accepted Answer · 2021-02-23 16:37:48Z

1

You can just explode twice:

(df.assign(col3=df.col3.str.split(','),
           col4=df.col4.str.split(','))
   .explode('col3')
   .explode('col4')
).reset_index(drop=True)

Output:

    col1    col2    col3    col4
0   val1    val2    aaa     ddd
1   val1    val2    aaa     eee
2   val1    val2    aaa     fff
3   val1    val2    bbb     ddd
4   val1    val2    bbb     eee
5   val1    val2    bbb     fff
6   val1    val2    ccc     ddd
7   val1    val2    ccc     eee
8   val1    val2    ccc     fff

edited Feb 23, 2021 at 16:37

Pygirl

13.4k6 gold badges36 silver badges48 bronze badges

answered Feb 23, 2021 at 16:34

Quang Hoang

151k11 gold badges64 silver badges86 bronze badges

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

Collectives™ on Stack Overflow

explode a pandas dataframe using two comma separated columns

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related