---
title: "Wrangling & Summarising data"
output: 
  html_document:
    toc: TRUE
    toc_float: TRUE 
---

In this practice document, you will get to know our three best friends for summarising data: `group_by()`, `summarise()`, and `mutate()`.

# Data

We are using the Global Burden of Disease data - the same extract we used in the bar charts Demo in Week 2. Don't worry if you can't remember what it looked like exactly, you should run the lines here (Control+Enter as usual) and it will then appear in the Environment tab. You can then click on it and have a look.

```{r message=FALSE, warning=FALSE}
library(tidyverse)
library(knitr)
library(kableExtra)

mydata <- read_csv("global_burden_disease_long.csv")
```

We can check our data using the `summary()` function. 

```{r}
mydata %>% 
  summary()
```

# Basic functions for summarising data

You can always pick a column and ask R to give you the `sum()`, `mean()`, `min()`, `max()`, etc. for it:

```{r}
mydata$deaths_millions %>% 
  sum()

mydata$deaths_millions %>% 
  mean()
```

Another way of writing this code without using the `$` to index is to use `select()` or `pull()`. As we have mentioned before, there are often different ways to do the same thing in R! 

```{r}
mydata %>%
  select(deaths_millions) %>%
  sum()

mydata %>%
  select(deaths_millions) %>%
  mean()
```

You may have noticed that the code above to calculate the mean provided a warning message, and the result was `NA`. This is not because the cannot be calculated, but rather a more technical issue - the `mean()` function expects a numeric vector, but when using `select()` the resulting data is in the form of a tibble. Instead, if you use the `pull()` function from `dplyr`, which is similar to `$` in base R syntax, the code will run as expected: 

```{r}
mydata %>%
  pull(deaths_millions) %>%
  mean()
```

Another way of doing this would be to use the `summarise()` function, described in more detail below.  

```{r}
mydata %>% 
  summarise(mean = mean(deaths_millions))
```


But if you want to get the total number of deaths for each `year` (or `cause`, or `sex`, whichever grouping variables you have in your dataset) you can use `group_by()` and `summarise()` that make subgroup analysis very convenient and efficient.


# Subgroup analysis: `group_by()` and `summarise()`

The `group_by()` function tells R that you are about to perform subgroup analysis on your data. It retains information about your groupings and calculations are applied on each group separately. To go back to summarising the whole dataset again use `ungroup()`. Note that `summarise()` is different to the `summary()` function we used above.

With `summarise()`, we can calculate the total number of deaths per year:

```{r}
summary_data1 <- mydata %>% 
  group_by(year) %>% 
  summarise(total_per_year = sum(deaths_millions))

summary_data1

summary_data2 <- mydata %>% 
  group_by(year, cause) %>% 
  summarise(total_per_cause = sum(deaths_millions))

summary_data2
```

* `summary_data1` includes the total number of deaths per year.
* `summary_data2` includes the number of deaths per cause per year.

## Exercise 1

Compare the number of rows (observations) and number of columns (variables) of:

* `mydata`, 
* `summary_data1`, and 
* `summary_data2` (in the Environment tab).

Notice that:
* `summary_data2` has exactly 3 times as many rows as `summary_data1`. Why?
* `mydata` has 5 variables, whereas the summarised tibbles have 2 and 3. Which variables got dropped? Why?


## Exercise 2

For each cause, calculate its percentage to total deaths in each year.

Hint: Use `full_join()` on `summary_data1` and `summary_data2`.

```{r}
# Exercise 2 - your R code:

```


# `mutate()`

Mutate works similarly to `summarise()` (as in it respects groupings set with `group_by()`), but it adds a new column into the original data. `summarise()`, on the other hand, condenses the data into a minimal table that only includes the variables specifically asked for.

## Exercise 3

Investigate these examples to learn how `summarise()` and `mutate()` differ.

```{r}
summarise_example <- mydata %>% 
	summarise(total_deaths = sum(deaths_millions)) 

summarise_example

mutate_example <- mydata %>% 
	mutate(total_deaths = sum(deaths_millions))

mutate_example
```

You should see that `mutate()` adds the same number total number (309.4174) to every line in the tibble. 

## Exercise 4 (optional)

Based on what we just observed on how `mutate()` adds a value to each row, can you think of a way to redo **Exercise 2** without using a join? 

Hint: instead of creating `summary_data1` (total deaths per year) as a separate tibble which we then join with `summary_data2` (total deaths for all causes per year), we can use `mutate()` to add total death per year to each row.

