Using group_by in a FOR loop to create lead variables

I need to create a set of lead variables based on several variables in my data set. While I can do this without using a loop, I'd much rather use a loop and greatly reduce the lines of code in my program.

When I run the code outside of the loop, it returns the exact result I'm looking for (see below). When I put the code into a FOR loop (see below), the code runs without errors and the lead variables are created, but its as if the group_by function is ignored. In addition, the names of the lead variables created by the loop code contain an extra instance of the field name on which they were based. I'd like to understand:

1. Why the group_by function doesn't work inside the FOR loop
2. Why the field names created by the loop code are different than I would expect

This code will provide sample data as well as illustrate the issue:

library(lubridate)
library(sqldf)
library(tidyverse)
library(dplyr)
all_data <- lakers
# creating a date field
all_data <- transform(all_data, date = as.Date(as.character(date), "%Y%m%d"))
# creating YEAR, MONTH, and YEARMONTH fields
all_data$CY <- year(all_data$date)
all_data$MONTH <- month(all_data$date)
all_data$MONTH <- formatC(all_data$MONTH, width = 2, format = "d", flag = "0")
all_data$MONTH <- paste0("M",all_data$MONTH)
all_data$CY_M <- paste0(all_data$CY, all_data$MONTH)
# creating a grouping field
all_data$GROUP_ID <- paste0(all_data$game_type,"_", all_data$etype)
# aggregating to the GROUP_ID, CY_M level
all_data_agg <- sqldf("SELECT GROUP_ID, SUM(points) points, CY_M
FROM all_data 
GROUP BY GROUP_ID, CY_M")
# sorting dataset in descending order by time
all_data_agg <- all_data_agg[order(all_data_agg$GROUP_ID,-xtfrm(all_data_agg$CY_M)),]
# creating lead variables - THIS CODE RUNS PERFECTLY
all_data_agg <- all_data_agg %>% 
group_by(GROUP_ID) %>%
mutate(points_LEAD1A = lead(points, n = 1, default = NA),
points_LEAD2A = lead(points, n = 2, default = NA),
points_LEAD3A = lead(points, n = 3, default = NA)
)
# creating lead variables with FOR loop - THIS CODE IGNORES THE GROUP_BY FUNCTION
fieldname_list <- c("points")
for (i in fieldname_list) {
all_data_agg <- all_data_agg %>%
group_by(GROUP_ID) %>%
mutate(!!paste0(as.character(i),"_LEAD1B") := !!lead(all_data_agg[as.character(i)], n = 1, default = NA),
!!paste0(as.character(i),"_LEAD2B") := !!lead(all_data_agg[as.character(i)], n = 2, default = NA),
!!paste0(as.character(i),"_LEAD3B") := !!lead(all_data_agg[as.character(i)], n = 3, default = NA)
)
}

An easier approach which takes account of the grouping and avoids a for loop would be to use dplyr::across:

library(dplyr)

fieldname_list <- c("points")

all_data_agg %>%
  group_by(GROUP_ID) %>%
  mutate(across(
    all_of(fieldname_list),
    list(
      LEAD1B = ~ lead(.x, n = 1, default = NA),
      LEAD2B = ~ lead(.x, n = 2, default = NA),
      LEAD3B = ~ lead(.x, n = 3, default = NA)
    )
  ))
#> # A tibble: 127 × 6
#> # Groups:   GROUP_ID [19]
#>    GROUP_ID        CY_M    points points_LEAD1B points_LEAD2B points_LEAD3B
#>    <chr>           <chr>    <int>         <int>         <int>         <int>
#>  1 away_ejection   2009M03      0            NA            NA            NA
#>  2 away_foul       2009M04      0             0             0             0
#>  3 away_foul       2009M03      0             0             0             0
#>  4 away_foul       2009M02      0             0             0             0
#>  5 away_foul       2009M01      0             0             0             0
#>  6 away_foul       2008M12      0             0             0            NA
#>  7 away_foul       2008M11      0             0            NA            NA
#>  8 away_foul       2008M10      0            NA            NA            NA
#>  9 away_free throw 2009M04    115           300           311           212
#> 10 away_free throw 2009M03    300           311           212           322
#> # ℹ 117 more rows

But if you insist on using a for loop then inside mutate you have to use sym() on the LHS and make e.g. use of the .data pronoun or !!sym() too on the RHS:

for (i in fieldname_list) {
  all_data_agg <- all_data_agg %>%
    group_by(GROUP_ID) %>%
    mutate(
      !!sym(paste0(as.character(i), "_LEAD1B")) := lead(.data[[i]], n = 1, default = NA),
      !!sym(paste0(as.character(i), "_LEAD2B")) := lead(.data[[i]], n = 2, default = NA),
      !!sym(paste0(as.character(i), "_LEAD3B")) := lead(.data[[i]], n = 3, default = NA)
    )
}

all_data_agg
#> # A tibble: 127 × 6
#> # Groups:   GROUP_ID [19]
#>    GROUP_ID        CY_M    points points_LEAD1B points_LEAD2B points_LEAD3B
#>    <chr>           <chr>    <int>         <int>         <int>         <int>
#>  1 away_ejection   2009M03      0            NA            NA            NA
#>  2 away_foul       2009M04      0             0             0             0
#>  3 away_foul       2009M03      0             0             0             0
#>  4 away_foul       2009M02      0             0             0             0
#>  5 away_foul       2009M01      0             0             0             0
#>  6 away_foul       2008M12      0             0             0            NA
#>  7 away_foul       2008M11      0             0            NA            NA
#>  8 away_foul       2008M10      0            NA            NA            NA
#>  9 away_free throw 2009M04    115           300           311           212
#> 10 away_free throw 2009M03    300           311           212           322
#> # ℹ 117 more rows