For this project we were provided with a chess cross table file and were asking to imported the .txt chess cross table file to R and clean it then convert to a .csv file. I will upload the chess cross table file to my github account so that he can be easily view. There are different way for import .txt file to R such as read.delim, read.fwf. I will be using read.fwf to clean the data then converted it to .csv. For the assignment we were asked to analyzed a file into a format that contains the Player’s Name, Player’s State, Total Number of Points, Player’s Pre-Rating, and Average Pre Chess Rating of Opponents.
ChessFinalResults <-('https://raw.githubusercontent.com/jnaval88/DATA607/main/Project%201/tournamentinfo.txt')
colNames <- c("PairNum","PlayerName","TotalPoints","Round1","Round2","Round3","Round4","Round5","Round6","Round7","State","USCFID","PreRating","PostRating","TotalPointsL2","Round1L2","Round2L2","Round3L2","Round4L2","Round5L2","Round6L2","Round7L2")
colClasses <- c("factor", "character", "numeric", "character", "character", "character", "character", "character", "character", "character", "factor", "integer", "character","character", "character", "character", "character", "character", "character", "character", "character", "character" )
ChessFinal <- read.fwf(ChessFinalResults, width = list( c(6,-1,33,-1,5,-1,5,-1,5,-1,5,-1,5,-1,5,-1,5,-1,5,-1), c(6,-1,10,-4, 8,-2,9,-1,5,-1,5,-1,5,-1,5,-1,5,-1,5,-1,5,-1,5,-1),c(-89)) , skip =4, strip.white=TRUE, col.names = colNames, colClasses= colClasses )
#Reorder the Rows of the DataTable
ChessFinal <- ChessFinal %>% select("PairNum", "PlayerName","State",starts_with("TotalPoints"),"PreRating","PostRating","USCFID", starts_with("R"))
print(ChessFinal)## PairNum PlayerName State TotalPoints TotalPointsL2 PreRating
## 1 1 GARY HUA ON 6.0 N:2 1794
## 2 2 DAKSHESH DARURI MI 6.0 N:2 1553
## 3 3 ADITYA BAJAJ MI 6.0 N:2 1384
## 4 4 PATRICK H SCHILLING MI 5.5 N:2 1716
## 5 5 HANSHI ZUO MI 5.5 N:2 1655
## 6 6 HANSEN SONG OH 5.0 N:3 1686
## 7 7 GARY DEE SWATHELL MI 5.0 N:3 1649
## 8 8 EZEKIEL HOUGHTON MI 5.0 N:3 1641P17
## 9 9 STEFANO LEE ON 5.0 N:2 1411
## 10 10 ANVIT RAO MI 5.0 N:3 1365
## 11 11 CAMERON WILLIAM MC LEMAN MI 4.5 N:3 1712
## 12 12 KENNETH J TACK MI 4.5 N:3 1663
## 13 13 TORRANCE HENRY JR MI 4.5 N:3 1666
## 14 14 BRADLEY SHAW MI 4.5 N:3 1610
## 15 15 ZACHARY JAMES HOUGHTON MI 4.5 N:3 1220P13
## 16 16 MIKE NIKITIN MI 4.0 N:3 1604
## 17 17 RONALD GRZEGORCZYK MI 4.0 N:3 1629
## 18 18 DAVID SUNDEEN MI 4.0 N:3 1600
## 19 19 DIPANKAR ROY MI 4.0 N:3 1564
## 20 20 JASON ZHENG MI 4.0 N:4 1595
## 21 21 DINH DANG BUI ON 4.0 N:3 1563P22
## 22 22 EUGENE L MCCLURE MI 4.0 N:4 1555
## 23 23 ALAN BUI ON 4.0 1363
## 24 24 MICHAEL R ALDRICH MI 4.0 N:4 1229
## 25 25 LOREN SCHWIEBERT MI 3.5 N:4 1745
## 26 26 MAX ZHU ON 3.5 N:4 1579
## 27 27 GAURAV GIDWANI MI 3.5 N:4 1552
## 28 28 SOFIA ADINA STANESCU-BELLU MI 3.5 N:3 1507
## 29 29 CHIEDOZIE OKORIE MI 3.5 N:4 1602P6
## 30 30 GEORGE AVERY JONES ON 3.5 1522
## 31 31 RISHI SHETTY MI 3.5 1494
## 32 32 JOSHUA PHILIP MATHEWS ON 3.5 N:4 1441
## 33 33 JADE GE MI 3.5 1449
## 34 34 MICHAEL JEFFERY THOMAS MI 3.5 1399
## 35 35 JOSHUA DAVID LEE MI 3.5 1438
## 36 36 SIDDHARTH JHA MI 3.5 N:4 1355
## 37 37 AMIYATOSH PWNANANDAM MI 3.5 980P12
## 38 38 BRIAN LIU MI 3.0 N:4 1423
## 39 39 JOEL R HENDON MI 3.0 N:4 1436P23
## 40 40 FOREST ZHANG MI 3.0 1348
## 41 41 KYLE WILLIAM MURPHY MI 3.0 1403P5
## 42 42 JARED GE MI 3.0 1332
## 43 43 ROBERT GLEN VASEY MI 3.0 1283
## 44 44 JUSTIN D SCHILLING MI 3.0 1199
## 45 45 DEREK YAN MI 3.0 1242
## 46 46 JACOB ALEXANDER LAVALLEY MI 3.0 377P3
## 47 47 ERIC WRIGHT MI 2.5 1362
## 48 48 DANIEL KHAIN MI 2.5 1382
## 49 49 MICHAEL J MARTIN MI 2.5 1291P12
## 50 50 SHIVAM JHA MI 2.5 1056
## 51 51 TEJAS AYYAGARI MI 2.5 1011
## 52 52 ETHAN GUO MI 2.5 N:4 935
## 53 53 JOSE C YBARRA MI 2.0 1393
## 54 54 LARRY HODGE MI 2.0 1270
## 55 55 ALEX KONG MI 2.0 1186
## 56 56 MARISA RICCI MI 2.0 1153
## 57 57 MICHAEL LU MI 2.0 1092
## 58 58 VIRAJ MOHILE MI 2.0 917
## 59 59 SEAN M MC CORMICK MI 2.0 853
## 60 60 JULIA SHEN MI 1.5 967
## 61 61 JEZZEL FARKAS ON 1.5 955P11
## 62 62 ASHWIN BALAJI MI 1.0 1530
## 63 63 THOMAS JOSEPH HOSMER MI 1.0 1175
## 64 64 BEN LI MI 1.0 1163
## PostRating USCFID Round1 Round2 Round3 Round4 Round5 Round6 Round7
## 1 1817 15445895 W 39 W 21 W 18 W 14 W 7 D 12 D 4
## 2 1663 14598900 W 63 W 58 L 4 W 17 W 16 W 20 W 7
## 3 1640 14959604 L 8 W 61 W 25 W 21 W 11 W 13 W 12
## 4 1744 12616049 W 23 D 28 W 2 W 26 D 5 W 19 D 1
## 5 1690 14601533 W 45 W 37 D 12 D 13 D 4 W 14 W 17
## 6 1687 15055204 W 34 D 29 L 11 W 35 D 10 W 27 W 21
## 7 1673 11146376 W 57 W 46 W 13 W 11 L 1 W 9 L 2
## 8 1657P24 15142253 W 3 W 32 L 14 L 9 W 47 W 28 W 19
## 9 1564 14954524 W 25 L 18 W 59 W 8 W 26 L 7 W 20
## 10 1544 14150362 D 16 L 19 W 55 W 31 D 6 W 25 W 18
## 11 1696 12581589 D 38 W 56 W 6 L 7 L 3 W 34 W 26
## 12 1670 12681257 W 42 W 33 D 5 W 38 H D 1 L 3
## 13 1662 15082995 W 36 W 27 L 7 D 5 W 33 L 3 W 32
## 14 1618 10131499 W 54 W 44 W 8 L 1 D 27 L 5 W 31
## 15 1416P20 15619130 D 19 L 16 W 30 L 22 W 54 W 33 W 38
## 16 1613 10295068 D 10 W 15 H W 39 L 2 W 36 U
## 17 1610 10297702 W 48 W 41 L 26 L 2 W 23 W 22 L 5
## 18 1600 11342094 W 47 W 9 L 1 W 32 L 19 W 38 L 10
## 19 1570 14862333 D 15 W 10 W 52 D 28 W 18 L 4 L 8
## 20 1569 14529060 L 40 W 49 W 23 W 41 W 28 L 2 L 9
## 21 1562 15495066 W 43 L 1 W 47 L 3 W 40 W 39 L 6
## 22 1529 12405534 W 64 D 52 L 28 W 15 H L 17 W 40
## 23 1371 15030142 L 4 W 43 L 20 W 58 L 17 W 37 W 46
## 24 1300 13469010 L 28 L 47 W 43 L 25 W 60 W 44 W 39
## 25 1681 12486656 L 9 W 53 L 3 W 24 D 34 L 10 W 47
## 26 1564 15131520 W 49 W 40 W 17 L 4 L 9 D 32 L 11
## 27 1539 14476567 W 51 L 13 W 46 W 37 D 14 L 6 U
## 28 1513 14882954 W 24 D 4 W 22 D 19 L 20 L 8 D 36
## 29 1508P12 15323285 W 50 D 6 L 38 L 34 W 52 W 48 U
## 30 1444 12577178 L 52 D 64 L 15 W 55 L 31 W 61 W 50
## 31 1444 15131618 L 58 D 55 W 64 L 10 W 30 W 50 L 14
## 32 1433 14073750 W 61 L 8 W 44 L 18 W 51 D 26 L 13
## 33 1421 14691842 W 60 L 12 W 50 D 36 L 13 L 15 W 51
## 34 1400 15051807 L 6 W 60 L 37 W 29 D 25 L 11 W 52
## 35 1392 14601397 L 46 L 38 W 56 L 6 W 57 D 52 W 48
## 36 1367 14773163 L 13 W 57 W 51 D 33 H L 16 D 28
## 37 1077P17 15489571 B L 5 W 34 L 27 H L 23 W 61
## 38 1439 15108523 D 11 W 35 W 29 L 12 H L 18 L 15
## 39 1413 12923035 L 1 W 54 W 40 L 16 W 44 L 21 L 24
## 40 1346 14892710 W 20 L 26 L 39 W 59 L 21 W 56 L 22
## 41 1341P9 15761443 W 59 L 17 W 58 L 20 X U U
## 42 1256 14462326 L 12 L 50 L 57 D 60 D 61 W 64 W 56
## 43 1244 14101068 L 21 L 23 L 24 W 63 W 59 L 46 W 55
## 44 1199 15323504 B L 14 L 32 W 53 L 39 L 24 W 59
## 45 1191 15372807 L 5 L 51 D 60 L 56 W 63 D 55 W 58
## 46 1076P10 15490981 W 35 L 7 L 27 L 50 W 64 W 43 L 23
## 47 1341 12533115 L 18 W 24 L 21 W 61 L 8 D 51 L 25
## 48 1335 14369165 L 17 W 63 H D 52 H L 29 L 35
## 49 1259P17 12531685 L 26 L 20 D 63 D 64 W 58 H U
## 50 1111 14773178 L 29 W 42 L 33 W 46 H L 31 L 30
## 51 1097 15205474 L 27 W 45 L 36 W 57 L 32 D 47 L 33
## 52 1092 14918803 W 30 D 22 L 19 D 48 L 29 D 35 L 34
## 53 1359 12578849 H L 25 H L 44 U W 57 U
## 54 1200 12836773 L 14 L 39 L 61 B L 15 L 59 W 64
## 55 1163 15412571 L 62 D 31 L 10 L 30 B D 45 L 43
## 56 1140 14679887 H L 11 L 35 W 45 H L 40 L 42
## 57 1079 15113330 L 7 L 36 W 42 L 51 L 35 L 53 B
## 58 941 14700365 W 31 L 2 L 41 L 23 L 49 B L 45
## 59 878 12841036 L 41 B L 9 L 40 L 43 W 54 L 44
## 60 984 14579262 L 33 L 34 D 45 D 42 L 24 H U
## 61 979P18 15771592 L 32 L 3 W 54 L 47 D 42 L 30 L 37
## 62 1535 15219542 W 55 U U U U U U
## 63 1125 15057092 L 2 L 48 D 49 L 43 L 45 H U
## 64 1112 15006561 L 22 D 30 L 31 D 49 L 46 L 42 L 54
## Round1L2 Round2L2 Round3L2 Round4L2 Round5L2 Round6L2 Round7L2
## 1 W B W B W B W
## 2 B W B W B W B
## 3 W B W B W B W
## 4 W B W B W B B
## 5 B W B W B W B
## 6 W B W B B W B
## 7 W B W B B W W
## 8 B W B W B W W
## 9 W B W B W B B
## 10 W W B B W B W
## 11 B W B W B W B
## 12 W B W B W B
## 13 B W B B W W B
## 14 W B W W B B W
## 15 B B W W B B W
## 16 B W B W B
## 17 W B W B W B W
## 18 B W B W B W B
## 19 W B W B W W B
## 20 W B W B W B W
## 21 B W B W W B W
## 22 W B W B W B
## 23 B W B W B W B
## 24 B W B B W W B
## 25 B W B W B W B
## 26 B W B W B W W
## 27 W B W B W B
## 28 W W B W B B W
## 29 B W B W W B
## 30 W B B W W B B
## 31 B W B W B W B
## 32 W B W B W B W
## 33 B W B W B W B
## 34 B W B B W B W
## 35 W W B W B B W
## 36 W B W B W B
## 37 B W W B W
## 38 W B W W B B
## 39 B W B W B W W
## 40 B B W W B W W
## 41 B W B W
## 42 B W B B W W B
## 43 W B W W B B W
## 44 W B B W B W
## 45 W B W B W B W
## 46 B W B W B W W
## 47 W B W B W B W
## 48 B W B W B
## 49 W W B W B
## 50 W B W B B W
## 51 B W B W B W W
## 52 B W B W B W B
## 53 B W W
## 54 B B W W B W
## 55 W B W B W B
## 56 B W W B W
## 57 B W W B W B
## 58 W B W B W B
## 59 W B B W W B
## 60 W B B W B
## 61 B W B W B W B
## 62 B
## 63 W B W B B
## 64 B W W B W B BIf we take a look at the data, we noticed that some columns from the data sets have unused or irrelevant details that not need for the purpose of this assignment. looking at column Total Points up to column Round 7 there are two sets of data which I refer to as Line 2. Since Line 2 of these column is irrelevant for this assignment therefore I will eliminating them.
ChessFinal <- ChessFinal%>% select (everything(), -matches("^Round\\dL2$") , -"TotalPointsL2", -"PostRating" , -"USCFID")I will now remove the outcome from the table then set it as factor.
ChessFinal<-ChessFinal %>% mutate_at( vars( matches("^Round\\d$")) ,~str_extract( . ,"[:digit:]+") )
ChessFinal<-ChessFinal %>% mutate_at( vars( matches("^Round\\d$")) , ~as.factor(.) )As we can see that some of the player pre-rating also include some other P data, this step will cleanup the pre-rating data and remove other data from the pre-rating.
ChessFinal<-ChessFinal %>% mutate( PreRating = str_extract(PreRating ,"[:digit:]+") )
ChessFinal<-ChessFinal %>% mutate( PreRating = as.integer(PreRating) )
reactable(ChessFinal)ChessFinal <- ChessFinal %>% mutate(NumberOfGames = rowSums(!is.na(select(ChessFinal,matches("^Round\\d$")))))
ChessFinal$OpponentSum <- apply(select ( ChessFinal,matches("^Round\\d$") ), 1, function(x){sum(ChessFinal$PreRating[match(x,ChessFinal$PairNum)] , na.rm = TRUE) } )
ChessFinal <- ChessFinal %>% mutate ( AverageOpponentRating = round(OpponentSum / NumberOfGames, digits = 0) )
reactable( select (ChessFinal, -matches("^Round\\d$") , -"NumberOfGames", -"OpponentSum" ))The following command will export the data frame above to a csv, I am using the relative path this will save in the working directory of my R environment.
write.table(ChessFinal,sep = ",", file = "tournamentinfo.csv", row.names = FALSE, col.names = c("PairNum","PlayerName","State","TotalPoints","Pre-Rating","Round1","Round2","Round3","Round4","Round5","Round6","Round7","NumberOfGames","OpponentSum", "AverageOpponentRating"))
reactable(ChessFinal)