6.106
Import Data into R from CSV, TSV, and Excel Files

Introduction

The first step in data collection, processing, and analytics is to get the data from its various source files. Among the common file types for data exchange are CSV (comma-separated values), TSV (tab-separated values), Excel (.xlsx and .xls files), and XML files. This tutorial demonstrates how to load from all but the latter file type.

In addition to reading data from files, it is also critical that the data analyst knows how to store binary objects in files for faster loading after initial processing. For R, this means learning how to save and load R object files.

Base R contains most commonly used functions for reading CSV and TSV files. However, other packages are often needed for special files types (e.g., the readxl and xlsx packages for Excel files) and packages such as readr for faster loading of large files.

The term text file means that the file is human-readable text and can be inspected in any plain text editor, e.g., R Studio, Sublime, JEdit, Notepad, TextEdit, etc. Note that you should never use a word processor to inspect, modify, edit, or create a text file.

As always the packages must be installed first into your local installation of R and then loaded using the library() or require() functions as needed.

Of course, data is also found in relational and non-relational (NoSQL) databases, as well as on web pages. This tutorial does not show how to get data from these sources.

Importing Data from CSV and TSV Text Files

Text files are a common way to store and exchange tabular (row/column) data as almost any data application supports exporting data to the .csv text file format. Text file formats use delimiters to separate the columns on each row; each row of data is on its own line in the text file. Therefore, importing different kinds of text files can follow a fairly consistent process once you know the delimiter. The most common delimiter is the comma (,) and thus text files that use comma delimiters are called comma-separated values (CSV) files. Other common delimiters include semicolon (;), colon (:), slash (/), bar (|), and the tab (\t) character.

There are three main groups of functions that are commonly used to import data from text files:

• Base R functions
• readr package functions
• data.table package functions

In this tutorial we will restrict ourselves to the functions in Base R. However, the other two packages often have versions of the functions found in Base R that are more efficient for very large (100k rows or more) text files.

All of the functions import the data and store it in memory in a data frame in R. The data frame mimics the row/column structure of the file.

All rows in the file must have a value for each column. Empty values are either between consecutive delimiters or use a special marker for a missing value such as NA, ““, or ’’.

There is no standard CSV file format, although RFC 4180 is an attempt to standardize some aspects of the CSV format. You might still encounter processing problems when dealing with non-standard CSV files.

Base R Functions: read.table() and read.csv()

The function read.table() is a Base R function and does not require any additional packages. It is among the most commonly used functions. A simplified version of read.table() that sets the delimiter to comma (,) is read.csv(), while another that sets the delimiter to semicolon (;) is read.csv2().

A Simple CSV

We will use the following CSV file for some of the examples below. You can download the files from the link or create it using any text editor (R Studio, TextEdit, Sublime, JEdit, Notepad, etc.) and save it under the file name beverages.csv in the folder of your choice. You will need to know the path of the folder, so be sure to remember it.

Note that text (aka, a string or string of characters) is often enclosed in quotes, generally double quotes (“Almond Delight”) but single quotes are fine, too (‘Almond Delight’). It’s one or the other. Of course that makes it tricky to put quotes into a string, which is why we have the backslash for “escaping” a character: “\”Gordon’s\“” Bran”. This can be a bit difficult to load and will require “text parsing”. Some CSV files, like ours, will not use any quotes but that will make it impossible to store text to place commas into the text. So, you can have “Coca, Cola” but not Coca, Cola.

Another common issue is the encoding of true/false or Boolean values. They are sometimes encoded as 0 (false) and 1 (true), as 0 (false) and any other number as true (e.g., -1), as T and F, as TRUE and FALSE, or some other way. A data dictionary sometimes accompanies a CSV file that explains what data each column contains and how it is encoded and to be interpreted. Finally, some CSV files will contain a header row as the first row that contains the names of each column, while some CSV files will only have the data – then, of course, a data dictionary is critical.

Here’s an example of a CSV:

