library ("stringr")
library ("kableExtra")

## Warning: package 'kableExtra' was built under R version 3.5.3

Raw Data

url<-read.csv( "https://raw.githubusercontent.com/uplotnik/Data607/master/Project%201.txt")
a<-url
head(a,15)

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

Data cleaning

clean_data <- a[c(4:nrow(a)),]
head(clean_data)

## [1]     1 | GARY HUA                        |6.0  |W  39|W  21|W  18|W  14|W   7|D  12|D   4|
## [2]    ON | 15445895 / R: 1794   ->1817     |N:2  |W    |B    |W    |B    |W    |B    |W    |
## [3] -----------------------------------------------------------------------------------------
## [4]     2 | DAKSHESH DARURI                 |6.0  |W  63|W  58|L   4|W  17|W  16|W  20|W   7|
## [5]    MI | 14598900 / R: 1553   ->1663     |N:2  |B    |W    |B    |W    |B    |W    |B    |
## [6] -----------------------------------------------------------------------------------------
## 131 Levels: ----------------------------------------------------------------------------------------- ...

clean_data1 <- str_replace_all(clean_data,  "\\s+",   " ")
head(clean_data1,6)

## [1] " 1 | GARY HUA |6.0 |W 39|W 21|W 18|W 14|W 7|D 12|D 4|"                                    
## [2] " ON | 15445895 / R: 1794 ->1817 |N:2 |W |B |W |B |W |B |W |"                              
## [3] "-----------------------------------------------------------------------------------------"
## [4] " 2 | DAKSHESH DARURI |6.0 |W 63|W 58|L 4|W 17|W 16|W 20|W 7|"                             
## [5] " MI | 14598900 / R: 1553 ->1663 |N:2 |B |W |B |W |B |W |B |"                              
## [6] "-----------------------------------------------------------------------------------------"

cl_data<- clean_data[seq(1, 190, 3)]
head(cl_data,2)

## [1]     1 | GARY HUA                        |6.0  |W  39|W  21|W  18|W  14|W   7|D  12|D   4|
## [2]     2 | DAKSHESH DARURI                 |6.0  |W  63|W  58|L   4|W  17|W  16|W  20|W   7|
## 131 Levels: ----------------------------------------------------------------------------------------- ...

Data extraction

Player’s ID

ID <- unlist(str_extract_all(unlist(clean_data), "\\d{1,2}(?=\\s\\|)"))
head(ID,10)

##  [1] "1"  "2"  "3"  "4"  "5"  "6"  "7"  "8"  "9"  "10"

Player’s Name

Player_name<-unlist(str_extract_all(unlist(clean_data), "([[:alpha:]]+\\s){2,}"))
head(Player_name,10)

##  [1] "GARY HUA "            "DAKSHESH DARURI "     "ADITYA BAJAJ "       
##  [4] "PATRICK H SCHILLING " "HANSHI ZUO "          "HANSEN SONG "        
##  [7] "GARY DEE SWATHELL "   "EZEKIEL HOUGHTON "    "STEFANO LEE "        
## [10] "ANVIT RAO "

Player’s State

state <- "([[:alpha:]]){2}\\s(?=\\|)"
State <- unlist(str_extract_all(unlist(clean_data), state))
head(State,10)

##  [1] "ON " "MI " "MI " "MI " "MI " "OH " "MI " "MI " "ON " "MI "

Total Points

Points <- unlist(str_extract_all(unlist(clean_data1), "\\d\\.\\d(?=\\s\\|)"))
head(Points,10)

##  [1] "6.0" "6.0" "6.0" "5.5" "5.5" "5.0" "5.0" "5.0" "5.0" "5.0"

Pre-rating

r <- unlist(str_extract_all(clean_data, "[:]([[:space:]]+)([[:alnum:]]+)([[:space:]]*)"))
r2 <- unlist(str_extract_all(unlist(str_extract_all(r, "\\d+[[:alnum:]]+")), "\\d\\d\\d+", simplify=TRUE))
Pre_Ratings <- r2[!r2[,] == " ",]
head(Pre_Ratings,10)

##  [1] "1794" "1553" "1384" "1716" "1655" "1686" "1649" "1641" "1411" "1365"

Post-rating

post_r <- unlist(str_extract_all(clean_data, "[->]\\s*[[:digit:]]+"))
Post_Rating <- unlist(str_extract_all(post_r, "[[:digit:]]+"))
head(Post_Rating,10)

##  [1] "1817" "1663" "1640" "1744" "1690" "1687" "1673" "1657" "1564" "1544"

