如何在多匹配场景下合并DataFrame且不产生重复数据

要实现仅当两个DataFrame中同一ID仅出现一次时才合并，多对多匹配的ID行各自独立保留并填充NA，可以按以下思路处理：

Python (Pandas) #

import pandas as pd
import numpy as np

# 构造示例数据
df1 = pd.DataFrame({
    'ID': ['A1', 'B1', 'C1', 'C1'],
    'Question 1': [1, 3, 5, 7],
    'Question 2': [2, 4, 6, 8]
})

df2 = pd.DataFrame({
    'ID': ['A1', 'B1', 'C1', 'C1'],
    'Question 3': ['a', 'c', 'e', 'g'],
    'Question 4': ['b', 'd', 'f', 'h']
})

# 1. 找出在两个DataFrame中都唯一的ID
df1_id_counts = df1['ID'].value_counts()
df2_id_counts = df2['ID'].value_counts()
# 筛选出在df1和df2中出现次数都为1的ID
unique_ids = df1_id_counts[(df1_id_counts == 1) & (df2_id_counts.reindex(df1_id_counts.index, fill_value=0) == 1)].index

# 2. 合并唯一ID的行
merged_unique = pd.merge(df1[df1['ID'].isin(unique_ids)], df2[df2['ID'].isin(unique_ids)], on='ID')

# 3. 处理df1中非唯一ID的行，填充df2列的NA
df1_non_unique = df1[~df1['ID'].isin(unique_ids)].assign(
    **{col: np.nan for col in df2.columns if col != 'ID'}
)

# 4. 处理df2中非唯一ID的行，填充df1列的NA
df2_non_unique = df2[~df2['ID'].isin(unique_ids)].assign(
    **{col: np.nan for col in df1.columns if col != 'ID'}
)

# 5. 合并所有结果
final_df = pd.concat([merged_unique, df1_non_unique, df2_non_unique], ignore_index=True)
print(final_df)

输出结果：

R (dplyr) #

library(dplyr)

# 构造示例数据
df1 <- data.frame(
    ID = c('A1', 'B1', 'C1', 'C1'),
    `Question 1` = c(1, 3, 5, 7),
    `Question 2` = c(2, 4, 6, 8)
)

df2 <- data.frame(
    ID = c('A1', 'B1', 'C1', 'C1'),
    `Question 3` = c('a', 'c', 'e', 'g'),
    `Question 4` = c('b', 'd', 'f', 'h')
)

# 1. 找出在两个DataFrame中都唯一的ID
unique_ids <- inner_join(count(df1, ID), count(df2, ID), by = "ID") %>%
    filter(n.x == 1 & n.y == 1) %>%
    pull(ID)

# 2. 合并唯一ID的行
merged_unique <- inner_join(
    filter(df1, ID %in% unique_ids),
    filter(df2, ID %in% unique_ids),
    by = "ID"
)

# 3. 处理df1中非唯一ID的行，填充df2列的NA
df1_non_unique <- df1 %>%
    filter(!ID %in% unique_ids) %>%
    mutate(across(all_of(setdiff(colnames(df2), "ID")), ~NA))

# 4. 处理df2中非唯一ID的行，填充df1列的NA
df2_non_unique <- df2 %>%
    filter(!ID %in% unique_ids) %>%
    mutate(across(all_of(setdiff(colnames(df1), "ID")), ~NA))

# 5. 合并所有结果
final_df <- bind_rows(merged_unique, df1_non_unique, df2_non_unique)
print(final_df)

核心逻辑说明 #

• 先筛选出在两个DataFrame中仅出现一次的ID，这类ID可以安全合并，不会产生重复行
• 对存在多匹配的ID，分别保留两个DataFrame中的原始行，填充对方DataFrame的列为NA
• 最后将三部分数据（合并后的唯一ID行、df1非唯一行、df2非唯一行）拼接得到最终结果

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