# Load packages
pacman::p_load(tidyverse, rio, here)Chess Tournament
Introduction
In this project, I’ve been given a text file with chess tournament results where the information has some structure. My job is to create an R Markdown file that generates a .csv file that could be imported into a SQL database with the following information for all of the players:
- Player Name
- Player State
- Total Number of Points
- Player Pre-Rating
- Average Pre Chess Rating of Opponents
The Average Pre Chess Rating of Opponents was calculated by using the pre-tournament opponents’ ratings of 1436, 1563, 1600, 1610, 1649, 1663, 1716, and dividing by the total number of games played.
Loading Packages
Import Data
# URL
url <- "https://raw.githubusercontent.com/Dave-Melchor/Data-607-Data-Acquisition-and-Management/main/data/raw/tournamentinfo.txt"
# Use URL to bring in .txt data
raw_data <- readLines(url)Warning in readLines(url): incomplete final line found on
'https://raw.githubusercontent.com/Dave-Melchor/Data-607-Data-Acquisition-and-Management/main/data/raw/tournamentinfo.txt'# Inspect the data
head(raw_data, 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] "-----------------------------------------------------------------------------------------" Looking at the data, there are a lot of lines and space that doesn’t include the information that we are trying to get. Each player data is found in two lines. For example, player 1’s data is on line 5 and line 6 and player 2’s data in found on line 8 and line 9 so we have to trim all that extra space.
Data Cleaning
# Generate row indices for Line 1 (Names, Points, Opponents)
line1_indices <- seq(5, length(raw_data), by = 3)
# Generate row indices for Line 2 (State, Ratings)
line2_indices <- seq(6, length(raw_data), by = 3)
# Extract those subset vectors
player_lines1 <- raw_data[line1_indices]
player_lines2 <- raw_data[line2_indices]
# Inspect the first 2 elements of each vector
head(player_lines1, 2)[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|"head(player_lines2, 2)[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 |"Now we have two vectors where index 1 in both vectors corresponds to player 1 and index 2 in both vectors corresponds to player 2.
Extracting Player Names
# Split each line by the vertical pipe
split_line1 <- str_split(player_lines1, "\\|")
# Extract the 2nd piece (Name) from every split line, and trim extra white space
player_names <- str_trim(sapply(split_line1, `[`, 2))
# Inspect the first 5 names
head(player_names, 5)[1] "GARY HUA" "DAKSHESH DARURI" "ADITYA BAJAJ"
[4] "PATRICK H SCHILLING" "HANSHI ZUO" Extracting Total Points
# Extract the 3rd piece (Total Points) and trim the extra space
total_points <- str_trim(sapply(split_line1, `[`, 3))
# Convert it to numeric format so R can do math on it later
total_points <- as.numeric(total_points)
# Preview the first 5
head(total_points, 5)[1] 6.0 6.0 6.0 5.5 5.5Extracting State
# Split the second line of data by vertical pipes
split_line2 <- str_split(player_lines2, "\\|")
# Extract the 1st piece (State) and trim the extra space
state <- str_trim(sapply(split_line2, `[`, 1))
# Preview the fist 5
head(state, 5)[1] "ON" "MI" "MI" "MI" "MI"Extracting Pre-Ratings
# Extract the 2nd piece (Raw Rating) and trim the extra space
raw_rating <- str_trim(sapply(split_line2, `[`, 2))
# Extract the digits following R:
pre_rating <- str_extract(raw_rating, "R:\\s*\\d+")
# Keep only the numbers and convert to numeric
pre_rating <- as.numeric(str_extract(pre_rating, "\\d+"))
# Preview
head(pre_rating, 5)[1] 1794 1553 1384 1716 1655Extracting Opponent’s ID
# Extract columns 4 through 10 for all players
rounds <- sapply(split_line1, function(x) x[4:10])
# Extract just the oponent's numbers
opponent_id <- apply(rounds, 2, function(x) str_extract(x, "\\d+"))
# Check Gary's opponents
opponent_id[, 1][1] "39" "21" "18" "14" "7" "12" "4" Calculate Average Opponent Pre-Rating
# Calculate the average opponent pre-rating for each player
avg_opp_rating <- sapply(1:ncol(opponent_id), function(i) {
# Get the opponent IDs for player i
opps <- as.numeric(opponent_id[, i])
# Look up their pre-ratings and take the mean (ignoring NAs)
round(mean(pre_rating[opps], na.rm = TRUE))
})
# Preview the average opponent ratings for the first 5 players
head(avg_opp_rating, 5)[1] 1605 1469 1564 1574 1501Combining the Clean Variable Into a Data Frame
# Combine all extracted variables into a single data frame
chess_tournament <- tibble(
Player_Name = player_names,
State = state,
Total_Points = total_points,
Pre_Rating = pre_rating,
Avg_Opp_Rating = avg_opp_rating
)
# View the final dataset
head(chess_tournament, 10)# A tibble: 10 × 5
Player_Name State Total_Points Pre_Rating Avg_Opp_Rating
<chr> <chr> <dbl> <dbl> <dbl>
1 GARY HUA ON 6 1794 1605
2 DAKSHESH DARURI MI 6 1553 1469
3 ADITYA BAJAJ MI 6 1384 1564
4 PATRICK H SCHILLING MI 5.5 1716 1574
5 HANSHI ZUO MI 5.5 1655 1501
6 HANSEN SONG OH 5 1686 1519
7 GARY DEE SWATHELL MI 5 1649 1372
8 EZEKIEL HOUGHTON MI 5 1641 1468
9 STEFANO LEE ON 5 1411 1523
10 ANVIT RAO MI 5 1365 1554Export
export(chess_tournament, here("data", "processed", "chess_tournament_summary.csv"))