library(tidyverse)

## ── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
## ✔ dplyr     1.1.4     ✔ readr     2.1.5
## ✔ forcats   1.0.0     ✔ stringr   1.5.1
## ✔ ggplot2   3.5.2     ✔ tibble    3.2.1
## ✔ lubridate 1.9.3     ✔ tidyr     1.3.1
## ✔ purrr     1.0.2     
## ── 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(janitor)

## 
## Attaching package: 'janitor'
## 
## The following objects are masked from 'package:stats':
## 
##     chisq.test, fisher.test

library(readxl)
library(writexl)
library(RSQLite)

Reading data from CSV file

All the csv file is importing into R Environment

df1 <- read_csv("C:/Users/ABHISHEK SINGH/Desktop/DATA ANALYISIS VIDEO AND PDF/202408-divvy-tripdata.csv")

## Rows: 755639 Columns: 13
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr  (7): ride_id, rideable_type, start_station_name, start_station_id, end_...
## dbl  (4): start_lat, start_lng, end_lat, end_lng
## dttm (2): started_at, ended_at
## 
## ℹ Use `spec()` to retrieve the full column specification for this data.
## ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

df2 <- read_csv("C:/Users/ABHISHEK SINGH/Desktop/DATA ANALYISIS VIDEO AND PDF/202409-divvy-tripdata.csv")

## Rows: 821276 Columns: 13
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr  (7): ride_id, rideable_type, start_station_name, start_station_id, end_...
## dbl  (4): start_lat, start_lng, end_lat, end_lng
## dttm (2): started_at, ended_at
## 
## ℹ Use `spec()` to retrieve the full column specification for this data.
## ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

df3 <- read_csv("C:/Users/ABHISHEK SINGH/Desktop/DATA ANALYISIS VIDEO AND PDF/202410-divvy-tripdata.csv")

## Rows: 616281 Columns: 13
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr  (7): ride_id, rideable_type, start_station_name, start_station_id, end_...
## dbl  (4): start_lat, start_lng, end_lat, end_lng
## dttm (2): started_at, ended_at
## 
## ℹ Use `spec()` to retrieve the full column specification for this data.
## ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

df4 <- read_csv("C:/Users/ABHISHEK SINGH/Desktop/DATA ANALYISIS VIDEO AND PDF/202411-divvy-tripdata.csv")

## Rows: 335075 Columns: 13
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr  (7): ride_id, rideable_type, start_station_name, start_station_id, end_...
## dbl  (4): start_lat, start_lng, end_lat, end_lng
## dttm (2): started_at, ended_at
## 
## ℹ Use `spec()` to retrieve the full column specification for this data.
## ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

df5 <- read_csv("C:/Users/ABHISHEK SINGH/Desktop/DATA ANALYISIS VIDEO AND PDF/202412-divvy-tripdata.csv")

## Rows: 178372 Columns: 13
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr  (7): ride_id, rideable_type, start_station_name, start_station_id, end_...
## dbl  (4): start_lat, start_lng, end_lat, end_lng
## dttm (2): started_at, ended_at
## 
## ℹ Use `spec()` to retrieve the full column specification for this data.
## ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

df6 <- read_csv("C:/Users/ABHISHEK SINGH/Desktop/DATA ANALYISIS VIDEO AND PDF/202501-divvy-tripdata.csv")

## Rows: 138689 Columns: 13
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr  (7): ride_id, rideable_type, start_station_name, start_station_id, end_...
## dbl  (4): start_lat, start_lng, end_lat, end_lng
## dttm (2): started_at, ended_at
## 
## ℹ Use `spec()` to retrieve the full column specification for this data.
## ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

df7 <- read_csv("C:/Users/ABHISHEK SINGH/Desktop/DATA ANALYISIS VIDEO AND PDF/202502-divvy-tripdata.csv")

## Rows: 151880 Columns: 13
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr  (7): ride_id, rideable_type, start_station_name, start_station_id, end_...
## dbl  (4): start_lat, start_lng, end_lat, end_lng
## dttm (2): started_at, ended_at
## 
## ℹ Use `spec()` to retrieve the full column specification for this data.
## ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

df8 <- read_csv("C:/Users/ABHISHEK SINGH/Desktop/DATA ANALYISIS VIDEO AND PDF/202503-divvy-tripdata.csv")

## Rows: 298155 Columns: 13
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr  (7): ride_id, rideable_type, start_station_name, start_station_id, end_...
## dbl  (4): start_lat, start_lng, end_lat, end_lng
## dttm (2): started_at, ended_at
## 
## ℹ Use `spec()` to retrieve the full column specification for this data.
## ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

