Converting or transposing Multi Index dataframe index column to header identifier

Question

I currently have a multi index dataframe where two columns are acting as the indexes, column 'Date', and 'Product'. And two additional columns acting as values, columns 'Sales', 'Cost'. . However, I would like to convert one of the index columns as the header identifier, while the other index column acts as the row identifier.

df2 = pd.DataFrame({'Date':['10-31','10-31','10-31','12-31','12-31','12-31'],'Product':['Apple','Pear','Banana','Apple','Pear','Banana'], 'Sales':[1, 0.8, 1.2, 2, 0.9, 1.7], 'Cost':[0.5, 0.3, 0.6, 0.4, 0.4, 0.7]})

Setting 'Date' and 'Product' as index columns:

` df2.set_index(['Date', 'Product'], inplace=True)

df2 `

The multi index dataframe looks like this:

Date	Product	Sales	Cost
10-31	Apple	1	0.5
	Pear	0.8	0.3
	Banana	1.2	0.6
12-31	Apple	2	0.4
	Pear	0.9	0.4
	Banana	1.7	0.7

But I want to transpose the 'Product' index column as the header identifier, whilst still keeping the 'Data' index column as the row identifier. Something like this:

         | Apple              | Pear               | Banana   |

Date	Sales	Cost	Sales	Cost	Sales	Cost
10-31	1	0.5	0.8	0.3	1.2	0.6
12-31	2	0.4	0.9	0.4	1.7	0.7

Answer 1

Use DataFrame.set_index with DataFrame.unstack, then swap levels of MultiIndex by DataFrame.swaplevel and sort MultiIndex by DataFrame.sort_index with first level:

df2 = pd.DataFrame({'Date':['10-31','10-31','10-31','12-31','12-31','12-31'],'Product':['Apple','Pear','Banana','Apple','Pear','Banana'], 'Sales':[1, 0.8, 1.2, 2, 0.9, 1.7], 'Cost':[0.5, 0.3, 0.6, 0.4, 0.4, 0.7]})

---

df = (df2.set_index(['Date', 'Product'])
      .unstack()
      .swaplevel(0,1, axis=1)
      .sort_index(axis=1, level=0, sort_remaining=False))
print (df)
Product Apple      Banana       Pear     
        Sales Cost  Sales Cost Sales Cost
Date                                     
10-31     1.0  0.5    1.2  0.6   0.8  0.3
12-31     2.0  0.4    1.7  0.7   0.9  0.4