I used the same chess text data file suggested in the project 1 page on blackboard (so that the data is available to whoever going to run the code again).

I’m first going to try to clean the data and turn into into a structured form.

data structuring

# structuring the data

library(stringr)


# indicating the separator lines
separator_indices <- grep("^-+$", lines)

player_data_indices <- c()
for (i in seq_along(separator_indices)[-length(separator_indices)]) {
  player_data_indices <- c(player_data_indices, (separator_indices[i] + 1), (separator_indices[i] + 2))
}

# extracting data lines
player_data_lines <- lines[player_data_indices]

# separating the lines into two columns
player_data <- data.frame(
  `Pair | Player Name |Total|Round|Round|Round|Round|Round|Round|Round|` = player_data_lines[c(TRUE, FALSE)],
  `Num | USCF ID / Rtg (Pre->Post) | Pts | 1 | 2 | 3 | 4 | 5 | 6 | 7 |` = player_data_lines[c(FALSE, TRUE)],
  stringsAsFactors = FALSE
)
# splitting a column into multiple columns
split_into_columns <- function(column, n) {
  do.call(rbind, strsplit(trimws(column), "\\s*\\|\\s*", perl = TRUE))
}

player_data_first_half <- split_into_columns(player_data$`Pair...Player.Name..Total.Round.Round.Round.Round.Round.Round.Round.`, 10)

player_data_second_half <- split_into_columns(player_data$`Num...USCF.ID...Rtg..Pre..Post....Pts...1...2...3...4...5...6...7..`, 10)

# combining into a single df
combined_player_data <- cbind(
  as.data.frame(player_data_first_half, stringsAsFactors = FALSE),
  as.data.frame(player_data_second_half, stringsAsFactors = FALSE)
)

# adding column names
colnames(combined_player_data) <- c(
  "PairNum", "PlayerName", "TotalPoints", "Round1", "Round2", "Round3", "Round4", "Round5", "Round6", "Round7",
  "State", "USCF_ID, PreRating, PostRating", "Pts", "Game1", "Game2", "Game3", "Game4", "Game5", "Game6", "Game7"
)
# Removing the first row that has the labels already used
combined_player_data <- combined_player_data[-1, ]

# separating the column "USCF_ID_PreRating_PostRating" into the respective new columns:
library(tidyr)

combined_player_data <- separate(combined_player_data, 
                                 col = `USCF_ID, PreRating, PostRating`, 
                                 into = c("USCF_ID", "PreRating", "PostRating"), 
                                 sep = " / | ->",
                                 remove = TRUE,
                                 convert = TRUE)

# fixing some issues with the PreRating column
# removing the 'R: ' from the PreRating column
combined_player_data$PreRating <- gsub("R: ", "", combined_player_data$PreRating)
# extract the missing values into the PostRating column
combined_player_data$PostRating <- ifelse(is.na(combined_player_data$PostRating),
                                         as.character(sapply(combined_player_data$PreRating, function(x) {
                                           matches <- regmatches(x, regexec("->(\\d+P\\d+)", x))
                                           if (length(matches[[1]]) > 1) {
                                             return(matches[[1]][2])
                                           } else {
                                             return(NA)
                                           }
                                         })),
                                         combined_player_data$PostRating)
# removing '->' from PreRating column if it exists
combined_player_data$PreRating <- gsub("->.*", "", combined_player_data$PreRating)
# making sure the PreRating and PostRating columns are numeric
combined_player_data$PreRating <- as.numeric(combined_player_data$PreRating)
combined_player_data$PostRating <- as.numeric(gsub("P\\d+", "", combined_player_data$PostRating))

