基于Pandas实现两个DataFrame的证券价格匹配与筛选

需求概述 #

现有两个DataFrame：

• DataFrame 1：包含唯一证券ID及对应旧价格
• DataFrame 2：包含指定时间窗口（14:50-14:59）内的彭博证券新价格

需要为每个证券从DataFrame 2中筛选符合要求的新价格，规则如下：

• 仅保留与旧价格百分比差异在1%容差范围内的价格
• 在符合容差的价格中，选取百分比差异最小的记录
• 需处理两种异常情况：无符合容差的价格、无对应彭博价格数据

已准备的数据与代码 #

内部价格数据（df1） #

创建代码：

import pandas as pd
import numpy as np

# 创建内部价格表
data = [['1',99.434],['2',99.987],['3',98.117],['4',95.557]]
df = pd.DataFrame(data, columns = ['Security_ID', 'OldPrice'])

输出结果：

Security_ID    OldPrice
0          1      99.434
1          2      99.987
2          3      98.117
3          4      95.557

彭博价格数据（df2） #

创建代码：

data = [['1','10/10/2022 14:50',98.50],
        ['1','10/10/2022 14:51',98.30],
        ['1','10/10/2022 14:52',98.00],
        ['1','10/10/2022 14:53',101.34],
        ['1','10/10/2022 14:54',98.30],
        ['1','10/10/2022 14:55',98.60],
        ['1','10/10/2022 14:56',99.24],
        ['1','10/10/2022 14:57',99.99],
        ['1','10/10/2022 14:58',98.40],
        ['1','10/10/2022 14:59',101.12],
        ['2','10/10/2022 14:50',101.12],
        ['2','10/10/2022 14:51',101.12],
        ['2','10/10/2022 14:52',98.9],
        ['2','10/10/2022 14:53',98.9],
        ['2','10/10/2022 14:54',98.9],
        ['2','10/10/2022 14:55',98.9],
        ['2','10/10/2022 14:56',98.9],
        ['2','10/10/2022 14:57',101.12],
        ['2','10/10/2022 14:58',101.12],
        ['2','10/10/2022 14:59',101.12],
        ['3','10/10/2022 14:50',99.6],
        ['3','10/10/2022 14:51',99.6],
        ['3','10/10/2022 14:52',99.6],
        ['3','10/10/2022 14:53',99.6],
        ['3','10/10/2022 14:54',100],
        ['3','10/10/2022 14:55',98.8],
        ['3','10/10/2022 14:56',99.6],
        ['3','10/10/2022 14:57',99.6],
        ['3','10/10/2022 14:58',99.6],
        ['3','10/10/2022 14:59',99.6]]
df2 = pd.DataFrame(data, columns = ['BB_Security_ID', 'BB_Time', 'BB_NewPrice'])

输出前5条结果：

BB_Security_ID          BB_Time    BB_NewPrice
0             1 10/10/2022 14:50          98.50
1             1 10/10/2022 14:51          98.30
2             1 10/10/2022 14:52          98.00
3             1 10/10/2022 14:53         101.34
4             1 10/10/2022 14:54          98.30

已完成的数据处理步骤 #

# 转换时间格式
df2['BB_Time'] = pd.to_datetime(df2['BB_Time'], errors='coerce')
df2['BB_Time'] = pd.to_datetime(df2['BB_Time'].dt.strftime('%d/%m/%Y %H:%M:%S'))
df2['BB_Security_ID']=df2['BB_Security_ID'].astype(str)

# 按证券ID合并两个DataFrame
df = pd.merge(df, df2, left_on = ['Security_ID'],right_on = ['BB_Security_ID'], how = 'left')

# 定义百分比差异计算函数
def percentage_dff(col1,col2):
    """计算两列的绝对百分比差异"""
    return abs(((col1 - col2)/ col2) * 100)

# 计算价格差异
df['PriceDff'] = percentage_dff(df['OldPrice'],df['BB_NewPrice'])

