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, and Average Pre Chess Rating of Opponents
The first step is to extract the file from Github for transformation and analysis. I will do this using the dplyr package:
library("dplyr")##
## Attaching package: 'dplyr'## The following objects are masked from 'package:stats':
##
## filter, lag## The following objects are masked from 'package:base':
##
## intersect, setdiff, setequal, unionlibrary("stringr")
url <- read.delim ("https://raw.githubusercontent.com/AtinaKarim/DATA607/master/tournamentinfo.txt")
url## X.........................................................................................
## 1 Pair | Player Name |Total|Round|Round|Round|Round|Round|Round|Round|
## 2 Num | USCF ID / Rtg (Pre->Post) | Pts | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
## 3 -----------------------------------------------------------------------------------------
## 4 1 | GARY HUA |6.0 |W 39|W 21|W 18|W 14|W 7|D 12|D 4|
## 5 ON | 15445895 / R: 1794 ->1817 |N:2 |W |B |W |B |W |B |W |
## 6 -----------------------------------------------------------------------------------------
## 7 2 | DAKSHESH DARURI |6.0 |W 63|W 58|L 4|W 17|W 16|W 20|W 7|
## 8 MI | 14598900 / R: 1553 ->1663 |N:2 |B |W |B |W |B |W |B |
## 9 -----------------------------------------------------------------------------------------
## 10 3 | ADITYA BAJAJ |6.0 |L 8|W 61|W 25|W 21|W 11|W 13|W 12|
## 11 MI | 14959604 / R: 1384 ->1640 |N:2 |W |B |W |B |W |B |W |
## 12 -----------------------------------------------------------------------------------------
## 13 4 | PATRICK H SCHILLING |5.5 |W 23|D 28|W 2|W 26|D 5|W 19|D 1|
## 14 MI | 12616049 / R: 1716 ->1744 |N:2 |W |B |W |B |W |B |B |
## 15 -----------------------------------------------------------------------------------------
## 16 5 | HANSHI ZUO |5.5 |W 45|W 37|D 12|D 13|D 4|W 14|W 17|
## 17 MI | 14601533 / R: 1655 ->1690 |N:2 |B |W |B |W |B |W |B |
## 18 -----------------------------------------------------------------------------------------
## 19 6 | HANSEN SONG |5.0 |W 34|D 29|L 11|W 35|D 10|W 27|W 21|
## 20 OH | 15055204 / R: 1686 ->1687 |N:3 |W |B |W |B |B |W |B |
## 21 -----------------------------------------------------------------------------------------
## 22 7 | GARY DEE SWATHELL |5.0 |W 57|W 46|W 13|W 11|L 1|W 9|L 2|
## 23 MI | 11146376 / R: 1649 ->1673 |N:3 |W |B |W |B |B |W |W |
## 24 -----------------------------------------------------------------------------------------
## 25 8 | EZEKIEL HOUGHTON |5.0 |W 3|W 32|L 14|L 9|W 47|W 28|W 19|
## 26 MI | 15142253 / R: 1641P17->1657P24 |N:3 |B |W |B |W |B |W |W |
## 27 -----------------------------------------------------------------------------------------
## 28 9 | STEFANO LEE |5.0 |W 25|L 18|W 59|W 8|W 26|L 7|W 20|
## 29 ON | 14954524 / R: 1411 ->1564 |N:2 |W |B |W |B |W |B |B |
## 30 -----------------------------------------------------------------------------------------
## 31 10 | ANVIT RAO |5.0 |D 16|L 19|W 55|W 31|D 6|W 25|W 18|
## 32 MI | 14150362 / R: 1365 ->1544 |N:3 |W |W |B |B |W |B |W |
## 33 -----------------------------------------------------------------------------------------
## 34 11 | CAMERON WILLIAM MC LEMAN |4.5 |D 38|W 56|W 6|L 7|L 3|W 34|W 26|
## 35 MI | 12581589 / R: 1712 ->1696 |N:3 |B |W |B |W |B |W |B |
## 36 -----------------------------------------------------------------------------------------
## 37 12 | KENNETH J TACK |4.5 |W 42|W 33|D 5|W 38|H |D 1|L 3|
## 38 MI | 12681257 / R: 1663 ->1670 |N:3 |W |B |W |B | |W |B |
## 39 -----------------------------------------------------------------------------------------
## 40 13 | TORRANCE HENRY JR |4.5 |W 36|W 27|L 7|D 5|W 33|L 3|W 32|
## 41 MI | 15082995 / R: 1666 ->1662 |N:3 |B |W |B |B |W |W |B |
## 42 -----------------------------------------------------------------------------------------
## 43 14 | BRADLEY SHAW |4.5 |W 54|W 44|W 8|L 1|D 27|L 5|W 31|
## 44 MI | 10131499 / R: 1610 ->1618 |N:3 |W |B |W |W |B |B |W |
## 45 -----------------------------------------------------------------------------------------
## 46 15 | ZACHARY JAMES HOUGHTON |4.5 |D 19|L 16|W 30|L 22|W 54|W 33|W 38|
## 47 MI | 15619130 / R: 1220P13->1416P20 |N:3 |B |B |W |W |B |B |W |
## 48 -----------------------------------------------------------------------------------------
## 49 16 | MIKE NIKITIN |4.0 |D 10|W 15|H |W 39|L 2|W 36|U |
## 50 MI | 10295068 / R: 1604 ->1613 |N:3 |B |W | |B |W |B | |
## 51 -----------------------------------------------------------------------------------------
## 52 17 | RONALD GRZEGORCZYK |4.0 |W 48|W 41|L 26|L 2|W 23|W 22|L 5|
## 53 MI | 10297702 / R: 1629 ->1610 |N:3 |W |B |W |B |W |B |W |
## 54 -----------------------------------------------------------------------------------------
## 55 18 | DAVID SUNDEEN |4.0 |W 47|W 9|L 1|W 32|L 19|W 38|L 10|
## 56 MI | 11342094 / R: 1600 ->1600 |N:3 |B |W |B |W |B |W |B |
## 57 -----------------------------------------------------------------------------------------
## 58 19 | DIPANKAR ROY |4.0 |D 15|W 10|W 52|D 28|W 18|L 4|L 8|
## 59 MI | 14862333 / R: 1564 ->1570 |N:3 |W |B |W |B |W |W |B |
## 60 -----------------------------------------------------------------------------------------
## 61 20 | JASON ZHENG |4.0 |L 40|W 49|W 23|W 41|W 28|L 2|L 9|
## 62 MI | 14529060 / R: 1595 ->1569 |N:4 |W |B |W |B |W |B |W |
## 63 -----------------------------------------------------------------------------------------
## 64 21 | DINH DANG BUI |4.0 |W 43|L 1|W 47|L 3|W 40|W 39|L 6|
## 65 ON | 15495066 / R: 1563P22->1562 |N:3 |B |W |B |W |W |B |W |
## 66 -----------------------------------------------------------------------------------------
## 67 22 | EUGENE L MCCLURE |4.0 |W 64|D 52|L 28|W 15|H |L 17|W 40|
## 68 MI | 12405534 / R: 1555 ->1529 |N:4 |W |B |W |B | |W |B |
## 69 -----------------------------------------------------------------------------------------
## 70 23 | ALAN BUI |4.0 |L 4|W 43|L 20|W 58|L 17|W 37|W 46|
## 71 ON | 15030142 / R: 1363 ->1371 | |B |W |B |W |B |W |B |
## 72 -----------------------------------------------------------------------------------------
## 73 24 | MICHAEL R ALDRICH |4.0 |L 28|L 47|W 43|L 25|W 60|W 44|W 39|
## 74 MI | 13469010 / R: 1229 ->1300 |N:4 |B |W |B |B |W |W |B |
## 75 -----------------------------------------------------------------------------------------
## 76 25 | LOREN SCHWIEBERT |3.5 |L 9|W 53|L 3|W 24|D 34|L 10|W 47|
## 77 MI | 12486656 / R: 1745 ->1681 |N:4 |B |W |B |W |B |W |B |
## 78 -----------------------------------------------------------------------------------------
## 79 26 | MAX ZHU |3.5 |W 49|W 40|W 17|L 4|L 9|D 32|L 11|
## 80 ON | 15131520 / R: 1579 ->1564 |N:4 |B |W |B |W |B |W |W |
## 81 -----------------------------------------------------------------------------------------
## 82 27 | GAURAV GIDWANI |3.5 |W 51|L 13|W 46|W 37|D 14|L 6|U |
## 83 MI | 14476567 / R: 1552 ->1539 |N:4 |W |B |W |B |W |B | |
## 84 -----------------------------------------------------------------------------------------
## 85 28 | SOFIA ADINA STANESCU-BELLU |3.5 |W 24|D 4|W 22|D 19|L 20|L 8|D 36|
## 86 MI | 14882954 / R: 1507 ->1513 |N:3 |W |W |B |W |B |B |W |
## 87 -----------------------------------------------------------------------------------------
## 88 29 | CHIEDOZIE OKORIE |3.5 |W 50|D 6|L 38|L 34|W 52|W 48|U |
## 89 MI | 15323285 / R: 1602P6 ->1508P12 |N:4 |B |W |B |W |W |B | |
## 90 -----------------------------------------------------------------------------------------
## 91 30 | GEORGE AVERY JONES |3.5 |L 52|D 64|L 15|W 55|L 31|W 61|W 50|
## 92 ON | 12577178 / R: 1522 ->1444 | |W |B |B |W |W |B |B |
## 93 -----------------------------------------------------------------------------------------
## 94 31 | RISHI SHETTY |3.5 |L 58|D 55|W 64|L 10|W 30|W 50|L 14|
## 95 MI | 15131618 / R: 1494 ->1444 | |B |W |B |W |B |W |B |
## 96 -----------------------------------------------------------------------------------------
## 97 32 | JOSHUA PHILIP MATHEWS |3.5 |W 61|L 8|W 44|L 18|W 51|D 26|L 13|
## 98 ON | 14073750 / R: 1441 ->1433 |N:4 |W |B |W |B |W |B |W |
## 99 -----------------------------------------------------------------------------------------
## 100 33 | JADE GE |3.5 |W 60|L 12|W 50|D 36|L 13|L 15|W 51|
## 101 MI | 14691842 / R: 1449 ->1421 | |B |W |B |W |B |W |B |
## 102 -----------------------------------------------------------------------------------------
## 103 34 | MICHAEL JEFFERY THOMAS |3.5 |L 6|W 60|L 37|W 29|D 25|L 11|W 52|
## 104 MI | 15051807 / R: 1399 ->1400 | |B |W |B |B |W |B |W |
## 105 -----------------------------------------------------------------------------------------
## 106 35 | JOSHUA DAVID LEE |3.5 |L 46|L 38|W 56|L 6|W 57|D 52|W 48|
## 107 MI | 14601397 / R: 1438 ->1392 | |W |W |B |W |B |B |W |
## 108 -----------------------------------------------------------------------------------------
## 109 36 | SIDDHARTH JHA |3.5 |L 13|W 57|W 51|D 33|H |L 16|D 28|
## 110 MI | 14773163 / R: 1355 ->1367 |N:4 |W |B |W |B | |W |B |
## 111 -----------------------------------------------------------------------------------------
## 112 37 | AMIYATOSH PWNANANDAM |3.5 |B |L 5|W 34|L 27|H |L 23|W 61|
## 113 MI | 15489571 / R: 980P12->1077P17 | | |B |W |W | |B |W |
## 114 -----------------------------------------------------------------------------------------
## 115 38 | BRIAN LIU |3.0 |D 11|W 35|W 29|L 12|H |L 18|L 15|
## 116 MI | 15108523 / R: 1423 ->1439 |N:4 |W |B |W |W | |B |B |
## 117 -----------------------------------------------------------------------------------------
## 118 39 | JOEL R HENDON |3.0 |L 1|W 54|W 40|L 16|W 44|L 21|L 24|
## 119 MI | 12923035 / R: 1436P23->1413 |N:4 |B |W |B |W |B |W |W |
## 120 -----------------------------------------------------------------------------------------
## 121 40 | FOREST ZHANG |3.0 |W 20|L 26|L 39|W 59|L 21|W 56|L 22|
## 122 MI | 14892710 / R: 1348 ->1346 | |B |B |W |W |B |W |W |
## 123 -----------------------------------------------------------------------------------------
## 124 41 | KYLE WILLIAM MURPHY |3.0 |W 59|L 17|W 58|L 20|X |U |U |
## 125 MI | 15761443 / R: 1403P5 ->1341P9 | |B |W |B |W | | | |
## 126 -----------------------------------------------------------------------------------------
## 127 42 | JARED GE |3.0 |L 12|L 50|L 57|D 60|D 61|W 64|W 56|
## 128 MI | 14462326 / R: 1332 ->1256 | |B |W |B |B |W |W |B |
## 129 -----------------------------------------------------------------------------------------
## 130 43 | ROBERT GLEN VASEY |3.0 |L 21|L 23|L 24|W 63|W 59|L 46|W 55|
## 131 MI | 14101068 / R: 1283 ->1244 | |W |B |W |W |B |B |W |
## 132 -----------------------------------------------------------------------------------------
## 133 44 | JUSTIN D SCHILLING |3.0 |B |L 14|L 32|W 53|L 39|L 24|W 59|
## 134 MI | 15323504 / R: 1199 ->1199 | | |W |B |B |W |B |W |
## 135 -----------------------------------------------------------------------------------------
## 136 45 | DEREK YAN |3.0 |L 5|L 51|D 60|L 56|W 63|D 55|W 58|
## 137 MI | 15372807 / R: 1242 ->1191 | |W |B |W |B |W |B |W |
## 138 -----------------------------------------------------------------------------------------
## 139 46 | JACOB ALEXANDER LAVALLEY |3.0 |W 35|L 7|L 27|L 50|W 64|W 43|L 23|
## 140 MI | 15490981 / R: 377P3 ->1076P10 | |B |W |B |W |B |W |W |
## 141 -----------------------------------------------------------------------------------------
## 142 47 | ERIC WRIGHT |2.5 |L 18|W 24|L 21|W 61|L 8|D 51|L 25|
## 143 MI | 12533115 / R: 1362 ->1341 | |W |B |W |B |W |B |W |
## 144 -----------------------------------------------------------------------------------------
## 145 48 | DANIEL KHAIN |2.5 |L 17|W 63|H |D 52|H |L 29|L 35|
## 146 MI | 14369165 / R: 1382 ->1335 | |B |W | |B | |W |B |
## 147 -----------------------------------------------------------------------------------------
## 148 49 | MICHAEL J MARTIN |2.5 |L 26|L 20|D 63|D 64|W 58|H |U |
## 149 MI | 12531685 / R: 1291P12->1259P17 | |W |W |B |W |B | | |
## 150 -----------------------------------------------------------------------------------------
## 151 50 | SHIVAM JHA |2.5 |L 29|W 42|L 33|W 46|H |L 31|L 30|
## 152 MI | 14773178 / R: 1056 ->1111 | |W |B |W |B | |B |W |
## 153 -----------------------------------------------------------------------------------------
## 154 51 | TEJAS AYYAGARI |2.5 |L 27|W 45|L 36|W 57|L 32|D 47|L 33|
## 155 MI | 15205474 / R: 1011 ->1097 | |B |W |B |W |B |W |W |
## 156 -----------------------------------------------------------------------------------------
## 157 52 | ETHAN GUO |2.5 |W 30|D 22|L 19|D 48|L 29|D 35|L 34|
## 158 MI | 14918803 / R: 935 ->1092 |N:4 |B |W |B |W |B |W |B |
## 159 -----------------------------------------------------------------------------------------
## 160 53 | JOSE C YBARRA |2.0 |H |L 25|H |L 44|U |W 57|U |
## 161 MI | 12578849 / R: 1393 ->1359 | | |B | |W | |W | |
## 162 -----------------------------------------------------------------------------------------
## 163 54 | LARRY HODGE |2.0 |L 14|L 39|L 61|B |L 15|L 59|W 64|
## 164 MI | 12836773 / R: 1270 ->1200 | |B |B |W | |W |B |W |
## 165 -----------------------------------------------------------------------------------------
## 166 55 | ALEX KONG |2.0 |L 62|D 31|L 10|L 30|B |D 45|L 43|
## 167 MI | 15412571 / R: 1186 ->1163 | |W |B |W |B | |W |B |
## 168 -----------------------------------------------------------------------------------------
## 169 56 | MARISA RICCI |2.0 |H |L 11|L 35|W 45|H |L 40|L 42|
## 170 MI | 14679887 / R: 1153 ->1140 | | |B |W |W | |B |W |
## 171 -----------------------------------------------------------------------------------------
## 172 57 | MICHAEL LU |2.0 |L 7|L 36|W 42|L 51|L 35|L 53|B |
## 173 MI | 15113330 / R: 1092 ->1079 | |B |W |W |B |W |B | |
## 174 -----------------------------------------------------------------------------------------
## 175 58 | VIRAJ MOHILE |2.0 |W 31|L 2|L 41|L 23|L 49|B |L 45|
## 176 MI | 14700365 / R: 917 -> 941 | |W |B |W |B |W | |B |
## 177 -----------------------------------------------------------------------------------------
## 178 59 | SEAN M MC CORMICK |2.0 |L 41|B |L 9|L 40|L 43|W 54|L 44|
## 179 MI | 12841036 / R: 853 -> 878 | |W | |B |B |W |W |B |
## 180 -----------------------------------------------------------------------------------------
## 181 60 | JULIA SHEN |1.5 |L 33|L 34|D 45|D 42|L 24|H |U |
## 182 MI | 14579262 / R: 967 -> 984 | |W |B |B |W |B | | |
## 183 -----------------------------------------------------------------------------------------
## 184 61 | JEZZEL FARKAS |1.5 |L 32|L 3|W 54|L 47|D 42|L 30|L 37|
## 185 ON | 15771592 / R: 955P11-> 979P18 | |B |W |B |W |B |W |B |
## 186 -----------------------------------------------------------------------------------------
## 187 62 | ASHWIN BALAJI |1.0 |W 55|U |U |U |U |U |U |
## 188 MI | 15219542 / R: 1530 ->1535 | |B | | | | | | |
## 189 -----------------------------------------------------------------------------------------
## 190 63 | THOMAS JOSEPH HOSMER |1.0 |L 2|L 48|D 49|L 43|L 45|H |U |
## 191 MI | 15057092 / R: 1175 ->1125 | |W |B |W |B |B | | |
## 192 -----------------------------------------------------------------------------------------
## 193 64 | BEN LI |1.0 |L 22|D 30|L 31|D 49|L 46|L 42|L 54|
## 194 MI | 15006561 / R: 1163 ->1112 | |B |W |W |B |W |B |B |
## 195 -----------------------------------------------------------------------------------------Player Name The player name appears in the text file in the following order: “id | Player Name |”. This is the logic I will follow to build the regexp pattern for player name.
pattern <- "\\d\\s\\| (\\w*+\\s\\w*+\\s?\\w*?) |$"
playername <- str_match(url$X........................................................................................., pattern)
playername <- na.omit(playername)The code above returns two columns for Player Name - we can fix this once we have incorporated this information into a data frame.
Player State or Province All 64 players are either from Ontario or Michigan or Ohio. So we can ask the code to look for one of these states/provinces from the text, followed by the “|”. We will also remove any missing values from the result:
library(dplyr)
library(stringr)
pattern <- "(ON|MI|OH) \\|"
playerstate <- str_match(url$X........................................................................................., pattern)
playerstate <- na.omit(playerstate)We have the list of states for all the 64 players. They appear in the order the names are appearing in Player Name - so they should correspond once we create a data frame with our variables.
Player Points
The points are listed immediately after the “|” and are up to one decimal point (e.g- 6.0). They are followed by white space and then “|”:
pattern <- "\\d+\\.\\d+"
TotalPoints <- str_match(url$X........................................................................................., pattern)
TotalPoints <- na.omit(TotalPoints)Player Pre-rating The pre ratings for each player is preceded by ‘R:’:
pattern <- "R:\\s*\\d*"
PreRating <- str_match(url$X........................................................................................., pattern)
PreRating <- na.omit(PreRating)
PreRating <- str_remove(PreRating,"R:")df<- data.frame(playername,playerstate,TotalPoints,PreRating)
df## X1 X2 X1.1 X2.1 TotalPoints
## 1 1 | GARY HUA GARY HUA ON | ON 6.0
## 2 2 | DAKSHESH DARURI DAKSHESH DARURI MI | MI 6.0
## 3 3 | ADITYA BAJAJ ADITYA BAJAJ MI | MI 6.0
## 4 4 | PATRICK H SCHILLING PATRICK H SCHILLING MI | MI 5.5
## 5 5 | HANSHI ZUO HANSHI ZUO MI | MI 5.5
## 6 6 | HANSEN SONG HANSEN SONG OH | OH 5.0
## 7 7 | GARY DEE SWATHELL GARY DEE SWATHELL MI | MI 5.0
## 8 8 | EZEKIEL HOUGHTON EZEKIEL HOUGHTON MI | MI 5.0
## 9 9 | STEFANO LEE STEFANO LEE ON | ON 5.0
## 10 0 | ANVIT RAO ANVIT RAO MI | MI 5.0
## 11 1 | CAMERON WILLIAM MC CAMERON WILLIAM MC MI | MI 4.5
## 12 2 | KENNETH J TACK KENNETH J TACK MI | MI 4.5
## 13 3 | TORRANCE HENRY JR TORRANCE HENRY JR MI | MI 4.5
## 14 4 | BRADLEY SHAW BRADLEY SHAW MI | MI 4.5
## 15 5 | ZACHARY JAMES HOUGHTON ZACHARY JAMES HOUGHTON MI | MI 4.5
## 16 6 | MIKE NIKITIN MIKE NIKITIN MI | MI 4.0
## 17 7 | RONALD GRZEGORCZYK RONALD GRZEGORCZYK MI | MI 4.0
## 18 8 | DAVID SUNDEEN DAVID SUNDEEN MI | MI 4.0
## 19 9 | DIPANKAR ROY DIPANKAR ROY MI | MI 4.0
## 20 0 | JASON ZHENG JASON ZHENG MI | MI 4.0
## 21 1 | DINH DANG BUI DINH DANG BUI ON | ON 4.0
## 22 2 | EUGENE L MCCLURE EUGENE L MCCLURE MI | MI 4.0
## 23 3 | ALAN BUI ALAN BUI ON | ON 4.0
## 24 4 | MICHAEL R ALDRICH MICHAEL R ALDRICH MI | MI 4.0
## 25 5 | LOREN SCHWIEBERT LOREN SCHWIEBERT MI | MI 3.5
## 26 6 | MAX ZHU MAX ZHU ON | ON 3.5
## 27 7 | GAURAV GIDWANI GAURAV GIDWANI MI | MI 3.5
## 28 8 | SOFIA ADINA SOFIA ADINA MI | MI 3.5
## 29 9 | CHIEDOZIE OKORIE CHIEDOZIE OKORIE MI | MI 3.5
## 30 0 | GEORGE AVERY JONES GEORGE AVERY JONES ON | ON 3.5
## 31 1 | RISHI SHETTY RISHI SHETTY MI | MI 3.5
## 32 2 | JOSHUA PHILIP MATHEWS JOSHUA PHILIP MATHEWS ON | ON 3.5
## 33 3 | JADE GE JADE GE MI | MI 3.5
## 34 4 | MICHAEL JEFFERY THOMAS MICHAEL JEFFERY THOMAS MI | MI 3.5
## 35 5 | JOSHUA DAVID LEE JOSHUA DAVID LEE MI | MI 3.5
## 36 6 | SIDDHARTH JHA SIDDHARTH JHA MI | MI 3.5
## 37 7 | AMIYATOSH PWNANANDAM AMIYATOSH PWNANANDAM MI | MI 3.5
## 38 8 | BRIAN LIU BRIAN LIU MI | MI 3.0
## 39 9 | JOEL R HENDON JOEL R HENDON MI | MI 3.0
## 40 0 | FOREST ZHANG FOREST ZHANG MI | MI 3.0
## 41 1 | KYLE WILLIAM MURPHY KYLE WILLIAM MURPHY MI | MI 3.0
## 42 2 | JARED GE JARED GE MI | MI 3.0
## 43 3 | ROBERT GLEN VASEY ROBERT GLEN VASEY MI | MI 3.0
## 44 4 | JUSTIN D SCHILLING JUSTIN D SCHILLING MI | MI 3.0
## 45 5 | DEREK YAN DEREK YAN MI | MI 3.0
## 46 6 | JACOB ALEXANDER LAVALLEY JACOB ALEXANDER LAVALLEY MI | MI 3.0
## 47 7 | ERIC WRIGHT ERIC WRIGHT MI | MI 2.5
## 48 8 | DANIEL KHAIN DANIEL KHAIN MI | MI 2.5
## 49 9 | MICHAEL J MARTIN MICHAEL J MARTIN MI | MI 2.5
## 50 0 | SHIVAM JHA SHIVAM JHA MI | MI 2.5
## 51 1 | TEJAS AYYAGARI TEJAS AYYAGARI MI | MI 2.5
## 52 2 | ETHAN GUO ETHAN GUO MI | MI 2.5
## 53 3 | JOSE C YBARRA JOSE C YBARRA MI | MI 2.0
## 54 4 | LARRY HODGE LARRY HODGE MI | MI 2.0
## 55 5 | ALEX KONG ALEX KONG MI | MI 2.0
## 56 6 | MARISA RICCI MARISA RICCI MI | MI 2.0
## 57 7 | MICHAEL LU MICHAEL LU MI | MI 2.0
## 58 8 | VIRAJ MOHILE VIRAJ MOHILE MI | MI 2.0
## 59 9 | SEAN M MC SEAN M MC MI | MI 2.0
## 60 0 | JULIA SHEN JULIA SHEN MI | MI 1.5
## 61 1 | JEZZEL FARKAS JEZZEL FARKAS ON | ON 1.5
## 62 2 | ASHWIN BALAJI ASHWIN BALAJI MI | MI 1.0
## 63 3 | THOMAS JOSEPH HOSMER THOMAS JOSEPH HOSMER MI | MI 1.0
## 64 4 | BEN LI BEN LI MI | MI 1.0
## PreRating
## 1 1794
## 2 1553
## 3 1384
## 4 1716
## 5 1655
## 6 1686
## 7 1649
## 8 1641
## 9 1411
## 10 1365
## 11 1712
## 12 1663
## 13 1666
## 14 1610
## 15 1220
## 16 1604
## 17 1629
## 18 1600
## 19 1564
## 20 1595
## 21 1563
## 22 1555
## 23 1363
## 24 1229
## 25 1745
## 26 1579
## 27 1552
## 28 1507
## 29 1602
## 30 1522
## 31 1494
## 32 1441
## 33 1449
## 34 1399
## 35 1438
## 36 1355
## 37 980
## 38 1423
## 39 1436
## 40 1348
## 41 1403
## 42 1332
## 43 1283
## 44 1199
## 45 1242
## 46 377
## 47 1362
## 48 1382
## 49 1291
## 50 1056
## 51 1011
## 52 935
## 53 1393
## 54 1270
## 55 1186
## 56 1153
## 57 1092
## 58 917
## 59 853
## 60 967
## 61 955
## 62 1530
## 63 1175
## 64 1163Adding player ID and dropping additional columns
df = subset(df, select = -c(X1,X1.1) )
df <- df %>% mutate(id = row_number())
df <-df %>%
rename(
"Player Name"=X2,
"Player State"=X2.1
)
df## Player Name Player State TotalPoints PreRating id
## 1 GARY HUA ON 6.0 1794 1
## 2 DAKSHESH DARURI MI 6.0 1553 2
## 3 ADITYA BAJAJ MI 6.0 1384 3
## 4 PATRICK H SCHILLING MI 5.5 1716 4
## 5 HANSHI ZUO MI 5.5 1655 5
## 6 HANSEN SONG OH 5.0 1686 6
## 7 GARY DEE SWATHELL MI 5.0 1649 7
## 8 EZEKIEL HOUGHTON MI 5.0 1641 8
## 9 STEFANO LEE ON 5.0 1411 9
## 10 ANVIT RAO MI 5.0 1365 10
## 11 CAMERON WILLIAM MC MI 4.5 1712 11
## 12 KENNETH J TACK MI 4.5 1663 12
## 13 TORRANCE HENRY JR MI 4.5 1666 13
## 14 BRADLEY SHAW MI 4.5 1610 14
## 15 ZACHARY JAMES HOUGHTON MI 4.5 1220 15
## 16 MIKE NIKITIN MI 4.0 1604 16
## 17 RONALD GRZEGORCZYK MI 4.0 1629 17
## 18 DAVID SUNDEEN MI 4.0 1600 18
## 19 DIPANKAR ROY MI 4.0 1564 19
## 20 JASON ZHENG MI 4.0 1595 20
## 21 DINH DANG BUI ON 4.0 1563 21
## 22 EUGENE L MCCLURE MI 4.0 1555 22
## 23 ALAN BUI ON 4.0 1363 23
## 24 MICHAEL R ALDRICH MI 4.0 1229 24
## 25 LOREN SCHWIEBERT MI 3.5 1745 25
## 26 MAX ZHU ON 3.5 1579 26
## 27 GAURAV GIDWANI MI 3.5 1552 27
## 28 SOFIA ADINA MI 3.5 1507 28
## 29 CHIEDOZIE OKORIE MI 3.5 1602 29
## 30 GEORGE AVERY JONES ON 3.5 1522 30
## 31 RISHI SHETTY MI 3.5 1494 31
## 32 JOSHUA PHILIP MATHEWS ON 3.5 1441 32
## 33 JADE GE MI 3.5 1449 33
## 34 MICHAEL JEFFERY THOMAS MI 3.5 1399 34
## 35 JOSHUA DAVID LEE MI 3.5 1438 35
## 36 SIDDHARTH JHA MI 3.5 1355 36
## 37 AMIYATOSH PWNANANDAM MI 3.5 980 37
## 38 BRIAN LIU MI 3.0 1423 38
## 39 JOEL R HENDON MI 3.0 1436 39
## 40 FOREST ZHANG MI 3.0 1348 40
## 41 KYLE WILLIAM MURPHY MI 3.0 1403 41
## 42 JARED GE MI 3.0 1332 42
## 43 ROBERT GLEN VASEY MI 3.0 1283 43
## 44 JUSTIN D SCHILLING MI 3.0 1199 44
## 45 DEREK YAN MI 3.0 1242 45
## 46 JACOB ALEXANDER LAVALLEY MI 3.0 377 46
## 47 ERIC WRIGHT MI 2.5 1362 47
## 48 DANIEL KHAIN MI 2.5 1382 48
## 49 MICHAEL J MARTIN MI 2.5 1291 49
## 50 SHIVAM JHA MI 2.5 1056 50
## 51 TEJAS AYYAGARI MI 2.5 1011 51
## 52 ETHAN GUO MI 2.5 935 52
## 53 JOSE C YBARRA MI 2.0 1393 53
## 54 LARRY HODGE MI 2.0 1270 54
## 55 ALEX KONG MI 2.0 1186 55
## 56 MARISA RICCI MI 2.0 1153 56
## 57 MICHAEL LU MI 2.0 1092 57
## 58 VIRAJ MOHILE MI 2.0 917 58
## 59 SEAN M MC MI 2.0 853 59
## 60 JULIA SHEN MI 1.5 967 60
## 61 JEZZEL FARKAS ON 1.5 955 61
## 62 ASHWIN BALAJI MI 1.0 1530 62
## 63 THOMAS JOSEPH HOSMER MI 1.0 1175 63
## 64 BEN LI MI 1.0 1163 64Re-arranging the columns in r
df <- df[c(5,1,2,3,4)]
df## id Player Name Player State TotalPoints PreRating
## 1 1 GARY HUA ON 6.0 1794
## 2 2 DAKSHESH DARURI MI 6.0 1553
## 3 3 ADITYA BAJAJ MI 6.0 1384
## 4 4 PATRICK H SCHILLING MI 5.5 1716
## 5 5 HANSHI ZUO MI 5.5 1655
## 6 6 HANSEN SONG OH 5.0 1686
## 7 7 GARY DEE SWATHELL MI 5.0 1649
## 8 8 EZEKIEL HOUGHTON MI 5.0 1641
## 9 9 STEFANO LEE ON 5.0 1411
## 10 10 ANVIT RAO MI 5.0 1365
## 11 11 CAMERON WILLIAM MC MI 4.5 1712
## 12 12 KENNETH J TACK MI 4.5 1663
## 13 13 TORRANCE HENRY JR MI 4.5 1666
## 14 14 BRADLEY SHAW MI 4.5 1610
## 15 15 ZACHARY JAMES HOUGHTON MI 4.5 1220
## 16 16 MIKE NIKITIN MI 4.0 1604
## 17 17 RONALD GRZEGORCZYK MI 4.0 1629
## 18 18 DAVID SUNDEEN MI 4.0 1600
## 19 19 DIPANKAR ROY MI 4.0 1564
## 20 20 JASON ZHENG MI 4.0 1595
## 21 21 DINH DANG BUI ON 4.0 1563
## 22 22 EUGENE L MCCLURE MI 4.0 1555
## 23 23 ALAN BUI ON 4.0 1363
## 24 24 MICHAEL R ALDRICH MI 4.0 1229
## 25 25 LOREN SCHWIEBERT MI 3.5 1745
## 26 26 MAX ZHU ON 3.5 1579
## 27 27 GAURAV GIDWANI MI 3.5 1552
## 28 28 SOFIA ADINA MI 3.5 1507
## 29 29 CHIEDOZIE OKORIE MI 3.5 1602
## 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 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 1355
## 37 37 AMIYATOSH PWNANANDAM MI 3.5 980
## 38 38 BRIAN LIU MI 3.0 1423
## 39 39 JOEL R HENDON MI 3.0 1436
## 40 40 FOREST ZHANG MI 3.0 1348
## 41 41 KYLE WILLIAM MURPHY MI 3.0 1403
## 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 377
## 47 47 ERIC WRIGHT MI 2.5 1362
## 48 48 DANIEL KHAIN MI 2.5 1382
## 49 49 MICHAEL J MARTIN MI 2.5 1291
## 50 50 SHIVAM JHA MI 2.5 1056
## 51 51 TEJAS AYYAGARI MI 2.5 1011
## 52 52 ETHAN GUO MI 2.5 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 MI 2.0 853
## 60 60 JULIA SHEN MI 1.5 967
## 61 61 JEZZEL FARKAS ON 1.5 955
## 62 62 ASHWIN BALAJI MI 1.0 1530
## 63 63 THOMAS JOSEPH HOSMER MI 1.0 1175
## 64 64 BEN LI MI 1.0 1163Unfortunately,I did this manually since the ‘for loop’ method was returning errors.A non-reproducible alternative I considered (but was unable to implement prior to the deadline) was to export the existing data frame as CSV after adding an average opponent rating column, loading it as a table into SQL and and updating the average opponent rating column through a case when statement.
avg <- case_when(
df$id == 1 ~ 1605.286,
df$id ==2 ~ 1469.286,
df$id ==3 ~ 1563.571,
df$id == 4 ~ 1573.571,
df$id == 5 ~ 1500.857,
df$id == 6 ~ 1518.714,
df$id == 7 ~ 1372.14,
df$id == 8 ~ 1468.43,
df$id == 9 ~ 1523.142,
df$id == 10 ~ 1554.1428,
df$id == 11 ~ 1467.571,
df$id == 12 ~ 1506.167,
df$id == 13 ~ 1497.857,
df$id == 14 ~ 1515,
df$id == 15 ~ 1483.857,
df$id == 16 ~ 1385.8,
df$id == 17 ~ 1498.571,
df$id == 18 ~ 1480,
df$id == 19 ~ 1426.285,
df$id == 20 ~ 1410.857,
df$id == 21 ~ 1470.43,
df$id == 22 ~ 1300,
df$id == 23 ~ 1213.857,
df$id == 24 ~ 1357,
df$id == 25 ~ 1363.28,
df$id == 26 ~ 1506.857,
df$id == 27 ~ 1221.666,
df$id == 28 ~ 1522.143,
df$id == 29 ~ 1313.5,
df$id == 30 ~ 1144,
df$id == 31 ~ 1259.86,
df$id == 32 ~ 1378.714,
df$id == 33 ~ 1276.857,
df$id == 34 ~ 1375.286,
df$id == 35 ~ 1149.714,
df$id == 36 ~ 1388.167,
df$id == 37 ~ 1384.8,
df$id == 38 ~ 1539.16,
df$id == 39 ~ 1429.57,
df$id == 40 ~ 1390.571,
df$id == 41 ~ 1248.5,
df$id == 42 ~ 1149.86,
df$id == 43 ~ 1106.571,
df$id == 44 ~ 1327,
df$id == 45 ~ 1152,
df$id == 46 ~ 1357.714,
df$id == 47 ~ 1392,
df$id == 48 ~ 1355.8,
df$id == 49 ~ 1285.8,
df$id == 50 ~ 1296,
df$id == 51 ~ 1356.143,
df$id == 52 ~ 1494.571,
df$id == 53 ~ 1345.33,
df$id == 54 ~ 1206.167,
df$id == 55 ~ 1406,
df$id == 56 ~ 1414.4,
df$id == 57 ~ 1363,
df$id == 58 ~ 1391,
df$id == 59 ~ 1319,
df$id == 60 ~ 1330.2,
df$id == 61 ~ 1327.285,
df$id == 62 ~ 1186,
df$id == 63 ~ 1350.2,
df$id == 64 ~ 1263
)
avg## [1] 1605.286 1469.286 1563.571 1573.571 1500.857 1518.714 1372.140 1468.430
## [9] 1523.142 1554.143 1467.571 1506.167 1497.857 1515.000 1483.857 1385.800
## [17] 1498.571 1480.000 1426.285 1410.857 1470.430 1300.000 1213.857 1357.000
## [25] 1363.280 1506.857 1221.666 1522.143 1313.500 1144.000 1259.860 1378.714
## [33] 1276.857 1375.286 1149.714 1388.167 1384.800 1539.160 1429.570 1390.571
## [41] 1248.500 1149.860 1106.571 1327.000 1152.000 1357.714 1392.000 1355.800
## [49] 1285.800 1296.000 1356.143 1494.571 1345.330 1206.167 1406.000 1414.400
## [57] 1363.000 1391.000 1319.000 1330.200 1327.285 1186.000 1350.200 1263.000Adding oppononent average to dataframe
df$OpponentAvg.<- avg
df## id Player Name Player State TotalPoints PreRating OpponentAvg.
## 1 1 GARY HUA ON 6.0 1794 1605.286
## 2 2 DAKSHESH DARURI MI 6.0 1553 1469.286
## 3 3 ADITYA BAJAJ MI 6.0 1384 1563.571
## 4 4 PATRICK H SCHILLING MI 5.5 1716 1573.571
## 5 5 HANSHI ZUO MI 5.5 1655 1500.857
## 6 6 HANSEN SONG OH 5.0 1686 1518.714
## 7 7 GARY DEE SWATHELL MI 5.0 1649 1372.140
## 8 8 EZEKIEL HOUGHTON MI 5.0 1641 1468.430
## 9 9 STEFANO LEE ON 5.0 1411 1523.142
## 10 10 ANVIT RAO MI 5.0 1365 1554.143
## 11 11 CAMERON WILLIAM MC MI 4.5 1712 1467.571
## 12 12 KENNETH J TACK MI 4.5 1663 1506.167
## 13 13 TORRANCE HENRY JR MI 4.5 1666 1497.857
## 14 14 BRADLEY SHAW MI 4.5 1610 1515.000
## 15 15 ZACHARY JAMES HOUGHTON MI 4.5 1220 1483.857
## 16 16 MIKE NIKITIN MI 4.0 1604 1385.800
## 17 17 RONALD GRZEGORCZYK MI 4.0 1629 1498.571
## 18 18 DAVID SUNDEEN MI 4.0 1600 1480.000
## 19 19 DIPANKAR ROY MI 4.0 1564 1426.285
## 20 20 JASON ZHENG MI 4.0 1595 1410.857
## 21 21 DINH DANG BUI ON 4.0 1563 1470.430
## 22 22 EUGENE L MCCLURE MI 4.0 1555 1300.000
## 23 23 ALAN BUI ON 4.0 1363 1213.857
## 24 24 MICHAEL R ALDRICH MI 4.0 1229 1357.000
## 25 25 LOREN SCHWIEBERT MI 3.5 1745 1363.280
## 26 26 MAX ZHU ON 3.5 1579 1506.857
## 27 27 GAURAV GIDWANI MI 3.5 1552 1221.666
## 28 28 SOFIA ADINA MI 3.5 1507 1522.143
## 29 29 CHIEDOZIE OKORIE MI 3.5 1602 1313.500
## 30 30 GEORGE AVERY JONES ON 3.5 1522 1144.000
## 31 31 RISHI SHETTY MI 3.5 1494 1259.860
## 32 32 JOSHUA PHILIP MATHEWS ON 3.5 1441 1378.714
## 33 33 JADE GE MI 3.5 1449 1276.857
## 34 34 MICHAEL JEFFERY THOMAS MI 3.5 1399 1375.286
## 35 35 JOSHUA DAVID LEE MI 3.5 1438 1149.714
## 36 36 SIDDHARTH JHA MI 3.5 1355 1388.167
## 37 37 AMIYATOSH PWNANANDAM MI 3.5 980 1384.800
## 38 38 BRIAN LIU MI 3.0 1423 1539.160
## 39 39 JOEL R HENDON MI 3.0 1436 1429.570
## 40 40 FOREST ZHANG MI 3.0 1348 1390.571
## 41 41 KYLE WILLIAM MURPHY MI 3.0 1403 1248.500
## 42 42 JARED GE MI 3.0 1332 1149.860
## 43 43 ROBERT GLEN VASEY MI 3.0 1283 1106.571
## 44 44 JUSTIN D SCHILLING MI 3.0 1199 1327.000
## 45 45 DEREK YAN MI 3.0 1242 1152.000
## 46 46 JACOB ALEXANDER LAVALLEY MI 3.0 377 1357.714
## 47 47 ERIC WRIGHT MI 2.5 1362 1392.000
## 48 48 DANIEL KHAIN MI 2.5 1382 1355.800
## 49 49 MICHAEL J MARTIN MI 2.5 1291 1285.800
## 50 50 SHIVAM JHA MI 2.5 1056 1296.000
## 51 51 TEJAS AYYAGARI MI 2.5 1011 1356.143
## 52 52 ETHAN GUO MI 2.5 935 1494.571
## 53 53 JOSE C YBARRA MI 2.0 1393 1345.330
## 54 54 LARRY HODGE MI 2.0 1270 1206.167
## 55 55 ALEX KONG MI 2.0 1186 1406.000
## 56 56 MARISA RICCI MI 2.0 1153 1414.400
## 57 57 MICHAEL LU MI 2.0 1092 1363.000
## 58 58 VIRAJ MOHILE MI 2.0 917 1391.000
## 59 59 SEAN M MC MI 2.0 853 1319.000
## 60 60 JULIA SHEN MI 1.5 967 1330.200
## 61 61 JEZZEL FARKAS ON 1.5 955 1327.285
## 62 62 ASHWIN BALAJI MI 1.0 1530 1186.000
## 63 63 THOMAS JOSEPH HOSMER MI 1.0 1175 1350.200
## 64 64 BEN LI MI 1.0 1163 1263.000write.csv(df,"Player_Statistics",row.names=TRUE)Currently the table seems to be sorted by Total Points, with players who scored the highest at the top. However, when viewing the Pre Rating, it appears that Aditya Bajaj may have performed better than expected.Aditya’s opponent average is also much higher.