12-tidy-data.Rmd

```{r setup12, include=FALSE, warning=FALSE, message=FALSE}
knitr::opts_chunk$set(echo = TRUE, cache = TRUE)

library(ggplot2)
library(dplyr)
library(tidyr)
```

# Ch. 12: Tidy data

```{block2, type='rmdimportant'}
**Key questions:**  
  
* 12.3.3. #4
* 12.4.3. #1
* 12.6.1 #4
```

```{block2, type='rmdtip'}
**Functions and notes:**
```

* `spread`: pivot, e.g. `spread(iris, Species)`
* `gather`: unpivot, e.g. `gather(mpg, drv, class, key = "drive_or_class", value = "value")`
* `separate`: one column into many, e.g. `separate(table3, rate, into = c("cases", "population"), sep = "/")`
    + default uses non-alphanumeric character as `sep`, can also use number to separate by width
* `extract` similar to separate but specify what to pull-out rather than what to split by
* `unite` inverse of separate  

```{r}
# example distinguishing separate, extract, unite
tibble(x = c("a,b,c", "d,e,f", "h,i,j", "k,l,m")) %>% 
  tidyr::separate(x, c("one", "two", "three"), sep = ",", remove = FALSE) %>% 
  tidyr::unite(one, two, three, col = "x2", sep = ",", remove = FALSE) %>% 
  tidyr::extract(x2, into = c("a", "b", "c"), regex = "([a-z]+),([a-z]+),([a-z]+)", remove = FALSE)
```

* `complete()` takes a set of columns, and finds all unique combinations. It then ensures the original dataset contains all those values, filling in explicit NAs where necessary.
* `fill()` takes a set of columns where you want missing values to be replaced by the most recent non-missing value (sometimes called last observation carried forward).
```{r}
# examples of complete and fill
treatment <- tribble(
  ~ person,           ~ treatment, ~response,
  "Derrick Whitmore", 1,           7,
  NA,                 2,           10,
  NA,                 3,           9,
  "Katherine Burke",  1,           4
)

treatment %>% 
  fill(person)

treatment %>% 
  fill(person) %>% 
  complete(person, treatment)

```


## 12.2: Tidy data

### 12.2.1.

*1. Using prose, describe how the variables and observations are organised in each of the sample tables.*  

* `table1`: each country-year is a row with cases and pop as values  
* `table2`: each country-year-type is a row  
* `table3`: each country-year is a row with rate containing values for both `cases` and `population`  
* `table4a` and `table4b`: a represents cases, b population, each row is a country and then column are the year for the value  

*2. Compute the `rate` for `table2`, and `table4a` + `table4b`. 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.  
e. Which representation is easiest to work with? Which is hardest? Why?  

with `table2`:
```{r}
table2 %>% 
  spread(type, count) %>% 
  mutate(rate = 1000 * cases / population) %>% 
  arrange(country, year)
```

with `table4` 'a' and 'b'`:
```{r}
table4a %>% 
  gather(2,3, key = "year", value = "cases") %>% 
  inner_join(table4b %>% 
               gather(c(2,3), key = "year", value = "population"),
             by = c("country", "year")) %>% 
  mutate(rate = 1000 * cases / population)
```

* between these, `table2` was easier, though `table1` would have been easiest -- is fewer steps to get 1 row = 1 observation (if we define an observation as a country in a year with certain attributes)

*3. Recreate the plot showing change in cases over time using `table2` instead of `table1`. What do you need to do first?*  
```{r}
table2 %>% 
  spread(type, count) %>% 
  ggplot(aes(x = year, y = cases, group = country))+
  geom_line(colour = "grey50")+
  geom_point(aes(colour = country))

```

* first had to spread data

## 12.3: Spreading and gathering

### 12.3.3.

*1. Why are `gather()` and `spread()` not perfectly symmetrical?*  
Carefully consider the following example:  

```{r}
stocks <- tibble(
  year   = c(2015, 2015, 2016, 2016),
  half  = c(   1,    2,     1,    2),
  return = c(1.88, 0.59, 0.92, 0.17)
)

stocks %>% 
  spread(year, return) %>% 
  gather("year", "return", `2015`:`2016`)

```
(Hint: look at the variable types and think about column names.)  
  
* are not perfectly symmetrical, because type for key = changes to character when using `gather` -- column type information is not transferred.  
* position of columns change as well


* Both spread() and gather() have a convert argument. What does it do?*  
  
Use this to automatically change `key` column type, otherwise will default in `gather` for example to become a character type.

*2. Why does this code fail?*  

```{r, error = TRUE}
table4a %>% 
  gather(1999, 2000, key = "year", value = "cases")

