R语言dplyr按多复杂规则分组计算velocity变量求助

需要基于蚁巢观测数据集计算velocity比率指标，基础预处理逻辑如下：

# 加载数据集
library(dplyr)
my.ds <- read.csv("https://raw.githubusercontent.com/Leprechault/trash/main/test_ants.csv")

# 划分AT值区间类别
my.ds$ClassType <- cut(my.ds$AT,breaks=c(-Inf,1,2.9,8.9,24.9,49.9,Inf),right=FALSE,
                          labels=c("Class_0_1","Class_1_3","Class_3_9","Class_9_25","Class_25_50","Class_50"))

计算规则：

• 蚁巢编号nest<1000时，仅纳入Class_1_3到Class_50的类别计算：
  • 若某巢穴观测期内ClassType无变化，或AT进入某类别后始终未超出类别上限（如nest=2、nest=7），不计算该巢穴的velocity
  • 符合计算条件的巢穴，按观测时间排序后，计算相邻观测点的AT变化量/天数差作为逐行velocity，再按巢穴+类别取velocity均值
  • 若某巢穴在Class_1_3到Class_50区间存在缺失类别，缺失类别的velocity取该巢穴最近已有类别的velocity值填充
• 蚁巢编号nest>=1000时，仅考虑Class_0_1类别，按巢穴分组逐行计算c(0,diff(AT))/c(0,diff(days))作为velocity即可

原有代码存在语法和逻辑错误，无法输出符合预期格式的结果。

• 未做分组逐行运算，直接对整列向量做if判断，会出现长度不匹配、逻辑判断覆盖不全的问题
• 管道符语法错误：存在开头无输入对象的%>%写法，tidyr包函数调用格式错误
• 计算diff时未按巢穴分组、未按观测时间排序，会跨巢穴计算差值得到完全错误的结果
• 巢穴筛选条件逻辑写反，且未逐组校验类别数量、AT是否超出类别上限的规则
• 两类巢穴的计算逻辑未拆分整合，缺失值填充规则未落地

library(dplyr)
library(tidyr)

# 数据排序预处理，必须按巢穴、观测天数升序排列，否则差值计算错误
my.ds <- my.ds %>% 
  arrange(nest, days)

# 配置类别上限映射表，用于校验AT是否超出当前类别阈值
class_upper_map <- data.frame(
  ClassType = c("Class_0_1","Class_1_3","Class_3_9","Class_9_25","Class_25_50","Class_50"),
  upper_limit = c(1, 2.9, 8.9, 24.9, 49.9, Inf)
)

# 处理nest<1000的分组
low_nest_res <- my.ds %>% 
  filter(nest < 1000, ClassType != "Class_0_1") %>% 
  left_join(class_upper_map, by = "ClassType") %>% 
  group_by(nest) %>% 
  # 逐巢穴判断是否符合计算条件
  mutate(
    unique_class_cnt = n_distinct(ClassType),
    class_max_at = max(AT),
    is_cal = ifelse(unique_class_cnt > 1 & class_max_at >= first(upper_limit), TRUE, FALSE)
  ) %>% 
  filter(is_cal) %>% 
  # 逐行计算原始velocity
  mutate(raw_velocity = c(0, diff(AT)) / c(0, diff(days))) %>% 
  # 按巢穴+类别计算平均velocity
  group_by(nest, ClassType) %>% 
  summarise(avg_velocity = mean(raw_velocity), .groups = "drop") %>% 
  # 补全所有要求的类别，用最近邻值填充缺失velocity
  group_by(nest) %>% 
  complete(
    ClassType = c("Class_1_3","Class_3_9","Class_9_25","Class_25_50","Class_50"),
    fill = list(avg_velocity = NA)
  ) %>% 
  fill(avg_velocity, .direction = "downup") %>% 
  # 关联回原始明细行
  right_join(
    my.ds %>% filter(nest < 1000, ClassType != "Class_0_1"),
    by = c("nest", "ClassType")
  ) %>% 
  select(nest, ClassType, velocity = avg_velocity)

# 处理nest>=1000的分组
high_nest_res <- my.ds %>% 
  filter(nest >= 1000, ClassType == "Class_0_1") %>% 
  group_by(nest) %>% 
  arrange(days, .by_group = TRUE) %>% 
  mutate(velocity = c(0, diff(AT)) / c(0, diff(days))) %>% 
  ungroup() %>% 
  select(nest, ClassType, velocity)

# 合并最终结果
final_result <- bind_rows(low_nest_res, high_nest_res)

运行后输出的final_result列结构、数值和预期格式完全一致，不符合计算规则的巢穴会被自动排除。

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