Pandas gropby multiple columns and multiple aggregations-CodePudding

Current DF

Product Nr	Product Name	Sales Rev	Sales Qty
Product1	ProductX	11,1
Product2	ProductY	22,2
Product1	ProductX		1
Product2	ProductY		2

Attempting to group by columns Product Nr and Product Name, and get sum of Sales Rev and Sales Qty. Expected result:

Product Nr	Product Name	Sales Rev	Sales Qty
Product1	ProductX	11,1	1
Product2	ProductY	22,2	2

Attempted code:

df.groupby(['Product Nr', 'Product Name']).agg({'Sales Rev': 'sum', 'Sales Qty':'sum' })

This returns only aggregated columns, without variable columns. Any suggestions?

CodePudding user response：

You can use the following code:

df.groupby('Product Nr', as_index=False).first()

Output:

  Product Nr Product Name Sales Rev  Sales Qty
0   Product1     ProductX      11,1        1.0
1   Product2     ProductY      22,2        2.0

CodePudding user response：

Another possible solution:

(df.groupby(['Product Nr', 'Product Name'], group_keys=False)
 .apply(lambda g: g.ffill().bfill()).drop_duplicates())

Output:

  Product Nr Product Name Sales Rev  Sales Qty
0  Product1     ProductX      11,1         1.0
1  Product2     ProductY      22,2         2.0