The oldest active European Soccer Player in season 2008

Still playing with a Soccer Database from Kaggle.com

As an exercise, I wanted to find the oldest players participating in any match. During my analysis I also learned something about the data quality of this Kaggle dataset.

As mentioned in a previous blogpost, I’ve downloaded a zipfile (36 MB) with Football data from data science community Kaggle.com. The archive contained an SQLite Database.

library(DBI)
con <- dbConnect(odbc::odbc(), "well-sqlite-footballdb")

For the sake of brevity, to see the R packages I’ve included to process the data, see the previous blogpost. Only the tidyr package is new here.

library(tidyr)   # gather

The database consists of 7 tables. We’ll read in all of them. The Player table contains basic data of ~10000 soccer players from the top leagues of 14 European countries. They are not strictly “European soccer players”, but players from all over the globe, competing in the top leagues of certain Western European countries.

Tables in the Sqlite database:

##                     Rows Columns
## Player_Attributes 183978      42
## Player             11060       7
## Match              25979     115
## League                11       3
## Country               11       2
## Team                 299       5
## Team_Attributes     1458      25

The Player table lists the soccer players’ data.

The Match table contains data from 25979 matches, played between 2008-07-18 and 2016-05-25. The table contains 115 columns.

The names of the players comprising the teams are listed in 22 columns named home_player_1 to home_player_11, and away_player_1 to away_player_11. The substitute players nominated for the match are not known.

This database table has an untidy format. Let’s tidy it with the tidyr::gather() function:

Match_players  <- Match %>%
        select(match_api_id, season, league_id, 
               home_player_1:away_player_11) %>%
        rename("match_id" = match_api_id)  %>%
        gather( k, player_api_id,  -match_id, -season, -league_id)

This table now looks like this:

head(Match_players %>% filter(!is.na(player_api_id)), 5)

##   match_id    season league_id             k player_api_id
## 1   493016 2008/2009         1 home_player_1         39890
## 2   493017 2008/2009         1 home_player_1         38327
## 3   493018 2008/2009         1 home_player_1         95597
## 4   493020 2008/2009         1 home_player_1         30934
## 5   493021 2008/2009         1 home_player_1         37990

I’ve only shown 5 rows of this 571538 x 5 table. This table can now be joined with the Player table:

Player_in_match <- Player %>%
        inner_join(Match_players, by = c("player_api_id" = "player_api_id")) %>%
        mutate(year_of_birth = year(birthday)) %>%
        rename("player_id" = id)

This table, Player_in_match is a 542281 x 12 table. Remember, the original, standalone Match_players table had 571538 records. The join reduced this to 542281 athletes. Hence, many players didn’t actually play, or the screenscraping process did not yield any information about the matches they ’ve participated in; or the parse-process of many downloaded files containing the matches data failed. In any case, they actually played or were nominated for the starting formation of their squads.

Player_distinct_by_year <- Player_in_match %>%
        select(year_of_birth, season, player_api_id) %>%
        group_by(year_of_birth, season) %>%
        summarize(distinct_players =n_distinct(player_api_id))