USCFID

USCFID <- unlist(str_extract_all(clean_data, "\\d{8}"))
head(USCFID,10)

##  [1] "15445895" "14598900" "14959604" "12616049" "14601533" "15055204"
##  [7] "11146376" "15142253" "14954524" "14150362"

Opponents

cleaning <- unlist(str_extract_all(clean_data, "[WLDUH][[:blank:]]{2,3}[[:digit:]]{0,2}"))
cleanresult <- subset(cleaning, cleaning != "W   ")
Opponents <- matrix(cleanresult, nrow = 64, ncol = 7, byrow = TRUE)

## Warning in matrix(cleanresult, nrow = 64, ncol = 7, byrow = TRUE): data
## length [446] is not a sub-multiple or multiple of the number of rows [64]

head(Opponents, 5)

##      [,1]    [,2]    [,3]    [,4]    [,5]    [,6]    [,7]   
## [1,] "W  39" "W  21" "W  18" "W  14" "W   7" "D  12" "D   4"
## [2,] "W  63" "W  58" "L   4" "W  17" "W  16" "W  20" "W   7"
## [3,] "L   8" "W  61" "W  25" "W  21" "W  11" "W  13" "W  12"
## [4,] "W  23" "D  28" "W   2" "W  26" "D   5" "W  19" "D   1"
## [5,] "W  45" "W  37" "D  12" "D  13" "D   4" "W  14" "W  17"

colnames(Opponents)<- c("Round1","Round2","Round3","Round4","Round5","Round6","Round7")

Clean data

extracteddata <-data.frame(ID,Player_name,State,Points,Pre_Ratings,Post_Rating, USCFID, Opponents)
kable(head(extracteddata)) %>%
kable_styling(bootstrap_options = c("striped", "hover", "condensed" )) %>%
  column_spec(1:2, bold = T, )%>%
scroll_box(width = "500px", height = "300px")

ID	Player_name	State	Points	Pre_Ratings	Post_Rating	USCFID	Round1	Round2	Round3	Round4	Round5	Round6	Round7
1	GARY HUA	ON	6.0	1794	1817	15445895	W 39	W 21	W 18	W 14	W 7	D 12	D 4
2	DAKSHESH DARURI	MI	6.0	1553	1663	14598900	W 63	W 58	L 4	W 17	W 16	W 20	W 7
3	ADITYA BAJAJ	MI	6.0	1384	1640	14959604	L 8	W 61	W 25	W 21	W 11	W 13	W 12
4	PATRICK H SCHILLING	MI	5.5	1716	1744	12616049	W 23	D 28	W 2	W 26	D 5	W 19	D 1
5	HANSHI ZUO	MI	5.5	1655	1690	14601533	W 45	W 37	D 12	D 13	D 4	W 14	W 17
6	HANSEN SONG	OH	5.0	1686	1687	15055204	W 34	D 29	L 11	W 35	D 10	W 27	W 21

Find the Average Rating of Opponents

##  Before using a loop convert "character" to "integer"
Pre_Ratings1<-as.integer(Pre_Ratings)
ID1 <- as.integer(str_extract(cl_data, "\\d+"))

Ave<- str_extract_all(str_extract_all(cl_data, "\\d+\\|"), "\\d+")

## Warning in stri_extract_all_regex(string, pattern, simplify = simplify, :
## argument is not an atomic vector; coercing

AveOppRating <- length(cl_data)
for (i in 1:length(cl_data)) { 
  AveOppRating [i] <- round(mean(Pre_Ratings1[as.numeric(unlist(Ave[ID1[i]]))])) 
}
opData <- data.frame(AveOppRating);
head(opData,10)

##    AveOppRating
## 1          1605
## 2          1469
## 3          1564
## 4          1574
## 5          1501
## 6          1519
## 7          1372
## 8          1468
## 9          1523
## 10         1554

Final Table

finaltable<-data.frame(ID,Player_name,State,Points,Pre_Ratings,opData)
kable(finaltable) %>%
kable_styling(bootstrap_options = c("striped", "hover", "condensed"))%>%
  column_spec(1:6, bold = T, border_right = T)%>%
scroll_box(width = "800px", height = "300px")

