Pandas groupby multiple columns, list of multiple columns

Question

I have the following data:

Invoice NoStockCode Description                         Quantity    CustomerID  Country
536365  85123A      WHITE HANGING HEART T-LIGHT HOLDER  6           17850       United Kingdom
536365  71053       WHITE METAL LANTERN                 6           17850       United Kingdom
536365  84406B      CREAM CUPID HEARTS COAT HANGER      8           17850       United Kingdom

I am trying to do a groupby so i have the following operation:

df.groupby(['InvoiceNo','CustomerID','Country'])['NoStockCode','Description','Quantity'].apply(list)

I want to get the output

|Invoice |CustomerID |Country        |NoStockCode              |Description                                                                                 |Quantity       
|536365| |17850      |United Kingdom |85123A, 71053, 84406B    |WHITE HANGING HEART T-LIGHT HOLDER, WHITE METAL LANTERN, CREAM CUPID HEARTS COAT HANGER     |6, 6, 8

Instead I get:

|Invoice |CustomerID |Country        |0         
|536365| |17850      |United Kingdom |['NoStockCode','Description','Quantity']

I have tried agg and other methods, but I haven't been able to get all of the columns to join as a list. I don't need to use the list function, but in the end I want the different columns to be lists.

Ben.T · Accepted Answer · 2018-07-29 20:49:34Z

37

I can't reproduce your code right now, but I think that:

print (df.groupby(['InvoiceNo','CustomerID','Country'], 
                  as_index=False)['NoStockCode','Description','Quantity']
          .agg(lambda x: list(x)))

would give you the expected output

answered Jul 29, 2018 at 20:49

Ben.T

29.7k6 gold badges39 silver badges57 bronze badges

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

3 Comments

shlomiLan Over a year ago

How can I use ['NoStockCode','Description','Quantity'] as a variable? something like: df.groupby(list_1, as_index=False)list_2.agg(lambda x: list(x))

Ben.T Over a year ago

can't try but does df.groupby(list_1, as_index=False)[list_2].agg(lambda x: list(x)) works?

shlomiLan Over a year ago

No, I add to create a dict with what action to do on each columns and use dataframe.groupby(self.get_index_keys()).agg(actions).reset_index() action could be lambda x: json.dumps(list(x), default=str) if x.item() else None or first

BENY · Accepted Answer · 2018-07-29 20:51:29Z

6

IIUC

df.groupby(['Invoice','CustomerID'],as_index=False)['Description','NoStockCode'].agg(','.join)
Out[47]: 
   Invoice  CustomerID                                        Description  \
0   536365       17850  WHITEHANGINGHEARTT-LIGHTHOLDER,WHITEMETALANTER...   
           NoStockCode  
0  85123A,71053,84406B

answered Jul 29, 2018 at 20:51

BENY

324k22 gold badges176 silver badges250 bronze badges

Comments

Syed · Accepted Answer · 2018-07-29 20:57:01Z

1

Try using a variation of the following:

df.groupby('company').product.agg([('count', 'count'), ('NoStockCode', ', '.join), ('Descrption', ', '.join), ('Quantity', ', '.join)])

answered Jul 29, 2018 at 20:57

Syed

767 bronze badges

Comments

unutbu · Accepted Answer · 2018-07-29 21:08:55Z

You could use pd.pivot_table with aggfunc=list:

import pandas as pd
df = pd.DataFrame({'Country': ['United Kingdom', 'United Kingdom', 'United Kingdom'],
                   'CustomerID': [17850, 17850, 17850],
                   'Description': ['WHITE HANGING HEART T-LIGHT HOLDER',
                                   'WHITE METAL LANTERN',
                                   'CREAM CUPID HEARTS COAT HANGER'],
                   'Invoice': [536365, 536365, 536365],
                   'NoStockCode': ['85123A', '71053', '84406B'],
                   'Quantity': [6, 6, 8]})

result = pd.pivot_table(df, index=['Invoice','CustomerID','Country'], 
                        values=['NoStockCode','Description','Quantity'], 
                        aggfunc=lambda x: ', '.join(map(str, x)))
print(result)

yields

                                                                         Description            NoStockCode Quantity
Invoice CustomerID Country                                                                                          
536365  17850      United Kingdom  WHITE HANGING HEART T-LIGHT HOLDER, WHITE META...  85123A, 71053, 84406B  6, 6, 8

Note that if Quantity are ints, you will need to convert them to strs before calling ', '.join. That is why map(str, x) was used above.

Collectives™ on Stack Overflow

Pandas groupby multiple columns, list of multiple columns

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related