6.101
First Steps in R

Preface

This tutorial presumes that you have R, R Studio, and SQLite installed, although to be technically accurate, you do actually need to have SQLite installed if you are connected from R as the RSQLite package contains all necessary code to open a SQLite database file.

Follow the tutorial by starting your own R Notebook in R Studio. If you do not already have R and/or R Studio you will need to download and install them. You must first install R from R Project and then the R Studio IDE from R Studio. Alternatively, rather than installing R and R Studio locally, you can do the tutorial using R Studio Cloud.

Introduction

This is an R Markdown Notebook. When you execute code within the notebook, the results appear beneath the code. It demonstrates how to work with data frames, one of the most important R data types for data processing in addition to vector and scalars.

The tutorial is geared towards students in information science, data science, and database design. It demonstrates basic syntax in R that are most often used for data processing rather than statistics.

Execute chunks by clicking the Run button within the chunk or by placing your cursor inside it and pressing Ctrl+Shift+Enter. The code runs in the order in which the chunks are executed, so non-linear code execution is possible unless you instruct R Studio to run all chunks starting at the first chunk.

Add a new chunk by clicking the Insert Chunk button on the toolbar or by pressing Ctrl+Alt+I.

When you save the notebook, an HTML file containing the code and output will be saved alongside it (click the Preview button or press Ctrl+Shift+K to preview the HTML file).

The preview shows you a rendered HTML copy of the contents of the editor. Consequently, unlike Knit, Preview does not run any R code chunks. Instead, the output of the chunk when it was last run in the editor is displayed.

Working in R

To write “programs” in R you will need Base R which you can download for Linux, MacOS, and Windows from R Project. This is the core language with an interactive console. Programs, or more aptly R scripts, can be built in any text editor (TextEdit, Notepad, vi, Sublime, JEdit, etc.).

R doesn’t really have “programs” in the traditional sense. There is no “compiling”; programs are “scripts” that are interpreted. It is possible to execute R scripts from the command line and thus have them behave like programs but most work is done in R Notebooks that are chunks of code.

Most programming is done with an IDE (Integrated Development Environment). The most common is R Studio downloadable from RStudio. There hosted version of R Studio available at rstudio.cloud.

The tutorial below explains how to get started with R Notebooks:

Basic R

R is a scripted language which means that you do not need to compile the program before running it. Statements and expressions are executed as you type them.

R Code Chunks

We will only discuss how to write R “programs” using an R Notebook in R Studio. Programs in R run from start to end. Each chunk should be a step in your analysis or data project. Name your code chunk, so you can quickly navigate to them.

In the chunk below, the variable cars passed to the built-in Base R function plot is one of the dozens of “built-in” data frames; a data frame being data arranged in rows and columns similar to a spreadsheet or CSV file.

Note that you call a function by using the function’s name followed by the arguments you wish to pass to the function. Of course, you need to follow the definition of the function. Many functions are simply “built-in” while others come from packages that you need to explicitly load into your program.

Note that there is no semicolon at the end of a line.

```{r namedChunk, eval=FALSE}
plot(x = mtcars$mpg, y = mtcars$hp)
```

Expressions

R can be directly used to solve simple or complex mathematical expressions.

# [1] in the above answer indicates the index of your results.
# R always shows the result with index for each row.

((2^3)*5)-1

## [1] 39

# sqrt and exp are built-in functions in R for finding Square root and exponential respectively.

sqrt(4)* exp(2)

## [1] 14.77811

Variables and Identifiers

Holding a value in a variable is done through assignment. Once you assign a value to a variable, the variables becomes an R object. There are two ways to do an assignment, using ‘=’ or with ‘<-’. The latter is the preferred way in R but the former might be more familiar to those programmers coming to R from Java, C++, or Python.

Note that variables are explicitly defined or declared. The first time a variable is assigned a value defines the variable and its type. The type is based on the value that is assigned. Unlike other programming languages such as C++, C#, or Java, R is not strongly typed: the type of a variable can change when a value of a different type is assigned. A variable can be used in an expression. Its value can be inspected by just using the variable by itself.

The value of a variable can be displayed either by using the variable by itself or using the print() function.

# assignment with '=' of a number
x = 12
# inspect (print/display) the value
x

## [1] 12

# assignment a new value and change its type to "text"
x = "Hello"
x

## [1] "Hello"

# assignment with '<-'
x <- 12
print(x)

## [1] 12

The rules for naming an identifier (variable, function, or package name) for an object are as follows:

identifiers are case-sensitive and cannot contain spaces or special characters such as #, %, $, @, *, &, ^, !, ~ an identifier must start with a letter, but may contain any combination of letters and digits thereafter special characters dot (.) and underscore (_) are allowed

The dot (.) is a regular character in R and that can be confusing as other language (e.g., Java) use dot to designate property or method access, e.g, in Java x.val means that you are accessing the val property of the object x.

Some examples of legal variable names are: df, df2, df.txns, df_all2017. These are some illegal variable names: 2df (cannot start with a digit), rs$all (cannot contain a $; the $ is used to access columns in a dataframe), rs# (only . and _ are allowed in addition to digits and letters).

It is considered good programming practice to give identifiers a sensible name that hints as to what is stored in the variable rather than using random name like x, val, or i33. Identifiers should be named consistently. Many programmers use one of two styles:

• underscores, e.g., interest_rate
• camelCase, e.g., squareRoot, graphData, currentWorkingDirectory

Note that R is case sensitive which means that R treats the identifiers AP and ap as different objects. As a side note, files may also be case sensitive but that depends on the operating system. MacOS and Linux are case sensitive, while Windows is case aware but not case sensitive. For example, on MacOS and Linux there is a difference between “AirPassengers.txt” and “airpassengers.txt” while on Windows there is not. SQL is also not case sensitive. It is a best practice to assume case sensitivity.

