data_wrangling.Rmd

```{r, message=FALSE, echo=FALSE, cache=FALSE}
source("./customization/knitr_options.R")
```

```{r, message=FALSE, echo=FALSE, cache=FALSE}
library(magrittr)
```

# (PART) Data Wrangling {-}


# Data Wrangling {#data-wrangling-chapter}

## Definition

> Data wrangling is loosely defined as the process of manually converting or mapping data from one "raw" form into another format that allows for more convenient consumption of the data with the help of semi-automated tools.
>  
> It typically follows a set of general steps which begin with extracting the data in a raw form from the data source, "wrangling" the raw data using algorithms (e.g. sorting) or parsing the data into predefined data structures, and finally depositing the resulting content into a data sink for storage and future use.

<https://en.wikipedia.org/wiki/Data_wrangling>

## Wrangling Challenges

Some of the challenges encountered in data wrangling are:

- Importing files
- Organizing data sets
- Transforming data
- Combining data sets
- Dealing with various data types (e.g., dates)
- Identifying errors

# Tidy Data

## Motivation

"Happy families are all alike; every unhappy family is unhappy in its own way."  -- Leo Tolstoy

"Tidy datasets are all alike, but every messy dataset is messy in its own way."  -- Hadley Wickham

From *R for Data Science*.

## Definition

> Tidy datasets are easy to manipulate, model and visualize, and have a specific structure: each variable is a column, each observation is a row, and each type of observational unit is a table.

From Wickham (2014), "Tidy Data", *Journal of Statistical Software*


> A dataset is a collection of values, usually either numbers (if quantitative) or strings (if qualitative). Values are organized in two ways. Every value belongs to a variable and an observation. A variable contains all values that measure the same underlying attribute (like height, temperature, duration) across units. An observation contains all values measured on the same unit (like a person, or a day, or a race) across attributes.

From: Wickham H (2014), "Tidy Data", *Journal of Statistical Software*

## Example: Titanic Data

According to the `Titanic` data from the `datasets` package: 367 males survived, 1364 males perished, 344 females survived, and 126 females perished.

How should we organize these data?

### Intuitive Format

\ | Survived | Perished
---- | ---- | ----
**Male** | 367 | 1364
**Female** | 344 | 126

### Tidy Format

fate | sex | number
---- | ---- | ----
perished | male | 1364
perished | female | 126
survived | male | 367
survived | female | 344

## Rules of Thumb

1. Something is a value if it represents different forms of a common object and it changes throughout the data set. 
2. Something is a value if the data can be arranged so that it appears across rows within a column and this makes sense.  

For example, `fate` and `sex` do not satisfy these criteria in the `Titanic` data, but `perished`/`survived` and `female`/`male` do.

# Tidyverse

## Idea

When the data are in tidy format, one can design functions around this format to consistently and intuitively perform data wrangling and analysis operations.  The packages containing these are called the "tidyverse."

Note:  The idea of tidy data was first proposed by Hadley Wickham and he created several of the core packages, so this used to be called (semi-seriously) the "hadleyverse."

## Packages

> The tidyverse is a set of packages that work in harmony because they share common data representations and API design. The `tidyverse` package is designed to make it easy to install and load core packages from the tidyverse in a single command.

https://blog.rstudio.org/2016/09/15/tidyverse-1-0-0/

## Primary Packages

- `dplyr`: data manipulation
- `ggplot2`: data visualization
- `purrr`: functional programming
- `readr`: data import
- `tibble`: modernization of data frames
- `tidyr`: data tidying

Loading `tidyverse`:

```{r, cache=FALSE}
library(tidyverse)
```

## Tidying Data

### `tidyr` Package

This package provides a variety of functions that allow one to tidy data.

Importantly, it solves two common ways that data come as untidy.

1. `gather()`:  Gathers a variable distributed across two or more columns into a single column.
2. `spread()`:  Spreads a column containing two or more variables into one column per variable.

### Untidy Titanic Data

This does not satisfy the definition of tidy data because a variable's observations are distributed as column names.

