Nested Match Functions in R

user_11029098 0
Viewed 102

This is a follow up to How to lookup and sum multiple columns in R :

I have 3 data frames as such:

Groups:

P1          P2          P3          P4      
"Joe"       "Sally"     "A.J."      "Mary"  
"Cory"      "Joe"       "Sally"     "Katy"

Names:

ID      NAME
123     "Joe"
213     "Sally"
312     "A.J."
231     "Mary"
345     "Cory"
567     "Katy"

Individual_Results:

ID      SCORE
123     23
213     12
312     11
231     19
345     10
567     22

My goal is to create a new column in Groups with a SCORE column that is a sum of each of the results in the group

P1          P2          P3          P4          SCORE
"Joe"       "Sally"     "A.J."      "Mary"      65

Following the example of the answer in the referenced question above, I've attempted the following

groups$score = apply(groups, 1, function(x){
    sum(Individual_Results$SCORE[match(match(x, Names$Name), Individual_Results$ID)])
    })

Unfortunately the result is creating the new column, but the result is NA for every score.

If I am understanding how to use both the apply and match correctly, what I am trying to do is apply the function to each row, passing x (the Name) as the parameter to the first match function in order to get the ID and then matching the returned ID to the second match to get the score - summing all the scores per row.

I think I am super close, but just not quite there. Appreciate any help!

dplyr r
1 Answers

This will do the trick.

Note that you don't need data.table. I just used it to make the example reproducible

require(data.table)

Groups <- data.frame(fread('"P1","P2","P3","P4"
"Joe","Sally","A.J.","Mary"
"Cory","Joe","Sally","Katy"'))

Names <- data.frame(fread('ID,NAME
123,"Joe"
213,"Sally"
312,"A.J."
231,"Mary"
345,"Cory"
567,"Katy"'))

Individual_Results <- data.frame(fread('ID,SCORE
123,23
213,12
312,11
231,19
345,10
567,22'))


Groups$SCORE <- apply(Groups, 1, function(x){
  sum(Individual_Results$SCORE[match(Names$ID[match(x, Names$NAME)], Individual_Results$ID)])
})


# Inspect groups:
Groups
#      P1    P2    P3   P4 SCORE
# 1  Joe Sally  A.J. Mary    65
# 2 Cory   Joe Sally Katy    67
user_24496560
Related