Built-in Data Frames

There are numerous data frames built into R that are accessible without loading them first from external files. These data frames are for experimentation and learning and not for actual analytics work. One such built-in data frame is mtcars. To get a list of all built-in data frames, run data().

mtcars

##                      mpg cyl  disp  hp drat    wt  qsec vs am gear carb
## Mazda RX4           21.0   6 160.0 110 3.90 2.620 16.46  0  1    4    4
## Mazda RX4 Wag       21.0   6 160.0 110 3.90 2.875 17.02  0  1    4    4
## Datsun 710          22.8   4 108.0  93 3.85 2.320 18.61  1  1    4    1
## Hornet 4 Drive      21.4   6 258.0 110 3.08 3.215 19.44  1  0    3    1
## Hornet Sportabout   18.7   8 360.0 175 3.15 3.440 17.02  0  0    3    2
## Valiant             18.1   6 225.0 105 2.76 3.460 20.22  1  0    3    1
## Duster 360          14.3   8 360.0 245 3.21 3.570 15.84  0  0    3    4
## Merc 240D           24.4   4 146.7  62 3.69 3.190 20.00  1  0    4    2
## Merc 230            22.8   4 140.8  95 3.92 3.150 22.90  1  0    4    2
## Merc 280            19.2   6 167.6 123 3.92 3.440 18.30  1  0    4    4
## Merc 280C           17.8   6 167.6 123 3.92 3.440 18.90  1  0    4    4
## Merc 450SE          16.4   8 275.8 180 3.07 4.070 17.40  0  0    3    3
## Merc 450SL          17.3   8 275.8 180 3.07 3.730 17.60  0  0    3    3
## Merc 450SLC         15.2   8 275.8 180 3.07 3.780 18.00  0  0    3    3
## Cadillac Fleetwood  10.4   8 472.0 205 2.93 5.250 17.98  0  0    3    4
## Lincoln Continental 10.4   8 460.0 215 3.00 5.424 17.82  0  0    3    4
## Chrysler Imperial   14.7   8 440.0 230 3.23 5.345 17.42  0  0    3    4
## Fiat 128            32.4   4  78.7  66 4.08 2.200 19.47  1  1    4    1
## Honda Civic         30.4   4  75.7  52 4.93 1.615 18.52  1  1    4    2
## Toyota Corolla      33.9   4  71.1  65 4.22 1.835 19.90  1  1    4    1
## Toyota Corona       21.5   4 120.1  97 3.70 2.465 20.01  1  0    3    1
## Dodge Challenger    15.5   8 318.0 150 2.76 3.520 16.87  0  0    3    2
## AMC Javelin         15.2   8 304.0 150 3.15 3.435 17.30  0  0    3    2
## Camaro Z28          13.3   8 350.0 245 3.73 3.840 15.41  0  0    3    4
## Pontiac Firebird    19.2   8 400.0 175 3.08 3.845 17.05  0  0    3    2
## Fiat X1-9           27.3   4  79.0  66 4.08 1.935 18.90  1  1    4    1
## Porsche 914-2       26.0   4 120.3  91 4.43 2.140 16.70  0  1    5    2
## Lotus Europa        30.4   4  95.1 113 3.77 1.513 16.90  1  1    5    2
## Ford Pantera L      15.8   8 351.0 264 4.22 3.170 14.50  0  1    5    4
## Ferrari Dino        19.7   6 145.0 175 3.62 2.770 15.50  0  1    5    6
## Maserati Bora       15.0   8 301.0 335 3.54 3.570 14.60  0  1    5    8
## Volvo 142E          21.4   4 121.0 109 4.11 2.780 18.60  1  1    4    2

mtcars and mtcars print out the first and last six rows of a data frame, respectively. You can specify the number of rows to display.

head(mtcars)

##                    mpg cyl disp  hp drat    wt  qsec vs am gear carb
## Mazda RX4         21.0   6  160 110 3.90 2.620 16.46  0  1    4    4
## Mazda RX4 Wag     21.0   6  160 110 3.90 2.875 17.02  0  1    4    4
## Datsun 710        22.8   4  108  93 3.85 2.320 18.61  1  1    4    1
## Hornet 4 Drive    21.4   6  258 110 3.08 3.215 19.44  1  0    3    1
## Hornet Sportabout 18.7   8  360 175 3.15 3.440 17.02  0  0    3    2
## Valiant           18.1   6  225 105 2.76 3.460 20.22  1  0    3    1

tail(mtcars)

##                 mpg cyl  disp  hp drat    wt qsec vs am gear carb
## Porsche 914-2  26.0   4 120.3  91 4.43 2.140 16.7  0  1    5    2
## Lotus Europa   30.4   4  95.1 113 3.77 1.513 16.9  1  1    5    2
## Ford Pantera L 15.8   8 351.0 264 4.22 3.170 14.5  0  1    5    4
## Ferrari Dino   19.7   6 145.0 175 3.62 2.770 15.5  0  1    5    6
## Maserati Bora  15.0   8 301.0 335 3.54 3.570 14.6  0  1    5    8
## Volvo 142E     21.4   4 121.0 109 4.11 2.780 18.6  1  1    4    2

head(mtcars, 3)

##                mpg cyl disp  hp drat    wt  qsec vs am gear carb
## Mazda RX4     21.0   6  160 110 3.90 2.620 16.46  0  1    4    4
## Mazda RX4 Wag 21.0   6  160 110 3.90 2.875 17.02  0  1    4    4
## Datsun 710    22.8   4  108  93 3.85 2.320 18.61  1  1    4    1

Accessing Rows, Columns, and Elements (Cells) of a Data Frame

Data frames are very similar to tables in relational databases and spreadsheets. They have rows and columns and the intersection of a row and column is a cell (or element). The order of access is row followed by column, e.g, the third element in the fourth row of the data frame mtcars is mtcars[4,3]. Note that this is reversed from the way Excel and other spreadsheets work. The <- is the operator for assignment, although = also works. We will see and use both.

