Zaina Hassan
GitHub
Repository
For this project, I will use R to clean and organize data from a text file containing chess tournament results. The goal is to create a structured dataset containing each player’s name, state, total points, pre-tournament rating, and the average pre-tournament rating of their opponents.
I will first import the tournament text file into R and identify the lines containing player information. I will then extract the relevant player information and the opponent numbers listed for each round. The opponent numbers will be matched to the corresponding players so that their pre-tournament ratings can be identified. I will calculate the average opponent pre-rating for each player and organize the results into a final data frame. Finally, I will export the completed data frame as a CSV file.
One anticipated complication is that the tournament data is stored in a semi-structured text format rather than a traditional table, so the player information will need to be extracted from different lines and positions in the file. Some pre-tournament ratings also contain additional characters, such as provisional ratings like “1641P17”, which will need to be cleaned to obtain the numeric rating. In addition, some rounds contain codes such as B, H, U, or X instead of an opponent number. These entries will need to be handled carefully so that only actual opponents are included when calculating the average opponent pre-rating. Finally, opponent numbers must be correctly matched back to each player’s pre-tournament rating.
The tournament results are stored in a text file rather than a
traditional tabular format. I first use readLines() to
import the file so that each line can be examined and processed
individually.
chess_raw <- readLines("tournamentinfo.txt")## Warning in readLines("tournamentinfo.txt"): incomplete final line found on
## 'tournamentinfo.txt'head(chess_raw)## [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 |"Each player record follows a three-line pattern: the player’s main information, their state and rating information, and a separator line. Starting at line 5, every third line therefore contains the next player’s main information.
player_lines <- chess_raw[seq(5, length(chess_raw), by = 3)]
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|"Each player’s main information is separated by the |
character. I split each line at these separators and extract the
player’s name and total tournament points. The total points are
converted from text to numeric values.
player_split <- strsplit(player_lines, "\\|")
player_name <- trimws(sapply(player_split, `[`, 2))
total_points <- as.numeric(trimws(sapply(player_split, `[`, 3)))
head(player_name)## [1] "GARY HUA" "DAKSHESH DARURI" "ADITYA BAJAJ"
## [4] "PATRICK H SCHILLING" "HANSHI ZUO" "HANSEN SONG"head(total_points)## [1] 6.0 6.0 6.0 5.5 5.5 5.0The second line of each player record contains the player’s state and rating information. Since these records also occur every three lines, I begin at line 6 and select every third line.
rating_lines <- chess_raw[seq(6, length(chess_raw), by = 3)]
head(rating_lines)## [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 |"The rating lines are again separated using the |
character. The first section contains the player’s state. The rating
information is cleaned to extract only the numeric pre-tournament rating
following R:. This also allows provisional ratings
containing additional characters to be stored as numeric ratings.
rating_split <- strsplit(rating_lines, "\\|")
player_state <- trimws(sapply(rating_split, `[`, 1))
rating_text <- sapply(rating_split, `[`, 2)
pre_rating <- as.numeric(
sub(".*R:\\s*([0-9]+).*", "\\1", rating_text)
)
head(player_state)## [1] "ON" "MI" "MI" "MI" "MI" "OH"head(pre_rating)## [1] 1794 1553 1384 1716 1655 1686The information extracted so far is combined into a single data frame. I also assign each player their tournament player number, which will later be used to match opponents to their pre-tournament ratings.
players <- data.frame(
player_number = 1:length(player_name),
player_name = player_name,
state = player_state,
total_points = total_points,
pre_rating = pre_rating
)
head(players)## player_number 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 1686nrow(players)## [1] 64The resulting data frame contains 64 players, confirming that all tournament participants were successfully extracted.
Sections 4 through 10 of each player’s main record represent the seven tournament rounds. I extract these sections so that the opponent number from each round can be identified.
rounds <- t(sapply(player_split, function(x) x[4:10]))
head(rounds)## [,1] [,2] [,3] [,4] [,5] [,6] [,7]
## [1,] "W 39" "W 21" "W 18" "W 14" "W 7" "D 12" "D 4"
## [2,] "W 63" "W 58" "L 4" "W 17" "W 16" "W 20" "W 7"
## [3,] "L 8" "W 61" "W 25" "W 21" "W 11" "W 13" "W 12"
## [4,] "W 23" "D 28" "W 2" "W 26" "D 5" "W 19" "D 1"
## [5,] "W 45" "W 37" "D 12" "D 13" "D 4" "W 14" "W 17"
## [6,] "W 34" "D 29" "L 11" "W 35" "D 10" "W 27" "W 21"Each round contains a result such as W, L,
or D followed by the opponent’s player number. I remove all
non-numeric characters to retain only the opponent number. Rounds
without an opponent number, such as byes or unplayed rounds, are
converted to NA so they are not included in the
opponent-rating calculation.
opponents <- apply(rounds, c(1, 2), function(x) {
number <- gsub("[^0-9]", "", x)
ifelse(number == "", NA, as.numeric(number))
})
head(opponents)## [,1] [,2] [,3] [,4] [,5] [,6] [,7]
## [1,] 39 21 18 14 7 12 4
## [2,] 63 58 4 17 16 20 7
## [3,] 8 61 25 21 11 13 12
## [4,] 23 28 2 26 5 19 1
## [5,] 45 37 12 13 4 14 17
## [6,] 34 29 11 35 10 27 21The opponent numbers correspond to the player numbers in the
players data frame. For each player, I use these numbers to
retrieve the pre-tournament ratings of their actual opponents. I then
calculate the mean of those ratings while excluding rounds without an
opponent. The resulting averages are rounded to whole-number chess
ratings.
avg_opponent_rating <- apply(opponents, 1, function(x) {
opponent_ratings <- players$pre_rating[x[!is.na(x)]]
mean(opponent_ratings)
})
avg_opponent_rating <- round(avg_opponent_rating)
head(avg_opponent_rating)## [1] 1605 1469 1564 1574 1501 1519I create the final data frame using only the five variables required for the project: player name, state, total points, pre-tournament rating, and average opponent pre-tournament rating.
final_data <- data.frame(
Player_Name = players$player_name,
State = players$state,
Total_Points = players$total_points,
Pre_Rating = players$pre_rating,
Avg_Opponent_Pre_Rating = avg_opponent_rating
)
head(final_data)## Player_Name State Total_Points Pre_Rating Avg_Opponent_Pre_Rating
## 1 GARY HUA ON 6.0 1794 1605
## 2 DAKSHESH DARURI MI 6.0 1553 1469
## 3 ADITYA BAJAJ MI 6.0 1384 1564
## 4 PATRICK H SCHILLING MI 5.5 1716 1574
## 5 HANSHI ZUO MI 5.5 1655 1501
## 6 HANSEN SONG OH 5.0 1686 1519Finally, I export the cleaned data frame as a CSV file. Setting
row.names = FALSE prevents R from creating an unnecessary
additional column containing row numbers.
write.csv(
final_data,
"chess_tournament_results.csv",
row.names = FALSE
)
dim(final_data)## [1] 64 5head(final_data)## Player_Name State Total_Points Pre_Rating Avg_Opponent_Pre_Rating
## 1 GARY HUA ON 6.0 1794 1605
## 2 DAKSHESH DARURI MI 6.0 1553 1469
## 3 ADITYA BAJAJ MI 6.0 1384 1564
## 4 PATRICK H SCHILLING MI 5.5 1716 1574
## 5 HANSHI ZUO MI 5.5 1655 1501
## 6 HANSEN SONG OH 5.0 1686 1519tail(final_data)## Player_Name State Total_Points Pre_Rating Avg_Opponent_Pre_Rating
## 59 SEAN M MC CORMICK MI 2.0 853 1319
## 60 JULIA SHEN MI 1.5 967 1330
## 61 JEZZEL FARKAS ON 1.5 955 1327
## 62 ASHWIN BALAJI MI 1.0 1530 1186
## 63 THOMAS JOSEPH HOSMER MI 1.0 1175 1350
## 64 BEN LI MI 1.0 1163 1263The final dataset contains 64 players and the five variables required
for the project: player name, state, total points, pre-tournament
rating, and average pre-tournament rating of opponents. The original
semi-structured text file was successfully cleaned and transformed into
a structured data frame that can be exported and used in other
applications, such as a SQL database. Overall, the project demonstrates
how semi-structured text data can be cleaned, reorganized, and
transformed into a structured dataset in R. The completed data frame was
exported as chess_tournament_results.csv, providing a clean
version of the tournament data containing all 64 players and the
required five variables.
The results show that information stored across different lines of the original text file can be combined into a single organized dataset. Player names and total points were extracted from the first line of each player record, while state and pre-tournament ratings were extracted from the second line. The opponent numbers from each of the seven tournament rounds were then used to match each opponent with their corresponding pre-tournament rating.
The average opponent pre-rating was calculated using only rounds in which an actual opponent was listed. Entries representing byes, half-point byes, unplayed rounds, or other rounds without an opponent number were treated as missing values and were not included in the calculation. This prevented non-player entries from affecting the calculated averages.
As a validation of the results, Gary Hua finished the tournament with 6.0 points and had a pre-tournament rating of 1794. His calculated average opponent pre-rating was approximately 1605 after rounding, which matches the expected result provided in the project instructions. This provided a useful check that the opponent numbers were being matched to the correct pre-tournament ratings.
The main complications involved working with a text file that was not
already organized into rows and columns, handling provisional ratings
containing additional characters, and identifying rounds that did not
contain an opponent. These issues were addressed during the cleaning
process by using the repeating structure of the tournament records,
extracting only the numeric portion of pre-tournament ratings, and
converting rounds without opponent numbers to NA.