2  Data manipulation with *dplyr* and *tidyr*

2.1 Data Manipulation with dplyr and tidyr

2.1.1 The power of packages

One of the great things about using R are the thousands of available packages, which provide additional functions for many analytical tasks, such as data cleaning, statistical modelling, mapping, and much more. R packages are open-source, which means that they are free to use and maintained by the R community.

2.1.1.1 Installing and loading packages

Throughout the rest of this training we will use a set of R packages manipulating data and creating plots and maps. As we covered in Chapter 1, we first need to install the package on our computer using the install.packages() function. This only needs to be done one time (you probably already did this earlier).

install.packages("dplyr")

Once the package has been installed, we can load it into our current R session using the library() function. Unlike installing, you will need to load the library each time you want to use it. This is because some libraries may have functions with the same names as other libraries or as our variables.

library(dplyr) 

For the next series of exercises, we will be using a group of packages which have been designed to work together to do common data science tasks. This group of packages is called the “Tidyverse”, because it is designed to work within the “tidy” data philosophy:

Some important qualities of this philosophy is that our data should have the following format:

1. Each column should be a single variable with one data type.
2. Each row should be a single observation.
3. Each cell should be a single value contains one piece of information.

We can install all of these packages at once using install.package("tidyverse"). Remember that we only install the package once, so it is actually better to type this directly into the console instead of in our R script since it does not need to be repeated. Also be aware that this may take some time especially if internet quality is poor. After the package has finished installing it is ready to be loaded into our R session.

library(tidyverse)

2.1.2 Reading data into R

Once the tidyverse package is loaded into our session we will have access to all of the functions in each of the Tidyverse packages. This includes packages for loading, manipulating, and plotting data. The function we will use is read_csv() to read in the district-level data we worked with previously. Note that this is similar but slightly different to the read.csv() function we used in our previous exercise.

scram_num <- function(x, offset = 0.5){
  set.seed(10)
  round(runif(1, x*offset, x*(1+offset)))
}

case_data <- read_csv("data/district-cases-long.csv") %>%
  rowwise() %>%
  mutate(count = scram_num(count))

This is the same dataset we used in Chapter 1 (values scrambled to prevent unauthorized access to confidential data), only this time we called the object case_data instead of dat. It’s good practice to name your objects something short and meaningful, so that it’s easy to type and remember (this is especially useful when you have multiple data objects).

Also, in this file the data are organized in “long” format, whereas the file used in Chapter 1 was in “wide” format. We will discuss the difference between “long” and “wide” formatted data in this chapter, as well as how to change the shape of our data.

Question 1: How many rows and columns are in case_data?

Question 2: What “type” of data are each column (character, vector, etc.)?

2.1.3 Data manipulation using *tidyverse*

In Chapter 1 we learned some built-in functions, or “base” functions, for simple data manipulations such as selecting a specific column or filter for only rows that match some criteria. In this lesson we will learn the *tidyverse* approach to these and additional common data manipulation tasks, using two packages called *dplyr* and *tidyr*. The *dplyr* package provides functions for the most common data manipulations jobs, and the *tidyr*package provides functions for reshaping or pivoting dataframes (similar to pivot tables in Microsoft Excel).

2.1.3.1 Selecting columns and filtering rows

To select a specific column from a dataframe, use the select() functions. The first argument will always be the dataframe object that you’re working with, followed by the name(s) of the column or columns you want to select.

# Select just one column (province)
select(case_data, province)
# Select multiple columns
select(case_data, district, data_type, count)

To select all the columns except certain ones, you can use a - in front of the column name.

# Select all but one column
select(case_data, -province)
# Removing multiple columns
select(case_data, -period, -province)

To choose specific rows based on some criteria, use filter(). Again, the first argument will be the dataframe, then the following argument will be the condition that we want use to subset the data.

filter(case_data, province == "Eastern")

# A tibble: 2,294 × 5
# Rowwise: 
   period     province district   data_type count
   <date>     <chr>    <chr>      <chr>     <dbl>
 1 2018-01-01 Eastern  Chadiza    Clinical     89
 2 2018-01-01 Eastern  Chadiza    Confirmed  3926
 3 2018-01-01 Eastern  Chadiza    Tested     8261
 4 2018-01-01 Eastern  Chasefu    Clinical    357
 5 2018-01-01 Eastern  Chasefu    Confirmed  2599
 6 2018-01-01 Eastern  Chasefu    Tested     9021
 7 2018-01-01 Eastern  Chipangali Confirmed  6330
 8 2018-01-01 Eastern  Chipangali Tested    21612
 9 2018-01-01 Eastern  Chipata    Clinical    417
10 2018-01-01 Eastern  Chipata    Confirmed  4079
# ℹ 2,284 more rows

