R Access values from different dataframe dependent on an ID value and an item and append column to first dataframe

Question

I am looking for a way to solve the following question: My first dataframe contains ratings, e.g., two subjects (subject ID 1 and 2) rate two items on a discrete scale.

ratings  <- data.frame(ID=c(1, 1, 2, 2), item=c(1, 2, 1, 2), rating=c(1, -4, 3, 2))

this produces the following data frame:

 ID item rating
  1    1      1
  1    2     -4
  2    1      3
  2    2      2

then I have a choice data frame, e.g., two subjects choose between 2 items.

choice  <- data.frame(ID=c(1, 1, 2, 2), item_L=c(1, 2, 1, 2), 
                      item_R=c(2,1,2,1), choice_item_Left=c(0,1,1,0))

This produces the following data frame:

 ID item_L item_R choice_item_Left
  1      1      2             0
  1      2      1             1
  2      1      2             1
  2      2      1             0

My problem is now the following: I want to access the ratings dataframe and use the ratings for left and right item as a new column in the choice dataframe, depending on subjectID and item number. So I need two new columns in the choice dataframe, namely rating_item_L, and rating_item_R with values dependent on the rating dataframe and the ID in the rating dataframe.

an exaple dataframe would look like this:

ID item_1 item_2   choice_item_Left  rating_item_L rating_item_R
1  1      1      2             0             1            -4
2  1      2      1             1            -4             1
3  2      1      2             1             3             2
4  2      2      1             0             2             3 

Critically, I have more choices than ratings, and the ratings are in order (e.g., from 1 to 20), but the choices are not in order. So there are choices like item 3 vs 9 or 2 vs 8.

Does anybody know a solution?

Answer 1

You can use merge like this.

## merge left items
xx= merge(ratings,choice,by.x=c('ID','item'),by.y=c('ID','item_L'))
## merge right data
yy = merge(ratings,choice,by.x=c('ID','item'),by.y=c('ID','item_R'))
## bind left and right data 
res <- merge(xx,yy,by=c('ID','item'))
#    ID item rating.x item_R choice_item_Left.x rating.y item_L choice_item_Left.y
# 1  1    1        1      2                  0        1      2                  1
# 2  1    2       -4      1                  1       -4      1                  0
# 3  2    1        3      2                  1        3      2                  0
# 4  2    2        2      1                  0        2      1                  1

Of course you can rearrange columns and rename them to get the exact output.

setNames(res[,c("ID","item_R","item_L","choice_item_Left.x","rating.x","rating.y")],
         c("ID","item_1","item_2","choice_item_Left","rating_item_L","rating_item_R"))

#    ID item_1 item_2 choice_item_Left rating_item_L rating_item_R
# 1  1      2      2                0             1             1
# 2  1      1      1                1            -4            -4
# 3  2      2      2                1             3             3
# 4  2      1      1                0             2             2