I would start by getting this into a tidy format i.e. where every variable is a column and every observation is a row. In your student_score dataset, I see three variables: year, age, and score. My opinion is that it would be a lot easier to work with this dataset if it were shaped something like this:
library(dplyr)
library(tidyr)
library(stringr)
student_score <- data.frame(
subjectid = c("a","b","c","d","e","f","g","h","i","j","k"),
age1 = c(27,18,28,14,11,32,20,19,12,23,34),
age2 = c(28,19,29,15,12,33,21,20,13,24,35),
age3 = c(29,20,30,16,13,34,22,21,14,25,36),
age4 = c(30,21,31,17,14,35,23,22,15,26,37),
age5 = c(31,22,32,18,15,36,24,23,16,27,38),
age6 = c(32,23,33,19,16,37,25,24,17,28,39),
age7 = c(33,24,34,20,17,38,26,25,18,29,40),
age8 = c(34,25,35,21,18,39,27,26,19,30,41),
age9 = c(35,26,36,22,19,40,28,27,20,31,42),
score1 = c(1,4,2,1,1,0,0,1,0,0,0),
score2 = c(1,0,0,1,0,0,2,1,0,2,0),
score3 = c(1,5,2,1,0,0,1,1,1,0,0),
score4 = c(1,1,0,1,2,0,1,1,5,1,1),
score5 = c(0,4,2,1,0,0,0,1,1,1,1),
score6 = c(1,2,0,1,0,2,1,1,0,1,0),
score7 = c(1,1,0,0,1,2,1,0,0,1,1),
score8 = c(1,1,0,0,2,5,1,0,0,3,1),
score9 = c(1,1,3,0,1,0,1,4,5,1,1))
tidy_scores <- student_score %>%
pivot_longer(cols = -subjectid) %>%
mutate(
year = str_sub(name, start = nchar(name)) %>% #Extract the year from the original variable name and encode it as its own variable
as.numeric,
name = str_sub(name, start = 1, end = nchar(name) - 1) # remove the year from the name since it now has the 'year' column
) %>%
pivot_wider(
id_cols = c(subjectid, year),
names_from = name,
values_from = value
)
tidy_scores
#> # A tibble: 99 x 4
#> subjectid year age score
#> <fct> <dbl> <dbl> <dbl>
#> 1 a 1 27 1
#> 2 a 2 28 1
#> 3 a 3 29 1
#> 4 a 4 30 1
#> 5 a 5 31 0
#> 6 a 6 32 1
#> 7 a 7 33 1
#> 8 a 8 34 1
#> 9 a 9 35 1
#> 10 b 1 18 4
#> # ... with 89 more rows
From this comes a clear use for dplyr::case_when:
tidy_scores %>%
mutate(
score = case_when(
age < 20 ~ 0,
T ~ score
)
)
#> # A tibble: 99 x 4
#> subjectid year age score
#> <fct> <dbl> <dbl> <dbl>
#> 1 a 1 27 1
#> 2 a 2 28 1
#> 3 a 3 29 1
#> 4 a 4 30 1
#> 5 a 5 31 0
#> 6 a 6 32 1
#> 7 a 7 33 1
#> 8 a 8 34 1
#> 9 a 9 35 1
#> 10 b 1 18 0
#> # ... with 89 more rows
Hopefully that helps!