This example data contains a column called Unit. Each Unit can have multiple Depts and each Dept can have multiple Employees under it.
Unit Dept Employee Month Days Amount Flag
0 BU1 D1 Emp1 Aug 2 10 P
1 BU1 D1 Emp1 Aug 4 15 T
2 BU1 D1 Emp1 Sep 3 25 T
3 BU1 D1 Emp2 Sep 1 30 P
4 BU1 D2 Emp1 Aug 3 20 P
5 BU1 D2 Emp3 Aug 4 10 P
6 BU1 D2 Emp3 Sep 5 5 P
7 BU1 D2 Emp3 Sep 6 5 T
8 BU1 D2 Emp3 Sep 2 25 T
9 BU2 D3 Emp2 Aug 6 15 P
10 BU2 D3 Emp2 Aug 7 15 T
11 BU2 D3 Emp2 Sep 2 10 T
12 BU2 D3 Emp2 Sep 2 10 P
13 BU2 D3 Emp2 Sep 3 20 P
14 BU2 D3 Emp4 Aug 1 20 P
15 BU2 D3 Emp4 Aug 4 20 P
16 BU2 D3 Emp4 Aug 2 20 T
17 BU2 D3 Emp4 Sep 4 5 P
18 BU2 D3 Emp4 Sep 6 15 P
I want to calculate the sum of Amount and sum of Days at each level in the above hierarchy ( Total, monthly and also separately for each value under Flag) using Python.
An Excel Pivot would look like this (This contains all information that I need except monthwise total irrespective of the Flag).
I finally need the output in the below json format (Employee level summary nested under Dept, Dept level summary nested under Unit, Unit level summary nested under Total summary). It is also fine if month name comes as a key in json instead of value.
{
"Monthly_Summary": [
{
"Month": "Aug",
"P_Amount": 95,
"P_Days": 20,
"T_Amount": 50,
"T_Days": 13,
"Total_Amount": 145,
"Total_Days": 33
},
{
"Month": "Sep",
"P_Amount": 85,
"P_Days": 21,
"T_Amount": 65,
"T_Days": 13,
"Total_Amount": 150,
"Total_Days": 34
}
],
"P_Amount": 180,
"P_Days": 41,
"T_Amount": 115,
"T_Days": 26,
"Total_Amount": 295,
"Total_Days": 67,
"Unit": {
"BU1": {
"Monthly_Summary": [
{
"Month": "Aug",
"P_Amount": 40,
"P_Days": 9,
"T_Amount": 15,
"T_Days": 4,
"Total_Amount": 55,
"Total_Days": 13
},
{
"Month": "Sep",
"P_Amount": 35,
"P_Days": 6,
"T_Amount": 55,
"T_Days": 11,
"Total_Amount": 90,
"Total_Days": 17
}
],
"P_Amount": 75,
"P_Days": 15,
"T_Amount": 70,
"T_Days": 15,
"Total_Amount": 145,
"Total_Days": 30,
"Dept": {
"D1": {
"Monthly_Summary": [
{
"Month": "Aug",
"P_Amount": 10,
"P_Days": 2,
"T_Amount": 15,
"T_Days": 4,
"Total_Amount": 25,
"Total_Days": 6
},
{
"Month": "Sep",
"P_Amount": 30,
"P_Days": 1,
"T_Amount": 25,
"T_Days": 3,
"Total_Amount": 55,
"Total_Days": 4
}
],
"P_Amount": 40,
"P_Days": 3,
"T_Amount": 40,
"T_Days": 7,
"Total_Amount": 80,
"Total_Days": 10,
"Employee": {
"Emp1": {
"Monthly_Summary": [
{
"Month": "Aug",
"P_Amount": 10,
"P_Days": 2,
"T_Amount": 15,
"T_Days": 4,
"Total_Amount": 25,
"Total_Days": 6
},
{
"Month": "Sep",
"P_Amount": 0,
"P_Days": 0,
"T_Amount": 25,
"T_Days": 3,
"Total_Amount": 25,
"Total_Days": 3
}
],
"P_Amount": 10,
"P_Days": 2,
"T_Amount": 40,
"T_Days": 7,
"Total_Amount": 50,
"Total_Days": 9
},
"Emp2" : {}
}
},
"D2": {}
}
},
"BU2": {}
}
}
I tried using multi-index pivot in pandas and tried to convert it to json, but couldn't get it in the desired json format and with all needed calculation.
Is there a way to perform this calculation and convert it to the above json format in one shot or is there any other efficient solution?
Thanks.