Class 607, Project 1: Chess Data from .txt to .csv

Author

Troy Tournat

Published

November 11, 2026

Instructions here: https://brightspace.cuny.edu/content/enforced/1404182-SPS01_DATA_607_1269_1_56662/Project1.pdf?ou=1404182

Introduction

Data sources: The following data is a .txt file of chess tournament results provided to us.

Tackling the problem: First, I need to figure out the best way to import the .txt file so I can begin cleaning the data. The file has a clear visual structure, with dashes separating rows and | separating columns, but I don’t think that structure will translate well into RStudio, so I’ll need to rely on a lot of base R shorthand. I’ll likely use an LLM to help with the shorthand and reformatting. I also expect to need a loop or group_by() to calculate each player’s average opponent pre-rating.

Anticipated data challenges: The biggest challenge might end up being the tedious rebuilding a row and column data structure in order to extract the needed columns.

Citations:

Anthropic. (2025). Claude Opus 4.5 [Large language model]. (https://claude.ai/) Accessed November 8, 2026. Link to chat.

Analysis

Load Packages

#Install packages needed 
pacman::p_load(
  # Package Install and Management
  pacman,           # package install/load
  janitor,          #clean up data names 
  
  # Project and File Management
  readr,            # import data
  httr,             #github passkey 
  usethis,          #r environment
  RPostgres,        #SQL 
  DBI,              #SQL 
  jsonlite,         #working with json
  rvest,           #html files
  rstuioapi,        #api
  knitr,            #displaying data in quarto
  
  # General Data Management
  tidyverse,        # work with dates
  scales,           # ggplot 
  gmodels,          # freq tables SAS 
  skimr, 
  hms,              # recommended for dataset 4 
  patchwork        #data visualization, side by side 

)

Load data

location <- "~/Desktop/CUNY/607-assignments/projects/project 1/"

#importing txt file
#restructure by dropping the dashed - and verticle |  lines that separates the information 

#import using readr 
vector <- read_lines(paste0(location, "tournamentinfo.txt"))

Initial data clean up

#becomes vector, use str_subset and shorthand
body <- vector |> str_subset("^\\s*-+\\s*$", negate = TRUE) |> #looking for multiple hyphens ----. use negate = true because str_subset looks only for the sequences, so this says to leave ---- out. 
  tail(-2) #takes out header 


#using base r to separate each row into two separate rows
l1 <- body[seq(1, length(body), by = 2)] #by = 2 so every other line 
head(l1)

[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|"
[4] "    4 | PATRICK H SCHILLING             |5.5  |W  23|D  28|W   2|W  26|D   5|W  19|D   1|"
[5] "    5 | HANSHI ZUO                      |5.5  |W  45|W  37|D  12|D  13|D   4|W  14|W  17|"
[6] "    6 | HANSEN SONG                     |5.0  |W  34|D  29|L  11|W  35|D  10|W  27|W  21|"

l2<- body[seq(2, length(body), by = 2)]
head(l2)

[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    |"
[4] "   MI | 12616049 / R: 1716   ->1744     |N:2  |W    |B    |W    |B    |W    |B    |B    |"
[5] "   MI | 14601533 / R: 1655   ->1690     |N:2  |B    |W    |B    |W    |B    |W    |B    |"
[6] "   OH | 15055204 / R: 1686   ->1687     |N:3  |W    |B    |W    |B    |B    |W    |B    |"

#then take out | and put into a dataframe 
p1<-read_delim(I(l1), delim = "|", col_names = FALSE,
                 col_types = cols(.default = col_character()),
                 trim_ws = TRUE) |>
  select(1:10) |> #there are 10 columns 
  set_names(c("pair", "player", "total_pts", str_c("round", 1:7)))

p1 <- p1|>  # put the labels back
  mutate(pair = p1$pair) #matches p2 to p1 number 
head(p1)

#separating win/loss from opponent in round columns 
p1 <- p1 |>
  mutate(across(
    starts_with("round"),
    list(wlt      = ~ str_sub(.x, 1, 1),
         opponent = ~ parse_number(.x)),
    .names = "{.col}_{.fn}"
  ), .keep = "unused") |>
  relocate(pair, player, total_pts)
head(p1)

#move on to the second rows 
p2 <- read_delim(I(l2), delim = "|", col_names = FALSE,
                 col_types = cols(.default = col_character()),
                 trim_ws = TRUE) |>
  select(1:3) |>
  set_names(c("state", "rating_info", "n_games")) 

p2 <- p2 |> mutate(pair = p1$pair) 
head(p2)

#more work with separating columns 
p2 <- p2 |>
  separate_wider_regex(
    rating_info,
    patterns = c(
      uscf_id     = "\\d+","\\s*/\\s*R:\\s*",
      pre_rating  = "\\d+", "[^>]*->\\s*",
      post_rating = "\\d+", ".*")
  ) |>
  mutate(across(c(pre_rating, post_rating), as.integer), 
         n_games = parse_number(n_games), .keep = "unused") #take out n: 
head(p2)

#merge datasets by pair 
players <- p1 |> left_join(p2, by = "pair") 
head(players)

Pivot to find averages

#get average pre score of opponents 

#long data and get every opponent pre rating 
players_long <- players |>
 select(pair, pre_rating, ends_with("_opponent")) |>
 pivot_longer(-c(pair,pre_rating), names_to = "round", values_to = "opponent")|>
 #mutate(opponent = as.integer(opponent)) |>  
 left_join(
  select(players, opponent = pair, opp_pre_rating = pre_rating) |> #creates a lookup table where its just the pair and pre_rating. but it relabels them 
 mutate(opponent = as.integer(opponent)), by = "opponent") #make sure opponent is compatible
head(players_long)

# group by and average 
avg_opp <- players_long |> 
  group_by(pair)|>
  summarise(avg_opp_rating = round(mean(opp_pre_rating, na.rm = TRUE)), .groups = "drop")   #divides by the number of games actually played, not 7
head(avg_opp)

Add averages to main data frame, select columns

Player’s Name, Player’s State, Total Number of Points, Player’s Pre-Rating, and Average Pre Chess Rating of Opponent

#re-add average column to main df and keep only what need 


players_avg <- players|> left_join(avg_opp, by = "pair")|> 
  select(player, state, total_pts, pre_rating, avg_opp_rating)


kable(players_avg,
      col.names = c("Player's Name", "Player's State", "Total Number of Points", "Player’s Pre-Rating", "Avg Pre Chess Rating of Opponent"), 
      caption = "Chess Tournament Results, by each player")

Chess Tournament Results, by each player
Player’s Name	Player’s State	Total Number of Points	Player’s Pre-Rating	Avg Pre Chess Rating of Opponent
GARY HUA	ON	6.0	1794	1605
DAKSHESH DARURI	MI	6.0	1553	1469
ADITYA BAJAJ	MI	6.0	1384	1564
PATRICK H SCHILLING	MI	5.5	1716	1574
HANSHI ZUO	MI	5.5	1655	1501
HANSEN SONG	OH	5.0	1686	1519
GARY DEE SWATHELL	MI	5.0	1649	1372
EZEKIEL HOUGHTON	MI	5.0	1641	1468
STEFANO LEE	ON	5.0	1411	1523
ANVIT RAO	MI	5.0	1365	1554
CAMERON WILLIAM MC LEMAN	MI	4.5	1712	1468
KENNETH J TACK	MI	4.5	1663	1506
TORRANCE HENRY JR	MI	4.5	1666	1498
BRADLEY SHAW	MI	4.5	1610	1515
ZACHARY JAMES HOUGHTON	MI	4.5	1220	1484
MIKE NIKITIN	MI	4.0	1604	1386
RONALD GRZEGORCZYK	MI	4.0	1629	1499
DAVID SUNDEEN	MI	4.0	1600	1480
DIPANKAR ROY	MI	4.0	1564	1426
JASON ZHENG	MI	4.0	1595	1411
DINH DANG BUI	ON	4.0	1563	1470
EUGENE L MCCLURE	MI	4.0	1555	1300
ALAN BUI	ON	4.0	1363	1214
MICHAEL R ALDRICH	MI	4.0	1229	1357
LOREN SCHWIEBERT	MI	3.5	1745	1363
MAX ZHU	ON	3.5	1579	1507
GAURAV GIDWANI	MI	3.5	1552	1222
SOFIA ADINA STANESCU-BELLU	MI	3.5	1507	1522
CHIEDOZIE OKORIE	MI	3.5	1602	1314
GEORGE AVERY JONES	ON	3.5	1522	1144
RISHI SHETTY	MI	3.5	1494	1260
JOSHUA PHILIP MATHEWS	ON	3.5	1441	1379
JADE GE	MI	3.5	1449	1277
MICHAEL JEFFERY THOMAS	MI	3.5	1399	1375
JOSHUA DAVID LEE	MI	3.5	1438	1150
SIDDHARTH JHA	MI	3.5	1355	1388
AMIYATOSH PWNANANDAM	MI	3.5	980	1385
BRIAN LIU	MI	3.0	1423	1539
JOEL R HENDON	MI	3.0	1436	1430
FOREST ZHANG	MI	3.0	1348	1391
KYLE WILLIAM MURPHY	MI	3.0	1403	1248
JARED GE	MI	3.0	1332	1150
ROBERT GLEN VASEY	MI	3.0	1283	1107
JUSTIN D SCHILLING	MI	3.0	1199	1327
DEREK YAN	MI	3.0	1242	1152
JACOB ALEXANDER LAVALLEY	MI	3.0	377	1358
ERIC WRIGHT	MI	2.5	1362	1392
DANIEL KHAIN	MI	2.5	1382	1356
MICHAEL J MARTIN	MI	2.5	1291	1286
SHIVAM JHA	MI	2.5	1056	1296
TEJAS AYYAGARI	MI	2.5	1011	1356
ETHAN GUO	MI	2.5	935	1495
JOSE C YBARRA	MI	2.0	1393	1345
LARRY HODGE	MI	2.0	1270	1206
ALEX KONG	MI	2.0	1186	1406
MARISA RICCI	MI	2.0	1153	1414
MICHAEL LU	MI	2.0	1092	1363
VIRAJ MOHILE	MI	2.0	917	1391
SEAN M MC CORMICK	MI	2.0	853	1319
JULIA SHEN	MI	1.5	967	1330
JEZZEL FARKAS	ON	1.5	955	1327
ASHWIN BALAJI	MI	1.0	1530	1186
THOMAS JOSEPH HOSMER	MI	1.0	1175	1350
BEN LI	MI	1.0	1163	1263

Export .csv file

write_csv(players_avg, file.path(location, paste0("tournamentinfo_cleaned.csv")))

Conclusion

Overall, this project was not as difficult as I initially thought it would be. It was helpful to have done this toward the end of the class, after the tidyverse vignette and Project 2, when I already knew to use shorthand and pivot_longer(). I also found that using an LLM can be helpful for restructuring code, especially when shorthand was needed.

I have seen a lot of complex data cleaning, such as extracting data from PDF files, done in Python, and I would be interested in extending this work to answer two questions: a) could I extract the needed data if I only had a PDF file, and b) how would I use Python to do it? Overall, this was an interesting and useful project for handling messy text data in the future.