Product,Cereal Name,Manufacturer,Calories,Sodium,Fiber,Carbs,Sugars,Shelf,Year
1,100% Bran,Nabisco,70,130,10,5,6,3,1942
2,All-Bran,Kellogg,70,260,9,7,5,3,1916
3,All-Bran w/Extra Fiber,Kellogg,50,140,14,8,0,3,1916
4,Almond Delight,Ralston Purina,110,200,1,14,8,3,1987
5,Apple Cinn Cheerios,General Mills,110,180,1.50,10.50,10,1,1988
6,Apple Jacks,Kellogg,110,125,1,11,14,2,1965
7,Basic 4,General Mills,130,210,2,18,8,3,1991
8,Bran Chex,Ralston Purina,90,200,4,15,6,1,1978
9,Bran Flakes,Post,90,210,5,13,5,3,1915
10,Cap'n'Crunch,Quaker,120,220,0,12,12,2,1963
11,Cheerios,General Mills,110,290,2,17,1,1,1941
12,Cinnamon Toast Crunch,General Mills,120,210,0,13,9,2,1984
13,Clusters,General Mills,110,140,2,13,7,3,1986
14,Cocoa Puffs,General Mills,110,180,0,12,13,2,1958

Many programs recognize what kind of a file it is by it’s “extension”, the characters at the end of a file name separated from the rest of the file name with a period (.). For example, rides.csv likely contains comma-separated values while flights.tsv likely contains tab-separated values. We say “likely” because there is no requirement that a text file with a .csv extension actually contain even text – it is simply a convention. So, be sure to inspect any text file first in a text editor before importing.

Reading a CSV File

CSV files are most commonly imported with the read.csv(). It is the same as read.table() but you do not have to specify the delimiter – it is assumed to be comma.

Remember that you can get the full definition of the function along with a full description of its parameters by consulting the R documentation or with ? read.csv from the R Console.

The first argument (parameter) to read.csv() is the file name; whether you use file = or not is up to you. If you do not specify the path as part of the file, R will search in its default directory (folder). It is strongly suggested that you provide path names or that you use the function setwd() to set a default directory. Alternatively, you can use the function file.choose() to interactively ask the user to pick a file. Naturally, you cannot use file.choose() if you want to knit an R Notebook.

fn <- "CerealData.csv"

bevDF <- read.csv(fn, header = TRUE, stringsAsFactors = FALSE)

Rather than specifying a path to a local file, you can also specify a URL and load the file from the web.

Reading a TSV File

A TSV file uses a tab as a delimiter. It is an “unprintable” character so we need to use a code: \t.

The section below is a sample of the TSV file we will read. The space between the fields is a “tab” – you will have to believe us…

"tconst"    "averageRating" "numVotes"
"1" "tt0000001" 5.6 1658
"2" "tt0000002" 6.1 201
"3" "tt0000003" 6.5 1371
"4" "tt0000004" 6.2 122
"5" "tt0000005" 6.2 2158
"6" "tt0000006" 5.3 115
"7" "tt0000007" 5.4 661

fn <- "title.ratings.sample.tsv"

ratingsDF <- read.table(fn, header = TRUE, stringsAsFactors = FALSE,
                        sep = '\t')

str(ratingsDF)

## 'data.frame':    7 obs. of  3 variables:
##  $ tconst       : chr  "tt0000001" "tt0000002" "tt0000003" "tt0000004" ...
##  $ averageRating: num  5.6 6.1 6.5 6.2 6.2 5.3 5.4
##  $ numVotes     : int  1658 201 1371 122 2158 115 661

Key Parameters

Some key parameters for read.csv(), read.tsv(), and read.table() (there are many more, so consult the documentation):

Parameter	Use/Meaning	Default
header	whether the first line contains headers	TRUE
col.names	a vector of column names if there are no headers or if the headers are to be overwritten	Empty
colClasses	the data type for each column that overrides the type that R guess	NA
stringsAsFactors	whether to import character strings as text or as “factor” variables	TRUE
skip	how many lines to skip before importing	0
quote	default quote character	”
nrows	the number of rows to read	-1

File Paths in Windows

A note about paths: On Windows, paths are generally written as c:\users\name\downloads using the backslash as the path separator. This will not work in R as the backslash has a special meaning. So, in R (and most other programming languages), we use forward slash, although you often cannot use that in Windows: c:/users/name/downloads. It’s a historical anomaly – ask Bill Gates… On MacOS, since it is based on Unix, we use the standard Unix path separator: the forward slash. And, on Unix, there are no drive letters, so a path on Unix or MacOS never starts with c:\.

Relative vs Absolute Paths

Rather than specifying a path to a file, it is often better to create an R Project and place all data files into the project folder. See Lesson 6.202 Working with R Projects.

