NB02_FirstAnalysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# First Analysis for Munich Traffic DWH\n",
    "\n",
    "## Connect"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import configparser\n",
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['dwh.cfg']"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "config = configparser.ConfigParser()\n",
    "config.read(\"dwh.cfg\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "connstring = config['Red']['DWH_CS']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Connected: dwhuser@sbmd1'"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql connstring"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Size\n",
    "Please find below some SQL queries, which show the size for each table in the data model"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://dwhuser:***@redshift-cluster-1sbmd.cg1lpo6kzhm9.eu-central-1.redshift.amazonaws.com:5439/sbmd1\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>314014</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(314014,)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT COUNT(*) FROM t01_delay_fact;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://dwhuser:***@redshift-cluster-1sbmd.cg1lpo6kzhm9.eu-central-1.redshift.amazonaws.com:5439/sbmd1\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1143</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1143,)]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT COUNT(*) FROM t02_dim_time;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://dwhuser:***@redshift-cluster-1sbmd.cg1lpo6kzhm9.eu-central-1.redshift.amazonaws.com:5439/sbmd1\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>22797</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(22797,)]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT COUNT(*) FROM t03_dim_conn;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://dwhuser:***@redshift-cluster-1sbmd.cg1lpo6kzhm9.eu-central-1.redshift.amazonaws.com:5439/sbmd1\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(2,)]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT COUNT(*) FROM t04_dim_part;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://dwhuser:***@redshift-cluster-1sbmd.cg1lpo6kzhm9.eu-central-1.redshift.amazonaws.com:5439/sbmd1\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>81267</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(81267,)]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT COUNT(*) FROM t05_dim_weather;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Examples for basic questions\n",
    "\n",
    "#### What is the average delay in % per transport method?\n",
    "Seems that the train times (part 1) take overall definitely longer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://dwhuser:***@redshift-cluster-1sbmd.cg1lpo6kzhm9.eu-central-1.redshift.amazonaws.com:5439/sbmd1\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>part_id</th>\n",
       "        <th>avg_delay_sec</th>\n",
       "        <th>avg_duration_sec</th>\n",
       "        <th>avg_delay_perc</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>-88.372477859947</td>\n",
       "        <td>4220.44777686479</td>\n",
       "        <td>-2.09391236504283</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>33.9061579963575</td>\n",
       "        <td>6398.35319470881</td>\n",
       "        <td>0.529920074190287</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(2, -88.372477859947, 4220.44777686479, -2.09391236504283),\n",
       " (1, 33.9061579963575, 6398.35319470881, 0.529920074190287)]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT\n",
    "part_id,\n",
    "AVG(delay_sec) AS Avg_Delay_Sec,\n",
    "AVG(duration_sec) AS Avg_Duration_Sec,\n",
    "AVG(delay_sec) / AVG(duration_sec) *100 AS Avg_Delay_Perc\n",
    "FROM\n",
    "t01_delay_fact\n",
    "GROUP BY \n",
    "part_id"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### What is the average delay in % per weekday for trains?\n",
    "Apparently - on a first look - there is defintely a high variance between the weekdays"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://dwhuser:***@redshift-cluster-1sbmd.cg1lpo6kzhm9.eu-central-1.redshift.amazonaws.com:5439/sbmd1\n",
      "7 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>weekday</th>\n",
       "        <th>avg_delay_perc</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>0</td>\n",
       "        <td>0.390186740325889</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>0.852500428660007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>0.712473580246334</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>0.846352723273282</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>-0.0840668843770511</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>0.25278087663306</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>6</td>\n",
       "        <td>0.570494855400964</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(0, 0.390186740325889),\n",
       " (1, 0.852500428660007),\n",
       " (2, 0.712473580246334),\n",
       " (3, 0.846352723273282),\n",
       " (4, -0.0840668843770511),\n",
       " (5, 0.25278087663306),\n",
       " (6, 0.570494855400964)]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT \n",
    "t.weekday,\n",
    "AVG(delay_sec) / AVG(duration_sec) * 100 AS Avg_Delay_Perc\n",
    "FROM\n",
    "t01_delay_fact f\n",
    "LEFT JOIN t02_dim_time t\n",
    "ON t.time_id = f.time_id\n",
    "WHERE f.part_id = 1\n",
    "GROUP BY \n",
    "t.weekday\n",
    "ORDER BY 1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### What is the average delay in % per hour for trains?\n",
    "Apparently - on a first look - there is defintely a high variance between the hours of the day. And there seems to be a pattern, that during the day the delays are increasing."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://dwhuser:***@redshift-cluster-1sbmd.cg1lpo6kzhm9.eu-central-1.redshift.amazonaws.com:5439/sbmd1\n",
      "24 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>hour</th>\n",
       "        <th>avg_delay_perc</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>0</td>\n",
       "        <td>0.554179984405692</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>0.615531758637769</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>0.428381458966565</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>0.319559717722249</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>0.296706737087606</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>0.423960189070874</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>6</td>\n",
       "        <td>0.642011116668265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>7</td>\n",
       "        <td>1.03773465497242</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "        <td>0.894398764110785</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>9</td>\n",
       "        <td>0.729050581189273</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10</td>\n",
       "        <td>0.611934168597119</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11</td>\n",
       "        <td>0.708731757499313</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12</td>\n",
       "        <td>0.656277265321827</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>13</td>\n",
       "        <td>0.978798654049521</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>14</td>\n",
       "        <td>0.660968347422368</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>15</td>\n",
       "        <td>1.03934482421693</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>16</td>\n",
       "        <td>1.01531353757583</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>17</td>\n",
       "        <td>1.2446260296781</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>18</td>\n",
       "        <td>1.28576952354715</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>19</td>\n",
       "        <td>1.6083066349543</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>20</td>\n",
       "        <td>1.54543518436948</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>21</td>\n",
       "        <td>-6.24636212640157</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>22</td>\n",
       "        <td>-23.4584625428432</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>23</td>\n",
       "        <td>-375.227383863081</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(0, 0.554179984405692),\n",
       " (1, 0.615531758637769),\n",
       " (2, 0.428381458966565),\n",
       " (3, 0.319559717722249),\n",
       " (4, 0.296706737087606),\n",
       " (5, 0.423960189070874),\n",
       " (6, 0.642011116668265),\n",
       " (7, 1.03773465497242),\n",
       " (8, 0.894398764110785),\n",
       " (9, 0.729050581189273),\n",
       " (10, 0.611934168597119),\n",
       " (11, 0.708731757499313),\n",
       " (12, 0.656277265321827),\n",
       " (13, 0.978798654049521),\n",
       " (14, 0.660968347422368),\n",
       " (15, 1.03934482421693),\n",
       " (16, 1.01531353757583),\n",
       " (17, 1.2446260296781),\n",
       " (18, 1.28576952354715),\n",
       " (19, 1.6083066349543),\n",
       " (20, 1.54543518436948),\n",
       " (21, -6.24636212640157),\n",
       " (22, -23.4584625428432),\n",
       " (23, -375.227383863081)]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT \n",
    "t.hour,\n",
    "AVG(delay_sec) / AVG(duration_sec) * 100 AS Avg_Delay_Perc\n",
    "FROM\n",
    "t01_delay_fact f\n",
    "LEFT JOIN t02_dim_time t\n",
    "ON t.time_id = f.time_id\n",
    "WHERE f.part_id = 1\n",
    "GROUP BY \n",
    "t.hour\n",
    "ORDER BY 1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### What is the average delay per weather state for trains?\n",
    "Apparently - on a first rough look - it seems that clear weather decreases the delay time."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://dwhuser:***@redshift-cluster-1sbmd.cg1lpo6kzhm9.eu-central-1.redshift.amazonaws.com:5439/sbmd1\n",
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>weather_status</th>\n",
       "        <th>avg_delay_perc</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Clear</td>\n",
       "        <td>-8.72413929178074e-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Clouds</td>\n",
       "        <td>0.00668589272750634</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Drizzle</td>\n",
       "        <td>0.0077923008513474</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Fog</td>\n",
       "        <td>0.00179210066901284</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mist</td>\n",
       "        <td>0.0048587014728483</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Rain</td>\n",
       "        <td>0.00788191824962308</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Snow</td>\n",
       "        <td>0.00963974486853289</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Squall</td>\n",
       "        <td>0.00179750506297259</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Thunderstorm</td>\n",
       "        <td>0.0185595275756617</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "        <td>0.00797852487843559</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Clear', -8.72413929178074e-05),\n",
       " ('Clouds', 0.00668589272750634),\n",
       " ('Drizzle', 0.0077923008513474),\n",
       " ('Fog', 0.00179210066901284),\n",
       " ('Mist', 0.0048587014728483),\n",
       " ('Rain', 0.00788191824962308),\n",
       " ('Snow', 0.00963974486853289),\n",
       " ('Squall', 0.00179750506297259),\n",
       " ('Thunderstorm', 0.0185595275756617),\n",
       " (None, 0.00797852487843559)]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT \n",
    "w.weather_status,\n",
    "AVG(delay_sec) / AVG(duration_sec) AS Avg_Delay_Perc\n",
    "FROM\n",
    "t01_delay_fact f\n",
    "LEFT JOIN t05_dim_weather w\n",
    "ON w.w_id = f.w_id\n",
    "WHERE f.part_id = 1\n",
    "GROUP BY \n",
    "w.weather_status\n",
    "ORDER BY 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}