Lecture: Data Manipulation and Transformation

Actuarial Data Science - Open Learning Resource

Author

Fei Huang, UNSW Sydney

Introduction

Motivation

  • It is rare that you get the data in exactly the form you need.
  • You may need to create new variables or summaries, or
  • Simply rename variables or reorder observations to make the data easier to work with.

In practice, a large part of an actuary’s work involves cleaning and reshaping data to ensure that it is trustworthy and easy to analyse.

In this lecture, we introduce a small set of powerful verbs (filter(), select(), mutate(), summarise(), group_by(), and joins) that can be combined to express many common data-wrangling tasks clearly.

Using R to Manipulate Data

  • R package: dplyr (a core member of tidyverse) for data manipulation and transformation
    • Note: dplyr overwrites some functions in base R (e.g. filter(), lag()). To use the base versions (after loading dplyr), specify stats::filter() and stats::lag().
  • Data: thenycflights13 package, which contains data on flights departing from New York City in 2013
  • We use ggplot2 to help us explore and understand the data.
#install.packages("nycflights13")
library(nycflights13)
library(tidyverse)

Data

flights #tibble, tweaked data frame to work better in tidyverse
view(flights) # will open the dataset in the RStudio viewer

See also: Filter Data

Types of variables

  • int: integers
  • dbl: doubles (real numbers)
  • chr: character vectors (strings)
  • dttm: date-times (a date + time)
  • lgl: logical vectors (TRUE or FALSE)
  • fctr: factors (categorical variables with fixed levels)
  • date: dates.

These are the common variable types used in tidyverse data frames (tibble)

Data Manipulation Functions

Functions for Data Manipulation

Functions in the dplyr package:

  • %>%: pipe operator, used to chain multiple operations together
  • glimpse(): a glimpse into the data and its structure
  • filter(): select observations (rows) that satisfy given conditions
  • arrange(): reorder rows based on variable values
  • select(): choose a subset of variables (columns)
  • mutate(): create new variables or transform existing ones
  • summarise(): reduce multiple values to a single summary
  • group_by(): group data so operations are performed within each group

Filter: Introduction

#The first argument is the name of the data frame. 
#The subsequent arguments are the expressions that filter the data frame.
filter(flights, month == 1, day == 1)
# use the assignment operator, <- to save the result
#jan1 <- filter(flights, month == 1, day == 1)
# Save and print the result at the same time
#(dec25 <- filter(flights, month == 12, day == 25))

See also: Full Data

Filter: Comparisons

To use filter() effectively, you need to know how to select observations using comparison operators. R provides the standard set:

  • > greater than
  • >= greater than or equal to
  • < less than
  • <= less than or equal to
  • != not equal to
  • == equal to

Note: be cautious when using == with floating point numbers. Consider using near() instead.

Filter: Logical Operators

  • Multiple arguments in filter() are combined with “and”: every condition must be true for a row to be included in the output.

Other logical operators:

  • &: “and”
  • |: “or”
  • !: “not”
  • x %in% y select rows where x is one of the values in y

According to De Morgan’s laws:

  • !(x & y) is equivalent to !x | !y
  • !(x | y) is equivalent to !x & !y

Exercise: Filtering Multiple Months

Question:

Find all flights that departed in November or December.

filter(flights, month %in% c(11, 12))
#Alternatively, use the following
#filter(flights, month == 11 | month == 12)

Exercise: Combining Logical Conditions

Question:

Find flights that were not delayed (on arrival or departure) by more than two hours.

filter(flights, !(arr_delay > 120 | dep_delay > 120))