Merging of multiple excel files in R

user_8198780 0
Viewed 23434

I am getting a basic problem in R. I have to merge 72 excel files with similar data type having same variables. I have to merge them to a single data set in R. I have used the below code for merging but this seems NOT practical for so many files. Can anyone help me please?

data1<-read.csv("D:/Customer_details1/data-01.csv")

data2<-read.csv("D:/Customer_details2/data-02.csv")

data3<-read.csv("D:/Customer_details3/data-03.csv")

data_merged<-merge(data1,data2,all.x = TRUE, all.y = TRUE)

data_merged2<-merge(data_merged,data3,all.x = TRUE, all.y = TRUE)
r
2 Answers

First, if the extensions are .csv, they're not Excel files, they're .csv files.

We can leverage the apply family of functions to do this efficiently.

First, let's create a list of the files:

setwd("D://Customer_details1/")

#  create a list of all files in the working directory with the .csv extension
files <- list.files(pattern="*.csv")

Let's use purrr::map in this case, although we could also use lapply - updated to map_dfr to remove the need for reduce, by automatically rbind-ing into a data frame:

library(purrr)

mainDF <- files %>% map_dfr(read.csv)

You can pass additional arguments to read.csv if you need to: map(read.csv, ...)

Note that for rbind to work the column names have to be the same, and I'm assuming they are based on your question.

user_44218700 
#Method I
library(readxl)
library(tidyverse)
path <- "C:/Users/Downloads"
setwd(path)
fn.import<- function(x) read_excel("country.xlsx", sheet=x)
sheet = excel_sheets("country.xlsx")
data_frame = lapply(setNames(sheet, sheet),  fn.import )
data_frame = bind_rows(data_frame, .id="Sheet")
print (data_frame)
#Method II
install.packages("rio")
library(rio)
path <- "C:/Users/country.xlsx"
data <- import_list(path , rbind=TRUE)
user_157413150
Related