```{r}
df <- tibble(sex=c("male", "female"), 
             survived=c(367, 344),
             perished=c(1364, 126))
df
```

### `gather()`

We apply the `gather()` function to make a column containing the `survived` and `perished` observations.

```{r}
df <- gather(df, survived, perished, 
               key="fate", value="number")
df
```

### `spread()`

This example is here to show that `spread()` does the opposite operation as `gather()`.  It isn't used appropriately here because we revert the data back to untidy format.

```{r}
spread(df, key=fate, value=number)
```

### Tidy with `spread()` 

Median cost of home and median income per city are two variables included in a single column.  This means we need to use `spread()`.

```{r, include=FALSE, cache=FALSE}
df <- tibble(city=c("Boston", "Boston", "Raleigh", "Raleigh"),
             median_value=rep(c("home", "income"), 2),
             dollars=c(527300, 71738, 215700, 65778))
```

```{r}
df
```

```{r}
spread(df, key=median_value, value=dollars)
```


## Reshaping Data

### Wide vs. Long Format

Tidy data are in "wide format" in that they have a column for each variable and there is one observed unit per row.

However, sometimes it's useful to transform to "long format."  The simplest long format data have two columns.  The first column contains the variable names and the second colum contains the values for the variables.  There are "wider" long format data that have additional columns that identify connections between observations.

Wide format data is useful for some analyses and long format for others.

### `reshape2` Package

The `reshape2` package has three important functions:  `melt`, `dcast`, and `acast`.  It allows one to move between wide and long tidy data formats.

```{r, cache=FALSE, message=FALSE}
library("reshape2")
library("datasets")
data(airquality, package="datasets")
names(airquality)
dim(airquality)
airquality <- as_tibble(airquality)
```

### Air Quality Data Set

```{r}
head(airquality)
```

```{r}
tail(airquality)
```

### Melt

Melting can be thought of as melting a piece of solid metal (wide data), so it drips into long format.

```{r}
aql <- melt(airquality)
head(aql)
```

```{r}
tail(aql)
```

### Guided Melt

In the previous example, we lose the fact that a set of measurements occurred on a particular day and month, so we can do a guided melt to keep this information.

```{r}
aql <- melt(airquality, id.vars = c("Month", "Day"))
head(aql)
```

```{r}
tail(aql)
```

### Casting

Casting allows us to go from long format to wide format data.  It can be visualized as pouring molten metal (long format) into a cast to create a solid piece of metal (wide format).

Casting is more difficult because choices have to be made to determine how the wide format will be organized.  It often takes some thought and experimentation for new users. 

Let's do an example with `dcast`, which is casting for data frames.

### `dcast()`

```{r}
aqw <- dcast(aql, Month + Day ~ variable)
head(aqw)
```

```{r}
tail(aqw)
```


# Transforming Data

## `dplyr` Package

`dplyr` is a package with the following description:

> A fast, consistent tool for working with data frame like objects, both in memory and out of memory.

This package offers a "grammar" for manipulating data frames.  

Everything that `dplyr` does can also be done using basic R commands -- however, it tends to be much faster and easier to use `dplyr`.

## Grammar of `dplyr`

Verbs:

- `filter`: extract a subset of rows from a data frame based on logical conditions
- `arrange`: reorder rows of a data frame
- `rename`: rename variables in a data frame
- `select`: return a subset of the columns of a data frame, using a flexible notation


- `mutate`: add new variables/columns or transform existing variables
- `distinct`: returns only the unique values in a table
- `summarize`: generate summary statistics of different variables in the data frame, possibly within strata
- `group_by`: breaks down a dataset into specified groups of rows

Partially based on *R Programming for Data Science* 

## Baby Names Data Set

```{r, include=FALSE}
rm(list=ls())
```

```{r}
library("dplyr", verbose=FALSE)
library("babynames")
ls()
babynames <- as_tibble(babynames::babynames)
ls()
```

The `babynames` Object

```{r}
class(babynames)
dim(babynames)
```

