CODEWITHSUNDEEP
pythonpandas
user3054852
user3054852

Reputation: 189

Pandas unstacking - order of nested columns

I have a dataframe in the following format:

    a   b   c   v1  v2
0   a1  b1  c1  1   5
1   a2  b1  c1  2   6
2   a1  b2  c1  3   7
3   a2  b2  c1  4   8

What I want to achieve is this format:

        b1      b2
        v1  v2  v1  v2
a   c   
a1  c1  1   3   5   7
a2  c1  2   4   6   8

So, the original data is constructed as follows:

foo = pd.DataFrame({
    "a": ["a1", "a2", "a1", "a2"],
    "b": ["b1", "b1", "b2", "b2"],
    "c": ["c1", "c1", "c1", "c1"],
    "v1": [1,2,3,4],
    "v2": [5,6,7,8]
})

I then tried to

foo.set_index(["a", "b", "c"]).unstack("b")

However, this resulted in


        v1      v2
        b1  b2  b1  b2
a   c               
a1  c1  1   3   5   7
a2  c1  2   4   6   8

Subsequently, my question is how do I make the unstacked b column align itself ontop of the value columns instead of below them?

Upvotes: 1

Views: 235

Answers (2)

Mayank Porwal
Mayank Porwal

Reputation: 34086

You can also use pivot instead of using set_index and unstack with swaplevel:

In [565]: foo.pivot(["a", "c"], columns='b').swaplevel(0,1, axis=1)
Out[565]: 
b     b1 b2 b1 b2
      v1 v1 v2 v2
a  c             
a1 c1  1  3  5  7
a2 c1  2  4  6  8

For getting your output, you can do this:

In [2941]: foo.pivot(["a", "c"], columns='b').swaplevel(0,1, axis=1).sort_index(axis=1).rename_axis((None, None), axis=1)
Out[2941]: 
      b1    b2   
      v1 v2 v1 v2
a  c             
a1 c1  1  5  3  7
a2 c1  2  6  4  8

Upvotes: 6

jezrael
jezrael

Reputation: 863246

Use DataFrame.swaplevel with DataFrame.sort_index and DataFrame.rename_axis:

df = (foo.set_index(["a", "b", "c"]).unstack("b")
         .swaplevel(1, 0, axis=1)
         .sort_index(axis=1)
         .rename_axis((None, None), axis=1))
print (df)
      b1    b2   
      v1 v2 v1 v2
a  c             
a1 c1  1  5  3  7
a2 c1  2  6  4  8

Upvotes: 2

Related Questions