how to solve pandas multi-column explode issue?

Question

I am trying to explode multi-columns at a time systematically. Such that:

[

and I want the final output as:

I tried

df=df.explode('sauce', 'meal')

but this only provides the first element ( sauce) in this case to be exploded, and the second one was not exploded.

I also tried:

df=df.explode(['sauce', 'meal'])

but this code provides

ValueError: column must be a scalar

error.

I tried this approach, and also this. none worked.

Note: cannot apply to index, there are some none- unique values in the fruits column.

Could you please share your data example instead of picture? — kağan hazal koçdemir, Dec 25 '21 at 20:29
What version of pandas are you using? You need to update, because passing a list of columns to `explode` was added recently. — , Dec 25 '21 at 20:29
@richardec which version of pandas you'd recommend to install — hilo, Dec 25 '21 at 20:44

Scott Boston · Accepted Answer · 2021-12-25T20:49:56.367

1

Prior to pandas 1.3.0 use:

df.set_index(['fruits', 'veggies'])[['sauce', 'meal']].apply(pd.Series.explode).reset_index()

Output:

  fruits veggies sauce meal
0     x1      y2     a    d
1     x1      y2     b    e
2     x1      y2     c    f
3     x2      y2     g    k
4     x2      y2     h    l

Many columns? Try:

df.set_index(df.columns.difference(['sauce', 'meal']).tolist())\
  .apply(pd.Series.explode).reset_index()

Output:

  fruits veggies sauce meal
0     x1      y2     a    d
1     x1      y2     b    e
2     x1      y2     c    f
3     x2      y2     g    k
4     x2      y2     h    l

edited Dec 25 '21 at 20:49

answered Dec 25 '21 at 20:35

Scott Boston

147,308
15
139
187

I have 78 columns, indexing might be an issue here... – hilo Dec 25 '21 at 20:43
It shouldn't be. Just move all columns not exploded into the index. – Scott Boston Dec 25 '21 at 20:47
@hilo See updated syntax for many columns. – Scott Boston Dec 25 '21 at 20:50
1

It was a very tricky and efficient solution. Thank you for your clean, insightful contribution Scott. Have a great day! – hilo Dec 25 '21 at 20:55

score 1 · Answer 2 · answered Dec 25 '21 at 20:56

Update your version of Pandas

# Setup
df = pd.DataFrame({'fruits': ['x1', 'x2'],
                   'veggies': ['y1', 'y2'],
                   'sauce': [list('abc'), list('gh')],
                   'meal': [list('def'), list('kl')]})
print(df)

# Output
  fruits veggies      sauce       meal
0     x1      y1  [a, b, c]  [d, e, f]
1     x2      y2     [g, h]     [k, l]

Explode (Pandas 1.3.5):

out = df.explode(['sauce', 'meal'])
print(out)

# Output
  fruits veggies sauce meal
0     x1      y1     a    d
0     x1      y1     b    e
0     x1      y1     c    f
1     x2      y2     g    k
1     x2      y2     h    l

how to solve pandas multi-column explode issue?

2 Answers2