PHPFixing
  • Privacy Policy
  • TOS
  • Ask Question
  • Contact Us
  • Home
  • PHP
  • Programming
  • SQL Injection
  • Web3.0

Tuesday, November 1, 2022

[FIXED] How to Unpivot a Pandas PivotTable?

 November 01, 2022     pandas, pivot, pivot-table, python, unpivot     No comments   

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)
  • Share This:  
  •  Facebook
  •  Twitter
  •  Stumble
  •  Digg
Newer Post Older Post Home

0 Comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Total Pageviews

Featured Post

Why Learn PHP Programming

Why Learn PHP Programming A widely-used open source scripting language PHP is one of the most popular programming languages in the world. It...

Subscribe To

Posts
Atom
Posts
Comments
Atom
Comments

Copyright © PHPFixing