```

Need backticks on year column names

```{r, error = TRUE}
table4a %>% 
  gather(`1999`, `2000`, key = "year", value = "cases")

```

*3. Why does spreading this tibble fail? How could you add a new column to fix the problem?*  

```{r, error = TRUE}

people <- tribble(
  ~name,             ~key,    ~value,
  #-----------------|--------|------
  "Phillip Woods",   "age",       45,
  "Phillip Woods",   "height",   186,
  "Phillip Woods",   "age",       50,
  "Jessica Cordero", "age",       37,
  "Jessica Cordero", "height",   156
)

people %>% 
  spread(key = "key", value = "value")
```

Fails because you have more than one age for philip woods, could add a unique ID column and it will work.
```{r}
people %>% 
  mutate(id = 1:n()) %>% 
  spread(key = "key", value = "value")

```

*4. Tidy the simple tibble below. Do you need to spread or gather it? What are the variables?*  

```{r}
preg <- tribble(
  ~pregnant, ~male, ~female,
  "yes",     NA,    10,
  "no",      20,    12
)
```

Need to gather `gender`

```{r}
preg %>% 
  gather(male, female, key="gender", value="Number")

```

## 12.4: Separating and uniting

### 12.4.3.

*1. What do the `extra` and `fill` arguments do in `separate()`? Experiment with the various options for the following two toy datasets.*  

```{r}
tibble(x = c("a,b,c", "d,e,f,g", "h,i,j")) %>% 
  separate(x, c("one", "two", "three"))

tibble(x = c("a,b,c", "d,e", "f,g,i")) %>% 
  separate(x, c("one", "two", "three"))

```

`fill` determines what to do when there are too few arguments, default is to fill right arguments with `NA` can change this though.  
```{r}
tribble(~a,~b,
        "so it goes","hello,you,are") %>% 
  separate(b, into=c("e","f","g", "h"), sep=",", fill = "left")
```

`extra` determines what to do when you have more splits than you do `into` spaces.  Default is to drop extra  
Can change to limit num of splits to length of `into` with `extra = "merge"`

```{r}
tribble(~a,~b,
        "so it goes","hello,you,are") %>% 
  separate(b, into = c("e", "f"), sep = ",", extra = "merge")
```


*2. Both `unite()` and `separate()` have a `remove` argument. What does it do? Why would you set it to `FALSE`?*  

`remove = FALSE` allows you to specify to keep the input column(s)
```{r}
tibble(x = c("a,b,c", "d,e,f", "h,i,j", "k,l,m")) %>% 
  separate(x, c("one", "two", "three"), remove = FALSE) %>% 
  unite(one, two, three, col = "x2", sep = ",", remove = FALSE)
```

*3. Compare and contrast `separate()` and `extract()`. Why are there three variations of separation (by position, by separator, and with groups), but only one unite?*  
  
`extract()` is like `separate()` but provide what to capture rather than what to split by as in `regex` instead of `sep`.
```{r}
df <- data.frame(x = c("a-b", "a-d", "b-c", "d&e", NA), y = 1)

df %>% 
  extract(col = x, into = c("1st", "2nd"), regex = "([A-z]).([A-z])")

df %>% 
  separate(col = x, into = c("1st", "2nd"), sep = "[^A-z]")
```

Because there are many ways to split something up, but only one way to bring multiple things together...  

## 12.5: Missing values

### 12.5.1. 

*1. Compare and contrast the fill arguments to `spread()` and `complete()`.*  

Both create open cells by filling out those that are not currently in the dataset, `complete` though does it by adding rows of iterations not included, whereas `spread` does it by the process of spreading out fields and naturally generating values that did not have row values previously. The`fill` in each specifies what value should go into these created cells.

```{r}
treatment2 <- tribble(
  ~ person,           ~ treatment, ~response,
  "Derrick Whitmore", 1,           7,
  "Derrick Whitmore", 2,           10,
  "Derrick Whitmore", 3,           9,
  "Katherine Burke",  1,           4
)

treatment2 %>% 
  complete(person, treatment, fill = list(response = 0))

treatment2 %>% 
  spread(key = treatment, value = response, fill = 0)
