Clever Geek Handbook

R: Add data from row to column with conditions

I have two data frames,

df1

number    x_1    y_1
1         a      a
2         a      b
3         b      b 
4         c      b
5         c      c

df2

number_1  x_1  y_1  number_2  x_2  y_2
1                   2
1                   3
1                   4
1                   5
2                   3
2                   4
2                   5
3                   4
3                   5
4                   5

I want to add data to columns x_1, y_1, x_2, y_2 based on whether number_1 and number_2 match the numbers in number column in df1. So in the first two lines of df2, I must have:

df2

number_1  x_1  y_1  number_2  x_2  y_2
1         a    a    2         a    b 
1         a    a    3         b    b

I've tried something like this but it doesn't work and it takes a very long time with an extremely huge dataset:

for(i in 1:nrow(df1))
  for(j in 1:nrow(df2))
    if(df2$number_1[j] == df1$number[i])
      df2$x_1[j] = df1$x_1[i]
      df2$y_1[j] = df1$y_1[i]
    if(df2$number_2[j] == df1$number_1[i])
      df2$x_1[j] = df1$x_1[i]
      df2$y_1[j] = df$y_1[i]

I am a beginner, so I would really appreciate if you can explain the steps you are suggesting. Thank you very much.

+3


source to share


1 answer


Try either merge

or left_join

fromdplyr 

merge(df1,
     merge(df1,df2[,c(1,4)], by.x='number', by.y='number_1'),
        by.x='number', by.y='number_2')

or



library(dplyr)
left_join(
    left_join(df2[c(1,4)], df1, by=c('number_1'='number')) ,
        df1, by=c('number_2'='number'))

Or, as @David Arenburg mentioned in the comments, match

can be used here.

df2[c("x_1", "y_1")] <- df1[match(df2$number_1, df1$number), 2:3]
df2[c("x_2", "y_2")] <- df1[match(df2$number_2, df1$number), 2:3]
+1


source






More articles:


All Articles