Pandas pivot table, creating ad hoc columns per dimension values

Question

I'm new to pivot tables and have the following dataset:

mydict = {'City' : ['Lexington', 'Lexington', 'Louisville', 'Hartford', 'Portland', 'Dallas'],
          'State': ['KY', 'KY', 'KY', 'CT', 'ME', 'TX'],
          'Zip': ['38293', '38293', '40207', '48488', '55849', '44930'],
          'Region': ['South', 'South', 'South', 'Northeast', 'Northeast', 'South'],
          'Sales': [1000, 2000, 3000, 1500, 2000, 2300],
          'Product Type': ['Industrial', 'Consumer', 'Consumer', 'Educational', 'Educational', 'Scientific']}
dftest = pd.DataFrame(mydict)
mypivot = pd.pivot_table(dftest, values=['Sales'], index=['State', 'City'])

This creates a single column for Sales.

                   Sales
State City             
CT    Hartford     1500
KY    Lexington    1500
      Louisville   3000
ME    Portland     2000
TX    Dallas       2300

But what I want is e.g. two columns under Sales, corresponding to an ad hoc list of my Product Types for example ('Industrial', 'Consumer').

Like this:

                  Sales
State City        Industrial   Consumer
CT    Hartford     0           0 
KY    Lexington    1000        2000
      Louisville   0           3000
ME    Portland     0           0
TX    Dallas       0           0

Is this possible using pivot tables? Or do I have to manually build up such a dataframe somehow (something that I think would result in excessively complex code)?

EDIT:

I see now that mypivot.columns returns a MultiIndex. I have heard of these but don't yet know how to manipulate them. I sense that the solution to the problem lies in how to specify a MultiIndex filter.

Answer 1

The solutions seems to be fairly straight forward. The only thing that is missing in your pivot is, what are the columns you want to put on top to access the pivot. In your case instead of using

mypivot = pd.pivot_table(dftest, values=['Sales'], index=['State', 'City'])

which produces

                  Sales
State City             
CT    Hartford     1500
KY    Lexington    1500
      Louisville   3000
ME    Portland     2000
TX    Dallas       2300

you can write

mypivot = pd.pivot_table(dftest, values=['Sales'], index=['State', 'City'], columns=['Product Type'])

which gives you

                    Sales                                  
Product Type     Consumer Educational Industrial Scientific
State City                                                 
CT    Hartford        NaN      1500.0        NaN        NaN
KY    Lexington    2000.0         NaN     1000.0        NaN
      Louisville   3000.0         NaN        NaN        NaN
ME    Portland        NaN      2000.0        NaN        NaN
TX    Dallas          NaN         NaN        NaN     2300.0

You can now use .fillna method on chaining to replace non-zero values in your pivot. Hope this helps.

Edit: If you want to take only a specific set of columns, I can seem to have a not so elegant but workable solution like

mypivot['Sales'][['Consumer', 'Educational']]

Which unfortunately removes the higher column Sales from picture producing something like

Product Type      Consumer  Educational
State City                             
CT    Hartford         0.0       1500.0
KY    Lexington     2000.0          0.0
      Louisville    3000.0          0.0
ME    Portland         0.0       2000.0
TX    Dallas           0.0          0.0

If I can think of anything, I will add later.