When working with data, sometimes it is not available in a readable or executable format. Even if the data is readable by the database management system, it may not be easy for a human to understand. In such situations, we would like to “tidy” our data. Some information in a data set may not be necessary in some situations. Removing this unneeded data can remove clutter and provide an interface that is easy to manage. In this project, we will use data collected from a chess tournament to create a new data-frame that can be accessed as a ‘.csv’ file. This new data-frame must consist of the following variable columns:
We will see that the initial data set, given as a ‘.txt’ file, presents itself in a relatively unstructured manner and includes many characters that will not be used in our analysis, therefore, we must transform this data to our liking.
We must first retrieve our data from a source. In this case, the .txt file is obtained from a url. I read this data in and assign it to the variable ‘chess_txt’. Then I use the ‘head()’ function to take a peak at the data and check its data type using ‘typeof()’.
chess_txt <- read.csv("https://raw.githubusercontent.com/JAdames27/DATA-607---Data-Acquisition-and-Management/main/DATA%20607%20-%20Project%201/tournamentinfo.txt")
head(chess_txt)## 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 -----------------------------------------------------------------------------------------typeof(chess_txt)## [1] "list"However, when read in, we see that the data type is a list using the typeof() function. I turn the data into one long string of characters to allow string operations from the ‘stringr’ library. This allows me to parse through the data as needed. The text data is vast, and since we now have a string, the ‘head()’ function will not allow us to peak into the data. We can use the ‘substr()’ function to achieve a similar goal with strings. Below I peaked at the first 2500 characters of the string.
chess_str <- toString(chess_txt)
substr(chess_str, 1, 2500)## [1] "c(\" Pair | Player Name |Total|Round|Round|Round|Round|Round|Round|Round| \", \" Num | USCF ID / Rtg (Pre->Post) | Pts | 1 | 2 | 3 | 4 | 5 | 6 | 7 | \", \"-----------------------------------------------------------------------------------------\", \" 1 | GARY HUA |6.0 |W 39|W 21|W 18|W 14|W 7|D 12|D 4|\", \" ON | 15445895 / R: 1794 ->1817 |N:2 |W |B |W |B |W |B |W |\", \"-----------------------------------------------------------------------------------------\", \n\" 2 | DAKSHESH DARURI |6.0 |W 63|W 58|L 4|W 17|W 16|W 20|W 7|\", \" MI | 14598900 / R: 1553 ->1663 |N:2 |B |W |B |W |B |W |B |\", \"-----------------------------------------------------------------------------------------\", \" 3 | ADITYA BAJAJ |6.0 |L 8|W 61|W 25|W 21|W 11|W 13|W 12|\", \" MI | 14959604 / R: 1384 ->1640 |N:2 |W |B |W |B |W |B |W |\", \"-----------------------------------------------------------------------------------------\", \n\" 4 | PATRICK H SCHILLING |5.5 |W 23|D 28|W 2|W 26|D 5|W 19|D 1|\", \" MI | 12616049 / R: 1716 ->1744 |N:2 |W |B |W |B |W |B |B |\", \"-----------------------------------------------------------------------------------------\", \" 5 | HANSHI ZUO |5.5 |W 45|W 37|D 12|D 13|D 4|W 14|W 17|\", \" MI | 14601533 / R: 1655 ->1690 |N:2 |B |W |B |W |B |W |B |\", \"-----------------------------------------------------------------------------------------\", \n\" 6 | HANSEN SONG |5.0 |W 34|D 29|L 11|W 35|D 10|W 27|W 21|\", \" OH | 15055204 / R: 1686 ->1687 |N:3 |W |B |W |B |B |W |B |\", \"-----------------------------------------------------------------------------------------\", \" 7 | GARY DEE SWATHELL |5.0 |W 57|W 46|W 13|W 11|L 1|W 9|L 2|\", \" MI | 11146376 / R: 1649 ->1673 |N:3 |W |B |W |B |B |W |W |\", \"-----------------------------------------------------------------------------------------\", \n\" 8 | EZEKIEL HOUGHTON |5.0 |W 3|W 32|L 14|L 9|W 47|W 28|W 19|\", \" MI | 15142253 / R: 1641P17->1657P24 |N:3 |B |W |B |W |B |W |W |\", \"-------------------------------------------------------------------------"When calling this new string, as above, we see that the data is much less readable than what we initially had. I use the strsplit() function to divide the text based on some unnecessary series of characters. From the text data above, I can see that the data is separated using a string of dashes and other special characters. This is what I used to split my text and assigned it to ‘chess_split1’.
chess_split1 <- strsplit(chess_str, split = '\", \"-----------------------------------------------------------------------------------------\", \n\"')After splitting, the resulting data returns a list within a list. All of our data is in the ‘inner’ list that is stored in the first index of of our encompassing list. I index this outer list to access only the nested list found in that first index, then I index even further to get the discrete elements in this list, ie. ‘chess_split1[[1]]’. These discrete elements correspond to the rows or observations of our data. I relabel this to ‘chess_list1’.
head(chess_split1[[1]])## [1] "c(\" Pair | Player Name |Total|Round|Round|Round|Round|Round|Round|Round| \", \" Num | USCF ID / Rtg (Pre->Post) | Pts | 1 | 2 | 3 | 4 | 5 | 6 | 7 | \", \"-----------------------------------------------------------------------------------------\", \" 1 | GARY HUA |6.0 |W 39|W 21|W 18|W 14|W 7|D 12|D 4|\", \" ON | 15445895 / R: 1794 ->1817 |N:2 |W |B |W |B |W |B |W |"
## [2] " 2 | DAKSHESH DARURI |6.0 |W 63|W 58|L 4|W 17|W 16|W 20|W 7|\", \" MI | 14598900 / R: 1553 ->1663 |N:2 |B |W |B |W |B |W |B |\", \"-----------------------------------------------------------------------------------------\", \" 3 | ADITYA BAJAJ |6.0 |L 8|W 61|W 25|W 21|W 11|W 13|W 12|\", \" MI | 14959604 / R: 1384 ->1640 |N:2 |W |B |W |B |W |B |W |"
## [3] " 4 | PATRICK H SCHILLING |5.5 |W 23|D 28|W 2|W 26|D 5|W 19|D 1|\", \" MI | 12616049 / R: 1716 ->1744 |N:2 |W |B |W |B |W |B |B |\", \"-----------------------------------------------------------------------------------------\", \" 5 | HANSHI ZUO |5.5 |W 45|W 37|D 12|D 13|D 4|W 14|W 17|\", \" MI | 14601533 / R: 1655 ->1690 |N:2 |B |W |B |W |B |W |B |"
## [4] " 6 | HANSEN SONG |5.0 |W 34|D 29|L 11|W 35|D 10|W 27|W 21|\", \" OH | 15055204 / R: 1686 ->1687 |N:3 |W |B |W |B |B |W |B |\", \"-----------------------------------------------------------------------------------------\", \" 7 | GARY DEE SWATHELL |5.0 |W 57|W 46|W 13|W 11|L 1|W 9|L 2|\", \" MI | 11146376 / R: 1649 ->1673 |N:3 |W |B |W |B |B |W |W |"
## [5] " 8 | EZEKIEL HOUGHTON |5.0 |W 3|W 32|L 14|L 9|W 47|W 28|W 19|\", \" MI | 15142253 / R: 1641P17->1657P24 |N:3 |B |W |B |W |B |W |W |\", \"-----------------------------------------------------------------------------------------\", \" 9 | STEFANO LEE |5.0 |W 25|L 18|W 59|W 8|W 26|L 7|W 20|\", \" ON | 14954524 / R: 1411 ->1564 |N:2 |W |B |W |B |W |B |B |"
## [6] " 10 | ANVIT RAO |5.0 |D 16|L 19|W 55|W 31|D 6|W 25|W 18|\", \" MI | 14150362 / R: 1365 ->1544 |N:3 |W |W |B |B |W |B |W |\", \"-----------------------------------------------------------------------------------------\", \" 11 | CAMERON WILLIAM MC LEMAN |4.5 |D 38|W 56|W 6|L 7|L 3|W 34|W 26|\", \" MI | 12581589 / R: 1712 ->1696 |N:3 |B |W |B |W |B |W |B |"chess_list1 <- c(chess_split1[[1]])However, when taking a closer look we can see that the initial split did not create the desire number of observations. This is because the specific string of dashes and special characters that was used to split the data does not match the other instances of separating dashes. I must use the ‘strsplit()’ function again, this time using a for loop and the new sequence of characters for the second split. To do this, I needed to use a data type that is iterable. The assignment of ‘chess_list2’ and ‘chess_list3’ allows for this. I can now iterate through using a for loop and assign the resulting data to ‘myList’. I again use the ‘head()’ function to take a peak at this new list.
chess_list2 <- list(chess_list1)
chess_list3 <- chess_list2[[1]]
myList <- list()
for (i in chess_list3){
i = strsplit(i, '\", \"-----------------------------------------------------------------------------------------\", \"')
list(i)
myList <- append(myList, list(i[[1]]))
}
head(myList)## [[1]]
## [1] "c(\" Pair | Player Name |Total|Round|Round|Round|Round|Round|Round|Round| \", \" Num | USCF ID / Rtg (Pre->Post) | Pts | 1 | 2 | 3 | 4 | 5 | 6 | 7 | "
## [2] " 1 | GARY HUA |6.0 |W 39|W 21|W 18|W 14|W 7|D 12|D 4|\", \" ON | 15445895 / R: 1794 ->1817 |N:2 |W |B |W |B |W |B |W |"
##
## [[2]]
## [1] " 2 | DAKSHESH DARURI |6.0 |W 63|W 58|L 4|W 17|W 16|W 20|W 7|\", \" MI | 14598900 / R: 1553 ->1663 |N:2 |B |W |B |W |B |W |B |"
## [2] " 3 | ADITYA BAJAJ |6.0 |L 8|W 61|W 25|W 21|W 11|W 13|W 12|\", \" MI | 14959604 / R: 1384 ->1640 |N:2 |W |B |W |B |W |B |W |"
##
## [[3]]
## [1] " 4 | PATRICK H SCHILLING |5.5 |W 23|D 28|W 2|W 26|D 5|W 19|D 1|\", \" MI | 12616049 / R: 1716 ->1744 |N:2 |W |B |W |B |W |B |B |"
## [2] " 5 | HANSHI ZUO |5.5 |W 45|W 37|D 12|D 13|D 4|W 14|W 17|\", \" MI | 14601533 / R: 1655 ->1690 |N:2 |B |W |B |W |B |W |B |"
##
## [[4]]
## [1] " 6 | HANSEN SONG |5.0 |W 34|D 29|L 11|W 35|D 10|W 27|W 21|\", \" OH | 15055204 / R: 1686 ->1687 |N:3 |W |B |W |B |B |W |B |"
## [2] " 7 | GARY DEE SWATHELL |5.0 |W 57|W 46|W 13|W 11|L 1|W 9|L 2|\", \" MI | 11146376 / R: 1649 ->1673 |N:3 |W |B |W |B |B |W |W |"
##
## [[5]]
## [1] " 8 | EZEKIEL HOUGHTON |5.0 |W 3|W 32|L 14|L 9|W 47|W 28|W 19|\", \" MI | 15142253 / R: 1641P17->1657P24 |N:3 |B |W |B |W |B |W |W |"
## [2] " 9 | STEFANO LEE |5.0 |W 25|L 18|W 59|W 8|W 26|L 7|W 20|\", \" ON | 14954524 / R: 1411 ->1564 |N:2 |W |B |W |B |W |B |B |"
##
## [[6]]
## [1] " 10 | ANVIT RAO |5.0 |D 16|L 19|W 55|W 31|D 6|W 25|W 18|\", \" MI | 14150362 / R: 1365 ->1544 |N:3 |W |W |B |B |W |B |W |"
## [2] " 11 | CAMERON WILLIAM MC LEMAN |4.5 |D 38|W 56|W 6|L 7|L 3|W 34|W 26|\", \" MI | 12581589 / R: 1712 ->1696 |N:3 |B |W |B |W |B |W |B |"We can now see that the data separates into 65 rows, (ie. 1 header row + 64 observations). We also see that this is still formatted as nested lists. The following for loop creates a new, empty list, ie. ‘myList2’, and appends the rows obtained from the nested lists in the original ‘myList’. The ‘myList2’ assignment contains all of the data in question, but in one un-nested, iterable list as shown below. The final ‘iteration’ has no element to iterate through so it returns as ‘NA’ for index 66, ie. ‘myList[66]’ Therefore our final element is indexed at 65, ie. ‘myList[65]’ represents the 64th observation.
myList2 <- list()
for (i in myList){
myList2 <- append(myList2, i[1])
myList2 <- append(myList2, i[2])
}
#full list
head(myList2)## [[1]]
## [1] "c(\" Pair | Player Name |Total|Round|Round|Round|Round|Round|Round|Round| \", \" Num | USCF ID / Rtg (Pre->Post) | Pts | 1 | 2 | 3 | 4 | 5 | 6 | 7 | "
##
## [[2]]
## [1] " 1 | GARY HUA |6.0 |W 39|W 21|W 18|W 14|W 7|D 12|D 4|\", \" ON | 15445895 / R: 1794 ->1817 |N:2 |W |B |W |B |W |B |W |"
##
## [[3]]
## [1] " 2 | DAKSHESH DARURI |6.0 |W 63|W 58|L 4|W 17|W 16|W 20|W 7|\", \" MI | 14598900 / R: 1553 ->1663 |N:2 |B |W |B |W |B |W |B |"
##
## [[4]]
## [1] " 3 | ADITYA BAJAJ |6.0 |L 8|W 61|W 25|W 21|W 11|W 13|W 12|\", \" MI | 14959604 / R: 1384 ->1640 |N:2 |W |B |W |B |W |B |W |"
##
## [[5]]
## [1] " 4 | PATRICK H SCHILLING |5.5 |W 23|D 28|W 2|W 26|D 5|W 19|D 1|\", \" MI | 12616049 / R: 1716 ->1744 |N:2 |W |B |W |B |W |B |B |"
##
## [[6]]
## [1] " 5 | HANSHI ZUO |5.5 |W 45|W 37|D 12|D 13|D 4|W 14|W 17|\", \" MI | 14601533 / R: 1655 ->1690 |N:2 |B |W |B |W |B |W |B |"Using the same ‘myList2’ assignment, we can confirm that the indexing works as intended by accessing the following:
ie. 'myList2[1]'
ie. 'myList2[2]'
ie. 'myList2[65]'#header row
myList2[1]## [[1]]
## [1] "c(\" Pair | Player Name |Total|Round|Round|Round|Round|Round|Round|Round| \", \" Num | USCF ID / Rtg (Pre->Post) | Pts | 1 | 2 | 3 | 4 | 5 | 6 | 7 | "#first entry
myList2[2]## [[1]]
## [1] " 1 | GARY HUA |6.0 |W 39|W 21|W 18|W 14|W 7|D 12|D 4|\", \" ON | 15445895 / R: 1794 ->1817 |N:2 |W |B |W |B |W |B |W |"#last entry
myList2[65]## [[1]]
## [1] " 64 | BEN LI |1.0 |L 22|D 30|L 31|D 49|L 46|L 42|L 54|\", \" MI | 15006561 / R: 1163 ->1112 | |B |W |W |B |W |B |B |\", \"-----------------------------------------------------------------------------------------\")"Even though we have a much ‘nicer’ or ‘tidier’ collection of data than what we started with, there is still unwanted information that makes this data “noisy”. However, within this structure there is a patterns that each observation row shares with one another. We can use Regular Expressions (regex) to extract only what we need from each row as long as they share the same pattern. Such is the case in this instance. I’ve narrowed down the data for each row even further. The resulting data strings contain all of the information needed to complete our task. However, as we’ve seen before, this is given in a nested list, ie. ‘results1_list[[1]]’.
#create a pattern to match
pattern <- '.*\\w*\\s\\w.*R:\\s*[0-9]*'
# Find all matches in the text - we only look at the observation rows, ie. indices 2 through 65.
matches <- gregexpr(pattern, myList2[2:65])
# Extract the matching substrings
results <- regmatches(myList2[2:65], matches)
#un-listing to string
results1 <- unlist(results)
#re-listing string to 'nicer' list
results1_list <- list(results1)
#results
head(results1_list[[1]])## [1] " 1 | GARY HUA |6.0 |W 39|W 21|W 18|W 14|W 7|D 12|D 4|\", \" ON | 15445895 / R: 1794"
## [2] " 2 | DAKSHESH DARURI |6.0 |W 63|W 58|L 4|W 17|W 16|W 20|W 7|\", \" MI | 14598900 / R: 1553"
## [3] " 3 | ADITYA BAJAJ |6.0 |L 8|W 61|W 25|W 21|W 11|W 13|W 12|\", \" MI | 14959604 / R: 1384"
## [4] " 4 | PATRICK H SCHILLING |5.5 |W 23|D 28|W 2|W 26|D 5|W 19|D 1|\", \" MI | 12616049 / R: 1716"
## [5] " 5 | HANSHI ZUO |5.5 |W 45|W 37|D 12|D 13|D 4|W 14|W 17|\", \" MI | 14601533 / R: 1655"
## [6] " 6 | HANSEN SONG |5.0 |W 34|D 29|L 11|W 35|D 10|W 27|W 21|\", \" OH | 15055204 / R: 1686"Column 1 of our desired data-frame consists of the full names of the tournament contestants. Let’s parse through the previously obtained list using a pattern match that represents the names, regardless of the number of middle names or initials. Then we can extract what we need. Here I used two for loops to complete the narrowing down process correctly. The desired name data is contained in a list called ‘name_results2’. We can unlist this to see the individual listings.
#column 1: name
#this for loop narrows down the entire string to just include the name data with some whitespaces
for (i in results1_list[[1]]){
name_pattern <- '[A-Z][A-Z]*[A-Z]\\s.*\\s\\s\\s\\s\\s+'
# Find all matches in the text
name_matches <- gregexpr(name_pattern, results1_list[[1]])
# Extract the matching substrings
name_results <- regmatches(results1_list[[1]], name_matches)
}
#this for loop narrows the previously narrowed down strings to only include the names and/or middle initials without leading or trailing whitespaces or special characters
name_pattern2 <- '[A-Z].*[A-Z]'
for (i in name_results){
#Find all matches in the text
name_matches2 <- gregexpr(name_pattern2, name_results)
# Extract the matching substrings
name_results2 <- regmatches(name_results, name_matches2)
}
print('Name Matches:')## [1] "Name Matches:"print(unlist(name_results2))## [1] "GARY HUA" "DAKSHESH DARURI"
## [3] "ADITYA BAJAJ" "PATRICK H SCHILLING"
## [5] "HANSHI ZUO" "HANSEN SONG"
## [7] "GARY DEE SWATHELL" "EZEKIEL HOUGHTON"
## [9] "STEFANO LEE" "ANVIT RAO"
## [11] "CAMERON WILLIAM MC LEMAN" "KENNETH J TACK"
## [13] "TORRANCE HENRY JR" "BRADLEY SHAW"
## [15] "ZACHARY JAMES HOUGHTON" "MIKE NIKITIN"
## [17] "RONALD GRZEGORCZYK" "DAVID SUNDEEN"
## [19] "DIPANKAR ROY" "JASON ZHENG"
## [21] "DINH DANG BUI" "EUGENE L MCCLURE"
## [23] "ALAN BUI" "MICHAEL R ALDRICH"
## [25] "LOREN SCHWIEBERT" "MAX ZHU"
## [27] "GAURAV GIDWANI" "SOFIA ADINA STANESCU-BELLU"
## [29] "CHIEDOZIE OKORIE" "GEORGE AVERY JONES"
## [31] "RISHI SHETTY" "JOSHUA PHILIP MATHEWS"
## [33] "JADE GE" "MICHAEL JEFFERY THOMAS"
## [35] "JOSHUA DAVID LEE" "SIDDHARTH JHA"
## [37] "AMIYATOSH PWNANANDAM" "BRIAN LIU"
## [39] "JOEL R HENDON" "FOREST ZHANG"
## [41] "KYLE WILLIAM MURPHY" "JARED GE"
## [43] "ROBERT GLEN VASEY" "JUSTIN D SCHILLING"
## [45] "DEREK YAN" "JACOB ALEXANDER LAVALLEY"
## [47] "ERIC WRIGHT" "DANIEL KHAIN"
## [49] "MICHAEL J MARTIN" "SHIVAM JHA"
## [51] "TEJAS AYYAGARI" "ETHAN GUO"
## [53] "JOSE C YBARRA" "LARRY HODGE"
## [55] "ALEX KONG" "MARISA RICCI"
## [57] "MICHAEL LU" "VIRAJ MOHILE"
## [59] "SEAN M MC CORMICK" "JULIA SHEN"
## [61] "JEZZEL FARKAS" "ASHWIN BALAJI"
## [63] "THOMAS JOSEPH HOSMER" "BEN LI"As in (4a), we use an identical process to obtain the state of origin information, but with a different pattern. The desired data is contained in ‘state_results2’.
# column 2: states
for (i in results1_list[[1]]){
state_pattern <- '\\s\\s[A-Z][A-Z]\\s'
# Find all matches in the text
state_matches <- gregexpr(state_pattern, results1_list[[1]])
# Extract the matching substrings
state_results <- regmatches(results1_list[[1]], state_matches)
}
state_pattern2 <- '[A-Z][A-Z]'
for (i in state_results){
#Find all matches in the text
state_matches2 <- gregexpr(state_pattern2, state_results)
# Extract the matching substrings
state_results2 <- regmatches(state_results, state_matches2)
}
print('State Matches:')## [1] "State Matches:"print(unlist(state_results2))## [1] "ON" "MI" "MI" "MI" "MI" "OH" "MI" "MI" "ON" "MI" "MI" "MI" "MI" "MI" "MI"
## [16] "MI" "MI" "MI" "MI" "MI" "ON" "MI" "ON" "MI" "MI" "ON" "MI" "MI" "MI" "ON"
## [31] "MI" "ON" "MI" "MI" "MI" "MI" "MI" "MI" "MI" "MI" "MI" "MI" "MI" "MI" "MI"
## [46] "MI" "MI" "MI" "MI" "MI" "MI" "MI" "MI" "MI" "MI" "MI" "MI" "MI" "MI" "MI"
## [61] "ON" "MI" "MI" "MI"We continue the process for the total points for each contestant. Here we only required one for loop as it accomplished the narrowing down process immediately. This data is contained in ‘pts_results’.
#column 3: total points
for (i in results1_list){
pts_pattern <- '[0-9]\\.[0-9]'
# Find all matches in the text
pts_matches <- gregexpr(pts_pattern, results1_list[[1]])
# Extract the matching substrings
pts_results <- regmatches(results1_list[[1]], pts_matches)
#print('\\.*\\w\\w*\\s\\w*')
}
print('Point Matches:')## [1] "Point Matches:"print(unlist(pts_results))## [1] "6.0" "6.0" "6.0" "5.5" "5.5" "5.0" "5.0" "5.0" "5.0" "5.0" "4.5" "4.5"
## [13] "4.5" "4.5" "4.5" "4.0" "4.0" "4.0" "4.0" "4.0" "4.0" "4.0" "4.0" "4.0"
## [25] "3.5" "3.5" "3.5" "3.5" "3.5" "3.5" "3.5" "3.5" "3.5" "3.5" "3.5" "3.5"
## [37] "3.5" "3.0" "3.0" "3.0" "3.0" "3.0" "3.0" "3.0" "3.0" "3.0" "2.5" "2.5"
## [49] "2.5" "2.5" "2.5" "2.5" "2.0" "2.0" "2.0" "2.0" "2.0" "2.0" "2.0" "1.5"
## [61] "1.5" "1.0" "1.0" "1.0"In columns 1 and 2, we used two for loops since it was more difficult to narrow down the desired information. Likewise, we do so to obtain the contestant ratings and contain it in ‘rating_results2’.
#column 4: Player's Pre-Rating
for (i in results1_list){
rating_pattern <- 'R:\\s*[0-9]*'
# Find all matches in the text
rating_matches <- gregexpr(rating_pattern, results1_list[[1]])
# Extract the matching substrings
rating_results <- regmatches(results1_list[[1]], rating_matches)
}
rating_pattern2 <- '[0-9][0-9]*'
for (i in rating_results){
#Find all matches in the text
rating_matches2 <- gregexpr(rating_pattern2, rating_results)
# Extract the matching substrings
rating_results2 <- regmatches(rating_results, rating_matches2)
}
print('Pre-Rating Matches:')## [1] "Pre-Rating Matches:"print(unlist(rating_results2))## [1] "1794" "1553" "1384" "1716" "1655" "1686" "1649" "1641" "1411" "1365"
## [11] "1712" "1663" "1666" "1610" "1220" "1604" "1629" "1600" "1564" "1595"
## [21] "1563" "1555" "1363" "1229" "1745" "1579" "1552" "1507" "1602" "1522"
## [31] "1494" "1441" "1449" "1399" "1438" "1355" "980" "1423" "1436" "1348"
## [41] "1403" "1332" "1283" "1199" "1242" "377" "1362" "1382" "1291" "1056"
## [51] "1011" "935" "1393" "1270" "1186" "1153" "1092" "917" "853" "967"
## [61] "955" "1530" "1175" "1163"The fifth and final column must represent the average pre-rating of all of an individual’s opponents. This information is not readily available, however, we can produce it using the data collected from their tournament match-ups. First, we can use the same pattern matching process, as shown previously, to obtain the sequence of chess match outcomes for each player.
#column 5: Average Pre-Chess Rating of Opponents
#game stats
for (i in results1_list[[1]]){
stats_pattern <- '[0-9]\\.[0-9].*[WLD]\\s\\s\\s*[0-9]*'
# Find all matches in the text
stats_matches <- gregexpr(stats_pattern, results1_list[[1]])
# Extract the matching substrings
stats_results <- regmatches(results1_list[[1]], stats_matches)
#print('\\.*\\w\\w*\\s\\w*')
}
print('Statistics Matches:')## [1] "Statistics Matches:"#print(unlist(games_results))
head(stats_results)## [[1]]
## [1] "6.0 |W 39|W 21|W 18|W 14|W 7|D 12|D 4"
##
## [[2]]
## [1] "6.0 |W 63|W 58|L 4|W 17|W 16|W 20|W 7"
##
## [[3]]
## [1] "6.0 |L 8|W 61|W 25|W 21|W 11|W 13|W 12"
##
## [[4]]
## [1] "5.5 |W 23|D 28|W 2|W 26|D 5|W 19|D 1"
##
## [[5]]
## [1] "5.5 |W 45|W 37|D 12|D 13|D 4|W 14|W 17"
##
## [[6]]
## [1] "5.0 |W 34|D 29|L 11|W 35|D 10|W 27|W 21"We only want the data on wins (represented by ‘W’), losses (represented by ‘L’), or draws (represented by ‘D’). All other outcomes, ie. ‘B’,‘H’,‘U’, imply that no game was played, so we exclude these. Regardless of the win/loss/draw outcome, we count these as played games. So, for every row/player, we use the opponent information for each game, extract the data and re-list it into a new list called ‘opp_idlist’. The for loop accomplishes this for every row/player.
opp_idlist <- list()
for (i in stats_results){
i = str_sub(i, 4, -1)
x <- str_extract_all(i, "\\d+")
opp_idlist <- append(opp_idlist, x)
}
head(opp_idlist)## [[1]]
## [1] "39" "21" "18" "14" "7" "12" "4"
##
## [[2]]
## [1] "63" "58" "4" "17" "16" "20" "7"
##
## [[3]]
## [1] "8" "61" "25" "21" "11" "13" "12"
##
## [[4]]
## [1] "23" "28" "2" "26" "5" "19" "1"
##
## [[5]]
## [1] "45" "37" "12" "13" "4" "14" "17"
##
## [[6]]
## [1] "34" "29" "11" "35" "10" "27" "21"Now that we have the listings of played opponents for each contestant in the form of their player ID’s, we can use this to return a corresponding listing of these opponent’s pre-ratings. Then we can take the average rating for those that completed games against the contestant in question.
Before we load that data in. Let’s first create the data-frame with what we have so far. To do so, we must ensure that whatever we load into each column matches the number of elements in the other columns and that they are all of the correct data type.
#set column names using unlisted data
name = unlist(name_results2)
state = unlist(state_results2)
points = unlist(pts_results)
rating = unlist(rating_results2)
#verifying matching lengths
length(name)## [1] 64length(state)## [1] 64length(points)## [1] 64length(rating)## [1] 64#verifying correct data types
typeof(name)## [1] "character"typeof(state)## [1] "character"typeof(points)## [1] "character"typeof(rating)## [1] "character"Now we have what we need to create our ‘tidy’ data-frame. We will call it ‘chess_df’.
chess_df <- data.frame(Name = name, State = state, Points = points, Pre_Rating = rating)
head(chess_df)## Name State Points 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 1686Notice that this data-frame is still missing a required column, namely the one that represents the opponents’ average pre-ratings.
In order to create this column, we must match the opponent’s player ID to their corresponding pre-rating, then take the average. However, we run into a problem taking this average with the current data since the values extracted from the pre-ratings are of ‘character’ data types and cannot be manipulated using Mathematical or Logical operations. Below, I created the new column labeled “Pre-Rating” and in the same instance, I transformed this column from ‘character’ values to ‘numerical’ values. This allows us to take the average properly. We can confirm below.
#turn Pre_Rating column from character to numeric
chess_df$Pre_Rating <- as.numeric(as.character(chess_df$Pre_Rating))
sapply(chess_df, class)## Name State Points Pre_Rating
## "character" "character" "character" "numeric"Now that we have the correct data types as well as matching column lengths, we can integrate this to obtain the correct data-frame. I used a nested for loop to iterate through the list of opponent player IDs, and since most contestants played multiple opponents, I iterated within each row to create a new list of with the ratings of each player’s opponents. Since this column now contains numerical values, we can use the mean() function to obtain the average opponent rating for each player. I’ve assigned it to the variable ‘opp_avg’ below and loaded the column values into the chess_df data-frame.
opp_rlist <- list()
opp_avg <- list()
#this for loop iterates through the list of contestants
#which will each have several opponent IDs
for (j in (1:64)){
#for each individual contestant, this for loop
#iterates through their respective opponent IDs
for (i in opp_idlist[j]){
#assign the returned values for each iteration to the variable y
y <- as.list(chess_df[i,]['Pre_Rating'])
#append that instance of y to the opponent ratings list before iterating again
opp_rlist <- append(opp_rlist, y)
}
#now that opp_rlist is a list of numerical opponent ratings,
#we can take the mean of the unlisted data
m = mean(as.numeric(unlist(opp_rlist[j])))
#print(m)
#we assign that mean calculation to opp_avg
opp_avg <- append(opp_avg, m)
}
#load the averages into their own column within the data-frame
chess_df$AvgOpp_Rating <- unlist(opp_avg)
#produce the data-frame
head(chess_df)## Name State Points Pre_Rating AvgOpp_Rating
## 1 GARY HUA ON 6.0 1794 1605.286
## 2 DAKSHESH DARURI MI 6.0 1553 1469.286
## 3 ADITYA BAJAJ MI 6.0 1384 1563.571
## 4 PATRICK H SCHILLING MI 5.5 1716 1573.571
## 5 HANSHI ZUO MI 5.5 1655 1500.857
## 6 HANSEN SONG OH 5.0 1686 1518.714The final step in this process is to write the completed data-frame to a ‘.csv’ file to be accessible by other database management systems like MySQL. I do this by using the ‘write.csv()’ function on ‘chess_df’ and assigning it the file name “chess_data.csv”. This file should be located in the same directory as the R markdown file used to create it. (I’ve included it in the appropriate Github repository).
write.csv(chess_df, file = "chess_data.csv")
read.csv('chess_data.csv')## X Name State Points Pre_Rating AvgOpp_Rating
## 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.143
## 8 8 EZEKIEL HOUGHTON MI 5.0 1641 1468.429
## 9 9 STEFANO LEE ON 5.0 1411 1523.143
## 10 10 ANVIT RAO MI 5.0 1365 1554.143
## 11 11 CAMERON WILLIAM MC LEMAN 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.286
## 20 20 JASON ZHENG MI 4.0 1595 1410.857
## 21 21 DINH DANG BUI ON 4.0 1563 1470.429
## 22 22 EUGENE L MCCLURE MI 4.0 1555 1300.333
## 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.286
## 26 26 MAX ZHU ON 3.5 1579 1506.857
## 27 27 GAURAV GIDWANI MI 3.5 1552 1221.667
## 28 28 SOFIA ADINA STANESCU-BELLU 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.143
## 31 31 RISHI SHETTY MI 3.5 1494 1259.857
## 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.167
## 39 39 JOEL R HENDON MI 3.0 1436 1429.571
## 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.857
## 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.333
## 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 CORMICK 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.286
## 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.000In this project, we successfully cleaned, restructured, and extracted the necessary information from the raw chess tournament data. By carefully selecting the relevant data points and organizing them into a structured format, we created a more readable and usable data frame. The initial unstructured ‘.txt’ file was transformed into a tidy and well-organized format, removing unnecessary characters and clutter. Finally, the cleaned data was exported as a ‘.csv’ file, allowing for easy access and further analysis. This process not only improved the clarity of the data but also laid the foundation for more efficient and meaningful analysis in future work.