DATA 607 Project 1

Load neccessary packages

library(tidyverse)

## -- Attaching packages --------------------------------------- tidyverse 1.3.2 --
## v ggplot2 3.4.0      v purrr   1.0.1 
## v tibble  3.1.6      v dplyr   1.0.10
## v tidyr   1.2.0      v stringr 1.5.0 
## v readr   2.1.2      v forcats 0.5.2

## Warning: package 'tibble' was built under R version 4.0.5

## Warning: package 'tidyr' was built under R version 4.0.5

## Warning: package 'readr' was built under R version 4.0.5

## -- Conflicts ------------------------------------------ tidyverse_conflicts() --
## x dplyr::filter() masks stats::filter()
## x dplyr::lag()    masks stats::lag()

Data Analysis on Chess Players

For this project, a text file of chess tournament was given (link). An analysis will be conducted to provide the player’s statistic and output it into a csv file.

Read Text File

chess_URL <-"https://raw.githubusercontent.com/eddiexunyc/607_project_1/main/chess_tournment_data.txt"

chess_data <-read.delim(chess_URL, skip =1, header = TRUE, sep = "|")

head(chess_data, 10)

##                                                                                         Pair
## 1                                                                                      Num  
## 2  -----------------------------------------------------------------------------------------
## 3                                                                                         1 
## 4                                                                                        ON 
## 5  -----------------------------------------------------------------------------------------
## 6                                                                                         2 
## 7                                                                                        MI 
## 8  -----------------------------------------------------------------------------------------
## 9                                                                                         3 
## 10                                                                                       MI 
##                          Player.Name Total Round Round.1 Round.2 Round.3
## 1   USCF ID / Rtg (Pre->Post)         Pts    1       2       3       4  
## 2                                                                       
## 3   GARY HUA                         6.0   W  39   W  21   W  18   W  14
## 4   15445895 / R: 1794   ->1817      N:2   W       B       W       B    
## 5                                                                       
## 6   DAKSHESH DARURI                  6.0   W  63   W  58   L   4   W  17
## 7   14598900 / R: 1553   ->1663      N:2   B       W       B       W    
## 8                                                                       
## 9   ADITYA BAJAJ                     6.0   L   8   W  61   W  25   W  21
## 10  14959604 / R: 1384   ->1640      N:2   W       B       W       B    
##    Round.4 Round.5 Round.6  X
## 1      5       6       7   NA
## 2                          NA
## 3    W   7   D  12   D   4 NA
## 4    W       B       W     NA
## 5                          NA
## 6    W  16   W  20   W   7 NA
## 7    B       W       B     NA
## 8                          NA
## 9    W  11   W  13   W  12 NA
## 10   W       B       W     NA

#check the data type of the dataframe
str(chess_data)

## 'data.frame':    194 obs. of  11 variables:
##  $ Pair       : chr  " Num  " "-----------------------------------------------------------------------------------------" "    1 " "   ON " ...
##  $ Player.Name: chr  " USCF ID / Rtg (Pre->Post)       " "" " GARY HUA                        " " 15445895 / R: 1794   ->1817     " ...
##  $ Total      : chr  " Pts " "" "6.0  " "N:2  " ...
##  $ Round      : chr  "  1  " "" "W  39" "W    " ...
##  $ Round.1    : chr  "  2  " "" "W  21" "B    " ...
##  $ Round.2    : chr  "  3  " "" "W  18" "W    " ...
##  $ Round.3    : chr  "  4  " "" "W  14" "B    " ...
##  $ Round.4    : chr  "  5  " "" "W   7" "W    " ...
##  $ Round.5    : chr  "  6  " "" "D  12" "B    " ...
##  $ Round.6    : chr  "  7  " "" "D   4" "W    " ...
##  $ X          : logi  NA NA NA NA NA NA ...

Data Cleaning

#remove the dash
chess_data <-chess_data[!grepl("-----------------------------------------------------------------------------------------", chess_data$Pair),]

#combine the 2 headers and delete a row
names(chess_data) <-paste(names(chess_data), chess_data[1, ], sep = "_")
chess_data <-chess_data[-1,]

#rename the column header
colnames(chess_data) <- c("Pair Num", "Player Name USCF ID/RTG(Pre>Post)", "Total Points", "Round 1", "Round 2", "Round 3", "Round 4", "Round 5", "Round 6", "Round 7")

knitr::kable(chess_data)

	Pair Num	Player Name USCF ID/RTG(Pre>Post)	Total Points	Round 1	Round 2	Round 3	Round 4	Round 5	Round 6	Round 7	NA
