Documentation

Business task:

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.

Gathering the data:

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.

Getting started…

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)
Data summary
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 ▁▁▁▇▁

Data Wrangling

From the summary statistics the things i found i need to deal with are

  • Missing values
  • Inconsistent column names
  • Duplicated Ride_id’s
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.

Other manipulations

  • I converted the available datetime series to posixct class as it’s quite useful for analysis of datetime data types.
  • Also filtered dataset for rows containing start time after the end time.
  • Generated the days associated with the starting day and ending day using weekdays() function from lubridate package.
  • Generated Duration column in minutes using difftime() function.
  • Also added month column for the starting date.
  • removed the columns start_station_id and end_sation_id.
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)
Data summary
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.

Moving to visualizing

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")

  • Based on the depicted plots, it is evident that both members and casual riders contribute nearly the same number of rentals. Additionally, the visualizations highlight that Saturday stands out as the peak day for rentals.
pie(radius = 1,(rideable_count <- table(cyclist_data$rideable_type)),main = "Pie chart showing the type of bikes used")

  • From this pie chart we can confirm that docked bikes are the showstopper and customer favorite type of bike category.
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:

  • The peek months recorded are july to september where the casual riders and members were so actively contributing to the rentals. Maybe the reason is that these are the times of summer vacation.

  • From this plot we can observe that until july there were no sight of significant rentals of electric bikes but from july onwards the trend continuously rised, even after the peak months there was a significant number of electric bike rides. And from december the casual bikes are also at rise and we can say that from december customers stopped renting docked bikes and went for electric bikes and casual ones.That means either customers love trying new bikes or there may be a reason for the declined usage of bikes for certain periods.Now this could be a limitation of the data i gathered.
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")

  • Sometimes scale of the graph can hide some potential insights, so i plotted this graph just to make sure that i did not overlook the magnitude of rise of rentals in the peak months. This shows that there was a huge demand of bikes in the peak months(july - september) from both casual riders and members.

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.

Analysing the top 10 stations

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.

Now coming to the improper returning of bikes

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.

My recommendations

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.