## Class

Reopen the `vis-for-gds` project you created in Session 1 and save this `.Rmd` to the `reports` folder. When working through the materials from this session run each code chunk separately by clicking the small green arrow beside each code chunk. By default I have set `eval=FALSE` on most code chunks, so when you knit the code chunk will not execute. We are working with a reasonably large dataset so this is to avoid the tedium of waiting long periods for this document to knit.

The technical element to this session involves importing, describing and processing data from a large bikeshare scheme -- New York's [Citibike](https://www.citibikenyc.com/) scheme.


### Setup

The code block below points to the packages you need to do the work discussed in the session. If you have been following the instructions from  the last session, you will have already installed the key packages -- `tidyverse` , `fst`, `lubridate`, `sf`, `here` -- and so simply need to make them available in session with `library(<package-name>)`. If you wish to use the [bikedata](https://github.com/ropensci/bikedata) package for accessing bikeshare data you will also need to **install** it. This requires the development version of the package which can be installed with `install_github()` using the `devtools` package. As `devtools` is only used in  on setup, its functions are accessed explicitly with `::`, e.g.  `devtools::` rather than `library<package-name>`.

IMPORTANT NOTE : I wanted to include the code for downloading and processing data via the `bikedata` API for information. However, as the package makes available large and reasonably messy data, it is not exactly "entry-level". Depending on how R and existing packages are configured on your machine, you may find several `error` messages relating to package dependencies -- other packages that need to be installed. For this reason I recommend that the code on importing the raw bike data (`import-raw`) be treated as optional (for the interested reader) and instead download the pre-processed data I have made available on a separate repo (`import-collected`).

Required packages necessary for all aspects of the session (**ALL**). If you have not yet downloaded these packages you will need to make separate calls to `install.packages("<package-name>")` as described in the previous session.

```{r setup-required, echo=TRUE, eval=FALSE, message=FALSE}
library(tidyverse) # Bundle of packages for doing modern data analysis.
library(fst) # Fast/efficient working with tbls.
library(sf) # Spatial operations
library(lubridate) # Work with dates
library(here) # For navigating project directory
# install.packages("geosphere") # Uncomment to install.
```

Packages necessary for working with `bikedata` (optional for **advanced R programmer only**):

```{r setup-optional, echo=TRUE, eval=FALSE, message=FALSE}
# remotes::install_github("ropensci/bikedata") # API to TfL's trip data. Uncomment to run.
library(bikedata)
# install.packages(DBI) # Uncomment to install.
# install.packages("SQLite") # Uncomment to install.
```

### Import

#### Import raw data (**interested/advanced only**)

You might notice that available via the `bikedata` package are trip and occupancy data for a number of bikeshare schemes. To see which are available, type `bike_cities()` into the Console. We will collect data for New York's [citibike](https://www.citibikenyc.com/) scheme using the `dl_bikedata()` function. Querying the documentation (with `?dl_bikedata()`) you will notice that there are several parameters to set here: the city/ scheme (`city = "citibike"`), time period (`dates = 2020` - we'll look at data from 2020). By default data are downloaded to a temporary directory `temp_dir()` and held in memory.  `bikedata` also has a function for converting downloaded data to an SQLite3 database. SQLite databases can be called from `R` and have various advantages for efficient storage and querying.

