Scraping SRF TV ratings.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Introduction"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this GitHub repo I maintain a data set on TV rating that is originally published by the Swiss broadcaster Schweizer Radio und Fernsehen (SRF). \n",
    "\n",
    "The SRF publishes the ratings of its TV channels SRF1 and SRFzwei on http://www.srf.ch/medien/publikumszahlen/ in PDF format (one PDF per day and channel). This makes it rather difficult to look at the program performance over time and/or to analyse the performance of certain shows.\n",
    "\n",
    "To get around this difficulty, I scraped and extracted the data from the PDFs. This notebook documents how this was done.\n",
    "\n",
    "## The elements\n",
    "\n",
    "To get from the PDF files to the dataset, several steps need to be made. So, we will write some functions in Python to achive the following tasks:\n",
    "\n",
    "1. I will generate the specific URLs corresponding to the date and channel we are interested in\n",
    "2. To download the PDF file, we will make a HTTP GET request.\n",
    "3. Then, we'll need to extract the text from the PDF and ...\n",
    "4. to format the text (rows and columns) so that it can be written in csv-like format.\n",
    "5. Finally, we will write the formatted text to a csv file.\n",
    "6. Additionally, we will need some helper functions and\n",
    "7. a wrapper function.\n",
    "\n",
    "Most of these functions are really straight forward. The main work is down by `get_program_pdf()` (generating the urls) and `convert_pdf()` (formatting the data). All functions a wrapped by `get_program_data()`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# I'll use the following packages trying to limit it to built-in packages (except for PyPDF2)\n",
    "# since later I'd like to run the script on a shared webhosting server\n",
    "\n",
    "import csv\n",
    "from datetime import datetime, timedelta\n",
    "import io\n",
    "import re\n",
    "import requests\n",
    "from PyPDF2 import PdfFileReader"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Reconstructing URLs\n",
    "\n",
    "The [SRF website](http://www.srf.ch/medien/publikumszahlen/) is Wordpress-based and uses a the common blog-like structure where the uploads a filed by year and month:\n",
    "\n",
    "http://www.srf.ch/medien/wp-content/uploads/<mark>2017/12/SRF1_171205_DI</mark>.pdf<br />\n",
    "http://www.srf.ch/medien/wp-content/uploads/2017/12/SRFzwei_171205_DI.pdf\n",
    "\n",
    "\n",
    "The name of the PDF files is structured along the name of the channel (e.g. SRF1 or SRFzwei), the date (formatted like `%y%m%d`) and the German abbreviation of the weekday (e.g., DI for Tuesday).\n",
    "\n",
    "Thus, in order to download the PDFs in an automated way, we first need to reconstruct the URL for each day for which we like to download the PDF.</p>\n",
    "\n",
    "The components needed are the following:\n",
    "\n",
    "* year: four digits (yyyy)\n",
    "* month: two digits (mm)\n",
    "* complete date: yymmdd (e.g., 171205)\n",
    "* weekday: german abbreviation\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_program_pdf(date, channel):\n",
    "    \"\"\"Constructs a URL for corresponding date and downloads the PDF file.\n",
    "    \n",
    "    Parameters:\n",
    "        date (string): '%Y-%m-%d'\n",
    "        channel (string): 'SRF1' or 'SRFzwei'\n",
    "        \n",
    "    returns a PDF file (file-like object)\n",
    "    \"\"\"\n",
    "    date = datetime.strptime(date, '%Y-%m-%d')\n",
    "    \n",
    "    # translation of weekday number to German weekday abbreviation\n",
    "    weekday_strings = {0: 'MO', 1: 'DI', 2: 'MI', 3: 'DO', 4: 'FR', 5: 'SA', 6: 'SO'}\n",
    "    \n",
    "    year = date.year        # year of the upload\n",
    "    m = date.strftime('%m') # month of the upload\n",
    "    wd_s = weekday_strings[date.weekday()]\n",
    "    short_date = date.strftime('%y%m%d')\n",
    "    \n",
    "    url = 'http://www.srf.ch/medien/wp-content/uploads/{0}/{1}/{2}_{3}_{4}.pdf'.format(year, m, channel, short_date, wd_s)\n",
    "\n",
    "    pdf = get_pdf(url)\n",
    "    \n",
    "    if pdf != None:\n",
    "        return pdf\n",
    "    else:\n",
    "        # the PDF files are uploaded with some delay, thus, the ratings of the last day(s) of the month are\n",
    "        # eventually uploaded in the following month\n",
    "        next_m = date + timedelta(days=5)\n",
    "        m = next_m.strftime('%m')\n",
    "        year = next_m.year\n",
    "        url = 'http://www.srf.ch/medien/wp-content/uploads/{0}/{1}/{2}_{3}_{4}.pdf'.format(year, m, channel, short_date, wd_s)\n",
    "        pdf = get_pdf(url)\n",
    "        if pdf != None:\n",
    "            return pdf\n",
    "        else:\n",
    "            raise ValueError(\"URL doesn't exist.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. HTTP GET request"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_pdf(url):\n",
    "    \"\"\"Downloads the specified PDF file.\n",
    "    \n",
    "    Parameter:\n",
    "        url (string): url\n",
    "        \n",
    "    returns file-like object or None\n",
    "    \"\"\"\n",
    "    # Look like a humane\n",
    "    headers = {'user-agent': 'Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:10.0) Gecko/20100101 Firefox/10.0'}\n",
    "    \n",
    "    rq = requests.get(url, headers=headers, timeout=5)\n",
    "    if rq.status_code == 200:\n",
    "        return io.BytesIO(rq.content)\n",
    "    else:\n",
    "        return None"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Extracting the text data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def extract_text(pdf):\n",
    "    \"\"\"Extracts the text of a PDF file.\n",
    "    \n",
    "    returns a string\n",
    "    \"\"\"\n",
    "    pdf = PdfFileReader(pdf)\n",
    "    page = pdf.getPage(0)\n",
    "    return page.extractText()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Formatting the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def convert_pdf(pdf, channel):\n",
    "    \"\"\"Converts the extracted PDF content to csv file format.\n",
    "    \n",
    "    Parameters:\n",
    "        pdf (io.ByteIO, file-like object): PDF file\n",
    "        channel (string): name of the channel\n",
    "        \n",
    "    Returns a list of lists\n",
    "    \"\"\"\n",
    "    txt = extract_text(pdf)\n",
    "    \n",
    "    # Splitting text into rows by \\n (end of line) if certain conditions are met\n",
    "    pat = re.compile('\\\\n(?=\\d{2}\\:\\d{2}\\\\n\\d{2}\\:\\d{2})|(?<=\\.\\d)\\\\n(?!\\d+\\\\n)')\n",
    "    rows = re.split(pat, txt)\n",
    "    \n",
    "    # Extract date from the first row\n",
    "    date_pat = re.compile('\\d{2}\\.\\d{2}\\.\\d{4}')\n",
    "    ref_date = datetime.strptime(re.findall(date_pat, rows[0])[0], '%d.%m.%Y')\n",
    "    \n",
    "    # Split rows into cells\n",
    "    table = [row.split('\\n') for row in rows[1:-1]]\n",
    "    \n",
    "    # The PDF columns Beginn and Ende contain only time\n",
    "    # So, we creating lists of datetime object to add the full date to each row\n",
    "    # using the reference date extracted from the first row\n",
    "    begin = [datetime.strptime(row[0], '%H:%M').time() for row in table]\n",
    "    end = [datetime.strptime(row[1], '%H:%M').time() for row in table]\n",
    "    \n",
    "    begin_fulldate = create_date(ref_date, begin)\n",
    "    end_fulldate = create_date(ref_date, end)\n",
    "    \n",
    "    # Ensure that the rows have the same length (9 columns)\n",
    "    # Rows with length 8 do not have a Untertitel in which case we'll insert an empty string ('')\n",
    "    # The last row contains meta information that will be cut off\n",
    "    new_table = []\n",
    "    for i, row in enumerate(table):\n",
    "        if len(row) == 8:\n",
    "            row.insert(4, '')\n",
    "        elif len(row) > 9:\n",
    "            row = row[:9]\n",
    "        row[0] = begin_fulldate[i]\n",
    "        row[1] = end_fulldate[i]\n",
    "        row.append(channel)\n",
    "        new_table.append(row)\n",
    "    \n",
    "    return new_table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. Writing the data to csv file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def write_csv(data, file_name):\n",
    "    \"\"\"Writes a csv file.\n",
    "    \n",
    "    Parameters:\n",
    "        data (list, array-like object): rows of the csv file\n",
    "        file:name (string): file name of the csv\n",
    "    \"\"\"\n",
    "    with open(file_name, 'a') as csvfile:\n",
    "        writer = csv.writer(csvfile)\n",
    "        writer.writerows(data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. Helper functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_date(d, ts):\n",
    "    \"\"\"Helper function to combine reference date and time.\n",
    "       \n",
    "   Parameters:\n",
    "       d (datetime.date object): reference date\n",
    "       ts (list, array-like object): datetime.time objects\n",
    "    \n",
    "    returns a list of datetime objects\n",
    "    \"\"\"\n",
    "    k = len(ts)\n",
    "    i = 1\n",
    "    dt_series = []\n",
    "    while i <= k:\n",
    "        try:\n",
    "            if ts[i-1] <= ts[i]:\n",
    "                dt_series.append(datetime.combine(d, ts[i-1]))\n",
    "            else:\n",
    "                dt_series.append(datetime.combine(d, ts[i-1]))\n",
    "                d = d + timedelta(days=1)\n",
    "        except IndexError:\n",
    "            dt_series.append(datetime.combine(d, ts[i-1])) # last row in the Series will be combined with date\n",
    "        i += 1\n",
    "    return [date.strftime('%Y%m%d %H:%M:%S') for date in dt_series]    \n",
    "    \n",
    "    \n",
    "def date_generator(lag=4):\n",
    "    \"\"\"Generates a date.\n",
    "    \n",
    "    Parameters:\n",
    "        lag (integer): time lag in days\n",
    "        \n",
    "    returns a date string\n",
    "    \"\"\"\n",
    "    date = datetime.today() - timedelta(days=lag)\n",
    "    return date.strftime('%Y-%m-%d')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. Main wrapper function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_program_data(date, channel, csvfile_name):\n",
    "    pdf = get_program_pdf(date, channel=channel)\n",
    "    data = convert_pdf(pdf, channel)\n",
    "    write_csv(data, file_name=csvfile_name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The script is executed like this:\n",
    "\n",
    "`get_program_data(date=\"2019-02-12\", channel=\"SRF1\", csvfile_name=\"srf_program_ratings.csv\")`"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}