How do I extract value of a cell with the row and column numbers from a dataframe in R and then create a separate dataframe with the extracted values?

My data are in a dataframe like this:

I have a dataframe with more than 10,000 rows and 14 columns (example attached in text using 8 columns). I indexed the row and column number from which I need to extract the value. The "Cl" column includes the column number (also id column) from which I would like to extract that value for each row. For example, from the first row I would like to extract the value “30” from the “D” column and also would like to keep the id column (P_1). Column “B” from the 2nd row with id P_2.

New dataset would be like,

How do I write the code in a way in R that would allow me to extract the value of a cell with the row and column number for my whole dataset? Any help would be appreciated.

You could do it as follows. First, make the data:

dat <- structure(list(id = c("p_1", "P_2", "P_3", "P_4", "P_5", "P_6", 
"P_7", "P_8", "P_9", "P_10"), A = c(23, 25, 23, 27, 30, 27, 23, 
25, 27, 30), B = c(25, 27, 25, 27, 30, 23, 24, 26, 28, 30), C = c(27, 
30, 27, 23, 25, 27, 30, 23, 16, 9), D = c(30, 23, 30, 13, 16, 
19, 22, 25, 28, 31), E = c(23, 34, 23, 25, 27, 30, 23, 24, 26, 
28)), row.names = c(NA, -10L), class = "data.frame")

You can define the row index and column names you want to extract. Below we are saying that take column "D" for row 1 and column "B" for row 2.

row_index <- c(1,2)
col_values <- c("D", "B")

Next, you need to make a new data frame where the id value just comes from the row_index values of id. The value column comes from the indicated column. You could even have it save the column from which the number came as column:

data.frame(id = dat$id[row_index], 
           value = dat[cbind(row_index, match(col_values, names(dat)))], 
           column = col_values)
#>    id value column
#> 1 p_1    30      D
#> 2 P_2    27      B

^{Created on 2023-08-09 with reprex v2.0.2}