Introduction

Chess is known as a noble game – “a game of kings“, and it is no coincidence, as it takes/develops similar skills.

In chess, just like in life, you can either aimlessly wonder around, hoping for a miracle, or set yourself a goal, build a plan and strive to accomplish it. The latter is no guarantee of success, but the former is a sure recipe for failure. Chess teaches you to have a goal and overcome the obstacles on your way there.

Requirements

In this project, a text file with chess tournament results where the information has some structure given to me - in a text format. The requirements were to :

  • Create an R Markdown file that generates a .CSV file that could for example be imported into a SQL database.

  • The information should be like the following :

    Player’s Name Player’s State Total Number of Points Player’s Pre-Rating Average Pre Chess Rating of Opponnents
    Gary Hua ON 6.0 1794 1605

Questions

For this project, I was curious to get more insights into thses dataset. 1. Who has the most Pre-rating? 2. Which state has players with highest pre-rating? 3. What are the factors affects on winning tournaments?

Work wireframe

I decided to put a plan how do I will handle, analyze the data given. This plan breaks down into steps, so I can easily follow these steps for more organized work.

Steps to follow (Methodology):

  1. Save the original tournamentinfo.txt file into my local machine then push it to my personal GitHub account.
  2. Download necessary libraries.
  3. Read the file as a dataframe using R Markdown.
  4. Clean up the data from any special characters, empty strings,..etc.
  5. Combine each two consecutive rows into one row - The table in the txt file are with two tier headers and rows
  6. Seperate the columns from one column into multiples.
  7. Get the opponents pre-rating year for each player
  8. Calculate the average pre-rating for all opponents pre-rating for each player.
  9. Explore the final dataframe and write the table to a csv file.
  10. Do analysis to the defined model and get conclusion.

Loading necessary liberaries

## Loading required package: bitops
## 
## Attaching package: 'dplyr'
## The following object is masked from 'package:kableExtra':
## 
##     group_rows
## The following objects are masked from 'package:stats':
## 
##     filter, lag
## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union
## ── Attaching packages ──────────────────────────────────────────────────────────────────────────────────────────────────────── tidyverse 1.2.1 ──
## ✔ ggplot2 3.2.1     ✔ readr   1.3.1
## ✔ tibble  2.1.3     ✔ purrr   0.3.2
## ✔ tidyr   0.8.3     ✔ forcats 0.4.0
## ── Conflicts ─────────────────────────────────────────────────────────────────────────────────────────────────────────── tidyverse_conflicts() ──
## ✖ tidyr::complete()   masks RCurl::complete()
## ✖ tidyr::extract()    masks magrittr::extract()
## ✖ dplyr::filter()     masks stats::filter()
## ✖ dplyr::group_rows() masks kableExtra::group_rows()
## ✖ dplyr::lag()        masks stats::lag()
## ✖ purrr::set_names()  masks magrittr::set_names()
## Loading required package: lattice
## Loading required package: survival
## Loading required package: Formula
## 
## Attaching package: 'Hmisc'
## The following objects are masked from 'package:dplyr':
## 
##     src, summarize
## The following objects are masked from 'package:base':
## 
##     format.pval, units

Here I pushed the file into my GitHub account and store it into a variable and call read.delim() on it.

V1
—————————————————————————————–
Pair | Player Name |Total|Round|Round|Round|Round|Round|Round|Round|
Num | USCF ID / Rtg (Pre->Post) | Pts | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
—————————————————————————————–
1 | GARY HUA |6.0 |W 39|W 21|W 18|W 14|W 7|D 12|D 4|
ON | 15445895 / R: 1794 ->1817 |N:2 |W |B |W |B |W |B |W | 
## [1] TRUE
  1. As shown in the output, the table has many issues need to be handle first before analysing:
  • The table has (-) needs to be removed
    • Has an empty rows
    • The data of each player is devided into two rows.
str_remove_all.tourn.V1………..
Pair | Player Name |Total|Round|Round|Round|Round|Round|Round|Round|
Num | USCF ID / Rtg (Pre>Post) | Pts | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
1 | GARY HUA |6.0 |W 39|W 21|W 18|W 14|W 7|D 12|D 4|
ON | 15445895 / R: 1794 >1817 |N:2 |W |B |W |B |W |B |W |
tourn_2..apply.tourn_2……..1..all….
Pair | Player Name |Total|Round|Round|Round|Round|Round|Round|Round|
Num | USCF ID / Rtg (Pre>Post) | Pts | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
1 | GARY HUA |6.0 |W 39|W 21|W 18|W 14|W 7|D 12|D 4|
ON | 15445895 / R: 1794 >1817 |N:2 |W |B |W |B |W |B |W |
2 | DAKSHESH DARURI |6.0 |W 63|W 58|L 4|W 17|W 16|W 20|W 7|
MI | 14598900 / R: 1553 >1663 |N:2 |B |W |B |W |B |W |B |
3 | ADITYA BAJAJ |6.0 |L 8|W 61|W 25|W 21|W 11|W 13|W 12|
MI | 14959604 / R: 1384 >1640 |N:2 |W |B |W |B |W |B |W |
4 | PATRICK H SCHILLING |5.5 |W 23|D 28|W 2|W 26|D 5|W 19|D 1|
MI | 12616049 / R: 1716 >1744 |N:2 |W |B |W |B |W |B |B |
5 | HANSHI ZUO |5.5 |W 45|W 37|D 12|D 13|D 4|W 14|W 17|
MI | 14601533 / R: 1655 >1690 |N:2 |B |W |B |W |B |W |B |
6 | HANSEN SONG |5.0 |W 34|D 29|L 11|W 35|D 10|W 27|W 21|
OH | 15055204 / R: 1686 >1687 |N:3 |W |B |W |B |B |W |B |
7 | GARY DEE SWATHELL |5.0 |W 57|W 46|W 13|W 11|L 1|W 9|L 2|
MI | 11146376 / R: 1649 >1673 |N:3 |W |B |W |B |B |W |W |
8 | EZEKIEL HOUGHTON |5.0 |W 3|W 32|L 14|L 9|W 47|W 28|W 19|
MI | 15142253 / R: 1641P17>1657P24 |N:3 |B |W |B |W |B |W |W |
9 | STEFANO LEE |5.0 |W 25|L 18|W 59|W 8|W 26|L 7|W 20|
ON | 14954524 / R: 1411 >1564 |N:2 |W |B |W |B |W |B |B |
10 | ANVIT RAO |5.0 |D 16|L 19|W 55|W 31|D 6|W 25|W 18|
MI | 14150362 / R: 1365 >1544 |N:3 |W |W |B |B |W |B |W |
11 | CAMERON WILLIAM MC LEMAN |4.5 |D 38|W 56|W 6|L 7|L 3|W 34|W 26|
MI | 12581589 / R: 1712 >1696 |N:3 |B |W |B |W |B |W |B |
12 | KENNETH J TACK |4.5 |W 42|W 33|D 5|W 38|H |D 1|L 3|
MI | 12681257 / R: 1663 >1670 |N:3 |W |B |W |B | |W |B |
13 | TORRANCE HENRY JR |4.5 |W 36|W 27|L 7|D 5|W 33|L 3|W 32|
MI | 15082995 / R: 1666 >1662 |N:3 |B |W |B |B |W |W |B |
14 | BRADLEY SHAW |4.5 |W 54|W 44|W 8|L 1|D 27|L 5|W 31|
MI | 10131499 / R: 1610 >1618 |N:3 |W |B |W |W |B |B |W |
15 | ZACHARY JAMES HOUGHTON |4.5 |D 19|L 16|W 30|L 22|W 54|W 33|W 38|
MI | 15619130 / R: 1220P13>1416P20 |N:3 |B |B |W |W |B |B |W |
16 | MIKE NIKITIN |4.0 |D 10|W 15|H |W 39|L 2|W 36|U |
MI | 10295068 / R: 1604 >1613 |N:3 |B |W | |B |W |B | |
17 | RONALD GRZEGORCZYK |4.0 |W 48|W 41|L 26|L 2|W 23|W 22|L 5|
MI | 10297702 / R: 1629 >1610 |N:3 |W |B |W |B |W |B |W |
18 | DAVID SUNDEEN |4.0 |W 47|W 9|L 1|W 32|L 19|W 38|L 10|
MI | 11342094 / R: 1600 >1600 |N:3 |B |W |B |W |B |W |B |
19 | DIPANKAR ROY |4.0 |D 15|W 10|W 52|D 28|W 18|L 4|L 8|
MI | 14862333 / R: 1564 >1570 |N:3 |W |B |W |B |W |W |B |
20 | JASON ZHENG |4.0 |L 40|W 49|W 23|W 41|W 28|L 2|L 9|
MI | 14529060 / R: 1595 >1569 |N:4 |W |B |W |B |W |B |W |
21 | DINH DANG BUI |4.0 |W 43|L 1|W 47|L 3|W 40|W 39|L 6|
ON | 15495066 / R: 1563P22>1562 |N:3 |B |W |B |W |W |B |W |
22 | EUGENE L MCCLURE |4.0 |W 64|D 52|L 28|W 15|H |L 17|W 40|
MI | 12405534 / R: 1555 >1529 |N:4 |W |B |W |B | |W |B |
23 | ALAN BUI |4.0 |L 4|W 43|L 20|W 58|L 17|W 37|W 46|
ON | 15030142 / R: 1363 >1371 | |B |W |B |W |B |W |B |
24 | MICHAEL R ALDRICH |4.0 |L 28|L 47|W 43|L 25|W 60|W 44|W 39|
MI | 13469010 / R: 1229 >1300 |N:4 |B |W |B |B |W |W |B |
25 | LOREN SCHWIEBERT |3.5 |L 9|W 53|L 3|W 24|D 34|L 10|W 47|
MI | 12486656 / R: 1745 >1681 |N:4 |B |W |B |W |B |W |B |
26 | MAX ZHU |3.5 |W 49|W 40|W 17|L 4|L 9|D 32|L 11|
ON | 15131520 / R: 1579 >1564 |N:4 |B |W |B |W |B |W |W |
27 | GAURAV GIDWANI |3.5 |W 51|L 13|W 46|W 37|D 14|L 6|U |
MI | 14476567 / R: 1552 >1539 |N:4 |W |B |W |B |W |B | |
28 | SOFIA ADINA STANESCUBELLU |3.5 |W 24|D 4|W 22|D 19|L 20|L 8|D 36|
MI | 14882954 / R: 1507 >1513 |N:3 |W |W |B |W |B |B |W |
29 | CHIEDOZIE OKORIE |3.5 |W 50|D 6|L 38|L 34|W 52|W 48|U |
MI | 15323285 / R: 1602P6 >1508P12 |N:4 |B |W |B |W |W |B | |
30 | GEORGE AVERY JONES |3.5 |L 52|D 64|L 15|W 55|L 31|W 61|W 50|
ON | 12577178 / R: 1522 >1444 | |W |B |B |W |W |B |B |
31 | RISHI SHETTY |3.5 |L 58|D 55|W 64|L 10|W 30|W 50|L 14|
MI | 15131618 / R: 1494 >1444 | |B |W |B |W |B |W |B |
32 | JOSHUA PHILIP MATHEWS |3.5 |W 61|L 8|W 44|L 18|W 51|D 26|L 13|
ON | 14073750 / R: 1441 >1433 |N:4 |W |B |W |B |W |B |W |
33 | JADE GE |3.5 |W 60|L 12|W 50|D 36|L 13|L 15|W 51|
MI | 14691842 / R: 1449 >1421 | |B |W |B |W |B |W |B |
34 | MICHAEL JEFFERY THOMAS |3.5 |L 6|W 60|L 37|W 29|D 25|L 11|W 52|
MI | 15051807 / R: 1399 >1400 | |B |W |B |B |W |B |W |
35 | JOSHUA DAVID LEE |3.5 |L 46|L 38|W 56|L 6|W 57|D 52|W 48|
MI | 14601397 / R: 1438 >1392 | |W |W |B |W |B |B |W |
36 | SIDDHARTH JHA |3.5 |L 13|W 57|W 51|D 33|H |L 16|D 28|
MI | 14773163 / R: 1355 >1367 |N:4 |W |B |W |B | |W |B |
37 | AMIYATOSH PWNANANDAM |3.5 |B |L 5|W 34|L 27|H |L 23|W 61|
MI | 15489571 / R: 980P12>1077P17 | | |B |W |W | |B |W |
38 | BRIAN LIU |3.0 |D 11|W 35|W 29|L 12|H |L 18|L 15|
MI | 15108523 / R: 1423 >1439 |N:4 |W |B |W |W | |B |B |
39 | JOEL R HENDON |3.0 |L 1|W 54|W 40|L 16|W 44|L 21|L 24|
MI | 12923035 / R: 1436P23>1413 |N:4 |B |W |B |W |B |W |W |
40 | FOREST ZHANG |3.0 |W 20|L 26|L 39|W 59|L 21|W 56|L 22|
MI | 14892710 / R: 1348 >1346 | |B |B |W |W |B |W |W |
41 | KYLE WILLIAM MURPHY |3.0 |W 59|L 17|W 58|L 20|X |U |U |
MI | 15761443 / R: 1403P5 >1341P9 | |B |W |B |W | | | |
42 | JARED GE |3.0 |L 12|L 50|L 57|D 60|D 61|W 64|W 56|
MI | 14462326 / R: 1332 >1256 | |B |W |B |B |W |W |B |
43 | ROBERT GLEN VASEY |3.0 |L 21|L 23|L 24|W 63|W 59|L 46|W 55|
MI | 14101068 / R: 1283 >1244 | |W |B |W |W |B |B |W |
44 | JUSTIN D SCHILLING |3.0 |B |L 14|L 32|W 53|L 39|L 24|W 59|
MI | 15323504 / R: 1199 >1199 | | |W |B |B |W |B |W |
45 | DEREK YAN |3.0 |L 5|L 51|D 60|L 56|W 63|D 55|W 58|
MI | 15372807 / R: 1242 >1191 | |W |B |W |B |W |B |W |
46 | JACOB ALEXANDER LAVALLEY |3.0 |W 35|L 7|L 27|L 50|W 64|W 43|L 23|
MI | 15490981 / R: 377P3 >1076P10 | |B |W |B |W |B |W |W |
47 | ERIC WRIGHT |2.5 |L 18|W 24|L 21|W 61|L 8|D 51|L 25|
MI | 12533115 / R: 1362 >1341 | |W |B |W |B |W |B |W |
48 | DANIEL KHAIN |2.5 |L 17|W 63|H |D 52|H |L 29|L 35|
MI | 14369165 / R: 1382 >1335 | |B |W | |B | |W |B |
49 | MICHAEL J MARTIN |2.5 |L 26|L 20|D 63|D 64|W 58|H |U |
MI | 12531685 / R: 1291P12>1259P17 | |W |W |B |W |B | | |
50 | SHIVAM JHA |2.5 |L 29|W 42|L 33|W 46|H |L 31|L 30|
MI | 14773178 / R: 1056 >1111 | |W |B |W |B | |B |W |
51 | TEJAS AYYAGARI |2.5 |L 27|W 45|L 36|W 57|L 32|D 47|L 33|
MI | 15205474 / R: 1011 >1097 | |B |W |B |W |B |W |W |
52 | ETHAN GUO |2.5 |W 30|D 22|L 19|D 48|L 29|D 35|L 34|
MI | 14918803 / R: 935 >1092 |N:4 |B |W |B |W |B |W |B |
53 | JOSE C YBARRA |2.0 |H |L 25|H |L 44|U |W 57|U |
MI | 12578849 / R: 1393 >1359 | | |B | |W | |W | |
54 | LARRY HODGE |2.0 |L 14|L 39|L 61|B |L 15|L 59|W 64|
MI | 12836773 / R: 1270 >1200 | |B |B |W | |W |B |W |
55 | ALEX KONG |2.0 |L 62|D 31|L 10|L 30|B |D 45|L 43|
MI | 15412571 / R: 1186 >1163 | |W |B |W |B | |W |B |
56 | MARISA RICCI |2.0 |H |L 11|L 35|W 45|H |L 40|L 42|
MI | 14679887 / R: 1153 >1140 | | |B |W |W | |B |W |
57 | MICHAEL LU |2.0 |L 7|L 36|W 42|L 51|L 35|L 53|B |
MI | 15113330 / R: 1092 >1079 | |B |W |W |B |W |B | |
58 | VIRAJ MOHILE |2.0 |W 31|L 2|L 41|L 23|L 49|B |L 45|
MI | 14700365 / R: 917 > 941 | |W |B |W |B |W | |B |
59 | SEAN M MC CORMICK |2.0 |L 41|B |L 9|L 40|L 43|W 54|L 44|
MI | 12841036 / R: 853 > 878 | |W | |B |B |W |W |B |
60 | JULIA SHEN |1.5 |L 33|L 34|D 45|D 42|L 24|H |U |
MI | 14579262 / R: 967 > 984 | |W |B |B |W |B | | |
61 | JEZZEL FARKAS |1.5 |L 32|L 3|W 54|L 47|D 42|L 30|L 37|
ON | 15771592 / R: 955P11> 979P18 | |B |W |B |W |B |W |B |
62 | ASHWIN BALAJI |1.0 |W 55|U |U |U |U |U |U |
MI | 15219542 / R: 1530 >1535 | |B | | | | | | |
63 | THOMAS JOSEPH HOSMER |1.0 |L 2|L 48|D 49|L 43|L 45|H |U |
MI | 15057092 / R: 1175 >1125 | |W |B |W |B |B | | |
64 | BEN LI |1.0 |L 22|D 30|L 31|D 49|L 46|L 42|L 54|
MI | 15006561 / R: 1163 >1112 | |B |W |W |B |W |B |B |
Now to the hard part, the target is to loop over the tourn_2 and merge every two consecutive rows into one single row. To acheive that approach, I initialized an empty dataframe and store it into a variable called new_table. After that, let the for-loop iterate over the dataframe dimension of 6 X 1, if the row number is odd, then seperate into two parts. Finally push them into the New_table dataframe.
Binded
Pair | Player Name |Total|Round|Round|Round|Round|Round|Round|Round| Num | USCF ID / Rtg (Pre>Post) | Pts | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
1 | GARY HUA |6.0 |W 39|W 21|W 18|W 14|W 7|D 12|D 4| ON | 15445895 / R: 1794 >1817 |N:2 |W |B |W |B |W |B |W |
2 | DAKSHESH DARURI |6.0 |W 63|W 58|L 4|W 17|W 16|W 20|W 7| MI | 14598900 / R: 1553 >1663 |N:2 |B |W |B |W |B |W |B |
3 | ADITYA BAJAJ |6.0 |L 8|W 61|W 25|W 21|W 11|W 13|W 12| MI | 14959604 / R: 1384 >1640 |N:2 |W |B |W |B |W |B |W |
4 | PATRICK H SCHILLING |5.5 |W 23|D 28|W 2|W 26|D 5|W 19|D 1| MI | 12616049 / R: 1716 >1744 |N:2 |W |B |W |B |W |B |B |
5 | HANSHI ZUO |5.5 |W 45|W 37|D 12|D 13|D 4|W 14|W 17| MI | 14601533 / R: 1655 >1690 |N:2 |B |W |B |W |B |W |B | 
## [1] TRUE
Next step, we need to chop this long column into multiple columns, each column represents piece of data. For this purpose, I used built in method within tidyverse library called separate() which takes three arguments which are dataframe, the column that needs to be chopped, new columns names, and finally the separator where the split should be done. For this case, I wanted t split on the “|”.

