Sporting and Data Analytics: An Exploration into Formula One

library(RCurl)

library(XML)
library(tidyr)

library(dplyr)

library(stringr)
library(knitr)
library(ggplot2)
library(jsonlite)

# The process will be very slow while knitting the R Markdown file. So I commended the following code and save the data but the evaluation will be silenced.

finalTable <- data.frame()
htmlTable <- data.frame()
for (i in 1958:2017){
  html_url <- paste0("https://www.formula1.com/en/results.html/",i,"/team.html")
  fetch_html <- getURL(html_url)
  htmlContent <- htmlParse(fetch_html,encoding = "UTF-8")
  htmlTable <- readHTMLTable(htmlContent, stringsAsFactors = FALSE)
  Sys.sleep(0.5)
  htmlTable <- htmlTable[[1]]
  htmlTable$year <- rep(i,nrow(htmlTable))
  finalTable <- rbind(finalTable,htmlTable)
}

colnames(finalTable) <- c("Empty1","Pos","Team","Pts","Empty2","year")
finalTable$Empty1 <-NULL
finalTable$Empty2 <-NULL

finalTable$Pos <- as.numeric(finalTable$Pos)

write.csv(finalTable,"F1_team.csv")

# F1_team.csv were uploaded to the Github repo and then downloaded.

finalTable <- read.csv(file="https://raw.githubusercontent.com/YunMai-SPS/DA607/master/DA607_final%20project/F1_team.csv", header=TRUE, sep=",", stringsAsFactors = F)

kable(head(finalTable,n=10))

kable(tail(finalTable, n=5))

tally <- as.data.frame(table(finalTable$Team))
team1 <- tally[which(tally$Freq == 1),]
team2 <- tally[which(tally$Freq == 2),]
team3 <- tally[which(tally$Freq == 3),]
team4 <- tally[which(tally$Freq == 4),]
team5 <- tally[which(tally$Freq == 5),]
team6 <- tally[which(tally$Freq == 6),]
team7 <- tally[which(tally$Freq == 7),]
team8 <- tally[which(tally$Freq == 8),]
team9 <- tally[which(tally$Freq == 9),]
team10 <- tally[which(tally$Freq == 10),]
teamLong <- as.data.frame(tally[which(tally$Freq != 1 & tally$Freq != 2 & tally$Freq != 3 & tally$Freq != 4 & tally$Freq != 5 & tally$Freq != 6 & tally$Freq != 7 & tally$Freq != 8 & tally$Freq != 9), ])

longlastTeam.name <- as.character(teamLong$Var1)

longlastTeam <- finalTable[which(finalTable$Team == longlastTeam.name[1] | finalTable$Team == longlastTeam.name[2] | finalTable$Team == longlastTeam.name[3] | finalTable$Team == longlastTeam.name[4] | finalTable$Team == longlastTeam.name[5] | finalTable$Team == longlastTeam.name[6] | finalTable$Team == longlastTeam.name[7] | finalTable$Team == longlastTeam.name[8] | finalTable$Team == longlastTeam.name[9]),]

longlastTeam <- group_by(longlastTeam,Team)
    
ggplot(data = longlastTeam, aes(x=year, y=Pos, color = Team, group = Team)) +
    geom_point() +
    geom_line() +
    labs(y="Position")+
    facet_grid(Team ~ .)

# The process will be held while knitting the R Markdown file. So I commended the following code and save the data but the evaluation will be silenced.

driverTable <- data.frame()
htmlTable <- data.frame()
for (i in 1950:2017){
  html_url <- paste0("https://www.formula1.com/en/results.html/",i,"/drivers/94/great-britain/race-result.html")
  fetch_html <- getURL(html_url)
  htmlContent <- htmlParse(fetch_html,encoding = "UTF-8")
  htmlTable <- readHTMLTable(htmlContent, stringsAsFactors = FALSE)
  Sys.sleep(0.5)
  htmlTable <- htmlTable[[1]]
  htmlTable$year <- rep(i,nrow(htmlTable))
  driverTable <- rbind(driverTable,htmlTable)
}
  
colnames(driverTable) <- c("Empty1","Pos","Driver","Nationality","Car","Pts","Empty2","year")

driverTable$Empty1 <-NULL
driverTable$Empty2 <-NULL

driverTable$Pos <- as.numeric(driverTable$Pos)

write.csv(driverTable,"F1_driver.csv")

# F1_driver.csv were uploaded to the Github repo and then downloaded.

