Foreword

For me, the value of this document is in tips and tricks I’ve learned along the way, some of which were not immediately obvious to me as a novice.

Off the top of my head: To create a random discrete palette with distinct colors, I followed the advice of geotheory.

For distinguishing leaflet markers by color, I followed the suggestion from help-info.de.

And so forth. Whether it was factoring the data or replacing multiple ‘left_joins’ with a single ‘pivot_wider’ line, whenever I needed to transform the data or create more advanced plots, I turned to Google. In most cases, I found helpful Q&A threads with precise solutions. So, thanks to everyone.

To reveal the hidden code, simply locate the inconspicuous ‘Show’ button on the right.

Enjoy reading!


Statement of business task

Cyclistic is a bike-share company in Chicago.

Cyclistic has a successful bike-share offering program comprising 5,824 bicycles that are geotracked and locked into a network of 692 stations.

Pricing plans allow the flexibility of purchasing single-ride passes, full-day passes or Cyclistic memberships. Customers who purchase single-ride or full-day passes are referred to as casual riders. Customers who purchase Cyclistic membership are Cyclistic members.

Cyclistic users are more likely to ride for leisure but about 30% use bikes to commute to work every day.

Lily Moreno, the director of marketing in Cyclistic, has set a clear goal: design marketing strategies aimed at converting casual riders into annual members.

I was assigned the question to answer: How do annual members and casual riders use Cyclistic bikes differently?

I am to identify, interpret and visualize these differences and help the marketing team to answer the next question: Why would casual riders buy Cyclistic annual membership?


Description of data sources

Phase: Ask

To analyze and identify trends, used Cyclistic’s historical trip data provided by the company, downloaded here.

The ‘Cyclistic’ data has been made available by Motivate International Inc. under this license.

As an auxiliary data set, I used Jersey City data from Citi Bike Trip Histories. This data was provided according to the NYCBS Data Use Policy.

Both data sets cover 12 months of riding from February 2023 to January 2024.

Both sets are:

  • reliable, because they originate from the sources I can trust;

  • comprehensive, because they have sufficient information to identify and distinguish riding patterns of casual clients and club members;

  • original, as they are provided by bicycle share services, who gathered the data .

  • definitely current,

  • cited.

Therefore, this data ROCCCS.


Cleaning and manipulating data

Phase: Prepare

Imported libraries, including:

  • ‘forcats’ for ordering bars in plots.

  • ‘ggrepel’ to spread out labels at the scatterplot in a non-overlapping manner.

  • ‘REdaS’ for calculations with degrees and radians.

  • ‘leaflet’ for map plots.

knitr::opts_chunk$set(warning = FALSE, message = FALSE)

library(tidyverse)
library(readr)
library(stringr)
library(forcats)
library(ggrepel)
library(REdaS)
library(leaflet)

Handled the main data set received from ‘Cyclistic’:

  • Read the columns from the first file into a string variable;

  • Checked if all the files have the same set of columns (they were);

files <- list.files('~/Google Data Analytics/8-2/Case A/Input Data/', 
                    pattern = ".csv$", recursive = TRUE, full.names = TRUE
)

files
##  [1] "D:/Docs/Google Data Analytics/8-2/Case A/Input Data/202302-divvy-tripdata.csv"
##  [2] "D:/Docs/Google Data Analytics/8-2/Case A/Input Data/202303-divvy-tripdata.csv"
##  [3] "D:/Docs/Google Data Analytics/8-2/Case A/Input Data/202304-divvy-tripdata.csv"
##  [4] "D:/Docs/Google Data Analytics/8-2/Case A/Input Data/202305-divvy-tripdata.csv"
##  [5] "D:/Docs/Google Data Analytics/8-2/Case A/Input Data/202306-divvy-tripdata.csv"
##  [6] "D:/Docs/Google Data Analytics/8-2/Case A/Input Data/202307-divvy-tripdata.csv"
##  [7] "D:/Docs/Google Data Analytics/8-2/Case A/Input Data/202308-divvy-tripdata.csv"
##  [8] "D:/Docs/Google Data Analytics/8-2/Case A/Input Data/202309-divvy-tripdata.csv"
##  [9] "D:/Docs/Google Data Analytics/8-2/Case A/Input Data/202310-divvy-tripdata.csv"
## [10] "D:/Docs/Google Data Analytics/8-2/Case A/Input Data/202311-divvy-tripdata.csv"
## [11] "D:/Docs/Google Data Analytics/8-2/Case A/Input Data/202312-divvy-tripdata.csv"
## [12] "D:/Docs/Google Data Analytics/8-2/Case A/Input Data/202401-divvy-tripdata.csv"
str1 <-
  colnames(read.table(files[1], head = TRUE, nrows = 1, sep = ";"))

tb <- TRUE

invisible(lapply(files, function(x) {
  tb <- tb * (str1 == colnames(read.table(x, head = TRUE, nrows = 1, sep = ";")))
}))

print(ifelse(tb, "All columns are identical", "Columns are not identical"))
## [1] "All columns are identical"
  • Merged input data into a single data frame named ‘rides’;
rides <- read_csv(files, show_col_types = FALSE)
  • Stored the backup of original data set in ‘original_rides’.
original_rides <- rides
  • Read and merged files from Citi Bike dataset in the same manner into data frame named ‘jc_rides’.
files <- list.files('~/Google Data Analytics/8-2/Case A/JC_Data/', 
  pattern = ".csv$", recursive = TRUE, full.names = TRUE
)

files
##  [1] "D:/Docs/Google Data Analytics/8-2/Case A/JC_Data/JC-202302-citibike-tripdata.csv"
##  [2] "D:/Docs/Google Data Analytics/8-2/Case A/JC_Data/JC-202303-citibike-tripdata.csv"
##  [3] "D:/Docs/Google Data Analytics/8-2/Case A/JC_Data/JC-202304-citibike-tripdata.csv"
##  [4] "D:/Docs/Google Data Analytics/8-2/Case A/JC_Data/JC-202305-citibike-tripdata.csv"
##  [5] "D:/Docs/Google Data Analytics/8-2/Case A/JC_Data/JC-202306-citibike-tripdata.csv"
##  [6] "D:/Docs/Google Data Analytics/8-2/Case A/JC_Data/JC-202307-citibike-tripdata.csv"
##  [7] "D:/Docs/Google Data Analytics/8-2/Case A/JC_Data/JC-202308-citibike-tripdata.csv"
##  [8] "D:/Docs/Google Data Analytics/8-2/Case A/JC_Data/JC-202309-citibike-tripdata.csv"
##  [9] "D:/Docs/Google Data Analytics/8-2/Case A/JC_Data/JC-202310-citibike-tripdata.csv"
## [10] "D:/Docs/Google Data Analytics/8-2/Case A/JC_Data/JC-202311-citibike-tripdata.csv"
## [11] "D:/Docs/Google Data Analytics/8-2/Case A/JC_Data/JC-202312-citibike-tripdata.csv"
## [12] "D:/Docs/Google Data Analytics/8-2/Case A/JC_Data/JC-202401-citibike-tripdata.csv"
jc_rides <- read_csv(files, show_col_types = FALSE)
  • Removed temporary data.
rm(str1)
rm(tb)
rm(files)

EXPLORING DATA

  • Checked contents of the downloaded data;
glimpse(rides)
## Rows: 5,674,449
## Columns: 13
## $ ride_id            <chr> "CBCD0D7777F0E45F", "F3EC5FCE5FF39DE9", "E54C1F27FA…
## $ rideable_type      <chr> "classic_bike", "electric_bike", "classic_bike", "e…
## $ started_at         <dttm> 2023-02-14 11:59:42, 2023-02-15 13:53:48, 2023-02-…
## $ ended_at           <dttm> 2023-02-14 12:13:38, 2023-02-15 13:59:08, 2023-02-…
## $ start_station_name <chr> "Southport Ave & Clybourn Ave", "Clarendon Ave & Go…
## $ start_station_id   <chr> "TA1309000030", "13379", "TA1309000030", "TA1309000…
## $ end_station_name   <chr> "Clark St & Schiller St", "Sheridan Rd & Lawrence A…
## $ end_station_id     <chr> "TA1309000024", "TA1309000041", "13156", "TA1309000…
## $ start_lat          <dbl> 41.92077, 41.95788, 41.92077, 41.92087, 41.79483, 4…
## $ start_lng          <dbl> -87.66371, -87.64958, -87.66371, -87.66373, -87.618…
## $ end_lat            <dbl> 41.90799, 41.96952, 41.88042, 41.87943, 41.78053, 4…
## $ end_lng            <dbl> -87.63150, -87.65469, -87.65552, -87.63550, -87.605…
## $ member_casual      <chr> "casual", "casual", "member", "member", "member", "…
  • Counted the number of unique ‘ride_id’ entries.
nrow(as.data.frame(unique(rides$ride_id)))
## [1] 5674449

All ‘ride_id’ values were unique.

Some ‘station_name’ values had “Public Rack -” prefix, some had not.

  • Removed such prefixes for consistency.
rides$start_station_name <- str_replace_all(
  rides$start_station_name, "Public Rack - ", ""
)
rides$end_station_name <- str_replace_all(
  rides$end_station_name, "Public Rack - ", ""
)
  • Considering only the start points, which represent half of the stations entried, identified non-unique combinations of station_id and station_name;

From here on, the term ‘tdf’ will refer to a ‘temporary data frame’.

tdf <- as.data.frame(ftable(rides$end_station_id, rides$end_station_name)) %>%
  filter(Freq > 0) %>%
  rename(station_id = Var1, station_name = Var2) %>%
  group_by(station_id) %>%
  filter(n() > 1) %>%
  arrange(station_id)
head(tdf, n = 10)
## # A tibble: 10 × 3
## # Groups:   station_id [5]
##    station_id station_name                          Freq
##    <fct>      <fct>                                <int>
##  1 13259      California Ave & Francis Pl           4551
##  2 13259      California Ave & Francis Pl (Temp)     617
##  3 13290      Noble St & Milwaukee Ave              5049
##  4 13290      Noble St & Milwaukee Ave (Temp)       1859
##  5 15541      Buckingham Fountain                   2159
##  6 15541      Buckingham Fountain (Columbus/Balbo)   159
##  7 15541      Buckingham Fountain (Temp)            4186
##  8 15541.1.1  Buckingham - Fountain                  173
##  9 15541.1.1  Buckingham Fountain                   1002
## 10 15623      Campbell Ave & Montrose Ave           3223
nrow(tdf)
## [1] 167

Station parameters, such as station ID, station name, latitude and longitude should be consistent all over the data set.

On the contrary, there were 167 ambiguous combinations of ‘end_station_id’ and ‘end_station_name’. This resulted in thousands of inconsistent entries, where several ‘end_station_names’ corresponded to a single ‘end_station_id’.

Counted the number of unique station_id entries.

nrow(as.data.frame(ftable(rides$end_station_id)))
## [1] 1527

