pkg <- c("readr","readxl","dplyr","stringr","ggplot2","tidyr","car")
pkgload <- lapply(pkg, require, character.only = TRUE)
## Loading required package: readr
## Loading required package: readxl
## Loading required package: dplyr
##
## Attaching package: 'dplyr'
## The following objects are masked from 'package:stats':
##
## filter, lag
## The following objects are masked from 'package:base':
##
## intersect, setdiff, setequal, union
## Loading required package: stringr
## Loading required package: ggplot2
## Loading required package: tidyr
## Loading required package: car
## Loading required package: carData
##
## Attaching package: 'car'
## The following object is masked from 'package:dplyr':
##
## recode
getwd()
## [1] "/Users/MichaelMiao/Documents/career/R"
library("graphics")
#library("sm")
library(lubridate)
##
## Attaching package: 'lubridate'
## The following object is masked from 'package:base':
##
## date
data1 <- read_xlsx("HopSkipDrive_data.xlsx",sheet = "Flat file")
data2 <- read_xlsx("HopSkipDrive_data.xlsx",sheet = "Driver home zipcode and metro")
glimpse(data2)
## Observations: 4,411
## Variables: 3
## $ `Driver ID` <dbl> 27255, 62901, 62908, 62912, 62923, 62926, 62930, 629…
## $ Zipcode <dbl> 90007, 90048, 80222, 90007, 90007, 90007, 90068, 900…
## $ Metro <chr> "LA", "LA", "DN", "LA", "LA", "LA", "LA", "LA", "LA"…
glimpse(data1)
## Observations: 1,766
## Variables: 11
## $ `Driver ID` <dbl> 183258, 184470, 183265, 184472, 184505, 185…
## $ `Date of Activation` <dttm> 2018-01-10, 2018-01-10, 2018-01-11, 2018-0…
## $ `Completed Trips` <dbl> 117, 6, 171, 292, 140, 1553, 3, 0, 28, 156,…
## $ `Date of 5th Trip` <dttm> 2018-01-12, 2018-01-17, 2018-01-18, 2018-0…
## $ `Date of 10th Trip` <dttm> 2018-01-16, NA, 2018-01-24, 2018-01-25, 20…
## $ `Date of 15th Trip` <dttm> 2018-01-17, NA, 2018-02-01, 2018-01-31, 20…
## $ `Date of 20th Trip` <dttm> 2018-01-22, NA, 2018-02-08, 2018-02-07, 20…
## $ `Date of 25th Trip` <dttm> 2018-01-23, NA, 2018-02-12, 2018-02-09, 20…
## $ `Date of 30th Trip` <dttm> 2018-01-25, NA, 2018-02-22, 2018-02-13, 20…
## $ `First Trip` <dttm> 2018-01-10, 2018-01-12, 2018-01-12, 2018-0…
## $ `Last Trip` <dttm> 2018-12-14, 2018-01-22, 2018-12-21, 2018-1…
str(data1)
## Classes 'tbl_df', 'tbl' and 'data.frame': 1766 obs. of 11 variables:
## $ Driver ID : num 183258 184470 183265 184472 184505 ...
## $ Date of Activation: POSIXct, format: "2018-01-10" "2018-01-10" ...
## $ Completed Trips : num 117 6 171 292 140 ...
## $ Date of 5th Trip : POSIXct, format: "2018-01-12" "2018-01-17" ...
## $ Date of 10th Trip : POSIXct, format: "2018-01-16" NA ...
## $ Date of 15th Trip : POSIXct, format: "2018-01-17" NA ...
## $ Date of 20th Trip : POSIXct, format: "2018-01-22" NA ...
## $ Date of 25th Trip : POSIXct, format: "2018-01-23" NA ...
## $ Date of 30th Trip : POSIXct, format: "2018-01-25" NA ...
## $ First Trip : POSIXct, format: "2018-01-10" "2018-01-12" ...
## $ Last Trip : POSIXct, format: "2018-12-14" "2018-01-22" ...
str(data2)
## Classes 'tbl_df', 'tbl' and 'data.frame': 4411 obs. of 3 variables:
## $ Driver ID: num 27255 62901 62908 62912 62923 ...
## $ Zipcode : num 90007 90048 80222 90007 90007 ...
## $ Metro : chr "LA" "LA" "DN" "LA" ...
data_full <- data1 %>% select(.,ID=`Driver ID`,DateAct = `Date of Activation`,Trip= `Completed Trips`,Date5=`Date of 5th Trip`, Date10= `Date of 10th Trip`,Date15=`Date of 15th Trip`,Date20=`Date of 20th Trip`, Date25=`Date of 25th Trip`,Date30=`Date of 30th Trip`, FTrip=`First Trip`,LTrip=`Last Trip` ) # rename and simply for
datause1= data_full # keep orginal.
data_full2 <- data2 %>% select(.,ID=`Driver ID`,Zipcode,Metro)
datause2=data_full2 # keep orginal.
datause1 %>% head(.,6)
## # A tibble: 6 x 11
## ID DateAct Trip Date5 Date10
## <dbl> <dttm> <dbl> <dttm> <dttm>
## 1 183258 2018-01-10 00:00:00 117 2018-01-12 00:00:00 2018-01-16 00:00:00
## 2 184470 2018-01-10 00:00:00 6 2018-01-17 00:00:00 NA
## 3 183265 2018-01-11 00:00:00 171 2018-01-18 00:00:00 2018-01-24 00:00:00
## 4 184472 2018-01-12 00:00:00 292 2018-01-22 00:00:00 2018-01-25 00:00:00
## 5 184505 2018-01-16 00:00:00 140 2018-01-30 00:00:00 2018-02-05 00:00:00
## 6 185325 2018-01-16 00:00:00 1553 2018-01-18 00:00:00 2018-01-22 00:00:00
## # … with 6 more variables: Date15 <dttm>, Date20 <dttm>, Date25 <dttm>,
## # Date30 <dttm>, FTrip <dttm>, LTrip <dttm>
summary_tab1 <- data.frame(mean(datause1$Date5-datause1$DateAct,na.rm = T),
median(datause1$Date5-datause1$DateAct,na.rm = T),
mean(datause1$Date10-datause1$DateAct,na.rm = T),
median(datause1$Date10-datause1$DateAct,na.rm = T),
mean(datause1$Date15-datause1$DateAct,na.rm = T),
median(datause1$Date15-datause1$DateAct,na.rm = T),
mean(datause1$Date20-datause1$DateAct,na.rm = T),
median(datause1$Date20-datause1$DateAct,na.rm = T),
mean(datause1$Date25-datause1$DateAct,na.rm = T),
median(datause1$Date25-datause1$DateAct,na.rm = T),
mean(datause1$Date30-datause1$DateAct,na.rm = T),
median(datause1$Date30-datause1$DateAct,na.rm = T),row.names= "Days")
colnames(summary_tab1) <- c("Mean for 5 Trips","Median for 5 Trips","Mean for 10 Trips","Median for 10 Trips","Mean for 15 Trips","Median for 15 Trips","Mean for 20 Trips","Median for 20 Trips","Mean for 25 Trips","Median for 25 Trips","Mean for 30 Trips","Median for 30 Trips")
table1 <- t(summary_tab1) %>% print()
## Days
## Mean for 5 Trips "14.9443 days"
## Median for 5 Trips "8 days"
## Mean for 10 Trips "22.51009 days"
## Median for 10 Trips "14 days"
## Mean for 15 Trips "28.66062 days"
## Median for 15 Trips "18 days"
## Mean for 20 Trips "33.86496 days"
## Median for 20 Trips "22 days"
## Mean for 25 Trips "39.15863 days"
## Median for 25 Trips "27 days"
## Mean for 30 Trips "43.56293 days"
## Median for 30 Trips "31 days"
# not normal distribution, so median is more useful than mean in this case
d30 <- density(as.double(na.omit(datause1$Date30-datause1$DateAct)))
plot(d30,main = "Distribution for 30 trips",col="red")

