Code Sample

Do a crosstab with an ordered categorical variable, with the order being different than the alphabetic default:

df = pd.DataFrame({'First': ['B', 'B', 'C', 'A', 'B', 'C'], 
                   'Second': ['C', 'B', 'B', 'B', 'C', 'A']})

df.First = df.First.astype(CategoricalDtype(ordered=True))

# Default order is alphabetic
df.First.cat.categories

>> Index(['A', 'B', 'C'], dtype='object')

# Change the order
df.First = df.First.cat.reorder_categories(['C', 'A', 'B'])
df.First.cat.categories

>> Index(['C', 'A', 'B'], dtype='object')

# Do a simple crosstab with margins
pd.crosstab(df.First, df.Second, margins=True)

>> Second   A   B   C   All
>> First 
>> C        1   1   0   1
>> A        0   1   0   3
>> B        0   1   2   2
>> All      1   3   2   6

Problem description

The margins are showing the wrong values. In fact, they're showing the values that would be expected from the default ordering of the categories. This might be related to #20496, but the output here surely looks like a bug

Output of pd.show_versions()

[paste the output of ``pd.show_versions()`` here below this line] commit: None python: 3.6.7.final.0 python-bits: 64 OS: Linux OS-release: 4.15.0-1032-aws machine: x86_64 processor: x86_64 byteorder: little LC_ALL: en_US.UTF-8 LANG: C.UTF-8 LOCALE: en_US.UTF-8 pandas: 0.24.1 pytest: 4.2.0 pip: 19.0.1 setuptools: 40.7.3 Cython: 0.29.4 numpy: 1.16.1 scipy: 1.2.0 pyarrow: 0.12.0 xarray: None IPython: 7.2.0 sphinx: 1.8.4 patsy: 0.5.1 dateutil: 2.7.5 pytz: 2018.9 blosc: None bottleneck: None tables: None numexpr: 2.6.9 feather: None matplotlib: 3.0.2 openpyxl: 2.5.14 xlrd: 1.2.0 xlwt: 1.3.0 xlsxwriter: None lxml.etree: 4.3.0 bs4: 4.7.1 html5lib: 1.0.1 sqlalchemy: 1.2.17 pymysql: None psycopg2: 2.7.7 (dt dec pq3 ext lo64) jinja2: 2.10 s3fs: None fastparquet: 0.2.1 pandas_gbq: 0.9.0 pandas_datareader: None gcsfs: None

Comment From: WillAyd

Thanks for the report. Investigation and PRs are always welcome

Comment From: hwalinga

I can confirm this is now fixed on the master.

>>> pd.crosstab(df.First, df.Second, margins=True)
Second  A  B  C  All
First               
C       1  1  0    2
A       0  1  0    1
B       0  1  2    3
All     1  3  2    6

So, this can be closed.

Comment From: aidoskanapyanov

Is this resolved or tests are still needed?

Comment From: aidoskanapyanov

take

Comment From: aidoskanapyanov

I can confirm this is now fixed on the master.

```

pd.crosstab(df.First, df.Second, margins=True) Second A B C All First
C 1 1 0 2 A 0 1 0 1 B 0 1 2 3 All 1 3 2 6 ```

So, this can be closed.

Hi @hwalinga! I've added a PR #52645 with the unit test for this example. It passes on the main branch.