Pandas explode multiple columns

Question

I have DF that has multiple columns. Two of the columns are list of the same len.( col2 and col3 are list. the len of the list is the same).

My goal is to list each element on it's own row.

I can use the df.explode(). but it only accepts one column. However, I want the pair of the two columns to be 'exploded'. If I do df.explode('col2') and then df.explode('col3'), it results it 9 rows instead of 3.

Original DF

col0      col1        col2        col3
1       aa          [1,2,3]     [1.1,2.2,3.3]
2       bb          [4,5,6]     [4.4,5.5,6.6]
3       cc          [7,8,9]     [7.7,8.8,9.9]
3       cc          [7,8,9]     [7.7,8.8,9.9]

End DataFrame

id      col1        col2        col3
1       aa          1           1.1
1       aa          2           2.2
1       aa          3           3.3
2       bb          4           4.4
2       bb          5           5.5
2       bb          6           6.6
3       cc          ...         ...

Update None of the column have unique values, so can't be used as index.

Better answer can be found [`here`](https://stackoverflow.com/questions/67097110/convert-elements-in-lists-to-separate-rows/67097208#67097208). No need to use `set_index` and `reset_index`. — Mayank Porwal, Apr 14 '21 at 18:43

score 19 · Accepted Answer · edited Apr 15 '21 at 06:42

19

You could set col1 as index and apply pd.Series.explode across the columns:

df.set_index('col1').apply(pd.Series.explode).reset_index()

Or:

df.apply(pd.Series.explode)


   col1 col2 col3
0    aa    1  1.1
1    aa    2  2.2
2    aa    3  3.3
3    bb    4  4.4
4    bb    5  5.5
5    bb    6  6.6
6    cc    7  7.7
7    cc    8  8.8
8    cc    9  9.9
9    cc    7  7.7
10   cc    8  8.8
11   cc    9  9.9

edited Apr 15 '21 at 06:42

jezrael

822,522
95
1,334
1,252

answered Jul 08 '20 at 18:44

yatu

86,083
12
84
139

Thanks, I thin it would give an error because none of the column have unique values, so can't be used as index. – Imsa Jul 08 '20 at 18:50
What do you mean? `col1` isn't unique? That is not a problem, try yourself, a col doesn't have to have unique values so it can be set as index @Imsa – yatu Jul 08 '20 at 18:53
Updated with the new df @Imsa – yatu Jul 08 '20 at 18:55
@lmsa it doesn't matter whether your column is unique valued or not, you can still set it as index. – Quang Hoang Jul 08 '20 at 18:56
15

`ValueError: cannot reindex from a duplicate axis` this the error I get when I run the following command. Please advise. – Imsa Jul 08 '20 at 19:11
What version of pandas? Have you tried updating pandas @imsa – yatu Jul 08 '20 at 19:27
2

I have Pandas 1.2.0 and I'm getting the same `ValueError`. I actually had a MultiIndex, but I tried dropping it for the sake of reproducibility, but it didn't work either way. – Adrian Guerra Jan 05 '21 at 18:05
2

No need to `set_index` and `reset_index`. Just use `df = df.apply(pd.Series.explode)`. This will explode all the columns with `lists` in your dataframe. – Mayank Porwal Apr 14 '21 at 18:42
2

@MayankPorwal I still get ValueError: cannot reindex from a duplicate axis – G M May 05 '21 at 14:07
@GM you need to upgrade your pandas. – Mayank Porwal May 06 '21 at 03:11
@RémyHosseinkhanBoucher You need to upgrade your pandas. – Mayank Porwal May 24 '21 at 16:28
@MayankPorwal please, i am in pandas 1.2.4 py38h2531618_0 – Rémy Hosseinkhan Boucher May 24 '21 at 16:50
same with pandas 1.2.4 – TOM Jun 05 '21 at 10:31
I can confirm just using `df = df.apply(pd.Series.explode)` works in pandas 1.1.5 – lys Jun 23 '21 at 00:56
`df = df.apply(pd.Series.explode)` works with pandas 1.3.2 – Jia Gao Sep 13 '21 at 11:50

score 6 · Answer 2 · answered Sep 15 '21 at 10:58

6

I borrowed this solution from other answers (forgot where):

df.explode(['col2', 'col3']).

The advantage: faster than the apply solution.

Make sure both col2 and col3 have the same number of elements in cells in the same row.

answered Sep 15 '21 at 10:58

Jia Gao

1,172
3
13
26

Awesome, exactly what I was looking for. Note: It requires pandas >=1.3.0 – Maximosaic Mar 01 '22 at 11:28

Pandas explode multiple columns

2 Answers2

Linked

Related