pandas reshape only two columns
I would suggest to use a pivot_table
with a MultiIndex:
df2 = ( df.pivot_table(index='Provider', columns='Month', fill_value=0, aggfunc='sum', sort=False)
.swaplevel(axis=1)
.sort_index(axis=1, level='Month', sort_remaining=False)
)
Output:
Month February January
Active Acute Hospital Total Active Acute Hospital Total
Provider
Joe 1 0 4 5 0 1 0 1
Mary 5 6 8 19 0 0 0 0
Susie 0 0 0 0 5 2 4 11
If you insist on having the provided format, you can rework the column headers (but will have ambiguous duplicated names):
df2.columns = df2.columns.map(lambda x: x[0] if x[1]=='Active' else x[1])
Output:
February Acute Hospital Total January Acute Hospital Total
Provider
Joe 1 0 4 5 0 1 0 1
Mary 5 6 8 19 0 0 0 0
Susie 0 0 0 0 5 2 4 11