Groupby and Sum over single column and find max

user_6433882 0
Viewed 59

I'm working on a df like this:

InvoiceNo StockCode              Description             Quantity  InvoiceDate         UnitPrice  CustomerID
536365    85123A                       WHITE T-LIGHT          6   2010-12-01 08:26:00       2.55     17850.0
536365     71053                  WHITE METAL LANTERN         6   2010-12-01 08:26:00       3.39     17850.0
536365    84406B                          COAT HANGER         8   2010-12-01 08:26:00       4.73     17850.0
536368    84029G                     HOT WATER BOTTLE         6   2010-12-01 09:41:00       9.11     12391.0
...

I need to find the StockCode which has sold the most quantity. I tried the following code:

clean_data.groupby(['StockCode']).sum().sort_values('Quantity', ascending=False)

But this gives me the sum over the other columns as well, which I don't want. I also tried using .idxmax() to find the max value with the previous statement, but I don't think the answer is accurate.

I also need to find the number of unique items sold per transaction. So, the number of rows for every unique (InvoiceNo, CustomerID) pair, and have no idea how to start with this. Some insight would be appreciated.

Thanks in advance!

pandas-groupby pandas
3 Answers

For the first part, you could try this - 

clean_data.groupby(['StockCode'])['Quantity'].sum().idxmax()

For the second part, try this - 

clean_data.groupby(['InvoiceNo', 'CustomerID'])['StockCode'].nunique()
user_108587860

IIUC:

code_with_max_quant = clean_data.groupby('StockCode')['Quantity'].sum().idxmax()
num_row_with_code = clean_data['StockCode'].eq(code_with_max_quant).sum()

# all rows with max code
clean_data[ clean_data['StockCode'].eq(code_with_max_quant)]

Output:

   InvoiceNo StockCode  Description  Quantity          InvoiceDate  UnitPrice  \
2     536365    84406B  COAT HANGER         8  2010-12-01 08:26:00       4.73   

   CustomerID  
2     17850.0
user_42384080

This will give you a pandas series ordering the 'Stock Code' and total quantity from largest to smallest.

clean_data.groupby('StockCode')['Quantity'].sum()

For the second part:

clean_data.groupby(['InvoiceNo', 'CustomerID'])['StockCode'].unique()

Will give you a series grouped by 'InvoiceNo', then by 'CustomerID' and the unique values of the 'StockCode' for each transaction

user_129395580
Related