For the columns names, I put it into a vector and stored it in a variable called “into”

## Warning: Expected 20 pieces. Additional pieces discarded in 65 rows [1, 2,
## 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, ...].
Pair Player Name Total Round Round.1 Round.2 Round.3 Round.4 Round.5 Round.6 State USCF ID / Rtg (Pre>Post) Pts 1 2 3 4 5 6 7
Pair Player Name Total Round Round Round Round Round Round Round Num USCF ID / Rtg (Pre>Post) Pts 1 2 3 4 5 6 7
1 GARY HUA 6.0 W 39 W 21 W 18 W 14 W 7 D 12 D 4 ON 15445895 / R: 1794 >1817 N:2 W B W B W B W
2 DAKSHESH DARURI 6.0 W 63 W 58 L 4 W 17 W 16 W 20 W 7 MI 14598900 / R: 1553 >1663 N:2 B W B W B W B
3 ADITYA BAJAJ 6.0 L 8 W 61 W 25 W 21 W 11 W 13 W 12 MI 14959604 / R: 1384 >1640 N:2 W B W B W B W
4 PATRICK H SCHILLING 5.5 W 23 D 28 W 2 W 26 D 5 W 19 D 1 MI 12616049 / R: 1716 >1744 N:2 W B W B W B B
5 HANSHI ZUO 5.5 W 45 W 37 D 12 D 13 D 4 W 14 W 17 MI 14601533 / R: 1655 >1690 N:2 B W B W B W B
6 HANSEN SONG 5.0 W 34 D 29 L 11 W 35 D 10 W 27 W 21 OH 15055204 / R: 1686 >1687 N:3 W B W B B W B
7 GARY DEE SWATHELL 5.0 W 57 W 46 W 13 W 11 L 1 W 9 L 2 MI 11146376 / R: 1649 >1673 N:3 W B W B B W W
8 EZEKIEL HOUGHTON 5.0 W 3 W 32 L 14 L 9 W 47 W 28 W 19 MI 15142253 / R: 1641P17>1657P24 N:3 B W B W B W W
9 STEFANO LEE 5.0 W 25 L 18 W 59 W 8 W 26 L 7 W 20 ON 14954524 / R: 1411 >1564 N:2 W B W B W B B
10 ANVIT RAO 5.0 D 16 L 19 W 55 W 31 D 6 W 25 W 18 MI 14150362 / R: 1365 >1544 N:3 W W B B W B W
11 CAMERON WILLIAM MC LEMAN 4.5 D 38 W 56 W 6 L 7 L 3 W 34 W 26 MI 12581589 / R: 1712 >1696 N:3 B W B W B W B
12 KENNETH J TACK 4.5 W 42 W 33 D 5 W 38 H D 1 L 3 MI 12681257 / R: 1663 >1670 N:3 W B W B W B
13 TORRANCE HENRY JR 4.5 W 36 W 27 L 7 D 5 W 33 L 3 W 32 MI 15082995 / R: 1666 >1662 N:3 B W B B W W B
14 BRADLEY SHAW 4.5 W 54 W 44 W 8 L 1 D 27 L 5 W 31 MI 10131499 / R: 1610 >1618 N:3 W B W W B B W
15 ZACHARY JAMES HOUGHTON 4.5 D 19 L 16 W 30 L 22 W 54 W 33 W 38 MI 15619130 / R: 1220P13>1416P20 N:3 B B W W B B W
16 MIKE NIKITIN 4.0 D 10 W 15 H W 39 L 2 W 36 U MI 10295068 / R: 1604 >1613 N:3 B W B W B
17 RONALD GRZEGORCZYK 4.0 W 48 W 41 L 26 L 2 W 23 W 22 L 5 MI 10297702 / R: 1629 >1610 N:3 W B W B W B W
18 DAVID SUNDEEN 4.0 W 47 W 9 L 1 W 32 L 19 W 38 L 10 MI 11342094 / R: 1600 >1600 N:3 B W B W B W B
19 DIPANKAR ROY 4.0 D 15 W 10 W 52 D 28 W 18 L 4 L 8 MI 14862333 / R: 1564 >1570 N:3 W B W B W W B
20 JASON ZHENG 4.0 L 40 W 49 W 23 W 41 W 28 L 2 L 9 MI 14529060 / R: 1595 >1569 N:4 W B W B W B W
21 DINH DANG BUI 4.0 W 43 L 1 W 47 L 3 W 40 W 39 L 6 ON 15495066 / R: 1563P22>1562 N:3 B W B W W B W
22 EUGENE L MCCLURE 4.0 W 64 D 52 L 28 W 15 H L 17 W 40 MI 12405534 / R: 1555 >1529 N:4 W B W B W B
23 ALAN BUI 4.0 L 4 W 43 L 20 W 58 L 17 W 37 W 46 ON 15030142 / R: 1363 >1371 B W B W B W B
24 MICHAEL R ALDRICH 4.0 L 28 L 47 W 43 L 25 W 60 W 44 W 39 MI 13469010 / R: 1229 >1300 N:4 B W B B W W B
25 LOREN SCHWIEBERT 3.5 L 9 W 53 L 3 W 24 D 34 L 10 W 47 MI 12486656 / R: 1745 >1681 N:4 B W B W B W B
26 MAX ZHU 3.5 W 49 W 40 W 17 L 4 L 9 D 32 L 11 ON 15131520 / R: 1579 >1564 N:4 B W B W B W W
27 GAURAV GIDWANI 3.5 W 51 L 13 W 46 W 37 D 14 L 6 U MI 14476567 / R: 1552 >1539 N:4 W B W B W B
28 SOFIA ADINA STANESCUBELLU 3.5 W 24 D 4 W 22 D 19 L 20 L 8 D 36 MI 14882954 / R: 1507 >1513 N:3 W W B W B B W
29 CHIEDOZIE OKORIE 3.5 W 50 D 6 L 38 L 34 W 52 W 48 U MI 15323285 / R: 1602P6 >1508P12 N:4 B W B W W B
30 GEORGE AVERY JONES 3.5 L 52 D 64 L 15 W 55 L 31 W 61 W 50 ON 12577178 / R: 1522 >1444 W B B W W B B
31 RISHI SHETTY 3.5 L 58 D 55 W 64 L 10 W 30 W 50 L 14 MI 15131618 / R: 1494 >1444 B W B W B W B
32 JOSHUA PHILIP MATHEWS 3.5 W 61 L 8 W 44 L 18 W 51 D 26 L 13 ON 14073750 / R: 1441 >1433 N:4 W B W B W B W
33 JADE GE 3.5 W 60 L 12 W 50 D 36 L 13 L 15 W 51 MI 14691842 / R: 1449 >1421 B W B W B W B
34 MICHAEL JEFFERY THOMAS 3.5 L 6 W 60 L 37 W 29 D 25 L 11 W 52 MI 15051807 / R: 1399 >1400 B W B B W B W
35 JOSHUA DAVID LEE 3.5 L 46 L 38 W 56 L 6 W 57 D 52 W 48 MI 14601397 / R: 1438 >1392 W W B W B B W
36 SIDDHARTH JHA 3.5 L 13 W 57 W 51 D 33 H L 16 D 28 MI 14773163 / R: 1355 >1367 N:4 W B W B W B
37 AMIYATOSH PWNANANDAM 3.5 B L 5 W 34 L 27 H L 23 W 61 MI 15489571 / R: 980P12>1077P17 B W W B W
38 BRIAN LIU 3.0 D 11 W 35 W 29 L 12 H L 18 L 15 MI 15108523 / R: 1423 >1439 N:4 W B W W B B
39 JOEL R HENDON 3.0 L 1 W 54 W 40 L 16 W 44 L 21 L 24 MI 12923035 / R: 1436P23>1413 N:4 B W B W B W W
40 FOREST ZHANG 3.0 W 20 L 26 L 39 W 59 L 21 W 56 L 22 MI 14892710 / R: 1348 >1346 B B W W B W W
41 KYLE WILLIAM MURPHY 3.0 W 59 L 17 W 58 L 20 X U U MI 15761443 / R: 1403P5 >1341P9 B W B W
42 JARED GE 3.0 L 12 L 50 L 57 D 60 D 61 W 64 W 56 MI 14462326 / R: 1332 >1256 B W B B W W B
43 ROBERT GLEN VASEY 3.0 L 21 L 23 L 24 W 63 W 59 L 46 W 55 MI 14101068 / R: 1283 >1244 W B W W B B W
44 JUSTIN D SCHILLING 3.0 B L 14 L 32 W 53 L 39 L 24 W 59 MI 15323504 / R: 1199 >1199 W B B W B W
45 DEREK YAN 3.0 L 5 L 51 D 60 L 56 W 63 D 55 W 58 MI 15372807 / R: 1242 >1191 W B W B W B W
46 JACOB ALEXANDER LAVALLEY 3.0 W 35 L 7 L 27 L 50 W 64 W 43 L 23 MI 15490981 / R: 377P3 >1076P10 B W B W B W W
47 ERIC WRIGHT 2.5 L 18 W 24 L 21 W 61 L 8 D 51 L 25 MI 12533115 / R: 1362 >1341 W B W B W B W
48 DANIEL KHAIN 2.5 L 17 W 63 H D 52 H L 29 L 35 MI 14369165 / R: 1382 >1335 B W B W B
49 MICHAEL J MARTIN 2.5 L 26 L 20 D 63 D 64 W 58 H U MI 12531685 / R: 1291P12>1259P17 W W B W B
50 SHIVAM JHA 2.5 L 29 W 42 L 33 W 46 H L 31 L 30 MI 14773178 / R: 1056 >1111 W B W B B W
51 TEJAS AYYAGARI 2.5 L 27 W 45 L 36 W 57 L 32 D 47 L 33 MI 15205474 / R: 1011 >1097 B W B W B W W
52 ETHAN GUO 2.5 W 30 D 22 L 19 D 48 L 29 D 35 L 34 MI 14918803 / R: 935 >1092 N:4 B W B W B W B
53 JOSE C YBARRA 2.0 H L 25 H L 44 U W 57 U MI 12578849 / R: 1393 >1359 B W W
54 LARRY HODGE 2.0 L 14 L 39 L 61 B L 15 L 59 W 64 MI 12836773 / R: 1270 >1200 B B W W B W
55 ALEX KONG 2.0 L 62 D 31 L 10 L 30 B D 45 L 43 MI 15412571 / R: 1186 >1163 W B W B W B
56 MARISA RICCI 2.0 H L 11 L 35 W 45 H L 40 L 42 MI 14679887 / R: 1153 >1140 B W W B W
57 MICHAEL LU 2.0 L 7 L 36 W 42 L 51 L 35 L 53 B MI 15113330 / R: 1092 >1079 B W W B W B
58 VIRAJ MOHILE 2.0 W 31 L 2 L 41 L 23 L 49 B L 45 MI 14700365 / R: 917 > 941 W B W B W B
59 SEAN M MC CORMICK 2.0 L 41 B L 9 L 40 L 43 W 54 L 44 MI 12841036 / R: 853 > 878 W B B W W B
60 JULIA SHEN 1.5 L 33 L 34 D 45 D 42 L 24 H U MI 14579262 / R: 967 > 984 W B B W B
61 JEZZEL FARKAS 1.5 L 32 L 3 W 54 L 47 D 42 L 30 L 37 ON 15771592 / R: 955P11> 979P18 B W B W B W B
62 ASHWIN BALAJI 1.0 W 55 U U U U U U MI 15219542 / R: 1530 >1535 B
63 THOMAS JOSEPH HOSMER 1.0 L 2 L 48 D 49 L 43 L 45 H U MI 15057092 / R: 1175 >1125 W B W B B
64 BEN LI 1.0 L 22 D 30 L 31 D 49 L 46 L 42 L 54 MI 15006561 / R: 1163 >1112 B W W B W B B 
##  [1] "Pair"                     "Player Name"             
##  [3] "Total"                    "Round"                   
##  [5] "Round.1"                  "Round.2"                 
##  [7] "Round.3"                  "Round.4"                 
##  [9] "Round.5"                  "Round.6"                 
## [11] "State"                    "USCF ID / Rtg (Pre>Post)"
## [13] "Pts"                      "1"                       
## [15] "2"                        "3"                       
## [17] "4"                        "5"                       
## [19] "6"                        "7"
Now the data has been successfully splitted into 21 columns. However, the first row is a duplicated header. In this case, this row can be deleted.
Pair Player Name Total Round Round.1 Round.2 Round.3 Round.4 Round.5 Round.6 State USCF ID / Rtg (Pre>Post) Pts 1 2 3 4 5 6 7
2 1 GARY HUA 6.0 W 39 W 21 W 18 W 14 W 7 D 12 D 4 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 MI 14598900 / R: 1553 >1663 N:2 B W B W B W B
4 3 ADITYA BAJAJ 6.0 L 8 W 61 W 25 W 21 W 11 W 13 W 12 MI 14959604 / R: 1384 >1640 N:2 W B W B W B W
5 4 PATRICK H SCHILLING 5.5 W 23 D 28 W 2 W 26 D 5 W 19 D 1 MI 12616049 / R: 1716 >1744 N:2 W B W B W B B
6 5 HANSHI ZUO 5.5 W 45 W 37 D 12 D 13 D 4 W 14 W 17 MI 14601533 / R: 1655 >1690 N:2 B W B W B W B
7 6 HANSEN SONG 5.0 W 34 D 29 L 11 W 35 D 10 W 27 W 21 OH 15055204 / R: 1686 >1687 N:3 W B W B B W B
8 7 GARY DEE SWATHELL 5.0 W 57 W 46 W 13 W 11 L 1 W 9 L 2 MI 11146376 / R: 1649 >1673 N:3 W B W B B W W
9 8 EZEKIEL HOUGHTON 5.0 W 3 W 32 L 14 L 9 W 47 W 28 W 19 MI 15142253 / R: 1641P17>1657P24 N:3 B W B W B W W
10 9 STEFANO LEE 5.0 W 25 L 18 W 59 W 8 W 26 L 7 W 20 ON 14954524 / R: 1411 >1564 N:2 W B W B W B B
11 10 ANVIT RAO 5.0 D 16 L 19 W 55 W 31 D 6 W 25 W 18 MI 14150362 / R: 1365 >1544 N:3 W W B B W B W
12 11 CAMERON WILLIAM MC LEMAN 4.5 D 38 W 56 W 6 L 7 L 3 W 34 W 26 MI 12581589 / R: 1712 >1696 N:3 B W B W B W B
13 12 KENNETH J TACK 4.5 W 42 W 33 D 5 W 38 H D 1 L 3 MI 12681257 / R: 1663 >1670 N:3 W B W B W B
14 13 TORRANCE HENRY JR 4.5 W 36 W 27 L 7 D 5 W 33 L 3 W 32 MI 15082995 / R: 1666 >1662 N:3 B W B B W W B
15 14 BRADLEY SHAW 4.5 W 54 W 44 W 8 L 1 D 27 L 5 W 31 MI 10131499 / R: 1610 >1618 N:3 W B W W B B W
16 15 ZACHARY JAMES HOUGHTON 4.5 D 19 L 16 W 30 L 22 W 54 W 33 W 38 MI 15619130 / R: 1220P13>1416P20 N:3 B B W W B B W
17 16 MIKE NIKITIN 4.0 D 10 W 15 H W 39 L 2 W 36 U MI 10295068 / R: 1604 >1613 N:3 B W B W B
18 17 RONALD GRZEGORCZYK 4.0 W 48 W 41 L 26 L 2 W 23 W 22 L 5 MI 10297702 / R: 1629 >1610 N:3 W B W B W B W
19 18 DAVID SUNDEEN 4.0 W 47 W 9 L 1 W 32 L 19 W 38 L 10 MI 11342094 / R: 1600 >1600 N:3 B W B W B W B
20 19 DIPANKAR ROY 4.0 D 15 W 10 W 52 D 28 W 18 L 4 L 8 MI 14862333 / R: 1564 >1570 N:3 W B W B W W B
21 20 JASON ZHENG 4.0 L 40 W 49 W 23 W 41 W 28 L 2 L 9 MI 14529060 / R: 1595 >1569 N:4 W B W B W B W
22 21 DINH DANG BUI 4.0 W 43 L 1 W 47 L 3 W 40 W 39 L 6 ON 15495066 / R: 1563P22>1562 N:3 B W B W W B W
23 22 EUGENE L MCCLURE 4.0 W 64 D 52 L 28 W 15 H L 17 W 40 MI 12405534 / R: 1555 >1529 N:4 W B W B W B
24 23 ALAN BUI 4.0 L 4 W 43 L 20 W 58 L 17 W 37 W 46 ON 15030142 / R: 1363 >1371 B W B W B W B
25 24 MICHAEL R ALDRICH 4.0 L 28 L 47 W 43 L 25 W 60 W 44 W 39 MI 13469010 / R: 1229 >1300 N:4 B W B B W W B
26 25 LOREN SCHWIEBERT 3.5 L 9 W 53 L 3 W 24 D 34 L 10 W 47 MI 12486656 / R: 1745 >1681 N:4 B W B W B W B
27 26 MAX ZHU 3.5 W 49 W 40 W 17 L 4 L 9 D 32 L 11 ON 15131520 / R: 1579 >1564 N:4 B W B W B W W
28 27 GAURAV GIDWANI 3.5 W 51 L 13 W 46 W 37 D 14 L 6 U MI 14476567 / R: 1552 >1539 N:4 W B W B W B
29 28 SOFIA ADINA STANESCUBELLU 3.5 W 24 D 4 W 22 D 19 L 20 L 8 D 36 MI 14882954 / R: 1507 >1513 N:3 W W B W B B W
30 29 CHIEDOZIE OKORIE 3.5 W 50 D 6 L 38 L 34 W 52 W 48 U MI 15323285 / R: 1602P6 >1508P12 N:4 B W B W W B
31 30 GEORGE AVERY JONES 3.5 L 52 D 64 L 15 W 55 L 31 W 61 W 50 ON 12577178 / R: 1522 >1444 W B B W W B B
32 31 RISHI SHETTY 3.5 L 58 D 55 W 64 L 10 W 30 W 50 L 14 MI 15131618 / R: 1494 >1444 B W B W B W B
33 32 JOSHUA PHILIP MATHEWS 3.5 W 61 L 8 W 44 L 18 W 51 D 26 L 13 ON 14073750 / R: 1441 >1433 N:4 W B W B W B W
34 33 JADE GE 3.5 W 60 L 12 W 50 D 36 L 13 L 15 W 51 MI 14691842 / R: 1449 >1421 B W B W B W B
35 34 MICHAEL JEFFERY THOMAS 3.5 L 6 W 60 L 37 W 29 D 25 L 11 W 52 MI 15051807 / R: 1399 >1400 B W B B W B W
36 35 JOSHUA DAVID LEE 3.5 L 46 L 38 W 56 L 6 W 57 D 52 W 48 MI 14601397 / R: 1438 >1392 W W B W B B W
37 36 SIDDHARTH JHA 3.5 L 13 W 57 W 51 D 33 H L 16 D 28 MI 14773163 / R: 1355 >1367 N:4 W B W B W B
38 37 AMIYATOSH PWNANANDAM 3.5 B L 5 W 34 L 27 H L 23 W 61 MI 15489571 / R: 980P12>1077P17 B W W B W
39 38 BRIAN LIU 3.0 D 11 W 35 W 29 L 12 H L 18 L 15 MI 15108523 / R: 1423 >1439 N:4 W B W W B B
40 39 JOEL R HENDON 3.0 L 1 W 54 W 40 L 16 W 44 L 21 L 24 MI 12923035 / R: 1436P23>1413 N:4 B W B W B W W
41 40 FOREST ZHANG 3.0 W 20 L 26 L 39 W 59 L 21 W 56 L 22 MI 14892710 / R: 1348 >1346 B B W W B W W
42 41 KYLE WILLIAM MURPHY 3.0 W 59 L 17 W 58 L 20 X U U MI 15761443 / R: 1403P5 >1341P9 B W B W
43 42 JARED GE 3.0 L 12 L 50 L 57 D 60 D 61 W 64 W 56 MI 14462326 / R: 1332 >1256 B W B B W W B
44 43 ROBERT GLEN VASEY 3.0 L 21 L 23 L 24 W 63 W 59 L 46 W 55 MI 14101068 / R: 1283 >1244 W B W W B B W
45 44 JUSTIN D SCHILLING 3.0 B L 14 L 32 W 53 L 39 L 24 W 59 MI 15323504 / R: 1199 >1199 W B B W B W
46 45 DEREK YAN 3.0 L 5 L 51 D 60 L 56 W 63 D 55 W 58 MI 15372807 / R: 1242 >1191 W B W B W B W
47 46 JACOB ALEXANDER LAVALLEY 3.0 W 35 L 7 L 27 L 50 W 64 W 43 L 23 MI 15490981 / R: 377P3 >1076P10 B W B W B W W
48 47 ERIC WRIGHT 2.5 L 18 W 24 L 21 W 61 L 8 D 51 L 25 MI 12533115 / R: 1362 >1341 W B W B W B W
49 48 DANIEL KHAIN 2.5 L 17 W 63 H D 52 H L 29 L 35 MI 14369165 / R: 1382 >1335 B W B W B
50 49 MICHAEL J MARTIN 2.5 L 26 L 20 D 63 D 64 W 58 H U MI 12531685 / R: 1291P12>1259P17 W W B W B
51 50 SHIVAM JHA 2.5 L 29 W 42 L 33 W 46 H L 31 L 30 MI 14773178 / R: 1056 >1111 W B W B B W
52 51 TEJAS AYYAGARI 2.5 L 27 W 45 L 36 W 57 L 32 D 47 L 33 MI 15205474 / R: 1011 >1097 B W B W B W W
53 52 ETHAN GUO 2.5 W 30 D 22 L 19 D 48 L 29 D 35 L 34 MI 14918803 / R: 935 >1092 N:4 B W B W B W B
54 53 JOSE C YBARRA 2.0 H L 25 H L 44 U W 57 U MI 12578849 / R: 1393 >1359 B W W
55 54 LARRY HODGE 2.0 L 14 L 39 L 61 B L 15 L 59 W 64 MI 12836773 / R: 1270 >1200 B B W W B W
56 55 ALEX KONG 2.0 L 62 D 31 L 10 L 30 B D 45 L 43 MI 15412571 / R: 1186 >1163 W B W B W B
57 56 MARISA RICCI 2.0 H L 11 L 35 W 45 H L 40 L 42 MI 14679887 / R: 1153 >1140 B W W B W
58 57 MICHAEL LU 2.0 L 7 L 36 W 42 L 51 L 35 L 53 B MI 15113330 / R: 1092 >1079 B W W B W B
59 58 VIRAJ MOHILE 2.0 W 31 L 2 L 41 L 23 L 49 B L 45 MI 14700365 / R: 917 > 941 W B W B W B
60 59 SEAN M MC CORMICK 2.0 L 41 B L 9 L 40 L 43 W 54 L 44 MI 12841036 / R: 853 > 878 W B B W W B
61 60 JULIA SHEN 1.5 L 33 L 34 D 45 D 42 L 24 H U MI 14579262 / R: 967 > 984 W B B W B
62 61 JEZZEL FARKAS 1.5 L 32 L 3 W 54 L 47 D 42 L 30 L 37 ON 15771592 / R: 955P11> 979P18 B W B W B W B
63 62 ASHWIN BALAJI 1.0 W 55 U U U U U U MI 15219542 / R: 1530 >1535 B
64 63 THOMAS JOSEPH HOSMER 1.0 L 2 L 48 D 49 L 43 L 45 H U MI 15057092 / R: 1175 >1125 W B W B B
65 64 BEN LI 1.0 L 22 D 30 L 31 D 49 L 46 L 42 L 54 MI 15006561 / R: 1163 >1112 B W W B W B B 
##  [1] "Pair"                     "Player Name"             
##  [3] "Total"                    "Round"                   
##  [5] "Round.1"                  "Round.2"                 
##  [7] "Round.3"                  "Round.4"                 
##  [9] "Round.5"                  "Round.6"                 
## [11] "State"                    "USCF ID / Rtg (Pre>Post)"
## [13] "Pts"                      "1"                       
## [15] "2"                        "3"                       
## [17] "4"                        "5"                       
## [19] "6"                        "7"
Now, we need to split the clean_df again at column “USCF ID / Rtg (Pre>Post)” to get pre and post rating columns to get the average as required at the beginning of the aasignment. To do so, I used Regex patterns to remove punctuations and generate three different columns for USCF ID, Pre, and Post rating using tidyverse method separate().
## [1] "col1"
## Warning in stri_replace_all_regex(string, pattern,
## fix_replacement(replacement), : argument is not an atomic vector; coercing
## [1] "as.character.str_remove_all.rtg_extract......punct...c...."
USCF ID Pre Post
15445895 1794 1817
14598900 1553 1663
14959604 1384 1640
12616049 1716 1744
14601533 1655 1690
15055204 1686 1687
11146376 1649 1673
15142253 1641 1657
14954524 1411 1564
14150362 1365 1544
12581589 1712 1696
12681257 1663 1670
15082995 1666 1662
10131499 1610 1618
15619130 1220 1416
10295068 1604 1613
10297702 1629 1610
11342094 1600 1600
14862333 1564 1570
14529060 1595 1569
15495066 1563 1562
12405534 1555 1529
15030142 1363 1371
13469010 1229 1300
12486656 1745 1681
15131520 1579 1564
14476567 1552 1539
14882954 1507 1513
15323285 1602 1508
12577178 1522 1444
15131618 1494 1444
14073750 1441 1433
14691842 1449 1421
15051807 1399 1400
14601397 1438 1392
14773163 1355 1367
15489571 980 1077
15108523 1423 1439
12923035 1436 1413
14892710 1348 1346
15761443 1403 1341
14462326 1332 1256
14101068 1283 1244
15323504 1199 1199
15372807 1242 1191
15490981 377 1076
12533115 1362 1341
14369165 1382 1335
12531685 1291 1259
14773178 1056 1111
15205474 1011 1097
14918803 935 1092
12578849 1393 1359
12836773 1270 1200
15412571 1186 1163
14679887 1153 1140
15113330 1092 1079
14700365 917 941
12841036 853 878
14579262 967 984
15771592 955 979
15219542 1530 1535
15057092 1175 1125
15006561 1163 1112
Now we need to merge those three columns into the main dataframe clean_df. Before doing the merge, we need to drop the “USCF ID / Rtg (Pre>Post)” column from clean_df then merge.
Pair Player Name Total Round Round.1 Round.2 Round.3 Round.4 Round.5 Round.6 State USCF ID / Rtg (Pre>Post) Pts 1 2 3 4 5 6 7
2 1 GARY HUA 6.0 W 39 W 21 W 18 W 14 W 7 D 12 D 4 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 MI 14598900 / R: 1553 >1663 N:2 B W B W B W B
4 3 ADITYA BAJAJ 6.0 L 8 W 61 W 25 W 21 W 11 W 13 W 12 MI 14959604 / R: 1384 >1640 N:2 W B W B W B W
5 4 PATRICK H SCHILLING 5.5 W 23 D 28 W 2 W 26 D 5 W 19 D 1 MI 12616049 / R: 1716 >1744 N:2 W B W B W B B
6 5 HANSHI ZUO 5.5 W 45 W 37 D 12 D 13 D 4 W 14 W 17 MI 14601533 / R: 1655 >1690 N:2 B W B W B W B
7 6 HANSEN SONG 5.0 W 34 D 29 L 11 W 35 D 10 W 27 W 21 OH 15055204 / R: 1686 >1687 N:3 W B W B B W B
8 7 GARY DEE SWATHELL 5.0 W 57 W 46 W 13 W 11 L 1 W 9 L 2 MI 11146376 / R: 1649 >1673 N:3 W B W B B W W
9 8 EZEKIEL HOUGHTON 5.0 W 3 W 32 L 14 L 9 W 47 W 28 W 19 MI 15142253 / R: 1641P17>1657P24 N:3 B W B W B W W
10 9 STEFANO LEE 5.0 W 25 L 18 W 59 W 8 W 26 L 7 W 20 ON 14954524 / R: 1411 >1564 N:2 W B W B W B B
11 10 ANVIT RAO 5.0 D 16 L 19 W 55 W 31 D 6 W 25 W 18 MI 14150362 / R: 1365 >1544 N:3 W W B B W B W
12 11 CAMERON WILLIAM MC LEMAN 4.5 D 38 W 56 W 6 L 7 L 3 W 34 W 26 MI 12581589 / R: 1712 >1696 N:3 B W B W B W B
13 12 KENNETH J TACK 4.5 W 42 W 33 D 5 W 38 H D 1 L 3 MI 12681257 / R: 1663 >1670 N:3 W B W B W B
14 13 TORRANCE HENRY JR 4.5 W 36 W 27 L 7 D 5 W 33 L 3 W 32 MI 15082995 / R: 1666 >1662 N:3 B W B B W W B
15 14 BRADLEY SHAW 4.5 W 54 W 44 W 8 L 1 D 27 L 5 W 31 MI 10131499 / R: 1610 >1618 N:3 W B W W B B W
16 15 ZACHARY JAMES HOUGHTON 4.5 D 19 L 16 W 30 L 22 W 54 W 33 W 38 MI 15619130 / R: 1220P13>1416P20 N:3 B B W W B B W
17 16 MIKE NIKITIN 4.0 D 10 W 15 H W 39 L 2 W 36 U MI 10295068 / R: 1604 >1613 N:3 B W B W B
18 17 RONALD GRZEGORCZYK 4.0 W 48 W 41 L 26 L 2 W 23 W 22 L 5 MI 10297702 / R: 1629 >1610 N:3 W B W B W B W
19 18 DAVID SUNDEEN 4.0 W 47 W 9 L 1 W 32 L 19 W 38 L 10 MI 11342094 / R: 1600 >1600 N:3 B W B W B W B
20 19 DIPANKAR ROY 4.0 D 15 W 10 W 52 D 28 W 18 L 4 L 8 MI 14862333 / R: 1564 >1570 N:3 W B W B W W B
21 20 JASON ZHENG 4.0 L 40 W 49 W 23 W 41 W 28 L 2 L 9 MI 14529060 / R: 1595 >1569 N:4 W B W B W B W
22 21 DINH DANG BUI 4.0 W 43 L 1 W 47 L 3 W 40 W 39 L 6 ON 15495066 / R: 1563P22>1562 N:3 B W B W W B W
23 22 EUGENE L MCCLURE 4.0 W 64 D 52 L 28 W 15 H L 17 W 40 MI 12405534 / R: 1555 >1529 N:4 W B W B W B
24 23 ALAN BUI 4.0 L 4 W 43 L 20 W 58 L 17 W 37 W 46 ON 15030142 / R: 1363 >1371 B W B W B W B
25 24 MICHAEL R ALDRICH 4.0 L 28 L 47 W 43 L 25 W 60 W 44 W 39 MI 13469010 / R: 1229 >1300 N:4 B W B B W W B
26 25 LOREN SCHWIEBERT 3.5 L 9 W 53 L 3 W 24 D 34 L 10 W 47 MI 12486656 / R: 1745 >1681 N:4 B W B W B W B
27 26 MAX ZHU 3.5 W 49 W 40 W 17 L 4 L 9 D 32 L 11 ON 15131520 / R: 1579 >1564 N:4 B W B W B W W
28 27 GAURAV GIDWANI 3.5 W 51 L 13 W 46 W 37 D 14 L 6 U MI 14476567 / R: 1552 >1539 N:4 W B W B W B
29 28 SOFIA ADINA STANESCUBELLU 3.5 W 24 D 4 W 22 D 19 L 20 L 8 D 36 MI 14882954 / R: 1507 >1513 N:3 W W B W B B W
30 29 CHIEDOZIE OKORIE 3.5 W 50 D 6 L 38 L 34 W 52 W 48 U MI 15323285 / R: 1602P6 >1508P12 N:4 B W B W W B
31 30 GEORGE AVERY JONES 3.5 L 52 D 64 L 15 W 55 L 31 W 61 W 50 ON 12577178 / R: 1522 >1444 W B B W W B B
32 31 RISHI SHETTY 3.5 L 58 D 55 W 64 L 10 W 30 W 50 L 14 MI 15131618 / R: 1494 >1444 B W B W B W B
33 32 JOSHUA PHILIP MATHEWS 3.5 W 61 L 8 W 44 L 18 W 51 D 26 L 13 ON 14073750 / R: 1441 >1433 N:4 W B W B W B W
34 33 JADE GE 3.5 W 60 L 12 W 50 D 36 L 13 L 15 W 51 MI 14691842 / R: 1449 >1421 B W B W B W B
35 34 MICHAEL JEFFERY THOMAS 3.5 L 6 W 60 L 37 W 29 D 25 L 11 W 52 MI 15051807 / R: 1399 >1400 B W B B W B W
36 35 JOSHUA DAVID LEE 3.5 L 46 L 38 W 56 L 6 W 57 D 52 W 48 MI 14601397 / R: 1438 >1392 W W B W B B W
37 36 SIDDHARTH JHA 3.5 L 13 W 57 W 51 D 33 H L 16 D 28 MI 14773163 / R: 1355 >1367 N:4 W B W B W B
38 37 AMIYATOSH PWNANANDAM 3.5 B L 5 W 34 L 27 H L 23 W 61 MI 15489571 / R: 980P12>1077P17 B W W B W
39 38 BRIAN LIU 3.0 D 11 W 35 W 29 L 12 H L 18 L 15 MI 15108523 / R: 1423 >1439 N:4 W B W W B B
40 39 JOEL R HENDON 3.0 L 1 W 54 W 40 L 16 W 44 L 21 L 24 MI 12923035 / R: 1436P23>1413 N:4 B W B W B W W
41 40 FOREST ZHANG 3.0 W 20 L 26 L 39 W 59 L 21 W 56 L 22 MI 14892710 / R: 1348 >1346 B B W W B W W
42 41 KYLE WILLIAM MURPHY 3.0 W 59 L 17 W 58 L 20 X U U MI 15761443 / R: 1403P5 >1341P9 B W B W
43 42 JARED GE 3.0 L 12 L 50 L 57 D 60 D 61 W 64 W 56 MI 14462326 / R: 1332 >1256 B W B B W W B
44 43 ROBERT GLEN VASEY 3.0 L 21 L 23 L 24 W 63 W 59 L 46 W 55 MI 14101068 / R: 1283 >1244 W B W W B B W
45 44 JUSTIN D SCHILLING 3.0 B L 14 L 32 W 53 L 39 L 24 W 59 MI 15323504 / R: 1199 >1199 W B B W B W
46 45 DEREK YAN 3.0 L 5 L 51 D 60 L 56 W 63 D 55 W 58 MI 15372807 / R: 1242 >1191 W B W B W B W
47 46 JACOB ALEXANDER LAVALLEY 3.0 W 35 L 7 L 27 L 50 W 64 W 43 L 23 MI 15490981 / R: 377P3 >1076P10 B W B W B W W
48 47 ERIC WRIGHT 2.5 L 18 W 24 L 21 W 61 L 8 D 51 L 25 MI 12533115 / R: 1362 >1341 W B W B W B W
49 48 DANIEL KHAIN 2.5 L 17 W 63 H D 52 H L 29 L 35 MI 14369165 / R: 1382 >1335 B W B W B
50 49 MICHAEL J MARTIN 2.5 L 26 L 20 D 63 D 64 W 58 H U MI 12531685 / R: 1291P12>1259P17 W W B W B
51 50 SHIVAM JHA 2.5 L 29 W 42 L 33 W 46 H L 31 L 30 MI 14773178 / R: 1056 >1111 W B W B B W
52 51 TEJAS AYYAGARI 2.5 L 27 W 45 L 36 W 57 L 32 D 47 L 33 MI 15205474 / R: 1011 >1097 B W B W B W W
53 52 ETHAN GUO 2.5 W 30 D 22 L 19 D 48 L 29 D 35 L 34 MI 14918803 / R: 935 >1092 N:4 B W B W B W B
54 53 JOSE C YBARRA 2.0 H L 25 H L 44 U W 57 U MI 12578849 / R: 1393 >1359 B W W
55 54 LARRY HODGE 2.0 L 14 L 39 L 61 B L 15 L 59 W 64 MI 12836773 / R: 1270 >1200 B B W W B W
56 55 ALEX KONG 2.0 L 62 D 31 L 10 L 30 B D 45 L 43 MI 15412571 / R: 1186 >1163 W B W B W B
57 56 MARISA RICCI 2.0 H L 11 L 35 W 45 H L 40 L 42 MI 14679887 / R: 1153 >1140 B W W B W
58 57 MICHAEL LU 2.0 L 7 L 36 W 42 L 51 L 35 L 53 B MI 15113330 / R: 1092 >1079 B W W B W B
59 58 VIRAJ MOHILE 2.0 W 31 L 2 L 41 L 23 L 49 B L 45 MI 14700365 / R: 917 > 941 W B W B W B
60 59 SEAN M MC CORMICK 2.0 L 41 B L 9 L 40 L 43 W 54 L 44 MI 12841036 / R: 853 > 878 W B B W W B
61 60 JULIA SHEN 1.5 L 33 L 34 D 45 D 42 L 24 H U MI 14579262 / R: 967 > 984 W B B W B
62 61 JEZZEL FARKAS 1.5 L 32 L 3 W 54 L 47 D 42 L 30 L 37 ON 15771592 / R: 955P11> 979P18 B W B W B W B
63 62 ASHWIN BALAJI 1.0 W 55 U U U U U U MI 15219542 / R: 1530 >1535 B
64 63 THOMAS JOSEPH HOSMER 1.0 L 2 L 48 D 49 L 43 L 45 H U MI 15057092 / R: 1175 >1125 W B W B B
65 64 BEN LI 1.0 L 22 D 30 L 31 D 49 L 46 L 42 L 54 MI 15006561 / R: 1163 >1112 B W W B W B B 
##  [1] "Pair"                     "Player Name"             
##  [3] "Total"                    "Round"                   
##  [5] "Round.1"                  "Round.2"                 
##  [7] "Round.3"                  "Round.4"                 
##  [9] "Round.5"                  "Round.6"                 
## [11] "State"                    "USCF ID / Rtg (Pre>Post)"
## [13] "Pts"                      "1"                       
## [15] "2"                        "3"                       
## [17] "4"                        "5"                       
## [19] "6"                        "7"
Pair Player Name Total Round Round.1 Round.2 Round.3 Round.4 Round.5 Round.6 State Pts 1 2 3 4 5 6 7
2 1 GARY HUA 6.0 W 39 W 21 W 18 W 14 W 7 D 12 D 4 ON 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 MI N:2 B W B W B W B
4 3 ADITYA BAJAJ 6.0 L 8 W 61 W 25 W 21 W 11 W 13 W 12 MI N:2 W B W B W B W
5 4 PATRICK H SCHILLING 5.5 W 23 D 28 W 2 W 26 D 5 W 19 D 1 MI N:2 W B W B W B B
6 5 HANSHI ZUO 5.5 W 45 W 37 D 12 D 13 D 4 W 14 W 17 MI N:2 B W B W B W B
7 6 HANSEN SONG 5.0 W 34 D 29 L 11 W 35 D 10 W 27 W 21 OH N:3 W B W B B W B
8 7 GARY DEE SWATHELL 5.0 W 57 W 46 W 13 W 11 L 1 W 9 L 2 MI N:3 W B W B B W W
9 8 EZEKIEL HOUGHTON 5.0 W 3 W 32 L 14 L 9 W 47 W 28 W 19 MI N:3 B W B W B W W
10 9 STEFANO LEE 5.0 W 25 L 18 W 59 W 8 W 26 L 7 W 20 ON N:2 W B W B W B B
11 10 ANVIT RAO 5.0 D 16 L 19 W 55 W 31 D 6 W 25 W 18 MI N:3 W W B B W B W
12 11 CAMERON WILLIAM MC LEMAN 4.5 D 38 W 56 W 6 L 7 L 3 W 34 W 26 MI N:3 B W B W B W B
13 12 KENNETH J TACK 4.5 W 42 W 33 D 5 W 38 H D 1 L 3 MI N:3 W B W B W B
14 13 TORRANCE HENRY JR 4.5 W 36 W 27 L 7 D 5 W 33 L 3 W 32 MI N:3 B W B B W W B
15 14 BRADLEY SHAW 4.5 W 54 W 44 W 8 L 1 D 27 L 5 W 31 MI N:3 W B W W B B W
16 15 ZACHARY JAMES HOUGHTON 4.5 D 19 L 16 W 30 L 22 W 54 W 33 W 38 MI N:3 B B W W B B W
17 16 MIKE NIKITIN 4.0 D 10 W 15 H W 39 L 2 W 36 U MI N:3 B W B W B
18 17 RONALD GRZEGORCZYK 4.0 W 48 W 41 L 26 L 2 W 23 W 22 L 5 MI N:3 W B W B W B W
19 18 DAVID SUNDEEN 4.0 W 47 W 9 L 1 W 32 L 19 W 38 L 10 MI N:3 B W B W B W B
20 19 DIPANKAR ROY 4.0 D 15 W 10 W 52 D 28 W 18 L 4 L 8 MI N:3 W B W B W W B
21 20 JASON ZHENG 4.0 L 40 W 49 W 23 W 41 W 28 L 2 L 9 MI N:4 W B W B W B W
22 21 DINH DANG BUI 4.0 W 43 L 1 W 47 L 3 W 40 W 39 L 6 ON N:3 B W B W W B W
23 22 EUGENE L MCCLURE 4.0 W 64 D 52 L 28 W 15 H L 17 W 40 MI N:4 W B W B W B
24 23 ALAN BUI 4.0 L 4 W 43 L 20 W 58 L 17 W 37 W 46 ON B W B W B W B
25 24 MICHAEL R ALDRICH 4.0 L 28 L 47 W 43 L 25 W 60 W 44 W 39 MI N:4 B W B B W W B
26 25 LOREN SCHWIEBERT 3.5 L 9 W 53 L 3 W 24 D 34 L 10 W 47 MI N:4 B W B W B W B
27 26 MAX ZHU 3.5 W 49 W 40 W 17 L 4 L 9 D 32 L 11 ON N:4 B W B W B W W
28 27 GAURAV GIDWANI 3.5 W 51 L 13 W 46 W 37 D 14 L 6 U MI N:4 W B W B W B
29 28 SOFIA ADINA STANESCUBELLU 3.5 W 24 D 4 W 22 D 19 L 20 L 8 D 36 MI N:3 W W B W B B W
30 29 CHIEDOZIE OKORIE 3.5 W 50 D 6 L 38 L 34 W 52 W 48 U MI N:4 B W B W W B
31 30 GEORGE AVERY JONES 3.5 L 52 D 64 L 15 W 55 L 31 W 61 W 50 ON W B B W W B B
32 31 RISHI SHETTY 3.5 L 58 D 55 W 64 L 10 W 30 W 50 L 14 MI B W B W B W B
33 32 JOSHUA PHILIP MATHEWS 3.5 W 61 L 8 W 44 L 18 W 51 D 26 L 13 ON N:4 W B W B W B W
34 33 JADE GE 3.5 W 60 L 12 W 50 D 36 L 13 L 15 W 51 MI B W B W B W B
35 34 MICHAEL JEFFERY THOMAS 3.5 L 6 W 60 L 37 W 29 D 25 L 11 W 52 MI B W B B W B W
36 35 JOSHUA DAVID LEE 3.5 L 46 L 38 W 56 L 6 W 57 D 52 W 48 MI W W B W B B W
37 36 SIDDHARTH JHA 3.5 L 13 W 57 W 51 D 33 H L 16 D 28 MI N:4 W B W B W B
38 37 AMIYATOSH PWNANANDAM 3.5 B L 5 W 34 L 27 H L 23 W 61 MI B W W B W
39 38 BRIAN LIU 3.0 D 11 W 35 W 29 L 12 H L 18 L 15 MI N:4 W B W W B B
40 39 JOEL R HENDON 3.0 L 1 W 54 W 40 L 16 W 44 L 21 L 24 MI N:4 B W B W B W W
41 40 FOREST ZHANG 3.0 W 20 L 26 L 39 W 59 L 21 W 56 L 22 MI B B W W B W W
42 41 KYLE WILLIAM MURPHY 3.0 W 59 L 17 W 58 L 20 X U U MI B W B W
43 42 JARED GE 3.0 L 12 L 50 L 57 D 60 D 61 W 64 W 56 MI B W B B W W B
44 43 ROBERT GLEN VASEY 3.0 L 21 L 23 L 24 W 63 W 59 L 46 W 55 MI W B W W B B W
45 44 JUSTIN D SCHILLING 3.0 B L 14 L 32 W 53 L 39 L 24 W 59 MI W B B W B W
46 45 DEREK YAN 3.0 L 5 L 51 D 60 L 56 W 63 D 55 W 58 MI W B W B W B W
47 46 JACOB ALEXANDER LAVALLEY 3.0 W 35 L 7 L 27 L 50 W 64 W 43 L 23 MI B W B W B W W
48 47 ERIC WRIGHT 2.5 L 18 W 24 L 21 W 61 L 8 D 51 L 25 MI W B W B W B W
49 48 DANIEL KHAIN 2.5 L 17 W 63 H D 52 H L 29 L 35 MI B W B W B
50 49 MICHAEL J MARTIN 2.5 L 26 L 20 D 63 D 64 W 58 H U MI W W B W B
51 50 SHIVAM JHA 2.5 L 29 W 42 L 33 W 46 H L 31 L 30 MI W B W B B W
52 51 TEJAS AYYAGARI 2.5 L 27 W 45 L 36 W 57 L 32 D 47 L 33 MI B W B W B W W
53 52 ETHAN GUO 2.5 W 30 D 22 L 19 D 48 L 29 D 35 L 34 MI N:4 B W B W B W B
54 53 JOSE C YBARRA 2.0 H L 25 H L 44 U W 57 U MI B W W
55 54 LARRY HODGE 2.0 L 14 L 39 L 61 B L 15 L 59 W 64 MI B B W W B W
56 55 ALEX KONG 2.0 L 62 D 31 L 10 L 30 B D 45 L 43 MI W B W B W B
57 56 MARISA RICCI 2.0 H L 11 L 35 W 45 H L 40 L 42 MI B W W B W
58 57 MICHAEL LU 2.0 L 7 L 36 W 42 L 51 L 35 L 53 B MI B W W B W B
59 58 VIRAJ MOHILE 2.0 W 31 L 2 L 41 L 23 L 49 B L 45 MI W B W B W B
60 59 SEAN M MC CORMICK 2.0 L 41 B L 9 L 40 L 43 W 54 L 44 MI W B B W W B
61 60 JULIA SHEN 1.5 L 33 L 34 D 45 D 42 L 24 H U MI W B B W B
62 61 JEZZEL FARKAS 1.5 L 32 L 3 W 54 L 47 D 42 L 30 L 37 ON B W B W B W B
63 62 ASHWIN BALAJI 1.0 W 55 U U U U U U MI B
64 63 THOMAS JOSEPH HOSMER 1.0 L 2 L 48 D 49 L 43 L 45 H U MI W B W B B
65 64 BEN LI 1.0 L 22 D 30 L 31 D 49 L 46 L 42 L 54 MI B W W B W B B
  • Merge the new three columns into drop_uncleandata
