[1] 1.4142143 Wrangling data
Learn the building blocks of data analysis: loading, filtering, manipulating and saving data for crime mapping.
To load the interactive tutorial for this chapter, copy and paste the following code into the RStudio console:
and press Enter.
3.1 Introduction
A major step in using any data to make decisions or draw conclusions is data wrangling: the process of transforming data from the format in which we originally have it to the format needed to analyse and present it to our audience.
Start off by watching this video that walks through the different steps in wrangling a dataset. We will cover all the steps in the video in more detail during the rest of this tutorial.
3.1.1 Functions
In this tutorial we will learn how to wrangle data in R using functions – specialised pieces of code that do something to the data we give it. The code to use a function (sometimes called calling the function) has two parts: the function name followed by a pair of parentheses, inside which are zero or more arguments separated by commas. Arguments are a way of providing input that a function works on, or to fine-tune the way the function works (we will see many examples of this later). Remember that you can identify a function in R because the name will always have parentheses after it.
One basic R function is sqrt(), which calculates the square root of a number. The sqrt() function has only one argument: the number that we want to find the square root of.
Type sqrt(2) in the box below and click Run Code to calculate the square root of 2.
When you run code in R, by default R prints the output of your code – in this case, just the number 1.414214 (for now, you can ignore the number [1] in square brackets).
3.1.2 Packages
R contains thousands of different functions that do different things. A few functions are contained in the default installation of R that you have already installed (this is sometimes referred to as base R). But most functions are contained in packages, which are extensions to base R. Most packages focus on a particular type of data analysis, so that there are packages devoted to time-series analysis, testing whether events are clustered in particular places, network analysis and thousands of other tasks. Packages are often developed by experts in the field, and are typically updated to introduce new features.
To use a package in R, we must do two things:
- install the package, which we have to do just once on each computer we want to use, then
- load the package, which we have to do each time we restart R (which happens when we open RStudio or switch between projects).
The install.packages() function downloads and installs packages from the Comprehensive R Archive Network (universally known as CRAN), which contains about 20,600 different packages. Some packages that are still in the early stages of development are not available on CRAN, but all the packages we will use are there.
So to install (for example) the package called tidyverse, which we will use extensively in this tutorial, we would run the R code:
We only have to install a package once for each computer that we will use to run R, although we would have to do it again if we updated to a new version of R. Once a package is installed on our computer, we have to load it so that we can use it in our code. We load packages using the library() function, which should probably have been called load_package() but isn’t. So to load the tidyverse package, we run the code:
Many packages are focused on specialist tasks and so are only used occasionally, but a few packages are likely to be useful in almost all the code we write. Fortunately, packages can themselves load other packages, and all the main packages we need are themselves loaded by the tidyverse package. That is why you will often see library(tidyverse) at the top of R code in subsequent tutorials – that short line of code loads several packages containing hundreds of functions that we can use in data analysis.
3.2 Loading data
Before we can do anything with any data, we have to load it into R. In this course we will read tabular data in comma-separated values (CSV) and Excel formats, as well as spatial data in different formats (because there are lots of ways to store spatial data). We will learn how to read CSV and Excel data now, but leave loading spatial data until later.
Tabular data contains multiple columns where every column has the same number of rows. For example, crime data might have columns for the type of crime, date and address at which the crime occurred.
| type | date | address |
|---|---|---|
| homicide | 28 Mar 2023 | 274 Main St |
| non-residential burglary | 07 Nov 2023 | 541 Station Rd |
| personal robbery | 10 Mar 2024 | 10 North Av |
3.2.1 Loading CSV data
Data stored in CSV format is easy to load with the read_csv() function from the readr package. readr is one of the packages loaded by the tidyverse package, so all we need to do to use this package is include the code library(tidyverse) on the first line of our R script. We will use comments (lines of code beginning with #) to help explain as we go.
# Load the tidyverse suite of packages, including the readr package
# that contains the read_csv() function
library(tidyverse)
# We can load data from a file in the same folder as our R script
san_fran_rob <- read_csv("san_francisco_robbery.csv")
# Or another folder on your computer ('../' is short for the parent
# folder of the current folder)
san_fran_rob <- read_csv("../san_francisco_robbery.csv")
# Or directly from a file online
san_fran_rob <- read_csv("http://example.com/san_francisco_robbery.csv")In each of these examples, the code stores the result of the read_csv() function in an object named san_fran_rob. Objects are places where we can store data. To create an object and store our data in it, we use the assignment operator <- (a less-than sign followed by a dash). Continually typing <- can be tedious, so in RStudio we can use the keyboard short cut Option+- (on Mac) or Alt+- (on Windows or Linux) to insert the complete operator.
When choosing object names, it is important to remember that if you assign a value (such as the number 1 or the result of the function read_csv()) to an object name, R will overwrite any existing value of that object name. We can see this in a simple example:
If we were to run this code, the object one_to_ten would not actually hold the numbers from one to ten, but instead the value 1.414214 (the square root of two). There is also no way to undo assignment of a value to an object, so once you have run the code one_to_ten <- sqrt(2) it is not possible to recover any previous value that was assigned to the object one_to_ten.
Objects come in several different types, with tabular data typically being stored as a data frame. The read_csv() function actually produces a modern variation on the data frame called (slightly strangely) a tibble, which makes use of some advances in how R handles data since the data-frame format was set 20 years ago. Tibbles behave just like data frames almost all of the time (so much so that people working with tibbles often call them data frames) except for a few occasions where they behave in a more-convenient way.
We can use read_csv() to load data from https://mpjashby.github.io/crimemappingdata/san_francisco_robbery.csv and store it in an object called san_fran_rob.
Use the code above to help you, or click the Solution button to reveal the answer.
Rows: 951 Columns: 5
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (1): offense_type
dbl (3): uid, longitude, latitude
dttm (1): date_time
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.If the data are loaded successfully, R will list the columns in the data and the type of variable (numeric, date etc.) stored in each column. The format of this is somewhat esoteric, but if you are interested they are explained in the ‘Extra detail’ box below.
What do the messages produced by read_csv() mean?
By default, the read_csv() function prints a message when it loads data to summarise the format of each data column. In the case of the san_fran_rob dataset, read_csv() tells us that:
- there is one column called
offense_typethat contains character (chr) values, - there are three columns called
uid,longitudeandlatitudecontaining numeric (dbl) values, and - there is one column called
date_timethat contains values stored as dates and times (dttm).
There are some other possible types of data, but we will learn about these later on. The numeric values are referred to as dbl values because they are stored in a format that can handle numbers that are not whole numbers (e.g. 123.456). This format for storing numbers is called the double-precision floating-point format, which is often known as the double format for short. Most numbers in R are stored in double format, so you can think of the format code dbl as meaning ‘numeric’.
To see the first few rows of data currently stored in an object, we can use the head() function.
Type the code needed to view the first few rows of the san_fran_rob object and click the Run Code button.
# A tibble: 6 × 5
uid offense_type date_time longitude latitude
<dbl> <chr> <dttm> <dbl> <dbl>
1 24103841 personal robbery 2019-01-01 19:50:00 -122. 37.8
2 24103948 personal robbery 2019-01-02 08:00:00 -122. 37.8
3 24104162 personal robbery 2019-01-03 00:30:00 -122. 37.8
4 24104203 personal robbery 2019-01-03 03:13:00 -122. 37.8
5 24104237 personal robbery 2019-01-03 09:30:00 -122. 37.7
6 24104238 personal robbery 2019-01-03 09:30:00 -122. 37.73.2.2 Loading Excel data
Loading data from Microsoft Excel files is very similar to loading CSV data, with a few important differences. Functions to load Excel data are contained in the readxl package, which was installed automatically when we installed the tidyverse package.
There are two main things we must do to import Excel data that are not required for importing CSV data. The first is that the readxl package cannot directly load files from a URL, instead only loading files that are present on your computer. To get round this, we will first download an Excel file and store it in a temporary directory (to avoid cluttering up our computers).
Using download.file() on Windows
If you are using a Windows computer, you may find that the download.file() function in the code below does not work as expected. This is because Windows handles files in a way that distinguishes between plain-text files such as .txt and .csv files and binary files, which includes most other file types (including compressed files). Since aggravated_assaults.xlsx is not a plain-text file, on Windows you need to specify that you want it to be downloaded as a binary file. To do this, add the argument mode = "wb" to the download.file() function so that it reads:
download.file(
url = "https://mpjashby.github.io/crimemappingdata/aggravated_assaults.xlsx",
destfile = temp_file,
mode = "wb"
)If you are using a Mac or a Linux computer then you do not need to worry about this.
# Specify the name of and location of our temporary file: it does not matter
# what this file is called or where it is stored, so we use the tempfile()
# function to create a file in the correct location automatically
temp_file <- tempfile(fileext = ".xlsx")
# Download the Excel file and store it in the temporary location
download.file(
url = "https://mpjashby.github.io/crimemappingdata/aggravated_assaults.xlsx",
destfile = temp_file,
mode = "wb"
)The download.file() function does not produce any output if the file has been successfully downloaded, so you will not see any output when you run this code.
Now we have downloaded our data, we can load it into R. Since Excel files can contain multiple sheets, we need to specify which sheet we would like to load into a tibble. We can use the excel_sheets() function to get a list of sheets in an Excel file:
# Load the readxl package
library(readxl)
# Get a list of sheets in an Excel file
excel_sheets(temp_file)[1] "Austin" "Fort Worth" "Seattle" We can now load the sheet containing data for Austin and view the first few rows of the resulting object:
# A tibble: 6 × 5
date longitude latitude location_type location_category
<dttm> <dbl> <dbl> <chr> <chr>
1 2019-01-01 00:00:00 -97.7 30.3 residence residence
2 2019-01-01 00:00:00 -97.8 30.2 residence residence
3 2019-01-01 00:01:00 -97.7 30.3 <NA> <NA>
4 2019-01-01 00:15:00 -97.7 30.3 <NA> <NA>
5 2019-01-01 00:27:00 -97.8 30.2 residence residence
6 2019-01-01 00:30:00 -97.7 30.3 <NA> <NA> Now we have learned how to load our data into an object, we can use other R functions to work with that data in many different ways.
Different types of data are loaded into R with different functions, e.g. CSV files are loaded with the read_csv() function from the readr package and Microsoft Excel files are loaded with the read_excel() function from the readxl package. You can download a table of functions for reading data into R to remind you of which function to use to load each type of file.
Learn more about how to read data into R by reading this chapter of the free online book R for Data Science.
Excel data can often be messy and the readxl package contains various other functions that can be used to deal with this. You can learn more about how to handle messy Excel data in this online tutorial.
3.2.3 Check your understanding
Answer the following questions to check your understanding of what we’ve learned so far in this tutorial. If you get a question wrong, you can keep trying until you get the right answer.
3.3 Selecting columns
In this section we will learn how to reduce the size of our data by selecting only the columns we need and discarding the rest. This can be particularly useful if we are working with a very-large dataset, or if we want to produce a table containing only some columns.
We can use the select() function from the dplyr package (one of the packages that is loaded automatically when we call the library(tidyverse) function) to select columns.
If we wanted to select just the date and location_type columns from the agg_assault_data we loaded in the previous section:
# A tibble: 8,696 × 2
date location_type
<dttm> <chr>
1 2019-01-01 00:00:00 residence
2 2019-01-01 00:00:00 residence
3 2019-01-01 00:01:00 <NA>
4 2019-01-01 00:15:00 <NA>
5 2019-01-01 00:27:00 residence
6 2019-01-01 00:30:00 <NA>
7 2019-01-01 00:51:00 <NA>
8 2019-01-01 01:00:00 residence
9 2019-01-01 01:00:00 <NA>
10 2019-01-01 01:12:00 residence
# ℹ 8,686 more rows
In a previous section, we mentioned that the code needed to run (or call) a function in R has two parts: the function name followed by a pair of parentheses, inside which are zero or more arguments separated by commas. The arguments in the select() function (and many other functions in the dplyr package) work in a slightly different way to many other functions. Here, the first argument is the name of the data object that we want to select from. All the remaining arguments (here, date and location_type) are the names of the columns we want to select from the data.
We can select as many columns as we want, by just adding the names of the columns separated by commas.
Write the code necessary to select the longitude and latitude columns from the agg_assault_data object:
# A tibble: 8,696 × 2
longitude latitude
<dbl> <dbl>
1 -97.7 30.3
2 -97.8 30.2
3 -97.7 30.3
4 -97.7 30.3
5 -97.8 30.2
6 -97.7 30.3
7 -97.7 30.3
8 -97.7 30.4
9 -97.7 30.3
10 -97.7 30.3
# ℹ 8,686 more rowsThe columns in our new dataset will appear in the order in which we specify them in the select() function.
We can also use select() to rename columns at the same time as selecting them. For example, to select the columns date and location_type while also renaming location_type to be called type:
# A tibble: 8,696 × 2
date type
<dttm> <chr>
1 2019-01-01 00:00:00 residence
2 2019-01-01 00:00:00 residence
3 2019-01-01 00:01:00 <NA>
4 2019-01-01 00:15:00 <NA>
5 2019-01-01 00:27:00 residence
6 2019-01-01 00:30:00 <NA>
7 2019-01-01 00:51:00 <NA>
8 2019-01-01 01:00:00 residence
9 2019-01-01 01:00:00 <NA>
10 2019-01-01 01:12:00 residence
# ℹ 8,686 more rowsIf we want to rename a column while keeping all the columns in the data, we can instead use the rename() function (also from the dplyr package):
# A tibble: 8,696 × 5
date longitude latitude type location_category
<dttm> <dbl> <dbl> <chr> <chr>
1 2019-01-01 00:00:00 -97.7 30.3 residence residence
2 2019-01-01 00:00:00 -97.8 30.2 residence residence
3 2019-01-01 00:01:00 -97.7 30.3 <NA> <NA>
4 2019-01-01 00:15:00 -97.7 30.3 <NA> <NA>
5 2019-01-01 00:27:00 -97.8 30.2 residence residence
6 2019-01-01 00:30:00 -97.7 30.3 <NA> <NA>
7 2019-01-01 00:51:00 -97.7 30.3 <NA> <NA>
8 2019-01-01 01:00:00 -97.7 30.4 residence residence
9 2019-01-01 01:00:00 -97.7 30.3 <NA> <NA>
10 2019-01-01 01:12:00 -97.7 30.3 residence residence
# ℹ 8,686 more rowsRemember that functions in R generally do not change existing objects, but instead produce (or return) new ones. This means if we want to store the result of this function so we can use it later, we have to assign the value returned by the function to a new object (or overwrite the existing object):
agg_assault_locations <- select(agg_assault_data, lon = longitude, lat = latitude)
head(agg_assault_locations)# A tibble: 6 × 2
lon lat
<dbl> <dbl>
1 -97.7 30.3
2 -97.8 30.2
3 -97.7 30.3
4 -97.7 30.3
5 -97.8 30.2
6 -97.7 30.3You can learn more about selecting, filtering and arranging data using the functions in the dplyr package by reading this Introduction to dplyr tutorial.
3.3.1 Check your understanding
3.4 Filtering rows
Often in crime mapping we will only be interested in part of a particular dataset. In the same way that we can select particular columns in our data, we can filter particular rows using the filter() function from the dplyr package.
If we were only interested in offences in the agg_assault_data dataset that occurred in residences, we could use filter():
# A tibble: 4,385 × 5
date longitude latitude location_type location_category
<dttm> <dbl> <dbl> <chr> <chr>
1 2019-01-01 00:00:00 -97.7 30.3 residence residence
2 2019-01-01 00:00:00 -97.8 30.2 residence residence
3 2019-01-01 00:27:00 -97.8 30.2 residence residence
4 2019-01-01 01:00:00 -97.7 30.4 residence residence
5 2019-01-01 01:12:00 -97.7 30.3 residence residence
6 2019-01-01 01:20:00 -97.7 30.4 residence residence
7 2019-01-01 01:50:00 -97.7 30.3 residence residence
8 2019-01-01 02:21:00 -97.8 30.2 residence residence
9 2019-01-01 02:26:00 -97.8 30.2 residence residence
10 2019-01-01 02:35:00 -97.7 30.4 residence residence
# ℹ 4,375 more rowsNote that:
- the column name
location_typeis not surrounded by quotes but the column value"residence"is, and - the
==(equal to) operator is used, since a single equals sign=has another meaning in R.
We can filter using the values of more than one column simultaneously. To filter offences in which the location_category is ‘retail’ and the location_type is ‘convenience store’:
# A tibble: 90 × 5
date longitude latitude location_type location_category
<dttm> <dbl> <dbl> <chr> <chr>
1 2019-01-06 16:58:00 -97.8 30.5 convenience store retail
2 2019-01-08 17:56:00 -97.7 30.3 convenience store retail
3 2019-01-10 01:25:00 -97.7 30.4 convenience store retail
4 2019-01-12 00:49:00 -97.6 30.4 convenience store retail
5 2019-01-12 05:53:00 -97.7 30.4 convenience store retail
6 2019-01-12 17:01:00 -97.7 30.4 convenience store retail
7 2019-01-12 19:12:00 -97.8 30.3 convenience store retail
8 2019-01-12 19:14:00 -97.7 30.4 convenience store retail
9 2019-01-13 04:10:00 -97.8 30.2 convenience store retail
10 2019-01-22 22:01:00 -97.7 30.3 convenience store retail
# ℹ 80 more rowsAs well as filtering using the == operator, we can filter using the greater-than (>), less-than (<), greater-than-or-equal-to (>=) and less-than-or-equal-to (<=) operators. For example, we can choose offences that occurred in residences on or after 1 July 2019:
# A tibble: 2,286 × 5
date longitude latitude location_type location_category
<dttm> <dbl> <dbl> <chr> <chr>
1 2019-07-01 02:40:00 -97.8 30.4 residence residence
2 2019-07-01 07:26:00 -97.7 30.3 residence residence
3 2019-07-01 08:25:00 -97.8 30.2 residence residence
4 2019-07-01 09:39:00 -97.7 30.3 residence residence
5 2019-07-01 09:40:00 -97.7 30.4 residence residence
6 2019-07-01 16:24:00 -97.8 30.1 residence residence
7 2019-07-01 17:30:00 -97.8 30.2 residence residence
8 2019-07-01 17:41:00 -97.8 30.2 residence residence
9 2019-07-01 18:03:00 -97.8 30.2 residence residence
10 2019-07-01 18:16:00 -97.8 30.1 residence residence
# ℹ 2,276 more rowsSometimes we will want to filter rows that are one thing or another. We can do this with the | (or) operator. For example, we can filter offences that occurred either in leisure facilities or shopping malls on or after 1 July 2019:
filter(
agg_assault_data,
location_category == "leisure" | location_type == "mall",
date >= as.Date("2019-07-01")
)# A tibble: 10 × 5
date longitude latitude location_type location_category
<dttm> <dbl> <dbl> <chr> <chr>
1 2019-07-01 09:00:00 -97.7 30.3 entertainment leisure
2 2019-07-15 14:06:00 -97.8 30.3 mall retail
3 2019-08-06 23:20:00 -97.7 30.4 mall retail
4 2019-08-18 13:31:00 -97.7 30.3 mall retail
5 2019-08-25 15:35:00 -97.8 30.3 mall retail
6 2019-09-06 15:58:00 -97.7 30.4 mall retail
7 2019-10-05 16:47:00 -97.7 30.2 mall retail
8 2019-11-01 22:23:00 -97.7 30.3 entertainment leisure
9 2019-11-18 16:29:00 -97.7 30.4 mall retail
10 2019-11-27 14:14:00 -97.8 30.3 mall retail If we want to filter offences that have any one of several different values of the same column, we can use the %in% (in) operator. To filter offences that occurred in either streets or publicly accessible open spaces:
# A tibble: 165 × 5
date longitude latitude location_type location_category
<dttm> <dbl> <dbl> <chr> <chr>
1 2019-01-07 21:37:00 -97.8 30.5 green space open space
2 2019-01-08 14:02:00 -97.7 30.3 green space open space
3 2019-01-10 01:59:00 -97.6 30.3 green space open space
4 2019-01-11 17:00:00 -97.7 30.3 green space open space
5 2019-01-12 15:50:00 -97.8 30.2 green space open space
6 2019-01-12 16:00:00 -97.8 30.4 green space open space
7 2019-01-15 07:20:00 -97.7 30.2 green space open space
8 2019-01-17 00:00:00 -97.7 30.4 green space open space
9 2019-01-22 22:00:00 -97.7 30.2 green space open space
10 2019-01-29 01:40:00 -97.7 30.2 green space open space
# ℹ 155 more rowsThe code c("open space", "street") produces what is referred to in R as a vector (sometimes referred to as an atomic vector, especially in error messages). A vector is a one-dimensional sequence of values of the same type (i.e. all numbers, all character strings etc.). For example, a vector might hold several strings of text (as in the vector c("open space", "street")) or a series of numbers such as c(1, 2, 3). There is lots we could learn about vectors, but for now it’s only necessary to know that we can create vectors with the c() or combine function.
If we wanted to re-use a vector of values several times in our code, it might make sense to store the vector as an object. For example:
# Create vector of location types we are interested in
location_types <- c("open space", "street")
# Filter the data
filter(agg_assault_data, location_category %in% location_types)# A tibble: 165 × 5
date longitude latitude location_type location_category
<dttm> <dbl> <dbl> <chr> <chr>
1 2019-01-07 21:37:00 -97.8 30.5 green space open space
2 2019-01-08 14:02:00 -97.7 30.3 green space open space
3 2019-01-10 01:59:00 -97.6 30.3 green space open space
4 2019-01-11 17:00:00 -97.7 30.3 green space open space
5 2019-01-12 15:50:00 -97.8 30.2 green space open space
6 2019-01-12 16:00:00 -97.8 30.4 green space open space
7 2019-01-15 07:20:00 -97.7 30.2 green space open space
8 2019-01-17 00:00:00 -97.7 30.4 green space open space
9 2019-01-22 22:00:00 -97.7 30.2 green space open space
10 2019-01-29 01:40:00 -97.7 30.2 green space open space
# ℹ 155 more rowsFinally, you can filter based on the output of any R function that returns TRUE or FALSE. For example, missing values are represented in R as NA. We can test whether a value is missing using the is.na() function. If we wanted to remove rows from our data that had missing location types, we would filter for those rows that are not NA. We can do this by combining the is.na() function with the ! (not) operator:
# A tibble: 5,344 × 5
date longitude latitude location_type location_category
<dttm> <dbl> <dbl> <chr> <chr>
1 2019-01-01 00:00:00 -97.7 30.3 residence residence
2 2019-01-01 00:00:00 -97.8 30.2 residence residence
3 2019-01-01 00:27:00 -97.8 30.2 residence residence
4 2019-01-01 01:00:00 -97.7 30.4 residence residence
5 2019-01-01 01:12:00 -97.7 30.3 residence residence
6 2019-01-01 01:20:00 -97.7 30.4 residence residence
7 2019-01-01 01:35:00 -97.7 30.3 hotel hotel
8 2019-01-01 01:50:00 -97.7 30.3 residence residence
9 2019-01-01 02:21:00 -97.8 30.2 residence residence
10 2019-01-01 02:26:00 -97.8 30.2 residence residence
# ℹ 5,334 more rowsWe will see lots more examples of how to use filter() in future tutorials.
3.4.1 Check your understanding
3.5 Transforming values
It is often useful to create new columns in our data, or change the values of existing columns. The mutate() function in the dplyr package gives us a way to transform existing columns in our dataset using almost any R function.
For example, say we wanted to create a new column in our aggravated-assault dataset specifying the day of the week on which each crime occurred. We can do this using the wday() function from the lubridate package (using the label = TRUE argument to produce weekday names, rather than numbers):
# A tibble: 8,696 × 6
date longitude latitude location_type location_category
<dttm> <dbl> <dbl> <chr> <chr>
1 2019-01-01 00:00:00 -97.7 30.3 residence residence
2 2019-01-01 00:00:00 -97.8 30.2 residence residence
3 2019-01-01 00:01:00 -97.7 30.3 <NA> <NA>
4 2019-01-01 00:15:00 -97.7 30.3 <NA> <NA>
5 2019-01-01 00:27:00 -97.8 30.2 residence residence
6 2019-01-01 00:30:00 -97.7 30.3 <NA> <NA>
7 2019-01-01 00:51:00 -97.7 30.3 <NA> <NA>
8 2019-01-01 01:00:00 -97.7 30.4 residence residence
9 2019-01-01 01:00:00 -97.7 30.3 <NA> <NA>
10 2019-01-01 01:12:00 -97.7 30.3 residence residence
# ℹ 8,686 more rows
# ℹ 1 more variable: weekday <ord>Depending on the width of your screen, you might need to click the ▸ button to see the new variable.
We can also categorise an existing variable, for example creating a variable to show whether an offence occurred in the northern or southern half of the city:
# A tibble: 8,696 × 6
date longitude latitude location_type location_category region
<dttm> <dbl> <dbl> <chr> <chr> <chr>
1 2019-01-01 00:00:00 -97.7 30.3 residence residence north…
2 2019-01-01 00:00:00 -97.8 30.2 residence residence south…
3 2019-01-01 00:01:00 -97.7 30.3 <NA> <NA> south…
4 2019-01-01 00:15:00 -97.7 30.3 <NA> <NA> south…
5 2019-01-01 00:27:00 -97.8 30.2 residence residence south…
6 2019-01-01 00:30:00 -97.7 30.3 <NA> <NA> south…
7 2019-01-01 00:51:00 -97.7 30.3 <NA> <NA> north…
8 2019-01-01 01:00:00 -97.7 30.4 residence residence north…
9 2019-01-01 01:00:00 -97.7 30.3 <NA> <NA> north…
10 2019-01-01 01:12:00 -97.7 30.3 residence residence north…
# ℹ 8,686 more rowsWe can change existing columns, although (as with objects) there is no way to undo this so you should only replace columns if you are sure you will not need them. For example, if we wanted to remove the time portion of the date variable (which may sometimes be useful, as shown in the next section) using the as_date() function (also from the lubridate package) and at the same time create the weekday variable:
# A tibble: 8,696 × 6
date longitude latitude location_type location_category weekday
<date> <dbl> <dbl> <chr> <chr> <ord>
1 2019-01-01 -97.7 30.3 residence residence Tue
2 2019-01-01 -97.8 30.2 residence residence Tue
3 2019-01-01 -97.7 30.3 <NA> <NA> Tue
4 2019-01-01 -97.7 30.3 <NA> <NA> Tue
5 2019-01-01 -97.8 30.2 residence residence Tue
6 2019-01-01 -97.7 30.3 <NA> <NA> Tue
7 2019-01-01 -97.7 30.3 <NA> <NA> Tue
8 2019-01-01 -97.7 30.4 residence residence Tue
9 2019-01-01 -97.7 30.3 <NA> <NA> Tue
10 2019-01-01 -97.7 30.3 residence residence Tue
# ℹ 8,686 more rowsYou may sometimes want to change only some values in a column. We can do this in various ways, depending on which values we want to change:
mutate(
agg_assault_data,
# Change a single value with a new value (and otherwise keep the existing
# value) using the if_else() function
location_type = if_else(location_type == "street", "road", location_type),
# Change multiple values in a categorical variable using the recode()
# function, in which values are changed using arguments in the format
# old_value = new_value
location_category = recode(
location_category,
"open space" = "public open space",
"street" = "street or road"
)
)# A tibble: 8,696 × 5
date longitude latitude location_type location_category
<dttm> <dbl> <dbl> <chr> <chr>
1 2019-01-01 00:00:00 -97.7 30.3 residence residence
2 2019-01-01 00:00:00 -97.8 30.2 residence residence
3 2019-01-01 00:01:00 -97.7 30.3 <NA> <NA>
4 2019-01-01 00:15:00 -97.7 30.3 <NA> <NA>
5 2019-01-01 00:27:00 -97.8 30.2 residence residence
6 2019-01-01 00:30:00 -97.7 30.3 <NA> <NA>
7 2019-01-01 00:51:00 -97.7 30.3 <NA> <NA>
8 2019-01-01 01:00:00 -97.7 30.4 residence residence
9 2019-01-01 01:00:00 -97.7 30.3 <NA> <NA>
10 2019-01-01 01:12:00 -97.7 30.3 residence residence
# ℹ 8,686 more rowsWe could also make changes based on more-complicated sets of criteria using the case_when() function, but we will return to that in a future tutorial.
The R functions that you use inside mutate() must return the same number of values as there are rows in the dataset. This is true for most R functions (which are referred to as vectorised functions), but there are some – such as mean() and max() – that return a single value. These summarising functions cannot be used inside mutate() (you will see an error message if you try) but are instead used with the next data-wrangling function we will learn about: summarise().
3.6 Summarising rows
Summarising data is often useful in crime analysis. We can use the summarise() function from the dplyr package to produce summaries of different columns in our data. There is an identical function called summarize() so that you do not have to remember whether to use the US or British spelling.
By default, summarise() collapses data into a single row, with each column summarised using a function that you specify. For example, suppose you want to find out which police station a specialist squad should be based at to most easily respond to reports of serious assaults. You might do this by working out the weighted centre of all the offence locations, i.e. the means of the longitudes and latitudes for all the crimes. You could then base the squad at the police station that was closest to the weighted centre.
summarise(
agg_assault_data,
mean_lng = mean(longitude, na.rm = TRUE),
mean_lat = mean(latitude, na.rm = TRUE)
)# A tibble: 1 × 2
mean_lng mean_lat
<dbl> <dbl>
1 -97.7 30.3
What does the argument na.rm = TRUE do?
Lots of functions in R have an argument called na.rm that can be set to either TRUE or FALSE. Setting na.rm = TRUE in this case specifies that the mean() function should remove (rm) any missing (NA) values before calculating the mean.
If we do not specify this and our data contain any missing values, the mean() function will return NA. Functions in R do this because it is not possible to completely answer the question ‘what is the mean of these values?’ if some of the values are missing.
This logic applies in lots of cases. For example, if you create an R object called value with the code value <- 2 and then run the R code value > 1, you will get the answer TRUE. But if you set the object value to be NA using the code value <- NA, when you run the R code value > 1 you will get the answer NA. This is because there is no way to know if the missing value represented by NA is greater than 1 or not. This is why it is often useful to calculate statistics such as a mean value after removing any missing values using the na.rm = TRUE argument.
summarise() becomes more useful if we first divide our data into groups, since we then get a summary for each group separately. We can use the .by argument of the summarise() function to specify that we want separate summaries for each unique value of one or more columns in the data. For example, to produce a separate summary for each unique value of location_category, we can use this code:
summarise(
agg_assault_data,
mean_lng = mean(longitude, na.rm = TRUE),
mean_lat = mean(latitude, na.rm = TRUE),
.by = location_category
)# A tibble: 12 × 3
location_category mean_lng mean_lat
<chr> <dbl> <dbl>
1 residence -97.7 30.3
2 <NA> -97.7 30.3
3 hotel -97.7 30.3
4 other -97.7 30.3
5 retail -97.7 30.3
6 healthcare -97.7 30.3
7 open space -97.7 30.3
8 commercial -97.7 30.3
9 education -97.7 30.3
10 government -97.7 30.3
11 leisure -97.7 30.4
12 transportation -97.7 30.2You can add multiple grouping variables using the c() (combine) function if you want to generate summary values for groups within groups:
summarise(
agg_assault_data,
mean_lng = mean(longitude, na.rm = TRUE),
mean_lat = mean(latitude, na.rm = TRUE),
.by = c(location_category, location_type)
)# A tibble: 24 × 4
location_category location_type mean_lng mean_lat
<chr> <chr> <dbl> <dbl>
1 residence residence -97.7 30.3
2 <NA> <NA> -97.7 30.3
3 hotel hotel -97.7 30.3
4 other other -97.7 30.3
5 retail other retail -97.7 30.3
6 healthcare healthcare -97.7 30.3
7 retail mall -97.8 30.3
8 retail convenience store -97.7 30.3
9 open space green space -97.7 30.3
10 commercial office -97.7 30.3
# ℹ 14 more rows3.6.1 Counting rows
One very common way of summarising data is to count the number of rows in a dataset that have each unique value of one or more columns. For example, if we have a dataset of crimes in which each row represents a single crime, we might want to count how many crimes happened on each day of the week, or how many crimes of each type are in the dataset. We can use summarise() to do that, together with the n() function (from the same dplyr package as summarise()). For example, if we wanted to count how many rows in the agg_assault_data dataset had each unique combination of location_category and location_type:
# A tibble: 24 × 3
location_category location_type n
<chr> <chr> <int>
1 residence residence 4385
2 <NA> <NA> 3352
3 hotel hotel 248
4 other other 205
5 retail other retail 30
6 healthcare healthcare 36
7 retail mall 11
8 retail convenience store 90
9 open space green space 165
10 commercial office 28
# ℹ 14 more rowsIn this code, the n() function simply returns the number of rows of data in each group, i.e. the number of rows with each unique combination of values of location_category and location_type.
Since counting the number of rows in each group a dataset is a very common task, dplyr includes another function called count() that allows you to do the same thing as in the code above, but with slightly less typing:
So if you wanted to know how many aggravated assaults had occurred in each location category and type:
# A tibble: 24 × 3
location_category location_type n
<chr> <chr> <int>
1 commercial construction 11
2 commercial factory/warehouse 2
3 commercial finance 1
4 commercial office 28
5 commercial storage 4
6 education child care 11
7 education college 5
8 education school 25
9 government government 18
10 healthcare healthcare 36
# ℹ 14 more rows3.7 Arranging rows
It is sometimes useful to be able to place rows in a dataset into a particular order. We can do this using the arrange() function from the dplyr package. For example, we can sort the aggravated-assault data by date:
# A tibble: 8,696 × 5
date longitude latitude location_type location_category
<dttm> <dbl> <dbl> <chr> <chr>
1 2019-01-01 00:00:00 -97.7 30.3 residence residence
2 2019-01-01 00:00:00 -97.8 30.2 residence residence
3 2019-01-01 00:01:00 -97.7 30.3 <NA> <NA>
4 2019-01-01 00:15:00 -97.7 30.3 <NA> <NA>
5 2019-01-01 00:27:00 -97.8 30.2 residence residence
6 2019-01-01 00:30:00 -97.7 30.3 <NA> <NA>
7 2019-01-01 00:51:00 -97.7 30.3 <NA> <NA>
8 2019-01-01 01:00:00 -97.7 30.4 residence residence
9 2019-01-01 01:00:00 -97.7 30.3 <NA> <NA>
10 2019-01-01 01:12:00 -97.7 30.3 residence residence
# ℹ 8,686 more rowsBy default, arrange() sorts rows in ascending order, i.e. it sorts numeric values from the smallest to the largest, dates from earliest to latest and character values alphabetically. We can instead sort values in descending order by wrapping the name of a column in the desc() function:
# A tibble: 8,696 × 5
date longitude latitude location_type location_category
<dttm> <dbl> <dbl> <chr> <chr>
1 2019-12-31 23:33:00 -97.7 30.4 residence residence
2 2019-12-31 23:26:00 -97.7 30.4 residence residence
3 2019-12-31 23:20:00 -97.7 30.3 <NA> <NA>
4 2019-12-31 23:19:00 -97.7 30.3 <NA> <NA>
5 2019-12-31 22:39:00 -97.7 30.3 other other
6 2019-12-31 22:29:00 -97.7 30.3 residence residence
7 2019-12-31 21:13:00 -97.8 30.2 residence residence
8 2019-12-31 20:52:00 -97.7 30.3 residence residence
9 2019-12-31 20:44:00 -97.7 30.3 residence residence
10 2019-12-31 20:19:00 -97.7 30.3 <NA> <NA>
# ℹ 8,686 more rowsWe can also sort the data based on multiple columns – the data are sorted first on the first column that you specify, with tied rows then sorted on the subsequent columns in order.
# A tibble: 8,696 × 5
date longitude latitude location_type location_category
<dttm> <dbl> <dbl> <chr> <chr>
1 2019-01-01 00:00:00 -97.7 30.3 residence residence
2 2019-01-01 00:00:00 -97.8 30.2 residence residence
3 2019-01-01 00:01:00 -97.7 30.3 <NA> <NA>
4 2019-01-01 00:15:00 -97.7 30.3 <NA> <NA>
5 2019-01-01 00:27:00 -97.8 30.2 residence residence
6 2019-01-01 00:30:00 -97.7 30.3 <NA> <NA>
7 2019-01-01 00:51:00 -97.7 30.3 <NA> <NA>
8 2019-01-01 01:00:00 -97.7 30.4 residence residence
9 2019-01-01 01:00:00 -97.7 30.3 <NA> <NA>
10 2019-01-01 01:12:00 -97.7 30.3 residence residence
# ℹ 8,686 more rows3.7.1 Check your understanding
Type the code necessary to arrange agg_assault_data in order of latitude, in descending order (from largest to smallest)
Run your code using the Run Code button, then (if necessary) correct your code and run it again. Once you are happy that your code does what it is intended to do, click the Solution button to check.
# A tibble: 8,696 × 5
date longitude latitude location_type location_category
<dttm> <dbl> <dbl> <chr> <chr>
1 2019-07-05 10:33:00 -97.7 30.5 residence residence
2 2019-09-28 19:31:00 -97.8 30.5 residence residence
3 2019-09-20 13:30:00 -97.8 30.5 residence residence
4 2019-08-25 22:04:00 -97.8 30.5 <NA> <NA>
5 2019-11-19 12:00:00 -97.8 30.5 school education
6 2019-01-26 19:52:00 -97.8 30.5 residence residence
7 2019-07-15 13:37:00 -97.8 30.5 <NA> <NA>
8 2019-01-28 21:14:00 -97.8 30.5 residence residence
9 2019-02-03 00:53:00 -97.8 30.5 residence residence
10 2019-07-31 01:37:00 -97.8 30.5 residence residence
# ℹ 8,686 more rows3.8 Saving data
Once we have finished wrangling a particular dataset, it is often useful to save it to a file so that we can use it again in future without going through all the steps of data wrangling again.
Most R functions that begin with read_ (like read_csv() and read_excel()) have equivalent functions that begin write_ and which save data into a particular file format. In this example, we will use the write_csv() function from the readr package, which is loaded when we load the tidyverse package.
# We can write data to a file in the same folder as our R script
write_csv(agg_assault_data, "fort_worth_agg_assault.csv")
# Or another folder on your computer ('../../' is short for the parent folder of
# the parent folder of the current folder)
write_csv(agg_assault_data, "../../fort_worth_agg_assault.csv")For very large datasets, we can save a compressed version of the file by adding .gz (for gzip) to the end of the file name, which tells R to compress the file after creating it.
read_csv() can read gzipped CSV files, but some other programs (such as Excel) cannot, so only use this option if you are sure you will only need to open the file in software that can handle it.
There are corresponding write functions for other types of data (which we will come back to when we learn how to handle spatial data), but in this course we will store all non-spatial data in CSV format because it can be read by many different programs.
3.9 Stringing functions together
In this tutorial we have learned how to use the dplyr functions select(), filter(), mutate(), summarise() and arrange() to wrangle data from one format to another. Data wrangling is part of almost all data analysis, so these are skills we will use frequently.
Data wrangling often involves multiple steps. For example, we might want to load some data, select certain columns, filter some rows, mutate some of the variables, summarise the dataset and save the result. We can do each of these steps separately, assigning the result of each step to a new object.
# Load the lubridate package, since we will need it below
library(lubridate)
# Read San Francisco robbery data
robbery1 <- read_csv("https://mpjashby.github.io/crimemappingdata/san_francisco_robbery.csv")Rows: 951 Columns: 5
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (1): offense_type
dbl (3): uid, longitude, latitude
dttm (1): date_time
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.# Select only the columns we need
robbery2 <- select(robbery1, date_time)
# Filter only those offences that occurred in the first quarter of 2019
robbery3 <- filter(robbery2, as.Date(date_time) <= as.Date("2019-03-31"))
# Create a new weekday variable
robbery4 <- mutate(robbery3, weekday = wday(date_time, label = TRUE))
# Count how many offences occurred on each weekday
q1_weekday_counts <- count(robbery4, weekday)
# Print the first few rows of the result
head(q1_weekday_counts, n = 7)# A tibble: 7 × 2
weekday n
<ord> <int>
1 Sun 34
2 Mon 27
3 Tue 28
4 Wed 20
5 Thu 34
6 Fri 27
7 Sat 26This code works, but involves creating six new objects, even though we only need the final object for our analysis. You may notice that the first argument expected by select(), filter(), mutate() and count() is always the data tibble produced by the previous step. This means we can skip saving the result of each step as a new object, and just run the previous function inside the first function. This approach produces the following code, which produces exactly the same result as the code above.
q1_weekday_counts <- count(
mutate(
filter(
select(
read_csv("https://mpjashby.github.io/crimemappingdata/san_francisco_robbery.csv"),
-offense_type
),
as.Date(date_time) <= as.Date("2019-03-31")
),
weekday = wday(date_time, label = TRUE)
),
weekday
)Rows: 951 Columns: 5
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (1): offense_type
dbl (3): uid, longitude, latitude
dttm (1): date_time
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.# A tibble: 7 × 2
weekday n
<ord> <int>
1 Sun 34
2 Mon 27
3 Tue 28
4 Wed 20
5 Thu 34
6 Fri 27
7 Sat 26This code works and takes up less space, but it’s quite difficult to read – which can be a problem for finding and fixing problems with your code. For example, it’s quite hard (without counting pairs of parentheses) to work out that the reference to the column weekday on line 12 of this code belongs to the count() function on line 1.
It’s possible to write this code so that it is readable and does not require us to create multiple different objects to store the result of each step in our code. This method uses the |> (or pipe) operator. The pipe operator works by using the result of the code on the left-hand side of the pipe as the first argument to a function on the right-hand side. So the code x |> fun1() |> fun2(y) is the same as the code fun2(fun1(x), y), but it is much easier to see that fun1() is run before fun2(). It may be useful to read the pipe operator as ‘and then’, since piped code does the first thing and then the second thing with the result and then the third thing with the result of that, and so on. Piped code (sometimes called a pipeline) is a lot like the series of steps in a recipe.
Since each function we are using returns the new data, and the first argument to all of those functions is the name of the input data object, the pipe means we can just omit the first argument to all except the first function.
Rows: 951 Columns: 5
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (1): offense_type
dbl (3): uid, longitude, latitude
dttm (1): date_time
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.q1_weekday_counts <- san_fran_rob |>
select(-offense_type) |>
filter(as.Date(date_time) <= as.Date("2019-03-31")) |>
mutate(weekday = wday(date_time, label = TRUE)) |>
count(weekday)
head(q1_weekday_counts, n = 7)# A tibble: 7 × 2
weekday n
<ord> <int>
1 Sun 34
2 Mon 27
3 Tue 28
4 Wed 20
5 Thu 34
6 Fri 27
7 Sat 26This code strikes a good balance between being easy to read and not requiring us to manage lots of intermediate variables. You might not find the pipe operator completely intuitive at the moment, but it will become easier as you see more examples in future tutorials.
What about the %>% pipe operator?
If you have learned any R coding before, you might have learned to use the %>% pipe operator from the magrittr package. The %>% pipe operator was introduced several years ago to allow people to construct pipelines of code in R. The %>% operator was so widely used that the team that writes the R programming language decided to provide a pipe operator in R itself, to avoid the need to load the magrittr package.
You will still see the %>% pipe operator used in lots of R code examples online. In almost all cases, when you see %>% you can replace it with the R pipe operator |>, since they both work in very similar ways.
You can find out more about how to use the pipe operator in the Introducing magrittr online tutorial.
3.10 In summary
In this tutorial, you have learned how to wrangle data in R using functions from packages in the tidyverse suite of packages. You can now construct a complete pipeline of R code to take raw data and transform it into the format(s) we need to effectively map crimes.
Developing your data wrangling skills will help you to produce better, faster analysis of crime (and other) data. If you would like to develop your skills further, you might be interested in:
- Data Wrangling with R by Claudia Engel, a free online book that explains the functions introduced in this tutorial (and some others) in more detail.
-
Data transformation with dplyr cheat sheet by the team that makes RStudio, which provides a handy two-page guide to the main functions in the
dplyrpackage, which is very useful for reminding you of the code needed to run each of the functions we have used in this tutorial. - Data wrangling with R and RStudio by Garrett Grolemund, a recording of a webinar covering the data-wrangling functions introduced in this tutorial and some other very-useful related functions.
- R for Data Science by Hadley Wickham and Garrett Grolemund, a free online book that is the bible for wrangling data in R.