data-tidy.qmd

# Data tidying {#sec-data-tidy}

```{r}
#| results: "asis"
#| echo: false
source("_common.R")
status("complete")
```

## Introduction

> "Happy families are all alike; every unhappy family is unhappy in its own way."\
> --- Leo Tolstoy

> "Tidy datasets are all alike, but every messy dataset is messy in its own way."\
> --- Hadley Wickham

In this chapter, you will learn a consistent way to organize your data in R using a system called **tidy data**.
Getting your data into this format requires some work up front, but that work pays off in the long term.
Once you have tidy data and the tidy tools provided by packages in the tidyverse, you will spend much less time munging data from one representation to another, allowing you to spend more time on the data questions you care about.

In this chapter, you'll first learn the definition of tidy data and see it applied to a simple toy dataset.
Then we'll dive into the primary tool you'll use for tidying data: pivoting.
Pivoting allows you to change the form of your data without changing any of the values.

### Prerequisites

In this chapter, we'll focus on tidyr, a package that provides a bunch of tools to help tidy up your messy datasets.
tidyr is a member of the core tidyverse.

```{r}
#| label: setup
#| message: false

library(tidyverse)
```

From this chapter on, we'll suppress the loading message from `library(tidyverse)`.

## Tidy data {#sec-tidy-data}

You can represent the same underlying data in multiple ways.
The example below shows the same data organized in three different ways.
Each dataset shows the same values of four variables: *country*, *year*, *population*, and number of documented *cases* of TB (tuberculosis), but each dataset organizes the values in a different way.

```{r}
#| echo: false

table2 <- table1 |> 
  pivot_longer(cases:population, names_to = "type", values_to = "count")

table3 <- table2 |> 
  pivot_wider(names_from = year, values_from = count)
```

```{r}
table1

table2

table3
```

These are all representations of the same underlying data, but they are not equally easy to use.
One of them, `table1`, will be much easier to work with inside the tidyverse because it's **tidy**.

There are three interrelated rules that make a dataset tidy:

1.  Each variable is a column; each column is a variable.
2.  Each observation is a row; each row is an observation.
3.  Each value is a cell; each cell is a single value.

@fig-tidy-structure shows the rules visually.

```{r}
#| label: fig-tidy-structure
#| echo: false
#| fig-cap: > 
#|   The following three rules make a dataset tidy: variables are columns,
#|   observations are rows, and values are cells.
#| fig-alt: > 
#|   Three panels, each representing a tidy data frame. The first panel
#|   shows that each variable is a column. The second panel shows that each
#|   observation is a row. The third panel shows that each value is
#|   a cell.

knitr::include_graphics("images/tidy-1.png", dpi = 270)
```

Why ensure that your data is tidy?
There are two main advantages:

1.  There's a general advantage to picking one consistent way of storing data.
    If you have a consistent data structure, it's easier to learn the tools that work with it because they have an underlying uniformity.

2.  There's a specific advantage to placing variables in columns because it allows R's vectorized nature to shine.
    As you learned in @sec-mutate and @sec-summarize, most built-in R functions work with vectors of values.
    That makes transforming tidy data feel particularly natural.

dplyr, ggplot2, and all the other packages in the tidyverse are designed to work with tidy data.
Here are a few small examples showing how you might work with `table1`.

```{r}
#| fig-width: 5
#| fig-alt: >
#|   This figure shows the number of cases in 1999 and 2000 for 
#|   Afghanistan, Brazil, and China, with year on the x-axis and number 
#|   of cases on the y-axis. Each point on the plot represents the number 
#|   of cases in a given country in a given year. The points for each
#|   country are differentiated from others by color and shape and connected
#|   with a line, resulting in three, non-parallel, non-intersecting lines.
#|   The numbers of cases in China are highest for both 1999 and 2000, with
#|   values above 200,000 for both years. The number of cases in Brazil is
#|   approximately 40,000 in 1999 and approximately 75,000 in 2000. The
#|   numbers of cases in Afghanistan are lowest for both 1999 and 2000, with
#|   values that appear to be very close to 0 on this scale.

# Compute rate per 10,000
table1 |>
  mutate(rate = cases / population * 10000)

# Compute cases per year
table1 |>
  count(year, wt = cases)

# Visualize changes over time
ggplot(table1, aes(x = year, y = cases)) +
  geom_line(aes(group = country), color = "grey50") +
  geom_point(aes(color = country, shape = country)) +
  scale_x_continuous(breaks = c(1999, 2000))
```