driverTable <- read.csv(file="https://raw.githubusercontent.com/YunMai-SPS/DA607/master/DA607_final%20project/F1_driver.csv", header=TRUE, sep=",", stringsAsFactors = F)

driverTable50 <- driverTable[which(driverTable$year < 1960),]
driverTable60 <- driverTable[which(driverTable$year >= 1960 & driverTable$year < 1970),]
driverTable70 <- driverTable[which(driverTable$year >= 1970 & driverTable$year < 1980),]
driverTable80 <- driverTable[which(driverTable$year >= 1980 & driverTable$year < 1990),]
driverTable90 <- driverTable[which(driverTable$year >= 1990 & driverTable$year < 2000),]
driverTable00 <- driverTable[which(driverTable$year >= 2000 & driverTable$year < 2010),]
driverTable10 <- driverTable[which(driverTable$year >= 2010 & driverTable$year < 2020),]

driverTable50$Driver<- str_replace_all(driverTable50$Driver,"[:space:]?\n[:space:]+", " ")
driverTable50$FirstName <- str_extract(driverTable50$Driver, "[:alpha:]+")
driverTable50$LastName <- str_extract(driverTable50$Driver, "[A-Z]{3}\\b")
driverTable50 <- transform(driverTable50, Name = paste(FirstName,LastName, sep = ""))

drivertally50 <- as.data.frame(table(driverTable50$Name))
colnames(drivertally50) <- c("Name","Freq")
d50.1 <- drivertally50[which(drivertally50$Freq == max(drivertally50$Freq)),]
d50.2 <- drivertally50[which(drivertally50$Freq == (max(drivertally50$Freq)-1)),]
d50.3 <- drivertally50[which(drivertally50$Freq == (max(drivertally50$Freq)-2)),]
d50.top3.tally <- rbind(d50.1,d50.2,d50.3)

sort(d50.top3.tally$Freq, decreasing = T)  

d50.top3.name <- as.character(d50.top3.tally$Name)

length(d50.top3.name)

driverTable50$Name <-as.character(driverTable50$Name)

d50.top3 <- driverTable50[which(driverTable50$Name == d50.top3.name[1] | driverTable50$Name == d50.top3.name[2] | driverTable50$Name == d50.top3.name[3] | driverTable50$Name == d50.top3.name[4] | driverTable50$Name == d50.top3.name[5] | driverTable50$Name == d50.top3.name[6] | driverTable50$Name == d50.top3.name[7] | driverTable50$Name == d50.top3.name[8]),]

d50.top3 <- group_by(d50.top3,Name)
 
ggplot(data = d50.top3, aes(x=year, y=Pos, color = Name, group = Name)) +
    geom_point() +
    geom_line() +
    labs(y="Position")+
    facet_grid(Name ~ .)

driverTable10$Driver <-str_replace_all(driverTable10$Driver,"[:space:]?\n[:space:]+", " ")
driverTable10$FirstName <- str_extract(driverTable10$Driver, "[:alpha:]+")
driverTable10$LastName <- str_extract(driverTable10$Driver, "[A-Z]{3}\\b")
driverTable10 <- transform(driverTable10, Name = paste(FirstName,LastName, sep = ""))

drivertally10 <- as.data.frame(table(driverTable10$Name))
colnames(drivertally10) <- c("Name","Freq")
d10.1 <- drivertally10[which(drivertally10$Freq == max(drivertally10$Freq)),]
d10.2 <- drivertally10[which(drivertally10$Freq == (max(drivertally10$Freq)-1)),]
d10.3 <- drivertally10[which(drivertally10$Freq == (max(drivertally10$Freq)-2)),]
d10.top3.tally <- rbind(d10.1,d10.2,d10.3)

sort(d10.top3.tally$Freq, decreasing = T)  

d10.top3.name <- as.character(d10.top3.tally$Name)

length(d10.top3.name)

driverTable10$Name <-as.character(driverTable10$Name)

d10.top3 <- driverTable10[which(driverTable10$Name == d10.top3.name[1] | driverTable10$Name == d10.top3.name[2] | driverTable10$Name == d10.top3.name[3] | driverTable10$Name == d10.top3.name[4] | driverTable10$Name == d50.top3.name[5] | driverTable10$Name == d10.top3.name[6] | driverTable10$Name == d10.top3.name[7] | driverTable10$Name == d10.top3.name[8]),]

