BUG: DataFrame.first has unexpected behavior when passing a DateOffset

[ehariri]

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
import numpy as np 

i = index=pd.date_range("2018-04-09", periods=30, freq="2D")
df = pd.DataFrame({"A": pd.Series(np.arange(30), index=i)}, index=i)

do = pd.DateOffset(days=15)
assert df.first(do).equals(df.first("15D"))

Issue Description

When passed a DateOffset, DataFrame.first exhibits incorrect behavior as follows.

>>> df.first(do)
            A
2018-04-09  0
>>> df.first("15D")
            A
2018-04-09  0
2018-04-11  1
2018-04-13  2
2018-04-15  3
2018-04-17  4
2018-04-19  5
2018-04-21  6
2018-04-23  7 

This appears to arise from first's implementation (starting on line 8387) handling the special edge case for when the first value is at the end of a period for string offset inputs (e.g. "1M") parsed with to_offset.

offset = to_offset(offset)
if not isinstance(offset, Tick) and offset.is_on_offset(self.index[0]):
    # GH#29623 if first value is end of period, remove offset with n = 1
    #  before adding the real offset
    end_date = end = self.index[0] - offset.base + offset
else:
    end_date = end = self.index[0] + offset

This (unintentionally) captures user-specified DateOffset's passed in as arguments as well.

Expected Behavior

Behavior when passing in a DateOffset should match the behavior when passing a string, i.e.

>>> do = pd.DateOffset(days=15)
>>> df.first(do)
            A
2018-04-09  0
2018-04-11  1
2018-04-13  2
2018-04-15  3
2018-04-17  4
2018-04-19  5
2018-04-21  6
2018-04-23  7

Installed Versions

INSTALLED VERSIONS

commit : 945c9ed
python : 3.8.12.final.0
python-bits : 64
OS : Darwin
OS-release : 21.3.0
Version : Darwin Kernel Version 21.3.0: Wed Jan 5 21:37:58 PST 2022; root:xnu-8019.80.24~20/RELEASE_ARM64_T6000
machine : x86_64
processor : i386
byteorder : little
LC_ALL : None
LANG : en_US.UTF-8
LOCALE : en_US.UTF-8

pandas : 1.3.4
numpy : 1.20.3
pytz : 2021.3
dateutil : 2.8.2
pip : 21.2.4
setuptools : 58.0.4
Cython : 0.29.24
pytest : 6.2.5
hypothesis : None
sphinx : 4.4.0
blosc : None
feather : None
xlsxwriter : None
lxml.etree : 4.7.1
html5lib : None
pymysql : 1.0.2
psycopg2 : None
jinja2 : 3.0.2
IPython : 7.29.0
pandas_datareader: None
bs4 : 4.10.0
bottleneck : None
fsspec : 2021.11.1
fastparquet : None
gcsfs : 2021.11.1
matplotlib : 3.4.3
numexpr : None
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : 5.0.0
pyxlsb : None
s3fs : None
scipy : 1.7.3
sqlalchemy : 1.4.27
tables : None
tabulate : None
xarray : None
xlrd : None
xlwt : None
numba : 0.55.0

[njriasan]

To elaborate on this in more detail, the changes provided here seem to assume the semantics of the offset are based on the user passing a string. In particular, I believe this change was enabled to handle edge cases for non-fixed frequencies (i.e. "1M" meaning within the same month).

I would argue that the issue here is that offsets should be treated differently when explicitly passed by a user. For example, if I pass "1M", it seems reasonable for that to be within the same month, but if I explicitly pass offset = pandas.tseries.offsets.MonthEnd(1) as my offset, then as a user, I expect first to include everything within S.iloc[0] + offset, regardless of what possible boundary cases exist.

[simonjayhawkins]

Thanks @ehariri for the report

Expected Behavior

Behavior when passing in a DateOffset should match the behavior when passing a string, i.e.

This was indeed the behavior in pandas 1.2.5

first bad commit: [a4efa9f] BUG: first("2M") returning incorrect results (#38446)

labelling as a regression for now pending further investigation

cc @phofl

[andjhall]

take

[DeaMariaLeon]

take

[MarcoGorelli]

As discussed in the call, let's deprecate DataFrame.first and DataFrame.last (likewise for Series.first and Series.last)

Motivation:

• they're poorly named and make incorrect analogies to SeriesGroupBy.first / last
• they're half-baked an buggy: they don't work for DateOffset, and arguably do the wrong thing when the index starts on the last day of the month
• they require the index to be sorted, but no error/warning is thrown if it's not
• they're just convenience functions, and can be easily rewritten on the user side with something like ser[ser.index < ser.index[0] + offset], which is also a lot more explicit

[DeaMariaLeon]

I would like to work on the deprecation if that's fine.

[MarcoGorelli]

let's do this, thanks!