This project is about transforming a semi-structured chess tournament text file into a clean and organized dataset that can be analyzed and exported as a CSV file. The tournament file contains information about each player, including their name, state, total points earned, pre-tournament rating, and the opponents they faced throughout the tournament.The goal of the project is to extract the required information for each player and calculate the average pre-tournament rating of all opponents they played against. Since the opponent ratings are not directly provided with each game result, additional processing is required to identify each opponent and retrieve their corresponding pre-tournament rating from the tournament data.Although the source file follows a consistent format, the data is spread across multiple lines and contains game results embedded within text fields. Therefore, much of the work in this project involves parsing, cleaning, and restructuring the data before performing the final calculations.
My approach for this project is to treat the tournament data similarly to a relational database. First, I will create a player table containing one record per player, including their player number, name, state, total points, pre-tournament rating, and opponent numbers for each round.Then I would generated a game table by iterating through the round information for each player and extracting valid opponent numbers. This produced a table containing the player number, round number, and opponent number for every recorded game. Rounds containing no number or played game value were excluded because they do not represent games against actual opponents. Once the game table is created, opponent numbers were matched back to the player table to retrieve the opponents’ pre-tournament ratings. These ratings is the averaged for each player to produce the required average opponent rating value.
The first step from my approach was to create an empty dataframe that would store the information extracted from the tournament file. The dataframe was designed to hold one record per player, including the player number, player name, state, total points earned, pre-tournament rating, and the opponent number for each of the seven rounds. This structure serves as the foundation for the remainder of the analysis.
players_df <- data.frame(
player_num = integer(),
player_name = character(),
state = character(),
points = numeric(),
pre_rating = integer(),
r1 = integer(),
r2 = integer(),
r3 = integer(),
r4 = integer(),
r5 = integer(),
r6 = integer(),
r7 = integer(),
stringsAsFactors = FALSE
)
players_df## [1] player_num player_name state points pre_rating r1
## [7] r2 r3 r4 r5 r6 r7
## <0 rows> (or 0-length row.names)The tournament results were provided in a text file rather than a traditional tabular format. To begin the parsing process, the file was read into R as a collection of text lines. Regular expressions were then used to identify the starting line for each player’s record, which allowed the data to be processed systematically.
lines<- read_lines("tournamentinfo.txt")
length(lines)## [1] 196head(lines,10)## [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] "-----------------------------------------------------------------------------------------"player_lines <- grep("^\\s*[0-9]+\\s*\\|", lines)
head(player_lines)## [1] 5 8 11 14 17 20Before processing the entire file, the parsing logic was tested using the first player record. This validation step ensured that player information, state, pre-tournament rating, total points, and opponent numbers could be extracted correctly. Once the logic was verified, it was incorporated into a loop to process all players automatically.
"testsection - not code, nothing is this section is being build"
lines<- read_lines("tournamentinfo.txt")
length(lines)
head(lines,10)
player_lines <- grep("^\\s*[0-9]+\\s*\\|", lines)
head(player_lines)
lines[player_lines[1]]
lines[player_lines[1]+1]
line1 <- lines[player_lines[1]]
parts1 <-trimws(strsplit(line1,"\\|")[[1]])
parts1
player_lines
player_num <- as.integer(parts1[1])
player_name <- parts1[2]
points <- as.numeric(parts1[3])
player_num
player_name
points
line2 <- lines[player_lines[1]+1]
line2
parts2 <- trimws(strsplit(line2,"\\|")[[1]])
parts2
state <- parts2[1]
state
pre_rating <-as.integer(sub(".*R:\\s*([0-9]+).*","\\1",parts2[2]))
pre_rating
Extract_opponent <- function(x) {
num <- gsub("[^0-9]", "", x)
if (num == "") {
return(NA)
}
as.integer(num)
}
Extract_opponent(parts1[4])
Extract_opponent(parts1[9])
r1 <-Extract_opponent(parts1[4])
r2 <-Extract_opponent(parts1[5])
r3 <-Extract_opponent(parts1[6])
r4 <-Extract_opponent(parts1[7])
r5 <-Extract_opponent(parts1[8])
r6 <-Extract_opponent(parts1[9])
r7 <-Extract_opponent(parts1[10])
new_player <- data.frame(
player_num = player_num,
player_name = player_name,
state = state,
points = points,
pre_rating = pre_rating,
r1 = r1,
r2 = r2,
r3 = r3,
r4 = r4,
r5 = r5,
r6 = r6,
r7 = r7
)
new_player
players_df <- rbind(players_df, new_player)
head(players_df)After validating the extraction logic, a loop was used to process every player in the tournament file. Each player’s information was extracted from the corresponding two-line record and added to the player table. During this step, opponent numbers from each round were also captured and stored for later analysis.
Extract_opponent <- function(x) {
num <- gsub("[^0-9]", "", x)
if (num == "") {
return(NA)
}
as.integer(num)
}
for(i in player_lines){
# First line
line1 <- lines[i]
parts1 <- trimws(strsplit(line1, "\\|")[[1]])
# Second line
line2 <- lines[i + 1]
parts2 <- trimws(strsplit(line2, "\\|")[[1]])
# Basic fields
player_num <- as.integer(parts1[1])
player_name <- parts1[2]
points <- as.numeric(parts1[3])
state <- parts2[1]
pre_rating <- as.integer(
sub(".*R:\\s*([0-9]+).*", "\\1", parts2[2])
)
# Opponents
r1 <- Extract_opponent(parts1[4])
r2 <- Extract_opponent(parts1[5])
r3 <- Extract_opponent(parts1[6])
r4 <- Extract_opponent(parts1[7])
r5 <- Extract_opponent(parts1[8])
r6 <- Extract_opponent(parts1[9])
r7 <- Extract_opponent(parts1[10])
# Create row
new_player <- data.frame(
player_num,
player_name,
state,
points,
pre_rating,
r1,r2,r3,r4,r5,r6,r7
)
# Add to dataframe
players_df <- rbind(players_df, new_player)
}
write.csv (players_df,"players_table.csv", row.names=FALSE)To simplify the calculation of opponent statistics, a separate game table was created. This table stores one record for each player-opponent matchup and contains the player number, round number, and opponent number. Converting the round information into this format makes it easier to perform relational-style joins and calculations.
games_df <- data.frame(
player_num = integer(),
round_num = integer(),
opponent_num = integer(),
stringsAsFactors = FALSE
)The game table was populated by iterating through each player’s round information. For every valid opponent number found, a new game record was created containing the player number, round number, and opponent number. Rounds without a valid opponent were excluded because they do not contribute to the average opponent rating calculation.
for(i in 1:nrow(players_df)) {
player_id <- players_df$player_num[i]
for(round in 1:7) {
opponent <- players_df[i, paste0("r", round)]
if(!is.na(opponent)) {
games_df <- rbind(
games_df,
data.frame(
player_num = player_id,
round_num = round,
opponent_num = opponent
)
)
}
}
}
write.csv (games_df,"gameplayed_table.csv", row.names=FALSE)The assignment requires calculating the average pre-tournament rating of each player’s opponents. To accomplish this, the game table was joined back to the player table using the opponent number as a lookup key. This allowed each opponent’s pre-tournament rating to be retrieved. The ratings were then grouped by player and averaged to produce the required statistic.
ratings_lookup <- players_df %>%
select(player_num, pre_rating)
head(ratings_lookup,10)## player_num pre_rating
## 1 1 1794
## 2 2 1553
## 3 3 1384
## 4 4 1716
## 5 5 1655
## 6 6 1686
## 7 7 1649
## 8 8 1641
## 9 9 1411
## 10 10 1365games_ratings_df <- games_df %>%
left_join(
ratings_lookup,
by = c("opponent_num" = "player_num")
)
head(games_ratings_df, 5)## player_num round_num opponent_num pre_rating
## 1 1 1 39 1436
## 2 1 2 21 1563
## 3 1 3 18 1600
## 4 1 4 14 1610
## 5 1 5 7 1649avg_ratings_df <- games_ratings_df %>%
group_by(player_num) %>%
summarise(
avg_opp_rating = round(mean(pre_rating))
)
avg_ratings_df %>%
filter(player_num == 1)## # A tibble: 1 × 2
## player_num avg_opp_rating
## <int> <dbl>
## 1 1 1605avg_ratings_df## # A tibble: 64 × 2
## player_num avg_opp_rating
## <int> <dbl>
## 1 1 1605
## 2 2 1469
## 3 3 1564
## 4 4 1574
## 5 5 1501
## 6 6 1519
## 7 7 1372
## 8 8 1468
## 9 9 1523
## 10 10 1554
## # ℹ 54 more rowsThe final step was to combine the calculated average opponent ratings with the player information. The resulting dataset contains the player’s name, state, total points, pre-tournament rating, and average opponent pre-tournament rating. This dataset was then exported as a CSV file to satisfy the project requirements.
final_df <- players_df %>%
select(
player_num,
player_name,
state,
points,
pre_rating
) %>%
left_join(
avg_ratings_df,
by = "player_num"
)
final_output <- final_df %>%
select(
player_name,
state,
points,
pre_rating,
avg_opp_rating
)
final_output## player_name state points pre_rating avg_opp_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
## 7 GARY DEE SWATHELL MI 5.0 1649 1372
## 8 EZEKIEL HOUGHTON MI 5.0 1641 1468
## 9 STEFANO LEE ON 5.0 1411 1523
## 10 ANVIT RAO MI 5.0 1365 1554
## 11 CAMERON WILLIAM MC LEMAN MI 4.5 1712 1468
## 12 KENNETH J TACK MI 4.5 1663 1506
## 13 TORRANCE HENRY JR MI 4.5 1666 1498
## 14 BRADLEY SHAW MI 4.5 1610 1515
## 15 ZACHARY JAMES HOUGHTON MI 4.5 1220 1484
## 16 MIKE NIKITIN MI 4.0 1604 1386
## 17 RONALD GRZEGORCZYK MI 4.0 1629 1499
## 18 DAVID SUNDEEN MI 4.0 1600 1480
## 19 DIPANKAR ROY MI 4.0 1564 1426
## 20 JASON ZHENG MI 4.0 1595 1411
## 21 DINH DANG BUI ON 4.0 1563 1470
## 22 EUGENE L MCCLURE MI 4.0 1555 1300
## 23 ALAN BUI ON 4.0 1363 1214
## 24 MICHAEL R ALDRICH MI 4.0 1229 1357
## 25 LOREN SCHWIEBERT MI 3.5 1745 1363
## 26 MAX ZHU ON 3.5 1579 1507
## 27 GAURAV GIDWANI MI 3.5 1552 1222
## 28 SOFIA ADINA STANESCU-BELLU MI 3.5 1507 1522
## 29 CHIEDOZIE OKORIE MI 3.5 1602 1314
## 30 GEORGE AVERY JONES ON 3.5 1522 1144
## 31 RISHI SHETTY MI 3.5 1494 1260
## 32 JOSHUA PHILIP MATHEWS ON 3.5 1441 1379
## 33 JADE GE MI 3.5 1449 1277
## 34 MICHAEL JEFFERY THOMAS MI 3.5 1399 1375
## 35 JOSHUA DAVID LEE MI 3.5 1438 1150
## 36 SIDDHARTH JHA MI 3.5 1355 1388
## 37 AMIYATOSH PWNANANDAM MI 3.5 980 1385
## 38 BRIAN LIU MI 3.0 1423 1539
## 39 JOEL R HENDON MI 3.0 1436 1430
## 40 FOREST ZHANG MI 3.0 1348 1391
## 41 KYLE WILLIAM MURPHY MI 3.0 1403 1248
## 42 JARED GE MI 3.0 1332 1150
## 43 ROBERT GLEN VASEY MI 3.0 1283 1107
## 44 JUSTIN D SCHILLING MI 3.0 1199 1327
## 45 DEREK YAN MI 3.0 1242 1152
## 46 JACOB ALEXANDER LAVALLEY MI 3.0 377 1358
## 47 ERIC WRIGHT MI 2.5 1362 1392
## 48 DANIEL KHAIN MI 2.5 1382 1356
## 49 MICHAEL J MARTIN MI 2.5 1291 1286
## 50 SHIVAM JHA MI 2.5 1056 1296
## 51 TEJAS AYYAGARI MI 2.5 1011 1356
## 52 ETHAN GUO MI 2.5 935 1495
## 53 JOSE C YBARRA MI 2.0 1393 1345
## 54 LARRY HODGE MI 2.0 1270 1206
## 55 ALEX KONG MI 2.0 1186 1406
## 56 MARISA RICCI MI 2.0 1153 1414
## 57 MICHAEL LU MI 2.0 1092 1363
## 58 VIRAJ MOHILE MI 2.0 917 1391
## 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 1263write.csv (final_output,"final_chess_result.csv", row.names=FALSE)The final dataset contains each player’s name, state, total points, pre-tournament rating, and average opponent pre-tournament rating. The tournament data was successfully parsed, transformed into structured tables, and exported as a CSV file. The calculated results matched the example provided in the assignment, including an average opponent rating of 1605 for Gary Hua.
This project demonstrated how semi-structured text data can be transformed into a structured dataset using parsing, data cleaning, and relational-style joins. By creating player and game tables, it was possible to calculate the average pre-tournament rating of each player’s opponents and generate the required CSV output. The project highlights the importance of data wrangling techniques when working with non-tabular source data and shows how relational concepts can be applied to solve real-world data processing problems.
I prompted copilot to write an paragraphs of a summary of all the questions I ask to complete this assignment. and this was the output.
Microsoft Copilot was used as a learning and coding assistant during this project. It was used to help explain concepts, review code logic, and discuss approaches for solving the assignment. All code was reviewed, tested, and modified by the author, who remains responsible for the final implementation and analysis.
Microsoft. (2026). Microsoft Copilot (AI-powered assistant based on GPT technology). Retrieved September 26, 2026, from Microsoft Copilot