replace each value of three columns of a data frame with the value of a column in a second data frame in R

Question

I have been looking to your posts talking about te replacement of a value of a column with one of a second column base on conditions, but they were no working.

I will try to simplify the two data frame I have:

> paircomparison 
V1 V2 V3 V4

A1 A1 A2 A2
A1 A1 A3 A3
A1 A1 A4 A4
A2 A2 A1 A1
A2 A2 A3 A3
A2 A2 A4 A4
A3 A3 A1 A1 
A3 A3 A2 A2
A3 A3 A4 A4
A4 A4 A1 A1
A4 A4 A2 A2
A4 A4 A3 A3

>Ids

V1 V2
A1 London
A2 Roma
A3 Paris
A4 New York

I wanted to replace each time the value in paircomparison[,1:4] == the values in Ids$V1 with the value of Ids$V2.

I have been trying the following for loop:

for (x in paircomparison[,1:4]){
    if (x == Ids[,1]){
        x <- Ids[,2])}
}

but it is not working. I'm pretty new with R script and most probably I'm missing some steps in the script.

score 0 · Accepted Answer · answered Jan 16 '17 at 05:33

We can try

paircomparison[] <- lapply(paircomparison, function(x) setNames(Ids$V2, Ids$V1)[x])
paircomparison
#         V1       V2       V3       V4
#1    London   London     Roma     Roma
#2    London   London    Paris    Paris
#3    London   London New York New York
#4      Roma     Roma   London   London
#5      Roma     Roma    Paris    Paris
#6      Roma     Roma New York New York
#7     Paris    Paris   London   London
#8     Paris    Paris     Roma     Roma
#9     Paris    Paris New York New York
#10 New York New York   London   London
#11 New York New York     Roma     Roma
#12 New York New York    Paris    Paris

Or just

paircomparison[] <- setNames(Ids$V2, Ids$V1)[as.matrix(paircomparison)]

score 0 · Answer 2 · answered Jan 16 '17 at 06:24

We can unlist the dataframe and use match to compare the values

paircomparison[] <- Ids$V2[match(unlist(paircomparison), Ids$V1)]
paircomparison

#       V1      V2      V3      V4
#1   London  London    Roma    Roma
#2   London  London   Paris   Paris
#3   London  London NewYork NewYork
#4     Roma    Roma  London  London
#5     Roma    Roma   Paris   Paris
#6     Roma    Roma NewYork NewYork
#7    Paris   Paris  London  London
#8    Paris   Paris    Roma    Roma
#9    Paris   Paris NewYork NewYork
#10 NewYork NewYork  London  London
#11 NewYork NewYork    Roma    Roma
#12 NewYork NewYork   Paris   Paris

replace each value of three columns of a data frame with the value of a column in a second data frame in R

2 Answers2