## Data Structures

Key themes overview:

- how to "look" at your data when you first load it in
- there are two familiar ways of storing tabulated data: in a “wide” format, and in a “long” format. *Wide* data has a column for each variable. 
- you will hear people use different terms like "variable" and "attribute" to describe "features" of data
- you've learned to 'index' using brackets and the `$` symbol, this lesson builds on your familiarity with "indexing" to manipulate data using data.table formatting
- today we will talk about `data.table` which works well with large files and is fast for things like subset, grouping, and joins

--- 

### long vs wide data

As I mentioned, there are typically two ways that "observational, "rectangular" data are typically stored. *Long* and *wide* data. In general, we are focusing on "rectangular" data, i.e. collections of values that are each associated with a variable and an observation. We can quickly think of data types that do not fit this description: images, videos, text, audio, and even sometimes network data. Let's consider the two examples of data organization below.

Which is this? 

Product   Attribute   Value
-------   ---------   -----
A         Height      10
A         Width       5
A         Weight      2
B         Height      20
B         Width       10

And this?

Product  Height  Width  Weight
-------  ------  -----  ------
A        10      5      2
B        20      10     NA

An easy way to spot the difference it to look for categorical data. In the first case we see that the variable "attribute" contains the categories "Weight" and "Height." For each variable to have their own column, as in the *wide* format, we'd want both "weight" and "height" to have their own column, like is shown in table 2. 

Let's get started exploring some data below. Begin with a few packages. You should already have most these on your machine, though you might not have data.table.

```{r}
#libraries

library(ggplot2)
library(tidyverse)
library(dplyr)
library(data.table)
```

Let's start with the flights data. Below, we see the *wide* format, which looks a lot like something you might view in excel.

What does the `fread` function do?

The `fread` function is from the `data.table` package. Use `?fread()` to find out more.
- check out this datacamp article for more information on reading data
- https://www.datacamp.com/community/tutorials/importing-data-r-part-two
- Note that reading in your data with the fread() function returns you a data table:


```{r}
#https://www.listendata.com/2016/10/r-data-table.html

flights = fread("https://github.com/arunsrinivasan/satrdays-workshop/raw/master/flights_2014.csv")
```

Use a few base R commands to explore the data. What do we see?

```{r}
nrow(flights)
```

We might say, depending on how the data is organized (we will examine this with `head` below) that it has over 250,000 cases (or units) and 17 variables (or features) that describe the units.

```{r}
ncol(flights)
```

```{r}
names(flights)
```

Already we are getting a feel for the shape and size of the data. How is it set up?

```{r}
head(flights)
view(flights)
```

Time series data! We can see that each row of the data is providing information about a year or date. Dates are typically considered to be *interval* data.

So in this case, our unit is actually a chunk of time. We will talk more about time series later in the course. For now, let's just investigate the data a bit more. Let's use indexing (or "subsetting") to examine the 'origin' feature (variable, row).

To do this, we take advantage of the data frame set up which is similar to a matrix that reflects the R X C (i.e., ROW * COLUMNS) principle. Below we create an object called `dat1` which takes all of the rows (indicated by a blank space) and only the `origin` column of the data.

```{r}
dat1 = flights[ , origin] # returns a vector

# try to subset on a different column (hint: names() is useful)
names(flights)
foo <- flights[, day]
```

Note: the above line of code returns a vector not a data.table. Try `DT[ , .(variable)]` instead. `.()` is an alias for `list()` and ensures a `data.table` is returned.
To get result in `data.table` format, run the code below :

```{r}
dat1 = flights[ , .(origin)] # returns a data.table

```

How might we quickly see which origin airports are included in the data?

```{r}
table(dat1)
unique(flights$origin)
```

Perhaps we want to view multiple columns at once. The following code tells R to select 'origin', 'year', 'month', 'hour' columns. The space + comma in the beginning tells R to show this for all rows.

```{r}
flights[, .(origin, year, month, hour)]
```

Can you find all the flights whose origin is 'JFK'?

```{r}
# Filter based on one variable
flights[origin == "JFK"]
```

## Fast Data Manipulation with data.table

`data.table` uses binary search algorithm that makes data manipulation faster.

Binary Search Algorithm: Binary search finds a value from a sorted list of values. It repeatedly splits a list that contains values in half, until you found the value that you were searching for. For efficiency, it is useful to set a key in your dataset which tells system that data is sorted by the key column. For example, you have employee’s name, address, salary, designation, department, employee ID. We can use 'employee ID' as a key to search a particular employee.

```{r}
# Indexing (Set Keys)
setkey(flights, origin)
head(flights)
```

What did `setkey()` do? It sorted the data based on flight origin. Setkey sorts a data.table and marks it as sorted with an attribute sorted. The sorted columns are the key. The key can be any number of columns. The columns are always sorted in ascending order. (Reference/see: r documentaiton for more).

We can also sort the data based on a variable of our choice. Or use multiple variables.  Below we can use the `setorder()` function to sort the data based on variables of our choice. We can do this in ascending or desending order.

```{r}
#set order 
mydata0 <- setorder(flights, origin)
mydata1 <- setorder(flights, -origin)
mydata2 <- setorder(flights, origin, -dest)
head(mydata2)
```

What do these show us about the data?

We can also summarize the data using built-in functions like `median`, `min`, `max` and `mean.`

