Step 1: Install and Load Required Libraries

# Install required packages

install.packages("tidyverse")

install.packages("readxl")

install.packages("lubridate")

# Load the libraries

library(tidyverse)

library(readxl)

library(lubridate)

Step 2: Import Data from Excel Files

# Load the Excel datasets

data_august <- read_excel("~/Desktop/CASE STUDY 1 /3/untitled folder/AUGUST DATA - NEETHU.xlsx")

data_july <- read_excel("~/Desktop/CASE STUDY 1 /3/JUY/Neethuv1 JULY(AutoRecovered).xlsx")

data_september <- read_excel("~/Desktop/CASE STUDY 1 /3/SEP/Sep 2024 neethu data.xlsx")

# Verify that data has been imported correctly 

print(head(data_august))  # Check first few rows of month 1 data

print(head(data_july))  # Check first few rows of month 2 data 

print(head(data_september))  # Check first few rows of month 3 data

Step 3: Inspect and Clean the Data

Inspect Column Names and Data Structure

# Check column names for each file

colnames(data_august)

colnames(data_july)

colnames(data_september)

# Inspect the structure of each file

str(data_august)

str(data_july)

str(data_september)

Rename Columns for Consistency (if necessary)

# Standardize column names across all datasets

# For August data, no renaming needed except for "Ride_length" and "day_of_week"

data_august <- rename(data_august,

                      ride_length = "Ride_length",  # Ensure consistent lowercase

                      weekday = "day_of_week")      # Match with September's Weekday

# For July data, renaming "Source.Name", "Ride_length", and "day_of_week"

data_july <- rename(data_july,

                    ride_length = "Ride_length",     # Ensure consistent lowercase

                    weekday = "day_of_week",         # Match with September's Weekday

                    source_name = "Source.Name")     # Renaming Source.Name

# For September data, rename "Weekday" to match other files

data_september <- rename(data_september,

                         day_of_week = "Weekday")    # Match "Weekday" to "day_of_week"

#verification of changes

colnames(data_august)

colnames(data_july)

colnames(data_september)

More Conversions

library(dplyr)

library(lubridate)  # For date-time conversion

Convert data time columns:
# Convert date-time columns in data_august

data_august <- data_august %>%

    mutate(

        started_at = as.POSIXct(started_at, format="%Y-%m-%d %H:%M:%S", tz="UTC"),

        ended_at = as.POSIXct(ended_at, format="%Y-%m-%d %H:%M:%S", tz="UTC")

    )

# Convert date-time columns in data_july

data_july <- data_july %>%

    mutate(

        started_at = as.POSIXct(started_at, format="%Y-%m-%d %H:%M:%S", tz="UTC"),

        ended_at = as.POSIXct(ended_at, format="%Y-%m-%d %H:%M:%S", tz="UTC")

    )

# Convert date-time columns in data_september

data_september <- data_september %>%

    mutate(

        started_at = as.POSIXct(started_at, format="%Y-%m-%d %H:%M:%S", tz="UTC"),

        ended_at = as.POSIXct(ended_at, format="%Y-%m-%d %H:%M:%S", tz="UTC")

    )

Convert end_station_id Columns: Convert the end_station_id column in each dataset to character type

# Convert end_station_id to character in data_august

data_august <- data_august %>%

    mutate(end_station_id = as.character(end_station_id))

# Convert end_station_id to character in data_july

data_july <- data_july %>%

    mutate(end_station_id = as.character(end_station_id))

# Convert end_station_id to character in data_september

data_september <- data_september %>%

    mutate(end_station_id = as.character(end_station_id))

Convert start_lat Columns: Convert the start_lat column in each dataset to double type for consistency

# Convert start_lat to double in data_august

data_august <- data_august %>%

    mutate(start_lat = as.double(start_lat))

# Convert start_lat to double in data_july

data_july <- data_july %>%

    mutate(start_lat = as.double(start_lat))

# Convert start_lat to double in data_september

data_september <- data_september %>%

    mutate(start_lat = as.double(start_lat))

# Convert start_lng to double in all datasets

data_august <- data_august %>%

    mutate(start_lng = as.double(start_lng))

data_july <- data_july %>%

    mutate(start_lng = as.double(start_lng))

data_september <- data_september %>%

    mutate(start_lng = as.double(start_lng))

# Convert end_lat to double in all datasets

data_august <- data_august %>%

    mutate(end_lat = as.double(end_lat))

data_july <- data_july %>%

    mutate(end_lat = as.double(end_lat))

