# install.packages("tidyverse")Chess Tournament Analysis
Chess Text File Analysis
Approach
Given that this is a project and not a typical assignment, the work to be done will require extensive research on several functions, terminology, and writing and compiling code with trial and error.
Focused Concepts
Regular Expressions
Non-CSV Files
R
The Project
Chess is a game that requires strategy, ability to think ahead, make tough choices and to learn during or after a match. With all these things in chess, it turned into a phenomena to which people host chess tournaments.
Just like any other tournament, there needs to be a way to keep track of every player and their statistics throughout, which leads to this project.
We will be given a file containing information from a chess tournament, our job is to make sure we can convert a non-csv file to a tabular format to be able to use it in SQL or in other scenarios.
Dataset Structure
To give an idea of how the dataset is structured, below is a snippet:
With this snippet, it is clear that the approach will be different than a typical CSV file. Given that, we can apply use cases for regex, regular expressions, to be able to extract pieces of information into their respective variables.
Information Needed:
Player’s Name
Player’s State
Total Number of Points
Player’s Pre-Rating
Average Pre Chess Rating of Opponents
With the nature of this text file, there are patterns shown to be able to extract information such as “- - - - - -” and a delimiter of “|”.
The Plan
Note: this will be a tentative plan
After importing the dataset, the idea will focus on testing for one player rather than the whole file at once, because if it works for one person then it will work for the rest, but there will be some rows where there will be refinement but that will come in later.
We can skip the header rows as it does not contain information to extract, it is there to help us identify the values.
Work row by row to extract values with the correct pattern for their respective variables and values. We can do that by separating into two different variables and to bind them later.
After extracting each pieces of information, then comes the tricky part of average pre chess rating of opponents. First I will need a way to map the player to their pre-rating in order to do the numerical calculation, following that, I need to find the average within the rows rather than columns.
Then after several extractions and calculations, the final table can be built.
Helpful Resources
- Regex101 - Website to help visualize the pattern
- Regex Cheatsheet - Guide to show the matching sequences
When working with Regex, it is not easy to come up with the pattern off the top of your head, using Regex101 will assist in visualizing the pattern and the key components needed for the lines, along with the cheatsheet to explain what each command does in the pattern.
Codebase
Packages To Install
Remove the ‘#’ to install package if you do not have it install already.
Libraries to import
library(tidyverse)Reading in the data
raw_data_text <- readLines("https://raw.githubusercontent.com/HaiderrX/CUNY-SPS-MSDS/refs/heads/main/DATA607/Projects/Project_1/tournamentinfo.txt")
head(raw_data_text)[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 |" Inspecting FIle Format
raw_data_text[4:7][1] "-----------------------------------------------------------------------------------------"
[2] " 1 | GARY HUA |6.0 |W 39|W 21|W 18|W 14|W 7|D 12|D 4|"
[3] " ON | 15445895 / R: 1794 ->1817 |N:2 |W |B |W |B |W |B |W |"
[4] "-----------------------------------------------------------------------------------------"From this chunk above each player statistics are surrounded with a dashed line above and below.
Another thing worth mentioning, each player information are two rows rather than [4:7]. In the example above, [4:7] contains the table with the surrounding dashed lines, but if we were to change the numbers to [5:6] it will show the information only without the dashed lines.
raw_data_text[5:6][1] " 1 | GARY HUA |6.0 |W 39|W 21|W 18|W 14|W 7|D 12|D 4|"
[2] " ON | 15445895 / R: 1794 ->1817 |N:2 |W |B |W |B |W |B |W |"Another thing to note, we can see that we can actually start at line 5 rather than 1, so when we actually start the process of extraction we can just start at 5.
However, it is easier to safely remove the “- - - - - -” lines and create a new variable to store the new format.
Filtering Boundary Lines
Doing this step is crucial as it can safely remove lines that will affect text extraction.
filtered_lines <- raw_data_text[!grepl("---", raw_data_text)]
head(filtered_lines)[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] " 1 | GARY HUA |6.0 |W 39|W 21|W 18|W 14|W 7|D 12|D 4|"
[4] " ON | 15445895 / R: 1794 ->1817 |N:2 |W |B |W |B |W |B |W |"
[5] " 2 | DAKSHESH DARURI |6.0 |W 63|W 58|L 4|W 17|W 16|W 20|W 7|"
[6] " MI | 14598900 / R: 1553 ->1663 |N:2 |B |W |B |W |B |W |B |" Line Extraction
To make it easier to extract pieces of the puzzle, it is better to extract both lines in their respective variables:
player_line: First line of player name and matchesplayer_state_line: Second line that contains information such as state, ratings
Player Name Extraction
To reiterate the line we would be extracting
filtered_lines[3][1] " 1 | GARY HUA |6.0 |W 39|W 21|W 18|W 14|W 7|D 12|D 4|"To build up the pattern for player_line, what we want to do is:
- Start at the beginning of the line
- Locate a whitespaces or tabs
- Locate the digit(s)
- Noting that there is a pipe delimiter with a space before it
test_line_1 <- filtered_lines[3]
str_match(test_line_1, "^\\s*\\d+\\s*\\|\\s*([^|]+?)\\s*\\|\\s*(\\d+\\.\\d+)") [,1] [,2] [,3]
[1,] " 1 | GARY HUA |6.0" "GARY HUA" "6.0"Pattern Test Explain:
^\\s*\\d+\\s\\|finds lines with a pair number next to a pipe:- Example:
1 |
- Example:
\\s*(\[\^\|\]+?)\\s*\\\|captures the name without a pipe operator- Example:
GARY HUA
- Example:
\\s*(\\d+\\.\\d+)captures the total points- Example:
6.0
- Example:
player_lines <- filtered_lines[str_detect(filtered_lines, "^\\s*\\d+\\s*\\|\\s*([^|]+?)\\s*\\|\\s*(\\d+\\.\\d+)")]
head(player_lines)[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|"We have successfully extracted the player lines without their state rows, now the next step is to split the information so we can extract their names only.
Player Name Split Extraction
Within this chunk, the goal is to split each row with the pipe delimiter as well as to correctly format each name so that it is not all capitalized.
Additionally, we want to split the total points for each row to its own variable as that will be its own column in the final table.
player_split <- strsplit(player_lines, "\\|") # splits with the pipe as a delimeter
# list has been made, we need to trim rows and apply this across the list, use sapply and trim rows function
# the list is in 2 dimensions, so we need the 2nd position for names, and 3rd for total points
head(player_split, 3)[[1]]
[1] " 1 " " GARY HUA "
[3] "6.0 " "W 39"
[5] "W 21" "W 18"
[7] "W 14" "W 7"
[9] "D 12" "D 4"
[[2]]
[1] " 2 " " DAKSHESH DARURI "
[3] "6.0 " "W 63"
[5] "W 58" "L 4"
[7] "W 17" "W 16"
[9] "W 20" "W 7"
[[3]]
[1] " 3 " " ADITYA BAJAJ "
[3] "6.0 " "L 8"
[5] "W 61" "W 25"
[7] "W 21" "W 11"
[9] "W 13" "W 12" # Player Names
#\(x) refers to the vector we split
player_name <- trimws(sapply(player_split, \(x) x[2]))
player_name_titled <- str_to_title(player_name)
head(player_name_titled) [1] "Gary Hua" "Dakshesh Daruri" "Aditya Bajaj"
[4] "Patrick H Schilling" "Hanshi Zuo" "Hansen Song" # Total Points
total_points <- as.numeric(trimws(sapply(player_split, \(x) x[3])))
head(total_points)[1] 6.0 6.0 6.0 5.5 5.5 5.0State and Rating Extraction
Same approach as before, we can test on one line before on all of them.
We want the state name and pre_rating score.
test_line_2 <- filtered_lines[4]
str_match(test_line_2, "^\\s*(\\w*)\\s*\\|\\s*\\d+\\s*/\\s*\\w*\\D\\s*(\\d+)") [,1] [,2] [,3]
[1,] " ON | 15445895 / R: 1794" "ON" "1794"Pattern Test Explain:
^\\s*(\\w*)\\s*\\|finds lines with a string text next to a pipe:- Example:
ON |
- Example:
\\s*\\d+\\s*/After the pipe it will go through the digits (the player ID) up to forward slash- Example:
15445895
- Example:
\\s*\\w*\\D\\s*(\\d+)After the forward slash, it will go up to their pre-rating score- Example:
R: 1794
- Example:
player_state_line <- filtered_lines[str_detect(filtered_lines, "^\\s*(\\w*)\\s*\\|\\s*\\d+\\s*/\\s*\\w*\\D\\s*(\\d+)")]
head(player_state_line)[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 |"State Name Split Extraction
Similar to the player name split extraction part, we want to split each row with the pipe as the delimiter.
We can leave the states in their abbreviated format as it is easier to read.
For pre-ratings, we will need to do more regex to extract it as it is further down in each line.
state_split <- strsplit(player_state_line, "\\|")
head(state_split, 3)[[1]]
[1] " ON " " 15445895 / R: 1794 ->1817 "
[3] "N:2 " "W "
[5] "B " "W "
[7] "B " "W "
[9] "B " "W "
[[2]]
[1] " MI " " 14598900 / R: 1553 ->1663 "
[3] "N:2 " "B "
[5] "W " "B "
[7] "W " "B "
[9] "W " "B "
[[3]]
[1] " MI " " 14959604 / R: 1384 ->1640 "
[3] "N:2 " "W "
[5] "B " "W "
[7] "B " "W "
[9] "B " "W " # State Names
#\(x) refers to the vector we split
player_state <- trimws(sapply(state_split, \(x) x[1]))
head(player_state) [1] "ON" "MI" "MI" "MI" "MI" "OH"# Pre-Rating
# To note, we need to extract it as it is combined with other things too
pre_rating_full_text <- trimws(sapply(state_split, \(x) x[2]))
head(pre_rating_full_text, 3) # extract again to make it simpler[1] "15445895 / R: 1794 ->1817" "14598900 / R: 1553 ->1663"
[3] "14959604 / R: 1384 ->1640"pre_rating <- as.numeric(str_match(pre_rating_full_text, "^\\d+\\s\\/\\s\\w\\D\\s*(\\d+)")[,2])
head(pre_rating)[1] 1794 1553 1384 1716 1655 1686Combining Variables to Table
Now that we have 90% of the pieces of the puzzle, we can create a new dataframe to store these information.
A new column will be added to mimic the pair_num column at the start so it can be utilized to calculate the last column, the opponents average pre-rating.
players_df <- data.frame(
pair_num = 1:length(player_name_titled),
player_name = player_name_titled,
state = player_state,
total_points = total_points,
pre_rating = pre_rating
)
head(players_df) pair_num player_name state total_points 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 1686Opponents Average Pre-Rating
The final requirement to compute the opponent average pre-rating.
Example:
- Player: Gary Hua
- Played Against: Players 39, 21, 18, 7, 12, 4
- Map the their pair number with their pre-rating
- Calculate Pre-Rating and store in variable
- Create column that does it for each player
We know that the opponents’ pair number is in the first row, player_split, so we can use that variable to start the process of creating that column.
Extracting Opponents Pair Numbers
Using regex, we will extract from player_split to have strings such as: “W 39 W 21 W 18 W 14 W 7 D 12 D 4”
test_line_3 <- player_lines[1]
str_match(test_line_3, "[A-Z]\\s+\\d+") [,1]
[1,] "W 39"str_extract_all(test_line_3, "[A-Z]\\s+\\d+")[[1]]
[1] "W 39" "W 21" "W 18" "W 14" "W 7" "D 12" "D 4"opponent_numbers <- str_extract_all(player_lines, "[A-Z]\\s+\\d+")
head(opponent_numbers, 3)[[1]]
[1] "W 39" "W 21" "W 18" "W 14" "W 7" "D 12" "D 4"
[[2]]
[1] "W 63" "W 58" "L 4" "W 17" "W 16" "W 20" "W 7"
[[3]]
[1] "L 8" "W 61" "W 25" "W 21" "W 11" "W 13" "W 12"Now we can loop and apply the numbers into one column, thereby getting rid of the characters ‘W’, ‘L’, ‘D’.
opponent_pair_numbers <- lapply(opponent_numbers, function(x) str_extract(x, "\\d+"))
head(opponent_pair_numbers, 3)[[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"We now have a new 2-D list opponent_pair_numbers which contains the pair numbers of the other players. Now the next step is to figure out how to map the number to the other players’ pre-rating.
Calculating Mean
Lets test on the first row before doing for all of them:
Example:
Gary Hua, his opponents pre-average rating would be 1605.
mean(players_df[players_df$pair_num %in% as.numeric(opponent_pair_numbers[[1]]), "pre_rating"]) |>
round(0)[1] 1605After calculating, we have gotten 1605 correctly, now we need to do it for all of them.
opponents_pre_avg_mean <- sapply(opponent_pair_numbers, function(x) {
round(mean(players_df[players_df$pair_num %in% as.numeric(x), "pre_rating"]), 0)
})
opponents_pre_avg_mean [1] 1605 1469 1564 1574 1501 1519 1372 1468 1523 1554 1468 1506 1498 1515 1484
[16] 1386 1499 1480 1426 1411 1470 1300 1214 1357 1363 1507 1222 1522 1314 1144
[31] 1260 1379 1277 1375 1150 1388 1385 1539 1430 1391 1248 1150 1107 1327 1152
[46] 1358 1392 1356 1286 1296 1356 1495 1345 1206 1406 1414 1363 1391 1319 1330
[61] 1327 1186 1350 1263Creating the Final Table
players_df_final <- data.frame(
pair_num = 1:length(player_name_titled),
player_name = player_name_titled,
state = player_state,
total_points = total_points,
pre_rating = pre_rating,
opponent_avg_pre_rating = opponents_pre_avg_mean
)
head(players_df_final) pair_num player_name state total_points 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 1686
opponent_avg_pre_rating
1 1605
2 1469
3 1564
4 1574
5 1501
6 1519And with that, we have created a new dataframe from a non-csv file to a csv file
Data Dictionary
| Variable | Meaning |
|---|---|
pair_num |
Identifier for player |
player_name |
The player’s name |
state |
Which state the player is from |
total_points |
Total points that the player has accumulated from games played |
|
Player’s pre-rating before playing games in the tournament The average of all the opponents that the player has played against in the tournament |
Export as CSV
write.csv(
players_df_final,
"chess_tournament_results.csv",
row.names = FALSE
)