Player_distinct_by_year %>%
        ggplot(aes(year_of_birth, distinct_players, color=season)) +
        geom_point( size=.4) +
        geom_smooth(method="loess", se=FALSE, size=0.5) +
        ylab("Number of distinct Players in Database") +
        xlab("Player's Year of Birth") +
        ggtitle("European Soccer Database: Players' appearances",
                subtitle = "Counting Players' appearances in the squads. Seasons 2008-2016.\n
Each player is counted once per season no matter how often he played.")

Some players were rarely nominated for the squad of any match. This can best be seen at the left side of the graph. These data points represent older players born before 1970.

Why do the humps have different heights? I think for older seasons, there is simply less data available at the websites of the commercial data vendors, where the data was scraped from.

Histogram of matches played, by year of birth

This code generates a histogram of the “age distribution” of players in the dataset. These are players for which match data is known. I think the axes are pretty self-explanatory.

Pl_lbl <- Player_in_match %>%
        arrange(birthday) %>% 
        select(year_of_birth, player_name) %>%
        filter(year_of_birth <= 1970) %>% 
        group_by(year_of_birth) %>% 
        arrange(year_of_birth) %>% 
        unique()

Pl_lbl  <- Pl_lbl %>% unique() %>% head(1)


Player_in_match %>%
        ggplot(aes(year_of_birth)) + 
        geom_histogram(binwidth=1, fill="maroon") +
        geom_text(data = Pl_lbl, 
                  aes(x=I(year_of_birth+3.5), y=I(4500), 
                      label = player_name, angle = 45), size=3) +
        ggtitle(sprintf("Matches by %s soccer players in top leagues in Europe, 2008-2016", nrow(Player_in_match)),
                subtitle="By year of birth. Histogram shows players for which match participation data is known.") +
        xlab("Year of Birth") +
        ylab("Appearances in Matches")

Find the oldest player in any match

We can use the Player_in_match table to find the oldest players who participated in any match. This corresponds to the leftmost bars in the histogram above. This bar corresponds to the matches of a the oldest soccer players, born in 1970 or earlier.

oldest_players_in_any_match <- Player_in_match %>%
        inner_join(League, by = c("league_id" = "id") ) %>%
        filter(year_of_birth < 1971) %>%
        select(year_of_birth, season, player_name, match_id, name) %>%
        group_by(year_of_birth, season, player_name, match_id) %>%
        summarize(n_matches = n()) %>%
        arrange(season) %>%
        ungroup()

Result:

(oldest <- oldest_players_in_any_match %>% 
        select(player_name, year_of_birth, n_matches) %>% 
        group_by(player_name, year_of_birth) %>% 
         summarize(n_matches=sum(n_matches)) %>% 
         arrange(year_of_birth, player_name) %>% 
         ungroup())

## # A tibble: 20 x 3
##    player_name         year_of_birth n_matches
##    <chr>                       <dbl>     <int>
##  1 Alberto Fontana             1967.         4
##  2 Paolo Maldini               1968.        30
##  3 Dean Windass                1969.         1
##  4 Francesco Antonioli         1969.        98
##  5 Jens Lehmann                1969.        66
##  6 Luca Bucci                  1969.         1
##  7 Michael Tarnat              1969.         6
##  8 Rob van Dijk                1969.        59
##  9 Antonio Chimenti            1970.         2
## 10 Chris Swailes               1970.        22
## 11 David James                 1970.        61
## 12 David Weir                  1970.       111
## 13 Dean Kiely                  1970.         3
## 14 Edwin van der Sar           1970.        87
## 15 Eugenio Corini              1970.        12
## 16 Hans Vonk                   1970.         4
## 17 Nico van Kerckhoven         1970.        32
## 18 Pedro Roma                  1970.         2
## 19 Sander Boschker             1970.        58
## 20 Tugay Kerimoglou            1970.        15

Before I studied this dataset, the oldest player, Alberto Fontana, was completely unknown to me. So let’s lookup some metadata about this player, and the other players born before 1971, in the Google Knowledge Graph.

The calculation in the following code block is a bit awkward and verbose, because

• Google Knowledge Graph does not return records with a perfectly consistent structure. I have to transform it to a consistent 2-column (key-value) table.
• the Google Knowledge Graph data contains -in this context- irrelevant info such as license data about photographs etc that I had too filter out.
• the database does not keep track of accents and special characters of the Turkish alphabet, so I better use the correctly encoded string from Wikipedia/Google.

# set/replace this one manually. Google returned another wrong athlete (wheelchair)
oldest[oldest$player_name == "David Weir", "player_name"] <- "David Gillespie Weir"

oldest.2 <- map(oldest$player_name, 
                kgapi_lookup_kv, 
                apikey=Sys.getenv("GOO_KGR_KEY"))



oldest.3 <- map_df(oldest.2, function(x) { 
        
    as_data_frame(x ) %>% 
    filter(str_detect(key, "result.name|detailedDescription") == TRUE) %>% 
    select(key, value) %>% 
    mutate(key = str_replace(key, "result.detailedDescription.", ""), 
           value = unlist(value)) %>% 
    spread(key=key, value=value) %>% 
    select(-license) %>% 
    mutate(Name =str_c("[", result.name, "](", url, ")"),
           Description = str_replace_all(articleBody, "\n", "")) %>% 
    select(result.name, Name,  Description) })



oldest.3 <- oldest.3 %>% 
   left_join(oldest, by=c("result.name" = "player_name")) %>%
   rename("Born" = "year_of_birth", "N" = "n_matches") %>%
   select(-result.name) %>% 
        mutate(IsG = ifelse(str_detect( Description, "goal"), "Y", "")) %>% 
        mutate_if(is.numeric, function(x) ifelse(is.na(x), "", x)) %>% 
  select(Name, Description, Born, IsG, N)

In the table below, column ‘IsG’ means “Is Goalie”, ‘N’ means number of matches in Database.

It seems to be quite common that goalkeepers have long careers:

(Click on the Wikipedia links below for more information).

More quality checks: Tugay’s last matches

One of the older players is Tugay Kerimoğlu, born 1970. I didn’t know anything about him before. Wikipedia says Tugay played for the Blackburn Rovers during his last years.

Does the database also say that? Luckily, yes:

Let’s find out which teams Tugay played for during his last seasons. For each record in the Match table, there exists an entry with home_team_api_id and away_team_api_id. In order to resolve anonymous team-ids to the actual team names, I join the team-ids twice to the oldest_players_in_any_match table. This yields two augmented temporary tables.

# get a smaller version of the Team table
Team_sm <- Team %>%  select(id, team_api_id, team_long_name)

oldest_augm <- oldest_players_in_any_match %>%
        select(-season) %>%
        inner_join(Match, by = c("match_id" = "match_api_id")) %>%
        inner_join(Team_sm, by = c("home_team_api_id" = "team_api_id")) %>%
        rename("home_team" = "team_long_name") %>% 
        filter(player_name == "Tugay Kerimoglou")


oldest_augm2 <- oldest_augm %>%
        inner_join(Team_sm, by = c("away_team_api_id" = "team_api_id")) %>%
        rename("away_team" = "team_long_name")

oldest_augm2 %>%
        select(season, match_id, home_team, away_team) %>%
        arrange(season, match_id) %>% 
        ht()           # head() and tail()

## # A tibble: 12 x 4
##    season    match_id home_team        away_team           
##    <chr>        <int> <chr>            <chr>               
##  1 2008/2009   489097 Newcastle United Blackburn Rovers    
##  2 2008/2009   489107 Blackburn Rovers Manchester United   
##  3 2008/2009   489119 Bolton Wanderers Blackburn Rovers    
##  4 2008/2009   489188 Portsmouth       Blackburn Rovers    
##  5 2008/2009   489197 Blackburn Rovers Liverpool           
##  6 2008/2009   489210 Wigan Athletic   Blackburn Rovers    
##  7 2008/2009   489353 Liverpool        Blackburn Rovers    
##  8 2008/2009   489378 Blackburn Rovers Wigan Athletic      
##  9 2008/2009   489388 Manchester City  Blackburn Rovers    
## 10 2008/2009   489398 Blackburn Rovers Portsmouth          
## 11 2008/2009   489412 Chelsea          Blackburn Rovers    
## 12 2008/2009   489419 Blackburn Rovers West Bromwich Albion

So the database says that Tugay Kerimoğlu has played for Blackburn rovers, and did so until the 2008/2009 season. According to the Wikipedia article about him, this is true.

players_weird_names <- Player %>% 
        select(player_name) %>% 
        filter(str_detect(player_name, "\\d") == TRUE)