data_september <- data_september %>%

    mutate(end_lat = as.double(end_lat))

# Convert end_lng to double in all datasets

data_august <- data_august %>%

    mutate(end_lng = as.double(end_lng))

data_july <- data_july %>%

    mutate(end_lng = as.double(end_lng))

data_september <- data_september %>%

    mutate(end_lng = as.double(end_lng))

Step 4: Combine the data sets

# Combine the three datasets into one dataframe

all_trips <- bind_rows(data_august, data_july, data_september)

# Check the structure of the combined dataframe

str(all_trips)

# Optionally, view the first few rows

head(all_trips)

Step 5: Clean and Process the Data

Remove unnecessary columns 

# Remove unnecessary columns

all_trips <- all_trips %>%

  select(-c(start_lat, start_lng, end_lat, end_lng))

Handle missing or incorrect Data 

# Remove rows with missing start/end times

all_trips <- all_trips %>%

  filter(!is.na(started_at) & !is.na(ended_at))  # Ensure there are no missing start/end times

# Convert ride_id to character (if it's not already)

all_trips <- all_trips %>%

  mutate(ride_id = as.character(ride_id))

# Standardize "member_casual" column values

all_trips <- all_trips %>%

  mutate(member_casual = recode(member_casual, "Subscriber" = "member", "Customer" = "casual"))

Add Date-Related Columns

# Ensure 'started_at' is in POSIXct format

all_trips$started_at <- as.POSIXct(all_trips$started_at)

# Add new columns for date, month, day, year, and day of the week

all_trips$date <- as.Date(all_trips$started_at)  # Extract date

all_trips$month <- format(all_trips$date, "%m")  # Extract month

all_trips$day <- format(all_trips$date, "%d")    # Extract day

all_trips$year <- format(all_trips$date, "%Y")   # Extract year

all_trips$day_of_week <- format(all_trips$date, "%A")  # Extract day of the week

Step 6: Calculate Ride Length

# Calculate the ride length in seconds

all_trips$ride_length <- difftime(all_trips$ended_at, all_trips$started_at, units = "secs")

# Convert ride_length to numeric (if necessary)

all_trips$ride_length <- as.numeric(as.character(all_trips$ride_length))

# Remove rides with negative durations

all_trips <- all_trips %>% filter(ride_length > 0)

Step 7: Conduct descriptive Analysis

Summary Statistics for Ride Length 

# Calculate basic summary statistics for ride length

mean_ride_length <- mean(all_trips$ride_length, na.rm = TRUE)

median_ride_length <- median(all_trips$ride_length, na.rm = TRUE)

max_ride_length <- max(all_trips$ride_length, na.rm = TRUE)

min_ride_length <- min(all_trips$ride_length, na.rm = TRUE)

# Display the summary statistics

mean_ride_length

median_ride_length

max_ride_length

min_ride_length

Grouped Analysis by User Type  

# Summary statistics by user type (member vs casual)

user_summary <- all_trips %>%

  group_by(member_casual) %>%

  summarise(

    mean_ride_length = mean(ride_length, na.rm = TRUE),

    median_ride_length = median(ride_length, na.rm = TRUE),

    max_ride_length = max(ride_length, na.rm = TRUE),

    min_ride_length = min(ride_length, na.rm = TRUE)

  )

# Display user_summary

print(user_summary)

Step 8: Visualization

install.packages("munsell")

install.packages("ggplot2")

packageVersion("ggplot2")

install.packages("farver")

Average Ride Length by User Type

# Plot the average ride length by user type

ggplot(all_trips, aes(x = member_casual, y = ride_length, fill = member_casual)) +

  geom_bar(stat = "summary", fun = "mean") +

  labs(title = "Average Ride Length by User Type", x = "User Type", y = "Average Ride Length (secs)")

Number of Rides by Day of the Week

# Plot the number of rides by day of the week

ggplot(all_trips, aes(x = day_of_week, fill = member_casual)) +

  geom_bar() +

  labs(title = "Number of Rides by Day of the Week", x = "Day of Week", y = "Number of Rides")

Histogram of Ride Lengths

# Example: Create all_trips_v2 from an existing data frame

all_trips_v2 <- all_trips  # Replace with your actual data processing steps

# Load required libraries

library(dplyr)

library(ggplot2)

library(lubridate)

# Load your data (adjust the file path and method as needed)

all_trips <- read.csv("path/to/your/data.csv")  # Update with your actual file path

