我有一个dataframe,其中每一行都有一个ID和值activity。下面是一个示例数据帧:
test=data.frame(
start=c(1,50,100,150,200,250,300,350,400,450,500,100,150,200),
end=c(50,100,150,200,250,300,350,400,450,500,550,150,200,250),
sub_id=c("subid_1_1","subid_1_2","subid_1_3","subid_1_4","subid_1_5","subid_1_6","subid_1_7","subid_1_8","subid_1_9","subid_1_10","subid_1_11","subid_2_1","subid_2_2","subid_2_3"),
id=c(rep("id_1",11),rep("id_2",3)),
activity=c(-0.2,-0.6,-1,-1.2,-1.6,-1.6,-0.5,0.2,-1.2,-0.8,0.1,0.1,1.2,0.5))对于具有相同ID的每一行:
我想合并所有没有被大于-1的值分隔的、接近最小活动值的行。并保持合并行的起始值和结束值的第一个值和最后一个值,以及在选定行上计算的平均值。
预期输出应为:
start end id activity
100 300 id_1 -1.2首先,我删除值大于-1的行
test=test[test$activity<=-1,]我尝试使用setDT合并行
library("data.table")
final_test=setDF(setDT(test)[, .(start = min(start), end = max(end),activity=mean(activity)),by=id])所以它保留了第一个和最后一个元素,通过id和活动的计算平均值,但这并不是我想要的。
这个不起作用
final_test=setDF(setDT(test)[, .(start = min(start), end = max(end),activity=mean(activity)),by=id[test$activity<=-1]])谢谢你的帮助
发布于 2020-03-17 07:06:10
首先,您可以创建一个grp列,它将按顺序将组编号为1、2等-每次您看到一个> -1的值时,该列都会递增生成。
library(dplyr)
threshold <- -1
test.group <- test %>%
mutate(grp = cumsum(activity > threshold))
print(test.group, row.names = F)输出:
start end sub_id id activity grp
1 50 subid_1_1 id_1 -0.2 1
50 100 subid_1_2 id_1 -0.6 2
100 150 subid_1_3 id_1 -1.0 2
150 200 subid_1_4 id_1 -1.2 2
200 250 subid_1_5 id_1 -1.6 2
250 300 subid_1_6 id_1 -1.0 2
300 350 subid_1_7 id_1 -0.5 3
350 400 subid_1_8 id_1 0.2 4
400 450 subid_1_9 id_1 -1.2 4
450 500 subid_1_10 id_1 -0.8 5
500 550 subid_1_11 id_1 0.1 6
100 150 subid_2_1 id_2 0.1 7
150 200 subid_2_2 id_2 1.2 8
200 250 subid_2_3 id_2 0.5 9然后,您可以删除大于-1的值并按变量grp进行分组,然后轻松地进行汇总以执行您想要的操作。如果需要,您可以对代码进行调整以对其进行微调。
test.result <-
test.group %>%
subset(activity <= threshold) %>%
group_by(id, grp) %>%
summarise(start = min(start), end = max(end), activity = mean(activity)) %>%
ungroup() %>%
select(start, end, id, activity)
print(test.result, row.names = F)输出:
# A tibble: 2 x 4
start end id activity
<dbl> <dbl> <fct> <dbl>
1 100 300 id_1 -1.2
2 400 450 id_1 -1.2如果您还想带出组内持续时间最短的活动的详细信息,您可以按照以下步骤操作:
test.result <-
test.group %>%
subset(activity <= -1) %>%
group_by(id, grp) %>%
arrange(activity) %>%
summarise(
start.min = first(start),
end.min = first(end),
sub_id.min = first(sub_id),
activity.min = first(activity),
start = min(start),
end = max(end),
activity = mean(activity)
) %>%
ungroup() %>%
select(start, end, id, activity, start.min, end.min, sub_id.min, activity.min)输出
start end id activity start.min end.min sub_id.min activity.min
<dbl> <dbl> <fct> <dbl> <dbl> <dbl> <fct> <dbl>
1 100 300 id_1 -1.2 200 250 subid_1_5 -1.6
2 400 450 id_1 -1.2 400 450 subid_1_9 -1.2https://stackoverflow.com/questions/60713886
复制相似问题