Gather data for football analysis regarding Manchester United. The time scope included is starting since the season after SAF retired up until most recent (When this code is written == 2022/2023).
library(worldfootballR) # fetch data
library(tidyverse) # processing data
library(DBI) # connecting to database driver
library(RPostgres) # driver to connect to postgre
library(jsonlite) # organize json fileSir Alex Ferguson retired in 2012/2013 season, so let’s collect 2013/2014 until most recently completed season. I will compare the aggregate data of how Man Utd perform vs the aggregate data of how the rest of big 5 leagues team perform. The data necessary will be: - Big 5 leagues team standard stats (2013/2014 - 2022/2023) - Big 5 leagues player standard stats (2013/2014 - 2022/2023) - Man Utd premier league team standard stats (2013/2014 - 2022/2023) - Man Utd premier league player standard stats (2013/2014 - 2022/2023) I will try to profile the pros and cons of each Man Utd managers after SAF. The data necessary will be: - Man Utd managers data history. - Premier League Regular Season stats.
big5_team_2014_2023 <-
fb_big5_advanced_season_stats(
season_end_year = c(2014:2023),
stat_type = "standard",
team_or_player = "team"
)manu_2014_2023 <-
big5_team_2014_2023 %>%
filter(Squad == "Manchester Utd", Team_or_Opponent == "team")big5_player_2014_2023 <-
fb_big5_advanced_season_stats(
season_end_year = c(2014:2023),
stat_type = "standard",
team_or_player = "player"
)manu_players_2014_2023 <-
big5_player_2014_2023 %>%
filter(Squad == "Manchester Utd")team_urls <- tm_league_team_urls(country_name = "England",start_year = 2023)manu_url <- team_urls[5]
manu_url## [1] "https://www.transfermarkt.com/manchester-united/startseite/verein/985/saison_id/2023"manu_managers <- tm_team_staff_history(team_urls = manu_url,staff_role = "Manager")Even though the team url is for 2023 only, the staff history includes previous seasons as well.
Prepare a premier league team url for the seasons we need.
start_years <- 2014:2022
country <- "England"
prem_team_since_2014 <-
map(
start_years,
~
tm_league_team_urls(
start_year = .x,
country_name = country)
)Check the resulting list.
prem_team_since_2014 %>% head(n = 3)## [[1]]
## [1] "https://www.transfermarkt.com/fc-chelsea/startseite/verein/631/saison_id/2014"
## [2] "https://www.transfermarkt.com/manchester-united/startseite/verein/985/saison_id/2014"
## [3] "https://www.transfermarkt.com/manchester-city/startseite/verein/281/saison_id/2014"
## [4] "https://www.transfermarkt.com/fc-arsenal/startseite/verein/11/saison_id/2014"
## [5] "https://www.transfermarkt.com/fc-liverpool/startseite/verein/31/saison_id/2014"
## [6] "https://www.transfermarkt.com/tottenham-hotspur/startseite/verein/148/saison_id/2014"
## [7] "https://www.transfermarkt.com/fc-southampton/startseite/verein/180/saison_id/2014"
## [8] "https://www.transfermarkt.com/fc-everton/startseite/verein/29/saison_id/2014"
## [9] "https://www.transfermarkt.com/newcastle-united/startseite/verein/762/saison_id/2014"
## [10] "https://www.transfermarkt.com/west-ham-united/startseite/verein/379/saison_id/2014"
## [11] "https://www.transfermarkt.com/swansea-city/startseite/verein/2288/saison_id/2014"
## [12] "https://www.transfermarkt.com/hull-city/startseite/verein/3008/saison_id/2014"
## [13] "https://www.transfermarkt.com/aston-villa/startseite/verein/405/saison_id/2014"
## [14] "https://www.transfermarkt.com/queens-park-rangers/startseite/verein/1039/saison_id/2014"
## [15] "https://www.transfermarkt.com/stoke-city/startseite/verein/512/saison_id/2014"
## [16] "https://www.transfermarkt.com/crystal-palace/startseite/verein/873/saison_id/2014"
## [17] "https://www.transfermarkt.com/west-bromwich-albion/startseite/verein/984/saison_id/2014"
## [18] "https://www.transfermarkt.com/afc-sunderland/startseite/verein/289/saison_id/2014"
## [19] "https://www.transfermarkt.com/leicester-city/startseite/verein/1003/saison_id/2014"
## [20] "https://www.transfermarkt.com/fc-burnley/startseite/verein/1132/saison_id/2014"
##
## [[2]]
## [1] "https://www.transfermarkt.com/fc-chelsea/startseite/verein/631/saison_id/2015"
## [2] "https://www.transfermarkt.com/manchester-city/startseite/verein/281/saison_id/2015"
## [3] "https://www.transfermarkt.com/fc-arsenal/startseite/verein/11/saison_id/2015"
## [4] "https://www.transfermarkt.com/manchester-united/startseite/verein/985/saison_id/2015"
## [5] "https://www.transfermarkt.com/fc-liverpool/startseite/verein/31/saison_id/2015"
## [6] "https://www.transfermarkt.com/tottenham-hotspur/startseite/verein/148/saison_id/2015"
## [7] "https://www.transfermarkt.com/fc-everton/startseite/verein/29/saison_id/2015"
## [8] "https://www.transfermarkt.com/fc-southampton/startseite/verein/180/saison_id/2015"
## [9] "https://www.transfermarkt.com/west-ham-united/startseite/verein/379/saison_id/2015"
## [10] "https://www.transfermarkt.com/newcastle-united/startseite/verein/762/saison_id/2015"
## [11] "https://www.transfermarkt.com/leicester-city/startseite/verein/1003/saison_id/2015"
## [12] "https://www.transfermarkt.com/stoke-city/startseite/verein/512/saison_id/2015"
## [13] "https://www.transfermarkt.com/swansea-city/startseite/verein/2288/saison_id/2015"
## [14] "https://www.transfermarkt.com/fc-watford/startseite/verein/1010/saison_id/2015"
## [15] "https://www.transfermarkt.com/crystal-palace/startseite/verein/873/saison_id/2015"
## [16] "https://www.transfermarkt.com/aston-villa/startseite/verein/405/saison_id/2015"
## [17] "https://www.transfermarkt.com/norwich-city/startseite/verein/1123/saison_id/2015"
## [18] "https://www.transfermarkt.com/west-bromwich-albion/startseite/verein/984/saison_id/2015"
## [19] "https://www.transfermarkt.com/afc-sunderland/startseite/verein/289/saison_id/2015"
## [20] "https://www.transfermarkt.com/afc-bournemouth/startseite/verein/989/saison_id/2015"
##
## [[3]]
## [1] "https://www.transfermarkt.com/fc-chelsea/startseite/verein/631/saison_id/2016"
## [2] "https://www.transfermarkt.com/manchester-city/startseite/verein/281/saison_id/2016"
## [3] "https://www.transfermarkt.com/tottenham-hotspur/startseite/verein/148/saison_id/2016"
## [4] "https://www.transfermarkt.com/fc-arsenal/startseite/verein/11/saison_id/2016"
## [5] "https://www.transfermarkt.com/manchester-united/startseite/verein/985/saison_id/2016"
## [6] "https://www.transfermarkt.com/fc-liverpool/startseite/verein/31/saison_id/2016"
## [7] "https://www.transfermarkt.com/fc-everton/startseite/verein/29/saison_id/2016"
## [8] "https://www.transfermarkt.com/west-ham-united/startseite/verein/379/saison_id/2016"
## [9] "https://www.transfermarkt.com/fc-southampton/startseite/verein/180/saison_id/2016"
## [10] "https://www.transfermarkt.com/crystal-palace/startseite/verein/873/saison_id/2016"
## [11] "https://www.transfermarkt.com/leicester-city/startseite/verein/1003/saison_id/2016"
## [12] "https://www.transfermarkt.com/stoke-city/startseite/verein/512/saison_id/2016"
## [13] "https://www.transfermarkt.com/fc-watford/startseite/verein/1010/saison_id/2016"
## [14] "https://www.transfermarkt.com/swansea-city/startseite/verein/2288/saison_id/2016"
## [15] "https://www.transfermarkt.com/hull-city/startseite/verein/3008/saison_id/2016"
## [16] "https://www.transfermarkt.com/afc-sunderland/startseite/verein/289/saison_id/2016"
## [17] "https://www.transfermarkt.com/afc-bournemouth/startseite/verein/989/saison_id/2016"
## [18] "https://www.transfermarkt.com/fc-middlesbrough/startseite/verein/641/saison_id/2016"
## [19] "https://www.transfermarkt.com/west-bromwich-albion/startseite/verein/984/saison_id/2016"
## [20] "https://www.transfermarkt.com/fc-burnley/startseite/verein/1132/saison_id/2016"Now filter the list for “manchester-united” strings.
manu_url_sy14_sy22 <-
prem_team_since_2014 %>%
map(
~ .x[grepl("manchester-united", .x)]
)manu_url_sy14_sy22## [[1]]
## [1] "https://www.transfermarkt.com/manchester-united/startseite/verein/985/saison_id/2014"
##
## [[2]]
## [1] "https://www.transfermarkt.com/manchester-united/startseite/verein/985/saison_id/2015"
##
## [[3]]
## [1] "https://www.transfermarkt.com/manchester-united/startseite/verein/985/saison_id/2016"
##
## [[4]]
## [1] "https://www.transfermarkt.com/manchester-united/startseite/verein/985/saison_id/2017"
##
## [[5]]
## [1] "https://www.transfermarkt.com/manchester-united/startseite/verein/985/saison_id/2018"
##
## [[6]]
## [1] "https://www.transfermarkt.com/manchester-united/startseite/verein/985/saison_id/2019"
##
## [[7]]
## [1] "https://www.transfermarkt.com/manchester-united/startseite/verein/985/saison_id/2020"
##
## [[8]]
## [1] "https://www.transfermarkt.com/manchester-united/startseite/verein/985/saison_id/2021"
##
## [[9]]
## [1] "https://www.transfermarkt.com/manchester-united/startseite/verein/985/saison_id/2022"Flatten from list to vector.
manu_url_sy14_sy22 <- manu_url_sy14_sy22 %>% unlist()
manu_url_sy14_sy22## [1] "https://www.transfermarkt.com/manchester-united/startseite/verein/985/saison_id/2014"
## [2] "https://www.transfermarkt.com/manchester-united/startseite/verein/985/saison_id/2015"
## [3] "https://www.transfermarkt.com/manchester-united/startseite/verein/985/saison_id/2016"
## [4] "https://www.transfermarkt.com/manchester-united/startseite/verein/985/saison_id/2017"
## [5] "https://www.transfermarkt.com/manchester-united/startseite/verein/985/saison_id/2018"
## [6] "https://www.transfermarkt.com/manchester-united/startseite/verein/985/saison_id/2019"
## [7] "https://www.transfermarkt.com/manchester-united/startseite/verein/985/saison_id/2020"
## [8] "https://www.transfermarkt.com/manchester-united/startseite/verein/985/saison_id/2021"
## [9] "https://www.transfermarkt.com/manchester-united/startseite/verein/985/saison_id/2022"Now extract transfer data from each Man Utd URL.
manu_transfers <-
map(
manu_url_sy14_sy22,
~
tm_team_transfers(team_url = .x)
)prem_stat_1423 <-
fb_season_team_stats(
country = "ENG",
gender = "M",
season_end_year = c(2014:2023),
tier = "1st",
stat_type = "league_table",
time_pause = 4
)Prepare Postgre driver with DBI and Rpostgres libraries. Use Jsonlite to organize json file. Load json file filled with my credentials to access the database.
mycred <- fromJSON(txt = "concred.json")Create connection object.
con <-
dbConnect(
drv = RPostgres::Postgres(),
user = mycred$user,
password = mycred$password,
host = "localhost",
port = 5432,
dbname = "football",
options = "-c client_encoding=UTF8"
)con## <PqConnection> football@localhost:5432Cek isi database.
dbListTables(con)## [1] "big5_p_1423" "big5_t_1423" "manu_p_1423"
## [4] "manu_t_1423" "manu_mngr_1423" "manu_transfer_1423"
## [7] "premstat_1423"Simpan tabel-tabel yang diperlukan ke dalam database football.
dbWriteTable(
conn = con,
name = "big5_p_1423",
value = big5_player_2014_2023,
overwrite = T
)dbWriteTable(
conn = con,
name = "big5_t_1423",
value = big5_team_2014_2023,
overwrite = T)dbWriteTable(
conn = con,
name = "manu_p_1423",
value = manu_players_2014_2023,
overwrite = T)dbWriteTable(
conn = con,
name = "manu_t_1423",
value = manu_2014_2023,
overwrite = T)dbWriteTable(
conn = con,
name = "manu_mngr_1423",
value = manu_managers,
overwrite = T
)Because manu_transfers is a list, I need to prepare the table beforehand in postgreSQL.
Preview the structure of dataframes in the said list.
manu_transfers[1] %>% glimpse()## List of 1
## $ :'data.frame': 52 obs. of 21 variables:
## ..$ team_name : chr [1:52] "Manchester United" "Manchester United" "Manchester United" "Manchester United" ...
## ..$ league : chr [1:52] "Premier League" "Premier League" "Premier League" "Premier League" ...
## ..$ country : chr [1:52] "England" "England" "England" "England" ...
## ..$ season : chr [1:52] "2014" "2014" "2014" "2014" ...
## ..$ transfer_type : chr [1:52] "Arrivals" "Arrivals" "Arrivals" "Arrivals" ...
## ..$ player_name : chr [1:52] "Ángel Di María" "Luke Shaw" "Ander Herrera" "Marcos Rojo" ...
## ..$ player_url : chr [1:52] "https://www.transfermarkt.com/angel-di-maria/profil/spieler/45320" "https://www.transfermarkt.com/luke-shaw/profil/spieler/183288" "https://www.transfermarkt.com/ander-herrera/profil/spieler/99343" "https://www.transfermarkt.com/marcos-rojo/profil/spieler/93176" ...
## ..$ player_position : chr [1:52] "Right Winger" "Left-Back" "Central Midfield" "Centre-Back" ...
## ..$ player_age : chr [1:52] "26" "18" "24" "24" ...
## ..$ player_nationality: chr [1:52] "Argentina" "England" "Spain" "Argentina" ...
## ..$ club_2 : chr [1:52] "Real Madrid" "Southampton" "Athletic" "Sporting CP" ...
## ..$ league_2 : chr [1:52] "LaLiga" "Premier League" "LaLiga" "Liga Portugal" ...
## ..$ country_2 : chr [1:52] "Spain" "England" "Spain" "Portugal" ...
## ..$ transfer_fee : Named num [1:52] 75000000 37500000 36000000 20000000 17500000 7600000 1750000 NA NA NA ...
## .. ..- attr(*, "names")= chr [1:52] "\20075.00m" "\20037.50m" "\20036.00m" "\20020.00m" ...
## ..$ is_loan : logi [1:52] FALSE FALSE FALSE FALSE FALSE TRUE ...
## ..$ transfer_notes : Named chr [1:52] NA NA NA NA ...
## .. ..- attr(*, "names")= chr [1:52] "\20075.00m" "\20037.50m" "\20036.00m" "\20020.00m" ...
## ..$ window : chr [1:52] "Summer" "Summer" "Summer" "Summer" ...
## ..$ in_squad : num [1:52] 33 22 37 26 29 34 NA 2 23 33 ...
## ..$ appearances : num [1:52] 32 20 31 26 29 29 NA 1 12 18 ...
## ..$ goals : num [1:52] 4 0 8 1 2 4 NA 0 0 0 ...
## ..$ minutes_played : num [1:52] 1996 1310 2136 2097 2528 ...manu_transfers[[1]] %>% names()## [1] "team_name" "league" "country"
## [4] "season" "transfer_type" "player_name"
## [7] "player_url" "player_position" "player_age"
## [10] "player_nationality" "club_2" "league_2"
## [13] "country_2" "transfer_fee" "is_loan"
## [16] "transfer_notes" "window" "in_squad"
## [19] "appearances" "goals" "minutes_played"manu_transfers[[1]] %>% class()## [1] "data.frame"# Replace with your table name and column definitions
table_name <- "manu_transfer_1423"
create_table_query <- "
CREATE TABLE IF NOT EXISTS manu_transfer_1423 (
id BIGSERIAL NOT NULL PRIMARY KEY,
team_name VARCHAR(50),
league VARCHAR(50),
country VARCHAR(50),
season INTEGER,
transfer_type VARCHAR(50),
player_name VARCHAR(50),
player_url VARCHAR(300),
player_position VARCHAR(50),
player_age INTEGER,
player_nationality VARCHAR(50),
club_2 VARCHAR(50),
league_2 VARCHAR(50),
country_2 VARCHAR(50),
transfer_fee REAL,
is_loan VARCHAR(50),
transfer_notes VARCHAR(100),
transfer_window VARCHAR(50),
in_squad INTEGER,
appearances INTEGER,
goals INTEGER,
minutes_played REAL
);
" dbExecute(conn = con, statement = "DROP TABLE manu_transfer_1423" )## [1] 0dbExecute(con, create_table_query)## [1] 0Check the table.
dbListTables(conn = con)## [1] "premstat_1423" "big5_p_1423" "big5_t_1423"
## [4] "manu_p_1423" "manu_t_1423" "manu_mngr_1423"
## [7] "manu_transfer_1423"Previously, when executing the table creation, the window column caused an error. I had to rename it into transfer_window. Now I have to rename the column in the dataframe as well.
manu_transfers <- map(
manu_transfers,
~ rename(.data = .x, transfer_window = window)
)Now that the table is ready, let’s insert the dataframes from our manu_transfers list into the manu_transfer_1423 table in postgreSQL.
Write the table into PostgreSQL with map function.
map(
manu_transfers,
~ dbWriteTable(
conn = con,
name = table_name,
value = .x,
append = TRUE,
row.names = FALSE
)
)## [[1]]
## [1] TRUE
##
## [[2]]
## [1] TRUE
##
## [[3]]
## [1] TRUE
##
## [[4]]
## [1] TRUE
##
## [[5]]
## [1] TRUE
##
## [[6]]
## [1] TRUE
##
## [[7]]
## [1] TRUE
##
## [[8]]
## [1] TRUE
##
## [[9]]
## [1] TRUETry fetching.
dbGetQuery(conn = con, statement =
"SELECT * FROM manu_transfer_1423 OFFSET 5 LIMIT 10"
)## id team_name league country season transfer_type
## 1 6 Manchester United Premier League England 2014 Arrivals
## 2 7 Manchester United Premier League England 2014 Arrivals
## 3 8 Manchester United Premier League England 2014 Arrivals
## 4 9 Manchester United Premier League England 2014 Arrivals
## 5 10 Manchester United Premier League England 2014 Arrivals
## 6 11 Manchester United Premier League England 2014 Arrivals
## 7 12 Manchester United Premier League England 2014 Arrivals
## 8 13 Manchester United Premier League England 2014 Arrivals
## 9 14 Manchester United Premier League England 2014 Arrivals
## 10 15 Manchester United Premier League England 2014 Arrivals
## player_name
## 1 Radamel Falcao
## 2 Vanja Milinkovic-Savic
## 3 Jesse Lingard
## 4 Tyler Blackett
## 5 Paddy McNair
## 6 James Wilson
## 7 Tom Cleverley
## 8 Anderson
## 9 Ángelo Henríquez
## 10 Federico Macheda
## player_url
## 1 https://www.transfermarkt.com/radamel-falcao/profil/spieler/39152
## 2 https://www.transfermarkt.com/vanja-milinkovic-savic/profil/spieler/257474
## 3 https://www.transfermarkt.com/jesse-lingard/profil/spieler/141660
## 4 https://www.transfermarkt.com/tyler-blackett/profil/spieler/134414
## 5 https://www.transfermarkt.com/paddy-mcnair/profil/spieler/167268
## 6 https://www.transfermarkt.com/james-wilson/profil/spieler/214104
## 7 https://www.transfermarkt.com/tom-cleverley/profil/spieler/73484
## 8 https://www.transfermarkt.com/anderson/profil/spieler/31645
## 9 https://www.transfermarkt.com/angelo-henriquez/profil/spieler/189740
## 10 https://www.transfermarkt.com/federico-macheda/profil/spieler/61887
## player_position player_age player_nationality club_2
## 1 Centre-Forward 28 Colombia Monaco
## 2 Goalkeeper 17 Serbia Vojvodina
## 3 Attacking Midfield 21 England Man Utd U21
## 4 Left-Back 20 England Man Utd U21
## 5 Centre-Back 19 Northern Ireland Man Utd U21
## 6 Centre-Forward 18 England Man Utd U18
## 7 Central Midfield 25 England Aston Villa
## 8 Central Midfield 26 Brazil Fiorentina
## 9 Centre-Forward 20 Chile Real Zaragoza
## 10 Centre-Forward 22 Italy Birmingham
## league_2 country_2 transfer_fee is_loan transfer_notes
## 1 Ligue 1 Monaco 7600000 true <NA>
## 2 Super liga Srbije Serbia 1750000 false <NA>
## 3 Premier League 2 England NA false -
## 4 Premier League 2 England NA false -
## 5 Premier League 2 England NA false -
## 6 <NA> England NA false -
## 7 Premier League England NA true End of loan May 31, 2015
## 8 Serie A Italy NA true End of loan Jun 30, 2014
## 9 LaLiga2 Spain NA true End of loan Jun 30, 2014
## 10 Championship England NA true End of loan Jun 30, 2014
## transfer_window in_squad appearances goals minutes_played
## 1 Summer 34 29 4 1496
## 2 Summer NA NA NA NA
## 3 Summer 2 1 0 24
## 4 Summer 23 12 0 739
## 5 Summer 33 18 0 1206
## 6 Summer 27 17 2 562
## 7 Summer 1 1 0 90
## 8 Summer 3 2 0 110
## 9 Summer NA NA NA NA
## 10 Summer NA NA NA NAdbWriteTable(
conn = con,
name = "premstat_1423",
value = prem_stat_1423,
overwrite = T,
)