cleanup.Rmd

# Cleaning Up Data {#cleanup}

```{r setup, include=FALSE}
source("etc/common.R")
```

## Questions
```{r questions, child="questions/cleanup.md"}
```

## Learning Objectives
```{r objectives, child="objectives/cleanup.md"}
```

Data is not born tidy.
Here is a sample of data from the original data set `raw/infant_hiv.csv`,
where `...` shows values elided to make the segment readable:

```
"Early Infant Diagnosis: Percentage of infants born to women living with HIV...",,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,2009,,,2010,,,2011,,,2012,,,2013,,,2014,,,2015,,,2016,,,2017,,,
ISO3,Countries,Estimate,hi,lo,Estimate,hi,lo,Estimate,hi,lo,Estimate,hi,lo,...
AFG,Afghanistan,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,
ALB,Albania,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,
DZA,Algeria,-,-,-,-,-,-,38%,42%,35%,23%,25%,21%,55%,60%,50%,27%,30%,25%,23%,25%,21%,33%,37%,31%,61%,68%,57%,
AGO,Angola,-,-,-,3%,4%,2%,5%,7%,4%,6%,8%,5%,15%,20%,12%,10%,14%,8%,6%,8%,5%,1%,2%,1%,1%,2%,1%,
... many more rows ...
YEM,Yemen,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,
ZMB,Zambia,59%,70%,53%,27%,32%,24%,70%,84%,63%,74%,88%,67%,64%,76%,57%,91%,>95%,81%,43%,52%,39%,43%,51%,39%,46%,54%,41%,
ZWE,Zimbabwe,-,-,-,12%,15%,10%,23%,28%,20%,38%,47%,33%,57%,70%,49%,54%,67%,47%,59%,73%,51%,71%,88%,62%,65%,81%,57%,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,2009,,,2010,,,2011,,,2012,,,2013,,,2014,,,2015,,,2016,,,2017,,,
,,Estimate,hi,lo,Estimate,hi,lo,Estimate,hi,lo,Estimate,hi,lo,...
Region,East Asia and the Pacific,25%,30%,22%,35%,42%,29%,30%,37%,26%,32%,38%,27%,28%,34%,24%,26%,31%,22%,31%,37%,27%,30%,35%,25%,28%,33%,24%,
,Eastern and Southern Africa,23%,29%,20%,44%,57%,37%,48%,62%,40%,54%,69%,46%,51%,65%,43%,62%,80%,53%,62%,79%,52%,54%,68%,45%,62%,80%,53%,
,Eastern Europe and Central Asia,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,
... several more rows ...
,Sub-Saharan Africa,16%,22%,13%,34%,46%,28%,37%,50%,30%,43%,57%,35%,41%,54%,33%,50%,66%,41%,50%,66%,41%,45%,60%,37%,52%,69%,42%,
,Global,17%,23%,13%,33%,45%,27%,36%,49%,29%,41%,55%,34%,40%,53%,32%,48%,64%,39%,49%,64%,40%,44%,59%,36%,51%,67%,41%,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
Indicator definition: Percentage of infants born to women living with HIV... ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
Note: Data are not available if country did not submit data...,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
Data source: Global AIDS Monitoring 2018 and UNAIDS 2018 estimates,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
"For more information on this indicator, please visit the guidance:...",,,,,,,,,,,,,,,,,,,,,,,,,,,,,
"For more information on the data, visit data.unicef.org",,,,,,,,,,,,,,,,,,,,,,,,,,,,,
```

This is a mess---no, more than that, it is an affront to decency.
There are comments mixed with data,
values' actual indices have to be synthesized by combining column headings from two rows
(two thirds of which have to be carried forward from previous columns),
and so on.
We want to create the tidy data found in `results/infant_hiv.csv`:

```
country,year,estimate,hi,lo
AFG,2009,NA,NA,NA
AFG,2010,NA,NA,NA
AFG,2011,NA,NA,NA
AFG,2012,NA,NA,NA
...
ZWE,2016,0.71,0.88,0.62
ZWE,2017,0.65,0.81,0.57
```

