Pandas: the best way for MultiIndex columns

Question

I'll start with the following DataFrame :

 df_1 = DataFrame({ "Cat1" : ["a", "b"], "Vals1" : [1,2] , "Vals2" : [3,4] }) df 

I want it to look like this:

And I can do it with this code:

 df_2 = ( pd.melt(df_1, id_vars=["Cat1"]) .T ) df_2.columns = ( pd.MultiIndex .from_tuples( list(zip(df_2.loc["Cat1", :] , df_2.loc["variable", :])) , names=["Cat1", None] ) ) df_2 = ( df_2 .loc[["value"], :] .reset_index(drop=True) .sortlevel(0, axis=1) ) df_2 

But there are so many steps here that I can smell the code, or at least something vaguely, not pandas-idiomatic, as if I'm missing something in the API. Running the equivalent for row-based indexes is just one step, for example via set_index() . (Note that I know that columns equivalent to set_index() remain an open problem ). Is there a better, more formal way to do this?

Answer 1

You can use stack() , to_frame() and T to transpose.

 df_1.set_index('Cat1').stack().to_frame().T Cat1 ab Vals1 Vals2 Vals1 Vals2 0 1 3 2 4 

Answer 2

Think of it as a transposed frame. Here you are:

 df.set_index('Cat1').unstack().swaplevel().sort_index().to_frame().T Out[46]: Cat1 ab Vals1 Vals2 Vals1 Vals2 0 1 3 2 4