How to multiple join with 2 dataframe in R

Question

I want to join 2 tables which contains connected information. The dataframe are just simplified version, there's a lot more perimeters.

I have this dataframe:

data <- tribble(
  ~id,~perimeter1,~perimeter2,~perimeter3,~other_cols,
  "a","1","11","100","whatever",
  "b","2","11","200","whatever",
  "c","2","11","200","whatever",
  "d","2","12","200","whatever",
  "e","2","12","200","whatever",
  "f","2","12","200","whatever"
)

perimeters <- tribble(
  ~perimeters,~lib_perimeters,~cod_perimeters,
  "perimeter1","perimeter1_name1","1",
  "perimeter1","perimeter1_name2","2",
  "perimeter2","perimeter2_name1","11",
  "perimeter2","perimeter2_name2","12"
)

and I want to make this result:

id|perimeter1|lib_perimeter1   | perimeter2|lib_perimeter2  |
a | 1        |perimeter_name_1 | 11        |perimeter2_name1|

I've tried to nest, to pivot_wider or to make loops but never succeed.

Answer 1

There might be a "prettier" way, but this works. First, you have to pivot so that the data is oriented the same way, then join.

I pivoted data, then joined, then dropped fields, pivoted again, renamed, then reordered.

There is a slight deviation from your expected results. You have a bit of inconsistency under the two fields lib_perimeter*. In this result, they are formatted the same.

library(tidyverse)             

#------ pivot1 -------
(d2 <- data %>% 
  pivot_longer(cols = c(perimeter1, 
                        perimeter2),   
               names_to = "perimeters", 
               values_to = "cod_perimeters"))  # match names for join     
   
#------ join -------
(lj_df <- left_join(d2, perimeters))


#------ pivot2 -------
(newFrame = lj_df[,c(-2,-3)] %>% # drop perimeter3 and other_cols
   pivot_wider(names_from = perimeters,               # new columns
               values_from = c(cod_perimeters,        # column fill
                               lib_perimeters)) %>% 
  rename(perimeter1 = cod_perimeters_perimeter1,      # match names
         lib_perimeter1 = lib_perimeters_perimeter1,
         perimeter2 = cod_perimeters_perimeter2,
         lib_perimeter2 = lib_perimeters_perimeter2) %>% 
  select(id, contains("perimeter1"),everything()))    # match order

# # A tibble: 6 x 5
#   id    perimeter1 lib_perimeter1   perimeter2 lib_perimeter2  
#   <chr> <chr>      <chr>            <chr>      <chr>           
# 1 a     1          perimeter1_name1 11         perimeter2_name1
# 2 b     2          perimeter1_name2 11         perimeter2_name1
# 3 c     2          perimeter1_name2 11         perimeter2_name1
# 4 d     2          perimeter1_name2 12         perimeter2_name2
# 5 e     2          perimeter1_name2 12         perimeter2_name2
# 6 f     2          perimeter1_name2 12         perimeter2_name2