3  Combine and Reshape Data

Author

Justin Millar and Benson Mwelwa

Published

April 4, 2025

3.1 Restructuring Data

In this section, we will learn how to restructure data using additional functions from the dplyr' andtidyr` packages.

Concepts that we will cover include: - Combine manipulation steps from the previous lesson using pipes - Grouping and summarization - Reshaping or “pivoting” data into different formats

Before we start, let’s load in the tidyverse package, which includes the dplyr and tidyr packages. We will also load in the dataset we used in the previous lesson.

Code 
library(tidyverse)
Code 
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))

3.2 Use Pipes to combine steps

What if you want to select and filter at the same time? There are three ways to do this: use intermediate steps, nested functions, or pipes.

For intermediate steps, we need to create a new intermediate object for the output of our first function, which will then be used as an input for the second function:

Code 
case_data_lusaka_confirmed <- filter(case_data, province == "Lusaka", data_type == "Confirmed")
case_data_lusaka_district_months <- select(case_data_lusaka_confirmed, district, period, count)
case_data_lusaka_district_months
# A tibble: 301 × 3
# Rowwise: 
   district period     count
   <chr>    <date>     <dbl>
 1 Chilanga 2018-01-01   151
 2 Chirundu 2018-01-01   198
 3 Chongwe  2018-01-01   426
 4 Kafue    2018-01-01   290
 5 Luangwa  2018-01-01   776
 6 Lusaka   2018-01-01  1248
 7 Rufunsa  2018-01-01  2907
 8 Chilanga 2018-02-01   165
 9 Chirundu 2018-02-01    89
10 Chongwe  2018-02-01   467
# ℹ 291 more rows

This approach is readable, but it can quickly clutter up your workspace and take up additional memory. And if you’re trying to use meaningful object names it can get tedious quickly.

You can also nest the functions (one function inside of the another).

Code 
case_data_lusaka_district_months <- select(
  filter(case_data, province == "Lusaka", data_type == "Confirmed"), 
  district, period, count)

This doesn’t clutter the workshop or take up unnecessary memory, but it is difficult to read especially since R will interpret these steps from the inside out (first filter, then select).

The last option is to use pipes, a new addition to R. A pipe lets you take the output from one function and input it directly into the next function. By default, this will automatically go into the first argument of the new function. This is useful for stringing together multiple data cleaning steps while maintaining readability and keeping our environment clear. The *tidyverse* package includes a pipe function which looks like %>%. In RStudio, the shortcut for this pipe is Ctrl + Shift + M if you have a PC or Cmd + Shift + M if you have a Mac. You can adjust this shortcut under Tools >> Modify Keyboard Shortcuts…

Here’s an example of using a pipe for combine the filter and select from the previous example.

Code 
case_data %>% 
  filter(province == "Lusaka", data_type == "Confirmed") %>% 
  select(district, period, count)
# A tibble: 301 × 3
# Rowwise: 
   district period     count
   <chr>    <date>     <dbl>
 1 Chilanga 2018-01-01   151
 2 Chirundu 2018-01-01   198
 3 Chongwe  2018-01-01   426
 4 Kafue    2018-01-01   290
 5 Luangwa  2018-01-01   776
 6 Lusaka   2018-01-01  1248
 7 Rufunsa  2018-01-01  2907
 8 Chilanga 2018-02-01   165
 9 Chirundu 2018-02-01    89
10 Chongwe  2018-02-01   467
# ℹ 291 more rows

In this code, we used a pipe to send case_data into a filter() function and keep the rows for confirmed cases in Lusaka province, then used another pipe to send that output into a select() where we only kept the district, period, and count columns. We didn’t need to explicitly state the data object in the filter and select because data is always the first argument.

You may find it helpful to read the pipe like the word “then”. Take the case, then filter for Lusaka province and confirmed cases, then select the districts, periods, and counts. We can also save this into a new object.

Code 
lusaka_confirmed_cases <- case_data %>% 
  filter(province == "Lusaka", data_type == "Confirmed") %>% 
  select(district, period, count)
Question

Using pipes, create a table contain the confirmed cases in January 2019 for each district in Southern Province. The table should only have two columns (district and counts)

3.2.1 Native Pipe Operator in R (R 4.1+)

