[FIXED] How to Unpivot a Pandas PivotTable?

Issue

I have the following pandas pivot table, and would like to shift only the 'Date' Column Header to a row.

Date     JAN2022             FEB2022    
   ID    Income  Expenses    Income  Expenses
0   A     100       -23          10       -53
1   B      90       -20         280       -70

Desired outcome:

      Date  ID  Income  Expenses  
0  JAN2022   A     100       -23           
1  JAN2022   B      90       -20           
2  FEB2022   A      10       -53        
3  FEB2022   B     280       -70

-- Edited --

Output of df.to_dict()

{('FEB2022', 'Expenses'): {0: -53, 1: -70},
 ('FEB2022', 'Income'): {0: 10, 1: 280},
 ('ID', ''): {0: 'A', 1: 'B'},
 ('JAN2022', 'Expenses'): {0: -23, 1: -20},
 ('JAN2022', 'Income'): {0: 100, 1: 90}}

Creation of sample dataframe

data={'Date':['JAN2022','JAN2022','FEB2022','FEB2022'],'ID':['A','B','A','B'],'Income':[100,90,10,280],'Expenses':[-23,-20,-53,-70]}
df = pd.DataFrame(data)
df = pd.pivot_table(df, index='ID', values=['Income','Expenses'], columns='Date', aggfunc='sum').swaplevel(0, 1, 1).sort_index(1).reset_index()

Solution

I'm assuming you have MultiIndex dataframe where the first column has column index ("", "ID") - the first level of this column is empty string:

df = df.set_index(("", "ID")).stack(level=0)
df.index.names = ["ID", "Date"]
print(df.reset_index())

Prints:

  ID     Date  Expenses  Income
0  A  FEB2022       -53      10
1  A  JAN2022       -23     100
2  B  FEB2022       -70     280
3  B  JAN2022       -20      90

---

EDIT: With the new data just swap the "ID", "":

df = df.set_index(("ID", "")).stack(level=0)
df.index.names = ["ID", "Date"]

print(df.reset_index())

Prints:

  ID     Date  Expenses  Income
0  A  FEB2022       -53      10
1  A  JAN2022       -23     100
2  B  FEB2022       -70     280
3  B  JAN2022       -20      90

Answered By - Andrej Kesely

Answer Checked By - Robin (PHPFixing Admin)