df9 <- read_csv("C:/Users/ABHISHEK SINGH/Desktop/DATA ANALYISIS VIDEO AND PDF/202504-divvy-tripdata.csv")

## Rows: 371341 Columns: 13
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr  (7): ride_id, rideable_type, start_station_name, start_station_id, end_...
## dbl  (4): start_lat, start_lng, end_lat, end_lng
## dttm (2): started_at, ended_at
## 
## ℹ Use `spec()` to retrieve the full column specification for this data.
## ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

df10 <- read_csv("C:/Users/ABHISHEK SINGH/Desktop/DATA ANALYISIS VIDEO AND PDF/202505-divvy-tripdata.csv")

## Rows: 502456 Columns: 13
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr  (7): ride_id, rideable_type, start_station_name, start_station_id, end_...
## dbl  (4): start_lat, start_lng, end_lat, end_lng
## dttm (2): started_at, ended_at
## 
## ℹ Use `spec()` to retrieve the full column specification for this data.
## ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

df11 <- read_csv("C:/Users/ABHISHEK SINGH/Desktop/DATA ANALYISIS VIDEO AND PDF/202506-divvy-tripdata.csv")

## Rows: 678904 Columns: 13
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr  (7): ride_id, rideable_type, start_station_name, start_station_id, end_...
## dbl  (4): start_lat, start_lng, end_lat, end_lng
## dttm (2): started_at, ended_at
## 
## ℹ Use `spec()` to retrieve the full column specification for this data.
## ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

df12 <- read_csv("C:/Users/ABHISHEK SINGH/Desktop/DATA ANALYISIS VIDEO AND PDF/202507-divvy-tripdata.csv")

## Rows: 763432 Columns: 13
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr  (7): ride_id, rideable_type, start_station_name, start_station_id, end_...
## dbl  (4): start_lat, start_lng, end_lat, end_lng
## dttm (2): started_at, ended_at
## 
## ℹ Use `spec()` to retrieve the full column specification for this data.
## ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

Combining

All the csv files will be combine into one data frame.

bike_rides <- rbind(df1,df2,df3,df4,df5,df6,df7,df8,df9,df10,df11,df12)
bike_rides <- janitor::remove_empty(bike_rides,which = c("cols"))
bike_rides <- janitor::remove_empty(bike_rides,which = c("rows"))

head(bike_rides)

## # A tibble: 6 × 13
##   ride_id          rideable_type started_at          ended_at           
##   <chr>            <chr>         <dttm>              <dttm>             
## 1 BAA154388A869E64 classic_bike  2024-08-02 13:35:14 2024-08-02 13:48:24
## 2 8752245932EFF67A electric_bike 2024-08-02 15:33:13 2024-08-02 15:55:23
## 3 44DDF9F57A9A161F classic_bike  2024-08-16 15:44:06 2024-08-16 15:57:52
## 4 44AAAF069B0C78C3 electric_bike 2024-08-19 18:47:11 2024-08-19 18:56:33
## 5 77138D500A6B7B4B classic_bike  2024-08-03 20:34:20 2024-08-03 20:46:29
## 6 F6F581F31A9C9BC2 electric_bike 2024-08-03 20:08:09 2024-08-03 20:44:53
## # ℹ 9 more variables: start_station_name <chr>, start_station_id <chr>,
## #   end_station_name <chr>, end_station_id <chr>, start_lat <dbl>,
## #   start_lng <dbl>, end_lat <dbl>, end_lng <dbl>, member_casual <chr>

Data cleaning

Removing duplicates

cyclistic_no_dups <- bike_rides[!duplicated(bike_rides$ride_id), ]

# Calculate how many rows were removed
removed_rows <- nrow(bike_rides) - nrow(cyclistic_no_dups)

# Print result
print(paste("Removed", removed_rows, "duplicated rows"))

## [1] "Removed 0 duplicated rows"

Parsing datetime columns

In R parsing a date time converting a column that looks like date but is stored as text or another format into proper date or datetime object so R can understand and work with it correctly.

cyclistic_no_dups$started_at <- as.POSIXct(cyclistic_no_dups$started_at, "%Y-%m-%d %H:%M:%S")
cyclistic_no_dups$ended_at <- as.POSIXct(cyclistic_no_dups$ended_at, "%Y-%m-%d %H:%M:%S")

Manipulating the data

New columns will help improve calculation time in the future

#ride_time_m Represent the total time of a bike ride,in minutes

cyclistic_no_dups <- cyclistic_no_dups %>%
  mutate(ride_time_m = as.numeric(cyclistic_no_dups$ended_at - cyclistic_no_dups$started_at) / 60)
