library(dplyr)
library(RSQLite)
library(plyr)
library(tidyr)
library(ggvis)
library(ggplot2)
library(RColorBrewer)

con <- dbConnect(SQLite(), dbname="database.sqlite")

# list all tables
dbListTables(con)
## [1] "Country"         "League"          "Match"           "Player"         
## [5] "Player_Stats"    "Team"            "sqlite_sequence"

Loading Data

player<- tbl_df(dbGetQuery(con,"SELECT * FROM player"))
player_stats <- tbl_df(dbGetQuery(con,"SELECT * FROM player_stats"))
country <- tbl_df(dbGetQuery(con,"SELECT * FROM Country"))
league <- tbl_df(dbGetQuery(con,"SELECT * FROM League"))
team <- tbl_df(dbGetQuery(con,"SELECT * FROM Team"))
match <- tbl_df(dbGetQuery(con,"SELECT * FROM Match"))

country <- country %>% rename(country = name, country_id = id)
## Error in rename(., country = name, country_id = id): unused arguments (country = name, country_id = id)
league  <- league %>%
        rename(league = name) %>%
        select(country_id, league) %>%
        left_join(country, by = "country_id")
## Error in rename(., league = name): unused argument (league = name)
team  <- team %>% rename(team_id = id)
## Error in rename(., team_id = id): unused argument (team_id = id)
sqlite_sequence <- tbl_df(dbGetQuery(con,"SELECT * FROM sqlite_sequence"))

Matching players to their respective leagues

I’m sure there is a better way to do this

match.trim <- match[,c(1,2,3,4,5,6,56,57,58,59,60,61,62,63,64,65,66)]
matchplayerid<- match.trim[rowSums(is.na(match.trim))!=11,]

p1 <- matchplayerid[,c(3,7)]
p2 <- matchplayerid[,c(3,8)]
p3 <- matchplayerid[,c(3,9)]
p4 <- matchplayerid[,c(3,10)]
p5 <- matchplayerid[,c(3,11)]
p6 <- matchplayerid[,c(3,12)]
p7 <- matchplayerid[,c(3,13)]
p8 <- matchplayerid[,c(3,14)]
p9 <- matchplayerid[,c(3,15)]
p10 <- matchplayerid[,c(3,16)]
p11 <- matchplayerid[,c(3,17)]


p1$playerid <- p1$home_player_1
p1 <- p1[,-2]
p2$playerid <- p2$home_player_2
p2 <- p2[,-2]
p3$playerid <- p3$home_player_3
p3 <- p3[,-2]
p4$playerid <- p4$home_player_4
p4 <- p4[,-2]
p5$playerid <- p5$home_player_5
p5 <- p5[,-2]
p6$playerid <- p6$home_player_6
p6 <- p6[,-2]
p7$playerid <- p7$home_player_7
p7 <- p7[,-2]
p8$playerid <- p8$home_player_8
p8 <- p8[,-2]
p9$playerid <- p9$home_player_9
p9 <- p9[,-2]
p10$playerid <- p10$home_player_10
p10 <- p10[,-2]
p11$playerid <- p11$home_player_11
p11 <- p11[,-2]

players_long <- bind_rows(p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11)

pl_narm<- players_long[is.na(players_long$playerid)==FALSE,]
plid <- distinct(pl_narm)
plid$player_api_id <- plid$playerid
plid <- plid[,-2]


player.league <- left_join(player, plid)
## Joining by: "player_api_id"

First Method used to make histograms

