01-cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Cleaning\n",
    "Die Rohdaten werden aus dem CSV eingelesen. Fehlende Daten werden entfernt und die Verspätung wird anhand \n",
    "von geplanter und tatsächlicher Abfahrtszeit berechnet."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Datum</th>\n",
       "      <th>Wochentag</th>\n",
       "      <th>Fahrweg</th>\n",
       "      <th>Fahrt</th>\n",
       "      <th>Fahrzeugnummer</th>\n",
       "      <th>Linienverlauf</th>\n",
       "      <th>SollAn</th>\n",
       "      <th>IstAn</th>\n",
       "      <th>SollAb</th>\n",
       "      <th>IstAb</th>\n",
       "      <th>GPS-Breite Soll</th>\n",
       "      <th>GPS-Länge Soll</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10.10.2016</td>\n",
       "      <td>Montag</td>\n",
       "      <td>2</td>\n",
       "      <td>1081890</td>\n",
       "      <td>5556</td>\n",
       "      <td>30 Ludgeriplatz B</td>\n",
       "      <td>05:17:00</td>\n",
       "      <td>05:17:58</td>\n",
       "      <td>05:17:00</td>\n",
       "      <td>05:17:58</td>\n",
       "      <td>519558997.0</td>\n",
       "      <td>76278508.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10.10.2016</td>\n",
       "      <td>Montag</td>\n",
       "      <td>2</td>\n",
       "      <td>1081890</td>\n",
       "      <td>5556</td>\n",
       "      <td>31 Goebenstr.</td>\n",
       "      <td>05:19:00</td>\n",
       "      <td>05:19:03</td>\n",
       "      <td>05:19:00</td>\n",
       "      <td>05:19:03</td>\n",
       "      <td>51952888.0</td>\n",
       "      <td>76254844.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10.10.2016</td>\n",
       "      <td>Montag</td>\n",
       "      <td>2</td>\n",
       "      <td>1081890</td>\n",
       "      <td>5556</td>\n",
       "      <td>32 St. Joseph-Kirche</td>\n",
       "      <td>05:20:00</td>\n",
       "      <td>05:20:00</td>\n",
       "      <td>05:20:00</td>\n",
       "      <td>05:20:00</td>\n",
       "      <td>519493516.0</td>\n",
       "      <td>76243561.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10.10.2016</td>\n",
       "      <td>Montag</td>\n",
       "      <td>2</td>\n",
       "      <td>1081890</td>\n",
       "      <td>5556</td>\n",
       "      <td>33 Alter Sch¿tzenhof A</td>\n",
       "      <td>05:21:00</td>\n",
       "      <td>05:20:44</td>\n",
       "      <td>05:21:00</td>\n",
       "      <td>05:20:44</td>\n",
       "      <td>519460313.0</td>\n",
       "      <td>76232794.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10.10.2016</td>\n",
       "      <td>Montag</td>\n",
       "      <td>2</td>\n",
       "      <td>1081890</td>\n",
       "      <td>5556</td>\n",
       "      <td>34 Metzer Str.</td>\n",
       "      <td>05:23:00</td>\n",
       "      <td>05:21:44</td>\n",
       "      <td>05:23:00</td>\n",
       "      <td>05:23:20</td>\n",
       "      <td>519422225.0</td>\n",
       "      <td>76234541.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Datum   Wochentag  Fahrweg    Fahrt  Fahrzeugnummer  \\\n",
       "0  10.10.2016  Montag            2  1081890            5556   \n",
       "1  10.10.2016  Montag            2  1081890            5556   \n",
       "2  10.10.2016  Montag            2  1081890            5556   \n",
       "3  10.10.2016  Montag            2  1081890            5556   \n",
       "4  10.10.2016  Montag            2  1081890            5556   \n",
       "\n",
       "            Linienverlauf    SollAn     IstAn    SollAb     IstAb  \\\n",
       "0       30 Ludgeriplatz B  05:17:00  05:17:58  05:17:00  05:17:58   \n",
       "1           31 Goebenstr.  05:19:00  05:19:03  05:19:00  05:19:03   \n",
       "2    32 St. Joseph-Kirche  05:20:00  05:20:00  05:20:00  05:20:00   \n",
       "3  33 Alter Sch¿tzenhof A  05:21:00  05:20:44  05:21:00  05:20:44   \n",
       "4          34 Metzer Str.  05:23:00  05:21:44  05:23:00  05:23:20   \n",
       "\n",
       "   GPS-Breite Soll  GPS-Länge Soll  \n",
       "0      519558997.0      76278508.0  \n",
       "1       51952888.0      76254844.0  \n",
       "2      519493516.0      76243561.0  \n",
       "3      519460313.0      76232794.0  \n",
       "4      519422225.0      76234541.0  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Load data with right types.\n",
    "dtypes = {\n",
    "    'Datum': str,\n",
    "    'Wochentag': str,\n",
    "    'Linienverlauf': str,\n",
    "    'SollAb': str,\n",
    "    'GPS-Breite Soll': np.float64,\n",
    "    'GPS-Länge Soll': np.float64,\n",
    "}\n",
    "\n",
    "df_linie1_hin_1 = pd.read_csv(filepath_or_buffer='Echtzeitdaten_Linie1_hin_sheet1.csv', \n",
    "                              sep=';', dtype=dtypes, thousands=',')\n",
    "df_linie1_hin_2 = pd.read_csv(filepath_or_buffer='Echtzeitdaten_Linie1_hin_sheet2.csv', \n",
    "                              sep=';', dtype=dtypes, thousands=',')\n",
    "df_linie1_hin_3 = pd.read_csv(filepath_or_buffer='Echtzeitdaten_Linie1_hin_sheet3.csv', \n",
    "                              sep=';', dtype=dtypes, thousands=',')\n",
    "df_linie1_hin_4 = pd.read_csv(filepath_or_buffer='Echtzeitdaten_Linie1_hin_sheet4.csv', \n",
    "                              sep=';', dtype=dtypes, thousands=',')\n",
    "df_linie1_hin_1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Datum               object\n",
       "Wochentag           object\n",
       "Fahrweg              int64\n",
       "Fahrt                int64\n",
       "Fahrzeugnummer       int64\n",
       "Linienverlauf       object\n",
       "SollAn              object\n",
       "IstAn               object\n",
       "SollAb              object\n",
       "IstAb               object\n",
       "GPS-Breite Soll    float64\n",
       "GPS-Länge Soll     float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check that parsing the right data types worked.\n",
    "df_linie1_hin_1.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Datum</th>\n",
       "      <th>Wochentag</th>\n",
       "      <th>Fahrweg</th>\n",
       "      <th>Fahrt</th>\n",
       "      <th>Fahrzeugnummer</th>\n",
       "      <th>Linienverlauf</th>\n",
       "      <th>SollAn</th>\n",
       "      <th>IstAn</th>\n",
       "      <th>SollAb</th>\n",
       "      <th>IstAb</th>\n",
       "      <th>GPS-Breite Soll</th>\n",
       "      <th>GPS-Länge Soll</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10.10.2016</td>\n",
       "      <td>Montag</td>\n",
       "      <td>2</td>\n",
       "      <td>1081890</td>\n",
       "      <td>5556</td>\n",
       "      <td>30 Ludgeriplatz B</td>\n",
       "      <td>05:17:00</td>\n",
       "      <td>05:17:58</td>\n",
       "      <td>05:17:00</td>\n",
       "      <td>05:17:58</td>\n",
       "      <td>519558997.0</td>\n",
       "      <td>76278508.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10.10.2016</td>\n",
       "      <td>Montag</td>\n",
       "      <td>2</td>\n",
       "      <td>1081890</td>\n",
       "      <td>5556</td>\n",
       "      <td>31 Goebenstr.</td>\n",
       "      <td>05:19:00</td>\n",
       "      <td>05:19:03</td>\n",
       "      <td>05:19:00</td>\n",
       "      <td>05:19:03</td>\n",
       "      <td>51952888.0</td>\n",
       "      <td>76254844.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10.10.2016</td>\n",
       "      <td>Montag</td>\n",
       "      <td>2</td>\n",
       "      <td>1081890</td>\n",
       "      <td>5556</td>\n",
       "      <td>32 St. Joseph-Kirche</td>\n",
       "      <td>05:20:00</td>\n",
       "      <td>05:20:00</td>\n",
       "      <td>05:20:00</td>\n",
       "      <td>05:20:00</td>\n",
       "      <td>519493516.0</td>\n",
       "      <td>76243561.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10.10.2016</td>\n",
       "      <td>Montag</td>\n",
       "      <td>2</td>\n",
       "      <td>1081890</td>\n",
       "      <td>5556</td>\n",
       "      <td>33 Alter Sch¿tzenhof A</td>\n",
       "      <td>05:21:00</td>\n",
       "      <td>05:20:44</td>\n",
       "      <td>05:21:00</td>\n",
       "      <td>05:20:44</td>\n",
       "      <td>519460313.0</td>\n",
       "      <td>76232794.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10.10.2016</td>\n",
       "      <td>Montag</td>\n",
       "      <td>2</td>\n",
       "      <td>1081890</td>\n",
       "      <td>5556</td>\n",
       "      <td>34 Metzer Str.</td>\n",
       "      <td>05:23:00</td>\n",
       "      <td>05:21:44</td>\n",
       "      <td>05:23:00</td>\n",
       "      <td>05:23:20</td>\n",
       "      <td>519422225.0</td>\n",
       "      <td>76234541.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Datum   Wochentag  Fahrweg    Fahrt  Fahrzeugnummer  \\\n",
       "0  10.10.2016  Montag            2  1081890            5556   \n",
       "1  10.10.2016  Montag            2  1081890            5556   \n",
       "2  10.10.2016  Montag            2  1081890            5556   \n",
       "3  10.10.2016  Montag            2  1081890            5556   \n",
       "4  10.10.2016  Montag            2  1081890            5556   \n",
       "\n",
       "            Linienverlauf    SollAn     IstAn    SollAb     IstAb  \\\n",
       "0       30 Ludgeriplatz B  05:17:00  05:17:58  05:17:00  05:17:58   \n",
       "1           31 Goebenstr.  05:19:00  05:19:03  05:19:00  05:19:03   \n",
       "2    32 St. Joseph-Kirche  05:20:00  05:20:00  05:20:00  05:20:00   \n",
       "3  33 Alter Sch¿tzenhof A  05:21:00  05:20:44  05:21:00  05:20:44   \n",
       "4          34 Metzer Str.  05:23:00  05:21:44  05:23:00  05:23:20   \n",
       "\n",
       "   GPS-Breite Soll  GPS-Länge Soll  \n",
       "0      519558997.0      76278508.0  \n",
       "1       51952888.0      76254844.0  \n",
       "2      519493516.0      76243561.0  \n",
       "3      519460313.0      76232794.0  \n",
       "4      519422225.0      76234541.0  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Combine the data frames into a single frame.\n",
    "df_linie1_hin = pd.concat([df_linie1_hin_1, df_linie1_hin_2, df_linie1_hin_3, df_linie1_hin_4], \n",
    "                          ignore_index=True)\n",
    "df_linie1_hin.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "9820"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# How many departure times are missing.\n",
    "len(df_linie1_hin[df_linie1_hin['IstAb'] == ' '])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Drop rows with missing values.\n",
    "df_linie1_hin = df_linie1_hin.drop(index=(df_linie1_hin[df_linie1_hin['IstAb'] == ' ']).index)\n",
    "df_linie1_hin = df_linie1_hin.reset_index(drop=True)\n",
    "len(df_linie1_hin[df_linie1_hin['IstAb'] == ' '])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(844111, 12)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# We still have enough values to work with.\n",
    "df_linie1_hin.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Datum              datetime64[ns]\n",
       "Wochentag                  object\n",
       "Fahrweg                     int64\n",
       "Fahrt                       int64\n",
       "Fahrzeugnummer              int64\n",
       "Linienverlauf              object\n",
       "SollAn                     object\n",
       "IstAn                      object\n",
       "SollAb             datetime64[ns]\n",
       "IstAb              datetime64[ns]\n",
       "GPS-Breite Soll           float64\n",
       "GPS-Länge Soll            float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Convert departure times to datetime objects while setting the correct date.\n",
    "df_linie1_hin.loc[:, 'SollAb'] = pd.to_datetime(df_linie1_hin['Datum'] + ' ' + df_linie1_hin['SollAb'], format='%d.%m.%Y %H:%M:%S')\n",
    "df_linie1_hin.loc[:, 'IstAb'] = pd.to_datetime(df_linie1_hin['Datum'] + ' ' + df_linie1_hin['IstAb'], format='%d.%m.%Y %H:%M:%S')\n",
    "df_linie1_hin.loc[:, 'Datum'] = pd.to_datetime(df_linie1_hin['Datum'], format='%d.%m.%Y')\n",
    "df_linie1_hin.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Datum</th>\n",
       "      <th>Wochentag</th>\n",
       "      <th>Fahrweg</th>\n",
       "      <th>Fahrt</th>\n",
       "      <th>Fahrzeugnummer</th>\n",
       "      <th>Linienverlauf</th>\n",
       "      <th>SollAb</th>\n",
       "      <th>IstAb</th>\n",
       "      <th>GPS-Breite Soll</th>\n",
       "      <th>GPS-Länge Soll</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016-10-10</td>\n",
       "      <td>Montag</td>\n",
       "      <td>2</td>\n",
       "      <td>1081890</td>\n",
       "      <td>5556</td>\n",
       "      <td>30 Ludgeriplatz B</td>\n",
       "      <td>2016-10-10 05:17:00</td>\n",
       "      <td>2016-10-10 05:17:58</td>\n",
       "      <td>519558997.0</td>\n",
       "      <td>76278508.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016-10-10</td>\n",
       "      <td>Montag</td>\n",
       "      <td>2</td>\n",
       "      <td>1081890</td>\n",
       "      <td>5556</td>\n",
       "      <td>31 Goebenstr.</td>\n",
       "      <td>2016-10-10 05:19:00</td>\n",
       "      <td>2016-10-10 05:19:03</td>\n",
       "      <td>51952888.0</td>\n",
       "      <td>76254844.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016-10-10</td>\n",
       "      <td>Montag</td>\n",
       "      <td>2</td>\n",
       "      <td>1081890</td>\n",
       "      <td>5556</td>\n",
       "      <td>32 St. Joseph-Kirche</td>\n",
       "      <td>2016-10-10 05:20:00</td>\n",
       "      <td>2016-10-10 05:20:00</td>\n",
       "      <td>519493516.0</td>\n",
       "      <td>76243561.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016-10-10</td>\n",
       "      <td>Montag</td>\n",
       "      <td>2</td>\n",
       "      <td>1081890</td>\n",
       "      <td>5556</td>\n",
       "      <td>33 Alter Sch¿tzenhof A</td>\n",
       "      <td>2016-10-10 05:21:00</td>\n",
       "      <td>2016-10-10 05:20:44</td>\n",
       "      <td>519460313.0</td>\n",
       "      <td>76232794.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2016-10-10</td>\n",
       "      <td>Montag</td>\n",
       "      <td>2</td>\n",
       "      <td>1081890</td>\n",
       "      <td>5556</td>\n",
       "      <td>34 Metzer Str.</td>\n",
       "      <td>2016-10-10 05:23:00</td>\n",
       "      <td>2016-10-10 05:23:20</td>\n",
       "      <td>519422225.0</td>\n",
       "      <td>76234541.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Datum   Wochentag  Fahrweg    Fahrt  Fahrzeugnummer  \\\n",
       "0 2016-10-10  Montag            2  1081890            5556   \n",
       "1 2016-10-10  Montag            2  1081890            5556   \n",
       "2 2016-10-10  Montag            2  1081890            5556   \n",
       "3 2016-10-10  Montag            2  1081890            5556   \n",
       "4 2016-10-10  Montag            2  1081890            5556   \n",
       "\n",
       "            Linienverlauf              SollAb               IstAb  \\\n",
       "0       30 Ludgeriplatz B 2016-10-10 05:17:00 2016-10-10 05:17:58   \n",
       "1           31 Goebenstr. 2016-10-10 05:19:00 2016-10-10 05:19:03   \n",
       "2    32 St. Joseph-Kirche 2016-10-10 05:20:00 2016-10-10 05:20:00   \n",
       "3  33 Alter Sch¿tzenhof A 2016-10-10 05:21:00 2016-10-10 05:20:44   \n",
       "4          34 Metzer Str. 2016-10-10 05:23:00 2016-10-10 05:23:20   \n",
       "\n",
       "   GPS-Breite Soll  GPS-Länge Soll  \n",
       "0      519558997.0      76278508.0  \n",
       "1       51952888.0      76254844.0  \n",
       "2      519493516.0      76243561.0  \n",
       "3      519460313.0      76232794.0  \n",
       "4      519422225.0      76234541.0  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Discard arrival times as they are not really important.\n",
    "df_linie1_hin = df_linie1_hin.drop(columns=['SollAn', 'IstAn'])\n",
    "df_linie1_hin.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Datum</th>\n",
       "      <th>Wochentag</th>\n",
       "      <th>Fahrweg</th>\n",
       "      <th>Fahrt</th>\n",
       "      <th>Fahrzeugnummer</th>\n",
       "      <th>Linienverlauf</th>\n",
       "      <th>SollAb</th>\n",
       "      <th>IstAb</th>\n",
       "      <th>GPS-Breite Soll</th>\n",
       "      <th>GPS-Länge Soll</th>\n",
       "      <th>Verspätung</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016-10-10</td>\n",
       "      <td>Montag</td>\n",
       "      <td>2</td>\n",
       "      <td>1081890</td>\n",
       "      <td>5556</td>\n",
       "      <td>30 Ludgeriplatz B</td>\n",
       "      <td>2016-10-10 05:17:00</td>\n",
       "      <td>2016-10-10 05:17:58</td>\n",
       "      <td>519558997.0</td>\n",
       "      <td>76278508.0</td>\n",
       "      <td>00:00:58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016-10-10</td>\n",
       "      <td>Montag</td>\n",
       "      <td>2</td>\n",
       "      <td>1081890</td>\n",
       "      <td>5556</td>\n",
       "      <td>31 Goebenstr.</td>\n",
       "      <td>2016-10-10 05:19:00</td>\n",
       "      <td>2016-10-10 05:19:03</td>\n",
       "      <td>51952888.0</td>\n",
       "      <td>76254844.0</td>\n",
       "      <td>00:00:03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016-10-10</td>\n",
       "      <td>Montag</td>\n",
       "      <td>2</td>\n",
       "      <td>1081890</td>\n",
       "      <td>5556</td>\n",
       "      <td>32 St. Joseph-Kirche</td>\n",
       "      <td>2016-10-10 05:20:00</td>\n",
       "      <td>2016-10-10 05:20:00</td>\n",
       "      <td>519493516.0</td>\n",
       "      <td>76243561.0</td>\n",
       "      <td>00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016-10-10</td>\n",
       "      <td>Montag</td>\n",
       "      <td>2</td>\n",
       "      <td>1081890</td>\n",
       "      <td>5556</td>\n",
       "      <td>33 Alter Sch¿tzenhof A</td>\n",
       "      <td>2016-10-10 05:21:00</td>\n",
       "      <td>2016-10-10 05:20:44</td>\n",
       "      <td>519460313.0</td>\n",
       "      <td>76232794.0</td>\n",
       "      <td>-1 days +23:59:44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2016-10-10</td>\n",
       "      <td>Montag</td>\n",
       "      <td>2</td>\n",
       "      <td>1081890</td>\n",
       "      <td>5556</td>\n",
       "      <td>34 Metzer Str.</td>\n",
       "      <td>2016-10-10 05:23:00</td>\n",
       "      <td>2016-10-10 05:23:20</td>\n",
       "      <td>519422225.0</td>\n",
       "      <td>76234541.0</td>\n",
       "      <td>00:00:20</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Datum   Wochentag  Fahrweg    Fahrt  Fahrzeugnummer  \\\n",
       "0 2016-10-10  Montag            2  1081890            5556   \n",
       "1 2016-10-10  Montag            2  1081890            5556   \n",
       "2 2016-10-10  Montag            2  1081890            5556   \n",
       "3 2016-10-10  Montag            2  1081890            5556   \n",
       "4 2016-10-10  Montag            2  1081890            5556   \n",
       "\n",
       "            Linienverlauf              SollAb               IstAb  \\\n",
       "0       30 Ludgeriplatz B 2016-10-10 05:17:00 2016-10-10 05:17:58   \n",
       "1           31 Goebenstr. 2016-10-10 05:19:00 2016-10-10 05:19:03   \n",
       "2    32 St. Joseph-Kirche 2016-10-10 05:20:00 2016-10-10 05:20:00   \n",
       "3  33 Alter Sch¿tzenhof A 2016-10-10 05:21:00 2016-10-10 05:20:44   \n",
       "4          34 Metzer Str. 2016-10-10 05:23:00 2016-10-10 05:23:20   \n",
       "\n",
       "   GPS-Breite Soll  GPS-Länge Soll        Verspätung  \n",
       "0      519558997.0      76278508.0          00:00:58  \n",
       "1       51952888.0      76254844.0          00:00:03  \n",
       "2      519493516.0      76243561.0          00:00:00  \n",
       "3      519460313.0      76232794.0 -1 days +23:59:44  \n",
       "4      519422225.0      76234541.0          00:00:20  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Calculate the delay.\n",
    "df_linie1_hin['Verspätung'] = df_linie1_hin['IstAb'] - df_linie1_hin['SollAb']\n",
    "df_linie1_hin.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Datum</th>\n",
       "      <th>Wochentag</th>\n",
       "      <th>Fahrweg</th>\n",
       "      <th>Fahrt</th>\n",
       "      <th>Fahrzeugnummer</th>\n",
       "      <th>Linienverlauf</th>\n",
       "      <th>SollAb</th>\n",
       "      <th>IstAb</th>\n",
       "      <th>GPS-Breite Soll</th>\n",
       "      <th>GPS-Länge Soll</th>\n",
       "      <th>Verspätung</th>\n",
       "      <th>Verspätung_total_seconds</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016-10-10</td>\n",
       "      <td>Montag</td>\n",
       "      <td>2</td>\n",
       "      <td>1081890</td>\n",
       "      <td>5556</td>\n",
       "      <td>30 Ludgeriplatz B</td>\n",
       "      <td>2016-10-10 05:17:00</td>\n",
       "      <td>2016-10-10 05:17:58</td>\n",
       "      <td>519558997.0</td>\n",
       "      <td>76278508.0</td>\n",
       "      <td>00:00:58</td>\n",
       "      <td>58.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016-10-10</td>\n",
       "      <td>Montag</td>\n",
       "      <td>2</td>\n",
       "      <td>1081890</td>\n",
       "      <td>5556</td>\n",
       "      <td>31 Goebenstr.</td>\n",
       "      <td>2016-10-10 05:19:00</td>\n",
       "      <td>2016-10-10 05:19:03</td>\n",
       "      <td>51952888.0</td>\n",
       "      <td>76254844.0</td>\n",
       "      <td>00:00:03</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016-10-10</td>\n",
       "      <td>Montag</td>\n",
       "      <td>2</td>\n",
       "      <td>1081890</td>\n",
       "      <td>5556</td>\n",
       "      <td>32 St. Joseph-Kirche</td>\n",
       "      <td>2016-10-10 05:20:00</td>\n",
       "      <td>2016-10-10 05:20:00</td>\n",
       "      <td>519493516.0</td>\n",
       "      <td>76243561.0</td>\n",
       "      <td>00:00:00</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016-10-10</td>\n",
       "      <td>Montag</td>\n",
       "      <td>2</td>\n",
       "      <td>1081890</td>\n",
       "      <td>5556</td>\n",
       "      <td>33 Alter Sch¿tzenhof A</td>\n",
       "      <td>2016-10-10 05:21:00</td>\n",
       "      <td>2016-10-10 05:20:44</td>\n",
       "      <td>519460313.0</td>\n",
       "      <td>76232794.0</td>\n",
       "      <td>-1 days +23:59:44</td>\n",
       "      <td>-16.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2016-10-10</td>\n",
       "      <td>Montag</td>\n",
       "      <td>2</td>\n",
       "      <td>1081890</td>\n",
       "      <td>5556</td>\n",
       "      <td>34 Metzer Str.</td>\n",
       "      <td>2016-10-10 05:23:00</td>\n",
       "      <td>2016-10-10 05:23:20</td>\n",
       "      <td>519422225.0</td>\n",
       "      <td>76234541.0</td>\n",
       "      <td>00:00:20</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Datum   Wochentag  Fahrweg    Fahrt  Fahrzeugnummer  \\\n",
       "0 2016-10-10  Montag            2  1081890            5556   \n",
       "1 2016-10-10  Montag            2  1081890            5556   \n",
       "2 2016-10-10  Montag            2  1081890            5556   \n",
       "3 2016-10-10  Montag            2  1081890            5556   \n",
       "4 2016-10-10  Montag            2  1081890            5556   \n",
       "\n",
       "            Linienverlauf              SollAb               IstAb  \\\n",
       "0       30 Ludgeriplatz B 2016-10-10 05:17:00 2016-10-10 05:17:58   \n",
       "1           31 Goebenstr. 2016-10-10 05:19:00 2016-10-10 05:19:03   \n",
       "2    32 St. Joseph-Kirche 2016-10-10 05:20:00 2016-10-10 05:20:00   \n",
       "3  33 Alter Sch¿tzenhof A 2016-10-10 05:21:00 2016-10-10 05:20:44   \n",
       "4          34 Metzer Str. 2016-10-10 05:23:00 2016-10-10 05:23:20   \n",
       "\n",
       "   GPS-Breite Soll  GPS-Länge Soll        Verspätung  Verspätung_total_seconds  \n",
       "0      519558997.0      76278508.0          00:00:58                      58.0  \n",
       "1       51952888.0      76254844.0          00:00:03                       3.0  \n",
       "2      519493516.0      76243561.0          00:00:00                       0.0  \n",
       "3      519460313.0      76232794.0 -1 days +23:59:44                     -16.0  \n",
       "4      519422225.0      76234541.0          00:00:20                      20.0  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Transform delay from timedelta to seconds.\n",
    "df_linie1_hin['Verspätung_total_seconds'] = df_linie1_hin['Verspätung'].dt.total_seconds()\n",
    "df_linie1_hin.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save results.\n",
    "df_linie1_hin.to_pickle('df_linie1_hin.pickle')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.6.6"
  },
  "widgets": {
   "state": {},
   "version": "1.1.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}