```{r}
# Exercise 4 - your R code:

```


# Wide vs long: `pivot_wider()` and `pivot_longer()`

## Wide format
Although having data in the long format is very convenient for R, for publication tables, it makes sense to spread some of the values out into columns (meaning wide format):

```{r}
alldata <- full_join(summary_data1, summary_data2)

alldata %>%
  mutate(percentage = 100*total_per_cause/total_per_year) %>% 
	mutate(percentage = paste0(round(percentage, 2), "%")) %>% # round and add % label
	select(year, cause, percentage) %>% #only select variables that you want in table
	pivot_wider(names_from = cause, values_from = percentage)
```

The next step in creating a table suitable for publication is formatting, where the `kable()` function comes to save the day! We can take out wrangling above and pipe it to `kable()` for nicer formatting. 

```{r}
alldata %>%
  mutate(percentage = 100*total_per_cause/total_per_year) %>% 
	mutate(percentage = paste0(round(percentage, 2), "%")) %>% # round and add % label
	select(year, cause, percentage) %>% #only select variables that you want in table
	pivot_wider(names_from = cause, values_from = percentage) %>%
  kable(align = "lccc")
```

## Exercise 5
Calculate the percentage of male and female deaths for each year. Then use `pivot_wider()` to put the values into columns. This makes it more human-readable. Finally, make a publication-ready table from your data wrangling. 

Hints:

* create `summary_data3` that includes a variable called `total_per_sex`
* join `summary_data1` and `summary_data3` into a new tibble
* calculate the percentage of `total_per_sex` to `total_per_year`
* round, add % labels (or use `percent()` by including `library(scales)`)
* then use `pivot_wider()`
* save your object as `result_wider`
* pass your saved object `result_wider` to `kable()` and (optional) add some further formatting to the table if you wish

Solution: 
```{r}
# Exercise 5 - your R code:

```

## Long format

The inverse of `pivot_wider()` is `pivot_longer()`. Be aware that making data longer is harder to get right at first. Don't be discouraged if `pivot_longer()` takes a few tries and tests. We find it harder to do as well! 

The code here is the solution to Exercise 5 (without the `kable()` table formatting) as we need something wider to be able to make it longer again:
```{r}
summary_data1 <- mydata %>% 
	group_by(year) %>% 
	summarise(total_per_year = sum(deaths_millions))

summary_data3 <- mydata %>% 
	group_by(year, sex) %>% 
	summarise(total_per_sex = sum(deaths_millions))

alldata <- full_join(summary_data1, summary_data3)

result_wider <- alldata %>% 
  mutate(percentage = round(100*total_per_sex/total_per_year, 0)) %>%
  mutate(percentage = paste0(percentage, "%")) %>% 
  select(year, sex, percentage) %>% 
  pivot_wider(names_from = sex, values_from = percentage)

result_wider
```

* After piping in the data, the next argument to `pivot_longer()` indicates which columns should be put together into the new longer variable. Since `result_wider` is a very small example tibble, we can just specify the column we don't want pulled together - `year`. In `pivot_longer()`, `-year` means we want to put everything but year into the new longer columns.

* We then add `names_to = "sex", values_to = "percentage"` to give the new columns names. These are quoted as they are new names we are assigning, feel free to experiment with changing "sex" or "percentage" to anything else here, even your name. It's just a new name you are choosing for the new columns. Whereas in the `pivot_wider()` example above, sex and percentage were not quoted, as they were referring to existing columns within the dataset, not new names we are assigning. Anytime we are assigning new names, we need to use quotation marks so R knows to use this new name exactly as wel have specified it. 

```{r}
# Exercise 6 - modify this:
result_wider %>% 
  pivot_longer(-year, names_to = "sex", values_to = "percentage")
```

## Exercise 6

The arguments `names_to` and `values_to` are not strictly necessary, if you omit them the columns will get assigned defaults - "name" and "value". Delete the arguments `names_to = "sex", values_to = "percentage"` from the code below, so the function becomes `pivot_longer(-year)` to see convince yourself the previous sentence holds true.

```{r}
result_wider %>% 
  pivot_longer(-year, names_to = "sex", values_to = "percentage")
```

### Selecting columns for pivot_longer, select helpers

If your tibble has many columns, you'll need to list the ones you do want to collect. For that, you will have to use a select helper. You can find the list of select helpers by typing `"select_helpers"` into the Help tab in RStudio and then clicking on the page listed as `"tidyselect::language Selection language"`. Alternatively, search online for examples. They can be extremely helpful and powerful.

