Pandas需求：基于另一DataFrame行查询并比较数值

需求说明 #

我有两个DataFrame：df1是基准记录（包含用于给模拟记录评分的数值数据），df2是模拟记录，需要完成以下操作：

• 对df2的每一行，在df1中找到Name、Time列完全匹配，且**Timestamp最接近（最新）**的行
• 基于匹配到的df1行，计算范围：low_range = df1['Po'] - df1['Ref']，high_range = df1['Po'] + df1['Ref']，判断df2['Sim']是否在该范围内，结果存入新列Sim Score（符合为True，否则为False）
• 对df2所有行重复上述操作

补充说明：

• df1和df2行数、列数可能不同，部分列名相同但值不同
• 仅需匹配df1中最新的符合条件的基准记录
• 之前在Google Sheets用IF+QUERY实现，现在要换成Python Pandas

示例数据 #

df1基准记录（关键列） #

Timestamp            Name     Time     Po  Ref
7/11/2022 11:30:00   trial   20 mins   5   2
7/10/2022 04:00:00   trial   20 mins   4   4
7/09/2022 02:45:00   trial   20 mins   2   2
6/28/2022 03:45:00   trial   20 mins   3   6

df2模拟记录（关键列） #

Timestamp             Name     Time     Sim
7/10/2022 05:15:00    trial   20 mins   7
7/11/2022 12:45:00    trial   20 mins   4
7/12/2022 03:30:00    trial   20 mins   8

期望结果 #

Timestamp             Name     Time     Sim  Sim Score
7/10/2022 05:15:00    trial   20 mins   7    True
7/11/2022 12:45:00    trial   20 mins   4    True
7/12/2022 03:30:00    trial   20 mins   8    False

解决方案 #

步骤1：统一时间格式 #

首先将两个DataFrame的Timestamp列转换为datetime类型，确保时间比较的准确性：

import pandas as pd

df1['Timestamp'] = pd.to_datetime(df1['Timestamp'])
df2['Timestamp'] = pd.to_datetime(df2['Timestamp'])

步骤2：提取每组最新基准记录 #

对df1按Name和Time分组，每组内按Timestamp降序排序，保留每组第一条（最新的基准记录）：

# 按分组键排序，确保每组最新记录排在最前
df1_latest = df1.sort_values(['Name', 'Time', 'Timestamp'], ascending=[True, True, False])
# 分组后取每组第一条
df1_latest = df1_latest.groupby(['Name', 'Time'], as_index=False).first()

步骤3：合并模拟记录与基准记录 #

通过Name和Time列合并df2和筛选后的基准记录，让每一行模拟记录对应到匹配的最新基准数据：

merged_df = pd.merge(df2, df1_latest, on=['Name', 'Time'], how='left', suffixes=('_sim', '_ref'))

步骤4：计算评分结果 #

基于合并后的数据计算范围，判断Sim值是否在范围内：

# 计算上下限范围
merged_df['low_range'] = merged_df['Po'] - merged_df['Ref']
merged_df['high_range'] = merged_df['Po'] + merged_df['Ref']

# 使用between方法快速判断是否在范围内
merged_df['Sim Score'] = merged_df['Sim'].between(merged_df['low_range'], merged_df['high_range'])

# 整理成期望的结果格式
result_df = merged_df[['Timestamp_sim', 'Name', 'Time', 'Sim', 'Sim Score']].rename(columns={'Timestamp_sim': 'Timestamp'})

完整可运行代码 #

import pandas as pd

# 构造示例数据
df1_data = {
    'Timestamp': ['7/11/2022 11:30:00', '7/10/2022 04:00:00', '7/09/2022 02:45:00', '6/28/2022 03:45:00'],
    'Name': ['trial', 'trial', 'trial', 'trial'],
    'Time': ['20 mins', '20 mins', '20 mins', '20 mins'],
    'Po': [5, 4, 2, 3],
    'Ref': [2, 4, 2, 6]
}

df2_data = {
    'Timestamp': ['7/10/2022 05:15:00', '7/11/2022 12:45:00', '7/12/2022 03:30:00'],
    'Name': ['trial', 'trial', 'trial'],
    'Time': ['20 mins', '20 mins', '20 mins'],
    'Sim': [7, 4, 8]
}

df1 = pd.DataFrame(df1_data)
df2 = pd.DataFrame(df2_data)

# 转换时间格式
df1['Timestamp'] = pd.to_datetime(df1['Timestamp'])
df2['Timestamp'] = pd.to_datetime(df2['Timestamp'])

# 获取最新基准记录
df1_latest = df1.sort_values(['Name', 'Time', 'Timestamp'], ascending=[True, True, False])
df1_latest = df1_latest.groupby(['Name', 'Time'], as_index=False).first()

# 合并数据并计算评分
merged_df = pd.merge(df2, df1_latest, on=['Name', 'Time'], how='left', suffixes=('_sim', '_ref'))
merged_df['low_range'] = merged_df['Po'] - merged_df['Ref']
merged_df['high_range'] = merged_df['Po'] + merged_df['Ref']
merged_df['Sim Score'] = merged_df['Sim'].between(merged_df['low_range'], merged_df['high_range'])

# 整理结果
result_df = merged_df[['Timestamp_sim', 'Name', 'Time', 'Sim', 'Sim Score']].rename(columns={'Timestamp_sim': 'Timestamp'})

print(result_df)

运行后输出结果与期望完全一致。

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