如何通过多列依次匹配实现两个DataFrame的full_join？

下面分R和Python两种常用语言给出解决方案，按col1→col2→col3→col4的优先级依次匹配，已匹配的记录不再参与后续匹配，最终实现包含所有可匹配记录及未匹配记录的全连接效果。

R语言实现 #

核心思路 #

先按优先级顺序依次匹配未关联的记录，标记已匹配项避免重复，最后合并匹配结果与两边未匹配的记录，模拟full_join的完整逻辑。

代码实现 #

library(dplyr)

stepwise_full_join <- function(df1, df2, match_cols = c("col1", "col2", "col3", "col4"), df1_key = "provincia") {
  # 初始化匹配标记，避免修改原数据
  df1_marked <- df1 %>% mutate(matched = FALSE)
  df2_marked <- df2 %>% mutate(matched = FALSE)
  result_parts <- list()
  
  # 按优先级遍历匹配列
  for (col in match_cols) {
    # 筛选两边未匹配的记录
    df1_unmatched <- df1_marked %>% filter(!matched)
    df2_unmatched <- df2_marked %>% filter(!matched)
    
    # 执行当前列的内连接
    current_match <- inner_join(df1_unmatched, df2_unmatched, by = setNames(col, df1_key))
    
    if (nrow(current_match) > 0) {
      # 更新匹配标记
      df1_marked$matched[df1_marked[[df1_key]] %in% current_match[[df1_key]]] <- TRUE
      df2_marked$matched[df2_marked[[col]] %in% current_match[[col]]] <- TRUE
      
      # 移除标记列，加入结果集合
      result_parts[[col]] <- current_match %>% select(-matches("matched"))
    }
  }
  
  # 合并所有匹配成功的记录
  matched_records <- bind_rows(result_parts)
  
  # 收集两边未匹配的记录
  unmatched_df1 <- df1_marked %>% filter(!matched) %>% select(-matched)
  unmatched_df2 <- df2_marked %>% filter(!matched) %>% select(-matched)
  
  # 构造全连接结果：匹配记录 + 两边未匹配记录（补充缺失列的NA值）
  final_result <- bind_rows(
    matched_records,
    unmatched_df1 %>% mutate(across(all_of(match_cols), ~NA)),
    unmatched_df2 %>% mutate(!!df1_key := NA, mean_price := NA)
  )
  
  return(final_result)
}

# 使用示例
# final_df <- stepwise_full_join(your_df1, your_df2)

关键说明 #

• 优先匹配高优先级列（col1→col2…），确保符合示例中"Alicante"匹配col3、"A Coruña"匹配col4的需求
• 已匹配的记录不会重复参与后续匹配，避免同一记录被多次关联
• 最终结果保留全连接的特性：包含所有匹配成功的记录，以及两边未匹配的独立记录

Python语言实现 #

核心思路 #

利用pandas的分步合并，维护已匹配的索引，按指定顺序依次关联未匹配的记录，最后拼接匹配结果与未匹配记录，实现全连接效果。

代码实现 #

import pandas as pd

def stepwise_full_join(df1, df2, match_cols=["col1", "col2", "col3", "col4"], df1_key="provincia"):
    # 复制数据，避免修改原DataFrame
    df1_marked = df1.copy().assign(matched=False)
    df2_marked = df2.copy().assign(matched=False)
    matched_dfs = []
    
    for col in match_cols:
        # 筛选未匹配的记录
        df1_unmatched = df1_marked[~df1_marked['matched']]
        df2_unmatched = df2_marked[~df2_marked['matched']]
        
        # 按当前列执行内连接
        current_match = pd.merge(df1_unmatched, df2_unmatched, left_on=df1_key, right_on=col, how='inner')
        
        if not current_match.empty:
            # 更新匹配标记
            df1_marked.loc[df1_marked[df1_key].isin(current_match[df1_key]), 'matched'] = True
            df2_marked.loc[df2_marked[col].isin(current_match[col]), 'matched'] = True
            
            # 移除匹配标记列，加入结果列表
            matched_dfs.append(current_match.drop(columns=['matched_x', 'matched_y']))
    
    # 合并所有匹配结果
    matched_records = pd.concat(matched_dfs, ignore_index=True) if matched_dfs else pd.DataFrame()
    
    # 收集未匹配的记录，并补充缺失列的NA值
    unmatched_df1 = df1_marked[~df1_marked['matched']].drop(columns='matched')
    unmatched_df1 = unmatched_df1.assign(**{col: pd.NA for col in match_cols})
    
    unmatched_df2 = df2_marked[~df2_marked['matched']].drop(columns='matched')
    unmatched_df2 = unmatched_df2.assign(**{df1_key: pd.NA, 'mean_price': pd.NA})
    
    # 拼接所有部分得到最终全连接结果
    final_result = pd.concat([matched_records, unmatched_df1, unmatched_df2], ignore_index=True)
    
    return final_result

# 使用示例
# final_df = stepwise_full_join(your_df1, your_df2)

关键说明 #

• 严格按照col1到col4的顺序匹配，优先关联高优先级的拼写变体
• 每个匹配步骤仅处理未关联的记录，避免重复匹配问题
• 最终结果包含所有可关联的记录，以及两边未匹配的独立记录，完全符合全连接的要求

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