在R中按自定义日期范围筛选姓名对应的日期数据

处理步骤： #

• 将所有日期字段转换为可比较的datetime类型，避免字符串比较误差
• 把主数据框和日期范围数据框按姓名关联，让每条记录匹配对应起止日期
• 筛选出日期落在对应范围内的记录

代码实现： #

import pandas as pd

# 构造主数据框df
df = pd.DataFrame({
    'Name': ['Luis']*5 + ['Pedro']*10 + ['John']*6 + ['Ingrid']*5,
    'Date': ['01/05/22','02/05/22','03/05/22','04/05/22','05/05/22',
             '01/05/22','02/05/22','03/05/22','04/05/22','05/05/22',
             '06/05/22','07/05/22','08/05/22','09/05/22','10/05/22',
             '03/05/22','04/05/22','05/05/22','06/05/22','07/05/22','08/05/22',
             '01/05/22','02/05/22','03/05/22','04/05/22','05/05/22']
})

# 构造日期范围数据框df_ranges
df_ranges = pd.DataFrame({
    'Name': ['Luis', 'Pedro', 'John', 'Ingrid'],
    'Onset': ['01/05/22', '04/05/22', '03/05/22', '01/05/22'],
    'End': ['03/05/22', '10/05/22', '06/05/22', '04/05/22']
})

# 转换日期列为datetime类型，指定格式为日/月/年
df['Date'] = pd.to_datetime(df['Date'], format='%d/%m/%y')
df_ranges['Onset'] = pd.to_datetime(df_ranges['Onset'], format='%d/%m/%y')
df_ranges['End'] = pd.to_datetime(df_ranges['End'], format='%d/%m/%y')

# 按姓名合并两个数据框
merged_df = pd.merge(df, df_ranges, on='Name')

# 筛选日期在起止范围之间的记录（包含两端）
filtered_df = merged_df[(merged_df['Date'] >= merged_df['Onset']) & (merged_df['Date'] <= merged_df['End'])]

# 保留目标列并重置索引
result = filtered_df[['Name', 'Date']].reset_index(drop=True)

# 将日期转换回原始格式
result['Date'] = result['Date'].dt.strftime('%d/%m/%y')

print(result)

输出结果： #

Name      Date
0     Luis  01/05/22
1     Luis  02/05/22
2     Luis  03/05/22
3     Pedro 04/05/22
4     Pedro 05/05/22
5     Pedro 06/05/22
6     Pedro 07/05/22
7     Pedro 08/05/22
8     Pedro 09/05/22
9     Pedro 10/05/22
10    John  03/05/22
11    John  04/05/22
12    John  05/05/22
13    John  06/05/22
14   Ingrid 01/05/22
15   Ingrid 02/05/22
16   Ingrid 03/05/22
17   Ingrid 04/05/22

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