library(openintro)
## Loading required package: airports
## Loading required package: cherryblossom
## Loading required package: usdata
library(tidyverse)
## ── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
## ✔ dplyr 1.1.0 ✔ readr 2.1.4
## ✔ forcats 1.0.0 ✔ stringr 1.5.0
## ✔ ggplot2 3.4.2 ✔ tibble 3.1.8
## ✔ lubridate 1.9.2 ✔ tidyr 1.3.0
## ✔ purrr 1.0.1
## ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
## ✖ dplyr::filter() masks stats::filter()
## ✖ dplyr::lag() masks stats::lag()
## ℹ Use the ]8;;http://conflicted.r-lib.org/conflicted package]8;; to force all conflicts to become errors
library(stringr)
library(readr)
###Project 1 In this project, you’re given a text file with chess tournament results where the information has some structure. Your job is to create an R Markdown file that generates a .CSV file (that could for example be imported into a SQL database) with the following information for all of the players: Player’s Name, Player’s State, Total Number of Points, Player’s Pre-Rating, and Average Pre Chess Rating of Opponents For the first player, the information would be: Gary Hua, ON, 6.0, 1794, 1605 1605 was calculated by using the pre-tournament opponents’ ratings of 1436, 1563, 1600, 1610, 1649, 1663, 1716, and dividing by the total number of games played. If you have questions about the meaning of the data or the results, please post them on the discussion forum. Data science, like chess, is a game of back and forth… The chess rating system (invented by a Minnesota statistician named Arpad Elo) has been used in many other contexts, including assessing relative strength of employment candidates by human resource departments. You may substitute another text file (or set of text files, or data scraped from web pages) of similar or greater complexity, and create your own assignment and solution. You may work in a small team. All of your code should be in an R markdown file (and published to rpubs.com); with your data accessible for the person running the script.
tournamentinfo <- 'https://raw.githubusercontent.com/KanizSanzida/DATA607/main/tournamentinfo.txt'
dfTournament <- read_csv(file = tournamentinfo,col_names = FALSE)
## Rows: 196 Columns: 1
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr (1): X1
##
## ℹ Use `spec()` to retrieve the full column specification for this data.
## ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
head(tournamentinfo)
## [1] "https://raw.githubusercontent.com/KanizSanzida/DATA607/main/tournamentinfo.txt"
tail(tournamentinfo)
## [1] "https://raw.githubusercontent.com/KanizSanzida/DATA607/main/tournamentinfo.txt"
summary(tournamentinfo)
## Length Class Mode
## 1 character character
using sep = “|” to create a new column after every “|” and name them according to their content creates a great first step towards a data frame.
rawtbl <- read.csv("https://raw.githubusercontent.com/KanizSanzida/DATA607/main/tournamentinfo.txt", sep = "|", stringsAsFactors=FALSE,
col.names = c("RankNState", "PlayerName", "TotalPts", "Round1", "Round2", "Round3",
"Round4", "Round5", "Round6", "Round7", "emptycol"))
We clean up the raw data by: (1) eliminating the final, empty column; and (2) removing any rows that indicate each player’s separation from the others, which are displayed in the text file as a row of repeated dashes. (3) We eliminate the headers from rows 1 and 2 of the original text file (.txt).
rawtbldropempty <- rawtbl[,-11]
rawtblnolines <- rawtbldropempty[!grepl("----", rawtbldropempty$RankNState),]
rawF<- rawtblnolines[-c(1:2),]
rawF[1:2,]
## RankNState PlayerName TotalPts Round1 Round2 Round3
## 4 1 GARY HUA 6.0 W 39 W 21 W 18
## 5 ON 15445895 / R: 1794 ->1817 N:2 W B W
## Round4 Round5 Round6 Round7
## 4 W 14 W 7 D 12 D 4
## 5 B W B W
We create a new data frame to house all data from the initial data frame in a clean format adding two new columns for splitting out the State and Rating
dimen <- dim(rawF)
cleantbl <- data.frame(matrix(nrow = dimen[1]/2, ncol = dimen[2]+2))
names(cleantbl) <- c("Rank","Player","TotalPoints","Round1", "Round2", "Round3",
"Round4", "Round5", "Round6", "Round7","State","Rating")
Since each player has information spread across two rows as of right now, as we’ve seen above, we consolidate all of that information into a single row.
len <- nrow(cleantbl)
j=1
for(i in 1:len){
k=i*2
cleantbl[i,1:10] <- rawF[j,]
cleantbl[i,11:12] <- rawF[k,1:2]
j=2*i+1
}
cleantbl[1,]
## Rank Player TotalPoints Round1 Round2 Round3
## 1 1 GARY HUA 6.0 W 39 W 21 W 18
## Round4 Round5 Round6 Round7 State Rating
## 1 W 14 W 7 D 12 D 4 ON 15445895 / R: 1794 ->1817
Since the starting rating and the rank of each opponent are not in their own column, as can be seen, we make additional columns that only include the player’s starting rating and the rank of each opponent.
cleantbl$RatingStart <- gsub('^.*R:\\s*|\\s*-.*$', '', cleantbl$Rating)
cleantbl$RatingStart<- gsub("P.*", "\\1", cleantbl$RatingStart)
cleantbl$R1 <- gsub('\\D+','', cleantbl$Round1)
cleantbl$R2 <- gsub('\\D+','', cleantbl$Round2)
cleantbl$R3 <- gsub('\\D+','', cleantbl$Round3)
cleantbl$R4 <- gsub('\\D+','', cleantbl$Round4)
cleantbl$R5 <- gsub('\\D+','', cleantbl$Round5)
cleantbl$R6 <- gsub('\\D+','', cleantbl$Round6)
cleantbl$R7 <- gsub('\\D+','', cleantbl$Round7)
cleantbl[1,]
## Rank Player TotalPoints Round1 Round2 Round3
## 1 1 GARY HUA 6.0 W 39 W 21 W 18
## Round4 Round5 Round6 Round7 State Rating
## 1 W 14 W 7 D 12 D 4 ON 15445895 / R: 1794 ->1817
## RatingStart R1 R2 R3 R4 R5 R6 R7
## 1 1794 39 21 18 14 7 12 4
The starting rating (ELO) of each component for each player is the final piece of information we add to the new columns, and we then compute the average of these new columns.
cleantbl$OppR1 <- as.numeric(cleantbl$RatingStart[as.numeric(cleantbl$R1)])
cleantbl$OppR2 <- as.numeric(cleantbl$RatingStart[as.numeric(cleantbl$R2)])
cleantbl$OppR3 <- as.numeric(cleantbl$RatingStart[as.numeric(cleantbl$R3)])
cleantbl$OppR4 <- as.numeric(cleantbl$RatingStart[as.numeric(cleantbl$R4)])
cleantbl$OppR5 <- as.numeric(cleantbl$RatingStart[as.numeric(cleantbl$R5)])
cleantbl$OppR6 <- as.numeric(cleantbl$RatingStart[as.numeric(cleantbl$R6)])
cleantbl$OppR7 <- as.numeric(cleantbl$RatingStart[as.numeric(cleantbl$R7)])
cleantbl$AvgOpp <- rowMeans(cleantbl[,21:27],na.rm=TRUE)
cleantbl[1,]
## Rank Player TotalPoints Round1 Round2 Round3
## 1 1 GARY HUA 6.0 W 39 W 21 W 18
## Round4 Round5 Round6 Round7 State Rating
## 1 W 14 W 7 D 12 D 4 ON 15445895 / R: 1794 ->1817
## RatingStart R1 R2 R3 R4 R5 R6 R7 OppR1 OppR2 OppR3 OppR4 OppR5 OppR6 OppR7
## 1 1794 39 21 18 14 7 12 4 1436 1563 1600 1610 1649 1663 1716
## AvgOpp
## 1 1605.286
Finally, we have all required information available in a clean format and can summarize it in the final output table.
finaltbl <- data.frame(matrix(nrow = dim(cleantbl)[1], ncol = 5))
names(finaltbl) <- c("Name","State","TotalPoints","PreRating", "AvgOppRank")
finaltbl$Name <- cleantbl$Player
finaltbl$State <- cleantbl$State
finaltbl$TotalPoints <- cleantbl$TotalPoints
finaltbl$PreRating <- cleantbl$RatingStart
finaltbl$AvgOppRank <- cleantbl$AvgOpp
head(finaltbl)
## Name State TotalPoints PreRating AvgOppRank
## 1 GARY HUA ON 6.0 1794 1605.286
## 2 DAKSHESH DARURI MI 6.0 1553 1469.286
## 3 ADITYA BAJAJ MI 6.0 1384 1563.571
## 4 PATRICK H SCHILLING MI 5.5 1716 1573.571
## 5 HANSHI ZUO MI 5.5 1655 1500.857
## 6 HANSEN SONG OH 5.0 1686 1518.714
Here we could see the csv file.
csv_df = read_csv('https://raw.githubusercontent.com/KanizSanzida/DATA607/main/tournamentinfo.txt')
## Rows: 195 Columns: 1
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr (1): -------------------------------------------------------------------...
##
## ℹ Use `spec()` to retrieve the full column specification for this data.
## ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
csv_df
## # A tibble: 195 × 1
## ---------------------------------------------------------------------------…¹
## <chr>
## 1 Pair | Player Name |Total|Round|Round|Round|Round|Round|…
## 2 Num | USCF ID / Rtg (Pre->Post) | Pts | 1 | 2 | 3 | 4 | 5 |…
## 3 ----------------------------------------------------------------------------…
## 4 1 | GARY HUA |6.0 |W 39|W 21|W 18|W 14|W 7|D …
## 5 ON | 15445895 / R: 1794 ->1817 |N:2 |W |B |W |B |W |B …
## 6 ----------------------------------------------------------------------------…
## 7 2 | DAKSHESH DARURI |6.0 |W 63|W 58|L 4|W 17|W 16|W …
## 8 MI | 14598900 / R: 1553 ->1663 |N:2 |B |W |B |W |B |W …
## 9 ----------------------------------------------------------------------------…
## 10 3 | ADITYA BAJAJ |6.0 |L 8|W 61|W 25|W 21|W 11|W …
## # … with 185 more rows, and abbreviated variable name
## # ¹`-----------------------------------------------------------------------------------------`