d10.top3 <- group_by(d10.top3,Name)
 
ggplot(data = d10.top3, aes(x=year, y=Pos, color = Name, group = Name)) +
    geom_point() +
    geom_line() +
    labs(y="Position")+
    facet_grid(Name ~ .)

# Grand Prix of Austrlia, July 2016, First Practice: AuJy.p1

AuJy.p1 <- read.table("https://raw.githubusercontent.com/YunMai-SPS/DA607/master/DA607_final%20project/practice_lap_times_55_raw%20-%20Copy.txt",fill = TRUE)
colnames(AuJy.p1) <- c("lapNo","pit","time","No","Name","V6","V7")
AuJy.p1 <- AuJy.p1[-1, ]
AuJy.p1.fix <- AuJy.p1[AuJy.p1$pit == "P",]
AuJy.p1.fix$pit <- NULL
AuJy.p1.fix$V6 <- paste(AuJy.p1.fix$V6,AuJy.p1.fix$V7)
AuJy.p1.fix$V7 <- NULL

colnames(AuJy.p1.fix) <- c("lapNo","pit","time","No","Name")

AuJy.p1.fix$pit <- str_replace(AuJy.p1.fix$pit, "11","1")

AuJy.p1.rest <- AuJy.p1[AuJy.p1$pit != "P",]
AuJy.p1.rest$V7 <- NULL
AuJy.p1.rest$Name <- paste(AuJy.p1.rest$Name,AuJy.p1.rest$V6)
AuJy.p1.rest$V6 <- NULL
AuJy.p1 <- rbind(AuJy.p1.fix,AuJy.p1.rest)

AuJy.p1$lapNo <- as.character(AuJy.p1$lapNo)
AuJy.p1$lapNo <- str_extract(AuJy.p1$lapNo,"\\d+")
AuJy.p1$lapNo <- as.numeric(AuJy.p1$lapNo)

AuJy.p1$No <- as.numeric(AuJy.p1$No)
AuJy.p1$time <- as.character(AuJy.p1$time)
AuJy.p1 <-arrange(AuJy.p1,No,lapNo)

# find out the driver who first recorded a laptime during the session
start.time <- AuJy.p1[which(AuJy.p1$lapNo == 1),]
start.time$hour <- str_extract(start.time$time,"\\d+")
start.time$hour <- as.numeric(start.time$hour)

start.time$minute <- str_extract(start.time$time,":\\d+:")
start.time$minute <- str_replace_all(start.time$minute,":","")
start.time$minute <- as.numeric(start.time$minute)

start.time$second <- as.numeric(str_sub(start.time$time, -2,-1))

earliest.start <- start.time[which(start.time$hour == min(start.time$hour)),]

if (nrow(earliest.start) == 1){
  basetime <- earliest.start$hour * 60 * 60 + earliest.start$minute * 60 + earliest.start$second
}else
{
  earliest.start <- earliest.start[which(earliest.start$minute == min(earliest.start$minute)),]
}

if (nrow(earliest.start) == 1){
  basetime <- earliest.start$hour * 60 * 60 + earliest.start$minute * 60 + earliest.start$second
}else
{
  earliest.start <- earliest.start[which(earliest.start$second == min(earliest.start$second)),]
  basetime <- earliest.start$hour * 60 * 60 + earliest.start$minute * 60 + earliest.start$second
}

# set basetime as the  time recorded by the driver who first recorded the laptime. It is converted to seconds.

# First laptime in AuJy.p1 is special as it is not laptime but the time of day the driver completed their first lap of the session. So calculate the first 'laptime' and the other laptime separately.

#calculate the first 'laptime'
#convert time to secondsand annotated as  "ctime". Set basetime as 0.
start.time$ctime <- start.time$hour*60*60+ start.time$minute*60+ start.time$second
start.time$ctime <- start.time$ctime-basetime

#remove first 'laptime' from the dataset and calculte the other laptime.
intermediate <- AuJy.p1[which(AuJy.p1$lapNo != 1),]

#convert time to seconds and annotated as "ctime"
intermediate$minute <- as.numeric(str_extract(intermediate$time,"\\d+"))

intermediate$second <- str_extract(intermediate$time,":\\d+.")
intermediate$second <- str_extract(intermediate$second,"\\d+")
intermediate$second <- as.numeric(intermediate$second)

intermediate$milisecond <- str_extract(intermediate$time, "\\.\\d+")
intermediate$milisecond <- str_extract(intermediate$milisecond,"\\d+")
intermediate$milisecond <- as.numeric(intermediate$milisecond) 
  
