Cleaning Football Data with R Tidyverse

Cleaning data is a big part of data science. Being able to script data cleaning makes it repeatable and transparent. R, Python, Power Query and SQL are all good tools for it; here I’m using the tidyverse packages on European football data from 2005 to 2019.

Loading the data

Start by loading the CSV into R as a tibble. The packages you need for this can be installed with tidyverse: library(tidyverse).

setwd("/home/lee/Documents/Shiny/Football")
data <- as.tibble(read.csv("football_data.csv", header = TRUE))

The data set has around 27,000 rows across many fields.

Selecting fields

select() pulls out the columns that matter: the player positions (away.player.0 to away.player.11, home.player.0 to home.player.11) and the match information (home.name, away.name, date, league).

Pivoting

I don’t want the players in columns in this case, hence a pivot is required. gather() turns the player columns into rows, taking the data to around 60,000 rows.

Final cleaning

final_dat <-select (data_pivot,
c("home.name", "away.name", "date", "league","player")) %>%
add_column(real.date=dmy(data_pivot$date), count=1) %>%
filter(player !='')

final_dat <- final_dat[,-3]

That formats the date, adds a count field and drops blank player entries.

A Shiny application

With the data clean it’s a short step to an interactive pivot table in Shiny using the rpivotTable package. The server aggregates player appearances by league:

library(shiny)
library(rpivotTable)

shinyServer(function(input, output) {
  output$pivot <- renderRpivotTable({
rpivotTable(data = playerData(), rows=c("player"),
cols="league", vals="Count",
aggregatorName = "sum",
rendererName = "Table", width="100%", height="500%")
  })
})

And the UI:

library(shiny)
library(rpivotTable)

shinyUI(fluidPage(
titlePanel("Football Player Data"),
mainPanel(rpivotTableOutput("pivot"))
))

Summary

The tidyverse group of packages make this relatively painless. The complete code is on GitHub. Next steps would be adding time fields, joining the team data and calculating player performance statistics. The underlying data set comes from Kaggle.