Starting with R version 4.1, a native pipe operator (|>) was introduced, providing an alternative to the %>% pipe from the magrittr package. The native pipe is part of the base R language, eliminating the need to load external packages for basic piping operations.

The native pipe operator works similarly to the %>% operator but uses |> instead.

Syntax:

data |> 
  function1() |>
  function2() |>
  function3()

Here is an example using traditional function chaining without the native pipe:

Example:

data <- mtcars
filtered_data <- filter(data, cyl == 6)
selected_data <- select(filtered_data, mpg, hp)
summarized_data <- summarize(selected_data, mean_mpg = mean(mpg), mean_hp = mean(hp))

print(summarized_data)

The same operations can be performed more concisely using the native pipe:

Example:

mtcars |>
  (\(df) filter(df, cyl == 6))() |>
  (\(df) select(df, mpg, hp))() |>
  (\(df) summarize(df, mean_mpg = mean(mpg), mean_hp = mean(hp)))() |>
  print()

The introduction of the native pipe operator in R 4.1 provides a built-in and efficient way to chain operations, enhancing code readability without relying on external packages. While it lacks some of the tools of %>%, such as the dot placeholder, it integrates seamlessly with base R functions and custom workflows.

You may encounter both types of pipes, and most often they work interchangeably. However, it is important to note the stuble differences between the tidyverse and native pipes, especially when debugging errors.

3.3 Grouping and summarizing data

Another common data manipulation tasks involve grouping data together and applying summary functions such as calculating means or totals. We can do some of these types of operations already. For instance, we can get the total number of clinical cases in Lusaka Province.

Code 
lusaka_clinical <- case_data %>% 
  filter(data_type == "Clinical", province == "Lusaka")
sum(lusaka_clinical$count, na.rm = T)
[1] 68524

But what if we want to get summaries for each province at once? We could repeat the steps above, separating each province, calculating the totals, and then grouping these summaries back together. In programming this concept is often referred to as the split-apply-combine paradigm. The key *dplyr* functions for these tasks are group_by() and summarize() (you can also use the “proper” summarise() spelling as well).

First, group_by() takes in a column that contains categorical data, then use summarize() to calculate new summary statistics.

Code 
case_data %>% 
  filter(data_type == "Tested") %>% 
  group_by(province) %>% 
  summarise(mean_tested = mean(count, na.rm = TRUE))
# A tibble: 11 × 2
   province     mean_tested
   <chr>              <dbl>
 1 Central            8199.
 2 Copperbelt        15741.
 3 Eastern           12389.
 4 Luapula            9217.
 5 Lusaka             5646.
 6 Muchinga           8709.
 7 Northern           7090.
 8 Northwestern      10500.
 9 Southern           1987.
10 Western            5524.
11 <NA>               1234.

You can also group by more than one column, and output multiple columns within a single summarize() call.

Code 
case_data %>% 
  filter(data_type == "Tested") %>% 
  group_by(province, district, year(period)) %>% 
  summarise(mean_tested_per_month = mean(count, na.rm = TRUE), 
            total_tested_per_month = sum(count, na.rm = TRUE))
# A tibble: 466 × 5
# Groups:   province, district [117]
   province district `year(period)` mean_tested_per_month total_tested_per_month
   <chr>    <chr>             <dbl>                 <dbl>                  <dbl>
 1 Central  Chibombo           2018                 8442.                 101301
 2 Central  Chibombo           2019                 7309.                  87711
 3 Central  Chibombo           2020                12910.                 154926
 4 Central  Chibombo           2021                14252.                  99763
 5 Central  Chisamba           2018                 6959.                  83511
 6 Central  Chisamba           2019                 5204                   62448
 7 Central  Chisamba           2020                 5815.                  69776
 8 Central  Chisamba           2021                 5825.                  40773
 9 Central  Chitambo           2018                 4071.                  48855
10 Central  Chitambo           2019                 5620.                  67442
# ℹ 456 more rows
Code 
case_data %>% 
  filter(data_type == "Tested") %>% 
  group_by(province, district, year(period)) %>% 
  summarise(mean_tested_per_month = mean(count, na.rm = TRUE), 
            total_tested_per_month = sum(count, na.rm = TRUE))
