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.
sqldf package to obtain the opponents’ pre-rating numbers (table3).sqldf by joining table1 and table4 (Chess_Tournament)..CSV file in the local GitHub repository and the link to the online repository.First I uploaded the Chess Tournament text file into my GitHub Project 1 repository. Using the package RCurl I uploaded the Chess Tournament from online (right click on the link to view the original file).
library(RCurl)## Loading required package: bitopschess <- getURL("https://raw.githubusercontent.com/nabilahossain/Class-IS607/master/Project%201/tournamentinfo.txt")
chess## [1] "-----------------------------------------------------------------------------------------\n Pair | Player Name |Total|Round|Round|Round|Round|Round|Round|Round| \n Num | USCF ID / Rtg (Pre->Post) | Pts | 1 | 2 | 3 | 4 | 5 | 6 | 7 | \n-----------------------------------------------------------------------------------------\n 1 | GARY HUA |6.0 |W 39|W 21|W 18|W 14|W 7|D 12|D 4|\n ON | 15445895 / R: 1794 ->1817 |N:2 |W |B |W |B |W |B |W |\n-----------------------------------------------------------------------------------------\n 2 | DAKSHESH DARURI |6.0 |W 63|W 58|L 4|W 17|W 16|W 20|W 7|\n MI | 14598900 / R: 1553 ->1663 |N:2 |B |W |B |W |B |W |B |\n-----------------------------------------------------------------------------------------\n 3 | ADITYA BAJAJ |6.0 |L 8|W 61|W 25|W 21|W 11|W 13|W 12|\n MI | 14959604 / R: 1384 ->1640 |N:2 |W |B |W |B |W |B |W |\n-----------------------------------------------------------------------------------------\n 4 | PATRICK H SCHILLING |5.5 |W 23|D 28|W 2|W 26|D 5|W 19|D 1|\n MI | 12616049 / R: 1716 ->1744 |N:2 |W |B |W |B |W |B |B |\n-----------------------------------------------------------------------------------------\n 5 | HANSHI ZUO |5.5 |W 45|W 37|D 12|D 13|D 4|W 14|W 17|\n MI | 14601533 / R: 1655 ->1690 |N:2 |B |W |B |W |B |W |B |\n-----------------------------------------------------------------------------------------\n 6 | HANSEN SONG |5.0 |W 34|D 29|L 11|W 35|D 10|W 27|W 21|\n OH | 15055204 / R: 1686 ->1687 |N:3 |W |B |W |B |B |W |B |\n-----------------------------------------------------------------------------------------\n 7 | GARY DEE SWATHELL |5.0 |W 57|W 46|W 13|W 11|L 1|W 9|L 2|\n MI | 11146376 / R: 1649 ->1673 |N:3 |W |B |W |B |B |W |W |\n-----------------------------------------------------------------------------------------\n 8 | EZEKIEL HOUGHTON |5.0 |W 3|W 32|L 14|L 9|W 47|W 28|W 19|\n MI | 15142253 / R: 1641P17->1657P24 |N:3 |B |W |B |W |B |W |W |\n-----------------------------------------------------------------------------------------\n 9 | STEFANO LEE |5.0 |W 25|L 18|W 59|W 8|W 26|L 7|W 20|\n ON | 14954524 / R: 1411 ->1564 |N:2 |W |B |W |B |W |B |B |\n-----------------------------------------------------------------------------------------\n 10 | ANVIT RAO |5.0 |D 16|L 19|W 55|W 31|D 6|W 25|W 18|\n MI | 14150362 / R: 1365 ->1544 |N:3 |W |W |B |B |W |B |W |\n-----------------------------------------------------------------------------------------\n 11 | CAMERON WILLIAM MC LEMAN |4.5 |D 38|W 56|W 6|L 7|L 3|W 34|W 26|\n MI | 12581589 / R: 1712 ->1696 |N:3 |B |W |B |W |B |W |B |\n-----------------------------------------------------------------------------------------\n 12 | KENNETH J TACK |4.5 |W 42|W 33|D 5|W 38|H |D 1|L 3|\n MI | 12681257 / R: 1663 ->1670 |N:3 |W |B |W |B | |W |B |\n-----------------------------------------------------------------------------------------\n 13 | TORRANCE HENRY JR |4.5 |W 36|W 27|L 7|D 5|W 33|L 3|W 32|\n MI | 15082995 / R: 1666 ->1662 |N:3 |B |W |B |B |W |W |B |\n-----------------------------------------------------------------------------------------\n 14 | BRADLEY SHAW |4.5 |W 54|W 44|W 8|L 1|D 27|L 5|W 31|\n MI | 10131499 / R: 1610 ->1618 |N:3 |W |B |W |W |B |B |W |\n-----------------------------------------------------------------------------------------\n 15 | ZACHARY JAMES HOUGHTON |4.5 |D 19|L 16|W 30|L 22|W 54|W 33|W 38|\n MI | 15619130 / R: 1220P13->1416P20 |N:3 |B |B |W |W |B |B |W |\n-----------------------------------------------------------------------------------------\n 16 | MIKE NIKITIN |4.0 |D 10|W 15|H |W 39|L 2|W 36|U |\n MI | 10295068 / R: 1604 ->1613 |N:3 |B |W | |B |W |B | |\n-----------------------------------------------------------------------------------------\n 17 | RONALD GRZEGORCZYK |4.0 |W 48|W 41|L 26|L 2|W 23|W 22|L 5|\n MI | 10297702 / R: 1629 ->1610 |N:3 |W |B |W |B |W |B |W |\n-----------------------------------------------------------------------------------------\n 18 | DAVID SUNDEEN |4.0 |W 47|W 9|L 1|W 32|L 19|W 38|L 10|\n MI | 11342094 / R: 1600 ->1600 |N:3 |B |W |B |W |B |W |B |\n-----------------------------------------------------------------------------------------\n 19 | DIPANKAR ROY |4.0 |D 15|W 10|W 52|D 28|W 18|L 4|L 8|\n MI | 14862333 / R: 1564 ->1570 |N:3 |W |B |W |B |W |W |B |\n-----------------------------------------------------------------------------------------\n 20 | JASON ZHENG |4.0 |L 40|W 49|W 23|W 41|W 28|L 2|L 9|\n MI | 14529060 / R: 1595 ->1569 |N:4 |W |B |W |B |W |B |W |\n-----------------------------------------------------------------------------------------\n 21 | DINH DANG BUI |4.0 |W 43|L 1|W 47|L 3|W 40|W 39|L 6|\n ON | 15495066 / R: 1563P22->1562 |N:3 |B |W |B |W |W |B |W |\n-----------------------------------------------------------------------------------------\n 22 | EUGENE L MCCLURE |4.0 |W 64|D 52|L 28|W 15|H |L 17|W 40|\n MI | 12405534 / R: 1555 ->1529 |N:4 |W |B |W |B | |W |B |\n-----------------------------------------------------------------------------------------\n 23 | ALAN BUI |4.0 |L 4|W 43|L 20|W 58|L 17|W 37|W 46|\n ON | 15030142 / R: 1363 ->1371 | |B |W |B |W |B |W |B |\n-----------------------------------------------------------------------------------------\n 24 | MICHAEL R ALDRICH |4.0 |L 28|L 47|W 43|L 25|W 60|W 44|W 39|\n MI | 13469010 / R: 1229 ->1300 |N:4 |B |W |B |B |W |W |B |\n-----------------------------------------------------------------------------------------\n 25 | LOREN SCHWIEBERT |3.5 |L 9|W 53|L 3|W 24|D 34|L 10|W 47|\n MI | 12486656 / R: 1745 ->1681 |N:4 |B |W |B |W |B |W |B |\n-----------------------------------------------------------------------------------------\n 26 | MAX ZHU |3.5 |W 49|W 40|W 17|L 4|L 9|D 32|L 11|\n ON | 15131520 / R: 1579 ->1564 |N:4 |B |W |B |W |B |W |W |\n-----------------------------------------------------------------------------------------\n 27 | GAURAV GIDWANI |3.5 |W 51|L 13|W 46|W 37|D 14|L 6|U |\n MI | 14476567 / R: 1552 ->1539 |N:4 |W |B |W |B |W |B | |\n-----------------------------------------------------------------------------------------\n 28 | SOFIA ADINA STANESCU-BELLU |3.5 |W 24|D 4|W 22|D 19|L 20|L 8|D 36|\n MI | 14882954 / R: 1507 ->1513 |N:3 |W |W |B |W |B |B |W |\n-----------------------------------------------------------------------------------------\n 29 | CHIEDOZIE OKORIE |3.5 |W 50|D 6|L 38|L 34|W 52|W 48|U |\n MI | 15323285 / R: 1602P6 ->1508P12 |N:4 |B |W |B |W |W |B | |\n-----------------------------------------------------------------------------------------\n 30 | GEORGE AVERY JONES |3.5 |L 52|D 64|L 15|W 55|L 31|W 61|W 50|\n ON | 12577178 / R: 1522 ->1444 | |W |B |B |W |W |B |B |\n-----------------------------------------------------------------------------------------\n 31 | RISHI SHETTY |3.5 |L 58|D 55|W 64|L 10|W 30|W 50|L 14|\n MI | 15131618 / R: 1494 ->1444 | |B |W |B |W |B |W |B |\n-----------------------------------------------------------------------------------------\n 32 | JOSHUA PHILIP MATHEWS |3.5 |W 61|L 8|W 44|L 18|W 51|D 26|L 13|\n ON | 14073750 / R: 1441 ->1433 |N:4 |W |B |W |B |W |B |W |\n-----------------------------------------------------------------------------------------\n 33 | JADE GE |3.5 |W 60|L 12|W 50|D 36|L 13|L 15|W 51|\n MI | 14691842 / R: 1449 ->1421 | |B |W |B |W |B |W |B |\n-----------------------------------------------------------------------------------------\n 34 | MICHAEL JEFFERY THOMAS |3.5 |L 6|W 60|L 37|W 29|D 25|L 11|W 52|\n MI | 15051807 / R: 1399 ->1400 | |B |W |B |B |W |B |W |\n-----------------------------------------------------------------------------------------\n 35 | JOSHUA DAVID LEE |3.5 |L 46|L 38|W 56|L 6|W 57|D 52|W 48|\n MI | 14601397 / R: 1438 ->1392 | |W |W |B |W |B |B |W |\n-----------------------------------------------------------------------------------------\n 36 | SIDDHARTH JHA |3.5 |L 13|W 57|W 51|D 33|H |L 16|D 28|\n MI | 14773163 / R: 1355 ->1367 |N:4 |W |B |W |B | |W |B |\n-----------------------------------------------------------------------------------------\n 37 | AMIYATOSH PWNANANDAM |3.5 |B |L 5|W 34|L 27|H |L 23|W 61|\n MI | 15489571 / R: 980P12->1077P17 | | |B |W |W | |B |W |\n-----------------------------------------------------------------------------------------\n 38 | BRIAN LIU |3.0 |D 11|W 35|W 29|L 12|H |L 18|L 15|\n MI | 15108523 / R: 1423 ->1439 |N:4 |W |B |W |W | |B |B |\n-----------------------------------------------------------------------------------------\n 39 | JOEL R HENDON |3.0 |L 1|W 54|W 40|L 16|W 44|L 21|L 24|\n MI | 12923035 / R: 1436P23->1413 |N:4 |B |W |B |W |B |W |W |\n-----------------------------------------------------------------------------------------\n 40 | FOREST ZHANG |3.0 |W 20|L 26|L 39|W 59|L 21|W 56|L 22|\n MI | 14892710 / R: 1348 ->1346 | |B |B |W |W |B |W |W |\n-----------------------------------------------------------------------------------------\n 41 | KYLE WILLIAM MURPHY |3.0 |W 59|L 17|W 58|L 20|X |U |U |\n MI | 15761443 / R: 1403P5 ->1341P9 | |B |W |B |W | | | |\n-----------------------------------------------------------------------------------------\n 42 | JARED GE |3.0 |L 12|L 50|L 57|D 60|D 61|W 64|W 56|\n MI | 14462326 / R: 1332 ->1256 | |B |W |B |B |W |W |B |\n-----------------------------------------------------------------------------------------\n 43 | ROBERT GLEN VASEY |3.0 |L 21|L 23|L 24|W 63|W 59|L 46|W 55|\n MI | 14101068 / R: 1283 ->1244 | |W |B |W |W |B |B |W |\n-----------------------------------------------------------------------------------------\n 44 | JUSTIN D SCHILLING |3.0 |B |L 14|L 32|W 53|L 39|L 24|W 59|\n MI | 15323504 / R: 1199 ->1199 | | |W |B |B |W |B |W |\n-----------------------------------------------------------------------------------------\n 45 | DEREK YAN |3.0 |L 5|L 51|D 60|L 56|W 63|D 55|W 58|\n MI | 15372807 / R: 1242 ->1191 | |W |B |W |B |W |B |W |\n-----------------------------------------------------------------------------------------\n 46 | JACOB ALEXANDER LAVALLEY |3.0 |W 35|L 7|L 27|L 50|W 64|W 43|L 23|\n MI | 15490981 / R: 377P3 ->1076P10 | |B |W |B |W |B |W |W |\n-----------------------------------------------------------------------------------------\n 47 | ERIC WRIGHT |2.5 |L 18|W 24|L 21|W 61|L 8|D 51|L 25|\n MI | 12533115 / R: 1362 ->1341 | |W |B |W |B |W |B |W |\n-----------------------------------------------------------------------------------------\n 48 | DANIEL KHAIN |2.5 |L 17|W 63|H |D 52|H |L 29|L 35|\n MI | 14369165 / R: 1382 ->1335 | |B |W | |B | |W |B |\n-----------------------------------------------------------------------------------------\n 49 | MICHAEL J MARTIN |2.5 |L 26|L 20|D 63|D 64|W 58|H |U |\n MI | 12531685 / R: 1291P12->1259P17 | |W |W |B |W |B | | |\n-----------------------------------------------------------------------------------------\n 50 | SHIVAM JHA |2.5 |L 29|W 42|L 33|W 46|H |L 31|L 30|\n MI | 14773178 / R: 1056 ->1111 | |W |B |W |B | |B |W |\n-----------------------------------------------------------------------------------------\n 51 | TEJAS AYYAGARI |2.5 |L 27|W 45|L 36|W 57|L 32|D 47|L 33|\n MI | 15205474 / R: 1011 ->1097 | |B |W |B |W |B |W |W |\n-----------------------------------------------------------------------------------------\n 52 | ETHAN GUO |2.5 |W 30|D 22|L 19|D 48|L 29|D 35|L 34|\n MI | 14918803 / R: 935 ->1092 |N:4 |B |W |B |W |B |W |B |\n-----------------------------------------------------------------------------------------\n 53 | JOSE C YBARRA |2.0 |H |L 25|H |L 44|U |W 57|U |\n MI | 12578849 / R: 1393 ->1359 | | |B | |W | |W | |\n-----------------------------------------------------------------------------------------\n 54 | LARRY HODGE |2.0 |L 14|L 39|L 61|B |L 15|L 59|W 64|\n MI | 12836773 / R: 1270 ->1200 | |B |B |W | |W |B |W |\n-----------------------------------------------------------------------------------------\n 55 | ALEX KONG |2.0 |L 62|D 31|L 10|L 30|B |D 45|L 43|\n MI | 15412571 / R: 1186 ->1163 | |W |B |W |B | |W |B |\n-----------------------------------------------------------------------------------------\n 56 | MARISA RICCI |2.0 |H |L 11|L 35|W 45|H |L 40|L 42|\n MI | 14679887 / R: 1153 ->1140 | | |B |W |W | |B |W |\n-----------------------------------------------------------------------------------------\n 57 | MICHAEL LU |2.0 |L 7|L 36|W 42|L 51|L 35|L 53|B |\n MI | 15113330 / R: 1092 ->1079 | |B |W |W |B |W |B | |\n-----------------------------------------------------------------------------------------\n 58 | VIRAJ MOHILE |2.0 |W 31|L 2|L 41|L 23|L 49|B |L 45|\n MI | 14700365 / R: 917 -> 941 | |W |B |W |B |W | |B |\n-----------------------------------------------------------------------------------------\n 59 | SEAN M MC CORMICK |2.0 |L 41|B |L 9|L 40|L 43|W 54|L 44|\n MI | 12841036 / R: 853 -> 878 | |W | |B |B |W |W |B |\n-----------------------------------------------------------------------------------------\n 60 | JULIA SHEN |1.5 |L 33|L 34|D 45|D 42|L 24|H |U |\n MI | 14579262 / R: 967 -> 984 | |W |B |B |W |B | | |\n-----------------------------------------------------------------------------------------\n 61 | JEZZEL FARKAS |1.5 |L 32|L 3|W 54|L 47|D 42|L 30|L 37|\n ON | 15771592 / R: 955P11-> 979P18 | |B |W |B |W |B |W |B |\n-----------------------------------------------------------------------------------------\n 62 | ASHWIN BALAJI |1.0 |W 55|U |U |U |U |U |U |\n MI | 15219542 / R: 1530 ->1535 | |B | | | | | | |\n-----------------------------------------------------------------------------------------\n 63 | THOMAS JOSEPH HOSMER |1.0 |L 2|L 48|D 49|L 43|L 45|H |U |\n MI | 15057092 / R: 1175 ->1125 | |W |B |W |B |B | | |\n-----------------------------------------------------------------------------------------\n 64 | BEN LI |1.0 |L 22|D 30|L 31|D 49|L 46|L 42|L 54|\n MI | 15006561 / R: 1163 ->1112 | |B |W |W |B |W |B |B |\n-----------------------------------------------------------------------------------------"``` UP
Using the stringr package I obtained the player’s name and state. Using str_extraxt_all and str_replace_all functions and regular expression I extracted and formatted the data from the .txt file.
library(stringr)
name <- unlist(str_extract_all(chess, "\\| [[:upper:]- ]{4,} \\|"))
name <- str_replace_all(name, pattern = "(\\| )|([[:space:]]{1,}\\|)", replacement = "")
head(name[25:30])## [1] "LOREN SCHWIEBERT" "MAX ZHU"
## [3] "GAURAV GIDWANI" "SOFIA ADINA STANESCU-BELLU"
## [5] "CHIEDOZIE OKORIE" "GEORGE AVERY JONES"state <- unlist(str_extract_all(chess, "\\|[[:space:]]{1,}[[A-Z]]{2} \\|"))
state <- str_replace_all(state, pattern = "(\\|[[:space:]]{1,})|([[:space:]]{1,}\\|)", replacement = "")
head(state, 10)## [1] "ON" "MI" "MI" "MI" "MI" "OH" "MI" "MI" "ON" "MI"Used str_extraxt_all and str_replace_all functions and regular expression to extract and format the information about the player’s total number of points and the player’s pre-rating.
total_points <- unlist(str_extract_all(chess, "\\|[[:digit:].[:digit:]]{3}[[:space:]]{1,}\\|"))
total_points <- str_replace_all(total_points, pattern = "(\\|)|([[:space:]]{1,}\\|)", replacement = "")
head(total_points, 10)## [1] "6.0" "6.0" "6.0" "5.5" "5.5" "5.0" "5.0" "5.0" "5.0" "5.0"pre_rating <- unlist(str_extract_all(chess, "[:] [[:alnum:] ]{2,9}\\-\\>"))
pre_rating <- str_replace_all(pre_rating, pattern = "(\\: )|(\\s{1,}\\-\\>)|([O-Q]\\d{1,2})|(\\-\\>)", replacement = "")
pre_rating <- as.numeric(pre_rating)
head(pre_rating, 10)## [1] 1794 1553 1384 1716 1655 1686 1649 1641 1411 1365I also extracted the player’s number (to be used for combining tables), using the same functions as before. Then I created a table with player’s number, name, state, total number of points and their pre-rating number. I titled the table as table1 for future reference.
player_num <- unlist(str_extract_all(chess, "\\d{1,2}\\s\\|"))
player_num <- str_replace_all(player_num, pattern = "(\\s\\|)", replacement = "")
player_num <- as.numeric(player_num)
table1 <- data.frame(player_num = player_num, name = name, state = state, total_pts = total_points, pre_rating = pre_rating)
head(table1)## player_num name state total_pts pre_rating
## 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 1686Here I also used str_extraxt_all and str_replace_all functions and regular expression. However here I used it to extract all the information found on the first line of the player’s information in the Chess Tournment text file. I obtained the player’s number, name, and total points and their opponent’s number in round 1 through round 7. Then using str_split_fixed function I created a table called rounds with 10 columns of information, as you can see below.
r1 <- unlist(str_extract_all(chess, "\\d{1,2}\\s\\|(.+?)\\|[[:upper:]]{1}\\s{1,4}[[:digit:]]{0,3}\\|\n"))
r1 <- str_replace_all(r1, pattern = "(\\|[[:upper:]]{1} )", replacement = "\\|")
head(r1, 10)## [1] "1 | GARY HUA |6.0 | 39| 21| 18| 14| 7| 12| 4|\n"
## [2] "2 | DAKSHESH DARURI |6.0 | 63| 58| 4| 17| 16| 20| 7|\n"
## [3] "3 | ADITYA BAJAJ |6.0 | 8| 61| 25| 21| 11| 13| 12|\n"
## [4] "4 | PATRICK H SCHILLING |5.5 | 23| 28| 2| 26| 5| 19| 1|\n"
## [5] "5 | HANSHI ZUO |5.5 | 45| 37| 12| 13| 4| 14| 17|\n"
## [6] "6 | HANSEN SONG |5.0 | 34| 29| 11| 35| 10| 27| 21|\n"
## [7] "7 | GARY DEE SWATHELL |5.0 | 57| 46| 13| 11| 1| 9| 2|\n"
## [8] "8 | EZEKIEL HOUGHTON |5.0 | 3| 32| 14| 9| 47| 28| 19|\n"
## [9] "9 | STEFANO LEE |5.0 | 25| 18| 59| 8| 26| 7| 20|\n"
## [10] "10 | ANVIT RAO |5.0 | 16| 19| 55| 31| 6| 25| 18|\n"r1 <- str_replace_all(r1, pattern = "([[:space:]]{2})|(\\|\n)", replacement = "")
rounds <- data.frame(str_split_fixed(r1, "\\|", 10))
names(rounds) <- c("num", "name", "total_pts", "round1", "round2", "round3", "round4", "round5", "round6", "round7")
tail(rounds, 4)## num name total_pts round1 round2 round3 round4 round5
## 61 61 JEZZEL FARKAS 1.5 32 3 54 47 42
## 62 62 ASHWIN BALAJI 1.0 55
## 63 63 THOMAS JOSEPH HOSMER 1.0 2 48 49 43 45
## 64 64 BEN LI 1.0 22 30 31 49 46
## round6 round7
## 61 30 37
## 62
## 63
## 64 42 54Then I used the melt function from the package reshape2 to combine each player’s opponents’ information from seven rounds into one column. Then I created a table (table2) using the subset function to get rid of all the information where there was no opponent number. For example a player might have played only 5 games out of the seven. Therefore, only five of rounds would have any opponent information, while the other two have none.
library(reshape2)
r3 <- data.frame(rounds[c(1, 4:10)])
r3$num <- str_replace_all(r3$num, pattern = "\\s{1,}(\\d{1,2})", replacement = "\\1")
r4 <- melt(r3,id.vars="num", variable.name = "rounds", value.name = "opponent_number" )## Warning: attributes are not identical across measure variables; they will
## be droppedtail(r4)## num rounds opponent_number
## 443 59 round7 44
## 444 60 round7
## 445 61 round7 37
## 446 62 round7
## 447 63 round7
## 448 64 round7 54table2 <- subset(r4, opponent_number != " ")
table2$num <- as.numeric(table2$num)
tail(table2)## num rounds opponent_number
## 439 55 round7 43
## 440 56 round7 42
## 442 58 round7 45
## 443 59 round7 44
## 445 61 round7 37
## 448 64 round7 54To obtain the opponent’s pre-rating information, I used the package sqldf to join table2 with table1. I pulled the name and pre-rating from the table1, and the player’s number, rounds they played and the opponent number from table2. By setting the opponent’s number in table2 equaling to the player number in table1, I was able to obtain the opponent’s pre-rating number and thier name as shown below. I set the new data frame as table3.
library(sqldf)## Loading required package: gsubfn
## Loading required package: proto
## Loading required package: RSQLite
## Loading required package: DBItable3 <- sqldf("select t2.num as 'player_num', t1.name as 'opponent_name', t2.rounds, t2.opponent_number, t1.pre_rating as 'opponent_pre_rating' from table2 t2
left join table1 t1
on t2.opponent_number = t1.player_num
order by t2.num asc")## Loading required package: tcltkhead(table3)## player_num opponent_name rounds opponent_number opponent_pre_rating
## 1 1 JOEL R HENDON round1 39 1436
## 2 1 DINH DANG BUI round2 21 1563
## 3 1 DAVID SUNDEEN round3 18 1600
## 4 1 BRADLEY SHAW round4 14 1610
## 5 1 GARY DEE SWATHELL round5 7 1649
## 6 1 KENNETH J TACK round6 12 1663Using stats package’s aggregate function I found the average opponent’s pre-rating. I set the subset with only the information about the player number and the average opponent’s pre-rating as table4.
library(stats)
table4 <- aggregate(opponent_pre_rating ~ player_num, data = table3, FUN = 'mean')
head(table4)## player_num opponent_pre_rating
## 1 1 1605.286
## 2 2 1469.286
## 3 3 1563.571
## 4 4 1573.571
## 5 5 1500.857
## 6 6 1518.714Finally I create the table Chess_Tournament, with the following information: Player’s Name, Player’s State, Total Number of Points, Player’s Pre-Rating, and Average Pre Chess Rating of Opponents. I use the sqldf function by joining table1 and table4. From table1 I obtain the player’s name, player’s state, total number of points, player’s pre-rating, and from table4 I obtain average pre-rating of opponents. I also use the format function to round the decimals in average pre-rating of opponents column to 1 decimal place.
Chess_Tournament <- sqldf("select t1.name as 'Player_Name', t1.state as 'Player_State', t1.total_pts as 'Total_Points', t1.pre_rating as 'Player_Pre-Rating', t4.opponent_pre_rating as 'Opponents_Average_Pre_Rating'
from table1 t1 left join table4 t4
on t4.player_num = t1.player_num")
Chess_Tournament$Opponents_Average_Pre_Rating <- format(round(Chess_Tournament$Opponents_Average_Pre_Rating, 1), nsmall = 1)
head(Chess_Tournament, 30)## Player_Name Player_State Total_Points Player_Pre-Rating
## 1 GARY HUA ON 6.0 1794
## 2 DAKSHESH DARURI MI 6.0 1553
## 3 ADITYA BAJAJ MI 6.0 1384
## 4 PATRICK H SCHILLING MI 5.5 1716
## 5 HANSHI ZUO MI 5.5 1655
## 6 HANSEN SONG OH 5.0 1686
## 7 GARY DEE SWATHELL MI 5.0 1649
## 8 EZEKIEL HOUGHTON MI 5.0 1641
## 9 STEFANO LEE ON 5.0 1411
## 10 ANVIT RAO MI 5.0 1365
## 11 CAMERON WILLIAM MC LEMAN MI 4.5 1712
## 12 KENNETH J TACK MI 4.5 1663
## 13 TORRANCE HENRY JR MI 4.5 1666
## 14 BRADLEY SHAW MI 4.5 1610
## 15 ZACHARY JAMES HOUGHTON MI 4.5 1220
## 16 MIKE NIKITIN MI 4.0 1604
## 17 RONALD GRZEGORCZYK MI 4.0 1629
## 18 DAVID SUNDEEN MI 4.0 1600
## 19 DIPANKAR ROY MI 4.0 1564
## 20 JASON ZHENG MI 4.0 1595
## 21 DINH DANG BUI ON 4.0 1563
## 22 EUGENE L MCCLURE MI 4.0 1555
## 23 ALAN BUI ON 4.0 1363
## 24 MICHAEL R ALDRICH MI 4.0 1229
## 25 LOREN SCHWIEBERT MI 3.5 1745
## 26 MAX ZHU ON 3.5 1579
## 27 GAURAV GIDWANI MI 3.5 1552
## 28 SOFIA ADINA STANESCU-BELLU MI 3.5 1507
## 29 CHIEDOZIE OKORIE MI 3.5 1602
## 30 GEORGE AVERY JONES ON 3.5 1522
## Opponents_Average_Pre_Rating
## 1 1605.3
## 2 1469.3
## 3 1563.6
## 4 1573.6
## 5 1500.9
## 6 1518.7
## 7 1372.1
## 8 1468.4
## 9 1523.1
## 10 1554.1
## 11 1467.6
## 12 1506.2
## 13 1497.9
## 14 1515.0
## 15 1483.9
## 16 1385.8
## 17 1498.6
## 18 1480.0
## 19 1426.3
## 20 1410.9
## 21 1470.4
## 22 1300.3
## 23 1213.9
## 24 1357.0
## 25 1363.3
## 26 1506.9
## 27 1221.7
## 28 1522.1
## 29 1313.5
## 30 1144.1I created a .CSV file in my local GitHub repository. You can view the CSV file in my online repository, when you right click on the link and open in new tab: Chess Tournament CSV or by copying and pasting the following link: https://github.com/nabilahossain/Class-IS607/blob/master/Project%201/Chess_Tournament.csv.
write.csv(Chess_Tournament, file = "C:/Users/Nabila/Documents/GitHub/Class-IS607/Project 1/Chess_Tournament.csv")