Notice here that just like in Chapter 1 use have to use a == sign for setting a condition. You read this as saying, “choose the rows in case_data where province is equal to”Eastern”. Also notice that the number of rows in the object has gone down from 18172 to 2294.

We can filter on multiple conditions at once using multiple arguments, using a , to state separate conditions.

filter(case_data, province == "Eastern", data_type == "Clinical")

# A tibble: 379 × 5
# Rowwise: 
   period     province district   data_type count
   <date>     <chr>    <chr>      <chr>     <dbl>
 1 2018-01-01 Eastern  Chadiza    Clinical     89
 2 2018-01-01 Eastern  Chasefu    Clinical    357
 3 2018-01-01 Eastern  Chipata    Clinical    417
 4 2018-01-01 Eastern  Kasenengwa Clinical    331
 5 2018-01-01 Eastern  Katete     Clinical     28
 6 2018-01-01 Eastern  Lumezi     Clinical     19
 7 2018-01-01 Eastern  Lundazi    Clinical     38
 8 2018-01-01 Eastern  Lusangazi  Clinical     58
 9 2018-01-01 Eastern  Petauke    Clinical     20
10 2018-01-01 Eastern  Sinda      Clinical      1
# ℹ 369 more rows

By default, each of the conditions in filter() must be TRUE to remain in the subset, however there are special operators that allow for more complex conditional operations. The most common are the AND (&) and OR (|) operators. Here are some examples:

# Province is Easter AND data type is Clinical 
filter(case_data, province == "Eastern" & data_type == "Clinical")

# Province is Eastern OR data type is Clinical 
filter(case_data, province == "Eastern" | data_type == "Clinical")

# Province is Central OR Eastern, AND count is over 1,000
filter(case_data, province == "Central" | province == "Eastern", count > 1000)

Question 3: Why did we not use & in the third example?

Another useful operator is the MATCH operator (%in%), which will return TRUE if a value matches any value in a list of possible options.

# Keep rows where data type could be Clinical, Confirmed, or Tested
filter(case_data, data_type %in% c("Clinical", "Confirmed", "Tested"))

# A tibble: 13,062 × 5
# Rowwise: 
   period     province district     data_type count
   <date>     <chr>    <chr>        <chr>     <dbl>
 1 2018-01-01 Central  Chibombo     Clinical     26
 2 2018-01-01 Central  Chibombo     Confirmed  1897
 3 2018-01-01 Central  Chibombo     Tested     8215
 4 2018-01-01 Central  Chisamba     Confirmed  2192
 5 2018-01-01 Central  Chisamba     Tested     7772
 6 2018-01-01 Central  Chitambo     Clinical     33
 7 2018-01-01 Central  Chitambo     Confirmed  6011
 8 2018-01-01 Central  Chitambo     Tested     3993
 9 2018-01-01 Central  Itezhi-tezhi Confirmed   890
10 2018-01-01 Central  Itezhi-tezhi Tested     5007
# ℹ 13,052 more rows

# Keep rows from a group of selected districts
study_districts <- c("Chadiza", "Chipata", "Katete", "Lumezi")
filter(case_data, district %in% study_districts)

# A tibble: 649 × 5
# Rowwise: 
   period     province district data_type count
   <date>     <chr>    <chr>    <chr>     <dbl>
 1 2018-01-01 Eastern  Chadiza  Clinical     89
 2 2018-01-01 Eastern  Chadiza  Confirmed  3926
 3 2018-01-01 Eastern  Chadiza  Tested     8261
 4 2018-01-01 Eastern  Chipata  Clinical    417
 5 2018-01-01 Eastern  Chipata  Confirmed  4079
 6 2018-01-01 Eastern  Chipata  Tested    20550
 7 2018-01-01 Eastern  Katete   Clinical     28
 8 2018-01-01 Eastern  Katete   Confirmed  3124
 9 2018-01-01 Eastern  Katete   Tested    18207
10 2018-01-01 Eastern  Lumezi   Clinical     19
# ℹ 639 more rows

Question 4: Can you show all of the “Tested” data in Western Province?

Question 5: Can you show all “Confirmed” that have a count over 2000?

Finally, the ! operator in R used for NOT or opposite conditions. The most common use cases are for using NOT EQUAL (!=) or does NOT MATCH operations.

# Keep all rows where province is NOT Lusaka
filter(case_data, province != "Lusaka")

# A tibble: 16,993 × 5
# Rowwise: 
   period     province district     data_type             count
   <date>     <chr>    <chr>        <chr>                 <dbl>
 1 2018-01-01 Central  Chibombo     Clinical                 26
 2 2018-01-01 Central  Chibombo     Confirmed              1897
 3 2018-01-01 Central  Chibombo     Tested                 8215
 4 2018-01-01 Central  Chisamba     Confirmed              2192
 5 2018-01-01 Central  Chisamba     Tested                 7772
 6 2018-01-01 Central  Chitambo     Clinical                 33
 7 2018-01-01 Central  Chitambo     Confirmed              6011
 8 2018-01-01 Central  Chitambo     Tested                 3993
 9 2018-01-01 Central  Itezhi-tezhi Confirmed               890