intermediate$ctime <- intermediate$minute * 60 + intermediate$second +(intermediate$milisecond)/1000

#attach ctime to the original dataset by combining start.time and intermediate.
start.time$hour <- NULL
start.time$minute <- NULL
start.time$second <- NULL
  
intermediate$minute <- NULL
intermediate$second <- NULL
intermediate$milisecond <- NULL

AuJy.p1 <- rbind(start.time,intermediate)

AuJy.p1 <- arrange(AuJy.p1,No,lapNo)

#calculate the cumulated time
AuJy.p1 <- AuJy.p1 %>% 
  group_by(No) %>% 
  mutate(cumtime = cumsum(ctime))

kable(head(AuJy.p1,n=5))

green <- do.call(rbind, by(AuJy.p1, AuJy.p1$No, function(x) x[which.min(x$ctime), ] ))

ggplot(data = AuJy.p1, aes(x=cumtime, y=Name, color = Name, group = Name)) +
    geom_point() +
    geom_line() +
    geom_point(data=AuJy.p1[which(AuJy.p1$pit == 1), ], aes(x=cumtime, y=Name),color="black",pch = 1,size = 1.7)+
    geom_point(data=green, aes(x=cumtime, y=Name),color="green",pch = 1,size = 3)+
  labs( title="Practice 1")+
  theme(legend.position="none")

AuJy.qli <- read.table("https://raw.githubusercontent.com/YunMai-SPS/DA607/master/DA607_final%20project/qulifying_lap_time_58_raw.txt",fill=T,row.names=NULL)

colnames(AuJy.qli) <- c("lapNo","pit","time","No","FN","LN")
AuJy.qli$Name <- paste(AuJy.qli$FN,AuJy.qli$LN)
AuJy.qli$FN <- NULL
AuJy.qli$LN <- NULL

AuJy.qli$time <- as.character(AuJy.qli$time)

# find out the driver who first recorded a laptime during the session
start.time.qli <-AuJy.qli[which(AuJy.qli$lapNo == 1),]
start.time.qli$hour <- str_extract(start.time.qli$time,"\\d+")
start.time.qli$hour <- as.numeric(start.time.qli$hour)

start.time.qli$minute <- str_extract(start.time.qli$time,":\\d+:")
start.time.qli$minute <- str_replace_all(start.time.qli$minute,":","")
start.time.qli$minute <- as.numeric(start.time.qli$minute)

start.time.qli$second <- as.numeric(str_sub(start.time.qli$time, -2,-1))

earliest.start.qli <- start.time.qli[which(start.time.qli$hour == min(start.time.qli$hour)),]

if (nrow(earliest.start.qli) == 1){
  basetime.qli <- earliest.start.qli$hour * 60 * 60 + earliest.start.qli$minute * 60 + earliest.start$second.qli
}else
{
  earliest.start.qli <- earliest.start.qli[which(earliest.start.qli$minute == min(earliest.start.qli$minute)),]
}

if (nrow(earliest.start.qli) == 1){
  basetime/qli <- earliest.start.qli$hour * 60 * 60 + earliest.start.qli$minute * 60 + earliest.start.qli$second
}else
{
  earliest.start.qli <- earliest.start.qli[which(earliest.start.qli$second == min(earliest.start.qli$second)),]
  basetime.qli <- earliest.start.qli$hour * 60 * 60 + earliest.start.qli$minute * 60 + earliest.start.qli$second
}

# set basetime as the  time recorded by the driver who first recorded the laptime. It is converted to seconds.

# First laptime in AuJy.p1 is special as it is not laptime but the time of day the driver completed their first lap of the session. So calculate the first 'laptime' and the other laptime separately.

#calculate the first 'laptime'
#convert time to secondsand annotated as  "ctime". Set basetime as 0.
start.time.qli$ctime <- start.time.qli$hour*60*60+ start.time.qli$minute*60+ start.time.qli$second
start.time.qli$ctime <- start.time.qli$ctime-basetime.qli

#remove first 'laptime' from the dataset and calculte the other laptime.
intermediate.qli <- AuJy.qli[which(AuJy.qli$lapNo != 1),]

#convert time to seconds and annotated as "ctime"
intermediate.qli$minute <- as.numeric(str_extract(intermediate.qli$time,"\\d+"))

