R语言：ifelse嵌套str_split_i实现双条件匹配的问题

需求说明 #

• 用较短的映射文件（含学生全名、考试文件名）替换长文件中的学生姓名
• 匹配条件：同时满足学生名前缀一致、考试文件名一致
• 长文件仅包含学生名前缀和考试文件名，需从映射文件中提取对应全名

数据定义 #

映射文件 student_file_mapping #

student_file_mapping <- data.frame(
  student=c("paul_johns","mary_bash","paul_simons"),
  file=c("johns bash hunter 2022_exam1.paul_johns.csv",
         "johns bash hunter 2022_exam1.mary_bash.csv",
         "nichols simons smith 2022_exam1.paul_simonsh.csv")
)

输出结果：

student                                             file
1  paul_johns      johns bash hunter 2022_exam1.paul_johns.csv
2   mary_bash       johns bash hunter 2022_exam1.mary_bash.csv
3 paul_simons nichols simons smith 2022_exam1.paul_simonsh.csv

长文件 data_full #

data_full <- data.frame(
  student_file=c("paul_johns bash hunter 2022_exam1",
                 "paul_johns bash hunter 2022_exam1",
                 "paul_johns bash hunter 2022_exam1",
                 "mary_johns bash hunter 2022_exam1",
                 "mary_johns bash hunter 2022_exam1",
                 "mary_johns bash hunter 2022_exam1",
                 "paul_nichols simons smith 2022_exam1",
                 "paul_nichols simons smith 2022_exam1",
                 "paul_nichols simons smith 2022_exam1",
                 "paul_johns bash hunter 2022_exam1",
                 "mary_johns bash hunter 2022_exam1",
                 "paul_nichols simons smith 2022_exam1"),
  File=c("johns bash hunter 2022_exam1.csv", 
         "johns bash hunter 2022_exam1.csv",
         "johns bash hunter 2022_exam1.csv",
         "johns bash hunter 2022_exam1.csv",
         "johns bash hunter 2022_exam1.csv",
         "johns bash hunter 2022_exam1.csv",
         "nichols simons smith 2022_exam1.csv",
         "nichols simons smith 2022_exam1.csv",
         "nichols simons smith 2022_exam1.csv",
         "jons bash hunter 2022_exam1.csv",
         "johns bash hunter 2022_exam1.csv",
         "nichols simons smith 2022_exam1.csv")
)

输出结果：

student_file                                File
1     paul_johns bash hunter 2022_exam1    johns bash hunter 2022_exam1.csv
2     paul_johns bash hunter 2022_exam1    johns bash hunter 2022_exam1.csv
3     paul_johns bash hunter 2022_exam1    johns bash hunter 2022_exam1.csv
4     mary_johns bash hunter 2022_exam1    johns bash hunter 2022_exam1.csv
5     mary_johns bash hunter 2022_exam1    johns bash hunter 2022_exam1.csv
6     mary_johns bash hunter 2022_exam1    johns bash hunter 2022_exam1.csv
7  paul_nichols simons smith 2022_exam1 nichols simons smith 2022_exam1.csv
8  paul_nichols simons smith 2022_exam1 nichols simons smith 2022_exam1.csv
9  paul_nichols simons smith 2022_exam1 nichols simons smith 2022_exam1.csv
10    paul_johns bash hunter 2022_exam1     jons bash hunter 2022_exam1.csv
11    mary_johns bash hunter 2022_exam1    johns bash hunter 2022_exam1.csv
12 paul_nichols simons smith 2022_exam1 nichols simons smith 2022_exam1.csv

尝试的代码及问题 #

提取匹配字段的代码 #

提取学生名前缀：

# 从映射文件提取前缀
str_split_i(student_file_mapping$student,"_",1)[1] -> paul
# 从长文件提取前缀
str_split_i(data_full$student_file,"_",1)[1] -> paul

提取考试文件名：

# 从映射文件提取考试文件名
str_split_i(student_file_mapping$file,"\\.",1)[1] -> "johns bash hunter 2022_exam1"
# 从长文件提取考试文件名
str_split_i(data_full$File,".csv",1)[1] -> "johns bash hunter 2022_exam1"

错误的匹配逻辑（ifelse） #

尝试用ifelse实现匹配，但仅能为长文件中每个学生匹配一行，实际数据集还会报错longer object length is not a multiple of shorter object length：

