基于正则表达式匹配合并R语言两个DataFrame的技术问询

我有两个R语言DataFrame：df和df2。df包含regular_ex列，存储着用于匹配df2中col列的正则表达式。需要通过df的regular_ex列，将df2的变量与df关联匹配。

以下是我的尝试代码：

df <- data.frame(var = c("D52", "D31", "D32", "D33", "D34_1"),
                 regular_ex = c("^D52_","^D31_\\d_1$", "^D32_", "^D33_", "^D34_\\d_1$"),
                 dt = c("STA", "MP", "ST", "LI", "DA"))

df2 <- data.frame(col = c("D52_1", "D3_1_1", "D32_1", "D33_1", "D34_1"),
                  city = c("Munich","Ceylon", "Dhaka", "London", "NY"),
                  rev = c(1653,1432,7642,9864,6522))

df$col <- NA
df$city <- NA
df$rev <- NA

for (i in 1:nrow(df)) {
  regular_exp <- df$regular_ex[i]
  matching_variables <- grep(regular_exp, df2$col, value = TRUE)
  if (length(matching_variables) > 0) {
    matched_index <- match(matching_variables[1], df2$col)
    df$col[i] <- df$col[matched_index]
    df$city[i] <- df$city[matched_index]
  }
}

原代码问题分析 #

1. 赋值错误：代码中df$col[i] <- df$col[matched_index]和df$city[i] <- df$city[matched_index]错误引用了df自身的列，应该从df2中取值。
2. 遗漏列赋值：未将df2的rev列对应值写入df。
3. 正则匹配不匹配：df中D34_1对应的正则^D34_\\d_1$要求匹配D34_数字_1格式，但df2中的D34_1不符合该格式，导致无法匹配，可根据实际需求调整正则（比如改为^D34_\\d?_1$）。

修正后的循环代码 #

df <- data.frame(var = c("D52", "D31", "D32", "D33", "D34_1"),
                 regular_ex = c("^D52_","^D31_\\d_1$", "^D32_", "^D33_", "^D34_\\d?_1$"), # 调整正则适配df2的D34_1
                 dt = c("STA", "MP", "ST", "LI", "DA"))

df2 <- data.frame(col = c("D52_1", "D3_1_1", "D32_1", "D33_1", "D34_1"),
                  city = c("Munich","Ceylon", "Dhaka", "London", "NY"),
                  rev = c(1653,1432,7642,9864,6522))

df$col <- NA
df$city <- NA
df$rev <- NA

for (i in 1:nrow(df)) {
  regular_exp <- df$regular_ex[i]
  matching_variables <- grep(regular_exp, df2$col, value = TRUE)
  if (length(matching_variables) > 0) {
    matched_index <- match(matching_variables[1], df2$col)
    df$col[i] <- df2$col[matched_index]
    df$city[i] <- df2$city[matched_index]
    df$rev[i] <- df2$rev[matched_index]
  }
}

print(df)

更高效的tidyverse实现 #

如果习惯使用tidyverse工具，可以用purrr实现更简洁的匹配：

library(tidyverse)

df <- tibble(var = c("D52", "D31", "D32", "D33", "D34_1"),
             regular_ex = c("^D52_","^D31_\\d_1$", "^D32_", "^D33_", "^D34_\\d?_1$"),
             dt = c("STA", "MP", "ST", "LI", "DA"))

df2 <- tibble(col = c("D52_1", "D3_1_1", "D32_1", "D33_1", "D34_1"),
              city = c("Munich","Ceylon", "Dhaka", "London", "NY"),
              rev = c(1653,1432,7642,9864,6522))

# 定义匹配函数，返回第一个匹配的行
match_regex_row <- function(regex) {
  matched <- df2 %>% filter(str_detect(col, regex)) %>% slice(1)
  if(nrow(matched) == 0) {
    return(tibble(col = NA, city = NA, rev = NA))
  }
  return(matched)
}

# 应用匹配并合并结果
result_df <- df %>%
  bind_cols(map_dfr(.$regular_ex, match_regex_row))

print(result_df)

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