intermediate.qli$second <- str_extract(intermediate.qli$time,":\\d+.")
intermediate.qli$second <- str_extract(intermediate.qli$second,"\\d+")
intermediate.qli$second <- as.numeric(intermediate.qli$second)

intermediate.qli$milisecond <- str_extract(intermediate.qli$time, "\\.\\d+")
intermediate.qli$milisecond <- str_extract(intermediate.qli$milisecond,"\\d+")
intermediate.qli$milisecond <- as.numeric(intermediate.qli$milisecond) 
  
intermediate.qli$ctime <- intermediate.qli$minute * 60 + intermediate.qli$second +(intermediate.qli$milisecond)/1000

#attach ctime to the original dataset by combining start.time and intermediate.
start.time.qli$hour <- NULL
start.time.qli$minute <- NULL
start.time.qli$second <- NULL
  
intermediate.qli$minute <- NULL
intermediate.qli$second <- NULL
intermediate.qli$milisecond <- NULL

AuJy.qli <- rbind(start.time.qli,intermediate.qli)

AuJy.qli$lapNo <- as.numeric(AuJy.qli$lapNo)
AuJy.qli <- arrange(AuJy.qli,No,lapNo)

#calculate the cumulated time
AuJy.qli <- AuJy.qli %>% 
  group_by(No) %>% 
  mutate(cumtime = cumsum(ctime))

kable(head(AuJy.qli,n=5))

green <- do.call(rbind, by(AuJy.qli, AuJy.qli$No, function(x) x[which.min(x$ctime), ] ))

ggplot(data = AuJy.qli, aes(x=cumtime, y=Name, color = Name, group = Name)) +
    geom_point() +
    geom_line() +
    geom_point(data=AuJy.qli[which(AuJy.qli$pit == 1), ], aes(x=cumtime, y=Name),color="black",pch = 1,size = 1.7)+
    geom_point(data=green, aes(x=cumtime, y=Name),color="green",pch = 1,size = 3)+
    labs( title="Qulifying 1")+
    theme(legend.position="none")

json_url <- "http://ergast.com/api/f1/2016/9/qualifying.json"
jsonFile <- getURL(json_url)
jsonContent <- fromJSON(jsonFile)
json_table5 <- jsonContent$MRData

json_table6 <-json_table5$RaceTable
json_table7 <-json_table6$Races
json_table8 <-json_table7$QualifyingResults
json_table9 <-json_table8[[1]]
json_table10 <-json_table9[["Driver"]]
json_table11 <-json_table9[["Constructor"]]
json_table9$Driver <- NULL
json_table9$Constructor <- NULL
json_table<- cbind(json_table9,json_table10)
json_table <- cbind(json_table,json_table11)
json_table <- json_table[,c("number","position", "Q1", "Q2", "Q3", "driverId","permanentNumber", "code", "url", "givenName", "familyName", "dateOfBirth", "nationality", "constructorId", "url", "name", "nationality")]
json_table$permanentNumber <- NULL
kable(head(json_table,n=10))

#convert qualifying time Q1 time to seconds and annotated as "ctime"
json_table$Q1m <- as.numeric(str_extract(json_table$Q1,"\\d+"))

json_table$Q1s <- str_extract(json_table$Q1,":\\d+.")
json_table$Q1s <- as.numeric(str_extract(json_table$Q1s,"\\d+"))

json_table$Q1ms <- str_extract(json_table$Q1, "\\.\\d+")
json_table$Q1ms <- as.numeric(str_extract(json_table$Q1ms,"\\d+")) 
  
json_table$Q1ctime <- json_table$Q1m * 60 + json_table$Q1s +(json_table$Q1ms)/1000

#convert qualifying time Q2 time to seconds and annotated as "ctime"
json_table$Q2m <- as.numeric(str_extract(json_table$Q2,"\\d+"))

json_table$Q2s <- str_extract(json_table$Q2,":\\d+.")
json_table$Q2s <- as.numeric(str_extract(json_table$Q2s,"\\d+"))

json_table$Q2ms <- str_extract(json_table$Q2, "\\.\\d+")
json_table$Q2ms <- as.numeric(str_extract(json_table$Q2ms,"\\d+")) 
  
json_table$Q2ctime <- json_table$Q2m * 60 + json_table$Q2s +(json_table$Q2ms)/1000

#convert qualifying time Q3 time to seconds and annotated as "ctime"
json_table$Q3m <- as.numeric(str_extract(json_table$Q3,"\\d+"))

