For Project 1, I will work with the provided chess tournament cross-table text file and transform the semi-structured data into a clean dataset using R. The final dataset will contain each player’s name, state, total points, pre-tournament rating, and average pre-tournament rating of the opponents they played. My plan is to first import the text file and identify the repeating structure of the tournament results. I will then extract the player information and opponent numbers, match the opponent numbers back to the appropriate players and ratings, and calculate the average opponent rating for each player.
One challenge I anticipate is that the original dataset is not organized like a traditional spreadsheet. Each player’s information is spread across two lines, and the round results contain both game outcomes and opponent numbers. I will need to clean and restructure these fields carefully before creating the final dataset and exporting it as a CSV file.
The purpose of Project 1 is to practice cleaning and transforming semi-structured data using R. Using chess tournament results, I will extract each player’s name, state, total points, and pre-tournament rating, then calculate the average pre-tournament rating of their opponents. The final cleaned dataset will be organized and exported as a CSV file for further analysis or database use.
The chess tournament data is stored in a semi-structured text format rather than a traditional rectangular dataset. I will first import the raw text and inspect its structure before extracting the player information needed for the final dataset. Then we want to see the first several lines of the tournament file for inspection.
data_url <- "https://raw.githubusercontent.com/yeimiperez14/Data_607/refs/heads/main/Project%201/tournamentinfo.txt"
chess_raw <- readLines(data_url, warn = FALSE)head(chess_raw, 20)## [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 |"
## [7] "-----------------------------------------------------------------------------------------"
## [8] " 2 | DAKSHESH DARURI |6.0 |W 63|W 58|L 4|W 17|W 16|W 20|W 7|"
## [9] " MI | 14598900 / R: 1553 ->1663 |N:2 |B |W |B |W |B |W |B |"
## [10] "-----------------------------------------------------------------------------------------"
## [11] " 3 | ADITYA BAJAJ |6.0 |L 8|W 61|W 25|W 21|W 11|W 13|W 12|"
## [12] " MI | 14959604 / R: 1384 ->1640 |N:2 |W |B |W |B |W |B |W |"
## [13] "-----------------------------------------------------------------------------------------"
## [14] " 4 | PATRICK H SCHILLING |5.5 |W 23|D 28|W 2|W 26|D 5|W 19|D 1|"
## [15] " MI | 12616049 / R: 1716 ->1744 |N:2 |W |B |W |B |W |B |B |"
## [16] "-----------------------------------------------------------------------------------------"
## [17] " 5 | HANSHI ZUO |5.5 |W 45|W 37|D 12|D 13|D 4|W 14|W 17|"
## [18] " MI | 14601533 / R: 1655 ->1690 |N:2 |B |W |B |W |B |W |B |"
## [19] "-----------------------------------------------------------------------------------------"
## [20] " 6 | HANSEN SONG |5.0 |W 34|D 29|L 11|W 35|D 10|W 27|W 21|"The tournament file contains several types of rows, including headers, separator lines, player result rows, and player detail rows. The player result rows begin with a numerical player number, so I will use that pattern to identify them.
player_rows <- chess_raw[
grepl("^\\s*[0-9]+\\s*\\|", chess_raw)
]
head(player_rows)## [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|"grepl() searches each line of the text file for a particular pattern: ^\s[0-9]+\s\| means ^ (start of the line),\s (allow spaces), [0-9]+ (look for one or more numbers), and \| (look for the pipe symbol). The matching rows are saved as player_rows.
I check the number of player rows that were identified to make sure the extraction step returned a reasonable number of records before continuing.
length(player_rows)## [1] 64This gives us the number of player records identified and stored in player_rows.
Each player row contains several pieces of information separated by the pipe symbol. I split each row at the pipe separators so that the player number, name, points, and round results can be accessed individually.
player_parts <- strsplit(player_rows, "\\|")strsplit() splits text into separate pieces and the | separator in the tournament file adds individual seprated fields that get stored in player_parts.
The first field of each player row contains the player’s tournament number. I extract this value because the player number will later be used to connect each opponent number with that opponent’s pre-tournament rating.
player_number <- sapply(
player_parts,
function(x) as.integer(trimws(x[1]))
)
head(player_number)## [1] 1 2 3 4 5 6sapply() applies the same operation to every player’s row, selects the first field x[1], removes unnecessary spaces trimws(), and onverts the player number from text into an integer as.integer().
#Extracting Player Names
The second field of each player row contains the player’s name. I extract the second field and remove extra spaces so that each player name is stored cleanly.
player_name <- sapply(
player_parts,
function(x) trimws(x[2])
)
head(player_name)## [1] "GARY HUA" "DAKSHESH DARURI" "ADITYA BAJAJ"
## [4] "PATRICK H SCHILLING" "HANSHI ZUO" "HANSEN SONG"#Extracting Total Tournament Points
The third field contains the player’s total tournament points. I extract this field and convert it to numeric format so the values can be treated as numbers rather than text.
total_points <- sapply(
player_parts,
function(x) as.numeric(trimws(x[3]))
)
head(total_points)## [1] 6.0 6.0 6.0 5.5 5.5 5.0This selects the third field from each player’s row that contains values like 6.0 and converts those values into numeric data, so that we can quantify.
The tournament file stores additional information for each player on a second line. These detail rows begin with a two-letter state abbreviation followed by a pipe symbol. I identify these rows separately so that the state and pre-tournament rating can be extracted.
detail_rows <- chess_raw[
grepl("^\\s*[A-Z]{2}\\s*\\|", chess_raw)
]
head(detail_rows)## [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 detail rows also use pipe symbols to separate information. I split these rows into individual fields so that the state and rating information can be extracted separately.
detail_parts <- strsplit(detail_rows, "\\|")The first field of each detail row contains the player’s state or regional abbreviation. I extract this field and remove any extra spaces.
player_state <- sapply(
detail_parts,
function(x) trimws(x[1])
)
head(player_state)## [1] "ON" "MI" "MI" "MI" "MI" "OH"The second field of the detail row contains several values, including a player ID, the pre-tournament rating, and the post-tournament rating. I first isolate this field before extracting only the pre-tournament rating.
rating_field <- sapply(
detail_parts,
function(x) trimws(x[2])
)
head(rating_field)## [1] "15445895 / R: 1794 ->1817" "14598900 / R: 1553 ->1663"
## [3] "14959604 / R: 1384 ->1640" "12616049 / R: 1716 ->1744"
## [5] "14601533 / R: 1655 ->1690" "15055204 / R: 1686 ->1687"This extracts the second section from every detail row.
The pre-tournament rating appears after the text R: in
the rating field. I use a regular expression to capture the number
immediately following R: and convert it to numeric
format.
pre_rating <- as.numeric(
sub(
".*R:\\s*([0-9]+).*",
"\\1",
rating_field
)
)
head(pre_rating)## [1] 1794 1553 1384 1716 1655 1686sub() searches text for a pattern and replaces it, that captures numbers after R, and turns it into a number.
I’ll combine the information extracted so far into a structured data frame. At this stage, the table contains the player’s tournament number, name, state, total points, and pre-tournament rating.
players <- data.frame(
player_number,
player_name,
player_state,
total_points,
pre_rating
)
head(players)## player_number player_name player_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 1686data.frame() takes the separate vectors created and combines them in one rectangular dataset.
Each tournament round contains a result followed by the opponent’s
player number. For example, W 39 indicates that the player
won against player number 39. I create a function that extracts the
numerical opponent IDs from all of the round fields for each player.
extract_opponents <- function(x) {
fields <- strsplit(x, "\\|")[[1]]
round_fields <- fields[4:length(fields)]
opponent_numbers <- sapply(
round_fields,
function(field) {
match_value <- regmatches(
field,
regexpr("[0-9]+", field)
)
if (length(match_value) == 0 || match_value == "") {
NA
} else {
as.integer(match_value)
}
}
)
opponent_numbers[!is.na(opponent_numbers)]
}strsplit(x, “\|”) splits one player’s row into its fields, then fields[4:length(fields)] selects the tournament round information because the first three fields contain player number/name/total points. regexpr(“[0-9]+”, field) looks for a number in each row and !is.na() removed missing values.
Before applying the function to every player, we can test it using the first player, Gary Hua. This allows me to verify that the opponent numbers are being extracted correctly.
gary_opponents <- extract_opponents(player_rows[1])
gary_opponents## W 39 W 21 W 18 W 14 W 7 D 12 D 4
## 39 21 18 14 7 12 4player_rows[1] selects the first player’s row.
The opponent numbers identify players rather than ratings. I’ll therefore match each opponent number to the corresponding player number in the player table and retrieve that opponent’s pre-tournament rating.
gary_ratings <- players$pre_rating[
match(gary_opponents, players$player_number)
]
gary_ratings## [1] 1436 1563 1600 1610 1649 1663 1716match() finds where each opponent number appears in players$player_number.
I’ll calculate the average of Gary Hua’s opponents’ pre-tournament ratings as a validation step. The project instructions state that Gary’s average should equal 1605, so this provides a useful check that the extraction and matching process is working correctly.
round(mean(gary_ratings, na.rm = TRUE))## [1] 1605After confirming that the calculation works for the first player, I’ll apply the same process to every player in the tournament. For each player, the code extracts the opponent numbers, matches those opponents to their pre-tournament ratings, and calculates the rounded average.
average_opponent_rating <- sapply(
player_rows,
function(row) {
opponents <- extract_opponents(row)
opponent_ratings <- players$pre_rating[
match(opponents, players$player_number)
]
round(mean(opponent_ratings, na.rm = TRUE))
}
)
head(average_opponent_rating)## 1 | GARY HUA |6.0 |W 39|W 21|W 18|W 14|W 7|D 12|D 4|
## 1605
## 2 | DAKSHESH DARURI |6.0 |W 63|W 58|L 4|W 17|W 16|W 20|W 7|
## 1469
## 3 | ADITYA BAJAJ |6.0 |L 8|W 61|W 25|W 21|W 11|W 13|W 12|
## 1564
## 4 | PATRICK H SCHILLING |5.5 |W 23|D 28|W 2|W 26|D 5|W 19|D 1|
## 1574
## 5 | HANSHI ZUO |5.5 |W 45|W 37|D 12|D 13|D 4|W 14|W 17|
## 1501
## 6 | HANSEN SONG |5.0 |W 34|D 29|L 11|W 35|D 10|W 27|W 21|
## 1519sapply() repeats the same process for every player and produces one average opponent rating for every player.
I’ll combine the required variables into the final dataset. The project requires Player Name, State, Total Points, Pre-Rating, and Average Pre Chess Rating of Opponents.
final_chess_data <- data.frame(
Player_Name = player_name,
State = player_state,
Total_Points = total_points,
Pre_Rating = pre_rating,
Average_Opponent_Pre_Rating = average_opponent_rating
)
head(final_chess_data)## Player_Name
## 1 | GARY HUA |6.0 |W 39|W 21|W 18|W 14|W 7|D 12|D 4| GARY HUA
## 2 | DAKSHESH DARURI |6.0 |W 63|W 58|L 4|W 17|W 16|W 20|W 7| DAKSHESH DARURI
## 3 | ADITYA BAJAJ |6.0 |L 8|W 61|W 25|W 21|W 11|W 13|W 12| ADITYA BAJAJ
## 4 | PATRICK H SCHILLING |5.5 |W 23|D 28|W 2|W 26|D 5|W 19|D 1| PATRICK H SCHILLING
## 5 | HANSHI ZUO |5.5 |W 45|W 37|D 12|D 13|D 4|W 14|W 17| HANSHI ZUO
## 6 | HANSEN SONG |5.0 |W 34|D 29|L 11|W 35|D 10|W 27|W 21| HANSEN SONG
## State
## 1 | GARY HUA |6.0 |W 39|W 21|W 18|W 14|W 7|D 12|D 4| ON
## 2 | DAKSHESH DARURI |6.0 |W 63|W 58|L 4|W 17|W 16|W 20|W 7| MI
## 3 | ADITYA BAJAJ |6.0 |L 8|W 61|W 25|W 21|W 11|W 13|W 12| MI
## 4 | PATRICK H SCHILLING |5.5 |W 23|D 28|W 2|W 26|D 5|W 19|D 1| MI
## 5 | HANSHI ZUO |5.5 |W 45|W 37|D 12|D 13|D 4|W 14|W 17| MI
## 6 | HANSEN SONG |5.0 |W 34|D 29|L 11|W 35|D 10|W 27|W 21| OH
## Total_Points
## 1 | GARY HUA |6.0 |W 39|W 21|W 18|W 14|W 7|D 12|D 4| 6.0
## 2 | DAKSHESH DARURI |6.0 |W 63|W 58|L 4|W 17|W 16|W 20|W 7| 6.0
## 3 | ADITYA BAJAJ |6.0 |L 8|W 61|W 25|W 21|W 11|W 13|W 12| 6.0
## 4 | PATRICK H SCHILLING |5.5 |W 23|D 28|W 2|W 26|D 5|W 19|D 1| 5.5
## 5 | HANSHI ZUO |5.5 |W 45|W 37|D 12|D 13|D 4|W 14|W 17| 5.5
## 6 | HANSEN SONG |5.0 |W 34|D 29|L 11|W 35|D 10|W 27|W 21| 5.0
## Pre_Rating
## 1 | GARY HUA |6.0 |W 39|W 21|W 18|W 14|W 7|D 12|D 4| 1794
## 2 | DAKSHESH DARURI |6.0 |W 63|W 58|L 4|W 17|W 16|W 20|W 7| 1553
## 3 | ADITYA BAJAJ |6.0 |L 8|W 61|W 25|W 21|W 11|W 13|W 12| 1384
## 4 | PATRICK H SCHILLING |5.5 |W 23|D 28|W 2|W 26|D 5|W 19|D 1| 1716
## 5 | HANSHI ZUO |5.5 |W 45|W 37|D 12|D 13|D 4|W 14|W 17| 1655
## 6 | HANSEN SONG |5.0 |W 34|D 29|L 11|W 35|D 10|W 27|W 21| 1686
## Average_Opponent_Pre_Rating
## 1 | GARY HUA |6.0 |W 39|W 21|W 18|W 14|W 7|D 12|D 4| 1605
## 2 | DAKSHESH DARURI |6.0 |W 63|W 58|L 4|W 17|W 16|W 20|W 7| 1469
## 3 | ADITYA BAJAJ |6.0 |L 8|W 61|W 25|W 21|W 11|W 13|W 12| 1564
## 4 | PATRICK H SCHILLING |5.5 |W 23|D 28|W 2|W 26|D 5|W 19|D 1| 1574
## 5 | HANSHI ZUO |5.5 |W 45|W 37|D 12|D 13|D 4|W 14|W 17| 1501
## 6 | HANSEN SONG |5.0 |W 34|D 29|L 11|W 35|D 10|W 27|W 21| 1519Before exporting the data, I’ll perform basic checks on the final dataset to confirm its dimensions, review the values, and identify any missing observations.
dim(final_chess_data)## [1] 64 5summary(final_chess_data)## Player_Name State Total_Points Pre_Rating
## Length :64 Length :64 Min. :1.000 Min. : 377
## N.unique :64 N.unique : 3 1st Qu.:2.500 1st Qu.:1227
## N.blank : 0 N.blank : 0 Median :3.500 Median :1407
## Min.nchar: 6 Min.nchar: 2 Mean :3.438 Mean :1378
## Max.nchar:26 Max.nchar: 2 3rd Qu.:4.000 3rd Qu.:1583
## Max. :6.000 Max. :1794
## Average_Opponent_Pre_Rating
## Min. :1107
## 1st Qu.:1310
## Median :1382
## Mean :1379
## 3rd Qu.:1481
## Max. :1605sum(is.na(final_chess_data))## [1] 0The final requirement is to generate a CSV file containing the cleaned player information. I export the completed data frame without R row numbers so the resulting file contains only the required variables. Then I verified that the CSV file was successfully successfully created and then read it back into R to confirm that the exported data can be accessed correctly.
write.csv(
final_chess_data,
"chess_players_final.csv",
row.names = FALSE
)file.exists("chess_players_final.csv")## [1] TRUEcsv_check <- read.csv("chess_players_final.csv")
head(csv_check)## Player_Name State Total_Points Pre_Rating Average_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 1519write.csv(
final_chess_data,
"chess_players_final.csv",
row.names = FALSE
)