This is a text file with chess tournament results where the information has some structure. The goal is to create an R Markdown file that generates a .CSV file (that could for example be imported into a SQL database) with the following information for all of the players:
Player’s Name, Player’s State, Total Number of Points, Player’s Pre-Rating, and Average Pre Chess Rating of Opponents
For the first player, the information would be: Gary Hua, ON, 6.0, 1794, 1605
1605 was calculated by using the pre-tournament opponents’ ratings of 1436, 1563, 1600, 1610, 1649, 1663, 1716, and dividing by the total number of games played.
ds<-readLines("7645617.txt")
ds <- ds[1:length(ds)%%3!=1]
oddlines<- ds[1:length(ds)%%2==1]
evenlines<- ds[1:length(ds)%%2!=1]
data<-paste(oddlines, evenlines)
writeLines(data,"newds.txt")
dataset<-read.table("newds.txt", sep = "|", header = TRUE)
library(readr)## Warning: package 'readr' was built under R version 4.2.3### Creating a new data set for structure
newdataset<-data.frame(
name=dataset$Player.Name,
State=dataset$Num,
TotalPoints=dataset$Total,
Prerating=dataset$USCF.ID...Rtg..Pre..Post.,
Oponent1=dataset$Round,
Oponent2=dataset$Round.1,
Oponent3=dataset$Round.2,
Oponent4=dataset$Round.3,
Oponent5=dataset$Round.4,
Oponent6=dataset$Round.5,
Oponent7=dataset$Round.6
)
library(tidyr)## Warning: package 'tidyr' was built under R version 4.2.3newdataset <-separate(newdataset, Prerating, c("notu","Prerating"), sep="R:")
library(dplyr)## Warning: package 'dplyr' was built under R version 4.2.3##
## Attaching package: 'dplyr'## The following objects are masked from 'package:stats':
##
## filter, lag## The following objects are masked from 'package:base':
##
## intersect, setdiff, setequal, unionnewdataset <-select(newdataset, -notu)
newdataset <-separate(newdataset, Prerating, c("Prerating", "notu2"), sep ="->" )
newdataset <-select(newdataset, -notu2)
newdataset <-separate(newdataset, Prerating, c("Prerating", "notu3"), sep ="P" )## Warning: Expected 2 pieces. Missing pieces filled with `NA` in 54 rows [1, 2, 3, 4, 5,
## 6, 7, 9, 10, 11, 12, 13, 14, 16, 17, 18, 19, 20, 22, 23, ...].newdataset <-select(newdataset, -notu3)
newdataset$Prerating<-parse_number(newdataset$Prerating)
newdataset <-separate(newdataset, Oponent1, c("op1", "Oponent1"), sep = " +")
newdataset <-select(newdataset, -op1)
newdataset <-separate(newdataset, Oponent2, c("op2", "Oponent2"), sep = " +")
newdataset <-select(newdataset, -op2)
newdataset <-separate(newdataset, Oponent3, c("op3", "Oponent3"), sep = " +")
newdataset <-select(newdataset, -op3)
newdataset <-separate(newdataset, Oponent4, c("op4", "Oponent4"), sep = " +")
newdataset <-select(newdataset, -op4)
newdataset <-separate(newdataset, Oponent5, c("op5", "Oponent5"), sep = " +")
newdataset <-select(newdataset, -op5)
newdataset <-separate(newdataset, Oponent6, c("op6", "Oponent6"), sep = " +")
newdataset <-select(newdataset, -op6)
newdataset <-separate(newdataset, Oponent7, c("op7", "Oponent7"), sep = " +")
newdataset <-select(newdataset, -op7)
library(readr)
### Creating a function for preratings
newdataset$Averating <-NA
for (i in 1:nrow(newdataset)) {
Oponent1Rating<-newdataset[parse_number(newdataset[i, "Oponent1"]), "Prerating"]
Oponent2Rating<-newdataset[parse_number(newdataset[i, "Oponent2"]), "Prerating"]
Oponent3Rating<-newdataset[parse_number(newdataset[i, "Oponent3"]), "Prerating"]
Oponent4Rating<-newdataset[parse_number(newdataset[i, "Oponent4"]), "Prerating"]
Oponent5Rating<-newdataset[parse_number(newdataset[i, "Oponent5"]), "Prerating"]
Oponent6Rating<-newdataset[parse_number(newdataset[i, "Oponent6"]), "Prerating"]
Oponent7Rating<-newdataset[parse_number(newdataset[i, "Oponent7"]), "Prerating"]
newdataset[i, "Averating" ]<- mean(c(Oponent1Rating,Oponent2Rating,Oponent3Rating,Oponent4Rating, Oponent5Rating,Oponent6Rating, Oponent7Rating), na.rm = TRUE)
}
newdataset <- select(newdataset, -starts_with("Oponent"))
newdataset## name State TotalPoints Prerating Averating
## 1 GARY HUA ON 6.0 1794 1605.286
## 2 DAKSHESH DARURI MI 6.0 1553 1469.286
## 3 ADITYA BAJAJ MI 6.0 1384 1563.571
## 4 PATRICK H SCHILLING MI 5.5 1716 1573.571
## 5 HANSHI ZUO MI 5.5 1655 1500.857
## 6 HANSEN SONG OH 5.0 1686 1518.714
## 7 GARY DEE SWATHELL MI 5.0 1649 1372.143
## 8 EZEKIEL HOUGHTON MI 5.0 1641 1468.429
## 9 STEFANO LEE ON 5.0 1411 1523.143
## 10 ANVIT RAO MI 5.0 1365 1554.143
## 11 CAMERON WILLIAM MC LEMAN MI 4.5 1712 1467.571
## 12 KENNETH J TACK MI 4.5 1663 1506.167
## 13 TORRANCE HENRY JR MI 4.5 1666 1497.857
## 14 BRADLEY SHAW MI 4.5 1610 1515.000
## 15 ZACHARY JAMES HOUGHTON MI 4.5 1220 1483.857
## 16 MIKE NIKITIN MI 4.0 1604 1385.800
## 17 RONALD GRZEGORCZYK MI 4.0 1629 1498.571
## 18 DAVID SUNDEEN MI 4.0 1600 1480.000
## 19 DIPANKAR ROY MI 4.0 1564 1426.286
## 20 JASON ZHENG MI 4.0 1595 1410.857
## 21 DINH DANG BUI ON 4.0 1563 1470.429
## 22 EUGENE L MCCLURE MI 4.0 1555 1300.333
## 23 ALAN BUI ON 4.0 1363 1213.857
## 24 MICHAEL R ALDRICH MI 4.0 1229 1357.000
## 25 LOREN SCHWIEBERT MI 3.5 1745 1363.286
## 26 MAX ZHU ON 3.5 1579 1506.857
## 27 GAURAV GIDWANI MI 3.5 1552 1221.667
## 28 SOFIA ADINA STANESCU-BELLU MI 3.5 1507 1522.143
## 29 CHIEDOZIE OKORIE MI 3.5 1602 1313.500
## 30 GEORGE AVERY JONES ON 3.5 1522 1144.143
## 31 RISHI SHETTY MI 3.5 1494 1259.857
## 32 JOSHUA PHILIP MATHEWS ON 3.5 1441 1378.714
## 33 JADE GE MI 3.5 1449 1276.857
## 34 MICHAEL JEFFERY THOMAS MI 3.5 1399 1375.286
## 35 JOSHUA DAVID LEE MI 3.5 1438 1149.714
## 36 SIDDHARTH JHA MI 3.5 1355 1388.167
## 37 AMIYATOSH PWNANANDAM MI 3.5 980 1384.800
## 38 BRIAN LIU MI 3.0 1423 1539.167
## 39 JOEL R HENDON MI 3.0 1436 1429.571
## 40 FOREST ZHANG MI 3.0 1348 1390.571
## 41 KYLE WILLIAM MURPHY MI 3.0 1403 1248.500
## 42 JARED GE MI 3.0 1332 1149.857
## 43 ROBERT GLEN VASEY MI 3.0 1283 1106.571
## 44 JUSTIN D SCHILLING MI 3.0 1199 1327.000
## 45 DEREK YAN MI 3.0 1242 1152.000
## 46 JACOB ALEXANDER LAVALLEY MI 3.0 377 1357.714
## 47 ERIC WRIGHT MI 2.5 1362 1392.000
## 48 DANIEL KHAIN MI 2.5 1382 1355.800
## 49 MICHAEL J MARTIN MI 2.5 1291 1285.800
## 50 SHIVAM JHA MI 2.5 1056 1296.000
## 51 TEJAS AYYAGARI MI 2.5 1011 1356.143
## 52 ETHAN GUO MI 2.5 935 1494.571
## 53 JOSE C YBARRA MI 2.0 1393 1345.333
## 54 LARRY HODGE MI 2.0 1270 1206.167
## 55 ALEX KONG MI 2.0 1186 1406.000
## 56 MARISA RICCI MI 2.0 1153 1414.400
## 57 MICHAEL LU MI 2.0 1092 1363.000
## 58 VIRAJ MOHILE MI 2.0 917 1391.000
## 59 SEAN M MC CORMICK MI 2.0 853 1319.000
## 60 JULIA SHEN MI 1.5 967 1330.200
## 61 JEZZEL FARKAS ON 1.5 955 1327.286
## 62 ASHWIN BALAJI MI 1.0 1530 1186.000
## 63 THOMAS JOSEPH HOSMER MI 1.0 1175 1350.200
## 64 BEN LI MI 1.0 1163 1263.000### Final data setwrite.csv(newdataset, "tabletournament.csv")