Pair Player Name Total Round Round.1 Round.2 Round.3 Round.4 Round.5 Round.6 State Pts 1 2 3 4 5 6 7 USCF ID Pre Post
2 1 GARY HUA 6.0 W 39 W 21 W 18 W 14 W 7 D 12 D 4 ON N:2 W B W B W B W 15445895 1794 1817
3 2 DAKSHESH DARURI 6.0 W 63 W 58 L 4 W 17 W 16 W 20 W 7 MI N:2 B W B W B W B 14598900 1553 1663
4 3 ADITYA BAJAJ 6.0 L 8 W 61 W 25 W 21 W 11 W 13 W 12 MI N:2 W B W B W B W 14959604 1384 1640
5 4 PATRICK H SCHILLING 5.5 W 23 D 28 W 2 W 26 D 5 W 19 D 1 MI N:2 W B W B W B B 12616049 1716 1744
6 5 HANSHI ZUO 5.5 W 45 W 37 D 12 D 13 D 4 W 14 W 17 MI N:2 B W B W B W B 14601533 1655 1690
7 6 HANSEN SONG 5.0 W 34 D 29 L 11 W 35 D 10 W 27 W 21 OH N:3 W B W B B W B 15055204 1686 1687
8 7 GARY DEE SWATHELL 5.0 W 57 W 46 W 13 W 11 L 1 W 9 L 2 MI N:3 W B W B B W W 11146376 1649 1673
9 8 EZEKIEL HOUGHTON 5.0 W 3 W 32 L 14 L 9 W 47 W 28 W 19 MI N:3 B W B W B W W 15142253 1641 1657
10 9 STEFANO LEE 5.0 W 25 L 18 W 59 W 8 W 26 L 7 W 20 ON N:2 W B W B W B B 14954524 1411 1564
11 10 ANVIT RAO 5.0 D 16 L 19 W 55 W 31 D 6 W 25 W 18 MI N:3 W W B B W B W 14150362 1365 1544
12 11 CAMERON WILLIAM MC LEMAN 4.5 D 38 W 56 W 6 L 7 L 3 W 34 W 26 MI N:3 B W B W B W B 12581589 1712 1696
13 12 KENNETH J TACK 4.5 W 42 W 33 D 5 W 38 H D 1 L 3 MI N:3 W B W B W B 12681257 1663 1670
14 13 TORRANCE HENRY JR 4.5 W 36 W 27 L 7 D 5 W 33 L 3 W 32 MI N:3 B W B B W W B 15082995 1666 1662
15 14 BRADLEY SHAW 4.5 W 54 W 44 W 8 L 1 D 27 L 5 W 31 MI N:3 W B W W B B W 10131499 1610 1618
16 15 ZACHARY JAMES HOUGHTON 4.5 D 19 L 16 W 30 L 22 W 54 W 33 W 38 MI N:3 B B W W B B W 15619130 1220 1416
17 16 MIKE NIKITIN 4.0 D 10 W 15 H W 39 L 2 W 36 U MI N:3 B W B W B 10295068 1604 1613
18 17 RONALD GRZEGORCZYK 4.0 W 48 W 41 L 26 L 2 W 23 W 22 L 5 MI N:3 W B W B W B W 10297702 1629 1610
19 18 DAVID SUNDEEN 4.0 W 47 W 9 L 1 W 32 L 19 W 38 L 10 MI N:3 B W B W B W B 11342094 1600 1600
20 19 DIPANKAR ROY 4.0 D 15 W 10 W 52 D 28 W 18 L 4 L 8 MI N:3 W B W B W W B 14862333 1564 1570
21 20 JASON ZHENG 4.0 L 40 W 49 W 23 W 41 W 28 L 2 L 9 MI N:4 W B W B W B W 14529060 1595 1569
22 21 DINH DANG BUI 4.0 W 43 L 1 W 47 L 3 W 40 W 39 L 6 ON N:3 B W B W W B W 15495066 1563 1562
23 22 EUGENE L MCCLURE 4.0 W 64 D 52 L 28 W 15 H L 17 W 40 MI N:4 W B W B W B 12405534 1555 1529
24 23 ALAN BUI 4.0 L 4 W 43 L 20 W 58 L 17 W 37 W 46 ON B W B W B W B 15030142 1363 1371
25 24 MICHAEL R ALDRICH 4.0 L 28 L 47 W 43 L 25 W 60 W 44 W 39 MI N:4 B W B B W W B 13469010 1229 1300
26 25 LOREN SCHWIEBERT 3.5 L 9 W 53 L 3 W 24 D 34 L 10 W 47 MI N:4 B W B W B W B 12486656 1745 1681
27 26 MAX ZHU 3.5 W 49 W 40 W 17 L 4 L 9 D 32 L 11 ON N:4 B W B W B W W 15131520 1579 1564
28 27 GAURAV GIDWANI 3.5 W 51 L 13 W 46 W 37 D 14 L 6 U MI N:4 W B W B W B 14476567 1552 1539
29 28 SOFIA ADINA STANESCUBELLU 3.5 W 24 D 4 W 22 D 19 L 20 L 8 D 36 MI N:3 W W B W B B W 14882954 1507 1513
30 29 CHIEDOZIE OKORIE 3.5 W 50 D 6 L 38 L 34 W 52 W 48 U MI N:4 B W B W W B 15323285 1602 1508
31 30 GEORGE AVERY JONES 3.5 L 52 D 64 L 15 W 55 L 31 W 61 W 50 ON W B B W W B B 12577178 1522 1444
32 31 RISHI SHETTY 3.5 L 58 D 55 W 64 L 10 W 30 W 50 L 14 MI B W B W B W B 15131618 1494 1444
33 32 JOSHUA PHILIP MATHEWS 3.5 W 61 L 8 W 44 L 18 W 51 D 26 L 13 ON N:4 W B W B W B W 14073750 1441 1433
34 33 JADE GE 3.5 W 60 L 12 W 50 D 36 L 13 L 15 W 51 MI B W B W B W B 14691842 1449 1421
35 34 MICHAEL JEFFERY THOMAS 3.5 L 6 W 60 L 37 W 29 D 25 L 11 W 52 MI B W B B W B W 15051807 1399 1400
36 35 JOSHUA DAVID LEE 3.5 L 46 L 38 W 56 L 6 W 57 D 52 W 48 MI W W B W B B W 14601397 1438 1392
37 36 SIDDHARTH JHA 3.5 L 13 W 57 W 51 D 33 H L 16 D 28 MI N:4 W B W B W B 14773163 1355 1367
38 37 AMIYATOSH PWNANANDAM 3.5 B L 5 W 34 L 27 H L 23 W 61 MI B W W B W 15489571 980 1077
39 38 BRIAN LIU 3.0 D 11 W 35 W 29 L 12 H L 18 L 15 MI N:4 W B W W B B 15108523 1423 1439
40 39 JOEL R HENDON 3.0 L 1 W 54 W 40 L 16 W 44 L 21 L 24 MI N:4 B W B W B W W 12923035 1436 1413
41 40 FOREST ZHANG 3.0 W 20 L 26 L 39 W 59 L 21 W 56 L 22 MI B B W W B W W 14892710 1348 1346
42 41 KYLE WILLIAM MURPHY 3.0 W 59 L 17 W 58 L 20 X U U MI B W B W 15761443 1403 1341
43 42 JARED GE 3.0 L 12 L 50 L 57 D 60 D 61 W 64 W 56 MI B W B B W W B 14462326 1332 1256
44 43 ROBERT GLEN VASEY 3.0 L 21 L 23 L 24 W 63 W 59 L 46 W 55 MI W B W W B B W 14101068 1283 1244
45 44 JUSTIN D SCHILLING 3.0 B L 14 L 32 W 53 L 39 L 24 W 59 MI W B B W B W 15323504 1199 1199
46 45 DEREK YAN 3.0 L 5 L 51 D 60 L 56 W 63 D 55 W 58 MI W B W B W B W 15372807 1242 1191
47 46 JACOB ALEXANDER LAVALLEY 3.0 W 35 L 7 L 27 L 50 W 64 W 43 L 23 MI B W B W B W W 15490981 377 1076
48 47 ERIC WRIGHT 2.5 L 18 W 24 L 21 W 61 L 8 D 51 L 25 MI W B W B W B W 12533115 1362 1341
49 48 DANIEL KHAIN 2.5 L 17 W 63 H D 52 H L 29 L 35 MI B W B W B 14369165 1382 1335
50 49 MICHAEL J MARTIN 2.5 L 26 L 20 D 63 D 64 W 58 H U MI W W B W B 12531685 1291 1259
51 50 SHIVAM JHA 2.5 L 29 W 42 L 33 W 46 H L 31 L 30 MI W B W B B W 14773178 1056 1111
52 51 TEJAS AYYAGARI 2.5 L 27 W 45 L 36 W 57 L 32 D 47 L 33 MI B W B W B W W 15205474 1011 1097
53 52 ETHAN GUO 2.5 W 30 D 22 L 19 D 48 L 29 D 35 L 34 MI N:4 B W B W B W B 14918803 935 1092
54 53 JOSE C YBARRA 2.0 H L 25 H L 44 U W 57 U MI B W W 12578849 1393 1359
55 54 LARRY HODGE 2.0 L 14 L 39 L 61 B L 15 L 59 W 64 MI B B W W B W 12836773 1270 1200
56 55 ALEX KONG 2.0 L 62 D 31 L 10 L 30 B D 45 L 43 MI W B W B W B 15412571 1186 1163
57 56 MARISA RICCI 2.0 H L 11 L 35 W 45 H L 40 L 42 MI B W W B W 14679887 1153 1140
58 57 MICHAEL LU 2.0 L 7 L 36 W 42 L 51 L 35 L 53 B MI B W W B W B 15113330 1092 1079
59 58 VIRAJ MOHILE 2.0 W 31 L 2 L 41 L 23 L 49 B L 45 MI W B W B W B 14700365 917 941
60 59 SEAN M MC CORMICK 2.0 L 41 B L 9 L 40 L 43 W 54 L 44 MI W B B W W B 12841036 853 878
61 60 JULIA SHEN 1.5 L 33 L 34 D 45 D 42 L 24 H U MI W B B W B 14579262 967 984
62 61 JEZZEL FARKAS 1.5 L 32 L 3 W 54 L 47 D 42 L 30 L 37 ON B W B W B W B 15771592 955 979
63 62 ASHWIN BALAJI 1.0 W 55 U U U U U U MI B 15219542 1530 1535
64 63 THOMAS JOSEPH HOSMER 1.0 L 2 L 48 D 49 L 43 L 45 H U MI W B W B B 15057092 1175 1125
65 64 BEN LI 1.0 L 22 D 30 L 31 D 49 L 46 L 42 L 54 MI B W W B W B B 15006561 1163 1112 
##  [1] "Pair"        "Player Name" "Total"       "Round"       "Round.1"    
##  [6] "Round.2"     "Round.3"     "Round.4"     "Round.5"     "Round.6"    
## [11] "State"       "Pts"         "1"           "2"           "3"          
## [16] "4"           "5"           "6"           "7"           "USCF ID"    
## [21] "Pre"         "Post"
Pair Player Name State Total Pre Post Pts Round Round.1 Round.2 Round.3 Round.4 Round.5 Round.6 1 2 2.1 4 5 6 7 USCF ID
2 1 GARY HUA ON 6.0 1794 1817 N:2 W 39 W 21 W 18 W 14 W 7 D 12 D 4 W B B B W B W 15445895
3 2 DAKSHESH DARURI MI 6.0 1553 1663 N:2 W 63 W 58 L 4 W 17 W 16 W 20 W 7 B W W W B W B 14598900
4 3 ADITYA BAJAJ MI 6.0 1384 1640 N:2 L 8 W 61 W 25 W 21 W 11 W 13 W 12 W B B B W B W 14959604
5 4 PATRICK H SCHILLING MI 5.5 1716 1744 N:2 W 23 D 28 W 2 W 26 D 5 W 19 D 1 W B B B W B B 12616049
6 5 HANSHI ZUO MI 5.5 1655 1690 N:2 W 45 W 37 D 12 D 13 D 4 W 14 W 17 B W W W B W B 14601533
7 6 HANSEN SONG OH 5.0 1686 1687 N:3 W 34 D 29 L 11 W 35 D 10 W 27 W 21 W B B B B W B 15055204
8 7 GARY DEE SWATHELL MI 5.0 1649 1673 N:3 W 57 W 46 W 13 W 11 L 1 W 9 L 2 W B B B B W W 11146376
9 8 EZEKIEL HOUGHTON MI 5.0 1641 1657 N:3 W 3 W 32 L 14 L 9 W 47 W 28 W 19 B W W W B W W 15142253
10 9 STEFANO LEE ON 5.0 1411 1564 N:2 W 25 L 18 W 59 W 8 W 26 L 7 W 20 W B B B W B B 14954524
11 10 ANVIT RAO MI 5.0 1365 1544 N:3 D 16 L 19 W 55 W 31 D 6 W 25 W 18 W W W B W B W 14150362
12 11 CAMERON WILLIAM MC LEMAN MI 4.5 1712 1696 N:3 D 38 W 56 W 6 L 7 L 3 W 34 W 26 B W W W B W B 12581589
13 12 KENNETH J TACK MI 4.5 1663 1670 N:3 W 42 W 33 D 5 W 38 H D 1 L 3 W B B B W B 12681257
14 13 TORRANCE HENRY JR MI 4.5 1666 1662 N:3 W 36 W 27 L 7 D 5 W 33 L 3 W 32 B W W B W W B 15082995
15 14 BRADLEY SHAW MI 4.5 1610 1618 N:3 W 54 W 44 W 8 L 1 D 27 L 5 W 31 W B B W B B W 10131499
16 15 ZACHARY JAMES HOUGHTON MI 4.5 1220 1416 N:3 D 19 L 16 W 30 L 22 W 54 W 33 W 38 B B B W B B W 15619130
17 16 MIKE NIKITIN MI 4.0 1604 1613 N:3 D 10 W 15 H W 39 L 2 W 36 U B W W B W B 10295068
18 17 RONALD GRZEGORCZYK MI 4.0 1629 1610 N:3 W 48 W 41 L 26 L 2 W 23 W 22 L 5 W B B B W B W 10297702
19 18 DAVID SUNDEEN MI 4.0 1600 1600 N:3 W 47 W 9 L 1 W 32 L 19 W 38 L 10 B W W W B W B 11342094
20 19 DIPANKAR ROY MI 4.0 1564 1570 N:3 D 15 W 10 W 52 D 28 W 18 L 4 L 8 W B B B W W B 14862333
21 20 JASON ZHENG MI 4.0 1595 1569 N:4 L 40 W 49 W 23 W 41 W 28 L 2 L 9 W B B B W B W 14529060
22 21 DINH DANG BUI ON 4.0 1563 1562 N:3 W 43 L 1 W 47 L 3 W 40 W 39 L 6 B W W W W B W 15495066
23 22 EUGENE L MCCLURE MI 4.0 1555 1529 N:4 W 64 D 52 L 28 W 15 H L 17 W 40 W B B B W B 12405534
24 23 ALAN BUI ON 4.0 1363 1371 L 4 W 43 L 20 W 58 L 17 W 37 W 46 B W W W B W B 15030142
25 24 MICHAEL R ALDRICH MI 4.0 1229 1300 N:4 L 28 L 47 W 43 L 25 W 60 W 44 W 39 B W W B W W B 13469010
26 25 LOREN SCHWIEBERT MI 3.5 1745 1681 N:4 L 9 W 53 L 3 W 24 D 34 L 10 W 47 B W W W B W B 12486656
27 26 MAX ZHU ON 3.5 1579 1564 N:4 W 49 W 40 W 17 L 4 L 9 D 32 L 11 B W W W B W W 15131520
28 27 GAURAV GIDWANI MI 3.5 1552 1539 N:4 W 51 L 13 W 46 W 37 D 14 L 6 U W B B B W B 14476567
29 28 SOFIA ADINA STANESCUBELLU MI 3.5 1507 1513 N:3 W 24 D 4 W 22 D 19 L 20 L 8 D 36 W W W W B B W 14882954
30 29 CHIEDOZIE OKORIE MI 3.5 1602 1508 N:4 W 50 D 6 L 38 L 34 W 52 W 48 U B W W W W B 15323285
31 30 GEORGE AVERY JONES ON 3.5 1522 1444 L 52 D 64 L 15 W 55 L 31 W 61 W 50 W B B W W B B 12577178
32 31 RISHI SHETTY MI 3.5 1494 1444 L 58 D 55 W 64 L 10 W 30 W 50 L 14 B W W W B W B 15131618
33 32 JOSHUA PHILIP MATHEWS ON 3.5 1441 1433 N:4 W 61 L 8 W 44 L 18 W 51 D 26 L 13 W B B B W B W 14073750
34 33 JADE GE MI 3.5 1449 1421 W 60 L 12 W 50 D 36 L 13 L 15 W 51 B W W W B W B 14691842
35 34 MICHAEL JEFFERY THOMAS MI 3.5 1399 1400 L 6 W 60 L 37 W 29 D 25 L 11 W 52 B W W B W B W 15051807
36 35 JOSHUA DAVID LEE MI 3.5 1438 1392 L 46 L 38 W 56 L 6 W 57 D 52 W 48 W W W W B B W 14601397
37 36 SIDDHARTH JHA MI 3.5 1355 1367 N:4 L 13 W 57 W 51 D 33 H L 16 D 28 W B B B W B 14773163
38 37 AMIYATOSH PWNANANDAM MI 3.5 980 1077 B L 5 W 34 L 27 H L 23 W 61 B B W B W 15489571
39 38 BRIAN LIU MI 3.0 1423 1439 N:4 D 11 W 35 W 29 L 12 H L 18 L 15 W B B W B B 15108523
40 39 JOEL R HENDON MI 3.0 1436 1413 N:4 L 1 W 54 W 40 L 16 W 44 L 21 L 24 B W W W B W W 12923035
41 40 FOREST ZHANG MI 3.0 1348 1346 W 20 L 26 L 39 W 59 L 21 W 56 L 22 B B B W B W W 14892710
42 41 KYLE WILLIAM MURPHY MI 3.0 1403 1341 W 59 L 17 W 58 L 20 X U U B W W W 15761443
43 42 JARED GE MI 3.0 1332 1256 L 12 L 50 L 57 D 60 D 61 W 64 W 56 B W W B W W B 14462326
44 43 ROBERT GLEN VASEY MI 3.0 1283 1244 L 21 L 23 L 24 W 63 W 59 L 46 W 55 W B B W B B W 14101068
45 44 JUSTIN D SCHILLING MI 3.0 1199 1199 B L 14 L 32 W 53 L 39 L 24 W 59 W W B W B W 15323504
46 45 DEREK YAN MI 3.0 1242 1191 L 5 L 51 D 60 L 56 W 63 D 55 W 58 W B B B W B W 15372807
47 46 JACOB ALEXANDER LAVALLEY MI 3.0 377 1076 W 35 L 7 L 27 L 50 W 64 W 43 L 23 B W W W B W W 15490981
48 47 ERIC WRIGHT MI 2.5 1362 1341 L 18 W 24 L 21 W 61 L 8 D 51 L 25 W B B B W B W 12533115
49 48 DANIEL KHAIN MI 2.5 1382 1335 L 17 W 63 H D 52 H L 29 L 35 B W W B W B 14369165
50 49 MICHAEL J MARTIN MI 2.5 1291 1259 L 26 L 20 D 63 D 64 W 58 H U W W W W B 12531685
51 50 SHIVAM JHA MI 2.5 1056 1111 L 29 W 42 L 33 W 46 H L 31 L 30 W B B B B W 14773178
52 51 TEJAS AYYAGARI MI 2.5 1011 1097 L 27 W 45 L 36 W 57 L 32 D 47 L 33 B W W W B W W 15205474
53 52 ETHAN GUO MI 2.5 935 1092 N:4 W 30 D 22 L 19 D 48 L 29 D 35 L 34 B W W W B W B 14918803
54 53 JOSE C YBARRA MI 2.0 1393 1359 H L 25 H L 44 U W 57 U B B W W 12578849
55 54 LARRY HODGE MI 2.0 1270 1200 L 14 L 39 L 61 B L 15 L 59 W 64 B B B W B W 12836773
56 55 ALEX KONG MI 2.0 1186 1163 L 62 D 31 L 10 L 30 B D 45 L 43 W B B B W B 15412571
57 56 MARISA RICCI MI 2.0 1153 1140 H L 11 L 35 W 45 H L 40 L 42 B B W B W 14679887
58 57 MICHAEL LU MI 2.0 1092 1079 L 7 L 36 W 42 L 51 L 35 L 53 B B W W B W B 15113330
59 58 VIRAJ MOHILE MI 2.0 917 941 W 31 L 2 L 41 L 23 L 49 B L 45 W B B B W B 14700365
60 59 SEAN M MC CORMICK MI 2.0 853 878 L 41 B L 9 L 40 L 43 W 54 L 44 W B W W B 12841036
61 60 JULIA SHEN MI 1.5 967 984 L 33 L 34 D 45 D 42 L 24 H U W B B W B 14579262
62 61 JEZZEL FARKAS ON 1.5 955 979 L 32 L 3 W 54 L 47 D 42 L 30 L 37 B W W W B W B 15771592
63 62 ASHWIN BALAJI MI 1.0 1530 1535 W 55 U U U U U U B 15219542
64 63 THOMAS JOSEPH HOSMER MI 1.0 1175 1125 L 2 L 48 D 49 L 43 L 45 H U W B B B B 15057092
65 64 BEN LI MI 1.0 1163 1112 L 22 D 30 L 31 D 49 L 46 L 42 L 54 B W W B W B B 15006561
It is better to take a subset from the data to focus on it and make plausible analysis into it. So, I took a subset from column 1 to column 7. Then, I add a new column for average initialized with 0 value - this will be the calculated average pre-rating for each player.
Pair Player Name State Total Pre Post Pts
2 1 GARY HUA ON 6.0 1794 1817 N:2
3 2 DAKSHESH DARURI MI 6.0 1553 1663 N:2
4 3 ADITYA BAJAJ MI 6.0 1384 1640 N:2
5 4 PATRICK H SCHILLING MI 5.5 1716 1744 N:2
6 5 HANSHI ZUO MI 5.5 1655 1690 N:2
7 6 HANSEN SONG OH 5.0 1686 1687 N:3
  • Initialize avg_rtg varaible filled with 0 numeric value.