### Exercises

1.  Using words, describe how the variables and observations are organized in each of the sample tables.

2.  Sketch out the process you'd use to calculate the `rate` for `table2` and `table3`.
    You will need to perform four operations:

    a.  Extract the number of TB cases per country per year.
    b.  Extract the matching population per country per year.
    c.  Divide cases by population, and multiply by 10000.
    d.  Store back in the appropriate place.

    You haven't yet learned all the functions you'd need to actually perform these operations, but you should still be able to think through the transformations you'd need.

## Lengthening data {#sec-pivoting}

The principles of tidy data might seem so obvious that you wonder if you'll ever encounter a dataset that isn't tidy.
Unfortunately, however, most real data is untidy.
There are two main reasons:

1.  Data is often organized to facilitate some goal other than analysis.
    For example, it's common for data to be structured to make data entry, not analysis, easy.

2.  Most people aren't familiar with the principles of tidy data, and it's hard to derive them yourself unless you spend a lot of time working with data.

This means that most real analyses will require at least a little tidying.
You'll begin by figuring out what the underlying variables and observations are.
Sometimes this is easy; other times you'll need to consult with the people who originally generated the data.
Next, you'll **pivot** your data into a tidy form, with variables in the columns and observations in the rows.

tidyr provides two functions for pivoting data: `pivot_longer()` and `pivot_wider()`.
We'll first start with `pivot_longer()` because it's the most common case.
Let's dive into some examples.

### Data in column names {#sec-billboard}

The `billboard` dataset records the billboard rank of songs in the year 2000:

```{r}
billboard
```

In this dataset, each observation is a song.
The first three columns (`artist`, `track` and `date.entered`) are variables that describe the song.
Then we have 76 columns (`wk1`-`wk76`) that describe the rank of the song in each week.
Here, the column names are one variable (the `week`) and the cell values are another (the `rank`).

To tidy this data, we'll use `pivot_longer()`.
After the data, there are three key arguments:

-   `cols` specifies which columns need to be pivoted, i.e. which columns aren't variables. This argument uses the same syntax as `select()` so here we could use `!c(artist, track, date.entered)` or `starts_with("wk")`.
-   `names_to` names of the variable stored in the column names, here `"week"`.
-   `values_to` names the variable stored in the cell values, here `"rank"`.

That gives the following call:

```{r, R.options=list(pillar.print_min = 10)}
billboard |> 
  pivot_longer(
    cols = starts_with("wk"), 
    names_to = "week", 
    values_to = "rank"
  )
```

What happens if a song is in the top 100 for less than 76 weeks?
Take 2 Pac's "Baby Don't Cry", for example.
The above output suggests that it was only the top 100 for 7 weeks, and all the remaining weeks are filled in with missing values.
These `NA`s don't really represent unknown observations; they're forced to exist by the structure of the dataset[^data-tidy-1], so we can ask `pivot_longer()` to get rid of them by setting `values_drop_na = TRUE`:

[^data-tidy-1]: We'll come back to this idea in @sec-missing-values.

```{r}
billboard |> 
  pivot_longer(
    cols = starts_with("wk"), 
    names_to = "week", 
    values_to = "rank",
    values_drop_na = TRUE
  )
```

You might also wonder what happens if a song is in the top 100 for more than 76 weeks?
We can't tell from this data, but you might guess that additional columns `wk77`, `wk78`, ... would be added to the dataset.

This data is now tidy, but we could make future computation a bit easier by converting `week` into a number using `mutate()` and `readr::parse_number()`.
`parse_number()` is a handy function that will extract the first number from a string, ignoring all other text.

```{r}
billboard_tidy <- billboard |> 
  pivot_longer(
    cols = starts_with("wk"), 
    names_to = "week", 
    values_to = "rank",
    values_drop_na = TRUE
  ) |> 
  mutate(
    week = parse_number(week)
  )
billboard_tidy
```

Now we're in a good position to look at how song ranks vary over time by drawing a plot.
The code is shown below and the result is @fig-billboard-ranks.

