Explode pandas dataframe column values to separate rows

Question

I have following pandas dataframe:

id          term            code
2445 | 2716 abcd | efgh     2345
1287        hgtz            6567

I would like to explode id and term column. How can I explode multiple columns to keep the values across the columns id, term and code together.

The expected output is:

id          term            code
2445        abcd            2345
2716        efgh            2345
1287        hgtz            6567

I have tried so far is: df.assign(id=df['id'].str.split(' | ')).explode('id')

Timeless · Accepted Answer · 2023-02-01 22:05:21Z

4

You're in the right way, you just need some help from concat with a listcomp :

out = (
        pd.concat([df[col].str.split("\s*\|\s*")
                          .explode() for col in ["id", "term"]], axis=1)
             .join(df["code"])
      )

Output : print(out)

     id  term  code
0  2445  abcd  2345
0  2716  efgh  2345
1  1287  hgtz  6567

edited Feb 1, 2023 at 22:05

answered Feb 1, 2023 at 21:57

Timeless

38.3k6 gold badges33 silver badges54 bronze badges

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

2 Comments

Corralien Over a year ago

You can remove replace and use a regex with split: str.split(r'\s*\|\s*'). Especially if the term column contains multiple words for one term.

Timeless Over a year ago

This make the code shorter/better. Thanks for the trick, Corralien ;)

rhug123 · Accepted Answer · 2023-02-01 22:11:41Z

3

here is a way using .str.split() and explode() which can accept multiple columns

(df[['id','term']].stack()
.str.split(' | ',regex=False)
.unstack()
.explode(['id','term'])
.join(df[['code']]))

Output:

     id  term  code
0  2445  abcd  2345
0  2716  efgh  2345
1  1287  hgtz  6567

answered Feb 1, 2023 at 22:11

rhug123

8,8801 gold badge14 silver badges27 bronze badges

Collectives™ on Stack Overflow

Explode pandas dataframe column values to separate rows

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related