Pair Player Name State Total Pre Post Pts avg_rtg
2 1 GARY HUA ON 6.0 1794 1817 N:2 0
3 2 DAKSHESH DARURI MI 6.0 1553 1663 N:2 0
4 3 ADITYA BAJAJ MI 6.0 1384 1640 N:2 0
5 4 PATRICK H SCHILLING MI 5.5 1716 1744 N:2 0
6 5 HANSHI ZUO MI 5.5 1655 1690 N:2 0
7 6 HANSEN SONG OH 5.0 1686 1687 N:3 0
8 7 GARY DEE SWATHELL MI 5.0 1649 1673 N:3 0
9 8 EZEKIEL HOUGHTON MI 5.0 1641 1657 N:3 0
10 9 STEFANO LEE ON 5.0 1411 1564 N:2 0
11 10 ANVIT RAO MI 5.0 1365 1544 N:3 0
12 11 CAMERON WILLIAM MC LEMAN MI 4.5 1712 1696 N:3 0
13 12 KENNETH J TACK MI 4.5 1663 1670 N:3 0
14 13 TORRANCE HENRY JR MI 4.5 1666 1662 N:3 0
15 14 BRADLEY SHAW MI 4.5 1610 1618 N:3 0
16 15 ZACHARY JAMES HOUGHTON MI 4.5 1220 1416 N:3 0
17 16 MIKE NIKITIN MI 4.0 1604 1613 N:3 0
18 17 RONALD GRZEGORCZYK MI 4.0 1629 1610 N:3 0
19 18 DAVID SUNDEEN MI 4.0 1600 1600 N:3 0
20 19 DIPANKAR ROY MI 4.0 1564 1570 N:3 0
21 20 JASON ZHENG MI 4.0 1595 1569 N:4 0
22 21 DINH DANG BUI ON 4.0 1563 1562 N:3 0
23 22 EUGENE L MCCLURE MI 4.0 1555 1529 N:4 0
24 23 ALAN BUI ON 4.0 1363 1371 0
25 24 MICHAEL R ALDRICH MI 4.0 1229 1300 N:4 0
26 25 LOREN SCHWIEBERT MI 3.5 1745 1681 N:4 0
27 26 MAX ZHU ON 3.5 1579 1564 N:4 0
28 27 GAURAV GIDWANI MI 3.5 1552 1539 N:4 0
29 28 SOFIA ADINA STANESCUBELLU MI 3.5 1507 1513 N:3 0
30 29 CHIEDOZIE OKORIE MI 3.5 1602 1508 N:4 0
31 30 GEORGE AVERY JONES ON 3.5 1522 1444 0
32 31 RISHI SHETTY MI 3.5 1494 1444 0
33 32 JOSHUA PHILIP MATHEWS ON 3.5 1441 1433 N:4 0
34 33 JADE GE MI 3.5 1449 1421 0
35 34 MICHAEL JEFFERY THOMAS MI 3.5 1399 1400 0
36 35 JOSHUA DAVID LEE MI 3.5 1438 1392 0
37 36 SIDDHARTH JHA MI 3.5 1355 1367 N:4 0
38 37 AMIYATOSH PWNANANDAM MI 3.5 980 1077 0
39 38 BRIAN LIU MI 3.0 1423 1439 N:4 0
40 39 JOEL R HENDON MI 3.0 1436 1413 N:4 0
41 40 FOREST ZHANG MI 3.0 1348 1346 0
42 41 KYLE WILLIAM MURPHY MI 3.0 1403 1341 0
43 42 JARED GE MI 3.0 1332 1256 0
44 43 ROBERT GLEN VASEY MI 3.0 1283 1244 0
45 44 JUSTIN D SCHILLING MI 3.0 1199 1199 0
46 45 DEREK YAN MI 3.0 1242 1191 0
47 46 JACOB ALEXANDER LAVALLEY MI 3.0 377 1076 0
48 47 ERIC WRIGHT MI 2.5 1362 1341 0
49 48 DANIEL KHAIN MI 2.5 1382 1335 0
50 49 MICHAEL J MARTIN MI 2.5 1291 1259 0
51 50 SHIVAM JHA MI 2.5 1056 1111 0
52 51 TEJAS AYYAGARI MI 2.5 1011 1097 0
53 52 ETHAN GUO MI 2.5 935 1092 N:4 0
54 53 JOSE C YBARRA MI 2.0 1393 1359 0
55 54 LARRY HODGE MI 2.0 1270 1200 0
56 55 ALEX KONG MI 2.0 1186 1163 0
57 56 MARISA RICCI MI 2.0 1153 1140 0
58 57 MICHAEL LU MI 2.0 1092 1079 0
59 58 VIRAJ MOHILE MI 2.0 917 941 0
60 59 SEAN M MC CORMICK MI 2.0 853 878 0
61 60 JULIA SHEN MI 1.5 967 984 0
62 61 JEZZEL FARKAS ON 1.5 955 979 0
63 62 ASHWIN BALAJI MI 1.0 1530 1535 0
64 63 THOMAS JOSEPH HOSMER MI 1.0 1175 1125 0
65 64 BEN LI MI 1.0 1163 1112 0
Pair Player Name
2 1 GARY HUA
3 2 DAKSHESH DARURI
4 3 ADITYA BAJAJ
5 4 PATRICK H SCHILLING
6 5 HANSHI ZUO
7 6 HANSEN SONG
8 7 GARY DEE SWATHELL
9 8 EZEKIEL HOUGHTON
10 9 STEFANO LEE
11 10 ANVIT RAO
12 11 CAMERON WILLIAM MC LEMAN
13 12 KENNETH J TACK
14 13 TORRANCE HENRY JR
15 14 BRADLEY SHAW
16 15 ZACHARY JAMES HOUGHTON
17 16 MIKE NIKITIN
18 17 RONALD GRZEGORCZYK
19 18 DAVID SUNDEEN
20 19 DIPANKAR ROY
21 20 JASON ZHENG
22 21 DINH DANG BUI
23 22 EUGENE L MCCLURE
24 23 ALAN BUI
25 24 MICHAEL R ALDRICH
26 25 LOREN SCHWIEBERT
27 26 MAX ZHU
28 27 GAURAV GIDWANI
29 28 SOFIA ADINA STANESCUBELLU
30 29 CHIEDOZIE OKORIE
31 30 GEORGE AVERY JONES
32 31 RISHI SHETTY
33 32 JOSHUA PHILIP MATHEWS
34 33 JADE GE
35 34 MICHAEL JEFFERY THOMAS
36 35 JOSHUA DAVID LEE
37 36 SIDDHARTH JHA
38 37 AMIYATOSH PWNANANDAM
39 38 BRIAN LIU
40 39 JOEL R HENDON
41 40 FOREST ZHANG
42 41 KYLE WILLIAM MURPHY
43 42 JARED GE
44 43 ROBERT GLEN VASEY
45 44 JUSTIN D SCHILLING
46 45 DEREK YAN
47 46 JACOB ALEXANDER LAVALLEY
48 47 ERIC WRIGHT
49 48 DANIEL KHAIN
50 49 MICHAEL J MARTIN
51 50 SHIVAM JHA
52 51 TEJAS AYYAGARI
53 52 ETHAN GUO
54 53 JOSE C YBARRA
55 54 LARRY HODGE
56 55 ALEX KONG
57 56 MARISA RICCI
58 57 MICHAEL LU
59 58 VIRAJ MOHILE
60 59 SEAN M MC CORMICK
61 60 JULIA SHEN
62 61 JEZZEL FARKAS
63 62 ASHWIN BALAJI
64 63 THOMAS JOSEPH HOSMER
65 64 BEN LI
Now, Creating the opponents dataframe, to get number of games, and pre- rating for each player and opponent.
Pair Player Name Opp 1 Opp 2 Opp 3 Opp 4 Opp 5 Opp 6 Opp 7 NGames
2 1 GARY HUA 39 21 18 14 7 12 4 7
3 2 DAKSHESH DARURI 63 58 4 17 16 20 7 7
4 3 ADITYA BAJAJ 8 61 25 21 11 13 12 7
5 4 PATRICK H SCHILLING 23 28 2 26 5 19 1 7
6 5 HANSHI ZUO 45 37 12 13 4 14 17 7
7 6 HANSEN SONG 34 29 11 35 10 27 21 7
8 7 GARY DEE SWATHELL 57 46 13 11 1 9 2 7
9 8 EZEKIEL HOUGHTON 3 32 14 9 47 28 19 7
10 9 STEFANO LEE 25 18 59 8 26 7 20 7
11 10 ANVIT RAO 16 19 55 31 6 25 18 7
12 11 CAMERON WILLIAM MC LEMAN 38 56 6 7 3 34 26 7
13 12 KENNETH J TACK 42 33 5 38 NA 1 3 6
14 13 TORRANCE HENRY JR 36 27 7 5 33 3 32 7
15 14 BRADLEY SHAW 54 44 8 1 27 5 31 7
16 15 ZACHARY JAMES HOUGHTON 19 16 30 22 54 33 38 7
17 16 MIKE NIKITIN 10 15 NA 39 2 36 NA 5
18 17 RONALD GRZEGORCZYK 48 41 26 2 23 22 5 7
19 18 DAVID SUNDEEN 47 9 1 32 19 38 10 7
20 19 DIPANKAR ROY 15 10 52 28 18 4 8 7
21 20 JASON ZHENG 40 49 23 41 28 2 9 7
22 21 DINH DANG BUI 43 1 47 3 40 39 6 7
23 22 EUGENE L MCCLURE 64 52 28 15 NA 17 40 6
24 23 ALAN BUI 4 43 20 58 17 37 46 7
25 24 MICHAEL R ALDRICH 28 47 43 25 60 44 39 7
26 25 LOREN SCHWIEBERT 9 53 3 24 34 10 47 7
27 26 MAX ZHU 49 40 17 4 9 32 11 7
28 27 GAURAV GIDWANI 51 13 46 37 14 6 NA 6
29 28 SOFIA ADINA STANESCUBELLU 24 4 22 19 20 8 36 7
30 29 CHIEDOZIE OKORIE 50 6 38 34 52 48 NA 6
31 30 GEORGE AVERY JONES 52 64 15 55 31 61 50 7
32 31 RISHI SHETTY 58 55 64 10 30 50 14 7
33 32 JOSHUA PHILIP MATHEWS 61 8 44 18 51 26 13 7
34 33 JADE GE 60 12 50 36 13 15 51 7
35 34 MICHAEL JEFFERY THOMAS 6 60 37 29 25 11 52 7
36 35 JOSHUA DAVID LEE 46 38 56 6 57 52 48 7
37 36 SIDDHARTH JHA 13 57 51 33 NA 16 28 6
38 37 AMIYATOSH PWNANANDAM NA 5 34 27 NA 23 61 5
39 38 BRIAN LIU 11 35 29 12 NA 18 15 6
40 39 JOEL R HENDON 1 54 40 16 44 21 24 7
41 40 FOREST ZHANG 20 26 39 59 21 56 22 7
42 41 KYLE WILLIAM MURPHY 59 17 58 20 NA NA NA 4
43 42 JARED GE 12 50 57 60 61 64 56 7
44 43 ROBERT GLEN VASEY 21 23 24 63 59 46 55 7
45 44 JUSTIN D SCHILLING NA 14 32 53 39 24 59 6
46 45 DEREK YAN 5 51 60 56 63 55 58 7
47 46 JACOB ALEXANDER LAVALLEY 35 7 27 50 64 43 23 7
48 47 ERIC WRIGHT 18 24 21 61 8 51 25 7
49 48 DANIEL KHAIN 17 63 NA 52 NA 29 35 5
50 49 MICHAEL J MARTIN 26 20 63 64 58 NA NA 5
51 50 SHIVAM JHA 29 42 33 46 NA 31 30 6
52 51 TEJAS AYYAGARI 27 45 36 57 32 47 33 7
53 52 ETHAN GUO 30 22 19 48 29 35 34 7
54 53 JOSE C YBARRA NA 25 NA 44 NA 57 NA 3
55 54 LARRY HODGE 14 39 61 NA 15 59 64 6
56 55 ALEX KONG 62 31 10 30 NA 45 43 6
57 56 MARISA RICCI NA 11 35 45 NA 40 42 5
58 57 MICHAEL LU 7 36 42 51 35 53 NA 6
59 58 VIRAJ MOHILE 31 2 41 23 49 NA 45 6
60 59 SEAN M MC CORMICK 41 NA 9 40 43 54 44 6
61 60 JULIA SHEN 33 34 45 42 24 NA NA 5
62 61 JEZZEL FARKAS 32 3 54 47 42 30 37 7
63 62 ASHWIN BALAJI 55 NA NA NA NA NA NA 1
64 63 THOMAS JOSEPH HOSMER 2 48 49 43 45 NA NA 5
65 64 BEN LI 22 30 31 49 46 42 54 7
  • More cleaning up - NA -> 0 value.
