CODEWITHSUNDEEP
rdataframematch
apples-oranges
apples-oranges

Reputation: 987

Match values of column based on other column per groups

I have a data frame where I would like to match the Score values of ID to New.Score.

The ID 123 has score 5 in group 1, but in group 2 it has score 1. And I only want to use the scores that appear within each group.

This is my df:

  Group  ID Score New.ID New.Score
     1 123     5    456         
     1 456     1    789         
     1 789     0    123         
     2 555     1    999         
     2 123     1    123         
     2 999     0    555

And my desired output:

  Group  ID Score New.ID New.Score
     1 123     5    456         1
     1 456     1    789         0
     1 789     0    123         5
     2 555     1    999         0
     2 123     1    123         1
     2 999     0    555         1

I tried with ave:

mtch <- function(x) {
  dt[match(x,dt$ID),"Score"]  
}

dt$New.Score <- ave(dt$New.ID, dt$Group, FUN = mtch)

But it gives me NA values.

Code for df creation:

Group <- as.factor(c(1, 1, 1, 2, 2, 2))
ID <- as.factor(c(123,456,789, 555, 123, 999))
Score <- c(5,1,0, 1,1,0)
dt <- data.frame(Group, ID, Score, New.ID)

Upvotes: 2

Views: 456

Answers (2)

Mike H.
Mike H.

Reputation: 14360

You can use a similar approach as in my previous answer (Set values of column based on other column), but this time use an interaction in the matching. Something like this:

dt$New.Score <- dt[match(interaction(dt$Group,dt$New.ID) , 
                         interaction(dt$Group,dt$ID)), "Score"]

   #   Group  ID Score New.ID New.Score
   # 1     1 123     5    456         1
   # 2     1 456     1    789         0
   # 3     1 789     0    123         5
   # 4     2 555     1    999         0
   # 5     2 123     1    123         1
   # 6     2 999     0    555         1

Data:

Group <- as.factor(c(1, 1, 1, 2, 2, 2))
ID <- as.factor(c(123,456,789, 555, 123, 999))
Score <- c(5,1,0, 1,1,0)
New.ID <- as.factor(c(456, 789, 123, 999, 123, 555))
dt <- data.frame(Group, ID, Score, New.ID)

Upvotes: 1

akrun
akrun

Reputation: 887118

We can use data.table. Convert the 'data.frame' to 'data.table' (setDT(dt)), grouped by 'Group', match the 'New.ID' with 'ID' to get the numeric index and use that to rearrange the 'Score' and assign (:=) it to 'New.Score' 

library(data.table)
setDT(dt)[, New.Score := Score[match(New.ID, ID)], Group]
dt
#    Group  ID Score New.ID New.Score
#1:     1 123     5    456         1
#2:     1 456     1    789         0
#3:     1 789     0    123         5
#4:     2 555     1    999         0
#5:     2 123     1    123         1
#6:     2 999     0    555         1

Upvotes: 2

Related Questions