Set index of multiple columns in pandas

Question

I am doing a dataframe as follows.

df = pd.DataFrame({ 'class' : ['A', 'A', 'A', 'A', 'A', 'B', 'B', 'B', 'B', 'B'], 'number' : [1,2,3,4,5,1,2,3,4,5], 'math' : [90, 20, 50, 30, 57, 67, 89, 79, 45, 23], 'english' : [40, 21, 68, 89, 90, 87, 89, 54, 21, 23] }) 

and I want to convert the index to this using some pandas methods (e.g. set_index, stack ,,)

 df1 = pd.DataFrame(np.random.randint(1, 100, (5, 4)), columns = [['A', 'A', 'B', 'B'],['english', 'math', 'english', 'math']], index = [1, 2, 3, 4, 5]) 

How can i do this?

Answer 1

I think you need set_index with unstack to change, then change the levels in MultiIndex in the swaplevel columns and the last sort_index sort sort_index :

 df1 = df.set_index(['number','class']).unstack().swaplevel(0,1,1).sort_index(1) print (df1) class AB english math english math number 1 40 90 87 67 2 21 20 89 89 3 68 50 54 79 4 89 30 21 45 5 90 57 23 23 

Another solution with stack and unstack :

 print (df.set_index(['number','class']).stack().unstack([1,2])) class AB english math english math number 1 40 90 87 67 2 21 20 89 89 3 68 50 54 79 4 89 30 21 45 5 90 57 23 23 

Answer 2

I like @jezrael's answer a lot, but only for completeness - you can also use pandas.DataFrame.pivot_table instead of set_index + unstack :

 >>> df.pivot_table(index='number', columns='class').swaplevel(axis=1).sort_index(1) class AB english math english math number 1 40 90 87 67 2 21 20 89 89 3 68 50 54 79 4 89 30 21 45 5 90 57 23 23