How we usually get started with a new `pivot_longer()` line is that we figure out the select helper for it first by trialling it inside `select()`.

For example, the previous example would be this first:

```{r}
result_wider %>% 
  select(-year)
```

This shows us the columns that `pivot_longer()` will work on.

Let's figure out which select helper will get us the two columns: Male and Female. If we were just using `select()` and not intending to change it to `pivot_longer()`, we can of course just do:

```{r}
result_wider %>% 
  select(Female, Male)
```

But `pivot_longer()` will require the specification in a single argument, whereas that comma between them makes them two arguments. We can tie them into a single argument using `c()`:

```{r}
result_wider %>% 
  pivot_longer(c(Female, Male))
```

Or if you had many more than just 2 columns to be combined, you should use a select helper. They don't make a lot of sense with this very simple example, but have a look at the select helpers examples in the Help tab and see how these could translate to the data you are dealing with. For example, if the columns you want to combine all start with the same prefix, use `starts_with()`. If you are familiar with regular expressions, use `matches()`, etc.

One final select or pivot_longer() tip to show here is the use of the semicolon. This selects all columns `from:to`.

```{r}
# a new example tibble:
tibble(x1 = "one", x2 = "more", x3 = "cool", x4 = "example", 
       x5 = "this", x6 = "is")
```

Selecting columns from `x3` to `x6`:

```{r}
tibble(x1 = "one", x2 = "more", x3 = "cool", x4 = "example", 
       x5 = "this", x6 = "is") %>% 
  select(x3:x6)
```

# Sorting: `arrange()`

To reorder data ascendingly or descendingly, use `arrange()`:

```{r}
mydata %>% 
	group_by(year) %>% 
	summarise(total = sum(deaths_millions))  %>%
	arrange(desc(-year)) # reorder after summarise()
```


## Exercise 7

This exercise includes multiple steps, combining all of the above.

* Start with `mydata`.
* Calculate the total number of deaths in Developed and Developing countries. Hint: use `group_by(location)` and `summarise( Include new column name = sum() here)`.
* Calculate the total number of deaths in Developed and Developing countries and for men and women. Hint: this is as easy as adding `, sex` to `group_by()`.
* Filter for 1990.
* `pivot_wider()` using `location` column as `names_from` and and your new calculated column as `values_from.`
* Format your data wrangled table for publication 

```{r}
# Exercise 7 - your R code:

```

## Export object or table as a spreadsheet. 

To save as a csv file use `write_csv()`:

```{r, warning=FALSE}
write_csv(result_wider, "gbd_genders_summarised.csv")
```

You can open a csv file with Excel and copy the table into Word or PowerPoint for presenting.



# Solutions

## Exercise 2 solution

```{r}
alldata <- full_join(summary_data1, summary_data2) %>% 
  mutate(percentage = 100*total_per_cause/total_per_year)
```

## Exercise 4 solution

```{r}
alldata2 <- mydata %>% 
	group_by(year, cause) %>% 
	summarise(total_per_cause = sum(deaths_millions)) %>% 
	group_by(year) %>% 
	mutate(total_per_year = sum(total_per_cause)) %>% 
	mutate(percentage = 100*total_per_cause/total_per_year)

alldata2
```

## Exercise 5 solution

```{r}
summary_data1 <- mydata %>% 
	group_by(year) %>% 
	summarise(total_per_year = sum(deaths_millions))

summary_data3 <- mydata %>% 
	group_by(year, sex) %>% 
	summarise(total_per_sex = sum(deaths_millions))

alldata <- full_join(summary_data1, summary_data3)

result_wider <- alldata %>% 
  mutate(percentage = round(100*total_per_sex/total_per_year, 0)) %>%
  mutate(percentage = paste0(percentage, "%")) %>% 
  select(year, sex, percentage) %>% 
  pivot_wider(names_from = sex, values_from = percentage)

result_wider %>% 
  kable(col.names = c("Year", "Female", "Male")) %>% 
  #optionally, rename the column names so they are all in capital letters and add stripes to the different rows 
  kable_styling(bootstrap_options = "striped")
```

## Exercise 6 solution

```{r}
result_wider %>% 
  pivot_longer(-year)
```


## Exercise 7 solution

```{r}
mydata %>% 
  filter(year == 1990) %>% 
  group_by(location, sex) %>% 
  summarise(total_deaths = sum(deaths_millions)) %>% 
  pivot_wider(names_from = location, values_from = total_deaths) %>%
  kable(col.names = c("Sex", "Developed", "Developing"), digits = 2) 
```