summary(cyclistic_no_dups$ride_time_m)

##     Min.  1st Qu.   Median     Mean  3rd Qu.     Max. 
##  -56.022    5.389    9.406   16.229   16.539 1574.900

year_month

Separate the year and the month into one column

cyclistic_no_dups <- cyclistic_no_dups %>%
  mutate(year_month = paste(strftime(cyclistic_no_dups$started_at, "%Y"),
                            "-",
                            strftime(cyclistic_no_dups$started_at, "%m"),
                            paste("(",strftime(cyclistic_no_dups$started_at, "%b"), ")", sep = "")))

unique(cyclistic_no_dups$year_month)

##  [1] "2024 - 08 (Aug)" "2024 - 09 (Sep)" "2024 - 07 (Jul)" "2024 - 10 (Oct)"
##  [5] "2024 - 11 (Nov)" "2024 - 12 (Dec)" "2025 - 01 (Jan)" "2025 - 02 (Feb)"
##  [9] "2025 - 03 (Mar)" "2025 - 04 (Apr)" "2025 - 05 (May)" "2025 - 06 (Jun)"
## [13] "2025 - 07 (Jul)" "2025 - 08 (Aug)"

Weekday

The weekday will be useful to determine patterns of travels in the week

cyclistic_no_dups <- cyclistic_no_dups %>%
  mutate(weekday = paste(strftime(cyclistic_no_dups$ended_at, "%u"), "- ", strftime(cyclistic_no_dups$ended_at, "%a")))

unique(cyclistic_no_dups$weekday)

## [1] "5 -  Fri" "2 -  Tue" "7 -  Sun" "3 -  Wed" "4 -  Thu" "6 -  Sat" "1 -  Mon"

start_hour

Getting the hour of the day also may be useful for intro day analysis

cyclistic_no_dups <- cyclistic_no_dups %>%
  mutate(start_hour = strftime(cyclistic_no_dups$ended_at , "%H"))

unique(cyclistic_no_dups$start_hour)

##  [1] "19" "21" "00" "02" "23" "17" "20" "22" "04" "01" "18" "14" "13" "03" "12"
## [16] "05" "15" "16" "11" "06" "07" "10" "09" "08"

Saving the result as a CSV

cyclistic_no_dups %>%
  write.csv("cyclistic_clean.csv")

## Warning in as.POSIXlt.POSIXct(x): unknown timezone '%Y-%m-%d %H:%M:%S'

## Warning in as.POSIXlt.POSIXct(x): unknown timezone '%Y-%m-%d %H:%M:%S'

Guiding questions

What tools are you choosing?

I’m using R for this project, for two main reason: because of the large data set and to gather experience with the language.

Have you ensured your data integrity?

yes,the data is consistent throughout the columns.

what steps have you taken to ensure that your data is clean?

First, the duplicated values where removed, then the columns where formatted to their correct format.

How can you verify that your data is clean and ready to analyze?

it can be verified by this notebook.

Have you documented your cleaning process so you can review and share those results?

yes, it’s all documented in this R notebook.

Key Tasks

check the data for errors.
choose your tools.
Transform the data so you can work with it effectively
Document the cleaning process.

Deliverable

Documentation of any cleaning or manipulation of data

Analyze

The data exploration will consist of building a profile for annual members and how they differ from casual riders.

putting in a new variable with a simpler name will help reduce some typing in the future.

# This function help to resize the plots
fig <- function(width, heigth){options(repr.plot.width = width, repr.plot.heigth = heigth)}

cyclistic <- cyclistic_no_dups
head(cyclistic)

## Warning in as.POSIXlt.POSIXct(x, tz): unknown timezone '%Y-%m-%d %H:%M:%S'

## Warning in as.POSIXlt.POSIXct(x, tz): unknown timezone '%Y-%m-%d %H:%M:%S'

## Warning in as.POSIXlt.POSIXct(x, tz): unknown timezone '%Y-%m-%d %H:%M:%S'

## Warning in as.POSIXlt.POSIXct(x, tz): unknown timezone '%Y-%m-%d %H:%M:%S'

