EDA

This is an R Markdown Notebook. When you execute code within the notebook, the results appear beneath the code.

Try executing this chunk by clicking the Run button within the chunk or by placing your cursor inside it and pressing Ctrl+Shift+Enter.

library(tidyverse)

── Attaching packages ──────────────────────────────────────────────────────────────────────────────────────────────── tidyverse 1.2.1 ──
✔ ggplot2 2.2.1     ✔ purrr   0.2.4
✔ tibble  1.4.2     ✔ dplyr   0.7.4
✔ tidyr   0.8.0     ✔ stringr 1.3.0
✔ readr   1.1.1     ✔ forcats 0.3.0
── Conflicts ─────────────────────────────────────────────────────────────────────────────────────────────────── tidyverse_conflicts() ──
✖ dplyr::filter() masks stats::filter()
✖ dplyr::lag()    masks stats::lag()

Add a new chunk by clicking the Insert Chunk button on the toolbar or by pressing Ctrl+Alt+I.

When you save the notebook, an HTML file containing the code and output will be saved alongside it (click the Preview button or press Ctrl+Shift+K to preview the HTML file).

load shapes

df.shapes <- read.csv('data/shapes.txt')

compute distance per shape_id

df.stops <- read.csv('data/stops.txt')
df.routes <- read.csv('data/routes.txt')
df.trips <- read.csv('data/trips.txt')
df.trips <- df.trips %>% filter(!grepl('-',trip_id))
df.routes <- merge(df.routes,df.trips,by='route_id')
df.routes <- merge(df.routes,df.distance,by='shape_id')
## Add total cost 
df.routes$costs <- df.routes$total.distance *3.25
weeks <- paste0('Week.',c(1:25))
minVal <- 0
maxValPassenger <- 72
maxFillVal <- 100
mnPassenger <- (maxValPassenger - minVal)/2
mnFill <- (maxFillVal - minVal)/2
# Generate numbers (mostly) from min to max
mysamp <- function(n, m, s, lwr, upr, rounding) {
  samp <- round(rnorm(n, m, s), rounding)
  samp[samp < lwr] <- lwr
  samp[samp > upr] <- upr
  return (samp)
}
create_traffic <- function(){
  for (w in weeks){
  df.routes[paste(w,'passenger',sep='.')]<-  mysamp(dim(df.routes)[1],mnPassenger,mnPassenger/3, minVal,maxValPassenger,3)
  df.routes[paste(w,'filling',sep='.')]<-  df.routes[paste(w,'passenger',sep='.')]/72*100
  df.routes[paste(w,'on.time',sep='.')]<-sample(c(TRUE,FALSE), dim(df.routes)[1], prob=c(.9,.1),TRUE)
  
  df.routes[paste(w,'revenues',sep='.')] <- df.routes[paste(w,'passenger',sep='.')] * df.routes$total.distance*0.09
  df.routes[paste(w,'profits',sep='.')]<- df.routes[paste(w,'revenues',sep='.')] - df.routes$costs
  }
  return (df.routes)
}
df.routes <- create_traffic()
head(df.routes)

plot top/worst 10 lines per filling ratio.

## Aggregate per trip_id
df.gato <- df.routes %>% select(trip_id,route_long_name,contains("filling")) %>% gather(weeka,filling,-trip_id,-route_long_name)
df.groupy.trip.id <- df.gato %>% group_by(trip_id,route_long_name) %>% summarise(average.filling =mean(filling))
df.top.10.filling <- top_n(ungroup(df.groupy.trip.id),10,average.filling) #%>% select(route_long_name,trip_id, average.filling)
df.worst.10.filling <-top_n(ungroup(df.groupy.trip.id),-10,average.filling) #%>% select(route_long_name,trip_id, average.filling)
write.csv(df.top.10.filling,'data/qlickview/top_filling.csv')
write.csv(df.worst.10.filling,'data/qlickview/worst_filling.csv')

- top 10 late arrival lines.

df.gato.late <- df.routes %>% select(trip_id,route_long_name,contains("time")) %>% gather(weeka,on.time,-trip_id,-route_long_name)
df.groupy.trip.late <- df.gato.late %>% group_by(trip_id,route_long_name) %>% summarise(average.on.time =mean(on.time))
df.top.10.on.time <- top_n(ungroup(df.groupy.trip.late),10,average.on.time) #%>% select(route_long_name,trip_id, average.filling)
df.worst.10.on.time <-top_n(ungroup(df.groupy.trip.late),-10,average.on.time) #%>% select(route_long_name,trip_id, average.filling)
write.csv(df.top.10.on.time,'data/qlickview/top_on_time.csv')
write.csv(df.worst.10.on.time,'data/qlickview/worst_on_time.csv')

top/worst 10 passenger volume averaged growth,

df.routes.valid.growth<-df.routes %>% filter(Week.1.passenger>0)
df.routes.valid.growth$growth <- ((df.routes.valid.growth$Week.25.passenger+df.routes.valid.growth$Week.24.passenger)/2 - (df.routes.valid.growth$Week.1.passenger+df.routes.valid.growth$Week.2.passenger)/2)/(df.routes.valid.growth$Week.1.passenger+df.routes.valid.growth$Week.2.passenger)/2*100
df.top.growth <- top_n(df.routes.valid.growth,10,growth) %>% select(route_long_name,trip_id, growth)
df.worst.growth <- top_n(df.routes.valid.growth,-10,growth) %>% select(route_long_name,trip_id, growth)
write.csv(df.top.growth,'data/qlickview/top_growth.csv')
write.csv(df.worst.growth,'data/qlickview/worst_growth.csv')

Overall Net profit (Turnover – Costs) per kilometer

df.gato.profit <- df.routes %>% select(trip_id,route_long_name,contains("profit")) %>% gather(weeka,profit,-trip_id,-route_long_name)
df.gato.profit %>% group_by(weeka)%>% summarise(total_profit = sum(profit))

write.csv(df.gato.profit,'data/qlickview/profits.csv')

top/worst 10 profitable lines

df.top.profit.trip <- top_n(df.gato.profit %>% group_by(trip_id)%>% summarise(average.profit = mean(profit)),10,average.profit)
df.worst.profit.trip <- top_n(df.gato.profit %>% group_by(trip_id)%>% summarise(average.profit = mean(profit)),-10,average.profit)

Year to Date overall passenger volume vs previous Years.

df.gato.passenger <- df.routes %>% select(trip_id,route_long_name,contains("passenger")) %>% gather(weeka,passenger,-trip_id,-route_long_name)
df.groupy.passenger <- df.gato.passenger %>% group_by(weeka)%>% summarise(total_passenger = sum(passenger))
df.groupy.passenger$year <- 2018
df.gato.passenger.previous.year <- create_traffic()%>% select(trip_id,route_long_name,contains("passenger")) %>% gather(weeka,passenger,-trip_id,-route_long_name)
df.groupy.passenger.previous.year <- df.gato.passenger %>% group_by(weeka)%>% summarise(total_passenger = sum(passenger))
df.groupy.passenger.previous.year$year <- 2017
df.groupy.passenger.ytd <- rbind(df.groupy.passenger,df.groupy.passenger.previous.year)
write.csv(df.groupy.passenger.ytd,'data/qlickview/passenger_ytd.csv')