```{r}
#| label: fig-billboard-ranks
#| fig-cap: >
#|   A line plot showing how the rank of a song changes over time.
#| fig-alt: >
#|   A line plot with week on the x-axis and rank on the y-axis, where
#|   each line represents a song. Most songs appear to start at a high rank,
#|   rapidly accelerate to a low rank, and then decay again. There are
#|   surprisingly few tracks in the region when week is >20 and rank is
#|   >50.

billboard_tidy |> 
  ggplot(aes(x = week, y = rank, group = track)) + 
  geom_line(alpha = 1/3) + 
  scale_y_reverse()
```

### How does pivoting work?

Now that you've seen what pivoting can do for you, it's worth taking a little time to gain some intuition about what it does to the data.
Let's start with a very simple dataset to make it easier to see what's happening:

```{r}
df <- tribble(
  ~var, ~col1, ~col2,
   "A",     1,     2,
   "B",     3,     4,
   "C",     5,     6
)
```

Here we'll say there are three variables: `var` (already in a variable), `name` (the column names in the column names), and `value` (the cell values).
So we can tidy it with:

```{r}
df |> 
  pivot_longer(
    cols = col1:col2,
    names_to = "name",
    values_to = "value"
  )
```

How does this transformation take place?
It's easier to see if we take it component by component.
Columns that are already variables need to be repeated, once for each column in `cols`, as shown in @fig-pivot-variables.

```{r}
#| label: fig-pivot-variables
#| echo: false
#| fig-cap: > 
#|   Columns that are already variables need to be repeated, once for
#|   each column that is pivotted.
#| fig-alt: > 
#|   A diagram showing how `pivot_longer()` transforms a simple
#|   dataset, using color to highlight how the values in the `var` column
#|   ("A", "B", "C") are each repeated twice in the output because there are
#|   two columns being pivotted ("col1" and "col2").

knitr::include_graphics("diagrams/tidy-data/variables.png", dpi = 270)
```

The column names become values in a new variable, whose name is given by `names_to`, as shown in @fig-pivot-names.
They need to be repeated once for each row in the original dataset.

```{r}
#| label: fig-pivot-names
#| echo: false
#| fig-cap: >
#|   The column names of pivoted columns become a new column. The values
#|   need to be repeated once for each row of the original dataset.
#| fig-alt: > 
#|   A diagram showing how `pivot_longer()` transforms a simple
#|   data set, using color to highlight how column names ("col1" and 
#|   "col2") become the values in a new `var` column. They are repeated
#|   three times because there were three rows in the input.

knitr::include_graphics("diagrams/tidy-data/column-names.png", dpi = 270)
```

The cell values also become values in a new variable, with a name given by `values_to`.
They are unwound row by row.
@fig-pivot-values illustrates the process.

```{r}
#| label: fig-pivot-values
#| echo: false
#| fig-cap: >
#|   The number of values is preserved (not repeated), but unwound
#|   row-by-row.
#| fig-alt: > 
#|   A diagram showing how `pivot_longer()` transforms data,
#|   using color to highlight how the cell values (the numbers 1 to 6)
#|   become the values in a new `value` column. They are unwound row-by-row,
#|   so the original rows (1,2), then (3,4), then (5,6), become a column
#|   running from 1 to 6.

knitr::include_graphics("diagrams/tidy-data/cell-values.png", dpi = 270)
```

### Many variables in column names

A more challenging situation occurs when you have multiple variables crammed into the column names.
For example, take the `who2` dataset, the source of `table1` and friends that you saw above:

```{r}
who2
```

This dataset records information about tuberculosis data collected by the WHO.
There are two columns that are already variables and are easy to interpret: `country` and `year`.
They are followed by 56 columns like `sp_m_014`, `ep_m_4554`, and `rel_m_3544`.
If you stare at these columns for long enough, you'll notice there's a pattern.
Each column name is made up of three pieces separated by `_`.
The first piece, `sp`/`rel`/`ep`, describes the method used for the `diagnosis`, the second piece, `m`/`f` is the `gender`, and the third piece, `014`/`1524`/`2535`/`3544`/`4554`/`65` is the `age` range.

So in this case we have six variables: two variables are already columns, three variables are contained in the column name, and one variable is in the cell value.
This requires two changes to our call to `pivot_longer()`: `names_to` gets a vector of column names and `names_sep` describes how to split the variable name up into pieces:

