At a dataframe how to explode a column with a list (with same length at all rows) into different columns at the same row

Question

I have following dataframe:

df=pd.DataFrame({'A': ['1','2', '3'], 'List': [['a1','a2'], ['b1','b2'], ['c1','c2']]})

Out[18]: 
   A      List
0  1  [a1, a2]
1  2  [b1, b2]
2  3  [c1, c2]

I would like to explode the column List into two new columns (L1 and L2) at the same row.

   A  L1  L2
0  1  a1  a2
1  2  b1  b2
2  3  c1  c2

Which would be the fastest way to do it?

It would be great to assign also the names for the columns at the same time (L1 and L2).

Thank you in advance and best regards,

Pablo G

Mark Wang · Accepted Answer · 2020-05-10 10:04:35Z

2

Try:

df[['A']].join(df['List'].apply(pd.Series, index=['L1', 'L2']))

answered May 10, 2020 at 10:04

Mark Wang

2,7579 silver badges18 bronze badges

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

1 Comment

Mark Wang Over a year ago

@CypherX always useful to check what pd.Series takes :)

CypherX · Accepted Answer · 2020-05-11 05:43:25Z

Solution

Try this: pd.concat + df[col].apply(pd.Series)

# Option-1
pd.concat([df['A'], df['B'].apply(pd.Series).rename(columns={0: 'L1', 1: 'L2'})], axis=1)

# Option-2
# credit: Mark Wang; for suggestion on using, index = ['L1', 'L2']
pd.concat([df['A'], df['B'].apply(pd.Series, index=['L1', 'L2'])], axis=1)

If you want to keep only the columns `L1` and `L2`

# Option-1
df['B'].apply(pd.Series).rename(columns={0: 'L1', 1: 'L2'})

# Option-2
# credit: Mark Wang; for suggestion on using, index = ['L1', 'L2']
df['B'].apply(pd.Series, index=['L1', 'L2'])

If you want to keep all the original columns

# with prefix
pd.concat([df, df['B'].apply(pd.Series).add_prefix(f'B_')], axis=1)

# with user given column-names
pd.concat([df, df['B'].apply(pd.Series).rename(columns={0: 'L1', 1: 'L2'})], axis=1)

Logic:

Concat df and df_expanded along the columns (axis=1).
Where, df_expanded is obtained by doing df[col].apply(pd.Series). This expands the lists into columns.
I added a .add_prefix('B_') to add clarity on where the columns originated from (column B).

Example

df = pd.DataFrame({'A': [1,2,3], 
                   'B': [['11', '12'], 
                         ['21', '22'], 
                         ['31', '32']]
                   })
col = 'B'
pd.concat([df, df[col].apply(pd.Series).add_prefix(f'{col}_')], axis=1)

Thank you it works great. Is there an additional paramenter to remove column 'A'?
If you want only columns L1 and L2, then don't do the concat. Just df['B'].apply(pd.Series).rename(columns={0: 'L1', 1: 'L2'}) should do the job.
Thans to your comments with further investigations I tryed this: df[['L1','L2']]=df['B'].apply(pd.Series) and it works.
Yes, that is because it assigns the created columns to two new columns in df. You can do these things in a lot of different ways. It’s up to the user to decide how he/she would choose to do so.

Collectives™ on Stack Overflow

At a dataframe how to explode a column with a list (with same length at all rows) into different columns at the same row

2 Answers 2

1 Comment

Solution

If you want to keep only the columns `L1` and `L2`

If you want to keep all the original columns

Example

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Solution

If you want to keep only the columns L1 and L2

If you want to keep all the original columns

Example

4 Comments

Related

If you want to keep only the columns `L1` and `L2`