BUG: `tseries.offsets.MonthEnd` is leaving out full 24h of period

[avibrazil]

Code Sample, a copy-pastable example

>>> pd.Timestamp('2021-05-20') + pd.tseries.offsets.MonthEnd
Timestamp('2021-05-31 00:00:00')

MonthEnd gets me the first second of the last day, while I was expecting the last second of the last day of month (Timestamp('2021-05-31 23:59:59.99999999')). This yields strange consequences as showed below.

A consequence of unexpected behavior

When using df.resample('M').ffill() to get the last known value for a month, data for the last 24h of a month are ignored.

>>> df=pd.DataFrame({'val': {
  pd.Timestamp('2021-03-24 12:03:00'): 1.0,
  pd.Timestamp('2021-03-31 12:00:00'): 1001.0,
  pd.Timestamp('2021-04-28 12:03:00'): 1.0,
  pd.Timestamp('2021-04-30 23:59:00'): 1002.0,
  pd.Timestamp('2021-05-30 00:00:01'): 1.0,
  pd.Timestamp('2021-05-31 12:00:00'): 1003.0
}})

>>> df
                        val
2021-03-24 12:03:00     1.0
2021-03-31 12:00:00  1001.0
2021-04-28 12:03:00     1.0
2021-04-30 23:59:00  1002.0
2021-05-30 00:00:01     1.0
2021-05-31 12:00:00  1003.0

>>> df.resample('M').ffill()
            val
2021-03-31  1.0
2021-04-30  1.0
2021-05-31  1.0

The expected behavior for the ffill operation is >1000 for all MonthEnd.

On the other hand, a sum operation works as expected:

>>> df.resample('M').sum()
               val
2021-03-31  1002.0
2021-04-30  1003.0
2021-05-31  1004.0

Output of pd.show_versions()

INSTALLED VERSIONS
------------------
commit           : 2cb96529396d93b46abab7bbc73a208e708c642e
python           : 3.8.2.final.0
python-bits      : 64
OS               : Darwin
OS-release       : 20.5.0
Version          : Darwin Kernel Version 20.5.0: Sat May  8 05:10:33 PDT 2021; root:xnu-7195.121.3~9/RELEASE_X86_64
machine          : x86_64
processor        : i386
byteorder        : little
LC_ALL           : None
LANG             : None
LOCALE           : en_US.UTF-8

pandas           : 1.2.4
numpy            : 1.20.1
pytz             : 2021.1
dateutil         : 2.8.1
pip              : 21.1
setuptools       : 41.2.0
Cython           : None
pytest           : None
hypothesis       : None
sphinx           : None
blosc            : None
feather          : None
xlsxwriter       : None
lxml.etree       : 4.6.3
html5lib         : None
pymysql          : 1.0.2
psycopg2         : None
jinja2           : 2.11.3
IPython          : 7.20.0
pandas_datareader: 0.9.0
bs4              : 4.9.3
bottleneck       : None
fsspec           : None
fastparquet      : None
gcsfs            : None
matplotlib       : 3.3.4
numexpr          : None
odfpy            : None
openpyxl         : 3.0.7
pandas_gbq       : None
pyarrow          : 3.0.0
pyxlsb           : None
s3fs             : None
scipy            : 1.6.1
sqlalchemy       : 1.3.23
tables           : None
tabulate         : None
xarray           : None
xlrd             : 2.0.1
xlwt             : None
numba            : 0.53.1

[jbrockmendel]

Seems reasonable. PR would be welcome.

[ra1nty]

To add more, this created huge inconsistency in the resampler:

>>> series = pd.Series(index=pd.date_range(start='2020-01-01', end='2020-02-29').to_period('D').to_timestamp(how='e'), data=1)

>>> series.resample('M').sum()
2020-01-31    31
2020-02-29    29
2020-03-31     0
Freq: M, dtype: int64

>>> series.resample('M').last()
2020-01-31    1
2020-02-29    1
2020-03-31     0
Freq: M, dtype: int64

[avibrazil]

This bug is even worse…

>>> df=pd.DataFrame({'val': {
  pd.Timestamp('2021-03-29 12:03:00'): 1,
  pd.Timestamp('2021-03-31 12:00:00'): 1001,
  pd.Timestamp('2021-04-28 12:03:00'): 1,
  pd.Timestamp('2021-04-30 23:59:00'): 1002,
  pd.Timestamp('2021-06-29 00:00:01'): 1,
  pd.Timestamp('2021-06-30 12:00:00'): 1003
}})

>>> for i in range(4):
    df[f'val{i}']=df.val

>>> df
|                     |   val |   val0 |   val1 |   val2 |   val3 |
|:--------------------|------:|-------:|-------:|-------:|-------:|
| 2021-03-29 12:03:00 |     1 |      1 |      1 |      1 |      1 |
| 2021-03-31 12:00:00 |  1001 |   1001 |   1001 |   1001 |   1001 |
| 2021-04-28 12:03:00 |     1 |      1 |      1 |      1 |      1 |
| 2021-04-30 23:59:00 |  1002 |   1002 |   1002 |   1002 |   1002 |
| 2021-06-29 00:00:01 |     1 |      1 |      1 |      1 |      1 |
| 2021-06-30 12:00:00 |  1003 |   1003 |   1003 |   1003 |   1003 |

>>> df.resample('ME').agg(dict(val0='sum',val1='ffill',val2='mean',val3='median'))
|                     |   val0 |   val1 |   val2 |   val3 |
|:--------------------|-------:|-------:|-------:|-------:|
| 2021-03-29 12:03:00 |    nan |      1 |  nan   |  nan   |
| 2021-03-31 00:00:00 |   1002 |    nan |  501   |  501   |
| 2021-03-31 12:00:00 |    nan |   1001 |  nan   |  nan   |
| 2021-04-28 12:03:00 |    nan |      1 |  nan   |  nan   |
| 2021-04-30 00:00:00 |   1003 |    nan |  501.5 |  501.5 |
| 2021-04-30 23:59:00 |    nan |   1002 |  nan   |  nan   |
| 2021-05-31 00:00:00 |      0 |    nan |  nan   |  nan   |
| 2021-06-29 00:00:01 |    nan |      1 |  nan   |  nan   |
| 2021-06-30 00:00:00 |   1004 |    nan |  502   |  502   |
| 2021-06-30 12:00:00 |    nan |   1003 |  nan   |  nan   |

The index and computed values are completely wrong.

I can keep going through bizarre land:

>>> df.resample('ME', kind='period').agg(dict(val0='sum',val1='ffill',val2='mean',val3='median')
|                     |   val0 |   val1 |   val2 |   val3 |
|:--------------------|-------:|-------:|-------:|-------:|
| 2021-03             |   1002 |    nan |  501   |  501   |
| 2021-04             |   1003 |    nan |  501.5 |  501.5 |
| 2021-05             |      0 |    nan |  nan   |  nan   |
| 2021-06             |   1004 |    nan |  502   |  502   |
| 2021-03-29 12:03:00 |    nan |      1 |  nan   |  nan   |
| 2021-03-31 12:00:00 |    nan |   1001 |  nan   |  nan   |
| 2021-04-28 12:03:00 |    nan |      1 |  nan   |  nan   |
| 2021-04-30 23:59:00 |    nan |   1002 |  nan   |  nan   |
| 2021-06-29 00:00:01 |    nan |      1 |  nan   |  nan   |
| 2021-06-30 12:00:00 |    nan |   1003 |  nan   |  nan   |

I'm in Pandas 2.2.3 and Python 3.13.7