Leftjoin one dataframe with three separately with loop in r choosing one column from each three which name starts with a prefix giving a name to each new dataframe

[ramgouveia]

I want to do:

• A left join of one dataframe with other three listed in a list;
• keeping a column from each of these three dataframes which starts with a prefix;
• giving a name to these new dataframes.

Can you help me please?

[nirgrahamuk]

library(tidyverse)
a_ <- data.frame(a = 1:3) |> mutate(pre_1 = a)
b_ <- data.frame(a = 2:4) |> mutate(pre_2 = a)
c_ <- data.frame(a = 3:5) |> mutate(pre_3 = a)

list_of_names <- list("b_", "c_")
prefix <- "pre_"
list_of_new_names <- list(
  "myb",
  "myc"
)

main_prep <- map(
  list_of_names,
  \(x){
    got <- get(x)
    got |> select(
      starts_with(prefix),
      intersect(
        names(a_),
        names(got)
      )
    )
    left_join(a_, got)
  }
) |> set_names(list_of_new_names)

attach(main_prep)
# using them 
b_
myb
c_
myc

[technocrat]

Assuming that each of the data frames to be joined has a unique prefix variable name consisting of three legal characters followed by _ followed by a single legal character."

# data
d <- data.frame(
  id = 32:63,
  bute = c(
    160, 160, 108, 258, 360,
    225, 360, 146.7, 140.8, 167.6, 167.6, 275.8, 275.8, 275.8, 472,
    460, 440, 78.7, 75.7, 71.1, 120.1, 318, 304, 350, 400, 79, 120.3,
    95.1, 351, 145, 301, 121
  )
)

j1 <- structure(list(
  id = 32:63, kren = c(
    110, 110, 93, 110, 175, 105,
    245, 62, 95, 123, 123, 180, 180, 180, 205, 215, 230, 66, 52,
    65, 97, 150, 150, 245, 175, 66, 91, 113, 264, 175, 335, 109
  ),
  pre_1 = c(
    3.9, 3.9, 3.85, 3.08, 3.15, 2.76, 3.21, 3.69, 3.92,
    3.92, 3.92, 3.07, 3.07, 3.07, 2.93, 3, 3.23, 4.08, 4.93,
    4.22, 3.7, 2.76, 3.15, 3.73, 3.08, 4.08, 4.43, 3.77, 4.22,
    3.62, 3.54, 4.11
  ), fbal = c(
    2.62, 2.875, 2.32, 3.215, 3.44,
    3.46, 3.57, 3.19, 3.15, 3.44, 3.44, 4.07, 3.73, 3.78, 5.25,
    5.424, 5.345, 2.2, 1.615, 1.835, 2.465, 3.52, 3.435, 3.84,
    3.845, 1.935, 2.14, 1.513, 3.17, 2.77, 3.57, 2.78
  )
), class = "data.frame", row.names = c(
  NA,
  -32L
))

j2 <- structure(list(id = 32:63, egon = c(
  16.46, 17.02, 18.61, 19.44,
  17.02, 20.22, 15.84, 20, 22.9, 18.3, 18.9, 17.4, 17.6, 18, 17.98,
  17.82, 17.42, 19.47, 18.52, 19.9, 20.01, 16.87, 17.3, 15.41,
  17.05, 18.9, 16.7, 16.9, 14.5, 15.5, 14.6, 18.6
), darb = c(
  0,
  0, 1, 1, 0, 1, 0, 1, 1, 1, 1, 0, 0, 0, 0, 0, 0, 1, 1, 1, 1, 0,
  0, 0, 0, 1, 0, 1, 0, 0, 0, 1
), pre_2 = c(
  1, 1, 1, 0, 0, 0, 0,
  0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 1, 0, 0, 0, 0, 0, 1, 1, 1,
  1, 1, 1, 1
)), class = "data.frame", row.names = c(NA, -32L))

j3 <- structure(list(id = 32:63, pre_3 = 32:63, zeda = c(
  4, 4, 4, 3,
  3, 3, 3, 4, 4, 4, 4, 3, 3, 3, 3, 3, 3, 4, 4, 4, 3, 3, 3, 3, 3,
  4, 5, 5, 5, 5, 5, 4
), rpar = c(
  4, 4, 1, 1, 2, 1, 4, 2, 2, 4,
  4, 3, 3, 3, 4, 4, 4, 1, 2, 1, 1, 2, 2, 4, 2, 1, 2, 2, 4, 6, 8,
  2
)), row.names = c(NA, -32L), class = "data.frame")


# prefix pattern
pat <- "^.*_."

# test for prefix
get_prefix <- function(x) grep(pat, colnames(x), value = TRUE)
# data frames to be joined
joiners <- list(j1, j2, j3)
prefixes <- sapply(sapply(joiners, colnames), has_prefix)
#> Error in eval(expr, envir, enclos): object 'has_prefix' not found

join_one <- function(x) merge(d, x, by.x = "id", by.y = "id")

for (i in seq_along(joiners)) {
  assign(get_prefix(joiners[i][[1]]), join_one(joiners[i][[1]]), pos = -1)
}

head(pre_1)
#>   id bute kren pre_1  fbal
#> 1 32  160  110  3.90 2.620
#> 2 33  160  110  3.90 2.875
#> 3 34  108   93  3.85 2.320
#> 4 35  258  110  3.08 3.215
#> 5 36  360  175  3.15 3.440
#> 6 37  225  105  2.76 3.460
head(pre_2)
#>   id bute  egon darb pre_2
#> 1 32  160 16.46    0     1
#> 2 33  160 17.02    0     1
#> 3 34  108 18.61    1     1
#> 4 35  258 19.44    1     0
#> 5 36  360 17.02    0     0
#> 6 37  225 20.22    1     0
head(pre_3)
#>   id bute pre_3 zeda rpar
#> 1 32  160    32    4    4
#> 2 33  160    33    4    4
#> 3 34  108    34    4    1
#> 4 35  258    35    3    1
#> 5 36  360    36    3    2
#> 6 37  225    37    3    1

^{Created on 2023-06-17 with reprex v2.0.2}