ID	Player_name	State	Points	Pre_Ratings	AveOppRating
1	GARY HUA	ON	6.0	1794	1605
2	DAKSHESH DARURI	MI	6.0	1553	1469
3	ADITYA BAJAJ	MI	6.0	1384	1564
4	PATRICK H SCHILLING	MI	5.5	1716	1574
5	HANSHI ZUO	MI	5.5	1655	1501
6	HANSEN SONG	OH	5.0	1686	1519
7	GARY DEE SWATHELL	MI	5.0	1649	1372
8	EZEKIEL HOUGHTON	MI	5.0	1641	1468
9	STEFANO LEE	ON	5.0	1411	1523
10	ANVIT RAO	MI	5.0	1365	1554
11	CAMERON WILLIAM MC LEMAN	MI	4.5	1712	1468
12	KENNETH J TACK	MI	4.5	1663	1506
13	TORRANCE HENRY JR	MI	4.5	1666	1498
14	BRADLEY SHAW	MI	4.5	1610	1515
15	ZACHARY JAMES HOUGHTON	MI	4.5	1220	1484
16	MIKE NIKITIN	MI	4.0	1604	1386
17	RONALD GRZEGORCZYK	MI	4.0	1629	1499
18	DAVID SUNDEEN	MI	4.0	1600	1480
19	DIPANKAR ROY	MI	4.0	1564	1426
20	JASON ZHENG	MI	4.0	1595	1411
21	DINH DANG BUI	ON	4.0	1563	1470
22	EUGENE L MCCLURE	MI	4.0	1555	1300
23	ALAN BUI	ON	4.0	1363	1214
24	MICHAEL R ALDRICH	MI	4.0	1229	1357
25	LOREN SCHWIEBERT	MI	3.5	1745	1363
26	MAX ZHU	ON	3.5	1579	1507
27	GAURAV GIDWANI	MI	3.5	1552	1222
28	SOFIA ADINA	MI	3.5	1507	1522
29	CHIEDOZIE OKORIE	MI	3.5	1602	1314
30	GEORGE AVERY JONES	ON	3.5	1522	1144
31	RISHI SHETTY	MI	3.5	1494	1260
32	JOSHUA PHILIP MATHEWS	ON	3.5	1441	1379
33	JADE GE	MI	3.5	1449	1277
34	MICHAEL JEFFERY THOMAS	MI	3.5	1399	1375
35	JOSHUA DAVID LEE	MI	3.5	1438	1150
36	SIDDHARTH JHA	MI	3.5	1355	1388
37	AMIYATOSH PWNANANDAM	MI	3.5	980	1385
38	BRIAN LIU	MI	3.0	1423	1539
39	JOEL R HENDON	MI	3.0	1436	1430
40	FOREST ZHANG	MI	3.0	1348	1391
41	KYLE WILLIAM MURPHY	MI	3.0	1403	1248
42	JARED GE	MI	3.0	1332	1150
43	ROBERT GLEN VASEY	MI	3.0	1283	1107
44	JUSTIN D SCHILLING	MI	3.0	1199	1327
45	DEREK YAN	MI	3.0	1242	1152
46	JACOB ALEXANDER LAVALLEY	MI	3.0	377	1358
47	ERIC WRIGHT	MI	2.5	1362	1392
48	DANIEL KHAIN	MI	2.5	1382	1356
49	MICHAEL J MARTIN	MI	2.5	1291	1286
50	SHIVAM JHA	MI	2.5	1056	1296
51	TEJAS AYYAGARI	MI	2.5	1011	1356
52	ETHAN GUO	MI	2.5	935	1495
53	JOSE C YBARRA	MI	2.0	1393	1345
54	LARRY HODGE	MI	2.0	1270	1206
55	ALEX KONG	MI	2.0	1186	1406
56	MARISA RICCI	MI	2.0	1153	1414
57	MICHAEL LU	MI	2.0	1092	1363
58	VIRAJ MOHILE	MI	2.0	917	1391
59	SEAN M MC CORMICK	MI	2.0	853	1319
60	JULIA SHEN	MI	1.5	967	1330
61	JEZZEL FARKAS	ON	1.5	955	1327
62	ASHWIN BALAJI	MI	1.0	1530	1186
63	THOMAS JOSEPH HOSMER	MI	1.0	1175	1350
64	BEN LI	MI	1.0	1163	1263

Save in csv.file

write.table(finaltable, file = "UlianaP-TournamentInfo.csv",row.names=FALSE, na="",col.names=TRUE, sep=",")

Project 1.

Uliana Plotnikova

September 22, 2019

Raw Data

Data cleaning

Data extraction

Player’s ID

Player’s Name

Player’s State

Total Points

Pre-rating

Post-rating

USCFID

Opponents

Clean data

Find the Average Rating of Opponents

Final Table

Save in csv.file