pandas Groupby sum and concatenate

user_9067786 0
Viewed 459

I have a dataframe as below

+-----------+----------+-----+
| InvoiceNo | ItemCode | Qty |
+-----------+----------+-----+
|  Inv-001  |     c    |  1  |
+-----------+----------+-----+
|  Inv-001  |     b    |  2  |
+-----------+----------+-----+
|  Inv-001  |     a    |  1  |
+-----------+----------+-----+
|  Inv-002  |     a    |  3  |
+-----------+----------+-----+
|  Inv-002  |     b    |  1  |
+-----------+----------+-----+
|  Inv-002  |     c    |  1  |
+-----------+----------+-----+
|  Inv-002  |     d    |  4  |
+-----------+----------+-----+
|  Inv-002  |     a    |  1  |
+-----------+----------+-----+
|  Inv-003  |     e    |  1  |
+-----------+----------+-----+
|  Inv-003  |     b    |  2  |
+-----------+----------+-----+

I want to calculate each individual InvoiceNo wise item combinations. i.e. sum of each ItemCode. sorted and concatenated to one string. Note : in Inv-002 product a has 2 lines.

My desired/required output is as below

+-----------+--------------------+
| InvoiceNo |   Desired result   |
+-----------+--------------------+
|  Inv-001  |    a-1, b-2, c-1   |
+-----------+--------------------+
|  Inv-002  | a-4, b-1, c-1, d-4 |
+-----------+--------------------+
|  Inv-003  |      b-2, e-1      |
+-----------+--------------------+

so far I have written the below code

#load data
df = pd.read_excel('data.xlsx')

#groupby and sum
g = df.groupby(['InvoiceNo','ItemCode']).sum()

# Codes to convert the MultiIndex to a regualr dataframe
g = g.unstack(fill_value=0)
g.reset_index(drop=True,inplace=True)
g = g.droplevel(level=0, axis=1).fillna(0)

#calculation
g.dot(g.columns+',').str[:-1]

below is the result I get. all the items separated.

+---+---------------------+
| 0 |       a,b,b,c       |
+---+---------------------+
| 1 | a,a,a,a,b,c,d,d,d,d |
+---+---------------------+
| 2 |        b,b,e        |
+---+---------------------+

please guide me to solve this.

pandas-groupby pandas python
2 Answers

groupby twice. The first gets the sum for each ['InvoiceNo', 'ItemCode']. Then we join the code and category together with a '-' and group over the Invoices to create the full string.

df1 = df.groupby(['InvoiceNo', 'ItemCode'])['Qty'].sum().reset_index('ItemCode')

df1 = df1['ItemCode'].str.cat(df1['Qty'].astype(str), '-').groupby(level=0).agg(', '.join)

#InvoiceNo
#Inv-001         a-1, b-2, c-1
#Inv-002    a-4, b-1, c-1, d-4
#Inv-003              b-2, e-1
#Name: ItemCode, dtype: object

You will notice I didn't need to sort anything. This is because groupby by default sorts the grouping keys, so after the first line the Series is guaranteed to be sorted on ['InvoiceNo', 'ItemCode'], which is what we want before we ', '.join

user_43333590

Here you go:

df1 = df.groupby(['InvoiceNo', 'ItemCode'], sort=False).Qty.sum().reset_index()
df1['Desired result'] = df1.ItemCode + '-' + df1.Qty.astype(str)
print(df1.groupby(['InvoiceNo'])['Desired result'].apply(lambda res: ', '.join(sorted(res))).reset_index())

Output:

  InvoiceNo      Desired result
0   Inv-001       a-1, b-2, c-1
1   Inv-002  a-4, b-1, c-1, d-4
2   Inv-003            b-2, e-1
user_126114090
Related