The first big project! This project is centered around cleaning up a nonstandard set of data, particularly one for chess tournament results. I am expected to create a organize some of the data that is already in this table as well as generate averages of each player’s opponent ranking. What’s left will be a consise look into who the players are, how they did in the tournament, and the average opponent they faced.
Some of the challenges I expect with this assignment are the cleaning of the data format as well as the augmentation of the columns in order to get the average opponent ranking. This will be a true test of some of the skills we have learned so far in this class, as well as apply new principles in order to clean values or match rows based on column values.
I approached this data entirely in R, while I could see a path to solving this problem using SQL as well.
First we will load in the .txt as is and see what we can do to start chipping away at it. after struggling through read.csv, read_csv, and other similar functions, I found that read.delim was the option that produced the best data load-in, and using “|” as the delimiter allowed us to get a rough graph to start.
Chess_Data <- read.delim("tournamentinfo.txt", header = FALSE, sep = "|")
glimpse(Chess_Data)## Rows: 196
## Columns: 11
## $ V1 <chr> "-----------------------------------------------------------------…
## $ V2 <chr> "", " Player Name ", " USCF ID / Rtg (Pre->Pos…
## $ V3 <chr> "", "Total", " Pts ", "", "6.0 ", "N:2 ", "", "6.0 ", "N:2 ", …
## $ V4 <chr> "", "Round", " 1 ", "", "W 39", "W ", "", "W 63", "B ", …
## $ V5 <chr> "", "Round", " 2 ", "", "W 21", "B ", "", "W 58", "W ", …
## $ V6 <chr> "", "Round", " 3 ", "", "W 18", "W ", "", "L 4", "B ", …
## $ V7 <chr> "", "Round", " 4 ", "", "W 14", "B ", "", "W 17", "W ", …
## $ V8 <chr> "", "Round", " 5 ", "", "W 7", "W ", "", "W 16", "B ", …
## $ V9 <chr> "", "Round", " 6 ", "", "D 12", "B ", "", "W 20", "W ", …
## $ V10 <chr> "", "Round", " 7 ", "", "D 4", "W ", "", "W 7", "B ", …
## $ V11 <lgl> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA…As you can see, the data, while now imported into R, is very much in need of some cleaning. we have rows that provide no value, and a lot of data with no strong corresponding columns. I noticed a pattern where after removing the “—–…” rows, there were different data sets on the even and odd rows. I added an index and used this along with a modulo operator (%), which allows you to use the remainder as a filtering condition. ThiS along with some other polishing I was able to get two tables that hold different sets of data we will need to complete the finished product.
## Rows: 64
## Columns: 3
## $ Area <chr> " ON ", " MI ", " MI ", " MI ", " MI ", " OH ", " …
## $ Rating <chr> " 15445895 / R: 1794 ->1817 ", " 14598900 / R: 1553 ->1…
## $ index <int> 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, …## Rows: 64
## Columns: 11
## $ Rank <chr> " 1 ", " 2 ", " 3 ", " 4 ", " 5 ", " 6 ", " …
## $ Name <chr> " GARY HUA ", " DAKSHESH DARURI …
## $ Points <chr> "6.0 ", "6.0 ", "6.0 ", "5.5 ", "5.5 ", "5.0 ", "5.0 ", …
## $ R1 <chr> "W 39", "W 63", "L 8", "W 23", "W 45", "W 34", "W 57", …
## $ R2 <chr> "W 21", "W 58", "W 61", "D 28", "W 37", "D 29", "W 46", …
## $ R3 <chr> "W 18", "L 4", "W 25", "W 2", "D 12", "L 11", "W 13", …
## $ R4 <chr> "W 14", "W 17", "W 21", "W 26", "D 13", "W 35", "W 11", …
## $ R5 <chr> "W 7", "W 16", "W 11", "D 5", "D 4", "D 10", "L 1", …
## $ R6 <chr> "D 12", "W 20", "W 13", "W 19", "W 14", "W 27", "W 9", …
## $ R7 <chr> "D 4", "W 7", "W 12", "D 1", "W 17", "W 21", "L 2", …
## $ index <int> 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, …Now that I have both of these tables, I will join them together and start cleaning the values within the tables, particularly the Elo and the Round results. I need these to be integers so that they can be manipulated. There is one caveat to this that I will go into later, and I still don’t fully understand it.
Full_Clean_Data <- full_join(Chess_Data_Even,Chess_Data_Odd) ## Joining with `by = join_by(index)`glimpse(Full_Clean_Data)## Rows: 64
## Columns: 13
## $ Area <chr> " ON ", " MI ", " MI ", " MI ", " MI ", " OH ", " …
## $ Rating <chr> " 15445895 / R: 1794 ->1817 ", " 14598900 / R: 1553 ->1…
## $ index <int> 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, …
## $ Rank <chr> " 1 ", " 2 ", " 3 ", " 4 ", " 5 ", " 6 ", " …
## $ Name <chr> " GARY HUA ", " DAKSHESH DARURI …
## $ Points <chr> "6.0 ", "6.0 ", "6.0 ", "5.5 ", "5.5 ", "5.0 ", "5.0 ", …
## $ R1 <chr> "W 39", "W 63", "L 8", "W 23", "W 45", "W 34", "W 57", …
## $ R2 <chr> "W 21", "W 58", "W 61", "D 28", "W 37", "D 29", "W 46", …
## $ R3 <chr> "W 18", "L 4", "W 25", "W 2", "D 12", "L 11", "W 13", …
## $ R4 <chr> "W 14", "W 17", "W 21", "W 26", "D 13", "W 35", "W 11", …
## $ R5 <chr> "W 7", "W 16", "W 11", "D 5", "D 4", "D 10", "L 1", …
## $ R6 <chr> "D 12", "W 20", "W 13", "W 19", "W 14", "W 27", "W 9", …
## $ R7 <chr> "D 4", "W 7", "W 12", "D 1", "W 17", "W 21", "L 2", …Full_Clean_Data2 <- Full_Clean_Data |>
mutate(
across(
c(R1, R2, R3, R4, R5, R6, R7),
~str_remove_all(.x, "W|L|D")
)
) |>
mutate(
across(
c(R1, R2, R3, R4, R5, R6, R7, Rank),
~as.integer(.x)
)
) |>
mutate(
Rating = str_remove(Rating, ".*:"),
Rating = str_remove(Rating, "-.*"),
Rating = str_remove(Rating, "P.*")
)## Warning: There were 7 warnings in `mutate()`.
## The first warning was:
## ℹ In argument: `across(c(R1, R2, R3, R4, R5, R6, R7, Rank), ~as.integer(.x))`.
## Caused by warning:
## ! NAs introduced by coercion
## ℹ Run `dplyr::last_dplyr_warnings()` to see the 6 remaining warnings.glimpse(Full_Clean_Data2)## Rows: 64
## Columns: 13
## $ Area <chr> " ON ", " MI ", " MI ", " MI ", " MI ", " OH ", " …
## $ Rating <chr> " 1794 ", " 1553 ", " 1384 ", " 1716 ", " 1655 ", " 1…
## $ index <int> 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, …
## $ Rank <int> 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, …
## $ Name <chr> " GARY HUA ", " DAKSHESH DARURI …
## $ Points <chr> "6.0 ", "6.0 ", "6.0 ", "5.5 ", "5.5 ", "5.0 ", "5.0 ", …
## $ R1 <int> 39, 63, 8, 23, 45, 34, 57, 3, 25, 16, 38, 42, 36, 54, 19, 10, 4…
## $ R2 <int> 21, 58, 61, 28, 37, 29, 46, 32, 18, 19, 56, 33, 27, 44, 16, 15,…
## $ R3 <int> 18, 4, 25, 2, 12, 11, 13, 14, 59, 55, 6, 5, 7, 8, 30, NA, 26, 1…
## $ R4 <int> 14, 17, 21, 26, 13, 35, 11, 9, 8, 31, 7, 38, 5, 1, 22, 39, 2, 3…
## $ R5 <int> 7, 16, 11, 5, 4, 10, 1, 47, 26, 6, 3, NA, 33, 27, 54, 2, 23, 19…
## $ R6 <int> 12, 20, 13, 19, 14, 27, 9, 28, 7, 25, 34, 1, 3, 5, 33, 36, 22, …
## $ R7 <int> 4, 7, 12, 1, 17, 21, 2, 19, 20, 18, 26, 3, 32, 31, 38, NA, 5, 1…Now here is where I really hit a roadblock with this assignment. My next goal was to use the cleaned up match results to pull in the Elo of the player they played in order to eventually generate an average. I am well aware of xlookup in excel, but trying to figure out how to get an operation that mirrors it in R was quite difficult for me. Through searching around and attempting different joins, I found that the most effective method of getting these was to use a more native solution through Rating[match(Rank from Match, Rank of player they played)].
The Caveat I described above was that this match function would not work if the RX values were integers. I have no Idea why this was the case but I rolled with it, and my work around was to just convert them after the match function.
With these data points matched, I was able to calculate the newest column that finds the average opponent Elo. Thanks to assignment 3A I had the know-how of using rowwise() to get accurate averages for each row. With this being done, all that was needed was a little bit of cleaning, and then I was done!
Chess_Data_Joins <- Full_Clean_Data2 |>
select(!index) |>
mutate(R1elo = Rating[match(R1, Rank)],
R2elo = Rating[match(R2, Rank)],
R3elo = Rating[match(R3, Rank)],
R4elo = Rating[match(R4, Rank)],
R5elo = Rating[match(R5, Rank)],
R6elo = Rating[match(R6, Rank)],
R7elo = Rating[match(R7, Rank)]) |>
mutate(
across(
c(R1elo, R2elo, R3elo, R4elo, R5elo, R6elo, R7elo),
~as.integer(.x)
)
) |>
rowwise() |>
mutate(Opponent_Avg_Elo = mean(
c(R1elo, R2elo, R3elo, R4elo, R5elo, R6elo, R7elo
)
, na.rm = TRUE)) |>
relocate(Name, Area, Points, Rating, Opponent_Avg_Elo) |>
arrange(Rank) |>
select(Name, Area, Points, Rating, Opponent_Avg_Elo) |>
mutate(Opponent_Avg_Elo = round(Opponent_Avg_Elo, digits = 0))
kable(Chess_Data_Joins)| Name | Area | Points | Rating | Opponent_Avg_Elo |
|---|---|---|---|---|
| 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 |
In conclusion, this was a well rounded test of being able clean non-standard data formats. After spending the first few weeks of this class focused on the basics of the different IDE’s and the building blocks of R writing, this assignment allowed me to bring everything together while still learning new critical skills for building. I was able to take the raw data, convert it to a readable and workable format, and then apply further logic to use the existing data to generate new data.
The areas I expected to be the most difficult ended up working out to be some of the simpler problems. For example, the actual reading of the document and being able to separate the data into two working tables I thought would take a lot longer to do. The hardest part for me was the generating of the average and understanding the best way to join tables. There are a lot of different joins and matches, and knowing which ones to use for which situation is difficult initially. also, I am not as comfortable with native R functions as I am with tidyverse functions, so I always feel less confident resorting to a native function to solve a problem.
In thinking about what could be done differently or what could be improved I can think of two things. One, I think there is room for some of this data manipulation to be done in SQL in potentially a more concise manner. Although more concise, it would also require an added layer of uploading the data to SQL and then exporting back out. Second, I think there is room to optimize the code. A lot of my code for this assignment was scrappy, and I feel that there are better ways to apply functions over a range of data differently and more compact than I did. I also think I could get more done within a function than I do now, such as taking care of multiple mutations under the same function. As I continue to figure out how everything interacts within the R language I feel more comfortable performing operations independent of eachother, but I know ultimately there are ways to make it neater and more concise.