Pandas - Explode multiple columns in pandas and assign value based on the exploded column

Question

Reproducible example:

ex = [{"explode1": ["a", "e", "i"], "word": "US_12", "explode2": []}, 
      {"explode1": [], "word": "US_34", "explode2": ["a", "e", "i"]}, 
      {"explode1": ["a", "e", "i"], "word": "US_56", "explode2": ["o", "u"]}]

df = pd.DataFrame(ex)

Gives you

        explode1   word   explode2
    0  [a, e, i]  US_12         []
    1         []  US_34  [a, e, i]
    2  [a, e, i]  US_56     [o, u]

You can assume there is also an explode3 and an explode4 column (excluded for the sake of brevity)

Intended Result DataFrame:

   exploded_alphabet   word    exploded_type
0                  a  US_12    explode1
1                  e  US_12    explode1
2                  i  US_12    explode1
3                  a  US_34    explode2
4                  e  US_34    explode2
5                  i  US_34    explode2
6                  a  US_54    explode1
7                  e  US_54    explode1
8                  i  US_54    explode1
9                  o  US_34    explode2
10                 u  US_34    explode2

The solution must be reproducible with 4 columns not just 2 mentioned above (I haven't included in my example explode3 and explode4 for the same of brevity)

So total number of rows will be equal to number of elements in all of the lists in explode1, explode2, explode3 and explode4 flattened.

My efforts:

Honestly, I'm thinking there must be a shorter Pythonic way rather than exploding each one individually and then exploding those that have multiple types.

df = df.explode("explode1")
df = df.explode("explode2")

The above is incorrect. Since this does not explode the rows simultaneously. It creates duplicates if list is non empty in multiple explosion columns.

The other one is the non-pythonic way where you iterate row wise and create and assign a new column - this is lengthy and easy to do. But this problem has probably been solved in a different way.

How is my question different from other "explode multiple columns" question?:

Exploding them separately. Every element in those columns creates a new row (This is probably already there on SO)
Assign the value in the exploded_type - Not sure if this has been solved on SO in conjunction to 1.

jezrael · Accepted Answer · 2021-05-25 11:26:13Z

4

Use DataFrame.melt before explode for unpivot and then remove rows with missing values (from empty lists):

df = (df.melt('word', value_name='exploded_alphabet', var_name='exploded_type')
        .explode("exploded_alphabet")
        .dropna(subset=['exploded_alphabet'])
        .reset_index(drop=True))
print (df)
     word exploded_type exploded_alphabet
0   US_12      explode1                 a
1   US_12      explode1                 e
2   US_12      explode1                 i
3   US_56      explode1                 a
4   US_56      explode1                 e
5   US_56      explode1                 i
6   US_34      explode2                 a
7   US_34      explode2                 e
8   US_34      explode2                 i
9   US_56      explode2                 o
10  US_56      explode2                 u

edited May 25, 2021 at 11:26

answered May 25, 2021 at 11:19

jezrael

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

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

Comments

Nk03 · Accepted Answer · 2021-05-25 11:37:54Z

3

you can stack and then explode:

result = df.set_index('word').stack().explode().dropna().reset_index(
    name='exploded_alphabet').rename(columns={'level_1': 'exploded_type'})

OUTPUT:

     word exploded_type exploded_alphabet
0   US_12      explode1                 a
1   US_12      explode1                 e
2   US_12      explode1                 i
3   US_34      explode2                 a
4   US_34      explode2                 e
5   US_34      explode2                 i
6   US_56      explode1                 a
7   US_56      explode1                 e
8   US_56      explode1                 i
9   US_56      explode2                 o
10  US_56      explode2                 u

PERFORMANCE:


for _ in range(20):
    df = df.append(df)
    
len(df) # 3145728

%%timeit 
(
    df.set_index('word')
    .stack().
    explode().
    dropna().
    reset_index(name='exploded_alphabet').
    rename(columns={'level_1': 'exploded_type'})
)

4.77 s ± 62.6 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

%%timeit
(
     df.melt('word', value_name='exploded_alphabet', var_name='exploded_type')
        .explode("exploded_alphabet")
        .dropna(subset=['exploded_alphabet'])
)
6.68 s ± 224 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

%%timeit
explode_columns = ['explode1', 'explode2']
pd.melt(
    frame=df,
    id_vars='word',
    value_vars=explode_columns,
    var_name='exploded_type',
    value_name='exploded_alphabet'
).explode('exploded_alphabet').dropna()

7.17 s ± 109 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

edited May 25, 2021 at 11:37

answered May 25, 2021 at 11:18

Nk03

15k2 gold badges11 silver badges24 bronze badges

2 Comments

jezrael Over a year ago

NOTE: Not tested for large dataframes. - hmmm, so then what is reason of timings for small data?

Nk03 Over a year ago

Hmmm... make sense! Let me test for large dataframes, and add the results. :)

chsws · Accepted Answer · 2021-05-25 11:23:03Z

1

You can use pd.melt to stack the columns then explode it.

explode_columns = ['explode1', 'explode2']
pd.melt(
    frame=df,
    id_vars='word',
    value_vars=explode_columns,
    var_name='exploded_type',
    value_name='exploded_alphabet'
).explode('exploded_alphabet').dropna()

It doesn't retain the same order as above but the rows are the same.

answered May 25, 2021 at 11:23

chsws

4432 silver badges6 bronze badges

Collectives™ on Stack Overflow

Pandas - Explode multiple columns in pandas and assign value based on the exploded column

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related