BUG: resample dropping column

[hyonschu]

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

df = pd.read_csv('data.csv')

df.resample('M', on='Date In').count()

Issue Description

df.resample('M', on='Date In').count()

In prior versions, this used to output a dataframe where it kept column "Date In" and the count of Date In for all months relative to Date In.

In 2.0.1, it returns the same dataframe but without the count of "Date In," which is now the index of the dataframe.

If I want to use this function to understand how many came IN and OUT in the same month, this function no longer tells me that.

Expected Behavior

It should also keep the 'on=' column in the resulting DataFrame

Installed Versions

INSTALLED VERSIONS ------------------ commit : 37ea63d python : 3.9.4.final.0 python-bits : 64 OS : Windows OS-release : 10 Version : 10.0.19041 machine : AMD64 processor : Intel64 Family 6 Model 142 Stepping 12, GenuineIntel byteorder : little LC_ALL : None LANG : None LOCALE : English_United States.1252

pandas : 2.0.1
numpy : 1.24.3
pytz : 2021.3
dateutil : 2.8.2
setuptools : 49.2.1
pip : 23.1.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 : 3.0.3
IPython : 8.13.1
pandas_datareader: None
bs4 : 4.10.0
bottleneck : None
brotli : None
fastparquet : None
fsspec : None
gcsfs : None
matplotlib : 3.5.1
numba : None
numexpr : None
odfpy : None
openpyxl : 3.0.9
pandas_gbq : None
pyarrow : None
pyreadstat : None
pyxlsb : None
s3fs : None
scipy : 1.8.0
snappy : None
sqlalchemy : 2.0.12
tables : None
tabulate : None
xarray : None
xlrd : None
zstandard : None
tzdata : 2023.3
qtpy : 2.0.1
pyqt5 : None

[lithomas1]

Can you provide a minimum reproducible example (see https://matthewrocklin.com/minimal-bug-reports)?

(In this case, please provide whatever 'data.csv' is).

[hyonschu]

df = pd.DataFrame({
    'Start Date': pd.to_datetime(['2018-02-22T00:00:00.000000000', '2018-02-22T00:00:00.000000000',
       '2019-02-22T00:00:00.000000000', '2018-02-22T00:00:00.000000000',
       '2019-02-23T00:00:00.000000000']),
    'Completion Date': pd.to_datetime([np.nan, '2018-02-28T00:00:00.000000000',
       '2018-03-05T00:00:00.000000000', '2018-02-23T00:00:00.000000000',
       '2018-03-05T00:00:00.000000000']),
    'Purchase': [1,1,np.nan,np.nan,1]
})

df.resample('Y', on='Completion Date').count()

		Start Date	Purchase
Completion Date		
2018-12-31	4		2


df.resample('Y', on='Start Date').count()

		Completion Date	Purchase
Start Date		
2018-12-31	3		2
2019-12-31	2		1

That is, it does not have a count of Start Date but rather uses it as the index and drops the results from the resulting DataFrame.

What I expected:

		Start Date	Completion Date		Purchase
Start Date		
2018-12-31	3		2			2
2019-12-31	2		2			1

As a use case, I want to know how many were completed in a time period (year/month) but i also want to be able to use that as a point of comparison to how many were began in that same year because the numbers would be different, i.e., "How many completed in 2022 were started in 2022... and 2021... 2020..."

This was normal behavior in older versions of pandas but seemed to have been removed in some subsequent version.

[hyonschu]

Confirming that this behaves as expected in 1.4.3:

import pandas as pd
pd.__version__
df = pd.DataFrame({
    'Start Date': pd.to_datetime(['2018-02-22T00:00:00.000000000', '2018-02-22T00:00:00.000000000',
       '2019-02-22T00:00:00.000000000', '2018-02-22T00:00:00.000000000',
       '2019-02-23T00:00:00.000000000']),
    'Completion Date': pd.to_datetime([np.nan, '2018-02-28T00:00:00.000000000',
       '2018-03-05T00:00:00.000000000', '2018-02-23T00:00:00.000000000',
       '2018-03-05T00:00:00.000000000']),
    'Purchase': [1,1,np.nan,np.nan,1]
})
df.resample('Y', on='Start Date').count()

	Start Date	Completion Date	Purchase
Start Date			
2018-12-31	3	2		2
2019-12-31	2	2		1

[hyonschu]

1.5.3 drops the on= column in the output.

[lithomas1]

Tentatively triaging as a regression.

I'm a little short on time right now, but I'll try to bisect sometime to figure out which PR caused the regression.

[hyonschu]

Anything on this?

Tentatively triaging as a regression.

I'm a little short on time right now, but I'll try to bisect sometime to figure out which PR caused the regression.