---
title: "Merging and tidying data"
author: "Julian Faraway"
date: "`r format(Sys.time(), '%d %B %Y')`"
output:
    html_document:
    keep_md: yes
---

```{r echo=FALSE}
library(knitr)
opts_chunk$set(comment=NA, warning=FALSE, fig.path="/tmp/Figs/")
```

# Merging

The `dplyr` package has facilities for merging two datasets based on a common
identifying variable.

We need to start by loading the `tidyverse` and the data on both
hospital admissions and air pollution:

```{r}
library(tidyverse)
library(lubridate)
library(scales)
load("uliral.rda")
load("ub8day.rda")
```

Take admissions data only from Ulaanbaatar and count the number of patients admitted on each day:

```{r}
uliral %>% filter(City == "Ulaanbaatar") %>% group_by(Admitted) %>% summarise(n=n()) -> ubadmit
ggplot(ubadmit, aes(Admitted,n)) + geom_col() 
```

For the sake of the example, we create two very small datasets:

```{r}
ubadmit %>% filter(Admitted %in% ymd(c("2013-12-17","2014-01-01","2014-01-02"))) -> admits
admits
```

```{r}
ub8day %>% filter(doy %in% ymd(c("2014-01-01","2014-01-02","2014-03-31"))) %>% select(SO2,doy) -> airs
airs
```

In practice, we will have much larger datasets but with these very small
datasets we can see how the functions work.

Now suppose we want to merge these two dataset based on the household. We
cannot just bind them together because they don't have the same set of households
in the same order. We have a number of options:

The inner join keeps only cases where the *key* value exists in each dataset:

```{r}
inner_join(airs,admits,by=c("doy" = "Admitted"))
```

The left join matches all the cases in the first dataset (the one on the left). If
the key does not exist in the second dataset, missing values are supplied:

```{r}
left_join(airs,admits,by=c("doy" = "Admitted"))
```

The full join matches cases that appear in either dataset. Missing values
are supplied where the information is missing:

```{r}
full_join(airs,admits,by=c("doy" = "Admitted"))
```

- there are other kinds of join: `right_join`, `semi_join` and `anti_join`

- More details can be found at<https://dplyr.tidyverse.org/reference/join.html>

## Exercises

1. Merge the full datasets `ubadmit` and `ub8day`. What form of join is best?

2. Make plots of how the admissions numbers relate to NO2, SO2 and PM10.

# Tidy data

So far we have used the `readr` and `dplyr` packages in the tidyverse. Now
it is time to introduce a third: `tidyr` which is useful for creating *tidy*
data. We will explain what this means shortly.

Let's focus on just the date of admission and illness category. We'll only use data from 2014 onwards.

```{r}
uliral %>% select(Admitted,Illness) %>% filter(Admitted >= "2014-01-01") %>% arrange(Admitted) -> datill
datill
```

We can count the frequency of each illness ICD category. We have sorted them by frequency:

```{r}
datill %>% group_by(Illness) %>% summarise(n=n()) %>% arrange(desc(n))
```

There are almost 200 different categories. This is inconvenient. We reduce
consideration to the highest level category:

```{r}
datill %>% mutate(shortill = str_sub(Illness,1,3)) -> datill
datill
```

The `str_sub` command is used to select the first three characters of the ICD code. The command
comes from the [stringr package](https://stringr.tidyverse.org) which is useful for manipulating strings.

Let's check the frequency again:

```{r}
datill %>% group_by(shortill) %>% summarise(n=n()) %>% arrange(desc(n))
```

There are still over 50 categories. Lets reduce that to the three most common categories
and group together everything else.

```{r}
datill %>% mutate(shortill = ifelse(shortill %in% c("J18","J20","J12"),shortill,"other")) -> datill
datill
```

The `%in%` operator checks where the components of the first argument are members of the set
specified in the second argument. If yes, the same code is applied. If no, the label is changed to `other`.

Check again

```{r}
datill %>% group_by(shortill) %>% summarise(n=n()) 
```

This is better. Now we count the number of patients within each ICD code admitted per day:

```{r}
datill %>% group_by(Admitted,shortill) %>% summarise(n=n()) -> sumdi
sumdi
```

Tidy data has the following three characteristics:

1. Each variable forms a column.

2. Each observation forms a row.

3. Each type of observational unit forms a table.

The data we have created has this format. Each observation is a number of patients for a particular
ICD code and day of admission. Sometimes this is called *long* format.

The alternative is *wide* format. We can create this with the `spread` command:

```{r}
sumdi %>% spread(shortill,n,fill=0) -> widedi
widedi
```

Now we see there are multiple observations per row. The `gather` command goes
from wide to long as illustrated here:

```{r}
widedi %>% gather(ICD,n,-Admitted) %>% arrange(Admitted)
```

We have restored the original long form (just for demonstration).

Both formats have advantages. For example, using the long format, we
can make a plot of the admissions over time for each ICD code:

```{r}
ggplot(sumdi, aes(Admitted, n, group=shortill, color=shortill)) + 
  geom_line() + 
  scale_x_date(labels=date_format("%B"))
```

We can use the wide format to compute the proportion of each category
by day:

```{r}
widedi %>% mutate(total = J12+J18+J20+other) %>% 
  mutate_at(vars(J12:other),~ ./total) -> propdi
propdi
```

We find it necessary to convert back to the long format to make the plot

```{r}
propdi %>% select(-total) %>% 
  gather(ICD,n,-Admitted) %>% 
  ggplot(aes(Admitted, n, group=ICD, color=ICD)) + 
  geom_line() +
  scale_x_date(breaks = 'month') +
  ylab("Proportion")
```

We see that the proportion within each category does not change much over time.

## Exercises

For these exercises, we construct a dataset using only Ulaanbaatar patients admitted
in 2014. They are recorded by the week number and day of the week. Each line is
a single patient.

```{r}
uliral %>% filter(City == "Ulaanbaatar" & Admitted >= "2014-01-01") %>% 
  select(Admitted) %>% 
  mutate(week=week(Admitted),wday = wday(Admitted,label = TRUE)) -> wad
wad
```

1. Construct a long format dataset where the observation is the number of
patients admitted on a particular day.

2. Construct a wide format dataset where each row is a week and there are columns
for each day of the week.

3. Make a plot of admissions where the horizontal axis is the week number and each
day of the week is represented by a different color.

4. Make a plot of admissions where the horizontal axis is the day and each
week is represented but not distinguished (i.e. plotted in black).