colSums(is.na(datause1)) #1043 drivers don't reach 30 trips after actived
## ID DateAct Trip Date5 Date10 Date15 Date20 Date25 Date30
## 0 0 0 599 725 829 907 978 1043
## FTrip LTrip
## 387 387
dim(datause1) # 1766 actived users
## [1] 1766 11
1766-1043 # number of drivers who complete 30 trips after activation
## [1] 723
# with mean
cat("mean",mean(datause1$Date30-datause1$DateAct,na.rm = T)," days \n")
## mean 43.56293 days
cat("median",median(datause1$Date30-datause1$DateAct,na.rm = T),"days \n")
## median 31 days
datause1$Conversation30 <- datause1$Date30-datause1$DateAct
head(datause1)
## # A tibble: 6 x 12
## ID DateAct Trip Date5 Date10
## <dbl> <dttm> <dbl> <dttm> <dttm>
## 1 183258 2018-01-10 00:00:00 117 2018-01-12 00:00:00 2018-01-16 00:00:00
## 2 184470 2018-01-10 00:00:00 6 2018-01-17 00:00:00 NA
## 3 183265 2018-01-11 00:00:00 171 2018-01-18 00:00:00 2018-01-24 00:00:00
## 4 184472 2018-01-12 00:00:00 292 2018-01-22 00:00:00 2018-01-25 00:00:00
## 5 184505 2018-01-16 00:00:00 140 2018-01-30 00:00:00 2018-02-05 00:00:00
## 6 185325 2018-01-16 00:00:00 1553 2018-01-18 00:00:00 2018-01-22 00:00:00
## # … with 7 more variables: Date15 <dttm>, Date20 <dttm>, Date25 <dttm>,
## # Date30 <dttm>, FTrip <dttm>, LTrip <dttm>, Conversation30 <drtn>
723/1766
## [1] 0.4093998
dim(datause1%>% filter(.,(datause1$Date30-datause1$DateAct)<=31))[1]
## [1] 362
dim(datause1%>% filter(.,(datause1$Date30-datause1$DateAct)<=31))[1]/1766
## [1] 0.204983
# the conversation rate is 40.94% for total
# 20.50% if within 31 days.
#left_join(datause1,datause2)
datajoin <- left_join(datause1,datause2)
## Joining, by = "ID"
table(datajoin$Metro) %>% plot(.,type = "p")

