In this project, 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. So, I will use R code to clean and organize data from a text file containing chess tournament results for this project.
At first, I will import ’tournamentinfo.txt’ text file into R and identify the lines containing player information. Then I will 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 format file.
Challenge of this project is, 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.
Code Base
Import data file
Chess tournament results are stored in a ’tournamentinfo.txt’ text file rather than a traditional tabular format. I will use readLines() in import the file that each line can be examined and processed individually.
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)
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.
The 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)
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.
The 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.
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 1686
nrow(players)
[1] 64
The resulting data frame contains 64 players, confirming that all tournament participants were successfully extracted.
Extract Round Results
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.
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.
The 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.
I 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.
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 1519
Export the CSV File
Finally, 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.
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 1519
tail(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 1263
Results
Here, the 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 completed data frame was exported as tournament_results.csv, providing a clean version of the tournament data containing all 64 players and the required five variables. As a validation of the results, player ‘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.
Overall, the project demonstrates how 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. As well, how semi-structured text data can be cleaned, reorganized, and transformed into a structured dataset in R.