# Ensure you process your data if necessary

# For example, if you are filtering or mutating:

all_trips_v2 <- all_trips %>%

  mutate(ride_length = as.numeric(difftime(ended_at, started_at, units = "secs")),

         day_of_week = weekdays(started_at))  # Create the required columns

# Check if all_trips_v2 is created successfully

print(head(all_trips_v2))  # View the first few rows

# Now run the ggplot commands

ggplot(all_trips_v2, aes(x = ride_length)) +

  geom_histogram(binwidth = 60, fill = "blue", color = "black", alpha = 0.7) +

  labs(title = "Distribution of Ride Lengths", x = "Ride Length (seconds)", y = "Number of Rides") +

  theme_minimal()

Boxplot of Ride Lengths by Member Type

# Check the structure of all_trips_v2

str(all_trips_v2)

# View the first few rows of your data frame

head(all_trips_v2)

# Ensure ride_length is numeric

all_trips_v2$ride_length <- as.numeric(all_trips_v2$ride_length)

# Convert member_casual to a factor if not already

all_trips_v2$member_casual <- as.factor(all_trips_v2$member_casual)

library(ggplot2)

# Create a boxplot for ride_length by member_casual

ggplot(all_trips_v2, aes(x = member_casual, y = ride_length, fill = member_casual)) +

  geom_boxplot() +

  labs(title = "Ride Length Distribution by User Type", x = "User Type", y = "Ride Length (seconds)") +

  theme_minimal()

# Check for NA values

sum(is.na(all_trips_v2$ride_length))

# Optionally remove rows with NA in ride_length

all_trips_v2 <- na.omit(all_trips_v2)

summary(all_trips_v2$ride_length)

Average Ride Duration Over Days of the Week

library(dplyr)

library(ggplot2)

library(lubridate)  # Ensure you have lubridate loaded for wday()

# Create the line plot for average ride duration by day of the week

all_trips_v2 %>% 

  mutate(weekday = wday(started_at, label = TRUE)) %>% 

  group_by(member_casual, weekday) %>% 

  summarise(average_duration = mean(ride_length), .groups = "drop") %>% 

  ggplot(aes(x = weekday, y = average_duration, color = member_casual, group = member_casual)) +

  geom_line(linewidth = 1) +  # Updated from size to linewidth

  geom_point(size = 3) +

  labs(title = "Average Ride Duration by Day of the Week", x = "Day of Week", y = "Average Duration (seconds)") +

  theme_minimal() +

  scale_y_continuous(labels = scales::comma)

Ride Count by Weekday and Member Type

# Ride Count by Weekday and Member Type (Stacked Bar Plot)

all_trips_v2 %>% 

  mutate(weekday = wday(started_at, label = TRUE)) %>% 

  group_by(member_casual, weekday) %>% 

  summarise(number_of_rides = n()) %>% 

  ggplot(aes(x = weekday, y = number_of_rides, fill = member_casual)) +

  geom_bar(stat = "identity") +

  labs(title = "Total Number of Rides by User Type and Day of the Week", x = "Day of Week", y = "Number of Rides") +

  theme_minimal()

Average Ride Length for Each User Type Over Time

# Average Ride Length Over Time

all_trips_v2 %>% 

  mutate(date = as.Date(started_at)) %>%  # Extract date from datetime

  group_by(date, member_casual) %>% 

  summarise(average_duration = mean(ride_length)) %>% 

  ggplot(aes(x = date, y = average_duration, color = member_casual)) +

  geom_line() +

  labs(title = "Average Ride Length Over Time by User Type", x = "Date", y = "Average Ride Length (seconds)") +

  theme_minimal()

Annual Members vs Casual Riders (Pie chart)

library(dplyr)

library(ggplot2)

# Summarize data: Count of rides by user type and calculate percentage

percentage_data <- all_trips_v2 %>% 

  group_by(member_casual) %>% 

  summarise(number_of_rides = n()) %>% 

  mutate(percentage = (number_of_rides / sum(number_of_rides)) * 100)  # Calculate percentage

# Create pie chart for percentage of rides by user type

ggplot(percentage_data, aes(x = "", y = percentage, fill = member_casual)) +

  geom_bar(stat = "identity", width = 1) +  # Create the pie chart

  coord_polar("y") +  # Convert to polar coordinates for pie chart

  labs(title = "Annual Members vs Casual Riders", 

       fill = "User Type",

       y = "Percentage of Rides") +

  theme_void() +  # Use a void theme for a cleaner look

  theme(plot.title = element_text(hjust = 0.5, size = 16, face = "bold")) +  # Center title

  geom_text(aes(label = paste0(round(percentage, 1), "%")), 

            position = position_stack(vjust = 0.5), 

            color = "white")  # Add percentage labels to the pie slices

