pandas groupby object to dataframe

Question

import pandas as pd
df1 = pd.DataFrame( {"name" : ["name1", "name1", "name2", "name3", "name3" , "name3"], "item" : ["item1", "item2", "item3", "item2", "item3", "item1"] } )
df2 = df1.groupby('name')['item']

In this groupby object I have all the items for each name. I can visualise this through:

print(df2.apply(list))

and get:

name
name1           [item1, item2]
name2                  [item3]
name3    [item2, item3, item1]

what I, however, would like to achieve is something like this:

name     itemA    itemB    itemC
name1    item1    item2    NaN
name2    item3    NaN      NaN
name3    item2    item3    item1

Any help is much appreciated.

BENY · Accepted Answer · 2018-02-12 15:49:20Z

2

Adding apply pd.Series at the end of your df2

df2.apply(list).apply(pd.Series)
Out[300]: 
           0      1      2
name                      
name1  item1  item2    NaN
name2  item3    NaN    NaN
name3  item2  item3  item1

answered Feb 12, 2018 at 15:49

BENY

324k22 gold badges176 silver badges250 bronze badges

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

3 Comments

Christian Over a year ago

Excellent. That's perfect. Thanks a lot.

BENY Over a year ago

@Christian yw :-)

Jochen Over a year ago

To me it seems like the object is not as asked for. The example has a flat header structure, while the answers result has the multi index structure. Can one flatten it out well?

Shahriar · Accepted Answer · 2018-02-12 15:58:07Z

-1

Added column name in Wen's answer

>>> df3 = df2.apply(list).apply(pd.Series)
>>> df3.columns = ['itemA', 'itemB', 'itemC']
# df3 = df3.rename(columns={0: 'itemA', 1: 'itemB', 2: 'itemC'})
>>> df3
       itemA  itemB  itemC
name                      
name1  item1  item2    NaN
name2  item3    NaN    NaN
name3  item2  item3  item1

answered Feb 12, 2018 at 15:58

Shahriar

13.9k11 gold badges83 silver badges97 bronze badges

Collectives™ on Stack Overflow

pandas groupby object to dataframe

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related