R语言如何根据name1、name2匹配规则更新数据框的name3列

原始数据结构 #

你提供的初始数据框代码如下：

df1 <- data.frame(number = c(1,2,3,4,5,6,7,8),
                  name1 = c("Acer laurinum", NA, "Acmella paniculata", "Acronychia cf. pedunculata", "Acronychia pedunculata", NA, "Adinandra cf. integerrima",NA),
                  name2 = c(NA, "Acer laurinum Hassk.", NA, NA, NA, "Acronychia pedunculata (L.) Miq.", NA, "Adinandra cf. integerrima T.Anderson"),
                  name3 = c("Acer laurinum", "Acer laurinum Hassk.", "Acmella paniculata", "Acronychia cf. pedunculata", "Acronychia pedunculata",
                            "Acronychia pedunculata (L.) Miq.", "Adinandra cf. integerrima", "Adinandra cf. integerrima T.Anderson"))

预览初始数据：

number                      name1                                name2                                name3
1      1              Acer laurinum                                 <NA>                        Acer laurinum
2      2                       <NA>                 Acer laurinum Hassk.                 Acer laurinum Hassk.
3      3         Acmella paniculata                                 <NA>                   Acmella paniculata
4      4 Acronychia cf. pedunculata                                 <NA>           Acronychia cf. pedunculata
5      5     Acronychia pedunculata                                 <NA>               Acronychia pedunculata
6      6                       <NA>     Acronychia pedunculata (L.) Miq.     Acronychia pedunculata (L.) Miq.
7      7  Adinandra cf. integerrima                                 <NA>            Adinandra cf. integerrima
8      8                       <NA> Adinandra cf. integerrima T.Anderson Adinandra cf. integerrima T.Anderson

需求说明 #

需要按照规则更新name3列：如果name3的内容和name1的短物种名匹配，且该物种存在带作者后缀的完整名称记录在name2中，则将name3中的短名称替换为带作者的完整名称，最终得到如下输出：

number                      name1                                name2                                name3
1      1              Acer laurinum                                 <NA>                 Acer laurinum Hassk.
2      2                       <NA>                 Acer laurinum Hassk.                 Acer laurinum Hassk.
3      3         Acmella paniculata                                 <NA>                   Acmella paniculata
4      4 Acronychia cf. pedunculata                                 <NA>           Acronychia cf. pedunculata
5      5     Acronychia pedunculata                                 <NA>     Acronychia pedunculata (L.) Miq.
6      6                       <NA>     Acronychia pedunculata (L.) Miq.     Acronychia pedunculata (L.) Miq.
7      7  Adinandra cf. integerrima                                 <NA>     Adinandra cf. integerrima T.Anderson
8      8                       <NA> Adinandra cf. integerrima T.Anderson Adinandra cf. integerrima T.Anderson

实现方案 #

思路 #

1. 提取所有非NA的短物种名（来自name1）和带作者的完整物种名（来自name2）
2. 由于完整名是短名加作者后缀，通过前缀匹配建立短名到对应完整名的映射关系
3. 用映射表匹配name3的值，匹配成功则替换为完整名，否则保留原值

代码实现（tidyverse方案） #

library(dplyr)
library(stringr)

# 提取短名、完整名列表，构建映射表
short_names <- na.omit(unique(df1$name1))
full_names <- na.omit(unique(df1$name2))

name_map <- lapply(short_names, function(s) {
  matched_full <- full_names[str_starts(full_names, fixed(s))]
  if(length(matched_full) > 0) return(data.frame(short_name = s, full_name = matched_full[1]))
  else return(NULL)
}) %>% bind_rows()

# 替换name3列
df_result <- df1 %>%
  left_join(name_map, by = c("name3" = "short_name")) %>%
  mutate(name3 = coalesce(full_name, name3)) %>%
  select(-full_name)

代码实现（基础R方案，无需额外安装包） #

# 提取短名、完整名列表，构建映射向量
short_names <- unique(na.omit(df1$name1))
full_names <- unique(na.omit(df1$name2))

name_map <- c()
for (s in short_names) {
  matched_full <- full_names[startsWith(full_names, s)]
  if (length(matched_full) > 0) {
    name_map[s] <- matched_full[1]
  }
}

# 替换name3列
df1$name3 <- ifelse(df1$name3 %in% names(name_map), 
                    name_map[df1$name3], 
                    df1$name3)

运行上述任意一种方案的代码，都可以得到你需要的结果。

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