Strings vs Factors

The parameter stringsAsFactors is very important when reading text files that contain, well, text. By default, any column with text will be converted to a “factor” – R’s way to representing categorical variables. That’s most often not what is desired, unless the column is indeed a categorical variables such as gender, job title, among many others. Factor variables are very important for many statistical and data mining functions in R but most often we want to read text columns as strings. Check out the difference in the code below; the class() function displays a variable’s “data type” in R.

bevDF <- read.csv(fn, header = TRUE, stringsAsFactors = FALSE)
str(bevDF)

## 'data.frame':    7 obs. of  1 variable:
##  $ tconst.averageRating.numVotes: chr  "1\ttt0000001\t5.6\t1658" "2\ttt0000002\t6.1\t201" "3\ttt0000003\t6.5\t1371" "4\ttt0000004\t6.2\t122" ...

head(bevDF)

##   tconst.averageRating.numVotes
## 1       1\ttt0000001\t5.6\t1658
## 2        2\ttt0000002\t6.1\t201
## 3       3\ttt0000003\t6.5\t1371
## 4        4\ttt0000004\t6.2\t122
## 5       5\ttt0000005\t6.2\t2158
## 6        6\ttt0000006\t5.3\t115

bevDF <- read.csv(fn, header = TRUE, stringsAsFactors = TRUE)
str(bevDF)

## 'data.frame':    7 obs. of  1 variable:
##  $ tconst.averageRating.numVotes: Factor w/ 7 levels "1\ttt0000001\t5.6\t1658",..: 1 2 3 4 5 6 7

Note how Manufacturer, as an example, is now a factor variable rather than a strings (a character in R).

It’s gets tricky if you have some columns that are factors and other that are text (strings). Now, you have to read them as strings and convert those columns that you want as factors explicity using as.factor.

bevDF <- read.csv(fn, header = TRUE, stringsAsFactors = FALSE)
bevDF$Manufacturer <- as.factor(bevDF$Manufacturer)
str(bevDF)

Now Cereal.Name is text while Manufacturer is a factor.

You could have specified the data types you need using the colClasses parameters as shown next.

bevDF <- read.csv(fn, header = TRUE, stringsAsFactors = TRUE,
                  colClasses = c("integer","character","factor","integer",
                                 "integer","double","double","integer","integer",
                                 "integer"))
str(bevDF)

The atomic modes that you can specify in colClasses are “logical”, “integer”, “numeric” (synonym “double”), “complex”, “character” and “raw”.

Instead of read.csv() you could have used read.table() which would have the same parameters, but it would allow you to specify the delimiter explicitly. You also will need to specify the ‘quote’ character explicitly.

bevDF <- read.table(fn, sep = ",", quote = "\"",
                    header = TRUE, stringsAsFactors = TRUE,
                    colClasses = c("integer","character","factor","integer",
                                   "integer","double","double","integer","integer",
                                   "integer"))
str(bevDF)

Loading CSV Files from a URL

Often a CSV file is located on a URL (on the web) instead of a local file. R also allows URLs to be specified instead of a filename, as long as it starts with _http://_. Note that R cannot access files behind paywalls or using SSL \(_https://_\). The RCurl package contains many additional functions for managing URLs and data on the web.

As an alternative, you can use download.file() to explicitly download a file through a URL using HTTP or FTP. This might be more efficient than retrieving the data over a network multiple times, particularly if the file is very large. Not only is that potentially quite slow, it also uses precious network bandwidth or cost money if the connection is metered.

url <- "https://drive.google.com/uc?id=1dYvbI2D85AQIHa8yeTL-SzMEjXXnqwpO"
bevDF <- read.csv(url, header = TRUE, stringsAsFactors = TRUE)
str(bevDF)

## 'data.frame':    67 obs. of  10 variables:
##  $ Product     : int  1 2 3 4 5 6 7 8 9 10 ...
##  $ Cereal.Name : Factor w/ 67 levels "100% Bran","All-Bran",..: 1 2 3 4 5 6 7 8 9 10 ...
##  $ Manufacturer: Factor w/ 6 levels "General Mills",..: 3 2 2 6 1 2 1 6 4 5 ...
##  $ Calories    : int  70 70 50 110 110 110 130 90 90 120 ...
##  $ Sodium      : int  130 260 140 200 180 125 210 200 210 220 ...
##  $ Fiber       : num  10 9 14 1 1.5 1 2 4 5 0 ...
##  $ Carbs       : num  5 7 8 14 10.5 11 18 15 13 12 ...
##  $ Sugars      : int  6 5 0 8 10 14 8 6 5 12 ...
##  $ Shelf       : int  3 3 3 3 1 2 3 1 3 2 ...
##  $ Year        : int  1942 1916 1916 1987 1988 1965 1991 1978 1915 1963 ...

