DATA607- Project 01

Problem Statement

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

Git-Hub and R-pub

The data, .rmd used in this exercise can be found at https://github.com/forhadakbar/data607fall2019/tree/master/Week%2004 http://rpubs.com/forhadakbar/data607_Project01

Loading packages

library(stringr) #Required for data manipulation
library(DT) # #Required for data table creation

Load Data from Github

 tournamentinfo <-read.csv(paste("https://raw.githubusercontent.com/forhadakbar/data607fall2019/master/Week%2004/tournamentinfo.txt"))
head(tournamentinfo)

Remove Legacy Column Headers

headers_removed <-tournamentinfo[-c(1:2),]
head(headers_removed)

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

Subset the data for transformation

set1<-headers_removed[seq(2, length(headers_removed), 3)] #Player Info
head(set1,3)

## [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|
## [3]     3 | ADITYA BAJAJ                    |6.0  |L   8|W  61|W  25|W  21|W  11|W  13|W  12|
## 131 Levels: ----------------------------------------------------------------------------------------- ...

set2<-headers_removed[seq(3, length(headers_removed), 3)] #Rating Info
head(set2,3)

## [1]    ON | 15445895 / R: 1794   ->1817     |N:2  |W    |B    |W    |B    |W    |B    |W    |
## [2]    MI | 14598900 / R: 1553   ->1663     |N:2  |B    |W    |B    |W    |B    |W    |B    |
## [3]    MI | 14959604 / R: 1384   ->1640     |N:2  |W    |B    |W    |B    |W    |B    |W    |
## 131 Levels: ----------------------------------------------------------------------------------------- ...

Apply Transformations

ID <- as.integer(str_extract(set1, "\\d+"))
PlayerName <- str_trim(str_extract(set1, "(\\w+\\s){2,3}"))
HomeState <- str_extract(set2, "\\w+")
TotalPoints <- as.numeric(str_extract(set1, "\\d+\\.\\d+"))
PlayerPreRating <- as.integer(str_extract(str_extract(set2, "[^\\d]\\d{3,4}[^\\d]"), "\\d+"))
Opponents <- str_extract_all(str_extract_all(set1, "\\d+\\|"), "\\d+")
Wins <- str_count(set1, "\\Q|W  \\E")
Loses <- str_count(set1, "\\Q|L  \\E")
Draws <- str_count(set1, "\\Q|D  \\E")

Compute Opponent Pre-Rating

OpponentPreRating <- length(set1)
for (i in 1:length(set1)) 
{ 
  OpponentPreRating[i] <- round(mean(PlayerPreRating[as.numeric(unlist(Opponents[ID[i]]))]), digits = 0)
}

Final DataSet

final_set <- data.frame( PlayerName, HomeState, TotalPoints, PlayerPreRating, OpponentPreRating);
datatable(final_set)

Create csv output

# CSV file will save in working directory
write.csv(final_set, file = "chesstournament.csv")

Exploratorory Analysis

#Check class
class (final_set)

## [1] "data.frame"

#Check dimension
dim (final_set)

## [1] 64  5

# Check column names
names(final_set)

## [1] "PlayerName"        "HomeState"         "TotalPoints"      
## [4] "PlayerPreRating"   "OpponentPreRating"

# Check for null values
any(is.null(final_set))

## [1] FALSE

# Check for missing values NA's
any(is.na(final_set))

## [1] FALSE

#check summary
summary(final_set)

##                 PlayerName HomeState  TotalPoints    PlayerPreRating
##  ADITYA BAJAJ        : 1   MI:55     Min.   :1.000   Min.   : 377   
##  ALAN BUI            : 1   OH: 1     1st Qu.:2.500   1st Qu.:1227   
##  ALEX KONG           : 1   ON: 8     Median :3.500   Median :1407   
##  AMIYATOSH PWNANANDAM: 1             Mean   :3.438   Mean   :1378   
##  ANVIT RAO           : 1             3rd Qu.:4.000   3rd Qu.:1583   
##  ASHWIN BALAJI       : 1             Max.   :6.000   Max.   :1794   
##  (Other)             :58                                            
##  OpponentPreRating
##  Min.   :1107     
##  1st Qu.:1310     
##  Median :1382     
##  Mean   :1379     
##  3rd Qu.:1481     
##  Max.   :1605     
## 

Bring Tableau in rmarkdowm

I wanted to see if i can embed a tableau dashboard in rmarkdown. I was able to embed my tableau dashboard to rmarkdown using iframe after some research. I created a simple tableau dashboard using the final output .csv data and published it to my tableau public server which is a free platform for everyone. I wanted my dashboard to have search option. You can search any player to see how they performed.