如何在R中基于映射数据框修正作物名称拼写错误？

需求说明 #

我需要处理包含拼写错误作物名称的数据框original_df，借助存储错误-正确拼写映射的数据集，为每个作物名称列生成对应的修正列，最终输出包含原列和修正列的数据框。之前尝试用str_match实现时因长度不匹配失败，寻求可行方案。

示例数据 #

原数据框 #

crops_A <- c(NA, "wheat", "wheet", "rice", "olifs")
crops_B <- c("olives", "maize", NA, "maisse", NA)
crops_C <- c("corn", "barly", "wheat", "rice", NA)
other_columns <- c(1, 2, 3, 4, 5)
original_df <- data.frame(crops_A, crops_B, crops_C, other_columns)

映射数据框 #

original_unique <- c("wheat", "wheet", "rice", "olifs", "olives", "maize", "maisse", "corn", "barly")
corrected <- c("wheat", "wheat", "rice", "olives", "olives", "maize", "maize", "maize", "barley")
mapping_df <- data.frame(original_unique, corrected)

映射关系表格：

期望输出 #

输出数据框代码 #

crops_A <- c(NA, "wheat", "wheet", "rice", "olifs")
crops_B <- c("olives", "corn", NA, "maisse", NA)
crops_C <- c("corn", "barly", "wheat", "rice", NA)
crops_A_corr <- c(NA, "wheat","wheat", "rice", "olives")
crops_B_corr <- c("olives", "maize", NA, "maize", NA)
crops_C_corr <- c("maize", "barley", "wheat", "rice", NA)
output_df <- data.frame(crops_A, crops_A_corr, crops_B, crops_B_corr, crops_C, crops_C_corr)

输出表格 #

解决方案 #

方法1：使用dplyr结合recode（推荐） #

先将映射数据框转为命名向量，再用mutate为每个作物列生成修正列：

library(dplyr)

# 将映射转为命名向量
correction_vec <- setNames(mapping_df$corrected, mapping_df$original_unique)

# 生成修正列并调整列顺序
output_df <- original_df %>%
  mutate(
    crops_A_corr = recode(crops_A, !!!correction_vec),
    crops_B_corr = recode(crops_B, !!!correction_vec),
    crops_C_corr = recode(crops_C, !!!correction_vec)
  ) %>%
  select(crops_A, crops_A_corr, crops_B, crops_B_corr, crops_C, crops_C_corr)

方法2：基R实现（无需额外包） #

通过match函数匹配映射关系：

# 生成修正列
original_df$crops_A_corr <- mapping_df$corrected[match(original_df$crops_A, mapping_df$original_unique)]
original_df$crops_B_corr <- mapping_df$corrected[match(original_df$crops_B, mapping_df$original_unique)]
original_df$crops_C_corr <- mapping_df$corrected[match(original_df$crops_C, mapping_df$original_unique)]

# 调整列顺序得到输出
output_df <- original_df[, c("crops_A", "crops_A_corr", "crops_B", "crops_B_corr", "crops_C", "crops_C_corr")]

方法3：批量处理多列（适合列数较多的情况） #

如果有大量作物列，用循环批量生成修正列：

# 定义需要处理的作物列名
crop_cols <- c("crops_A", "crops_B", "crops_C")

# 批量生成修正列
for(col in crop_cols) {
  corr_col <- paste0(col, "_corr")
  original_df[[corr_col]] <- mapping_df$corrected[match(original_df[[col]], mapping_df$original_unique)]
}

# 调整列顺序
output_cols <- unlist(lapply(crop_cols, function(x) c(x, paste0(x, "_corr"))))
output_df <- original_df[, output_cols]

以上三种方法均能正确处理NA值，匹配对应正确拼写，解决str_match长度不匹配的问题。

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