CODEWITHSUNDEEP
pythonpandasstackpivot
Saurabh Gokhale
Saurabh Gokhale

Reputation: 46405

Transpose and stacking levels in pandas dataframe

I am facing an issue with properly transposing and stacking multi-level columns in pandas dataframe.

Here's my initial dataset: 

1990                      1991     
Q1     Q2     Q3     Q4        Q1     Q2     Q3     Q4
A | B  A | B  A | B  A | B     A | B  A | B  A | B  A | B

I want to convert this multi-level columns (3 levels) into single column (flatten it basically).

Here's my expected output:

   Year   Quarter  A   B
   1990   Q1       a1  b1
          Q2       a2  b2
   ....   ...      ... ...
   1991 ..................

Below is the my code that I tried but it fails (output is not as expected). 

df.transpose().stack(level=0).to_frame().reset_index()

This expands Year, Quarter as expected but fails to properly align the remaining two columns of A and B.

Am I missing any stacking, unstacking or pivoting?

Upvotes: 1

Views: 874

Answers (1)

BENY
BENY

Reputation: 323266

You may check with stack with two levels

df.stack([0,1]).reset_index()

Data input 

df=pd.DataFrame(columns=idx,index=[1]).fillna(0)
df.stack([0,1])
           a  b
1 1900 q1  0  0
       q2  0  0
       q3  0  0
       q4  0  0
  1991 q1  0  0
       q2  0  0
       q3  0  0
       q4  0  0

Upvotes: 2

Related Questions