json_table$Q3s <- str_extract(json_table$Q3,":\\d+.")
json_table$Q3s <- as.numeric(str_extract(json_table$Q3s,"\\d+"))

json_table$Q3ms <- str_extract(json_table$Q3, "\\.\\d+")
json_table$Q3ms <- as.numeric(str_extract(json_table$Q3ms,"\\d+")) 
  
json_table$Q3ctime <- json_table$Q3m * 60 + json_table$Q3s +(json_table$Q3ms)/1000

hist(json_table$Q1ctime,breaks = 10,xlab = "Q1 qualifying time",main = "Q1 - Grand Prix of Austria July 3, 2016")

summary(json_table$Q1ctime)

q <-min(json_table$Q1ctime)*1.07
qualifying.failed <- json_table[which(json_table$Q1ctime > q),]
paste("There is/are",nrow(qualifying.failed),"racer who faild in 107% rule in 2016 Season Run 9 (Grand Prix of Austria - July 3, 2016).")

AuJy.race <- read.csv("https://raw.githubusercontent.com/YunMai-SPS/DA607/master/DA607_final%20project/history_chart_11_australiaJuly2016.csv")
AuJy.race <- AuJy.race [,1:4]
kable(head(AuJy.race,n=3))

#change class of variable from factor to character for further manipulation
AuJy.race$gap <- as.character(AuJy.race$gap)
AuJy.race$time <- as.character(AuJy.race$time)
AuJy.race$minute <-as.numeric(str_extract(AuJy.race$time,"\\d+"))
AuJy.race$second <-str_extract(AuJy.race$time,":\\d+")
AuJy.race$second <-as.numeric(str_extract(AuJy.race$second,"\\d+"))
AuJy.race$milisecond <-as.numeric(str_sub(AuJy.race$time,-3,-1))
AuJy.race$timeinsecond <- AuJy.race$minute*60 + AuJy.race$second + (AuJy.race$milisecond)/1000

AuJy.race <- AuJy.race %>% 
  group_by(No) %>% 
  mutate(cumtime=cumsum(timeinsecond))


# add the laptime of fastest racer in the lap to the laptime of racer who is one lap behind  
find.behind <- subset(AuJy.race, gap == "1 LAP")

find.lap <- levels(as.factor(find.behind$lapNo))
find.lap <- as.numeric(find.lap)
print("find.lap contain the lap No. in which there is racer who is more than 1 lap behind the fastest racer:")

print(find.lap)

find.leader<- AuJy.race[which(AuJy.race$lapNo == 25 & AuJy.race$gap == "0"),]
lap <- data.frame()
for(i in c(find.lap)){
  lap <- AuJy.race[which(AuJy.race$lapNo == i & AuJy.race$gap == "0"),]
  find.leader<- union(find.leader,lap)
}

leader.behind <- rbind(find.leader,find.behind)

leader.behind <- arrange(leader.behind,lapNo)

single.behind <- spread(leader.behind,gap,cumtime)
names(single.behind)[names(single.behind)=="0"]<- "zero"
names(single.behind)[names(single.behind)=="1 LAP"]<- "onelap"

require(zoo)

single.behind <- transform(single.behind, zero = na.locf(zero))

OneLap <- single.behind[which(is.na(single.behind$onelap) == F),]
OneLap$cumtime <- OneLap$zero + OneLap$onelap

#OneLap$timeinsecond <- OneLap$zero + OneLap$onelap

OneLap <- arrange(OneLap,No,lapNo)

# "1 LAP"" does not mean one more laptime of the fastest racer will be added to the gap. So the following thee sentences are not right and will not be run.
#OneLap <- OneLap %>% 
#  group_by(No) %>% 
#  mutate(cumtime=cumsum(timeinsecond))

OneLap$zero <- NULL
OneLap$onelap <- NULL

#OneLap$timeinsecond <- NULL

#OneLap$cumtime <- NULL

OneLap$gap <- "1 LAP"
OneLap <- OneLap[,c("lapNo","No","gap","time","minute","second","milisecond","timeinsecond","cumtime")]

#names(OneLap)[names(OneLap)=="cumtime"] <- "timeinsecond"
  
removeOneLap <- AuJy.race[which(AuJy.race$gap != "1 LAP"),]

AuJy.race <- union(removeOneLap,OneLap)

AuJy.race <- arrange(AuJy.race,lapNo,timeinsecond)