## # A tibble: 6 × 17
##   ride_id          rideable_type started_at          ended_at           
##   <chr>            <chr>         <dttm>              <dttm>             
## 1 BAA154388A869E64 classic_bike  2024-08-02 13:35:14 2024-08-02 13:48:24
## 2 8752245932EFF67A electric_bike 2024-08-02 15:33:13 2024-08-02 15:55:23
## 3 44DDF9F57A9A161F classic_bike  2024-08-16 15:44:06 2024-08-16 15:57:52
## 4 44AAAF069B0C78C3 electric_bike 2024-08-19 18:47:11 2024-08-19 18:56:33
## 5 77138D500A6B7B4B classic_bike  2024-08-03 20:34:20 2024-08-03 20:46:29
## 6 F6F581F31A9C9BC2 electric_bike 2024-08-03 20:08:09 2024-08-03 20:44:53
## # ℹ 13 more variables: start_station_name <chr>, start_station_id <chr>,
## #   end_station_name <chr>, end_station_id <chr>, start_lat <dbl>,
## #   start_lng <dbl>, end_lat <dbl>, end_lng <dbl>, member_casual <chr>,
## #   ride_time_m <dbl>, year_month <chr>, weekday <chr>, start_hour <chr>

To quick start, let’s generate a summary of the dateset

summary(cyclistic)

## Warning in as.POSIXlt.POSIXct(x, tz): unknown timezone '%Y-%m-%d %H:%M:%S'

## Warning in as.POSIXlt.POSIXct(x, tz): unknown timezone '%Y-%m-%d %H:%M:%S'

##    ride_id          rideable_type        started_at                    
##  Length:5611500     Length:5611500     Min.   :2024-07-30 23:06:26.89  
##  Class :character   Class :character   1st Qu.:2024-09-23 20:26:39.38  
##  Mode  :character   Mode  :character   Median :2025-01-25 15:40:16.36  
##                                        Mean   :2025-01-26 16:37:24.03  
##                                        3rd Qu.:2025-06-03 00:08:36.90  
##                                        Max.   :2025-07-31 23:56:06.46  
##                                                                        
##     ended_at                      start_station_name start_station_id  
##  Min.   :2024-08-01 00:00:16.84   Length:5611500     Length:5611500    
##  1st Qu.:2024-09-23 20:40:32.42   Class :character   Class :character  
##  Median :2025-01-25 15:52:13.52   Mode  :character   Mode  :character  
##  Mean   :2025-01-26 16:53:37.78                                        
##  3rd Qu.:2025-06-03 00:29:17.63                                        
##  Max.   :2025-07-31 23:59:41.76                                        
##                                                                        
##  end_station_name   end_station_id       start_lat       start_lng     
##  Length:5611500     Length:5611500     Min.   :41.64   Min.   :-87.91  
##  Class :character   Class :character   1st Qu.:41.88   1st Qu.:-87.66  
##  Mode  :character   Mode  :character   Median :41.90   Median :-87.64  
##                                        Mean   :41.90   Mean   :-87.65  
##                                        3rd Qu.:41.93   3rd Qu.:-87.63  
##                                        Max.   :42.07   Max.   :-87.52  
##                                                                        
##     end_lat         end_lng        member_casual       ride_time_m      
##  Min.   :16.06   Min.   :-129.71   Length:5611500     Min.   : -56.022  
##  1st Qu.:41.88   1st Qu.: -87.66   Class :character   1st Qu.:   5.389  
##  Median :41.90   Median : -87.64   Mode  :character   Median :   9.406  
##  Mean   :41.90   Mean   : -87.65                      Mean   :  16.229  
##  3rd Qu.:41.93   3rd Qu.: -87.63                      3rd Qu.:  16.539  
##  Max.   :83.14   Max.   : 152.53                      Max.   :1574.900  
##  NA's   :5910    NA's   :5910                                           
##   year_month          weekday           start_hour       
##  Length:5611500     Length:5611500     Length:5611500    
##  Class :character   Class :character   Class :character  
##  Mode  :character   Mode  :character   Mode  :character  
##                                                          
##                                                          
##                                                          
##

one thing that immediately catches the attention is ride_time_m. This field has negative values, and the biggest value is 1574.900, which is 40 days and 46 jours. this field will be explored further in the documents.

Data Distribution

Here we want to try to answer the most basic questions about how the data is distributed.

Casuals vs. Members

How much of the data is about members and how much is about casuals?

cyclistic %>%
  group_by(member_casual) %>%
  summarise(count = length(ride_id),
            "%" = (length(ride_id) / nrow(cyclistic)) * 100)

## # A tibble: 2 × 3
##   member_casual   count   `%`
##   <chr>           <int> <dbl>
## 1 casual        2058043  36.7
## 2 member        3553457  63.3

library(ggplot2)

ggplot(data = cyclistic, aes(x = member_casual, fill = member_casual)) +
  geom_bar() +
  labs(
    x = "Casuals vs Members",
    title = "Chart 01 - Casuals vs Members Distribution",
    caption = "Data is collected by Google Data Analytics Capstone study"
  )

