Read multiple CSV files into separate data frames

Question

Suppose we have files file1.csv, file2.csv, ... , and file100.csv in directory C:\R\Data and we want to read them all into separate data frames (e.g. file1, file2, ... , and file100).

The reason for this is that, despite having similar names they have different file structures, so it is not that useful to have them in a list.

I could use lapply but that returns a single list containing 100 data frames. Instead I want these data frames in the Global Environment.

How do I read multiple files directly into the global environment? Or, alternatively, How do I unpack the contents of a list of data frames into it?

Answer 1

Use list.files and map_dfr to read many csv files

df <- list.files(data_folder, full.names = TRUE) %>%
    map_dfr(read_csv)

Reproducible example

First write sample csv files to a temporary directory. It's more complicated than I thought it would be.

library(dplyr)
library(purrr)
library(purrrlyr)
library(readr)
data_folder <- file.path(tempdir(), "iris")
dir.create(data_folder)
iris %>%
    # Keep the Species column in the output
    # Create a new column that will be used as the grouping variable
    mutate(species_group = Species) %>%
    group_by(species_group) %>%
    nest() %>%
    by_row(~write.csv(.$data,
                      file = file.path(data_folder, paste0(.$species_group, ".csv")),
                      row.names = FALSE))

Read these csv files into one data frame. Note the Species column has to be present in the csv files, otherwise we would loose that information.

iris_csv <- list.files(data_folder, full.names = TRUE) %>%
    map_dfr(read_csv)

Answer 2

I want to update the answer given by Joran:

#If the path is different than your working directory
# you'll need to set full.names = TRUE to get the full
# paths.
my_files <- list.files(path="set your directory here", full.names=TRUE)
#full.names=TRUE is important to be added here

#Further arguments to read.csv can be passed in ...
all_csv <- lapply(my_files, read.csv)

#Set the name of each list element to its
# respective file name. Note full.names = FALSE to
# get only the file names, not the full path.
names(all_csv) <- gsub(".csv","",list.files("copy and paste your directory here",full.names = FALSE),fixed = TRUE)

#Now you can create a dataset based on each filename
df <- as.data.frame(all_csv$nameofyourfilename)

Answer 3

a simplified version, assuming your csv files are in the working directory:

listcsv <- list.files(pattern= "*.csv") #creates list from csv files
names <- substr(listcsv,1,nchar(listcsv)-4) #creates list of file names, no .csv
for (k in 1:length(listcsv)){
  assign(names[[k]] , read.csv(listcsv[k]))
}
#cycles through the names and assigns each relevant dataframe using read.csv

Answer 4

Thank you all for replying.

For completeness here is my final answer for loading any number of (tab) delimited files, in this case with 6 columns of data each where column 1 is characters, 2 is factor, and remainder numeric:

##Read files named xyz1111.csv, xyz2222.csv, etc.
filenames <- list.files(path="../Data/original_data",
    pattern="xyz+.*csv")

##Create list of data frame names without the ".csv" part 
names <-substr(filenames,1,7)

###Load all files
for(i in names){
    filepath <- file.path("../Data/original_data/",paste(i,".csv",sep=""))
    assign(i, read.delim(filepath,
    colClasses=c("character","factor",rep("numeric",4)),
    sep = "\t"))
}

Answer 5

Reading all the CSV files from a folder and creating vactors same as the file names:

setwd("your path to folder where CSVs are")

filenames <- gsub("\\.csv$","", list.files(pattern="\\.csv$"))

for(i in filenames){
  assign(i, read.csv(paste(i, ".csv", sep="")))
}

Answer 6

#copy all the files you want to read in R in your working directory
a <- dir()
#using lapply to remove the".csv" from the filename 
for(i in a){
list1 <- lapply(a, function(x) gsub(".csv","",x))
}
#Final step 
for(i in list1){
filepath <- file.path("../Data/original_data/..",paste(i,".csv",sep=""))
assign(i, read.csv(filepath))
}

Answer 7

This answer is intended as a more useful complement to Hadley's answer.

While the OP specifically wanted each file read into their R workspace as a separate object, many other people naively landing on this question may think that that's what they want to do, when in fact they'd be better off reading the files into a single list of data frames.

So for the record, here's how you might do that.

#If the path is different than your working directory
# you'll need to set full.names = TRUE to get the full
# paths.
my_files <- list.files("path/to/files")

#Further arguments to read.csv can be passed in ...
all_csv <- lapply(my_files,read.csv,...)

#Set the name of each list element to its
# respective file name. Note full.names = FALSE to
# get only the file names, not the full path.
names(all_csv) <- gsub(".csv","",
                       list.files("path/to/files",full.names = FALSE),
                       fixed = TRUE)

Now any of the files can be referred to by my_files[["filename"]], which really isn't much worse that just having separate filename variables in your workspace, and often it is much more convenient.

Answer 8

Here is a way to unpack a list of data.frames using just lapply

filenames <- list.files(path="../Data/original_data",
                        pattern="xyz+.*csv")

filelist <- lappy(filenames, read.csv)

#if necessary, assign names to data.frames
names(filelist) <- c("one","two","three")

#note the invisible function keeps lapply from spitting out the data.frames to the console

invisible(lapply(names(filelist), function(x) assign(x,filelist[[x]],envir=.GlobalEnv)))

Answer 9

A simple way to access the elements of a list from the global environment is to attach the list. Note that this actually creates a new environment on the search path and copies the elements of your list into it, so you may want to remove the original list after attaching to prevent having two potentially different copies floating around.

Answer 10

Use assign with a character variable containing the desired name of your data frame.

for(i in 1:100)
{
   oname = paste("file", i, sep="")
   assign(oname, read.csv(paste(oname, ".txt", sep="")))
}

Answer 11

Quick draft, untested:

1. Use list.files() aka dir() to dynamically generate your list of files.

2. This returns a vector, just run along the vector in a for loop.

3. Read the i-th file, then use assign() to place the content into a new variable file_i

That should do the trick for you.