10 2018-01-01 Central  Itezhi-tezhi Confirmed_Passive_CHW   897
# ℹ 16,983 more rows

# Keep rows where data type does NOT match Clinical, Confirmed, or Tested
filter(case_data, !data_type %in% c("Clinical", "Confirmed", "Tested"))

# A tibble: 5,110 × 5
# Rowwise: 
   period     province district     data_type             count
   <date>     <chr>    <chr>        <chr>                 <dbl>
 1 2018-01-01 Central  Itezhi-tezhi Confirmed_Passive_CHW   897
 2 2018-01-01 Central  Itezhi-tezhi Tested_Passive_CHW     2708
 3 2018-01-01 Central  Mumbwa       Confirmed_Passive_CHW   861
 4 2018-01-01 Central  Mumbwa       Tested_Passive_CHW     3173
 5 2018-01-01 Central  Shibuyunji   Confirmed_Passive_CHW     6
 6 2018-01-01 Central  Shibuyunji   Tested_Passive_CHW       94
 7 2018-01-01 Eastern  Petauke      Confirmed_Passive_CHW     0
 8 2018-01-01 Eastern  Petauke      Tested_Passive_CHW        0
 9 2018-01-01 Lusaka   Chirundu     Confirmed_Passive_CHW    55
10 2018-01-01 Lusaka   Chirundu     Tested_Passive_CHW      406
# ℹ 5,100 more rows

Note that for NOT EQUAL the ! operator comes right next to the = sign, but for the NOT MATCH condition the ! comes before the condition state. In the second case you can read that as, “do the opposite of this condition”.

Question 6a: Create a table for all malaria tests (health facility and CHW) in Western and Southern Province in 2020.

Question 6b: Create a table for all malaria tests (health facility and CHW) NOT in Western and Southern Province in 2020.

Question 7a: Create a table for all clinical and confirmed cases that are over 500.

Question 7b: Create a table for all data that are NOT clinical and confirmed cases that are over 500.

The types of conditional states that you can use depends on the type of column you want to base your filter()on. For example, filter(case_data, count > 1000) makes sense since the count column contains numeric data. However, filter(case_data, province > 1000) doesn’t make sense since the province column contains character data. The rule of thumb is that the value you use to set your condition should match the “type” of data in selected column.

In the next section, we see how to deal with a special case:

2.1.3.2 Working with dates using the *lubridate* package

In the “tidy” data approach to working with data each column is a specific type of data, each row is an observation, and each cell is an individual value which conveys a single piece of information. Our dataset matches this philosophy, except for the “period” values, which contain information on the year, month, and day of the observation.

We could create separate columns for the year, month, and day, but this may complicate our filtering. For instance, what happens if we want to filter for a study period that continues across over parts of adjacent months or year? Such a common task would require complex set of conditional statements to filter correctly.

The *lubridate* package provides a number of functions to make working with data much easier. This is not included in *tidyverse*, so we have to install and then load it into our session.

# install.packages(lubridate)
library(lubridate)

The ymd() function allows us to create a Date class object based on the string input for YEAR-MONTH-DAY:

# Vector of workshop days
workshop_days <- c("2021-11-15", "2021-11-16", "2021-11-17", "2021-11-18","2021-11-19")
class(workshop_days)

[1] "character"

# Convert to a Date class
workshop_days <- ymd(workshop_days)
class(workshop_days)

[1] "Date"

Once you have a Date class object, *lubridate* provides many, many functions for working with date information. The primary functions we will use in this workshop are year() and month(), but there are many more in this *lubridate* cheatsheet.

year(workshop_days)

[1] 2021 2021 2021 2021 2021

month(workshop_days)

[1] 11 11 11 11 11

month(workshop_days, label = TRUE)

[1] Nov Nov Nov Nov Nov
12 Levels: Jan < Feb < Mar < Apr < May < Jun < Jul < Aug < Sep < ... < Dec

These functions can be used in filter().

filter(case_data, year(period) == 2020)

# A tibble: 5,232 × 5
# Rowwise: 
   period     province district     data_type             count
   <date>     <chr>    <chr>        <chr>                 <dbl>
 1 2020-01-01 Central  Chibombo     Clinical                 51
 2 2020-01-01 Central  Chibombo     Confirmed              9473
 3 2020-01-01 Central  Chibombo     Tested                17192
 4 2020-01-01 Central  Chisamba     Confirmed              2885
 5 2020-01-01 Central  Chisamba     Tested                 5499
 6 2020-01-01 Central  Chitambo     Clinical               1895
 7 2020-01-01 Central  Chitambo     Confirmed              9929
 8 2020-01-01 Central  Chitambo     Tested                12642
 9 2020-01-01 Central  Itezhi-tezhi Confirmed              1406
