1. How many members and casuals customers do we have? What are their proportions of total trips?
2. Calculate the average ride lengths for members and casuals respectively.
3. Calculate the most common starting and ending stations for casuals and members respectively.
4. Are there something relevant on any days of the week by category?
5. Can we see any preference for the rideable type for casuals and members?

library("tidyverse")
library("lubridate")
library("rgdal")
library("markdown")
library("maps")
library("sqldf")
library("ggrepel")
library("sp")

setwd("/home/Documents/Google_course/tripdata_2020_09_2021_08_xml/")

sep_20 <- read.csv("202009-divvy-tripdata.csv", sep=",")
oct_20 <- read.csv("202010-divvy-tripdata.csv", sep=",")
nov_20 <- read.csv("202011-divvy-tripdata.csv", sep=",")
dec_20 <- read.csv("202012-divvy-tripdata.csv", sep=",")
jan_21 <- read.csv("202101-divvy-tripdata.csv", sep=",")
feb_21 <- read.csv("202102-divvy-tripdata.csv", sep=",")
mar_21 <- read.csv("202103-divvy-tripdata.csv", sep=",")
apr_21 <- read.csv("202104-divvy-tripdata.csv", sep=",")
may_21 <- read.csv("202105-divvy-tripdata.csv", sep=",")
jun_21 <- read.csv("202106-divvy-tripdata.csv", sep=",")
jul_21 <- read.csv("202107-divvy-tripdata.csv", sep=",")
aug_21 <- read.csv("202108-divvy-tripdata.csv", sep=",")

df <- do.call("rbind", list(sep_20, oct_20, nov_20, dec_20, jan_21, feb_21, mar_21, apr_21, may_21, jun_21, jul_21, aug_21))

View(head(df))

df <- df %>%
  mutate(started_at = as_datetime(df$started_at, format = "%d/%m/%Y %H:%M")) %>%
  mutate(ended_at = as_datetime(df$ended_at, format = "%d/%m/%Y %H:%M")) %>%
  mutate(ride_length = as.difftime(df$ride_length, format = "%H:%M:%S"))

mean_ride_length <- as.numeric(mean(df$ride_length))/60
cat("Average ride length from 2020-09 to 2021-08:",mean_ride_length,"minutes.")
max_ride_length <- as.numeric(max(df$ride_length))/3600
cat("The longest ride from 2020-09 to 2021-08:",max_ride_length,"hours.")

Average ride length from 2020-09 to 2021-08: 22 minutes.
The longest ride from 2020-09 to 2021-08: 24 hours.

day_week_total <- sqldf("SELECT day_of_week, member_casual, COUNT(day_of_week) AS Total
                 FROM df
                 GROUP BY member_casual, day_of_week
                 ORDER BY day_of_week DESC", 
method = "auto")

members_start_geo <- sqldf("SELECT member_casual, start_station_name AS Start_Station, 
                start_lat AS Start_Latitude,
                start_lng As Start_Longitude, COUNT(start_station_name) AS Num_Trips
                FROM df
                WHERE start_station_name IS NOT ''
                AND member_casual = 'member'
                GROUP BY start_station_name
                ORDER BY COUNT(start_station_name) DESC
                LIMIT 5", 
method = "auto")

casuals_start_geo <- sqldf("SELECT member_casual, start_station_name AS Start_Station, 
                start_lat AS Start_Latitude, start_lng As Start_Longitude,
                COUNT(start_station_name) AS Num_Trips
                FROM df
                WHERE start_station_name IS NOT ''
                AND member_casual = 'casual'
                GROUP BY start_station_name
                ORDER BY COUNT(start_station_name) DESC
                LIMIT 5", 
method = "auto")

start_all_geo <- rbind(members_start_geo, casuals_start_geo)
View(start_all_geo)

members_end_geo <- sqldf("SELECT member_casual, end_station_name AS End_Station, 
                end_lat AS End_Latitude,
                end_lng As End_Longitude, COUNT(end_station_name) AS Num_Trips
                FROM df
                WHERE end_station_name IS NOT ''
                AND member_casual = 'member'
                GROUP BY end_station_name
                ORDER BY COUNT(end_station_name) DESC
                LIMIT 5", 
method = "auto")

casuals_end_geo <- sqldf("SELECT member_casual, end_station_name AS End_Station, 
                end_lat AS End_Latitude, end_lng As End_Longitude,
                COUNT(end_station_name) AS Num_Trips
                FROM df
                WHERE end_station_name IS NOT ''
                AND member_casual = 'casual'
                GROUP BY end_station_name
                ORDER BY COUNT(end_station_name) DESC
                LIMIT 5", 
method = "auto")

end_all_geo <- rbind(members_end_geo, casuals_end_geo)
View(end_all_geo)

start_all_geo$Start_Latitude = as.numeric(gsub(",",".",start_all_geo$Start_Latitude,fixed=TRUE))
start_all_geo$Start_Longitude = as.numeric(gsub(",",".",start_all_geo$Start_Longitude,fixed=TRUE))

end_all_geo$End_Latitude = as.numeric(gsub(",",".",end_all_geo$End_Latitude, fixed=TRUE))
end_all_geo$End_Longitude = as.numeric(gsub(",",".",end_all_geo$End_Longitude, fixed=TRUE))

chicago_map <- readOGR(dsn="/home/david/Documents/Google_course/Course/Case 1/map_chicago", layer="geo_export_8079d6b6-c882-4cf2-8acb-20d99ac6850f")
chicago_df = fortify(chicago_map)

