如何快速基于唯一标识填充Pandas DataFrame缺失薪资数据？

问题背景 #

我有一个存储员工信息的df_cleaned DataFrame，其中HOURLY_BASE_RATE字段存在缺失（值≤0或为NaN），关联的CURRENCY、PAY_COMPONENT、FTE字段也有缺失，需要用df_integration_wages DataFrame填充这些缺失值。

当前用循环迭代实现，但数据量过大导致速度极慢。尝试过merge方法，但会生成额外列而非填充现有列；试过combine_first，仍有NaN值残留。需求是仅基于ASSOCIATE_ID和COUNTRY的唯一组合填充原DataFrame的缺失行，且不引入集成表中的额外行。

原实现代码 #

df_missing = df_cleaned.loc[(df_cleaned["HOURLY_BASE_RATE"]<= 0) | (df_cleaned["HOURLY_BASE_RATE"].isna())]
df_missing_in_integration = df_missing[["ASSOCIATE_ID", "COUNTRY"]].merge(df_integration_wages, on=["ASSOCIATE_ID", "COUNTRY"])

for index, row in df_missing_in_integration.iterrows():
  associate_id = row["ASSOCIATE_ID"]
  associate_country = row["COUNTRY"]
  associate_index = df_cleaned.index[(df_cleaned["ASSOCIATE_ID"] == associate_id) & (df_cleaned["COUNTRY"] == associate_country)]
  df_cleaned.loc[associate_index, "HOURLY_BASE_RATE"] = row["HOURLY_BASE_RATE"]
  df_cleaned.loc[associate_index, "CURRENCY"] = row["CURRENCY"]
  df_cleaned.loc[associate_index, "PAY_COMPONENT"] = row["PAY_COMPONENT"]
  df_cleaned.loc[associate_index, "FTE"] = row["FTE"]

示例DataFrames #

import pandas as pd
import numpy as np

df_cleaned = pd.DataFrame({"ASSOCIATE_ID": [1, 2, 3, 4, 5, 6, 7, 8, 9, 10], "COUNTRY": ["USA", "USA", "BEL", "GER", "BEL", "USA", "GER", "GER", "NLD", "NLD"], "HOURLY_BASE_RATE": [15, np.nan, 20, 18, np.nan, np.nan, 43, 38, np.nan, 13], "CURRENCY": ["USD", "USD", "EUR", "EUR", "EUR", "USD", "EUR", "EUR", "EUR", "EUR"], "PAY_COMPONENT": ["Hourly", np.nan, "Hourly", "Hourly", np.nan, np.nan, "Hourly", "Hourly", np.nan, "Hourly"], "FTE": [1, 1, 0.8, 1, np.nan, np.nan, 0.75, 0.75, np.nan, 1], "LOCATION_TYPE": ["Stores", "Stores", "Distribution Center", "Stores", "Headquarters", "Headquarters", "Headquarters", "Distribution Center", "Stores", "Stores"]})
df_integration_wages = pd.DataFrame({"ASSOCIATE_ID": [2, 5, 6, 9, 11, 12], "COUNTRY": ["USA", "USA", "USA", "NLD", "BEL", "BEL"], "HOURLY_BASE_RATE": [2500, 23, 37, 20, 32, 16], "CURRENCY": ["USD", "USD", "USD", "EUR", "EUR", "EUR"], "PAY_COMPONENT": ["Monthly", "Hourly", "Hourly", "Hourly", "Hourly", "Hourly"], "FTE": [1, 0.6, 1, 1, 0.8, 1]})

高效实现方案 #

利用pandas的向量化操作替代循环，大幅提升处理速度，同时精准满足填充需求：

实现代码 #

# 1. 筛选需要填充的行的掩码
mask = (df_cleaned["HOURLY_BASE_RATE"] <= 0) | df_cleaned["HOURLY_BASE_RATE"].isna()

# 2. 从集成表中匹配可用于填充的数据，仅保留需要更新的字段
fill_data = df_cleaned[mask][["ASSOCIATE_ID", "COUNTRY"]].merge(
    df_integration_wages[["ASSOCIATE_ID", "COUNTRY", "HOURLY_BASE_RATE", "CURRENCY", "PAY_COMPONENT", "FTE"]],
    on=["ASSOCIATE_ID", "COUNTRY"],
    how="inner"
)

# 3. 设置复合索引，方便批量匹配更新
df_cleaned.set_index(["ASSOCIATE_ID", "COUNTRY"], inplace=True)
fill_data.set_index(["ASSOCIATE_ID", "COUNTRY"], inplace=True)

# 4. 批量更新原DataFrame的缺失字段，仅覆盖对应索引的行
df_cleaned.update(fill_data)

# 恢复原索引结构
df_cleaned.reset_index(inplace=True)

方案优势 #

• 性能高效：完全使用pandas向量化操作，避免iterrows()循环的低效问题，处理大数据量时速度提升显著。
• 精准控制：仅更新原DataFrame中需要填充的行，不会引入df_integration_wages中的额外记录（比如示例中的ID11、12不会被加入）。
• 代码简洁：逻辑清晰，无需逐行处理，维护成本低。

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