如何用R的dplyr按日期分组并对指定Code列求和？

我有一个内容分析的大型数据集，包含不同日期的文档，还有大量以Code开头的列——文档包含特定表述时列值为"1"，否则为"0"。我想按Date列分组，对每组的各Code列求和，但运行代码时报错。

数据集示例 #

Document.name       Date Code 1 Code 2 Code 3 Code 4 Code 5 Code 6
1 10_03_29_Five states have responsibilities to administer Arctic 29/03/2010      0      0      0      0      0      0
2 10_03_29_Five states have responsibilities to administer Arctic 29/03/2010      0      0      0      0      0      0
3 10_05_27_Climate change seriously affects Arctic diversity repo 27/05/2010      0      0      0      0      0      0
4 10_05_27_Climate change seriously affects Arctic diversity repo 27/05/2010      0      0      0      0      0      0
5 10_05_27_Climate change seriously affects Arctic diversity repo 27/05/2010      0      0      0      0      0      0
6 10_05_27_Climate change seriously affects Arctic diversity repo 27/05/2010      0      0      1      0      0      0

尝试的代码 #

test <- cn_leg %>% 
  group_by(Date) %>% 
  summarise(across(contains("Code"), sum),.groups = 'drop')  %>%
  as.data.frame()

报错信息 #

Error in `summarise()`:
! Problem while computing `..1 = across(contains("Code"), sum)`.
ℹ The error occurred in group 1: Date = "01/02/2021".
Caused by error in `across()`:
! Problem while computing column `Code`.
Caused by error:
! invalid 'type' (character) of argument

可复现代码 #

structure(list(Document.name = c("10_03_29_Five states have responsibilities to administer Arctic", 
"10_03_29_Five states have responsibilities to administer Arctic", 
"10_05_27_Climate change seriously affects Arctic diversity repo"
), Date = c("29/03/2010", "29/03/2010", "27/05/2010"), Code..3a..Input.fairness = c(0L, 
0L, 0L), Code..3a..Input.expert.leadership = c(0L, 0L, 0L), Code..3a..Input.participation = c(0L, 
0L, 0L), Code..3a..Input.leadership.characteristics = c(0L, 0L, 
0L), Code..3b.Output.Effectiveness..general. = c(0L, 0L, 0L), 
    Code..3b.Output.Contribution.to.environment = c(0L, 0L, 0L
    ), Code..Contribution.to.environment.Contribution.to.environmental.issues = c(0L, 
    0L, 0L), Code..Contribution.to.environment.Contribution.to.climate.change = c(0L, 
    0L, 1L)), row.names = c(NA, 3L), class = "data.frame")

问题原因 #

报错核心是部分Code列是字符类型，sum()函数只能对数值型数据计算，无法处理字符值（比如数据中可能存在用字符串"1"/"0"存储的情况，或者混入了其他非数值字符）。

解决方案 #

先将所有Code列转换为数值类型，再执行分组求和：

方案1：直接转换为数值型 #

library(dplyr)

test <- cn_leg %>%
  # 将所有含"Code"的列转为数值型，自动处理字符型的"0"/"1"
  mutate(across(contains("Code"), as.numeric)) %>%
  group_by(Date) %>%
  summarise(across(contains("Code"), sum),.groups = 'drop') %>%
  as.data.frame()

方案2：更稳妥的转换（处理异常值） #

如果数据中存在非"0"/"1"的字符（比如空值、其他文本），用readr::parse_number会更稳妥，它会自动提取数值部分，非数值内容转为NA：

library(dplyr)
library(readr)

test <- cn_leg %>%
  mutate(across(contains("Code"), parse_number)) %>%
  group_by(Date) %>%
  summarise(across(contains("Code"), sum, na.rm = TRUE),.groups = 'drop') %>%
  as.data.frame()

测试结果（用可复现代码） #

运行上述代码后，输出结果如下：

Date Code..3a..Input.fairness Code..3a..Input.expert.leadership Code..3a..Input.participation Code..3a..Input.leadership.characteristics Code..3b.Output.Effectiveness..general. Code..3b.Output.Contribution.to.environment Code..Contribution.to.environment.Contribution.to.environmental.issues Code..Contribution.to.environment.Contribution.to.climate.change
1 27/05/2010                        0                                  0                              0                                          0                                          0                                                  0                                                                 0                                                                   1
2 29/03/2010                        0                                  0                              0                                          0                                          0                                                  0                                                                 0                                                                   0

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