Data is everywhere and the amount of digital data that exists is growing rapidly, that is projected to grow to 180 zettabytes by 2025. Data Science is a field that tries to extract insights and meaningful information from structured and unstructured data through various stages such as asking questions, getting the data, exploring the data, modeling the data, and communicating result as shown in the following diagaram:

Data scientists or analysts often need to load or collect data from various resources having different input formats into R. Although R has its own native data format, data usually exists in text formats, such as Comma Separated Values (CSV), JavaScript Object Notation (JSON), and Extensible Markup Language (XML). This chapter provides recipes to load such data into your R system for processing.

Raw, real-world datasets are often messy with missing values, unusable format, and outliers. Very rarely can we start analyzing data immediately after loading it. Often, we will need to preprocess the data to clean, impute, wrangle, and transform it before embarking on analysis. This chapter provides recipes for some common cleaning, missing value imputation, outlier detection, and preprocessing steps.

In the wild, datasets come in many different formats, but each computer program expects your data to be organized in a well-defined structure.

As a result, every data science project begins with the same tasks: gather the data, view the data, clean the data, correct or change the layout of the data to make it tidy, handle missing values and outliers from the data, model the data, and evaluate the data.

With R, you can do everything from collecting your data (from the web or a database) to cleaning it, transforming it, visualizing it, modelling it, and running statistical tests on it.

CSV formats are best used to represent sets or sequences of records in which each record has an identical list of fields. This corresponds to a single relation in a relational database, or to data (though not calculations) in a typical spreadsheet.

If you have not already downloaded the files for this chapter, do it now and ensure that the auto-mpg.csv file is in your R working directory.

Reading data from .csv files can be done using the following commands:

1. Read the data from auto-mpg.csv, which includes a header row:

> auto <- read.csv("auto-mpg.csv", header=TRUE, sep = ",") 

2. Verify the results:

> names(auto) 

The read.csv() function creates a data frame from the data in the .csv file. If we pass header=TRUE, then the function uses the very first row to name the variables in the resulting data frame:

> names(auto) 
 
[1] "No"           "mpg"          "cylinders" 
[4] "displacement" "horsepower"   "weight" 
[7] "acceleration" "model_year"   "car_name" 

The header and sep parameters allow us to specify whether the .csv file has headers and the character used in the file to separate fields. The header=TRUE and sep="," parameters are the defaults for the read.csv() function; we can omit these in the code example.

The read.csv() function is a specialized form of read.table(). The latter uses whitespace as the default field separator. We will discuss a few important optional arguments to these functions.

In regions where a comma is used as the decimal separator, the .csv files use ";" as the field delimiter. While dealing with such data files, use read.csv2() to load data into R.

Alternatively, you can use the read.csv("<file name>", sep=";", dec=",") command.

Use sep="t" for tab-delimited files.

If your data file does not have column headers, set header=FALSE.

The auto-mpg-noheader.csv file does not include a header row. The first command in the following snippet reads this file. In this case, R assigns default variable names V1, V2, and so on.

> auto  <- read.csv("auto-mpg-noheader.csv", header=FALSE) 
> head(auto,2) 
 
  V1 V2 V3  V4 V5   V6   V7 V8                  V9 
1  1 28  4 140 90 2264 15.5 71 chevrolet vega 2300 
2  2 19  3  70 97 2330 13.5 72     mazda rx2 coupe 

If your file does not have a header row, and you omit the header=FALSE optional argument, the read.csv() function uses the first row for variable names and ends up constructing variable names by adding X to the actual data values in the first row. Note the meaningless variable names in the following fragment:

> auto  <- read.csv("auto-mpg-noheader.csv") 
> head(auto,2) 
 
  X1 X28 X4 X140 X90 X2264 X15.5 X71 chevrolet.vega.2300 
1  2  19  3   70  97  2330  13.5  72     mazda rx2 coupe 
2  3  36  4  107  75  2205  14.5  82        honda accord 

We can use the optional col.names argument to specify the column names. If col.names is given explicitly, the names in the header row are ignored, even if header=TRUE is specified:

> auto <- read.csv("auto-mpg-noheader.csv",     header=FALSE, col.names =       c("No", "mpg", "cyl", "dis","hp",         "wt", "acc", "year", "car_name")) 
 
> head(auto,2) 
 
  No mpg cyl dis hp   wt  acc year            car_name 
1  1  28   4 140 90 2264 15.5   71 chevrolet vega 2300 
2  2  19   3  70 97 2330 13.5   72     mazda rx2 coupe 

When reading data from text files, R treats blanks in numerical variables as NA (signifying missing data). By default, it reads blanks in categorical attributes just as blanks and not as NA. To treat blanks as NA for categorical and character variables, set na.strings="":

