如何基于三个及以上DataFrame实现复杂过滤与列填充？

问题背景 #

需要基于多个DataFrame的复杂条件为main_df填充新列，具体场景如下：

• 场景1：当main_df.date == second_df.date且main_df.code == second_df.code时，添加new_value列，值为second_df.values
• 场景2：当main_df.code == second_df.code且main_df.start_date ≤ second_df.date ≤ main_df.end_date时，添加color列，多匹配时取第一个值
• 场景3：当main_df.date == second_df.date == third_df.date、main_df.code == second_df.code且third_df.shapes == 'circle'时，添加mixed_quantity列，值为third_df.quantity + second_df.values（允许空值）
• 场景4：结合main_df.date == second_df.date的条件，按main_df.furniture类型计算furniture_value（如chest对应/10，bed对应/15等）

遇到的问题：

1. 用iterrows实现场景4时，10000行数据集耗时超30分钟，性能极差
2. 手动过滤条件时频繁出现ValueError: Can only compare identically-labeled Series objects索引错误

可复现数据集（基于PyJanitor） #

import pandas as pd
import janitor
import numpy as np
from datetime import datetime

# 生成main_df
main_data = {
    "Date": pd.date_range(start="2023-01-01", periods=10000, freq="D"),
    "Code": np.random.choice(["A", "B", "C", "D"], 10000),
    "start_date": pd.date_range(start="2022-12-01", periods=10000, freq="D"),
    "end_date": pd.date_range(start="2023-02-01", periods=10000, freq="D"),
    "furniture": np.random.choice(["chest", "bed", "sofa", "table"], 10000)
}
main_df = pd.DataFrame(main_data).clean_names()

# 生成second_df
second_data = {
    "Date": pd.date_range(start="2023-01-01", periods=5000, freq="D"),
    "Code": np.random.choice(["A", "B", "C", "D"], 5000),
    "values": np.random.randint(10, 100, 5000),
    "colors": np.random.choice(["red", "blue", "green", "yellow"], 5000)
}
second_df = pd.DataFrame(second_data).clean_names()

# 生成third_df
third_data = {
    "Date": pd.date_range(start="2023-01-01", periods=3000, freq="D"),
    "Shapes": np.random.choice(["circle", "square", "triangle"], 3000),
    "quantity": np.random.randint(1, 20, 3000)
}
third_df = pd.DataFrame(third_data).clean_names()

分场景高效解决方案 #

场景1：精确匹配Date+Code填充new_value #

直接用merge做左连接，避免循环：

main_df = main_df.merge(
    second_df[["date", "code", "values"]],
    on=["date", "code"],
    how="left"
).rename(columns={"values": "new_value"})

场景2：Code匹配+日期区间匹配，取首个color #

先合并同Code的行，过滤日期条件后按原索引分组取第一个值：

# 交叉合并同Code的行
merged_temp = main_df.merge(second_df, on="code", how="left")
# 过滤日期区间条件
filtered_temp = merged_temp[merged_temp["date_y"].between(merged_temp["start_date"], merged_temp["end_date"])]
# 按main_df原索引分组，取首个匹配的color
color_mapping = filtered_temp.groupby(filtered_temp.index)["colors"].first()
# 填充回main_df
main_df["color"] = color_mapping

场景3：三表匹配+形状条件填充mixed_quantity #

分步合并过滤，避免复杂嵌套逻辑：

# 第一步：匹配main与second的Date+Code
main_second = main_df.merge(
    second_df[["date", "code", "values"]],
    on=["date", "code"],
    how="left"
)
# 第二步：匹配third的Date，且Shapes为circle
main_second_third = main_second.merge(
    third_df[third_df["shapes"] == "circle"][["date", "quantity"]],
    on="date",
    how="left"
)
# 计算混合值，空值自动保留
main_df["mixed_quantity"] = main_second_third["values"] + main_second_third["quantity"]

场景4：按家具类型高效计算furniture_value（解决性能问题） #

用np.select替代iterrows/apply，性能提升100倍以上：

# 先将second_df的values按Date映射到main_df
main_df = main_df.merge(
    second_df[["date", "values"]],
    on="date",
    how="left"
)

# 定义条件与对应计算规则
conditions = [
    main_df["furniture"] == "chest",
    main_df["furniture"] == "bed",
    main_df["furniture"] == "sofa",
    main_df["furniture"] == "table"
]
choices = [
    main_df["values"] / 10,
    main_df["values"] / 15,
    main_df["values"] * 2,
    main_df["values"] * 1.5
]

# 批量计算，空值默认设为NaN
main_df["furniture_value"] = np.select(conditions, choices, default=np.nan)

索引错误（ValueError）解决方法 #

出现Can only compare identically-labeled Series objects的核心原因是两个Series索引不一致，解决方式：

1. 比较前重置索引，确保对齐：

   df1 = df1.reset_index(drop=True)
   df2 = df2.reset_index(drop=True)
   # 或直接用values避开索引比较
   df1.values <= df2.values
   

2. 所有匹配操作优先用merge的on参数指定匹配列，避免索引参与匹配
3. 不要直接跨DataFrame做Series比较（如main_df["date"] == second_df["date"]），这种操作会强制按索引对齐，应该用merge后在同表内比较列

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