There were 1527 unique ‘end_station_id’ entries, which is ~2.2 times more than the real number of stations (692).

There were inconsistencies in the start station data as well.

  • For a further review, I generated a list of all nodes.

From here on, by node I mean either end of a ride route, be it starting or ending position.

nodes <- rbind(
  rides %>%
    select(
      start_station_name, start_station_id, start_lat,
      start_lng, member_casual
    ) %>%
    rename(
      station_name = start_station_name, station_id = start_station_id,
      lat = start_lat, lng = start_lng
    ) %>%
    mutate(point_type = "start"),
  rides %>%
    select(
      end_station_name, end_station_id, end_lat,
      end_lng, member_casual
    ) %>%
    rename(
      station_name = end_station_name, station_id = end_station_id,
      lat = end_lat, lng = end_lng
    ) %>%
    mutate(point_type = "end")
)
  • Counted number of consistent and inconsistent observations.

So far:

nrow(nodes %>%
  filter(!is.na(station_name) & !is.na(station_id) &
    !is.na(lat) & !is.na(lng) &
    !lat == 0 & !lng == 0))
## [1] 9558235
  • 9558235 nodes had both station_id, station_name and non-zero values of longitude and latitude;
nrow(nodes %>%
  filter(lat == 0 | lng == 0 | is.na(lat) | is.na(lng)))
## [1] 7154
  • 7154 nodes missed latitude and longitude. Only 119 of them had station_id and/or station_name. Other 7035 nodes were beyond repair.
nrow(nodes %>%
  filter(is.na(station_id) & !is.na(station_name)))
## [1] 273
  • 273 nodes missed station_id but could have been identified via station_name.
nrow(nodes %>%
  filter(!is.na(station_id) & is.na(station_name)))
## [1] 0
  • 0 nodes had station_id but no station_name.
nrow(nodes %>%
  filter((is.na(station_id) | is.na(station_id)) &
    !is.na(lat) & !is.na(lng) &
    !lat == 0 & !lng == 0))
## [1] 1783509
  • 1783509 nodes had both latitude and longitude but missed station_id and station_name.

15.6% of rides with undefined station_ID and/or no station_name was a lot. I had to check if I could identify these nodes by their spatial position and assign proper values of station_id and station_name to these entries.

In general, it is possible to use concatenation of variables ‘lat’ and ‘lng’ as unique node ID. But in this data set, latitude and longitude values jittered due to excessive precision of the positioning.

Four decimal places provide a positioning accuracy of ~11 meters. This accuracy level would have resulted in numerous combinations of ‘lat’ and ‘lng’ per station.

Three decimal places provide a positioning accuracy of ~111 meters, which seemed little bit rough.

For a further analysis of problematic data:

  • Rounded ‘lat’ and ‘lng’ to achieve accuracy of ~56 meters;
nodes <- rbind(
  nodes %>%
    filter(!is.na(station_name) & !is.na(station_id) &
      !is.na(lat) & !is.na(lng) &
      !lat == 0 & !lng == 0) %>%
    mutate(nodetype = "identified"),
  nodes %>%
    filter((is.na(station_id) | is.na(station_id)) &
      !is.na(lat) & !is.na(lng) &
      !lat == 0 & !lng == 0) %>%
    mutate(nodetype = "non-identified")
) %>%
  mutate(lat = 2 * lat, lng = 2 * lng) %>%
  mutate_at(vars(lat, lng), ~ round(., 3)) %>%
  mutate(lat = lat / 2, lng = lng / 2)

nrow(nodes)
## [1] 11341744
  • Concatenated ‘lat’ and ‘lng’ into ‘latlng’, the unique spatial id of the node;
nodes$latlng <- paste(nodes$lat, nodes$lng, sep = " ")
  • Counted unique combinations of ‘lat’ and ‘lng’ in the grouped table.
nodes_latlng_uniques <- nodes %>%
  group_by(latlng, nodetype) %>%
  count(latlng, lat, lng) %>%
  ungroup()
  • Plotted identified and non-identified nodes together.
ggplot(nodes_latlng_uniques %>% filter(nodetype == "identified")) +
  geom_point(mapping = aes(x = lng, y = lat, color = "identified")) +
  geom_point(
    data = nodes_latlng_uniques %>%
      filter(nodetype == "non-identified"),
    mapping = aes(
      x = lng, y = lat,
      color = "non-identified", alpha = 0.2
    )
  ) +
  ylim(41.61, 42.18) +
  xlim(-88.16, -87.44) +
  labs(title = "Spatial distribution of nodes")

Noticed something odd.

  • Zoomed in.
ggplot(nodes_latlng_uniques %>% filter(nodetype == "identified")) +
  geom_point(mapping = aes(x = lng, y = lat, color = "identified")) +
  geom_point(
    data = nodes_latlng_uniques %>%
      filter(nodetype == "non-identified"),
    mapping = aes(
      x = lng, y = lat,
      color = "non-identified", alpha = 0.2
    )
  ) +
  ylim(41.80, 41.84) +
  xlim(-87.70, -87.64) +
  labs(title = "Spatial distribution of nodes (zoom in)")

Longitude and latitude values of non-identified nodes were of low precision (only 2 decimals places). I was not able to attribute them to any station.

Giving up 15.6% of the data seemed like a big decision.

To do this, I had to ensure that spatial distribution and statistical parameters of the discarded data were similar to a main part of the data set.

I guessed that the subset may have had a skewed proportion of riders of different types.

round(prop.table(as.table(ftable(
  nodes$member_casual,
  nodes$nodetype
)), margin = 2), 3)
##         
##          identified non-identified
##   casual      0.354          0.391
##   member      0.646          0.609
  • 35 % of identified rides were performed by casual riders.

  • 39 % of the non-identified rides were performed by casual riders.

It did not seem random.

I had visualized both ‘identified’ and ‘unidentified’ subsets side by side via geom_bin2d plot to check out the spatial point density.

ggplot(nodes) +
  geom_bin2d(mapping = aes(x = lng, y = lat), bins = 50) +
  scale_fill_gradient2(
    low = "#FFFFFF",
    mid = "#56B1F7",
    high = "#FF00FF",
    limits = c(0, 500000),
    midpoint = 500000 / 2,
    oob = scales::squish
  ) +
  ylim(41.61, 42.18) +
  xlim(-88.16, -87.44) +
  labs(title = "Map of starting and ending points") +
  facet_wrap(nodetype ~ .)

plotname <- as.integer(1)
ggsave(paste(sprintf("%02d", plotname), "jpg", sep = "."))

The two subsets were indeed different. Non-identified nodes were scattered away.

I decided to keep the subset of non-identified rides in analytics. Unfortunately, these rows were of no use for station traffic analysis.


MERGING IDS

For further analysis, I identified and merged duplicate station_ids.

Here’s the sequence of manipulations.

  1. Identified unique values of ‘station_id’.
freq_id <- as.data.frame(ftable(nodes$station_id)) %>%
  rename(id = Var1, n_id = Freq)

nrow(freq_id)
## [1] 1542

There were 1542 of them.

  1. Assumed that any ‘station_id’ was identified by the most common ‘station name’ and most common ‘latlng’ values.

  2. Found values of ‘latlng’ that correspond to two or more ‘station_id’ and identified the most frequent ‘station_id’ values among this subset.

latlng_per_id <- as.data.frame(ftable(nodes$latlng, nodes$station_id)) %>%
  filter(Freq > 0) %>%
  rename(latlng = Var1, id = Var2) %>%
  group_by(id) %>%
  arrange(desc(Freq)) %>%
  top_n(1, Freq) %>%
  rename(n_latlng = Freq) %>%
  distinct(id, .keep_all = TRUE)

head(latlng_per_id)
## # A tibble: 6 × 3
## # Groups:   id [6]
##   latlng          id           n_latlng
##   <fct>           <fct>           <int>
## 1 41.8925 -87.612 13022          119238
## 2 41.881 -87.6165 13300           70369
## 3 41.9115 -87.627 LF-005          70340
## 4 41.901 -87.624  13042           68041
## 5 41.889 -87.6385 KA1503000043    67233
## 6 41.903 -87.6315 TA1307000039    64554
  1. Found values of ‘station_name’ that correspond to two or more ‘station_id’ and identified the most frequent ‘station_id’ values among this subset.
name_per_id <- as.data.frame(ftable(nodes$station_name, nodes$station_id)) %>%
  filter(Freq > 0) %>%
  rename(name = Var1, id = Var2) %>%
  group_by(id) %>%
  top_n(1, Freq) %>%
  arrange(desc(Freq)) %>%
  rename(n_name = Freq) %>%
  distinct(id, .keep_all = TRUE)

head(name_per_id)
## # A tibble: 6 × 3
## # Groups:   id [6]
##   name                               id           n_name
##   <fct>                              <fct>         <int>
## 1 Streeter Dr & Grand Ave            13022        126958
## 2 DuSable Lake Shore Dr & Monroe St  13300         78078
## 3 DuSable Lake Shore Dr & North Blvd LF-005        75078
## 4 Michigan Ave & Oak St              13042         75058
## 5 Clark St & Elm St                  TA1307000039  69919
## 6 Kingsbury St & Kinzie St           KA1503000043  69093

Any distinct ‘station_id’ entry that shared ‘latlng’ or ‘station_name’ with other ‘station_id’ entries was step by step “absorbed” by the most frequent entry in few steps.

  1. Generated an ‘idnamelatlng’ table to list combinations of current ‘id’ and most frequent ‘latlng’ and ‘station_name’ entries that correspond to this ‘id’.
idnamelatlng <- merge(
  merge(
    freq_id,
    latlng_per_id,
    by = "id"
  ),
  name_per_id,
  by = "id"
) %>%
  arrange(desc(n_id))

head(idnamelatlng)
##             id   n_id          latlng n_latlng
## 1        13022 126958 41.8925 -87.612   119238
## 2        13300  78078 41.881 -87.6165    70369
## 3       LF-005  75078 41.9115 -87.627    70340
## 4        13042  75058  41.901 -87.624    68041
## 5 TA1307000039  69919 41.903 -87.6315    64554
## 6 KA1503000043  69093 41.889 -87.6385    67233
##                                 name n_name
## 1            Streeter Dr & Grand Ave 126958
## 2  DuSable Lake Shore Dr & Monroe St  78078
## 3 DuSable Lake Shore Dr & North Blvd  75078
## 4              Michigan Ave & Oak St  75058
## 5                  Clark St & Elm St  69919
## 6           Kingsbury St & Kinzie St  69093

This table had duplicates in the ‘name’ column, meaning that there were two or more ‘id’’s with the very same name.

  1. Any id (‘orig_id’) entry in this table was matched to the most frequent ‘id’ value (‘interim_id’) with the same ‘name’.
tdf <- idnamelatlng %>%
  group_by(name) %>%
  slice(which.max(n_id)) %>%
  select(name, id) %>%
  rename(interim_id = id)