Pair Player Name Opp 1 Opp 2 Opp 3 Opp 4 Opp 5 Opp 6 Opp 7 NGames
2 1 GARY HUA 39 21 18 14 7 12 4 7
3 2 DAKSHESH DARURI 63 58 4 17 16 20 7 7
4 3 ADITYA BAJAJ 8 61 25 21 11 13 12 7
5 4 PATRICK H SCHILLING 23 28 2 26 5 19 1 7
6 5 HANSHI ZUO 45 37 12 13 4 14 17 7
7 6 HANSEN SONG 34 29 11 35 10 27 21 7
8 7 GARY DEE SWATHELL 57 46 13 11 1 9 2 7
9 8 EZEKIEL HOUGHTON 3 32 14 9 47 28 19 7
10 9 STEFANO LEE 25 18 59 8 26 7 20 7
11 10 ANVIT RAO 16 19 55 31 6 25 18 7
12 11 CAMERON WILLIAM MC LEMAN 38 56 6 7 3 34 26 7
13 12 KENNETH J TACK 42 33 5 38 0 1 3 6
14 13 TORRANCE HENRY JR 36 27 7 5 33 3 32 7
15 14 BRADLEY SHAW 54 44 8 1 27 5 31 7
16 15 ZACHARY JAMES HOUGHTON 19 16 30 22 54 33 38 7
17 16 MIKE NIKITIN 10 15 0 39 2 36 0 5
18 17 RONALD GRZEGORCZYK 48 41 26 2 23 22 5 7
19 18 DAVID SUNDEEN 47 9 1 32 19 38 10 7
20 19 DIPANKAR ROY 15 10 52 28 18 4 8 7
21 20 JASON ZHENG 40 49 23 41 28 2 9 7
22 21 DINH DANG BUI 43 1 47 3 40 39 6 7
23 22 EUGENE L MCCLURE 64 52 28 15 0 17 40 6
24 23 ALAN BUI 4 43 20 58 17 37 46 7
25 24 MICHAEL R ALDRICH 28 47 43 25 60 44 39 7
26 25 LOREN SCHWIEBERT 9 53 3 24 34 10 47 7
27 26 MAX ZHU 49 40 17 4 9 32 11 7
28 27 GAURAV GIDWANI 51 13 46 37 14 6 0 6
29 28 SOFIA ADINA STANESCUBELLU 24 4 22 19 20 8 36 7
30 29 CHIEDOZIE OKORIE 50 6 38 34 52 48 0 6
31 30 GEORGE AVERY JONES 52 64 15 55 31 61 50 7
32 31 RISHI SHETTY 58 55 64 10 30 50 14 7
33 32 JOSHUA PHILIP MATHEWS 61 8 44 18 51 26 13 7
34 33 JADE GE 60 12 50 36 13 15 51 7
35 34 MICHAEL JEFFERY THOMAS 6 60 37 29 25 11 52 7
36 35 JOSHUA DAVID LEE 46 38 56 6 57 52 48 7
37 36 SIDDHARTH JHA 13 57 51 33 0 16 28 6
38 37 AMIYATOSH PWNANANDAM 0 5 34 27 0 23 61 5
39 38 BRIAN LIU 11 35 29 12 0 18 15 6
40 39 JOEL R HENDON 1 54 40 16 44 21 24 7
41 40 FOREST ZHANG 20 26 39 59 21 56 22 7
42 41 KYLE WILLIAM MURPHY 59 17 58 20 0 0 0 4
43 42 JARED GE 12 50 57 60 61 64 56 7
44 43 ROBERT GLEN VASEY 21 23 24 63 59 46 55 7
45 44 JUSTIN D SCHILLING 0 14 32 53 39 24 59 6
46 45 DEREK YAN 5 51 60 56 63 55 58 7
47 46 JACOB ALEXANDER LAVALLEY 35 7 27 50 64 43 23 7
48 47 ERIC WRIGHT 18 24 21 61 8 51 25 7
49 48 DANIEL KHAIN 17 63 0 52 0 29 35 5
50 49 MICHAEL J MARTIN 26 20 63 64 58 0 0 5
51 50 SHIVAM JHA 29 42 33 46 0 31 30 6
52 51 TEJAS AYYAGARI 27 45 36 57 32 47 33 7
53 52 ETHAN GUO 30 22 19 48 29 35 34 7
54 53 JOSE C YBARRA 0 25 0 44 0 57 0 3
55 54 LARRY HODGE 14 39 61 0 15 59 64 6
56 55 ALEX KONG 62 31 10 30 0 45 43 6
57 56 MARISA RICCI 0 11 35 45 0 40 42 5
58 57 MICHAEL LU 7 36 42 51 35 53 0 6
59 58 VIRAJ MOHILE 31 2 41 23 49 0 45 6
60 59 SEAN M MC CORMICK 41 0 9 40 43 54 44 6
61 60 JULIA SHEN 33 34 45 42 24 0 0 5
62 61 JEZZEL FARKAS 32 3 54 47 42 30 37 7
63 62 ASHWIN BALAJI 55 0 0 0 0 0 0 1
64 63 THOMAS JOSEPH HOSMER 2 48 49 43 45 0 0 5
65 64 BEN LI 22 30 31 49 46 42 54 7
  • Merging final_df with the opponents
