explode a csv in python

Question

I have an excel file with 200 rows, 2 of which have comma separated values in them. If I output them to tab-separated, it would look like this:

col1  col2
a     b,c
d     e,f,g

I need to explode to get a dataframe like this, exploding 200 rows into ~4,000:

col1  col2
a     b
a     c
d     e
d     f
d     g

I don't see any explode functionality in pandas and haven't been able to figure out how to do this having the columns of comma-separated values uneven in length - not sure how split would work here.

Help me stack-overflow, you're my only hope. Thanks!

I found this, but I do not have my data is a list format - not sure if that's a helpful thread for anyone stackoverflow.com/questions/32468402/… — Super_John
– Super_John, Commented Jun 5, 2017 at 3:18

Scott Boston · Accepted Answer · 2017-06-05 03:19:16Z

2

Let's use pd.DataFrame, .str.split, stack:

df_out = (pd.DataFrame(df.col2.str.split(',').tolist(), index=df.col1)
      .stack()
      .reset_index()
      .drop('level_1',axis=1)
      .rename(columns={0:'col2'}))

Output:

  col1 col2
0    a    b
1    a    c
2    d    e
3    d    f
4    d    g

answered Jun 5, 2017 at 3:19

Scott Boston

154k15 gold badges160 silver badges207 bronze badges

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

3 Comments

Super_John Over a year ago

incredible. mind blown. Thank you! I have 2 csv columns - how would I add a second split column to get all combinations of col1 , col2 (csv), col3(csv)?

Super_John Over a year ago

sure, no problem. I'll comment a link here once it's posted.

Super_John Over a year ago

stackoverflow.com/questions/44361394/…

Collectives™ on Stack Overflow

explode a csv in python

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related