0

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
ByHala
  • 81
  • 6
  • Please do not post several times the same question. The answer is in the duplicate, you need to use `df.explode('color')`. – mozway Apr 30 '22 at 21:39
  • @mozway You didn't understood my question and it's not an duplicate question. I know I can use 'df.explode('color')` it will expand the row but not increment +1 for relate product like `#VDY45P-01` – ByHala Apr 30 '22 at 21:44
  • see my second product have two variant color. so the sku will be somethings like this `VFY55P-01` for black and `VFY55P-02` for orange – ByHala Apr 30 '22 at 21:46
  • 1
    Ok I see, my bad, but that why it's important to provide code and a detailed description, not just to dump the input/output. I reopened. – mozway Apr 30 '22 at 21:51

1 Answers1

1

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
mozway
  • 194,879
  • 13
  • 39
  • 75
  • mozway Thanks for your help. It's working. can you please explain your third line of code. So we can understand how it's working. what groupby,astype,zfill doing here? – ByHala Apr 30 '22 at 22:01
  • Explode duplicates the indices, so we group by those to get the cumcount, add 1, convert the integers to string, fill with zeros to have 2 digits. – mozway Apr 30 '22 at 22:02