2
$\begingroup$

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.

$\endgroup$
  • $\begingroup$ Thanks, but I find that SO is a hostile environment, despite the level of care I take in posting. I'd prefer a data scientist's take on this if anyone is willing to assist. $\endgroup$ – David Sep 28 '19 at 14:48
2
$\begingroup$

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.

| improve this answer | |
$\endgroup$
  • $\begingroup$ Thanks, I may not have been sufficiently clear in my question, I want to only display columns for an ad hoc subset of Product Type. E.g. I want a report that only has columns for the list ['Educational', 'Industrial']. $\endgroup$ – David Sep 28 '19 at 14:59
  • $\begingroup$ I just now am seeing that pivot tables are of type dataframe. maybe i'll have to create a df with all the columns, then surgically remove the ones I don't want...? $\endgroup$ – David Sep 28 '19 at 15:21
  • $\begingroup$ @David Look at the edits and see whether it helps you out. $\endgroup$ – Kiritee Gak Sep 28 '19 at 15:39
  • $\begingroup$ See my addition to original post, which I think provides a clue to a clean solution. Thanks. $\endgroup$ – David Sep 28 '19 at 15:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.