Packages dplyr, stringr, data.table, and kableExtra are necessary to run the following code. Running will create a solution file entitled “ScoreSubset.csv” in the working directory.
library(dplyr)
library(stringr)
#load data text file with simple delimiter.
player.data<-read.delim('https://raw.githubusercontent.com/sigmasigmaiota/project1/master/tournamentinfo.txt',skip=1,sep="|",header=TRUE)
#remove dashline and create a duplicate dataframe.
avoid<-toString(player.data[2,1])
players<-player.data %>% filter(Pair != avoid)
titles_extrarow<-player.data %>% filter(Pair != avoid)
#change column names of one dataframe.
names(players)<-lapply(players[1,],as.character)
#remove extra row..
titles<-titles_extrarow[-1,]
#delete alternate rows of each dataframe; odds in one, evens in the other.
toDelete <- seq(1, nrow(players), 2)
players2_extrarow<-players[toDelete,]
#remove extra row.
players2<-players2_extrarow[-1,]
toDelete2 <- seq(1, nrow(players), 2)
titles2<-titles[toDelete,]
#remove unnecessary columns.
titles2['X']<-NULL
players2[11]<-NULL
#merge altered data frames, check alignment.
master<-as.data.frame(cbind(titles2[complete.cases(titles2),],players2[complete.cases(players2),]))
colnames(master)<-str_trim(colnames(master))
colnames(master)[12]<-"largestring"
#extract USCF.ID and prerating.
master$USCF.ID<-str_extract(master$largestring,"[0-9]{8}")
master$PreRating<-as.numeric(as.character(str_trim(substr(master$largestring,15,19))))
#extract OpponentIDs.
master$OppID<-as.numeric(as.character(master$Pair))
#create reference table for opponent preratings by ID.
refscore<-master[c("OppID","PreRating")]
master$Opponent1<-as.numeric(as.character(str_trim(str_extract(master$Round," [0-9]{1,2}"))))
master$Opponent2<-as.numeric(as.character(str_trim(str_extract(master$Round.1," [0-9]{1,2}"))))
master$Opponent3<-as.numeric(as.character(str_trim(str_extract(master$Round.2," [0-9]{1,2}"))))
master$Opponent4<-as.numeric(as.character(str_trim(str_extract(master$Round.3," [0-9]{1,2}"))))
master$Opponent5<-as.numeric(as.character(str_trim(str_extract(master$Round.4," [0-9]{1,2}"))))
master$Opponent6<-as.numeric(as.character(str_trim(str_extract(master$Round.5," [0-9]{1,2}"))))
master$Opponent7<-as.numeric(as.character(str_trim(str_extract(master$Round.6," [0-9]{1,2}"))))
#merge repeatedly for each opponent prerating.
colnames(refscore)[1]<-"Opponent1"
colnames(refscore)[2]<-"OppScore1"
master1<-merge(master,refscore,by="Opponent1")
colnames(refscore)[1]<-"Opponent2"
colnames(refscore)[2]<-"OppScore2"
master1<-merge(master1,refscore,by="Opponent2")
colnames(refscore)[1]<-"Opponent3"
colnames(refscore)[2]<-"OppScore3"
master1<-merge(master1,refscore,by="Opponent3")
colnames(refscore)[1]<-"Opponent4"
colnames(refscore)[2]<-"OppScore4"
master1<-merge(master1,refscore,by="Opponent4")
colnames(refscore)[1]<-"Opponent5"
colnames(refscore)[2]<-"OppScore5"
master1<-merge(master1,refscore,by="Opponent5")
colnames(refscore)[1]<-"Opponent6"
colnames(refscore)[2]<-"OppScore6"
master1<-merge(master1,refscore,by="Opponent6")
colnames(refscore)[1]<-"Opponent7"
colnames(refscore)[2]<-"OppScore7"
master1<-merge(master1,refscore,by="Opponent7")
colnames(master1)[18]<-"State"
#calculate means.
master1$AveOppPreRating<-round(rowMeans(master1[,c("OppScore1","OppScore2","OppScore3","OppScore4","OppScore5","OppScore6","OppScore7")],na.rm=TRUE),digits=0)
sort.master1<-master1[order(master1$OppID),]
library(data.table)
solution<-data.table(sort.master1[c("Player.Name","State","Total","PreRating","AveOppPreRating")])
#alter column name.
colnames(solution)[1]<-"PlayerName"
library(kableExtra)
anstable<-knitr::kable(solution,"html",align='lcccc')%>%
kable_styling("striped",
full_width = F)
anstable| PlayerName | State | Total | PreRating | AveOppPreRating |
|---|---|---|---|---|
| GARY HUA | ON | 6.0 | 1794 | 1605 |
| DAKSHESH DARURI | MI | 6.0 | 1553 | 1469 |
| ADITYA BAJAJ | MI | 6.0 | 1384 | 1564 |
| PATRICK H SCHILLING | MI | 5.5 | 1716 | 1574 |
| HANSHI ZUO | MI | 5.5 | 1655 | 1501 |
| HANSEN SONG | OH | 5.0 | 1686 | 1519 |
| GARY DEE SWATHELL | MI | 5.0 | 1649 | 1372 |
| EZEKIEL HOUGHTON | MI | 5.0 | 1641 | 1468 |
| STEFANO LEE | ON | 5.0 | 1411 | 1523 |
| ANVIT RAO | MI | 5.0 | 1365 | 1554 |
| CAMERON WILLIAM MC LEMAN | MI | 4.5 | 1712 | 1468 |
| TORRANCE HENRY JR | MI | 4.5 | 1666 | 1498 |
| BRADLEY SHAW | MI | 4.5 | 1610 | 1515 |
| ZACHARY JAMES HOUGHTON | MI | 4.5 | 1220 | 1484 |
| RONALD GRZEGORCZYK | MI | 4.0 | 1629 | 1499 |
| DAVID SUNDEEN | MI | 4.0 | 1600 | 1480 |
| DIPANKAR ROY | MI | 4.0 | 1564 | 1426 |
| JASON ZHENG | MI | 4.0 | 1595 | 1411 |
| DINH DANG BUI | ON | 4.0 | 1563 | 1470 |
| ALAN BUI | ON | 4.0 | 1363 | 1214 |
| MICHAEL R ALDRICH | MI | 4.0 | 1229 | 1357 |
| LOREN SCHWIEBERT | MI | 3.5 | 1745 | 1363 |
| MAX ZHU | ON | 3.5 | 1579 | 1507 |
| SOFIA ADINA STANESCU-BELLU | MI | 3.5 | 1507 | 1522 |
| GEORGE AVERY JONES | ON | 3.5 | 1522 | 1144 |
| RISHI SHETTY | MI | 3.5 | 1494 | 1260 |
| JOSHUA PHILIP MATHEWS | ON | 3.5 | 1441 | 1379 |
| JADE GE | MI | 3.5 | 1449 | 1277 |
| MICHAEL JEFFERY THOMAS | MI | 3.5 | 1399 | 1375 |
| JOSHUA DAVID LEE | MI | 3.5 | 1438 | 1150 |
| JOEL R HENDON | MI | 3.0 | 1436 | 1430 |
| FOREST ZHANG | MI | 3.0 | 1348 | 1391 |
| JARED GE | MI | 3.0 | 1332 | 1150 |
| ROBERT GLEN VASEY | MI | 3.0 | 1283 | 1107 |
| DEREK YAN | MI | 3.0 | 1242 | 1152 |
| JACOB ALEXANDER LAVALLEY | MI | 3.0 | 377 | 1358 |
| ERIC WRIGHT | MI | 2.5 | 1362 | 1392 |
| TEJAS AYYAGARI | MI | 2.5 | 1011 | 1356 |
| ETHAN GUO | MI | 2.5 | 935 | 1495 |
| JEZZEL FARKAS | ON | 1.5 | 955 | 1327 |
| BEN LI | MI | 1.0 | 1163 | 1263 |
#write .csv
write.csv(solution,file="ScoreSubset.csv")