I have a dataframe with a one-to-many relationship where the one in the relationship are the column names and the many in the relationship are entries in the first column. I would like to create a vector or dataframe where the elements are selected from columns whose names match the entry in the first column. What follows is an example:
df <- data.frame(
id = c('A','A','A','B','B','C'),
A = c(1,2,3,4,5,6),
B = c(7,8,9,10,11,12),
C = c(13,14,15,16,17,18),
D = c(19,20,21,22,23,24)
)
The resulting vector or data frame should have the following elements:
1
2
3
10
11
18
I would like a second vector or dataframe (ideally the first and second are joined) which are the neighboring values to the right. The final results should therefore be
1 7
2 8
3 9
10 16
11 17
18 24
I am hoping for a solution which doesn't involve looping. Thank you for your help.
You can try
sapply(0:1, \(n, x=df[-1]) x[col(x) == match(df$id, names(x))+n])
or
t(mapply(\(i, j) t(df[i, j+0:1]), 1:nrow(df), match(df$id, names(df))))
Both gives
[,1] [,2]
[1,] 1 7
[2,] 2 8
[3,] 3 9
[4,] 10 16
[5,] 11 17
[6,] 18 24