合并后前5条结果：

Security_ID OldPrice    BB_Security_ID              BB_Time BB_NewPrice PriceDff
0         1 99.434                   1  2022-10-10 14:50:00       98.50 0.948223
1         1 99.434                   1  2022-10-10 14:51:00       98.30 1.153611
2         1 99.434                   1  2022-10-10 14:52:00       98.00 1.463265
3         1 99.434                   1  2022-10-10 14:53:00      101.34 1.880797
4         1 99.434                   1  2022-10-10 14:54:00       98.30 1.153611

期望输出 #

Security_ID    OldPrice                BB_Time BB_NewPrice PriceDff    WithinTolerence
0          1      99.434    2022-10-10 14:57:00       99.24 0.195486                  Y
1          2      99.987                    NaN         NaN      NaN                  N
2          3      98.117    2022-10-10 14:55:00       98.80 0.691296                  Y
3          4      95.557                    NaN         NaN      NaN       NoPriceFound

结果说明 #

• 证券ID1：选取14:57的99.24，其差异最小且在1%容差内，标记为Y
• 证券ID2：所有价格均超出容差，标记为N
• 证券ID3：仅14:55的98.80符合容差，标记为Y
• 证券ID4：无彭博数据，标记为NoPriceFound

解决方案代码 #

# 设置容差阈值
TOLERANCE = 1.0

# 标记每条记录是否在容差范围内，同时处理无彭博数据的情况
df['WithinTolerence'] = np.where(df['PriceDff'] <= TOLERANCE, 'Y', 'N')
df['WithinTolerence'] = np.where(df['BB_Security_ID'].isna(), 'NoPriceFound', df['WithinTolerence'])

# 定义分组筛选函数，为每个证券选取最优价格
def select_best_price(group):
    # 筛选当前证券下符合容差的记录
    valid_records = group[group['WithinTolerence'] == 'Y']
    
    if not valid_records.empty:
        # 按价格差异升序排序，取差异最小的第一条记录
        return valid_records.sort_values('PriceDff', ascending=True).head(1)
    else:
        # 检查是否有彭博数据（排除NoPriceFound的情况）
        if group['WithinTolerence'].iloc[0] != 'NoPriceFound':
            # 返回空值记录，标记为N
            return pd.DataFrame({
                'Security_ID': [group['Security_ID'].iloc[0]],
                'OldPrice': [group['OldPrice'].iloc[0]],
                'BB_Time': [np.nan],
                'BB_NewPrice': [np.nan],
                'PriceDff': [np.nan],
                'WithinTolerence': ['N']
            })
        else:
            # 返回无数据标记的记录
            return pd.DataFrame({
                'Security_ID': [group['Security_ID'].iloc[0]],
                'OldPrice': [group['OldPrice'].iloc[0]],
                'BB_Time': [np.nan],
                'BB_NewPrice': [np.nan],
                'PriceDff': [np.nan],
                'WithinTolerence': ['NoPriceFound']
            })

# 应用分组函数并整理结果
result = df.groupby('Security_ID').apply(select_best_price).reset_index(drop=True)
# 调整列顺序匹配期望输出
result = result[['Security_ID', 'OldPrice', 'BB_Time', 'BB_NewPrice', 'PriceDff', 'WithinTolerence']]

# 打印最终结果
print(result)

代码逻辑说明 #

1. 标记容差状态：先为每条记录标记是否在1%容差内，同时将无彭博数据的记录直接标记为NoPriceFound
2. 分组筛选最优记录：按证券ID分组后，对每个分组：
   • 若存在符合容差的记录，按差异从小到大排序，取第一条（差异最小的）
   • 若无符合容差但有彭博数据，返回一条空值记录并标记为N
   • 若无彭博数据，返回标记为NoPriceFound的空值记录
3. 整理结果格式：调整列顺序，确保与期望输出一致

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