As we can see on the member x casual table, members have a bigger proportion of the data set, composing ~63%, ~27% bigger than the count of casual riders.

Month

How much of the data is distributed by month?

cyclistic %>%
  group_by(year_month) %>%
  summarise(count = length(ride_id),
            "%" = (length(ride_id) / nrow(cyclistic)) * 100,
            'members_p' = (sum(member_casual == "member") / length(ride_id)) * 100,
            'casual_p' = (sum(member_casual == "casual") / length(ride_id)) * 100,
            'Memeber X Casual Pre Difer' = members_p - casual_p)

## # A tibble: 14 × 6
##    year_month       count       `%` members_p casual_p Memeber X Casual Pre Di…¹
##    <chr>            <int>     <dbl>     <dbl>    <dbl>                     <dbl>
##  1 2024 - 07 (Jul)     41  0.000731      17.1     82.9                     -65.9
##  2 2024 - 08 (Aug) 748307 13.3           58.0     42.0                      16.1
##  3 2024 - 09 (Sep) 823149 14.7           57.6     42.4                      15.2
##  4 2024 - 10 (Oct) 619171 11.0           64.8     35.2                      29.6
##  5 2024 - 11 (Nov) 337060  6.01          72.2     27.8                      44.4
##  6 2024 - 12 (Dec) 178027  3.17          78.5     21.5                      57.1
##  7 2025 - 01 (Jan) 138519  2.47          82.5     17.5                      65.0
##  8 2025 - 02 (Feb) 151404  2.70          81.8     18.2                      63.5
##  9 2025 - 03 (Mar) 298148  5.31          71.2     28.8                      42.3
## 10 2025 - 04 (Apr) 370033  6.59          70.6     29.4                      41.2
## 11 2025 - 05 (May) 500896  8.93          63.8     36.2                      27.7
## 12 2025 - 06 (Jun) 678121 12.1           56.9     43.1                      13.8
## 13 2025 - 07 (Jul) 761365 13.6           57.7     42.3                      15.4
## 14 2025 - 08 (Aug)   7259  0.129         55.9     44.1                      11.7
## # ℹ abbreviated name: ¹`Memeber X Casual Pre Difer`

cyclistic %>%
  ggplot(aes(year_month,fill=member_casual))+
  geom_bar()+
  labs(x="Month", title="Chart02 - Distribution  by month")+
  coord_flip()

Some consideration can be taken by this charts:

The month with the biggest count of data points was September
In all months we have more member’s rides than casual rides (maybe because of returning members).

The distribution looks cyclical. Let’s compare it with climate data for Chicago. The data will be taken by https://en.wikipedia.org/wiki/Climate_of_Chicago (Daily mean °C, 1991-2020).

chicago_mean_temp <- c(-3.2,-1.2, 4.4, 10.5, 16.6, 22.2, 24.8, 23.9, 19.9, 12.9, 5.8, -0.3)
month <- c("001 - Jan","002 - Feb","003 - Mar","004 - Apr","005 - May","006 - Jun","007 - Jul","008 - Aug","009 - Sep","010 - Oct","011 - Nov","012 - Dec")

data.frame(month, chicago_mean_temp) %>%
    ggplot(aes(x=month, y=chicago_mean_temp)) +
    labs(x="Month", y="Mean temperature", title="Chart 02.5 - Mean temperature for Chicago (1991-2020)") +
    geom_col()

Temperature heavily influence the volume of rides in the month.

weekday

How much of the data is distributed by weekday?

cyclistic %>%
  group_by(weekday) %>%
  summarise(count = length(ride_id),
            '%' = (length(ride_id) / nrow(cyclistic)) * 100,
            'member_p' = (sum(member_casual == "member") / length(ride_id)) * 100,
            'casual_p' = (sum(member_casual == "member") / length(ride_id)) * 100,
            'Member X Casual Perc Difer' = member_p - casual_p)

## # A tibble: 7 × 6
##   weekday   count   `%` member_p casual_p `Member X Casual Perc Difer`
##   <chr>     <int> <dbl>    <dbl>    <dbl>                        <dbl>
## 1 1 -  Mon 722864  12.9     66.1     66.1                            0
## 2 2 -  Tue 779877  13.9     70.7     70.7                            0
## 3 3 -  Wed 783345  14.0     70.3     70.3                            0
## 4 4 -  Thu 820592  14.6     68.5     68.5                            0
## 5 5 -  Fri 832617  14.8     63.3     63.3                            0
## 6 6 -  Sat 881895  15.7     53.5     53.5                            0
## 7 7 -  Sun 790310  14.1     52.1     52.1                            0

