Dalam pelajaran kali ini, kita mencoba menganalisis data perjalanan historis Cylistic untuk menganalisis dan mengidentifikasi tren yang sedang terjadi. Ini merupakan data publik yang disediakan oleh Motivate International Inc, yang tentunya dataset tersebut dapat kita gunakan untuk media belajar Link Dataset Di sini saya menggunakan dataset : Divvy_Trips_2020_Q1,Divvy_Trips_2019_Q2,Divvy_Trips_2019_Q3 dan Divvy_Trips_2019_Q4
library(tidyverse)
## ── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
## ✔ dplyr 1.1.2 ✔ readr 2.1.4
## ✔ forcats 1.0.0 ✔ stringr 1.5.0
## ✔ ggplot2 3.4.2 ✔ tibble 3.2.1
## ✔ lubridate 1.9.2 ✔ tidyr 1.3.0
## ✔ purrr 1.0.1
## ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
## ✖ dplyr::filter() masks stats::filter()
## ✖ dplyr::lag() masks stats::lag()
## ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors
library(ggplot2)
library(readr)
library(lubridate)
Disini saya menggunakan penamaan q1 untuk Dataset Divvy_Trips_2020_Q1.csv dan seterusnya.
q1 <- read.csv("Divvy_Trips_2020_Q1.csv")
q2 <- read.csv("Divvy_Trips_2019_Q2.csv")
q3 <- read.csv("Divvy_Trips_2019_Q3.csv")
q4 <- read.csv("Divvy_Trips_2019_Q4.csv")
Sebelum menggabungkan dataset menjadi satu file dataset, alangkah lebih baiknya kita terlebih dahulu memeriksa apakah semua data telah sesuai atau tidak, khususnya untuk penamaan kolom, jika nama kolom sudah sesuai maka kita bisa lanjutkan untuk menggabungkannya. Jika tidak, maka kita harus sesuaikan terlebih dahulu.
colnames(q1)
## [1] "ride_id" "rideable_type" "started_at"
## [4] "ended_at" "start_station_name" "start_station_id"
## [7] "end_station_name" "end_station_id" "start_lat"
## [10] "start_lng" "end_lat" "end_lng"
## [13] "member_casual"
colnames(q2)
## [1] "X01...Rental.Details.Rental.ID"
## [2] "X01...Rental.Details.Local.Start.Time"
## [3] "X01...Rental.Details.Local.End.Time"
## [4] "X01...Rental.Details.Bike.ID"
## [5] "X01...Rental.Details.Duration.In.Seconds.Uncapped"
## [6] "X03...Rental.Start.Station.ID"
## [7] "X03...Rental.Start.Station.Name"
## [8] "X02...Rental.End.Station.ID"
## [9] "X02...Rental.End.Station.Name"
## [10] "User.Type"
## [11] "Member.Gender"
## [12] "X05...Member.Details.Member.Birthday.Year"
colnames(q3)
## [1] "trip_id" "start_time" "end_time"
## [4] "bikeid" "tripduration" "from_station_id"
## [7] "from_station_name" "to_station_id" "to_station_name"
## [10] "usertype" "gender" "birthyear"
colnames(q4)
## [1] "trip_id" "start_time" "end_time"
## [4] "bikeid" "tripduration" "from_station_id"
## [7] "from_station_name" "to_station_id" "to_station_name"
## [10] "usertype" "gender" "birthyear"
str(q2)
## 'data.frame': 1108163 obs. of 12 variables:
## $ X01...Rental.Details.Rental.ID : int 22178529 22178530 22178531 22178532 22178533 22178534 22178535 22178536 22178537 22178538 ...
## $ X01...Rental.Details.Local.Start.Time : chr "2019-04-01 00:02:22" "2019-04-01 00:03:02" "2019-04-01 00:11:07" "2019-04-01 00:13:01" ...
## $ X01...Rental.Details.Local.End.Time : chr "2019-04-01 00:09:48" "2019-04-01 00:20:30" "2019-04-01 00:15:19" "2019-04-01 00:18:58" ...
## $ X01...Rental.Details.Bike.ID : int 6251 6226 5649 4151 3270 3123 6418 4513 3280 5534 ...
## $ X01...Rental.Details.Duration.In.Seconds.Uncapped: chr "446.0" "1,048.0" "252.0" "357.0" ...
## $ X03...Rental.Start.Station.ID : int 81 317 283 26 202 420 503 260 211 211 ...
## $ X03...Rental.Start.Station.Name : chr "Daley Center Plaza" "Wood St & Taylor St" "LaSalle St & Jackson Blvd" "McClurg Ct & Illinois St" ...
## $ X02...Rental.End.Station.ID : int 56 59 174 133 129 426 500 499 211 211 ...
## $ X02...Rental.End.Station.Name : chr "Desplaines St & Kinzie St" "Wabash Ave & Roosevelt Rd" "Canal St & Madison St" "Kingsbury St & Kinzie St" ...
## $ User.Type : chr "Subscriber" "Subscriber" "Subscriber" "Subscriber" ...
## $ Member.Gender : chr "Male" "Female" "Male" "Male" ...
## $ X05...Member.Details.Member.Birthday.Year : int 1975 1984 1990 1993 1992 1999 1969 1991 NA NA ...
Agar semua dataset memiliki nama kolom yang sesuai maka kita harus merubah namanya. Disini saya menggunakan pernamaan seperi pada dataset Q1 2020 karena memang itu merupakan dataset terbaru yang dimiliki perusahaan. Maka kita harus merubah beberapa nama kolom di dataset q2,q3 dan q4.
(q4 <- rename(q4,ride_id = trip_id
,rideable_type = bikeid
,started_at = start_time
,ended_at = end_time
,start_station_name = from_station_name
,start_station_id = from_station_id
,end_station_name = to_station_name
,end_station_id = to_station_id
,member_casual = usertype))
(q3 <- rename(q3
,ride_id = trip_id
,rideable_type = bikeid
,started_at = start_time
,ended_at = end_time
,start_station_name = from_station_name
,start_station_id = from_station_id
,end_station_name = to_station_name
,end_station_id = to_station_id
,member_casual = usertype))
(q2 <- rename(q2
,ride_id = "X01...Rental.Details.Rental.ID"
,rideable_type = "X01...Rental.Details.Bike.ID"
,started_at = "X01...Rental.Details.Local.Start.Time"
,ended_at = "X01...Rental.Details.Local.End.Time"
,start_station_name = "X03...Rental.Start.Station.Name"
,start_station_id = "X03...Rental.Start.Station.ID"
,end_station_name = "X02...Rental.End.Station.Name"
,end_station_id = "X02...Rental.End.Station.ID"
,member_casual = "User.Type"))
str(q1)
## 'data.frame': 426887 obs. of 13 variables:
## $ ride_id : chr "EACB19130B0CDA4A" "8FED874C809DC021" "789F3C21E472CA96" "C9A388DAC6ABF313" ...
## $ rideable_type : chr "docked_bike" "docked_bike" "docked_bike" "docked_bike" ...
## $ started_at : chr "2020-01-21 20:06:59" "2020-01-30 14:22:39" "2020-01-09 19:29:26" "2020-01-06 16:17:07" ...
## $ ended_at : chr "2020-01-21 20:14:30" "2020-01-30 14:26:22" "2020-01-09 19:32:17" "2020-01-06 16:25:56" ...
## $ start_station_name: chr "Western Ave & Leland Ave" "Clark St & Montrose Ave" "Broadway & Belmont Ave" "Clark St & Randolph St" ...
## $ start_station_id : int 239 234 296 51 66 212 96 96 212 38 ...
## $ end_station_name : chr "Clark St & Leland Ave" "Southport Ave & Irving Park Rd" "Wilton Ave & Belmont Ave" "Fairbanks Ct & Grand Ave" ...
## $ end_station_id : int 326 318 117 24 212 96 212 212 96 100 ...
## $ start_lat : num 42 42 41.9 41.9 41.9 ...
## $ start_lng : num -87.7 -87.7 -87.6 -87.6 -87.6 ...
## $ end_lat : num 42 42 41.9 41.9 41.9 ...
## $ end_lng : num -87.7 -87.7 -87.7 -87.6 -87.6 ...
## $ member_casual : chr "member" "member" "member" "member" ...
str(q2)
## 'data.frame': 1108163 obs. of 12 variables:
## $ ride_id : int 22178529 22178530 22178531 22178532 22178533 22178534 22178535 22178536 22178537 22178538 ...
## $ started_at : chr "2019-04-01 00:02:22" "2019-04-01 00:03:02" "2019-04-01 00:11:07" "2019-04-01 00:13:01" ...
## $ ended_at : chr "2019-04-01 00:09:48" "2019-04-01 00:20:30" "2019-04-01 00:15:19" "2019-04-01 00:18:58" ...
## $ rideable_type : int 6251 6226 5649 4151 3270 3123 6418 4513 3280 5534 ...
## $ X01...Rental.Details.Duration.In.Seconds.Uncapped: chr "446.0" "1,048.0" "252.0" "357.0" ...
## $ start_station_id : int 81 317 283 26 202 420 503 260 211 211 ...
## $ start_station_name : chr "Daley Center Plaza" "Wood St & Taylor St" "LaSalle St & Jackson Blvd" "McClurg Ct & Illinois St" ...
## $ end_station_id : int 56 59 174 133 129 426 500 499 211 211 ...
## $ end_station_name : chr "Desplaines St & Kinzie St" "Wabash Ave & Roosevelt Rd" "Canal St & Madison St" "Kingsbury St & Kinzie St" ...
## $ member_casual : chr "Subscriber" "Subscriber" "Subscriber" "Subscriber" ...
## $ Member.Gender : chr "Male" "Female" "Male" "Male" ...
## $ X05...Member.Details.Member.Birthday.Year : int 1975 1984 1990 1993 1992 1999 1969 1991 NA NA ...
str(q3)
## 'data.frame': 1640718 obs. of 12 variables:
## $ ride_id : int 23479388 23479389 23479390 23479391 23479392 23479393 23479394 23479395 23479396 23479397 ...
## $ started_at : chr "2019-07-01 00:00:27" "2019-07-01 00:01:16" "2019-07-01 00:01:48" "2019-07-01 00:02:07" ...
## $ ended_at : chr "2019-07-01 00:20:41" "2019-07-01 00:18:44" "2019-07-01 00:27:42" "2019-07-01 00:27:10" ...
## $ rideable_type : int 3591 5353 6180 5540 6014 4941 3770 5442 2957 6091 ...
## $ tripduration : chr "1,214.0" "1,048.0" "1,554.0" "1,503.0" ...
## $ start_station_id : int 117 381 313 313 168 300 168 313 43 43 ...
## $ start_station_name: chr "Wilton Ave & Belmont Ave" "Western Ave & Monroe St" "Lakeview Ave & Fullerton Pkwy" "Lakeview Ave & Fullerton Pkwy" ...
## $ end_station_id : int 497 203 144 144 62 232 62 144 195 195 ...
## $ end_station_name : chr "Kimball Ave & Belmont Ave" "Western Ave & 21st St" "Larrabee St & Webster Ave" "Larrabee St & Webster Ave" ...
## $ member_casual : chr "Subscriber" "Customer" "Customer" "Customer" ...
## $ gender : chr "Male" "" "" "" ...
## $ birthyear : int 1992 NA NA NA NA 1990 NA NA NA NA ...
str(q4)
## 'data.frame': 704054 obs. of 12 variables:
## $ ride_id : int 25223640 25223641 25223642 25223643 25223644 25223645 25223646 25223647 25223648 25223649 ...
## $ started_at : chr "2019-10-01 00:01:39" "2019-10-01 00:02:16" "2019-10-01 00:04:32" "2019-10-01 00:04:32" ...
## $ ended_at : chr "2019-10-01 00:17:20" "2019-10-01 00:06:34" "2019-10-01 00:18:43" "2019-10-01 00:43:43" ...
## $ rideable_type : int 2215 6328 3003 3275 5294 1891 1061 1274 6011 2957 ...
## $ tripduration : chr "940.0" "258.0" "850.0" "2,350.0" ...
## $ start_station_id : int 20 19 84 313 210 156 84 156 156 336 ...
## $ start_station_name: chr "Sheffield Ave & Kingsbury St" "Throop (Loomis) St & Taylor St" "Milwaukee Ave & Grand Ave" "Lakeview Ave & Fullerton Pkwy" ...
## $ end_station_id : int 309 241 199 290 382 226 142 463 463 336 ...
## $ end_station_name : chr "Leavitt St & Armitage Ave" "Morgan St & Polk St" "Wabash Ave & Grand Ave" "Kedzie Ave & Palmer Ct" ...
## $ member_casual : chr "Subscriber" "Subscriber" "Subscriber" "Subscriber" ...
## $ gender : chr "Male" "Male" "Female" "Male" ...
## $ birthyear : int 1987 1998 1991 1990 1987 1994 1991 1995 1993 NA ...
Untuk menyesuaikan dengan format data di dataset Q1 2020, maka kita perlu merubah type data pada kolom ride_id dan rideable_type pada q4,q3,q2 yang awalnya integer menjadi character.
q4 <- mutate(q4, ride_id = as.character(ride_id)
,rideable_type = as.character(rideable_type))
q3 <- mutate(q3, ride_id = as.character(ride_id)
,rideable_type = as.character(rideable_type))
q2 <- mutate(q2, ride_id = as.character(ride_id)
,rideable_type = as.character(rideable_type))
all_trips <- bind_rows(q1,q2,q3,q4)
Kita akan menghapus beberapa kolom yang tidak dibutuhkan seperti lat, long, birthyear, dan gender karena pada data tahun 2020, kolom tersebut tidak dibutuhkan lagi.
all_trips <- all_trips %>%
select(-c(start_lat, start_lng, end_lat, end_lng, birthyear, gender, "X01...Rental.Details.Duration.In.Seconds.Uncapped", "X05...Member.Details.Member.Birthday.Year", "Member.Gender", "tripduration"))
colnames(all_trips)
## [1] "ride_id" "rideable_type" "started_at"
## [4] "ended_at" "start_station_name" "start_station_id"
## [7] "end_station_name" "end_station_id" "member_casual"
dim(all_trips)
## [1] 3879822 9
head(all_trips)
nrow(all_trips)
## [1] 3879822
summary(all_trips)
## ride_id rideable_type started_at ended_at
## Length:3879822 Length:3879822 Length:3879822 Length:3879822
## Class :character Class :character Class :character Class :character
## Mode :character Mode :character Mode :character Mode :character
##
##
##
##
## start_station_name start_station_id end_station_name end_station_id
## Length:3879822 Min. : 1.0 Length:3879822 Min. : 1.0
## Class :character 1st Qu.: 77.0 Class :character 1st Qu.: 77.0
## Mode :character Median :174.0 Mode :character Median :174.0
## Mean :202.9 Mean :203.8
## 3rd Qu.:291.0 3rd Qu.:291.0
## Max. :675.0 Max. :675.0
## NA's :1
## member_casual
## Length:3879822
## Class :character
## Mode :character
##
##
##
##
table(all_trips$member_casual)
##
## casual Customer member Subscriber
## 48480 857474 378407 2595461
Disini kita dapat melihat bahwa di dalam kolom member casual terdapat dua data untuk member,yaitu : member dan subscriber. Dan data untuk casual rider,yaitu : casual dan customer. Selanjutnya kita akan menggabungkan data tersebut menjadi dua data saja.Yaitu: member dan casual saja.
all_trips <- all_trips %>%
mutate(member_casual = recode(member_casual
,"Subscriber" = "member"
,"Customer" = "casual"))
table(all_trips$member_casual)
##
## casual member
## 905954 2973868
head(all_trips)
Dengan ini kita dapat mengumpulkan data perjalanan untuk setiap bulan, hari ataupun tahun
all_trips$date <- as.Date(all_trips$started_at)
all_trips$month <- format(as.Date(all_trips$date), "%m")
all_trips$day <- format(as.Date(all_trips$date), "%d")
all_trips$year <- format(as.Date(all_trips$date), "%Y")
all_trips$day_of_week <- format(as.Date(all_trips$date), "%A")
all_trips$ride_length <- difftime(all_trips$ended_at,all_trips$started_at)
str(all_trips)
## 'data.frame': 3879822 obs. of 15 variables:
## $ ride_id : chr "EACB19130B0CDA4A" "8FED874C809DC021" "789F3C21E472CA96" "C9A388DAC6ABF313" ...
## $ rideable_type : chr "docked_bike" "docked_bike" "docked_bike" "docked_bike" ...
## $ started_at : chr "2020-01-21 20:06:59" "2020-01-30 14:22:39" "2020-01-09 19:29:26" "2020-01-06 16:17:07" ...
## $ ended_at : chr "2020-01-21 20:14:30" "2020-01-30 14:26:22" "2020-01-09 19:32:17" "2020-01-06 16:25:56" ...
## $ start_station_name: chr "Western Ave & Leland Ave" "Clark St & Montrose Ave" "Broadway & Belmont Ave" "Clark St & Randolph St" ...
## $ start_station_id : int 239 234 296 51 66 212 96 96 212 38 ...
## $ end_station_name : chr "Clark St & Leland Ave" "Southport Ave & Irving Park Rd" "Wilton Ave & Belmont Ave" "Fairbanks Ct & Grand Ave" ...
## $ end_station_id : int 326 318 117 24 212 96 212 212 96 100 ...
## $ member_casual : chr "member" "member" "member" "member" ...
## $ date : Date, format: "2020-01-21" "2020-01-30" ...
## $ month : chr "01" "01" "01" "01" ...
## $ day : chr "21" "30" "09" "06" ...
## $ year : chr "2020" "2020" "2020" "2020" ...
## $ day_of_week : chr "Tuesday" "Thursday" "Thursday" "Monday" ...
## $ ride_length : 'difftime' num 451 223 171 529 ...
## ..- attr(*, "units")= chr "secs"
head(all_trips)
is.factor(all_trips$ride_length)
## [1] FALSE
all_trips$ride_length <- as.numeric(as.character(all_trips$ride_length))
is.numeric(all_trips$ride_length)
## [1] TRUE
Untuk memudahkan proses analisis, selanjutnya kita akan membuang data yang tidak dibutuhkan, misalkan yang mengandung angka negatif atau kurang dari nol. Dan kita akan membuat data baru dengan nama all_trips_v2
all_trips_v2 <- all_trips[!(all_trips$start_station_name == "HQ QR" | all_trips$ride_length<0),]
mean(all_trips_v2$ride_length)
## [1] 1479.139
median(all_trips_v2$ride_length)
## [1] 712
max(all_trips_v2$ride_length)
## [1] 9387024
min(all_trips_v2$ride_length)
## [1] 1
Ataupun kita dapat memadatkannya dari 4 baris menjadi satu baris agar mempermudah.
summary(all_trips_v2$ride_length)
## Min. 1st Qu. Median Mean 3rd Qu. Max.
## 1 412 712 1479 1289 9387024
aggregate(all_trips_v2$ride_length ~ all_trips_v2$member_casual, FUN = mean)
aggregate(all_trips_v2$ride_length ~ all_trips_v2$member_casual, FUN = median)
aggregate(all_trips_v2$ride_length ~ all_trips_v2$member_casual, FUN = max)
aggregate(all_trips_v2$ride_length ~ all_trips_v2$member_casual, FUN = min)
aggregate(all_trips_v2$ride_length ~ all_trips_v2$member_casual + all_trips_v2$day_of_week, FUN = mean)
Kita lihat sebelumnya bahwa hari tidak teratur. Selanjutnya kita akan memperbaikinya.
all_trips_v2$day_of_week <- ordered(all_trips_v2$day_of_week, levels=c("Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"))
aggregate(all_trips_v2$ride_length ~ all_trips_v2$member_casual + all_trips_v2$day_of_week, FUN = mean)
all_trips_v2 %>%
mutate(weekday = wday(started_at, label = TRUE)) %>% #membuat weekday field menggunakan wday()
group_by(member_casual, weekday) %>% #mengelompokkan berdasarkan usertype dan weekday
summarise(number_of_rides = n()
,average_duration = mean(ride_length)) %>% # menghitung rata-rata
arrange(member_casual, weekday)
## `summarise()` has grouped output by 'member_casual'. You can override using the
## `.groups` argument.
all_trips_v2 %>%
mutate(weekday = wday(started_at, label = TRUE)) %>%
group_by(member_casual, weekday) %>%
summarise(number_of_rides = n()
,average_duration = mean(ride_length)) %>%
arrange(member_casual, weekday) %>%
ggplot(aes(x = weekday, y = number_of_rides, fill = member_casual)) +
geom_col(position = "dodge")
## `summarise()` has grouped output by 'member_casual'. You can override using the
## `.groups` argument.
all_trips_v2 %>%
mutate(weekday = wday(started_at, label = TRUE)) %>%
group_by(member_casual, weekday) %>%
summarise(number_of_rides = n()
,average_duration = mean(ride_length)) %>%
arrange(member_casual, weekday) %>%
ggplot(aes(x = weekday, y = average_duration, fill = member_casual)) +
geom_col(position = "dodge")
## `summarise()` has grouped output by 'member_casual'. You can override using the
## `.groups` argument.
Selanjutnya untuk menganalisis lebih lanjut tentang data yang sudah kita analisis tadi kita dapat mengeksport file csv dataset tersebut dan menganalisisnya menggunakan Excel,tableau, looker studio ataupun yang lainnya. Sekaligus membuat presentasi yang bagus untuk hasil analisis yang sudah dilakukan.
Terimakasih, selamat mencoba.