head(combined_player_data)
##   PairNum          PlayerName TotalPoints Round1 Round2 Round3 Round4 Round5
## 2       1            GARY HUA         6.0  W  39  W  21  W  18  W  14  W   7
## 3       2     DAKSHESH DARURI         6.0  W  63  W  58  L   4  W  17  W  16
## 4       3        ADITYA BAJAJ         6.0  L   8  W  61  W  25  W  21  W  11
## 5       4 PATRICK H SCHILLING         5.5  W  23  D  28  W   2  W  26  D   5
## 6       5          HANSHI ZUO         5.5  W  45  W  37  D  12  D  13  D   4
## 7       6         HANSEN SONG         5.0  W  34  D  29  L  11  W  35  D  10
##   Round6 Round7 State  USCF_ID PreRating PostRating Pts Game1 Game2 Game3 Game4
## 2  D  12  D   4    ON 15445895      1794       1817 N:2     W     B     W     B
## 3  W  20  W   7    MI 14598900      1553       1663 N:2     B     W     B     W
## 4  W  13  W  12    MI 14959604      1384       1640 N:2     W     B     W     B
## 5  W  19  D   1    MI 12616049      1716       1744 N:2     W     B     W     B
## 6  W  14  W  17    MI 14601533      1655       1690 N:2     B     W     B     W
## 7  W  27  W  21    OH 15055204      1686       1687 N:3     W     B     W     B
##   Game5 Game6 Game7
## 2     W     B     W
## 3     B     W     B
## 4     W     B     W
## 5     W     B     B
## 6     B     W     B
## 7     B     W     B

Now I can see that I have 64 players’ data, each player’s data is in a separate row.

Let’s explore the data a bit more, before thinking about further steps to clean it further (as it clearly still needs some cleaning, especially for the )

#  checking if there are any duplicates in the PlayerNames column
duplicates <- combined_player_data[duplicated(combined_player_data$PlayerName) | duplicated(combined_player_data$PlayerName, fromLast = TRUE), ]
print(paste("Number of duplicates in PlayerName:", nrow(duplicates)))
## [1] "Number of duplicates in PlayerName: 0"
# I found 0 duplicates

# summary statistics of the TotalPoints, PreRating, and PostRating variables
summary_statistics <- summary(combined_player_data[, c("TotalPoints", "PreRating", "PostRating")])
print(summary_statistics)
##  TotalPoints          PreRating      PostRating  
##  Length:64          Min.   : 853   Min.   : 878  
##  Class :character   1st Qu.:1249   1st Qu.:1200  
##  Mode  :character   Median :1417   Median :1421  
##                     Mean   :1403   Mean   :1402  
##                     3rd Qu.:1591   3rd Qu.:1600  
##                     Max.   :1794   Max.   :1817  
##                     NA's   :10     NA's   :3

Let’s see if there is a correlation between the PreRating and the PostRating scores for a player:

cor_test_result <- cor.test(combined_player_data$PreRating, combined_player_data$PostRating, 
                            method = "pearson", use = "complete.obs")
p_value <- cor_test_result$p.value
conf_int <- cor_test_result$conf.int
print(cor_test_result)
## 
##  Pearson's product-moment correlation
## 
## data:  combined_player_data$PreRating and combined_player_data$PostRating
## t = 25.412, df = 52, p-value < 2.2e-16
## alternative hypothesis: true correlation is not equal to 0
## 95 percent confidence interval:
##  0.9351389 0.9778841
## sample estimates:
##       cor 
## 0.9620169
# and a graph to visualize that

library(ggplot2)

plot <- ggplot(combined_player_data, aes(x = PreRating, y = PostRating)) +
  geom_point(alpha = 0.5) +
  geom_smooth(method = "lm", se = FALSE, color = "blue") +
  labs(title = "Correlation between PreRating and PostRating",
       x = "PreRating", y = "PostRating") +
  annotate("text", x = Inf, y = Inf, hjust = 1, vjust = 3,
           label = paste("Pearson r:", round(cor_test_result$estimate, 3), 
                         "\np-value:", signif(p_value, digits = 3),
                         "\n95% CI:", paste("(", round(conf_int[1], 3), ",", round(conf_int[2], 3), ")", sep = "")),
           size = 3.5) +
  theme_minimal()