The code block below stores the data held in-memory to an SQLite database with `store_bikedata()`.
The [`fst`](https://www.fstpackage.org/) package is then used for serializing and reading in the these data. `fst` implements in the background various operations such as multi-threading to reduce load on disk space -- and therefore makes it possible to work with large datasets in-memory in `R`. Data are then written out to disk with a `.fst` and then read back into R. This code block takes some time to run and there is also a problem with how `bikedata` handles the New York record data. The code below is therefore for illustrative purposes -- should you wish to work with other bikeshare data. Instead jump to the next code block where we download a version of the New York data from from an external repository that I have created.


```{r import-raw, echo=TRUE, eval=FALSE, message=FALSE}
# Create subdirectory in data folder for storing bike data.
if(!dir.exists(here("data", "bikedata"))) dir.create(here("data", "bikedata"))
# Download data for June 2020.
dl_bikedata (city = "citibike",  data_dir = here("data", "bikedata"), dates=202006)
# Read in and store in SQLite3 database.
bikedb <- file.path (tempdir (), "bikedb.sqlite") # Create sqlite db container.
store_bikedata (data_dir = here("data", "bikedata"), bikedb = bikedb)
# Index dbase to speed up working.
index_bikedata_db(bikedb = bikedb)
# Access db with dbplyr
db <- dplyr::src_sqlite(bikedb, create=F)
# Check tables that form the dbase.
dplyr::src_tbls(db)
# Collect trips and stations tables for writing out to fst.
trips <- tbl(db, "trips") %>%  collect()
stations <- tbl(db, "stations") %>%  collect()
# Write trips out to .fst.
write_fst(trips, here("data", "ny_trips.fst"))
# Write stations out to .csv
write_csv(stations, here("data", "ny_stations.csv"))
# Clean workspace
bike_rm_db(bikedb)
rm(db,stations, trips, bikedb)
```

#### Import pre-processed data (**All**)

Run the code block below to download the pre-processed and extracted data to your project's data folder. Note that you should only run the import code below once. When returning to this work in a new R session, you need only then read in the `ny_trips.fst` and `ny_stations.csv` file that is held locally on your machines. Unfortunately due to storage limitations I had to make `ny_trips.fst` just a 500k random sample of the c.2million records that appear in the session.

```{r import-collected, echo=TRUE, eval=FALSE, message=FALSE}
# Create subdirectory in data folder for storing bike data.
if(!dir.exists(here("data", "bikedata"))) dir.create(here("data", "bikedata"))

# Read in .csv file of stations data from url.
tmp_file <- tempfile()
url <- "https://www.roger-beecham.com/datasets/ny_stations.csv"
curl::curl_download(url, tmp_file, mode="wb")
ny_stations <- read_csv(tmp_file)

# Read in .fst file of trips data from url.
tmp_file <- tempfile()
url <- "https://www.roger-beecham.com/datasets/ny_trips.fst"
curl::curl_download(url, tmp_file, mode="wb")
ny_trips <- read_fst(tmp_file)

# Write out to subdirectory for future use.
write_fst(ny_trips, here("data", "ny_trips.fst"))
write_csv(ny_stations, here("data", "ny_stations.csv"))

# Clean workspace.
rm(url, tmp_file)
```
The code that you would run for reading in the data from your local directory.

```{r read-collected, echo=TRUE, eval=FALSE, message=FALSE}
# Read in these local copies of the trips and stations data.
ny_trips <- read_fst(here("data", "ny_trips.fst"))
ny_stations <- read_csv(here("data", "ny_stations.csv"))
```

### Describe

The code block below performs some useful recoding operations:  convert `chr` fields to `int` for efficient storage, temporal fields `dttm` objects, and longitude and latitude to `dbl` so that we can do calculations.

```{r recode, echo=TRUE, eval=FALSE, message=FALSE}
ny_trips <- ny_trips %>%
  select(-c(city)) %>%
  mutate_at(vars(start_station_id, end_station_id), ~as.integer(str_remove(., "ny"))) %>%
  mutate_at(vars(start_time, stop_time), ~as.POSIXct(., format="%Y-%m-%d %H:%M:%S")) %>%
  mutate(
    bike_id=as.integer(bike_id),
    # birth_year=year(as.POSIXct(birth_year, format="%Y")),
    gender=case_when(
      gender == 0 ~ "unknown",
      gender == 1 ~ "male",
      gender == 2 ~ "female")
  )

ny_stations <- ny_stations %>%
  select(-city) %>%
  mutate(stn_id=as.integer(str_remove(stn_id, "ny"))) %>%
  mutate_at(vars(longitude, latitude), ~as.double(.))
```

### Transform

An example application of `dplyr` for counting:

```{r dplyr-count, echo=TRUE, eval=FALSE, message=FALSE}
ny_trips %>%
  group_by(gender) %>%
  summarise(count=n()) %>%
  arrange(desc(count))
```
An example application of `dplyr` for computing over aggregates:

```{r dplyr-summarise-user, echo=TRUE, eval=FALSE, message=FALSE}
ny_trips %>%
  group_by(user_type) %>%
  summarise(
    count=n(),
    avg_duration=mean(trip_duration/60),
    median_duration=median(trip_duration/60),
    sd_duration=sd(trip_duration/60),
    min_duration=min(trip_duration/60),
    max_duration=max(trip_duration/60)
    ) %>%
  arrange(desc(count))
```

Example code for generating a temporal summary by gender:

```{r dplyr-count-temporal, echo=TRUE, eval=FALSE, message=FALSE}
ny_temporal <- ny_trips %>%
  mutate(
    day=wday(start_time, label=TRUE),
    hour=hour(start_time)) %>%
  group_by(gender, day, hour) %>%
  summarise(count=n()) %>%
  ungroup()

ny_temporal %>%
  filter(gender!="unknown") %>%
  group_by(gender) %>%
  mutate(gender_count=sum(count)) %>%
  group_by(gender, day) %>%
  summarise(count=sum(count), prop=count/first(gender_count)) %>%
  select(gender, day, prop) %>%
  pivot_wider(names_from=gender, values_from=prop)
```

`dplyr` code with `ggplot2` for exploring temporal travel behaviours by gender visually:

```{r dplyr-temporal-plot, echo=TRUE, eval=FALSE, message=FALSE}
ny_temporal %>%
  filter(gender!="unknown") %>%
  ggplot(aes(x=hour, y=count, group=gender)) +
  geom_line(aes(colour=gender), size=1.1) +
  scale_colour_manual(values=c("#e31a1c", "#1f78b4")) +
  facet_wrap(~day, nrow=1)+
  labs(
    title="Citibike trip counts by gender",
    subtitle="--Jun 2020",
    caption="Data provided and owned by: NYC Bike Share, LLC and Jersey City Bike Share, LLC",
    x="", y="trip counts"
    )
```

Code for calculating approximate distance travelled by od_pair.

First we generate a table of distinct ODs that have been cycled and from here calculate approximate distance travelled:

```{r calculate-dist, echo=TRUE, eval=FALSE, message=FALSE}
ny_trips <- ny_trips %>%
  mutate(duration_minutes=as.numeric(as.duration(stop_time-start_time),"minutes"))

od_pairs <- ny_trips %>% select(start_station_id, end_station_id) %>% unique() %>%
  left_join(ny_stations %>% select(stn_id, longitude, latitude), by=c("start_station_id"="stn_id")) %>%
  rename(o_lon=longitude, o_lat=latitude) %>%
  left_join(ny_stations %>% select(stn_id, longitude, latitude), by=c("end_station_id"="stn_id")) %>%
  rename(d_lon=longitude, d_lat=latitude) %>%
  rowwise() %>%
  mutate(dist=geosphere::distHaversine(c(o_lat, o_lon), c(d_lat, d_lon))/1000) %>%
  ungroup()
```
The `left_join` trips on distance in od_pairs data frame:

```{r join-dist, echo=TRUE, eval=FALSE, message=FALSE}
ny_trips <- ny_trips %>%
  mutate(od_pair=paste0(start_station_id,"-",end_station_id)) %>%
  left_join(od_pairs %>%
              mutate(od_pair=paste0(start_station_id,"-",end_station_id)) %>%
              select(od_pair, dist)
            )
```

The function to calculate age from year of birth:

```{r calculate-age, echo=TRUE, eval=FALSE, message=FALSE}
# Function for calculating time elapsed between two dates in years (age).
get_age <- function(yob, yref) {
    period <- lubridate::as.period(lubridate::interval(yob, yref),unit = "year")
    return(period$year)
}

ny_trips <- ny_trips %>% # Take the ny_trips data frame.
  mutate(
    age=get_age(as.POSIXct(birth_year, format="%Y"), as.POSIXct("2020", format="%Y")) # Calculate age from birth_date.
    )
````

The code for plotting trip speeds (approximate) by age, gender and distance travelled.

```{r plot-speed, echo=TRUE, eval=FALSE, message=FALSE}
# Utility trips -- peak-time, rush hour, subscriber, <60 mins, filter very old.
temp <- ny_trips %>%
  mutate(day=wday(start_time, label=TRUE), is_weekday=as.numeric(!day %in% c("Sat", "Sun"))) %>%
  filter(
    is_weekday==1,
    start_station_id!=end_station_id,
    duration_minutes<=60,
    user_type=="Subscriber",
    between(age, 16, 74),
    gender!="unknown") %>%
  mutate(
    dist_bands=case_when(
      dist < 1.5 ~ "<1.5km",
      dist < 3 ~ ">1.5-3km",
      dist < 4.5 ~ ">3-4.5km",
      TRUE ~ ">4.5km"),
    age_band=if_else(age %% 10 > 4, ceiling(age/5)*5, floor(age/5)*5),
    speed=dist/(duration_minutes/60)
  ) %>%
  group_by(gender, age_band, dist_bands) %>%
  summarise(speed=mean(speed), n=n())

temp %>%
  ggplot(aes(x=age_band, y=speed))+
  geom_line(aes(colour=gender))+
  facet_wrap(~dist_bands, nrow=1)

rm(temp)
```

## Homework

## Task 1: Describe data

Complete the data description table below:

| Variable name  | Variable value   | Measurement level |
|----------------|------------------|-------------------|
| `name`         | "Central Park"   |  <enter here>     |
| `capacity`     |  80              |                   |
| `rank_capacity`|  45              |                   |
| `date_opened`  |  "2014-05-23"    |                   |
| `longitude`    | -74.00149746     |                   |
| `latitude`     | 40.74177603      |                   |



## Task 2: Diagnose data

Provide a layout for a tidy version of the tables below.

### UK General Election Results 2019

| party                   | percent_vote     | num_mps    |
|-------------------------|------------------|------------|
| Conservative            | 43.6             |  365       |
| Labour                  | 32.2             |  202       |
| Scottish National Party | 3.9              |  48        |
| Liberal Democrats       | 11.6             |  11        |
| Democratic Union Party  | 0.8              |  9         |

### UK General Election Results 2017 and 2019

| party                   | percent_vote_2017 | num_mps_2017 | percent_vote_2019 | num_mps_2019 |
|-------------------------|-------------------|--------------| ------------------|--------------|
| Conservative            | 42.4              | 317          | 43.6              | 365          |
| Labour                  | 40.0              | 262          | 32.2              | 202          |
| Scottish National Party | 3.0               | 35           | 3.9               | 48           |
| Liberal Democrats       | 7.4               | 12           | 11.6              | 11           |
| Democratic Union Party  | 0.9               | 10           | 0.8               | 8            |


### Task 3: Fix data

The two untidy datasets can be found at:

* [`ny_spread_rows`](https://www.roger-beecham.com/datasets/ny_spread_rows.csv)
* [`ny_spread_columns`](https://www.roger-beecham.com/datasets/ny_spread_columns.csv)


Display your code for fixing the `ny_spread_rows` dataset below and copy and paste the output that was returned to the console.

```{r fix-ny-spread-rows, echo=TRUE, eval=FALSE, message=FALSE}
# Place code for tidying here.

# Copy and paste the RConsole output here.

```

Display your code for fixing the `ny_spread_columns` dataset below and copy and paste the output that was returned to the console.

```{r fix-ny-spread-columns, echo=TRUE, eval=FALSE, message=FALSE}
# Place code for tidying here.

# Copy and paste the RConsole output here.

```

### Task 4: Compute from data

Display your code in the block below

```{r compute-ny-fixed-data, echo=TRUE, eval=FALSE, message=FALSE}
# Place code for computing here.

# Copy and paste the RConsole output here.

```