Pair Player Name State Total Pre Post Pts avg_rtg Opp 1 Opp 2 Opp 3 Opp 4 Opp 5 Opp 6 Opp 7 NGames
1 GARY HUA ON 6.0 1794 1817 N:2 0 39 21 18 14 7 12 4 7
2 DAKSHESH DARURI MI 6.0 1553 1663 N:2 0 63 58 4 17 16 20 7 7
3 ADITYA BAJAJ MI 6.0 1384 1640 N:2 0 8 61 25 21 11 13 12 7
4 PATRICK H SCHILLING MI 5.5 1716 1744 N:2 0 23 28 2 26 5 19 1 7
5 HANSHI ZUO MI 5.5 1655 1690 N:2 0 45 37 12 13 4 14 17 7
6 HANSEN SONG OH 5.0 1686 1687 N:3 0 34 29 11 35 10 27 21 7
7 GARY DEE SWATHELL MI 5.0 1649 1673 N:3 0 57 46 13 11 1 9 2 7
8 EZEKIEL HOUGHTON MI 5.0 1641 1657 N:3 0 3 32 14 9 47 28 19 7
9 STEFANO LEE ON 5.0 1411 1564 N:2 0 25 18 59 8 26 7 20 7
10 ANVIT RAO MI 5.0 1365 1544 N:3 0 16 19 55 31 6 25 18 7
11 CAMERON WILLIAM MC LEMAN MI 4.5 1712 1696 N:3 0 38 56 6 7 3 34 26 7
12 KENNETH J TACK MI 4.5 1663 1670 N:3 0 42 33 5 38 0 1 3 6
13 TORRANCE HENRY JR MI 4.5 1666 1662 N:3 0 36 27 7 5 33 3 32 7
14 BRADLEY SHAW MI 4.5 1610 1618 N:3 0 54 44 8 1 27 5 31 7
15 ZACHARY JAMES HOUGHTON MI 4.5 1220 1416 N:3 0 19 16 30 22 54 33 38 7
16 MIKE NIKITIN MI 4.0 1604 1613 N:3 0 10 15 0 39 2 36 0 5
17 RONALD GRZEGORCZYK MI 4.0 1629 1610 N:3 0 48 41 26 2 23 22 5 7
18 DAVID SUNDEEN MI 4.0 1600 1600 N:3 0 47 9 1 32 19 38 10 7
19 DIPANKAR ROY MI 4.0 1564 1570 N:3 0 15 10 52 28 18 4 8 7
20 JASON ZHENG MI 4.0 1595 1569 N:4 0 40 49 23 41 28 2 9 7
21 DINH DANG BUI ON 4.0 1563 1562 N:3 0 43 1 47 3 40 39 6 7
22 EUGENE L MCCLURE MI 4.0 1555 1529 N:4 0 64 52 28 15 0 17 40 6
23 ALAN BUI ON 4.0 1363 1371 0 4 43 20 58 17 37 46 7
24 MICHAEL R ALDRICH MI 4.0 1229 1300 N:4 0 28 47 43 25 60 44 39 7
25 LOREN SCHWIEBERT MI 3.5 1745 1681 N:4 0 9 53 3 24 34 10 47 7
26 MAX ZHU ON 3.5 1579 1564 N:4 0 49 40 17 4 9 32 11 7
27 GAURAV GIDWANI MI 3.5 1552 1539 N:4 0 51 13 46 37 14 6 0 6
28 SOFIA ADINA STANESCUBELLU MI 3.5 1507 1513 N:3 0 24 4 22 19 20 8 36 7
29 CHIEDOZIE OKORIE MI 3.5 1602 1508 N:4 0 50 6 38 34 52 48 0 6
30 GEORGE AVERY JONES ON 3.5 1522 1444 0 52 64 15 55 31 61 50 7
31 RISHI SHETTY MI 3.5 1494 1444 0 58 55 64 10 30 50 14 7
32 JOSHUA PHILIP MATHEWS ON 3.5 1441 1433 N:4 0 61 8 44 18 51 26 13 7
33 JADE GE MI 3.5 1449 1421 0 60 12 50 36 13 15 51 7
34 MICHAEL JEFFERY THOMAS MI 3.5 1399 1400 0 6 60 37 29 25 11 52 7
35 JOSHUA DAVID LEE MI 3.5 1438 1392 0 46 38 56 6 57 52 48 7
36 SIDDHARTH JHA MI 3.5 1355 1367 N:4 0 13 57 51 33 0 16 28 6
37 AMIYATOSH PWNANANDAM MI 3.5 980 1077 0 0 5 34 27 0 23 61 5
38 BRIAN LIU MI 3.0 1423 1439 N:4 0 11 35 29 12 0 18 15 6
39 JOEL R HENDON MI 3.0 1436 1413 N:4 0 1 54 40 16 44 21 24 7
40 FOREST ZHANG MI 3.0 1348 1346 0 20 26 39 59 21 56 22 7
41 KYLE WILLIAM MURPHY MI 3.0 1403 1341 0 59 17 58 20 0 0 0 4
42 JARED GE MI 3.0 1332 1256 0 12 50 57 60 61 64 56 7
43 ROBERT GLEN VASEY MI 3.0 1283 1244 0 21 23 24 63 59 46 55 7
44 JUSTIN D SCHILLING MI 3.0 1199 1199 0 0 14 32 53 39 24 59 6
45 DEREK YAN MI 3.0 1242 1191 0 5 51 60 56 63 55 58 7
46 JACOB ALEXANDER LAVALLEY MI 3.0 377 1076 0 35 7 27 50 64 43 23 7
47 ERIC WRIGHT MI 2.5 1362 1341 0 18 24 21 61 8 51 25 7
48 DANIEL KHAIN MI 2.5 1382 1335 0 17 63 0 52 0 29 35 5
49 MICHAEL J MARTIN MI 2.5 1291 1259 0 26 20 63 64 58 0 0 5
50 SHIVAM JHA MI 2.5 1056 1111 0 29 42 33 46 0 31 30 6
51 TEJAS AYYAGARI MI 2.5 1011 1097 0 27 45 36 57 32 47 33 7
52 ETHAN GUO MI 2.5 935 1092 N:4 0 30 22 19 48 29 35 34 7
53 JOSE C YBARRA MI 2.0 1393 1359 0 0 25 0 44 0 57 0 3
54 LARRY HODGE MI 2.0 1270 1200 0 14 39 61 0 15 59 64 6
55 ALEX KONG MI 2.0 1186 1163 0 62 31 10 30 0 45 43 6
56 MARISA RICCI MI 2.0 1153 1140 0 0 11 35 45 0 40 42 5
57 MICHAEL LU MI 2.0 1092 1079 0 7 36 42 51 35 53 0 6
58 VIRAJ MOHILE MI 2.0 917 941 0 31 2 41 23 49 0 45 6
59 SEAN M MC CORMICK MI 2.0 853 878 0 41 0 9 40 43 54 44 6
60 JULIA SHEN MI 1.5 967 984 0 33 34 45 42 24 0 0 5
61 JEZZEL FARKAS ON 1.5 955 979 0 32 3 54 47 42 30 37 7
62 ASHWIN BALAJI MI 1.0 1530 1535 0 55 0 0 0 0 0 0 1
63 THOMAS JOSEPH HOSMER MI 1.0 1175 1125 0 2 48 49 43 45 0 0 5
64 BEN LI MI 1.0 1163 1112 0 22 30 31 49 46 42 54 7
Calculating the average pre-rating for each player. Construct a for-loop to iterate over the dataframe and if the value of the opponent is equal to any pair number in the df then calculate the average value.
Pair Player Name State Total Pre Post Pts avg_rtg
2 1 GARY HUA ON 6.0 1794 1817 N:2 1605
3 2 DAKSHESH DARURI MI 6.0 1553 1663 N:2 1469
4 3 ADITYA BAJAJ MI 6.0 1384 1640 N:2 1564
5 4 PATRICK H SCHILLING MI 5.5 1716 1744 N:2 1574
6 5 HANSHI ZUO MI 5.5 1655 1690 N:2 1501
7 6 HANSEN SONG OH 5.0 1686 1687 N:3 1519
8 7 GARY DEE SWATHELL MI 5.0 1649 1673 N:3 1372
9 8 EZEKIEL HOUGHTON MI 5.0 1641 1657 N:3 1468
10 9 STEFANO LEE ON 5.0 1411 1564 N:2 1523
11 10 ANVIT RAO MI 5.0 1365 1544 N:3 1554
12 11 CAMERON WILLIAM MC LEMAN MI 4.5 1712 1696 N:3 1468
13 12 KENNETH J TACK MI 4.5 1663 1670 N:3 1506
14 13 TORRANCE HENRY JR MI 4.5 1666 1662 N:3 1498
15 14 BRADLEY SHAW MI 4.5 1610 1618 N:3 1515
16 15 ZACHARY JAMES HOUGHTON MI 4.5 1220 1416 N:3 1484
17 16 MIKE NIKITIN MI 4.0 1604 1613 N:3 1386
18 17 RONALD GRZEGORCZYK MI 4.0 1629 1610 N:3 1499
19 18 DAVID SUNDEEN MI 4.0 1600 1600 N:3 1480
20 19 DIPANKAR ROY MI 4.0 1564 1570 N:3 1426
21 20 JASON ZHENG MI 4.0 1595 1569 N:4 1411
22 21 DINH DANG BUI ON 4.0 1563 1562 N:3 1470
23 22 EUGENE L MCCLURE MI 4.0 1555 1529 N:4 1300
24 23 ALAN BUI ON 4.0 1363 1371 1214
25 24 MICHAEL R ALDRICH MI 4.0 1229 1300 N:4 1357
26 25 LOREN SCHWIEBERT MI 3.5 1745 1681 N:4 1363
27 26 MAX ZHU ON 3.5 1579 1564 N:4 1507
28 27 GAURAV GIDWANI MI 3.5 1552 1539 N:4 1222
29 28 SOFIA ADINA STANESCUBELLU MI 3.5 1507 1513 N:3 1522
30 29 CHIEDOZIE OKORIE MI 3.5 1602 1508 N:4 1314
31 30 GEORGE AVERY JONES ON 3.5 1522 1444 1144
32 31 RISHI SHETTY MI 3.5 1494 1444 1260
33 32 JOSHUA PHILIP MATHEWS ON 3.5 1441 1433 N:4 1379
34 33 JADE GE MI 3.5 1449 1421 1277
35 34 MICHAEL JEFFERY THOMAS MI 3.5 1399 1400 1375
36 35 JOSHUA DAVID LEE MI 3.5 1438 1392 1150
37 36 SIDDHARTH JHA MI 3.5 1355 1367 N:4 1388
38 37 AMIYATOSH PWNANANDAM MI 3.5 980 1077 1385
39 38 BRIAN LIU MI 3.0 1423 1439 N:4 1539
40 39 JOEL R HENDON MI 3.0 1436 1413 N:4 1430
41 40 FOREST ZHANG MI 3.0 1348 1346 1391
42 41 KYLE WILLIAM MURPHY MI 3.0 1403 1341 1248
43 42 JARED GE MI 3.0 1332 1256 1150
44 43 ROBERT GLEN VASEY MI 3.0 1283 1244 1107
45 44 JUSTIN D SCHILLING MI 3.0 1199 1199 1327
46 45 DEREK YAN MI 3.0 1242 1191 1152
47 46 JACOB ALEXANDER LAVALLEY MI 3.0 377 1076 1358
48 47 ERIC WRIGHT MI 2.5 1362 1341 1392
49 48 DANIEL KHAIN MI 2.5 1382 1335 1356
50 49 MICHAEL J MARTIN MI 2.5 1291 1259 1286
51 50 SHIVAM JHA MI 2.5 1056 1111 1296
52 51 TEJAS AYYAGARI MI 2.5 1011 1097 1356
53 52 ETHAN GUO MI 2.5 935 1092 N:4 1495
54 53 JOSE C YBARRA MI 2.0 1393 1359 1345
55 54 LARRY HODGE MI 2.0 1270 1200 1206
56 55 ALEX KONG MI 2.0 1186 1163 1406
57 56 MARISA RICCI MI 2.0 1153 1140 1414
58 57 MICHAEL LU MI 2.0 1092 1079 1363
59 58 VIRAJ MOHILE MI 2.0 917 941 1391
60 59 SEAN M MC CORMICK MI 2.0 853 878 1319
61 60 JULIA SHEN MI 1.5 967 984 1330
62 61 JEZZEL FARKAS ON 1.5 955 979 1327
63 62 ASHWIN BALAJI MI 1.0 1530 1535 1186
64 63 THOMAS JOSEPH HOSMER MI 1.0 1175 1125 1350
65 64 BEN LI MI 1.0 1163 1112 1263