To display a value, either use the print function or just use the variable by itself. To print multiple items, use the paste0 function.

v <- mtcars[4,3]
x = mtcars[4,3]

print(paste0("v = ",v," and x = ",x))

## [1] "v = 258 and x = 258"

Leaving out a dimension (row or column) accesses the entire row or column. The resultant is a data frame with a single row. Often the values must be converted to a vector data type. Conversions of variables from one type to another is done with the family of as.xxxx functions, e.g., as.vector, as.numeric, or as.factor. Vectors can contain numeric or character data but all elements must be of the same type. In R, a list is similar to a vector but it may contain a mix of elements. A matrix is similar to a data frame but it can only contain numbers and it can have more than two dimensions.

Some functions expect data frames, some vectors, some lists. You need to read the documentation of a function to find out. Furthermore, some functions will automatically convert (also called coerce) a variable from one type to the one it requires.

You can also access a column in data frame by its column name. For an entire column you either use the columns position or its name: df[,column] or df$columnName.

# all of row 4; the result is a data frame
r <- mtcars[4,]
sum(r)

## [1] 426.135

c <- mtcars[3,]
c[1,3]

## [1] 108

mtcars[c(1,4)]   # columns 1 and 4 as a new dataframe

##                      mpg  hp
## Mazda RX4           21.0 110
## Mazda RX4 Wag       21.0 110
## Datsun 710          22.8  93
## Hornet 4 Drive      21.4 110
## Hornet Sportabout   18.7 175
## Valiant             18.1 105
## Duster 360          14.3 245
## Merc 240D           24.4  62
## Merc 230            22.8  95
## Merc 280            19.2 123
## Merc 280C           17.8 123
## Merc 450SE          16.4 180
## Merc 450SL          17.3 180
## Merc 450SLC         15.2 180
## Cadillac Fleetwood  10.4 205
## Lincoln Continental 10.4 215
## Chrysler Imperial   14.7 230
## Fiat 128            32.4  66
## Honda Civic         30.4  52
## Toyota Corolla      33.9  65
## Toyota Corona       21.5  97
## Dodge Challenger    15.5 150
## AMC Javelin         15.2 150
## Camaro Z28          13.3 245
## Pontiac Firebird    19.2 175
## Fiat X1-9           27.3  66
## Porsche 914-2       26.0  91
## Lotus Europa        30.4 113
## Ford Pantera L      15.8 264
## Ferrari Dino        19.7 175
## Maserati Bora       15.0 335
## Volvo 142E          21.4 109

mtcars[,2]       # all of column 2

##  [1] 6 6 4 6 8 6 8 4 4 6 6 8 8 8 8 8 8 4 4 4 4 8 8 8 8 4 4 4 8 6 8 4

mtcars[5:7,]     # rows 5 to 7 as a new dataframe

##                    mpg cyl disp  hp drat   wt  qsec vs am gear carb
## Hornet Sportabout 18.7   8  360 175 3.15 3.44 17.02  0  0    3    2
## Valiant           18.1   6  225 105 2.76 3.46 20.22  1  0    3    1
## Duster 360        14.3   8  360 245 3.21 3.57 15.84  0  0    3    4

mtcars$cyl       # column named "cyl"

##  [1] 6 6 4 6 8 6 8 4 4 6 6 8 8 8 8 8 8 4 4 4 4 8 8 8 8 4 4 4 8 6 8 4

mtcars$cyl[2]    # 2nd row in the column "cyl"

## [1] 6

mtcars$cyl[3:9]  # rows 3 to 9 for column "cyl" as a vector

## [1] 4 6 8 6 8 4 4

w <- mtcars$mpg
mean(w)

## [1] 20.09062

Aggregation and Statistical Functions

As a language with its origin in statistics and statistical data processing, R has a plethora of statistical functions. Some of the most important functions for data processing are shown below. Consult online documentation and statistics references for more information, e.g., How To Get Descriptive Statistics In R and Base R Statistical Functions.

# import data for descriptive statistics, the built-in data frame 'warpbreaks'
data(warpbreaks)

# summary descriptive statistics
summary(warpbreaks)

##      breaks      wool   tension
##  Min.   :10.00   A:27   L:18   
##  1st Qu.:18.25   B:27   M:18   
##  Median :26.00          H:18   
##  Mean   :28.15                 
##  3rd Qu.:34.00                 
##  Max.   :70.00

There are thousands of functions across hundreds of packages (external libraries of functions written for specific purposes, e.g., data mining, statistical inference, machine learning, image processing, web development, visualization, XML processing, SQL, and so forth). You will learn them over time – and it’s unlikely you will ever learn all of them, so have patience. For a package to be usable in an R project it must be installed; installation is done once. Then every time you need an installed package in some R code, you must load it using the library function.

The psych library (aka package) is particularly useful for statistical analysis of data.

# import the psych package -- if this fails, then first install the package by uncommenting the next line
#install.packages("psych")
library(psych)

# detailed descriptive statistics
describe(warpbreaks)

##          vars  n  mean    sd median trimmed   mad min max range skew kurtosis
## breaks      1 54 28.15 13.20   26.0   26.41 11.86  10  70    60 1.24     1.33
## wool*       2 54  1.50  0.50    1.5    1.50  0.74   1   2     1 0.00    -2.04
## tension*    3 54  2.00  0.82    2.0    2.00  1.48   1   3     2 0.00    -1.56
##            se
## breaks   1.80
## wool*    0.07
## tension* 0.11

It is not unusual that data is divided into groups and that you may want categorical statistics for it. The aggregate function is among the easiest methods of doing that. The dataset warpbreaks has data for two different kinds of wool, A and B. Using the aggregate function you can calculate statistics for each wool separately. This is similar to a GROUP BY clause in SQL.

