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

[jcunhafonte]

• I have checked that this issue has not already been reported.

• I have confirmed this bug exists on the latest version of pandas.

• (optional) I have confirmed this bug exists on the master branch of pandas.

---

Code Sample, a copy-pastable example

import pandas as pd 
import numpy as np

df = pd.DataFrame([
    {'JOB':'Worker','NAME':'Bob' ,'YEAR':2013,'MONTH':12,'DAYS': 3,'SALARY': 17},
    {'JOB':'Employ','NAME':'Mary','YEAR':2013,'MONTH':12,'DAYS': 5,'SALARY': 23},
    {'JOB':'Worker','NAME':'Bob' ,'YEAR':2014,'MONTH': 1,'DAYS':10,'SALARY':100},
    {'JOB':'Worker','NAME':'Bob' ,'YEAR':2014,'MONTH': 1,'DAYS':11,'SALARY':110},
    {'JOB':'Employ','NAME':'Mary','YEAR':2014,'MONTH': 1,'DAYS':15,'SALARY':200},
    {'JOB':'Worker','NAME':'Bob' ,'YEAR':2014,'MONTH': 2,'DAYS': 8,'SALARY': 80},
    {'JOB':'Employ','NAME':'Mary','YEAR':2014,'MONTH': 2,'DAYS': 5,'SALARY':190},
    {'JOB':'Cloudbeds','NAME':'Rob','YEAR':2020,'MONTH': 4,'DAYS': 20,'SALARY':690},
    {'JOB':'Worker','NAME':'Jose','YEAR':2010,'MONTH': 3,'DAYS': 20,'SALARY':90},
    {'JOB':'Cloudbeds','NAME':'Jose','YEAR':2030,'MONTH': 2,'DAYS': 20,'SALARY':60}
])

pivot = df.pivot_table(
    index=['JOB','NAME'],
    columns=['YEAR','MONTH'],
    values=['DAYS','SALARY'],
    aggfunc={
        'DAYS': ['mean'],
        'SALARY': [np.mean, np.sum, np.max, np.min],
    },
    dropna=False,
    margins=True
)

Problem description

When executing the pivot_table function with aggfunc argument as a dict with multiple metrics as on a single value, e.g.: 'SALARY': [np.mean, np.sum, np.max, np.min] and margins=True to get the totals the following error is raised:

---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
<ipython-input-5-68d98b312428> in <module>
      8   },
      9   dropna=False,
---> 10     margins=True
     11 )

/srv/conda/envs/notebook/lib/python3.7/site-packages/pandas/core/frame.py in pivot_table(self, values, index, columns, aggfunc, fill_value, margins, dropna, margins_name)
   5757                            aggfunc=aggfunc, fill_value=fill_value,
   5758                            margins=margins, dropna=dropna,
-> 5759                            margins_name=margins_name)
   5760 
   5761     def stack(self, level=-1, dropna=True):

/srv/conda/envs/notebook/lib/python3.7/site-packages/pandas/core/reshape/pivot.py in pivot_table(data, values, index, columns, aggfunc, fill_value, margins, dropna, margins_name)
    133                              cols=columns, aggfunc=aggfunc,
    134                              observed=dropna,
--> 135                              margins_name=margins_name, fill_value=fill_value)
    136 
    137     # discard the top level

/srv/conda/envs/notebook/lib/python3.7/site-packages/pandas/core/reshape/pivot.py in _add_margins(table, data, values, rows, cols, aggfunc, observed, margins_name, fill_value)
    165     if hasattr(table, 'columns'):
    166         for level in table.columns.names[1:]:
--> 167             if margins_name in table.columns.get_level_values(level):
    168                 raise ValueError(msg)
    169 

