R语言按quantity累计和分组并合并小分组的实现

背景与初始代码 #

首先生成测试数据框：

df <- data.frame(items=sample(LETTERS,replace= T),quantity=sample(1:100,26,replace=FALSE),price=sample(100:1000,26,replace=FALSE))

最初需求是按quantity的累计和接近500分组，后续调整阈值为250，使用以下代码生成分组标识和组内累计值：

set.seed(123)

df <- data.frame(items=sample(LETTERS,replace= T),quantity=sample(1:100,26,replace=FALSE),price=sample(100:1000,26,replace=FALSE))

df$group=cumsum(c(1,ifelse(diff(cumsum(df$quantity)%% 250) < 0,1,0)))
df$total=ave(df$quantity,df$group,FUN=cumsum)
df %>% group_by(group) %>% summarise(max = max(total, na.rm=TRUE))

遇到的问题 #

运行上述代码后得到分组统计结果：

A tibble: 6 × 2 #

group max

1 1 238
2 2 254
3 3 256
4 4 246
5 5 237
6 6 101

可见第6组的最大累计值仅为101，小于200，需要将该组合并到第5组，但尝试用ifelse实现未成功，寻求可行方法。

期望结果 #

合并后的数据框应如下所示：

> df
   items quantity price group total
1      O       36   393     1    36
2      S       78   376     1   114
3      N       81   562     1   195
4      C       43   140     1   238
5      J       76   530     2    76
6      R       15   189     2    91
7      V       32   415     2   123
8      K        7   322     2   130
9      E        9   627     2   139
10     T       41   215     2   180
11     N       74   705     2   254
12     V       23   873     3    23
13     Y       27   846     3    50
14     Z       60   555     3   110
15     E       53   697     3   163
16     S       93   953     3   256
17     Y       86   138     4    86
18     Y       88   258     4   174
19     I       38   851     4   212
20     C       34   308     4   246
21     H       69   473     5    69
22     Z       72   917     5   141
23     G       96   133     5   237
24     J       63   615     5   300
25     I       13   112     5   376
26     S       25   168     5   477

解决方案 #

可以通过以下步骤实现分组合并：

1. 完成初始分组与累计值计算；
2. 提取各分组的最大累计值；
3. 判断最后一组是否小于阈值（200），若是则将该组的group值替换为前一组的编号；
4. 重新计算组内累计值。

完整代码如下：

set.seed(123)

df <- data.frame(items=sample(LETTERS,replace= T),quantity=sample(1:100,26,replace=FALSE),price=sample(100:1000,26,replace=FALSE))

# 初始分组与累计值计算
df$group <- cumsum(c(1, ifelse(diff(cumsum(df$quantity) %% 250) < 0, 1, 0)))
df$total <- ave(df$quantity, df$group, FUN = cumsum)

# 获取各分组的最大累计值
group_summary <- df %>% 
  group_by(group) %>% 
  summarise(max_total = max(total, na.rm = TRUE)) %>% 
  ungroup()

# 检查最后一组是否需要合并
if (tail(group_summary$max_total, 1) < 200) {
  last_group_num <- tail(group_summary$group, 1)
  # 将最后一组的group替换为前一组编号
  df$group[df$group == last_group_num] <- last_group_num - 1
  # 重新计算累计值
  df$total <- ave(df$quantity, df$group, FUN = cumsum)
}

# 查看最终结果
print(df)

运行上述代码后，即可得到符合期望的合并后数据框。

内容的提问来源于stack exchange，提问作者Alegría