---
title: "Merging Data"
author: "Statistical Computing, 36-350"
date: "Friday October 28, 2016"
---

Merging data frames
===

Suppose you have two data frames `X`, `Y`, and you want to combine them

- Simplest case: the data frames have exactly the same number of rows, that the rows represent exactly the same units, and you want all columns from both; just use, `data.frame(X,Y)`
- Next best case: you know that the two data frames have the same rows, but you only want certain columns from each; just use, e.g., `data.frame(X$col1,X$col5,Y$col3)`
- Next best case: same number of rows but in different order; put one of them in same order as the other, with `order()`. Alternatively, use `merge()`
- Worse cases: different numbers of rows ... hard to line up rows ...

Example: big city drivers
===

People in larger cities (larger areas) drive more---seems like a reasonable hypothesis, but is it true?

Distance driven, and city population, from http://www.fhwa.dot.gov/policyinformation/statistics/2011/hm71.cfm:

```{r}
fha = read.csv("http://www.stat.cmu.edu/~ryantibs/statcomp-F16/data/fha.csv",
               na.strings="NA", 
               colClasses=c("character","double","double","double"))
nrow(fha)
head(fha)
```

(Continued)
===

Area and population of "urbanized areas", from http://www2.census.gov/geo/ua/ua_list_all.txt:

```{r}
ua = read.csv("http://www.stat.cmu.edu/~ryantibs/statcomp-F16/data/ua.dat",
              sep=";")
nrow(ua)
head(ua)
```

Difficulties in navigating the merge
===

- $\approx 500$ cities versus $\approx 4000$ "urbanized areas"
- `fha` orders cities by population, `ua` is alphabetical by name
- Both have place names, but those don't always agree
- Not even common names for the shared columns

Lesson: find the unique identifier
===

But both use the same census figures for population! And it turns out every settlement (in the top 498) has a unique census population:

```{r}
length(unique(fha$Population)) == nrow(fha)
ua.pop.top498 = sort(ua$POP, decreasing=TRUE)[1:nrow(fha)]
max(abs(fha$Population - ua.pop.top498))
```

First way to merge
===

Reorder area column in second table by population, append to first table

```{r}
ind.pop = order(ua$POP, decreasing=TRUE) # Order by population
df1 = data.frame(fha, area=ua$AREALANDSQMI[ind.pop][1:nrow(fha)])
# Neaten up names
colnames(df1) = c("City","Population","Roads","Mileage","Area")
nrow(df1)
head(df1)
```

Second way to merge
===

Use the `merge()` function

```{r}
df2 = merge(x=fha, y=ua, by.x="Population", by.y="POP")
nrow(df2)
tail(df2, 3)
```

`merge()`
===

The `merge()` function tries to merge two data frames according to common columns, as in: `merge(x, y, by.x="SomeXCol", by.y="SomeYCol")`, to join two data frames `x`, `y`, by matching the columns "SomeXCol" and "SomeYCol"

- Default (no `by.x`, `by.y` specified) is to match all columns with common names
- Output will be a new data frame that has all the columns of both data frames 
- If you know databases, then `merge()` is doing a JOIN (if you don't know what that means, you will by the end of the semester!)

Using `order()` and manual tricks versus `merge()`
===

- Reordering is easier to grasp; `merge()` takes some learning
- Reordering is simplest when there's only one column to merge on; `merge()` handles many columns
- Reorderng is simplest when the data frames are the same size; `merge()` handles different sizes automatically

So, do bigger cities mean more driving?
===

```{r}
# Convert 1,000s of miles to miles
df1$Mileage = 1000*df1$Mileage
# Plot daily miles per person vs. area
plot(x=df1$Area, y=df1$Mileage/df1$Population, log="x",
     xlab="Miles driven per person per day",
     ylab="City area in square miles")
# Impressively flat regression line
abline(lm(Mileage/Population ~ Area, data=df1), col="red")
```