3	1	GARY HUA	6.0	W 39	W 21	W 18	W 14	W 7	D 12	D 4	NA
4	ON	15445895 / R: 1794 ->1817	N:2	W	B	W	B	W	B	W	NA
6	2	DAKSHESH DARURI	6.0	W 63	W 58	L 4	W 17	W 16	W 20	W 7	NA
7	MI	14598900 / R: 1553 ->1663	N:2	B	W	B	W	B	W	B	NA
9	3	ADITYA BAJAJ	6.0	L 8	W 61	W 25	W 21	W 11	W 13	W 12	NA
10	MI	14959604 / R: 1384 ->1640	N:2	W	B	W	B	W	B	W	NA
12	4	PATRICK H SCHILLING	5.5	W 23	D 28	W 2	W 26	D 5	W 19	D 1	NA
13	MI	12616049 / R: 1716 ->1744	N:2	W	B	W	B	W	B	B	NA
15	5	HANSHI ZUO	5.5	W 45	W 37	D 12	D 13	D 4	W 14	W 17	NA
16	MI	14601533 / R: 1655 ->1690	N:2	B	W	B	W	B	W	B	NA
18	6	HANSEN SONG	5.0	W 34	D 29	L 11	W 35	D 10	W 27	W 21	NA
19	OH	15055204 / R: 1686 ->1687	N:3	W	B	W	B	B	W	B	NA
21	7	GARY DEE SWATHELL	5.0	W 57	W 46	W 13	W 11	L 1	W 9	L 2	NA
22	MI	11146376 / R: 1649 ->1673	N:3	W	B	W	B	B	W	W	NA
24	8	EZEKIEL HOUGHTON	5.0	W 3	W 32	L 14	L 9	W 47	W 28	W 19	NA
25	MI	15142253 / R: 1641P17->1657P24	N:3	B	W	B	W	B	W	W	NA
27	9	STEFANO LEE	5.0	W 25	L 18	W 59	W 8	W 26	L 7	W 20	NA
28	ON	14954524 / R: 1411 ->1564	N:2	W	B	W	B	W	B	B	NA
30	10	ANVIT RAO	5.0	D 16	L 19	W 55	W 31	D 6	W 25	W 18	NA
31	MI	14150362 / R: 1365 ->1544	N:3	W	W	B	B	W	B	W	NA
33	11	CAMERON WILLIAM MC LEMAN	4.5	D 38	W 56	W 6	L 7	L 3	W 34	W 26	NA
34	MI	12581589 / R: 1712 ->1696	N:3	B	W	B	W	B	W	B	NA
36	12	KENNETH J TACK	4.5	W 42	W 33	D 5	W 38	H	D 1	L 3	NA
37	MI	12681257 / R: 1663 ->1670	N:3	W	B	W	B		W	B	NA
39	13	TORRANCE HENRY JR	4.5	W 36	W 27	L 7	D 5	W 33	L 3	W 32	NA
40	MI	15082995 / R: 1666 ->1662	N:3	B	W	B	B	W	W	B	NA
42	14	BRADLEY SHAW	4.5	W 54	W 44	W 8	L 1	D 27	L 5	W 31	NA
43	MI	10131499 / R: 1610 ->1618	N:3	W	B	W	W	B	B	W	NA
45	15	ZACHARY JAMES HOUGHTON	4.5	D 19	L 16	W 30	L 22	W 54	W 33	W 38	NA
46	MI	15619130 / R: 1220P13->1416P20	N:3	B	B	W	W	B	B	W	NA
48	16	MIKE NIKITIN	4.0	D 10	W 15	H	W 39	L 2	W 36	U	NA
49	MI	10295068 / R: 1604 ->1613	N:3	B	W		B	W	B		NA
51	17	RONALD GRZEGORCZYK	4.0	W 48	W 41	L 26	L 2	W 23	W 22	L 5	NA
52	MI	10297702 / R: 1629 ->1610	N:3	W	B	W	B	W	B	W	NA
54	18	DAVID SUNDEEN	4.0	W 47	W 9	L 1	W 32	L 19	W 38	L 10	NA
55	MI	11342094 / R: 1600 ->1600	N:3	B	W	B	W	B	W	B	NA
57	19	DIPANKAR ROY	4.0	D 15	W 10	W 52	D 28	W 18	L 4	L 8	NA
58	MI	14862333 / R: 1564 ->1570	N:3	W	B	W	B	W	W	B	NA
60	20	JASON ZHENG	4.0	L 40	W 49	W 23	W 41	W 28	L 2	L 9	NA
61	MI	14529060 / R: 1595 ->1569	N:4	W	B	W	B	W	B	W	NA
63	21	DINH DANG BUI	4.0	W 43	L 1	W 47	L 3	W 40	W 39	L 6	NA
64	ON	15495066 / R: 1563P22->1562	N:3	B	W	B	W	W	B	W	NA
66	22	EUGENE L MCCLURE	4.0	W 64	D 52	L 28	W 15	H	L 17	W 40	NA
67	MI	12405534 / R: 1555 ->1529	N:4	W	B	W	B		W	B	NA
69	23	ALAN BUI	4.0	L 4	W 43	L 20	W 58	L 17	W 37	W 46	NA
70	ON	15030142 / R: 1363 ->1371		B	W	B	W	B	W	B	NA
72	24	MICHAEL R ALDRICH	4.0	L 28	L 47	W 43	L 25	W 60	W 44	W 39	NA
73	MI	13469010 / R: 1229 ->1300	N:4	B	W	B	B	W	W	B	NA
75	25	LOREN SCHWIEBERT	3.5	L 9	W 53	L 3	W 24	D 34	L 10	W 47	NA
76	MI	12486656 / R: 1745 ->1681	N:4	B	W	B	W	B	W	B	NA
78	26	MAX ZHU	3.5	W 49	W 40	W 17	L 4	L 9	D 32	L 11	NA
79	ON	15131520 / R: 1579 ->1564	N:4	B	W	B	W	B	W	W	NA
81	27	GAURAV GIDWANI	3.5	W 51	L 13	W 46	W 37	D 14	L 6	U	NA
82	MI	14476567 / R: 1552 ->1539	N:4	W	B	W	B	W	B		NA
84	28	SOFIA ADINA STANESCU-BELLU	3.5	W 24	D 4	W 22	D 19	L 20	L 8	D 36	NA
85	MI	14882954 / R: 1507 ->1513	N:3	W	W	B	W	B	B	W	NA
87	29	CHIEDOZIE OKORIE	3.5	W 50	D 6	L 38	L 34	W 52	W 48	U	NA
88	MI	15323285 / R: 1602P6 ->1508P12	N:4	B	W	B	W	W	B		NA
90	30	GEORGE AVERY JONES	3.5	L 52	D 64	L 15	W 55	L 31	W 61	W 50	NA
91	ON	12577178 / R: 1522 ->1444		W	B	B	W	W	B	B	NA
93	31	RISHI SHETTY	3.5	L 58	D 55	W 64	L 10	W 30	W 50	L 14	NA
94	MI	15131618 / R: 1494 ->1444		B	W	B	W	B	W	B	NA
96	32	JOSHUA PHILIP MATHEWS	3.5	W 61	L 8	W 44	L 18	W 51	D 26	L 13	NA
97	ON	14073750 / R: 1441 ->1433	N:4	W	B	W	B	W	B	W	NA
99	33	JADE GE	3.5	W 60	L 12	W 50	D 36	L 13	L 15	W 51	NA
100	MI	14691842 / R: 1449 ->1421		B	W	B	W	B	W	B	NA
102	34	MICHAEL JEFFERY THOMAS	3.5	L 6	W 60	L 37	W 29	D 25	L 11	W 52	NA
103	MI	15051807 / R: 1399 ->1400		B	W	B	B	W	B	W	NA
105	35	JOSHUA DAVID LEE	3.5	L 46	L 38	W 56	L 6	W 57	D 52	W 48	NA
106	MI	14601397 / R: 1438 ->1392		W	W	B	W	B	B	W	NA
108	36	SIDDHARTH JHA	3.5	L 13	W 57	W 51	D 33	H	L 16	D 28	NA
109	MI	14773163 / R: 1355 ->1367	N:4	W	B	W	B		W	B	NA
111	37	AMIYATOSH PWNANANDAM	3.5	B	L 5	W 34	L 27	H	L 23	W 61	NA
112	MI	15489571 / R: 980P12->1077P17			B	W	W		B	W	NA
114	38	BRIAN LIU	3.0	D 11	W 35	W 29	L 12	H	L 18	L 15	NA
115	MI	15108523 / R: 1423 ->1439	N:4	W	B	W	W		B	B	NA
117	39	JOEL R HENDON	3.0	L 1	W 54	W 40	L 16	W 44	L 21	L 24	NA
118	MI	12923035 / R: 1436P23->1413	N:4	B	W	B	W	B	W	W	NA
120	40	FOREST ZHANG	3.0	W 20	L 26	L 39	W 59	L 21	W 56	L 22	NA
121	MI	14892710 / R: 1348 ->1346		B	B	W	W	B	W	W	NA
123	41	KYLE WILLIAM MURPHY	3.0	W 59	L 17	W 58	L 20	X	U	U	NA
124	MI	15761443 / R: 1403P5 ->1341P9		B	W	B	W				NA
126	42	JARED GE	3.0	L 12	L 50	L 57	D 60	D 61	W 64	W 56	NA
127	MI	14462326 / R: 1332 ->1256		B	W	B	B	W	W	B	NA
129	43	ROBERT GLEN VASEY	3.0	L 21	L 23	L 24	W 63	W 59	L 46	W 55	NA
130	MI	14101068 / R: 1283 ->1244		W	B	W	W	B	B	W	NA
132	44	JUSTIN D SCHILLING	3.0	B	L 14	L 32	W 53	L 39	L 24	W 59	NA
133	MI	15323504 / R: 1199 ->1199			W	B	B	W	B	W	NA
135	45	DEREK YAN	3.0	L 5	L 51	D 60	L 56	W 63	D 55	W 58	NA
136	MI	15372807 / R: 1242 ->1191		W	B	W	B	W	B	W	NA
138	46	JACOB ALEXANDER LAVALLEY	3.0	W 35	L 7	L 27	L 50	W 64	W 43	L 23	NA
139	MI	15490981 / R: 377P3 ->1076P10		B	W	B	W	B	W	W	NA
141	47	ERIC WRIGHT	2.5	L 18	W 24	L 21	W 61	L 8	D 51	L 25	NA
142	MI	12533115 / R: 1362 ->1341		W	B	W	B	W	B	W	NA
144	48	DANIEL KHAIN	2.5	L 17	W 63	H	D 52	H	L 29	L 35	NA
145	MI	14369165 / R: 1382 ->1335		B	W		B		W	B	NA
147	49	MICHAEL J MARTIN	2.5	L 26	L 20	D 63	D 64	W 58	H	U	NA
148	MI	12531685 / R: 1291P12->1259P17		W	W	B	W	B			NA
150	50	SHIVAM JHA	2.5	L 29	W 42	L 33	W 46	H	L 31	L 30	NA
151	MI	14773178 / R: 1056 ->1111		W	B	W	B		B	W	NA
153	51	TEJAS AYYAGARI	2.5	L 27	W 45	L 36	W 57	L 32	D 47	L 33	NA
154	MI	15205474 / R: 1011 ->1097		B	W	B	W	B	W	W	NA
156	52	ETHAN GUO	2.5	W 30	D 22	L 19	D 48	L 29	D 35	L 34	NA
157	MI	14918803 / R: 935 ->1092	N:4	B	W	B	W	B	W	B	NA
159	53	JOSE C YBARRA	2.0	H	L 25	H	L 44	U	W 57	U	NA
160	MI	12578849 / R: 1393 ->1359			B		W		W		NA
162	54	LARRY HODGE	2.0	L 14	L 39	L 61	B	L 15	L 59	W 64	NA
163	MI	12836773 / R: 1270 ->1200		B	B	W		W	B	W	NA
165	55	ALEX KONG	2.0	L 62	D 31	L 10	L 30	B	D 45	L 43	NA
166	MI	15412571 / R: 1186 ->1163		W	B	W	B		W	B	NA
168	56	MARISA RICCI	2.0	H	L 11	L 35	W 45	H	L 40	L 42	NA
169	MI	14679887 / R: 1153 ->1140			B	W	W		B	W	NA
171	57	MICHAEL LU	2.0	L 7	L 36	W 42	L 51	L 35	L 53	B	NA
172	MI	15113330 / R: 1092 ->1079		B	W	W	B	W	B		NA
174	58	VIRAJ MOHILE	2.0	W 31	L 2	L 41	L 23	L 49	B	L 45	NA
175	MI	14700365 / R: 917 -> 941		W	B	W	B	W		B	NA
177	59	SEAN M MC CORMICK	2.0	L 41	B	L 9	L 40	L 43	W 54	L 44	NA
178	MI	12841036 / R: 853 -> 878		W		B	B	W	W	B	NA
180	60	JULIA SHEN	1.5	L 33	L 34	D 45	D 42	L 24	H	U	NA
181	MI	14579262 / R: 967 -> 984		W	B	B	W	B			NA
183	61	JEZZEL FARKAS	1.5	L 32	L 3	W 54	L 47	D 42	L 30	L 37	NA
184	ON	15771592 / R: 955P11-> 979P18		B	W	B	W	B	W	B	NA
186	62	ASHWIN BALAJI	1.0	W 55	U	U	U	U	U	U	NA
187	MI	15219542 / R: 1530 ->1535		B							NA
189	63	THOMAS JOSEPH HOSMER	1.0	L 2	L 48	D 49	L 43	L 45	H	U	NA
190	MI	15057092 / R: 1175 ->1125		W	B	W	B	B			NA
192	64	BEN LI	1.0	L 22	D 30	L 31	D 49	L 46	L 42	L 54	NA
193	MI	15006561 / R: 1163 ->1112		B	W	W	B	W	B	B	NA

