如何无需循环将频率表按指定时间区间分组求和？

需求 #

需要不使用循环、不手动创建大部分数据框，通过筛选member_casual变量和指定时间区间，将现有频率表汇总为更精简的版本。

现有数据 #

head(ride_duration)
  member_casual   duration frequency
1        casual 0.00000000       235
2        member 0.00000000       228
3        casual 0.01666667       567
4        member 0.01666667       813
5        casual 0.03333333      1527
6        member 0.03333333      3570
...

tail(ride_duration)
      member_casual duration frequency
45193        casual 35820.63         1
45194        member 35820.63         0
45195        casual 36257.80         1
45196        member 36257.80         0
45197        casual 40705.02         1
45198        member 40705.02         0

期望输出 #

member_casual        interval frequency
1         member      0-1 minute   sum of duration <=1
2         member     1-5 minutes   sum of duration >1<=5
3         member    5-10 minutes   sum of duration >5<=10
4         member   10-15 minutes         .
5         member   15-30 minutes         .
6         member   30-60 minutes         .
7         member  60-120 minutes         .
8         member  120-180 minute         .
9         member 180-240 minutes         .
10        member    240+ minutes         .
11        casual      0-1 minute   sum of duration <=1
12        casual     1-5 minutes   sum of duration >1<=5
13        casual    5-10 minutes   sum of duration >5<=10
14        casual   10-15 minutes         .
15        casual   15-30 minutes         .
16        casual  30-60 minutes         .
17        casual  60-120 minutes         .
18        casual 120-180 minute         .
19        casual 180-240 minutes         .
20        casual    240+ minutes         .

当前循环实现（耗时较长） #

已通过循环实现需求，但效率偏低，代码如下：

# 创建指定分钟区间的频率表
ride_duration_pyramid <- data.frame("member_casual"=c("casual","casual","casual","casual","casual","casual","casual","casual","casual","casual",
                                                      "member","member","member","member","member","member","member","member","member","member"),
                                    "interval"=c("0-1 minute", "1-5 minutes", "5-10 minutes", "10-15 minutes", "15-30 minutes",
                                                 "30-60 minutes","60-120 minutes","120-180 minute", "180-240 minutes", "240+ minutes",
                                                 "0-1 minute", "1-5 minutes", "5-10 minutes", "10-15 minutes", "15-30 minutes",
                                                 "30-60 minutes","60-120 minutes","120-180 minute", "180-240 minutes", "240+ minutes"),
                                    "frequency"=replicate(20,0))
current_interval <- 1 # 起始区间
interval_high <- c(1,5,10,15,30,60,120,180,240,40706) # 区间最大值
for (i in 1:nrow(ride_duration)) { # 遍历所有行
  row <- ride_duration[i,]
  # 找到当前数据点所属的区间
  while (row[2] >= interval_high[current_interval]) {
    current_interval <- current_interval + 1;
  }
  if (row[1] == "casual") {
    ride_duration_pyramid[current_interval,3] <- ride_duration_pyramid[current_interval,3] + row[3];
  } else {
    ride_duration_pyramid[current_interval+10,3] <- ride_duration_pyramid[current_interval+10,3] + row[3];
  }
}

优化方案：用dplyr+cut实现高效分组汇总 #

可以利用R的函数式编程工具，结合dplyr包的分组聚合和cut()函数的区间划分，快速实现需求，无需手动创建数据框或循环：

完整代码 #

library(dplyr)
library(tidyr)

# 定义区间断点和标签
breaks <- c(0, 1, 5, 10, 15, 30, 60, 120, 180, 240, Inf)
labels <- c("0-1 minute", 
            "1-5 minutes", 
            "5-10 minutes", 
            "10-15 minutes", 
            "15-30 minutes", 
            "30-60 minutes", 
            "60-120 minutes", 
            "120-180 minute", 
            "180-240 minutes", 
            "240+ minutes")

# 处理数据
ride_duration_pyramid <- ride_duration %>%
  # 为每条数据分配对应区间
  mutate(interval = cut(duration, breaks = breaks, labels = labels, include.lowest = TRUE)) %>%
  # 按用户类型和区间分组，求和frequency
  group_by(member_casual, interval) %>%
  summarise(frequency = sum(frequency), .groups = "drop") %>%
  # 补全所有用户类型+区间的组合，缺失项用0填充
  complete(member_casual, interval, fill = list(frequency = 0)) %>%
  # 按指定顺序排序，匹配期望输出格式
  arrange(member_casual, factor(interval, levels = labels))

代码说明 #

• cut()：自动将duration映射到指定区间，include.lowest = TRUE确保0值被归入第一个区间
• group_by()+summarise()：高效完成分组求和，性能远优于循环
• complete()：补全所有可能的用户类型和区间组合，避免出现缺失的行
• arrange()：按指定顺序排序，让输出结构与期望一致

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