Description
Pandas version checks
-
I have checked that this issue has not already been reported.
-
I have confirmed this bug exists on the latest version of pandas.
-
I have confirmed this bug exists on the main branch of pandas.
Reproducible Example
import pandas as pd
data = {"by": ["a", "a", "a", "b", "b", "b", "b", "b", "b"], "values": [1, 1, 0, 1, 1, 1, 0, 0, 0]}
df = pd.DataFrame(data)
df.groupby(by="by", sort=False).value_counts(sort=True, normalize=True)Issue Description
(continued from #59307)
Per documentation, the sort flag of value_counts will "Sort by frequencies." The current behavior of GroupBy.value_counts follows this to the letter, and even when normalize=True is set, the resulting frame is sorted by the count column.
However, when groupby is called with sort=False, the order of the (integer) counts will not necessarily be the same as the order of the (fractional) proportions. This issue is unique to groupby(sort=False) because for groupby(sort=True), frequencies are sorted within groups; since the denominator of the proportions is the size of each group, their orderings end up being equivalent.
In the specific case where groupby is called with sort=False, and value_counts is called with normalize=True and sort=True, this may result in counterintuitive behavior where the proportion column is actually NOT sorted. The output of the above example is as follows:
>>> import pandas as pd
>>> data = {"by": ["a", "a", "a", "b", "b", "b", "b", "b", "b"], "values": [1, 1, 0, 1, 1, 1, 0, 0, 0]}
>>> df = pd.DataFrame(data)
>>> df.groupby(by="by", sort=False).value_counts(sort=True, normalize=True)
by values
b 1 0.500000
0 0.500000
a 1 0.666667
0 0.333333
Name: proportion, dtype: float64
>>> df.groupby(by="by", sort=False).value_counts(sort=True, normalize=False)
by values
b 1 3
0 3
a 1 2
0 1
Name: count, dtype: int64
Group a has 3 items, so a value appearing 2 times has a proportion of 0.66…. Group b has 6 items, so a value appearing 3 times has a proportion of 0.5. IMO when the sort flag is passed in this scenario, the sort should be performed on the normalized proportions rather than the frequencies, since under current behavior, passing sort=True would result in a frame that isn't actually sorted.
Expected Behavior
Existing behavior agrees with documentation, but IMO this existing behavior should be modified to sort on proportion rather than un-normalized frequencies to ensure the result frame is sorted.
Installed Versions
INSTALLED VERSIONS
commit : 67a58cd
python : 3.11.7
python-bits : 64
OS : Darwin
OS-release : 23.6.0
Version : Darwin Kernel Version 23.6.0: Mon Jul 29 21:13:04 PDT 2024; root:xnu-10063.141.2~1/RELEASE_ARM64_T6020
machine : arm64
processor : arm
byteorder : little
LC_ALL : None
LANG : en_US.UTF-8
LOCALE : en_US.UTF-8
pandas : 3.0.0.dev0+1239.g67a58cddc2
numpy : 2.1.0.dev0+git20240720.d489c83
pytz : 2024.1
dateutil : 2.9.0.post0
pip : 23.3.1
Cython : None
sphinx : None
IPython : None
adbc-driver-postgresql: None
adbc-driver-sqlite : None
bs4 : None
blosc : None
bottleneck : None
fastparquet : None
fsspec : None
html5lib : None
hypothesis : None
gcsfs : None
jinja2 : None
lxml.etree : None
matplotlib : None
numba : None
numexpr : None
odfpy : None
openpyxl : None
psycopg2 : None
pymysql : None
pyarrow : None
pyreadstat : None
pytest : None
python-calamine : None
pyxlsb : None
s3fs : None
scipy : None
sqlalchemy : None
tables : None
tabulate : None
xarray : None
xlrd : None
xlsxwriter : None
zstandard : None
tzdata : 2023.4
qtpy : None
pyqt5 : None