BUG: pd.crosstab fails when passed multiple columns, margins True and normalize True

[HughKelley]

• 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

df = pd.DataFrame({"A": ["foo", "foo", "foo", "foo", "foo",
          "bar", "bar", "bar", "bar"],
                    "B": ["one", "one", "one", "two", "two",
                          "one", "one", "two", "two"],
                    "C": ["small", "large", "large", "small",
                          "small", "large", "small", "small",
                          "large"],
                    "D": [1, 2, 2, 3, 3, 4, 5, 6, 7],
                    "E": [2, 4, 5, 5, 6, 6, 8, 9, 9]})

pd.crosstab(index=df.C,
            columns=[df.A,df.B],  
            margins=True,
            margins_name='Sub-Total',
            normalize='all')

# returns ValueError: Sub-Total not in pivoted DataFrame

Problem description

#27663 found that pd.crosstab failed when margins and normalize were true. This continues to be the case when more than one column is passed.

This is not the case when a single column and multiple rows is passed.

Output of pd.show_versions()

INSTALLED VERSIONS

commit : None
python : 3.7.7.final.0
python-bits : 64
OS : Windows
OS-release : 10
machine : AMD64
processor : Intel64 Family 6 Model 142 Stepping 9, GenuineIntel
byteorder : little
LC_ALL : None
LANG : None
LOCALE : None.None

pandas : 1.0.3
numpy : 1.18.1
pytz : 2020.1
dateutil : 2.8.1
pip : 20.0.2
setuptools : 47.1.1.post20200604
Cython : None
pytest : None
hypothesis : None
sphinx : 3.1.1
blosc : None
feather : None
xlsxwriter : 1.2.9
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : 2.8.4 (dt dec pq3 ext lo64)
jinja2 : 2.11.2
IPython : 7.13.0
pandas_datareader: None
bs4 : None
bottleneck : None
fastparquet : None
gcsfs : None
lxml.etree : None
matplotlib : 3.1.3
numexpr : None
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : None
pytables : None
pytest : None
pyxlsb : None
s3fs : None
scipy : 1.4.1
sqlalchemy : 1.3.17
tables : None
tabulate : None
xarray : None
xlrd : None
xlwt : None
xlsxwriter : 1.2.9
numba : None

[paste the output of pd.show_versions() here leaving a blank line after the details tag]

[CloseChoice]

this still exists on the latest version.

[CloseChoice]

take

[bdatko]

I am seeing the same issue given the version below. Same error as og:

In [1]: import pandas as pd

In [2]: df = pd.DataFrame({"A": ["foo", "foo", "foo", "foo", "foo",
   ...:           "bar", "bar", "bar", "bar"],
   ...:                     "B": ["one", "one", "one", "two", "two",
   ...:                           "one", "one", "two", "two"],
   ...:                     "C": ["small", "large", "large", "small",
   ...:                           "small", "large", "small", "small",
   ...:                           "large"],
   ...:                     "D": [1, 2, 2, 3, 3, 4, 5, 6, 7],
   ...:                     "E": [2, 4, 5, 5, 6, 6, 8, 9, 9]})

In [3]: pd.crosstab(index=df.C,
   ...:             columns=[df.A,df.B],
   ...:             margins=True,
   ...:             margins_name='Sub-Total',
   ...:             normalize='all')
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
<ipython-input-3-8e93d08650b4> in <module>
      3             margins=True,
      4             margins_name='Sub-Total',
----> 5             normalize='all')

...

ValueError: Sub-Total not in pivoted DataFrame

In [4]: pd.show_versions()

INSTALLED VERSIONS
------------------
commit           : db08276bc116c438d3fdee492026f8223584c477
python           : 3.7.9.final.0
python-bits      : 64
OS               : Darwin
OS-release       : 18.7.0
Version          : Darwin Kernel Version 18.7.0: Mon Feb 10 21:08:45 PST 2020; root:xnu-4903.278.28~1/RELEASE_X86_64
machine          : x86_64
processor        : i386
byteorder        : little
LC_ALL           : None
LANG             : en_US.UTF-8
LOCALE           : en_US.UTF-8

pandas           : 1.1.3
numpy            : 1.19.2
pytz             : 2020.1
dateutil         : 2.8.1
pip              : 20.2.4
setuptools       : 50.3.1.post20201107
Cython           : None
pytest           : 6.1.1
hypothesis       : 5.37.4
sphinx           : None
blosc            : None
feather          : None
xlsxwriter       : None
lxml.etree       : None
html5lib         : None
pymysql          : None
psycopg2         : None
jinja2           : 2.11.2
IPython          : 7.18.1
pandas_datareader: None
bs4              : None
bottleneck       : None
fsspec           : None
fastparquet      : None
gcsfs            : None
matplotlib       : 3.3.2
numexpr          : None
odfpy            : None
openpyxl         : None
pandas_gbq       : None
pyarrow          : None
pytables         : None
pyxlsb           : None
s3fs             : None
scipy            : 1.5.2
sqlalchemy       : None
tables           : None
tabulate         : None
xarray           : None
xlrd             : None
xlwt             : None
numba            : 0.51.2

[HughKelley]

Hi @bdatko looks like you have pandas 1.1.3 installed. The PR closechoice made to fix this issue was merged into 1.2. I believe if you install from github instead of pypi or a conda source you can include that code. for instance pip install [email protected]:pandas-dev/pandas.git will do the trick i think.

[bdatko]

@HughKelley Thank you for the information. Reviewing the the 1.2.0 release notes under reshaping the bug was indeed fixed. I installed pandas 1.2.0 from Github using

pip install git+https://github.com/pandas-dev/pandas

and the og's example works as expected. =D

A side note:
I couldn't install using your suggested pip command. When I do pip install [email protected]:pandas-dev/pandas.git I just get

$ pip install git@github.com:pandas-dev/pandas.git
ERROR: Invalid requirement: '[email protected]:pandas-dev/pandas.git'
Hint: It looks like a path. File '[email protected]:pandas-dev/pandas.git' does not exist.

[HughKelley]

I couldn't install

Sorry i use an SSH key to communicate with github. You probably would have succeeded with https://github.com/pandas-dev/pandas.git instead as it relies on https instead of ssh.

glad you got it working