Combine the row values based on time interval

Viewed 115

I have a data frame like this

node <- c("ABCC","ABCC","ABCC","ABCC","ABCC","ABCC","ABCC","ABCC","ABCC")
activity <-c("NODE_ISOLATION","NE_NOT_REACH_VIA_PRIMARY_MNG_INTERF","NODE_ISOLATION","LOSS_OF_MULTIPLEX_SECTION-OMS_A","NODE_ISOLATION","NE_NOT_REACH_VIA_PRIMARY_MNG_INTERF","NE_NOT_REACH_VIA_PRIMARY_MNG_INTERF", "UNDERLYING_RESOURCE_UNAVAILABLE-OMS_A","UNDERLYING_RESOURCE_UNAVAILABLE-OMS_A") 

e <-c("2020-05-08 16:11:58","2020-05-08 16:11:58","2020-05-08 16:30:07","2020-05-09 03:00:08","2020-05-09 03:08:08","2020-05-09 03:28:08","2020-05-09 13:08:08","2020-05-09 13:10:08","2020-05-09 13:28:08")

df <- data.frame(node, activity, e)
df

I want to combine the row values based on 30 min time interval. desired output is follows

node <- c("ABCC","ABCC","ABCC")

activity <-c("NODE_ISOLATION,NE_NOT_REACH_VIA_PRIMARY_MNG_INTERF,NODE_ISOLATION","LOSS_OF_MULTIPLEX_SECTION-OMS_A,NODE_ISOLATION,NE_NOT_REACH_VIA_PRIMARY_MNG_INTERF","NE_NOT_REACH_VIA_PRIMARY_MNG_INTERF,UNDERLYING_RESOURCE_UNAVAILABLE-OMS_A,UNDERLYING_RESOURCE_UNAVAILABLE-OMS_A") 

cnt <- c(3,3,3)
df1 <- data.frame(node, activity, cnt)
df1
3 Answers

This looks like a cumulative sum of time differences, with reset.

Set a threshold which is the maximum time difference to have in a group. In this case, use 30 minutes (30 * 60 = 1800 seconds).

Make sure e is POSIXct.

A function cumsum_with_reset will calculate the cumulative sum of time differences, and once the threshold is exceeded, reset to zero for the next group.

So, the approach using this custom function is: first, group_by(node) for evaluations made within each node. Calculate differences diff between rows. Determine the group of no more than 30 minutes each, using the custom function. Then, group_by this new group, count rows for each group, and use toString to put activity into collective comma-separated values.

library(tidyverse)

threshold <- 30 * 60 # 30 minutes * 60 seconds

df$e <- as.POSIXct(df$e)

cumsum_with_reset <- function(x, threshold) {
  cumsum <- 0
  group <- 0
  result <- numeric()
  for (i in seq_along(x)) {
    cumsum <- cumsum + x[i]
    if (cumsum >= threshold) {
      group <- group + 1
      cumsum <- 0
    }
    result = c(result, group)
  }
  return (result)
}

df %>%
  group_by(node) %>%
  mutate(diff = c(0, diff(e)),
         group = cumsum_with_reset(diff, threshold)) %>%
  group_by(group, .add = TRUE) %>%
  summarise(cnt = n(),
            activity = toString(activity)) %>%
  dplyr::select(-group)

Output

  node    cnt activity                                                                                                         
  <chr> <int> <chr>                                                                                                            
1 ABCC      3 NODE_ISOLATION, NE_NOT_REACH_VIA_PRIMARY_MNG_INTERF, NODE_ISOLATION                                              
2 ABCC      3 LOSS_OF_MULTIPLEX_SECTION-OMS_A, NODE_ISOLATION, NE_NOT_REACH_VIA_PRIMARY_MNG_INTERF                             
3 ABCC      3 NE_NOT_REACH_VIA_PRIMARY_MNG_INTERF, UNDERLYING_RESOURCE_UNAVAILABLE-OMS_A, UNDERLYING_RESOURCE_UNAVAILABLE-OMS_A
node <- c("ABCC","ABCC","ABCC","ABCC","ABCC","ABCC","ABCC","ABCC","ABCC")
activity <-c("NODE_ISOLATION","NE_NOT_REACH_VIA_PRIMARY_MNG_INTERF","NODE_ISOLATION","LOSS_OF_MULTIPLEX_SECTION-OMS_A","NODE_ISOLATION","NE_NOT_REACH_VIA_PRIMARY_MNG_INTERF","NE_NOT_REACH_VIA_PRIMARY_MNG_INTERF", "UNDERLYING_RESOURCE_UNAVAILABLE-OMS_A","UNDERLYING_RESOURCE_UNAVAILABLE-OMS_A") 

e <-c("2020-05-08 16:11:58","2020-05-08 16:11:58","2020-05-08 16:30:07","2020-05-09 03:00:08","2020-05-09 03:08:08","2020-05-09 03:28:08","2020-05-09 13:08:08","2020-05-09 13:10:08","2020-05-09 13:28:08")

df <- data.frame(node, activity, e)

library(dplyr)
##### Change e format 
df <- df %>% mutate(e=as.POSIXct(e))

##### 1st Breaks option automatic 
start <- as.POSIXct("2020-05-08 16:00:00") # Can be min(as.POSIXct(df$e))
end <- max(as.POSIXct(df$e))+30*60
breaks_a <- seq(start,end,"30 min")

##### 2nd Breaks option manual
breaks_m <- c(as.POSIXct("2020-05-08 16:00:00"),
              as.POSIXct("2020-05-08 16:31:00"),
              as.POSIXct("2020-05-09 03:30:00"),
              as.POSIXct("2020-05-09 13:30:00"))

df <- df %>% mutate(e_cut_automatic=cut(e,breaks_a),
                    e_cut_manual=cut(e,breaks_m))

count_act <- function(x) data.frame(node=x$node[1],
                                    activity=paste(x$activity,collapse=","),
                                    cnt=nrow(x))

count_n <- function(x) do.call("rbind",lapply(split(x,x$node),count_act))


##### With automatic breaks (recomend)
r <- do.call("rbind",lapply(split(df,df$e_cut_automatic),count_n))
print(r,row.names = FALSE)

Output: enter image description here

##### With manual breaks
r <- do.call("rbind",lapply(split(df,df$e_cut_manual),count_n))
print(r,row.names = FALSE)

Output: enter image description here

Try this:

node <- c("ABCC","ABCC","ABCC","ABCC","ABCC","ABCC","ABCC","ABCC","ABCC")
activity <-c("NODE_ISOLATION","NE_NOT_REACH_VIA_PRIMARY_MNG_INTERF","NODE_ISOLATION","LOSS_OF_MULTIPLEX_SECTION-OMS_A","NODE_ISOLATION","NE_NOT_REACH_VIA_PRIMARY_MNG_INTERF","NE_NOT_REACH_VIA_PRIMARY_MNG_INTERF", "UNDERLYING_RESOURCE_UNAVAILABLE-OMS_A","UNDERLYING_RESOURCE_UNAVAILABLE-OMS_A") 
e <-c("2020-05-08 16:11:58","2020-05-08 16:11:58","2020-05-08 16:30:07","2020-05-09 03:00:08","2020-05-09 03:08:08","2020-05-09 03:28:08","2020-05-09 13:08:08","2020-05-09 13:10:08","2020-05-09 13:28:08")

df <- data.frame(node, activity, e)

as.data.table(df) %>% mutate(e_cut=cut(as.POSIXct(e),"30 min")) %>%
  group_by(node, e_cut) %>%
  summarise(activity=paste(activity,collapse=","),cnt = n())%>%
  select(-e_cut)
Related