Data Formating

## split the data based on sequence
player_rating <- chess_data[seq(2, nrow(chess_data), 2),]
player_info <- chess_data[seq(1, nrow(chess_data), 2),]

## extract individual chess data
player_pre_rating <- str_sub_all(player_rating$`Player Name USCF ID/RTG(Pre>Post)`,15, 19)
player_uscf_id <- str_sub_all(player_rating$`Player Name USCF ID/RTG(Pre>Post)`,1, 8)
player_state <- str_sub_all(player_rating$`Pair Num`)
player_round <- lapply(player_info[,4:10], function(x) str_extract(x, '\\d+'))

## merge chess data into one dataframe
chess_sub_data <- data.frame(unlist(player_uscf_id), unlist(player_state), unlist(player_pre_rating), player_round)
chess_new_data <- cbind(player_info[,1:3], chess_sub_data)

## rename the column and reorder the column by name
colnames(chess_new_data) <- c("ID", "Player Name", "Total Points", "Player USCF ID", "Player State", "Player Pre-Rating", "Round 1", "Round 2", "Round 3", "Round 4", "Round 5", "Round 6", "Round 7")

knitr::kable(chess_new_data)

	ID	Player Name	Total Points	Player USCF ID	Player State	Player Pre-Rating	Round 1	Round 2	Round 3	Round 4	Round 5	Round 6	Round 7
