Guidance with lag / rolling categories

tidyverse
1

Hi there,
Looking for some guidance with 'lag' and/or the capability of rolling into a list.
I want to produce a list of the last 5 results based on the name of the individual. I understand lag(result,1) will give me the last result but that's a far as I can get.

Here is my example data
data <- tibble::tribble(
~Name, ~Result,
"A", 1L,
"B", 2L,
"A", 3L,
"A", 4L,
"B", 5L,
"A", 6L,
"B", 7L,
"A", 8L,
"A", 9L,
"A", 5L
)

here is my wanted result
expected <- tibble::tribble(
~Name, ~Result, ~Expected,
"A", 1L, NA,
"B", 2L, NA,
"A", 3L, "1",
"A", 4L, "1,3",
"B", 5L, "2",
"A", 6L, "1,3,4",
"B", 7L, "2,5",
"A", 8L, "1,3,4,6",
"A", 9L, "1,3,4,6,8",
"A", 5L, "3,4,6,8,9"
)

Any guidance would be appreciated!

Regards,

M

2

Hi @matt19 ,

Thank you for your question.

I think the dplyr::lag() function is designed to selectively bring back a single value, rather than a range.

The following approach gives very close to what you're after, though:

data <- tibble::tribble(
  ~Name, ~Result,
  "A", 1L,
  "B", 2L,
  "A", 3L,
  "A", 4L,
  "B", 5L,
  "A", 6L,
  "B", 7L,
  "A", 8L,
  "A", 9L,
  "A", 5L
)

data_new <- data |> 
  # use accumulate to sequentially add 'Results' values to a vector *within* 
  # each 'Name' group
  dplyr::mutate(
    Expected = purrr::accumulate(dplyr::lag(Result), ~ c(.x, .y)),
    .by = Name
  ) |> 
  # switch to rowwise execution to deal with each vector separately
  dplyr::rowwise() |> 
  dplyr::mutate(
    # limit each vector to the last 5 records
    Expected = list(tail(Expected[!is.na(Expected)], n = 5)),
    # combine together with ',' separation
    Expected = paste(Expected, collapse = ',')
  )

# see the results
data_new
#> # A tibble: 10 × 3
#> # Rowwise: 
#>    Name  Result Expected   
#>    <chr>  <int> <chr>      
#>  1 A          1 ""         
#>  2 B          2 ""         
#>  3 A          3 "1"        
#>  4 A          4 "1,3"      
#>  5 B          5 "2"        
#>  6 A          6 "1,3,4"    
#>  7 B          7 "2,5"      
#>  8 A          8 "1,3,4,6"  
#>  9 A          9 "1,3,4,6,8"
#> 10 A          5 "3,4,6,8,9"

Created on 2025-01-20 with reprex v2.1.1

1 Like