ggplot(cyclistic, aes(weekday, fill=member_casual))+
  geom_bar()+
  labs(x="Weekday", title="Chart03 Distribution by weekday")+
  coord_flip()

It’s interesting to see

The biggest volume of data is on weekend.
Saturday has the biggest data points.
Members may have the biggest volume of data besides on Saturday On the weekday, casual take place as having most data points.
Weekends have the biggest volume of casual, starting on Friday, a ~19% increase.

Hour of the day

cyclistic %>%
  group_by(start_hour) %>%
  summarise(count = length(ride_id),
            '%' = (length(ride_id) / nrow(cyclistic)) * 100,
            'member_p' = (sum(member_casual == "member") / length(ride_id)) * 100,
            'casual_p' = (sum(member_casual == "casual") / length(ride_id)) * 100,
            'member_casual_Perc_Difer' = member_p - casual_p)

## # A tibble: 24 × 6
##    start_hour  count   `%` member_p casual_p member_casual_Perc_Difer
##    <chr>       <int> <dbl>    <dbl>    <dbl>                    <dbl>
##  1 00         430342 7.67      62.8     37.2                    25.5 
##  2 01         298136 5.31      60.9     39.1                    21.9 
##  3 02         220133 3.92      60.1     39.9                    20.3 
##  4 03         177700 3.17      57.3     42.7                    14.7 
##  5 04         132859 2.37      52.8     47.2                     5.57
##  6 05          90479 1.61      49.0     51.0                    -2.01
##  7 06          60048 1.07      45.5     54.5                    -9.05
##  8 07          39515 0.704     43.4     56.6                   -13.1 
##  9 08          22485 0.401     43.3     56.7                   -13.4 
## 10 09          15776 0.281     48.8     51.2                    -2.35
## # ℹ 14 more rows

cyclistic %>%
  ggplot(aes(start_hour, fill=member_casual))+
  labs(x="Hour of the day", title="Chart04 Distribution by hour of the day")+
  geom_bar()

From this chart we can see

There’s bigger volume of bikers in the afternon.
We have more members during the morning, mainly in between 5am and 11am.
And more casuals between 11pm and 4am.

This chart can be expanded seen ity divided by day of the week.

cyclistic %>%
  ggplot(aes(start_hour, fill=member_casual))+
  geom_bar()+
  labs(x="Hour of the day", title="Chart05 Distribution by hour of the day divided by week")+
  facet_wrap(~weekday)

There’s clear difference between the midweek and weekends. Let’s generate charts for this two configurations.

cyclistic %>%
  mutate(
    type_of_weekday = ifelse(
      grepl("Sat|Sun", weekday),
      "weekend",
      "midweek"
    ),
    type_of_weekday = factor(type_of_weekday, levels = c("midweek", "weekend"))
  ) %>%
  ggplot(aes(start_hour, fill = member_casual)) +
  geom_bar() +
  facet_wrap(~type_of_weekday) +
  labs(
    x = "Hour of the day",
    title = "Chart06 - Distribution by hour of the day: Midweek vs Weekend"
  )

The two plots differs on some key ways:

While the weekends have a smooth flow of data points, the midweek have a more step flow of data.
The count of data points doesn’t have much meaning knowing each plot represents a different amount of days.
There’s a big increase of data points in the midweek between 1pm to 2pm. Then it fall a bit.
Another big increase is from 10pm to 11pm.
During the weekend we have a bigger flow of casuals between 1pm to 9pm.

It’s fundamental to question who are the riders who use the bikes during this time of day. We can assume some factors, one is that members may are people who use the bikes during they daily routine activities, like go to work (data points between 10am to 2pm), go back from work (data points between )

Rideable type

cyclistic %>%
    group_by(rideable_type) %>%
  summarise(count = length(ride_id),
            '%' = (length(ride_id) / nrow(cyclistic)) * 100,
            'member_p' = (sum(member_casual == "member") / length(ride_id)) * 100,
            'casual_p' = (sum(member_casual == "casual") / length(ride_id)) * 100,
            'member_casual_Perc_Difer' = member_p - casual_p)

## # A tibble: 3 × 6
##   rideable_type      count   `%` member_p casual_p member_casual_Perc_Difer
##   <chr>              <int> <dbl>    <dbl>    <dbl>                    <dbl>
## 1 classic_bike     2224294 39.6      64.4     35.6                     28.7
## 2 electric_bike    3242869 57.8      63.6     36.4                     27.2
## 3 electric_scooter  144337  2.57     41.0     59.0                    -18.1

