---
title: 'Tidyverse II: Tidyr and Advanced Dplyr'
author: "Statistical Computing, 36-350"
date: "Monday November 4, 2019"
---

Last week: Pipes and dplyr
===

- The tidyverse is a collection of packages for common data science tasks
- Tidyverse functionality is greatly enhanced using pipes (`%>%` operator)
- Pipes allow you to string together commands to get a flow of results
- `dplyr` is a package for data wrangling, with several key verbs (functions)
- `slice()` and `filter()`: subset rows based on numbers or conditions
- `select()` and `pull()`: select columns or a single column as a vector
- `arrange()`: order rows by one or multiple columns
- `rename()` and `mutate()`: rename or create columns
- `mutate_at()`: apply a function to given columns

Recall: using pipes (`%>%`)
===

Tidyverse functions are at their best when composed together using the pipe (`%>%`) operator!

```{r, message=FALSE, warning =F}
library(tidyverse) 
mtcars %>% 
  filter((mpg >= 14 & disp >= 200) | (drat <= 3)) %>% 
  head(2)
```

**Shortcut**: use `ctrl + shift + m` in RStudio as a shortcut for typing `%>%`

<span style="color: blue">Part I</span>
===

<span style="color: blue">*Mastering the `tidyr` verbs*</span>

`tidyr` verbs 
===

Our `tidyr` journey starts of with learning the following verbs (functions):

- `pivot_longer()`: make "wide" data longer 
- `pivot_wider()`: make "long" data wider
- `separate()`: split a single column into multiple columns
- `unite()`: combine multiple columns into a single column

**Key takeaway**: as with `dplyr`, think of data frames as nouns and `tidyr` verbs as actions that you apply to manipulate them---especially natural when using pipes

`pivot_longer()`
===

Use `pivot_longer()` to make "wide" data longer:

```{r warning=F, message=F}
# devtools::install_github("rstudio/EDAWR")
library(EDAWR) # Load some nice data sets

EDAWR::cases %>% 
  head(3)

EDAWR::cases %>% 
  pivot_longer(names_to = "year", values_to = "n", cols = 2:4) %>% 
  head(5)
```

---

- Here we transposed columns 2:4 into a `year` column 
- We put the corresponding count values into a column called `n`
- Note `tidyr` did all the heavy lifting of the transposing work
- We just had to declaratively specify the output

---

```{r}
# Different approach to do the same thing
EDAWR::cases %>% 
  pivot_longer(names_to = "year", values_to = "n", -country) %>% 
  head(5)
# Could also do: 
# EDAWR::cases %>% 
#   pivot_longer(names_to = "year", values_to = "n", c(`2011`, `2012`, `2013`)) 
# or: 
# EDAWR::cases %>% 
#   pivot_longer(names_to = "year", values_to = "n",  `2011`:`2013`) 
```

`pivot_wider()`
===

Use `pivot_wider()` to make "long" data wider:

```{r}
EDAWR::pollution %>% 
  head(5)

EDAWR::pollution %>% 
  pivot_wider(names_from = "size", 
              values_from = "amount")
```

---

- Here we transposed to a wide format by size 
- We tabulated the corresponding amount for each size
- Note `tidyr` did all the heavy lifting again
- We just had to declaratively specify the output 
- Note that `pivot_wider()` and `pivot_longer()` are inverses

When could I used these?
===

- To visualize things like matrices in `ggplot` (`pivot_longer`)
- To make prettier / more readable "tables" (`pivot_wider`)