```{r}
who2 |> 
  pivot_longer(
    cols = !(country:year),
    names_to = c("diagnosis", "gender", "age"), 
    names_sep = "_",
    values_to = "count"
  )
```

An alternative to `names_sep` is `names_pattern`, which you can use to extract variables from more complicated naming scenarios, once you've learned about regular expressions in @sec-regular-expressions.

Conceptually, this is only a minor variation on the simpler case you've already seen.
@fig-pivot-multiple-names shows the basic idea: now, instead of the column names pivoting into a single column, they pivot into multiple columns.
You can imagine this happening in two steps (first pivoting and then separating) but under the hood it happens in a single step because that gives better performance.

```{r}
#| label: fig-pivot-multiple-names
#| echo: false
#| fig-cap: >
#|   Pivotting with many variables in the column names means that each
#|   column name now fills in values in multiple output columns.
#| fig-alt: >
#|   A diagram that uses color to illustrate how supplying `names_sep` 
#|   and multiple `names_to` creates multiple variables in the output.
#|   The input has variable names "x_1" and "y_2" which are split up
#|   by "_" to create name and number columns in the output. This is
#|   is similar case with a single `names_to`, but what would have been a
#|   single output variable is now separated into multiple variables.

knitr::include_graphics("diagrams/tidy-data/multiple-names.png", dpi = 270)
```

### Data and variable names in the column headers

The next step up in complexity is when the column names include a mix of variable values and variable names.
For example, take the `household` dataset:

```{r}
household
```

