---
title: "Comp/Stat 112: Joining Data"
author: "INSERT STUDENT NAME HERE (COLLABORATOR NAMES HERE)"
output:
  bookdown::html_document2:
    code_download: true
    split_by: none
    toc: yes
    toc_depth: 3
    toc_float:
      toc_collapsed: true
    number_sections: false
---

```{r 09_setup, include=FALSE}
knitr::opts_chunk$set(echo = TRUE, error = TRUE)
library(tidyverse)
library(ggplot2)
library(lubridate)
library(ggmap)
```




```{r}
Grades <- read_csv("https://bcheggeseth.github.io/112_spring_2023/data/grades.csv")
Grades <- Grades %>%
  select(sid, sessionID, grade) %>%
  distinct(sid, sessionID, .keep_all = TRUE)
```

```{r, cache=TRUE}
Courses <- read_csv("https://bcheggeseth.github.io/112_spring_2023/data/courses.csv")
```


## Mutating joins {-}

```{example name="Average class size: varying viewpoints"}
Determine the average class size from the viewpoint of a student (getting an average size for each student and they classes they take) and the viewpoint of the Provost / Admissions Office (getting an average size across all classes).

```

Try yourself and then check online for solution.

> ANSWER: 

```{r}

```

## Filtering joins {-}

```{example, name="semi_join to compare to a filtered summary"}
Find a subset of the `Grades` data that only contains data on the four largest sections in the `Courses` data set.

```

Try yourself and then check online for solution.

> ANSWER: 

```{r}

```


```{example,name="semi_join"}
Use `semi_join()` to create a table with a subset of the rows of `Grades` corresponding to all classes taken in department `J`.

```

Try yourself and then check online for solution.

> ANSWER: 

```{r}

```

## Practice {-}

```{exercise}
Use your wrangling skills to answer the following questions.
``` 

*Hint 1: start by thinking about what tables you might need to join (if any) and identifying the corresponding variables to match.* 
*Hint 2: you'll need an extra table to convert grades to grade point averages. I've given you the code below.*

```{r}
(GPAConversion <- tibble(grade = c("A+", "A", "A-", "B+", "B", "B-", "C+", "C", "C-", "D+", "D", "D-", "NC"), gp = c(4.3, 4, 3.7, 3.3, 3, 2.7, 2.3, 2, 1.7, 1.3, 1, 0.7, 0)))
```

a. How many student enrollments in each department?

> ANSWER: 

```{r}

```

b. What's the grade-point average (GPA) for each student? The average student GPA? *Hint: There are some "S" and "AU" grades that we want to exclude from GPA calculations. What is the correct variant of join to accomplish this?* 

> ANSWER: 

```{r}

```

c. What fraction of grades are below B+?

> ANSWER: 

```{r}

```

d. What's the grade-point average for each instructor? 

> ANSWER: 

```{r}

```

e. Estimate the grade-point average for each department. *We cannot actually compute the correct grade-point average for each department from the information we have. The reason why is due to cross-listed courses. Students for those courses could be enrolled under either department, and we do not know which department to assign the grade to. There are a number of possible workarounds to get an estimate. One would be to assign all grades in a section to the department of the instructor, which we'd have to infer from the data.* For this exercise, start by creating a table with all cross-listed courses. Then use an `anti_join` to eliminate all cross-listed courses. Finally, use an `inner_join` to compute the grade-point average for each department.

> ANSWER: 

```{r}

```



## Bicycle-Use Patterns {-}

In this activity, you'll examine some factors that may influence the use of bicycles in a bike-renting program.  The data come from Washington, DC and cover the last quarter of 2014.

Two data tables are available:

- `Trips` contains records of individual rentals
- `Stations` gives the locations of the bike rental stations


Here is the code to read in the data:^[**Important**: To avoid repeatedly re-reading the files, start the data import chunk with `{r cache = TRUE}` rather than the usual `{r}`.]


```{r cache=TRUE}
data_site <-
  "https://bcheggeseth.github.io/112_spring_2023/data/2014-Q4-Trips-History-Data-Small.rds"
Trips <- readRDS(gzcon(url(data_site)))
Stations <- read_csv("https://bcheggeseth.github.io/112_spring_2023/data/DC-Stations.csv")
```