# belgium.league <- filter(player.league, player.league$league_id == 1)
# hist(belgium.league$height)
# hist(belgium.league$weight)
# 
# premier.league <- filter(player.league, player.league$league_id == 1729)
# hist(premier.league$height)
# hist(premier.league$weight)
# 
# france.league <- filter(player.league, player.league$league_id == 4735)
# hist(france.league$height)
# hist(france.league$weight)
# 
# Bundesliga.league <- filter(player.league, player.league$league_id == 7775)
# hist(Bundesliga.league$height)
# hist(Bundesliga.league$weight)
# 
# italy.league <- filter(player.league, player.league$league_id == 10223)
# hist(italy.league$height)
# hist(italy.league$weight)
# 
# nether.league <- filter(player.league, player.league$league_id == 13240)
# hist(nether.league$height)
# hist(nether.league$weight)
# 
# poland.league <- filter(player.league, player.league$league_id == 15688)
# hist(poland.league$height)
# hist(poland.league$weight)
# 
# portugal.league <- filter(player.league, player.league$league_id == 17608)
# hist(portugal.league$height)
# hist(portugal.league$weight)
# 
# scotland.league <- filter(player.league, player.league$league_id == 19660)
# hist(scotland.league$height)
# hist(scotland.league$weight)
# 
# bbva.league <- filter(player.league, player.league$league_id == 21484)
# hist(bbva.league$height)
# hist(bbva.league$weight)
# 
# swiss.league <- filter(player.league, player.league$league_id == 24524)
# hist(swiss.league$height)
# hist(swiss.league$weight)

Organizing data for ggplot

league.grouped <- player.league %>% group_by(league_id) %>% arrange(league_id)

class(player.league$league_id)
## [1] "integer"
player.league$league_id <- as.factor(player.league$league_id)

class(player.league$birthday)
## [1] "character"
player.league$birthday <- as.Date(player.league$birthday)

levels(player.league$league_id)[levels(player.league$league_id)== 1] <- "Belgium Jupiler League"
levels(player.league$league_id)[levels(player.league$league_id)== 1729] <- "England Premier League"
levels(player.league$league_id)[levels(player.league$league_id)== 4735] <- "France Ligue 1"
levels(player.league$league_id)[levels(player.league$league_id)== 7775] <- "Germany 1. Bundesliga1"
levels(player.league$league_id)[levels(player.league$league_id)== 10223] <- "Italy Serie A"
levels(player.league$league_id)[levels(player.league$league_id)== 13240] <- "Netherlands Eredivisie"
levels(player.league$league_id)[levels(player.league$league_id)== 15688] <- "Poland Ekstraklasa"
levels(player.league$league_id)[levels(player.league$league_id)== 17608] <- "Portugal Liga ZON Sagres"
levels(player.league$league_id)[levels(player.league$league_id)== 19660] <- "Scotland Premier League"
levels(player.league$league_id)[levels(player.league$league_id)== 21484] <- "Spain LIGA BBVA"
levels(player.league$league_id)[levels(player.league$league_id)== 24524] <- "Switzerland Super League"

Multiple Density Plot in ggplot

ggplot(player.league, aes(weight)) + geom_density(aes(color=league_id)) + scale_color_brewer(palette="Paired")

ggplot(player.league, aes(height)) + geom_density(aes(color=league_id)) + scale_color_brewer(palette="Paired")

ggplot(player.league, aes(birthday)) + geom_density(aes(color=league_id)) + scale_color_brewer(palette="Paired")

Practice Using ggvis

I want to come back to try to make this interactive. Also need to fix NAs. I only plotted the first one for a sample of how it might look.

# player.league %>% ggvis(x = ~weight, stroke := ~league_id, fill = ~league_id) %>% group_by(league_id) %>% layer_densities() 
# player.league %>% ggvis(x = ~height, stroke := ~league_id, fill = ~league_id) %>% group_by(league_id) %>% layer_densities()
# player.league %>% ggvis(x = ~as.numeric(birthday), stroke := ~league_id, fill = ~league_id) %>% group_by(league_id) %>% layer_densities()

Player Stats

player.stats <- left_join(player.league, player_stats, by= "player_api_id")

player.stats <- player.stats[,c(2,3,5,6,7,8,11:49)]

Ploting Stats by League

Kinda went crazy with density plots. It would be a good idea to put this into a shiny ap that is selectable based in stat that user is interested in.