idnamelatlng <- idnamelatlng %>%
  rename(orig_id = id) %>%
  merge(tdf, by = "name") %>%
  select(orig_id, n_id, latlng, n_latlng, name, n_name, interim_id)

head(idnamelatlng[duplicated(idnamelatlng$interim_id) |
  duplicated(idnamelatlng$interim_id, fromLast = TRUE), ] %>%
  arrange(desc(interim_id), desc(n_id)))
##        orig_id n_id           latlng n_latlng                     name n_name
## 1 TA1308000035 4417  41.8095 -87.592     4414  Lake Park Ave & 47th St   4417
## 2          812   19    41.81 -87.592        6  Lake Park Ave & 47th St     19
## 3 KA1504000114 1628  41.8955 -87.706     1626 Kedzie Ave & Chicago Ave   1628
## 4         1055   71 41.8955 -87.7065       62 Kedzie Ave & Chicago Ave     71
## 5 KA1503000055  491 41.7795 -87.6445      450     Halsted St & 63rd St    491
## 6          745    9 41.7795 -87.6415        4     Halsted St & 63rd St      9
##     interim_id
## 1 TA1308000035
## 2 TA1308000035
## 3 KA1504000114
## 4 KA1504000114
## 5 KA1503000055
## 6 KA1503000055
  1. Any ‘interim_id’ entry in this table was matched to the most frequent ‘interim_id’ value with the same ‘latlng’.
tdf <- idnamelatlng %>%
  arrange(desc(n_id)) %>%
  distinct(interim_id, .keep_all = TRUE) %>%
  group_by(latlng) %>%
  slice(which.max(n_id)) %>%
  select(latlng, interim_id) %>%
  rename(id = interim_id)

idnamelatlng <- merge(idnamelatlng, tdf, by = "latlng") %>%
  rename(orig_name = name, orig_n_id = n_id, orig_latlng = latlng) %>%
  merge(latlng_per_id %>% select(!n_latlng), by = "id") %>%
  merge(name_per_id %>% select(!n_name), by = "id") %>%
  select(
    orig_id, orig_n_id, orig_latlng, n_latlng, orig_name,
    n_name, interim_id, id, latlng, name
  )

head(idnamelatlng[duplicated(idnamelatlng$id) |
  duplicated(idnamelatlng$id, fromLast = TRUE), ] %>%
  arrange(desc(id), desc(orig_n_id)))
##        orig_id orig_n_id      orig_latlng n_latlng                   orig_name
## 1 TA1307000066      1210  41.849 -87.6755     1209      Damen Ave & Coulter St
## 2      PI00001       273  41.849 -87.6755      269        Damen Ave/Coulter St
## 3 TA1307000039     69919  41.903 -87.6315    64554           Clark St & Elm St
## 4      20249.0       859  41.903 -87.6315      488       N Clark St & W Elm St
## 5 TA1306000014     35094 41.9325 -87.6525    34569  Wilton Ave & Diversey Pkwy
## 6 chargingstx2      3299 41.9325 -87.6525     3105 Wilton Ave & Diversey Pkwy*
##   n_name   interim_id           id           latlng                       name
## 1   1210 TA1307000066 TA1307000066  41.849 -87.6755     Damen Ave & Coulter St
## 2    273      PI00001 TA1307000066  41.849 -87.6755     Damen Ave & Coulter St
## 3  69919 TA1307000039 TA1307000039  41.903 -87.6315          Clark St & Elm St
## 4    859      20249.0 TA1307000039  41.903 -87.6315          Clark St & Elm St
## 5  35094 TA1306000014 TA1306000014 41.9325 -87.6525 Wilton Ave & Diversey Pkwy
## 6   3299 chargingstx2 TA1306000014 41.9325 -87.6525 Wilton Ave & Diversey Pkwy
  • Introduced ‘view_merges’, a temporary data frame needed only for a visualization. This table included duplicates only and was properly grouped and sorted.
view_merges <- idnamelatlng %>%
  group_by(id) %>%
  filter(n() > 1) %>%
  mutate(n_id = sum(orig_n_id)) %>%
  arrange(desc(n_id), desc(orig_n_id)) %>%
  head(view_merges, n = 9) %>%
  ungroup() %>%
  mutate(row_number = row_number())

view_merges
## # A tibble: 9 × 12
##   orig_id       orig_n_id orig_latlng n_latlng orig_name n_name interim_id id   
##   <fct>             <int> <fct>          <int> <fct>      <int> <fct>      <fct>
## 1 TA1307000039      69919 41.903 -87…    64554 Clark St…  69919 TA1307000… TA13…
## 2 20249.0             859 41.903 -87…      488 N Clark …    859 20249.0    TA13…
## 3 TA1306000014      35094 41.9325 -8…    34569 Wilton A…  35094 TA1306000… TA13…
## 4 chargingstx2       3299 41.9325 -8…     3105 Wilton A…   3299 chargings… TA13…
## 5 chargingstx0       1564 41.9325 -8…     1459 Wilton A…   1564 chargings… TA13…
## 6 13075             10324 41.853 -87…    10307 Wentwort…  10324 13075      13075
## 7 chargingstx07      8904 41.853 -87…     8895 Wentwort…   8904 chargings… 13075
## 8 16991              2197 41.9545 -8…     2195 Milwauke…   2197 16991      16991
## 9 1110.0              131 41.9545 -8…       85 Milwauke…    131 1110.0     16991
## # ℹ 4 more variables: latlng <fct>, name <fct>, n_id <int>, row_number <int>
col.par <- function(n) sample(seq(0.3, 1, length.out = 50), n)

cols <- rainbow(13, s = col.par(13), v = col.par(13))[sample(1:13, 13)]

ggplot(
  view_merges,
  aes(
    x = fct_reorder(name, desc(n_id)),
    y = orig_n_id, fill = fct_reorder(orig_name, row_number)
  )
) +
  geom_col() +
  scale_fill_manual(values = cols) +
  theme(axis.text.x = element_text(angle = 45, vjust = 1, hjust = 1)) +
  labs(title = "Merging non-unique station_ids", x = "name") +
  guides(fill = guide_legend(title = "orig_name"))

plotname <- plotname + 1
ggsave(paste(sprintf("%02d", plotname), "jpg", sep = "."))

nrow(as.data.frame(unique(idnamelatlng$id)))
## [1] 1466

For example, 69,919 observations named “Clark St & Elm St” and 859 observations named “N Clark St & W Elm St” corresponded to similar most frequent values of ‘latitude’ and ‘longitude’.

Merged them into a single category defined by the combination of the most frequent values of ‘station_id’, ‘name’, ‘latitude’ and ‘longitude’, specifically, [“TA1307000039”, “Clark St & Elm St”, 41.903, -87.631].

Total number of unique ‘station_id’ entries reduced from 1542 to 1466. It was still far from the number 692 mentioned by stakeholders. (A question to be asked).

  • Introduced numerical columns lat, lng.
idnamelatlng[c("lat", "lng")] <- str_split_fixed(idnamelatlng$latlng, " ", 2)
idnamelatlng <- transform(idnamelatlng,
  lat = as.numeric(lat),
  lng = as.numeric(lng)
)
  • Cleaned up ‘idnamelatlng’, removing all auxiliary columns.
idnamelatlng <- idnamelatlng %>%
  select(orig_id, id, name, lat, lng)
  • Removed temporary data frames.
rm(freq_id)
rm(latlng_per_id)
rm(name_per_id)
  • Filled the main data frame ‘rides’ with consistent information about station_id, station_name, station_latitude and station_longitude.
rides <- rides %>%
  rename(
    orig_end_station_id = end_station_id,
    orig_end_station_name = end_station_name,
    orig_start_station_id = start_station_id,
    orig_start_station_name = start_station_name
  )

rides <- left_join(rides,
  idnamelatlng %>% rename(orig_start_station_id = orig_id),
  by = "orig_start_station_id"
) %>%
  rename(
    start_station_id = id,
    start_station_name = name,
    start_station_lat = lat,
    start_station_lng = lng
  )

rides <- left_join(rides,
  idnamelatlng %>% rename(orig_end_station_id = orig_id),
  by = "orig_end_station_id"
) %>%
  rename(
    end_station_id = id,
    end_station_name = name,
    end_station_lat = lat,
    end_station_lng = lng
  ) %>%
  select(!contains("orig"))

TRANSFORMING DATA

  • Calculated columns for:

    • weekdays,

    • months,

    • ‘hours’ (numeric),

    • ‘time of the day’ (string),

    • travel distance,

    • average travel speed of the ride.

  • Calculated a distance between station coordinates and ride start or end points if they were attributed to a station. These columns are needed to identify the outliers erroneously attributed to a station.

For calculating travel distance (km) based on longitude and latitude, I had to convert degrees to radians. Formula for distance produces NaN, if start and end points collapse. Therefore, replaced NaNs by nulls.

# take a note that business day calculation is far from perfect 
# and does not account for holidays
rides <- rides %>%
  mutate(weekday = c(
    "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday",
    "Friday", "Saturday"
  )[as.POSIXlt(rides$started_at)$wday + 1]) %>%
  mutate(bizday = c(
    "Weekend", "Business day", "Business day", "Business day", "Business day",
    "Business day", "Weekend"
  )[as.POSIXlt(rides$started_at)$wday + 1]) %>%
  mutate(month = as.factor(c(
    "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul",
    "Aug", "Sep", "Oct", "Nov", "Dec"
  )[as.POSIXlt(rides$started_at)$mon + 1])) %>%
  mutate(hours = as.integer(format(as.POSIXct(started_at), format = "%H"))) %>%
  mutate(duration = ended_at - started_at) %>%
  mutate(travel_distance = as.numeric(
    round(acos(
      sin(deg2rad(start_lat)) * sin(deg2rad(end_lat)) +
        cos(deg2rad(start_lat)) * cos(deg2rad(end_lat)) *
          cos(deg2rad(end_lng) - deg2rad(start_lng))
    )
    * 6371 * 1000, digits = 0)
  )) %>%
  mutate(start_distance_to_station = as.numeric(
    round(
      acos(
        sin(deg2rad(start_lat)) * sin(deg2rad(start_station_lat)) +
          cos(deg2rad(start_lat)) * cos(deg2rad(start_station_lat)) *
            cos(deg2rad(start_station_lng) - deg2rad(start_lng))
      ) *
        6371 * 1000,
      digits = 0
    )
  )) %>%
  mutate(end_distance_to_station = as.numeric(
    round(
      acos(
        sin(deg2rad(end_station_lat)) * sin(deg2rad(end_lat)) +
          cos(deg2rad(end_station_lat)) * cos(deg2rad(end_lat)) *
            cos(deg2rad(end_lng) - deg2rad(end_station_lng))
      ) * 6371 * 1000,
      digits = 0
    )
  )) %>%
  mutate(travel_distance = ifelse(
    is.nan(travel_distance), 0, travel_distance
  )) %>%
  mutate(start_distance_to_station = ifelse(
    is.nan(start_distance_to_station), 0, start_distance_to_station
  )) %>%
  mutate(end_distance_to_station = ifelse(
    is.nan(end_distance_to_station), 0, end_distance_to_station
  )) %>%
  mutate(avg_travel_speed = travel_distance / as.numeric(duration)
    * 3600 / 1000)
  • Ordered factors.
