{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "# Car accidents in Berlin\n",
    "\n",
    "## How did the amount of car accidents develop over the years in Berlin?\n",
    "\n",
    "- Set up requirements, import packages\n",
    "- Load data with datenguide\n",
    "- Plot results\n",
    "\n",
    "### Set up requirements, import packages\n",
    "\n",
    "Assumes to use code from local, parent directory as in the repository folder structure."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "%load_ext autoreload\n",
    "%autoreload\n",
    "\n",
    "import os\n",
    "if not os.path.basename(os.getcwd()) == \"datenguide-python\":\n",
    "    os.chdir(\"..\")\n",
    "    \n",
    "    \n",
    "from datenguidepy.query_helper import get_all_regions, get_statistics\n",
    "from datenguidepy import Query\n",
    "import pandas as pd\n",
    "import matplotlib\n",
    "%matplotlib inline\n",
    "\n",
    "pd.set_option('display.max_colwidth', 150)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "### Load data with datenguide"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# get the ID of Berlin by querying all states by name\n",
    "get_all_regions().query(\"name == 'Berlin'\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# find out the name of the desired statistic\n",
    "get_statistics().query('long_description.str.contains(\"Unfälle\")', engine='python')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "get_statistics().query('statistics.str.contains(\"AI1302\")', engine = 'python')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q = Query.region('11')\n",
    "\n",
    "f1 = q.add_field('AI1302')\n",
    "f2 = q.add_field('AI1304')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "f1.get_info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "f2.get_info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "### Plot results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "results = q.results()\n",
    "results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "results.set_index('year')[['AI1302', 'AI1304']].plot()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Same but interactive plot with plotly"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import plotly.graph_objs as go\n",
    "\n",
    "df = results.set_index('year')\n",
    "\n",
    "fig = go.Figure()\n",
    "fig.add_trace(\n",
    "    go.Scattergl(\n",
    "        x=df.index,\n",
    "        y=df['AI1302'],\n",
    "        mode=\"lines+markers\",\n",
    "        name=\"AI1302\",\n",
    "    )\n",
    ")\n",
    "\n",
    "fig.add_trace(\n",
    "    go.Scattergl(\n",
    "        x=df.index,\n",
    "        y=df['AI1304'],\n",
    "        mode=\"lines+markers\",\n",
    "        name=\"AI1304\",\n",
    "    )\n",
    ")\n",
    "\n",
    "fig.update_xaxes(title_text=\"Time\")\n",
    "fig.update_yaxes(title_text=\"Accidents\")\n",
    "\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Both, accidents per 10.000 inhabitants (AI1302), as well as killed people per 100.000 inhabitants (AI1304) decreased significantly especially between 1995 and 2005."
   ]
  }
 ],
 "metadata": {
  "file_extension": ".py",
  "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"
  },
  "mimetype": "text/x-python",
  "name": "python",
  "npconvert_exporter": "python",
  "pycharm": {
   "stem_cell": {
    "cell_type": "raw",
    "metadata": {
     "collapsed": false
    },
    "source": []
   }
  },
  "pygments_lexer": "ipython3",
  "version": 3
 },
 "nbformat": 4,
 "nbformat_minor": 4
}