How to explode the column value without duplicating the other columns values in panda dataframe?

user_5037278 0
Viewed 527

I have df like this:

id       ColumnA           ColumnB           ColumnC
1        Audi_BMW_VW       BMW_Audi             VW
2          VW                 Audi            Audi_BMW_VW

I want to explode the columns based on explode when _ appear. For example for "Column A" like this

df['Column A'].str.split('_')).explode('Column A')

but when i use similar query for column B then it repeats the values of column A, but i really want that only ID should duplicate. The desired output would be something like this:

id       ColumnA           ColumnB           ColumnC
1        Audi                BMW             VW
1         BMW                Audi
1         VW                  
2         VW                 Audi            Audi
2                                            BMW
2                                            VW
pandas python
3 Answers

Lots of reshaping. The key point is to stack then call Series.str.split on a single Series with the id as the Index.

(df.set_index('id')             # keep 'id' bound to cells in the row 
   .stack()                     # to a single Series
   .str.split('_', expand=True) # split into separate cells on '_'
   .unstack(-1).stack(0)        # original column labels back to columns
   .reset_index(-1, drop=True)  # remove split number label
)

   ColumnA ColumnB ColumnC
id                        
1     Audi     BMW      VW
1      BMW    Audi    None
1       VW    None    None
2       VW    Audi    Audi
2     None    None     BMW
2     None    None      VW
user_43333590

zip_longest

Very similar to a recent explode answer

from itertools import zip_longest

data = [
    (i, *tup)
    for i, *other in df.itertuples(index=False)
    for tup in zip_longest(*[x.split('_') for x in other])
]

pd.DataFrame(data, columns=df.columns)

   id ColumnA ColumnB ColumnC
0   1    Audi     BMW      VW
1   1     BMW    Audi    None
2   1      VW    None    None
3   2      VW    Audi    Audi
4   2    None    None     BMW
5   2    None    None      VW
user_23366540

IIUC with melt and join after str.split, I am not change the column name , since the name here is no related to the original df.

s=df.melt('id')
s=s.join(s.value.str.split('_',expand=True))
s
Out[265]: 
   id variable        value     0     1     2
0   1  ColumnA  Audi_BMW_VW  Audi   BMW    VW
1   2  ColumnA           VW    VW  None  None
2   1  ColumnB     BMW_Audi   BMW  Audi  None
3   2  ColumnB         Audi  Audi  None  None
4   1  ColumnC           VW    VW  None  None
5   2  ColumnC  Audi_BMW_VW  Audi   BMW    VW
user_79645270
Related