Pandas Datetime Series按间隔取值报错：无法解析symbol日期格式

我尝试基于以下数据构建仪表盘，生成包含不同时间间隔净持仓数据的表格：

print(cot_report_splice)输出：

date        symbol     Net Positioning
2020-10-20  PA         3413
            PL         7825
2020-10-27  PA         3468
            PL        10051
2020-11-03  PA         2416
                      ...  
2022-12-06  PL        25636
2022-12-13  PA         -883
            PL        28445
2022-12-20  PA        -2627
            PL        24052

目标仪表盘表格如下：

但执行下方for循环代码时，出现错误：unknown datetime string format, unable to parse: symbol，请求解决办法：

cot_report = get_cot_report_fmp(start_date=START_DATE, end_date=END_DATE)

contracts_list = ['PL', 'PA']

cot_report = cot_report[cot_report['symbol'].isin(contracts_list)]

cot_report = cot_report.reset_index().set_index(['date', 'symbol']).unstack().stack()

cot_report = cot_report[['noncomm_positions_long_all', 'noncomm_positions_short_all']]

cot_report['net_positioning'] = cot_report['noncomm_positions_long_all']-cot_report['noncomm_positions_short_all']

cot_report_splice = cot_report.loc[:, 'net_positioning']

results = pd.DataFrame(columns=['symbol', '1W', '2W', '3MO', '1YR'])

for symbol in cot_report_splice['symbol'].unique():
    symbol_df = cot_report_splice[cot_report_splice['symbol'] == symbol]
    
    most_recent_date = symbol_df['date'].max()
    
    week_mask = (symbol_df['date'] >= most_recent_date - pd.Timedelta(weeks=1)) & (symbol_df['date'] <= most_recent_date)
    two_week_mask = (symbol_df['date'] >= most_recent_date - pd.Timedelta(weeks=2)) & (symbol_df['date'] <= most_recent_date)
    three_mo_mask = (symbol_df['date'] >= most_recent_date - pd.Timedelta(weeks=15)) & (symbol_df['date'] <= most_recent_date)
    year_mask = (symbol_df['date'] >= most_recent_date - pd.Timedelta(weeks=52)) & (symbol_df['date'] <= most_recent_date)
    week_row = symbol_df.loc[week_mask, 'net_positioning'].iloc[0]
    two_week_row = symbol_df.loc[two_week_mask, 'net_positioning'].iloc[-1]
    three_mo_row = symbol_df.loc[three_mo_mask, 'net_positioning'].iloc[-1]
    year_row = symbol_df.loc[year_mask, 'net_positioning'].iloc[-1]
    
    results = results.append({'symbol': contract, '1W': week_row, '2W': two_week_row, '3M': three_mo_row, '1YR': year_row}, ignore_index=True)

results.set_index('symbol', inplace=True)

display(HTML(results._repr_html_()))

1. 核心问题：cot_report_splice是一个带有date和symbol双层索引的Series，不是DataFrame。当你尝试cot_report_splice['symbol']时，Pandas会把索引名symbol当成字符串去尝试解析成datetime（因为索引的第一层是datetime类型），从而抛出解析错误。
2. 次要问题：
   • 循环中使用未定义的变量contract，应该用当前循环的symbol
   • 结果字典中的3M列名和初始DataFrame的3MO不匹配
   • 时间区间的数据获取逻辑有误（比如week_row取iloc[0]可能拿到旧数据，应该取最新的）

import pandas as pd
from IPython.display import HTML

cot_report = get_cot_report_fmp(start_date=START_DATE, end_date=END_DATE)

contracts_list = ['PL', 'PA']

cot_report = cot_report[cot_report['symbol'].isin(contracts_list)]

# 保留必要列后计算净持仓，避免不必要的unstack/stack操作
cot_report['net_positioning'] = cot_report['noncomm_positions_long_all'] - cot_report['noncomm_positions_short_all']
cot_report_splice = cot_report[['date', 'symbol', 'net_positioning']].copy()

# 确保date是datetime类型
cot_report_splice['date'] = pd.to_datetime(cot_report_splice['date'])

results = pd.DataFrame(columns=['symbol', '1W', '2W', '1MO', '3MO', '1YR'])

for symbol in contracts_list:
    symbol_df = cot_report_splice[cot_report_splice['symbol'] == symbol].sort_values('date')
    most_recent_date = symbol_df['date'].max()
    
    # 1W：最近1周内的最新数据
    week_data = symbol_df[symbol_df['date'] >= most_recent_date - pd.Timedelta(weeks=1)]
    week_row = week_data['net_positioning'].iloc[-1] if not week_data.empty else None
    
    # 2W：最近2周内，除了最新1周的最新数据（即上周的数据）
    two_week_data = symbol_df[(symbol_df['date'] >= most_recent_date - pd.Timedelta(weeks=2)) & 
                              (symbol_df['date'] < most_recent_date - pd.Timedelta(weeks=1))]
    two_week_row = two_week_data['net_positioning'].iloc[-1] if not two_week_data.empty else None
    
    # 1MO：最近1个月的最新数据（这里按4周计算，可根据需求调整）
    one_mo_data = symbol_df[symbol_df['date'] >= most_recent_date - pd.Timedelta(weeks=4)]
    one_mo_row = one_mo_data['net_positioning'].iloc[-1] if not one_mo_data.empty else None
    
    # 3MO：最近3个月的最新数据（按12周计算）
    three_mo_data = symbol_df[symbol_df['date'] >= most_recent_date - pd.Timedelta(weeks=12)]
    three_mo_row = three_mo_data['net_positioning'].iloc[-1] if not three_mo_data.empty else None
    
    # 1YR：最近1年的最新数据
    year_data = symbol_df[symbol_df['date'] >= most_recent_date - pd.Timedelta(weeks=52)]
    year_row = year_data['net_positioning'].iloc[-1] if not year_data.empty else None
    
    results = results.append({
        'symbol': symbol, 
        '1W': week_row, 
        '2W': two_week_row, 
        '1MO': one_mo_row, 
        '3MO': three_mo_row, 
        '1YR': year_row
    }, ignore_index=True)

results.set_index('symbol', inplace=True)
display(HTML(results._repr_html_()))

1. 重构数据处理流程：直接从原DataFrame中提取date、symbol和计算后的net_positioning，避免了复杂的索引操作，减少出错概率。
2. 明确数据类型：强制将date列转为datetime类型，确保时间比较逻辑正确。
3. 修正变量名和列名：把contract改成symbol，3M改成3MO，保持前后一致。
4. 优化时间区间逻辑：
   • 1W取最近1周的最新数据
   • 2W取最近2周内、上周的最新数据（匹配目标表格的逻辑）
   • 对空数据做判断，避免索引越界错误
5. 直接遍历指定合约列表：比遍历unique值更稳定，确保只处理需要的合约。

内容的提问来源于stack exchange，提问作者SJ1