ggplot(cyclistic, aes(rideable_type, fill=member_casual))+
  labs(x="Rideable type", title="Chart07 Distribution of types of bikes")+
  geom_bar()+
  coord_flip()

It’s important to note that

electric bikes have the biggest volume of rides, but this can be that the company may have more docked bikes.
Members have a bigger preference for classic bikes
Also for electric bikes

ride_time_m

First get some summarized statistic from the dataset.

summary(cyclistic$ride_time_m)

##     Min.  1st Qu.   Median     Mean  3rd Qu.     Max. 
##  -56.022    5.389    9.406   16.229   16.539 1574.900

The min and max may be a problem to plot some charts. How the ride time of some bikes is a negative value? Maybe there’s some malfunction station return bad dates. checking the start and end station does’t appear to have a problem.

ventiles = quantile(cyclistic$ride_time_m, seq(0, 1, by=0.5))
ventiles

##          0%         50%        100% 
##  -56.021700    9.406317 1574.900183

We can see that

The difference between 0% and 100% is 1630.921883 minutes.

cyclistic_without_outliners <- cyclistic %>%
  filter(ride_time_m > as.numeric(ventiles['5%'])) %>%
  filter(ride_time_m < as.numeric(ventiles['95%']))

print(paste("Removed", nrow(cyclistic) - nrow(cyclistic_without_outliners), "rows as outliners"))

## [1] "Removed 5611500 rows as outliners"

ride_time_m multivariable exploration

One of the first interactions between the columns and ride_length is a box plot, with subplots based on the casual_members column. Also the summarized data.

Get a summary of the ride_time_m column to understand its distribution

summary(cyclistic$ride_time_m)

##     Min.  1st Qu.   Median     Mean  3rd Qu.     Max. 
##  -56.022    5.389    9.406   16.229   16.539 1574.900

# Check data exists
nrow(cyclistic_without_outliners)

## [1] 0

# Preview first rows
head(cyclistic_without_outliners)

## # A tibble: 0 × 17
## # ℹ 17 variables: ride_id <chr>, rideable_type <chr>, started_at <dttm>,
## #   ended_at <dttm>, start_station_name <chr>, start_station_id <chr>,
## #   end_station_name <chr>, end_station_id <chr>, start_lat <dbl>,
## #   start_lng <dbl>, end_lat <dbl>, end_lng <dbl>, member_casual <chr>,
## #   ride_time_m <dbl>, year_month <chr>, weekday <chr>, start_hour <chr>

# Check distinct member_casual values
table(cyclistic_without_outliners$member_casual)

## < table of extent 0 >

# Check ride_time_m range
summary(cyclistic_without_outliners$ride_time_m)

##    Min. 1st Qu.  Median    Mean 3rd Qu.    Max. 
##

ventiles <- quantile(cyclistic$ride_time_m, probs = seq(0, 1, 0.05), na.rm = TRUE)

cyclistic_without_outliners <- cyclistic %>%
  filter(ride_time_m > as.numeric(ventiles['5%']),
         ride_time_m < as.numeric(ventiles['95%']))

print(paste("Removed", nrow(cyclistic) - nrow(cyclistic_without_outliners), "rows as outliers"))

## [1] "Removed 561150 rows as outliers"

print(ventiles['5%'])

##       5% 
## 2.185566

print(ventiles['95%'])

##      95% 
## 40.22731

summary(cyclistic$ride_time_m)

##     Min.  1st Qu.   Median     Mean  3rd Qu.     Max. 
##  -56.022    5.389    9.406   16.229   16.539 1574.900

table(cut(cyclistic$ride_time_m, breaks = c(0, 2.19, 40.23, Inf)))

## 
##    (0,2.19] (2.19,40.2]  (40.2,Inf] 
##      281427     5049489      280541

ventiles <- quantile(cyclistic$ride_time_m, probs = c(0.01, 0.99), na.rm = TRUE)

cyclistic_without_outliners <- cyclistic %>%
  filter(ride_time_m > ventiles[1],
         ride_time_m < ventiles[2])

cyclistic_without_outliners %>%
  group_by(member_casual) %>%
  summarise(
    mean = mean(ride_time_m, na.rm = TRUE),
    first_quarter = quantile(ride_time_m, 0.25, na.rm = TRUE),
    median = median(ride_time_m, na.rm = TRUE),
    third_quarter = quantile(ride_time_m, 0.75, na.rm = TRUE),
    IQR = third_quarter - first_quarter
  )