Making analysis

Here Need to know the which player has the max average pre-rating compared to his peers aggregated by state. Bar chart are the best in describing that.
To analyse, I will merge final_table with final_df in one table to get insight details.
##  [1] "Pair"        "Player Name" "State"       "Pre"         "Post"       
##  [6] "Total"       "Pts"         "avg_rtg.x"   "avg_rtg.y"   "Opp 1"      
## [11] "Opp 2"       "Opp 3"       "Opp 4"       "Opp 5"       "Opp 6"      
## [16] "Opp 7"       "NGames"
Pair Player Name State Pre Post Total Pts avg_rtg.x avg_rtg.y Opp 1 Opp 2 Opp 3 Opp 4 Opp 5 Opp 6 Opp 7 NGames
1 GARY HUA ON 1794 1817 6.0 N:2 1605 0 39 21 18 14 7 12 4 7
2 DAKSHESH DARURI MI 1553 1663 6.0 N:2 1469 0 63 58 4 17 16 20 7 7
3 ADITYA BAJAJ MI 1384 1640 6.0 N:2 1564 0 8 61 25 21 11 13 12 7
4 PATRICK H SCHILLING MI 1716 1744 5.5 N:2 1574 0 23 28 2 26 5 19 1 7
5 HANSHI ZUO MI 1655 1690 5.5 N:2 1501 0 45 37 12 13 4 14 17 7
6 HANSEN SONG OH 1686 1687 5.0 N:3 1519 0 34 29 11 35 10 27 21 7
7 GARY DEE SWATHELL MI 1649 1673 5.0 N:3 1372 0 57 46 13 11 1 9 2 7
8 EZEKIEL HOUGHTON MI 1641 1657 5.0 N:3 1468 0 3 32 14 9 47 28 19 7
9 STEFANO LEE ON 1411 1564 5.0 N:2 1523 0 25 18 59 8 26 7 20 7
10 ANVIT RAO MI 1365 1544 5.0 N:3 1554 0 16 19 55 31 6 25 18 7
11 CAMERON WILLIAM MC LEMAN MI 1712 1696 4.5 N:3 1468 0 38 56 6 7 3 34 26 7
12 KENNETH J TACK MI 1663 1670 4.5 N:3 1506 0 42 33 5 38 0 1 3 6
13 TORRANCE HENRY JR MI 1666 1662 4.5 N:3 1498 0 36 27 7 5 33 3 32 7
14 BRADLEY SHAW MI 1610 1618 4.5 N:3 1515 0 54 44 8 1 27 5 31 7
15 ZACHARY JAMES HOUGHTON MI 1220 1416 4.5 N:3 1484 0 19 16 30 22 54 33 38 7
16 MIKE NIKITIN MI 1604 1613 4.0 N:3 1386 0 10 15 0 39 2 36 0 5
17 RONALD GRZEGORCZYK MI 1629 1610 4.0 N:3 1499 0 48 41 26 2 23 22 5 7
18 DAVID SUNDEEN MI 1600 1600 4.0 N:3 1480 0 47 9 1 32 19 38 10 7
19 DIPANKAR ROY MI 1564 1570 4.0 N:3 1426 0 15 10 52 28 18 4 8 7
20 JASON ZHENG MI 1595 1569 4.0 N:4 1411 0 40 49 23 41 28 2 9 7
21 DINH DANG BUI ON 1563 1562 4.0 N:3 1470 0 43 1 47 3 40 39 6 7
22 EUGENE L MCCLURE MI 1555 1529 4.0 N:4 1300 0 64 52 28 15 0 17 40 6
23 ALAN BUI ON 1363 1371 4.0 1214 0 4 43 20 58 17 37 46 7
24 MICHAEL R ALDRICH MI 1229 1300 4.0 N:4 1357 0 28 47 43 25 60 44 39 7
25 LOREN SCHWIEBERT MI 1745 1681 3.5 N:4 1363 0 9 53 3 24 34 10 47 7
26 MAX ZHU ON 1579 1564 3.5 N:4 1507 0 49 40 17 4 9 32 11 7
27 GAURAV GIDWANI MI 1552 1539 3.5 N:4 1222 0 51 13 46 37 14 6 0 6
28 SOFIA ADINA STANESCUBELLU MI 1507 1513 3.5 N:3 1522 0 24 4 22 19 20 8 36 7
29 CHIEDOZIE OKORIE MI 1602 1508 3.5 N:4 1314 0 50 6 38 34 52 48 0 6
30 GEORGE AVERY JONES ON 1522 1444 3.5 1144 0 52 64 15 55 31 61 50 7
31 RISHI SHETTY MI 1494 1444 3.5 1260 0 58 55 64 10 30 50 14 7
32 JOSHUA PHILIP MATHEWS ON 1441 1433 3.5 N:4 1379 0 61 8 44 18 51 26 13 7
33 JADE GE MI 1449 1421 3.5 1277 0 60 12 50 36 13 15 51 7
34 MICHAEL JEFFERY THOMAS MI 1399 1400 3.5 1375 0 6 60 37 29 25 11 52 7
35 JOSHUA DAVID LEE MI 1438 1392 3.5 1150 0 46 38 56 6 57 52 48 7
36 SIDDHARTH JHA MI 1355 1367 3.5 N:4 1388 0 13 57 51 33 0 16 28 6
37 AMIYATOSH PWNANANDAM MI 980 1077 3.5 1385 0 0 5 34 27 0 23 61 5
38 BRIAN LIU MI 1423 1439 3.0 N:4 1539 0 11 35 29 12 0 18 15 6
39 JOEL R HENDON MI 1436 1413 3.0 N:4 1430 0 1 54 40 16 44 21 24 7
40 FOREST ZHANG MI 1348 1346 3.0 1391 0 20 26 39 59 21 56 22 7
41 KYLE WILLIAM MURPHY MI 1403 1341 3.0 1248 0 59 17 58 20 0 0 0 4
42 JARED GE MI 1332 1256 3.0 1150 0 12 50 57 60 61 64 56 7
43 ROBERT GLEN VASEY MI 1283 1244 3.0 1107 0 21 23 24 63 59 46 55 7
44 JUSTIN D SCHILLING MI 1199 1199 3.0 1327 0 0 14 32 53 39 24 59 6
45 DEREK YAN MI 1242 1191 3.0 1152 0 5 51 60 56 63 55 58 7
46 JACOB ALEXANDER LAVALLEY MI 377 1076 3.0 1358 0 35 7 27 50 64 43 23 7
47 ERIC WRIGHT MI 1362 1341 2.5 1392 0 18 24 21 61 8 51 25 7
48 DANIEL KHAIN MI 1382 1335 2.5 1356 0 17 63 0 52 0 29 35 5
49 MICHAEL J MARTIN MI 1291 1259 2.5 1286 0 26 20 63 64 58 0 0 5
50 SHIVAM JHA MI 1056 1111 2.5 1296 0 29 42 33 46 0 31 30 6
51 TEJAS AYYAGARI MI 1011 1097 2.5 1356 0 27 45 36 57 32 47 33 7
52 ETHAN GUO MI 935 1092 2.5 N:4 1495 0 30 22 19 48 29 35 34 7
53 JOSE C YBARRA MI 1393 1359 2.0 1345 0 0 25 0 44 0 57 0 3
54 LARRY HODGE MI 1270 1200 2.0 1206 0 14 39 61 0 15 59 64 6
55 ALEX KONG MI 1186 1163 2.0 1406 0 62 31 10 30 0 45 43 6
56 MARISA RICCI MI 1153 1140 2.0 1414 0 0 11 35 45 0 40 42 5
57 MICHAEL LU MI 1092 1079 2.0 1363 0 7 36 42 51 35 53 0 6
58 VIRAJ MOHILE MI 917 941 2.0 1391 0 31 2 41 23 49 0 45 6
59 SEAN M MC CORMICK MI 853 878 2.0 1319 0 41 0 9 40 43 54 44 6
60 JULIA SHEN MI 967 984 1.5 1330 0 33 34 45 42 24 0 0 5
61 JEZZEL FARKAS ON 955 979 1.5 1327 0 32 3 54 47 42 30 37 7
62 ASHWIN BALAJI MI 1530 1535 1.0 1186 0 55 0 0 0 0 0 0 1
63 THOMAS JOSEPH HOSMER MI 1175 1125 1.0 1350 0 2 48 49 43 45 0 0 5
64 BEN LI MI 1163 1112 1.0 1263 0 22 30 31 49 46 42 54 7 
##    Min. 1st Qu.  Median    Mean 3rd Qu.    Max. 
##    1107    1310    1382    1379    1481    1605

