I have two data frames, x and y. For each value of x[,2], I look if the value is equal to the value of the elements of y[,1]. If so, I add a third column in the first data frame that contains the values of y[,2].
I managed to do that with loops, but how can I do this using vectors?
x=data.frame(1:15,15:1)
y=data.frame(3:5,c(7.2,8.5,0.3))
for ( i in 1:nrow(x)) {
for (j in 1:nrow(y)) {
if (x[i,2]==y[j,1]){
x[i,3]=y[j,2]
}else{
}
}
}
Use a join instead of loops - based on the loop comparision, the second column of 'x' is compared with the first column of 'y', thus those columns are used in the on, assign (:=) the second column (col2) from the second dataset to create the new column 'col3' in first data
library(data.table)
setDT(x)[y, col3 := i.col2, on = .(col2 = col1)]
-output
> x
col1 col2 col3
1: 1 15 NA
2: 2 14 NA
3: 3 13 NA
4: 4 12 NA
5: 5 11 NA
6: 6 10 NA
7: 7 9 NA
8: 8 8 NA
9: 9 7 NA
10: 10 6 NA
11: 11 5 0.3
12: 12 4 8.5
13: 13 3 7.2
14: 14 2 NA
15: 15 1 NA
x <- data.frame(col1 = 1:15, col2 = 15:1)
y <- data.frame(col1 = 3:5, col2 = c(7.2,8.5,0.3))
Update: Many thanks to @TrainingPizza (who has drawn my attention to the false output of my first answer and also provided how it could work:
library(dplyr)
x %>%
rowwise() %>%
mutate(col3 = ifelse(col2 %in% y$col1, y$col2[y$col1==col2], NA))
col1 col2 col3
<int> <int> <dbl>
1 1 15 NA
2 2 14 NA
3 3 13 NA
4 4 12 NA
5 5 11 NA
6 6 10 NA
7 7 9 NA
8 8 8 NA
9 9 7 NA
10 10 6 NA
11 11 5 0.3
12 12 4 8.5
13 13 3 7.2
14 14 2 NA
15 15 1 NA
First answer (not correct)
Here is dplyr way how to avoid the for - loop:
library(dplyr)
x %>%
mutate(V3 = ifelse(V2 %in% y$V1, y$V2, NA))
V1 V2 V3
1 1 15 NA
2 2 14 NA
3 3 13 NA
4 4 12 NA
5 5 11 NA
6 6 10 NA
7 7 9 NA
8 8 8 NA
9 9 7 NA
10 10 6 NA
11 11 5 8.5
12 12 4 0.3
13 13 3 7.2
14 14 2 NA
15 15 1 NA