Chapter 12. Data Manipulation

In the previous chapter, you learned the methods used to access different types of databases such as relational databases (SQLite and MySQL) and non-relational databases (MongoDB and Redis). Relational databases usually return data in a tabular form, while non-relational databases may support nested data structures and other features.

Even though the data is loaded into memory, it is usually far from ready for data analysis. Most data at this stage still needs cleaning and transforming, which, in fact, may take a large proportion of time before any statistical model and visualization can be applied. In this chapter, you'll learn about a set of built-in functions and several packages for data manipulation. The packages are extremely powerful. However, to better work with these packages, we need a concrete understanding of the knowledge introduced in the previous chapters.

In this chapter, we'll cover the following topics:

• Using basic functions to manipulate data...

Using built-in functions to manipulate data frames

Previously, you learned the basics of data frames. Here, we will review the built-in functions used to filter a data frame. Although a data frame is essentially a list of vectors, we can access it like a matrix since all column vectors are of the same length. To select rows that meet certain conditions, we will supply a logical vector as the first argument of [], while the second is left empty.

In R, these operations can be done with built-in functions. In this section, we will introduce some built-in functions that are most helpful to manipulate data into the form we need as model input or for presentation. Some of the functions or techniques are already presented in the previous chapters.

Most of the code in this section and subsequent sections are based on a group of fictitious data about some products. We will use the readr package to load the data for better handling of column types. If you don't have this package installed, run install...

Using SQL to query data frames via the sqldf package

In the previous chapter, you learned how to compose SQL statements to query data from relational databases such as SQLite and MySQL. Is there a way to directly use SQL to query data frames in R as if these data frames are tables in relational databases? The sqldf package says yes.

This package takes advantage of SQLite, thanks to its lightweight structure and easiness to embed into an R session. Run the following command to install this package if you don't have it:

install.packages("sqldf") 

First, let's attach the package, as shown in the following code:

library(sqldf) 
## Loading required package: gsubfn 
## Loading required package: proto 
## Loading required package: RSQLite 
## Loading required package: DBI 

Note that when we attach sqldf, a number of other packages are automatically loaded. The sqldf package depends on these packages, because what it does is basically transferring data and converting data...

Using data.table to manipulate data

In the first section, we reviewed some built-in functions used to manipulate data frames. Then, we introduced sqldf, which makes simple data query and summary easier. However, both approaches have their limitations. Using built-in functions can be verbose and slow, and it is not easy to summarize data because SQL is not as powerful as the full spectrum of R functions.

The data.table package provides a powerful enhanced version of data.frame. It is blazing fast and has the ability to handle large data that fits into memory. It invents a natural syntax of data manipulation using []. Run the following command to install the package from CRAN if you don't have it yet:

install.packages("data.table") 

Once the package is successfully installed, we will load the package and see what it offers:

library(data.table) 
##  
## Attaching package: 'data.table' 
## The following objects are masked from 'package:reshape2': 
##  
##     dcast...

Using dplyr pipelines to manipulate data frames

Another popular package is dplyr, which invents a grammar of data manipulation. Instead of using the subset function ([]), dplyr defines a set of basic erb functions as the building blocks of data operations and imports a pipeline operator to chain these functions to perform complex multistep tasks.

Run the following code to install dplyr from CRAN if you don't have it yet:

install.packages("dplyr") 

First, we will reload the product tables again to reset all data to their original forms:

library(readr) 
product_info <- read_csv("data/product-info.csv") 
product_stats <- read_csv("data/product-stats.csv") 
product_tests <- read_csv("data/product-tests.csv") 
toy_tests <- read_csv("data/product-toy-tests.csv") 

Then, we will load the dplyr package:

library(dplyr) 
##  
## Attaching package: 'dplyr' 
## The following objects are masked from 'package:data.table': 
##  
##     between...

Using rlist to work with nested data structures

In the previous chapter, you learned about both relational databases that store data in tables and non-relational databases that support nested data structures. In R, the most commonly used nested data structure is a list object. All previous sections focus on manipulating tabular data. In this section, let's play with the rlist package I developed, which is designed for manipulating non-tabular data.

The design of rlist is very similar to dplyr. It provides mapping, filtering, selecting, sorting, grouping, and aggregating functionality for list objects. Run the following code to install the rlist package from CRAN:

install.packages("rlist") 

We have the non-tabular version of the product data stored in data/products.json. In this file, each product has a JSON representation as follows:

{ 
    "id": "T01", 
    "name": "SupCar", 
    "type": "toy", 
    "class": "vehicle", 
    "released": true, 
    "stats"...

Summary

In this chapter, you learned a number of basic functions and various packages for data manipulation. Using built-in functions to manipulate data can be redundant. Several packages are tailored for filtering and aggregating data based on different techniques and philosophies. The sqldf packages use embedded SQLite databases so that we can directly write SQL statements to query data frame in our working environment. On the other hand, data.table provides an enhanced version of data.frame and a powerful syntax, and dplyr defines a grammar of data manipulation by providing a set of pipeline friendly verb functions. The rlist class provides a set of pipeline friendly functions for non-tabular data manipulation. No single package is best for all situations. Each of them represents a way of thinking, and which best fits a certain problem depends on how you understand the problem and your experience of working with data.

Processing data and doing simulation require considerable computing...