BUG (?): dtype.value_counts() shows categorical multiple times

[MarcoGorelli]

• 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.

---

Note: Please read this guide detailing how to provide the necessary information for us to reproduce your bug.

Code Sample, a copy-pastable example

>>> df = pd.DataFrame({'a': [1], 'b': ['2'], 'c': [3], 'd': [3]}).astype({'a': 'category', 'c': 'category', 'd': 'category'})
>>> df
   a  b  c  d
0  1  2  3  3
>>> df.dtypes.value_counts()
category    2
category    1
object      1
dtype: int64

Problem description

category appears twice with different counts

Expected Output

Either

category    3
object      1
dtype: int64

or

CategoricalDtype(categories=[3], ordered=False)    2
CategoricalDtype(categories=[1], ordered=False)    1
object      1
dtype: int64

Output of pd.show_versions()

INSTALLED VERSIONS

commit : 8064973
python : 3.8.6.final.0
python-bits : 64
OS : Linux
OS-release : 5.4.0-70-generic
Version : #78-Ubuntu SMP Fri Mar 19 13:29:52 UTC 2021
machine : x86_64
processor : x86_64
byteorder : little
LC_ALL : None
LANG : en_GB.UTF-8
LOCALE : en_GB.UTF-8

pandas : 1.3.0.dev0+1211.g8064973159
numpy : 1.19.5
pytz : 2021.1
dateutil : 2.8.1
pip : 20.3.3
setuptools : 49.6.0.post20201009
Cython : 0.29.22
pytest : 6.2.2
hypothesis : 6.8.1
sphinx : 3.5.2
blosc : None
feather : None
xlsxwriter : 1.3.7
lxml.etree : 4.6.2
html5lib : 1.1
pymysql : None
psycopg2 : None
jinja2 : 2.11.3
IPython : 7.19.0
pandas_datareader: None
bs4 : 4.9.3
bottleneck : 1.3.2
fsspec : 0.8.7
fastparquet : 0.5.0
gcsfs : 0.7.2
matplotlib : 3.3.3
numexpr : 2.7.3
odfpy : None
openpyxl : 3.0.7
pandas_gbq : None
pyarrow : 2.0.0
pyxlsb : None
s3fs : 0.5.2
scipy : 1.6.1
sqlalchemy : 1.4.2
tables : 3.6.1
tabulate : 0.8.9
xarray : 0.17.0
xlrd : 2.0.1
xlwt : 1.3.0
numba : 0.52.0

[jorisvandenbossche]

The explanation for the behaviour is that two categorical dtypes with different categories are not considered as equal:

In [26]: pd.CategoricalDtype(categories=['a']) == pd.CategoricalDtype(categories=['b'])
Out[26]: False

But since the repr is the same, that of course gives a bit a surprising result. And typically when doing a value counts of the dtypes, you will probably want to regard the different categorical dtypes as equal ..

[MarcoGorelli]

Thanks Joris - so , when you say

And typically when doing a value counts of the dtypes, you will probably want to regard the different categorical dtypes as equal ..

do you suggest that

category    3
object      1
dtype: int64

should be the expected output, or that the current output is correct but that users should mentally combine the different categorical dtypes?

[jorisvandenbossche]

Yeah, so that the current output is "technically" correct, but I think users will typically want your expected output. And I am not sure what the easiest way is to get that (probably converting the dtypes to string first? (or to it's type) Eg df.dtypes.astype(str).value_counts())

[MarcoGorelli]

OK, thanks - so perhaps we can leave value_counts as is, and just put this as an extra example in the docs, something like

Note that the repr of the values is used to populate the index of the output - when working with different categorical dtypes, you might want to convert them to str first:

>>> df = pd.DataFrame({'a': [1], 'b': ['2'], 'c': [3], 'd': [3]}).astype({'a': 'category', 'c': 'category', 'd': 'category'})
>>> df.dtypes.astype(str).value_counts()
category    3
object      1
dtype: int64

[dsaxton]

OK, thanks - so perhaps we can leave value_counts as is, and just put this as an extra example in the docs, something like

I like the idea of documenting this as well (if it isn't already). This tripped me up in the past and I had to use the same workaround.

[jreback]

might be able to use an abbreviated repr here (eg not category) but not also the full repr

[rhshadrach]

Edit: Doh, I missed #40735 (comment). +1 on that.

And typically when doing a value counts of the dtypes, you will probably want to regard the different categorical dtypes as equal

I think that's a guess - some users might also want them to be not equal (because, after all, they aren't). I agree that users will most likely find the output in the OP confusing, but that is a general issue with trying to differentiate Python objects via their repr and nothing particular to dtypes themselves.

Special casing the logic makes things more complicated for users to predict/understand. I would be okay with leaving this as-is. Definitely open to improving the repr, but does that lead to difficulties with length when there are many categories?

[rhshadrach]

@jorisvandenbossche - are you good with the resolution proposed in #40735 (comment)

[jorisvandenbossche]

Yes, certainly, documenting this gotcha with counting data types and giving an example how to count the categorical dtypes as one group sound certainly useful.

[ghost]

take

[aniketDash7]

It seems like there is a discrepancy between the expected output and the actual output.This suggests that there might be a mistake in the data or in the expected output.

If you're trying to set the data types of columns in a Pandas DataFrame, you can do it like this:
df = pd.DataFrame({'a': [1], 'b': ['2'], 'c': [3], 'd': [3]}).astype({'a': 'category', 'b': 'category', 'c': 'category', 'd': 'category'})
This will set all columns to have the 'category' data type.

If there's a specific issue or error you're encountering, please provide more context or clarify the problem so I can assist you further.

[jahn96]

@HoWeiChin are you currently working on this? If not, I would be happy to work on this issue. What's the expected behavior here though?

[ghost]

Pls feel free to assign yourself. No longer working on it. Thank you!

[jahn96]

Pls feel free to assign yourself. No longer working on it. Thank you!

sounds good!

[jahn96]

Hey @jorisvandenbossche, is this a bug? It seems like this is expected.

[jahn96]

take

[Maverick1905]

im trying to setup a venv environment and hence pulled pandas but i get stuck when building dependencies:

Installing collected packages: setuptools, packaging, numpy, Cython, setuptools_scm
ERROR: Cannot set --home and --prefix together

  [notice] A new release of pip is available: 24.0 -> 24.1.2
  [notice] To update, run: python.exe -m pip install --upgrade pip
  [end of output]

Any idea guys?

[Maverick1905]

Solved it was realted to the fact I set pip config set global.target to my C: