R语言获取最新更新值场景下代码行拆分与匹配问题咨询

问题描述 #

本文跟进Stack Overflow主题《Get the latest updated value in R》，运行@TarJae提供的解决方案时遇到新问题，特咨询可行方案。
首先构建的示例数据如下：

df <- data.frame(name1 = c("Acacia pinnata", "Acer laurinum", "Acmella paniculata", "Aglaia lawii", NA, NA),
                 name2 = c(NA, NA, NA, NA, "Acer laurinum Hassk.", "Aglaia lawii (Wight)"),
                 name3 = c("Senegalia rugata (Lam.) Britton & Rose", "Acer laurinum", "Acmella paniculata", "Aglaia lawii", "Acer laurinum Hassk.", "Aglaia lawii (Wight)"))

打印效果：

name1                name2                                  name3
1     Acacia pinnata                 <NA> Senegalia rugata (Lam.) Britton & Rose
2      Acer laurinum                 <NA>                          Acer laurinum
3 Acmella paniculata                 <NA>                     Acmella paniculata
4       Aglaia lawii                 <NA>                           Aglaia lawii
5               <NA> Acer laurinum Hassk.                   Acer laurinum Hassk.
6               <NA> Aglaia lawii (Wight)                   Aglaia lawii (Wight)

使用原解决方案代码运行：

df %>% 
  mutate(id = row_number()) %>% 
  pivot_longer(
    cols = -id
  ) %>% 
  mutate(helper= word(value, 1)) %>% 
  group_by(helper) %>% 
  mutate(value= last(value)) %>% 
  pivot_wider(
    names_from = name,
    values_from = value
  ) %>% 
  ungroup() %>% 
  select(-id, -helper) %>% 
  filter(if_any(everything(), ~ !is.na(.)))

输出结果中原有首行被拆分为两行，无法手动批量处理：

name1                name2                name3                                 
  <chr>                <chr>                <chr>                                 
1 Acacia pinnata       NA                   NA                                    
2 NA                   NA                   Senegalia rugata (Lam.) Britton & Rose
3 Acer laurinum Hassk. NA                   Acer laurinum Hassk.                  
4 Acmella paniculata   NA                   Acmella paniculata                    
5 Aglaia lawii (Wight) NA                   Aglaia lawii (Wight)                  
6 NA                   Acer laurinum Hassk. Acer laurinum Hassk.                  
7 NA                   Aglaia lawii (Wight) Aglaia lawii (Wight)       

后续构建新示例数据时又出现匹配错误：

df <- data.frame(name1 = c("Acacia pinnata", "Acer laurinum", "Acmella paniculata", "Aglaia lawii", NA, NA, "Alangium javanicum", "Alangium longiflorum", NA),
                 name2 = c(NA, NA, NA, NA, "Acer laurinum Hassk.", "Aglaia lawii (Wight)", NA,NA, "Alangium javanicum (Blume) Wangerin"),
                 name3 = c("Senegalia rugata (Lam.) Britton & Rose", "Acer laurinum", "Acmella paniculata", "Aglaia lawii", "Acer laurinum Hassk.", "Aglaia lawii (Wight)",
                           "Alangium javanicum", "Celtis cf. rigescens (Miq.) Planch.", "Alangium javanicum (Blume) Wangerin"))

运行同一段代码后，Celtis cf. rigescens (Miq.) Planch.被错误匹配到Alangium javanicum (Blume) Wangerin，而非对应的Alangium longiflorum。
期望输出效果如下：

name1                               name2                              name3                                                                                                                        
1 Acacia pinnata                      NA                                  Senegalia rugata (Lam.) Britton & ~
2 Acer laurinum Hassk.                NA                                  Acer laurinum Hassk.               
3 Acmella paniculata                  NA                                  Acmella paniculata                 
4 Aglaia lawii (Wight)                NA                                  Aglaia lawii (Wight)               
5 NA                                  Acer laurinum Hassk.                Acer laurinum Hassk.               
6 NA                                  Aglaia lawii (Wight)                Aglaia lawii (Wight)               
7 Alangium javanicum (Blume) Wangerin NA                                  Alangium javanicum (Blume) Wangerin
8 Alangium longiflorum                NA                                  Celtis cf. rigescens (Miq.) Planch.
9 NA                                  Alangium javanicum (Blume) Wangerin Alangium javanicum (Blume) Wangerin

解决方案 #

两个问题的根源都来自原方案的逻辑缺陷：一是仅按物种第一个单词分组，会把首单词相同但实际不属于同一物种的名称错误匹配；二是分组转换后没有按原行号聚合，导致同一行多个不同物种的条目被拆分。
可使用如下通用方案解决，核心逻辑是先生成「短物种名-最新完整物种名」的映射表，再替换原数据对应值，完全保留原行结构：

library(tidyverse)
library(stringr)

# 1. 提取所有非空物种名，生成映射关系
name_map <- df %>% 
  pivot_longer(everything(), values_drop_na = TRUE) %>% 
  pull(value) %>% 
  unique() %>% 
  # 以名称前两个单词为匹配依据，避免首单词相同的物种错配
  enframe(name = NULL, value = "raw_name") %>% 
  mutate(match_key = word(raw_name, 1, 2)) %>% 
  group_by(match_key) %>% 
  # 取字符长度最长的作为该物种的标准完整名
  mutate(std_name = last(raw_name[order(nchar(raw_name))])) %>% 
  ungroup() %>% 
  select(raw_name, std_name) %>% 
  deframe()

# 2. 替换原数据中所有物种名为标准完整名
result <- df %>%
  mutate(across(everything(), ~ifelse(is.na(.), NA, name_map[.])))

运行上述代码即可得到符合预期的输出，适配所有同场景需求，无需手动干预。

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