# Aggregate Function Example: Summary Statistics by Group
aggregate(breaks~tension , data= warpbreaks, mean)

##   tension   breaks
## 1       L 36.38889
## 2       M 26.38889
## 3       H 21.66667

Installing and Loading Packages

Installing Packages

To ensure that packages are automatically installed, you can use the followign code. That way your code becomes portable.

if("RSQLite" %in% rownames(installed.packages()) == FALSE) {
  install.packages("RSQLite")
}

library("RSQLite")

In the above code the function installed.packages() returns a list of the names of all installed packages. The operator %in% is a set operator that checks if “RSQLite” is one of the returned names. If it is, the Boolean expression evaluates to \(TRUE\), otherwise \(FALSE\). If it is false, then it means the package is not installed and the optional code that installs the package is executed. The way, the loading of the package with library(“RSQLite”) cannot fail.

Simple Plotting and Visual Data Exploration

Scatter Plot

plot(x = warpbreaks$breaks, y = warpbreaks$tension)

Q-Q Plot for Normality Checking

A 45 degree line indicates a normal distribution of the data, while a “hockey stick” curve indicates a non-normal or skewed distribution. The column below is “reasonably” normally distributed. The second QQ Plot, plots the log-transformed values which better fit a normal (Gaussian) distribution.

qqnorm(warpbreaks$breaks)

qqnorm(log(warpbreaks$breaks))

Note that the gplot2 package provide much more sophisticated visualization mechanisms and should be used in practice to produce plots for publication.

Data Frame Dimensions and Structure

nrow(mtcars)

## [1] 32

ncol(mtcars)

## [1] 11

str(mtcars)

## 'data.frame':    32 obs. of  11 variables:
##  $ mpg : num  21 21 22.8 21.4 18.7 18.1 14.3 24.4 22.8 19.2 ...
##  $ cyl : num  6 6 4 6 8 6 8 4 4 6 ...
##  $ disp: num  160 160 108 258 360 ...
##  $ hp  : num  110 110 93 110 175 105 245 62 95 123 ...
##  $ drat: num  3.9 3.9 3.85 3.08 3.15 2.76 3.21 3.69 3.92 3.92 ...
##  $ wt  : num  2.62 2.88 2.32 3.21 3.44 ...
##  $ qsec: num  16.5 17 18.6 19.4 17 ...
##  $ vs  : num  0 0 1 1 0 1 0 1 1 1 ...
##  $ am  : num  1 1 1 0 0 0 0 0 0 0 ...
##  $ gear: num  4 4 4 3 3 3 3 4 4 4 ...
##  $ carb: num  4 4 1 1 2 1 4 2 2 4 ...

mtcars[nrow(mtcars),]   # last row only of a data frame   

##             mpg cyl disp  hp drat   wt qsec vs am gear carb
## Volvo 142E 21.4   4  121 109 4.11 2.78 18.6  1  1    4    2

Adding and Removing Columns from a Data Frame

To add a new column, you simply “access” the column or use a new name for the column. Note in the example below that you can operate on entire columns (as vectors) and the operation is applied to each pair of values in the two vectors in the operation. This is much more efficient than using loops as is necessary in other programming languages.

# copy the data frame mtcars to a new data frame df
df <- mtcars

# create a new column "dispcyl" which is the displacement per cylinder
df$dispcyl <- df$disp / df$cyl

head(df)

##                    mpg cyl disp  hp drat    wt  qsec vs am gear carb  dispcyl
## Mazda RX4         21.0   6  160 110 3.90 2.620 16.46  0  1    4    4 26.66667
## Mazda RX4 Wag     21.0   6  160 110 3.90 2.875 17.02  0  1    4    4 26.66667
## Datsun 710        22.8   4  108  93 3.85 2.320 18.61  1  1    4    1 27.00000
## Hornet 4 Drive    21.4   6  258 110 3.08 3.215 19.44  1  0    3    1 43.00000
## Hornet Sportabout 18.7   8  360 175 3.15 3.440 17.02  0  0    3    2 45.00000
## Valiant           18.1   6  225 105 2.76 3.460 20.22  1  0    3    1 37.50000

Create a New Data Frame

Data frames are created in various ways: use the <code<>data.frame function, load a CSV file, execute a SQL query, or as a result of many package functions.

Load a Data Frame from CSV

Quick note: Capitalization in path and file names does not matter in Windows, but does matter on MacOS and Linux. Furthermore, note that even in Windows the path delimiter is a forward slash / and not the usual backwards slash \. The \ is an “escape” character and used to inject non-printable characters into a string (text), e.g., “This string contains”quotes”.” which would be written in R as “this string contains \”quotes\“.”

Also, the parameters header = F instructs read.csv not to interpret the first line as header labels. Of course, if there are no labels, then you need to define your own.

Aside from CSV files, R can also load a number of other file format using various packages, including XML, Excel, SPSS, MatLab, among many others.

df <- read.csv(file = "customertxndata.csv", header = F)
head(df)

df <- read.csv(file = "customertxndata.csv", 
               header = F,
               col.names = c("numVisits","NumTxn","OS","Gender","TotSp"))
head(df)

Note that the value of the ‘Male’ column in the first row is NA which is the way that R indicates a missing data value. It is not 0 or an empty string, it is unknown. So, statistical functions and algebraic operations would result in an NA as well.

Strings vs Factors

The factor data type encodes categorical data, e.g., the value of a variable is one of a fixed value set. Many statistical functions in R require categorical variables to be of type factor. However, often, during data processing, we need the actual text rather than having it encoded as a factor (which is actually stored in R as an integer for efficiency). So, when reading a CSV file you need to decide if you want text columns to be character strings or factors by setting the stringsAsFactors parameter.

You may use either F and T or FALSE and TRUE.