# rank the racers in each lap
sorted <- AuJy.race %>% 
  group_by(lapNo) %>% 
  mutate(rank=row_number(cumtime)) %>% 
  arrange(lapNo,rank)
sorted$No <- as.character(sorted$No)

# plot all the drivers
ggplot(data = sorted, aes(x=lapNo, y=rank, color = No, group=No)) +
    geom_point() +
    geom_line() +
    scale_y_reverse()

# plot the 5 start at the close position  
ggplot(data = subset(sorted,No == 44 | No == 7 | No == 3 | No == 6 | No == 33), aes(x=lapNo, y=rank, color = No, group=No)) +
    geom_point() +
    geom_line() +
    scale_x_continuous(breaks=seq(0,72, by = 4))+
    scale_y_continuous(breaks=seq(0,15, by = 1))+
    scale_y_reverse()

ggplot(data = subset(sorted,No == 44 | No == 6 ), aes(x=lapNo, y=rank, color = No, group=No)) +
    geom_point() +
    geom_line() +
    scale_x_continuous(breaks=seq(0,72, by = 4))+
    scale_y_continuous(breaks=seq(0,15, by = 1))+
    scale_y_reverse()

#calculate the gap between each car and the fastest car in each lap. There are gap records in the history chart from FIA but the gap time when the car it pit are not shown in the sheet. The missing number could be derived from the laptime.
a <- data.frame(sorted)
a <- mutate(a, calgap=0)
k <- 1
n <- 1
for(i in 1:71){
  for(j in 1:nrow(a[which(a$lapNo == i),])){
    if (i==1){
      a[j,11] <- a[j,9] - a[i,9]
      k <- 1+j
    }else{
      a[k,11] <- a[k,9] - a[n,9]
      k <- k+1
      n <- k-j
    }
  }
  n<-n+nrow(a[which(a$lapNo == i),])
} 

a$diff <- a$calgap*(-1)
a$No <- as.character(a$No)

ggplot(data = a, aes(x=lapNo, y=diff, color = No, group=No)) +
    geom_point() +
    geom_line() 

#plot the first 5 drivers at the start standing (No.27 retired at lap 67 according to the race chart above so he was not picked for this graph)
ggplot(data = subset(a, No == 44 | No == 22 | No == 7 | No == 6 | No == 3 | No==33), aes(x=lapNo, y=diff, color = No, group=No)) +
    geom_point() +
    geom_line()+
    scale_x_continuous(breaks=seq(0,72, by = 4))+
    labs(y="Gap")

b <- a[which(a$lapNo==71 & a$No==44),]
c <- a[which(a$lapNo==71 & a$No==6),]
d <- rbind(b,c)
kable(d)

#The 1st of the Championship in 2006

json_url <- "http://ergast.com/api/f1/2006/results/1.json"
jsonFile <- getURL(json_url)
jsonContent <- fromJSON(jsonFile)
json_table5 <- jsonContent$MRData

json_table6 <-json_table5$RaceTable
json_table7 <-json_table6$Races
json_table8 <-json_table7$Results
json_table9 <- data.frame(json_table8[[1]])[,c(1,2,4)]

for( i in 2:18){
  json_table9 <- rbind(json_table9,data.frame(json_table8[[i]])[,c(1,2,4)])
}

json_table10 <- json_table8[[1]][["Driver"]]

for( i in 2:18){
  if (i %in% c(2,4,5,10,11,12,15,16)){
     b <- json_table8[[i]][["Driver"]]
     b$permanentNumber <- NA 
     json_table10 <- rbind(json_table10,b)
  }else{
     c <- json_table8[[i]][["Driver"]]
     json_table10 <- rbind(json_table10,c)
  }
}

json_table11 <- json_table8[[1]][["Constructor"]]
for(i in 2:18){
 json_table11 <- rbind(json_table11,json_table8[[i]][["Constructor"]])
}

json_table12 <- data.frame(json_table8[[1]][["FastestLap"]])[,1:2]
json_table12 <- json_table12[,1:2]
for( i in 2:18){
  json_table12 <- rbind(json_table12,data.frame(json_table8[[i]][["FastestLap"]])[,1:2])
}

json_table13 <- json_table8[[1]][["FastestLap"]]
json_table14 <- json_table13[["AverageSpeed"]]

for( i in 2:18){
     c <- json_table8[[i]][["FastestLap"]]
     d <- c[["AverageSpeed"]]
     json_table14 <- rbind(json_table14,d)
}
json_table15 <- cbind(json_table12,json_table14)

