02-weather_feature.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Weather Feature\n",
    "Die Wetterdaten aus der Radverkehrszählung der Standmünster werden geladen und in ein passendes Format überführt."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import pickle\n",
    "import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "with open('df_linie1_hin.pickle', 'rb') as fp:\n",
    "    df = pickle.load(fp)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Data can be downloaded from http://www.stadt-muenster.de/verkehrsplanung/verkehr-in-zahlen/radverkehrszaehlungen/wolbecker-strasse.html\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>Stunden</th>\n",
       "      <th>Wolbecker Straße (gesamt)</th>\n",
       "      <th>FR stadteinwärts</th>\n",
       "      <th>FR stadtauswärts</th>\n",
       "      <th>Temperatur (°C)</th>\n",
       "      <th>Windstärke (km/h)</th>\n",
       "      <th>Wetter</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01/01/2016 00:00</td>\n",
       "      <td>89</td>\n",
       "      <td>30.0</td>\n",
       "      <td>59.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>Klarer Himmel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01/01/2016 01:00</td>\n",
       "      <td>164</td>\n",
       "      <td>73.0</td>\n",
       "      <td>91.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>Klarer Himmel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>01/01/2016 02:00</td>\n",
       "      <td>188</td>\n",
       "      <td>101.0</td>\n",
       "      <td>87.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>Klarer Himmel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>01/01/2016 03:00</td>\n",
       "      <td>186</td>\n",
       "      <td>103.0</td>\n",
       "      <td>83.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>teilweise bewölkt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>01/01/2016 04:00</td>\n",
       "      <td>141</td>\n",
       "      <td>82.0</td>\n",
       "      <td>59.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>wolkig</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Stunden Wolbecker Straße (gesamt)  FR stadteinwärts  \\\n",
       "0  01/01/2016 00:00                        89              30.0   \n",
       "1  01/01/2016 01:00                       164              73.0   \n",
       "2  01/01/2016 02:00                       188             101.0   \n",
       "3  01/01/2016 03:00                       186             103.0   \n",
       "4  01/01/2016 04:00                       141              82.0   \n",
       "\n",
       "   FR stadtauswärts  Temperatur (°C)  Windstärke (km/h)             Wetter  \n",
       "0              59.0              3.0                9.0      Klarer Himmel  \n",
       "1              91.0              3.0                7.0      Klarer Himmel  \n",
       "2              87.0              3.0                9.0      Klarer Himmel  \n",
       "3              83.0              3.0                9.0  teilweise bewölkt  \n",
       "4              59.0              2.0                6.0             wolkig  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Load bicyle data that contains wheather information.\n",
    "df_bicyle = pd.read_csv('zaehlstelle_wolbecker_2016_stundenauswertung.csv', skipfooter=2, engine='python')\n",
    "df_bicyle.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Stunden                      datetime64[ns]\n",
       "Wolbecker Straße (gesamt)            object\n",
       "FR stadteinwärts                    float64\n",
       "FR stadtauswärts                    float64\n",
       "Temperatur (°C)                     float64\n",
       "Windstärke (km/h)                   float64\n",
       "Wetter                               object\n",
       "dtype: object"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Parse to timestamps.\n",
    "df_bicyle['Stunden'] = pd.to_datetime(df_bicyle['Stunden'], format='%d/%m/%Y %H:%M')\n",
    "df_bicyle.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>Stunden</th>\n",
       "      <th>Wolbecker Straße (gesamt)</th>\n",
       "      <th>FR stadteinwärts</th>\n",
       "      <th>FR stadtauswärts</th>\n",
       "      <th>Temperatur (°C)</th>\n",
       "      <th>Windstärke (km/h)</th>\n",
       "      <th>Wetter</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016-01-01 00:00:00</td>\n",
       "      <td>89</td>\n",
       "      <td>30.0</td>\n",
       "      <td>59.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>Klarer Himmel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016-01-01 01:00:00</td>\n",
       "      <td>164</td>\n",
       "      <td>73.0</td>\n",
       "      <td>91.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>Klarer Himmel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016-01-01 02:00:00</td>\n",
       "      <td>188</td>\n",
       "      <td>101.0</td>\n",
       "      <td>87.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>Klarer Himmel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016-01-01 03:00:00</td>\n",
       "      <td>186</td>\n",
       "      <td>103.0</td>\n",
       "      <td>83.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>teilweise bewölkt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2016-01-01 04:00:00</td>\n",
       "      <td>141</td>\n",
       "      <td>82.0</td>\n",
       "      <td>59.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>wolkig</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              Stunden Wolbecker Straße (gesamt)  FR stadteinwärts  \\\n",
       "0 2016-01-01 00:00:00                        89              30.0   \n",
       "1 2016-01-01 01:00:00                       164              73.0   \n",
       "2 2016-01-01 02:00:00                       188             101.0   \n",
       "3 2016-01-01 03:00:00                       186             103.0   \n",
       "4 2016-01-01 04:00:00                       141              82.0   \n",
       "\n",
       "   FR stadtauswärts  Temperatur (°C)  Windstärke (km/h)             Wetter  \n",
       "0              59.0              3.0                9.0      Klarer Himmel  \n",
       "1              91.0              3.0                7.0      Klarer Himmel  \n",
       "2              87.0              3.0                9.0      Klarer Himmel  \n",
       "3              83.0              3.0                9.0  teilweise bewölkt  \n",
       "4              59.0              2.0                6.0             wolkig  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Interpolate missing values using the value with forward fill.\n",
    "df_bicyle['Wetter'] = df_bicyle['Wetter'].fillna(method='ffill')\n",
    "df_bicyle['Temperatur (°C)'] = df_bicyle['Temperatur (°C)'].fillna(df_bicyle['Temperatur (°C)'].mean())\n",
    "df_bicyle['Windstärke (km/h)'] = df_bicyle['Windstärke (km/h)'].fillna(df_bicyle['Windstärke (km/h)'].mean())\n",
    "df_bicyle.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>Stunden</th>\n",
       "      <th>Wolbecker Straße (gesamt)</th>\n",
       "      <th>FR stadteinwärts</th>\n",
       "      <th>FR stadtauswärts</th>\n",
       "      <th>Temperatur (°C)</th>\n",
       "      <th>Windstärke (km/h)</th>\n",
       "      <th>Wetter</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017-01-01 00:00:00</td>\n",
       "      <td>89</td>\n",
       "      <td>30.0</td>\n",
       "      <td>59.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>Klarer Himmel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017-01-01 01:00:00</td>\n",
       "      <td>164</td>\n",
       "      <td>73.0</td>\n",
       "      <td>91.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>Klarer Himmel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017-01-01 02:00:00</td>\n",
       "      <td>188</td>\n",
       "      <td>101.0</td>\n",
       "      <td>87.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>Klarer Himmel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017-01-01 03:00:00</td>\n",
       "      <td>186</td>\n",
       "      <td>103.0</td>\n",
       "      <td>83.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>teilweise bewölkt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017-01-01 04:00:00</td>\n",
       "      <td>141</td>\n",
       "      <td>82.0</td>\n",
       "      <td>59.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>wolkig</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              Stunden Wolbecker Straße (gesamt)  FR stadteinwärts  \\\n",
       "0 2017-01-01 00:00:00                        89              30.0   \n",
       "1 2017-01-01 01:00:00                       164              73.0   \n",
       "2 2017-01-01 02:00:00                       188             101.0   \n",
       "3 2017-01-01 03:00:00                       186             103.0   \n",
       "4 2017-01-01 04:00:00                       141              82.0   \n",
       "\n",
       "   FR stadtauswärts  Temperatur (°C)  Windstärke (km/h)             Wetter  \n",
       "0              59.0              3.0                9.0      Klarer Himmel  \n",
       "1              91.0              3.0                7.0      Klarer Himmel  \n",
       "2              87.0              3.0                9.0      Klarer Himmel  \n",
       "3              83.0              3.0                9.0  teilweise bewölkt  \n",
       "4              59.0              2.0                6.0             wolkig  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Fake 2017 data, since they are missing.\n",
    "df_bicyle2017 = df_bicyle.copy()\n",
    "df_bicyle2017['Stunden'] = df_bicyle2017['Stunden'] + datetime.timedelta(days=366)\n",
    "df_bicyle2017.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>Stunden</th>\n",
       "      <th>Wolbecker Straße (gesamt)</th>\n",
       "      <th>FR stadteinwärts</th>\n",
       "      <th>FR stadtauswärts</th>\n",
       "      <th>Temperatur (°C)</th>\n",
       "      <th>Windstärke (km/h)</th>\n",
       "      <th>Wetter</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016-01-01 00:00:00</td>\n",
       "      <td>89</td>\n",
       "      <td>30.0</td>\n",
       "      <td>59.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>Klarer Himmel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016-01-01 01:00:00</td>\n",
       "      <td>164</td>\n",
       "      <td>73.0</td>\n",
       "      <td>91.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>Klarer Himmel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016-01-01 02:00:00</td>\n",
       "      <td>188</td>\n",
       "      <td>101.0</td>\n",
       "      <td>87.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>Klarer Himmel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016-01-01 03:00:00</td>\n",
       "      <td>186</td>\n",
       "      <td>103.0</td>\n",
       "      <td>83.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>teilweise bewölkt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2016-01-01 04:00:00</td>\n",
       "      <td>141</td>\n",
       "      <td>82.0</td>\n",
       "      <td>59.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>wolkig</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              Stunden Wolbecker Straße (gesamt)  FR stadteinwärts  \\\n",
       "0 2016-01-01 00:00:00                        89              30.0   \n",
       "1 2016-01-01 01:00:00                       164              73.0   \n",
       "2 2016-01-01 02:00:00                       188             101.0   \n",
       "3 2016-01-01 03:00:00                       186             103.0   \n",
       "4 2016-01-01 04:00:00                       141              82.0   \n",
       "\n",
       "   FR stadtauswärts  Temperatur (°C)  Windstärke (km/h)             Wetter  \n",
       "0              59.0              3.0                9.0      Klarer Himmel  \n",
       "1              91.0              3.0                7.0      Klarer Himmel  \n",
       "2              87.0              3.0                9.0      Klarer Himmel  \n",
       "3              83.0              3.0                9.0  teilweise bewölkt  \n",
       "4              59.0              2.0                6.0             wolkig  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Concat dataframes of 2017 and 2018.\n",
    "df_bicyle = pd.concat([df_bicyle, df_bicyle2017])\n",
    "df_bicyle.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>Stunden</th>\n",
       "      <th>Wolbecker Straße (gesamt)</th>\n",
       "      <th>FR stadteinwärts</th>\n",
       "      <th>FR stadtauswärts</th>\n",
       "      <th>Temperatur (°C)</th>\n",
       "      <th>Windstärke (km/h)</th>\n",
       "      <th>Wetter</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016-01-01 00:00:00</td>\n",
       "      <td>89</td>\n",
       "      <td>30.0</td>\n",
       "      <td>59.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>Klarer Himmel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016-01-01 01:00:00</td>\n",
       "      <td>164</td>\n",
       "      <td>73.0</td>\n",
       "      <td>91.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>Klarer Himmel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016-01-01 02:00:00</td>\n",
       "      <td>188</td>\n",
       "      <td>101.0</td>\n",
       "      <td>87.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>Klarer Himmel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016-01-01 03:00:00</td>\n",
       "      <td>186</td>\n",
       "      <td>103.0</td>\n",
       "      <td>83.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>teilweise bewölkt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2016-01-01 04:00:00</td>\n",
       "      <td>141</td>\n",
       "      <td>82.0</td>\n",
       "      <td>59.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>wolkig</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              Stunden Wolbecker Straße (gesamt)  FR stadteinwärts  \\\n",
       "0 2016-01-01 00:00:00                        89              30.0   \n",
       "1 2016-01-01 01:00:00                       164              73.0   \n",
       "2 2016-01-01 02:00:00                       188             101.0   \n",
       "3 2016-01-01 03:00:00                       186             103.0   \n",
       "4 2016-01-01 04:00:00                       141              82.0   \n",
       "\n",
       "   FR stadtauswärts  Temperatur (°C)  Windstärke (km/h)             Wetter  \n",
       "0              59.0              3.0                9.0      Klarer Himmel  \n",
       "1              91.0              3.0                7.0      Klarer Himmel  \n",
       "2              87.0              3.0                9.0      Klarer Himmel  \n",
       "3              83.0              3.0                9.0  teilweise bewölkt  \n",
       "4              59.0              2.0                6.0             wolkig  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_bicyle = df_bicyle.reset_index(drop=True)\n",
    "df_bicyle.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop useless columns.\n",
    "df_bicyle = df_bicyle.drop(columns=['Wolbecker Straße (gesamt)', 'FR stadteinwärts', 'FR stadtauswärts'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save results.\n",
    "df_bicyle.to_pickle('df_weather.pickle')"
   ]
  }
 ],
 "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
}