Search code examples
pandaspivot-tablemulti-index

Pandas Pivot Table with 3 levels of Multi-indexing

I have tried every way imaginable to take this data frame 

import pandas as pd

dataDict = {'State': ['Idaho', 'Wyoming', 'Montana', 'Idaho', 'Idaho', 'Wyoming', 'Montana', 'Idaho', 'Idaho', 'Wyoming', 'Montana', 'Idaho'],
            'City': ['Boise', 'Jackson', 'Missoula', 'Sandpoint', 'Boise', 'Jackson', 'Missoula', 'Sandpoint', 'Boise', 'Jackson', 'Missoula', 'Sandpoint'],
            'Years': [2010, 2010, 2010, 2010, 2011, 2011, 2011, 2011, 2012, 2012, 2012, 2012],
            'PizzaOrdered' : [3000, 50, 1000, 78, 3250, 75, 1250, 82, 4000, 98, 4100, 92],
            'TacosOrdered' : [5000, 65, 1900, 88, 5780, 78, 2128, 90, 6125, 87, 5999, 95]
            }
testData = pd.DataFrame(data=dataDict)

enter image description here

And transform it to this

enter image description here

I have tried pivots, groupby, set_index, stacking, unstacking and I can get close but not quiet to this example I have provided below

Solution

  • You need melt before unstack

    df_final = (testData.melt(['State', 'City', 'Years'], var_name='Ordered')
                    .set_index(['State', 'City', 'Ordered', 'Years'])['value']
                    .unstack())

Out[54]:
Years                           2010  2011  2012
State   City      Ordered
Idaho   Boise     PizzaOrdered  3000  3250  4000
                  TacosOrdered  5000  5780  6125
        Sandpoint PizzaOrdered    78    82    92
                  TacosOrdered    88    90    95
Montana Missoula  PizzaOrdered  1000  1250  4100
                  TacosOrdered  1900  2128  5999
Wyoming Jackson   PizzaOrdered    50    75    98
                  TacosOrdered    65    78    87