table(datajoin$Metro)
##
## BA Default (Unassigned) DN
## 320 4 304
## LA OC SD
## 874 164 100
datajoin$Category <- 0
head(datajoin)
## # A tibble: 6 x 15
## ID DateAct Trip Date5 Date10
## <dbl> <dttm> <dbl> <dttm> <dttm>
## 1 183258 2018-01-10 00:00:00 117 2018-01-12 00:00:00 2018-01-16 00:00:00
## 2 184470 2018-01-10 00:00:00 6 2018-01-17 00:00:00 NA
## 3 183265 2018-01-11 00:00:00 171 2018-01-18 00:00:00 2018-01-24 00:00:00
## 4 184472 2018-01-12 00:00:00 292 2018-01-22 00:00:00 2018-01-25 00:00:00
## 5 184505 2018-01-16 00:00:00 140 2018-01-30 00:00:00 2018-02-05 00:00:00
## 6 185325 2018-01-16 00:00:00 1553 2018-01-18 00:00:00 2018-01-22 00:00:00
## # … with 10 more variables: Date15 <dttm>, Date20 <dttm>, Date25 <dttm>,
## # Date30 <dttm>, FTrip <dttm>, LTrip <dttm>, Conversation30 <drtn>,
## # Zipcode <dbl>, Metro <chr>, Category <dbl>
datajoin$Category[datajoin$Conversation30<=31]="Power"
datajoin$Category[datajoin$Category==0]="Casual"
datajoin$Category <- as.factor(datajoin$Category)
table2 <- tapply(datajoin$Category,datajoin$Metro,summary) %>% print()
## $BA
## Casual Power
## 270 50
##
## $`Default (Unassigned)`
## Casual Power
## 4 0
##
## $DN
## Casual Power
## 276 28
##
## $LA
## Casual Power
## 634 240
##
## $OC
## Casual Power
## 128 36
##
## $SD
## Casual Power
## 92 8
Total=c(1404,362)
table2 <- list(table2,Total=Total)
table2
## [[1]]
## [[1]]$BA
## Casual Power
## 270 50
##
## [[1]]$`Default (Unassigned)`
## Casual Power
## 4 0
##
## [[1]]$DN
## Casual Power
## 276 28
##
## [[1]]$LA
## Casual Power
## 634 240
##
## [[1]]$OC
## Casual Power
## 128 36
##
## [[1]]$SD
## Casual Power
## 92 8
##
##
## $Total
## [1] 1404 362
datajoin$Activation_Month <- month(datajoin$DateAct)
table3 <- tapply(datajoin$Category,datajoin$Activation_Month,summary) %>% print()
## $`1`
## Casual Power
## 17 17
##
## $`2`
## Casual Power
## 42 24
##
## $`3`
## Casual Power
## 41 25
##
## $`4`
## Casual Power
## 35 20
##
## $`5`
## Casual Power
## 74 24
##
## $`6`
## Casual Power
## 54 5
##
## $`7`
## Casual Power
## 103 23
##
## $`8`
## Casual Power
## 280 57
##
## $`9`
## Casual Power
## 219 66
##
## $`10`
## Casual Power
## 187 57
##
## $`11`
## Casual Power
## 159 36
##
## $`12`
## Casual Power
## 193 8
ratio <- NULL
# table3[[1]][1]+table3[[1]][2]
for(i in 1:12){
ratio[i]= table3[[i]][[2]]/(table3[[i]][1]+table3[[i]][2])
print(ratio[i])
}
## [1] 0.5
## [1] 0.3636364
## [1] 0.3787879
## [1] 0.3636364
## [1] 0.244898
## [1] 0.08474576
## [1] 0.1825397
## [1] 0.1691395
## [1] 0.2315789
## [1] 0.2336066
## [1] 0.1846154
## [1] 0.039801
ratio %>% max()
## [1] 0.5