Expand Pandas columns with lists

user_4462161 0
Viewed 143

I have a dataframe with one row that looks like the following:

a   b   c   d   e
1   [2,4]   [2,7]   apple   orange

I know i how to do this with one list column, but wasn't sure how this changes with multiple list columns. I essentially want to expand the dataframe into n rows depending how many elements in each list. The number is always the equivalent between the columns with lists. So the example above would become:

a   b   c   d   e
1   2   2   apple   orange
1   4   7   apple   orange
pandas list python
4 Answers

Funny how a simple problem can be difficult:

(pd.DataFrame(df.loc[0,['b','c']].to_list(), columns=['b','c'])
   .join(df.loc[df.index.repeat(len(df.loc[0,'b'])),['a','d','e']].reset_index(drop=True))
)
user_42384080

An alternative way using explode to arrive at the solution:

ndf = pd.concat([df.explode('c').drop('b', axis=1), df.explode('b').drop('c', axis=1)], axis=1)

ndf.loc[:,~ndf.columns.duplicated()]
user_40086570

I'm not shure if this solution will work for your real dataframe. It seems to me, that this only works because of the simple example data.

import pandas as pd
import io

t = '''
a   b   c   d   e
1   [2,4,5]   [2,7,2]   apple   orange'''

# Setting up the dataframe with lists in `b` and `c`.
df = pd.read_csv(io.StringIO(t), sep='\s+', converters={'b': eval, 'c': eval})

df.apply(pd.Series.explode)

Out:

   a  b  c      d       e
0  1  2  2  apple  orange
0  1  4  7  apple  orange
0  1  5  2  apple  orange
user_142777220

if your lists are all the same length you can use the pandas dataframe constructor with a dictionary:

import pandas as pd

data = pd.Series([1, [2,4], [2,7], 'apple', 'orange'],
                  index=['a','b','c','d','e'])
data = pd.DataFrame(data).T
print(data, '\n\n')

output = pd.DataFrame(dict(zip(data.columns, data.loc[0])))
print(output)

   a       b       c      d       e
0  1  [2, 4]  [2, 7]  apple  orange


   a  b  c      d       e
0  1  2  2  apple  orange
1  1  4  7  apple  orange
user_8753430
Related