## More on data manipulation, shape changing, merging, transformations

Reading: Hadley Wickham,
["Tidy Data"](http://vita.had.co.nz/papers/tidy-data.pdf) and
["Reshaping data with the reshape package"](http://had.co.nz/reshape/introduction.pdf)

Agenda for today:

- Reshaping/tidy data/wide vs. long format

- Merging

-----

## Tidy data

We usually want our data in the folowing form:

- In a rectangular data frame

- One column per sample or observation

Data don't always come this way!

. . .

Even if the data do satisfy the "one row per observation" rule for one
analysis, they don't necessarily do so for another, and we often need
to change the "shape" of the data.

## Data semantics

Three concepts:

- Values

- Variables

- Observations

Datasets contain _values_, each of which belongs to a _variable_ and
an _observation_.

-----

Datasets can encode these in a lot of different ways. The "tidy" way
is to have

- Each variable a column

- Each observation a row

- Each cell a value

This is usually the way that other functions want the data to come in.

-----

For example:
```{r,echo=FALSE}
religion = c("Agnostic", "Atheist", "Buddhist", "Catholic", "Don't know/refused", "Evangelical Prot", "Hindu", "Historically Black Prot", "Jehovah's Witness", "Jewish")
income_bins = c("<$10k", "$10k-$20k", "$20k-$30k", "$30k-$40k", "$40k-$50k", "$50k-75k")
values = c(27, 12, 27, 418, 15, 575, 1, 228, 20, 19, 34, 27, 21, 617, 14, 869, 9, 244, 27, 19, 60, 37, 30, 732, 15, 1064, 7, 236, 24, 25, 81, 52, 34, 670, 11, 982, 9, 238, 24, 25, 76, 35, 33, 638, 10, 881, 11, 197, 21, 30, 137, 70, 58, 1116, 35, 1486, 34, 223, 30, 95)
income_and_religion = matrix(values, byrow = FALSE, nrow = length(religion), ncol = length(income_bins))
colnames(income_and_religion) = income_bins
rownames(income_and_religion) = religion
```

```{r}
income_and_religion
```

This table is easy to read, but the variables are encoded in the row
and column names, which makes it hard to use with modeling functions
in R.


-----

This data table contains all the same information:

```{r}
melt(income_and_religion, varnames = c("income", "religion"), value.name = "count")
```

It's harder to look at this table, but easier to manipulate
programmatically.

We'll see `melt` in a couple slides.

-----

## Reshaping

The term for transforming these datasets into each other is called
"reshaping", and pretty much all reshaping can be done with a
combination of two operations: melting and casting.

- Melting: Taking a wide dataset and making it long.

- Casting: Taking a melted dataset and making it wide.

The mneumonic is that you first "melt" the data set and then you
"cast" it. The "molten" data isn't usually the final form you want it
to be in, but it allows you to change it into other shapes easily.

## Melting data

The `melt` function takes data from wide form to long form.

Conceptually, we have two sets of variables:

- Identification variables, that describe the observations, and

- Measurement variables, which are measurements.

To melt a dateset, you need to decide what these are.

-----

What does a molten data table look like?

- Columns (potentially more than one) for id variables.

- If there is more than one measured variable, one column describing
the variable measured.

- One column for the value of the measured variable on the corresponding
observation.

. . .

Here id variables are income and religion, and the measurement is the
count of each.

Since we only have one measurement variable, we don't need an extra
column describing what the measured variable is.

```{r}
head(melt(income_and_religion))
```

## Melting

Based on what we said before, we will need to provide data, id
variables, and measurement variables, but in practice we have two
distinct situations:

- Matrix or array input: here we assume that the id variables are the
dimensions of the matrix or array, and the measurement variables are
the elements of the matrix or the array. Therefore, we don't actually
specify id variables or measurement variables.

- Data frame input: id variables and measurement variables have to be specified.


## Melting data frames

Let's see an example.

```{r}
head(french_fries)
```

-----

We want to melt this data frame so that 

- `id.vars` are time, treatment, subject, rep

- `measure.vars` are the remainder: potato, buttery, grassy, rancid, painty

```{r}
head(melt(french_fries, id.vars = c("time", "treatment", "subject", "rep"),
     measure.vars = c("potato", "buttery", "grassy", "rancid", "painty")))
```

-----

You can specify just one of `id.vars` and `measure.vars`, in which
case the function will assume that all the other variables should be
in the other class.

You can also specify the variables by number instead of by name.

```{r}
head(melt(french_fries, id.vars = 1:4))
head(melt(french_fries, measure.vars = 5:9))
```

-----

. . .

If you don't specify either, the function will assume that all the
factor variables should be id variables and all of the numeric
variables should be measured variables.

```{r}
ldply(french_fries, class)
melt(french_fries)
```

-----

You can make the output slightly nicer by specifying variable names
and value names:

```{r}
head(melt(french_fries, id.vars = 1:4, variable.name = "flavor", value.name = "flavor_intensity"))
```

-----


## Melting arrays

- Assume that the id variables are the dimensions of the array

- Assume the measured variable is stored as elements in the array.

- We don't specify id variables or measurement variables.

Therefore, the syntax is simply `melt(array)`

-----

For example:

```{r}
HairEyeColor
melt(HairEyeColor)
```

-----

There are a couple of optional arguments for making the output a
little nicer, like giving a different name to the column in the output
describing the measured value:

```{r}
melt(HairEyeColor, value.name = "number")
```



## Casting

reshape2 uses `*cast`: either `dcast` or `acast` for data frame or
array output, respectively.

Syntax: `*cast(mdata, col_var_1 + ... + col_var_n ~ row_var_1 + ... +
row_var_m, value.var)`

- `mdata` should be a molten data set

- The columns of the output will be all the combinations of `col_var_1`,
..., `col_var_n`

- The rows of the output will be all the combinations of `row_var_1`,
..., `row_var_n`

- `value.var` is the value variable, the values that should go in the
cells of the output. If you don't specify it, `melt` will guess.


-----

Example

```{r}
ff_m = melt(french_fries, id.vars = 1:4, variable.name = "flavor", na.rm = TRUE)
head(ff_m)
cast_ex_1 = dcast(ff_m, time + treatment ~ subject + rep + flavor, value.var = "value")
cast_ex_1[1:5,1:5]
## check to see if the numbers match
subset(ff_m, time == 1 & subject == "3" &  rep == 1 & flavor == "potato")
```

## Aggregation and casting

When you cast data, you often don't use all of the variables.

This means that each element of the cast table will correspond to more
than one measurement, and so they need to be aggregated in some way.



```{r}
head(ff_m)
dcast(ff_m, subject ~ flavor, value.var = "value")
```

-----

The aggregation function is specified with `fun.aggregate`:

```{r}
dcast(ff_m, subject ~ flavor, value.var = "value", fun.aggregate = mean)
```

-----

A couple of other notes:

- You can use `.` to represent all the other variables in the formula.

- The order that you specify the variables in the formula matters for
the way the output is ordered: the first variables change most slowly,
and the later ones change more quickly.

-----

```{r}
head(dcast(ff_m, subject + treatment ~ flavor, fun.aggregate = mean), n = 10)
head(dcast(ff_m, treatment + subject ~ flavor, fun.aggregate = mean), n = 10)
```

## Merging 

Final topic: What if you have data from two different places and you
need to put them together?

Basic syntax: `merge(x, y, by.x, by.y)`

- `x` and `y` are the two datasets you want to merge.

- `by.x` is the column of `x` to merge on.

- `by.y` is the column of `y` to merge on.


-----

Example:

```{r}
cities = data.frame(
    city=c('New York','Boston','Juneau',
        'Anchorage','San Diego',
        'Philadelphia','Los Angeles',
        'Fairbanks','Ann Arbor','Seattle'),
    state.abb=c('NY','MA','AK','AK','CA',
        'PA','CA','AK','MI','WA'))

states = data.frame(state.name, state.abb)
cities
head(states)
```


We want to add the state name to the cities data frame, and we can use merge.

```{r}
merge(states, cities, by.x = "state.abb", by.y = "state.abb")
```

-----

Notice in the last example that there was some ambiguity in how the
merge took place because the two datasets have different sets of
values for `state.abb`.

- Default: the output dataset only has rows for which we had
values in both data frames.

. . .

Can modify with `all`, `all.x`, or `all.y`

- `all = TRUE` means that you get one row for values of the merging
variable that were seen in either `x` or `y`

- `all.x = TRUE` means you get one row for each value of the merging
variable that was seen in `x`

- `all.y = TRUE` means you get one row for each value of the merging
variable that was seen in `y`.

-----

```{r}
merge(states, cities, all.x = TRUE)
```
-----


Some additional notes:

- Default if you don't specify `by.x` and `by.y` is to use the columns
that are common to the two.

- `by.x`/`by.y` can have length more than 1, in which case we match
on the entire set of specified variables.

- Can use `by` instead of `by.x` and `by.y`, in which case the name of
the column to merge on has to be the same in both `x` and `y`.


## Some final notes

Merging, melting/casting, and split/apply/combine from last week are useful enough that there are multiple
implementations.

You can use other implementations, but you shouldn't try to
re-make them from scratch.

Some other options are:

- `join` in `plyr` does the same thing as `merge`

- `gather` and `spread` in the `tidyr` package do roughly the same
thing as `cast` and `melt`.

- There are multiple iterations of reshape, `reshape2` was a redesign
of `reshape`.

- `plyr` and `dplyr` are also two packages that have basically the
same goal, but `dplyr` is focused more on data frames.

Next week: Debugging and testing!
