library(RCurl)## Loading required package: bitopslibrary(stringr)
headers <- read.delim("tournamentinfo.txt", sep="|", header=FALSE, nrows = 2, skip = 1, stringsAsFactors = FALSE, skipNul = TRUE, strip.white = TRUE)
header1 <- str_trim(headers[1,])[1:10]
header2 <- str_trim(headers[2,])[1:10]
header1[1] <- "PlayerId"
header1[4:10] <- paste("Round-",header2[4:10], "-Opponent")
header2[4:10] <- paste("Round-", header2[4:10], "-Result")
headers <- c(header1,header2)
headers## [1] "PlayerId" "Player Name"
## [3] "Total" "Round- 1 -Opponent"
## [5] "Round- 2 -Opponent" "Round- 3 -Opponent"
## [7] "Round- 4 -Opponent" "Round- 5 -Opponent"
## [9] "Round- 6 -Opponent" "Round- 7 -Opponent"
## [11] "Num" "USCF ID / Rtg (Pre->Post)"
## [13] "Pts" "Round- 1 -Result"
## [15] "Round- 2 -Result" "Round- 3 -Result"
## [17] "Round- 4 -Result" "Round- 5 -Result"
## [19] "Round- 6 -Result" "Round- 7 -Result"lines<-readLines(con=file("tournamentinfo.txt"), warn=FALSE)
lines<-str_replace_all(lines, "-{2,}", " ")
count<-length(lines)
lines <- lines[4:count]
data<-read.table(text=lines, blank.lines.skip = TRUE, sep="|", header=FALSE, strip.white = TRUE, skipNul = TRUE, na.strings=c("", "NA"),as.is = TRUE)
num_players<-nrow(data)/2
oddRows <- seq(1,by=2,len=num_players)
evenRows <- seq(2,by=2,len=num_players)
rows<-seq(1,by=1,len=num_players)
data1<-data[oddRows,][1:10]
row.names(data1)<-rows
data2<-data[evenRows,][1:10]
row.names(data2)<-rows
dataJoined<-merge(data1,data2,by=0)[2:21]
colnames(dataJoined) <- headers
dataJoined## PlayerId Player Name Total Round- 1 -Opponent
## 1 1 GARY HUA 6.0 W 39
## 2 10 ANVIT RAO 5.0 D 16
## 3 11 CAMERON WILLIAM MC LEMAN 4.5 D 38
## 4 12 KENNETH J TACK 4.5 W 42
## 5 13 TORRANCE HENRY JR 4.5 W 36
## 6 14 BRADLEY SHAW 4.5 W 54
## 7 15 ZACHARY JAMES HOUGHTON 4.5 D 19
## 8 16 MIKE NIKITIN 4.0 D 10
## 9 17 RONALD GRZEGORCZYK 4.0 W 48
## 10 18 DAVID SUNDEEN 4.0 W 47
## 11 19 DIPANKAR ROY 4.0 D 15
## 12 2 DAKSHESH DARURI 6.0 W 63
## 13 20 JASON ZHENG 4.0 L 40
## 14 21 DINH DANG BUI 4.0 W 43
## 15 22 EUGENE L MCCLURE 4.0 W 64
## 16 23 ALAN BUI 4.0 L 4
## 17 24 MICHAEL R ALDRICH 4.0 L 28
## 18 25 LOREN SCHWIEBERT 3.5 L 9
## 19 26 MAX ZHU 3.5 W 49
## 20 27 GAURAV GIDWANI 3.5 W 51
## 21 28 SOFIA ADINA STANESCU-BELLU 3.5 W 24
## 22 29 CHIEDOZIE OKORIE 3.5 W 50
## 23 3 ADITYA BAJAJ 6.0 L 8
## 24 30 GEORGE AVERY JONES 3.5 L 52
## 25 31 RISHI SHETTY 3.5 L 58
## 26 32 JOSHUA PHILIP MATHEWS 3.5 W 61
## 27 33 JADE GE 3.5 W 60
## 28 34 MICHAEL JEFFERY THOMAS 3.5 L 6
## 29 35 JOSHUA DAVID LEE 3.5 L 46
## 30 36 SIDDHARTH JHA 3.5 L 13
## 31 37 AMIYATOSH PWNANANDAM 3.5 B
## 32 38 BRIAN LIU 3.0 D 11
## 33 39 JOEL R HENDON 3.0 L 1
## 34 4 PATRICK H SCHILLING 5.5 W 23
## 35 40 FOREST ZHANG 3.0 W 20
## 36 41 KYLE WILLIAM MURPHY 3.0 W 59
## 37 42 JARED GE 3.0 L 12
## 38 43 ROBERT GLEN VASEY 3.0 L 21
## 39 44 JUSTIN D SCHILLING 3.0 B
## 40 45 DEREK YAN 3.0 L 5
## 41 46 JACOB ALEXANDER LAVALLEY 3.0 W 35
## 42 47 ERIC WRIGHT 2.5 L 18
## 43 48 DANIEL KHAIN 2.5 L 17
## 44 49 MICHAEL J MARTIN 2.5 L 26
## 45 5 HANSHI ZUO 5.5 W 45
## 46 50 SHIVAM JHA 2.5 L 29
## 47 51 TEJAS AYYAGARI 2.5 L 27
## 48 52 ETHAN GUO 2.5 W 30
## 49 53 JOSE C YBARRA 2.0 H
## 50 54 LARRY HODGE 2.0 L 14
## 51 55 ALEX KONG 2.0 L 62
## 52 56 MARISA RICCI 2.0 H
## 53 57 MICHAEL LU 2.0 L 7
## 54 58 VIRAJ MOHILE 2.0 W 31
## 55 59 SEAN M MC CORMICK 2.0 L 41
## 56 6 HANSEN SONG 5.0 W 34
## 57 60 JULIA SHEN 1.5 L 33
## 58 61 JEZZEL FARKAS 1.5 L 32
## 59 62 ASHWIN BALAJI 1.0 W 55
## 60 63 THOMAS JOSEPH HOSMER 1.0 L 2
## 61 64 BEN LI 1.0 L 22
## 62 7 GARY DEE SWATHELL 5.0 W 57
## 63 8 EZEKIEL HOUGHTON 5.0 W 3
## 64 9 STEFANO LEE 5.0 W 25
## Round- 2 -Opponent Round- 3 -Opponent Round- 4 -Opponent
## 1 W 21 W 18 W 14
## 2 L 19 W 55 W 31
## 3 W 56 W 6 L 7
## 4 W 33 D 5 W 38
## 5 W 27 L 7 D 5
## 6 W 44 W 8 L 1
## 7 L 16 W 30 L 22
## 8 W 15 H W 39
## 9 W 41 L 26 L 2
## 10 W 9 L 1 W 32
## 11 W 10 W 52 D 28
## 12 W 58 L 4 W 17
## 13 W 49 W 23 W 41
## 14 L 1 W 47 L 3
## 15 D 52 L 28 W 15
## 16 W 43 L 20 W 58
## 17 L 47 W 43 L 25
## 18 W 53 L 3 W 24
## 19 W 40 W 17 L 4
## 20 L 13 W 46 W 37
## 21 D 4 W 22 D 19
## 22 D 6 L 38 L 34
## 23 W 61 W 25 W 21
## 24 D 64 L 15 W 55
## 25 D 55 W 64 L 10
## 26 L 8 W 44 L 18
## 27 L 12 W 50 D 36
## 28 W 60 L 37 W 29
## 29 L 38 W 56 L 6
## 30 W 57 W 51 D 33
## 31 L 5 W 34 L 27
## 32 W 35 W 29 L 12
## 33 W 54 W 40 L 16
## 34 D 28 W 2 W 26
## 35 L 26 L 39 W 59
## 36 L 17 W 58 L 20
## 37 L 50 L 57 D 60
## 38 L 23 L 24 W 63
## 39 L 14 L 32 W 53
## 40 L 51 D 60 L 56
## 41 L 7 L 27 L 50
## 42 W 24 L 21 W 61
## 43 W 63 H D 52
## 44 L 20 D 63 D 64
## 45 W 37 D 12 D 13
## 46 W 42 L 33 W 46
## 47 W 45 L 36 W 57
## 48 D 22 L 19 D 48
## 49 L 25 H L 44
## 50 L 39 L 61 B
## 51 D 31 L 10 L 30
## 52 L 11 L 35 W 45
## 53 L 36 W 42 L 51
## 54 L 2 L 41 L 23
## 55 B L 9 L 40
## 56 D 29 L 11 W 35
## 57 L 34 D 45 D 42
## 58 L 3 W 54 L 47
## 59 U U U
## 60 L 48 D 49 L 43
## 61 D 30 L 31 D 49
## 62 W 46 W 13 W 11
## 63 W 32 L 14 L 9
## 64 L 18 W 59 W 8
## Round- 5 -Opponent Round- 6 -Opponent Round- 7 -Opponent Num
## 1 W 7 D 12 D 4 ON
## 2 D 6 W 25 W 18 MI
## 3 L 3 W 34 W 26 MI
## 4 H D 1 L 3 MI
## 5 W 33 L 3 W 32 MI
## 6 D 27 L 5 W 31 MI
## 7 W 54 W 33 W 38 MI
## 8 L 2 W 36 U MI
## 9 W 23 W 22 L 5 MI
## 10 L 19 W 38 L 10 MI
## 11 W 18 L 4 L 8 MI
## 12 W 16 W 20 W 7 MI
## 13 W 28 L 2 L 9 MI
## 14 W 40 W 39 L 6 ON
## 15 H L 17 W 40 MI
## 16 L 17 W 37 W 46 ON
## 17 W 60 W 44 W 39 MI
## 18 D 34 L 10 W 47 MI
## 19 L 9 D 32 L 11 ON
## 20 D 14 L 6 U MI
## 21 L 20 L 8 D 36 MI
## 22 W 52 W 48 U MI
## 23 W 11 W 13 W 12 MI
## 24 L 31 W 61 W 50 ON
## 25 W 30 W 50 L 14 MI
## 26 W 51 D 26 L 13 ON
## 27 L 13 L 15 W 51 MI
## 28 D 25 L 11 W 52 MI
## 29 W 57 D 52 W 48 MI
## 30 H L 16 D 28 MI
## 31 H L 23 W 61 MI
## 32 H L 18 L 15 MI
## 33 W 44 L 21 L 24 MI
## 34 D 5 W 19 D 1 MI
## 35 L 21 W 56 L 22 MI
## 36 X U U MI
## 37 D 61 W 64 W 56 MI
## 38 W 59 L 46 W 55 MI
## 39 L 39 L 24 W 59 MI
## 40 W 63 D 55 W 58 MI
## 41 W 64 W 43 L 23 MI
## 42 L 8 D 51 L 25 MI
## 43 H L 29 L 35 MI
## 44 W 58 H U MI
## 45 D 4 W 14 W 17 MI
## 46 H L 31 L 30 MI
## 47 L 32 D 47 L 33 MI
## 48 L 29 D 35 L 34 MI
## 49 U W 57 U MI
## 50 L 15 L 59 W 64 MI
## 51 B D 45 L 43 MI
## 52 H L 40 L 42 MI
## 53 L 35 L 53 B MI
## 54 L 49 B L 45 MI
## 55 L 43 W 54 L 44 MI
## 56 D 10 W 27 W 21 OH
## 57 L 24 H U MI
## 58 D 42 L 30 L 37 ON
## 59 U U U MI
## 60 L 45 H U MI
## 61 L 46 L 42 L 54 MI
## 62 L 1 W 9 L 2 MI
## 63 W 47 W 28 W 19 MI
## 64 W 26 L 7 W 20 ON
## USCF ID / Rtg (Pre->Post) Pts Round- 1 -Result Round- 2 -Result
## 1 15445895 / R: 1794 ->1817 N:2 W B
## 2 14150362 / R: 1365 ->1544 N:3 W W
## 3 12581589 / R: 1712 ->1696 N:3 B W
## 4 12681257 / R: 1663 ->1670 N:3 W B
## 5 15082995 / R: 1666 ->1662 N:3 B W
## 6 10131499 / R: 1610 ->1618 N:3 W B
## 7 15619130 / R: 1220P13->1416P20 N:3 B B
## 8 10295068 / R: 1604 ->1613 N:3 B W
## 9 10297702 / R: 1629 ->1610 N:3 W B
## 10 11342094 / R: 1600 ->1600 N:3 B W
## 11 14862333 / R: 1564 ->1570 N:3 W B
## 12 14598900 / R: 1553 ->1663 N:2 B W
## 13 14529060 / R: 1595 ->1569 N:4 W B
## 14 15495066 / R: 1563P22->1562 N:3 B W
## 15 12405534 / R: 1555 ->1529 N:4 W B
## 16 15030142 / R: 1363 ->1371 <NA> B W
## 17 13469010 / R: 1229 ->1300 N:4 B W
## 18 12486656 / R: 1745 ->1681 N:4 B W
## 19 15131520 / R: 1579 ->1564 N:4 B W
## 20 14476567 / R: 1552 ->1539 N:4 W B
## 21 14882954 / R: 1507 ->1513 N:3 W W
## 22 15323285 / R: 1602P6 ->1508P12 N:4 B W
## 23 14959604 / R: 1384 ->1640 N:2 W B
## 24 12577178 / R: 1522 ->1444 <NA> W B
## 25 15131618 / R: 1494 ->1444 <NA> B W
## 26 14073750 / R: 1441 ->1433 N:4 W B
## 27 14691842 / R: 1449 ->1421 <NA> B W
## 28 15051807 / R: 1399 ->1400 <NA> B W
## 29 14601397 / R: 1438 ->1392 <NA> W W
## 30 14773163 / R: 1355 ->1367 N:4 W B
## 31 15489571 / R: 980P12->1077P17 <NA> <NA> B
## 32 15108523 / R: 1423 ->1439 N:4 W B
## 33 12923035 / R: 1436P23->1413 N:4 B W
## 34 12616049 / R: 1716 ->1744 N:2 W B
## 35 14892710 / R: 1348 ->1346 <NA> B B
## 36 15761443 / R: 1403P5 ->1341P9 <NA> B W
## 37 14462326 / R: 1332 ->1256 <NA> B W
## 38 14101068 / R: 1283 ->1244 <NA> W B
## 39 15323504 / R: 1199 ->1199 <NA> <NA> W
## 40 15372807 / R: 1242 ->1191 <NA> W B
## 41 15490981 / R: 377P3 ->1076P10 <NA> B W
## 42 12533115 / R: 1362 ->1341 <NA> W B
## 43 14369165 / R: 1382 ->1335 <NA> B W
## 44 12531685 / R: 1291P12->1259P17 <NA> W W
## 45 14601533 / R: 1655 ->1690 N:2 B W
## 46 14773178 / R: 1056 ->1111 <NA> W B
## 47 15205474 / R: 1011 ->1097 <NA> B W
## 48 14918803 / R: 935 ->1092 N:4 B W
## 49 12578849 / R: 1393 ->1359 <NA> <NA> B
## 50 12836773 / R: 1270 ->1200 <NA> B B
## 51 15412571 / R: 1186 ->1163 <NA> W B
## 52 14679887 / R: 1153 ->1140 <NA> <NA> B
## 53 15113330 / R: 1092 ->1079 <NA> B W
## 54 14700365 / R: 917 -> 941 <NA> W B
## 55 12841036 / R: 853 -> 878 <NA> W <NA>
## 56 15055204 / R: 1686 ->1687 N:3 W B
## 57 14579262 / R: 967 -> 984 <NA> W B
## 58 15771592 / R: 955P11-> 979P18 <NA> B W
## 59 15219542 / R: 1530 ->1535 <NA> B <NA>
## 60 15057092 / R: 1175 ->1125 <NA> W B
## 61 15006561 / R: 1163 ->1112 <NA> B W
## 62 11146376 / R: 1649 ->1673 N:3 W B
## 63 15142253 / R: 1641P17->1657P24 N:3 B W
## 64 14954524 / R: 1411 ->1564 N:2 W B
## Round- 3 -Result Round- 4 -Result Round- 5 -Result Round- 6 -Result
## 1 W B W B
## 2 B B W B
## 3 B W B W
## 4 W B <NA> W
## 5 B B W W
## 6 W W B B
## 7 W W B B
## 8 <NA> B W B
## 9 W B W B
## 10 B W B W
## 11 W B W W
## 12 B W B W
## 13 W B W B
## 14 B W W B
## 15 W B <NA> W
## 16 B W B W
## 17 B B W W
## 18 B W B W
## 19 B W B W
## 20 W B W B
## 21 B W B B
## 22 B W W B
## 23 W B W B
## 24 B W W B
## 25 B W B W
## 26 W B W B
## 27 B W B W
## 28 B B W B
## 29 B W B B
## 30 W B <NA> W
## 31 W W <NA> B
## 32 W W <NA> B
## 33 B W B W
## 34 W B W B
## 35 W W B W
## 36 B W <NA> <NA>
## 37 B B W W
## 38 W W B B
## 39 B B W B
## 40 W B W B
## 41 B W B W
## 42 W B W B
## 43 <NA> B <NA> W
## 44 B W B <NA>
## 45 B W B W
## 46 W B <NA> B
## 47 B W B W
## 48 B W B W
## 49 <NA> W <NA> W
## 50 W <NA> W B
## 51 W B <NA> W
## 52 W W <NA> B
## 53 W B W B
## 54 W B W <NA>
## 55 B B W W
## 56 W B B W
## 57 B W B <NA>
## 58 B W B W
## 59 <NA> <NA> <NA> <NA>
## 60 W B B <NA>
## 61 W B W B
## 62 W B B W
## 63 B W B W
## 64 W B W B
## Round- 7 -Result
## 1 W
## 2 W
## 3 B
## 4 B
## 5 B
## 6 W
## 7 W
## 8 <NA>
## 9 W
## 10 B
## 11 B
## 12 B
## 13 W
## 14 W
## 15 B
## 16 B
## 17 B
## 18 B
## 19 W
## 20 <NA>
## 21 W
## 22 <NA>
## 23 W
## 24 B
## 25 B
## 26 W
## 27 B
## 28 W
## 29 W
## 30 B
## 31 W
## 32 B
## 33 W
## 34 B
## 35 W
## 36 <NA>
## 37 B
## 38 W
## 39 W
## 40 W
## 41 W
## 42 W
## 43 B
## 44 <NA>
## 45 B
## 46 W
## 47 W
## 48 B
## 49 <NA>
## 50 W
## 51 B
## 52 W
## 53 <NA>
## 54 B
## 55 B
## 56 B
## 57 <NA>
## 58 B
## 59 <NA>
## 60 <NA>
## 61 B
## 62 W
## 63 W
## 64 B#install.packages('RMySQL', type = 'source')
library(RMySQL)## Loading required package: DBIcon <- dbConnect(MySQL(),
user = 'root',
password = 'yahoo77_',
host = 'localhost',
dbname='Movies')
dbSendQuery(conn=con,statement="Drop Database If Exists Project1")## <MySQLResult:182913536,0,0>dbSendQuery(conn=con,statement="Create Database Project1")## <MySQLResult:NA,0,1>dbSendQuery(conn=con,statement="Use Project1")## <MySQLResult:3,0,2>dbSendQuery(conn=con,statement="DROP TABLE IF EXISTS Tournament")## <MySQLResult:1,0,3>dbWriteTable(conn=con, name="Tournament", value=dataJoined, fields=header, row.names=FALSE)## [1] TRUEdbSendQuery(conn=con,statement="DROP TABLE IF EXISTS Tournament_Interim")## <MySQLResult:1699967849,0,7>dbSendQuery(conn=con,statement="CREATE TABLE PROJECT1.TOURNAMENT_INTERIM(
PLAYERID VARCHAR(3),
R1ID VARCHAR(3),
R2ID VARCHAR(3),
R3ID VARCHAR(3),
R4ID VARCHAR(3),
R5ID VARCHAR(3),
R6ID VARCHAR(3),
R7ID VARCHAR(3)
)
SELECT PLAYERID,
SUBSTR(T1.`Round- 1 -Opponent`, 3) R1ID,
SUBSTR(T1.`Round- 2 -Opponent`, 3) R2ID,
SUBSTR(T1.`Round- 3 -Opponent`, 3) R3ID,
SUBSTR(T1.`Round- 4 -Opponent`, 3) R4ID,
SUBSTR(T1.`Round- 5 -Opponent`, 3) R5ID,
SUBSTR(T1.`Round- 6 -Opponent`, 3) R6ID,
SUBSTR(T1.`Round- 7 -Opponent`, 3) R7ID
from project1.tournament T1")## <MySQLResult:110738576,0,8>Results <- dbGetQuery(conn = con, statement = "Select `Player Name` , Num , Total , SUBSTR(`USCF ID / Rtg (Pre->Post)`,15,4) Pre from project1.tournament")
write.csv(Results, file="TournamentResults.csv", quote=FALSE, row.names=FALSE)