如何基于N/A值执行关联查询且保留非空字段？

用R的dplyr实现 #

首先模拟示例数据：

library(tibble)

# 主表
main_df <- tibble(
  Name = c("George", "Mary", "Sarah"),
  Brand = c("Samsung", NA, "Nokia"),
  Country = c(NA, "USA", "USA"),
  Color = c("Blue", NA, "Green")
)

# 品牌表
brand_df <- tibble(
  Name = c("Mary"),
  Brand = c("Nokia")
)

# 颜色表（示例）
color_df <- tibble(
  Name = c("Mary"),
  Color = c("Yellow")
)

# 国家表（示例）
country_df <- tibble(
  Name = c("George"),
  Country = c("Canada")
)

通过left_join关联所有表后，用coalesce()函数取第一个非空值实现填充：

library(dplyr)

filled_df <- main_df %>%
  # 关联各维度表，添加后缀区分原字段和关联表字段
  left_join(brand_df, by = "Name", suffix = c("", "_brand")) %>%
  left_join(color_df, by = "Name", suffix = c("", "_color")) %>%
  left_join(country_df, by = "Name", suffix = c("", "_country")) %>%
  # 对每个字段，优先保留主表非空值，缺失时用关联表值填充
  mutate(
    Brand = coalesce(Brand, Brand_brand),
    Color = coalesce(Color, Color_color),
    Country = coalesce(Country, Country_country)
  ) %>%
  # 清理临时生成的关联表字段
  select(-ends_with("_brand"), -ends_with("_color"), -ends_with("_country"))

print(filled_df)

coalesce()会依次返回参数中第一个非NA的值，完美满足"不覆盖已有非空值"的要求。

用Python的pandas实现 #

先模拟示例数据：

import pandas as pd

# 主表
main_df = pd.DataFrame({
    "Name": ["George", "Mary", "Sarah"],
    "Brand": ["Samsung", pd.NA, "Nokia"],
    "Country": [pd.NA, "USA", "USA"],
    "Color": ["Blue", pd.NA, "Green"]
})

# 品牌表
brand_df = pd.DataFrame({
    "Name": ["Mary"],
    "Brand": ["Nokia"]
})

# 颜色表（示例）
color_df = pd.DataFrame({
    "Name": ["Mary"],
    "Color": ["Yellow"]
})

# 国家表（示例）
country_df = pd.DataFrame({
    "Name": ["George"],
    "Country": ["Canada"]
})

通过merge关联表后，用combine_first()填充缺失值：

# 关联所有维度表
merged_df = main_df.merge(brand_df, on="Name", how="left", suffixes=("", "_brand"))
merged_df = merged_df.merge(color_df, on="Name", how="left", suffixes=("", "_color"))
merged_df = merged_df.merge(country_df, on="Name", how="left", suffixes=("", "_country"))

# 填充缺失值：主表非空值保留，缺失时用关联表值补充
merged_df["Brand"] = merged_df["Brand"].combine_first(merged_df["Brand_brand"])
merged_df["Color"] = merged_df["Color"].combine_first(merged_df["Color_color"])
merged_df["Country"] = merged_df["Country"].combine_first(merged_df["Country_country"])

# 清理临时字段
filled_df = merged_df.drop(columns=[col for col in merged_df.columns if any(s in col for s in ["_brand", "_color", "_country"])])

print(filled_df)

combine_first()仅用右侧数据填充左侧的缺失值，不会覆盖左侧已有的非空内容，完全匹配需求。

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