如何在Pandas中使用BETWEEN子句筛选字符串类型批次编码列

直接使用batch_code.between()得到错误结果，核心原因是字符串字典序和实际季度的时间顺序不匹配：比如字符串'B1_2024'会被判定为小于'B3_2023'，但实际时间上B1_2024在B3_2023之后，导致筛选漏掉中间的季度记录。

下面是三种可行的解决方法：

方法1：拆分季度与年份，构造数值化排序键 #

将批次编码拆分为年份和季度的整数数值，通过年份*4 + 季度生成一个能代表时间顺序的数值键，再基于该键做区间筛选。

import pandas as pd

# 初始化示例DataFrame
money = [100,200,300,400,500,600,700,800,900,1000,1100,1200]
batch_code = ['B1_2023','B2_2023','B3_2023','B4_2023','B1_2024','B2_2024','B3_2024','B4_2024','B1_2025','B2_2025','B3_2025','B4_2025']
test_df = pd.DataFrame({'money': money, 'batch_code': batch_code})
test_df['money'] = test_df['money'].astype(int)

# 定义筛选参数
from_batch = 'B3_2023'
to_batch = 'B4_2024'

# 拆分年份和季度，转换为整数
test_df['year'] = test_df['batch_code'].str.split('_').str[1].astype(int)
test_df['quarter'] = test_df['batch_code'].str.split('_').str[0].str[1].astype(int)

# 生成时间排序键
test_df['time_key'] = test_df['year'] * 4 + test_df['quarter']

# 计算起止批次对应的排序键
from_year, from_q = int(from_batch.split('_')[1]), int(from_batch.split('_')[0][1])
from_key = from_year * 4 + from_q

to_year, to_q = int(to_batch.split('_')[1]), int(to_batch.split('_')[0][1])
to_key = to_year * 4 + to_q

# 执行筛选并清理临时列
filtered_df = test_df[(test_df['time_key'] >= from_key) & (test_df['time_key'] <= to_key)]
filtered_df = filtered_df.drop(['year', 'quarter', 'time_key'], axis=1)

print(filtered_df)

方法2：转换为季度时间对象 #

将批次编码转换为对应的季度日期（如季度末日期），利用Pandas的日期区间筛选功能实现需求。

import pandas as pd

# 初始化示例DataFrame
money = [100,200,300,400,500,600,700,800,900,1000,1100,1200]
batch_code = ['B1_2023','B2_2023','B3_2023','B4_2023','B1_2024','B2_2024','B3_2024','B4_2024','B1_2025','B2_2025','B3_2025','B4_2025']
test_df = pd.DataFrame({'money': money, 'batch_code': batch_code})
test_df['money'] = test_df['money'].astype(int)

# 定义筛选参数
from_batch = 'B3_2023'
to_batch = 'B4_2024'

# 批次转日期的函数：将Bn_YYYY转换为对应季度的最后一天
def batch_to_quarter_end(batch):
    q_str, year_str = batch.split('_')
    quarter = int(q_str[1])
    # 构造季度末日期，如B3_2023对应2023-09-30
    return pd.to_datetime(f"{year_str}-{quarter*3}-01") + pd.offsets.MonthEnd(0)

# 生成日期列
test_df['quarter_end'] = test_df['batch_code'].apply(batch_to_quarter_end)

# 转换起止批次为日期
from_date = batch_to_quarter_end(from_batch)
to_date = batch_to_quarter_end(to_batch)

# 筛选并清理临时列
filtered_df = test_df[test_df['quarter_end'].between(from_date, to_date)].drop('quarter_end', axis=1)

print(filtered_df)

方法3：基于固定顺序的索引筛选 #

如果DataFrame已经严格按季度时间顺序排列，可以直接通过批次的位置索引进行区间筛选。

import pandas as pd

# 初始化示例DataFrame
money = [100,200,300,400,500,600,700,800,900,1000,1100,1200]
batch_code = ['B1_2023','B2_2023','B3_2023','B4_2023','B1_2024','B2_2024','B3_2024','B4_2024','B1_2025','B2_2025','B3_2025','B4_2025']
test_df = pd.DataFrame({'money': money, 'batch_code': batch_code})
test_df['money'] = test_df['money'].astype(int)

# 定义筛选参数
from_batch = 'B3_2023'
to_batch = 'B4_2024'

# 获取所有批次的列表
batch_list = test_df['batch_code'].tolist()
# 找到起止批次的索引
start_idx = batch_list.index(from_batch)
end_idx = batch_list.index(to_batch)

# 按索引区间筛选
filtered_df = test_df.iloc[start_idx:end_idx+1]

print(filtered_df)

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