如何统计R数据框中指定列值匹配列表的出现次数

我有一个数据框df1，其中包含约100个以i10_pr开头的列。需要统计每行中，这些列的取值在另一个数据框df2的CODE列中的匹配次数，并新增名为sum的列来展示该频次。

期望输出 #

#Desired output
  visitlink visitorder i10_pr1   i10_pr2   i10_pr3   i10_pr4   i10_pr5   sum
1   7466851          3 "BW28ZZZ" "BR30Y0Z" "BR39Y0Z" ""        ""        0
2   7023336          1 "0BDC8ZX" "0BDC8ZX" "07D78ZX" ""        ""        0
3   2481935          3 "5A09357" "3C1ZX8Z" "06HN33Z" "B54CZZA" "0W993ZX" 1
4   4605446          1 "5A1955Z" "0BH17EZ" "03HY32Z" "02HV33Z" "GZ58ZZZ" 3
5   7287173          2 ""        ""        ""        ""        ""        0

#df2
     CODE
1 GZ58ZZZ
2 3C1ZX8Z
3 0BH17EZ
4 HZ89ZZZ
5 02HV33Z
6 HZ99ZZZ

示例数据 #

df1 <- structure(list(visitlink = c(7466851, 7023336, 2481935, 4605446, 
7287173), visitorder = c(3L, 1L, 3L, 1L, 2L), i10_pr1 = c("BW28ZZZ", 
"0BDC8ZX", "5A09357", "5A1955Z", ""), i10_pr2 = c("BR30Y0Z", 
"0BDC8ZX", "3C1ZX8Z", "0BH17EZ", ""), i10_pr3 = c("BR39Y0Z", 
"07D78ZX", "06HN33Z", "03HY32Z", ""), i10_pr4 = c("", "", "B54CZZA", 
"02HV33Z", ""), i10_pr5 = c("", "", "0W993ZX", "GZ58ZZZ", "")), class = "data.frame", row.names = c(NA, 
-5L), groups = structure(list(visitlink = c(2481935, 4605446, 
7023336, 7287173, 7466851), .rows = structure(list(3L, 4L, 2L, 
    5L, 1L), ptype = integer(0), class = c("vctrs_list_of", "vctrs_vctr", 
"list"))), class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA, 
-5L), .drop = TRUE))

df2 <- structure(list(CODE = structure(1:7, levels = c("GZ58ZZZ", "3C1ZX8Z", 
"0BH17EZ", "HZ89ZZZ", "02HV33Z", "HZ99ZZZ", "XW03351"), class = "factor")), class = "data.frame", row.names = c(NA, 
-7L))

解决方案 #

方法1：基础R实现 #

先提取所有以i10_pr开头的列，再逐行统计匹配次数：

# 获取目标列名
pr_cols <- grep("^i10_pr", names(df1), value = TRUE)
# 新增sum列，统计每行匹配次数
df1$sum <- apply(df1[pr_cols], 1, function(x) sum(x %in% as.character(df2$CODE)))

方法2：tidyverse（dplyr）实现 #

利用rowwise()和c_across()处理行级统计：

library(dplyr)

df1 <- df1 %>%
  rowwise() %>%
  mutate(sum = sum(c_across(starts_with("i10_pr")) %in% as.character(df2$CODE))) %>%
  ungroup()

注意：因为df2$CODE是因子类型，所以用as.character()转换为字符型，避免匹配时因类型不一致出错。

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