I am having issue with formatting a dataframe that has been pivot'd with pandas.
If somebody could point me in the right direction to change the way a pandas pivot formats a data frame that would be amazing. I am currently using the following formula for the pivot table creation:
tempTable = pd.pivot_table(newArr, values=['TotalTime', 'AvailTime'], margins = True, index=['UserID','Workgroup','Date'], aggfunc='sum')
Which produces the correct data, but in the incorrect format..
So I need to change it from this format:
| UserID | Workgroup | Date | AvailTime | TotalTime |
|---|---|---|---|---|
| James | Customer Service | 2021-08-29 | 9000 | 10000 |
| 2021-08-30 | 12000 | 15000 | ||
| Service Delivery | 2021-08-29 | 9000 | 90000 | |
| 2021-08-30 | 7000 | 12000 | ||
| Allen | Customer Service | 2021-08-29 | 9000 | 10000 |
| 2021-08-30 | 12000 | 15000 | ||
| Service Delivery | 2021-08-29 | 9000 | 90000 | |
| 2021-08-30 | 7000 | 12000 |
To this format:
| UserID | Workgroup | Date | AvailTime | TotalTime |
|---|---|---|---|---|
| James | Customer Service | 2021-08-29 | 9000 | 10000 |
| James | Customer Service | 2021-08-30 | 12000 | 15000 |
| James | Service Delivery | 2021-08-29 | 9000 | 90000 |
| James | Service Delivery | 2021-08-30 | 7000 | 12000 |
| Allen | Customer Service | 2021-08-29 | 9000 | 10000 |
| Allen | Customer Service | 2021-08-30 | 12000 | 15000 |
| Allen | Service Delivery | 2021-08-29 | 9000 | 90000 |
| Allen | Service Delivery | 2021-08-30 | 7000 | 12000 |
Long story short, I need the data stored in that format to get excel to interact with it correctly for xlookups etc (as from a best practice standpoint, you should not be storing information in merged cells when calculating in excel).