ggplot(player.stats, aes(overall_rating)) + geom_density(aes(color=league_id)) + scale_color_brewer(palette="Paired")

ggplot(player.stats, aes(potential)) + geom_density(aes(color=league_id)) + scale_color_brewer(palette="Paired")

ggplot(player.stats, aes(crossing)) + geom_density(aes(color=league_id)) + scale_color_brewer(palette="Paired")

ggplot(player.stats, aes(finishing)) + geom_density(aes(color=league_id)) + scale_color_brewer(palette="Paired")

ggplot(player.stats, aes(heading_accuracy)) + geom_density(aes(color=league_id)) + scale_color_brewer(palette="Paired")

ggplot(player.stats, aes(short_passing)) + geom_density(aes(color=league_id)) + scale_color_brewer(palette="Paired")

ggplot(player.stats, aes(volleys)) + geom_density(aes(color=league_id)) + scale_color_brewer(palette="Paired")

ggplot(player.stats, aes(dribbling)) + geom_density(aes(color=league_id)) + scale_color_brewer(palette="Paired")

ggplot(player.stats, aes(curve)) + geom_density(aes(color=league_id)) + scale_color_brewer(palette="Paired")

ggplot(player.stats, aes(free_kick_accuracy)) + geom_density(aes(color=league_id)) + scale_color_brewer(palette="Paired")

ggplot(player.stats, aes(long_passing)) + geom_density(aes(color=league_id)) + scale_color_brewer(palette="Paired")

ggplot(player.stats, aes(ball_control)) + geom_density(aes(color=league_id)) + scale_color_brewer(palette="Paired")

ggplot(player.stats, aes(acceleration)) + geom_density(aes(color=league_id)) + scale_color_brewer(palette="Paired")

ggplot(player.stats, aes(sprint_speed)) + geom_density(aes(color=league_id)) + scale_color_brewer(palette="Paired")

ggplot(player.stats, aes(agility)) + geom_density(aes(color=league_id)) + scale_color_brewer(palette="Paired")

ggplot(player.stats, aes(reactions)) + geom_density(aes(color=league_id)) + scale_color_brewer(palette="Paired")

ggplot(player.stats, aes(balance)) + geom_density(aes(color=league_id)) + scale_color_brewer(palette="Paired")

ggplot(player.stats, aes(shot_power)) + geom_density(aes(color=league_id)) + scale_color_brewer(palette="Paired")

ggplot(player.stats, aes(jumping)) + geom_density(aes(color=league_id)) + scale_color_brewer(palette="Paired")

ggplot(player.stats, aes(stamina)) + geom_density(aes(color=league_id)) + scale_color_brewer(palette="Paired")

ggplot(player.stats, aes(strength)) + geom_density(aes(color=league_id)) + scale_color_brewer(palette="Paired")

ggplot(player.stats, aes(long_shots)) + geom_density(aes(color=league_id)) + scale_color_brewer(palette="Paired")

ggplot(player.stats, aes(aggression)) + geom_density(aes(color=league_id)) + scale_color_brewer(palette="Paired")

ggplot(player.stats, aes(interceptions)) + geom_density(aes(color=league_id)) + scale_color_brewer(palette="Paired")

ggplot(player.stats, aes(positioning)) + geom_density(aes(color=league_id)) + scale_color_brewer(palette="Paired")

ggplot(player.stats, aes(vision)) + geom_density(aes(color=league_id)) + scale_color_brewer(palette="Paired")

ggplot(player.stats, aes(penalties)) + geom_density(aes(color=league_id)) + scale_color_brewer(palette="Paired")

ggplot(player.stats, aes(marking)) + geom_density(aes(color=league_id)) + scale_color_brewer(palette="Paired")

ggplot(player.stats, aes(standing_tackle)) + geom_density(aes(color=league_id)) + scale_color_brewer(palette="Paired")

ggplot(player.stats, aes(sliding_tackle)) + geom_density(aes(color=league_id)) + scale_color_brewer(palette="Paired")