R Extract values from data frame with vectors

I hope my question is not a duplicate, but I really couldn't find what I need. I dived in dplyr and even did the data.table course from DataCamp, but I can't fix it. Maybe overlooking something?

I have this df:

set.seed(1964)
df<-data.frame(id = c( "XEY", "NZH", "DYE", "JNF", "LHH", "WNB"),
q_1 = sample(5,6, replace = TRUE),
q_2 = sample(5,6, replace = TRUE),
q_3 = sample(5,6, replace = TRUE),
q_4 = sample(5,6, replace = TRUE),
q_5 = sample(5,6, replace = TRUE))


So my df looks like this:

Next I have two vectors with id's (row-indicators) and q's (question numbers) like these:

id <- c("XEY", "DYE", "JNF", "DYE", "XEY", "LHH", "WNB", "JNF" )
question <- c("val_1", "val_1","val_3","val_3","val_3", "val_4", "val_4", "val_5")


I want to extract the values from the df using the two vectors to get an output like this:

I have tried a lot of things to extract the values, but I keep getting errors. I have tried things like:

df[id == (id[1]), (question[1])]
df[id == id[1], question[1]]


The strange thing is that the data.table package seems to accept the 'row-notation'. Because:

df[id == (id[1]),]


gives back the valuea for row "XEY":

id q_1 q_2 q_3 q_4 q_5
1: XEY 5 1 5 4 1


But how to do the trick for the right column notation using my vector?

Sorry for this very basic question, appreciate your help.

id <- c("XEY", "DYE", "JNF", "DYE", "XEY", "LHH", "WNB", "JNF" )

question <- c("val_1", "val_1","val_3","val_3","val_3", "val_4", "val_4", "val_5")

value <- c(5, 1, 2, 1, 5, 2, 2, 3)

output <- as.data.frame(cbind(id, question, value))

2 Answers
2

You can do that with tidyr.
I add stringsAsFactors=FALSE to your df to avoid a warning during the join.

set.seed(1964)
df<-data.frame(id = c( "XEY", "NZH", "DYE", "JNF", "LHH", "WNB"),
q_1 = sample(5,6, replace = TRUE),
q_2 = sample(5,6, replace = TRUE),
q_3 = sample(5,6, replace = TRUE),
q_4 = sample(5,6, replace = TRUE),
q_5 = sample(5,6, replace = TRUE),stringsAsFactors=FALSE)
id <- c("XEY", "DYE", "JNF", "DYE", "XEY", "LHH", "WNB", "JNF" )
question <- c("q_1", "q_1","q_3","q_3","q_3", "q_4", "q_4", "q_5")

library(tidyr)
df2 <- data.frame(id,question,stringsAsFactors=FALSE)
df %>% gather(k,Value,-id) %>% inner_join(df2,by="id") %>% filter(question==k) %>% arrange(question) %>%
select(-k)


In base R you can do the following.

Value <- diag(as.matrix(df[match(id, df$id), sub("val", "q", question)]))
result <- data.frame(id, Question = sub("val", "q", question), Value)
result
# id Question Value
#1 XEY q_1 5
#2 DYE q_1 1
#3 JNF q_3 2
#4 DYE q_3 1
#5 XEY q_3 5
#6 LHH q_4 2
#7 WNB q_4 2
#8 JNF q_5 3


Edit.

After seeing the answer by @Nicolas2, with the use of inner_join, I have decided to give merge a try. But to do it I will need function melt from external package reshape2.

inner_join

merge

melt

reshape2

df2 <- data.frame(id, question = sub("val", "q", question))
df3 <- reshape2::melt(df, id.vars = "id")
names(df3)[2] <- "question"
result2 <- merge(df2, df3)
result2
# id question value
#1 DYE q_1 1
#2 DYE q_3 1
#3 JNF q_3 2
#4 JNF q_5 3
#5 LHH q_4 2
#6 WNB q_4 2
#7 XEY q_1 5
#8 XEY q_3 5


The result is the same, with a different row order.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.