3	1	GARY HUA	6.0	1544589	ON	1794	39	21	18	14	7	12	4
6	2	DAKSHESH DARURI	6.0	1459890	MI	1553	63	58	4	17	16	20	7
9	3	ADITYA BAJAJ	6.0	1495960	MI	1384	8	61	25	21	11	13	12
12	4	PATRICK H SCHILLING	5.5	1261604	MI	1716	23	28	2	26	5	19	1
15	5	HANSHI ZUO	5.5	1460153	MI	1655	45	37	12	13	4	14	17
18	6	HANSEN SONG	5.0	1505520	OH	1686	34	29	11	35	10	27	21
21	7	GARY DEE SWATHELL	5.0	1114637	MI	1649	57	46	13	11	1	9	2
24	8	EZEKIEL HOUGHTON	5.0	1514225	MI	1641	3	32	14	9	47	28	19
27	9	STEFANO LEE	5.0	1495452	ON	1411	25	18	59	8	26	7	20
30	10	ANVIT RAO	5.0	1415036	MI	1365	16	19	55	31	6	25	18
33	11	CAMERON WILLIAM MC LEMAN	4.5	1258158	MI	1712	38	56	6	7	3	34	26
36	12	KENNETH J TACK	4.5	1268125	MI	1663	42	33	5	38	NA	1	3
39	13	TORRANCE HENRY JR	4.5	1508299	MI	1666	36	27	7	5	33	3	32
42	14	BRADLEY SHAW	4.5	1013149	MI	1610	54	44	8	1	27	5	31
45	15	ZACHARY JAMES HOUGHTON	4.5	1561913	MI	1220	19	16	30	22	54	33	38
48	16	MIKE NIKITIN	4.0	1029506	MI	1604	10	15	NA	39	2	36	NA
51	17	RONALD GRZEGORCZYK	4.0	1029770	MI	1629	48	41	26	2	23	22	5
54	18	DAVID SUNDEEN	4.0	1134209	MI	1600	47	9	1	32	19	38	10
57	19	DIPANKAR ROY	4.0	1486233	MI	1564	15	10	52	28	18	4	8
60	20	JASON ZHENG	4.0	1452906	MI	1595	40	49	23	41	28	2	9
63	21	DINH DANG BUI	4.0	1549506	ON	1563	43	1	47	3	40	39	6
66	22	EUGENE L MCCLURE	4.0	1240553	MI	1555	64	52	28	15	NA	17	40
69	23	ALAN BUI	4.0	1503014	ON	1363	4	43	20	58	17	37	46
72	24	MICHAEL R ALDRICH	4.0	1346901	MI	1229	28	47	43	25	60	44	39
75	25	LOREN SCHWIEBERT	3.5	1248665	MI	1745	9	53	3	24	34	10	47
78	26	MAX ZHU	3.5	1513152	ON	1579	49	40	17	4	9	32	11
81	27	GAURAV GIDWANI	3.5	1447656	MI	1552	51	13	46	37	14	6	NA
84	28	SOFIA ADINA STANESCU-BELLU	3.5	1488295	MI	1507	24	4	22	19	20	8	36
87	29	CHIEDOZIE OKORIE	3.5	1532328	MI	1602	50	6	38	34	52	48	NA
90	30	GEORGE AVERY JONES	3.5	1257717	ON	1522	52	64	15	55	31	61	50
93	31	RISHI SHETTY	3.5	1513161	MI	1494	58	55	64	10	30	50	14
96	32	JOSHUA PHILIP MATHEWS	3.5	1407375	ON	1441	61	8	44	18	51	26	13
99	33	JADE GE	3.5	1469184	MI	1449	60	12	50	36	13	15	51
102	34	MICHAEL JEFFERY THOMAS	3.5	1505180	MI	1399	6	60	37	29	25	11	52
105	35	JOSHUA DAVID LEE	3.5	1460139	MI	1438	46	38	56	6	57	52	48
108	36	SIDDHARTH JHA	3.5	1477316	MI	1355	13	57	51	33	NA	16	28
111	37	AMIYATOSH PWNANANDAM	3.5	1548957	MI	980	NA	5	34	27	NA	23	61
114	38	BRIAN LIU	3.0	1510852	MI	1423	11	35	29	12	NA	18	15
117	39	JOEL R HENDON	3.0	1292303	MI	1436	1	54	40	16	44	21	24
120	40	FOREST ZHANG	3.0	1489271	MI	1348	20	26	39	59	21	56	22
123	41	KYLE WILLIAM MURPHY	3.0	1576144	MI	1403	59	17	58	20	NA	NA	NA
126	42	JARED GE	3.0	1446232	MI	1332	12	50	57	60	61	64	56
129	43	ROBERT GLEN VASEY	3.0	1410106	MI	1283	21	23	24	63	59	46	55
132	44	JUSTIN D SCHILLING	3.0	1532350	MI	1199	NA	14	32	53	39	24	59
135	45	DEREK YAN	3.0	1537280	MI	1242	5	51	60	56	63	55	58
138	46	JACOB ALEXANDER LAVALLEY	3.0	1549098	MI	377	35	7	27	50	64	43	23
141	47	ERIC WRIGHT	2.5	1253311	MI	1362	18	24	21	61	8	51	25
144	48	DANIEL KHAIN	2.5	1436916	MI	1382	17	63	NA	52	NA	29	35
147	49	MICHAEL J MARTIN	2.5	1253168	MI	1291	26	20	63	64	58	NA	NA
150	50	SHIVAM JHA	2.5	1477317	MI	1056	29	42	33	46	NA	31	30
153	51	TEJAS AYYAGARI	2.5	1520547	MI	1011	27	45	36	57	32	47	33
156	52	ETHAN GUO	2.5	1491880	MI	935	30	22	19	48	29	35	34
159	53	JOSE C YBARRA	2.0	1257884	MI	1393	NA	25	NA	44	NA	57	NA
162	54	LARRY HODGE	2.0	1283677	MI	1270	14	39	61	NA	15	59	64
165	55	ALEX KONG	2.0	1541257	MI	1186	62	31	10	30	NA	45	43
168	56	MARISA RICCI	2.0	1467988	MI	1153	NA	11	35	45	NA	40	42
171	57	MICHAEL LU	2.0	1511333	MI	1092	7	36	42	51	35	53	NA
174	58	VIRAJ MOHILE	2.0	1470036	MI	917	31	2	41	23	49	NA	45
177	59	SEAN M MC CORMICK	2.0	1284103	MI	853	41	NA	9	40	43	54	44
180	60	JULIA SHEN	1.5	1457926	MI	967	33	34	45	42	24	NA	NA
183	61	JEZZEL FARKAS	1.5	1577159	ON	955	32	3	54	47	42	30	37
186	62	ASHWIN BALAJI	1.0	1521954	MI	1530	55	NA	NA	NA	NA	NA	NA
189	63	THOMAS JOSEPH HOSMER	1.0	1505709	MI	1175	2	48	49	43	45	NA	NA
192	64	BEN LI	1.0	1500656	MI	1163	22	30	31	49	46	42	54

