preprocess_data.Rmd

---
title: "Preprocess data"
author: "Adam J Campbell"
date: "14/07/2020"
output: html_document
---

This file will help process the census data into the scoring information in the Shiny app (app.R).

You will need to download the individual shapefiles and census data.

Load your packages
```{r}
library(sf)
library(ggplot2)
library(dplyr)
library(rmapshaper)
library(leaflet)
library(tidyr)
```


load commuting data and pre-process 
```{r}
commute_data <- read.csv("raw_data/statsnz2018-census-main-means-of-travel-to-work-by-statistical-area-CSV/2018-census-main-means-of-travel-to-work-by-statistical-area.csv", na.strings = "-999") %>%  # load data treat -999 as NA
  rename(ID_RES = ï..SA2_code_usual_residence_address ) %>%  # do some basic renamings
  rename(ID_WORK = SA2_code_workplace_address) %>%
  select(-c("SA2_name_usual_residence_address",   # drop columns you don't need
    "SA2_usual_residence_easting",
    "SA2_usual_residence_northing",
    "SA2_name_workplace_address",
    "SA2_workplace_easting",
    "SA2_workplace_northing")) %>%
  mutate(ID_RES = as.character(ID_RES)) %>%  # change columns to character type for joins
  mutate(ID_WORK = as.character(ID_WORK)) 
```

Read Statistic area shapefile
```{r}
SA2 <- st_read('raw_data/DCC_SA2.shp') %>% # read data
  #ms_simplify(keep = 0.2) %>% # simplify 
  #st_transform("+proj=longlat +datum=WGS84") %>% # change projection to lat/long
  rename(ID = SA22018_V1) %>%
  rename(ID_NAME = SA22018__1) %>%
  rename(LAND_AREA = LAND_AREA_) %>%
  rename(TOT_AREA = AREA_SQ_KM) %>%
  ms_simplify(keep = 0.2) %>%
  filter(!ID %in% c("349500", "350800", "363900"))
    



SA2_data <- st_drop_geometry(SA2)  # make a df of just data
```


```{r}
join_residence <- left_join(SA2,commute_data, by = c("ID" = "ID_RES"))

# change NA to zero
join_residence[is.na(join_residence)] <- 0

Dunedin_Commuting_from <- join_residence %>% 
  st_drop_geometry() %>%
  group_by(ID) %>%
  summarise(
    Name = ID_NAME, 
    Drive = sum(Drive_a_private_car_truck_or_van) + sum(Drive_a_company_car_truck_or_van) + sum(Passenger_in_a_car_truck_van_or_company_bus),
    Bike = sum(Bicycle),
    Bus = sum(Public_bus),
    Walk = sum(Walk_or_jog),
    WFH = sum(Work_at_home), 
    Total = sum(Total)
    ) %>%
  unique()
```

```{r}
# make total
Dunedin_Summary <- Dunedin_Commuting_from %>%
  ungroup()%>%
  summarise(Total_Drive = sum(Drive),
    Total_Bike = sum(Bike),
    Total_Bus = sum(Bus),
    Total_WFH = sum(WFH),
    Total_Walk = sum(Walk),
    Total_Total = sum(Total),
    pct_drive = Total_Drive/ Total_Total,
    pct_bike = Total_Bike/ Total_Total,
    pct_WFH = Total_WFH/ Total_Total,
    pct_Walk = Total_Walk/Total_Total,
    pct_bus = Total_Bus/Total_Total
    )


Dunedin_Commuting_from_pcts <-Dunedin_Commuting_from %>%
  ungroup()%>%
  summarise(
    ID = ID,
#    Name = Name,
    Total = Total,
#    Drive = Drive,
    drive_pct = Drive/Total,
    bike_pct = Bike/Total,
    bus_pct = Bus/Total,
    onfoot_pct = Walk/Total,
    wfh_pct = WFH/Total,
    
    drive_sum = Drive,
    bike_sum = Bike,
    bus_sum = Bus,
    onfoot_sum = Walk,
    wfh_sum = Drive,
    
    drive_ifaverage = Total * (sum(Drive)/sum(Total)),
    bike_ifaverage = Total * (sum(Bike)/sum(Total)),
    bus_ifaverage = Total * (sum(Bus)/sum(Total)),
    onfoot_ifaverage = Total * (sum(Walk)/sum(Total)),
    wfh_ifaverage = Total * (sum(WFH)/sum(Total)),
    
    drive_diff = Drive - drive_ifaverage,
    bike_diff = Bike - bike_ifaverage,
    bus_diff = Bus - bus_ifaverage,
    onfoot_diff = Walk - onfoot_ifaverage,
    wfh_diff = WFH - wfh_ifaverage,
  )


pivoted_commuting <-Dunedin_Commuting_from_pcts %>%
  pivot_longer(
    cols = -c(ID, Total),
    names_to = c("mode", "type"),
    names_sep = "_",
    values_to = "value"
  )

pivoted_commuting <- pivoted_commuting %>%
  rename(total_sum = Total) %>%
  mutate(direction = "from")

pivoted_commuting <- pivoted_commuting[c(1,2,6,3,4,5)]

```


test total error
```{r}
error_test <- join_residence %>% 
  st_drop_geometry() %>%
  mutate(
    total_all_modes = Work_at_home + Drive_a_private_car_truck_or_van + Drive_a_company_car_truck_or_van + Passenger_in_a_car_truck_van_or_company_bus + Public_bus + Train + Bicycle + Walk_or_jog + Ferry + Other,
    total_tracked_modes = Work_at_home + Drive_a_private_car_truck_or_van + Drive_a_company_car_truck_or_van + Passenger_in_a_car_truck_van_or_company_bus + Public_bus + Bicycle + Walk_or_jog ,
    Total_diff = Total  -total_all_modes,
    tracked_diff = total_all_modes -total_tracked_modes
  )

error_summary <- error_test %>%
  summarise(
    Total = sum(Total),
    Total_diff = sum(Total_diff),
    tracked_diff = sum(tracked_diff))
```

So 	5214 out of 46002 or ~ 11% are not captured in the data here,  I also remove 66 commuting which is 0.1% of all data or 0.2% of the reduced dataset.


```{r}
saveRDS(Dunedin_Summary, file="data/Dunedin_Summary")
saveRDS(pivoted_commuting, file="data/commute_data")
```