Pandas pivot_table fails with columns and margin

Question

I'm getting a KeyError: "... not in index" while using pivot_table of pandas. Here is sample code:

arrays = [['bar', 'bar', 'foo', 'foo'],
          ['one', 'two', 'one', 'two'], ['A','A','B','B']]
tuples = list(zip(*arrays))
index = pd.MultiIndex.from_tuples(tuples, names=['first', 'second', 'third'])
values = np.array([[1,2,3,4],[5,6,7,8]])
df = pd.DataFrame(values.T, index=index, columns = ['0', '1'])

# here comes the pivot_table, this one works, it has 'colums'
df.pivot_table(index = ['first', 'second'],  columns = 'third', aggfunc = 'sum')

#this one works, it has 'margins'
df.pivot_table(index = ['first', 'second'],  aggfunc = 'sum', margins=True)

#this one fails, it has both 'columns' and 'margins'
df.pivot_table(index = ['first', 'second'],  columns = 'third', aggfunc = 'sum', margins=True)

KeyError Traceback (most recent call last)
...
KeyError: "['first' 'second'] not in index"

Somehow columns and margins are not compatible.

looks buggy - as a workaround, df.reset_index() before the .pivot_table(...) seems to work — Asish M.
– Asish M., Commented Dec 24, 2020 at 19:17
Yes, it does, thank you. reset_index turns the Multindex into a set of columns. So, it seems like this functionality works with columns but doesn't work with index — David Makovoz
– David Makovoz, Commented Dec 24, 2020 at 22:14

Gonçalo Peres · Accepted Answer · 2021-05-05 08:37:18Z

1

As Asish mentioned, performing a reset_index() before calling .pivot_table will do the work.

The problem was raised in pandas GitHub Issues and one can follow all the updates here.

answered May 5, 2021 at 8:37

Gonçalo Peres

13.9k5 gold badges73 silver badges95 bronze badges

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

Collectives™ on Stack Overflow

Pandas pivot_table fails with columns and margin

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