df <- read.csv(file = "customertxndata.csv", 
               header = F,
               stringsAsFactors = FALSE,
               col.names = c("numVisits","NumTxn","OS","Gender","TotSp"))
head(df)

Create a New Data Frame

The code below creates a new data frame from column vectors. Notice how the column names are the names of the vectors. A new vector is created with the c function, e.g., v <- c(3,5,1,9).

df1 <- data.frame(state = c('Arizona','Georgia', 'New York','Indiana','Washington','Texas'),
                  code = as.factor(c('AZ','GA','NY','IN','WA','TX')),
                  score = c(62,47,55,74,31,85))

head(df1)

##        state code score
## 1    Arizona   AZ    62
## 2    Georgia   GA    47
## 3   New York   NY    55
## 4    Indiana   IN    74
## 5 Washington   WA    31
## 6      Texas   TX    85

Search Data Frames

There are two important functions for “searching” data frames: which and any. The code below uses the built-in Orange data frame which contains measurements of orange trees. It has three columns: the tree, the age of the tree (days since 1968/12/31), and circumference (in mm).

which

df <- Orange

head(df)

## Grouped Data: circumference ~ age | Tree
##   Tree  age circumference
## 1    1  118            30
## 2    1  484            58
## 3    1  664            87
## 4    1 1004           115
## 5    1 1231           120
## 6    1 1372           142

# find all rows where the circumference is more than 200mm
rs <- which(df$circumference > 200)

# display all rows where the circumference is more than 200mm
df[rs,]

## Grouped Data: circumference ~ age | Tree
##    Tree  age circumference
## 13    2 1372           203
## 14    2 1582           203
## 27    4 1372           209
## 28    4 1582           214

# compound conditions are possible with & (and), | (or), and ! (not)
rs2 <- which(df$circumference > 200 & df$age < 1500)
rs3 <- which(df$circumference < 200 | !(df$age < 1500))
rs4 <- which(df$circumference > 400 | df$age > 1500)

rs2

## [1] 13 27

rs3

##  [1]  1  2  3  4  5  6  7  8  9 10 11 12 14 15 16 17 18 19 20 21 22 23 24 25 26
## [26] 28 29 30 31 32 33 34 35

rs4

## [1]  7 14 21 28 35

mean(df[rs4,2])

## [1] 1582

mean(df$age[rs3])

## [1] 894.8788

In the above example rs <- which(df$circumference > 200) finds all rows in the data frame df where circumference > 200. The rows are saved in rs.

any

The any function returns \(TRUE\) or \(FALSE\) depending on whether any column (or row) in the dataframe satisfies a Boolean expression.

# is there any tree with age > 2000?
any(df$age > 25)

## [1] TRUE

Using SQL with Data Frames

The package sqldf allows you to use SQL to access, process, search, and aggregate data in data frames. The sqldf package actually loads the data frame into an in-memory SQLite database.

For more information on sqldf, consult Manipulate R Data Frames Using SQL

The tidyverse package also contains numerous functions for processing data frames in a SQL-like manner.

#install.packages("sqldf")
library(sqldf)

## Loading required package: gsubfn

## Loading required package: proto

## Warning in doTryCatch(return(expr), name, parentenv, handler): unable to load shared object '/Library/Frameworks/R.framework/Resources/modules//R_X11.so':
##   dlopen(/Library/Frameworks/R.framework/Resources/modules//R_X11.so, 0x0006): Library not loaded: /opt/X11/lib/libSM.6.dylib
##   Referenced from: <FFA47D77-8F35-36FC-B0E5-38351B8D9512> /Library/Frameworks/R.framework/Versions/4.3-arm64/Resources/modules/R_X11.so
##   Reason: tried: '/opt/X11/lib/libSM.6.dylib' (no such file), '/System/Volumes/Preboot/Cryptexes/OS/opt/X11/lib/libSM.6.dylib' (no such file), '/opt/X11/lib/libSM.6.dylib' (no such file), '/Library/Frameworks/R.framework/Resources/lib/libSM.6.dylib' (no such file), '/Library/Java/JavaVirtualMachines/jdk-11.0.18+10/Contents/Home/lib/server/libSM.6.dylib' (no such file)

## tcltk DLL is linked to '/opt/X11/lib/libX11.6.dylib'

## Could not load tcltk.  Will use slower R code instead.

# perform SQL queries on the built-in 'iris' data frame
sqldf("select * from iris limit 5")

##   Sepal.Length Sepal.Width Petal.Length Petal.Width Species
## 1          5.1         3.5          1.4         0.2  setosa
## 2          4.9         3.0          1.4         0.2  setosa
## 3          4.7         3.2          1.3         0.2  setosa
## 4          4.6         3.1          1.5         0.2  setosa
## 5          5.0         3.6          1.4         0.2  setosa

sqldf("select count(*) from iris")

##   count(*)
## 1      150

sqldf("select Species, count(*) from iris group by Species")

##      Species count(*)
## 1     setosa       50
## 2 versicolor       50
## 3  virginica       50

# note that each sqldf query actually returns a data frame
rs <- sqldf("select Species, count(*) as num from iris group by Species")
mean(rs$num)

## [1] 50

Load Data from a Database via SQL

To read data from a database into R, follows these steps:

• open connection to database
• build SQL query
• execute SQL query by sending to database
• capture result in dataframe

Connecting to a database is done in a database-specific way and each database is different. Packages specific to the database need to be loaded (of course, after installation). The code below assumes that the package RSQLite for connecting to SQLite databases is installed but not loaded. To use SQLite from R does not actually require that SQLite is downloaded and installed.

To connect to a database you need to know where the database is located. For most client/server databases like MySQL you need to know the server’s IP address on which the database runs. For SQLite you need the database file path (as SQLite does run not on an actual remote server).

