Pandas Error when creating a pivot table (KeyError)

Question

I am trying to create a pivot table from a Dataframe using Pandas. Given below is the view of my Dataframe.

category,date,type1,type2,total
PROD_A,2018-10-01,2,2,4
PROD_A,2018-10-02,2,0,2
PROD_B,2018-10-01,0,0,0
PROD_A,2018-10-03,0,0,0

I am trying to create a pivot and save the output to an excel file

Summary = pd.pivot_table(df, values=['total'], index=['category'], columns='date')

Summary.to_excel(writer, sheet_name='Summary')

I get the below error

KeyError : 'total'

Could anyone guide me where am I gong wrong with this. Thanks

Updating on the datatype:

category   object
date       object
type1      int64
type2      int64
total      float64
dtype:     object

Output of df.head():

category,date,type1,type2,total
PROD_A,2018-10-01,2,2,4
PROD_A,2018-10-02,2,0,2
PROD_B,2018-10-01,0,0,0
PROD_A,2018-10-03,0,0,0
PROD_B,2018-10-03,2,3,5

One idea - How working Summary = pd.pivot_table(df, values='total', index='category', columns='date') — jezrael
– jezrael, Commented Oct 12, 2018 at 9:28
If using Summary = pd.pivot_table(df, values='total', index='category', columns='date') then no problem? — jezrael
– jezrael, Commented Oct 12, 2018 at 10:13
Maybe need Summary = pd.pivot_table(df, values='total', index='category', columns='date').reset_index() — jezrael
– jezrael, Commented Oct 12, 2018 at 10:44

jezrael · Accepted Answer · 2018-10-12 10:56:34Z

Problem is ['total'], it create MultiIndex in columns:

Summary = pd.pivot_table(df, values=['total'], index=['category'], columns='date')

print (Summary)

              total                      
date     2018-10-01 2018-10-02 2018-10-03
category                                 
PROD_A          4.0        2.0        0.0
PROD_B          0.0        NaN        NaN

Solution is use remove it:

Summary = pd.pivot_table(df, values='total', index='category', columns='date')
print (Summary)
date      2018-10-01  2018-10-02  2018-10-03
category                                    
PROD_A           4.0         2.0         0.0
PROD_B           0.0         NaN         NaN

Last convert index to column by reset_index:

Summary = (pd.pivot_table(df, values='total', index='category', columns='date')
             .reset_index(drop=True))
print (Summary)
date  2018-10-01  2018-10-02  2018-10-03
0            4.0         2.0         0.0
1            0.0         NaN         5.0

Collectives™ on Stack Overflow

Pandas Error when creating a pivot table (KeyError)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related