Project 1

Approach

For this project, I will work with the chess tournament text file and turn it into a clean dataset. The main challenge will be working with a text file where the information is spread across multiple lines. There are a few patterns in the file. For example, each player’s information is spread across two lines followed by a separator, and the round results contain information about the player’s opponents. I will use these patterns to extract the required information, create a dataframe, and export the final results as a CSV file.

flowchart LR
    A(Read TXT file) --> B(Clean and structure data)
    B --> C(Extract player and opponent information)
    C --> D(Calculate average opponent rating)
    D --> E(Create dataframe and export CSV)

Read txt file

Since each player’s information is spread across multiple lines, I used readLines() to read file line by line.

library(tidyverse)
chess <- readLines("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] "-----------------------------------------------------------------------------------------" 

I removed the first four lines since it’s just the headers, and checked the first 10 lines again to make sure the dataset now starts with the first player record.

chess_data <- chess[-(1:4)]
head(chess_data, 10)

 [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] "-----------------------------------------------------------------------------------------"
 [4] "    2 | DAKSHESH DARURI                 |6.0  |W  63|W  58|L   4|W  17|W  16|W  20|W   7|"
 [5] "   MI | 14598900 / R: 1553   ->1663     |N:2  |B    |W    |B    |W    |B    |W    |B    |"
 [6] "-----------------------------------------------------------------------------------------"
 [7] "    3 | ADITYA BAJAJ                    |6.0  |L   8|W  61|W  25|W  21|W  11|W  13|W  12|"
 [8] "   MI | 14959604 / R: 1384   ->1640     |N:2  |W    |B    |W    |B    |W    |B    |W    |"
 [9] "-----------------------------------------------------------------------------------------"
[10] "    4 | PATRICK H SCHILLING             |5.5  |W  23|D  28|W   2|W  26|D   5|W  19|D   1|"

Clean and structure data

Since readLines() stores each line of the text file as an element in a vector, we can use vector indexing to select specific lines by their position. We found a repeating pattern where the player information is on lines 1, 4, 7, etc., and the additional details are on lines 2, 5, 8, etc. We can use seq() to select these lines and store them in two separate vectors.

player_info <- chess_data[seq(1, length(chess_data), by = 3)]
head(player_info,3)

[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|"

details_info <- chess_data[seq(2, length(chess_data), by = 3)]
head(details_info,3)

[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    |"

Since player_info is currently a vector, I converted it into a tibble so I can use separate_wider_delim() to split the player information into separate columns.

players <- tibble(player_info = player_info)
players <- players %>%
  separate_wider_delim(player_info,delim = "|", names = c("player_num", "player_name", "points","round1", "round2", "round3", "round4", "round5", "round6", "round7"),too_many = "drop")
head(players)

details <- tibble(details_info = details_info)
details <- details %>%
  separate_wider_delim(details_info, delim = "|", names = c("state", "rating_info"),too_many = "drop")
head(details)

The rating_info column contains the player ID, pre-rating, and post-rating in one string. I will use str_extract() to get the pre-rating, convert it to numeric and keep only two columns I need.

details <- details %>% mutate(pre_rating = as.numeric(str_extract(rating_info, "(?<=R: )\\d+"))) %>% select(state, pre_rating)

head(details)

Extract player and opponent info

The round columns contain the game result and the opponent’s player number. I used pivot_longer() to combine the seven round columns into rows, so each round can be analyzed separately.

opponents <- players %>%
  pivot_longer(
    cols = round1:round7,
    names_to = "round",
    values_to = "result")

opponents <- opponents %>%
  mutate(opponent = str_extract(result, "\\d+"))
head(opponents, 10)

Calculate average opponent rating

Since the rows in details are in the same order as the player numbers, I used row_number() to add the corresponding player number to each row. This allows each player’s pre-rating to be matched with their player number.

details <- details %>% mutate(player_num = row_number())
head(details,2)

Now opponents has the opponent number, and details has the player number and pre-rating. I need to match them.

opponents <- opponents %>%
  mutate(opponent = as.numeric(opponent)) %>%
  left_join(details, by = c("opponent" = "player_num"))
head(opponents,3)

Now we can calculate the average opponents rating for each of the players

avg_ratings <- opponents %>%
  group_by(player_num) %>%
  summarise(avg_opponent_rating = round(mean(pre_rating, na.rm = TRUE)))
head(avg_ratings,3)

Create a dataframe and export to CSV

I combined the player information with the average opponent ratings and player details using LEFT_JOIN

players <- players %>%
  select(player_num, player_name, points) %>%
  left_join(avg_ratings, by = "player_num") %>%
  mutate(player_num = as.numeric(player_num)) %>%
  left_join(details, by = "player_num")

head(players, 3)

Finally, I selected and organized the columns to match the assignment requirements and exported it as csv file.

players <- players %>%
  select(player_name, state, points, pre_rating, avg_opponent_rating)

head(players, 3)

write_csv(players, "chess_players.csv")