To make a text or any other data file accessible on Google Drive, generate a share link with permissions set so Anyone with link can view the file. The link will look like this: _https://drive.google.com/file/d/fc5e038c38a570/view?usp=sharing_. Copy the file identifier fc5e038c38a570 and add it to the end of this URL: _https://drive.google.com/uc?id=_. The complete URL is: _https://drive.google.com/uc?id=fc5e038c38a570_

Compressed Files

Text files are often compressed to save space; it can reduce the size of a file down to 10-20% of its original size. This not only saves space on disk, it also makes downloading over a network much faster. Compressed files often have a .csv.zip, .zip, or a .gz extension. Fortunately, R automatically uncompresses (expands) files if it recognizes the extension. If it can’t, then either uncompress on your operating system or use the unzip() function.

fn <- "CerealData.zip"

bevDF <- read.csv(unzip(fn), header = TRUE, stringsAsFactors = FALSE)

The readr Package

Compared to the equivalent Base R functions, the functions in readr can be about 10x faster (although, truth be told, I often found the Base R functions to be faster). They have another advantage though: they bring consistency to importing functions, they produce data frames in a data.table format (rather than data frame) which are easier to view for large data sets, the default settings remove the problems with strings and factors, and they have a more flexible column specification.

To demonstrate, we can use read_csv() which is equivalent to Base R’s read.csv() function. However, notice how read_csv() maintains the full variable names.

library(readr)

fn <- "CerealData.csv"

cerDF <- read_csv(fn)
head(cerDF)

One other benefit of read_csv() is its max parameters which allows you to set how many lines it will read at most – rather than reading the entire file. This can be useful for “sampling” just a portion of the file.

Writing CSV Files

To save a data frame and its rows to a CSV file, use the function write.csv() from Base R. In the code example below, we write the built-in data frame warpbreaks to a CSV file.

fn <- "weaving.csv"

bevDF <- write.csv(warpbreaks, file = fn)

Importing Excel Files

Excel files (having a .xls or .xlsx) are another common source of data. to import data from Excel you will need the readxl package; Base R cannot read them. If possible, it is often easier to write the data as a CSV from Excel (use the Export feature of Excel).

While there are several packages for reading Excel files, we recommend either xlsx or the newest package from Hadley Wickham at R Studio, readxl. Unlike the functions in readxl that are built in C++ (and thus are blazing fast), the xlsx package is built in Java. That means it requires a Java VM and additional set up, so we do not recommend its use.

The code sample below uses the read.xlsx() function from the readxl package.

library(readxl)

fn <- "CerealData.xlsx"

bevDF <- read_excel(fn, sheet = 1)
head(bevDF)

## # A tibble: 6 × 10
##   Product `Cereal Name`          Manufacturer   Calories Sodium Fiber Carbs Sugars Shelf  Year
##     <dbl> <chr>                  <chr>             <dbl>  <dbl> <dbl> <dbl>  <dbl> <dbl> <dbl>
## 1       1 100% Bran              Nabisco              70    130  10     5        6     3  1942
## 2       2 All-Bran               Kellogg              70    260   9     7        5     3  1916
## 3       3 All-Bran w/Extra Fiber Kellogg              50    140  14     8        0     3  1916
## 4       4 Almond Delight         Ralston Purina      110    200   1    14        8     3  1987
## 5       5 Apple Cinn Cheerios    General Mills       110    180   1.5  10.5     10     1  1988
## 6       6 Apple Jacks            Kellogg             110    125   1    11       14     2  1965

The sheet parameter specifies which worksheet to read from the Excel workbook; it is either the number of the worksheet or its name.

Importing R Object Files

Creating large and complex data frames is compute-intensive. However, after an R session terminates, all objects in memory go away. The solution is to save them in an R object file using save() function as a .RData file. This is also useful for archiving.