rides$time_of_the_day <- factor(
  x = rides$hours,
  levels = sort(unique(rides$hours)),
  labels = c(
    "12AM", "1AM", "2AM", "3AM", "4AM", "5AM", "6AM", "7AM", "8AM",
    "9AM", "10AM", "11AM", "12PM", "1PM", "2PM", "3PM", "4PM", "5PM",
    "6PM", "7PM", "8PM", "9PM", "10PM", "11PM"
  )
)
months_as_factor <- factor(rides$month,
  ordered = TRUE,
  levels = c(
    "Jan", "Feb", "Mar", "Apr", "May", "Jun",
    "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"
  )
)
  • Transformed Citi Bike data frame in a similar manner.
jc_rides <- jc_rides %>%
  mutate(bizday = c(
    "Weekend", "Business day", "Business day", "Business day", "Business day",
    "Business day", "Weekend"
  )[as.POSIXlt(jc_rides$started_at)$wday + 1]) %>%
  mutate(month = as.factor(c(
    "Jan", "Feb", "Mar", "Apr", "May", "Jun",
    "Jul", "Aug", "Sep", "Oct", "Nov",
    "Dec"
  )[as.POSIXlt(jc_rides$started_at)$mon + 1])) %>%
  mutate(hours = as.integer(format(as.POSIXct(started_at), format = "%H"))) %>%
  mutate(duration = ended_at - started_at) %>%
  mutate(travel_distance = as.numeric(
    round(acos(
      sin(deg2rad(start_lat)) * sin(deg2rad(end_lat)) +
        cos(deg2rad(start_lat)) * cos(deg2rad(end_lat)) *
          cos(deg2rad(end_lng) - deg2rad(start_lng))
    ) *
      6371 * 1000, digits = 0)
  )) %>%
  mutate(travel_distance = ifelse(is.nan(travel_distance),
    0, travel_distance
  )) %>%
  mutate(avg_travel_speed = travel_distance /
    as.numeric(duration) * 3600 / 1000)

jc_rides$time_of_the_day <- factor(
  x = jc_rides$hours,
  levels = sort(unique(jc_rides$hours)),
  labels = c(
    "12AM", "1AM", "2AM", "3AM", "4AM", "5AM", "6AM", "7AM", "8AM", "9AM",
    "10AM", "11AM", "12PM", "1PM", "2PM", "3PM", "4PM", "5PM", "6PM",
    "7PM", "8PM", "9PM", "10PM", "11PM"
  )
)

Phase: Process

  • Cleaned the main data frame.

Number of rows before cleaning:

nrow(rides)
## [1] 5674449
  • Removed rows with negative duration of the ride
rides <- rides %>%
  filter(duration > 0)

I prefer “3 sigma” rule for filtering out outliers.

  • Checked out descriptive statistics for the distance to station (when applicable),
summary(rides %>%
  select(start_distance_to_station, end_distance_to_station) %>%
  filter(!is.na(start_distance_to_station), !is.na(end_distance_to_station)))
##  start_distance_to_station end_distance_to_station
##  Min.   :    0.00          Min.   :      0        
##  1st Qu.:   15.00          1st Qu.:     15        
##  Median :   21.00          Median :     20        
##  Mean   :   28.45          Mean   :     31        
##  3rd Qu.:   27.00          3rd Qu.:     25        
##  Max.   :34629.00          Max.   :9815442
  • Checked out descriptive statistics for the average travel speed: ‘ts_mean’ is the mean value, ‘ts_sd’ is the standard deviation,
# Now that we understand descriptive statistics well, we can replace missing
# values (N/A) with zeros. Any resulting bias is no longer a concern.

rides$start_distance_to_station[is.na(rides$start_distance_to_station)] <- 0
rides$end_distance_to_station[is.na(rides$end_distance_to_station)] <- 0

rides$avg_travel_speed[is.infinite(rides$avg_travel_speed)] <- 50
rides$avg_travel_speed[rides$avg_travel_speed > 50] <- 50
rides$avg_travel_speed[is.na(rides$avg_travel_speed)] <- 0
ts_mean <- mean(rides$avg_travel_speed)
ts_sd <- sd(rides$avg_travel_speed)
summary(rides$avg_travel_speed)
##    Min. 1st Qu.  Median    Mean 3rd Qu.    Max. 
##   0.000   7.685  10.970  10.860  14.209  50.000
ts_sd
## [1] 5.849725
  • Checked out descriptive statistics for the duration of the ride: ‘dur_mean’ is the mean value, ‘dur_sd’ is the standard deviation.
rides$duration[is.infinite(rides$duration)] <- 3600 * 24
rides$duration[rides$duration > 3600 * 24] <- 3600 * 24
rides$duration[is.na(rides$duration)] <- 0
dur_mean <- mean(rides$duration)
dur_sd <- sd(rides$duration)
summary(as.integer(rides$duration))
##    Min. 1st Qu.  Median    Mean 3rd Qu.    Max. 
##       1     326     573    1013    1018   86400
dur_sd
## [1] 3500.094

Mean and median values weren’t close. This was a clear sign of outliers in population.

  • Removed rows with:

    • no latitude and longitude of the starting or ending point,

    • zero latitude and longitude values,

    • unrealistic speed or duration of ride,

    • start point or end point erroneously attributed to incorrect station.

rides <- rides %>%
  filter(!is.na(member_casual) & !is.na(start_lat) & !is.na(end_lat) & !is.na(start_lng) & !is.na(end_lng)) %>%
  filter(!(start_lat == 0) & !(end_lat == 0) & !(start_lng == 0) & !(end_lng == 0)) %>%
  filter(avg_travel_speed >= 0 & avg_travel_speed < ts_mean + 3 * ts_sd) %>%
  filter(duration < dur_mean + 3 * dur_sd) %>%
  filter(start_distance_to_station < 111 | end_distance_to_station < 111)

nrow(rides)
## [1] 5618027

After the cleaning, main data frame comprised 5,618,027 rows.

Excluded 56,442 rows, or 0.99% of the data.

Assumed that mean values and standard deviations for average speed and trip duration calculated in Chicago were good enough for the cleaning of Citi Bike outliers, too.

  • Processed the Citi Bike data frame in a similar manner.
jc_rides <- jc_rides %>%
  filter(!is.na(member_casual) & !is.na(start_lat) & !is.na(end_lat) & !is.na(start_lng) & !is.na(end_lng)) %>%
  filter(!(start_lat == 0) & !(end_lat == 0) & !(start_lng == 0) & !(end_lng == 0)) %>%
  filter(avg_travel_speed >= 0 & avg_travel_speed < ts_mean + 3 * ts_sd) %>%
  filter(duration > 0 & duration < dur_mean + 3 * dur_sd)

rm(ts_mean, ts_sd, dur_mean, dur_sd)

Documentation of analysis

Phase: Analyze

SPATIAL ANALYTICS

For analytics of the spatial distribution of trips, an accuracy of up to 2 decimal places was chosen.

  • With clean data, reconstructed the ‘nodes’ data frame and rounded latitude and longitude to 2 decimal places.
nodes <- rbind(
  rides %>%
    select(start_station_name, start_station_id, start_lat, start_lng, member_casual) %>%
    rename(station_name = start_station_name, station_id = start_station_id) %>%
    rename(lat = start_lat, lng = start_lng) %>%
    mutate(point_type = "start"),
  rides %>%
    select(end_station_name, end_station_id, end_lat, end_lng, member_casual) %>%
    rename(station_name = end_station_name, station_id = end_station_id) %>%
    rename(lat = end_lat, lng = end_lng) %>%
    mutate(point_type = "end")
) %>%
  mutate(lat = round(lat, 2), lng = round(lng, 2)) %>%
  mutate(latlng = paste(lat, lng, sep = " "))
  • For any unique node, assigned number of starts and ends of rides by member/casual rider.
# Summarize ride counts by latlng and rider type

tdf <- nodes %>%
  mutate(point_type_member_casual = paste(point_type, member_casual, sep = "_")) %>%
  group_by(latlng, lat, lng, point_type_member_casual, point_type, member_casual) %>%
  summarize(count = n())

nodes <- tdf %>%
  pivot_wider(names_from = point_type_member_casual, values_from = count, names_prefix = "n_", values_fill = 0) %>%
  mutate(
    n_casual = n_end_casual + n_start_casual,
    n_member = n_end_member + n_start_member,
    n = n_end_member + n_end_casual + n_start_member + n_start_casual
  ) %>%
  group_by(latlng, lat, lng) %>%
  summarise(across(where(is.numeric), sum, na.rm = TRUE))

head(nodes %>% arrange(desc(n)))
## # A tibble: 6 × 10
## # Groups:   latlng, lat [6]
##   latlng       lat   lng n_end_casual n_end_member n_start_member n_start_casual
##   <chr>      <dbl> <dbl>        <int>        <int>          <int>          <int>
## 1 41.88 -87…  41.9 -87.6        62701       196585         192114          64252
## 2 41.89 -87…  41.9 -87.6        73917       132769         135930          72329
## 3 41.9 -87.…  41.9 -87.6        59592       134534         137516          62430
## 4 41.88 -87…  41.9 -87.6        51122       125964         124864          55409
## 5 41.89 -87…  41.9 -87.6        44934       127203         131335          49655
## 6 41.89 -87…  41.9 -87.6        63405       102715         102537          63124
## # ℹ 3 more variables: n_casual <int>, n_member <int>, n <int>
  • Plotted spatial density of rides.
nodes <- nodes %>%
  mutate(casual_percentage = (n_end_casual + n_start_casual) / n) %>%
  mutate(norm_ride_density = n / 517457)

ggplot(nodes) +
  geom_point(mapping = aes(x = lng, y = lat, color = casual_percentage, size = n, alpha = n, shape = )) +
  ylim(41.61, 42.18) +
  xlim(-88.16, -87.44) +
  labs(title = "Casual riders tend to use bikes at peripheral areas") +
  scale_color_continuous(high = "red", low = "yellow") +
  scale_size_continuous(range = c(0.9, 4)) +
  scale_alpha_continuous(range = c(0.5, 1))

plotname <- plotname + 1
ggsave(paste(sprintf("%02d", plotname), "jpg", sep = "."))

It was clear that the most of the activities of the Cyclistic clients were concentrated in the city center.

  • Divided area into two zones:

    1. City center with high ride density.

    2. Periphery with lower ride density.

