| readxl | Hmisc |
|---|---|
| 1.4.3 | 5.1.1 |
4 Reading and Writing Data Files
4.1 Reading data files into R
Data files can be loaded from the R session’s working directory, from a directory structure relative to the working directory using the single dot . or double dot .. syntax, or (for some file types) directly from a website. The following sections will expose you to a mixture of data file environments. For a refresher on directory structures, review Understanding directory structures.
4.1.1 Reading from a comma delimitted (CSV) file
A popular data file format (and one that has withstood the test of time) is the text file format where columns are separated by a tab, space or comma. In the following example, R reads a comma delimited file called ACS.csv into a data object called dat.
dat <- read.csv("ACS.csv", header=TRUE)If the CSV file resides on a website, you can load the file directly from that site as follows:
dat <- read.csv("http://mgimond.github.io/ES218/Data/ACS.csv", header=TRUE)Note that not all data file formats can be readily loaded directly from a website in a “read” function without additional lines of code. Examples are given in the next two sub-sections.
To read other text formats that use different delimiters, invoke the command read.table() and define the type of delimiter using the sep= parameter. For example, to read a tab delimited data file called ACS.txt, run the command read.table("ACS.txt", sep="\t").
Note that if a number or a string is identified as being a placeholder for missing values in the data file, you can use the na.strings = parameter in the read.csv function. For example, assume that the word "missing" was used in the csv file to denote a missing value, the function would be modified as follows:
dat <- read.csv("ACS.csv", na.strings = "missing")If more than one value is used as a placeholder for a missing value, you will need to combine the values using the c() operator. For example, if in addition to the word "missing" the value of -9999 was used to designate missing values, you would modify the above chunk of code as follows:
dat <- read.csv("ACS.csv", na.strings = c("missing", "-9999") )Note how the number is wrapped in double quotes. Also, note that the na.strings parameter is applied to all columns in the dataframe. So if the word "missing" or the number -9999 are valid values for some of the columns, you should not use this option. Instead, you would need to selectively replace the missing values after the dataset is loaded. You will learn how to replace values in a dataframe in subsequent chapters.
4.1.2 Reading from an R data file
R has its own data file format–it’s usually saved using the .rds extension. To read an R data file, invoke the readRDS() function.
dat <- readRDS("ACS.rds")As with a CSV file, you can load an .rds file straight from a website, however, you must first run the file through a decompressor before attempting to load it via readRDS. A built-in decompressor function called gzcon can be used for this purpose.
dat <- readRDS(gzcon(url("http://mgimond.github.io/ES218/Data/ACS.rds")))The .rds file format is usually smaller than its text file counterpart and will therefore take up less storage space. The .rds file will also preserve data types and classes such as factors and dates eliminating the need to redefine data types after loading the file.
4.1.3 Reading from an Excel file
A package that does a good job in importing Excel files is readxl. It recognizes most column formats defined by Excel including date formats. However, only one sheet can be loaded at a time. So, if multiple Excel sheets are to be worked on, each sheet will need to be loaded into separate dataframe objects.
If you don’t have the readxl package installed, install the package as you would any other package via RStudio’s interface or in R using the following command:
install.packages("readxl")In this example, we will load an Excel data sheet called Discharge which tabulates daily river water discharge. The sample file, Discharge_2004_2014.xlsx, can be downloaded here.
library(readxl)
xl <- read_excel("Discharge_2004_2014.xlsx", sheet = "Discharge")An advantage to using this package for loading Excel files is its ability to preserve data types–including date formatted columns! In the above example, the Excel file has a column called Date which stores the month/day/year data as a date object. We can check that the loaded xl object recognizes the Date column as a date data type:
str(xl)tibble [3,866 × 3] (S3: tbl_df/tbl/data.frame)
$ Date : POSIXct[1:3866], format: "2004-06-01" "2004-06-02" "2004-06-03" "2004-06-04" ...
$ Discharge: num [1:3866] 6170 6590 6210 7120 6990 6160 5570 4500 4940 4550 ...
$ Code : chr [1:3866] "A" "A" "A" "A" ...The Date column is defined as a POSIXct data type; this is the computer’s way of storing dates as the number of seconds since some internal reference date. We would therefore not need to convert the date column as would be the case if the date column was loaded from a CSV file. If such was the case, then the date column would most likely be loaded as a character or factor data type. A more in-depth discussion on date objects and their manipulation in R is covered in the next chapter.
Excel files can be loaded directly from the web using the following code chunk:
web.file <- "http://mgimond.github.io/ES218/Data/Discharge_2004_2014.xlsx"
tmp <- tempfile(fileext=".xlsx")
download.file(web.file,destfile=tmp, mode="wb")
xl <- read_excel(tmp, sheet = "Discharge")Instead of downloading the file into virtual memory, R needs to download the file into a temporary folder before it can open it. However, that temporary file my not be available in a later session, so you will probably need to reload the data if you launch a new R session.
4.1.4 Importing data from proprietary data file formats
It’s usually recommended that a data file be stored as a CSV or tab delimited file format if compatibility across software platforms is desired. However, you might find yourself in a situation where you have no option but to import data stored in a proprietary format. This requires the use (and installation) of a package called Hmisc. The package will convert the following file formats: SAS (XPT format), SPSS (SAV format) and Stata (dta format). You can install the package on your computer as follows:
install.packages("Hmisc")In this example, a SAS file of blood pressure from the CDC will be loaded into an object called dat (file documentation can be found here). You can donwload the file here.
library(Hmisc)
dat <- sasxport.get("BPX_J.xpt")Likewise, to import an SPSS file, use the spss.get() function; and to import a STATA file, use the stata.get() function.
4.2 How to save R objects to data files
4.2.1 Export to a CSV file
To export a data object called dat.sub as a comma delimited file, run the following:
write.csv(dat.sub, "ACS_sub.csv")4.2.2 Export to a .rds file
To export a data object called dat.sub to an R native .rds file format, run the following:
saveRDS(dat.sub, "ACS_sub.rds")4.3 Saving an R session
You can save an entire R session (which includes all data objects) using the save function.
To save all objects, set the list= parameter to ls():
save(list=ls(), file = "ACS_all.Rdata")To save only two R session objects–dat and dat.sub–to a file, pass the list of objects to the list= parameter:
save(list=c(dat, dat.sub), file = "ACS_subset.Rdata")4.4 Loading an R session
To load a previously saved R session type:
load("ACS_all.Rdata")