# A tibble: 466 × 5
# Groups:   province, district [117]
   province district `year(period)` mean_tested_per_month total_tested_per_month
   <chr>    <chr>             <dbl>                 <dbl>                  <dbl>
 1 Central  Chibombo           2018                 8442.                 101301
 2 Central  Chibombo           2019                 7309.                  87711
 3 Central  Chibombo           2020                12910.                 154926
 4 Central  Chibombo           2021                14252.                  99763
 5 Central  Chisamba           2018                 6959.                  83511
 6 Central  Chisamba           2019                 5204                   62448
 7 Central  Chisamba           2020                 5815.                  69776
 8 Central  Chisamba           2021                 5825.                  40773
 9 Central  Chitambo           2018                 4071.                  48855
10 Central  Chitambo           2019                 5620.                  67442
# ℹ 456 more rows

Sometimes it is useful to rearrange the result of our summarized dataset, in which case we can use the arrange() function.

Code 
case_data %>% 
  filter(data_type == "Tested", 
         year(period) == 2019) %>% 
  group_by(district, period) %>% 
  summarise(total_tested_per_month = sum(count)) %>% 
  arrange(total_tested_per_month)
# A tibble: 1,396 × 3
# Groups:   district [117]
   district   period     total_tested_per_month
   <chr>      <date>                      <dbl>
 1 Mwandi     2019-10-01                     45
 2 Mwandi     2019-11-01                    144
 3 Mwandi     2019-01-01                    267
 4 Mwandi     2019-09-01                    287
 5 Mwandi     2019-12-01                    292
 6 Shang'ombo 2019-11-01                    312
 7 Pemba      2019-08-01                    319
 8 Shang'ombo 2019-07-01                    348
 9 Shang'ombo 2019-10-01                    369
10 Mwandi     2019-06-01                    389
# ℹ 1,386 more rows

By default arranging with be in ascending order, you can use desc() to make the output descending.

Code 
case_data %>% 
  filter(data_type == "Tested", 
         year(period) == 2019) %>% 
  group_by(district, period) %>% 
  summarise(total_tested_per_month = sum(count)) %>% 
  arrange(desc(total_tested_per_month))
# A tibble: 1,396 × 3
# Groups:   district [117]
   district      period     total_tested_per_month
   <chr>         <date>                      <dbl>
 1 Kapiri-Mposhi 2019-03-01                  53173
 2 Kapiri-Mposhi 2019-04-01                  45125
 3 Petauke       2019-04-01                  44856
 4 Kitwe         2019-01-01                  44378
 5 Kapiri-Mposhi 2019-05-01                  40387
 6 Ndola         2019-04-01                  39858
 7 Kitwe         2019-02-01                  39630
 8 Chipangali    2019-04-01                  39166
 9 Solwezi       2019-12-01                  38709
10 Kitwe         2019-03-01                  37622
# ℹ 1,386 more rows
Question

What were the total number of confirmed cases in each province in 2019?

Question

What were the total number of cases (Clinical and Confirmed) in each month for all provinces for each year?

Question

What were the total number of cases (Clinical and Confirmed) in the peak (dec - may) and the low (june - nov) transmission seasons for each district in Southern Province during the 2019/2020 transmission season (i.e. Dec 2019 - Nov 2020)?

3.4 Reshaping data with *tidyr*

So far we have covered a bunch of *dplyr* functions for manipulating data, most of which have changed the number of rows and/or columns in our dataframe. However, even though the columns, rows, and values have changed none of these have changed the “structure” of the dataframe. At the end of each function or piped function, the output always followed the conditions we discussed early:

  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.

This is commonly referred to as “long” format data, and often this means that there are relatively more rows than columns. Typically it is best to work in “long” format data, especially in R, however there are instances when we may want to change the “shape” of our data into the “wide” format. In Microsoft Excel this would be called creating a Pivot Table.

The *tidyr* package provides functions for reshaping data, including creating “wide” format pivot tables. To illustrate, lets take a look at records for a single district at a single timepoint.

Code 
case_data %>% 
  filter(district == "Chadiza") %>%
  filter(period == ymd("2020-01-01"))
# A tibble: 4 × 5
# Rowwise: 
  period     province district data_type             count
  <date>     <chr>    <chr>    <chr>                 <dbl>
