In this assignment, I will use a text file containing the results of a chess tournament with 64 players and seven rounds. The dataset includes each player’s name, state, total points, pre-tournament rating, and results from each round. My plan is to load the text file into R and clean the data so that the information for each player can be organized into a structured dataset.
One challenge I anticipate is that the original data is stored in a text format rather than a traditional table, with each player’s information spread across multiple lines. I will need to separate the player information and extract the opponent numbers from the round results. I will then use the opponent numbers to match each opponent with their pre-tournament rating and calculate the average opponent pre-rating for each player.
The final dataset will contain the player’s name, state, total points, pre-tournament rating, and average pre-tournament rating of their opponents. I will export the cleaned dataset as a CSV file that can be imported into a SQL database.
Data source: tournamentinfo.txt
library(tidyverse)## Warning: package 'ggplot2' was built under R version 4.4.3## Warning: package 'dplyr' was built under R version 4.4.3## ── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
## ✔ dplyr 1.2.0 ✔ readr 2.1.5
## ✔ forcats 1.0.1 ✔ stringr 1.5.2
## ✔ ggplot2 4.0.2 ✔ tibble 3.3.0
## ✔ lubridate 1.9.4 ✔ tidyr 1.3.1
## ✔ purrr 1.1.0
## ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
## ✖ dplyr::filter() masks stats::filter()
## ✖ dplyr::lag() masks stats::lag()
## ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errorschess <- readLines("tournamentinfo.txt")## Warning in readLines("tournamentinfo.txt"): incomplete final line found on
## 'tournamentinfo.txt'head(chess, 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] "-----------------------------------------------------------------------------------------"chess_data <- chess[-c(1:4)]
chess_data <- chess_data[!grepl("^---", chess_data)]
head(chess_data)## [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 |"
## [3] " 2 | DAKSHESH DARURI |6.0 |W 63|W 58|L 4|W 17|W 16|W 20|W 7|"
## [4] " MI | 14598900 / R: 1553 ->1663 |N:2 |B |W |B |W |B |W |B |"
## [5] " 3 | ADITYA BAJAJ |6.0 |L 8|W 61|W 25|W 21|W 11|W 13|W 12|"
## [6] " MI | 14959604 / R: 1384 ->1640 |N:2 |W |B |W |B |W |B |W |"length(chess_data)## [1] 128player_lines <- chess_data[seq(1, length(chess_data), by = 2)]
rating_lines <- chess_data[seq(2, length(chess_data), by = 2)]
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|"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 |"player_split <- strsplit(player_lines, "\\|")
player_split[[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"player_number <- as.numeric(trimws(sapply(player_split, '[', 1)))
player_name <- trimws(sapply(player_split, '[', 2))
total_points <- as.numeric(trimws(sapply(player_split, '[', 3)))
head(player_number)## [1] 1 2 3 4 5 6head(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.0rating_split <- strsplit(rating_lines, "\\|")
state <- trimws(sapply(rating_split, '[', 1))
head(state)## [1] "ON" "MI" "MI" "MI" "MI" "OH"rating_info <- trimws(sapply(rating_split, '[', 2))
pre_rating <- as.numeric(
sub(".*R:\\s*([0-9]+).*", "\\1", rating_info)
)
head(pre_rating)## [1] 1794 1553 1384 1716 1655 1686players <- data.frame(
Player_Number = player_number,
Player_Name = player_name,
State = 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 1686rounds <- sapply(player_split, function(x) x[4:10])
rounds <- t(rounds)
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"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 21avg_opponent_rating <- apply(opponents, 1, function(x) {
x <- x[!is.na(x)]
opponent_ratings <- players$Pre_Rating[
match(x, players$Player_Number)
]
mean(opponent_ratings)
}
)
head(avg_opponent_rating)## [1] 1605.286 1469.286 1563.571 1573.571 1500.857 1518.714players$Avg_Opponent_Pre_Rating <- round(avg_opponent_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 1686
## Avg_Opponent_Pre_Rating
## 1 1605
## 2 1469
## 3 1564
## 4 1574
## 5 1501
## 6 1519final_data <- players %>%
select(
Player_Name,
State,
Total_Points,
Pre_Rating,
Avg_Opponent_Pre_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 1519nrow(final_data)## [1] 64write.csv(
final_data,
"chess_tournament_results.csv",
row.names = FALSE
)
file.exists("chess_tournament_results.csv")## [1] TRUEIn this project, I transformed the chess tournament text file into a structured dataset containing information for all 64 players. I extracted each player’s name, state, total points, and pre-tournament rating. I also used the results from each round to identify each player’s opponents and calculate the average pre-tournament rating of those opponents. The main challenge was working with the semi-structured text file because the player information was spread across multiple lines and the round results contained both letters and opponent numbers. After cleaning and organizing the data, I was able to create the five required variables and export the final dataset as a CSV file that can be imported into a SQL database.