To run a query (retrieve data) you most commonly use the dbGetQuery function. To perform an INSERT, UPDATE, DELETE, CREATE TABLE, DROP TABLE, ALTER TABLE you need to use dbSendQuery.

The example below performs a direct connect to the database and uses functions to retrieve data from the database via SQL. This is an alternative to embedding SQL chunks ({sql}) in an R Notebook.

library(RSQLite)

# connect to the SQLite database in the specified file
db.conn <- dbConnect(SQLite(), dbname="CoffeeDB.sqlitedb")

# construct a SQL query
sqlCmd = "SELECT * FROM coffees"

# send the SQL query to the database
rs = dbGetQuery(db.conn, sqlCmd)

# print part of the result table
head(rs,3)

##   id  coffee_name price
## 1  1    Colombian  7.99
## 2  2 French_Roast  8.99
## 3  3     Espresso  9.99

Example: Compute Sum of Numbers

Problem Statement

Given a vector (or array) of numbers, calculate the sum of those numbers. Solve the problem first using loops and then using recursion. Write a function and then call the function. The function should ignore any non-numeric elements.

Test Cases

1. Given the vector <2,6,1,0,-4,99>, the function should return 104.
2. Given an empty vector <>, the function should return NA.
3. Given the vector <2,6,1,‘11’>, the function should return 9.
4. Given the vector <‘2’,‘Two’,’‘,’11’>, the function should return 0.
5. Given the empty vector <>, the function should return 0.

Sample Solution

Let’s start by working out the algorithm for only numbers and not (yet) using a function. We can calculate the sum by adding each number to some “accumulator” variable, e.g., the sum of <2,6,1> is x = 2, x = x + 6, x = x + 1. In this example, x is the accumulator variable. Using an accumulator is a common practice in these types of problems. The accumulator could either start at 0 or at the first element. Having it start at 0 is simpler because it would account for an empty vector that doesn’t have a first element.

When you write x = x + 1, remember that the evaluation of = is from right to left, i.e., the right side is evaluated first and then the value of the result of that expression is assigned to the variable on the left side. Let’s say that x = 5 before you write the aforementioned statement. So, the expression x + 1 is 5 + 1 which evaluates to 6. That value is then assigned to x, so after that x = 6.

v = c(3, 6, 1)         # a vector/array of numbers

x = 0                  # x is our accumulator; it starts at 0

# loop through each element of the vector
for (i in 1:length(v)) {
  # add the next element in the vector to x
  x = (x + v[i])
}

print (x)

## [1] 10

What would happen if the vector is empty? Test the above code. Note that it still works if v = c(). Test it with negative numbers in the vector. It still works. But what about strings? Now it fails. So, we need to improve the program to ignore negative numbers. All programming languages have some way of guarding against that. Some languages like Java and C++ require that all elements of a vector are of the same declared type, so adding a string would cause a compile-time error. No worries in those languages. But untyped and uncompiled languages like R, Python, and JavaScript require run-time checks. So, let’s add one. We need a function that checks the type of a variable. R has functions such as is.numeric for that purpose.

But there is another problem. Vectors in R are of a single type, i.e., all elements must be of the same type. R attempts to coerce (aka cast) the elements in the vector to a common type. Since a string cannot be converted to a number, it’ll convert the numbers to text. Try it out: what is the value of v[1]? It’s “3” – a string. So, we need to convert each element to a number and ignore the number it fails. as.numeric will attempt a conversion of any text containing a number to a number, e.g. it will convert “3” to 3 and “3.14” to 3.14. Again, if you are in doubt or are not sure, try it out – experiment. In fact, create a new Notebook or just open up the R console and type in the statements; see what happens.

v = c(3, 6, "two", 1)
print(v[1])

## [1] "3"

print(as.numeric(v[1]))

## [1] 3

print(as.numeric(v[3]))

## Warning in print(as.numeric(v[3])): NAs introduced by coercion

## [1] NA

So, if the conversion to a number is not possible, it will return NA. NA is similar to null or nil in many other languages. We can check of a value is NA by using the function is.na; we cannot simply say if (v[i] == NA) – NA is not a value so you cannot use the comparison operator. Many other languages work the same way.

v = c(3, 6, "two", 1)         

x = 0                         # x is our accumulator; it starts at 0

# loop through each element of the vector
for (i in 1:length(v)) {
  # attempt to convert the text to a number
  k = as.numeric(v[i])
  # add the next element in the vector to x if it's not NA
  if (!is.na(k))
    x = (x + k)
}

print (x)

## [1] 10

Does our code still work if the vector is all numbers? What if it’s all strings? Let’s check that. Remember to always test prior test cases to ensure that some new code did not break your old code. Software developers call that regression testing.

v = c(3, 6, 2, 1)         

x = 0                         # x is our accumulator; it starts at 0

# loop through each element of the vector
for (i in 1:length(v)) {
  # attempt to convert the text to a number
  k = as.numeric(v[i])
  # add the next element in the vector to x if it's not NA
  if (!is.na(k))
    x = (x + k)
}

print (x)

## [1] 12

Indeed, it still works. Now what about all text or empty… this is getting difficult without repeating code. If you find yourself copying and pasting code, it means that you need a function. Let’s put the code into a function. We’ll also add code to guard against an empty vector.

sumVector <- function (v)
{
  x = 0    # x is an accumulator; it starts at 0
  
  if (length(v) == 0)
    return (0)
  
  # loop through each element of the vector
  for (i in 1:length(v)) {
    # attempt to convert the text to a number
    k = as.numeric(v[i])
    # add the next element in the vector to x if it's not NA
    if (!is.na(k))
      x = (x + k)
  }
  
  return (x)
}

Quick comment on R: strings can be enclosed in either double or single quote, e.g., ‘text’ is the same as “text”. Whether you use single or double quotes depends on context. For example, if you want to use the string Bob’s Car, then you would need to enclode that in double quotes: “Bob’s Car”.

