如何基于两个DataFrame实现多Vlookup并合并生成目标DataFrame

问题背景 #

需要从df1和df2生成df3，再合并df1、df2、df3得到df4，要求df4的“Beg Date”列和索引日期对齐。尝试循环匹配df2中带top/bottom的列提取价格时，因为NaT值导致合并乱套。

原始数据 #

import pandas as pd
import numpy as np

df1 = {'Date': ['2023-01-01', '2023-01-02','2023-01-03','2023-01-04','2023-01-05','2023-01-06','2023-01-07','2023-01-08','2023-01-09',],
       'High': [10,20,30,40,50,60,70,80,90],
       'Low': [1,2,3,4,5,6,7,8,9]}
df2 = {'Beg Date': ['2023-01-01', '2023-01-02','2023-01-03','2023-01-04','2023-01-05'],
       'RT_1 top date':['2023-01-01', '2023-01-02','2023-01-03','2023-01-04',np.nan],
       'RT_2 top date':['2023-01-02', np.nan, '2023-01-05', np.nan, np.nan],
       'RT_3 top date':['2023-01-03', np.nan, np.nan, '2023-01-05', np.nan],
       'RT_4 top date':['2023-01-04', np.nan, np.nan, '2023-01-02', np.nan],
       'RT_5 top date':['2023-01-05', np.nan, np.nan, np.nan, np.nan],
       'random': [3,4,9,10,10],
       'RT_1 bottom date':['2023-01-01', '2023-01-02','2023-01-03','2023-01-04','2023-01-05'],
       'RT_2 bottom date':['2023-01-02', np.nan, '2023-01-05', np.nan, np.nan],
       'RT_3 bottom date':['2023-01-03', np.nan, np.nan, '2023-01-05', np.nan],
       'RT_4 bottom date':['2023-01-04', np.nan, np.nan, '2023-01-02', np.nan],
       'RT_5 bottom date':['2023-01-05', np.nan, np.nan, np.nan, np.nan],}

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

原代码问题 #

你之前写的循环合并有两个明显问题：

• 合并方向错了：应该用df2里的日期去匹配df1的价格，你反过来用df1左连df2，直接导致行索引错位
• 价格对应关系错了：没区分top对应High价、bottom对应Low价，而且原数据里根本没有price列，肯定出问题

解决方案 #

1. 先统一日期格式 #

把所有日期列转成datetime类型，避免字符串匹配的坑：

# 转换df1的日期列
df1['Date'] = pd.to_datetime(df1['Date'])
# 转换df2的Beg Date列
df2['Beg Date'] = pd.to_datetime(df2['Beg Date'])
# 转换df2中所有top/bottom的日期列
for col in df2.filter(regex='top|bottom').columns:
    df2[col] = pd.to_datetime(df2[col])

2. 生成df3：用映射代替合并 #

直接构建日期到价格的字典映射，避免NaT导致的合并异常：

df3 = pd.DataFrame()

# 先把df1的High和Low转成字典：日期→价格
high_dict = df1.set_index('Date')['High'].to_dict()
low_dict = df1.set_index('Date')['Low'].to_dict()

# 遍历df2中所有top/bottom列
for col in df2.filter(regex='top|bottom').columns:
    # 判断是top还是bottom，选对应的价格字典
    if 'top' in col:
        price_dict = high_dict
    else:
        price_dict = low_dict
    # 生成新列名：比如"RT_1 top date"→"RT_1_top_price"
    new_col = col.replace(' ', '_').replace('date', 'price')
    # 用map提取价格，NaT自动转成NaN
    df3[new_col] = df2[col].map(price_dict)

3. 合并生成df4 #

先把df2和df3横向合并（行数一致），再和df1按日期对齐合并，确保索引和Beg Date匹配：

# 合并df2和df3
df2_3 = pd.concat([df2, df3], axis=1)
# 按Date和Beg Date合并df1和df2_3，保留df1的所有日期
df4 = pd.merge(df1, df2_3, left_on='Date', right_on='Beg Date', how='left')
# 设置Date为索引
df4 = df4.set_index('Date')

这样生成的df4，索引是df1的所有日期，Beg Date只在匹配的日期有值，其余为NaN，所有价格列也完全对应df2的行数据，不会再出现NaT导致的合并问题。

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