解决Pandas DataFrame query方法中日期时间变量引用失败的问题

import datetime as dt
import pandas as pd

# 模拟数据加载
# 假设df已经从"Ships.xlsx"加载，并且'Commissioned'和'Decommissioned'列已正确转换为datetime类型
data = {
    'BF': ['X', 'X', 'X', 'X', 'X'],
    'Ship': ['Able', 'Abraham Lincoln', 'Acadia', 'Ajax', 'Alabama'],
    'Type': ['Destroyer', 'Cruiser', 'Frigate', 'Cruiser', 'Battleship'],
    'Commissioned': [pd.Timestamp('1992-07-22'), pd.Timestamp('1989-11-11'), pd.Timestamp('1981-06-06'), pd.Timestamp('1943-09-30'), pd.Timestamp('1985-05-25')],
    'Decommissioned': [pd.Timestamp('2030-09-30'), pd.NaT, pd.Timestamp('1994-12-16'), pd.Timestamp('1986-12-31'), pd.Timestamp('2028-09-30')]
}
df = pd.DataFrame(data)

now = dt.date.today() # 获取当前日期
print(f"当前日期: {now}, 类型: {type(now)}")
print("\nDataFrame数据类型:\n", df.dtypes)

# 筛选当前活跃的战列舰
qrystr_bf = "BF == 'X' " \
            "and (Commissioned != 'NaT' and Commissioned <= @now)" \
            "and (Decommissioned >= @now or Decommissioned == 'NaT')"
df_battle_force = df.query(qrystr_bf)
print(f"\n当前战列舰数量: {len(df_battle_force)}")

# 筛选当前活跃的巡洋舰
qrystr_cruiser = "Type == 'Cruiser' " \
                 "and (Commissioned != 'NaT' and Commissioned <= @now)" \
                 "and (Decommissioned >= @now or Decommissioned == 'NaT')"
df_active_cruisers = df.query(qrystr_cruiser)
print("\n当前活跃的巡洋舰:\n", df_active_cruisers)

# ... (接上面的代码)

lst_cruisers_by_year = []
year_start = 1980
year_stop = 1985

for yr in range(year_start, year_stop + 1):
    stopDate = pd.Timestamp(yr, 12, 31).date()
    print(f"\n当前年份截止日期: {stopDate}, 类型: {type(stopDate)}")

    # 错误的查询字符串构造方式
    # qrystr_fail = "Type == 'Cruiser' " \
    #               " and (Commissioned != 'NaT' and Commissioned <= 'stopDate')" \
    #               " and (Decommissioned >= 'stopDate' or Decommissioned == 'NaT') "
    # df_cruisers_fail = df.query(qrystr_fail) # 这里会引发错误

    # ... (省略错误处理后的代码)

# ... (接上面的代码)

lst_cruisers_by_year = []
year_start = 1980
year_stop = 1985

for yr in range(year_start, year_stop + 1):
    stopDate = pd.Timestamp(yr, 12, 31).date() # 确保stopDate是datetime.date对象
    print(f"\n当前年份截止日期: {stopDate}, 类型: {type(stopDate)}")

    # 正确的查询字符串构造方式：使用 @ 引用 stopDate 变量
    qrystr_corrected = "Type == 'Cruiser' " \
                       " and (Commissioned != 'NaT' and Commissioned <= @stopDate)" \
                       " and (Decommissioned >= @stopDate or Decommissioned == 'NaT') "

    df_cruisers_year = df.query(qrystr_corrected)

    nr_ships = len(df_cruisers_year)
    lst_cruisers_by_year.append([yr, nr_ships])
    print(f"年份 {yr} 活跃巡洋舰数量: {nr_ships}")

print("\n每年活跃巡洋舰统计结果:\n", lst_cruisers_by_year)