Pandas split column to columns based on comma . but add to existing columns

Question

I have a data unformatted like below in a pandas dataframe. The 3rd row is supposed to be split in 3 columns. But it is added in col1. I have read this data from an excel file.

 Col1                                            Col2             Col3
 0.500L PET X12                                  1.0L PET X12
 0.250L RGB X12                                  0.330L RGB X12
 0.330L CAN X24, 0.330L CAN X10, 0.330L CAN X12,

What is the best method to get this data formatted as below?

 Col1             Col2              Col3
 0.500L PET X12   1.0L PET X12
 0.250L RGB X12   0.330L RGB X12
 0.330L CAN X24   0.330L CAN X10    0.330L CAN X12,

Right now str.split combined with fillna for each col i used. As the dataframe has around 20 columns to combine. I am looking for an alternate method if any.

jezrael · Accepted Answer · 2021-04-08 09:32:05Z

1

One idea is add , to each column, then join with replace NaN and last split with n=2 for splitting to 3 new columns and last remove possible , :

df['Col1'] = (df['Col1'].add(', ').fillna('') +
              df['Col2'].add(', ').fillna('') +
              df['Col3'].add(', ').fillna(''))

f = lambda x: x.str.strip(', ')
df[['Col1','Col2','Col3']] = df['Col1'].str.split(', ', n=2, expand=True).apply(f)
    
print (df)
             Col1            Col2            Col3
0  0.500L PET X12    1.0L PET X12                
1  0.250L RGB X12  0.330L RGB X12                
2  0.330L CAN X24  0.330L CAN X10  0.330L CAN X12

edited Apr 8, 2021 at 9:32

answered Apr 8, 2021 at 9:26

jezrael

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

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

Collectives™ on Stack Overflow

Pandas split column to columns based on comma . but add to existing columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related