在R语言中为DataFrame添加基于分组计算的行

原始数据构造 #

CC_SHORT <- c("1-101", "1-101","1-101", "1-101","1-102", "1-102","1-102", "1-102")
CC_FULL <- c("Line 101", "Line 101","Line 101", "Line 101","Line 102", "Line 102","Line 102", "Line 102")
ACC_SHORT <- c("230L", "231H","230L", "231H","230L", "231H","230L", "231H")
ACC_FULL <- c("230 low", "231 high","230 low", "231 high","230 low", "231 high","230 low", "231 high")
Date <-c ("04/01/2022","04/01/2022","05/01/2022","05/01/2022","04/01/2022","04/01/2022","05/01/2022","05/01/2022")
Value <- c(60,100,50,80,65,75,85,105)  
DF<-data.frame(CC_SHORT, CC_FULL,ACC_SHORT,ACC_FULL,Date,Value)

需求说明 #

需要按CC_SHORT、CC_FULL、Date分组，为每组新增一行数据：

• ACC_SHORT固定为23M
• ACC_FULL固定为23 MID
• Value为同组内ACC_SHORT="230L"的Value减去ACC_SHORT="231H"的Value

目标数据示例 #

CC_SHORT1 <- c("1-101", "1-101","1-101", "1-101","1-101", "1-101","1-102", "1-102","1-102", "1-102","1-102", "1-102")
CC_FULL1 <- c("Line 101", "Line 101","Line 101", "Line 101","Line 101", "Line 101","Line 102", "Line 102","Line 102", "Line 102","Line 102", "Line 102")
ACC_SHORT1 <- c("230L", "231H","23M", "230L","231H", "23M","230L", "231H","23M", "230L","231H", "23M")
ACC_FULL1 <- c("230 low", "231 high","23 MID","230 low", "231 high","23 MID","230 low", "231 high","23 MID","230 low", "231 high", "23 MID")
Date1 <-c ("04/01/2022","04/01/2022","04/01/2022","05/01/2022","05/01/2022","05/01/2022","04/01/2022","04/01/2022","04/01/2022","05/01/2022","05/01/2022","05/01/2022")
Value1 <- c(60,100,40,50,80,30,65,75,-10,85,105,20)  # 修正原示例中65-75的计算误差
DF1<-data.frame(CC_SHORT1, CC_FULL1,ACC_SHORT1,ACC_FULL1,Date1,Value1)

实现代码（基于tidyverse） #

结合dplyr分组计算和tidyr行绑定完成需求：

library(tidyverse)

# 1. 计算所有分组的新增行数据
new_rows <- DF %>%
  group_by(CC_SHORT, CC_FULL, Date) %>%
  summarise(
    ACC_SHORT = "23M",
    ACC_FULL = "23 MID",
    Value = Value[ACC_SHORT == "230L"] - Value[ACC_SHORT == "231H"],
    .groups = "drop"
  )

# 2. 合并原始数据与新增行，并按分组排序
result_df <- DF %>%
  bind_rows(new_rows) %>%
  arrange(CC_SHORT, Date, ACC_SHORT)

# 查看最终结果
print(result_df)

代码解释 #

• 分组计算：按CC_SHORT、CC_FULL、Date分组，确保每组内仅针对同一日期、同一账户分类做计算；
• 新增行构造：在每组内提取对应230L和231H的Value做差，生成23M的Value；
• 合并排序：用bind_rows合并原始数据和新增行，再用arrange调整行顺序，让结果与目标结构一致。

异常处理提示 #

如果部分分组缺少230L或231H数据，会生成NA值，可根据业务需求添加filter过滤异常分组，或用replace_na填充默认值。

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