To bring this data to a state of grace will take some trial and effort,
which we shall do in stages.

## How do I inspect the raw data?

We will begin by reading the data into a tibble:

```{r read-and-head}
raw <- read_csv("data/infant_hiv.csv")
head(raw)
```

All right:
R isn't able to infer column names,
so it uses the entire first comment string as a very long column name
and then makes up names for the other columns.
Looking at the file,
the second row has years (spaced at three-column intervals)
and the column after that has the [ISO3 country code](glossary.html#iso3-country-code),
the country's name,
and then "Estimate", "hi", and "lo" repeated for every year.
We are going to have to combine what's in the second and third rows,
so we're going to have to do some work no matter which we skip or keep.
Since we want the ISO3 code and the country name,
let's skip the first two rows.

```{r read-skip-and-head}
raw <- read_csv("data/infant_hiv.csv", skip = 2)
head(raw)
```

That's a bit of an improvement,
but why are all the columns `character` instead of numbers?
This happens because:

1.  our CSV file uses `-` (a single dash) to show missing data, and
2.  all of our numbers end with `%`, which means those values actually *are* character strings.

We will tackle the first problem by setting `na = c("-")` in our `read_csv` call
(since we should never do ourselves what a library function will do for us):

```{r read-skip-na-and-head}
raw <- read_csv("data/infant_hiv.csv", skip = 2, na = c("-"))
head(raw)
```

That's progress.
We now need to strip the percentage signs and convert what's left to numeric values.
To simplify our lives,
let's get the `ISO3` and `Countries` columns out of the way.
We will save the ISO3 values for later use
(and because it will illustrate a point about data hygiene that we want to make later,
but which we don't want to reveal just yet).

```{r read-and-filter-mistake, error=TRUE}
raw <- read_csv("data/infant_hiv.csv", skip = 2, na = c("-"))
countries <- raw$ISO3
body <- raw %>%
  filter(-ISO3, -Countries)
```

In the Hollywood version of this lesson,
we would sigh heavily at this point as we realize that we should have called `select`, not `filter`.
Once we make that change,
we can move forward once again:

```{r read-and-select}
raw <- read_csv("data/infant_hiv.csv", skip = 2, na = c("-"))
countries <- raw$ISO3
body <- raw %>%
  select(-ISO3, -Countries)
head(body)
```

But wait.
Weren't there some aggregate lines of data at the end of our input?
What happened to them?

```{r tail-raw-data}
tail(countries, n = 25)
```

Once again the actor playing our part on screen sighs heavily.
How are we to trim this?
Since there is only one file,
we can manually count the number of rows we are interested in
(or rather, open the file with an editor or spreadsheet program, scroll down, and check the line number),
and then slice there.
This is a very bad idea if we're planning to use this script on other files---we should
instead look for the first blank line or the entry for Zimbabwe or something like that---but
let's revisit the problem once we have our data in place.

```{r read-slice-and-tail}
num_rows <- 192
raw <- read_csv("data/infant_hiv.csv", skip = 2, na = c("-"))
sliced <- slice(raw, 1:num_rows)
countries <- sliced$ISO3
tail(countries, n = 5)
```

Notice that we're counting rows *not including* the two we're skipping,
which means that the 192 in the call to `slice` above corresponds to row 195 of our original data:
195, not 194, because we're using the first row of unskipped data as headers and yes,
you are in fact making that faint whimpering sound you now hear.
You will hear it often when dealing with real-world data...

And notice also that we are slicing, *then* extracting the column containing the countries.
We did, in a temporary version of this script,
peel off the countries, slice those, and then wonder why our main data table still had unwanted data at the end.
Vigilance, my friends---vigilance shall be our watchword,
and in light of that,
we shall first test our plan for converting our strings to numbers:

```{r demonstrate-str-replace}
fixture <- c(NA, "1%", "10%", "100%")
result <- as.numeric(str_replace(fixture, "%", "")) / 100
result
```

And as a further check:

```{r check-numeric-result}
is.numeric(result)
```

The function `is.numeric` is `TRUE` for both `NA` and actual numbers,
so it is doing the right thing here,
and so are we.
Our updated conversion script is now:

```{r str-replace-fail}
raw <- read_csv("data/infant_hiv.csv", skip = 2, na = c("-"))
sliced <- slice(raw, 1:192)
countries <- sliced$ISO3
body <- raw %>%
  select(-ISO3, -Countries)
numbers <- as.numeric(str_replace(body, "%", "")) / 100
is.numeric(numbers)
```

Oh dear.
It appears that some function that `str_replace` is calling is expecting an atomic vector,
not a tibble.
It worked for our test case because that was a character vector,
but tibbles have more structure than that.

The second complaint is that `NA`s were introduced,
which is troubling because we didn't get a complaint when we had actual `NA`s in our data.
However,
`is.numeric` tells us that all of our results are numbers.
Let's take a closer look:

```{r check-body-is-tibble}
is_tibble(body)
```
```{r check-numbers-is-tibble}
is_tibble(numbers)
```

Perdition.
After browsing the data,
we realize that some entries are `">95%"`,
i.e.,
there is a greater-than sign as well as a percentage in the text.
We will need to regularize those before we do any conversions.

Before that,
however,
let's see if we can get rid of the percent signs.
The obvious way is is to use `str_replace(body, "%", "")`,
but that doesn't work:
`str_replace` works on vectors,
but a tibble is a list of vectors.
Instead,
we can use a [higher-order function](glossary.html#higher-order-function) called `map`
to apply the function `str_replace` to each column in turn to get rid of the percent signs:

```{r read-and-trim-with-map, output.lines=40}
raw <- read_csv("data/infant_hiv.csv", skip = 2, na = c("-"))
sliced <- slice(raw, 1:192)
countries <- sliced$ISO3
body <- raw %>%
  select(-ISO3, -Countries)
trimmed <- map(body, str_replace, pattern = "%", replacement = "")
head(trimmed)
```

Perdition once again.
The problem now is that `map` produces a raw list as output.
The function we want is `map_dfr`,
which maps a function across the rows of a tibble and returns a tibble as a result.
(There is a corresponding function `map_dfc` that maps a function across columns.)

```{r read-and-trim-with-map-dfr}
raw <- read_csv("data/infant_hiv.csv", skip = 2, na = c("-"))
sliced <- slice(raw, 1:192)
countries <- sliced$ISO3
body <- raw %>%
  select(-ISO3, -Countries)
trimmed <- map_dfr(body, str_replace, pattern = "%", replacement = "")
head(trimmed)
```

Now to tackle those `">95%"` values.
It turns out that `str_replace` uses [regular expressions](glossary.html#regular-expression),
not just direct string matches,
so we can get rid of the `>` at the same time as we get rid of the `%`.
We will check by looking at the first `Estimate` column,
which earlier inspection informed us had at least one `">95%"` in it:

```{r use-regexp, output.lines=NA}
raw <- read_csv("data/infant_hiv.csv", skip = 2, na = c("-"))
sliced <- slice(raw, 1:192)
countries <- sliced$ISO3
body <- raw %>%
  select(-ISO3, -Countries)
trimmed <- map_dfr(body, str_replace, pattern = ">?(\\d+)%", replacement = "\\1")
trimmed$Estimate
```

Excellent.
We can now use `map_dfr` to convert the columns to numeric percentages
using an [anonymous function](glossary.html#anonymous-function) that we define inside the `map_dfr` call itself:

```{r convert-to-percent, output.lines=NA}
raw <- read_csv("data/infant_hiv.csv", skip = 2, na = c("-"))
sliced <- slice(raw, 1:192)
countries <- sliced$ISO3
body <- raw %>%
  select(-ISO3, -Countries)
trimmed <- map_dfr(body, str_replace, pattern = ">?(\\d+)%", replacement = "\\1")
percents <- map_dfr(trimmed, function(col) as.numeric(col) / 100)
head(percents)
```

27 warnings is rather a lot,
so let's see what running `warnings()` produces right after the `as.numeric` call:

```{r look-at-warnings, output.lines=20}
raw <- read_csv("data/infant_hiv.csv", skip = 2, na = c("-"))
sliced <- slice(raw, 1:192)
countries <- sliced$ISO3
body <- raw %>%
  select(-ISO3, -Countries)
trimmed <- map_dfr(body, str_replace, pattern = ">?(\\d+)%", replacement = "\\1")
percents <- map_dfr(trimmed, function(col) as.numeric(col) / 100)
warnings()
```

Something is still not right.
The first `Estimates` column looks all right,
so let's have a look at the second column:

```{r look-at-trimmed-hi}
trimmed$hi
```

Where are those empty strings coming from?
Let's backtrack by examining the `hi` column of each of our intermediate variables interactively in the console...

...and there's our bug.
We are creating a variable called `sliced` that has only the rows we care about,
but then using the full table in `raw` to create `body`.
It's a simple mistake,
and one that could easily have slipped by us.
Here is our revised script:

```{r fixing-trim-bug}
raw <- read_csv("data/infant_hiv.csv", skip = 2, na = c("-"))
sliced <- slice(raw, 1:192)
countries <- sliced$ISO3
body <- sliced %>%
  select(-ISO3, -Countries)
trimmed <- map_dfr(body, str_replace, pattern = ">?(\\d+)%", replacement = "\\1")
percents <- map_dfr(trimmed, function(col) as.numeric(col) / 100)
```

and here are the checks on the head:

```{r head-of-percents}
head(percents)
```

and tail:

```{r tail-of-percents}
tail(percents)
```

Comparing this to the raw data file convinces us that yes,
we are now converting the percentages properly,
which means we are halfway home.

## How do I tidy the data?

We now have numeric values in `percents` and corresponding ISO3 codes in `countries`.
What we do *not* have is tidy data:
countries are not associated with records,
years are not recorded at all,
and the column headers for `percents` have mostly been manufactured for us by R.
We must now sew these parts together like Dr. Frankenstein's trusty assistant Igor
(who, like so many lab assistants, did most of the actual work but was given only crumbs of credit).

We could write a loop to grab three columns at a time and relabel them,
but a more concise solution makes use of two functions called `gather` and `separate`.
`gather` takes multiple columns and collapses them into key-value pairs.
To show how it works,
let's create a small tibble by hand using the function `tribble`.
The first few arguments use `~` as a [prefix operator](glossary.html#prefix-operator) to define columns names,
and all of the other values are then put into a tibble with those columns:

```{r demonstrate-tribble}
small <- tribble(
  ~ISO, ~est, ~hi, ~lo,
  'ABC', 0.25, 0.3, 0.2,
  'DEF', 0.55, 0.6, 0.5
)
small
```

and then gather the three columns `est`, `hi`, and `lo`:

```{r demonstrate-gather}
small %>%
  gather(key = "kind", value = "reported", est, hi, lo)
```

The `key` and `value` parameters tell `gather` to create new columns with the specified names
(in this case, `kind` and `reported`).
The first of these columns (`kind` in our case) is filled by repeating the column headings from the original tibble;
the second column (`reported` in our case) is then filled with the original tibble's values.

`separate` splits one column into two.
For example, if we have the year and the heading type in a single column:

```{r fixture-for-separate}
single <- tribble(
  ~combined, ~value,
  '2009-est', 123,
  '2009-hi',  456,
  '2009-lo',  789,
  '2010-est', 987,
  '2010-hi',  654,
  '2010-lo',  321
)
single
```

we can get the year and the heading into separate columns by separating on the `-` character:

```{r demonstrate-separate}
single %>%
  separate(combined, sep = "-", c("year", "kind"))
```

Our strategy is therefore going to be:

1.  Replace the double column headers in the existing data with a single header that combines the year with the kind.
2.  Gather the data so that the year-kind values are in a single column.
3.  Split that column.

We've seen the tools we need for the second and third step;
the first involves a little bit of list manipulation.
Let's start by repeating `"est"`, `"hi"`, and `"lo"` as many times as we need them:

```{r repeating-kind}
num_years <- 1 + 2017 - 2009
kinds <- rep(c("est", "hi", "lo"), num_years)
kinds
```

As you can probably guess from its name,
`rep` repeats things a specified number of times,
and as noted previously,
a vector of vectors is flattened into a single vector.

What about the years?
We want to wind up with:

```{r desired-output, eval=FALSE}
c("2009", "2009" "2009", "2010", "2010", "2010", ...)
```

i.e., with each year repeated three times.
`rep` won't do this,
but we can get there with `map`:

```{r repeating-years-nested}
years <- map(2009:2017, rep, 3)
years
```

That's almost right,
but `map` hasn't flattened the list for us.
Luckily,
we can use `unlist` to do that:

```{r repeating-years-flattened}
years <- map(2009:2017, rep, 3) %>% unlist()
years
```

We can now combine the years and kinds by pasting the two vectors together with `"-"` as a separator:

```{r combined-headers}
headers <- paste(years, kinds, sep = "-")
headers
```

Let's use this to relabel the columns of `percents`
(which holds our data without the ISO country codes):

```{r relabel-columns-extra}
names(percents) <- headers
percents
```

Uh oh:
the warning message tells us that `percents` has the wrong number of columns.
Inspecting the tibble in the console,
we see that the last column is full of NAs,
which we can prove like this:

```{r check-last-column-is-all-na}
all(is.na(percents[,ncol(percents)]))
```

Let's relabel our data again and then drop the empty column.
(There are other ways to do this, but I find steps easier to read after the fact this way.)

```{r relabel-and-drop-empty}
headers <- c(headers, "empty")
names(percents) <- headers
percents <- select(percents, -empty)
percents
```

It's time to put the country codes back on the table,
move the year and kind from column headers to a column with `gather`,
and then split that column with `separate`:

```{r create-final-table}
final <- percents %>%
  mutate(country = countries) %>%
  gather(key = "year_kind", value = "value", -country) %>%
  separate(year_kind, c("year", "kind"))
final
```

Here's everything in one function:

```{r all-in-one}
clean_infant_hiv <- function(filename, num_rows) {
  # Read raw data.
  raw <- read_csv(filename, skip = 2, na = c("-")) %>%
    slice(1:num_rows)
  
  # Save the country names to reattach later.
  countries <- raw$ISO3
  
  # Convert data values to percentages.
  percents <- raw %>%
    select(-ISO3, -Countries) %>%
    slice(1:num_rows) %>%
    map_dfr(str_replace, pattern = ">?(\\d+)%", replacement = "\\1") %>%
    map_dfr(function(col) as.numeric(col) / 100)

  # Change the headers on the percentages.
  num_years <- 1 + 2017 - 2009
  kinds <- rep(c("est", "hi", "lo"), num_years)
  years <- map(2009:2017, rep, 3) %>% unlist()
  headers <- c(paste(years, kinds, sep = "-"), "empty")
  names(percents) <- headers
  
  # Stitch everything back together.
  percents %>%
    mutate(country = countries) %>%
    gather(key = "year_kind", value = "value", -country) %>%
    separate(year_kind, c("year", "kind"))
}
clean_infant_hiv("data/infant_hiv.csv", 192)
```

We're done,
and we have learned a lot of R,
but what we have also learned is that we make mistakes,
and that those mistakes can easily slip past us.
If people are going to use our cleaned-up data in their analyses,
we need a better way to develop and check our scripts.

## Key Points
```{r keypoints, child="keypoints/cleanup.md"}
```

```{r links, child="etc/links.md"}
```