Substitute one row with mupltiple according to a convertor

[Jakub_Komarek]

Hi,

I have a tibble which looks like this

id trid_prom hod_trid_prom promenna cas hodnota jednotka

1 CZ010 NA NA median_mezd 2019 37602 Kč
2 CZ020 NA NA median_mezd 2019 32570 Kč
3 CZ031 NA NA median_mezd 2019 29460 Kč
4 CZ032 NA NA median_mezd 2019 31985 Kč

and a convertor table which looks like this

okres kraj

1 CZ0100 CZ010
2 CZ0201 CZ020
3 CZ0202 CZ020
4 CZ0203 CZ020
5 CZ0204 CZ020
6 CZ0311 CZ031
7 CZ0312 CZ031
8 CZ0321 CZ032
9 CZ0322 CZ032

I would like to substitute each row with multiple rows according to a convertor tibble so that the final data looks like this

id trid_prom hod_trid_prom promenna cas hodnota jednotka

1 CZ0100 NA NA median_mezd 2019 37602 Kč
2 CZ0201 NA NA median_mezd 2019 32570 Kč
3 CZ0202 NA NA median_mezd 2019 32570 Kč
4 CZ0203 NA NA median_mezd 2019 32570 Kč
5 CZ0311 NA NA median_mezd 2019 29460 Kč
6 CZ0312 NA NA median_mezd 2019 29460 Kč
7 CZ0321 NA NA median_mezd 2019 31985 Kč
8 CZ0322 NA NA median_mezd 2019 31985 Kč

Could you suggest me the most efficient solution, please?

Thank you!

[nirgrahamuk]

dplyr/tidyverse provides a left_join()

library(tidyverse)
 
#load example data
(df1 <- structure(list(a = 1:4,
                       b = c("CZ010", "CZ020", "CZ031", "CZ032"),
                       c = c(NA, NA, NA, NA), 
                       d = c(NA, NA, NA, NA),
                       e = c(
  "median_mezd",
  "median_mezd", "median_mezd", "median_mezd"
), f = c(
  2019L, 2019L,
  2019L, 2019L
), g = c(37602L, 32570L, 29460L, 31985L), h = c(
  "Kc",
  "Kc", "Kc", "Kc"
)), row.names = c(NA, -4L), class = c(
  "tbl_df",
  "tbl", "data.frame"
)))



(df2 <- structure(list(x = 1:9, y = c(
  "CZ0100", "CZ0201", "CZ0202", "CZ0203",
  "CZ0204", "CZ0311", "CZ0312", "CZ0321", "CZ0322"
), z = c(
  "CZ010",
  "CZ020", "CZ020", "CZ020", "CZ020", "CZ031", "CZ031", "CZ032",
  "CZ032"
)), row.names = c(NA, -9L), class = c(
  "tbl_df", "tbl",
  "data.frame"
)))


#solution


left_join(df1,
  df2,
  by = c("b" = "z")
) %>% select(
  y,
  names(df1)
)