This is one way to do it
library(tidyverse)
# Sample data in a copy/paste friendly format, replace this with your own data frame
sample_df <- data.frame(
stringsAsFactors = FALSE,
ID = c("001", "002", "003", "004", "005"),
Current_Age = c(75, 38, 29, 45, 47),
Response_2015 = c("Yes", "No", "Yes", "Yes", "No"),
Response_2010 = c("No", "No", "Yes", NA, "Yes"),
Response_2007 = c("Yes", "No", "Yes", "No", NA),
Response_2005 = c("Yes", "Yes", NA, "No", "No")
)
# Relevant code
sample_df %>%
pivot_longer(cols = starts_with("Response"),
names_to = "Year",
names_pattern = "Response_(.+)",
names_transform = list(Year = as.integer),
values_to = "Response") %>%
group_by(ID) %>%
mutate(Response_Age = Current_Age - (max(Year)-Year)) %>%
arrange(desc(Year), ID)
#> # A tibble: 20 × 5
#> # Groups: ID [5]
#> ID Current_Age Year Response Response_Age
#> <chr> <dbl> <int> <chr> <dbl>
#> 1 001 75 2015 Yes 75
#> 2 002 38 2015 No 38
#> 3 003 29 2015 Yes 29
#> 4 004 45 2015 Yes 45
#> 5 005 47 2015 No 47
#> 6 001 75 2010 No 70
#> 7 002 38 2010 No 33
#> 8 003 29 2010 Yes 24
#> 9 004 45 2010 <NA> 40
#> 10 005 47 2010 Yes 42
#> 11 001 75 2007 Yes 67
#> 12 002 38 2007 No 30
#> 13 003 29 2007 Yes 21
#> 14 004 45 2007 No 37
#> 15 005 47 2007 <NA> 39
#> 16 001 75 2005 Yes 65
#> 17 002 38 2005 Yes 28
#> 18 003 29 2005 <NA> 19
#> 19 004 45 2005 No 35
#> 20 005 47 2005 No 37
Created on 2021-10-27 by the reprex package (v2.0.1)