I have an pandas dataframe like this:

   product name             color                 sku
   
Men regular T-Shirt    White, Black, Orange      #VDY45P
Men  T-Shirt            Black, Orange            #VFY55P

my expected result will be like this:

Basically I want to add 1 end of my sku for related product.

 product name             color                 sku
   
Men regular T-Shirt    White                 #VDY45P-01
Men regular T-Shirt    Black                 #VDY45P-02
Men regular T-Shirt    Orange                #VDY45P-03 
Men  T-Shirt           Black                 #VFY55P-01
Men  T-Shirt           Orange                #VFY55P-02

CodePudding user response：

explode the column, and add the cumcount value per group:

(df
 .assign(color=lambda d: d['color'].str.split(',\s*'))
 .explode('color')
 .assign(sku=lambda d: d['sku'] '-' d.groupby(level=0) 
                                     .cumcount().add(1)
                                     .astype(str).str.zfill(2))
)

Output:

          product name   color         sku
0  Men regular T-Shirt   White  #VDY45P-01
0  Men regular T-Shirt   Black  #VDY45P-02
0  Men regular T-Shirt  Orange  #VDY45P-03
1          Men T-Shirt   Black  #VFY55P-01
1          Men T-Shirt  Orange  #VFY55P-02