v = c(2,6,1,0,-4,99)         
x = sumVector(v)
print (x)

## [1] 104

v = c(2,6,1,'11')         
x = sumVector(v)
print (x)

## [1] 20

v = c()         
x = sumVector(v)
print (x)

## [1] 0

v = c('2','Two','','11')         
x = sumVector(v)
print (x)

## [1] 13

This takes care of our originally defined test cases. Are there test cases we forgot to account for? Can you think of other situations? What if someone passed a list or a matrix or a single number? Would it still work? In R, Python, and JavaScript, as well as other non-compiled languages, you need to add many more run-time checks.

One quick programming: the variable x in the function is local to the function and is not known or even visible outside the function. So, it’s a totally different variable than the x in the test code. This is so because R uses, like most modern languages, local scoping. Anything declared within curly braces {…} is local to that scope.

v = 99         
x = sumVector(v)
print (x)

## [1] 99

v = list(3, c(34, 55, 99))  

# this will not work as v is a list not the expected vector
#   x = sumVector(v)
print (x)

## [1] 99

It works for single numbers but not for lists, so let’s add a check to ensure that the passed argument is a vector. Again, this is a common need for many languages: run-time type checking.

sumVector <- function (v)
{
  # if v is a list or not a vector, return 0
  if (!is.vector(v) || is.list(v))
    return (0)
  
  if (length(v) == 0)
    return (0)
  
  # v is a valid vector; let's add the numbers and ignore strings
  
  x = 0    # x is an accumulator; it starts at 0
  
  # loop through each element of the vector
  for (i in 1:length(v)) {
    # attempt to convert the text to a number
    k = as.numeric(v[i])
    # add the next element in the vector to x if it's not NA
    if (!is.na(k))
      x = (x + k)
  }
  
  return (x)
}

v = 99         
x = sumVector(v)
print (x)

## [1] 99

v = list(3, c(34, 55, 99))         
x = sumVector(v)
print (x)

## [1] 0

There is an interesting programming design argument in the above code: what is the correct return value when the passed argument is not a vector? Should it be 0 or should it be NA as it cannot be computed. Generally, returning 0 implies that the calculation was successful and evaluated to 0, but that’s not the case here, so returning NA might allow the caller of the function to catch that. In Java and C++ we might have thrown an exception, but R does not support exceptions.

Note how we built the code in stages. It was not all done at once. Start simple, keep adding to it, take care of more and more situations. Eventually you have solved the entire problem.

Native R Solution

Before we leave this example, let’s take a look at a more “native” R implementation that takes advantage of the inherent vector processing capabilities (alongside potential hardware-based vectorization of the code) and attendant significant performance improvements.

v = c(2,6,1,0,-4,99)         
x = sum(v)
print (x)

## [1] 104

The solution below prints not just the sum of the elements but the sum of squares of the elements. Notice how the R implementation does not require the use of a loop because any operation on a vector is automatically carried out for each element of the vector – in effect the loop is implied.

v = c(2,6,1,0,-4,99)         
x = sum(v ^ 2)
print (x)

## [1] 9858

Example: Find Time Difference

Problem Statement

Given two time values based on a 24 hour clock, determine how much time has elapsed between the first and the second time. The time is given in the format HH:MM:SS. HH is between 0 and 23, and MM and SS are between 0 and 59. Values less than 10 have a leading zero, e.g., 07:15:00. Midnight is 00:00

The output should at first be the number of seconds between the two time values. Then, the program should be improved to display the difference in the number of hours, the number of minutes, and the number of seconds that have passed from the first to the second time, in the form HH HOURS MM MINUTES SS SECONDS.

If the second time is “before” the first time, assume that it is the following day.

Test Cases

• 10:03:43 15:00:58 should display 4 HOURS 57 MINUTES 15 SECONDS
• 07:25:00 20:52:14 should display 13 HOURS 27 MINUTES 14 SECONDS
• 16:30:30 05:40:10 should display 13 HOURS 9 MINUTES 40 SECONDS

Sample Solution

Solution I

A few quick explanations on R syntax. The period (dot) is considered a valid character for identiers in R; it is not a property access operator like in Java or C++. FOr assignment, one can use = or <- with the latter being more common.

This solution converts each time value into seconds and then takes the difference, resulting in the time difference in seconds as long as we do not cross a day boundary.

# define two time values broken down into three variables

t1.hh = 20
t1.mm = 00
t1.ss = 00

t2.hh = 23
t2.mm = 01
t2.ss = 30

# convert each time value to all seconds

t1 = ((t1.hh * 60 * 60) + (t1.mm * 60) + t1.ss)
t2 = ((t2.hh * 60 * 60) + (t2.mm * 60) + t2.ss)

d = t2 - t1

print(d)

## [1] 10890

Solution II

In this improvement step, we will convert the result from seconds into the required format. One issue with integer division in most languages is that it will result in a fraction (floating point number). We will need to round down or coerce the result back into an integer. So, we will first find the total number of hours in the difference by dividing the difference in seconds by 360 (60 * 60 seconds per hour). The remaining seconds are then converted into minutes, and finally, the remaining seconds are left over. Perform the calculation by hand on a piece of paper and ensure that you understand our “algorithm”. In fact, using examples and working it out by hand will help you envision how you might program this.

As an aside, the calculation of the remainder could have also been done using the modulus operator; just goes to show that there is often more than one good way to solve a programming problem. Never be afraid to experiment, but start with the simplest solution first. Worry about correctness first, then about elegance and speed of execution.

In the code below, the paste() function is used in R to concatenate strings. paste() inserts a space between the strings, while paste0() does not.

# define two time values broken down into three variables

t1.hh = 00
t1.mm = 30
t1.ss = 00

t2.hh = 03
t2.mm = 30
t2.ss = 30

# convert each time value to all seconds