```{r}
babynames
```

Peek at the Data

```{r}
set.seed(201)
sample_n(babynames, 10) 
## try also sample_frac(babynames, 6e-6)
```

## `%>%` Operator

Originally from R package `magrittr`.  Provides a mechanism for chaining commands with a forward-pipe operator, `%>%`.

```{r}
x <- 1:10

x %>% log(base=10) %>% sum()

sum(log(x,base=10))
```

```{r}
babynames %>% sample_n(5)
```

## `filter()`

```{r}
filter(babynames, year==1880, sex=="F")
## same as filter(babynames, year==1880 & sex=="F")
```

```{r}
filter(babynames, year==1880, sex=="F", n > 5000)
```

## `arrange()`

```{r}
arrange(babynames, name, year, sex)
```

```{r}
arrange(babynames, desc(name), desc(year), sex)
```

## `rename()`

```{r}
rename(babynames, number=n)
```

## `select()`

```{r}
select(babynames, sex, name, n)
## same as select(babynames, sex:n)
```

Renaming with `select()`:

```{r}
select(babynames, sex, name, number=n)
```

## `mutate()`

```{r}
mutate(babynames, total_by_year=round(n/prop))
## see also transmutate
```

## `distinct()`

Let's put a few things together now adding the function `distinct()`...

```{r}
babynames %>% mutate(total_by_year=round(n/prop)) %>% 
  select(sex, year, total_by_year) %>% distinct()
```

## `summarize()`

```{r}
summarize(babynames, mean_n = mean(n), median_n = median(n), 
          number_sex = n_distinct(sex), 
          distinct_names = n_distinct(name))
```

## `group_by()`

```{r}
babynames %>% group_by(year, sex)
```

## Chaining Verbs Together

#### No. Individuals by Year and Sex {-}

```{r}
babynames %>% group_by(year, sex) %>% 
  summarize(total_by_year=sum(n))
```

#### How Many Distinct Names? {-}

```{r}
babynames %>% group_by(sex) %>% 
  summarize(mean_n = mean(n), 
            distinct_names_sex = n_distinct(name))
```


#### Most Popular Names by Year {-}

```{r}
top_names <- babynames %>% group_by(year, sex) %>% 
  summarize(top_name = name[which.max(n)])

head(top_names)
```


#### Most Popular Names in Recent Years {-}

```{r}
tail(top_names, n=10)
```

#### Most Popular Female Names in the 1990s {-}

```{r}
top_names %>% filter(year >= 1990 & year < 2000, sex=="F")
```

#### Most Popular Male Names in the 1990s {-}

```{r}
top_names %>% filter(year >= 1990 & year < 2000, sex=="M")
```

#### Analyzing the name 'John' {-}

```{r, small.mar=TRUE}
john <- babynames %>% filter(sex=="M", name=="John")
plot(john$year, john$prop, type="l")
```


#### Analyzing the name 'Bella' {-}

```{r, small.mar=TRUE}
bella <- babynames %>% filter(sex=="F", name=="Bella") 
plot(bella$year, bella$prop, type="l")
```

# Relational Data

## Multiple Data Sets

In many data analyses you will have multiple tables of related data that must be combined in order to carry out your analysis.

The `dplyr` package includes a number of tools to facilitate this.

## Toy Example

Here are two data frames that are related through a common variable called `key`.

```{r}
x <- tibble(key = c(1, 2, 3), x_val = c("x1", "x2", "x3"))
y <- tibble(key = c(1, 2, 4), y_val = c("y1", "y2", "y4"))
```
```{r}
x
y
```

## Verbs

To work with relational data you need verbs that work with pairs of tables. There are three families of verbs designed to work with relational data.

- *Mutating joins* add new variables to one data frame from matching observations in another.
- *Filtering joins* filter observations from one data frame based on whether or not they match an observation in the other table.
- *Set operations* treat observations as if they were set elements.

From *R for Data Science*


## `inner_join()`

An inner-join matches pairs of observations when their keys are equal.