##Data Calculations

#create a matrix and use a nested for loop to find and replace ID with Pre-rating
new_round <- chess_new_data[7:13]

match_round <- matrix(unlist(new_round), nrow = 64, ncol = 7)

for(x in 1:nrow(match_round)){
  
  for(y in 1:ncol(match_round)){
    
    if(!is.na(match_round[x, y])){ 
      index <- match_round[x, y] 
      index <- strtoi(index, base=0L) 
      match_round[x,y] = strtoi(player_pre_rating[index])
    }
  }
}
#change the matrix to a numeric data type
calc_round <- matrix(as.numeric(match_round), nrow = 64, ncol = 7)

#calculate the average of the opponent's pre-rating

chess_new_data['Opponent Avg Rating'] <- round(rowMeans(calc_round, na.rm = TRUE))

knitr::kable(chess_new_data)

	ID	Player Name	Total Points	Player USCF ID	Player State	Player Pre-Rating	Round 1	Round 2	Round 3	Round 4	Round 5	Round 6	Round 7	Opponent Avg Rating
3	1	GARY HUA	6.0	1544589	ON	1794	39	21	18	14	7	12	4	1605
6	2	DAKSHESH DARURI	6.0	1459890	MI	1553	63	58	4	17	16	20	7	1469
9	3	ADITYA BAJAJ	6.0	1495960	MI	1384	8	61	25	21	11	13	12	1564
12	4	PATRICK H SCHILLING	5.5	1261604	MI	1716	23	28	2	26	5	19	1	1574
15	5	HANSHI ZUO	5.5	1460153	MI	1655	45	37	12	13	4	14	17	1501
18	6	HANSEN SONG	5.0	1505520	OH	1686	34	29	11	35	10	27	21	1519
21	7	GARY DEE SWATHELL	5.0	1114637	MI	1649	57	46	13	11	1	9	2	1372
24	8	EZEKIEL HOUGHTON	5.0	1514225	MI	1641	3	32	14	9	47	28	19	1468
27	9	STEFANO LEE	5.0	1495452	ON	1411	25	18	59	8	26	7	20	1523
30	10	ANVIT RAO	5.0	1415036	MI	1365	16	19	55	31	6	25	18	1554
33	11	CAMERON WILLIAM MC LEMAN	4.5	1258158	MI	1712	38	56	6	7	3	34	26	1468
36	12	KENNETH J TACK	4.5	1268125	MI	1663	42	33	5	38	NA	1	3	1506
39	13	TORRANCE HENRY JR	4.5	1508299	MI	1666	36	27	7	5	33	3	32	1498
42	14	BRADLEY SHAW	4.5	1013149	MI	1610	54	44	8	1	27	5	31	1515
45	15	ZACHARY JAMES HOUGHTON	4.5	1561913	MI	1220	19	16	30	22	54	33	38	1484
48	16	MIKE NIKITIN	4.0	1029506	MI	1604	10	15	NA	39	2	36	NA	1386
51	17	RONALD GRZEGORCZYK	4.0	1029770	MI	1629	48	41	26	2	23	22	5	1499
54	18	DAVID SUNDEEN	4.0	1134209	MI	1600	47	9	1	32	19	38	10	1480
57	19	DIPANKAR ROY	4.0	1486233	MI	1564	15	10	52	28	18	4	8	1426
60	20	JASON ZHENG	4.0	1452906	MI	1595	40	49	23	41	28	2	9	1411
63	21	DINH DANG BUI	4.0	1549506	ON	1563	43	1	47	3	40	39	6	1470
66	22	EUGENE L MCCLURE	4.0	1240553	MI	1555	64	52	28	15	NA	17	40	1300
69	23	ALAN BUI	4.0	1503014	ON	1363	4	43	20	58	17	37	46	1214
72	24	MICHAEL R ALDRICH	4.0	1346901	MI	1229	28	47	43	25	60	44	39	1357
75	25	LOREN SCHWIEBERT	3.5	1248665	MI	1745	9	53	3	24	34	10	47	1363
78	26	MAX ZHU	3.5	1513152	ON	1579	49	40	17	4	9	32	11	1507
81	27	GAURAV GIDWANI	3.5	1447656	MI	1552	51	13	46	37	14	6	NA	1222
84	28	SOFIA ADINA STANESCU-BELLU	3.5	1488295	MI	1507	24	4	22	19	20	8	36	1522
87	29	CHIEDOZIE OKORIE	3.5	1532328	MI	1602	50	6	38	34	52	48	NA	1314
90	30	GEORGE AVERY JONES	3.5	1257717	ON	1522	52	64	15	55	31	61	50	1144
93	31	RISHI SHETTY	3.5	1513161	MI	1494	58	55	64	10	30	50	14	1260
96	32	JOSHUA PHILIP MATHEWS	3.5	1407375	ON	1441	61	8	44	18	51	26	13	1379
99	33	JADE GE	3.5	1469184	MI	1449	60	12	50	36	13	15	51	1277
102	34	MICHAEL JEFFERY THOMAS	3.5	1505180	MI	1399	6	60	37	29	25	11	52	1375
105	35	JOSHUA DAVID LEE	3.5	1460139	MI	1438	46	38	56	6	57	52	48	1150
108	36	SIDDHARTH JHA	3.5	1477316	MI	1355	13	57	51	33	NA	16	28	1388
111	37	AMIYATOSH PWNANANDAM	3.5	1548957	MI	980	NA	5	34	27	NA	23	61	1385
114	38	BRIAN LIU	3.0	1510852	MI	1423	11	35	29	12	NA	18	15	1539
117	39	JOEL R HENDON	3.0	1292303	MI	1436	1	54	40	16	44	21	24	1430
120	40	FOREST ZHANG	3.0	1489271	MI	1348	20	26	39	59	21	56	22	1391
123	41	KYLE WILLIAM MURPHY	3.0	1576144	MI	1403	59	17	58	20	NA	NA	NA	1248
126	42	JARED GE	3.0	1446232	MI	1332	12	50	57	60	61	64	56	1150
129	43	ROBERT GLEN VASEY	3.0	1410106	MI	1283	21	23	24	63	59	46	55	1107
132	44	JUSTIN D SCHILLING	3.0	1532350	MI	1199	NA	14	32	53	39	24	59	1327
135	45	DEREK YAN	3.0	1537280	MI	1242	5	51	60	56	63	55	58	1152
138	46	JACOB ALEXANDER LAVALLEY	3.0	1549098	MI	377	35	7	27	50	64	43	23	1358
141	47	ERIC WRIGHT	2.5	1253311	MI	1362	18	24	21	61	8	51	25	1392
144	48	DANIEL KHAIN	2.5	1436916	MI	1382	17	63	NA	52	NA	29	35	1356
147	49	MICHAEL J MARTIN	2.5	1253168	MI	1291	26	20	63	64	58	NA	NA	1286
150	50	SHIVAM JHA	2.5	1477317	MI	1056	29	42	33	46	NA	31	30	1296
153	51	TEJAS AYYAGARI	2.5	1520547	MI	1011	27	45	36	57	32	47	33	1356
156	52	ETHAN GUO	2.5	1491880	MI	935	30	22	19	48	29	35	34	1495
159	53	JOSE C YBARRA	2.0	1257884	MI	1393	NA	25	NA	44	NA	57	NA	1345
162	54	LARRY HODGE	2.0	1283677	MI	1270	14	39	61	NA	15	59	64	1206
165	55	ALEX KONG	2.0	1541257	MI	1186	62	31	10	30	NA	45	43	1406
168	56	MARISA RICCI	2.0	1467988	MI	1153	NA	11	35	45	NA	40	42	1414
171	57	MICHAEL LU	2.0	1511333	MI	1092	7	36	42	51	35	53	NA	1363
174	58	VIRAJ MOHILE	2.0	1470036	MI	917	31	2	41	23	49	NA	45	1391
177	59	SEAN M MC CORMICK	2.0	1284103	MI	853	41	NA	9	40	43	54	44	1319
180	60	JULIA SHEN	1.5	1457926	MI	967	33	34	45	42	24	NA	NA	1330
183	61	JEZZEL FARKAS	1.5	1577159	ON	955	32	3	54	47	42	30	37	1327
186	62	ASHWIN BALAJI	1.0	1521954	MI	1530	55	NA	NA	NA	NA	NA	NA	1186
189	63	THOMAS JOSEPH HOSMER	1.0	1505709	MI	1175	2	48	49	43	45	NA	NA	1350
192	64	BEN LI	1.0	1500656	MI	1163	22	30	31	49	46	42	54	1263