The city center zone is the concatenation of two circles. First circle is centered at [41.89,-87.64] and has radius equal to 0.044 degrees. Second circle has center in [41.94.-87.64] and radius of 0.028 degrees. These values were selected manually.

nodes <- nodes %>%
  mutate(
    zones =
      c("Periphery", "Center")[(as.integer(
        (((lat - 41.94)^2 + (lng + 87.64)^2)^(0.5) < 0.028) |
          (((lat - 41.89)^2 + (lng + 87.64)^2)^(0.5) < 0.044)
      )) + 1]
  )

rides <- rides %>%
  mutate(start_zone = as.character(as.integer(
    (((start_lat - 41.94)^2 + (start_lng + 87.64)^2)^(0.5) < 0.028) |
      (((start_lat - 41.89)^2 + (start_lng + 87.64)^2)^(0.5) < 0.044)
  ))) %>%
  mutate(end_zone = as.character(as.integer(
    (((end_lat - 41.94)^2 + (end_lng + 87.64)^2)^(0.5) < 0.028) |
      (((end_lat - 41.89)^2 + (end_lng + 87.64)^2)^(0.5) < 0.044)
  )))

rides$travel_type <- paste(
  c("at the periphery", "To/From center", "Within center")
  [as.integer(rides$start_zone) + as.integer(rides$end_zone) + 1]
)
rides$travel_type_binary <- paste(
  c("To/From/Within center", "at the periphery")
  [(1 - as.integer(rides$start_zone)) * (1 - as.integer(rides$end_zone)) + 1]
)
  • Plotted zones.
tdf <- nodes %>%
  group_by(zones) %>%
  summarise(
    casual_rides_per_node = mean((n_start_casual + n_end_casual)),
    members_ride_per_node = mean((n_start_member + n_end_member))
  ) %>%
  as.data.frame()

row.names(tdf) <- tdf$zones

ggplot(nodes) +
  geom_point(
    mapping = aes(
      x = lng, y = lat,
      colour = zones, fill = zones,
      alpha = n, size = n
    )
  ) +
  ylim(41.61, 42.18) +
  xlim(-88.16, -87.44) +
  labs(
    title = "Central and peripheral zones",
    subtitle = "Ride density in city center is higher"
  ) +
  scale_size_continuous(range = c(0.9, 4)) +
  scale_alpha_continuous(range = c(0.5, 1)) +
  scale_colour_manual(values = c("#00A4CCFF", "#F95700FF"))

plotname <- plotname + 1
ggsave(paste(sprintf("%02d", plotname), "jpg", sep = "."))

round(tdf %>% select(!zones))
##           casual_rides_per_node members_ride_per_node
## Center                    43522                 76796
## Periphery                  1314                  2429
  • Out of curiosity, plotted nodes where only one trip took place (and, more precisely, ended) throughout the year.

Interesting fact: casual riders are more likely to leave bike somewhere very far from stations or public racks. The next leaflet depicts the most unusual spots, where a ride ended, including highways, railroads and Lake Michigan. Casual rides are colored light red.

tdf <- nodes %>%
  filter(n == 1) %>%
  mutate(mc = cut(casual_percentage,
    breaks = c(-0.01, 0.5, 1.01),
    labels = c("m", "C")
  ))

getColor <- function(tdf) {
  sapply(tdf$mc, function(mc) {
    if (mc == "m") {
      "blue"
    } else {
      "lightred"
    }
  })
}

icons <- awesomeIcons(
  icon = "heart",
  markerColor = getColor(tdf),
  iconColor = "white",
  library = "ion"
)

leaflet(tdf) %>%
  addTiles() %>%
  addAwesomeMarkers(~lng, ~lat, icon = icons, popup = ~mc)

MONTHLY PATTERNS

  • Checked monthly patterns in Chicago.

When comparing 5 business days in a left part of the plot to 2 weekend days in a right part of a plot, it is important to show average value of rides per day, not the sum.

rides_per_day <- rides %>%
  select(
    started_at, month, member_casual, travel_type,
    travel_type_binary, bizday, time_of_the_day, rideable_type
  ) %>%
  mutate(ride_date = as.Date.POSIXct(started_at)) %>%
  group_by(
    ride_date, month, member_casual, travel_type,
    travel_type_binary, bizday, time_of_the_day, rideable_type
  ) %>%
  summarize(rides_count = n()) %>%
  ungroup()

tdf <- rides_per_day %>%
  group_by(ride_date, month, member_casual, bizday) %>%
  summarize(rides_count = sum(rides_count)) %>%
  ungroup()

tdf <- tdf %>%
  group_by(month, member_casual, bizday) %>%
  summarize(avg_rides_count = round(mean(rides_count), 0)) %>%
  ungroup()

tdf$month <- factor(tdf$month, levels = month.abb)

ggplot(
  data = tdf,
  mapping = aes(x = month, y = avg_rides_count, fill = member_casual)
) +
  geom_bar(stat = "identity", position = position_dodge()) +
  ylim(0, 16000) +
  facet_wrap(bizday ~ .) +
  labs(
    title = "Chicago monthly patterns",
    subtitle = "Average number of rides per day"
  ) +
  theme(axis.text.x = element_text(angle = 45, vjust = 1, hjust = 1))

plotname <- plotname + 1
ggsave(paste(sprintf("%02d", plotname), "jpg", sep = "."))

Casual riding decreases during winter, indicating it’s more of a leisure activity. Casual riders activity increases on weekends, Cyclistic members stays the same or drops insignificantly.

The leisure cyclists ride for leisure, prefer warm weather and more active during weekends. It seemed reasonable to assume that a significant part of the weekend rides are leisure rides. The presence of leisure cyclists among the members demonstrates the attractiveness of membership for them. Nevertheless, Cyclistic struggles to convert casual clients into members.

There are also regular everyday cyclists. It is a category of cyclists who ride for their everyday errands, regardless of the month or weather.

There are commuting cyclists. It is a category of clients who ride predominantly on weekdays, regardless of the month or weather.

Finally, inactive members are the members who pay for membership but rarely ride or do not ride at all. For the current study, they are practically invisible.

It is important to find the difference between those leisure cyclists who are already in the program and those who aren’t. On weekends, both groups are seemingly equally active from May to September. The member rides to casual rides ratio is about 1:1. Both groups become less active in cold months, but Cyclistic members are way more tolerant to weather. In January, the member to casual ratio rises dramatically to almost 4:1.

The number of unique casual clients must be higher than the number of unique leisure cyclists among Cyclistic members, despite the seemingly equal number of ‘leisure rides’ in both categories.

  • Checked out the same kind of data from Jersey City.
jc_rides_per_day <- jc_rides %>%
  select(started_at, month, member_casual, bizday, time_of_the_day) %>%
  mutate(ride_date = as.Date.POSIXct(started_at)) %>%
  group_by(ride_date, month, member_casual, bizday, time_of_the_day) %>%
  summarize(rides_count = n()) %>%
  ungroup()

tdf <- jc_rides_per_day %>%
  group_by(ride_date, month, member_casual, bizday) %>%
  summarize(rides_count = sum(rides_count)) %>%
  ungroup()

tdf <- tdf %>%
  group_by(month, member_casual, bizday) %>%
  summarize(avg_rides_count = round(mean(rides_count), 0)) %>%
  ungroup()

tdf$month <- factor(tdf$month, levels = month.abb)

ggplot(
  data = tdf,
  mapping = aes(x = month, y = avg_rides_count, fill = member_casual)
) +
  geom_bar(stat = "identity", position = position_dodge()) +
  ylim(0, 16000) +
  facet_wrap(bizday ~ .) +
  labs(
    title = "Jersey City monthly patterns",
    subtitle = "Average number of rides per day"
  ) +
  theme(axis.text.x = element_text(angle = 45, vjust = 1, hjust = 1))

plotname <- plotname + 1
ggsave(paste(sprintf("%02d", plotname), "jpg", sep = "."))

Compared Chicago ‘Cyclistic’ patterns to the Jersey City ‘Citi Bike’ data.

City Number of rides Population Rides per resident
Chicago 5,648,082 2,697,000 2.1
Jersey City 901,280 283 927 3.2

Given the number of shared bike rides and the size of the population, the market penetration in Jersey City is higher than in Chicago. The member-to-casual ratio in Jersey City is also higher than in Chicago. This is definitely a sign that the marketing team should review and analyze the marketing history of Citi Bike and learn from their experience.


DAILY PATTERNS

  • Calculated average ride count per hour and plotted daily patterns:
tdf <- rides_per_day %>%
  filter(travel_type_binary == "To/From/Within center") %>%
  group_by(ride_date, time_of_the_day, member_casual, bizday) %>%
  summarize(rides_count = sum(rides_count)) %>%
  ungroup()

tdf <- tdf %>%
  group_by(time_of_the_day, member_casual, bizday) %>%
  summarize(avg_rides_count = round(mean(rides_count), 0)) %>%
  ungroup()

ggplot(
  data = tdf,
  mapping = aes(x = time_of_the_day, y = avg_rides_count, fill = member_casual)
) +
  geom_bar(stat = "identity", position = position_dodge()) +
  ylim(0, 1100) +
  facet_wrap(bizday ~ .) +
  labs(
    title = "Chicago daily patterns in downtown",
    subtitle = "Average number of rides per hour"
  ) +
  theme(axis.text.x = element_text(angle = 45, vjust = 1, hjust = 1, size = 4))

plotname <- plotname + 1
ggsave(paste(sprintf("%02d", plotname), "jpg", sep = "."))

On business days, commuting cyclists show two peaks of activity, before and after work. (From prior information, it is known that 30% of the users commute to work each day using bikes). Casual riders tend to use bikes mostly during the second half of the day.

Weekend daily patterns of casual riders and Cyclistic members are almost indistinguishable based on this chart.

Commuting to work by bike is more common in the city center.

tdf2 <- rides_per_day %>%
  filter(travel_type_binary == "at the periphery") %>%
  group_by(ride_date, time_of_the_day, member_casual, bizday) %>%
  summarize(rides_count = sum(rides_count)) %>%
  ungroup()

tdf2 <- tdf2 %>%
  group_by(time_of_the_day, member_casual, bizday) %>%
  summarize(avg_rides_count = round(mean(rides_count), 0)) %>%
  ungroup()

ggplot(
  data = tdf2,
  mapping = aes(
    x = time_of_the_day,
    y = avg_rides_count,
    fill = member_casual
  )
) +
  geom_bar(stat = "identity", position = position_dodge()) +
  ylim(0, 1100) +
  facet_wrap(bizday ~ .) +
  labs(
    title = "Chicago daily patterns at the periphery",
    subtitle = "Average number of rides per hour"
  ) +
  theme(axis.text.x = element_text(angle = 45, vjust = 1, hjust = 1, size = 4))

plotname <- plotname + 1
ggsave(paste(sprintf("%02d", plotname), "jpg", sep = "."))

At the periphery, members to casual ratio is higher than in the city center.