1 2020-01-01 Eastern  Chadiza  Confirmed              3986
2 2020-01-01 Eastern  Chadiza  Confirmed_Passive_CHW  7321
3 2020-01-01 Eastern  Chadiza  Tested                 8152
4 2020-01-01 Eastern  Chadiza  Tested_Passive_CHW    12297
Why use filter() twice?

In the code above, we could have combined the two filters into one (e.g., case_data %>% filter(district == "Chadiza" & period == ymd("2020-01-01"))). However, filtering the date is a more time consuming operation than filtering the district, so we can do this first to speed up the process. Try testing out both versions to see the difference!

The resulting table has multiple rows, one for each of the different types of records included. But what if we wanted to create a table where there is a separate column for each type of record? The pivot_wider() function will allow us to create this kind of “wide” format table. This function requires us to state the column which our column names will come from (names_from), and which column the values in the new columns will come from (values_from).

Code 
case_data %>% 
  filter(district == "Chadiza") %>%
  filter(period == ymd("2020-01-01")) %>%
  pivot_wider(names_from = data_type, values_from = count)
# A tibble: 1 × 7
  period     province district Confirmed Confirmed_Passive_CHW Tested
  <date>     <chr>    <chr>        <dbl>                 <dbl>  <dbl>
1 2020-01-01 Eastern  Chadiza       3986                  7321   8152
# ℹ 1 more variable: Tested_Passive_CHW <dbl>

The resulting output just has one row, but new columns for each of the types of records. This “wide” format is often useful for creating summary tables.

The opposite function is called pivot_longer(), which will take in a “wide” format table and output a “long” format table. For pivot_longer() we need to state the column name for the “key” which provides the label, the column names for the values, and which columns we want to pivot on. Here is how we can convert the above example from “wide” format to “long” format.

Code 
wide_data <- case_data %>% 
  filter(district == "Chadiza") %>%
  filter(period == ymd("2020-01-01")) %>% 
  pivot_wider(names_from = data_type, values_from = count)


wide_data %>% 
  pivot_longer(
    names_to = "data_type", values_to = "count", 
    cols = c(Confirmed, Confirmed_Passive_CHW, Tested, 
             Tested_Passive_CHW))
# A tibble: 4 × 5
  period     province district data_type             count
  <date>     <chr>    <chr>    <chr>                 <dbl>
1 2020-01-01 Eastern  Chadiza  Confirmed              3986
2 2020-01-01 Eastern  Chadiza  Confirmed_Passive_CHW  7321
3 2020-01-01 Eastern  Chadiza  Tested                 8152
4 2020-01-01 Eastern  Chadiza  Tested_Passive_CHW    12297
Question

Create a table that contains the total number of confirmed cases each year for each province, then pivot wider to make it so the rows are the year and there is a column for each province.

Tip

Think of this as a multi-step process
1. Filter for confirmed cases
2. Work out which columns you are grouping by (i.e. what are we grouping over….think geography/time?)
3. Look at the output so far - what do we now want to sum over for our table?
4. Now we need to pivot wider - we want to make new columns - where do those names to come from (names_from)?, where will we find the values to populate the new cells (values_from)?

Question

Can you make this table longer again, where we now have 3 columns - year, province and count

Question

Create a table that contains the total number of CHW tests for each district and each year in Luapula Province, now pivot wider to make each district its own column where year is now the row - start by writing out the steps 1 by 1 as above

3.5 Final Exercises

  1. For each province, calculate the total number of tests conducted by CHWs each year and present as a wide dataframe, where each column is a year and each row is a province and finally, order the dataframe such that the province with the most tests in 2020 is at the top
  1. For each district in Eastern Province, calculate the test positivity rate at health facilities and by CHWs in 2020 (Test positivity = confirmed cases / total tests)
Tip

Tips for Final Exercises 1. Start by filtering your data - what geographical region, year, and data types do we need?
2. Now we need to pivot wider - how can we now have data_type as our column names?
3. Now we need to group_by - what are we grouping by?
4. Almost there!! Now we need to sum up some columns - how can we do this?
5. Now to calculate TPR - let’s use the mutate function
6. Now let’s use the ‘select’ function to retrieve the columns we want

3.6 Function cheatsheet