start_map <-ggplot() +
    geom_polygon(data = chicago_df, aes(x = long, y=lat , group = group), colour = 'grey', 
    fill = 'chartreuse4', size = .2) +
    geom_point(data = start_all_geo,
             aes(x = Start_Longitude, y = Start_Latitude, size = Num_Trips, color = member_casual), 
             alpha = 1) +
    geom_label_repel(data = start_all_geo,
                   aes(x = Start_Longitude, y = Start_Latitude, label = Start_Station),
                   size = 3,
                   box.padding   = 0.4, 
                   point.padding = 0.65,
                   segment.color = 'yellow') +
  scale_colour_manual(values=c(member = 'brown2',  casual= 'blueviolet'))+
  facet_wrap(~member_casual) +
  labs(title = "Top 5 Starting Stations", size = 'Nº Trips',
       color = 'Rider Type') +
  coord_cartesian(xlim = c(-87.7, -87.55), ylim = c(41.85, 41.95))+
  theme(plot.title = element_text(hjust = 0.5)) +
  theme(panel.background = element_rect(fill = "lightblue")) +
        theme(panel.border = element_blank(),
        panel.grid.major = element_blank(),
        panel.grid.minor = element_blank())
start_map

end_map <- ggplot() +
    geom_polygon(data = chicago_df, aes(x = long, y=lat , group = group), colour = 'grey', 
    fill = 'chartreuse4', size = .2) +
  geom_point(data = end_all_geo,
             aes(x = End_Longitude, y = End_Latitude, size = Num_Trips, color = member_casual),
             alpha = 1) +
  geom_label_repel(data = end_all_geo,
                   aes(x = End_Longitude, y = End_Latitude, label = End_Station),
                   size = 3,
                   box.padding   = 0.4, 
                   point.padding = 0.65,
                   segment.color = 'yellow') +
  scale_colour_manual(values=c(member = 'brown2',  casual= 'blueviolet')) +
  facet_wrap(~member_casual) +
  labs(title = "Top 5 Ending Stations.", size = 'Nº Trips',
       color = 'Rider Type') +
  coord_cartesian(xlim = c(-87.7, -87.55), ylim = c(41.85, 41.95)) + 
    theme(plot.title = element_text(hjust = 0.5)) +
    theme(panel.background = element_rect(fill = "lightblue")) +
    theme(panel.border = element_blank(),
    panel.grid.major = element_blank(),
    panel.grid.minor = element_blank())
end_map

day_week_total$day_of_week[day_week_total$day_of_week == "1"] <- "Sunday"
day_week_total$day_of_week[day_week_total$day_of_week == "2"] <- "Monday"
day_week_total$day_of_week[day_week_total$day_of_week == "3"] <- "Tuesday"
day_week_total$day_of_week[day_week_total$day_of_week == "4"] <- "Wednesday"
day_week_total$day_of_week[day_week_total$day_of_week == "5"] <- "Thursday"
day_week_total$day_of_week[day_week_total$day_of_week == "6"] <- "Friday"
day_week_total$day_of_week[day_week_total$day_of_week == "7"] <- "Saturday"

day_week_total$day_of_week <- factor(day_week_total$day_of_week, levels = rev(unique(day_week_total$day_of_week)), ordered=TRUE)

day_week_total <- day_week_total %>%
  arrange(day_of_week, rev(member_casual)) %>%
  group_by(day_of_week) %>%
  mutate(GTotal = cumsum(Total) - 0.5 * Total)

View(day_week_total)

yearly_plot <- ggplot(data = day_week_total, aes(x = day_of_week, y = Total, fill = member_casual)) +
             scale_fill_manual(values=c(member = 'brown2',  casual= 'blueviolet')) +
  geom_col() +
  geom_text(aes(y = GTotal, label = Total), vjust = 1.5, colour = "white") +
  labs(title = "Total rides per day of week from 2020-09 to 2021-08", x = "Day of Week",
       y = "Total Rides", fill = "Rider Type") +
  scale_y_continuous(labels = function(x) format(x, scientific = FALSE)) +
      theme(plot.title = element_text(hjust = 0.5)) 
yearly_plot

bike_type <- sqldf("SELECT rideable_type, member_casual, COUNT(rideable_type) as number_of_uses
                 FROM df
                 GROUP BY member_casual, rideable_type
                 ORDER BY count(rideable_type) DESC", 
method = "auto" )

View(bike_type)

bike_type$rideable_type[bike_type$rideable_type == "classic_bike"] <- "Classic Bike"
bike_type$rideable_type[bike_type$rideable_type == "docked_bike"] <- "Docked Bike"
bike_type$rideable_type[bike_type$rideable_type == "electric_bike"] <- "Electric Bike"

View(bike_type)

bike_type_plot <- ggplot(data = bike_type, aes(x = rideable_type, y = number_of_uses, fill = member_casual)) +
  scale_fill_manual(values=c(member = 'brown2',  casual= 'blueviolet')) +
  geom_col(position = "dodge") +
  geom_text(aes(label = number_of_uses),  vjust = -0.3 ,colour = "black", 
            position = position_dodge(.9)) +
  labs(title = "Total Rides Per Rideable Type from 2020-09 to 2021-08", x = "Rideable Type",
       y = "Total Rides", fill = "Rider Type") +
  scale_y_continuous(labels = function(x) format(x, scientific = FALSE)) +
      theme(plot.title = element_text(hjust = 0.5)) 
bike_type_plot