Bikes Share (Capstone)

Personal Background

During my job-search and some downtime I started the Google Data Analytics Certificate, more or less for fun, so here is the fictional capstone project. The whole data and scripts can be found here

Report on Bicycle Usage by Members and Non-Members

Goal

Use bike usage data to inform the marketing team on how to lead to a better conversion from casual users to annual members.

Questions that needs to be addressed:

• Are there different usage patterns, do casual members use bikes more on weekdays or weekends?
• How are bikes used (round-trip or one-way)?
• Do casual and annual members differ in terms of trip-durations?

Data Sources

The data has been provided by divvy bikes here, under this license.

The data is not connected to the fictional company the report is for, but let’s pretend it is. Data description in the capstone protocol are outdated, so certain questions cannot be addressed as for example individual identifiers have been removed over time.

Furthermore, usage patterns of the last 12 months (which are the most actual) might not represent actual use, due to the COVID-19 pandemic.

Possible changes due to the pandemic:

• Lockdowns (i.e. less bike use)
• Higher use of bikes, to avoid public transportation

To not save the data in the repository, I have written a small bash script (getdata.sh) to create the data folder and download the monthly .csv files from the resource into it. Data from April 2020 to April 2021 will be retrieved.

The data folder will also be used to store cleaned and aggregate data files.

Data Preprocessing

I downloaded the monthly files, so we need to get a quick overlook over the columns and structures and, if necessary adapt column names etc.

To make the whole calculation a bit more efficient, I am already transforming dates to datetime and drop a few unneeded columns.

Additionally, I am also creating summary tables for each month to have a few consistency checks.

files = dir(path="data/", pattern = glob2rx("20*.csv"))

dataframes = list()
controls = list()

c <- 1

for (fname in files){
    print(fname)
    # All ready reduce the amount of data in the loaded data, to keep things clean,
    # for validation also change dates to datetime
    dataframes[[c]] <- fread(paste('data/', fname, sep=''), sep=',',
                             select=c('ride_id', 'started_at', 'ended_at', 'member_casual',
                                      'start_station_name', 'end_station_name')) %>%
        mutate(started_at = as_datetime(started_at), ended_at = as_datetime(ended_at))

    # Do some consistency checks
    controls[[c]] <- summarise(dataframes[[c]], min_s = min(started_at), max_s = max(started_at),
                                     min_e = min(ended_at), max_e = max(ended_at),
                                     member = n_distinct(member_casual),
                                     stations = n_distinct(start_station_name),
                                     n = n())
    c <- c + 1
}

bind_rows(controls)

There are a few suspiciously long end dates for rides and possibly a few datapoints missing in early June 2020. So we need to see what will happen there. Also in May and June rides are heavily increasing as compared to April (pandemic?).

# Combine data and save, so we can load a single df.
whole <- bind_rows(dataframes)
write_csv(whole, 'data/wholedata.csv')

# Now do a few calculations, which we will need for further uses and analysis.

data <- whole %>%
  mutate(month = month(started_at, label=TRUE),
         date = date(started_at),
         day = day(started_at),
         year = year(started_at),
         day_of_week = wday(whole$started_at, label=TRUE),
         start_station_name = as.character(start_station_name),
         end_station_name = as.character(end_station_name))

data$tripduration <- as.numeric(difftime(whole$ended_at, whole$started_at))
data$samestation <- data$start_station_name == data$end_station_name

Now, also given the data, a bit of data cleaning has still to be performed. There are negative values, and a few very, very long times (let’s keep them in first).

The data has been processed to remove trips that are taken by staff as they service and inspect the system; and any trips that were below 60 seconds in length (potentially false starts or users trying to re-dock a bike to ensure it was secure).

But according to the new information, we do not need to remove service trips, so there appears to be some data cleaning which had already been done.

Unfortunately - there are still some testing stations in there, which will now be removed.

# Remove negative trips and testing stations (as well as missing data)
test_stations <- c("hubbard_test_lws", "WATSON TESTING - DIVVY", "",
                   "HUBBARD ST BIKE CHECKING (LBS-WH-TEST)")
data_clean <- filter(data, tripduration > 60) %>%
  filter(! start_station_name %in% test_stations,
         !end_station_name %in% test_stations)

write_csv(data_clean, 'data/clean_data.csv')

Analysis and Visualization

# Read data directly, to save time.
data <- fread('data/clean_data.csv')

To keep this project a bit more simple, I will summarize the data in terms of descriptive statistics and rely on simple generalized linear models to come to conclusions.

First show a summary of the data

summary(data %>%
          select(member_casual, month, day_of_week, tripduration, samestation) %>%
          mutate(tripduration = tripduration / 60))

