Grouped recurrence by periods over a data.table

user_12873028 0
Viewed 163

I have a dataset with names, dates, and several categorical columns. Let's say

data <- data.table(name = c('Anne', 'Ben', 'Cal', 'Anne', 'Ben', 'Cal', 'Anne', 'Ben', 'Ben', 'Ben', 'Cal'),
               period = c(1,1,1,1,1,1,2,2,2,3,3), 
               category = c("A","A","A","B","B","B","A","B","A","B","A"))

Which looks like this:

  name  period  category
  Anne       1         A
   Ben       1         A
   Cal       1         A
  Anne       1         B
   Ben       1         B
   Cal       1         B
  Anne       2         A
   Ben       2         B
   Ben       2         A
   Ben       3         A
   Cal       3         B

I want to compute, for each period, how many names were present in the past period, for every group of my categorical variables. The output should be as follows:

period  category  recurrence_count
    2         A                 2   # due to Anne and Ben being on A, period 1
    2         B                 1   # due to Ben being on B, period 1
    3         A                 1   # due to Ben being on A, period 2 
    3         B                 0   # no match from B, period 2

I am aware of the .I and .GRP operators in data.table, but I have no idea how to write the notion of 'next group' in the j entry of my statement. I imagine something like this might be a reasonable path, but I can't figure out the correct syntax:

data[, .(recurrence_count = length(intersect(name, name[last(.GRP)]))), by = .(category, period)]
data.table r
4 Answers

You can first summarize your data by category and period.

previous_period_names <- data[, .(names = list(name)), .(category, period)]

previous_period_names[, next_period := period + 1]

Join your summary with your original data.

data[previous_period_names, names := i.names, on = c('period==next_period')]

Now count how many names you see the name in the summarized names

data[, .(recurrence_count = sum(name %in% unlist(names))), by = .(period, category)]
user_99059640

One option in base R is to split the 'data' by 'category', then loop over the list (lapply), use Reduce with intersect on the splitted 'name' by 'period' with accumulate as TRUE, get the lengths of the list, create a data.frame with the unique elements of 'period' and use Map to create the 'category' from the names of the list output, rbind the list of data.frame into a single dataset

library(data.table)
lst1 <- lapply(split(data, data$category), function(x) 
   data.frame(period = unique(x$period)[-1], 
   recurrence_count = lengths(Reduce(intersect, 
           split(x$name, x$period), accumulate = TRUE)[-1])))
rbindlist(Map(cbind, category = names(lst1), lst1))[
      order(period), .(period, category, recurrence_count)]
#     period category recurrence_count
#1:      2        A                2
#2:      2        B                1
#3:      3        A                1
#4:      3        B                0

Or using the same logic within data.table, grouped by 'category, do the split of 'name' by 'period' and apply the Reduce with intersect

setDT(data)[, .(period = unique(period), 
    recurrence_count = lengths(Reduce(intersect, 
    split(name, period), accumulate = TRUE))), .(category)][duplicated(category)]
#   category period recurrence_count
#1:        A      2                2
#2:        A      3                1
#3:        B      2                1
#4:        B      3                0

Or similar option in tidyverse

library(dplyr)
library(purrr)
data %>% 
   group_by(category) %>% 
   summarise(reccurence_count = lengths(accumulate(split(name, period),
        intersect)), period = unique(period), .groups = 'drop' ) %>%  
   filter(duplicated(category))
# A tibble: 4 x 3
#  category reccurence_count period
#  <chr>               <int>  <int>
#1 A                       2      2
#2 A                       1      3
#3 B                       1      2
#4 B                       0      3

data

data <- structure(list(name = c("Anne", "Ben", "Cal", "Anne", "Ben", 
"Cal", "Anne", "Ben", "Ben", "Ben", "Cal"), period = c(1L, 1L, 
1L, 1L, 1L, 1L, 2L, 2L, 2L, 3L, 3L), category = c("A", "A", "A", 
"B", "B", "B", "A", "B", "A", "A", "B")), class = "data.frame",
row.names = c(NA, 
-11L))
user_37322710

A data.table option

setDT(df)[
  ,
  {
    u <- split(name, period)
    data.table(
      period = unique(period)[-1],
      recurrence_count = lengths(
        Map(
          intersect,
          head(u, -1),
          tail(u, -1)
        )
      )
    )
  },
  category
]

gives

   category period recurrence_count
1:        A      2                2
2:        A      3                1
3:        B      2                1
4:        B      3                0
user_121587570

Another data.table alternative. For rows that can have a previous period (period != 1), create such a variable (prev_period := period - 1).

Join original data with a subset that has values for 'prev_period' (data[data[!is.na(prev_period)]). Join on 'category', 'period = prev_period' and 'name'.

In the resulting data set, for each 'period' and 'category' (by = .(period = i.period, category)), count the number of names from original data (x.name) that had a match with previous period (length(na.omit(x.name))).

data[period != 1, prev_period := period - 1]

data[data[!is.na(prev_period)], on = c("category", period = "prev_period", "name"),
     .(category, i.period, x.name)][
       , .(n = length(na.omit(x.name))), by = .(period = i.period, category)]

#    period category n
# 1:      2        A 2
# 2:      2        B 1
# 3:      3        B 1
# 4:      3        A 0
user_18517120
Related