10 2020-01-01 Central  Itezhi-tezhi Confirmed_Passive_CHW  1184
# ℹ 5,222 more rows

filter(case_data, between(period, ymd("2019-01-01"), ymd("2019-06-30")))

# A tibble: 2,394 × 5
# Rowwise: 
   period     province district     data_type count
   <date>     <chr>    <chr>        <chr>     <dbl>
 1 2019-01-01 Central  Chibombo     Clinical    347
 2 2019-01-01 Central  Chibombo     Confirmed  3706
 3 2019-01-01 Central  Chibombo     Tested     8116
 4 2019-01-01 Central  Chisamba     Confirmed  2304
 5 2019-01-01 Central  Chisamba     Tested     6975
 6 2019-01-01 Central  Chitambo     Clinical      2
 7 2019-01-01 Central  Chitambo     Confirmed  7328
 8 2019-01-01 Central  Chitambo     Tested     8320
 9 2019-01-01 Central  Itezhi-tezhi Clinical      6
10 2019-01-01 Central  Itezhi-tezhi Confirmed   833
# ℹ 2,384 more rows

Question 8: What were the reported total tests (HF and CHW) in Chadiza district each month during 2018?

Question 9: How many tests (HF and CHW) were conducted in April 2020 in Nchelenge district?

Question 10 (HARD): What were the monthly confirmed cases in Chadiza during the peak transmission season (December to May) each year?

2.1.3.3 Creating new columns with mutate()

Another common task is creating new columns based on values in existing columns. The *dplyr* function for this action is mutate().

Here is an example using the *lubridate* function from the section above to make a column for the year of observation:

mutate(case_data, year = year(period))

# A tibble: 18,172 × 6
# Rowwise: 
   period     province district     data_type             count  year
   <date>     <chr>    <chr>        <chr>                 <dbl> <dbl>
 1 2018-01-01 Central  Chibombo     Clinical                 26  2018
 2 2018-01-01 Central  Chibombo     Confirmed              1897  2018
 3 2018-01-01 Central  Chibombo     Tested                 8215  2018
 4 2018-01-01 Central  Chisamba     Confirmed              2192  2018
 5 2018-01-01 Central  Chisamba     Tested                 7772  2018
 6 2018-01-01 Central  Chitambo     Clinical                 33  2018
 7 2018-01-01 Central  Chitambo     Confirmed              6011  2018
 8 2018-01-01 Central  Chitambo     Tested                 3993  2018
 9 2018-01-01 Central  Itezhi-tezhi Confirmed               890  2018
10 2018-01-01 Central  Itezhi-tezhi Confirmed_Passive_CHW   897  2018
# ℹ 18,162 more rows

First, state the name for the new column, then = followed by the function for the new value. You can create multiple new columns in a single mutate() call, using a , to separate each column.

mutate(case_data,
    year = year(period), 
    month_num = month(period),
    month_name = month(period, label = TRUE))

# A tibble: 18,172 × 8
# Rowwise: 
   period     province district     data_type   count  year month_num month_name
   <date>     <chr>    <chr>        <chr>       <dbl> <dbl>     <dbl> <ord>     
 1 2018-01-01 Central  Chibombo     Clinical       26  2018         1 Jan       
 2 2018-01-01 Central  Chibombo     Confirmed    1897  2018         1 Jan       
 3 2018-01-01 Central  Chibombo     Tested       8215  2018         1 Jan       
 4 2018-01-01 Central  Chisamba     Confirmed    2192  2018         1 Jan       
 5 2018-01-01 Central  Chisamba     Tested       7772  2018         1 Jan       
 6 2018-01-01 Central  Chitambo     Clinical       33  2018         1 Jan       
 7 2018-01-01 Central  Chitambo     Confirmed    6011  2018         1 Jan       
 8 2018-01-01 Central  Chitambo     Tested       3993  2018         1 Jan       
 9 2018-01-01 Central  Itezhi-tezhi Confirmed     890  2018         1 Jan       
10 2018-01-01 Central  Itezhi-tezhi Confirmed_…   897  2018         1 Jan       
# ℹ 18,162 more rows

Remember that if you want to save any changes you will have to save the output into an object using the <- assignment operator.

case_data_dates <- mutate(case_data,
    year = year(period), 
    month_num = month(period),
    month_name = month(period, label = TRUE))

In later sections we will see how to use mutate() to make calculations.

Question 11: Can you add a new variable (column) to the dataset that gives the quarter of the year?

Question 12: Can you add a new variable (column) to the dataset that gives just the last 2 digits of the year? i.e. 2021 becomes 21