> auto  <- read.csv("auto-mpg.csv", na.strings="") 

If the data file uses a specified string (such as "N/A" or "NA" for example) to indicate the missing values, you can specify that string as the na.strings argument, as in na.strings= "N/A" or na.strings = "NA".

By default, R treats strings as factors (categorical variables). In some situations, you may want to leave them as character strings. Use stringsAsFactors=FALSE to achieve this:

> auto <- read.csv("auto-mpg.csv",stringsAsFactors=FALSE) 

However, to selectively treat variables as characters, you can load the file with the defaults (that is, read all strings as factors) and then use as.character() to convert the requisite factor variables to characters.

If the data file is available on the web, you can load it directly into R, instead of downloading and saving it locally before loading it into R:

> dat <- read.csv("http://www.exploredata.net/ftp/WHO.csv") 

You may sometimes need to extract data from websites. Many providers also supply data in XML and JSON formats. In this recipe, we learn about reading XML data.

Make sure you have downloaded the files for this chapters and the files cd_catalog.xml and WorldPopulation-wiki.htm are in working directory of R. If the XML package is not already installed in your R environment, install the package now, as follows:

> install.packages("XML") 

XML data can be read by following these steps:

1. Load the library and initialize:

> library(XML) 
> url <- "cd_catalog.xml" 

2. Parse the XML file and get the root node:

> xmldoc <- xmlParse(url) 
> rootNode <- xmlRoot(xmldoc) 
> rootNode[1] 

3. Extract the XML data:

> data <- xmlSApply(rootNode,function(x) xmlSApply(x, xmlValue)) 

4. Convert the extracted data into a data frame:

> cd.catalog <- data.frame(t(data),row.names=NULL) 

5. Verify the results:

> cd.catalog[1:2,] 

The xmlParse function returns an object of the XMLInternalDocument class, which is a C-level internal data structure.

The xmlRoot() function gets access to the root node and its elements. Let us check the first element of the root node:

> rootNode[1] 
 
$CD 
<CD> 
  <TITLE>Empire Burlesque</TITLE> 
  <ARTIST>Bob Dylan</ARTIST> 
  <COUNTRY>USA</COUNTRY> 
  <COMPANY>Columbia</COMPANY> 
  <PRICE>10.90</PRICE> 
  <YEAR>1985</YEAR> 
</CD> 
attr(,"class") 
[1] "XMLInternalNodeList" "XMLNodeList" 

To extract data from the root node, we use the xmlSApply() function iteratively over all the children of the root node. The xmlSApply function returns a matrix.

To convert the preceding matrix into a data frame, we transpose the matrix using the t() function and then extract the first two rows from the cd.catalog data frame:

> cd.catalog[1:2,] 
             TITLE       ARTIST COUNTRY     COMPANY PRICE YEAR 
1 Empire Burlesque    Bob Dylan     USA    Columbia 10.90 1985 
2  Hide your heart Bonnie Tyler      UK CBS Records  9.90 1988 

XML data can be deeply nested and hence can become complex to extract. Knowledge of XPath is helpful to access specific XML tags. R provides several functions, such as xpathSApply and getNodeSet, to locate specific elements.

Though it is possible to treat HTML data as a specialized form of XML, R provides specific functions to extract data from HTML tables, as follows:

> url <- "WorldPopulation-wiki.htm" 
> tables <- readHTMLTable(url) 
> world.pop <- tables[[6]] 

The readHTMLTable() function parses the web page and returns a list of all the tables that are found on the page. For tables that have an id attribute, the function uses the id attribute as the name of that list element.

We are interested in extracting the "10 most populous countries", which is the fifth table, so we use tables[[6]].

A single table can be extracted using the following command:

> table <- readHTMLTable(url,which=5) 

Specify which to get data from a specific table. R returns a data frame.

Several RESTful web services return data in JSON format, in some ways simpler and more efficient than XML. This recipe shows you how to read JSON data.

R provides several packages to read JSON data, but we will use the jsonlite package. Install the package in your R environment, as follows:

> install.packages("jsonlite")

If you have not already downloaded the files for this chapter, do it now and ensure that the students.json files and student-courses.json files are in your R working directory.

Once the files are ready, load the jsonlite package and read the files as follows:

1. Load the library:

> library(jsonlite) 

2. Load the JSON data from the files:

> dat.1 <- fromJSON("students.json") 
> dat.2 <- fromJSON("student-courses.json") 

3. Load the JSON document from the web:

> url <- "http://finance.yahoo.com/webservice/v1/symbols/allcurrencies/quote?format=json" 
> jsonDoc <- fromJSON(url) 

