Starting from an imported df from excel like that:
| Code | Material | Text | QTY |
|---|---|---|---|
| A1 | X222 | Model3 | 1 |
| A2 | 4027721 | Gruoup1 | 1 |
| A2 | 4647273 | Gruoup1.1 | 4 |
| A1 | 573828 | Gruoup1.2 | 1 |
I want to create a new pivot table like that:
| Code | Qty |
|---|---|
| A1 | 2 |
| A2 | 5 |
I tried with the following command but they do not work:
df.pivot(index='Code', columns='',values='Qty')
df_pivot = df ("Code").Qty([sum, max])
>Solution :
You don’t need pivot but groupby:
out = df.groupby('Code', as_index=False)['QTY'].sum()
# Or
out = df.groupby('Code')['QTY'].agg(['sum', 'max']).reset_index()
Output:
>>> out
Code sum max
0 A1 2 1
1 A2 5 4
The equivalent code with pivot_table:
out = (df.pivot_table('QTY', 'Code', aggfunc=['sum', 'max'])
.droplevel(1, axis=1).reset_index())