We see here in the member_casual column, that more trips have been conducted by subscribers, than by casual members, that the most trips have been done on Saturdays, that August and July have had the highest usage in general. That the average trip duration is about 27 minutes and the median duration 15. The distribution is heavily skewed, as there are a few very long trips in the dataset, that do not appear to be noise. I would assume that these heavy users rented the bike for a whole month, though there is the possibility, that something during check-out went wrong.

Statistical analysis

The first question I want to address here is whether week-days and membership status have a significant influence on trip duration.

data <- mutate(data, tripduration_min = tripduration / 60)

tripduration_model <- glm(tripduration_min ~ day_of_week * member_casual, data=data)

summ(tripduration_model)

MODEL INFO:
Observations: 3535941
Dependent Variable: tripduration_min
Type: Linear regression

MODEL FIT:
χ²(13) = 693803591.94, p = 0.00
Pseudo-R² (Cragg-Uhler) = 0.00
Pseudo-R² (McFadden) = 0.00
AIC = 48466417.58, BIC = 48466613.75

Standard errors: MLE
-----------------------------------------------------------------------
                                            Est.   S.E.   t val.      p
--------------------------------------- -------- ------ -------- ------
(Intercept)                                40.84   0.59    69.56   0.00
day_of_weekDo                               0.78   0.81     0.95   0.34
day_of_weekFr                               1.50   0.77     1.96   0.05
day_of_weekMi                              -1.23   0.82    -1.49   0.14
day_of_weekMo                               3.20   0.83     3.86   0.00
day_of_weekSa                               5.00   0.71     7.08   0.00
day_of_weekSo                               8.28   0.73    11.27   0.00
member_casualmember                       -25.76   0.72   -35.57   0.00
day_of_weekDo:member_casualmember          -0.80   1.01    -0.80   0.43
day_of_weekFr:member_casualmember          -1.03   0.97    -1.06   0.29
day_of_weekMi:member_casualmember           1.18   1.02     1.17   0.24
day_of_weekMo:member_casualmember          -3.07   1.03    -2.99   0.00
day_of_weekSa:member_casualmember          -2.26   0.92    -2.46   0.01
day_of_weekSo:member_casualmember          -5.39   0.96    -5.63   0.00
-----------------------------------------------------------------------

Estimated dispersion parameter = 52518.1 

For the analysis of round trips versus one way trips, I will rely on summary statistics.

data %>%
  group_by(member_casual, day_of_week) %>%
  summarise(Proportion = round(mean(samestation) * 100, 2))

What we see, is that members mostly do one-way trips, whereas casual rides do round-trips.

data %>%
  group_by(member_casual, samestation) %>%
  summarise("Median Tripduration" = median(tripduration_min))

Looking at median trip durations for members and one-way and round trips, we see that round-trips are about twice as long. And again longer for casual users than for members.

Finally, let’s look at differences in riding behavior.

data %>%
  group_by(member_casual, day_of_week) %>%
  summarise(n = n()) %>%
  mutate(Proportion = round(100 * (n / sum(n)), 2))

`summarise()` has grouped output by 'member_casual'. You can override using the `.groups` argument.

Here we also see a trend in that casual riders use bikes more often on the weekends, whereas members do not appear to have strong differences in weekly riding.

Report / Conclusions

Our population:

First of all, we see that in the last 12 months, there have been many more rides by members than by casual users.

However, due to data privacy, we cannot say how many riders we have so this dataset is incomplete in this regard.

Rides during the Week

`summarise()` has grouped output by 'member_casual'. You can override using the `.groups` argument.

`summarise()` has grouped output by 'member_casual'. You can override using the `.groups` argument.

Further interesting observations in terms of biking behavior are:

• Casual members use bikes more often on the weekends
• Members use bikes throughout the week
• Casual members use bikes more often for round trips
• Members do one-way trips

Trip durations

The graphic shows, also referring to our GLM analysis above, that casual rides do longer rides and that rides during the weekend are generally longer.

Conclusion and Advice

In my opinion given the data and having some experience in using bike-shares, I think the marketing team could consider the following three points to convert casual riders to annual riders:

• Cater to the great network of stations and explain that using bikes for shorter trips is definitely worth it. As a member you do not need to carry your bike with you all day, you can change it just like that.
• Especially during the pandemic: The network of bike stations is a healthier alternative to public transportation. It can also be used for the customers commute.
• More generally: Stress that an annual subscription is cheaper and reduces stress, as you can always chose a bike, whererver you want and do your next trip with it - Not only on the weekend but also for commutes or grocery shopping during the week.