How to loop through Pandas DataFrame and split a string into multiple rows

Question

What would be the best way to loop through a dataframe with strings that I would like to split into multiple rows while retaining the other value at the same time?

input:

genres                   revenue
action|comedy|drama       5000
action|romance            10000

output:

genres      revenue
action      5000
comedy      5000
drama       5000
action      10000
romance     10000

jezrael · Accepted Answer · 2020-11-17 06:55:09Z

5

Use Series.str.split with assign back column by DataFrame.assign and DataFrame.explode, last for default index add DataFrame.reset_index with drop=True:

df1=df.assign(genres = df['genres'].str.split('|')).explode('genres').reset_index(drop=True)
print (df1)
    genres  revenue
0   action     5000
1   comedy     5000
2    drama     5000
3   action    10000
4  romance    10000

answered Nov 17, 2020 at 6:55

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

1 Comment

Archita Sundaray Over a year ago

this solution is very helpful and I strongly recommend it!

Mayank Porwal · Accepted Answer · 2020-11-17 07:06:08Z

2

You can use Series.str.split with df.explode:

Note: df.explode works for pandas version >= 0.25

In [2240]: df.genres = df.genres.str.split('|')

In [2242]: df = df.explode('genres')

In [2243]: df
Out[2243]: 
    genres  revenue
0   action     5000
0   comedy     5000
0    drama     5000
1   action    10000
1  romance    10000

edited Nov 17, 2020 at 7:06

answered Nov 17, 2020 at 6:55

Mayank Porwal

34.2k9 gold badges45 silver badges65 bronze badges

Collectives™ on Stack Overflow

How to loop through Pandas DataFrame and split a string into multiple rows

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related