print(plot)

Looks like there is a VERY STRONG correlation between the two scores, which makes sense.

Further data cleaning:

Splitting “Round” Columns into Result and Opponent Number

library(readxl)
library(dplyr)
library(stringr)

df <- read_excel("C:/Users/TMHFXA53/OneDrive - Houston Methodist/_CUNY SPS MSDS/607/Week 3/Week 3 - project 1/7645617output2.xlsx")

# splitting each "Round" column into two: "Round X result" and "Round X opponent"
for(i in 1:7) {
  round_col <- sprintf("Round%d", i)
  result_col <- sprintf("Round %d result", i)
  opponent_col <- sprintf("Round %d opponent", i)
  
  df <- df %>%
    mutate(!!result_col := ifelse(str_detect(get(round_col), "^[WLD]"), str_sub(get(round_col), 1, 1), NA_character_),
           !!opponent_col := as.numeric(str_extract(get(round_col), "\\d+")))
}
# checking
head(df)
## # A tibble: 6 × 36
##   PairNum PlayerName       TotalPoints Round1 Round2 Round3 Round4 Round5 Round6
##     <dbl> <chr>                  <dbl> <chr>  <chr>  <chr>  <chr>  <chr>  <chr> 
## 1       1 GARY HUA                 6   W  39  W  21  W  18  W  14  W   7  D  12 
## 2       2 DAKSHESH DARURI          6   W  63  W  58  L   4  W  17  W  16  W  20 
## 3       3 ADITYA BAJAJ             6   L   8  W  61  W  25  W  21  W  11  W  13 
## 4       4 PATRICK H SCHIL…         5.5 W  23  D  28  W   2  W  26  D   5  W  19 
## 5       5 HANSHI ZUO               5.5 W  45  W  37  D  12  D  13  D   4  W  14 
## 6       6 HANSEN SONG              5   W  34  D  29  L  11  W  35  D  10  W  27 
## # ℹ 27 more variables: Round7 <chr>, State <chr>, USCF_ID <dbl>,
## #   PreRating <chr>, PostRating <chr>, Pts <chr>, Game1 <chr>, Game2 <chr>,
## #   Game3 <chr>, Game4 <chr>, Game5 <chr>, Game6 <chr>, Game7 <chr>,
## #   `Round 1 result` <chr>, `Round 1 opponent` <dbl>, `Round 2 result` <chr>,
## #   `Round 2 opponent` <dbl>, `Round 3 result` <chr>, `Round 3 opponent` <dbl>,
## #   `Round 4 result` <chr>, `Round 4 opponent` <dbl>, `Round 5 result` <chr>,
## #   `Round 5 opponent` <dbl>, `Round 6 result` <chr>, …

Computing the Average Pre-Rating Score of the 7 Opponents

# mapping of PairNum to PreRating
pairnum_to_prerating <- with(df, setNames(PreRating, PairNum))

# fetching an opponent's pre-rating using opponent's PairNum
get_opponent_prerating <- function(opponent_pairnum, mapping) {
  if(!is.na(opponent_pairnum) && !is.null(mapping[as.character(opponent_pairnum)])) {
    return(mapping[as.character(opponent_pairnum)])
  } else {
    return(NA)
  }
}

# new variables for each of the 7 games
for(i in 1:7) {
  round_opponent_col <- paste("Round", i, "opponent", sep = " ")
  opponent_prerating_col <- paste("Opponent", i, "prerating", sep = " ")

    df[opponent_prerating_col] <- sapply(df[[round_opponent_col]], get_opponent_prerating, mapping = pairnum_to_prerating)
}

