Convert column into rows in Pandas dataframe

Question

I have Pandas dataframe with two columns. One is unique identifier and second is the name of product attached to this unique identifier. I have duplicate values for identifier and product names. I want to convert one column of product names into several columns without duplicating identifier. Maybe I need to aggregate product names through identifier.

My dataframe looks like:

ID  Product_Name
100  Apple
100  Banana
200  Cherries
200  Apricots
200  Apple
300  Avocados

I want to have dataframe like this:

ID 
100  Apple Banana
200  Cherries Apricots Apple
300  Avocados

Each product along each identifier has to be in separate column

I tried pd.melt, pd.pivot, pd.pivot_table but only errors and this errors says No numeric types to aggregate

Any idea how to do this?

jezrael · Accepted Answer · 2018-11-05 12:56:04Z

4

Use cumcount for new columns names to MultiIndex by set_index and reshape by unstack:

df = df.set_index(['ID',df.groupby('ID').cumcount()])['Product_Name'].unstack()

Or create Series of lists and new DataFrame by contructor:

s = df.groupby('ID')['Product_Name'].apply(list)
df = pd.DataFrame(s.values.tolist(), index=s.index)

print (df)
            0         1      2
ID                            
100     Apple    Banana    NaN
200  Cherries  Apricots  Apple
300  Avocados       NaN    NaN

But if want 2 column DataFrame:

df1 = df.groupby('ID')['Product_Name'].apply(' '.join).reset_index(name='new')
print (df1)
    ID                      new
0  100             Apple Banana
1  200  Cherries Apricots Apple
2  300                 Avocados

edited Nov 5, 2018 at 12:56

answered Nov 5, 2018 at 12:48

jezrael

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

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

Comments

AmilaMGunawardana · Accepted Answer · 2018-11-05 12:49:24Z

1

use pivot funtion pivoting it can do the required thing!!

answered Nov 5, 2018 at 12:49

AmilaMGunawardana

1,8752 gold badges16 silver badges34 bronze badges

Collectives™ on Stack Overflow

Convert column into rows in Pandas dataframe

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related