First step is to load in the data table from the tournamentinfo.txt file and proceed to clean up the data by removing the various characters misconstruing the categories. Basically remove all of these characters that are separate from the actual data using regex. (I aim to add the columns/categories later.)
lines <- readLines("C:/Users/aldai/Downloads/tournamentinfo.txt")## Warning in readLines("C:/Users/aldai/Downloads/tournamentinfo.txt"): incomplete
## final line found on 'C:/Users/aldai/Downloads/tournamentinfo.txt'lines <- lines[!grepl("^[-]+", lines)]
lines <- lines[!grepl("Pair \\|", lines)]
lines <- lines[!grepl("Num \\|", lines)]
lines <- lines[!grepl("USCF ID / Rtg", lines)]
lines <- lines[!grepl(" Pts ", lines)]
head(lines)## [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 |"Upon inspection of the table, each player and their info takes up 2 lines worth of space. Not only is the goal to combine them into one line for the finale of this project, but also to extract those specific categories to align with this information here: Gary Hua, ON, 6.0, 1794, and 1605 but that will be calculated in the next following code block.
player_records <- split(lines, rep(seq_len(length(lines) / 2), each = 2))
parse_record <- function(record) {
parts1 <- str_trim(strsplit(record[1], "\\|")[[1]])
parts2 <- str_trim(strsplit(record[2], "\\|")[[1]])
pair_num <- as.integer(parts1[1])
name <- parts1[2] #Player name along with total points from the first line
total_points <- as.numeric(parts1[3])
state <- parts2[1] #Player state and player pre-rating from the second line
rating_match <- str_extract(parts2[2], "(?<=R:\\s)\\d+")
pre_rating <- as.numeric(rating_match)
temp <- str_match(parts2[2], "R:\\s*(\\d+)")
rating_match <- temp[, 2]
pre_rating <- as.numeric(rating_match)
round_info <- parts1[4:length(parts1)] #Grabbing round info of each player of only the games that count (W, L or D)
get_opponent_num <- function(x) {
first_char <- substr(x, 1, 1)
if (!first_char %in% c("W", "L", "D")) { #Games that count
return(NA_integer_)
}
as.integer(str_extract(x, "\\d+"))
}
opp_nums <- sapply(round_info, get_opponent_num, USE.NAMES = FALSE) #Check if a string starts with W, L, or D
opp_nums <- opp_nums[!is.na(opp_nums)]
data.frame( #Create the data frame to be used, and label for the upcoming list
Name = name,
State = state,
TotalPoints = total_points,
PreRating = pre_rating,
Opponents = paste(opp_nums, collapse = ","),
PairNum = pair_num,
stringsAsFactors = FALSE
)
}
play_list <- lapply(player_records, parse_record)
tourna_df <- bind_rows(play_list)
head(tourna_df) #Displaying result to all conditions except the average prechess column is split into 2. The following code block is using this data to create the left join.## Name State TotalPoints PreRating Opponents PairNum
## 1 GARY HUA ON 6.0 1794 39,21,18,14,7,12,4 1
## 2 DAKSHESH DARURI MI 6.0 1553 63,58,4,17,16,20,7 2
## 3 ADITYA BAJAJ MI 6.0 1384 8,61,25,21,11,13,12 3
## 4 PATRICK H SCHILLING MI 5.5 1716 23,28,2,26,5,19,1 4
## 5 HANSHI ZUO MI 5.5 1655 45,37,12,13,4,14,17 5
## 6 HANSEN SONG OH 5.0 1686 34,29,11,35,10,27,21 6For this code block I’m instead using a joins approach rather than the simple rowwise() function for 2 main reasons, because I already have a way of identifying each player’s tournament run with PairNum and thus confirms each list of player’s opponents is correct. Secondly, rowwise() may only the grab the PairNum and directly cause each row’s code to see only that row rather than the entire data set. In using joins, I’m essentially using the data from Opponents and their PairNum for each player’s run to a separate table. This table will be used to combine into the final dataframe in the next code block.
df_opponents <- tourna_df %>%
select(PairNum, Opponents) %>%
separate_rows(Opponents, sep = ",") %>% #split opponents into separate numbers within their PairNum
mutate(Opponents = as.integer(Opponents)) %>% #Convert from character to integer
filter(!is.na(Opponents))
df_opponents <- df_opponents %>%
#Join so that Opponents (the opponent's PairNum) uses the opponent's PreRating
left_join(
tourna_df %>% select(PairNum, OppPreRating = PreRating),
by = c("Opponents" = "PairNum")
)
head(df_opponents)## # A tibble: 6 × 3
## PairNum Opponents OppPreRating
## <int> <int> <dbl>
## 1 1 39 1436
## 2 1 21 1563
## 3 1 18 1600
## 4 1 14 1610
## 5 1 7 1649
## 6 1 12 1663Finding the AvgOppRating or the Average Pre Chess Opponent rating in the tournament requires the mean of those games counted within the last separate table, which will be combined in this code block.
df_avg <- df_opponents %>%
group_by(PairNum) %>%
summarize(AvgOppRating = mean(OppPreRating, na.rm = TRUE)) %>%
mutate(AvgOppRating = round(AvgOppRating, 0)) %>% #Hides decimals but still rounds beforehand
ungroup()
players_df <- tourna_df %>%
left_join(df_avg, by = "PairNum")
head(players_df) #Display all columns, next will only contain what is necessary## Name State TotalPoints PreRating Opponents PairNum
## 1 GARY HUA ON 6.0 1794 39,21,18,14,7,12,4 1
## 2 DAKSHESH DARURI MI 6.0 1553 63,58,4,17,16,20,7 2
## 3 ADITYA BAJAJ MI 6.0 1384 8,61,25,21,11,13,12 3
## 4 PATRICK H SCHILLING MI 5.5 1716 23,28,2,26,5,19,1 4
## 5 HANSHI ZUO MI 5.5 1655 45,37,12,13,4,14,17 5
## 6 HANSEN SONG OH 5.0 1686 34,29,11,35,10,27,21 6
## AvgOppRating
## 1 1605
## 2 1469
## 3 1564
## 4 1574
## 5 1501
## 6 1519The last code block was the calculation and the whole tableset, now to select the name, state, the total points, the prerating, and the average pre chess rating of opponents.
players_df <- players_df %>%
select(Name, State, TotalPoints, PreRating, AvgOppRating)
players_df## Name State TotalPoints PreRating AvgOppRating
## 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 1519
## 7 GARY DEE SWATHELL MI 5.0 1649 1372
## 8 EZEKIEL HOUGHTON MI 5.0 1641 1468
## 9 STEFANO LEE ON 5.0 1411 1523
## 10 ANVIT RAO MI 5.0 1365 1554
## 11 CAMERON WILLIAM MC LEMAN MI 4.5 1712 1468
## 12 KENNETH J TACK MI 4.5 1663 1506
## 13 TORRANCE HENRY JR MI 4.5 1666 1498
## 14 BRADLEY SHAW MI 4.5 1610 1515
## 15 ZACHARY JAMES HOUGHTON MI 4.5 1220 1484
## 16 MIKE NIKITIN MI 4.0 1604 1386
## 17 RONALD GRZEGORCZYK MI 4.0 1629 1499
## 18 DAVID SUNDEEN MI 4.0 1600 1480
## 19 DIPANKAR ROY MI 4.0 1564 1426
## 20 JASON ZHENG MI 4.0 1595 1411
## 21 DINH DANG BUI ON 4.0 1563 1470
## 22 EUGENE L MCCLURE MI 4.0 1555 1300
## 23 ALAN BUI ON 4.0 1363 1214
## 24 MICHAEL R ALDRICH MI 4.0 1229 1357
## 25 LOREN SCHWIEBERT MI 3.5 1745 1363
## 26 MAX ZHU ON 3.5 1579 1507
## 27 GAURAV GIDWANI MI 3.5 1552 1222
## 28 SOFIA ADINA STANESCU-BELLU MI 3.5 1507 1522
## 29 CHIEDOZIE OKORIE MI 3.5 1602 1314
## 30 GEORGE AVERY JONES ON 3.5 1522 1144
## 31 RISHI SHETTY MI 3.5 1494 1260
## 32 JOSHUA PHILIP MATHEWS ON 3.5 1441 1379
## 33 JADE GE MI 3.5 1449 1277
## 34 MICHAEL JEFFERY THOMAS MI 3.5 1399 1375
## 35 JOSHUA DAVID LEE MI 3.5 1438 1150
## 36 SIDDHARTH JHA MI 3.5 1355 1388
## 37 AMIYATOSH PWNANANDAM MI 3.5 980 1385
## 38 BRIAN LIU MI 3.0 1423 1539
## 39 JOEL R HENDON MI 3.0 1436 1430
## 40 FOREST ZHANG MI 3.0 1348 1391
## 41 KYLE WILLIAM MURPHY MI 3.0 1403 1248
## 42 JARED GE MI 3.0 1332 1150
## 43 ROBERT GLEN VASEY MI 3.0 1283 1107
## 44 JUSTIN D SCHILLING MI 3.0 1199 1327
## 45 DEREK YAN MI 3.0 1242 1152
## 46 JACOB ALEXANDER LAVALLEY MI 3.0 377 1358
## 47 ERIC WRIGHT MI 2.5 1362 1392
## 48 DANIEL KHAIN MI 2.5 1382 1356
## 49 MICHAEL J MARTIN MI 2.5 1291 1286
## 50 SHIVAM JHA MI 2.5 1056 1296
## 51 TEJAS AYYAGARI MI 2.5 1011 1356
## 52 ETHAN GUO MI 2.5 935 1495
## 53 JOSE C YBARRA MI 2.0 1393 1345
## 54 LARRY HODGE MI 2.0 1270 1206
## 55 ALEX KONG MI 2.0 1186 1406
## 56 MARISA RICCI MI 2.0 1153 1414
## 57 MICHAEL LU MI 2.0 1092 1363
## 58 VIRAJ MOHILE MI 2.0 917 1391
## 59 SEAN M MC CORMICK MI 2.0 853 1319
## 60 JULIA SHEN MI 1.5 967 1330
## 61 JEZZEL FARKAS ON 1.5 955 1327
## 62 ASHWIN BALAJI MI 1.0 1530 1186
## 63 THOMAS JOSEPH HOSMER MI 1.0 1175 1350
## 64 BEN LI MI 1.0 1163 1263The following code block is to generate a csv file:
write.csv(players_df, "players_info.csv")I read in the raw text data from tournamentinfo.txt and used regular expressions to remove extraneous headers and dashes. Then I split the file so that each player’s information appeared on two lines. I extracted each player’s opponent numbers (only the rounds labeled W, L, or D) and created a code to looked up the opponents’ pre-ratings in a list unique to each player. Finally, I calculated the average of those opponent ratings for each player and combined the results into a new separate table/dataframe with columns for name, state, total points, pre-rating, and the average pre chess rating of opponents, to fulfill all conditions.