data_full$student <- ifelse( 
  str_split_i(student_file_mapping$file,"\\.",1) == str_split_i(data_full$File,".csv",1) & 
  str_split_i(student_file_mapping$student,"_",1) == str_split_i(data_full$student_file,"_",1) , 
  paste0(student_file_mapping$student),
  "NOT FOUND" 
)

执行后结果：

student_file                                File     student
1     paul_johns bash hunter 2022_exam1    johns bash hunter 2022_exam1.csv  paul_johns
2     paul_johns bash hunter 2022_exam1    johns bash hunter 2022_exam1.csv   NOT FOUND
3     paul_johns bash hunter 2022_exam1    johns bash hunter 2022_exam1.csv   NOT FOUND
4     mary_johns bash hunter 2022_exam1    johns bash hunter 2022_exam1.csv   NOT FOUND
5     mary_johns bash hunter 2022_exam1    johns bash hunter 2022_exam1.csv   mary_bash
6     mary_johns bash hunter 2022_exam1    johns bash hunter 2022_exam1.csv   NOT FOUND
7  paul_nichols simons smith 2022_exam1 nichols simons smith 2022_exam1.csv   NOT FOUND
8  paul_nichols simons smith 2022_exam1 nichols simons smith 2022_exam1.csv   NOT FOUND
9  paul_nichols simons smith 2022_exam1 nichols simons smith 2022_exam1.csv paul_simons
10    paul_johns bash hunter 2022_exam1     jons bash hunter 2022_exam1.csv   NOT FOUND
11    mary_johns bash hunter 2022_exam1    johns bash hunter 2022_exam1.csv   mary_bash
12 paul_nichols simons smith 2022_exam1 nichols simons smith 2022_exam1.csv paul_simons

正确解决方案 #

问题出在ifelse是逐元素循环，当两个对象长度不匹配时会触发循环补齐，导致匹配错误。正确做法是先为两个数据集生成匹配键，再用连接操作完成匹配：

步骤1：加载必要包 #

library(dplyr)
library(stringr)

步骤2：处理映射文件，生成匹配键 #

mapping_processed <- student_file_mapping %>%
  mutate(
    # 提取学生名前缀
    student_prefix = str_split_i(student, "_", 1),
    # 提取考试文件名（去掉后缀部分）
    exam_file = str_split_i(file, "\\.", 1)
  )

步骤3：处理长文件，生成匹配键 #

data_full_processed <- data_full %>%
  mutate(
    # 提取学生名前缀
    student_prefix = str_split_i(student_file, "_", 1),
    # 提取考试文件名（去掉.csv后缀）
    exam_file = str_remove(File, "\\.csv$")
  )

步骤4：通过匹配键连接两个数据集 #

result <- data_full_processed %>%
  left_join(mapping_processed, by = c("student_prefix", "exam_file")) %>%
  # 将未匹配到的项替换为"NOT FOUND"
  mutate(student = ifelse(is.na(student), "NOT FOUND", student)) %>%
  # 保留原长文件的列和匹配后的student列
  select(student_file, File, student)

最终结果 #

student_file                                File     student
1     paul_johns bash hunter 2022_exam1    johns bash hunter 2022_exam1.csv  paul_johns
2     paul_johns bash hunter 2022_exam1    johns bash hunter 2022_exam1.csv  paul_johns
3     paul_johns bash hunter 2022_exam1    johns bash hunter 2022_exam1.csv  paul_johns
4     mary_johns bash hunter 2022_exam1    johns bash hunter 2022_exam1.csv   mary_bash
5     mary_johns bash hunter 2022_exam1    johns bash hunter 2022_exam1.csv   mary_bash
6     mary_johns bash hunter 2022_exam1    johns bash hunter 2022_exam1.csv   mary_bash
7  paul_nichols simons smith 2022_exam1 nichols simons smith 2022_exam1.csv paul_simons
8  paul_nichols simons smith 2022_exam1 nichols simons smith 2022_exam1.csv paul_simons
9  paul_nichols simons smith 2022_exam1 nichols simons smith 2022_exam1.csv paul_simons
10    paul_johns bash hunter 2022_exam1     jons bash hunter 2022_exam1.csv   NOT FOUND
11    mary_johns bash hunter 2022_exam1    johns bash hunter 2022_exam1.csv   mary_bash
12 paul_nichols simons smith 2022_exam1 nichols simons smith 2022_exam1.csv paul_simons

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