如何基于多列Y/N标记对tibble中数值按月份求和并写入目标tibble

需求说明 #

已有两个tibble：

• 初始的months tibble，包含1-12月的空count列
• relevant_months tibble，每行有一个count值，以及1-12月的"y"/"n"标记，需要将每个月份标记为"y"的count值求和，填入months对应行的count列

已创建的tibble代码 #

# 初始months tibble
months <- tibble(month = 1:12, count = NA) 

# 包含标记的relevant_months tibble
relevant_months <- tibble(count = c(40 ,125, 77, 81, 321), 
                          "1" = c("n", "n", "n", "y", "n"),
                          "2" = c("y", "y", "n", "y", "n"),
                          "3" = c("n", "n", "n", "n", "n"),
                          "4" = c("y", "n", "n", "n", "y"),
                          "5" = c("y", "n", "n", "n", "n"),
                          "6" = c("n", "n", "n", "n", "n"),
                          "7" = c("n", "y", "y", "n", "y"),
                          "8" = c("n", "y", "y", "y", "n"),
                          "9" = c("n", "y", "n", "n", "y"),
                          "10" = c("n", "n", "n", "y", "n"),
                          "11" = c("n", "n", "n", "n", "n"),
                          "12" = c("y", "y", "n", "n", "n"))

解决方案 #

使用tidyverse工具包（dplyr+tidyr）处理，步骤如下：

1. 将relevant_months从宽格式转为长格式，把月份列转换为行数据
2. 筛选出标记为"y"的记录，按月份分组计算count总和
3. 将计算结果与months tibble匹配，更新count列

完整代码 #

library(tidyverse)

# 计算各月份求和结果
month_sums <- relevant_months %>%
  pivot_longer(cols = -count, names_to = "month", values_to = "flag") %>%
  filter(flag == "y") %>%
  group_by(month) %>%
  summarise(total = sum(count)) %>%
  mutate(month = as.integer(month))

# 更新months的count列
months <- months %>%
  left_join(month_sums, by = "month") %>%
  mutate(count = replace_na(total, 0)) %>%
  select(-total)

# 查看最终结果
print(months)

结果验证 #

运行后months的count列会更新为对应月份的求和值，例如：

• 一月count为81
• 二月count为246（40+125+81）
• 三月count为0（无标记"y"的记录）

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