BUG: pd.col does not support & for combining conditions in .loc

[mattharrison]

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 io


data = '''datetime,cfs
2018-02-28,350
2018-03-15,420
2019-04-10,380
2019-06-01,450
'''
dd = pd.read_csv(io.StringIO(data), parse_dates=['datetime']).set_index('datetime')
(dd
 .reset_index()
 .sort_values('datetime')
 .loc[(pd.col('datetime') > '2018-03-01') & (pd.col('datetime') <= '2019-05-31')]
 .assign(cfs=pd.col('cfs').clip(upper=400))

)

Issue Description

When using pd.col inside .loc to filter rows based on multiple conditions, combining conditions with & does not work as expected.

Expected Behavior

It works like this lambda version:

import pandas as pd
import io


data = '''datetime,cfs
2018-02-28,350
2018-03-15,420
2019-04-10,380
2019-06-01,450
'''
dd = pd.read_csv(io.StringIO(data), parse_dates=['datetime']).set_index('datetime')
(dd
 .reset_index()
 .sort_values('datetime')
 #.loc[(pd.col('datetime') > '2018-03-01') & (pd.col('datetime') <= '2019-05-31')]
 .loc[lambda df_: (df_['datetime'] > '2018-03-01') & (df_['datetime'] <= '2019-05-31')]
 .assign(cfs=pd.col('cfs').clip(upper=400))
)

Installed Versions

Replace this line with the output of pd.show_versions()

[rhshadrach]

Thanks for the report. Tagging this as 3.0 as it would be great to get a fix in before the release, but it's not clear to me how difficult fixing this will be. Certainly not a blocker.

[zhangbowen-coder]

I added & operator support and it performs just as mattharrison expected in this case, tests will be added later these days.