1 Introduction

1.1 Client & Business Problem

Our Client is the Department of General Services of Baltimore City Government and their fleet repair & management team. They are having an unusual Business problem, they are spending too much on vehicles and too many are coming back too often. Or at least this is what they perceive, they want us to analyse their data set and give a mathematical definition of ‘what exactly is a comeback’. We start by exploring two vehicle spending and developing some tools to keep visiting thorough our analysis. This leads to ultimately defining a comeback. Lastly we finally predict the chance of comeback to help the city better manage their fleet and reduce their spending.

1.2 Preparation

This step involves setting Up libraries and themes and loading all the relevant data and converting it into the appropriate required format.

library(tidyverse)
library(sf)
library(QuantPsyc)
library(RSocrata)
library(viridis)
library(caret)
library(spatstat)
library(spdep)
library(FNN)
library(grid)
library(gridExtra)
library(knitr)
library(kableExtra)
library(rgdal)
library(tidycensus)
library(knitr)
library(rmarkdown)
library(readxl)
library(lubridate)
library(dygraphs)
library(zoo)
library(xts)
library(rmdformats)
library(tidyverse)
library(readxl)
library(lubridate)
library(tidyverse)

This is the step for accessing our data in the desired format and below we can even have a quick glimpse at it.

setwd("/Users/akshaynagar/Desktop/To_Org/In_Out_Temp")




dat <- read_xlsx(path = "Comeback_data_12_6_2019.xlsx", sheet = 1) %>%
  rbind(read_xlsx(path = "Comeback_data_12_6_2019.xlsx", sheet = 2)) %>%
  rbind(read_xlsx(path = "Comeback_data_12_6_2019.xlsx", sheet = 3)) %>%
  rbind(read_xlsx(path = "Comeback_data_12_6_2019.xlsx", sheet = 4)) %>%
  rename(EPCycleLength = EPCycleLenghth) %>%
  rbind(read_xlsx(path = "Comeback_data_12_6_2019.xlsx", sheet = 5)) %>%
  mutate(PostDate = ymd(as.character(PostDate)))


glimpse(dat)

## Observations: 925,256
## Variables: 21
## $ VehicleCost   <dbl> 209353, 209353, 209353, 209353, 209353, 209353, 209353,…
## $ EhKey         <chr> "000024", "000024", "000024", "000024", "000024", "0000…
## $ WHKey         <chr> "0000539542", "0000546043", "0000546043", "0000546725",…
## $ Make          <chr> "PIERCE", "PIERCE", "PIERCE", "PIERCE", "PIERCE", "PIER…
## $ Model         <chr> "SABER", "SABER", "SABER", "SABER", "SABER", "SABER", "…
## $ MajGroup      <chr> "Fire", "Fire", "Fire", "Fire", "Fire", "Fire", "Fire",…
## $ EqcDesc       <chr> "FIRE DEPARTMENT", "FIRE DEPARTMENT", "FIRE DEPARTMENT"…
## $ Dept          <chr> "64-00", "64-00", "64-00", "64-00", "64-00", "64-00", "…
## $ EPCycleLength <dbl> 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6…
## $ CycleDESC     <chr> "MONTHS", "MONTHS", "MONTHS", "MONTHS", "MONTHS", "MONT…
## $ BhLineChr     <dbl> 879.18, 87.71, 81.33, 146.48, 131.57, 131.57, 249.90, 3…
## $ AcctType      <chr> "SU", "LA", "PA", "LA", "LA", "LA", "LA", "LA", "LA", "…
## $ PostDate      <date> 2014-07-15, 2014-07-09, 2014-08-21, 2014-07-15, 2014-0…
## $ MechID        <dbl> NA, 5217, NA, 5151, 5010, 5025, 5178, 5205, 5232, 5247,…
## $ WHShop        <chr> "TRK", "FIR", "FIR", "FWY", "FIR", "FIR", "FIR", "FIR",…
## $ SHPDESC       <chr> "TRUCK SIDE - CENTRAL", "FIRE MAINTENANCE", "FIRE MAINT…
## $ Reason        <chr> "9", "R", "R", NA, "8", "8", "8", "8", "R", "R", "2", "…
## $ RTRDESC       <chr> "Road Call (Vendor)", "Agency Request", "Agency Request…
## $ RGDESC        <chr> "Tire Service", "Engine Exhaust", "Electrical", "Igniti…
## $ RCDESC        <chr> "Tire Heavy", "Misc Parts", "System", "System", "Comple…
## $ RRDESC        <chr> "Replace L/F", "Repair", "Part Issue", "Road Service", …

1.3 Creating General Time Lag

This step involves creating a time lag for all the data, this takes into account EhKey, which is referred to as Vehicle ID in this documentation, and WHKey which is referred to as Trip ID for the purpose of this documentation, keeping both the Vehicle ID, and the Trip ID, and taking into account the initial date for each of these along with each successive date, a Time lag data is created, please note this is done for the purpose of creating a powerful tool, which will help us better understand and define comeback rates, this in itself is not the comeback rate.

dat_01  <-  dat %>%
  group_by(EhKey) %>%
  group_by(WHKey) %>%
  dplyr::slice(which.min(PostDate))
dat_02 <- dat_01 %>%
  mutate(PostDate = ymd(as.character(PostDate))) %>%
  group_by(EhKey) %>%
  arrange(PostDate) %>%
  mutate(lagTime = PostDate - lag(PostDate))

2 What is a Comeback

2.1 Comeback Defining

A comeback Trip is thus best explained in Detail in the following Chart Below. The Image in the Red is comeback trips these are trip that always talk about the same part and where warranty gets lapsed, a regular is similar except the same part is getting repaired after it has successfully lived it’s warranty, if two parts are different for two different repairs, then these are Non related trips. This is best shown with the case of examples and with splitting sample data sets, as well as with date examples and examples on actual parts with actual warranty periods taken for a fire truck let’s say with Vehicle ID 24, in the following example, illustrated in detail in the chart below.

Baltimore Fleet Management