如何在R语言中基于条件合并数据框？附示例数据

嘿，我来帮你搞定R里的条件合并问题！首先先把你给出的df1代码还原出来，方便我们后续操作演示：

df1 <- structure(
  list(
    name = c("Katie", "Eve", "James", "Alexander", "Mary", "Barrie", "Harry", "Sam"),
    postcode = c("CB12FR", "CB12FR", "NE34TR", "DH34RL", "PE46YH", "IL57DS", "IP43WR", "IL45TR")
  ),
  .Names = c("name", "postcode"),
  class = c("tbl_df", "tbl", "data.frame"),
  row.names = c(NA, -8L),
  spec = structure(
    list(
      cols = structure(
        list(
          name = structure(list(), class = c("collector_character", "collector")),
          postcode = structure(list(), class = c("collector_character", "collector"))
        ),
        .Names = c("name", "postcode")
      ),
      default = structure(list(), class = c("collector_guess", "collector"))
    ),
    class = "col_spec"
  )
)

为了让演示更直观，我先假设你还有一个df2（毕竟要合并两个数据框嘛），比如包含邮编对应的区域信息，结构大概是这样：

df2 <- tibble(
  postcode = c("CB12FR", "NE34TR", "DH34RL", "PE46YH", "IL57DS"),
  region = c("Cambridgeshire", "Tyne and Wear", "County Durham", "Peterborough", "Illinois")
)

下面分几种常见的条件合并场景来讲解：

如果你的合并条件是两个数据框的某一列（比如postcode）完全匹配，这是最常见的场景，推荐用dplyr包的连接函数，语法更清晰；当然基础R的merge()也能实现。

用dplyr包操作 #

首先确保你安装并加载了dplyr：

install.packages("dplyr")
library(dplyr)

左连接（保留df1所有行，匹配df2对应数据） #

这种方式会保留df1的每一行，只要在df2里找到匹配的postcode，就会把对应的region列加进来；没匹配到的行（比如Harry的IP43WR、Sam的IL45TR）会显示NA：

merged_df_left <- df1 %>% 
  left_join(df2, by = "postcode")

内连接（只保留两个数据框都匹配到的行） #

如果你只想保留同时在两个数据框里有匹配postcode的行，用内连接就可以，这时候Harry和Sam的行就会被过滤掉：

merged_df_inner <- df1 %>% 
  inner_join(df2, by = "postcode")

用基础R的merge()函数 #

要是你不想额外加载包，基础R的merge()函数也能实现同样的效果：

# 左连接，对应dplyr的left_join
merged_df_left_base <- merge(df1, df2, by = "postcode", all.x = TRUE)
# 内连接，对应dplyr的inner_join
merged_df_inner_base <- merge(df1, df2, by = "postcode")

如果你的合并条件不是简单的列值相等，比如邮编前缀匹配、或者数值范围匹配，这时候可以用fuzzyjoin包来实现灵活的条件匹配。

先安装并加载fuzzyjoin：

install.packages("fuzzyjoin")
library(fuzzyjoin)

示例：邮编前缀匹配 #

假设我们要按邮编的前两位来匹配，先调整df2为前缀格式：

df2_prefix <- tibble(
  postcode_prefix = c("CB", "NE", "DH", "PE", "IL"),
  region = c("Cambridgeshire", "Tyne and Wear", "County Durham", "Peterborough", "Illinois")
)

然后用regex_left_join()实现前缀匹配：

merged_df_fuzzy <- df1 %>% 
  regex_left_join(df2_prefix, by = c("postcode" = "postcode_prefix"))

这样所有邮编以CB、NE等开头的行都会匹配到对应的区域信息。

示例：数值范围匹配 #

如果是数值型的条件（比如假设邮编转成数值后匹配范围），可以用fuzzy_left_join()自定义匹配逻辑：

# 先把df1的邮编转成前两位的数值
df1_num <- df1 %>% mutate(postcode_num = as.numeric(substr(postcode, 1, 2)))
# 构造包含数值范围的df2
df2_range <- tibble(
  range_low = c(10, 30, 34, 40, 50),
  range_high = c(20, 35, 38, 48, 60),
  region = c("Cambridgeshire", "Tyne and Wear", "County Durham", "Peterborough", "Illinois")
)

# 自定义匹配条件：postcode_num在range_low和range_high之间
merged_df_range <- fuzzy_left_join(
  df1_num, df2_range,
  by = c("postcode_num" = "range_low", "postcode_num" = "range_high"),
  match_fun = list(`>=`, `<=`)
)

如果你的条件是多列同时匹配（比如name和postcode都要完全一致），只需要在by参数里指定多个列即可：

# 构造包含name、postcode和额外信息的df2
df2_multi <- tibble(
  name = c("Katie", "James", "Mary"),
  postcode = c("CB12FR", "NE34TR", "PE46YH"),
  age = c(28, 32, 45)
)

# 基于name和postcode两列合并
merged_df_multi <- df1 %>% 
  left_join(df2_multi, by = c("name", "postcode"))

这样只有name和postcode都完全匹配的行才会带上对应的age信息。

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