Combining multiple columns to new column based on another column

I have a dataframe with multiple columns describing a persons data each week and a column describing their reference week. I would like to create a new variable containing the value corresponding to their reference week.

Example data;

Desired output;

So far, I have achived to extract the variable names corresponding to the reference week but I can’t figure out how to insert the corresponding value into the "want" variable.

dfnames <- c("Col1", "Col2", "Col3", "Col4", "Col5") 
wantname <- dfnames[df$week]

Thanks in advance

>Solution :

You can get the values from your target column.

library(dplyr)

df %>% rowwise() %>% mutate(want = get(paste0("Col", Week))) %>% ungroup()

# A tibble: 3 × 7
  Col1  Col2  Col3  Col4  Col5   Week want 
  <chr> <chr> <chr> <chr> <chr> <int> <chr>
1 A     D     G     J     M         3 G    
2 B     E     H     K     N         2 E    
3 C     F     H     L     O         5 O 

Or base R with sapply to iterate through rows and do basic index []:

df$want <- sapply(1:nrow(df), \(x) df[x, paste0("Col", df$Week[x])])
df
  Col1 Col2 Col3 Col4 Col5 Week want
1    A    D    G    J    M    3    G
2    B    E    H    K    N    2    E
3    C    F    H    L    O    5    O