To load such archived objects back into your current R workspace, use the load() function. The code below shows how to save and then how to load back an object. Run the first chunk, then restart R – the object will be “gone”, then load the object and you can access it again.

fn <- "CerealData.csv"
fnD <- "bevDF.RData"

bevDF <- read.csv(fn, header = TRUE, stringsAsFactors = FALSE)
head(bevDF)

##   Product            Cereal.Name   Manufacturer Calories Sodium Fiber Carbs Sugars Shelf Year
## 1       1              100% Bran        Nabisco       70    130  10.0   5.0      6     3 1942
## 2       2               All-Bran        Kellogg       70    260   9.0   7.0      5     3 1916
## 3       3 All-Bran w/Extra Fiber        Kellogg       50    140  14.0   8.0      0     3 1916
## 4       4         Almond Delight Ralston Purina      110    200   1.0  14.0      8     3 1987
## 5       5    Apple Cinn Cheerios  General Mills      110    180   1.5  10.5     10     1 1988
## 6       6            Apple Jacks        Kellogg      110    125   1.0  11.0     14     2 1965

# save the bevDF object to a binary RData file
save(bevDF, file = fnD)

So, now, restart your R Session or clear the bevDF data frame from memory with rm().

rm(bevDF)
gc(verbose = TRUE)

##           used  (Mb) gc trigger  (Mb) limit (Mb) max used  (Mb)
## Ncells 2014203 107.6    3545600 189.4         NA  3545600 189.4
## Vcells 4620926  35.3   10146329  77.5      32768 10146329  77.5

To “prove” that it is gone, access the object. You should get the error “Error in head(bevDF) : object ‘bevDF’ not found”.

head(bevDF)

Let’s load it back (but without rebuilding it), directly from its “binary” representation. This is extremely fast. The function load() recreates all R objects contained in the file.

fnD <- "bevDF.RData"
load(fnD)

And to prove that it is there, let’s access it…

head(bevDF)

##   Product            Cereal.Name   Manufacturer Calories Sodium Fiber Carbs Sugars Shelf Year
## 1       1              100% Bran        Nabisco       70    130  10.0   5.0      6     3 1942
## 2       2               All-Bran        Kellogg       70    260   9.0   7.0      5     3 1916
## 3       3 All-Bran w/Extra Fiber        Kellogg       50    140  14.0   8.0      0     3 1916
## 4       4         Almond Delight Ralston Purina      110    200   1.0  14.0      8     3 1987
## 5       5    Apple Cinn Cheerios  General Mills      110    180   1.5  10.5     10     1 1988
## 6       6            Apple Jacks        Kellogg      110    125   1.0  11.0     14     2 1965

Voila… the object is back.

Other Data File Formats

Other than text and R binary files, R can also import data from many statistical programs and analytics platforms using the functions of the foreign package. Among many others, the foreign package supports importing from: Stata, S3, SPSS, Minitab, and SAS.

In addition, R supports reading and writing data to and from XML files, as well as relational (and NoSQL) databases. However, importing for these sources is beyond the scope of this tutorial.

The table below lists the most important functions from the foreign package.

Function	Common Use
data.restore	Read an S3 Binary File
read.dbf	Read a DBF File
read.dta	Read Stata binary files
read.mtp	Read a Minitab Portable Worksheet
read.spss	Read an SPSS data file
read.systat	Obtain a Data Frame from a Systat File
read.xport	Read a SAS XPORT Format Library
write.dbf	Write a DBF File
write.dta	Write Files in Stata Binary Format

Working with the File System

An important function for checking directory (folder) or file existence is file.exists(). It checks if the file or directory exists and returns TRUE if it does, FALSE otherwise. The functiondir.create() creates a directory if it does not exist.

See Lesson 6.402 Navigating the File System in R for more information on working with files.

Summary

This tutorial presented mechanisms for importing data from two common text files (CSV and TSV) and from Excel. It also showed how to save data frames into a CSV text file. Additionally, it demonstrated how to save in-memory objects in their binary representation for archiving and future rapid re-creation.

Aside from important data from CSV and Excel files, data is also often encoded in XML or stored in relational databases. Other tutorials show how to access data in those stores.

---

Tutorial

The video tutorial demonstrates the constructs introduced in this lesson.

---

Files & Resources

All Files for Lesson 6.106

---

References

None yet.

Errata

Let us know.