BUG: Resampling to monthly/quarterly/yearly ignores offset parameter

[rwijtvliet]

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
# Quarterly values at 06:00
quarterly = pd.Series(1.0, pd.date_range('2020-01-01 06:00', freq = 'QS', periods=4))
# 2020-01-01 06:00:00    1.0
# 2020-04-01 06:00:00    1.0
# 2020-07-01 06:00:00    1.0
# 2020-10-01 06:00:00    1.0
# Freq: QS-JAN, dtype: float64

# If we upsample to daily values we need to specify the offset parameter, e.g.
offset = pd.Timedelta('06:00:00')
quarterly.resample('D', offset=offset).asfreq().ffill()
# 2020-01-01 06:00:00    1.0
# 2020-01-02 06:00:00    1.0
# 2020-01-03 06:00:00    1.0
#                       ... 
# 2020-09-29 06:00:00    1.0
# 2020-09-30 06:00:00    1.0
# 2020-10-01 06:00:00    1.0
# Freq: D, Length: 275, dtype: float64

# However, if we upsample to monthly values, this does not work (=bug):
quarterly.resample('MS', offset=offset).asfreq().ffill()
# 2020-01-01   NaN
# 2020-02-01   NaN
# 2020-03-01   NaN
# 2020-04-01   NaN
# 2020-05-01   NaN
# 2020-06-01   NaN
# 2020-07-01   NaN
# 2020-08-01   NaN
# 2020-09-01   NaN
# 2020-10-01   NaN
# Freq: MS, dtype: float64

Issue Description

Resampling to monthly, quarterly, or yearly values does not take the offset parameter into account. This is an issue in both up- and downsampling. For a downsampling example, see here.

Expected Behavior

# 2020-01-01 06:00:00  1.0
# 2020-02-01 06:00:00  1.0
# 2020-03-01 06:00:00  1.0
# 2020-04-01 06:00:00  1.0
# 2020-05-01 06:00:00  1.0
# 2020-06-01 06:00:00  1.0
# 2020-07-01 06:00:00  1.0
# 2020-08-01 06:00:00  1.0
# 2020-09-01 06:00:00  1.0
# 2020-10-01 06:00:00  1.0
# Freq: MS, dtype: float64

Installed Versions

INSTALLED VERSIONS ------------------ commit : 91111fd python : 3.9.13.final.0 python-bits : 64 OS : Windows OS-release : 10 Version : 10.0.19045 machine : AMD64 processor : Intel64 Family 6 Model 165 Stepping 2, GenuineIntel byteorder : little LC_ALL : None LANG : None LOCALE : de_DE.cp1252

pandas : 1.5.1
numpy : 1.23.4
pytz : 2022.6
dateutil : 2.8.2
setuptools : 63.4.1
pip : 22.2.2
Cython : None
pytest : 7.1.3
hypothesis : None
sphinx : 5.3.0
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : 3.1.2
IPython : 8.5.0
pandas_datareader: None
bs4 : 4.11.1
bottleneck : None
brotli : None
fastparquet : None
fsspec : None
gcsfs : None
matplotlib : 3.6.2
numba : None
numexpr : None
odfpy : None
openpyxl : 3.0.10
pandas_gbq : None
pyarrow : None
pyreadstat : None
pyxlsb : None
s3fs : None
scipy : None
snappy : None
sqlalchemy : None
tables : None
tabulate : None
xarray : None
xlrd : None
xlwt : None
zstandard : None
tzdata : None

[haleemur]

I agree that it would be consistent if resample with offset always generated an index where the offset was specified. Currently, the offset is specified for D, but does not seem to be specified for Q or QS or M or MS or 'W', where the time seems normalized to midnight.

I'm not sure what the expected labels should be for Q (or M), because I'm unclear on whether 2022-03-31 should mean 2022-03-31 00:00:00.000000 or 2022-03-31 23:59:59.999999

instead of

2022-03-31
2022-04-30

should it be a)

2022-03-31 06:00:00
2020-06-30 06:00:00

or b)

2022-04-01 06:00:00
2020-07-01 06:00:00

or c)

2022-04-01 05:59:59.999999
2020-07-01 05:59:59.999999

[rwijtvliet]

The M and Q question is a good one. I always mentally expand the not-shown parts of a timestamp with zeroes (or ones, in dates). So, 2022-03-31 is actually 2022-03-31 00:00:00 and 2022 is actually 2022-01-01 00:00:00. I think this is the only sensible way, as the alternative would require keeping track of 'where/how the timestamp was created'. It's also how pandas does it:

# uniform 1 for every hour in March
hours = pd.Series(1, pd.date_range('2022-03', '2022-04', freq='H', inclusive='left'))

# resampling to "month-end":
hours.resample('M').sum()
# 2022-03-31    744
# Freq: M, dtype: int64

# let's look at that timestamp
hours.resample('M').sum().index[0]
# Timestamp('2022-03-31 00:00:00', freq='M')

Now, it's certainly a bit strange, in that 2022-03-31 00:00:00 is actually 24h before the end of the month. The timestamp 2022-03-31 00:00:00 is therefore the sum of 720 hourly values that precede it and 23 hourly values that follow it (and 1 that has the same timestamp). This is weird, confusing, and the reason I only ever use left-closed intervals instead of right-closed ones - exactly to avoid this kind of ambiguity. But, it shows how to handle this case and be consistent with the way end-of-X is handled elsewhere.

[haleemur]

\take

[jeflem]

Hi,

very similar/same issue with business day frequency. Example:

s = pd.Series(data=1, index=[
    pd.Timestamp('2019-01-01 01:00:00'),
    pd.Timestamp('2019-01-01 08:00:00'),
    pd.Timestamp('2019-01-02 03:00:00'),
    pd.Timestamp('2019-01-02 04:00:00'),
    pd.Timestamp('2019-01-03 05:00:00'),
    pd.Timestamp('2019-01-03 09:00:00'),
    pd.Timestamp('2019-01-04 10:00:00'),
    pd.Timestamp('2019-01-04 11:00:00'),
    pd.Timestamp('2019-01-05 12:00:00'),
    pd.Timestamp('2019-01-05 13:00:00'),
    pd.Timestamp('2019-01-06 14:00:00'),
    pd.Timestamp('2019-01-06 15:00:00'),
    pd.Timestamp('2019-01-07 16:00:00'),
    pd.Timestamp('2019-01-07 17:00:00'),
    pd.Timestamp('2019-01-08 18:00:00'),
    pd.Timestamp('2019-01-08 19:00:00'),
    pd.Timestamp('2019-01-09 20:00:00'),
    pd.Timestamp('2019-01-09 21:00:00'),
]).sort_index()

# with D the offset works
print(s.resample('D', offset='7H', closed='right', label='right').sum())

# output:
# 2019-01-01 07:00:00    1
# 2019-01-02 07:00:00    3
# 2019-01-03 07:00:00    1
# 2019-01-04 07:00:00    1
# 2019-01-05 07:00:00    2
# 2019-01-06 07:00:00    2
# 2019-01-07 07:00:00    2
# 2019-01-08 07:00:00    2
# 2019-01-09 07:00:00    2
# 2019-01-10 07:00:00    2
# Freq: D, dtype: int64

# with B the offset is ignored
print(s.resample('B', offset='7H', closed='right', label='right').sum())

# output:
# 2019-01-01    2
# 2019-01-02    2
# 2019-01-03    2
# 2019-01-04    2
# 2019-01-07    6
# 2019-01-08    2
# 2019-01-09    2

Many thanks for looking into this!

By the way: could be related to #41943.