Visualizations

How many players per state involved at this tournmant. Count observations by group is always a good idea. We can aggregate the the number of occurence with n(). The code below computes the number of Players played by State.

As concluded from the graph that the majority of the players are from MI where 85% of players are from there.

It shows that the majority of players in the three states played their 7 games. Get the average pre-rating for each State to find out its statistics (mean, median, max, and min)

As Illustrated, the mean for ON state is higher than MI state, the max is higher fir ON as well; however, the min is nearly the same. From observation, we can exclude OH from comparasion because it has only one player and the pre-rating average value is above 1500.

As shown in the label graph that although ON has less players than MI, it has the highest pre-rating average compared to the two other states. GARY HUA has the highest average of 1600.

Group by Player Name and State to find out each state statistics (mean, median, max, and min)

Conclusion and next steps

After getting basic exploring analysis for the chess tournament, we need further information about each player to get more details about the winning factors. For example, have more details about game time for each player, age, education,..etc.

Adjusting the table header to exactly matches the user requirement.

In this section, I will remove the extra columns for Pair,Post, Pts and change the columns names to match the user requirements .

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

Test that the file is already created.

Please be sure that you set working directory in Rstudio to the current working directory.

## [1] TRUE

open the file from local machine

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

Open the file from GitHub account

Player.s.Name Player.s.State Total.Number.of.Points Player.s.Pre.Rating X.Average.Pre.Chess.Rating
GARY HUA ON 1794 6.0 1605
DAKSHESH DARURI MI 1553 6.0 1469
ADITYA BAJAJ MI 1384 6.0 1564
PATRICK H SCHILLING MI 1716 5.5 1574
HANSHI ZUO MI 1655 5.5 1501
HANSEN SONG OH 1686 5.0 1519