In Chapter 3 we learned how to load tabular data and choose the rows and columns needed for an analysis. This chapter continues the data-wrangling process. We will learn how to create new columns, summarise and arrange rows, connect several operations in a pipeline and save the processed result without changing the original data.
- Open Positron or – if you already have Positron open – start a new R session by clicking the Restart R (⟳) button in the Console panel. This makes sure no code you ran previously will interfere with the work you do in this chapter.
- Make sure you are working in the crime mapping project workspace you created in Section 1.5. In the top-right corner of the Positron window, you should see a folder icon and the words
crime_mapping. If not, click the File menu, then Open Folder … and choose the crime_mapping folder.
Introduction
Selecting columns and filtering rows allow us to choose existing data. But often we also need to be more active in wrangling data, to create new columns, update existing columns and summarise groups of data. In this chapter we will continue working with the San Francisco robbery and Austin aggravated-assault datasets introduced in Chapter 3.
In this chapter, we will learn how to:
- create or change columns with
mutate();
- summarise and count rows;
- arrange rows into a useful order;
- save processed data separately from the original files;
- combine sequential operations with the pipe operator; and
- check that a complete script runs in a fresh R session.
Most of the examples in this chapter are labelled R Console because they are temporary code that helps us understand and practise each function. In Section 4.7, we will use what we have learned to extend copies of the scripts from Chapter 3 and create two complete, reproducible analyses.
The complete data-wrangling process follows these stages:
Transform data
by filtering and creating columns
Summarise data
by counting and arranging rows
Save data
in the data/processed folder
Loading the data needed for this chapter
In this chapter we will learn how to use several R functions to transform data. We will do that using the same datasets about aggravated assaults in Austin and robberies in San Francisco (both in the USA) that we introduced in Chapter 3. Since we have started a new R session since we ran the code in Chapter 3, we will need to load these datasets again. We can do that by running this code in the R Console:
You might be wondering why we are running this code in the R Console rather than in an R script. The reason is that for the first part of this chapter we will keep reusing the same code to learn about several different functions, rather than constructing a complete script. We will then use those functions to create two complete scripts in Section 4.7.
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>
We can also change existing columns. However (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 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 rows
You may sometimes want to change only some values in a column. With a categorical variable, we can change one value to another using the replace_values() function from the dplyr package. Look at this code and use the comments (lines starting with #) to understand how it works.
# 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 rows
replace_values() keeps the existing value for any values that we have not specified.
We could also make changes based on more-complicated sets of criteria using the case_when() function, but we will return to that in Chapter 15.
Functions used inside mutate() usually return one value for every row in the dataset. These are known as vectorised functions. A function such as mean() or max() instead returns a single summary value. R can repeat that value in every row if it is used inside mutate(), but that is usually not what we want. To reduce several rows to one summary row, we use the next data-wrangling function: summarise().
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, we can calculate the mean longitude and latitude of the aggravated-assault locations.
# A tibble: 1 × 2
mean_lng mean_lat
<dbl> <dbl>
1 -97.7 30.3
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:
# 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.2
You can add multiple grouping variables using the c() (combine) function if you want to generate summary values for groups within groups:
# 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 rows
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 rows
By 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 rows
We 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 rows
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.
In Section 1.5 we learned that it is important to keep our original downloaded data files sterile, i.e. not to change them after we have downloaded them. That means we can always go back to the original data if we need to. To help with that, we will keep any files we save after wrangling in a separate sub-folder inside the data folder called processed. This is a common practice in data analysis, and it is good to get into the habit of doing it from the start.
There are corresponding write functions for other types of data, but in this course we will store most non-spatial data in CSV format because it can be read by many different programs.
Stringing functions together
In this chapter 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.
This code works, but creates three intermediate objects as well as the final q1_weekday_counts object. Intermediate objects can be useful when debugging a complicated analysis. However, they also create the substantial risk that you will accidentally use the wrong intermediate object in a later step, which can often cause errors in your code that are hard to fix. For example, when you write the code that creates the robbery4 object, if you accidentally use robbery2 as the input instead of robbery3, the filtering step in the data-wrangling process will have no effect. For that reason, it is generally better to avoid creating intermediate objects unless you actually need them.
Notice that:
- the first argument expected by
select(), filter(), mutate() and count() is always the tibble produced by the previous step and
- once each of
robbery2, robbery3 and robbery4 has been created, it is never used again in the code.
For example, filter() uses the robbery2 object created by select(), but after that robbery2 is never used again. Whenever you write code that consists of several sequential steps in which (a) each step uses the data produced by the previous step and (b) the data produced by each step is only used once, there is a better way to do it. 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() starts with an object called x, uses that as the input to the function fun1(), then passes the result produced by fun1() and uses that as the input to 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.
For example, instead of creating multiple objects called robbery2, robbery3, robbery4 and q1_weekday_counts and so on, we can use a single code pipeline. We will add this pipeline to a script in Section 4.7. For now, look at Code 4.15 and its numbered explanations – note that each pipe operator (|>) corresponds to ‘and then’ in the explanations accompanying Code 4.15.
- 1
-
Take the
san_fran_rob dataset, and then
- 2
-
filter the rows to keep only those that occurred in the first quarter of 2019, and then
- 3
-
create a new column called
weekday that contains the day of the week on which each robbery occurred, and then
- 4
-
count how many robberies occurred on each weekday.
You might not find the pipe operator completely intuitive at the moment, but it will become easier as you see more examples in future chapters.
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 the |> pipe operator in R itself, to avoid the need to load the magrittr package. You might sometimes see the |> referred to as the native pipe operator.
You will still see the %>% pipe operator used in lots of R code examples online, and in code produced by AI tools that were trained on old data. In many straightforward pipelines, %>% can be replaced with the R pipe operator |>, since they work in similar ways. There are some differences between them, so do not assume every advanced example can be converted without changes.
Complete scripts
In Chapter 3 you created chapter_03a.R and chapter_03b.R, which contain the code needed to download and load the two datasets used in this chapter. We will now make copies of those scripts and add the code needed to transform, summarise and save the data.
First check that chapter_03a.R and chapter_03b.R are in the R folder and that the files downloaded in Chapter 3 are in data/raw. Then make a copy of each script using the Explorer panel in Positron:
- If you cannot see the contents of the
R folder, click the arrow beside the folder name to expand it.
- Right-click
chapter_03a.R and choose Copy.
- Right-click the
R folder and choose Paste. A copy of the script will appear in the folder.
- Right-click the copied file, choose Rename, type
chapter_04a.R and press on your keyboard.
- Repeat these steps for
chapter_03b.R, naming the copy chapter_04b.R.
Take care to copy the files rather than renaming the originals. When you have finished, the R folder should contain all four scripts: chapter_03a.R, chapter_03b.R, chapter_04a.R and chapter_04b.R.
Now amend chapter_04a.R by adding the pipeline from Section 4.6 that creates q1_weekday_counts. Then use the information in Section 4.5 to add the code needed to save those weekday counts in a new file called q1_weekday_counts.csv in the processed sub-folder within the data folder.
Once you have tried this yourself, click the ‘Solution’ button to compare your complete script with the model answer.
Now complete chapter_04b.R by:
- adding a pipeline that counts aggravated assaults by location category and weekday, then
- adding the code needed to save the result in a new file called
aggravated_assault_counts.csv, stored in the relevant folder.
Try to complete the script before clicking the ‘Solution’ button.
Save both scripts by pressing .
Check chapter_04a.R and chapter_04b.R separately, following these steps for each script:
To check that the analysis is reproducible, restart R using the Restart R (⟳) button in Positron’s Console panel, then run the script from beginning to end.
Keep chapter_04a.R and chapter_04b.R in the R folder. Also keep the downloaded files in the data/raw folder and the two CSV files in the data/processed folder. The raw files should remain unchanged: if you want to alter the data, change the R code and create a new processed file.
In summary
In this chapter we practised how to:
- create and transform columns;
- summarise, count and arrange rows;
- combine several operations using the pipe operator; and
- save derived data in
data/processed while preserving the original files.
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 Posit, which provides a handy two-page guide to the main functions in the
dplyr package, which is very useful for reminding you of the code needed to run each of the functions we have used in this tutorial.
- R for Data Science (second edition) by Hadley Wickham, Mine Çetinkaya-Rundel and Garrett Grolemund, a widely used free introduction to data analysis with R.
Answer these questions to check you have understood the main points covered in this chapter. Write between 50 and 100 words to answer each question.
- How does
mutate() differ from summarise()?
- How can grouping variables change the result produced by
summarise() or count()?
- What does
arrange() do, and how can you reverse the order?
- Why should processed data be saved separately from raw data?
- What are the benefits of using the pipe (
|>) operator in R?
Artwork by Allison Horst