Quarterly Trip Totals (July, August, September)

# Filter data for Q3 (July, August, September)

q3_data <- all_trips_v2 %>%

  filter(month(started_at) %in% c(7, 8, 9))

# Summarize total rides for members vs casual riders in Q3

q3_totals <- q3_data %>%

  group_by(member_casual) %>%

  summarise(total_rides = n())

# Visualize total rides by user type

ggplot(q3_totals, aes(x = member_casual, y = total_rides, fill = member_casual)) +

  geom_bar(stat = "identity") +

  labs(title = "Total Rides in Q3 (July, August, September)", x = "User Type", y = "Total Rides") +

  theme_minimal() +

  scale_y_continuous(labels = scales::comma)

Median Ride Length by User Type

library(dplyr)

library(ggplot2)

library(lubridate)  # Make sure to load lubridate for month() function

# Check if all_trips_v2 is loaded

if (exists("all_trips_v2")) {

  # Filter data for Q3 (July, August, September)

  q3_data <- all_trips_v2 %>%

    filter(month(started_at) %in% c(7, 8, 9))

  # Check if filtered data is valid

  if (nrow(q3_data) > 0) {

    # Calculate median ride length for members and casual riders

    q3_median_ride_length <- q3_data %>%

      group_by(member_casual) %>%

      summarise(median_ride_length = median(ride_length, na.rm = TRUE))  # Remove NA values

    # Visualize median ride length for members vs casual riders

    ggplot(q3_median_ride_length, aes(x = member_casual, y = median_ride_length, fill = member_casual)) +

      geom_bar(stat = "identity") +

      labs(title = "Median Cyclist Bike Ride Length", x = "User Type", y = "Median Ride Length (seconds)") +

      theme_minimal() +

      scale_y_continuous(labels = scales::comma)  # Format y-axis with commas

  } else {

    print("Filtered data for Q3 is empty.")

  }

} else {

  print("Dataset 'all_trips_v2' is not loaded.")

}

Ride Duration by Day of the Week

# Add a weekday column

q3_data <- q3_data %>%

  mutate(weekday = wday(started_at, label = TRUE))

# Summarize median ride length by day of week and user type

q3_ride_duration_by_day <- q3_data %>%

  group_by(member_casual, weekday) %>%

 summarise(median_ride_length = median(ride_length, na.rm = TRUE), .groups = 'drop')  # Add .groups argument to avoid warning

# Visualize median ride length by day of the week

ggplot(q3_ride_duration_by_day, aes(x = weekday, y = median_ride_length, color = member_casual, group = member_casual)) +

  geom_line(linewidth = 1) +

  geom_point(size = 3) +

  labs(title = "Median Ride Length by Day of Week", x = "Day of Week", y = "Median Ride Length (seconds)") +  # Updated title

  theme_minimal() +

  scale_y_continuous(labels = scales::comma)

Bike Type Preferences 

# Summarize bike type preferences by user type

q3_bike_type_pref <- q3_data %>%

  group_by(member_casual, rideable_type) %>%

  summarise(total_rides = n(), .groups = 'drop')  # Added .groups argument to avoid warning

# Visualize bike type preferences by user type

ggplot(q3_bike_type_pref, aes(x = rideable_type, y = total_rides, fill = member_casual)) +

  geom_bar(stat = "identity", position = "dodge") +

  labs(title = "Bike Type Preferences", x = "Bike Type", y = "Total Rides") +  # Updated title

  theme_minimal() +

  scale_y_continuous(labels = scales::comma)

Total Rides by Day of the Week (Casual Riders vs Annual Members)

# Summarize total rides by day of week and user type

q3_rides_by_day <- q3_data %>%

  group_by(member_casual, weekday) %>%

  summarise(total_rides = n(), .groups = 'drop')  # Added .groups argument to avoid warning

# Visualize total rides by day of the week

ggplot(q3_rides_by_day, aes(x = weekday, y = total_rides, fill = member_casual)) +

  geom_col(position = "dodge") +

  labs(title = "Total Rides by the Day of the Week", x = "Day of Week", y = "Total Rides") +  # Updated title

  theme_minimal() +

  scale_y_continuous(labels = scales::comma)