BUG: groupby resample different results with .agg() vs .mean()

[pblankley]

• 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

import pandas as pd
import numpy as np

data = pd.DataFrame({
    'cat': ['cat_1', 'cat_1', 'cat_2', 'cat_1', 'cat_2', 'cat_1', 'cat_2', 'cat_1'],
    'num': [5,20,22,3,4,30,10,50],
    'date': ['2019-2-1', '2018-02-03','2020-3-11','2019-2-2', '2019-2-2', '2018-12-4','2020-3-11', '2020-12-12']
})
data['date'] = pd.to_datetime(data['date'])
using_agg = data.groupby('cat').resample('Y', on='date').agg({'num': 'mean'})
using_mean = data.groupby('cat').resample('Y', on='date').mean()

print(using_agg)
print(using_mean)

assert(np.allclose(using_agg['num'], using_mean['num']))

Problem description

I expect to get the same result from using .agg({col_name: 'mean'}) and I expect to get from .mean()

It's very surprising the results are different here, and really worrying for me, considering historic code for us might be producing incorrect results. Can anyone shed some light on why this may be the case?

Expected Output

The output from using_mean is correct and should be equal to the output from using_agg

Output of pd.show_versions()

INSTALLED VERSIONS

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

pandas : 1.0.3
numpy : 1.18.2
pytz : 2019.3
dateutil : 2.8.1
pip : 18.1
setuptools : 40.6.2
Cython : None
pytest : None
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : None
IPython : None
pandas_datareader: None
bs4 : None
bottleneck : None
fastparquet : None
gcsfs : None
lxml.etree : None
matplotlib : None
numexpr : None
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : None
pytables : None
pytest : None
pyxlsb : None
s3fs : None
scipy : None
sqlalchemy : None
tables : None
tabulate : None
xarray : None
xlrd : None
xlwt : None
xlsxwriter : None
numba : None

[bkachroo]

I'm seeing the same issue:

df.groupby('cat').resample(on='time', rule='H').mean().reset_index()

has the expected result, whereas:

df.groupby('cat').resample(on='time', rule='H').agg({'val':'mean'}).reset_index()

is missing rows.

[CloseChoice]

This example makes it even more clear:

import pandas as pd
import numpy as np

data = pd.DataFrame({
    'cat': ['cat_1', 'cat_1', 'cat_2', 'cat_1', 'cat_2', 'cat_1', 'cat_2', 'cat_1'],
    'num': [5,20,22,3,4,30,10,50],
    'date': ['2019-2-1', '2018-02-03','2020-3-11','2019-2-2', '2019-2-2', '2018-12-4','2020-3-11', '2020-12-12']
})

data = pd.DataFrame({
    'cat': ['cat_2', 'cat_2', 'cat_1', 'cat_2'],
    'num': [2,1,0,3],
    'date': ['2020-3-11', '2019-2-2', '2018-12-04', '2020-3-11']
})
data['date'] = pd.to_datetime(data['date'])
aggreg = data.groupby('cat').resample('Y', on='date')
mean_ = aggreg.mean()
agg_mean_ = aggreg.agg({'num': 'mean'})

with

(Pdb++) data
     cat  num       date
0  cat_2    2 2020-03-11
1  cat_2    1 2019-02-02
2  cat_1    0 2018-12-04
3  cat_2    3 2020-03-11

(Pdb++) mean_
                  num
cat   date           
cat_1 2018-12-31  0.0
cat_2 2019-12-31  1.0
      2020-12-31  2.5

(Pdb++) agg_mean_
                  num
cat   date           
cat_1 2020-12-31    0
cat_2 2018-12-31    2
      2019-12-31    1
      2020-12-31    3

I investigated this a bit and found that we are creating the bins differently. I guess somewhere here lies the problem. It looks like the aggregate function somehow mixes up the bins, since it assignes 2020-12-31 to cat_1 where this clearly must be 2018-12-31. Has anyone ideas how to narrow the problem down?

[CloseChoice]

Ok, I think I know what is going on. We confuse index-names with index-positions when using agg.
Having a closer look at the function get_binner reveals:

obj:      
    cat     num       date
2  cat_1    0     2018-12-04
1  cat_2    1     2019-02-02
0  cat_2    2     2020-03-11
3  cat_2    3     2020-03-11
bins: [1 2 4]
binlabels: DatetimeIndex(['2018-12-31', '2019-12-31', '2020-12-31'], dtype='datetime64[ns]', name='date', freq='A-DEC')

This gives exactly how the values are processed. The index-name 0 is mapped to 2018-12-31 with value 2 though it should be 2020-12-31. Index-name 2 should be mapped to 2018-12-31 but is mapped to 2020-12-31. So the way to fix this is to use the index-names instead of ilocs.