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

Time:06-23

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?

CodePudding user response:

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)

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

CodePudding user response:

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
  • Related