Merging 2 datasets by calling on the row numbers (without using merge() or lookup functions)

user_11116340 0
Viewed 80

Hi This is a problem that I run into often in R programing and am in need of simple solution from this community. In sort, the problem requires a lookup value to be returned to a dataframe. I would like to call on the rownumber of the lookup table

> x1 <- c(2, 3, 1, 5, 4) 
> x2 <- c("a", "b", "c", "d", "e")
> 
> set.seed(5) 
> x3 <- round(runif (10, 1, 5))
> 
> lookup.df <- data.frame(x1, x2)
> Data.df <- data.frame(x3)
> lookup.df
  x1 x2
1  2  a
2  3  b
3  1  c
4  5  d
5  4  e
> Data.df
   x3
1   2
2   4
3   5
4   2
5   1
6   4
7   3
8   4
9   5
10  1

Data.df$x2 <- df1 [ (matching row numbers from Data.df with lookup.df$x1) , 2 ]

In theory, the code should be able to generate a list that would look like

rows <- c(1, 5, 4, 1, 3, 5, 2, 5, 4, 3)

so that the following would result

> Data.df$x2 <- df1 [ rows , 2 ]
> Data.df
   x3 x2
1   2  a
2   4  e
3   5  d
4   2  a
5   1  c
6   4  e
7   3  b
8   4  e
9   5  d
10  1  c

I appreciate an ideas. Thanks.

r lookup merge
2 Answers

We can use a named vector to match

Data.df$x2 <- setNames(lookup.df$x2, lookup.df$x1)[as.character(Data.df$x3)]

-output

> Data.df
   x3 x2
1   2  a
2   4  e
3   5  d
4   2  a
5   1  c
6   4  e
7   3  b
8   4  e
9   5  d
10  1  c
user_37322710

You may use match function -

Data.df$x2 <- lookup.df$x2[match(Data.df$x3, lookup.df$x1)]

#   x3 x2
#1   2  a
#2   4  e
#3   5  d
#4   2  a
#5   1  c
#6   4  e
#7   3  b
#8   4  e
#9   5  d
#10  1  c

From the title of the post I understand that you don't want to use merge function but that would be the most straightforward solution.

merge(lookup.df, Data.df, by.x = 'x1', by.y = 'x3')
user_39629140
Related