**Additionally,** if you find yourself getting stuck (with nuance situations) they are more complicated functions like `pivot_wider_spec`, etc. for these cases (see [Manual Specs]( https://tidyr.tidyverse.org/articles/pivot.html#manual-specs))

`separate()` 
===

Use `separate()` to split a single column into multiple ones:

```{r, cache=TRUE}
EDAWR::storms %>% 
  head(3)

storms2 <- EDAWR::storms %>% 
  separate(date, c("y", "m", "d")) # sep = "-"
```

`unite()`
===

Use `unite()` to combine multiple columns into a single column:

```{r}
storms2 %>%
  unite(date, y, m, d, sep = "-")
```

Note that `unite()` and `separate()` are inverse operations

<span style="color: blue">Part II</span>
===

<span style="color: blue">*`dplyr`: `group_by()` and `summarize()`*</span>

Recall: `dplyr` and SQL
===

- Once you learn `dplyr` you should find SQL very natural, and vice versa!
- This will make it much easier for tasks that require using both R and SQL to munge data and build statistical models
- One major link is through powerful verbs like `group_by()` and `summarize()`, which are used to aggregate data (now)
- Another major link to SQL is through merging/joining data frames, via `left_join()` and `inner_join()` verbs (shortly)

`group_by()`
===

Use `group_by()` to define a grouping of rows based on a column:

```{r}
mtcars %>% 
  group_by(cyl) %>%
  head(4)

mtcars %>% 
  group_by(cyl) %>%
  head(4) %>% class
```

---

- This doesn't actually change anything about the way the df looks
- Only difference is that when it prints, we're told about the groups
- But it will play a big role in how other `dplyr` verbs act 

`summarize()`
===

Use `summarise()` (or `summarize()` for us Americans) to apply functions to rows---ungrouped or grouped---of a data frame:

```{r}
# Ungrouped
mtcars %>% 
  summarize(mpg = mean(mpg),
            hp = mean(hp))

# Grouped by number of cylinders
mtcars %>%
  group_by(cyl) %>%
  summarize(mpg = mean(mpg),
            hp = mean(hp))
```

---

```{r}
mtcars %>%
  group_by(cyl) %>%
  summarize(mpg_mean = mean(mpg),
            mpg_max = max(mpg),
            hp_mean = mean(hp),
            hp_max = max(hp))
```

`ungroup()`
===

Use `ungroup()` to remove groupings structure from a data frame:

```{r}
mtcars %>%
  group_by(cyl) %>%
  ungroup() %>%
  summarize(hp = mean(hp),
            mpg = mean(mpg))
```

Beyond `summarize()` (bonus material)
===

`summarize()` in `dplyr` only allows you to calculate single numerical summaries, but you could image that you want more than that.

For example:
```{r}
mtcars %>% 
  pull(hp) %>% tapply(INDEX = mtcars$cyl, FUN = summary)
```

There are tools to do this in tidyverse, specifically `nest`/`unnest`ing data into columns and `purrr::map` function. (please see the first section of this [lecture](https://benjaminleroy.github.io/documents/36350/Lectures/6.2_advanced_computing_slides.html) ([Rmd](https://benjaminleroy.github.io/documents/36350/Lectures/6.2_advanced_computing.Rmd), [html](https://benjaminleroy.github.io/documents/36350/Lectures/6.2_advanced_computing.html)) for some notes). These are pretty useful if you are using a lot of the tidyverse paradigm

```{r}
mtcars %>%
  group_by(cyl) %>%
  nest() %>% # creates a column with the data conditional of subset
  mutate(sum = purrr::map(data, function(df) summary(df$hp)),
         sum_df = purrr::map(sum, broom::tidy)) %>% # unravel things to be data.frames
  select(cyl, sum_df) %>%
  unnest(cols = sum_df) 
```

<span style="color: blue">Part III</span>
===

<span style="color: blue">*`dplyr`: `left_join()` and `inner_join()`*</span>

Join operations
===

A "join" operation in database terminology is a merging of two data frames for us. There are 4 types of joins:

- **Inner join** (or just **join**): retain just the rows each table that match the condition
- **Left outer join** (or just **left join**): retain all rows in the first table, and just the rows in the second table that match the condition
- **Right outer join** (or just **right join**): retain just the rows in the first table that match the condition, and all rows in the second table
- **Full outer join** (or just **full join**): retain all rows in both tables

Column values that cannot be filled in are assigned `NA` values

---

It helps to visualize the join types:

![](http://www.stat.cmu.edu/~ryantibs/statcomp-F19/lectures/join_inner.png)
![](http://www.stat.cmu.edu/~ryantibs/statcomp-F19/lectures/join_left.png)
![](http://www.stat.cmu.edu/~ryantibs/statcomp-F19/lectures/join_right.png)
![](http://www.stat.cmu.edu/~ryantibs/statcomp-F19/lectures/join_full.png)

Two toy data frames
===

```{r}
has_kids_tab1 <- data.frame(name = c("Robert Downey, Jr", "Scarlett Johansson", "Chris Hemsworth"), 
                  children = c(3, 1, 3),
                  stringsAsFactors = FALSE)
americans_tab2 <- data.frame(name = c("Chris Evans", "Robert Downey, Jr", "Scarlett Johansson"),
                  age = c(38, 54, 34),
                  stringsAsFactors = FALSE)
has_kids_tab1
americans_tab2
```

`inner_join()`
===

Suppose we want to join `tab1` and `tab2` by name, but keep only actors in intersection (aka in both tables):

```{r}
inner_join(x = has_kids_tab1, y = americans_tab2, by = "name")
```

`left_join()`
===

Suppose we want to join `tab1` and `tab2` by name, but keep all actors from `tab1`:

```{r}
left_join(x = has_kids_tab1, y = americans_tab2, by = c("name" = "name"))
```

`right_join()`
===

Suppose we want to join `tab1` and `tab2` by name, but keep all actors from `tab2`:

```{r}
right_join(x = has_kids_tab1, y = americans_tab2, by = "name")
```

`full_join()`
===

Finally, suppose we want to join `tab1` and `tab2` by name, and keep all actors from both:

```{r}
full_join(x = has_kids_tab1, y = americans_tab2, by = "name")
```

More nuanced structure
===
```{r echo = F}
my_peeps <- data.frame(pol = factor(sample(c("R", "D"), 
                                          10, replace =T)),
                       gender = factor(sample(c("F", "M"), 
                                          10, replace =T)),
                       state = factor(sample(c("AZ", "PA"), 10,
                                            replace = T)),
                       IQ = round(rnorm(n = 10,mean = 100, sd = 10)))

politics <- data.frame(senator = c(
  "Kyrsten Sinema", "Martha McSally",
  "Pat Toomey","Boy Casey Jr."),
  pol = c("D", "R", "R", "D"),
  gender = c("F", "F", "M", "M"),
  STATE = c("AZ", "AZ", "PA", "PA")
  )
```

```{r}
my_peeps %T>% print() %>% dim()
politics %T>% print() %>% dim()
```

- `%T>%` is a special pipe that passes `my_peeps` into `print()` as a "side-effect" and then also passes `my_peeps` onto the rest of the chain (which in this case is just `dim()`)

---

```{r}
my_peeps %>% left_join(politics,by = c("state" = "STATE",
                                       "pol" = "pol")) %>% head(6)
```

Notice: 

- You can join on multiple columns
- `gender`, not used in "by", becomes `gender.x` and `gender.y` indicating columns for the first (`x`) and second (`y`) data frames

Summary
===

- `tidyr` is a package for manipulating the structure of data frames
- `pivot_longer()`: make wide data longer
- `pivot_wider()`: make long data wider
- `unite()` and `separate()`: combine or split columns
- `dplyr` has advanced functionality that mirrors SQL
- `group_by()`: create groups of rows according to a condition
- `summarize()`: apply computations across groups of rows
- `*_join()` where `*` = `inner`, `left`, `right`, or `full`: join two data frames together according to common values in certain columns, and `*` indicates how many rows to keep
- `nest()`, `map()`, ...

References
===

- [tidyverse.org](https://www.tidyverse.org/): the main place for package updates and news
- [tidyverse forum](https://community.rstudio.com/c/tidyverse): the official forum for questions
- [dplyr+tidyr cheatsheet (2015 version) from RStudio](https://www.rstudio.com/wp-content/uploads/2015/02/data-wrangling-cheatsheet.pdf)
- [dplyr cheatsheet (current version) from RStudio](https://www.rstudio.com/resources/cheatsheets/#dplyr)
- [tidyr cheatsheet (current version) from RStudio](https://www.rstudio.com/resources/cheatsheets/#import)