Load all libraries

##install.packages("kableExtra")
library(knitr)
library(kableExtra)
library(dplyr)
library(tidyr)
library(RCurl)
library(ggplot2)

Import text file from github

df <- read.table(text = getURL("https://raw.githubusercontent.com/ltcancel/DATA607Project1/master/RawData.txt"), header = TRUE, sep = "|", fill = TRUE, comment.char = "-")

##diplay data
kable(df[1:10,]) %>%
  kable_styling(bootstrap_options = c("striped", "hover"), full_width = F, font_size = 12)
Pair Player.Name Total Round Round.1 Round.2 Round.3 Round.4 Round.5 Round.6 X
Num USCF ID / Rtg (Pre NA NA
1 GARY HUA 6.0 W 39 W 21 W 18 W 14 W 7 D 12 D 4 NA
ON 15445895 / R: 1794 NA NA
2 DAKSHESH DARURI 6.0 W 63 W 58 L 4 W 17 W 16 W 20 W 7 NA
MI 14598900 / R: 1553 NA NA
3 ADITYA BAJAJ 6.0 L 8 W 61 W 25 W 21 W 11 W 13 W 12 NA
MI 14959604 / R: 1384 NA NA
4 PATRICK H SCHILLING 5.5 W 23 D 28 W 2 W 26 D 5 W 19 D 1 NA
MI 12616049 / R: 1716 NA NA
5 HANSHI ZUO 5.5 W 45 W 37 D 12 D 13 D 4 W 14 W 17 NA

Filter even and odd rows

##Even rows
even_rows <- df %>% dplyr::filter(row_number() %% 2 == 0) ##select even rows
kable(even_rows[1:10,]) %>%
  kable_styling(bootstrap_options = c("striped", "hover"), full_width = F, font_size = 12)
Pair Player.Name Total Round Round.1 Round.2 Round.3 Round.4 Round.5 Round.6 X
1 GARY HUA 6.0 W 39 W 21 W 18 W 14 W 7 D 12 D 4 NA
2 DAKSHESH DARURI 6.0 W 63 W 58 L 4 W 17 W 16 W 20 W 7 NA
3 ADITYA BAJAJ 6.0 L 8 W 61 W 25 W 21 W 11 W 13 W 12 NA
4 PATRICK H SCHILLING 5.5 W 23 D 28 W 2 W 26 D 5 W 19 D 1 NA
5 HANSHI ZUO 5.5 W 45 W 37 D 12 D 13 D 4 W 14 W 17 NA
6 HANSEN SONG 5.0 W 34 D 29 L 11 W 35 D 10 W 27 W 21 NA
7 GARY DEE SWATHELL 5.0 W 57 W 46 W 13 W 11 L 1 W 9 L 2 NA
8 EZEKIEL HOUGHTON 5.0 W 3 W 32 L 14 L 9 W 47 W 28 W 19 NA
9 STEFANO LEE 5.0 W 25 L 18 W 59 W 8 W 26 L 7 W 20 NA
10 ANVIT RAO 5.0 D 16 L 19 W 55 W 31 D 6 W 25 W 18 NA 
##Odd rows
odd_rows <- df %>% dplyr::filter(row_number() %% 2 == 1) ##select odd rows
kable(odd_rows[1:10,]) %>%
  kable_styling(bootstrap_options = c("striped", "hover"), full_width = F, font_size = 12)
Pair Player.Name Total Round Round.1 Round.2 Round.3 Round.4 Round.5 Round.6 X
Num USCF ID / Rtg (Pre NA NA
ON 15445895 / R: 1794 NA NA
MI 14598900 / R: 1553 NA NA
MI 14959604 / R: 1384 NA NA
MI 12616049 / R: 1716 NA NA
MI 14601533 / R: 1655 NA NA
OH 15055204 / R: 1686 NA NA
MI 11146376 / R: 1649 NA NA
MI 15142253 / R: 1641P17 NA NA
ON 14954524 / R: 1411 NA NA

Create dataframe with even rows only

##Selecting only rows needed 
clean.odd.rows <- odd_rows[2:65,1:2]

##Separate USCF ID from rating
clean.odd.rows <- separate(data = clean.odd.rows, col = Player.Name, into = c("USCF ID", "Rating"), sep = " / R: ")

##Separate columns
state <- clean.odd.rows$Pair
USCF_ID <- clean.odd.rows$`USCF ID`
rating <- clean.odd.rows$Rating

##Rename columns
colnames(clean.odd.rows) <- c("State", "USCF ID", "Rating")

##Merge both dataframes
player.data.cleaned <- cbind(even_rows, state, USCF_ID, rating)
kable(player.data.cleaned[1:10,]) %>%
  kable_styling(bootstrap_options = c("striped", "hover"), full_width = F, font_size = 12)
Pair Player.Name Total Round Round.1 Round.2 Round.3 Round.4 Round.5 Round.6 X state USCF_ID rating
1 GARY HUA 6.0 W 39 W 21 W 18 W 14 W 7 D 12 D 4 NA ON 15445895 1794
2 DAKSHESH DARURI 6.0 W 63 W 58 L 4 W 17 W 16 W 20 W 7 NA MI 14598900 1553
3 ADITYA BAJAJ 6.0 L 8 W 61 W 25 W 21 W 11 W 13 W 12 NA MI 14959604 1384
4 PATRICK H SCHILLING 5.5 W 23 D 28 W 2 W 26 D 5 W 19 D 1 NA MI 12616049 1716
5 HANSHI ZUO 5.5 W 45 W 37 D 12 D 13 D 4 W 14 W 17 NA MI 14601533 1655
6 HANSEN SONG 5.0 W 34 D 29 L 11 W 35 D 10 W 27 W 21 NA OH 15055204 1686
7 GARY DEE SWATHELL 5.0 W 57 W 46 W 13 W 11 L 1 W 9 L 2 NA MI 11146376 1649
8 EZEKIEL HOUGHTON 5.0 W 3 W 32 L 14 L 9 W 47 W 28 W 19 NA MI 15142253 1641P17
9 STEFANO LEE 5.0 W 25 L 18 W 59 W 8 W 26 L 7 W 20 NA ON 14954524 1411
10 ANVIT RAO 5.0 D 16 L 19 W 55 W 31 D 6 W 25 W 18 NA MI 14150362 1365

separate wins and numbers

round.one <- separate(data = player.data.cleaned, col = Round, into = c("1.1","1.2"))
round.two <- separate(data = round.one, col = Round.1, into = c("2.1","2.2"))
round.three <- separate(data = round.two, col = Round.2, into = c("3.1","3.2"))
round.four <- separate(data = round.three, col = Round.3, into = c("4.1","4.2"))
round.five <- separate(data = round.four, col = Round.4, into = c("5.1","5.2"))
round.six <- separate(data = round.five, col = Round.5, into = c("6.1","6.2"))
player.data <- separate(data = round.six, col = Round.6, into = c("7.1","7.2"))

colnames(player.data) <- c("Pair_Num","Player_Name","Total_Pts","R1","R1_Pts","R2","R2_Pts","R3","R3_Pts","R4","R4_Pts","R5","R5_Pts","R6","R6_Pts","R7","R7_Pts","X","State","USCF_ID","Rating")
kable(player.data[1:10,]) %>%
  kable_styling(bootstrap_options = c("striped", "hover"), full_width = F, position = "center", font_size = 12)
Pair_Num Player_Name Total_Pts R1 R1_Pts R2 R2_Pts R3 R3_Pts R4 R4_Pts R5 R5_Pts R6 R6_Pts R7 R7_Pts X State USCF_ID Rating
1 GARY HUA 6.0 W 39 W 21 W 18 W 14 W 7 D 12 D 4 NA ON 15445895 1794
2 DAKSHESH DARURI 6.0 W 63 W 58 L 4 W 17 W 16 W 20 W 7 NA MI 14598900 1553
3 ADITYA BAJAJ 6.0 L 8 W 61 W 25 W 21 W 11 W 13 W 12 NA MI 14959604 1384
4 PATRICK H SCHILLING 5.5 W 23 D 28 W 2 W 26 D 5 W 19 D 1 NA MI 12616049 1716
5 HANSHI ZUO 5.5 W 45 W 37 D 12 D 13 D 4 W 14 W 17 NA MI 14601533 1655
6 HANSEN SONG 5.0 W 34 D 29 L 11 W 35 D 10 W 27 W 21 NA OH 15055204 1686
7 GARY DEE SWATHELL 5.0 W 57 W 46 W 13 W 11 L 1 W 9 L 2 NA MI 11146376 1649
8 EZEKIEL HOUGHTON 5.0 W 3 W 32 L 14 L 9 W 47 W 28 W 19 NA MI 15142253 1641P17
9 STEFANO LEE 5.0 W 25 L 18 W 59 W 8 W 26 L 7 W 20 NA ON 14954524 1411
10 ANVIT RAO 5.0 D 16 L 19 W 55 W 31 D 6 W 25 W 18 NA MI 14150362 1365