r - 检查表超过特定值和计数次数超过相应 id 和标签的相应阈值

Question

我有一个数据框 df

df <- data.frame(id =c(1,2,1,4,1,5,6),
                    label=c("a","b", "a", "a","a", "e", "a"), 
                    color = c("g","a","g","g","a","a","a"),
                    threshold = c(12, 10, 12, 12, 12, 35, 40),
                    value =c(32.1,0,15.0,10,1,50,45),stringsAsFactors = F
            )

阈值基于标签

我应该通过考虑每个 id 得到如下表格，以及相应的标签超过其阈值的次数

考虑到超值的计算，颜色是独立的

我试过这样

final_df <- df %>% 
  mutate(check = if_else(value > threshold, 1, 0)) %>% 
  group_by(id, label) %>% 
  summarise(exceed = sum(check))

但是我没有得到各自的 id，而是得到了超过总数的总数

Answer 1

仅使用 base R，使用 aggregate。

aggregate(seq.int(nrow(df)) ~ id + label, df, function(i) sum(df[i, 4] < df[i, 5]))
#  id label seq.int(nrow(df))
#1  1     a                 2
#2  4     a                 0
#3  6     a                 1
#4  2     b                 0
#5  5     e                 1

为了匹配问题中发布的预期输出，需要做一些额外的工作。

exceed <- seq.int(nrow(df))
agg <- aggregate(exceed ~ id + label, df, function(i) sum(df[i, 4] < df[i, 5]))
res <- merge(df[1:3], agg)
unique(res)
#  id label color exceed
#1  1     a     g      2
#3  1     a     a      2
#4  2     b     a      0
#5  4     a     g      0
#6  5     e     a      1
#7  6     a     a      1