BUG: 'groupby()' results in shifted results for 'quantile()'

[1313e]

Code Sample, a copy-pastable example if possible

# Imports
import pandas as pd
import numpy as np

# Define x and bins
x = np.linspace(0, 1, 100)
bins = np.arange(0, 1, 0.1)

# Create Series object with the x-data
series = pd.Series(x, name='X')

# Group series by provided bins
data_cut = pd.cut(series, bins, include_lowest=True)
grp = series.groupby(by=data_cut)

# Calculate the 0.5 quantile of this group
perc = grp.quantile()

# For every group, print the 0.5 quantile as determined by that group and its values
for group, indices in grp.indices.items():
    print()
    print("Bin:", group)
    print("Group quantile:", perc.loc[group])
    grp_series = grp.get_group(group)
    print("Group values quantile:", grp_series.quantile())

Problem description

NOTE: I see that there are several other issues already open that discuss problems with groupby and quantile in v1.0.3, but I figured that I post this anyway, as it is a very easy and simple reproducible example.

Executing the code above in pandas v1.0.3 (or any v1.0.x version) results in the quantiles not agreeing with each other for every group, even though they should.
Instead, it seems that the quantile as calculated by a group is shifted by 1 group.
This will become 2 groups when using bins = np.arange(0, 0.9, 0.1) and goes away when using bins = np.arange(0, 1.1, 0.1) (or the bins = np.linspace(0, 1, 11) equivalent).
The problem above does not occur for pandas v0.24.x, but instead gives the proper output.

Additionally, replacing x and bins with

# Define x and bins
x = np.linspace(1, 10, 100)
bins = 10**np.arange(0, 1, 0.1)

in order to use logarithmic values (that are not equally binned), will not result in a simple shift, but simply values that make no sense at all at first.

After some more testing, it seems that the quantile method of a GroupBy object, first goes through all values that are not in a group (their indices are NaN) and then goes through the remaining data normally.
Not sure if that is helpful.

Expected Output

The expected output is that the two different values that are printed are always the same for each group, regardless of the bins that are used.

Output of pd.show_versions()

INSTALLED VERSIONS

commit : None
python : 3.7.4.final.0
python-bits : 64
OS : Linux
OS-release : 4.15.0-91-generic
machine : x86_64
processor : x86_64
byteorder : little
LC_ALL : None
LANG : nl_NL.UTF-8
LOCALE : nl_NL.UTF-8

pandas : 1.0.3
numpy : 1.18.1
pytz : 2019.3
dateutil : 2.8.1
pip : 20.0.2
setuptools : 46.0.0.post20200309
Cython : None
pytest : 5.4.1
hypothesis : None
sphinx : 2.4.4
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : 2.11.1
IPython : 7.13.0
pandas_datareader: None
bs4 : None
bottleneck : None
fastparquet : None
gcsfs : None
lxml.etree : None
matplotlib : 3.2.0
numexpr : None
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : None
pytables : None
pytest : 5.4.1
pyxlsb : None
s3fs : None
scipy : 1.4.1
sqlalchemy : None
tables : None
tabulate : None
xarray : None
xlrd : None
xlwt : None
xlsxwriter : None
numba : None

[jorisvandenbossche]

@1313e Thanks for the report!
It doesn't directly seem similar to other open quantile issues, based on a quick look.

I also seems this is a regression that already happened from 0.24 to 0.25:

In [21]: pd.__version__     
Out[21]: '0.25.3'

In [22]: for group, indices in grp.indices.items(): 
    ...:     print() 
    ...:     print("Bin:", group) 
    ...:     print("Group quantile:", perc.loc[group]) 
    ...:     grp_series = grp.get_group(group) 
    ...:     print("Group values quantile:", grp_series.quantile()) 
    ...:  

Bin: (-0.001, 0.1]
Group quantile: 0.9545454545454546
Group values quantile: 0.045454545454545456
....

In [5]: pd.__version__
Out[5]: '0.24.2'

In [6]: for group, indices in grp.indices.items(): 
   ...:     print() 
   ...:     print("Bin:", group) 
   ...:     print("Group quantile:", perc.loc[group]) 
   ...:     grp_series = grp.get_group(group) 
   ...:     print("Group values quantile:", grp_series.quantile()) 
   ...:    

Bin: (-0.001, 0.1]
Group quantile: 0.045454545454545456
Group values quantile: 0.045454545454545456

Further investigation / pull request is certainly welcome!

[simonjayhawkins]

I also seems this is a regression that already happened from 0.24 to 0.25:

regression in #20405 (0.25.0)

64e5612 is the first bad commit
commit 64e5612
Author: William Ayd [email protected]
Date: Thu Feb 28 05:36:57 2019 -0800

Cythonized GroupBy Quantile (#20405)

cc @WillAyd

[trungv0]

Hi, it seems to relate to nan values in grouped columns. This example might be helpful:

In [1]: import pandas as pd 
   ...: import numpy as np                                                                                                                                                                                                                                                           

In [2]: pd.__version__                                                                                                                                                                                                                                                               
Out[2]: '1.0.3'

In [3]: df = pd.DataFrame({'x': ['a', 'b', 'b', np.nan], 'y': np.arange(4)}) 
   ...: df                                                                                                                                                                                                                                                                           
Out[3]: 
     x  y
0    a  0
1    b  1
2    b  2
3  NaN  3

In [4]: df.groupby('x')['y'].quantile()                                                                                                                                                                                                                                              
Out[4]: 
x
a    3.0
b    0.5
Name: y, dtype: float64

In [5]: df.dropna().groupby('x')['y'].quantile()  # Expected results                                                                                                                                                                                                                 
Out[5]: 
x
a    0.0
b    1.5
Name: y, dtype: float64

[mabelvj]

I proposed a solution. It seems that it is happening due to np.lexsort ordering labels assigned to Nans (-1) in the lower part, in group_quantile function in groupby.pyx.