e <- cbind(json_table9,json_table10)
json_table.results <-cbind(e,json_table15)
names(json_table.results)[names(json_table.results) == "driverId"]<-"name"
json_table.results$year<- "2006"
json_table.results <- json_table.results[c("year","number","position", "points" ,"name", "permanentNumber", "code", "url","givenName", "familyName", "dateOfBirth","nationality", "rank", "lap", "units", "speed")]
json_table.results1 <- json_table.results[,c("year","number","position", "points" ,"name", "rank", "lap", "units", "speed")]
kable(head(json_table.results,n=2))

#The 2nd of the Championship in 2006

json_url <- "http://ergast.com/api/f1/2006/results/2.json"
jsonFile <- getURL(json_url)
jsonContent <- fromJSON(jsonFile)
json_table5 <- jsonContent$MRData

json_table6 <-json_table5$RaceTable
json_table7 <-json_table6$Races
json_table8 <-json_table7$Results
json_table9 <- data.frame(json_table8[[1]])[,c(1,2,4)]

for( i in 2:18){
  json_table9 <- rbind(json_table9,data.frame(json_table8[[i]])[,c(1,2,4)])
}

json_table10 <- json_table8[[1]][["Driver"]]
json_table10 <- json_table10[["driverId"]]

for( i in 2:18){
     f <- json_table8[[i]][["Driver"]][["driverId"]]
     json_table10 <- rbind(json_table10,f)
}

json_table11 <- json_table8[[1]][["Constructor"]]
for(i in 2:18){
 json_table11 <- rbind(json_table11,json_table8[[i]][["Constructor"]])
}

json_table12 <- data.frame(json_table8[[1]][["FastestLap"]])[,1:2]
json_table12 <- json_table12[,1:2]
for( i in 2:18){
  json_table12 <- rbind(json_table12,data.frame(json_table8[[i]][["FastestLap"]])[,1:2])
}

json_table13 <- json_table8[[1]][["FastestLap"]]
json_table14 <- json_table13[["AverageSpeed"]]

for( i in 2:18){
     c <- json_table8[[i]][["FastestLap"]]
     d <- c[["AverageSpeed"]]
     json_table14 <- rbind(json_table14,d)
}

json_table.results2 <- cbind(json_table9,json_table10,json_table12,json_table14)

names(json_table.results2)[names(json_table.results2) == "json_table10"] <- "name" 
json_table.results2$year<- "2006"

json_table.results2 <- json_table.results2[c("year","number","position", "points" ,"name", "rank", "lap", "units", "speed")]

kable(head(json_table.results2,n=2))

#combine 2006 to 2016 data (9 years, 2011 data were discarded because of missing information from ergast API)

championship <- rbind(json_table.results1,json_table.results2,json_table.results3,json_table.results4,json_table.results5,json_table.results6,json_table.results7,json_table.results8,json_table.results9,json_table.results10,json_table.results11,json_table.results12,json_table.results13,json_table.results14,json_table.results15,json_table.results16,json_table.results19,json_table.results20,json_table.results21,json_table.results22)

championship$points <- as.numeric(championship$points)
championship$speed <- as.numeric(championship$speed)
championship$position <- as.numeric(championship$position)
points <- championship %>% 
  group_by(name) %>% 
  summarise(points=sum(points),maxspeed=max(speed),avg.speed=mean(speed))
first <- championship[which(championship$position == 1),]
second <- championship[which(championship$position == 2),]

position.1 <- first %>% 
  group_by(name) %>% 
  summarise(first = n())

position.2 <- second %>% 
  group_by(name) %>% 
  summarise(second=n())

a <- left_join(position.2,position.1,by="name")
a <- gather(a,position,counts,2:3)

ggplot(data=a, aes(x=name, y=counts, fill=position)) +
    geom_bar(stat="identity", position=position_dodge())+
    coord_flip() + 
     geom_hline(yintercept = 20, col="red")+
    theme(axis.text.y=element_text(angle=0, hjust=1))+
     labs(y="", x="Drivers Name",
              title="Who won the most Champoionship in the Last Decades")

ggplot(championship,aes(x=name,y=speed),group=name)+
  geom_boxplot()+
  labs(y=" Speed",title="speed")+
  theme(axis.text.x=element_text(angle=90,hjust=1,vjust=0.5))

famous <- a[which(a$counts > 20 ),]
kable(famous)