Goal

Create a post with similar parameters to the Minnesota DOT’s Covid-19 & Historic Total Traffic Count graph.

Connect to MySQL Database

Load the table into R

counts_daily <- tbl(con, 'counts_daily')
head(counts_daily)

Combining ‘In’ and ‘Out’ Values

Below is the code we created in In Part 5, to combine the ‘I’ and ‘O’ values.

counts_daily_total <- counts_daily %>% 
  group_by(date, bikeometer_id) %>% 
  summarize(count = sum(count), is_weekend = is_weekend, 
            month = month, day = day, year = year, month_day = month_day) %>% 
  collect()
## `summarise()` has grouped output by 'date'. You can override using the `.groups` argument.
counts_daily_total
class(counts_daily_total$month_day)
## [1] "character"

I’m going to manipulate the counts_daily_total ‘tibble’ using the mutate() function.

I realized that I should have used a hyphen instead of an underscore for the ‘month-day’ values so first I need to do some character replacement. I’ll use the mutate() function to make changes to columns and pass in the gsub() function to replace the underscore with a hyphen.

Then, we will add an arbitrary year to the end of month_day to make it easier to convert to a date class and subsequently graph.

counts_daily_total <- counts_daily_total %>% mutate(month_day = gsub("[_]", "-", month_day))

counts_daily_total <- counts_daily_total %>% mutate(month_day=paste(month_day, '-2020', sep = ''))
counts_daily_total