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