```

*2. What does the `.direction` argument to `fill()` do?*  
  
Let's you fill either up or down. E.g. below is filling up example.
```{r}
treatment <- tribble(
  ~ person,           ~ treatment, ~response,
  "Derrick Whitmore", 1,           7,
  NA,                 2,           10,
  NA,                 3,           9,
  "Katherine Burke",  1,           4
)

treatment %>% 
  fill(person, .direction = "up")
```


## 12.6 Case Study

### 12.6.1. 

*1. In this case study I set `na.rm = TRUE` just to make it easier to check that we had the correct values. Is this reasonable? Think about how missing values are represented in this dataset. Are there implicit missing values? What's the difference between an `NA` and zero?*  
  
In this case it's reasonable, an `NA` perhaps means the metric wasn't recorded in that year, whereas 0 means it was recorded but there were 0 cases.  

Implicit missing values represented by say Afghanistan not having any reported cases for females.
  
*2. What happens if you neglect the `mutate()` step? (`mutate(key = stringr::str_replace(key, "newrel", "new_rel"))`)*  
  
You would have had one less column, so 'newtype' would have been on column, rather than these splitting.  

*3. I claimed that `iso2` and `iso3` were redundant with `country`. Confirm this claim.*  

```{r}
who %>% 
  select(1:3) %>% 
  distinct() %>% 
  count()

who %>% 
  select(1:3) %>% 
  distinct() %>% 
  unite(country, iso2, iso3, col = "country_combined") %>% 
  count()
```

Both of the above are the same length.

*4. For each country, year, and sex compute the total number of cases of TB. Make an informative visualisation of the data.*  

```{r}
who_present <- who %>%
  gather(code, value, new_sp_m014:newrel_f65, na.rm = TRUE) %>% 
  mutate(code = stringr::str_replace(code, "newrel", "new_rel")) %>%
  separate(code, c("new", "var", "sexage")) %>% 
  select(-new, -iso2, -iso3) %>% 
  separate(sexage, c("sex", "age"), sep = 1)
```

```{r}
who_present %>% 
  group_by(sex, year, country) %>% 
  summarise(mean=mean(value)) %>% 
  ggplot(aes(x=year, y=mean, colour=sex))+
  geom_point()+
  geom_jitter()

#ratio of female tb cases over time
who_present %>% 
  group_by(sex, year) %>% 
  summarise(meansex=sum(value)) %>%
  ungroup() %>% 
  group_by(year) %>% 
  mutate(tot=sum(meansex)) %>% 
  ungroup() %>% 
  mutate(ratio=meansex/tot) %>% 
  filter(sex=="f") %>% 
  ggplot(aes(x=year, y=ratio, colour=sex))+
  geom_line()

#countries with the most outbreaks
who_present %>%
  group_by(country, year) %>% 
  summarise(n=sum(value)) %>% 
  ungroup() %>% 
  group_by(country) %>% 
  mutate(total_country=sum(n)) %>% 
  filter(total_country>1000000) %>% 
  ggplot(aes(x=year,y=n,colour=country))+
  geom_line()

#countries with the most split by gender as well
who_present %>%
  group_by(country, sex, year) %>% 
  summarise(n=sum(value)) %>% 
  ungroup() %>% 
  group_by(country) %>% 
  mutate(total_country=sum(n)) %>% 
  filter(total_country>1000000) %>% 
  ggplot(aes(x=year,y=n,colour=sex))+
  geom_line()+
  facet_wrap(~country)

#take log and summarise
who_present %>%
  group_by(country, year) %>% 
  summarise(n=sum(value), logn=log(n)) %>% 
  ungroup() %>% 
  group_by(country) %>% 
  mutate(total_c=sum(n)) %>% 
  filter(total_c>1000000) %>% 
  ggplot(aes(x=year,y=logn, colour=country))+
  geom_line(show.legend=TRUE)

#average # of countries with more female TB cases
who_present %>%
  group_by(country, year, sex) %>% 
  summarise(n=sum(value), logn=log(n)) %>% 
  ungroup() %>% 
  group_by(country, year) %>% 
  mutate(total_c=sum(n)) %>%
  ungroup() %>% 
  mutate(perc_gender=n/total_c, femalemore=ifelse(perc_gender>.5,1,0)) %>% 
  filter(sex=="f") %>% 
  group_by(year) %>% 
  summarise(summaryfem=mean(femalemore,na.rm=TRUE )) %>% 
  ggplot(aes(x=year,y=summaryfem))+
  geom_line()
```