9  Import from Spreadsheets

Most R users will have to work with spreadsheets at some point in their careers. This chapter will teach you how to import data from a .csv or .xlsx file, and how to get the imported data into a format that’s easy to work with. Additionally, this chapter will demonstrate how to import multiple files at once and combine them all into a single dataframe.

9.1 Import from .csv Files

R has a function called “read.csv” which allows you to read a csv file directly into a dataframe. The following code snippet is a simple example of how to execute this function.

Note

It’s worth noting that it isn’t necessary to store the file path as a variable before calling the function; however, this habit may save you time down the road.

input <- "C:/File Location/example.csv"
df <- read.csv(input)

Alternatively, if you have multiple files from the same directory that need to be imported, you could do something more like the following code snippet.

directory <- "C:/File Location/"
first_file <- paste(directory, "first_file.csv", sep="")
second_file <- paste(directory, "second_file.csv", sep="")
first_df <- read.csv(first_file)
second_df <- read.csv(second_file)

9.2 Import from .xlsx Files

Excel files are handled very similarly to CSV files with the exception being that you will need to use the “read_excel” function from the “readxl” library. The following code snippet demonstrates how to import an Excel file into R.

library(readxl)
input <- "C:/File Location/example.xlsx"
df <- read_excel(input)

9.3 Import and Combine Multiple Files

You may come across a situation where you have multiple CSV files in a folder that need to be combined into a single data frame. The read_csv() function from the readr package accepts the paths to multiple files and will automatically concatenate them along their rows (if the columns match).

install.packages("readr")
library(readr)

You can list the paths to all .csv files in a directory with the dir() command:

wd <- "C:/YOURWORKINGDIRECTORY"
dir(wd, full.names = TRUE, pattern = ".csv")

And read them into a single data.frame with a single command:

df <- read_csv(dir(wd, full.names = TRUE, pattern = ".csv"))
Note

All of the headers must match in your CSV files must match exactly for this function to work as expected.

9.4 Resources