Fussball.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# load the packages needed --> For Jupyter no installation needed\n",
    "library(data.table)\n",
    "library(rvest)\n",
    "library(magrittr)\n",
    "library(stringr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# The webpage for scraping the results\n",
    "url <- \"https://www.weltfussball.de/alle_spiele/bundesliga-2018-2019/\"\n",
    "# read the whole html-text into a variable\n",
    "content <- read_html(url)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "################## APPROACH 1 ############################"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Extract the first column of the table which holds the date\n",
    "Spieldatum <- content %>%\n",
    "  html_nodes(\".portfolio .standard_tabelle\") %>%\n",
    "  html_nodes(\"td:nth-of-type(1)\") %>% # get first column of table\n",
    "  html_text()\n",
    "\n",
    "Spieldatum %>% head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Extract the third column of the table which holds the name of the home team\n",
    "Heimteam <- content %>%\n",
    "  html_nodes(\".portfolio .standard_tabelle\") %>%\n",
    "  html_nodes(\"td:nth-of-type(3)\") %>% # get third column of table\n",
    "  html_text()\n",
    "\n",
    "Heimteam %>% head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Names of teams must not contain any spaces --> Werder Bremen should be WerderBremen\n",
    "Heimteam <- Heimteam %>% str_replace_all(\" \", \"\") \n",
    "\n",
    "Heimteam %>% head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Extract the fifth column of the table which holds the name of the home team\n",
    "Austeam <- content %>%\n",
    "  html_nodes(\".portfolio .standard_tabelle\") %>%\n",
    "  html_nodes(\"td:nth-of-type(5)\") %>% # get fifth column of table\n",
    "  html_text()\n",
    "\n",
    "Austeam %>% head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Names of teams must not contain any spaces --> Werder Bremen should be WerderBremen\n",
    "Austeam <- Austeam %>% str_replace_all(\" \", \"\")\n",
    "\n",
    "Austeam %>% head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Extract the sixth column of the table which holds the result of the game as well as the half-time score\n",
    "Ergebnis <- content %>%\n",
    "  html_nodes(\".portfolio .standard_tabelle\") %>%\n",
    "  html_nodes(\"td:nth-of-type(6)\") %>% # get sixth column of table\n",
    "  html_node(\"a\") %>% \n",
    "  html_text()\n",
    "\n",
    "Ergebnis %>% head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Extract from the scores only the final results\n",
    "Ergebnis <- Ergebnis %>% str_extract(\"^\\\\d+:\\\\d+\")\n",
    "\n",
    "Ergebnis %>% head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# combine all in one data.table\n",
    "resultTable <- data.table(Spieldatum,\n",
    "                          Heimteam, \n",
    "                          Austeam,\n",
    "                          Ergebnis)\n",
    "\n",
    "resultTable %>% head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# fill the missing dates with the previous ones.\n",
    "resultTable[, Spieldatum := Spieldatum[1], .(cumsum(Spieldatum != \"\"))]\n",
    "\n",
    "resultTable %>% head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# split the date into 3 columns; 24.08.2018 should be in three columns: 24 | 08 | 2018\n",
    "resultTable[, c(\"Tag\", \"Monat\", \"Jahr\") := tstrsplit(Spieldatum, \".\", fixed = TRUE)]\n",
    "\n",
    "resultTable %>% head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# split the result into 2 columns; 0:5 should be in two columns: 0 | 5\n",
    "resultTable[, c(\"Heim\", \"Aus\") := tstrsplit(Ergebnis, \":\", fixed = TRUE)]\n",
    "\n",
    "resultTable %>% head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# add the dummy column \"Bericht\" to make the data readable in Excel\n",
    "resultTable[, Bericht := \"Bericht\"]\n",
    "\n",
    "resultTable %>% head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "################## APPROACH 2 ############################"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Scrap the html-table as a whole (all columns at once).\n",
    "full_table <- content %>% \n",
    "  html_nodes(\".portfolio .standard_tabelle\") %>% \n",
    "  html_table()\n",
    "\n",
    "# We see that the table can also be read in total\n",
    "# The 1. indicates that we have the first element of a list of tables\n",
    "# From the structure of the website we know that only one table is available\n",
    "# with class \"standard_tabelle\".\n",
    "head(full_table)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Verify the data types of the scraped variable \"full_table\"\n",
    "full_table %>% class()      # as expected a list of data.frames\n",
    "full_table[[1]] %>% class() # the first element is a data.frame \n",
    "full_table  %>% str()       # get same info with structure  \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Because we only have one element in the list of data.frames we take this element \n",
    "# and save it as a data.table\n",
    "full_table <- full_table[[1]] %>% setDT()\n",
    "full_table %>% str()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Delete those columns which are not longer needed\n",
    "# we have to work with column-indices because all column-names are equal\n",
    "set(full_table, j = c(2L, 4L, 7L, 8L), value = NULL)\n",
    "full_table %>% head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# rename the columns\n",
    "setnames(full_table, c(\"Spieldatum\", \"Heimteam\", \"Austeam\", \"Ergebnis\"))\n",
    "full_table %>% head()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "R",
   "language": "R",
   "name": "ir"
  },
  "language_info": {
   "codemirror_mode": "r",
   "file_extension": ".r",
   "mimetype": "text/x-r-source",
   "name": "R",
   "pygments_lexer": "r",
   "version": "3.6.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}