1

Is there a way to split rows into multiple rows based on some column values?

My input dataframe is

   A              B           C
0  1  1234.0,5643.0     One,Two
1  2           5432  Three,Four
2  3  1278.0,5678.0        Five

I want to split the column B and C with ','. The output dataset to should be:

   A       B      C
0  1  1234.0    One
0  1  1234.0    Two
0  1  5643.0    One
0  1  5643.0    Two
1  2    5432  Three
1  2    5432   Four
2  3  1278.0   Five
2  3  5678.0   Five
DumbCoder
  • 233
  • 2
  • 9

2 Answers2

4

Using str.split to turn the strings into lists then explode each one:

df["B"] = df["B"].str.split(",")
df["C"] = df["C"].str.split(",")
for col in ["B", "C"]:
    df = df.explode(col)

#    A       B      C
# 0  1  1234.0    One
# 0  1  1234.0    Two
# 0  1  5643.0    One
# 0  1  5643.0    Two
# 1  2    5432  Three
# 1  2    5432   Four
# 2  3  1278.0   Five
# 2  3  5678.0   Five

In pandas v1.3.0 you can do a multi-column explode, but this requires lists be the same length in all columns.

Alex
  • 6,610
  • 3
  • 20
  • 38
  • I am using pandas 0.23.4. I don't think it supports explode. – DumbCoder Jul 08 '21 at 12:08
  • That version is from August 2018, `explode` was added in 0.25 (July 2019). – Alex Jul 08 '21 at 12:09
  • @DumbCoder I think your options here are to either upgrade your pandas to at least 0.25.0 (which is still 2 years old!) or to [implement your own `explode`](https://stackoverflow.com/questions/12680754/split-explode-pandas-dataframe-string-entry-to-separate-rows). – Alex Jul 08 '21 at 12:33
  • in pandas 1.3 you can explode multiple columns, removing the need for the for loop – sammywemmy Jul 08 '21 at 12:53
  • 1
    @sammywemmy see my note under the snippet. That will only work if all the columns have matching element counts, which in this case they don't – Alex Jul 08 '21 at 12:54
0

You can use:

pd.concat([df[[0]], df[1].str.split(', ', expand=True)], axis=1)

More reference: Pandas split column into multiple columns by comma

HARSH MITTAL
  • 750
  • 4
  • 17