4. Extract the data into data frames:

> dat <- jsonDoc$list$resources$resource$fields 
> dat.1 <- jsonDoc$list$resources$resource$fields 
> dat.2 <- jsonDoc$list$resources$resource$fields 

5. Verify the results:

> dat[1:2,] 
> dat.1[1:3,] 
> dat.2[,c(1,2,4:5)] 

The jsonlite package provides two key functions: fromJSON and toJSON.

The fromJSON function can load data either directly from a file or from a web page, as the preceding steps 2 and 3 show. If you get errors in downloading content directly from the web, install and load the httr package.

Depending on the structure of the JSON document, loading the data can vary in complexity.

If given a URL, the fromJSON function returns a list object. In the preceding list, in step 4, we see how to extract the enclosed data frame.

In fixed-width formatted files, columns have fixed widths; if a data element does not use up the entire allotted column width, then the element is padded with spaces to make up the specified width. To read fixed-width text files, specify the columns either by column widths or by starting positions.

Download the files for this chapter and store the student-fwf.txt file in your R working directory.

Read the fixed-width formatted file as follows:

> student  <- read.fwf("student-fwf.txt",     widths=c(4,15,20,15,4),       col.names=c("id","name","email","major","year")) 

In the student-fwf.txt file, the first column occupies 4 character positions, the second 15, and so on. The c(4,15,20,15,4) expression specifies the widths of the 5 columns in the data file.

We can use the optional col.names argument to supply our own variable names.

The read.fwf() function has several optional arguments that come in handy. We discuss a few of these, as follows:

Files with headers use the following command:

> student  <- read.fwf("student-fwf-header.txt",     widths=c(4,15,20,15,4), header=TRUE, sep="t",skip=2) 

If header=TRUE, the first row of the file is interpreted as having the column headers. Column headers, if present, need to be separated by the specified sep argument. The sep argument only applies to the header row.

The skip argument denotes the number of lines to skip; in this recipe, the first two lines are skipped.

To exclude a column, make the column width negative. Thus, to exclude the email column, we will specify its width as -20 and also remove the column name from the col.names vector, as follows:

> student <- read.fwf("student-fwf.txt",widths=c(4,15,-20,15,4),     col.names=c("id","name","major","year")) 

During data analysis, you will create several R objects. You can save these in the native R data format and retrieve them later as needed.

First, create and save the R objects interactively, as shown in the following code. Make sure you have write access to the R working directory.

> customer <- c("John", "Peter", "Jane") 
> orderdate <- as.Date(c('2014-10-1','2014-1-2','2014-7-6')) 
> orderamount <- c(280, 100.50, 40.25) 
> order <- data.frame(customer,orderdate,orderamount) 
> names <- c("John", "Joan") 
> save(order, names, file="test.Rdata") 
> saveRDS(order,file="order.rds") 
> remove(order) 

After saving the preceding code, the remove() function deletes the object from the current session.

To be able to read data from R files and libraries, follow these steps:

1. Load data from the R data files into memory:

> load("test.Rdata") 
> ord <- readRDS("order.rds")

2. The datasets package is loaded in the R environment by default and contains the iris and cars datasets. To load these datasets data into memory, use the following code:

> data(iris) 
> data(list(cars,iris)) 

The first command loads only the iris dataset, and the second loads both the cars and iris datasets.

The save() function saves the serialized version of the objects supplied as arguments along with the object name. The subsequent load() function restores the saved objects, with the same object names that they were saved with, to the global environment by default. If there are existing objects with the same names in that environment, they will be replaced without any warnings.

The saveRDS() function saves only one object. It saves the serialized version of the object and not the object name. Hence, with the readRDS() function, the saved object can be restored into a variable with a different name from when it was saved.

The preceding recipe has shown you how to read saved R objects. We see more options in this section.

The following command can be used to save all objects:

> save.image(file = "all.RData") 

To save objects selectively, use the following commands:

> odd <- c(1,3,5,7) 
> even <- c(2,4,6,8) 
> save(list=c("odd","even"),file="OddEven.Rdata") 

The list argument specifies a character vector containing the names of the objects to be saved. Subsequently, loading data from the OddEven.Rdata file creates both odd and even objects. The saveRDS() function can save only one object at a time.

While loading Rdata files, if we want to be notified whether objects with the same names already exist in the environment, we can use:

> attach("order.Rdata") 

The order.Rdata file contains an object named order. If an object named order already exists in the environment, we will get the following error:

The following object is masked _by_ .GlobalEnv: 
 
    order 

All the loaded packages can be listed using the following command:

> data()