基于activity与in_out列计算DataFrame的qty差值并重塑结果

原始数据 #

给定的eventlog类型DataFrame定义如下：

vol<-structure(list(activity = c("RAAMELK", "RAAMELK", "Separering", 
"Separering", "Sweetmilk Pasteurizer 8331", "Sweetmilk Pasteurizer 8331", 
"9004 - T42 kartong 70x70", "9004 - T42 kartong 70x70", "9006 - T61 BIB", 
"9006 - T61 BIB", "9004 - T41 kartong 70x70", "9004 - T41 kartong 70x70"
), qty = c(0, 31, 31, 9, 31, 31, 6, 6, 3, 3, 28, 28), in_out = c("in", 
"out", "in", "out", "in", "out", "in", "out", "in", "out", "in", 
"out"), qty_scrap = c(0, 0, 0, 0, 0, -270.64, 0, 524, 0, 260, 
0, 0)), 
row.names = c(NA, -12L), case_id = "case_id", activity_id = "activity", 
activity_instance_id = "action", lifecycle_id = "registration_type", 
resource_id = "resource", timestamp = "timestamp", 
class = c("eventlog", "log", "tbl_df", "tbl", "data.frame"))

需求 #

对每个activity列值相同的行，按in_out列的in值减去out值的逻辑计算qty列的差值，存入新的qty_scrap列；最终输出包含4列的DataFrame：唯一的activity值、对应in的qty值（命名为qty_in）、对应out的qty值（命名为qty_out）、以及差值qty_scrap。

解决方案 #

使用dplyr包进行分组聚合处理，代码如下：

# 加载dplyr包（未安装需先运行 install.packages("dplyr")）
library(dplyr)

# 数据处理流程
result_df <- vol %>%
  group_by(activity) %>%
  summarise(
    qty_in = qty[in_out == "in"],
    qty_out = qty[in_out == "out"],
    qty_scrap = qty_in - qty_out
  ) %>%
  ungroup()

# 查看处理结果
print(result_df)

处理结果 #

运行上述代码后得到的DataFrame如下：

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