#create a output dataframe with selected data
chess_output <- subset(chess_new_data, select = c("Player Name", "Total Points", "Player State", "Player Pre-Rating", "Opponent Avg Rating"))

col_order <- c("Player Name", "Player State", "Total Points", "Player Pre-Rating", "Opponent Avg Rating")
chess_output <- chess_output[, col_order]

#export the dataframe to a CSV file
write.csv(chess_output, "final_chess.csv", row.names = FALSE)
knitr::kable(chess_output)

	Player Name	Player State	Total Points	Player Pre-Rating	Opponent Avg Rating
3	GARY HUA	ON	6.0	1794	1605
6	DAKSHESH DARURI	MI	6.0	1553	1469
9	ADITYA BAJAJ	MI	6.0	1384	1564
12	PATRICK H SCHILLING	MI	5.5	1716	1574
15	HANSHI ZUO	MI	5.5	1655	1501
18	HANSEN SONG	OH	5.0	1686	1519
21	GARY DEE SWATHELL	MI	5.0	1649	1372
24	EZEKIEL HOUGHTON	MI	5.0	1641	1468
27	STEFANO LEE	ON	5.0	1411	1523
30	ANVIT RAO	MI	5.0	1365	1554
33	CAMERON WILLIAM MC LEMAN	MI	4.5	1712	1468
36	KENNETH J TACK	MI	4.5	1663	1506
39	TORRANCE HENRY JR	MI	4.5	1666	1498
42	BRADLEY SHAW	MI	4.5	1610	1515
45	ZACHARY JAMES HOUGHTON	MI	4.5	1220	1484
48	MIKE NIKITIN	MI	4.0	1604	1386
51	RONALD GRZEGORCZYK	MI	4.0	1629	1499
54	DAVID SUNDEEN	MI	4.0	1600	1480
57	DIPANKAR ROY	MI	4.0	1564	1426
60	JASON ZHENG	MI	4.0	1595	1411
63	DINH DANG BUI	ON	4.0	1563	1470
66	EUGENE L MCCLURE	MI	4.0	1555	1300
69	ALAN BUI	ON	4.0	1363	1214
72	MICHAEL R ALDRICH	MI	4.0	1229	1357
75	LOREN SCHWIEBERT	MI	3.5	1745	1363
78	MAX ZHU	ON	3.5	1579	1507
81	GAURAV GIDWANI	MI	3.5	1552	1222
84	SOFIA ADINA STANESCU-BELLU	MI	3.5	1507	1522
87	CHIEDOZIE OKORIE	MI	3.5	1602	1314
90	GEORGE AVERY JONES	ON	3.5	1522	1144
93	RISHI SHETTY	MI	3.5	1494	1260
96	JOSHUA PHILIP MATHEWS	ON	3.5	1441	1379
99	JADE GE	MI	3.5	1449	1277
102	MICHAEL JEFFERY THOMAS	MI	3.5	1399	1375
105	JOSHUA DAVID LEE	MI	3.5	1438	1150
108	SIDDHARTH JHA	MI	3.5	1355	1388
111	AMIYATOSH PWNANANDAM	MI	3.5	980	1385
114	BRIAN LIU	MI	3.0	1423	1539
117	JOEL R HENDON	MI	3.0	1436	1430
120	FOREST ZHANG	MI	3.0	1348	1391
123	KYLE WILLIAM MURPHY	MI	3.0	1403	1248
126	JARED GE	MI	3.0	1332	1150
129	ROBERT GLEN VASEY	MI	3.0	1283	1107
132	JUSTIN D SCHILLING	MI	3.0	1199	1327
135	DEREK YAN	MI	3.0	1242	1152
138	JACOB ALEXANDER LAVALLEY	MI	3.0	377	1358
141	ERIC WRIGHT	MI	2.5	1362	1392
144	DANIEL KHAIN	MI	2.5	1382	1356
147	MICHAEL J MARTIN	MI	2.5	1291	1286
150	SHIVAM JHA	MI	2.5	1056	1296
153	TEJAS AYYAGARI	MI	2.5	1011	1356
156	ETHAN GUO	MI	2.5	935	1495
159	JOSE C YBARRA	MI	2.0	1393	1345
162	LARRY HODGE	MI	2.0	1270	1206
165	ALEX KONG	MI	2.0	1186	1406
168	MARISA RICCI	MI	2.0	1153	1414
171	MICHAEL LU	MI	2.0	1092	1363
174	VIRAJ MOHILE	MI	2.0	917	1391
177	SEAN M MC CORMICK	MI	2.0	853	1319
180	JULIA SHEN	MI	1.5	967	1330
183	JEZZEL FARKAS	ON	1.5	955	1327
186	ASHWIN BALAJI	MI	1.0	1530	1186
189	THOMAS JOSEPH HOSMER	MI	1.0	1175	1350
192	BEN LI	MI	1.0	1163	1263

DATA 607 Project 1

2023-02-12

Load neccessary packages

Data Analysis on Chess Players

Read Text File

Data Cleaning

Data Formating