如何用R实现按interval_block分组统计block类型并生成summary_data

原始数据 #

data <- data.frame(
  is.on = c("FALSE","FALSE","FALSE","TRUE","FALSE","TRUE","FALSE","FALSE","TRUE","TRUE","TRUE","TRUE"),
  dur = c(10,20,30,10,10,10,10,20,10,20,30,40),
  dt = c(10,10,10,10,10,10,10,10,10,10,10,10),
  block = c(2,2,2,3,4,5,6,6,7,7,7,7),
  interval_block = c(1,1,1,2,2,2,3,3,3,4,4,4)
)

需求说明 #

需基于上述数据生成summary_data，规则如下：

• summary_data行数等于interval_block的不同取值数量
• 先计算每个interval_block分组内block的不同类型数量的最大值max_num_types，据此生成dur_1、dur_2…dur_n列（本例中max_num_types=3，对应3列）
• 对每个interval_block分组，统计各block类型的出现次数，将次数乘以10依次填入dur_1、dur_2…列，剩余列填0，最后保留interval_block列

期望输出：

summary_data <- data.frame(
  dur_1 = c(30,10,20,30),
  dur_2 = c(0,10,10,0),
  dur_3 = c(0,10,10,0),
  interval_block = c(1,2,3,4)
)

实现代码 #

# 若未安装依赖包，先运行：install.packages(c("dplyr", "tidyr"))
library(dplyr)
library(tidyr)

# 步骤1：计算每个interval_block内的唯一block数量，取最大值得到max_num_types
max_types <- data %>%
  group_by(interval_block) %>%
  summarise(num_blocks = n_distinct(block)) %>%
  pull(num_blocks) %>%
  max()

# 步骤2：统计次数、转换宽表并补全列
summary_data <- data %>%
  group_by(interval_block, block) %>%
  summarise(count = n(), .groups = "drop") %>%  # 统计每个block在interval内的出现次数
  mutate(dur_value = count * 10,
         col_order = row_number(by = interval_block)) %>%  # 为每个interval内的block分配列顺序
  pivot_wider(
    id_cols = interval_block,
    names_from = col_order,
    names_prefix = "dur_",
    values_from = dur_value,
    values_fill = 0
  ) %>%
  # 补全到max_num_types对应的列，缺失列填0
  mutate(!!!setNames(rep(0, max_types - (ncol(.) - 1)), paste0("dur_", (ncol(.) - 1):max_types))) %>%
  # 按dur_1到dur_n的顺序排序列，并将interval_block移到最后
  select(paste0("dur_", 1:max_types), interval_block)

代码说明 #

1. 计算max_num_types：通过分组统计每个interval_block内的唯一block数量，再取最大值确定需要生成的dur_*列数
2. 统计与转换：先分组统计每个interval_block下各block的出现次数，计算count*10得到目标值；再用pivot_wider将长表转换为宽表，缺失列自动填充0
3. 补全与排序：确保列数等于max_num_types，并按dur_1到dur_n的顺序排列列，最后调整interval_block到末尾

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