Combining multiple columns to new column based on another column

Question

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;

Col1	Col2	Col3	Col4	Col5	Week
A	D	G	J	M	3
B	E	H	K	N	2
C	F	H	L	O	5

Desired output;

Col1	Col2	Col3	Col4	Col5	Week	want
A	D	G	J	M	3	G
B	E	H	K	N	2	E
C	F	H	L	O	5	O

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]

Reproducible example data:

df <- as.data.frame(matrix(LETTERS[1:(3 * 5)], 3))
names(df) <- paste0("Col", 1:5)
df$Week <- c(3, 2, 5)

Answer 1

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