Python pandas pivot_table margins keyError

Question

Consider the following dataframe:

test = pd.DataFrame({'A': [datetime.datetime.now(), datetime.datetime.now()], 'B': [1, 2]})

If I use pivot_table like below then everything is fine:

test.pivot_table(index = 'A', aggfunc = {'B': 'mean'}, margins = True)

However, if I do the following, I can't set margins = True (throws the error KeyError: 'A'):

test.pivot_table(index = test['A'], aggfunc = {'B': 'mean'}, margins = True)

I am really confused. Let's say I need do something like below AND need to set margin = True. Is that impossible?

test.pivot_table(index = test['A'].dt.year, aggfunc = {'B': 'mean'}, margins = True)

Thanks but this does not change anything. Same issue persists. — CHRD
– CHRD, Commented Jan 22, 2020 at 22:38

Georgina Skibinski · Accepted Answer · 2020-01-22 23:01:56Z

1

Try:

test['Ax']=test['A'].dt.year

test.pivot_table(index = 'Ax' , aggfunc = 'mean', values='B', margins = True)

Outputs:

        B
Ax
2020  1.5
All   1.5

Explanation: in case if you don't pass values it will default to df.columns (all columns of dataframe, that you're pivoting over). https://github.com/pandas-dev/pandas/blob/v0.25.3/pandas/core/reshape/pivot.py#L87

So technically by passing no values you were passing ALL columns into values, yet at the same time providing function for just one, so this is where this KeyError was coming from.

Source here is oddly off with the documentation: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.pivot_table.html

edited Jan 22, 2020 at 23:01

answered Jan 22, 2020 at 22:51

Georgina Skibinski

13.5k2 gold badges16 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Python pandas pivot_table margins keyError

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related