Hawthorne, Tilikum, and Steel Bridges
library(readxl)
library(lubridate)
library(tidyverse)
input_file <- "data/Hawthorne Tilikum Steel daily bike counts 073118.xlsx"
bridge_name <- "Hawthorne"
# define a funtion that load bike counts data
load_data <- function(input_file, bridge_name) {
bikecounts <- read_excel(input_file,
sheet = bridge_name,
skip = 1)
bikecounts$brname <- bridge_name
bikecounts
}
Tilikum <- load_data(input_file, "Tilikum")
Hawthorne <- load_data(input_file, "Hawthorne")
names(Hawthorne) <- names(Tilikum) # use the column names of Tilikum for Hawthorne
Steel <- load_data(input_file, "Steel")
names(Steel) <- c("date", "lower", "westbound", "eastbound", "total", "brname")
# combine all three data frames for all three bridges
bikecounts <- bind_rows(Hawthorne,
Tilikum,
Steel %>% select(-lower)) # exclude the `lower` col in Steel data frame
# weather data
weatherData <- read_csv("data/NCDC-CDO-USC00356750.csv")
## Parsed with column specification:
## cols(
## STATION = col_character(),
## NAME = col_character(),
## DATE = col_date(format = ""),
## PRCP = col_double(),
## SNOW = col_double(),
## SNWD = col_double(),
## TMAX = col_integer(),
## TMIN = col_integer(),
## TOBS = col_character()
## )
weatherData
## # A tibble: 2,768 x 9
## STATION NAME DATE PRCP SNOW SNWD TMAX TMIN TOBS
## <chr> <chr> <date> <dbl> <dbl> <dbl> <int> <int> <chr>
## 1 USC00356~ PORTLAND WEAT~ 2011-01-01 0 0 0 33 29 <NA>
## 2 USC00356~ PORTLAND WEAT~ 2011-01-02 0 0 0 35 28 <NA>
## 3 USC00356~ PORTLAND WEAT~ 2011-01-03 0 0 0 36 27 <NA>
## 4 USC00356~ PORTLAND WEAT~ 2011-01-04 0 0 0 35 27 <NA>
## 5 USC00356~ PORTLAND WEAT~ 2011-01-05 0.36 0 0 39 31 <NA>
## 6 USC00356~ PORTLAND WEAT~ 2011-01-06 0.01 0 0 47 38 <NA>
## 7 USC00356~ PORTLAND WEAT~ 2011-01-07 0.23 0 0 47 32 <NA>
## 8 USC00356~ PORTLAND WEAT~ 2011-01-08 0.02 0 0 41 29 <NA>
## 9 USC00356~ PORTLAND WEAT~ 2011-01-09 0 0 0 41 34 <NA>
## 10 USC00356~ PORTLAND WEAT~ 2011-01-10 0 0 0 41 34 <NA>
## # ... with 2,758 more rows
# join bikeconts and weatherData
bikeCntWeather <- bikecounts %>%
mutate(DATE=as_date(date)) %>%
left_join(weatherData)
## Joining, by = "DATE"
library(ggplot2)
p <- ggplot(data = bikecounts) +
geom_point(mapping = aes(x = date, y = total,color = brname))
p + labs(color="Bridge") + labs(title = "Daily Total Bike Counts")
Bar chart - Average Bike Counts per day for each Bridge
by day of week
bikecounts$dow <- wday(as.Date(bikecounts$date), label = TRUE)
bikeCNTdow <- bikecounts %>%
group_by(dow,brname) %>%
summarise(avgBikesPerDay=mean(total))
# bar chart by dow, bridge
p <- ggplot(bikeCNTdow) +
geom_bar(mapping = aes(x = brname, y = avgBikesPerDay, fill = dow), stat = "identity", position = "dodge")
p + labs(x = "Bridge")
Bike Counts vs. Precipitaion (inches)
ggplot(data = bikeCntWeather) +
geom_point(mapping = aes(x = PRCP, y = total,color = brname)) +
facet_wrap(~ brname, nrow = 1) +
labs(x = "Precipitation") + labs(color = "Bridge")
Bike Counts vs. Minimum Temperature
ggplot(data = bikeCntWeather) +
geom_point(mapping = aes(x = TMIN, y = total,color = brname)) +
facet_wrap(~ brname, nrow = 1) +
labs(x = "Minimum Temperature") + labs(color = "Bridge")
