How to unify multiple columns in pandas dataframe into a multiindex?

Question

I have a dataframe like this:

pd.DataFrame(data={"a": [1,2], "b": [3,4], "c": [5,6]}, index=[0,1])

In tabular form:

   a  b  c
0  1  3  5
1  2  4  6

I want to transform it to a dataframe like this:

pd.DataFrame(data={"foo":[1,2,3,4,5,6]},
             index=pd.MultiIndex.from_tuples(
               [('a', 0), ('a', 1), ('b', 0), ('b', 1), ('c', 0), ('c', 1)],
               names=("var", "id")))

tabular form:

         foo
var id     
a   0      1
    1      2
b   0      3
    1      4
c   0      5
    1      6

What is the easiest way to achieve this?

BENY · Accepted Answer · 2022-06-23 13:28:36Z

2

Try unstack

out = df.unstack().to_frame('foo')
Out[146]: 
     foo
a 0    1
  1    2
b 0    3
  1    4
c 0    5
  1    6

answered Jun 23, 2022 at 13:28

BENY

324k22 gold badges176 silver badges250 bronze badges

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

2 Comments

Umar.H Over a year ago

wow much better than mine

BENY Over a year ago

@Umar.H yours good as well ~ :-)

Umar.H · Accepted Answer · 2022-06-23 13:28:05Z

2

First create a new multiindex

df.columns = pd.MultiIndex.from_product([df.columns.tolist(), ['foo']])

print(df)

    a   b   c
  foo foo foo
0   1   3   5
1   2   4   6

then use .stack with .swaplevel()

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

answered Jun 23, 2022 at 13:28

Umar.H

23.1k7 gold badges50 silver badges94 bronze badges

Collectives™ on Stack Overflow

How to unify multiple columns in pandas dataframe into a multiindex?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related