In this project, you’re given a text file with chess tournament results where the information has some structure. Your job 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
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.
Load text file into GitHub repository and read into R. Trim any trailing white spaces
library(stringr)
path <- 'https://raw.githubusercontent.com/dhairavc/DATA607/master/tournamentinfo.txt'
chess_results <- readLines(path, warn = FALSE)
chess_results <- str_trim(chess_results, side = 'right')
chess_results## [1] "-----------------------------------------------------------------------------------------"
## [2] " Pair | Player Name |Total|Round|Round|Round|Round|Round|Round|Round|"
## [3] " Num | USCF ID / Rtg (Pre->Post) | Pts | 1 | 2 | 3 | 4 | 5 | 6 | 7 |"
## [4] "-----------------------------------------------------------------------------------------"
## [5] " 1 | GARY HUA |6.0 |W 39|W 21|W 18|W 14|W 7|D 12|D 4|"
## [6] " ON | 15445895 / R: 1794 ->1817 |N:2 |W |B |W |B |W |B |W |"
## [7] "-----------------------------------------------------------------------------------------"
## [8] " 2 | DAKSHESH DARURI |6.0 |W 63|W 58|L 4|W 17|W 16|W 20|W 7|"
## [9] " MI | 14598900 / R: 1553 ->1663 |N:2 |B |W |B |W |B |W |B |"
## [10] "-----------------------------------------------------------------------------------------"
## [11] " 3 | ADITYA BAJAJ |6.0 |L 8|W 61|W 25|W 21|W 11|W 13|W 12|"
## [12] " MI | 14959604 / R: 1384 ->1640 |N:2 |W |B |W |B |W |B |W |"
## [13] "-----------------------------------------------------------------------------------------"
## [14] " 4 | PATRICK H SCHILLING |5.5 |W 23|D 28|W 2|W 26|D 5|W 19|D 1|"
## [15] " MI | 12616049 / R: 1716 ->1744 |N:2 |W |B |W |B |W |B |B |"
## [16] "-----------------------------------------------------------------------------------------"
## [17] " 5 | HANSHI ZUO |5.5 |W 45|W 37|D 12|D 13|D 4|W 14|W 17|"
## [18] " MI | 14601533 / R: 1655 ->1690 |N:2 |B |W |B |W |B |W |B |"
## [19] "-----------------------------------------------------------------------------------------"
## [20] " 6 | HANSEN SONG |5.0 |W 34|D 29|L 11|W 35|D 10|W 27|W 21|"
## [21] " OH | 15055204 / R: 1686 ->1687 |N:3 |W |B |W |B |B |W |B |"
## [22] "-----------------------------------------------------------------------------------------"
## [23] " 7 | GARY DEE SWATHELL |5.0 |W 57|W 46|W 13|W 11|L 1|W 9|L 2|"
## [24] " MI | 11146376 / R: 1649 ->1673 |N:3 |W |B |W |B |B |W |W |"
## [25] "-----------------------------------------------------------------------------------------"
## [26] " 8 | EZEKIEL HOUGHTON |5.0 |W 3|W 32|L 14|L 9|W 47|W 28|W 19|"
## [27] " MI | 15142253 / R: 1641P17->1657P24 |N:3 |B |W |B |W |B |W |W |"
## [28] "-----------------------------------------------------------------------------------------"
## [29] " 9 | STEFANO LEE |5.0 |W 25|L 18|W 59|W 8|W 26|L 7|W 20|"
## [30] " ON | 14954524 / R: 1411 ->1564 |N:2 |W |B |W |B |W |B |B |"
## [31] "-----------------------------------------------------------------------------------------"
## [32] " 10 | ANVIT RAO |5.0 |D 16|L 19|W 55|W 31|D 6|W 25|W 18|"
## [33] " MI | 14150362 / R: 1365 ->1544 |N:3 |W |W |B |B |W |B |W |"
## [34] "-----------------------------------------------------------------------------------------"
## [35] " 11 | CAMERON WILLIAM MC LEMAN |4.5 |D 38|W 56|W 6|L 7|L 3|W 34|W 26|"
## [36] " MI | 12581589 / R: 1712 ->1696 |N:3 |B |W |B |W |B |W |B |"
## [37] "-----------------------------------------------------------------------------------------"
## [38] " 12 | KENNETH J TACK |4.5 |W 42|W 33|D 5|W 38|H |D 1|L 3|"
## [39] " MI | 12681257 / R: 1663 ->1670 |N:3 |W |B |W |B | |W |B |"
## [40] "-----------------------------------------------------------------------------------------"
## [41] " 13 | TORRANCE HENRY JR |4.5 |W 36|W 27|L 7|D 5|W 33|L 3|W 32|"
## [42] " MI | 15082995 / R: 1666 ->1662 |N:3 |B |W |B |B |W |W |B |"
## [43] "-----------------------------------------------------------------------------------------"
## [44] " 14 | BRADLEY SHAW |4.5 |W 54|W 44|W 8|L 1|D 27|L 5|W 31|"
## [45] " MI | 10131499 / R: 1610 ->1618 |N:3 |W |B |W |W |B |B |W |"
## [46] "-----------------------------------------------------------------------------------------"
## [47] " 15 | ZACHARY JAMES HOUGHTON |4.5 |D 19|L 16|W 30|L 22|W 54|W 33|W 38|"
## [48] " MI | 15619130 / R: 1220P13->1416P20 |N:3 |B |B |W |W |B |B |W |"
## [49] "-----------------------------------------------------------------------------------------"
## [50] " 16 | MIKE NIKITIN |4.0 |D 10|W 15|H |W 39|L 2|W 36|U |"
## [51] " MI | 10295068 / R: 1604 ->1613 |N:3 |B |W | |B |W |B | |"
## [52] "-----------------------------------------------------------------------------------------"
## [53] " 17 | RONALD GRZEGORCZYK |4.0 |W 48|W 41|L 26|L 2|W 23|W 22|L 5|"
## [54] " MI | 10297702 / R: 1629 ->1610 |N:3 |W |B |W |B |W |B |W |"
## [55] "-----------------------------------------------------------------------------------------"
## [56] " 18 | DAVID SUNDEEN |4.0 |W 47|W 9|L 1|W 32|L 19|W 38|L 10|"
## [57] " MI | 11342094 / R: 1600 ->1600 |N:3 |B |W |B |W |B |W |B |"
## [58] "-----------------------------------------------------------------------------------------"
## [59] " 19 | DIPANKAR ROY |4.0 |D 15|W 10|W 52|D 28|W 18|L 4|L 8|"
## [60] " MI | 14862333 / R: 1564 ->1570 |N:3 |W |B |W |B |W |W |B |"
## [61] "-----------------------------------------------------------------------------------------"
## [62] " 20 | JASON ZHENG |4.0 |L 40|W 49|W 23|W 41|W 28|L 2|L 9|"
## [63] " MI | 14529060 / R: 1595 ->1569 |N:4 |W |B |W |B |W |B |W |"
## [64] "-----------------------------------------------------------------------------------------"
## [65] " 21 | DINH DANG BUI |4.0 |W 43|L 1|W 47|L 3|W 40|W 39|L 6|"
## [66] " ON | 15495066 / R: 1563P22->1562 |N:3 |B |W |B |W |W |B |W |"
## [67] "-----------------------------------------------------------------------------------------"
## [68] " 22 | EUGENE L MCCLURE |4.0 |W 64|D 52|L 28|W 15|H |L 17|W 40|"
## [69] " MI | 12405534 / R: 1555 ->1529 |N:4 |W |B |W |B | |W |B |"
## [70] "-----------------------------------------------------------------------------------------"
## [71] " 23 | ALAN BUI |4.0 |L 4|W 43|L 20|W 58|L 17|W 37|W 46|"
## [72] " ON | 15030142 / R: 1363 ->1371 | |B |W |B |W |B |W |B |"
## [73] "-----------------------------------------------------------------------------------------"
## [74] " 24 | MICHAEL R ALDRICH |4.0 |L 28|L 47|W 43|L 25|W 60|W 44|W 39|"
## [75] " MI | 13469010 / R: 1229 ->1300 |N:4 |B |W |B |B |W |W |B |"
## [76] "-----------------------------------------------------------------------------------------"
## [77] " 25 | LOREN SCHWIEBERT |3.5 |L 9|W 53|L 3|W 24|D 34|L 10|W 47|"
## [78] " MI | 12486656 / R: 1745 ->1681 |N:4 |B |W |B |W |B |W |B |"
## [79] "-----------------------------------------------------------------------------------------"
## [80] " 26 | MAX ZHU |3.5 |W 49|W 40|W 17|L 4|L 9|D 32|L 11|"
## [81] " ON | 15131520 / R: 1579 ->1564 |N:4 |B |W |B |W |B |W |W |"
## [82] "-----------------------------------------------------------------------------------------"
## [83] " 27 | GAURAV GIDWANI |3.5 |W 51|L 13|W 46|W 37|D 14|L 6|U |"
## [84] " MI | 14476567 / R: 1552 ->1539 |N:4 |W |B |W |B |W |B | |"
## [85] "-----------------------------------------------------------------------------------------"
## [86] " 28 | SOFIA ADINA STANESCU-BELLU |3.5 |W 24|D 4|W 22|D 19|L 20|L 8|D 36|"
## [87] " MI | 14882954 / R: 1507 ->1513 |N:3 |W |W |B |W |B |B |W |"
## [88] "-----------------------------------------------------------------------------------------"
## [89] " 29 | CHIEDOZIE OKORIE |3.5 |W 50|D 6|L 38|L 34|W 52|W 48|U |"
## [90] " MI | 15323285 / R: 1602P6 ->1508P12 |N:4 |B |W |B |W |W |B | |"
## [91] "-----------------------------------------------------------------------------------------"
## [92] " 30 | GEORGE AVERY JONES |3.5 |L 52|D 64|L 15|W 55|L 31|W 61|W 50|"
## [93] " ON | 12577178 / R: 1522 ->1444 | |W |B |B |W |W |B |B |"
## [94] "-----------------------------------------------------------------------------------------"
## [95] " 31 | RISHI SHETTY |3.5 |L 58|D 55|W 64|L 10|W 30|W 50|L 14|"
## [96] " MI | 15131618 / R: 1494 ->1444 | |B |W |B |W |B |W |B |"
## [97] "-----------------------------------------------------------------------------------------"
## [98] " 32 | JOSHUA PHILIP MATHEWS |3.5 |W 61|L 8|W 44|L 18|W 51|D 26|L 13|"
## [99] " ON | 14073750 / R: 1441 ->1433 |N:4 |W |B |W |B |W |B |W |"
## [100] "-----------------------------------------------------------------------------------------"
## [101] " 33 | JADE GE |3.5 |W 60|L 12|W 50|D 36|L 13|L 15|W 51|"
## [102] " MI | 14691842 / R: 1449 ->1421 | |B |W |B |W |B |W |B |"
## [103] "-----------------------------------------------------------------------------------------"
## [104] " 34 | MICHAEL JEFFERY THOMAS |3.5 |L 6|W 60|L 37|W 29|D 25|L 11|W 52|"
## [105] " MI | 15051807 / R: 1399 ->1400 | |B |W |B |B |W |B |W |"
## [106] "-----------------------------------------------------------------------------------------"
## [107] " 35 | JOSHUA DAVID LEE |3.5 |L 46|L 38|W 56|L 6|W 57|D 52|W 48|"
## [108] " MI | 14601397 / R: 1438 ->1392 | |W |W |B |W |B |B |W |"
## [109] "-----------------------------------------------------------------------------------------"
## [110] " 36 | SIDDHARTH JHA |3.5 |L 13|W 57|W 51|D 33|H |L 16|D 28|"
## [111] " MI | 14773163 / R: 1355 ->1367 |N:4 |W |B |W |B | |W |B |"
## [112] "-----------------------------------------------------------------------------------------"
## [113] " 37 | AMIYATOSH PWNANANDAM |3.5 |B |L 5|W 34|L 27|H |L 23|W 61|"
## [114] " MI | 15489571 / R: 980P12->1077P17 | | |B |W |W | |B |W |"
## [115] "-----------------------------------------------------------------------------------------"
## [116] " 38 | BRIAN LIU |3.0 |D 11|W 35|W 29|L 12|H |L 18|L 15|"
## [117] " MI | 15108523 / R: 1423 ->1439 |N:4 |W |B |W |W | |B |B |"
## [118] "-----------------------------------------------------------------------------------------"
## [119] " 39 | JOEL R HENDON |3.0 |L 1|W 54|W 40|L 16|W 44|L 21|L 24|"
## [120] " MI | 12923035 / R: 1436P23->1413 |N:4 |B |W |B |W |B |W |W |"
## [121] "-----------------------------------------------------------------------------------------"
## [122] " 40 | FOREST ZHANG |3.0 |W 20|L 26|L 39|W 59|L 21|W 56|L 22|"
## [123] " MI | 14892710 / R: 1348 ->1346 | |B |B |W |W |B |W |W |"
## [124] "-----------------------------------------------------------------------------------------"
## [125] " 41 | KYLE WILLIAM MURPHY |3.0 |W 59|L 17|W 58|L 20|X |U |U |"
## [126] " MI | 15761443 / R: 1403P5 ->1341P9 | |B |W |B |W | | | |"
## [127] "-----------------------------------------------------------------------------------------"
## [128] " 42 | JARED GE |3.0 |L 12|L 50|L 57|D 60|D 61|W 64|W 56|"
## [129] " MI | 14462326 / R: 1332 ->1256 | |B |W |B |B |W |W |B |"
## [130] "-----------------------------------------------------------------------------------------"
## [131] " 43 | ROBERT GLEN VASEY |3.0 |L 21|L 23|L 24|W 63|W 59|L 46|W 55|"
## [132] " MI | 14101068 / R: 1283 ->1244 | |W |B |W |W |B |B |W |"
## [133] "-----------------------------------------------------------------------------------------"
## [134] " 44 | JUSTIN D SCHILLING |3.0 |B |L 14|L 32|W 53|L 39|L 24|W 59|"
## [135] " MI | 15323504 / R: 1199 ->1199 | | |W |B |B |W |B |W |"
## [136] "-----------------------------------------------------------------------------------------"
## [137] " 45 | DEREK YAN |3.0 |L 5|L 51|D 60|L 56|W 63|D 55|W 58|"
## [138] " MI | 15372807 / R: 1242 ->1191 | |W |B |W |B |W |B |W |"
## [139] "-----------------------------------------------------------------------------------------"
## [140] " 46 | JACOB ALEXANDER LAVALLEY |3.0 |W 35|L 7|L 27|L 50|W 64|W 43|L 23|"
## [141] " MI | 15490981 / R: 377P3 ->1076P10 | |B |W |B |W |B |W |W |"
## [142] "-----------------------------------------------------------------------------------------"
## [143] " 47 | ERIC WRIGHT |2.5 |L 18|W 24|L 21|W 61|L 8|D 51|L 25|"
## [144] " MI | 12533115 / R: 1362 ->1341 | |W |B |W |B |W |B |W |"
## [145] "-----------------------------------------------------------------------------------------"
## [146] " 48 | DANIEL KHAIN |2.5 |L 17|W 63|H |D 52|H |L 29|L 35|"
## [147] " MI | 14369165 / R: 1382 ->1335 | |B |W | |B | |W |B |"
## [148] "-----------------------------------------------------------------------------------------"
## [149] " 49 | MICHAEL J MARTIN |2.5 |L 26|L 20|D 63|D 64|W 58|H |U |"
## [150] " MI | 12531685 / R: 1291P12->1259P17 | |W |W |B |W |B | | |"
## [151] "-----------------------------------------------------------------------------------------"
## [152] " 50 | SHIVAM JHA |2.5 |L 29|W 42|L 33|W 46|H |L 31|L 30|"
## [153] " MI | 14773178 / R: 1056 ->1111 | |W |B |W |B | |B |W |"
## [154] "-----------------------------------------------------------------------------------------"
## [155] " 51 | TEJAS AYYAGARI |2.5 |L 27|W 45|L 36|W 57|L 32|D 47|L 33|"
## [156] " MI | 15205474 / R: 1011 ->1097 | |B |W |B |W |B |W |W |"
## [157] "-----------------------------------------------------------------------------------------"
## [158] " 52 | ETHAN GUO |2.5 |W 30|D 22|L 19|D 48|L 29|D 35|L 34|"
## [159] " MI | 14918803 / R: 935 ->1092 |N:4 |B |W |B |W |B |W |B |"
## [160] "-----------------------------------------------------------------------------------------"
## [161] " 53 | JOSE C YBARRA |2.0 |H |L 25|H |L 44|U |W 57|U |"
## [162] " MI | 12578849 / R: 1393 ->1359 | | |B | |W | |W | |"
## [163] "-----------------------------------------------------------------------------------------"
## [164] " 54 | LARRY HODGE |2.0 |L 14|L 39|L 61|B |L 15|L 59|W 64|"
## [165] " MI | 12836773 / R: 1270 ->1200 | |B |B |W | |W |B |W |"
## [166] "-----------------------------------------------------------------------------------------"
## [167] " 55 | ALEX KONG |2.0 |L 62|D 31|L 10|L 30|B |D 45|L 43|"
## [168] " MI | 15412571 / R: 1186 ->1163 | |W |B |W |B | |W |B |"
## [169] "-----------------------------------------------------------------------------------------"
## [170] " 56 | MARISA RICCI |2.0 |H |L 11|L 35|W 45|H |L 40|L 42|"
## [171] " MI | 14679887 / R: 1153 ->1140 | | |B |W |W | |B |W |"
## [172] "-----------------------------------------------------------------------------------------"
## [173] " 57 | MICHAEL LU |2.0 |L 7|L 36|W 42|L 51|L 35|L 53|B |"
## [174] " MI | 15113330 / R: 1092 ->1079 | |B |W |W |B |W |B | |"
## [175] "-----------------------------------------------------------------------------------------"
## [176] " 58 | VIRAJ MOHILE |2.0 |W 31|L 2|L 41|L 23|L 49|B |L 45|"
## [177] " MI | 14700365 / R: 917 -> 941 | |W |B |W |B |W | |B |"
## [178] "-----------------------------------------------------------------------------------------"
## [179] " 59 | SEAN M MC CORMICK |2.0 |L 41|B |L 9|L 40|L 43|W 54|L 44|"
## [180] " MI | 12841036 / R: 853 -> 878 | |W | |B |B |W |W |B |"
## [181] "-----------------------------------------------------------------------------------------"
## [182] " 60 | JULIA SHEN |1.5 |L 33|L 34|D 45|D 42|L 24|H |U |"
## [183] " MI | 14579262 / R: 967 -> 984 | |W |B |B |W |B | | |"
## [184] "-----------------------------------------------------------------------------------------"
## [185] " 61 | JEZZEL FARKAS |1.5 |L 32|L 3|W 54|L 47|D 42|L 30|L 37|"
## [186] " ON | 15771592 / R: 955P11-> 979P18 | |B |W |B |W |B |W |B |"
## [187] "-----------------------------------------------------------------------------------------"
## [188] " 62 | ASHWIN BALAJI |1.0 |W 55|U |U |U |U |U |U |"
## [189] " MI | 15219542 / R: 1530 ->1535 | |B | | | | | | |"
## [190] "-----------------------------------------------------------------------------------------"
## [191] " 63 | THOMAS JOSEPH HOSMER |1.0 |L 2|L 48|D 49|L 43|L 45|H |U |"
## [192] " MI | 15057092 / R: 1175 ->1125 | |W |B |W |B |B | | |"
## [193] "-----------------------------------------------------------------------------------------"
## [194] " 64 | BEN LI |1.0 |L 22|D 30|L 31|D 49|L 46|L 42|L 54|"
## [195] " MI | 15006561 / R: 1163 ->1112 | |B |W |W |B |W |B |B |"
## [196] "-----------------------------------------------------------------------------------------"Seperate data into into two patterns for extraction
Line1 <- grep(chess_results, pattern = "[[:space:]]{3,}[[:digit:]]{1,2}", value = TRUE)
Line1## [1] " 1 | GARY HUA |6.0 |W 39|W 21|W 18|W 14|W 7|D 12|D 4|"
## [2] " 2 | DAKSHESH DARURI |6.0 |W 63|W 58|L 4|W 17|W 16|W 20|W 7|"
## [3] " 3 | ADITYA BAJAJ |6.0 |L 8|W 61|W 25|W 21|W 11|W 13|W 12|"
## [4] " 4 | PATRICK H SCHILLING |5.5 |W 23|D 28|W 2|W 26|D 5|W 19|D 1|"
## [5] " 5 | HANSHI ZUO |5.5 |W 45|W 37|D 12|D 13|D 4|W 14|W 17|"
## [6] " 6 | HANSEN SONG |5.0 |W 34|D 29|L 11|W 35|D 10|W 27|W 21|"
## [7] " 7 | GARY DEE SWATHELL |5.0 |W 57|W 46|W 13|W 11|L 1|W 9|L 2|"
## [8] " 8 | EZEKIEL HOUGHTON |5.0 |W 3|W 32|L 14|L 9|W 47|W 28|W 19|"
## [9] " 9 | STEFANO LEE |5.0 |W 25|L 18|W 59|W 8|W 26|L 7|W 20|"
## [10] " 10 | ANVIT RAO |5.0 |D 16|L 19|W 55|W 31|D 6|W 25|W 18|"
## [11] " 11 | CAMERON WILLIAM MC LEMAN |4.5 |D 38|W 56|W 6|L 7|L 3|W 34|W 26|"
## [12] " 12 | KENNETH J TACK |4.5 |W 42|W 33|D 5|W 38|H |D 1|L 3|"
## [13] " 13 | TORRANCE HENRY JR |4.5 |W 36|W 27|L 7|D 5|W 33|L 3|W 32|"
## [14] " 14 | BRADLEY SHAW |4.5 |W 54|W 44|W 8|L 1|D 27|L 5|W 31|"
## [15] " 15 | ZACHARY JAMES HOUGHTON |4.5 |D 19|L 16|W 30|L 22|W 54|W 33|W 38|"
## [16] " 16 | MIKE NIKITIN |4.0 |D 10|W 15|H |W 39|L 2|W 36|U |"
## [17] " 17 | RONALD GRZEGORCZYK |4.0 |W 48|W 41|L 26|L 2|W 23|W 22|L 5|"
## [18] " 18 | DAVID SUNDEEN |4.0 |W 47|W 9|L 1|W 32|L 19|W 38|L 10|"
## [19] " 19 | DIPANKAR ROY |4.0 |D 15|W 10|W 52|D 28|W 18|L 4|L 8|"
## [20] " 20 | JASON ZHENG |4.0 |L 40|W 49|W 23|W 41|W 28|L 2|L 9|"
## [21] " 21 | DINH DANG BUI |4.0 |W 43|L 1|W 47|L 3|W 40|W 39|L 6|"
## [22] " 22 | EUGENE L MCCLURE |4.0 |W 64|D 52|L 28|W 15|H |L 17|W 40|"
## [23] " 23 | ALAN BUI |4.0 |L 4|W 43|L 20|W 58|L 17|W 37|W 46|"
## [24] " 24 | MICHAEL R ALDRICH |4.0 |L 28|L 47|W 43|L 25|W 60|W 44|W 39|"
## [25] " 25 | LOREN SCHWIEBERT |3.5 |L 9|W 53|L 3|W 24|D 34|L 10|W 47|"
## [26] " 26 | MAX ZHU |3.5 |W 49|W 40|W 17|L 4|L 9|D 32|L 11|"
## [27] " 27 | GAURAV GIDWANI |3.5 |W 51|L 13|W 46|W 37|D 14|L 6|U |"
## [28] " 28 | SOFIA ADINA STANESCU-BELLU |3.5 |W 24|D 4|W 22|D 19|L 20|L 8|D 36|"
## [29] " 29 | CHIEDOZIE OKORIE |3.5 |W 50|D 6|L 38|L 34|W 52|W 48|U |"
## [30] " 30 | GEORGE AVERY JONES |3.5 |L 52|D 64|L 15|W 55|L 31|W 61|W 50|"
## [31] " 31 | RISHI SHETTY |3.5 |L 58|D 55|W 64|L 10|W 30|W 50|L 14|"
## [32] " 32 | JOSHUA PHILIP MATHEWS |3.5 |W 61|L 8|W 44|L 18|W 51|D 26|L 13|"
## [33] " 33 | JADE GE |3.5 |W 60|L 12|W 50|D 36|L 13|L 15|W 51|"
## [34] " 34 | MICHAEL JEFFERY THOMAS |3.5 |L 6|W 60|L 37|W 29|D 25|L 11|W 52|"
## [35] " 35 | JOSHUA DAVID LEE |3.5 |L 46|L 38|W 56|L 6|W 57|D 52|W 48|"
## [36] " 36 | SIDDHARTH JHA |3.5 |L 13|W 57|W 51|D 33|H |L 16|D 28|"
## [37] " 37 | AMIYATOSH PWNANANDAM |3.5 |B |L 5|W 34|L 27|H |L 23|W 61|"
## [38] " 38 | BRIAN LIU |3.0 |D 11|W 35|W 29|L 12|H |L 18|L 15|"
## [39] " 39 | JOEL R HENDON |3.0 |L 1|W 54|W 40|L 16|W 44|L 21|L 24|"
## [40] " 40 | FOREST ZHANG |3.0 |W 20|L 26|L 39|W 59|L 21|W 56|L 22|"
## [41] " 41 | KYLE WILLIAM MURPHY |3.0 |W 59|L 17|W 58|L 20|X |U |U |"
## [42] " 42 | JARED GE |3.0 |L 12|L 50|L 57|D 60|D 61|W 64|W 56|"
## [43] " 43 | ROBERT GLEN VASEY |3.0 |L 21|L 23|L 24|W 63|W 59|L 46|W 55|"
## [44] " 44 | JUSTIN D SCHILLING |3.0 |B |L 14|L 32|W 53|L 39|L 24|W 59|"
## [45] " 45 | DEREK YAN |3.0 |L 5|L 51|D 60|L 56|W 63|D 55|W 58|"
## [46] " 46 | JACOB ALEXANDER LAVALLEY |3.0 |W 35|L 7|L 27|L 50|W 64|W 43|L 23|"
## [47] " 47 | ERIC WRIGHT |2.5 |L 18|W 24|L 21|W 61|L 8|D 51|L 25|"
## [48] " 48 | DANIEL KHAIN |2.5 |L 17|W 63|H |D 52|H |L 29|L 35|"
## [49] " 49 | MICHAEL J MARTIN |2.5 |L 26|L 20|D 63|D 64|W 58|H |U |"
## [50] " 50 | SHIVAM JHA |2.5 |L 29|W 42|L 33|W 46|H |L 31|L 30|"
## [51] " 51 | TEJAS AYYAGARI |2.5 |L 27|W 45|L 36|W 57|L 32|D 47|L 33|"
## [52] " 52 | ETHAN GUO |2.5 |W 30|D 22|L 19|D 48|L 29|D 35|L 34|"
## [53] " 53 | JOSE C YBARRA |2.0 |H |L 25|H |L 44|U |W 57|U |"
## [54] " 54 | LARRY HODGE |2.0 |L 14|L 39|L 61|B |L 15|L 59|W 64|"
## [55] " 55 | ALEX KONG |2.0 |L 62|D 31|L 10|L 30|B |D 45|L 43|"
## [56] " 56 | MARISA RICCI |2.0 |H |L 11|L 35|W 45|H |L 40|L 42|"
## [57] " 57 | MICHAEL LU |2.0 |L 7|L 36|W 42|L 51|L 35|L 53|B |"
## [58] " 58 | VIRAJ MOHILE |2.0 |W 31|L 2|L 41|L 23|L 49|B |L 45|"
## [59] " 59 | SEAN M MC CORMICK |2.0 |L 41|B |L 9|L 40|L 43|W 54|L 44|"
## [60] " 60 | JULIA SHEN |1.5 |L 33|L 34|D 45|D 42|L 24|H |U |"
## [61] " 61 | JEZZEL FARKAS |1.5 |L 32|L 3|W 54|L 47|D 42|L 30|L 37|"
## [62] " 62 | ASHWIN BALAJI |1.0 |W 55|U |U |U |U |U |U |"
## [63] " 63 | THOMAS JOSEPH HOSMER |1.0 |L 2|L 48|D 49|L 43|L 45|H |U |"
## [64] " 64 | BEN LI |1.0 |L 22|D 30|L 31|D 49|L 46|L 42|L 54|"Line2 <- grep(chess_results, pattern = "[[:space:]]{3}[[:alpha:]]{2}", value = TRUE)
Line2## [1] " ON | 15445895 / R: 1794 ->1817 |N:2 |W |B |W |B |W |B |W |"
## [2] " MI | 14598900 / R: 1553 ->1663 |N:2 |B |W |B |W |B |W |B |"
## [3] " MI | 14959604 / R: 1384 ->1640 |N:2 |W |B |W |B |W |B |W |"
## [4] " MI | 12616049 / R: 1716 ->1744 |N:2 |W |B |W |B |W |B |B |"
## [5] " MI | 14601533 / R: 1655 ->1690 |N:2 |B |W |B |W |B |W |B |"
## [6] " OH | 15055204 / R: 1686 ->1687 |N:3 |W |B |W |B |B |W |B |"
## [7] " MI | 11146376 / R: 1649 ->1673 |N:3 |W |B |W |B |B |W |W |"
## [8] " MI | 15142253 / R: 1641P17->1657P24 |N:3 |B |W |B |W |B |W |W |"
## [9] " ON | 14954524 / R: 1411 ->1564 |N:2 |W |B |W |B |W |B |B |"
## [10] " MI | 14150362 / R: 1365 ->1544 |N:3 |W |W |B |B |W |B |W |"
## [11] " MI | 12581589 / R: 1712 ->1696 |N:3 |B |W |B |W |B |W |B |"
## [12] " MI | 12681257 / R: 1663 ->1670 |N:3 |W |B |W |B | |W |B |"
## [13] " MI | 15082995 / R: 1666 ->1662 |N:3 |B |W |B |B |W |W |B |"
## [14] " MI | 10131499 / R: 1610 ->1618 |N:3 |W |B |W |W |B |B |W |"
## [15] " MI | 15619130 / R: 1220P13->1416P20 |N:3 |B |B |W |W |B |B |W |"
## [16] " MI | 10295068 / R: 1604 ->1613 |N:3 |B |W | |B |W |B | |"
## [17] " MI | 10297702 / R: 1629 ->1610 |N:3 |W |B |W |B |W |B |W |"
## [18] " MI | 11342094 / R: 1600 ->1600 |N:3 |B |W |B |W |B |W |B |"
## [19] " MI | 14862333 / R: 1564 ->1570 |N:3 |W |B |W |B |W |W |B |"
## [20] " MI | 14529060 / R: 1595 ->1569 |N:4 |W |B |W |B |W |B |W |"
## [21] " ON | 15495066 / R: 1563P22->1562 |N:3 |B |W |B |W |W |B |W |"
## [22] " MI | 12405534 / R: 1555 ->1529 |N:4 |W |B |W |B | |W |B |"
## [23] " ON | 15030142 / R: 1363 ->1371 | |B |W |B |W |B |W |B |"
## [24] " MI | 13469010 / R: 1229 ->1300 |N:4 |B |W |B |B |W |W |B |"
## [25] " MI | 12486656 / R: 1745 ->1681 |N:4 |B |W |B |W |B |W |B |"
## [26] " ON | 15131520 / R: 1579 ->1564 |N:4 |B |W |B |W |B |W |W |"
## [27] " MI | 14476567 / R: 1552 ->1539 |N:4 |W |B |W |B |W |B | |"
## [28] " MI | 14882954 / R: 1507 ->1513 |N:3 |W |W |B |W |B |B |W |"
## [29] " MI | 15323285 / R: 1602P6 ->1508P12 |N:4 |B |W |B |W |W |B | |"
## [30] " ON | 12577178 / R: 1522 ->1444 | |W |B |B |W |W |B |B |"
## [31] " MI | 15131618 / R: 1494 ->1444 | |B |W |B |W |B |W |B |"
## [32] " ON | 14073750 / R: 1441 ->1433 |N:4 |W |B |W |B |W |B |W |"
## [33] " MI | 14691842 / R: 1449 ->1421 | |B |W |B |W |B |W |B |"
## [34] " MI | 15051807 / R: 1399 ->1400 | |B |W |B |B |W |B |W |"
## [35] " MI | 14601397 / R: 1438 ->1392 | |W |W |B |W |B |B |W |"
## [36] " MI | 14773163 / R: 1355 ->1367 |N:4 |W |B |W |B | |W |B |"
## [37] " MI | 15489571 / R: 980P12->1077P17 | | |B |W |W | |B |W |"
## [38] " MI | 15108523 / R: 1423 ->1439 |N:4 |W |B |W |W | |B |B |"
## [39] " MI | 12923035 / R: 1436P23->1413 |N:4 |B |W |B |W |B |W |W |"
## [40] " MI | 14892710 / R: 1348 ->1346 | |B |B |W |W |B |W |W |"
## [41] " MI | 15761443 / R: 1403P5 ->1341P9 | |B |W |B |W | | | |"
## [42] " MI | 14462326 / R: 1332 ->1256 | |B |W |B |B |W |W |B |"
## [43] " MI | 14101068 / R: 1283 ->1244 | |W |B |W |W |B |B |W |"
## [44] " MI | 15323504 / R: 1199 ->1199 | | |W |B |B |W |B |W |"
## [45] " MI | 15372807 / R: 1242 ->1191 | |W |B |W |B |W |B |W |"
## [46] " MI | 15490981 / R: 377P3 ->1076P10 | |B |W |B |W |B |W |W |"
## [47] " MI | 12533115 / R: 1362 ->1341 | |W |B |W |B |W |B |W |"
## [48] " MI | 14369165 / R: 1382 ->1335 | |B |W | |B | |W |B |"
## [49] " MI | 12531685 / R: 1291P12->1259P17 | |W |W |B |W |B | | |"
## [50] " MI | 14773178 / R: 1056 ->1111 | |W |B |W |B | |B |W |"
## [51] " MI | 15205474 / R: 1011 ->1097 | |B |W |B |W |B |W |W |"
## [52] " MI | 14918803 / R: 935 ->1092 |N:4 |B |W |B |W |B |W |B |"
## [53] " MI | 12578849 / R: 1393 ->1359 | | |B | |W | |W | |"
## [54] " MI | 12836773 / R: 1270 ->1200 | |B |B |W | |W |B |W |"
## [55] " MI | 15412571 / R: 1186 ->1163 | |W |B |W |B | |W |B |"
## [56] " MI | 14679887 / R: 1153 ->1140 | | |B |W |W | |B |W |"
## [57] " MI | 15113330 / R: 1092 ->1079 | |B |W |W |B |W |B | |"
## [58] " MI | 14700365 / R: 917 -> 941 | |W |B |W |B |W | |B |"
## [59] " MI | 12841036 / R: 853 -> 878 | |W | |B |B |W |W |B |"
## [60] " MI | 14579262 / R: 967 -> 984 | |W |B |B |W |B | | |"
## [61] " ON | 15771592 / R: 955P11-> 979P18 | |B |W |B |W |B |W |B |"
## [62] " MI | 15219542 / R: 1530 ->1535 | |B | | | | | | |"
## [63] " MI | 15057092 / R: 1175 ->1125 | |W |B |W |B |B | | |"
## [64] " MI | 15006561 / R: 1163 ->1112 | |B |W |W |B |W |B |B |"Extract the Player ID. This ID can be used a primary identifier in the data for the player table
player_ID <- str_extract(Line2, "[[:digit:]]{8}")
chess_results_table <- data.frame(player_ID)
head(chess_results_table)## player_ID
## 1 15445895
## 2 14598900
## 3 14959604
## 4 12616049
## 5 14601533
## 6 15055204Extract the player’s pair number
pair_num <- str_extract(Line1, "^(.{5})")
pair_num <- str_trim(pair_num, side = 'both')
chess_results_table$Pair_Num <- as.numeric(pair_num)
#chess_results_table <- data.frame(as.numeric(pair_num))
#chess_results_table)[2] <- "Pair_Num"
head(chess_results_table)## player_ID Pair_Num
## 1 15445895 1
## 2 14598900 2
## 3 14959604 3
## 4 12616049 4
## 5 14601533 5
## 6 15055204 6Extract player names
player_names <- str_extract(Line1, "\\|[[:space:]]([[:alpha:]]*[[[:space:]]|[[:punct:]]])*[[:space:]]")
player_names <- str_sub(player_names, 3, length(player_names))
player_names <- str_trim(player_names, side = 'right')
chess_results_table$Player_Name <- player_names
head(chess_results_table)## player_ID Pair_Num Player_Name
## 1 15445895 1 GARY HUA
## 2 14598900 2 DAKSHESH DARURI
## 3 14959604 3 ADITYA BAJAJ
## 4 12616049 4 PATRICK H SCHILLING
## 5 14601533 5 HANSHI ZUO
## 6 15055204 6 HANSEN SONGExtract player state
player_state <- str_extract(Line2, "[[:alpha:]]{2}")
chess_results_table$State <- player_state
head(chess_results_table)## player_ID Pair_Num Player_Name State
## 1 15445895 1 GARY HUA ON
## 2 14598900 2 DAKSHESH DARURI MI
## 3 14959604 3 ADITYA BAJAJ MI
## 4 12616049 4 PATRICK H SCHILLING MI
## 5 14601533 5 HANSHI ZUO MI
## 6 15055204 6 HANSEN SONG OHExtract player points
player_points <- str_extract(Line1, "[[:digit:]]\\.[[:digit:]]")
chess_results_table$Points <- as.numeric(player_points)
head(chess_results_table)## player_ID Pair_Num Player_Name State Points
## 1 15445895 1 GARY HUA ON 6.0
## 2 14598900 2 DAKSHESH DARURI MI 6.0
## 3 14959604 3 ADITYA BAJAJ MI 6.0
## 4 12616049 4 PATRICK H SCHILLING MI 5.5
## 5 14601533 5 HANSHI ZUO MI 5.5
## 6 15055204 6 HANSEN SONG OH 5.0Extract each player’s rating
pre_rating <- str_extract(Line2, "R:[[:space:]]{1,2}[[:digit:]]{3,4}")
pre_rating <- str_extract(pre_rating, "[[:digit:]]{3,4}")
chess_results_table$Pre_Rating <- as.numeric(pre_rating)
head(chess_results_table)## player_ID Pair_Num Player_Name State Points Pre_Rating
## 1 15445895 1 GARY HUA ON 6.0 1794
## 2 14598900 2 DAKSHESH DARURI MI 6.0 1553
## 3 14959604 3 ADITYA BAJAJ MI 6.0 1384
## 4 12616049 4 PATRICK H SCHILLING MI 5.5 1716
## 5 14601533 5 HANSHI ZUO MI 5.5 1655
## 6 15055204 6 HANSEN SONG OH 5.0 1686Based on the amount of matches each player has played, compute the average rating for all of each’s player’s opponents
opps_played <- str_extract_all(Line1, "[W,L,D,U][[:space:]]{2}[[[:digit:]][[:space:]]][[:digit:]]")
opps <- suppressWarnings(str_extract_all(opps_played, "[[:digit:]]{1,2}"))
avg_rating <- vector()
for(i in 1:nrow(chess_results_table))
{
avg_rating[i] <- mean(chess_results_table$Pre_Rating[as.integer(opps[[i]])])
}
chess_results_table$Opp_Avg_Rating <- avg_ratingDisplay the final table in a format that can be imported into a database
library(kableExtra)
kable(chess_results_table) %>% kable_styling(bootstrap_options = c("striped", "hover"))| player_ID | Pair_Num | Player_Name | State | Points | Pre_Rating | Opp_Avg_Rating |
|---|---|---|---|---|---|---|
| 15445895 | 1 | GARY HUA | ON | 6.0 | 1794 | 1605.286 |
| 14598900 | 2 | DAKSHESH DARURI | MI | 6.0 | 1553 | 1469.286 |
| 14959604 | 3 | ADITYA BAJAJ | MI | 6.0 | 1384 | 1563.571 |
| 12616049 | 4 | PATRICK H SCHILLING | MI | 5.5 | 1716 | 1573.571 |
| 14601533 | 5 | HANSHI ZUO | MI | 5.5 | 1655 | 1500.857 |
| 15055204 | 6 | HANSEN SONG | OH | 5.0 | 1686 | 1518.714 |
| 11146376 | 7 | GARY DEE SWATHELL | MI | 5.0 | 1649 | 1372.143 |
| 15142253 | 8 | EZEKIEL HOUGHTON | MI | 5.0 | 1641 | 1468.429 |
| 14954524 | 9 | STEFANO LEE | ON | 5.0 | 1411 | 1523.143 |
| 14150362 | 10 | ANVIT RAO | MI | 5.0 | 1365 | 1554.143 |
| 12581589 | 11 | CAMERON WILLIAM MC LEMAN | MI | 4.5 | 1712 | 1467.571 |
| 12681257 | 12 | KENNETH J TACK | MI | 4.5 | 1663 | 1506.167 |
| 15082995 | 13 | TORRANCE HENRY JR | MI | 4.5 | 1666 | 1497.857 |
| 10131499 | 14 | BRADLEY SHAW | MI | 4.5 | 1610 | 1515.000 |
| 15619130 | 15 | ZACHARY JAMES HOUGHTON | MI | 4.5 | 1220 | 1483.857 |
| 10295068 | 16 | MIKE NIKITIN | MI | 4.0 | 1604 | 1385.800 |
| 10297702 | 17 | RONALD GRZEGORCZYK | MI | 4.0 | 1629 | 1498.571 |
| 11342094 | 18 | DAVID SUNDEEN | MI | 4.0 | 1600 | 1480.000 |
| 14862333 | 19 | DIPANKAR ROY | MI | 4.0 | 1564 | 1426.286 |
| 14529060 | 20 | JASON ZHENG | MI | 4.0 | 1595 | 1410.857 |
| 15495066 | 21 | DINH DANG BUI | ON | 4.0 | 1563 | 1470.429 |
| 12405534 | 22 | EUGENE L MCCLURE | MI | 4.0 | 1555 | 1300.333 |
| 15030142 | 23 | ALAN BUI | ON | 4.0 | 1363 | 1213.857 |
| 13469010 | 24 | MICHAEL R ALDRICH | MI | 4.0 | 1229 | 1357.000 |
| 12486656 | 25 | LOREN SCHWIEBERT | MI | 3.5 | 1745 | 1363.286 |
| 15131520 | 26 | MAX ZHU | ON | 3.5 | 1579 | 1506.857 |
| 14476567 | 27 | GAURAV GIDWANI | MI | 3.5 | 1552 | 1221.667 |
| 14882954 | 28 | SOFIA ADINA STANESCU-BELLU | MI | 3.5 | 1507 | 1522.143 |
| 15323285 | 29 | CHIEDOZIE OKORIE | MI | 3.5 | 1602 | 1313.500 |
| 12577178 | 30 | GEORGE AVERY JONES | ON | 3.5 | 1522 | 1144.143 |
| 15131618 | 31 | RISHI SHETTY | MI | 3.5 | 1494 | 1259.857 |
| 14073750 | 32 | JOSHUA PHILIP MATHEWS | ON | 3.5 | 1441 | 1378.714 |
| 14691842 | 33 | JADE GE | MI | 3.5 | 1449 | 1276.857 |
| 15051807 | 34 | MICHAEL JEFFERY THOMAS | MI | 3.5 | 1399 | 1375.286 |
| 14601397 | 35 | JOSHUA DAVID LEE | MI | 3.5 | 1438 | 1149.714 |
| 14773163 | 36 | SIDDHARTH JHA | MI | 3.5 | 1355 | 1388.167 |
| 15489571 | 37 | AMIYATOSH PWNANANDAM | MI | 3.5 | 980 | 1384.800 |
| 15108523 | 38 | BRIAN LIU | MI | 3.0 | 1423 | 1539.167 |
| 12923035 | 39 | JOEL R HENDON | MI | 3.0 | 1436 | 1429.571 |
| 14892710 | 40 | FOREST ZHANG | MI | 3.0 | 1348 | 1390.571 |
| 15761443 | 41 | KYLE WILLIAM MURPHY | MI | 3.0 | 1403 | 1248.500 |
| 14462326 | 42 | JARED GE | MI | 3.0 | 1332 | 1149.857 |
| 14101068 | 43 | ROBERT GLEN VASEY | MI | 3.0 | 1283 | 1106.571 |
| 15323504 | 44 | JUSTIN D SCHILLING | MI | 3.0 | 1199 | 1327.000 |
| 15372807 | 45 | DEREK YAN | MI | 3.0 | 1242 | 1152.000 |
| 15490981 | 46 | JACOB ALEXANDER LAVALLEY | MI | 3.0 | 377 | 1357.714 |
| 12533115 | 47 | ERIC WRIGHT | MI | 2.5 | 1362 | 1392.000 |
| 14369165 | 48 | DANIEL KHAIN | MI | 2.5 | 1382 | 1355.800 |
| 12531685 | 49 | MICHAEL J MARTIN | MI | 2.5 | 1291 | 1285.800 |
| 14773178 | 50 | SHIVAM JHA | MI | 2.5 | 1056 | 1296.000 |
| 15205474 | 51 | TEJAS AYYAGARI | MI | 2.5 | 1011 | 1356.143 |
| 14918803 | 52 | ETHAN GUO | MI | 2.5 | 935 | 1494.571 |
| 12578849 | 53 | JOSE C YBARRA | MI | 2.0 | 1393 | 1345.333 |
| 12836773 | 54 | LARRY HODGE | MI | 2.0 | 1270 | 1206.167 |
| 15412571 | 55 | ALEX KONG | MI | 2.0 | 1186 | 1406.000 |
| 14679887 | 56 | MARISA RICCI | MI | 2.0 | 1153 | 1414.400 |
| 15113330 | 57 | MICHAEL LU | MI | 2.0 | 1092 | 1363.000 |
| 14700365 | 58 | VIRAJ MOHILE | MI | 2.0 | 917 | 1391.000 |
| 12841036 | 59 | SEAN M MC CORMICK | MI | 2.0 | 853 | 1319.000 |
| 14579262 | 60 | JULIA SHEN | MI | 1.5 | 967 | 1330.200 |
| 15771592 | 61 | JEZZEL FARKAS | ON | 1.5 | 955 | 1327.286 |
| 15219542 | 62 | ASHWIN BALAJI | MI | 1.0 | 1530 | 1186.000 |
| 15057092 | 63 | THOMAS JOSEPH HOSMER | MI | 1.0 | 1175 | 1350.200 |
| 15006561 | 64 | BEN LI | MI | 1.0 | 1163 | 1263.000 |
Produce a CSV file, which can be imporated into a database
write.csv(chess_results_table, file = "ChessResults.csv", row.names = FALSE )