We are given the text file with chess tournament results. Let’s load the file in R as fixed format file. We will drop first 4 rows (no useful info in these rows). We will combine 3 consequent rows into 1 row (rows 5, 6, and 7 will become row 1 in our dataframe)
chess<-read.fwf(file="c://data/tournamentinfo.txt", skip=4, widths=list(c(-3,2,-2,33,-1,3,-6,2,-4,2,-4,2,-4,2,-4,2,-4,2,-4,2),c(-4,2,-17,4),c()), col.names=c("Pnum","PlayerName","TotalNumberofPoints","g1","g2","g3","g4","g5","g6","g7","PlayerState","PlayerPreRating"),strip.white=TRUE,n=195)Next step, we will create new DF containing only player number and its Elo score. We will rename columns to avoid confusion and errors
PElo<-chess[c("Pnum","PlayerPreRating")]
names(PElo)<-c("Pnum1","elo1")We merge two DF’s to add first’s competetor Elo score to main DF. We repeat the process 6 more time to capture all competotors’ Elo scores. Each iteration we will rename column for competotor’s Elo score in xwalk DF (PElo). The process would need automation, but the step was skipped due to the lack of time
chess<-merge(x=chess,y=PElo,by.x="g1",by.y="Pnum1",all.x=TRUE)
names(PElo)[2]<-c("elo2")
chess<-merge(x=chess,y=PElo,by.x="g2",by.y="Pnum1",all.x=TRUE)
names(PElo)[2]<-c("elo3")
chess<-merge(x=chess,y=PElo,by.x="g3",by.y="Pnum1",all.x=TRUE)
names(PElo)[2]<-c("elo4")
chess<-merge(x=chess,y=PElo,by.x="g4",by.y="Pnum1",all.x=TRUE)
names(PElo)[2]<-c("elo5")
chess<-merge(x=chess,y=PElo,by.x="g5",by.y="Pnum1",all.x=TRUE)
names(PElo)[2]<-c("elo6")
chess<-merge(x=chess,y=PElo,by.x="g6",by.y="Pnum1",all.x=TRUE)
names(PElo)[2]<-c("elo7")
chess<-merge(x=chess,y=PElo,by.x="g7",by.y="Pnum1",all.x=TRUE)Let’s sum competotors’ ELo. And also let’s count how many games were played. After we devide total competotors’ Elo by number of games/competitors, we will get an average
chess$TotElo<-rowSums(chess[,c("elo1", "elo2","elo3","elo4","elo5","elo6","elo7")], na.rm=TRUE)
chess$TotGam<-7-rowSums(is.na(chess[,c("elo1", "elo2","elo3","elo4","elo5","elo6","elo7")]))
chess$AvePreChessRatOpp<-round(chess$TotElo/chess$TotGam,0)Now we will create final output. We will display it for review and check player one (Gary Hua), if it matches control results. The last step will be creating csv file.
Output<-chess[c("PlayerName", "PlayerState", "TotalNumberofPoints", "PlayerPreRating", "AvePreChessRatOpp")]
Output## PlayerName PlayerState TotalNumberofPoints
## 1 PATRICK H SCHILLING MI 5.5
## 2 GARY DEE SWATHELL MI 5.0
## 3 KENNETH J TACK MI 4.5
## 4 GARY HUA ON 6.0
## 5 RONALD GRZEGORCZYK MI 4.0
## 6 DINH DANG BUI ON 4.0
## 7 DAKSHESH DARURI MI 6.0
## 8 DIPANKAR ROY MI 4.0
## 9 JASON ZHENG MI 4.0
## 10 DAVID SUNDEEN MI 4.0
## 11 MAX ZHU ON 3.5
## 12 ADITYA BAJAJ MI 6.0
## 13 JOSHUA PHILIP MATHEWS ON 3.5
## 14 RISHI SHETTY MI 3.5
## 15 BRIAN LIU MI 3.0
## 16 HANSHI ZUO MI 5.5
## 17 ANVIT RAO MI 5.0
## 18 EZEKIEL HOUGHTON MI 5.0
## 19 STEFANO LEE ON 5.0
## 20 HANSEN SONG OH 5.0
## 21 FOREST ZHANG MI 3.0
## 22 JACOB ALEXANDER LAVALLEY MI 3.0
## 23 JOEL R HENDON MI 3.0
## 24 ERIC WRIGHT MI 2.5
## 25 CAMERON WILLIAM MC LEMAN MI 4.5
## 26 SIDDHARTH JHA MI 3.5
## 27 SHIVAM JHA MI 2.5
## 28 BRADLEY SHAW MI 4.5
## 29 TORRANCE HENRY JR MI 4.5
## 30 TEJAS AYYAGARI MI 2.5
## 31 ETHAN GUO MI 2.5
## 32 DANIEL KHAIN MI 2.5
## 33 SOFIA ADINA STANESCU-BELLU MI 3.5
## 34 JEZZEL FARKAS ON 1.5
## 35 ZACHARY JAMES HOUGHTON MI 4.5
## 36 MICHAEL R ALDRICH MI 4.0
## 37 EUGENE L MCCLURE MI 4.0
## 38 MARISA RICCI MI 2.0
## 39 ALEX KONG MI 2.0
## 40 SEAN M MC CORMICK MI 2.0
## 41 VIRAJ MOHILE MI 2.0
## 42 ALAN BUI ON 4.0
## 43 LOREN SCHWIEBERT MI 3.5
## 44 JOSHUA DAVID LEE MI 3.5
## 45 GEORGE AVERY JONES ON 3.5
## 46 JADE GE MI 3.5
## 47 MICHAEL JEFFERY THOMAS MI 3.5
## 48 BEN LI MI 1.0
## 49 ROBERT GLEN VASEY MI 3.0
## 50 JARED GE MI 3.0
## 51 DEREK YAN MI 3.0
## 52 JUSTIN D SCHILLING MI 3.0
## 53 AMIYATOSH PWNANANDAM MI 3.5
## 54 LARRY HODGE MI 2.0
## 55 ASHWIN BALAJI MI 1.0
## 56 GAURAV GIDWANI MI 3.5
## 57 JOSE C YBARRA MI 2.0
## 58 CHIEDOZIE OKORIE MI 3.5
## 59 KYLE WILLIAM MURPHY MI 3.0
## 60 MIKE NIKITIN MI 4.0
## 61 JULIA SHEN MI 1.5
## 62 MICHAEL LU MI 2.0
## 63 THOMAS JOSEPH HOSMER MI 1.0
## 64 MICHAEL J MARTIN MI 2.5
## PlayerPreRating AvePreChessRatOpp
## 1 1716 1574
## 2 1649 1372
## 3 1663 1506
## 4 1794 1605
## 5 1629 1499
## 6 1563 1470
## 7 1553 1469
## 8 1564 1426
## 9 1595 1411
## 10 1600 1480
## 11 1579 1507
## 12 1384 1564
## 13 1441 1379
## 14 1494 1260
## 15 1423 1539
## 16 1655 1501
## 17 1365 1554
## 18 1641 1468
## 19 1411 1523
## 20 1686 1519
## 21 1348 1391
## 22 377 1358
## 23 1436 1430
## 24 1362 1392
## 25 1712 1468
## 26 1355 1388
## 27 1056 1296
## 28 1610 1515
## 29 1666 1498
## 30 1011 1356
## 31 935 1495
## 32 1382 1356
## 33 1507 1522
## 34 955 1327
## 35 1220 1484
## 36 1229 1357
## 37 1555 1300
## 38 1153 1414
## 39 1186 1406
## 40 853 1319
## 41 917 1391
## 42 1363 1214
## 43 1745 1363
## 44 1438 1150
## 45 1522 1144
## 46 1449 1277
## 47 1399 1375
## 48 1163 1263
## 49 1283 1107
## 50 1332 1150
## 51 1242 1152
## 52 1199 1327
## 53 980 1385
## 54 1270 1206
## 55 1530 1186
## 56 1552 1222
## 57 1393 1345
## 58 1602 1314
## 59 1403 1248
## 60 1604 1386
## 61 967 1330
## 62 1092 1363
## 63 1175 1350
## 64 1291 1286subset(Output, PlayerName=="GARY HUA" )## PlayerName PlayerState TotalNumberofPoints PlayerPreRating
## 4 GARY HUA ON 6 1794
## AvePreChessRatOpp
## 4 1605write.csv(file="c://data/Output.csv", x=Output)