---
title: "Reading in, Reordering, and Merging Data"
author: "Statistical Computing, 36-350"
date: "Tuesday March 20, 2018"
---

Last week: Simulation
===

- Running simulations is an integral part of being a statistician in the 21st century
- R provides us with a utility functions for simulations from a wide variety of distributions
- To make your simulation results reproducible, you must set the seed, using `set.seed()`
- There is a natural connection between iteration, functions, and simulations
- Saving and loading results can be done in two formats: rds and rdata formats

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

<span style="color: blue">*Reading in and reordering data*</span>

Reading in data from the outside
===

All along, we've already been reading in data from the outside, using:

- `readLines()`: reading in lines of text from a file or webpage; returns vector of strings
- `read.table()`: read in a data table from a file or webpage; returns data frame
- `read.csv()`: like the above, but assumes comma separated data; returns data frame

This week we'll focus on `read.table()`, `read.csv()` and their counterparts `write.table()`, `write.csv()`, respectively

Reading in data from a previous R session
===

Reminder that we've already learned two ways to read/write in data in specialized R formats:

- `readRDS()`, `saveRDS()`: functions for reading/writing single R objects from/to a file
- `load()`, `save()`: functions for reading/writing any number of R objects from/to a file

Advantage: these can be a lot more memory efficient than what we'll cover this week. Disadvantage: they're limited in scope in the sense that they can only communicate with R

`read.table()`, `read.csv()`
===

Have a table full of data, just not in the R file format? Then `read.table()` is the function for you. It works as in:

- `read.table(file=file.name, sep=" ")`, to read data from a local file on your computer called `file.name`, assuming (say) space separated data
- `read.table(file=webpage.link, sep="\t")`, to read data from a webpage up at `webpage.link`, assuming (say) tab separated data

The function `read.csv()` is just a shortcut for using `read.table()` with `sep=","`. (But note: these two actually differ on some of the default inputs!)

Examples of reading in data 
===

```{r}
# This data table is comma separated, so we can use read.csv()
strike.df = 
  read.csv("http://www.stat.cmu.edu/~ryantibs/statcomp-S18/data/strikes.csv")
head(strike.df)
sapply(strike.df, class)
```

---

```{r}
# This data table is tab separated, so let's specify sep="\t"
anss.df = read.table(
  file="http://www.stat.cmu.edu/~ryantibs/statcomp-S18/data/anss.dat",
  sep="\t")
head(anss.df)
sapply(anss.df, class)

# Oops! It comes with column names, so let's set header=TRUE
anss.df = read.table(
  file="http://www.stat.cmu.edu/~ryantibs/statcomp-S18/data/anss.dat",
  sep="\t", header=TRUE)
head(anss.df)
sapply(anss.df, class)
```

Helpful input arguments
===

The following inputs apply to either `read.table()` or `read.csv()` (though these two functions actually have different default inputs in general---e.g., `header` defaults to TRUE in `read.table()` but FALSE in `read.csv()`)

- `header`: boolean, TRUE is the first line should be interpreted as column names
- `sep`: string, specifies what separates the entries; empty string "" is interpreted to mean any white space
- `quote`: string, specifies what set of characters signify the beginning and end of quotes; empty string "" disables quotes altogether

Other helpful inputs: `skip`, `row.names`, `col.names`. You can read about them in the help file for `read.table()` 

Another example of reading in data
===

```{r, error=TRUE}
# This data table is tab separated, and it comes with column names
sprint.df = read.table(
  file="http://www.stat.cmu.edu/~ryantibs/statcomp-S18/data/sprint.dat",
  sep="\t", header=TRUE)

# Oops! It turns out it has some apostrophe marks in the City column, 
# so we have to set quote=""
sprint.df = read.table(
  file="http://www.stat.cmu.edu/~ryantibs/statcomp-S18/data/sprint.dat",
  sep="\t", header=TRUE, quote="")
head(sprint.df)
sapply(sprint.df, class)
unique(grep("'", sprint.df$City, value=TRUE)) # This is the troublemaker
```

`write.table()`, `write.csv()`
===

To write a data frame (or matrix) to a text file, use `write.table()` or `write.csv()`. These are the counterparts to `read.table()` and `read.csv()`, and they work as in:

- `write.table(my.df, file="my.df.txt", sep=" ", quote=FALSE)`, to write `my.dat` to the text file "my.df.txt" (to be created in your working directory), with (say) space separation, and no quotes around the entries
- `write.csv(my.df, file="my.df.csv", quote=FALSE)`, to write `my.dat` to the text file "my.df.csv" (to be created in your working directory), with comma separation, and no quotes

Note that `quote=FALSE`, signifying that no quotes should be put around the printed data entries, seems always preferable (default is `quote=TRUE`). Also, setting `row.names=FALSE` and `col.names=FALSE` will disable the printing of row and column names (defaults are `row.names=TRUE` and `col.names=TRUE`)

Reordering data
===

Sometimes it's convenient to reorder our data, say the rows of our data frame (or matrix). Recall:

- The function `order()` takes in a vector, and returns the vector of indices that put this vector in increasing order
- Set the input `decreasing=TRUE` in `order()` to get decreasing order
- We can apply compute an appropriate vector of indices, and then use this on rows of our data frame to reorder all of the columns simultaneously

Examples of reordering
===

```{r}
# The sprint data has its rows ordered by the fastest 100m time to the slowest.
# Suppose we wanted to reorder, from slowest to fastest
i.slow = order(sprint.df$Time, decreasing=TRUE) # By decreasing sprint time
sprint.df.slow = sprint.df[i.slow,] # Reorder rows by decreasing sprint time
head(sprint.df.slow)

# Suppose we wanted to reorder the rows by sprinter name, alphabetically
i.name = order(sprint.df$Name) # By sprinter name
sprint.df.name = sprint.df[i.name,] # Reorder rows by name
head(sprint.df.name)
```

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

<span style="color: blue">*Merging data*</span>


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(
  file="http://www.stat.cmu.edu/~ryantibs/statcomp-S18/data/fha.csv",
  colClasses=c("character","double","double","double"), na.strings="NA")
nrow(fha)
head(fha)
```

---

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

```{r}
ua = read.csv(file="http://www.stat.cmu.edu/~ryantibs/statcomp-S18/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")
```

Summary
===

- Read in data from a previous R session with `readRDS()`, `load()`
- Read in data from the outside with `read.table()`, `read.csv()`
- Can sometimes be tricky to get arguments right in `read.table()`, `read.csv()`; helps sometimes take a look at the original data files to see their structure
- For reordering data, use `order()`, `rev()`, and proper indexing 
- For merging data, use `merge()`; but can do it manually using reordering tricks