Pandas pivot_table is giving the error ValueError: The name None occurs multiple times, use a level number

Question

I have a pandas dataframe

print(df.head())

     Row ID        Order ID  Order Date  ... Quantity Discount    Profit
0       1  CA-2013-152156  09/11/2013  ...        2     0.00   41.9136
1       2  CA-2013-152156  09/11/2013  ...        3     0.00  219.5820
2       3  CA-2013-138688  13/06/2013  ...        2     0.00    6.8714
3       4  US-2012-108966  11/10/2012  ...        5     0.45 -383.0310
4       5  US-2012-108966  11/10/2012  ...        2     0.20    2.5164

When I execute this command:

ans = pd.pivot_table(data=df, index=['Segment'], columns=['Region'], values     = ['Sales'], aggfunc={'Sales':['sum', 'mean']}, margins=True, dropna=False)

It gives this error:

Traceback (most recent call last):

  File "<stdin>", line 1, in <module>

  File "/usr/local/lib/python3.6/dist-packages/pandas/core/reshape/pivot.py", line 162, in pivot_table

   fill_value=fill_value,

  File "/usr/local/lib/python3.6/dist-packages/pandas/core/reshape/pivot.py", line 208, in _add_margins

   if margins_name in table.columns.get_level_values(level):

  File "/usr/local/lib/python3.6/dist-packages/pandas/core/indexes/multi.py", line 1598, in 
get_level_values

   level = self._get_level_number(level)

  File "/usr/local/lib/python3.6/dist-packages/pandas/core/indexes/multi.py", line 1292, in 
_get_level_number

   "The name %s occurs multiple times, use a " "level number" % level

ValueError: The name None occurs multiple times, use a level number

Answer 1

Maybe help omit lists from:

ans = pd.pivot_table(data=df, 
                     index=['Segment'], 
                     columns=['Region'], 
                     values= ['Sales'], 
                     aggfunc={'Sales':['sum', 'mean']}, margins=True, dropna=False)

to:

ans = pd.pivot_table(data=df, 
                     index= 'Segment', 
                     columns= 'Region', 
                     values=  'Sales', 
                     aggfunc={'Sales':['sum', 'mean']}, margins=True, dropna=False)

Answer 2

This is a bug (still open as of Apr 2022): https://github.com/pandas-dev/pandas/issues/37246 . It seems that if margins=True, then you cannot use lists for aggfunc.

The workaround is to not use lists in aggfunc, and to explicitly specify with a dict. Since you need 2 functions calculated for a column, the workaround is to duplicate the column first. For example:

df['Sales2'] = df['Sales']

pd.pivot_table(... aggfunc={'Sales':'sum', 'Sales2':'mean'} ...)