ggplot(
  data = tdf %>% filter(
    member_casual == "casual",
    bizday == "Business day"
  ),
  mapping = aes(
    x = time_of_the_day, y = avg_rides_count,
    fill = member_casual
  )
) +
  geom_bar(stat = "identity", position = position_dodge()) +
  labs(
    title = "Limited potential to involve more weekly cyclists",
    subtitle = "Average number of **casual** rides per hour **within city center**"
  ) +
  annotate(
    geom = "text", x = 8.5, y = 300,
    label = "Note the small peak at 8 a.m. \n\nSome casual riders follow\nregular weekday cyclists pattern\nand commute to work by bike \n in the morning\n", size = 3
  ) +
  theme(axis.text.x = element_text(angle = 45, vjust = 1, hjust = 1, size = 8))

plotname <- plotname + 1
ggsave(paste(sprintf("%02d", plotname), "jpg", sep = "."))

There are commuting casual riders, too. Some casual riders commute to work by bike in the morning on business days. However, it is safe to conclude that the majority of the commuting cyclists is already in the club.

tdf <- jc_rides_per_day %>%
  group_by(ride_date, time_of_the_day, member_casual, bizday) %>%
  summarize(rides_count = sum(rides_count)) %>%
  ungroup()

tdf <- tdf %>%
  group_by(time_of_the_day, member_casual, bizday) %>%
  summarize(avg_rides_count = round(mean(rides_count), 0)) %>%
  ungroup()

ggplot(data = tdf, mapping = aes(
  x = time_of_the_day, y = avg_rides_count,
  fill = member_casual
)) +
  geom_bar(stat = "identity", position = position_dodge()) +
  ylim(0, 1100) +
  facet_wrap(bizday ~ .) +
  labs(
    title = "Jersey City daily patterns",
    subtitle = "Average number of rides per hour"
  ) +
  theme(axis.text.x = element_text(
    angle = 45,
    vjust = 1, hjust = 1, size = 4
  ))

plotname <- plotname + 1
ggsave(paste(sprintf("%02d", plotname), "jpg", sep = "."))

Citi Bike data demonstrated the repetitiveness of daily pattern, indicating consistent usage habits among the riders.

tdf <- rides_per_day %>%
  group_by(ride_date, time_of_the_day, member_casual, bizday, rideable_type) %>%
  summarize(rides_count = sum(rides_count)) %>%
  ungroup()

tdf <- tdf %>%
  group_by(time_of_the_day, member_casual, bizday, rideable_type) %>%
  summarize(avg_rides_count = round(mean(rides_count), 0)) %>%
  ungroup()

tdf$rideable_type <- factor(tdf$rideable_type,
  ordered = TRUE,
  levels = c(
    "classic_bike", "electric_bike",
    "docked_bike"
  )
)

ggplot(
  data = tdf,
  mapping = aes(
    x = time_of_the_day,
    y = avg_rides_count,
    fill = member_casual
  )
) +
  geom_bar(stat = "identity", position = position_dodge()) +
  facet_wrap(bizday ~ rideable_type) +
  labs(
    title = "Popularity of different types of bikes",
    subtitle = "Only casual riders use docked bikes"
  ) +
  theme(axis.text.x = element_text(
    angle = 45, vjust = 1, hjust = 1,
    size = 4
  ))

plotname <- plotname + 1
ggsave(paste(sprintf("%02d", plotname), "jpg", sep = "."))

Docked bikes are only used by casual riders.


RIDE DURATION AND SPEED

tdf <-
  rbind(
    rides %>%
      group_by(member_casual) %>%
      summarise(
        average_distance = mean(travel_distance),
        average_speed = mean(avg_travel_speed),
        average_duration = mean(duration)
      ) %>%
      as.data.frame() %>%
      mutate(city = "Chicago"),
    jc_rides %>%
      group_by(member_casual) %>%
      summarise(
        average_distance = mean(travel_distance),
        average_speed = mean(avg_travel_speed),
        average_duration = mean(duration)
      ) %>%
      as.data.frame() %>%
      mutate(city = "Jersey City")
  )

ggplot(
  data = tdf,
  mapping = aes(x = city, y = average_duration, fill = member_casual)
) +
  geom_bar(stat = "identity", position = position_dodge()) +
  labs(title = "Average ride duration")

plotname <- plotname + 1
ggsave(paste(sprintf("%02d", plotname), "jpg", sep = "."))

Casual bike rides are longer.

  • Plotted average travel speed v. ride duration for electric bikes and for classic bikes separately.
tdf <-
  rbind(
    rides %>%
      # excluded round trips
      filter(travel_distance > 50) %>%
      select(avg_travel_speed, duration, member_casual, rideable_type) %>%
      mutate(city = "Chicago"),
    jc_rides %>%
      filter(travel_distance > 50) %>%
      select(avg_travel_speed, duration, member_casual, rideable_type) %>%
      mutate(city = "Jersey City")
  )

# plotting was very slow, so I decided that 1/4th of the population
# would be sufficient for an accurate representation.

tdf <- tdf %>%
  mutate(rnd_num = ceiling(runif(nrow(tdf), min = 0, max = 4))) %>%
  filter(rnd_num == 1) %>%
  filter()

tdf2 <- tdf %>%
  filter(rideable_type == "electric_bike")

ggplot(data = tdf2, aes(x = duration, y = avg_travel_speed)) +
  geom_bin_2d() +
  scale_fill_continuous(high = "lightblue", low = "white") +
  geom_smooth(
    data = tdf2 %>% filter(member_casual == "casual"),
    aes(color = "casual")
  ) +
  geom_smooth(
    data = tdf2 %>% filter(member_casual == "member"),
    aes(color = "member")
  ) +
  labs(title = "Electric Bikes: Travel Speed vs Ride Duration") +
  ylim(0, 20) +
  xlim(0, 7200) +
  facet_wrap(~city)

plotname <- plotname + 1
ggsave(paste(sprintf("%02d", plotname), "jpg", sep = "."))

tdf2 <- tdf %>%
  filter(rideable_type == "classic_bike")

ggplot(data = tdf2, aes(x = duration, y = avg_travel_speed)) +
  geom_bin_2d() +
  scale_fill_continuous(high = "lightblue", low = "white") +
  geom_smooth(
    data = tdf2 %>% filter(member_casual == "casual"),
    aes(color = "casual")
  ) +
  geom_smooth(
    data = tdf2 %>% filter(member_casual == "member"),
    aes(color = "member")
  ) +
  labs(title = "Classic Bikes: Travel Speed vs Ride Duration") +
  ylim(0, 20) +
  xlim(0, 7200) +
  facet_wrap(~city)

plotname <- plotname + 1
ggsave(paste(sprintf("%02d", plotname), "jpg", sep = "."))

Speed: Note how Cyclistic members are focused in their desire to travel from point A to point B. They typically outpace casual riders, a trend that holds true even on weekends. This is likely a result of their regular cycling habits and training.

Ride Frequency: The enhanced training of Cyclistic members can be explained naturally by a higher ride frequency.

Number of Unique Clients: Despite similar numbers of weekend rides, the lower frequency of rides per individual casual rider suggests that the total number of casual riders is greater. This indicates a broader target audience.

Chicago vs. Jersey City: Club members and casual riders speed patterns in Jersey City are similar, indicating that Citi Bike managed to encourage leisure cyclists to join the membership program. Unlike Jersey City, Chicago might see a larger speed difference between club members and casual riders. This could be due to a higher proportion of trained, regular cyclists among club members in Chicago and the prevalence of longer, slower rides in the city.

Marketing Approach: The less enthusiastic casual riders require a different marketing strategy.


RUSH HOURS

  • Moved on to identify the most popular destinations among casual customers and Cyclistic members during peak hours on business days.
rides_per_station <- rides %>%
  filter(!is.na(end_station_id)) %>%
  group_by(end_station_id, end_station_name, member_casual, hours, bizday) %>%
  summarize(n = sum(n())) %>%
  ungroup()


bizday_rush_hours_rides <-
  rides_per_station %>%
  filter((hours == 7 | hours == 8) & bizday == "Business day") %>%
  group_by(end_station_id, end_station_name, member_casual) %>%
  summarize(n = sum(n)) %>%
  ungroup() %>%
  group_by(end_station_name)

tdf <- bizday_rush_hours_rides %>%
  group_by(end_station_id, end_station_name) %>%
  summarize(n = sum(n)) %>%
  ungroup() %>%
  top_n(10, n)

tdf2 <- bizday_rush_hours_rides %>%
  filter(end_station_id %in% tdf$end_station_id)

ggplot(tdf2, aes(
  x = fct_reorder(end_station_name, n),
  y = n, fill = member_casual
)) +
  geom_bar(stat = "identity") +
  coord_flip() +
  labs(
    title = "Most popular destinations during rush hours on weekdays",
    x = " "
  )

plotname <- plotname + 1
ggsave(paste(sprintf("%02d", plotname), "jpg", sep = "."))

During morning rush hours on weekdays, member trips prevail, accounting for 85% of the total at the busiest stations.

  • Plotted number of casual rides versus number of member rides and checked whether there was any correlation.
casual_threshold <- 700
member_threshold <- 3700

tdf <-
  full_join(
    bizday_rush_hours_rides %>%
      filter(member_casual == "member") %>%
      select(!member_casual) %>%
      rename(n_member = n),
    bizday_rush_hours_rides %>%
      filter(member_casual == "casual") %>%
      select(end_station_id, n) %>%
      rename(n_casual = n)
  )

tdf[is.na(tdf)] <- 0

tdf <- tdf %>%
  mutate(n = n_casual + n_member)

model <- lm(n_casual ~ n_member, data = tdf %>% filter(n > 1000))
r_squared <- summary(model)$r.squared

ggplot(tdf %>% filter(n > 1000), aes(n_member, y = n_casual, size = n)) +
  geom_rect(aes(
    xmin = 0, xmax = member_threshold, ymin = casual_threshold,
    ymax = 1700
  ), fill = "#F8766D", alpha = 0.03) +
  geom_rect(aes(
    xmin = member_threshold, xmax = 7000, ymin = casual_threshold,
    ymax = 1700
  ), fill = "#7C9B99", alpha = 0.03) +
  geom_rect(aes(
    xmin = member_threshold, xmax = 7000, ymin = 0000,
    ymax = casual_threshold
  ), fill = "#00BFC4", alpha = 0.03) +
  geom_point() +
  annotate("text",
    x = 4000, y = 200,
    label = paste("R-squared =", round(r_squared, 2))
  ) +
  geom_label_repel(
    data = tdf %>% filter(n_casual > casual_threshold |
      n_member > member_threshold), # Filter data first
    aes(label = end_station_name, hjust = 0), size = 3
  ) +
  annotate("text",
    x = 50, y = 1700 - 50,
    label = "Popular\namong\ncasual riders",
    color = "#FFFFFF", hjust = 0, vjust = 1
  ) +
  annotate("text",
    x = 7000 - 50, y = 1700 - 50,
    label = "Popular among both casual riders\nand club members",
    color = "#FFFFFF", hjust = 1, vjust = 1
  ) +
  annotate("text",
    x = 7000 - 50, y = 50, label = "Popular\namong club members",
    color = "#FFFFFF", hjust = 1, vjust = 0
  ) +
  labs(title = "Popular destinations during morning rush hours")