head(df[, c("PairNum", "PlayerName", paste("Opponent", 1:7, "prerating", sep = " "))])
## # A tibble: 6 × 9
##   PairNum PlayerName          `Opponent 1 prerating` `Opponent 2 prerating`
##     <dbl> <chr>               <chr>                  <chr>                 
## 1       1 GARY HUA            NA                     NA                    
## 2       2 DAKSHESH DARURI     1175                   917                   
## 3       3 ADITYA BAJAJ        NA                     NA                    
## 4       4 PATRICK H SCHILLING 1363                   1507                  
## 5       5 HANSHI ZUO          1242                   NA                    
## 6       6 HANSEN SONG         1399                   NA                    
## # ℹ 5 more variables: `Opponent 3 prerating` <chr>,
## #   `Opponent 4 prerating` <chr>, `Opponent 5 prerating` <chr>,
## #   `Opponent 6 prerating` <chr>, `Opponent 7 prerating` <chr>
#--- ---

# calculating the average of the Opponents' (1-7) PreRating scores for each row
# opponent_prerating_columns <- paste("Opponent", 1:7, "prerating", sep = " ")

# a new column for the average pre-rating
# df$Avg_Opponent_Prerating <- rowMeans(df[opponent_prerating_columns], na.rm = TRUE)

# head(df[, c("PairNum", "PlayerName", "Avg_Opponent_Prerating")])

# Debugging an error that seems to be due to NA values in the opponent prerating scores
opponent_prerating_columns <- paste("Opponent", 1:7, "prerating", sep = " ")
df[opponent_prerating_columns] <- lapply(df[opponent_prerating_columns], function(x) as.numeric(as.character(x)))

df$Avg_Opponent_Prerating <- rowMeans(df[opponent_prerating_columns], na.rm = TRUE)

head(df[, c("PairNum", "PlayerName", "Avg_Opponent_Prerating")])
## # A tibble: 6 × 3
##   PairNum PlayerName          Avg_Opponent_Prerating
##     <dbl> <chr>                                <dbl>
## 1       1 GARY HUA                             1648.
## 2       2 DAKSHESH DARURI                      1469.
## 3       3 ADITYA BAJAJ                         1696.
## 4       4 PATRICK H SCHILLING                  1574.
## 5       5 HANSHI ZUO                           1588.
## 6       6 HANSEN SONG                          1493.

Visualization and summary stats for the mean opponent prerating for each player

df$Avg_Opponent_Prerating <- as.numeric(df$Avg_Opponent_Prerating)

mean_val <- mean(df$Avg_Opponent_Prerating, na.rm = TRUE)
sd_val <- sd(df$Avg_Opponent_Prerating, na.rm = TRUE)
median_val <- median(df$Avg_Opponent_Prerating, na.rm = TRUE)
iqr_val <- IQR(df$Avg_Opponent_Prerating, na.rm = TRUE)
quantiles <- quantile(df$Avg_Opponent_Prerating, probs = c(0.25, 0.75), na.rm = TRUE)
min_val <- min(df$Avg_Opponent_Prerating, na.rm = TRUE)
max_val <- max(df$Avg_Opponent_Prerating, na.rm = TRUE)

cat("Mean:", mean_val, "\n",
    "Standard Deviation:", sd_val, "\n",
    "Median:", median_val, "\n",
    "Interquartile Range (IQR):", iqr_val, "\n",
    "25th Percentile:", quantiles[1], "\n",
    "75th Percentile:", quantiles[2], "\n",
    "Minimum Value:", min_val, "\n",
    "Maximum Value:", max_val, "\n")
## Mean: 1404.83 
##  Standard Deviation: 119.9393 
##  Median: 1412 
##  Interquartile Range (IQR): 165.7375 
##  25th Percentile: 1326.75 
##  75th Percentile: 1492.487 
##  Minimum Value: 1152 
##  Maximum Value: 1696.5
#--- ---

library(ggplot2)
library(gridExtra)

