英文:
How to group events that happened close in time
问题
我想要通过为发生在彼此在时间上接近的事件分配一个“组ID”来对数据行进行分组。
例如，考虑有一台计算机的事件日志，您想要将发生在彼此接近的事件分组在一起。
tibble::tribble(~event_id, ~happened_at,"xyz", "2023-07-31 13:35:06","tsv", "2023-07-31 13:35:07","abc", "2023-07-31 13:41:30","fgh", "2023-07-31 13:42:05","dda", "2023-07-31 13:42:12","ggf", "2023-08-01 4:43:15","oor", "2023-08-01 13:49:36","wqe", "2023-08-01 14:33:10","oop", "2023-08-01 14:34:14")
我想要添加另一列来表示事件组，如果2个或更多事件在几秒钟内发生，它们应该获得相同的“组ID”。否则，单个事件将获得自己的组ID。
# 期望的输出tibble::tribble(~event_id, ~happened_at, ~group_id,"xyz", "2023-07-31 13:35:06", 1,"tsv", "2023-07-31 13:35:07", 1,"abc", "2023-07-31 13:41:30", 2,"fgh", "2023-07-31 13:42:05", 2,"dda", "2023-07-31 13:42:12", 2,"ggf", "2023-08-01 4:43:15", 3,"oor", "2023-08-01 13:49:36", 4,"wqe", "2023-08-01 14:33:10", 5,"oop", "2023-08-01 14:34:14", 5)
虽然这似乎是一个基本的问题，但我想不出如何做到。对于这样的问题，有任何想法或“最佳实践”吗？
英文:
I want to group – by assigning a "group ID" – rows of data for events that happened in temporal proximity to each other.
For example, consider having a log of events from a computer, and you want to group together events that happened close to each other.
tibble::tribble(~event_id, ~happened_at,"xyz", "2023-07-31 13:35:06","tsv", "2023-07-31 13:35:07","abc", "2023-07-31 13:41:30","fgh", "2023-07-31 13:42:05","dda", "2023-07-31 13:42:12","ggf", "2023-08-01 4:43:15","oor", "2023-08-01 13:49:36","wqe", "2023-08-01 14:33:10","oop", "2023-08-01 14:34:14")
I want to add another column that accounts for groups of events, such that if 2 or more events happened within several seconds, they should get the same "group ID". Otherwise, single events would get a group ID of their own.
# desired outputtibble::tribble(~event_id, ~happened_at, ~group_id,"xyz", "2023-07-31 13:35:06", 1,"tsv", "2023-07-31 13:35:07", 1,"abc", "2023-07-31 13:41:30", 2,"fgh", "2023-07-31 13:42:05", 2,"dda", "2023-07-31 13:42:12", 2,"ggf", "2023-08-01 4:43:15", 3,"oor", "2023-08-01 13:49:36", 4,"wqe", "2023-08-01 14:33:10", 5,"oop", "2023-08-01 14:34:14", 5)
While this seems like a basic problem, I cannot think of a way to do it. Any idea or "best practice" on such a thing?
答案1
得分: 1
需要首先定义接近性。为了演示目的，我已将其定义为2分钟，但您可以根据您的数据集选择它。
library(dplyr)library(lubridate)cutoff_secs <- 120Ldf %>%mutate(happened_at = ymd_hms(happened_at)) %>%arrange(happened_at) %>%mutate(group_id = cumsum(as.integer(difftime(happened_at,lag(happened_at, default = first(happened_at)),units = "secs")) > cutoff_secs) + 1)# event_id happened_at group_id#1 xyz 2023-07-31 13:35:06 1#2 tsv 2023-07-31 13:35:07 1#3 abc 2023-07-31 13:41:30 2#4 fgh 2023-07-31 13:42:05 2#5 dda 2023-07-31 13:42:12 2#6 ggf 2023-08-01 04:43:15 3#7 oor 2023-08-01 13:49:36 4#8 wqe 2023-08-01 14:33:10 5#9 oop 2023-08-01 14:34:14 5
在difftime中，我们计算了happened_at的连续值之间的差异，如果它们超过了cutoff_secs，则增加了group_id的计数。
英文:
You need to define the proximity first. For demonstration purposes, I have defined it as 2 mins but you can chose it based on your dataset.
library(dplyr)library(lubridate)cutoff_secs <- 120Ldf %>%mutate(happened_at = ymd_hms(happened_at)) %>%arrange(happened_at) %>%mutate(group_id = cumsum(as.integer(difftime(happened_at,lag(happened_at, default = first(happened_at)),units = "secs")) > cutoff_secs) + 1)# event_id happened_at group_id# <chr> <dttm> <dbl>#1 xyz 2023-07-31 13:35:06 1#2 tsv 2023-07-31 13:35:07 1#3 abc 2023-07-31 13:41:30 2#4 fgh 2023-07-31 13:42:05 2#5 dda 2023-07-31 13:42:12 2#6 ggf 2023-08-01 04:43:15 3#7 oor 2023-08-01 13:49:36 4#8 wqe 2023-08-01 14:33:10 5#9 oop 2023-08-01 14:34:14 5
In difftime we calculate the difference between the consecutive values of happened_at and if they exceed cutoff_secs increment the count of group_id.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论