The business task involves me being a data analyst for a bike rental company and the problem mainly focusses on converting casual riders to members which could potentially lead to company growth.
Now i’m going to explain the way I addressed this problem from scatch and share my veiws and recommendations to acheive this task.
Having obtained the dataset through Coursera, I utilized Python’s Pandas and OS libraries to amalgamate the data of the 12 months from April 2020 - March 2021, resulting in a comprehensive dataset of 3.4 million records. Recognizing Python’s limitations in handling such voluminous data and Excel’s inability to load the entire dataset, I transitioned to R programming. This shift allows for more efficient processing and analysis of the extensive dataset, facilitating a clearer and more streamlined workflow.
First I loaded all the essential packages that i’m going to need
library("dplyr")
##
## Attaching package: 'dplyr'
## The following objects are masked from 'package:stats':
##
## filter, lag
## The following objects are masked from 'package:base':
##
## intersect, setdiff, setequal, union
library("skimr")
library("tidyverse")
## ── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
## ✔ forcats 1.0.0 ✔ readr 2.1.5
## ✔ ggplot2 3.4.4 ✔ stringr 1.5.1
## ✔ lubridate 1.9.3 ✔ tibble 3.2.1
## ✔ purrr 1.0.2 ✔ tidyr 1.3.1
## ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
## ✖ dplyr::filter() masks stats::filter()
## ✖ dplyr::lag() masks stats::lag()
## ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors
library("ggplot2")
library("data.table")
##
## Attaching package: 'data.table'
##
## The following objects are masked from 'package:lubridate':
##
## hour, isoweek, mday, minute, month, quarter, second, wday, week,
## yday, year
##
## The following object is masked from 'package:purrr':
##
## transpose
##
## The following objects are masked from 'package:dplyr':
##
## between, first, last
library("janitor")
##
## Attaching package: 'janitor'
##
## The following objects are masked from 'package:stats':
##
## chisq.test, fisher.test
Now I loaded my dataset that i’m going to work with in a variable cyclist_data and converted the data frame to enhanced version of dataframe called data table as this is more efficient and used for quick manipulation.
cyclist_data <- read.csv("C:\\Users\\acer\\Desktop\\case study1\\combined_data.csv")
cyclist_data <- data.table(cyclist_data)
Generating summary statistics and a basic understanding on the data using Summary() and also using skim() from skimr package which is a powerful tool for generating summary statistics and exploring the structure of your dataset.:
summary(cyclist_data)
## ride_id rideable_type started_at ended_at
## Length:3489748 Length:3489748 Length:3489748 Length:3489748
## Class :character Class :character Class :character Class :character
## Mode :character Mode :character Mode :character Mode :character
##
##
##
##
## start_station_name start_station_id end_station_name end_station_id
## Length:3489748 Length:3489748 Length:3489748 Length:3489748
## Class :character Class :character Class :character Class :character
## Mode :character Mode :character Mode :character Mode :character
##
##
##
##
## start_lat start_lng end_lat end_lng
## Min. :41.64 Min. :-87.87 Min. :41.54 Min. :-88.07
## 1st Qu.:41.88 1st Qu.:-87.66 1st Qu.:41.88 1st Qu.:-87.66
## Median :41.90 Median :-87.64 Median :41.90 Median :-87.64
## Mean :41.90 Mean :-87.64 Mean :41.90 Mean :-87.64
## 3rd Qu.:41.93 3rd Qu.:-87.63 3rd Qu.:41.93 3rd Qu.:-87.63
## Max. :42.08 Max. :-87.52 Max. :42.16 Max. :-87.44
## NA's :4738 NA's :4738
## member_casual
## Length:3489748
## Class :character
## Mode :character
##
##
##
##
skim(cyclist_data)
| Name | cyclist_data |
| Number of rows | 3489748 |
| Number of columns | 13 |
| Key | NULL |
| _______________________ | |
| Column type frequency: | |
| character | 9 |
| numeric | 4 |
| ________________________ | |
| Group variables | None |
Variable type: character
| skim_variable | n_missing | complete_rate | min | max | empty | n_unique | whitespace |
|---|---|---|---|---|---|---|---|
| ride_id | 0 | 1 | 16 | 16 | 0 | 3489539 | 0 |
| rideable_type | 0 | 1 | 11 | 13 | 0 | 3 | 0 |
| started_at | 0 | 1 | 19 | 19 | 0 | 3040228 | 0 |
| ended_at | 0 | 1 | 19 | 19 | 0 | 3027775 | 0 |
| start_station_name | 0 | 1 | 0 | 53 | 122175 | 709 | 0 |
| start_station_id | 0 | 1 | 0 | 35 | 122801 | 1344 | 0 |
| end_station_name | 0 | 1 | 0 | 53 | 143242 | 707 | 0 |
| end_station_id | 0 | 1 | 0 | 35 | 143703 | 1345 | 0 |
| member_casual | 0 | 1 | 6 | 6 | 0 | 2 | 0 |
Variable type: numeric
| skim_variable | n_missing | complete_rate | mean | sd | p0 | p25 | p50 | p75 | p100 | hist |
|---|---|---|---|---|---|---|---|---|---|---|
| start_lat | 0 | 1 | 41.90 | 0.04 | 41.64 | 41.88 | 41.90 | 41.93 | 42.08 | ▁▁▇▇▁ |
| start_lng | 0 | 1 | -87.64 | 0.03 | -87.87 | -87.66 | -87.64 | -87.63 | -87.52 | ▁▁▂▇▁ |
| end_lat | 4738 | 1 | 41.90 | 0.04 | 41.54 | 41.88 | 41.90 | 41.93 | 42.16 | ▁▁▇▅▁ |
| end_lng | 4738 | 1 | -87.65 | 0.03 | -88.07 | -87.66 | -87.64 | -87.63 | -87.44 | ▁▁▁▇▁ |
From the summary statistics the things i found i need to deal with are
cyclist_data <- na.omit(cyclist_data)
cyclist_data <- cyclist_data[!duplicated(cyclist_data$ride_id)]
clean_names(cyclist_data)
## ride_id rideable_type started_at ended_at
## <char> <char> <char> <char>
## 1: A847FADBBC638E45 docked_bike 2020-04-26 17:45:14 2020-04-26 18:12:03
## 2: 5405B80E996FF60D docked_bike 2020-04-17 17:08:54 2020-04-17 17:17:03
## 3: 5DD24A79A4E006F4 docked_bike 2020-04-01 17:54:13 2020-04-01 18:08:36
## 4: 2A59BBDF5CDBA725 docked_bike 2020-04-07 12:50:19 2020-04-07 13:02:31
## 5: 27AD306C119C6158 docked_bike 2020-04-18 10:22:59 2020-04-18 11:15:54
## ---
## 3484798: 9397BDD14798A1BA docked_bike 2021-03-20 14:58:56 2021-03-20 17:22:47
## 3484799: BBBEB8D51AAD40DA classic_bike 2021-03-02 11:35:10 2021-03-02 11:43:37
## 3484800: 637FF754DA0BD9E1 classic_bike 2021-03-09 11:07:36 2021-03-09 11:49:11
## 3484801: F8F43A0B978A7A35 classic_bike 2021-03-01 18:11:57 2021-03-01 18:18:37
## 3484802: 3AE64EA5BF43CF72 electric_bike 2021-03-26 17:58:14 2021-03-26 18:06:43
## start_station_name start_station_id
## <char> <char>
## 1: Eckhart Park 86.0
## 2: Drake Ave & Fullerton Ave 503.0
## 3: McClurg Ct & Erie St 142.0
## 4: California Ave & Division St 216.0
## 5: Rush St & Hubbard St 125.0
## ---
## 3484798: Michigan Ave & Oak St 13042
## 3484799: Kingsbury St & Kinzie St KA1503000043
## 3484800: Michigan Ave & Oak St 13042
## 3484801: Kingsbury St & Kinzie St KA1503000043
## 3484802:
## end_station_name end_station_id start_lat start_lng
## <char> <char> <num> <num>
## 1: Lincoln Ave & Diversey Pkwy 152.0 41.89640 -87.66100
## 2: Kosciuszko Park 499.0 41.92440 -87.71540
## 3: Indiana Ave & Roosevelt Rd 255.0 41.89450 -87.61790
## 4: Wood St & Augusta Blvd 657.0 41.90300 -87.69750
## 5: Sheridan Rd & Lawrence Ave 323.0 41.89020 -87.62620
## ---
## 3484798: New St & Illinois St TA1306000013 41.90096 -87.62378
## 3484799: New St & Illinois St TA1306000013 41.88918 -87.63851
## 3484800: Clark St & Berwyn Ave KA1504000146 41.90096 -87.62378
## 3484801: New St & Illinois St TA1306000013 41.88918 -87.63851
## 3484802: New St & Illinois St TA1306000013 41.88000 -87.63000
## end_lat end_lng member_casual
## <num> <num> <char>
## 1: 41.93220 -87.65860 member
## 2: 41.93060 -87.72380 member
## 3: 41.86790 -87.62300 member
## 4: 41.89920 -87.67220 member
## 5: 41.96950 -87.65470 casual
## ---
## 3484798: 41.89085 -87.61862 casual
## 3484799: 41.89085 -87.61862 member
## 3484800: 41.97800 -87.66805 member
## 3484801: 41.89085 -87.61862 member
## 3484802: 41.89082 -87.61954 member
Clean names() is a function in janitor package that ensures all the column names to be formatted consistently.
cyclist_data$started_at <-as.POSIXct(cyclist_data$started_at,format="%Y-%m-%d %H:%M:%S")
cyclist_data$ended_at <- as.POSIXct(cyclist_data$ended_at,format="%Y-%m-%d %H:%M:%S")
cyclist_data <- subset(cyclist_data,ended_at>started_at)
########## adding weekdays wrt starting and ending date
cyclist_data <- cyclist_data %>%
mutate(starting_day=weekdays(cyclist_data$started_at),endidng_day=weekdays(cyclist_data$ended_at))
cyclist_data <- cyclist_data %>%
mutate(duration_in_min=as.numeric(difftime(ended_at,started_at,units="mins")))
cyclist_data <- cyclist_data %>%
mutate(month=format(started_at,"%B"))
cyclist_data <- cyclist_data %>%
select(-c(start_station_id,end_station_id))
Now i rechecked the summary statistics for the cleaned and transformed data.
summary(cyclist_data)
## ride_id rideable_type started_at
## Length:3474133 Length:3474133 Min. :2020-04-01 00:00:30.0
## Class :character Class :character 1st Qu.:2020-07-14 18:49:22.0
## Mode :character Mode :character Median :2020-08-29 14:41:51.0
## Mean :2020-09-10 01:54:20.4
## 3rd Qu.:2020-10-20 21:28:59.0
## Max. :2021-03-31 23:59:08.0
## ended_at start_station_name end_station_name
## Min. :2020-04-01 00:10:45.00 Length:3474133 Length:3474133
## 1st Qu.:2020-07-14 19:21:01.00 Class :character Class :character
## Median :2020-08-29 15:13:04.00 Mode :character Mode :character
## Mean :2020-09-10 02:21:50.80
## 3rd Qu.:2020-10-20 21:44:42.00
## Max. :2021-04-06 11:00:11.00
## start_lat start_lng end_lat end_lng
## Min. :41.64 Min. :-87.87 Min. :41.54 Min. :-88.07
## 1st Qu.:41.88 1st Qu.:-87.66 1st Qu.:41.88 1st Qu.:-87.66
## Median :41.90 Median :-87.64 Median :41.90 Median :-87.64
## Mean :41.90 Mean :-87.64 Mean :41.90 Mean :-87.65
## 3rd Qu.:41.93 3rd Qu.:-87.63 3rd Qu.:41.93 3rd Qu.:-87.63
## Max. :42.08 Max. :-87.52 Max. :42.16 Max. :-87.44
## member_casual starting_day endidng_day duration_in_min
## Length:3474133 Length:3474133 Length:3474133 Min. : 0.02
## Class :character Class :character Class :character 1st Qu.: 7.93
## Mode :character Mode :character Mode :character Median : 14.55
## Mean : 27.51
## 3rd Qu.: 26.63
## Max. :58720.03
## month
## Length:3474133
## Class :character
## Mode :character
##
##
##
skim(cyclist_data)
| Name | cyclist_data |
| Number of rows | 3474133 |
| Number of columns | 15 |
| Key | NULL |
| _______________________ | |
| Column type frequency: | |
| character | 8 |
| numeric | 5 |
| POSIXct | 2 |
| ________________________ | |
| Group variables | None |
Variable type: character
| skim_variable | n_missing | complete_rate | min | max | empty | n_unique | whitespace |
|---|---|---|---|---|---|---|---|
| ride_id | 0 | 1 | 16 | 16 | 0 | 3474133 | 0 |
| rideable_type | 0 | 1 | 11 | 13 | 0 | 3 | 0 |
| start_station_name | 0 | 1 | 0 | 53 | 122126 | 709 | 0 |
| end_station_name | 0 | 1 | 0 | 53 | 138384 | 707 | 0 |
| member_casual | 0 | 1 | 6 | 6 | 0 | 2 | 0 |
| starting_day | 0 | 1 | 6 | 9 | 0 | 7 | 0 |
| endidng_day | 0 | 1 | 6 | 9 | 0 | 7 | 0 |
| month | 0 | 1 | 3 | 9 | 0 | 12 | 0 |
Variable type: numeric
| skim_variable | n_missing | complete_rate | mean | sd | p0 | p25 | p50 | p75 | p100 | hist |
|---|---|---|---|---|---|---|---|---|---|---|
| start_lat | 0 | 1 | 41.90 | 0.04 | 41.64 | 41.88 | 41.90 | 41.93 | 42.08 | ▁▁▇▇▁ |
| start_lng | 0 | 1 | -87.64 | 0.03 | -87.87 | -87.66 | -87.64 | -87.63 | -87.52 | ▁▁▂▇▁ |
| end_lat | 0 | 1 | 41.90 | 0.04 | 41.54 | 41.88 | 41.90 | 41.93 | 42.16 | ▁▁▇▅▁ |
| end_lng | 0 | 1 | -87.65 | 0.03 | -88.07 | -87.66 | -87.64 | -87.63 | -87.44 | ▁▁▁▇▁ |
| duration_in_min | 0 | 1 | 27.51 | 251.98 | 0.02 | 7.93 | 14.55 | 26.63 | 58720.03 | ▇▁▁▁▁ |
Variable type: POSIXct
| skim_variable | n_missing | complete_rate | min | max | median | n_unique |
|---|---|---|---|---|---|---|
| started_at | 0 | 1 | 2020-04-01 00:00:30 | 2021-03-31 23:59:08 | 2020-08-29 14:41:51 | 3031568 |
| ended_at | 0 | 1 | 2020-04-01 00:10:45 | 2021-04-06 11:00:11 | 2020-08-29 15:13:04 | 3016751 |
After checking the summary statistics i found there is something wrong with the duration column as the quartiles 1,3 and median seems to be normal but the max duration is way off the charts.So i plotted a simple box plot to checkout the outliers.
boxplot(cyclist_data$duration_in_min, main = "Box plot for duration", col = "lightblue", border = "black")
title(main = "Box plot for duration")
xlabel <- "Cyclist Data"
ylabel <- "Duration (minutes)"
title(xlab = xlabel, ylab = ylabel)
Now it’s clear that i need to deal with the outliers here. There is no specific idea given about how much time can person rent a bike. As the mean is around 30 minutes and the company gives bikes on a daily basis i’m considering a person could rent for maximum 10 hours(600 minutes) and minimum could be 3 minutes (< 3 min is not even a ride in my opinion).First i need to check how many records are with this error.
dim(cyclist_data[cyclist_data$duration_in_min<3])
## [1] 153744 15
dim(cyclist_data[cyclist_data$duration_in_min>600])
## [1] 6090 15
The ride with less than 3 minute duration may seem normal in some cases as biker may have some other work to deal with.But the duration more than 10 hours is definitely not normal, and also the max duration: 40 days is just not right.This could be due to improper returning of bikes or long duration rentals.So instead of removing these outliers i thought of using them for other analysis, so i saved them.And i removed them from original dataset that i’ve been working with.
duration_outlier_data <- cyclist_data %>%
filter(duration_in_min >600)
cyclist_data <- cyclist_data %>%
filter(duration_in_min>=3 & duration_in_min <=600)
I also noticed that there missing values in start_station and end_station although i omitted the rows with null values.This is because the field is not null but maybe it contains character like ’ ’ this. I thought i will deal with this later because there are nearly 1 lakh rows like this and removing them might not be the most useful thing i could do right now.
ggplot(data = cyclist_data)+
geom_bar(mapping = aes(x=starting_day,color=starting_day,fill=starting_day))+
labs(title = "Weekly rental trends",subtitle = "Analysing peak days for cycle rentals",x="Bikes rented on",y="Frequency")
ggplot(data=cyclist_data)+
geom_bar(mapping=aes(x=starting_day,fill=member_casual))+
labs(title = "Type of customer contributing to renting on weekdays",x="Bikes rented on",y="Frequency")
pie(radius = 1,(rideable_count <- table(cyclist_data$rideable_type)),main = "Pie chart showing the type of bikes used")
ggplot(data=cyclist_data)+
geom_bar(mapping=aes(x=starting_day,fill=member_casual))+facet_wrap(~month)+
labs(title = "Monthly trends of bike usage",x="Weekday",y="frequency")
ggplot(data=cyclist_data)+
geom_bar(mapping=aes(x=starting_day,fill=rideable_type))+facet_wrap(~month)+
labs(title = "Monthly trends of bike usage based on bike type",x="Weekday",y="frequency")
These both codes generate the following plots:
ggplot(data=cyclist_data)+
geom_bar(mapping=aes(x=month,fill=rideable_type))+facet_wrap(~member_casual)+
labs(title="Monthly bike usage trends based on the customer type",x='',y="frequency")
I even utilized Tableau for vizualising the regions where there are significant volumes of bike rentals.I used the latitude and longitude of starting points to create a scatterplotplot on map and compared this with the map of chicago from google maps.
Analyzing the provided plots reveals that peak ride activity is concentrated in areas to the north, particularly in River North and Lincoln Park. Additionally, the surge in rides during the summer months can be attributed to the allure of biking near Lake Michigan, offering a delightful experience during the warmer season.
Previously i noticed that there were around 1 lakh records missing the starting and ending stations names. So first i filtered the data to remove all the records with start station name as ’ ’ and used this new dataset to get the top 10 stations.
cleaned_data <- cyclist_data %>%
filter(start_station_name!='')
your_datatable_top_10 <- cleaned_data %>%
count(start_station_name, sort = TRUE) %>%
head(10) %>%
inner_join(woo_data, by = "start_station_name")
# Create a bar plot using ggplot2
ggplot(your_datatable_top_10, aes(x = start_station_name)) +
geom_bar(color="blue",fill="lightblue") +
ggtitle("Top 10 Stations") +
xlab("Station") +
ylab("Frequency")
In the plot, “Streeter Dr & Grand Ave” stands out as the top station with approximately 33 thousand rides. Following closely are “Theater on the Lake,” “Clark and Elm Street,” and “Lake Shore Dr & North Blvd,” each recording approximately 29 thousand rides.
I previously stored the records with duration more than 10 hours in duration_outlier_data now i want to generate some plots to see whether i can get any pattern associated with these improper returning of bikes.
ggplot(data = duration_outlier_data)+
geom_bar(mapping=aes(x=month,fill=rideable_type))+
labs(title="montly trends of Bikes being inappropriately returned",subtitle = "Type of Bikes being taken by customers for more than 25 days",x="Month",y="frequency")+
facet_wrap(~member_casual)
In the displayed plot, it is clear that from June to September, there is a notable increase in instances where casual riders fail to return bikes, indicating unauthorized actions. Notably, docked bikes are primarily involved in these incidents. Electric bikes, on the other hand, seem to be exempt from this trend, possibly due to their built-in tracking features, suggesting that docked bikes are more vulnerable and require closer supervision. While casual bikes currently have a lower likelihood of facing similar issues as they experienced rise from december onwards but recent trends indicate a potential rise in such incidents for this category as well.
The main goal of the business task is to recommend some potential ideas that could convert the non casual riders to members.After analysing the data that i’ve been provided with, the recommendations that i came up with are:
Provide a 1-hour pass for a set price, especially targeting rides that are typically less than 30 minutes. This encourages casual riders to opt for longer rides at a reasonable cost.
Introduce a monthly membership plan, especially during peak months (July-September). Offering this plan at a reduced rate can encourage ridership during summer vacation.
Introducing privilage of long-term rentals for riders with membership that last for some days by taking appropriate measures. For casual riders we could just increase the price.
Launch advertising campaigns for memberships between May and July to capitalize on the peak months of bike rentals. This ensures that potential customers are aware of the benefits and cost savings of becoming members during the busiest cycling season. Emphasize the health benefits of cycling in promotional materials to attract more members.
Target advertising efforts at popular and refreshing locations such as River North and Lincoln Park, which are near Lake Michigan. These areas are likely to attract outdoor enthusiasts and potential cyclists. Highlight the convenience of bike sharing and the joy of cycling in these attractive locations.This could enhance the reach of our advertisements.
Promote the diverse range of bikes available for rental. Showcase the different types of bikes to cater to various preferences and needs. This information can be included in advertising materials to educate potential customers about the options available.
Deploying bike trackers to prevent unauthorized or improper rentals, thereby efficiently addressing the high demand during peak times.