```{r}
inner_join(x, y, key="key")
```


## `left_join()`

A left-join keeps all observations in the first argument, `x`.

```{r}
left_join(x, y, key="key")
```

```{r}
x %>% left_join(y, key="key")
```

## `right_join()`

A right-join keeps all observations in the second argument, `y`.

```{r}
right_join(x, y)
```

## `full_join()`

A full-join keeps all observations in either argument, `x` or `y`.

```{r}
full_join(x, y, key="key")
```

## `anti_join()`

An anti-join removes all observations in the first argument, `x`, that appear in the second argument, `y`.

```{r}
anti_join(x, y, key="key")
```

## `semi_join()`

A semi-join keeps all observations in the first argument, `x`, that have a match in the second argument, `y`.

```{r}
semi_join(x, y, key="key")
```

## Repeated Key Values

When one of the two data frames has repeated `key` values, the observations are repeated in the other data frame.

```{r, echo=FALSE}
y2 <- tibble(key = c(1, 2, 2, 4), 
            y_val = c("y1", "y2a", "y2b", "y4"))
```

```{r}
y2
```

```{r}
x %>% left_join(y2, key="key")
```

## Set Operations

One can perform traditional set operations on the rows of data frames. 

- `intersect(x, y)`: return only observations in both `x` and `y`
- `union(x, y)`: return unique observations in `x` and `y`
- `setdiff(x, y)`: return observations in `x`, but not in `y`

From *R for Data Science*

#### Example `setdiff()` {-}

```{r, echo=FALSE}
df1 <- tibble(x=c(1, 2), y=c(1, 1))
df2 <- tibble(x=c(1, 1), y=c(1, 2))
```

```{r}
df1
df2
setdiff(df1, df2)
```

# Case Study in Data Wrangling

## Yeast Genomics

