---
title: "Tidyverse II: Tidyr and Advanced Dplyr"
author: "Statistical Computing, 36-350"
date: "Monday November 5, 2018"
---

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()`: select columns
- `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}
library(tidyverse) 
mtcars %>% 
  filter(., (mpg >= 14 & disp >= 200) | (drat <= 3)) %>% 
  head(., 2)
```

**Pro tip**: 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):

- `gather()`: make "wide" data longer 
- `spread()`: 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

`gather()`
===

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

```{r}
library(EDAWR) # Load some nice data sets

EDAWR::cases %>% 
  head(., 3)

EDAWR::cases %>% 
  gather(., "year", "n", 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

`spread()`
===

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

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

EDAWR::pollution %>% 
  spread(., size, 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 of
- We just had to declaratively specify the output 
- Note that `spread()` and `gather()` are inverses

`separate()` 
===

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

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

`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(., 6)
```

---

- 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 

`summarise()`
===

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

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

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

---

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

`ungroup()`
===

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

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

Simplifying indexing
===

In last week's lab we encountered the following example:

```{r, cache=TRUE}
str.url <- "http://www.stat.cmu.edu/~ryantibs/statcomp-F18/data/trump.txt" 
str.url %>% 
  readLines %>% 
  paste(collapse=" ") %>%
  strsplit(split="[[:space:]]|[[:punct:]]") %>%
  unlist(x = .) %>%
  .[. != ""] %>%
  table(.) %>%
  sort(decreasing=TRUE) %>%
  head(.)
```

This required a tricky indexing operation using `%>%` on vectors: `.[. != ""]`

---

We can perform the same operations using `dplyr` if we convert to a data frame:

```{r}
str.url %>%
  readLines(.) %>%
  paste(collapse=" ") %>%
  strsplit(split="[[:space:]]|[[:punct:]]") %>%
  unlist(.) %>%
  as_data_frame() %>%            # Convert to a data frame (dplyr version)
  rename(word_list = value) %>%  # Rename the column to `word_list`
  filter(word_list != "") %>%    # Now indexing is easy!
  group_by(word_list) %>% 
  summarise(word_count = n()) %>%
  arrange(desc(word_count)) %>% 
  slice(1:6)
```

<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-F18/lectures/join_inner.png)
![](http://www.stat.cmu.edu/~ryantibs/statcomp-F18/lectures/join_left.png)
![](http://www.stat.cmu.edu/~ryantibs/statcomp-F18/lectures/join_right.png)
![](http://www.stat.cmu.edu/~ryantibs/statcomp-F18/lectures/join_full.png)

Two toy data frames
===

```{r}
tab1 = data.frame(name = c("Alexis", "Bernie", "Charlie"),
                  children = 1:3,
                  stringsAsFactors = FALSE)
tab2 = data.frame(name = c("Alexis", "Bernie", "David"),
                  age = c(54, 34, 63),
                  stringsAsFactors = FALSE)
tab1
tab2
```

`inner_join()`
===

Suppose we want to join `tab1` and `tab2` by name, but keep only customers in intersection:

```{r}
inner_join(x=tab1, y=tab2, by="name")
```

`left_join()`
===

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

```{r}
left_join(x=tab1, y=tab2, by="name")
```

`right_join()`
===

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

```{r}
right_join(x=tab1, y=tab2, by="name")
```

`full_join()`
===

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

```{r}
full_join(x=tab1, y=tab2, by="name")
```

Summary
===

- `tidyr` is a package for manipulating the structure of data frames
- `gather()`: make wide data longer
- `spread()`: 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
- `summarise()`: 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

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)