CODEWITHSUNDEEP
pythonpandas
Bob Fang
Bob Fang

Reputation: 7411

How to pivot column index to row index in pandas?

I have a pandas dataframe that looks like this:

           A           B
Date       a   b   c   a    b   c 
20180101   1   1   1   365 365 365
20180102   2   2   2   364 364 364
...      ... ... ...  ...  ... ...
20181231 365 365 365    1    1   1

You can see I have a multilevel column index. However, I want to transform it into the following format:


                    a   b   c
Date     Catelog1
20180101        A   1   1   1
                B 365 365 365
20180102        A   2   2   2
                B 364 364 364 
...           ... ... ... ... 
20181231        A 365 365 365 
                B   1   1   1

However this is not obvious to me that how we can achieve this, moreover, what if I have more than two levels on the column axis and I only want to keep the one at the bottom and move all the above indexes on the row axis?

Upvotes: 2

Views: 587

Answers (1)

cs95
cs95

Reputation: 402814

Setup

idx = pd.MultiIndex.from_product([list('AB'), list('abc')])
df = pd.DataFrame(0, columns=idx, index=list('pqrs'))

df
   A        B      
   a  b  c  a  b  c
p  0  0  0  0  0  0
q  0  0  0  0  0  0
r  0  0  0  0  0  0
s  0  0  0  0  0  0

Use swaplevel and stack:

df.swaplevel(0, 1, axis=1).stack()

     a  b  c
p A  0  0  0
  B  0  0  0
q A  0  0  0
  B  0  0  0
r A  0  0  0
  B  0  0  0
s A  0  0  0
  B  0  0  0

Another alternative is stack with parameter level=0:

df.stack(level=0)

     a  b  c
p A  0  0  0
  B  0  0  0
q A  0  0  0
  B  0  0  0
r A  0  0  0
  B  0  0  0
s A  0  0  0
  B  0  0  0

Upvotes: 4

Related Questions