plotname <- plotname + 1
ggsave(paste(sprintf("%02d", plotname), "jpg", sep = "."))

The end destinations of rides for both casual riders and members are very much similar. The correlation between the numbers of rides per station for different groups cannot be denied: R-squared=0.69.

  • Indicated places that are popular among both categories during business day morning rush hours:
common_places_during_bizday_rush_hours <-
  inner_join(
    tdf %>%
      filter(n_casual > casual_threshold),
    tdf %>%
      filter(n_member > member_threshold)
  )

common_places_during_bizday_rush_hours <-
  left_join(
    common_places_during_bizday_rush_hours %>%
      ungroup() %>%
      select(end_station_id),
    idnamelatlng %>%
      rename(end_station_id = id)
  )

common_places_during_bizday_rush_hours
## # A tibble: 13 × 5
##    end_station_id orig_id      name                                  lat   lng
##    <fct>          <fct>        <fct>                               <dbl> <dbl>
##  1 13138          13138        Canal St & Jackson Blvd              41.9 -87.6
##  2 KA1503000043   KA1503000043 Kingsbury St & Kinzie St             41.9 -87.6
##  3 TA1305000006   TA1305000006 Dearborn St & Monroe St              41.9 -87.6
##  4 TA1305000011   TA1305000011 Michigan Ave & Lake St               41.9 -87.6
##  5 TA1305000022   TA1305000022 Orleans St & Merchandise Mart Plaza  41.9 -87.6
##  6 TA1305000025   TA1305000025 Franklin St & Jackson Blvd           41.9 -87.6
##  7 TA1305000032   TA1305000032 Clinton St & Madison St              41.9 -87.6
##  8 TA1306000009   TA1306000009 Larrabee St & Kingsbury St           41.9 -87.6
##  9 TA1307000111   TA1307000111 Franklin St & Lake St                41.9 -87.6
## 10 TA1307000151   TA1307000151 Wells St & Hubbard St                41.9 -87.6
## 11 TA1309000004   TA1309000004 LaSalle St & Jackson Blvd            41.9 -87.6
## 12 TA1309000007   TA1309000007 Franklin St & Monroe St              41.9 -87.6
## 13 WL-012         WL-012       Clinton St & Washington Blvd         41.9 -87.6

All these places are located near the central business district of Chicago, often referred to as the Loop.

This area is known for its high concentration of businesses, shopping, entertainment, and cultural venues. It’s also where many of the city’s most iconic buildings and landmarks, such as the Willis Tower and Union Station, are located. The Loop is bounded by the Chicago River to the north and west, Harrison Street to the south, and Lake Michigan to the east. So, these places are all in the heart of downtown Chicago.

They are depicted on a leaflet below.

leaflet() %>%
  addTiles() %>% #' Add default OpenStreetMap map tiles
  addAwesomeMarkers(
    lng = common_places_during_bizday_rush_hours$lng,
    lat = common_places_during_bizday_rush_hours$lat,
    icon = awesomeIcons(
      icon = "heart",
      markerColor = "cadetblue",
      iconColor = "white",
      library = "ion"
    ),
    popup = common_places_during_bizday_rush_hours$end_station_name
  )

WEEKENDS

The next step was to review the weekend data.

weekend_rides <-
  rides_per_station %>%
  filter(bizday == "Weekend") %>%
  group_by(end_station_id, end_station_name, member_casual) %>%
  summarize(n = sum(n)) %>%
  ungroup() %>%
  group_by(end_station_name)

tdf <- weekend_rides %>%
  group_by(end_station_id, end_station_name) %>%
  summarize(n = sum(n)) %>%
  ungroup() %>%
  top_n(10, n)

tdf <- weekend_rides %>%
  filter(end_station_id %in% tdf$end_station_id)

ggplot(tdf, aes(
  x = fct_reorder(end_station_name, n), y = n,
  fill = member_casual
)) +
  geom_bar(stat = "identity") +
  coord_flip() +
  labs(title = "Most popular destinations on weekends", x = " ")

plotname <- plotname + 1
ggsave(paste(sprintf("%02d", plotname), "jpg", sep = "."))

Casual member activity prevails on weekends. The most popular places among casual riders are:

tdf <- weekend_rides %>%
  filter(member_casual == "casual") %>%
  group_by(end_station_id, end_station_name) %>%
  summarize(n = sum(n)) %>%
  ungroup() %>%
  top_n(10, n) %>%
  arrange(desc(n))

tdf
## # A tibble: 10 × 3
##    end_station_id end_station_name                       n
##    <fct>          <fct>                              <int>
##  1 13022          Streeter Dr & Grand Ave            21497
##  2 13300          DuSable Lake Shore Dr & Monroe St  13149
##  3 13042          Michigan Ave & Oak St              10300
##  4 13008          Millennium Park                     9569
##  5 LF-005         DuSable Lake Shore Dr & North Blvd  9426
##  6 TA1308000001   Theater on the Lake                 8240
##  7 15544          Shedd Aquarium                      6747
##  8 KA1503000064   Dusable Harbor                      6487
##  9 13179          Clark St & Lincoln Ave              5206
## 10 TA1308000012   Montrose Harbor                     4976

The leaflet below depicts the most popular places among casual riders on weekends:

tdf <-
  left_join(
    tdf %>% select(end_station_id),
    idnamelatlng %>% rename(end_station_id = id)
  )

leaflet() %>%
  addTiles() %>% #' Add default OpenStreetMap map tiles
  addAwesomeMarkers(
    lng = tdf$lng,
    lat = tdf$lat,
    icon = awesomeIcons(
      icon = "heart",
      markerColor = "lightred",
      iconColor = "white",
      library = "ion"
    ),
    popup = tdf2$end_station_name
  )

These places are all located along the Lake Michigan shoreline in Chicago. They are part of the city’s Lakefront Trail, an 18-mile multi-use trail that runs alongside the shoreline. This trail is a popular spot for biking, jogging, and walking, offering stunning views of the lake and the city skyline. It passes through various parks and recreational spots, including Millennium Park, Theater on the Lake, and Shedd Aquarium.

So, these places are all part of Chicago’s vibrant and scenic lakefront area.

  • Plotted and analyzed number of casual rides versus number of member rides.
tdf <-
  full_join(
    weekend_rides %>%
      filter(member_casual == "member") %>%
      select(!member_casual) %>%
      rename(n_member = n),
    weekend_rides %>%
      filter(member_casual == "casual") %>%
      select(end_station_id, n) %>%
      rename(n_casual = n)
  )

tdf[is.na(tdf)] <- 0

tdf <- tdf %>%
  mutate(n = n_casual + n_member)

casual_threshold <- 5000
member_threshold <- 5000

model <- lm(n_casual ~ n_member, data = tdf %>% filter(n > 2000))
r_squared <- summary(model)$r.squared

ggplot(tdf %>% filter(n > 2000), aes(n_member, y = n_casual, size = n)) +
  geom_rect(
    aes(
      xmin = 0, xmax = casual_threshold,
      ymin = member_threshold, ymax = 25000
    ),
    fill = "#F8766D", alpha = 0.03
  ) +
  geom_rect(
    aes(
      xmin = casual_threshold, xmax = 9000,
      ymin = member_threshold, ymax = 25000
    ),
    fill = "#7C9B99", alpha = 0.03
  ) +
  geom_rect(
    aes(
      xmin = casual_threshold, xmax = 9000,
      ymin = 0000, ymax = member_threshold
    ),
    fill = "#00BFC4", alpha = 0.03
  ) +
  geom_point() +
  annotate("text",
    x = 1400, y = 15000,
    label = paste("R-squared =", round(r_squared, 2))
  ) +
  geom_label_repel(
    data = tdf %>% filter(
      n_casual > casual_threshold | n_member > member_threshold
    ),
    aes(
      label = end_station_name,
      hjust = 0
    ),
    size = 3,
    position = position_nudge(x = -200)
  ) +
  xlim(0, 9000) +
  ylim(0, 25000) +
  annotate("text",
    x = 100, y = 24500,
    label = "Popular\namong\ncasual riders",
    color = "#FFFFFF", hjust = 0, vjust = 1
  ) +
  annotate("text",
    x = 8900, y = 24500,
    label = "Popular\namong both casual riders\nand club members",
    color = "#FFFFFF", hjust = 1, vjust = 1
  ) +
  annotate("text",
    x = 8900, y = 500,
    label = "Popular\namong club members",
    color = "#FFFFFF", hjust = 1, vjust = 0
  ) +
  labs(title = "Popular destinations on weekends")

plotname <- plotname + 1
ggsave(paste(sprintf("%02d", plotname), "jpg", sep = "."))

During weekends, the spatial pattern of casual rides and member rides differ a lot. The correlation coefficient between the numbers of rides per station for different groups is quite low: R-squared=0.29, telling that these categories of people are different indeed.

The most popular places among Cyclistic members are:

weekend_rides %>%
  filter(member_casual == "member") %>%
  group_by(end_station_id, end_station_name) %>%
  summarize(n = sum(n)) %>%
  ungroup() %>%
  top_n(10, n) %>%
  arrange(desc(n))
## # A tibble: 10 × 3
##    end_station_id end_station_name                       n
##    <fct>          <fct>                              <int>
##  1 TA1308000050   Wells St & Concord Ln               6513
##  2 TA1307000039   Clark St & Elm St                   6215
##  3 13022          Streeter Dr & Grand Ave             5488
##  4 13137          Broadway & Barry Ave                5474
##  5 LF-005         DuSable Lake Shore Dr & North Blvd  5364
##  6 KA1504000135   Wells St & Elm St                   5173
##  7 TA1308000001   Theater on the Lake                 4953
##  8 KA1503000043   Kingsbury St & Kinzie St            4913
##  9 13179          Clark St & Lincoln Ave              4887
## 10 13146          Clark St & Armitage Ave             4728

There are few intersections with a casual riders’ Top 10 stations, but, generally, this is a different list.

These places are all located in the Near North Side of Chicago. This area is known for its high concentration of businesses, shopping, entertainment, and cultural venues. It’s also where many of the city’s most iconic buildings and landmarks are located1. The Near North Side includes neighborhoods like Old Town, which is home to many of Chicago’s older, Victorian-era buildings.

So, these places are all in a vibrant and historically rich part of Chicago.

The leaflet below depicts the most popular places among Casual riders (light red), Cyclistic members (blue) and both categories (cadet blue) during weekends.

