Return to Question

Augmented original with new learnings

Source Link

edited Sep 28, 2019 at 15:56

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.

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)?

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:

Source Link

asked Sep 28, 2019 at 14:00

David

Pandas pivot table, creating ad hoc columns per dimension values

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)?

pandas