按日期、子类别计算类别出现次数累计和及可视化实现

原始数据 #

df <- structure(list(Date_time = structure(c(1641025800, 1641025800, 
1641025800, 1641025800, 1641025800, 1641025800, 1641025800, 1641025800, 
1641027600, 1641027600, 1641027600, 1641027600, 1641027600, 1641027600, 
1641027600, 1641027600, 1641027600, 1641027600, 1641027600, 1641027600, 
1641027600, 1651396800, 1651396800, 1651396800, 1651396800, 1651396800, 
1651396800, 1651396800, 1651396800, 1651396800, 1651401000, 1651401000, 
1651401000, 1651401000, 1651401000, 1669966200, 1669966200, 1669966200, 
1669966200, 1669966200, 1669966200, 1669966200, 1669966200, 1669969800, 
1669969800, 1669969800, 1669969800, 1669969800, 1669969800, 1669969800, 
1669969800, 1669969800, 1669969800, 1669969800, 1669969800), class = c("POSIXct", 
"POSIXt"), tzone = "Europe/London"), Category = c("heat", "heat", 
"heat", "heat", "heat", "heat", "heat", "heat", "cold", "cold", 
"cold", "cold", "cold", "cold", "cold", "medium", "medium", "medium", 
"medium", "medium", "medium", "heat", "heat", "heat", "heat", 
"cold", "cold", "cold", "cold", "cold", "cold", "cold", "medium", 
"medium", "medium", "heat", "heat", "heat", "heat", "heat", "cold", 
"cold", "cold", "cold", "cold", "cold", "cold", "medium", "medium", 
"medium", "medium", "medium", "medium", "heat", "heat"), SubCat = c("r", 
"r", "r", "r", "n", "n", "n", "r", "r", "r", "r", "n", "n", "n", 
"n", "r", "r", "r", "n", "n", "n", "n", "n", "n", "r", "r", "r", 
"r", "n", "n", "n", "n", "r", "r", "r", "r", "r", "r", "n", "n", 
"n", "n", "n", "n", "r", "r", "r", "r", "n", "n", "r", "r", "r", 
"n", "n"), Site = c("1a", "1a", "1a", "1a", "1a", "1a", "1a", 
"1a", "1a", "1a", "1b", "1b", "1b", "1b", "1b", "1b", "1b", "1b", 
"1b", "1b", "1b", "2c", "2c", "2c", "2c", "2c", "2c", "2c", "2c", 
"2c", "2c", "2c", "2c", "2c", "2c", "7c", "7c", "7c", "7c", "7c", 
"7c", "7c", "7c", "7c", "7c", "7c", "7c", "7c", "7c", "7c", "7c", 
"7c", "7c", "7c", "7c")), row.names = c(NA, -55L), class = "data.frame")

需求 #

按日期、SubCat（可选包含Site）统计各Category的出现次数：先按日统计每组的当日总数，再将该数值逐日累加，最终得到可用于绘图的结构化数据（示例如下）：

Date Category Subcategory Count
1  01/01/2022     Heat           r     5
2  01/01/2022     Cold           r     6
3  01/01/2022   Medium           r     9
4  01/01/2022     Heat           n     3
5  01/01/2022     Cold           n     6
6  01/01/2022   Medium           n    10
7  05/01/2022     Heat           r     3
8  05/01/2022     Cold           r     6
9  05/01/2022   Medium           r     9
10 05/01/2022     Heat           n     4
11 05/01/2022     Cold           n     8
12 05/01/2022   Medium           n    12
13 12/01/2022     Heat           r     3
14 12/01/2022     Cold           r     6
15 12/01/2022   Medium           r    10
16 12/01/2022     Heat           n     3
17 12/01/2022     Cold           n     3
18 12/01/2022   Medium           n     5

解决方案 #

使用dplyr和lubridate包实现，步骤如下：

1. 加载依赖包 #

library(dplyr)
library(lubridate)
library(stringr)

2. 基础分组累计求和（不含Site） #

result <- df %>%
  # 提取日期并格式化，统一Category首字母大写，重命名列名匹配示例
  mutate(Date = format(date(Date_time), "%d/%m/%Y"),
         Category = str_to_title(Category),
         Subcategory = SubCat) %>%
  # 按日、分类、子分类统计当日数量
  group_by(Date, Category, Subcategory) %>%
  summarise(Daily_Count = n(), .groups = "drop") %>%
  # 按日期排序，保证累加顺序正确
  arrange(dmy(Date)) %>%
  # 按分类、子分类分组，计算累计求和
  group_by(Category, Subcategory) %>%
  mutate(Count = cumsum(Daily_Count)) %>%
  # 保留目标列并按日期排序输出
  select(Date, Category, Subcategory, Count) %>%
  arrange(dmy(Date))

3. 扩展分组累计求和（包含Site） #

如果需要加入Site维度，修改分组逻辑即可：

result_with_site <- df %>%
  mutate(Date = format(date(Date_time), "%d/%m/%Y"),
         Category = str_to_title(Category),
         Subcategory = SubCat) %>%
  # 加入Site到分组条件
  group_by(Date, Category, Subcategory, Site) %>%
  summarise(Daily_Count = n(), .groups = "drop") %>%
  arrange(dmy(Date)) %>%
  group_by(Category, Subcategory, Site) %>%
  mutate(Count = cumsum(Daily_Count)) %>%
  select(Date, Category, Subcategory, Site, Count) %>%
  arrange(dmy(Date))

输出结果 #

运行上述代码后，result对象将生成与示例一致的结构化数据，可直接用于可视化绘图。

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