Cyclistic Daily and Weekly Usage of Services
Casual Users vs. Members
Dashboard
Cyclistic Daily and Weekly Usage of Services
Casual Users vs. Members
Dashboard
Cyclistic is a bike-share service operating in Chicago, providing bikes for public use on a subscription or casual basis. This report analyzes Cyclistic's bike usage data, focusing on user behavior differences between members and casual riders. The analysis aims to answer three main questions regarding usage patterns over a one-year period from June 2023 to June 2024.
To compare the behavior of members and casual riders.
To determine if there is a significant difference between the usage of members and casual riders at different hours of the day.
To determine if there is a significant difference between the usage of members and casual riders on different days of the week.
The data was sourced from Divvy Trip Data and comprised 12 zip files, each representing one month of trip data from June 2023 to June 2024. Each file contained the following columns:
ride_id: Unique ID for each ride.
rideable_type: Type of bike used (electric_bike, classic_bike, docker_bike).
started_at: Session start date and time.
ended_at: Session end date and time.
start_station_name: Session starting station name.
start_station_id: Session starting station ID.
end_station_name: Session ending station name.
end_station_id: Session ending station ID.
start_lat: Starting latitude.
start_lng: Starting longitude.
end_lat: Ending latitude.
end_lng: Ending longitude.
member_casual: Indicates if the user is a member or a casual rider.
Creating New Columns:
Ride Duration (minutes): Calculated as the difference between ended_at and started_at, expressed in minutes.
Hour of the Ride: Extracted the hour from the started_at timestamp to analyze usage by hour.
Weekday: Extracted the day of the week from the started_at timestamp to analyze usage by day.
Aggregating Data:
Number of Rides: Counted the number of rides (ride_id) to determine the total rides taken by members and casual riders.
By following these data transformation steps, the dataset was prepared for analysis, enabling the comparison of usage patterns between members and casual riders at different times of the day and days of the week.
Loaded csvs to powerquery
Removed columns: "Source.Name", "start_station_name", "start_station_id", "end_station_name", "end_station_id", "start_lat", "start_lng", "end_lat", "end_lng", "rideable_type"
Checked for NA values
Custom column: Ride Duration (seconds) = Duration.TotalSeconds([ended_at] - [started_at])
Custom column: "Ride Duration (minutes)" = "Ride Duration (seconds)" / 60
Custom column: "Hour of the Ride" = Time.Hour(DateTime.Time([started_at]))
Custom column: "Weekday" = Date.DayOfWeekName([started_at], Day.Monday)
Custom column: "week_day_order" = Date.DayOfWeek([started_at], Day.Monday)
Removed duplicates
Set datatypes
"Ride Duration (seconds)" -> Whole
"Ride Duration (minutes)" -> Decimal
"Hour of the Ride" -> Whole
Remove columns: started_at, ended_at
Filtered out rows where ride_duration <= 0
Capitalized first letters of member and casual.
Removed Columns: "started_at", "ended_at"