common_places_during_weekends <-
  full_join(
    tdf %>% 
      filter(n_casual > casual_threshold) %>% 
      mutate(popular_among_casuals="C"),
    tdf %>% 
      filter(n_member > member_threshold) %>% 
      mutate(popular_among_members="m")
  ) %>% 
  ungroup 

common_places_during_weekends <-
  left_join(
    common_places_during_weekends %>% 
      select(end_station_id, end_station_name, popular_among_members, popular_among_casuals) %>% 
      rename(id=end_station_id),
    idnamelatlng %>% 
      select(id, lat, lng)
  ) %>% 
  distinct() 

  common_places_during_weekends[is.na(common_places_during_weekends)] <- " "

  common_places_during_weekends <- common_places_during_weekends %>% 
    mutate(popular=paste(popular_among_members, popular_among_casuals,sep = "~"))
  
getColor2 <- function(common_places_during_weekends) {
  sapply(common_places_during_weekends$popular, function(popular) {
    if (popular == "m~C") {
      "cadetblue"
    } else if (popular == " ~C") {
        "lightred"
    } else {
        "blue"
    } }, simplify=T)
}

icons2 <- awesomeIcons(
      icon = "heart",
      iconColor = "white",
      library = "ion",
      markerColor = getColor2(common_places_during_weekends)
    )

icons2["markerColor"]<-data.frame(as.list(icons2["markerColor"]))

leaflet() %>%
  addTiles() %>%  
  addAwesomeMarkers( 
    lng=common_places_during_weekends$lng, 
    lat=common_places_during_weekends$lat, 
    icon = icons2, 
    popup = common_places_during_weekends$end_station_name)

REGULAR DAY

  • Finally, analyzed spatial patterns of everyday cyclists during a regular weekday, excluding rush hours.
everyday_rides <-
  rides_per_station %>%
  filter(!(hours == 7 | hours == 8 | hours == 16 | hours == 17 | hours == 18) &
    bizday == "Business day") %>%
  group_by(end_station_id, end_station_name, member_casual) %>%
  summarize(n = sum(n)) %>%
  ungroup() %>%
  group_by(end_station_name)

tdf <- everyday_rides %>%
  group_by(end_station_id, end_station_name) %>%
  summarize(n = sum(n)) %>%
  ungroup() %>%
  top_n(10, n)

tdf <- everyday_rides %>%
  filter(end_station_id %in% tdf$end_station_id)

ggplot(tdf, aes(
  x = fct_reorder(end_station_name, n),
  y = n,
  fill = member_casual
)) +
  geom_bar(stat = "identity") +
  coord_flip() +
  labs(title = "Most popular destinations on a regular day", x = " ")

plotname <- plotname + 1
ggsave(paste(sprintf("%02d", plotname), "jpg", sep = "."))

The difference between club members and casual riders is even more obvious.

Just like during weekends, casual riders tend to prefer ‘leisure’ destinations such as the Streeter Dr & Grand Ave, Michigan Ave & Oak St, DuSable Lake Shore Dr & Monroe Str, DuSable Lake Shore Dr & North Blvd.

  • Plotted and analyzed number of casual rides versus number of member rides.
tdf <-
  full_join(
    everyday_rides %>%
      filter(member_casual == "member") %>%
      select(!member_casual) %>%
      rename(n_member = n),
    everyday_rides %>%
      filter(member_casual == "casual") %>%
      select(end_station_id, n) %>%
      rename(n_casual = n)
  )

tdf[is.na(tdf)] <- 0

tdf <- tdf %>%
  mutate(n = n_casual + n_member)

casual_threshold <- 5000
member_threshold <- 7000

model <- lm(n_casual ~ n_member, data = tdf %>% filter(n > 2000))
r_squared <- summary(model)$r.squared

ggplot(tdf %>% filter(n > 2000), aes(n_member, y = n_casual, size = n)) +
  geom_rect(
    aes(
      xmin = 0, xmax = member_threshold,
      ymin = casual_threshold, ymax = 20000
    ),
    fill = "#F8766D", alpha = 0.03
  ) +
  geom_rect(
    aes(
      xmin = member_threshold, xmax = 12000,
      ymin = casual_threshold, ymax = 20000
    ),
    fill = "#7C9B99", alpha = 0.03
  ) +
  geom_rect(
    aes(
      xmin = member_threshold, xmax = 12000,
      ymin = 0000, ymax = casual_threshold
    ),
    fill = "#00BFC4", alpha = 0.03
  ) +
  geom_point() +
  annotate("text",
    x = 2500, y = 15000,
    label = paste("R-squared =", round(r_squared, 2))
  ) +
  geom_label_repel(
    data = tdf %>% filter(
      n_casual > casual_threshold | n_member > member_threshold
    ),
    aes(
      label = end_station_name,
      hjust = 0
    ),
    size = 3,
    position = position_nudge(x = -200)
  ) +
  annotate("text",
    x = 50, y = 20000 - 50,
    label = "Popular\namong\ncasual riders",
    color = "#FFFFFF",
    hjust = 0, vjust = 1
  ) +
  annotate("text",
    x = 12000 - 50, y = 20000 - 50,
    label = "Popular among\nboth casual riders\nand club members",
    color = "#FFFFFF",
    hjust = 1, vjust = 1
  ) +
  annotate("text",
    x = 12000 - 50, y = 1000,
    label = "Popular\namong club members",
    color = "#FFFFFF",
    hjust = 1, vjust = 0
  ) +
  labs(title = "Popular destinations on a regular day")

plotname <- plotname + 1
ggsave(paste(sprintf("%02d", plotname), "jpg", sep = "."))

The correlation coefficient between the numbers of rides per station for different groups is low: R-squared=0.14.

The upper right quadrant of the plot is empty, indicating that there are no places that are equally attractive for both user groups.

The leaflet below shows that on a regular day, casual riders still prefer the shoreline, while Cyclistic members ride in the downtown.

common_places_on_a_regular_day <-
  full_join(
    tdf %>% 
      filter(n_casual > casual_threshold) %>% 
      mutate(popular_among_casuals="C"),
    tdf %>% 
      filter(n_member > member_threshold) %>% 
      mutate(popular_among_members="m")
  ) %>% 
  ungroup %>% 
  top_n (12, n)

common_places_on_a_regular_day <-
  left_join(
    common_places_on_a_regular_day %>% 
      select(end_station_id, end_station_name, popular_among_members, popular_among_casuals) %>% 
      rename(id=end_station_id),
    idnamelatlng %>% 
      select(id, lat, lng)
  ) %>% 
  distinct() 

  common_places_on_a_regular_day[is.na(common_places_on_a_regular_day)] <- " "

  common_places_on_a_regular_day <- common_places_on_a_regular_day %>% 
    mutate(popular=paste(popular_among_members, popular_among_casuals,sep = "~"))
  
getColor3 <- function(common_places_on_a_regular_day) {
  sapply(common_places_on_a_regular_day$popular, function(popular) {
    if (popular == "m~C") {
      "cadetblue"
    } else if (popular == " ~C") {
        "lightred"
    } else {
        "blue"
    } }, simplify=T)
}

icons3 <- awesomeIcons(
      icon = "heart",
      iconColor = "white",
      library = "ion",
      markerColor = getColor3(common_places_on_a_regular_day)
    )

icons3["markerColor"]<-data.frame(as.list(icons3["markerColor"]))

leaflet() %>%
  addTiles() %>%  
  addAwesomeMarkers( 
    lng=common_places_on_a_regular_day$lng, 
    lat=common_places_on_a_regular_day$lat, 
    icon = icons3, 
    popup = common_places_on_a_regular_day$end_station_name)

Summary of my analysis

Phase: Share

The study focused on the spatial and temporal distribution of rides by Cyclistic members and casual riders. The data was cleaned and spatial analytics were performed with an accuracy of ~111 m. The area was divided into two zones: the city center with high ride density and the periphery with lower ride density.

Three categories of cyclists were identified: leisure cyclists, everyday cyclists, commuting cyclists.

  • Leisure cyclists, who prefer warm weather and are more active during weekends, are present among both Cyclistic members and casual riders.

  • Everyday cyclists ride for their daily errands.

  • Commuting cyclists ride predominantly on weekdays.

Fourth suggested category, inactive members, rarely ride or do not ride at all.

Cyclistic struggles to convert casual clients into members. On weekends, both categories are equally active from May to September, but Cyclistic members are more tolerant to cold weather. The number of casual leisure cyclists is higher than the number of leisure cyclists among Cyclistic members.

At the periphery, the members to casual ratio is higher, possibly indicating a low market penetration rate.

Cyclistic members typically outpace casual riders, likely due to their regular cycling habits and training. Despite similar numbers of weekend rides, the lower frequency of rides per individual casual rider suggests that the total number of casual riders is greater, indicating a broader target audience. The marketing approach for less enthusiastic casual riders should be different.

Unlike Jersey City, Chicago might see a larger speed difference between club members and casual riders, possibly due to a higher proportion of trained cyclists among Cyclistic members in Chicago and the prevalence of longer, slower rides in the city.

The study identified the most popular destinations among casual customers and Cyclistic members during peak hours on business days.

During morning rush hours on weekdays, member trips account for 85% of the total at the busiest stations, which are located near the central business district of Chicago, often referred to as the Loop. A significant majority of commuting cyclists are already members of the club, indicating a high level of market penetration in this segment.

On weekends, casual member activity prevails. The most popular places among casual riders are located along the Lake Michigan shoreline in Chicago, part of the city’s Lakefront Trail. The correlation coefficient between the numbers of rides per station for different groups is quite low (R-squared=0.29 on weekends, R-squared=0.14 on weekdays excluding rush hours), indicating that these categories of people are indeed different.

The most popular places among Cyclistic members are located in the Near North Side of Chicago, a vibrant and historically rich part of the city.

Despite a few intersections, the set of popular places is generally different for casual riders and Cyclistic members on weekends and especially on weekdays, further highlighting the differences between these two groups.

Selected relevant plots and leaflets from the detailed analysis section serve as supporting visualizations for the summary (see Output).


Top recommendations

Phase: Act

  1. Targeted Marketing Campaigns: Develop marketing campaigns that specifically target leisure cyclists, as they are majority of casual riders. Emphasize the fun and freedom in marketing campaign, because leisure cyclists often cycle for enjoyment and to explore the city.

  2. Partnerships with Local Businesses: Partner with businesses in popular casual rider destinations along the Lake Michigan shoreline. These businesses could offer discounts or benefits to Cyclistic members, providing an additional incentive for casual riders to join.

  3. Seasonal Promotions: Since casual riders are more active during warm weather and on weekends, consider offering seasonal promotions to encourage them to join as members during these peak times.

  4. Competitor Analysis: Study Citi Bike’s strategies in Jersey City. Understand their success factors and adapt relevant tactics to enhance Cyclistic’s membership conversion.


Post scriptum

This study was fun. I learned a lot. The R Markdown file I created for this study is practically my very first cheatsheet for some common operations with data and plots in R.