Check the Col Header values in Table1 and fill the Table2 Cost col-CodePudding

Compare the Weight value in Table2 with Headers in Table1 if it matches then pull the respective value into Cost col of Table2.

Table 1:
structure(list(`0` = c("SYD", "MEL", "BRIS"), `1` = c(0.29, 0.4, 
0.46), `3` = c(0.4, 0.29, 0.87), `5` = c(0.46, 0.85, 0.29)), class = "data.frame", row.names = c(NA, 
-3L))

Table2:
structure(list(FROM = c("SYD", "SYD", "SYD", "MEL", "MEL"), Weight = c(1L, 
3L, 4L, 1L, 5L)), class = "data.frame", row.names = c(NA, -5L
))

Expected output:

FROM    Weight  COST
SYD        1    0.29
SYD        3    0.4
SYD        4    ?
MEL        1    ?
MEL        5    ?

In Excell i got this is a simple IF & VLOOKUP. How can I do this in R.

Thanks Raul

CodePudding user response：

You may try

library(janitor)
library(dplyr)
library(reshape2)

table3 <- table1 %>% 
  reshape2::melt(id = '0',variable.name = "Weight") %>%
  dplyr::rename('FROM' = '0') %>%
  mutate(Weight = as.numeric(Weight))

table2 %>% 
  left_join(table3, by = c("FROM", "Weight"))

  FROM Weight value
1  SYD      1  0.29
2  SYD      3  0.40
3  SYD      4    NA
4  MEL      1  0.40
5  MEL      5  0.85

CodePudding user response：

It is almost the same as @Park, but slightly different using pivot_longer and in one dplyr chain:

library(dplyr)
library(tidyr)
table_1 %>%
  pivot_longer(!`0`, names_to = "Weight", values_to = "value") %>%
  dplyr::rename('FROM' = '0') %>%
  mutate(Weight = as.numeric(Weight)) %>%
  right_join(., table_2, by = c("FROM", "Weight"))

Output:

# A tibble: 5 × 3
  FROM  Weight value
  <chr>  <dbl> <dbl>
1 SYD        1  0.29
2 SYD        3  0.4 
3 MEL        1  0.4 
4 MEL        5  0.85
5 SYD        4 NA