How to assign value to last row of group in R?

January 22, 2024

Similar to this question, I want to select the last row of each group and assign a value to it.

 a <- data.frame("ID" = c("A", "A", "B", "B", "C", "C"),
                 "NUM" = c(1, 2, 4, 3, 6, 9),
                 "VAL" = c(1, 0, 1, 0, 1, 0))
 
 ID NUM VAL
  A   1   1
  A   2   0
  B   4   1
  B   3   0
  C   6   1
  C   9   0

My desired output:

 ID NUM VAL end.spell
  A   1   1         0
  A   2   0         1
  B   4   1         0
  B   3   0         1
  C   6   1         0
  C   9   0         1

How do I do this? Any help is appreciated.

>Solution :

Using group_by() from dplyr, you could identify cases where the row number is the same as the number of rows for that group and give a 1 for that observation and zero otherwise.

library(dplyr)
a <- data.frame("ID" = c("A", "A", "B", "B", "C", "C"),
                "NUM" = c(1, 2, 4, 3, 6, 9),
                "VAL" = c(1, 0, 1, 0, 1, 0))


a %>% 
  group_by(ID) %>% 
  mutate(end.spell = ifelse(row_number() == n(), 1,0))
#> # A tibble: 6 × 4
#> # Groups:   ID [3]
#>   ID      NUM   VAL end.spell
#>   <chr> <dbl> <dbl>     <dbl>
#> 1 A         1     1         0
#> 2 A         2     0         1
#> 3 B         4     1         0
#> 4 B         3     0         1
#> 5 C         6     1         0
#> 6 C         9     0         1

^{Created on 2024-01-22 with reprex v2.0.2}