Create a loop that appends columns with leading variables

Gosha K 0
Viewed 25

My data frame contains the following columns: date, id, category. I want to create a loop which appends additional columns (category1, category2, etc.) , containing leading categories for each id by date.

My code without a loop looks like this:

df <- df %>%
  dplyr::group_by(id) %>%
  dplyr::mutate(category1 = dplyr::lead(category, n = 1L, order_by = date))

df <- df %>%
  dplyr::group_by(id) %>%
  dplyr::mutate(category2 = dplyr::lead(category, n = 2L, order_by = date))

df <- df %>%
  dplyr::group_by(id) %>%
  dplyr::mutate(category3 = dplyr::lead(category, n = 3L, order_by = date))

#and so on.

What would be the best way to create this loop?

Thank you!

dplyr loops r
2 Answers

Using data.table:

# example data
dt <- mtcars[1:10, c("cyl", "disp", "mpg")]

setDT(dt)[ order(mpg), c("cat1", "cat2") := lapply(1:2, function(i) shift(disp, i, type = "lead")), by = cyl ]

dt
#     cyl  disp  mpg  cat1  cat2
#  1:   6 160.0 21.0 160.0 258.0
#  2:   6 160.0 21.0 258.0    NA
#  3:   4 108.0 22.8 140.8 146.7
#  4:   6 258.0 21.4    NA    NA
#  5:   8 360.0 18.7    NA    NA
#  6:   6 225.0 18.1 167.6 160.0
#  7:   8 360.0 14.3 360.0    NA
#  8:   4 146.7 24.4    NA    NA
#  9:   4 140.8 22.8 146.7    NA
# 10:   6 167.6 19.2 160.0 160.0
zx87540

We can create the desired lead functions with map and partial from purrr, and apply them all at once with the mutate(across()) pattern.

library(purrr)
library(dplyr)

df %>% 
  group_by(id) %>% 
  mutate(across(
    category, 
    map(1:7, ~partial(lead, n = .x, order_by = date)),
    .names = "{.col}{.fn}"
  ))

# A tibble: 100 × 10
# Groups:   id [4]
      id category date       category1 category2 category3 category4 category5
   <int> <chr>    <date>     <chr>     <chr>     <chr>     <chr>     <chr>    
 1     1 B        2022-01-02 B         B         C         B         C        
 2     1 B        2022-01-03 B         C         B         C         C        
 3     1 B        2022-01-05 C         B         C         C         C        
 4     1 C        2022-01-06 B         C         C         C         B        
 5     1 B        2022-01-10 C         C         C         B         C        
 6     1 C        2022-01-10 C         C         B         C         A        
 7     1 C        2022-01-10 C         B         C         A         C        
 8     1 C        2022-01-11 B         C         A         C         B        
 9     1 B        2022-01-12 C         A         C         B         B        
10     1 C        2022-01-17 A         C         B         B         A        
11     1 A        2022-01-18 C         B         B         A         B        
12     1 C        2022-01-20 B         B         A         B         A        
13     1 B        2022-01-25 B         A         B         A         C        
14     1 B        2022-01-25 A         B         A         C         C        
15     1 A        2022-01-25 B         A         C         C         C        
16     1 B        2022-01-25 A         C         C         C         C        
17     1 A        2022-01-26 C         C         C         C         B        
18     1 C        2022-01-27 C         C         C         B         A        
19     1 C        2022-01-30 C         C         B         A         B        
20     1 C        2022-02-01 C         B         A         B         C        
21     1 C        2022-02-04 B         A         B         C         C        
22     1 B        2022-02-06 A         B         C         C         A        
23     1 A        2022-02-06 B         C         C         A         C        
24     1 B        2022-02-06 C         C         A         C         NA       
25     1 C        2022-02-11 C         A         C         NA        NA       
26     1 C        2022-02-12 A         C         NA        NA        NA       
27     1 A        2022-02-12 C         NA        NA        NA        NA       
28     1 C        2022-02-14 NA        NA        NA        NA        NA       
29     2 B        2022-01-06 B         C         C         A         A        
# … with 71 more rows, and 2 more variables: category6 <chr>, category7 <chr>

(Where df is sample data defined as:

n <- 100
set.seed(123) ; df <- data.frame(
  id = sample(1:4, n, replace = TRUE),
  category = sample(LETTERS[1:3], n, replace = TRUE),
  date = sample(seq(as.Date("2022-01-01"), by = "1 day", length.out = 45), n,
                replace = TRUE)
)
df <- df %>% arrange(id, date)
Aurèle0
Related