如何在Python中复刻VLOOKUP填补Airbnb数据集的缺失字段值

核心逻辑（对应Excel VLOOKUP） #

以AirBnB_ID为关联键，仅用2018.8-2020.6段同ID的有效历史属性值，填补2020.10-2021.1段的指定缺失字段（bathrooms/bedroom/guests_included/bed_type），不覆盖已有正确值。

分步实现（基于Pandas） #

前置准备 #

确保你的汇总DataFrame（命名为df_all）已完成以下预处理：

• 时间列（假设命名为date）已转为datetime格式：df_all["date"] = pd.to_datetime(df_all["date"])
• 关联键AirBnB_ID无格式异常（如首尾空格、大小写不一致，可清洗为：df_all["AirBnB_ID"] = df_all["AirBnB_ID"].str.strip()）

代码实现 #

# 定义需补全的目标字段列表
target_cols = ["bathrooms", "bedroom", "guests_included", "bed_type"]

# 1. 提取有效历史基准集（2018.8-2020.6，且目标字段全非空）
mask_history_valid = (
    (df_all["date"] >= "2018-08") & 
    (df_all["date"] <= "2020-06") & 
    df_all[target_cols].notna().all(axis=1)
)
df_history_valid = df_all.loc[mask_history_valid]

# 2. 整理历史查找表：按AirBnB_ID去重，保留**最新时间的有效记录**
# （解决Excel VLOOKUP默认取首个匹配项的局限性，用最新历史值更贴合房源实际属性）
df_history_lookup = (
    df_history_valid
    .sort_values(by=["AirBnB_ID", "date"], ascending=[True, False])
    .drop_duplicates(subset="AirBnB_ID", keep="first")
    [["AirBnB_ID"] + target_cols]  # 仅保留关联键和目标字段，减少匹配体积
)

# 3. 左连接匹配历史值（仅针对2020.10-2021.1的待补全段）
# 给历史匹配字段加后缀，避免覆盖原字段
df_merged = df_all.merge(
    df_history_lookup, 
    on="AirBnB_ID", 
    how="left", 
    suffixes=("", "_hist")
)

# 4. 核心补全：仅用历史值填补原字段的缺失项
for col in target_cols:
    df_merged[col] = df_merged[col].fillna(df_merged[f"{col}_hist"])
    # 补全后删除临时的历史匹配列
    df_merged.drop(columns=f"{col}_hist", inplace=True)

# 得到补全后的最终数据集
df_all_filled = df_merged

比Excel VLOOKUP更优的优化点 #

• 避免过时值匹配：Excel VLOOKUP默认取首个匹配项，本方案优先取同ID最新的历史有效记录，更符合房源属性（如房源调整过卧室数，用最近的历史值而非最早的）
• 精准补全范围：仅针对2020.10-2021.1的缺失字段补全，不覆盖原数据的正确值
• 可追溯性：可额外新增标记列（如is_filled_from_history），标记哪些行是通过历史数据补全的，方便后续校验

注意事项 #

• 2020.7-9为源数据本身缺失的时间段，无需处理，可在后续分析时直接过滤
• 若某AirBnB_ID在历史基准集中无有效记录，对应缺失字段会保留为空，可根据分析需求选择是否标记为unmatched

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