How to perform vlookup with R

[ernijs12]

Hi reader,

I have 2 data sets the first:

tibble::tribble(
  ~Material,        ~Date, ~Amount,
      "saw", "01/01/2020",      1L,
    "nails", "02/01/2020",      2L,
   "hammer", "03/01/2020",      3L
  )

And the second one:

tibble::tribble(
  ~Material,        ~Date, ~Amount, ~safty.stock.level,
      "saw", "01/01/2020",      1L,                20L,
    "nails", "02/01/2020",      2L,                30L,
   "hammer", "03/01/2020",      3L,                40L,
      "saw", "04/02/2020",      4L,                20L,
      "saw", "05/02/2020",      5L,                20L,
     "nals", "06/07/2020",      2L,                30L,
   "hammer", "01/04/2020",      3L,                40L
  )

I need to perform a vlookup, but I cannot do it with excel because the data sets are too big for excel to manage.
I have to vlookup a safety stock level so that the material and date match.
This is how the first data set should look like:

tibble::tribble(
  ~Material,        ~Date, ~Amount, ~safty.stock,
      "saw", "01/01/2020",      1L,          20L,
    "nails", "02/01/2020",      2L,          30L,
   "hammer", "03/01/2020",      3L,          40L
  )

I know that this is an easy solution and probably doable with merge, but for some reason I cant find it or get it to work.

Huge thank you in advance!

[FJCC]

Yes, you can do this with merge(). In my code, I dropped the the third column, Amount, from the second data frame to avoid getting an extra column in the final data frame.

DF1 <- tibble::tribble(
  ~Material,        ~Date, ~Amount,
  "saw", "01/01/2020",      1L,
  "nails", "02/01/2020",      2L,
  "hammer", "03/01/2020",      3L
)

DF2 <- tibble::tribble(
  ~Material,        ~Date, ~Amount, ~safty.stock.level,
  "saw", "01/01/2020",      1L,                20L,
  "nails", "02/01/2020",      2L,                30L,
  "hammer", "03/01/2020",      3L,                40L,
  "saw", "04/02/2020",      4L,                20L,
  "saw", "05/02/2020",      5L,                20L,
  "nals", "06/07/2020",      2L,                30L,
  "hammer", "01/04/2020",      3L,                40L
)

DF1 <- merge(DF1, DF2[,-3], 
      by = c("Material", "Date"))
DF1
#>   Material       Date Amount safty.stock.level
#> 1   hammer 03/01/2020      3                40
#> 2    nails 02/01/2020      2                30
#> 3      saw 01/01/2020      1                20

^{Created on 2022-05-16 by the reprex package (v2.0.1)}

[ernijs12]

@FJCC

DF1 <- merge(DF1, DF2[,-3], 
      by = c("Material", "Date"))

Once again you have saved me many thanks!

[Scoco]

merge() is more powerful than vlookup because you can match on more than one column, and the matching columns don't need to be first in the big table.