Looking up values from one column in data frame to another column

[piku9290dgp]

Hello everyone,

I am trying to mimic excel vlookup in R. Basically, I have a data frame with columns( order date, Order Qty, Order to Ship days, Shipping Date, Shipping Qty). My order date and shipping date are continuous date variable. I have to fill the column shipping qty on the shipping date, looking up the order qty on that correspoding order date.

OrderDate OrderQty ORder2Ship Days Shipping Date shipping qty
2021-02-16 10 2 2021-02-16 0
2021-02-17 12 2 2021-02-17 0
2021-02-18 8 2 2021-02-18 10
2021-02-19 9 2 2021-02-19 12

[technocrat]

dat <- structure(list(OrderDate = structure(c(18674, 18675, 18676, 18677), class = "Date"), OrderQty = c(10, 12, 8, 9), ORder2Ship_Days = c(
  2,
  2, 2, 2
), Shipping_Date = structure(c(18674, 18675, 18676, 18677), class = "Date"), shipping_qty = c(0, 0, 10, 12)), class = c(
  "spec_tbl_df",
  "tbl_df", "tbl", "data.frame"
), row.names = c(NA, -4L), spec = structure(list(
  cols = list(OrderDate = structure(list(format = ""), class = c(
    "collector_date",
    "collector"
  )), OrderQty = structure(list(), class = c(
    "collector_double",
    "collector"
  )), ORder2Ship_Days = structure(list(), class = c(
    "collector_double",
    "collector"
  )), Shipping_Date = structure(list(format = ""), class = c(
    "collector_date",
    "collector"
  )), shipping_qty = structure(list(), class = c(
    "collector_double",
    "collector"
  ))), default = structure(list(), class = c(
    "collector_guess",
    "collector"
  )), skip = 1L
), class = "col_spec"))


dat
#> # A tibble: 4 x 5
#>   OrderDate  OrderQty ORder2Ship_Days Shipping_Date shipping_qty
#>   <date>        <dbl>           <dbl> <date>               <dbl>
#> 1 2021-02-16       10               2 2021-02-16               0
#> 2 2021-02-17       12               2 2021-02-17               0
#> 3 2021-02-18        8               2 2021-02-18              10
#> 4 2021-02-19        9               2 2021-02-19              12

for (i in seq_along(dat)) ifelse(dat[i, 4] == dat[i, 1], dat[i, 5] <- dat[i, 2], dat[i, 2] <- dat[i, 2])

dat
#> # A tibble: 4 x 5
#>   OrderDate  OrderQty ORder2Ship_Days Shipping_Date shipping_qty
#>   <date>        <dbl>           <dbl> <date>               <dbl>
#> 1 2021-02-16       10               2 2021-02-16              10
#> 2 2021-02-17       12               2 2021-02-17              12
#> 3 2021-02-18        8               2 2021-02-18               8
#> 4 2021-02-19        9               2 2021-02-19               9

[nirgrahamuk]

I think this is a left_join

# the set up
(order_df <- structure(list(
  OrderDate = structure(c(18674, 18675, 18676, 18677), class = "Date"), 
  OrderQty = c(10, 12, 8, 9),
  ORder2Ship_Days = c(2,  2, 2, 2)),
  row.names = c(NA, -4L), 
  class = c("tbl_df", "tbl", "data.frame"
)))

(ship_df <- structure(list(
  Shipping_Date = structure(c(18674, 18675, 18676, 18677), class = "Date")),
  row.names = c(NA, -4L), 
  class = c("tbl_df","tbl", "data.frame")))

#answer
left_join(order_df,ship_df,
          by=c("OrderDate"="Shipping_Date"),keep=TRUE) %>% 
  mutate(ShipQty=OrderQty)