Explode single DataFrame row into multiple ones

user_5296106 0
Viewed 73

My DataFrame has some columns where each value can be "1", "2", "3" or "any". Here is an example:

>>> df = pd.DataFrame({'a': ['1', '2', 'any', '3'], 'b': ['any', 'any', '3', '1']})
>>> df
     a    b
0    1  any
1    2  any
2  any    3
3    3    1

In my case, "any" means that the value can be "1", "2" or "3". I would like to generate all possible rows using only values "1", "2" and "3" (or, in general, any list of values that I might have). Here is the expected output for the example above:

   a  b
0  1  1
1  1  2
2  1  3
3  2  1
4  2  2
5  2  3
6  3  3
7  3  1

I got this output with this kind of ugly and complicated approach:

a = df['a'].replace('any', '1,2,3').apply(lambda x: eval(f'[{str(x)}]')).explode()
result = pd.merge(df.drop(columns=['a']), a, left_index=True, right_index=True)
b = result['b'].replace('any', '1,2,3').apply(lambda x: eval(f'[{str(x)}]')).explode()
result = pd.merge(result.drop(columns=['b']), b, left_index=True, right_index=True)
result = result.drop_duplicates().reset_index(drop=True)

Is there any simpler and/or nicer approach?

pandas dataframe python-3.x python
2 Answers

You can replace the string any with, e.g. '1,2,3', then split and explode:

(df.replace('any', '1,2,3')
   .apply(lambda x: x.str.split(',') if x.name in ['a','b'] else x)
   .explode('a').explode('b')
   .drop_duplicates(['a','b'])
)

Output:

   a  b  c
0  1  1  1
0  1  2  1
0  1  3  1
1  2  1  1
1  2  2  1
1  2  3  1
2  3  3  1
3  3  1  1
user_42384080

I would not use eval and string manipulations, but just replace 'any' with a set of values

import pandas as pd
df = pd.DataFrame({'a': ['1', '2', 'any', '3'], 'b': ['any', 'any', '3', '1']})
df['c'] = '1'

df[df == 'any'] = {'1', '2', '3'}
for col in df:
    df = df.explode(col)
df = df.drop_duplicates().reset_index(drop=True)
print(df)

This gives the result

   a  b  c
0  1  2  1
1  1  3  1
2  1  1  1
3  2  2  1
4  2  3  1
5  2  1  1
6  3  3  1
7  3  1  1
user_45151140
Related