[Smith and Kruglyak (2008)](http://journals.plos.org/plosbiology/article?id=10.1371/journal.pbio.0060083) is a study that measured 2820 genotypes in 109 yeast F1 segregants from a cross between parental lines BY and RM.

They also measured gene expression on 4482 genes in each of these segregants when growing in two different Carbon sources, glucose and ethanol. 

### Load Data

The data was distributed as a collection of matrices in R.

```{r, cache=FALSE}
rm(list=ls())
load("./data/smith_kruglyak.RData")
ls()
eapply(env=.GlobalEnv, dim)
```

### Gene Expression Matrices

```{r}
exp.g %>% cbind(rownames(exp.g), .) %>% as_tibble() %>% 
  print()
```

### Gene Position Matrix

```{r}
exp.pos %>% cbind(rownames(exp.pos), .) %>% as_tibble() %>% 
  print()
```

### Row Names

The gene names are contained in the row names.

```{r}
head(rownames(exp.g))
head(rownames(exp.e))
head(rownames(exp.pos))
all.equal(rownames(exp.g), rownames(exp.e))
all.equal(rownames(exp.g), rownames(exp.pos))
```

### Unify Column Names

The segregants are column names, and they are inconsistent across matrices.

```{r}
head(colnames(exp.g))
head(colnames(marker))
     
##fix column names with gsub
colnames(exp.g) %<>% strsplit(split=".", fixed=TRUE) %>%
  lapply(function(x) {x[2]})
colnames(exp.e) %<>% strsplit(split=".", fixed=TRUE) %>%
  lapply(function(x) {x[2]})
head(colnames(exp.g))
```

### Gene Positions

Let's first pull out rownames of `exp.pos` and make them a column in the data frame.

```{r}
gene_pos <- exp.pos %>% as_tibble() %>%
  mutate(gene = rownames(exp.pos)) %>%
  dplyr::select(gene, chr = Chromsome, start = Start_coord, 
                end = End_coord)
print(gene_pos, n=7)
```

### Tidy Each Expression Matrix

We `melt` the expression matrices and bind them together into one big tidy data frame.

```{r melt_expression, cache=FALSE}
exp_g <- melt(exp.g) %>% as_tibble() %>% 
  dplyr::select(gene = Var1, segregant = Var2, 
                expression = value) %>%
  mutate(condition = "glucose")
exp_e <- melt(exp.e) %>% as_tibble() %>% 
  dplyr::select(gene = Var1, segregant = Var2, 
                expression = value) %>%
  mutate(condition = "ethanol")
print(exp_e, n=4)

```

### Combine Into Single Data Frame

Combine gene expression data from two conditions into a single data frame.

```{r, cache=FALSE}
exp_all <- bind_rows(exp_g, exp_e)
sample_n(exp_all, size=10)
```

### Join Gene Positions

Now we want to join the gene positions with the expression data.

```{r merge_positions}
exp_all <- exp_all %>%
  mutate(gene = as.character(gene), 
         segregant = as.character(segregant))
sk_tidy <- exp_all %>%
  left_join(gene_pos, by = "gene")
sample_n(sk_tidy, size=7)
```

### Apply `dplyr` Functions

Now that we have the data made tidy in the data frame `sk_tidy`, let's apply some `dplyr` operations...


Does each gene have the same number of observations?

```{r}
sk_tidy %>% group_by(gene) %>% 
  summarize(value = n()) %>%
  summary()
```

No, so let's see which genes have more than one set of observations.

```{r}
sk_tidy %>% group_by(gene) %>% 
  summarize(value = n()) %>%
  filter(value > median(value))
```


Let's remove replicated measurements for these genes.

```{r}
sk_tidy %<>% distinct(gene, segregant, condition, 
                      .keep_all = TRUE)

sk_tidy %>% group_by(gene) %>% 
  summarize(value = n()) %>%
  summary()
```

As an exercise, think about how you would use `dplyr` to replace the replicated gene expression values with a single averaged expression value for these genes.


Get the mean and standard deviation expression per chromosome.

```{r}
sk_tidy %>%
  group_by(chr) %>%
  summarize(mean = mean(expression), sd=sd(expression))
```


Get the mean and standard deviation expression per chromosome in each condition.

```{r}
sk_tidy %>%
  group_by(chr, condition) %>%
  summarize(mean = mean(expression), sd=sd(expression))
```


Count the number of genes per chromosome.

```{r}
sk_tidy %>%
  filter(condition == "glucose", segregant == "20_4_c") %>%
  group_by(chr) %>% 
  summarize(num.genes = n())
```


Filter for the first gene on every chromosome.

```{r}
sk_tidy %>%
  filter(condition == "glucose", segregant == "20_4_c") %>%
  group_by(chr) %>%
  filter(start == min(start))
```
  

To plot expression in glucose versus ethanol we first need to use `dcast()`.

```{r}
sk_tidy %>% dcast(gene + segregant ~ condition, 
                  value.var = "expression") %>%
  as_tibble()
```


```{r}
sk_tidy %>% dcast(gene + segregant ~ condition, 
                  value.var = "expression") %>%
  filter(gene == "YAL002W") %>%
  ggplot(aes(x = glucose, y = ethanol)) +
  geom_point() +  theme_bw() +
  theme(legend.position = "none")
```

# Further Reading

## Additional Examples

You should study additional tutorials of `dplyr` that utilize other data sets:

- Read the `dplyr` [introductory vignette](https://cran.rstudio.com/web/packages/dplyr/vignettes/introduction.html)
- Read the examples given in the *R for Data Science* assigned reading

## Additional `dplyr` Features

- We've only scratched the surface -- many interesting demos of `dplyr` can be found online
- `dplyr` can work with other data frame backends such as SQL databases
- There is an SQL interface for relational databases via the `DBI` package
- `dplyr` can be integrated with the `data.table` package for large fast tables
- There is a [healthy rivalry](http://stackoverflow.com/questions/21435339/data-table-vs-dplyr-can-one-do-something-well-the-other-cant-or-does-poorly) between `dplyr` and [`data.table`](https://cran.r-project.org/web/packages/data.table/index.html)