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.
# 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.
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.
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)
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'