The `Trips` data table is a random subset of 10,000 trips from the full quarterly data.

### Warm-up: Temporal patterns {-}

It's natural to expect that bikes are rented more at some times of day, some days of the week, some months of the year than others. The variable `sdate` in `Trips` gives the time (including the date) that the rental started.

```{exercise exr-temp, name="Warm-up: temporal patterns"}
Make the following plots and interpret them:
```

(a) A density plot of the events versus `sdate`. Use `ggplot()` and `geom_density()`.

> ANSWER: 

```{r fig.alt='Alt Text Here'}

```

(b) A density plot of the events versus time of day (values from 0 being midnight to 12 noon to 24 being midnight of the full day).  You can use `mutate` with `lubridate::hour()`, and `lubridate::minute()` to extract the hour of the day and minute within the hour from `sdate`. Hint: A minute is 1/60 of an hour, so create a field where 3:30am is 3.5 and 3:45am is 3.75.

> ANSWER: 

```{r fig.alt='Alt Text Here'}

```

(c) A bar plot of the events versus day of the week.

> ANSWER: 

```{r fig.alt='Alt Text Here'}

```

(d) Facet your graph from (b) by day of the week. Is there a pattern?

> ANSWER: 

```{r fig.alt='Alt Text Here'}

```



The variable `client` describes whether the renter is a regular user (level `Registered`) or has not joined the bike-rental organization (`Causal`). Do you think these two different categories of users show different rental behavior? How might it interact with the patterns you found in Exercise \@ref(exr:exr-temp)?

```{exercise name="Customer segmentation"}
Repeat the graphic from Exercise \@ref(exr:exr-temp) (d) with the following changes:
```

(a) Set the `fill` aesthetic for `geom_density()` to the `client` variable. You may also want to set the `alpha` for transparency and `color=NA` to suppress the outline of the density function.

> ANSWER: 

```{r fig.alt='Alt Text Here'}

```

(b) Now add the argument `position = position_stack()` to `geom_density()`. In your opinion, is this better or worse in terms of telling a story? What are the advantages/disadvantages of each?

> ANSWER: 

```{r fig.alt='Alt Text Here'}

```

(c) Rather than faceting on day of the week, create a new faceting variable like this: `mutate(wkday = ifelse(lubridate::wday(sdate) %in% c(1,7), "weekend", "weekday"))`. What does the variable `wkday` represent? Try to understand the code. 

> ANSWER: 

```{r fig.alt='Alt Text Here'}

```

(d) Is it better to facet on `wkday` and fill with `client`, or vice versa?

> ANSWER: 

```{r}

```

(e) Of all of the graphics you created so far, which is most effective at telling an interesting story?

> ANSWER: 

```{r}

```


### Mutating join practice: Spatial patterns {-}

```{exercise, name="Visualization of bicycle departures by station"}
Use the latitude and longitude variables in `Stations` to make a visualization of the total number of departures from each station in the `Trips` data. To layer your data on top of a map, start your plotting code as follows:

```

> ANSWER: 

```{r}
myMap <- get_stamenmap(
  c(-77.1,38.87,-76.975,38.95),
  zoom = 14,
  maptype = "toner-lite") 

ggmap(myMap) 
```



```{exercise}
Only 14.4% of the trips in our data are carried out by casual users.^[We can compute this statistic via `mean(Trips$client=="Casual")`.] Create a map that shows which area(s) of the city have stations with a much higher percentage of departures by casual users. Interpret your map.

```

> ANSWER: 

```{r}

```


### Filtering join practice: Spatiotemporal patterns {-}

```{exercise, name="High traffic points"}
Consider the following
```

(a) Make a table with the ten station-date combinations (e.g., 14th & V St., 2014-10-14) with the highest number of departures, sorted from most departures to fewest. Hint: `as_date(sdate)` converts `sdate` from date-time format to date format.

> ANSWER: 

```{r}

```

(b) Use a join operation to make a table with only those trips whose departures match those top ten station-date combinations from part (a).

> ANSWER: 

```{r}

```

(c) Group the trips you filtered out in part (b) by client type and `wkday` (weekend/weekday), and count the total number of trips in each of the four groups. Interpret your results.

> ANSWER: 

```{r}

```