```{r}
min(flights$air_time)
max(flights$air_time)
range(flights$air_time)

flights[, .(mean = mean(arr_delay, na.rm = TRUE),
median = median(arr_delay, na.rm = TRUE),
min = min(arr_delay, na.rm = TRUE),
max = max(arr_delay, na.rm = TRUE))]
```

While all of this sorting is a useful way to get a first look at the data, we might also want to go ahead and begin to summarize the data. This is also a good moment to repeat 'reading' the code structure: ie: DT[where, select|update|do, by]. The way to read it (out loud) is: Take DT, subset rows using i, then calculate j, grouped by "k". "On which rows (i), what to do (j), grouped by what? (k).  [Note: see ArunSrinivasan-DataTable.pdf in BrightSpace.]

```{r}
flights[, .(mean_arr_delay = mean(arr_delay, na.rm = TRUE)), by = origin]
```

# New data: breweries

Now try to load the data below. Take some time to 'look' at the data and get familiar with its structure.

Load in "ncbreweries data" using 'load' or the Rstudio GUI.


```{r}
# load "ncbreweries" data
load("~/ProjectsGit/eda-class-materials/week2/ncbreweries.rdata")

# use the commands from the earlier part of the lesson to check the dimension, set up, and variable names
head(ncbreweries)
dim(ncbreweries)
```

_What do we see?_

In the columns we see attributes that contain information about each Brewery. This information is easy to "read" but might prove difficult, in this format, for plotting and analysis.

"Long" formatted data (versus wide) is useful for visualization because each **variable** (or attribute) is represented by a column.

Using some quick indexing, how would we view the different type of breweries available in the data?

```{r echo=TRUE}
# try to create a new object called "type" that subsets ncbreweries by the "type" variable
temp <- ncbreweries[, type]
temp <- ncbreweries[, "type"]
```

Why didn't the above code work? What does the error indicate?

Answer: because ncbreweries is not a data table!

```{r}
temp <- ncbreweries[, "type"]
```

You might thing you can resolve this by recognizing the 'character' format of 'type.' (See above). That won't work later when we use data.table commands. So:

```{r}
ncbreweries<-as.data.table(ncbreweries)
```

Now, how can we calculate the mean beer count for each type of brewery? (There are five types)?

```{r}
#calculate the mean beer count for each type of brewery:
ncb_mean_bc <- ncbreweries[, .(meanBeerCount = mean(beercount, na.rm=TRUE)), by = type]
```

What if you wanted to know the earliest year each type of brewery was established, and the overall mean beer count for each type of brewery?

```{r}
nc_mean_bc_est <- ncbreweries[, .(meanBeerCount = mean(beercount, na.rm = TRUE),  earliestYear = min(est)), by = type]
```

Now let's think a little about visualization, what is an easy plot for visualizing a trend from this particular data?

```{r}
hist(ncbreweries$est, col="gray", main="",
     xlab="Establishment of Breweries")
```

Earlier we saw that microbreweries were established quite a long time ago, and yet the simple plot above showing the frequencies of observations broken down by time tells us that not many breweries were in north carolina until the last 3 decades.

How can we look at this similar trend across types of breweries?


```{r}
library(lattice)

histogram(~ est | type ,data=ncbreweries,
          layout=c(3,2) #  columns and rows of individual plots
          )
```

## Comparison to Tidy Data - in class exercise

You have already learned about `tidy data` which we will focus on next for the basis of visualization. In general you can do a lot of great things both in base R and in the tidyverse. You should always work on improving both skillsets! 

Now, how can we calculate the mean beer count for each type of brewery? (There are five types)? Using a tidy approach?

```{r}
ncb_mean_bc <- ncbreweries[, .(meanBeerCount = mean(beercount, na.rm = TRUE)), by = type]
ncb_mean_bc

# in tidy:

```

Next, can you make a simple graphic of this datain base R? Try using a barchart.

```{r}

```

----

## homework assignment 
*(Due Thursday):*

Practice summarizing data using data table and making a graphic in base R:

*Question 1*: What is the median beer count for each type of brewery? Compare this with the mean beer count by type (do not use a tidyverse approach).

```{r}
nc_median <- 

# What is the mean beer count by status of brewery? 
nc_mean_status <- 
```

*Question 2*: Could you explore the relationship between breweries' year of establishment and beercount using a graphic from base R? 

```{r}

```

*Part 3*: 

- Complete the readings for visualization (due Thursday too) which will be posted on Brighspace under "week 2."
- Review the references below, try to learn more about reading data into R and data.table on your own. 
- I will administer a brief reading quiz next class on the readings. 

--- 
## references for data.table & today's lecture
*The last one is particularly useful! *

- https://rawgit.com/wiki/Rdatatable/data.table/vignettes/datatable-intro.html
- https://github.com/arunsrinivasan/flights
- https://www.datacamp.com/community/tutorials/data-table-r-tutorial
- https://stackoverflow.com/questions/27886839/what-does-error-object-myvariable-not-found-mean
- https://www.listendata.com/2016/10/r-data-table.html
- https://cran.r-project.org/web/packages/data.table/vignettes/datatable-intro.html
- https://rcompanion.org/handbook/C_04.html
- https://cran.r-project.org/web/packages/data.table/vignettes/datatable-faq.html