## # A tibble: 2 × 6
##   member_casual  mean first_quarter median third_quarter   IQR
##   <chr>         <dbl>         <dbl>  <dbl>         <dbl> <dbl>
## 1 casual         16.4          6.45  11.4           20.5 14.1 
## 2 member         11.3          5.05   8.54          14.4  9.30

library(ggplot2)

ggplot(cyclistic_without_outliners, aes(x = member_casual, y = ride_time_m, fill = member_casual)) +
  geom_boxplot(outlier.colour = "red", outlier.size = 2) +
  labs(
    title = "Chart 08 - Distribution of Riding time From Casual x Member",
    x = "Member x casual",
    y = "Ride Time"
  )

It’s important to note that

Casual have more riding time than members.
Mean and IQR is also bigger for casual.

ggplot(cyclistic_without_outliners,aes(x=weekday, y=ride_time_m, fill=member_casual))+
  geom_boxplot()+
  facet_wrap(~member_casual)+
  labs(x="Weekday", y="Riding time", title="Chart09 Distribution of Riding Time For day of the week")+
  coord_flip()

Riding time for members keep unchanged during the midweek increasing during weekends.
Casuals follow a more curve distribution, peaking on Sundays and Valleying on Tuesday/Thursday

ggplot(cyclistic_without_outliners, aes(x=rideable_type, y=ride_time_m, fill=member_casual))+
  geom_boxplot()+
  facet_wrap(~member_casual)+
  labs(x="Rideable Type", y="Riding Time", title="Chart 10 - Distribution of Riding time for rideable type")+
  coord_flip()

Electric bikes have less riding time than other bikes, for casuals.
Classic bikes have more riding time and for electric scooter casuals have more riding than members.

Guiding questions

How should you organize your data to perform analysis on it? The data has been organized into a multiple CSV concatenating all the files from the dataset.
Has your data been properly formatted? yes, all the columns have their correct data type.
What surprises did you discover in the data? One of the main surprises is how members different from casuals when analyzed from weekdays. Also that members have less riding time than casuals.
What trends or relationships did you find in the data?
- There are more members than casuals in the dataset.
- There are more of a difference between the flow of members/casuals from midweek to weekends.
- members use bikes on schedules that differs from casuals
- members have less riding time.
- members tend to prefer classic bikes.
How will these insights help answer your business questions? This insights helps to build a profile for members.

Key tasks

Aggregate your data so it’s useful and accesible.
Organize and format your data.
Perform calculations.
Identify trends and relationships.

Deliverable

A summary of your analysis

Key tasks

Determine the best way to share your findings.
Create effective data visualizations.
Present your findings.
Ensure your work is accessible.

Deliverable

The act phase would be done by the marketing team of the company. The main takeaway will be the top three recommendation for the marketing.

1 Build a marketing campaign focusing on show how bikes help people to get to ,while maintaining the planet green and avoid traffic. The ads could be show on professional social networks. 2 Increase benefits for riding during cold months. Coupons and discounts could be handed out. 3 As the bikes are also used for recreations on the weekends, ads campaigns could also be made showing people using the bikes for exercise during the weeks. The ads could focus on how practical and consistent the bikes can be.

Conclusion

The Google Analytics Professional Certificate teach a lot and the R language is really helpful for analyzing data.

Bike share navigate speedy success

Jatin Singh

2025-08-11

Reading data from CSV file

Combining

Data cleaning

Parsing datetime columns

Manipulating the data

year_month

Weekday

start_hour

Saving the result as a CSV

Guiding questions

Key Tasks

Deliverable

Analyze

To quick start, let’s generate a summary of the dateset

Data Distribution

Casuals vs. Members

Month

Some consideration can be taken by this charts:

weekday

It’s interesting to see

Hour of the day

The two plots differs on some key ways:

Rideable type

We can see that

Get a summary of the ride_time_m column to understand its distribution

Guiding questions

Key tasks

Deliverable

Key tasks

Deliverable

Conclusion

Bike share navigate speedy success

Jatin Singh

2025-08-11

Reading data from CSV file

Combining

Data cleaning

Parsing datetime columns

Manipulating the data

year_month

Weekday

start_hour

Saving the result as a CSV

Guiding questions

Key Tasks

Deliverable

Analyze

To quick start, let’s generate a summary of the dateset

Data Distribution

Casuals vs. Members

Month

Some consideration can be taken by this charts:

weekday

It’s interesting to see

Hour of the day

The two plots differs on some key ways:

Rideable type

We can see that

Get a summary of the ride_time_m column to understand its distribution

Guiding questions

Key tasks

Deliverable

Share

Key tasks

Deliverable

Conclusion