t1 = ((t1.hh * 60 * 60) + (t1.mm * 60) + t1.ss)
t2 = ((t2.hh * 60 * 60) + (t2.mm * 60) + t2.ss)

d = t2 - t1

# find number of hours, minutes, and seconds

# convert seconds to whole hours
hh = as.integer(d / (60 * 60))

# find the remaining seconds
r = (d - (hh * (60 * 60)))

# convert seconds to whole minutes
mm = as.integer(r / 60)

# find the remaining seconds (using the mod operator this time)
ss = ( r - (mm * 60))

# format the required output
out <- paste(hh, "HOURS", mm, "MINUTES", ss, "SECONDS")
print(out)

## [1] "3 HOURS 0 MINUTES 30 SECONDS"

An issue with Solution II is that it does not consider day boundaries, so let’s work on that next. Example: What is the time difference between 23:00:00 and 03:00:00? It is exactly four hours as it’s 23h today and 3h the next morning – we are crossing days. So, how do we implement this? One approach is to check if time #1 is before or after time #2, i.e., one value is larger or smaller than the other. For the normal case, time #1 < time #2, but if the day boundary is crossed, then time #1 > time #2. If they are equal, then the difference is zero and it doesn’t matter which calculation you use. Now, when we detect that time #1 > time #2, then we need to calculate the time remaining until midnight and then the difference between midnight and the time the next day and add those two time values.

So, we have two time calculations – might be better to first define a function so we do not have duplicate code. The function will calculate the time difference and return the time difference in seconds.

Solution III

Let’s just write the function first without worrying about the day boundary crossing.

The code below makes use of the R substr() function which extracts characters from a text string starting at one position and ending at another position. For example, substr(“abcdef”, 2, 4) results in “bcd”. Every programming language has a similar function, although it might require an external string library. The result of an extraction from a string is a string despite being just digits – 12 is not the same as ‘12’. One is interpreted by a computer program as an integer while the other is text (a string). We need to convert. TO convert a string of digits to an integer, use as.integer() in R; other languages have similar functions.

# FUNCTION: timeDiff (t1, t2)
# Calculates the difference between two time values in the form HH:MM:SS
# Returns the difference in seconds.

timeDiff <- function (t1, t2)
{
  t1.hh = as.integer(substr(t1, 1, 2))
  t1.mm = as.integer(substr(t1, 4, 5))
  t1.ss = as.integer(substr(t1, 7, 8))
  
  t2.hh = as.integer(substr(t2, 1, 2))
  t2.mm = as.integer(substr(t2, 4, 5))
  t2.ss = as.integer(substr(t2, 7, 8))
  
  # convert each time value to all seconds
  
  t1 = ((t1.hh * 60 * 60) + (t1.mm * 60) + t1.ss)
  t2 = ((t2.hh * 60 * 60) + (t2.mm * 60) + t2.ss)
  
  d = t2 - t1
  
  return (d)
}

######## TEST CODE ######## 

t1 = "00:00:00"
t2 = "03:59:59"

d <- timeDiff(t1, t2)

# convert seconds to whole hours
hh = as.integer(d / (60 * 60))

# find the remaining seconds
r = (d - (hh * (60 * 60)))

# convert seconds to whole minutes
mm = as.integer(r / 60)

# find the remaining seconds (using the mod operator this time)
ss = ss = ( r - (mm * 60))

# format the required output
out <- paste(hh, "HOURS", mm, "MINUTES", ss, "SECONDS")
print(out)

## [1] "3 HOURS 59 MINUTES 59 SECONDS"

Note how we added a testing code block. Remember: always perform unit testing and test your functions.

So, now let’s deal with the day boundary crossing issue.

Solution IV

# FUNCTION: timeDiff (t1, t2)
# Calculates the difference between two time values in the form HH:MM:SS
# Returns the difference in seconds.

timeDiff <- function (t1, t2)
{
  t1.hh = as.integer(substr(t1, 1, 2))
  t1.mm = as.integer(substr(t1, 4, 5))
  t1.ss = as.integer(substr(t1, 7, 8))
  
  t2.hh = as.integer(substr(t2, 1, 2))
  t2.mm = as.integer(substr(t2, 4, 5))
  t2.ss = as.integer(substr(t2, 7, 8))
  
  # convert each time value to all seconds
  
  t1.secs = ((t1.hh * 60 * 60) + (t1.mm * 60) + t1.ss)
  t2.secs = ((t2.hh * 60 * 60) + (t2.mm * 60) + t2.ss)
  
  # is t1 < t2?
  if (t1.secs <= t2.secs) {
    d = t2.secs - t1.secs
  } else {
    # we are crossing a day boundary, so let's find the time
    # until midnight and then the time from midnight to
    # the second time; note the recursive call and adding
    # one extra second since we are going until the time
    # that is one minute before midnight as midnight is 
    # 00:00:00 and that would break the code
    
    ttm = (timeDiff(t1, "23:59:59") + 1)
    taf = (timeDiff("00:00:00", t2))
    
    d = (ttm + taf)
  }
  
  return (d)
}

######## TEST CODE ######## 

t1 = "23:30:45"
t2 = "03:12:00"

d <- timeDiff(t1, t2)

# convert seconds to whole hours
hh = as.integer(d / (60 * 60))

# find the remaining seconds
r = (d - (hh * (60 * 60)))

# convert seconds to whole minutes
mm = as.integer(r / 60)

# find the remaining seconds (using the mod operator this time)
ss = (r - (mm * 60))

# format the required output
out <- paste(hh, "HOURS", mm, "MINUTES", ss, "SECONDS")
print(out)

## [1] "3 HOURS 41 MINUTES 15 SECONDS"

How would you solve the problem without the use of recursion? Is that solution more elegant? Easier to understand?

---

Files & Resources

All Files for Lesson 6.101

---

References

No references.

Errata

Let us know.