qcut doesn't always properly distribute across bins

[AndrewRook]

Code Sample, a copy-pastable example if possible

import pandas as pd
df = pd.DataFrame({"col": list(range(100))})
quantiles = pd.qcut(df["col"], 11)
print(quantiles.groupby(quantiles).size())

prints

col
(-0.001, 9.0]    10
(9.0, 18.0]       9
(18.0, 27.0]      8
(27.0, 36.0]     10
(36.0, 45.0]      9
(45.0, 54.0]      8
(54.0, 63.0]     10
(63.0, 72.0]      9
(72.0, 81.0]      9
(81.0, 90.0]      9
(90.0, 99.0]      9
Name: col, dtype: int64

Problem description

qcut isn't distributing values across bins quite right for this case – for range(100) there are 10 values in the interval (-0.001, 9.0] and 9 in all the others.

Expected Output

col
(-0.001, 9.0]    10
(9.0, 18.0]       9
(18.0, 27.0]      9
(27.0, 36.0]      9
(36.0, 45.0]      9
(45.0, 54.0]      9
(54.0, 63.0]      9
(63.0, 72.0]      9
(72.0, 81.0]      9
(81.0, 90.0]      9
(90.0, 99.0]      9
Name: col, dtype: int64

Output of pd.show_versions()

INSTALLED VERSIONS
------------------
commit: None
python: 3.6.1.final.0
python-bits: 64
OS: Darwin
OS-release: 16.5.0
machine: x86_64
processor: i386
byteorder: little
LC_ALL: None
LANG: en_US.UTF-8
LOCALE: en_US.UTF-8

pandas: 0.20.1
pytest: None
pip: 9.0.1
setuptools: 27.2.0
Cython: None
numpy: 1.12.1
scipy: None
xarray: None
IPython: None
sphinx: None
patsy: None
dateutil: 2.6.0
pytz: 2017.2
blosc: None
bottleneck: None
tables: None
numexpr: None
feather: None
matplotlib: None
openpyxl: None
xlrd: None
xlwt: None
xlsxwriter: None
lxml: None
bs4: None
html5lib: None
sqlalchemy: None
pymysql: None
psycopg2: None
jinja2: None
s3fs: None
pandas_gbq: None
pandas_datareader: None

Apologies if this has been posted already, but I didn't see anything from searching around.

[chris-b1]

May be some kind of floating point issue - or this could be right and is just a formatting one, I'm not sure. Looking at the percentile levels.

q = np.linspace(0, 1, 11 + 1)
arr = np.arange(100)
breaks = np.percentile(arr, q * 100)

breaks
Out[115]: 
array([  0.,   9.,  18.,  27.,  36.,  45.,  54.,  63.,  72.,  81.,  90.,
        99.])

That fourth value is actually slightly less than 27, it's the next smallest floating point value.

In [120]: breaks[3]
Out[120]: 26.999999999999996

In [121]: breaks[3] == np.float64(27.0)
Out[121]: False

In [128]: np.nextafter(breaks[3], 999)
Out[128]: 27.0

[AndrewRook]

Ah, that's what's causing the problem, thanks for surfacing that! To me this seems like undesirable behavior, although I can see both sides of the issue so I'd be happy to be persuaded otherwise. Judging from the code, it looks like you could make it check for these slight floating point mismatches by changing this if statement to something like this:

if idx % 1 == 0:
    score = values[int(idx)]
elif np.nextafter(idx, idx + 1) % 1 == 0:
    score = values[int(np.nextafter(idx, idx + 1))]
elif np.nextafter(idx, idx - 1) % 1 == 0:
    score = values[int(np.nextafter(idx, idx - 1))]
else:
...

That's pretty ugly looking IMO but would do the trick. Another option would be to round idx to a given tolerance level before the if statement.

[eidalex]

Actually I have the same problem but at another scale... I work with arrays of size (1276900,) and when I use either qcut or df.quantile() (and bin the data according to the values obtained) with 10 bins I get arrays from size 127690 to 128188... one or more value could work but 498 more is quite difficult to work with...
Is there any update on the issue ? Thanks in advance.

I use Pandas 1.1.3