2

I have data frame like this:

     title            price        weight
   0 Crloni Model145  $45,$50,$60  200gm,500gm,800gm

here $45 for 200gm, $50 for 500gm and $60 for 800 gm. My expected data frame will be look like this:

    title              price      weight
    0 Crloni Model145  $45        200gm 
    1 Crloni Model145  $50        500gm
    2 Crloni Model145  $60        800gm

But now I getting data frame like this

       title           price      weight
    0 Crloni Model145  $45        200gm 
    1 Crloni Model145  $45        500gm
    2 Crloni Model145  $45        800gm
    3 Crloni Model145  $50        200gm 
    4 Crloni Model145  $50        500gm
    5 Crloni Model145  $50        800gm
    6 Crloni Model145  $45        200gm 
    7 Crloni Model145  $45        500gm
    8 Crloni Model145  $45        800gm
    9 Crloni Model145  $60        200gm 
    10 Crloni Model145  $60        500gm
    11 Crloni Model145  $60        800gm

here is my code:

data['price']=data['price'].str.split(',')
data = data.explode('price')
data['weight']=data['weight'].str.split(',')
data = data.explode('weight')

updated question:

I applied Bill the Lizard solution. I am not getting any error but I am not seeing any price, weight in my csv when export the csv file.

data['price']=data['price'].str.split(',')
data['weight']=df['weight'].str.split(',')
data = data.set_index(['title']).apply(pd.Series.explode).reset_index()

data.to_csv('...mypath/data.csv')

see the pic: enter image description here

here is BENY solution but I am getting cannot reindex from a duplicate axis

data['price'] = data['price'].str.split(',')
data['weight'] = data['weight'].str.split(',')
out = data.explode(['price','weight'])
data['description'] = data['description'].mask(data['description].shift() == data['description'])

#update2

Bill the Lizard solution worked but I am not understanding why BENY solution not working? why I am getting cannot reindex from a duplicate axis this error when applying BENY solution

update3 few row of my original excel file

     category     title       price     weight             description
       Shirt    men-shirt    20,25,35    100gm,50gm,150gm   shirt description....   
      pant    men-pent    40,35,90    200gm,350gm,150gm   pant description....
boyenec
  • 1,405
  • 5
  • 29
  • Do you think `.explode` is deficient or your expectations are wrong? Can you make the DataFrame differently - do you have control of that? – wwii Sep 29 '21 at 15:04
  • @wwii I am start learning pandas and python. I searched on google and stack overflow for find solution but I didn't find any solution. I have no idea how to solve this problems so here I am posted my problem. Still now I am trying to find solution – boyenec Sep 29 '21 at 15:07
  • If **you** are making the DataFrame, it might be easier to *fix* during that step. You should include a minimal example of the data and the code you use to make the DataFrame. – wwii Sep 29 '21 at 15:09
  • @wwii can you please show me an example? – boyenec Sep 29 '21 at 15:10
  • I think you'll get that error when you try to explode multiple columns that don't have the same number of values in their lists. Can you add the original values for `price` and `weight` for the few title shown in your Excel? – Bill the Lizard Sep 29 '21 at 16:14
  • @Bill the Lizard see my updated question. I added few row of my original excel file – boyenec Sep 29 '21 at 16:27

2 Answers2

5

If you have a Pandas version prior to 1.3.0, where multi-column explode was added:

Since the lists after splitting the strings have the same number of elements, you can apply Series.explode to the price and weight columns to the the expected output.

import pandas as pd

df = pd.DataFrame({'title': ['Crloni Model145'],
                   'price': ['$45,$50,$60'],
                   'weight': ['200gm,500gm,800gm']})

df['price']=df['price'].str.split(',')
df['weight']=df['weight'].str.split(',')

df = df.set_index(['title']).apply(pd.Series.explode).reset_index()

print(df)

I set the index to title because I don't want explode to be applied to that column, then I reset the index at the end so title becomes a regular column again.

Output:

             title price weight
0  Crloni Model145   $45  200gm
1  Crloni Model145   $50  500gm
2  Crloni Model145   $60  800gm
Bill the Lizard
  • 398,270
  • 210
  • 566
  • 880
  • when export csv I am not seeing any price and weight. see the pic https://drive.google.com/file/d/1sbGDntZiG9SGVHtTkMCppApcRjnR4Zjt/view?usp=sharing – boyenec Sep 29 '21 at 15:45
  • @boyenec Please provide enough data in your example so we can reproduce this error. – Bill the Lizard Sep 29 '21 at 15:49
  • sure. I am updating my questing – boyenec Sep 29 '21 at 15:51
  • I updated my question – boyenec Sep 29 '21 at 16:00
  • how to apply this function `data['image-link'] = data['image-link'].mask(data['image-link'].shift() == data['image-link'])` so I can prevent expanding image row ? – boyenec Sep 29 '21 at 16:30
  • @boyenec Any columns that you don't want to expand should go in the index. `df.set_index(['title', 'image-link', ...])` – Bill the Lizard Sep 29 '21 at 16:40
  • getting error `ValueError: cannot reindex from a duplicate axis` when I added extra column. Only working when I have only three column in my csv title, weight and price. But I need to include others column in my csv – boyenec Sep 29 '21 at 17:40
4

Update your pandas and explode now can accept two columns

df['price'] = df['price'].str.split(',')
df['weight'] = df['weight'].str.split(',')
out = df.explode(['price','weight'])
BENY
  • 317,841
  • 20
  • 164
  • 234