/srv/conda/envs/notebook/lib/python3.7/site-packages/pandas/core/indexes/multi.py in get_level_values(self, level)
   1410         Index(['d', 'e', 'f'], dtype='object', name='level_2')
   1411         """
-> 1412         level = self._get_level_number(level)
   1413         values = self._get_level_values(level)
   1414         return values

/srv/conda/envs/notebook/lib/python3.7/site-packages/pandas/core/indexes/multi.py in _get_level_number(self, level)
   1116         if (count > 1) and not is_integer(level):
   1117             raise ValueError('The name %s occurs multiple times, use a '
-> 1118                              'level number' % level)
   1119         try:
   1120             level = self.names.index(level)

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

Output of pd.show_versions()

INSTALLED VERSIONS

commit: None
python: 3.7.8.final.0
python-bits: 64
OS: Linux
OS-release: 4.19.112+
machine: x86_64
processor: x86_64
byteorder: little
LC_ALL: en_US.UTF-8
LANG: en_US.UTF-8
LOCALE: en_US.UTF-8

pandas: 0.24.2
pytest: None
pip: 20.1.1
setuptools: 49.2.0.post20200712
Cython: None
numpy: 1.19.2
scipy: 1.5.2
pyarrow: None
xarray: None
IPython: 7.16.1
sphinx: None
patsy: 0.5.1
dateutil: 2.8.1
pytz: 2020.1
blosc: None
bottleneck: None
tables: None
numexpr: None
feather: None
matplotlib: 3.1.3
openpyxl: None
xlrd: None
xlwt: None
xlsxwriter: None
lxml.etree: None
bs4: None
html5lib: None
sqlalchemy: 1.3.18
pymysql: None
psycopg2: None
jinja2: 2.11.2
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None
gcsfs: None

[madhurdevkota]

I am also getting the exact error.

sales_Type_Holi = sales_df.pivot_table( index= ['store_type','is_holid'], values= ['tempr','week_sales'], aggfunc= { 'tempr':[np.mean], 'week_sales':[np.max,np.sum] }, margins= False )

This works and gives following output:

Sales by StoreType & Holidays = 

                          tempr week_sales              
                          mean       amax           sum
store_type is_holid                                    
A          False     15.247873  293966.05  2.336927e+08
           True      11.165833    5350.00  2.360181e+04
B          False     21.208636  232558.51  2.317678e+07
           True      22.422222    1590.00  1.621410e+03

while this givers error:
sales_Type_Holi = sales_df.pivot_table( index= ['store_type','is_holid'], #columns= , values= ['tempr','week_sales'], aggfunc= { 'tempr':[np.mean], 'week_sales':[np.max,np.sum] }, margins= True )

Error -->

ValueError                                Traceback (most recent call last)
<ipython-input-748-231564ecd26d> in <module>
      2                                         values= ['tempr','week_sales'],
      3                                         aggfunc=  { 'tempr':[np.mean], 'week_sales':[np.max,np.sum] },
----> 4                                         margins= True
      5                                       )

c:\python-projects\machine-learning\lib\site-packages\pandas\core\frame.py in pivot_table(self, values, index, columns, aggfunc, fill_value, margins, dropna, margins_name, observed)
   6834             dropna=dropna,
   6835             margins_name=margins_name,
-> 6836             observed=observed,
   6837         )
   6838 

c:\python-projects\machine-learning\lib\site-packages\pandas\core\reshape\pivot.py in pivot_table(data, values, index, columns, aggfunc, fill_value, margins, dropna, margins_name, observed)
    178             observed=dropna,
    179             margins_name=margins_name,
--> 180             fill_value=fill_value,
    181         )
    182 

c:\python-projects\machine-learning\lib\site-packages\pandas\core\reshape\pivot.py in _add_margins(table, data, values, rows, cols, aggfunc, observed, margins_name, fill_value)
    224         # i.e. DataFrame
    225         for level in table.columns.names[1:]:
--> 226             if margins_name in table.columns.get_level_values(level):
    227                 raise ValueError(msg)
    228 

c:\python-projects\machine-learning\lib\site-packages\pandas\core\indexes\multi.py in get_level_values(self, level)
   1573         Index(['d', 'e', 'f'], dtype='object', name='level_2')
   1574         """
-> 1575         level = self._get_level_number(level)
   1576         values = self._get_level_values(level)
   1577         return values

c:\python-projects\machine-learning\lib\site-packages\pandas\core\indexes\multi.py in _get_level_number(self, level)
   1421         if (count > 1) and not is_integer(level):
   1422             raise ValueError(
-> 1423                 f"The name {level} occurs multiple times, use a level number"
   1424             )
   1425         try:

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

I see people discussing this issue at multiple platforms but have not found any workaround. Any updates on the bug?

[kburchfiel]

I would love an update on this as well, as I am running into the same error message when trying to add margins=True as a parameter within the pd.pivot_table function.

[jreback]

anyone can debug and propose a patch.