I need to calculate Total Hours and Hours by Status per Week using Python / Pandas GROUP BY.
JavaScript
x
13
13
1
Id Week Status Hours
2
3
1 01/10/2022 - 01/16/2022 On 5
4
2 01/10/2022 - 01/16/2022 Off 2
5
3 01/17/2022 - 01/23/2022 Off 6
6
4 01/17/2022 - 01/23/2022 On 1
7
5 01/17/2022 - 01/23/2022 On 5
8
6 01/03/2022 - 01/09/2022 On 10
9
7 01/10/2022 - 01/16/2022 Off 9
10
8 01/03/2022 - 01/09/2022 On 3
11
9 01/24/2022 - 01/30/2022 Off 4
12
10 01/24/2022 - 01/30/2022 On 7
13
JavaScript
1
7
1
test_data = {'Id': [1,2,3,4,5,6,7,8,9,10],
2
'Week': ['01/10/2022 - 01/16/2022', '01/10/2022 - 01/16/2022', '01/17/2022 - 01/23/2022', '01/17/2022 - 01/23/2022', '01/17/2022 - 01/23/2022', '01/03/2022 - 01/09/2022', '01/10/2022 - 01/16/2022', '01/03/2022 - 01/09/2022', '01/24/2022 - 01/30/2022', '01/24/2022 - 01/30/2022'],
3
'Status': ['On', 'Off', 'Off', 'On', 'On', 'On', 'Off', 'On', 'Off', 'On'],
4
'Hours': [5,2,6,1,5,10,9,3,4,7]}
5
6
test_df = pd.DataFrame(data=test_data)
7
I can get Total Hours by each Week:
JavaScript
1
2
1
test_df.groupby(by=['Week'], as_index=False).agg({"Hours": "sum"})
2
But I don’t know how to also group by Status, so it will be 2 additional columns (On Status Hours and Off Status Hours)
If I add Status column just to the groupby part, it creates extra rows (I understand why)
JavaScript
1
2
1
test_df.groupby(by=['Week', 'Status'], as_index=False).agg({"Hours": "sum"})
2
Output I want:
Week | Total Hours | On Status Hours | Off Status Hours |
---|---|---|---|
01/03/2022 – 01/09/2022 | 13 | 13 | 0 |
01/10/2022 – 01/16/2022 | 16 | 5 | 11 |
01/17/2022 – 01/23/2022 | 12 | 6 | 6 |
01/24/2022 – 01/30/2022 | 11 | 7 | 4 |
Advertisement
Answer
You can use:
JavaScript
1
8
1
(test_df
2
.groupby(['Week', 'Status'])['Hours']
3
.sum()
4
.unstack(1, fill_value=0)
5
.add_suffix(' Status Hours')
6
.assign(**{'Total Hours': lambda d: d.sum(1)})
7
)
8
Output:
JavaScript
1
7
1
Status Off Status Hours On Status Hours Total Hours
2
Week
3
01/03/2022 - 01/09/2022 0 13 13
4
01/10/2022 - 01/16/2022 11 5 16
5
01/17/2022 - 01/23/2022 6 6 12
6
01/24/2022 - 01/30/2022 4 7 11
7