This dataset contains data about five families, with the names and dates of birth of up to two children.
The new challenge in this dataset is that the column names contain the names of two variables (`dob`, `name)` and the values of another (`child,` with values 1 or 2).
To solve this problem we again need to supply a vector to `names_to` but this time we use the special `".value"` sentinel.
This overrides the usual `values_to` argument to use the first component of the pivoted column name as a variable name in the output.

```{r}
household |> 
  pivot_longer(
    cols = !family, 
    names_to = c(".value", "child"), 
    names_sep = "_", 
    values_drop_na = TRUE
  ) |> 
  mutate(
    child = parse_number(child)
  )
```

We again use `values_drop_na = TRUE`, since the shape of the input forces the creation of explicit missing variables (e.g. for families with only one child), and `parse_number()` to convert (e.g.) `child1` into 1.

@fig-pivot-names-and-values illustrates the basic idea with a simpler example.
When you use `".value"` in `names_to`, the column names in the input contribute to both values and variable names in the output.

```{r}
#| label: fig-pivot-names-and-values
#| echo: false
#| fig-cap: >
#|   Pivoting with `names_to = c(".value", "id")` splits the column names
#|   into two components: the first part determines the output column
#|   name (`x` or `y`), and the second part determines the value of the
#|   `id` column.
#| fig-alt: >
#|   A diagram that uses color to illustrate how the special ".value"
#|   sentinel works. The input has names "x_1", "x_2", "y_1", and "y_2",
#|   and we want to use the first component ("x", "y") as a variable name
#|   and the second ("1", "2") as the value for a new "id" column.

knitr::include_graphics("diagrams/tidy-data/names-and-values.png", dpi = 270)
```

## Widening data

So far we've used `pivot_longer()` to solve the common class of problems where values have ended up in column names.
Next we'll pivot (HA HA) to `pivot_wider()`, which which makes datasets **wider** by increasing columns and reducing rows and helps when one observation is spread across multiple rows.
This seems to arise less commonly in the wild, but it does seem to crop up a lot when dealing with governmental data.

We'll start by looking at `cms_patient_experience`, a dataset from the Centers of Medicare and Medicaid services that collects data about patient experiences:

```{r}
cms_patient_experience
```

An observation is an organization, but each organization is spread across six rows, with one row for each variable, or measure.
We can see the complete set of values for `measure_cd` and `measure_title` by using `distinct()`:

```{r}
cms_patient_experience |> 
  distinct(measure_cd, measure_title)
```

Neither of these columns will make particularly great variable names: `measure_cd` doesn't hint at the meaning of the variable and `measure_title` is a long sentence containing spaces.
We'll use `measure_cd` for now, but in a real analysis you might want to create your own variable names that are both short and meaningful.

`pivot_wider()` has the opposite interface to `pivot_longer()`: we need to provide the existing columns that define the values (`values_from`) and the column name (`names_from)`:

```{r}
cms_patient_experience |> 
  pivot_wider(
    names_from = measure_cd,
    values_from = prf_rate
  )
```

The output doesn't look quite right; we still seem to have multiple rows for each organization.
That's because, by default, `pivot_wider()` will attempt to preserve all the existing columns including `measure_title` which has six distinct observations for each organizations.
To fix this problem we need to tell `pivot_wider()` which columns identify each row; in this case those are the variables starting with `"org"`:

```{r}
cms_patient_experience |> 
  pivot_wider(
    id_cols = starts_with("org"),
    names_from = measure_cd,
    values_from = prf_rate
  )
```

This gives us the output that we're looking for.

### How does `pivot_wider()` work?

To understand how `pivot_wider()` works, let's again start with a very simple dataset:

```{r}
df <- tribble(
  ~id, ~name, ~value,
  "A", "x", 1,
  "B", "y", 2,
  "B", "x", 3, 
  "A", "y", 4,
  "A", "z", 5,
)
```

We'll take the values from the `value` column and the names from the `name` column:

```{r}
df |> 
  pivot_wider(
    names_from = name,
    values_from = value
  )
```

The connection between the position of the row in the input and the cell in the output is weaker than in `pivot_longer()` because the rows and columns in the output are primarily determined by the values of variables, not their locations.

To begin the process `pivot_wider()` needs to first figure out what will go in the rows and columns.
Finding the column names is easy: it's just the unique values of `name`.

```{r}
df |> 
  distinct(name) |> 
  pull()
```

By default, the rows in the output are formed by all the variables that aren't going into the names or values.
These are called the `id_cols`.
Here there is only one column, but in general there can be any number.

```{r}
df |> 
  select(-name, -value) |> 
  distinct()
```

`pivot_wider()` then combines these results to generate an empty data frame:

```{r}
df |> 
  select(-name, -value) |> 
  distinct() |> 
  mutate(x = NA, y = NA, z = NA)
```

It then fills in all the missing values using the data in the input.
In this case, not every cell in the output has corresponding value in the input as there's no entry for id "B" and name "z", so that cell remains missing.
We'll come back to this idea that `pivot_wider()` can "make" missing values in @sec-missing-values.

You might also wonder what happens if there are multiple rows in the input that correspond to one cell in the output.
The example below has two rows that correspond to id "A" and name "x":

```{r}
df <- tribble(
  ~id, ~name, ~value,
  "A", "x", 1,
  "A", "x", 2,
  "A", "y", 3,
  "B", "x", 4, 
  "B", "y", 5, 
)
```

If we attempt to pivot this we get an output that contains list-columns, which you'll learn more about in @sec-rectangling:

```{r}
df |> pivot_wider(
  names_from = name,
  values_from = value
)
```

Since you don't know how to work with this sort of data yet, you'll want to follow the hint in the warning to figure out where the problem is:

```{r}
df |> 
  group_by(id, name) |> 
  summarize(n = n(), .groups = "drop") |> 
  filter(n > 1L) 
```

It's then up to you to figure out what's gone wrong with your data and either repair the underlying damage or use your grouping and summarizing skills to ensure that each combination of row and column values only has a single row.

## Summary

In this chapter you learned about tidy data: data that has variables in columns and observations in rows.
Tidy data makes working in the tidyverse easier, because it's a consistent structure understood by most functions: the main challenge is data from whatever structure you receive it in to a tidy format.
To that end, you learned about `pivot_longer()` and `pivot_wider()` which allow you to tidy up many untidy datasets.
The examples we used here are just a selection of those from `vignette("pivot", package = "tidyr")`, so if you encounter a problem that this chapter doesn't help you with, that vignette is a good place to try next.

If you particularly enjoyed this chapter and want to learn more about the underlying theory, you can learn more about the history and theoretical underpinnings in the [Tidy Data](https://www.jstatsoft.org/article/view/v059i10) paper published in the Journal of Statistical Software.

Now that you're writing a substantial amount of R code, it's time to learn more about organizing your code into files and directories.
In the next chapter, you'll learn all about the advantages of scripts and projects, and some of the many tools that they provide to make your life easier.