Python pandas sum of rows grouped by multiple columns

Question

I have a dataframe with below structure,

| Date       |   Item | Location | Event   |
|------------|-------:|----------|---------|
| 01-06-2019 | Item_1 | Loc_1    | Event_1 |
| 01-06-2019 | Item_1 | Loc_1    | Event_1 |
| 02-06-2019 | Item_1 | Loc_1    | Event_1 |
| 02-06-2019 | Item_1 | Loc_1    | Event_2 |
| 02-06-2019 | Item_1 | Loc_2    | Event_2 |
| 02-06-2019 | Item_2 | Loc_1    | Event_3 |
| 03-06-2019 | Item_2 | Loc_1    | Event_3 |
| 03-06-2019 | Item_2 | Loc_1    | Event_3 |

I want to count the number of events occurred with reference to Item + Location in a day. Result as below,

| Date       |   Item | Location | Event_1 | Event_2 | Event_3 |
|------------|-------:|----------|---------|---------|---------|
| 01-06-2019 | Item_1 | Loc_1    | 2       | 0       | 0       |
| 02-06-2019 | Item_1 | Loc_1    | 1       | 1       | 0       |
| 02-06-2019 | Item_1 | Loc_2    | 0       | 1       | 0       |
| 02-06-2019 | Item_2 | Loc_1    | 0       | 0       | 1       |
| 03-06-2019 | Item_2 | Loc_1    | 0       | 0       | 2       |

Tried pandas pivot_table, could not get the result I want.

Thanks!

Pyd · Accepted Answer · 2019-06-27 07:23:32Z

6

Use crosstab with DataFrame.reset_index:

df1 = pd.crosstab([df['Date'], df['Item'], df['Location']], df['Event']).reset_index()
print (df1)
Event        Date    Item Location  Event_1  Event_2  Event_3
0      01-06-2019  Item_1    Loc_1        2        0        0
1      02-06-2019  Item_1    Loc_1        1        1        0
2      02-06-2019  Item_1    Loc_2        0        1        0
3      02-06-2019  Item_2    Loc_1        0        0        1
4      03-06-2019  Item_2    Loc_1        0        0        2

Alternative solution:

df1=df.groupby(['Date','Item','Location','Event']).size().unstack(level = -1,fill_value=0).reset_index()

edited Jun 27, 2019 at 7:23

Pyd

6,16919 gold badges59 silver badges117 bronze badges

answered Jun 27, 2019 at 6:52

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

Collectives™ on Stack Overflow

Python pandas sum of rows grouped by multiple columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related