How to pivot column index to row index in pandas?

Question

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?

@coldspeed hi thanks it worked perfectly!

Bob Fang
– Bob Fang

2019-01-12 20:58:45 +00:00
Commented Jan 12, 2019 at 20:58 — Bob Fang
– Bob Fang, Commented Jan 12, 2019 at 20:58

cs95 · Accepted Answer · 2019-01-12 17:39:46Z

4

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

answered Jan 12, 2019 at 17:39

cs95

406k106 gold badges744 silver badges797 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to pivot column index to row index in pandas?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related