# histogram
p1 <- ggplot(df, aes(x = Avg_Opponent_Prerating)) +
  geom_histogram(aes(y = after_stat(density)), binwidth = 50, fill = "skyblue", color = "black", alpha = 0.6) +
  geom_density(aes(y = after_stat(density)), color = "blue", linewidth = 1, linetype = "solid") +
  labs(title = "Distribution of Avg Opponent Pre-Rating",
       x = "Avg Opponent Pre-Rating",
       y = "Density") +
  theme_minimal()

# boxplot
p2 <- ggplot(df, aes(x = "", y = Avg_Opponent_Prerating)) +
  geom_boxplot(fill = "lightgreen", color = "darkgreen", width = 0.5) +
  labs(title = "Boxplot of Avg Opponent Pre-Rating",
       x = "",
       y = "Avg Opponent Pre-Rating") +
  theme_minimal() +
  theme(axis.title.x=element_blank(),
        axis.text.x=element_blank(),
        axis.ticks.x=element_blank())

library(gridExtra)
grid.arrange(p1, p2, ncol = 2)

how the prerating scores of opponents compares to the prerating score of each player

df <- df %>%
  mutate(PreRating_num = as.numeric(as.character(PreRating))) %>%
  filter(!is.na(PreRating_num))

# visualizations for Player PreRating
p_player <- ggplot(df, aes(x = PreRating_num)) +
  geom_histogram(aes(y = ..density..), binwidth = 50, fill = "coral", color = "darkred", alpha = 0.6) +
  geom_density(aes(y = ..density..), color = "red", linewidth = 1, linetype = "solid") +
  labs(title = "Distribution of Player Pre-Rating",
       x = "Player Pre-Rating",
       y = "Density") +
  theme_minimal()

# visualizations for Avg Opponent PreRating
p_opponent <- ggplot(df, aes(x = Avg_Opponent_Prerating)) +
  geom_histogram(aes(y = ..density..), binwidth = 50, fill = "lightblue", color = "darkblue", alpha = 0.6) +
  geom_density(aes(y = ..density..), color = "blue", linewidth = 1, linetype = "solid") +
  labs(title = "Distribution of Avg Opponent Pre-Rating",
       x = "Avg Opponent Pre-Rating",
       y = "Density") +
  theme_minimal()

grid.arrange(p_player, p_opponent, ncol = 2)

#-- -- --

# statistics for Player PreRating
summary_player <- summary(df$PreRating_num)
iqr_player <- IQR(df$PreRating_num, na.rm = TRUE)

# statistics for Avg Opponent PreRating
summary_opponent <- summary(df$Avg_Opponent_Prerating)
iqr_opponent <- IQR(df$Avg_Opponent_Prerating, na.rm = TRUE)


cat("Summary Statistics for Player Pre-Rating:\n")
## Summary Statistics for Player Pre-Rating:
print(summary_player)
##    Min. 1st Qu.  Median    Mean 3rd Qu.    Max. 
##     853    1249    1417    1403    1591    1794
cat("Interquartile Range (IQR):", iqr_player, "\n\n")
## Interquartile Range (IQR): 342
cat("Summary Statistics for Avg Opponent Pre-Rating:\n")
## Summary Statistics for Avg Opponent Pre-Rating:
print(summary_opponent)
##    Min. 1st Qu.  Median    Mean 3rd Qu.    Max. 
##    1152    1334    1414    1407    1494    1696
cat("Interquartile Range (IQR):", iqr_opponent, "\n")
## Interquartile Range (IQR): 160.2042
scatter_plot <- ggplot(df, aes(x = PreRating_num, y = Avg_Opponent_Prerating)) +
  geom_point(aes(color = PreRating_num), alpha = 0.6) + 
  geom_smooth(method = "lm", se = FALSE, color = "blue") +  
  labs(title = "Player Pre-Rating vs. Avg Opponent Pre-Rating",
       x = "Player Pre-Rating",
       y = "Average Opponent Pre-Rating") +
  theme_minimal() +
  scale_color_gradient(low = "yellow", high = "red")

print(scatter_plot)
## `geom_smooth()` using formula = 'y ~ x'