merge dataframes with same suffix in R

Question

Very new to this so please bare with me...

I have two sets of data frames INFO and DATA and the individual data frames are named INFO1, DATA1 etc. I want to merge INFO1 with DATA1, INFO2 with DATA2 and so on at the moment I am using the following:

File <- "path to excel"
INFO1 <- read_excel(File, sheet = "INFO1")                                                                                                      
DATA1 <- read_excel(File, sheet = "DATA1")                                                                                                     
PCR1 <- merge(INFO1, DATA1)

INFO2 <- read_excel(File, sheet = "INFO2")                                                                                                     
DATA2 <- read_excel(File, sheet = "DATA2")                                                                                                     
PCR2 <- merge(INFO2, DATA2)

Is there a way I can loop this?

By to `merge`, do you mean the sheets have common names? – Rui Barradas Aug 31 '22 at 12:14 — Rui Barradas, Aug 31 '22 at 12:14

Rui Barradas · Answer 1 · 2022-08-31T12:22:53.453

Se if the following solves the problem. Untested, since there are no data sets.

merge_xl_sheets <- function(n, file, info = "INFO", data = "DATA") {
  info <- paste0(info, n)
  data <- paste0(data, n)
  info_n <- read_excel(file, sheet = info)
  data_n <- read_excel(file, sheet = data)
  merge(info_n, data_n)
}

lapply(1:2, merge_xl_sheets, file = File)

Another, simpler function is to use mapply's ability to pass several arguments to a function, in this case two arguments vectors, plus fixed arguments (just one, the Excel file name).

merge_xl_sheets <- function(info, data, file) {
  info_n <- read_excel(file, sheet = info)
  data_n <- read_excel(file, sheet = data)
  merge(info_n, data_n)
}

INFO_vec <- paste0("INFO", 1:2)
DATA_vec <- paste0("DATA", 1:2)
mapply(merge_xl_sheets, INFO_vec, DATA_vec, 
       MoreArgs = list(file = File), SIMPLIFY = FALSE)

RyanF · Answer 2 · 2022-08-31T12:19:25.867

-1

I created sample tables to illustrate a reproducible answer.

In this case, I am using the assign( ) function to create a new table called PCR[i] where i is the value in the for for loop. The eval(as.name()) allows you to call objects from your environment based on provided strings.

library(data.table)

INFO1 <- data.table(
  ID = c(1,2,3),
  INFO = c("A","B","C"))

DATA1 <- data.table(
  ID = c(1,2,3),
  DATA = c("X","Y","Z"))

for (i in 1:1) {
  assign(
    x = paste0("PCR",i),
    merge(
      x = eval(as.name(paste0("INFO",i))),
      y = eval(as.name(paste0("DATA",i))),
      by = "ID"))
}

edited Aug 31 '22 at 12:19

answered Aug 31 '22 at 12:13

RyanF

119
6

why using assign is bad: https://stackoverflow.com/questions/17559390/why-is-using-assign-bad – Clemsang Aug 31 '22 at 12:37
@Clemsang, sure there are more optimal ways, but that does not mean that this does not work. Appreciate the feedback, nonetheless! – RyanF Aug 31 '22 at 12:48

merge dataframes with same suffix in R

2 Answers2