volumes/miovision/dev_notebooks/05 - Compare Miovision API and CSV Data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Compare Miovision API and CSV Data\n",
    "\n",
    "Now that we have an old API pull, a new API pull and a CSV dump from Miovision, we can check the data integrity of the three. I chose to do this by binning the raw minute-bin data in `miovision_csv.volumes_2020` and `miovision_api.volumes` up to the hour, and joining the two aggregate tables together."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import psycopg2\n",
    "import datetime\n",
    "import pytz\n",
    "import pathlib\n",
    "import configparser\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "from plotly import graph_objs as go\n",
    "from ipywidgets import interact\n",
    "\n",
    "import intersection_tmc_notebook03test as itmc\n",
    "\n",
    "config = configparser.ConfigParser()\n",
    "config.read(pathlib.Path.home().joinpath('.charlesconfig').as_posix())\n",
    "postgres_settings = config['POSTGRES']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql_query = \"\"\"SELECT intersection_uid,\n",
    "   date_trunc('hour', datetime_bin) count_date,\n",
    "   classification_uid,\n",
    "   SUM(volume) volume_newapi,\n",
    "   SUM(volume_20201007) volume_oldapi\n",
    "FROM miovision_api.volumes\n",
    "WHERE datetime_bin BETWEEN '2019-01-01' AND '2020-09-30 23:59:59'\n",
    "GROUP BY intersection_uid, date_trunc('hour', datetime_bin), classification_uid\n",
    "ORDER BY 1, 2, 3\n",
    "\"\"\"\n",
    "\n",
    "with psycopg2.connect(database='bigdata', **postgres_settings) as conn:\n",
    "    df_api = pd.read_sql(sql_query, con=conn)\n",
    "\n",
    "sql_query = \"\"\"SELECT intersection_uid,\n",
    "       date_trunc('hour', datetime_bin) count_date,\n",
    "       classification_uid,\n",
    "       SUM(volume) volume_csv\n",
    "FROM miovision_csv.volumes_2020\n",
    "WHERE datetime_bin BETWEEN '2019-01-01' AND '2020-09-30 23:59:59'\n",
    "GROUP BY intersection_uid, date_trunc('hour', datetime_bin), classification_uid\n",
    "ORDER BY 1, 2, 3\n",
    "\"\"\"\n",
    "\n",
    "with psycopg2.connect(database='bigdata', **postgres_settings) as conn:\n",
    "    df_csv = pd.read_sql(sql_query, con=conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0         2019-01-01 00:00:00\n",
       "1         2019-01-01 00:00:00\n",
       "2         2019-01-01 00:00:00\n",
       "3         2019-01-01 00:00:00\n",
       "4         2019-01-01 01:00:00\n",
       "                  ...        \n",
       "2032666   2020-09-30 23:00:00\n",
       "2032667   2020-09-30 23:00:00\n",
       "2032668   2020-09-30 23:00:00\n",
       "2032669   2020-09-30 23:00:00\n",
       "2032670   2020-09-30 23:00:00\n",
       "Name: count_date, Length: 2032671, dtype: datetime64[ns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_api['count_date']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.merge(df_api, df_csv, how='outer', on=('intersection_uid', 'count_date', 'classification_uid'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>intersection_uid</th>\n",
       "      <th>count_date</th>\n",
       "      <th>classification_uid</th>\n",
       "      <th>volume_newapi</th>\n",
       "      <th>volume_oldapi</th>\n",
       "      <th>volume_csv</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2032671</th>\n",
       "      <td>2</td>\n",
       "      <td>2020-04-16 23:00:00</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2032672</th>\n",
       "      <td>5</td>\n",
       "      <td>2019-04-29 04:00:00</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2032673</th>\n",
       "      <td>5</td>\n",
       "      <td>2019-07-23 03:00:00</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2032674</th>\n",
       "      <td>5</td>\n",
       "      <td>2019-12-03 18:00:00</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2032675</th>\n",
       "      <td>5</td>\n",
       "      <td>2020-01-05 19:00:00</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2502772</th>\n",
       "      <td>39</td>\n",
       "      <td>2020-09-30 09:00:00</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>335.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2502773</th>\n",
       "      <td>39</td>\n",
       "      <td>2020-09-30 10:00:00</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>336.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2502774</th>\n",
       "      <td>39</td>\n",
       "      <td>2020-09-30 11:00:00</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>481.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2502775</th>\n",
       "      <td>40</td>\n",
       "      <td>2020-07-28 14:00:00</td>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2502776</th>\n",
       "      <td>40</td>\n",
       "      <td>2020-07-28 17:00:00</td>\n",
       "      <td>9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>470106 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         intersection_uid          count_date  classification_uid  \\\n",
       "2032671                 2 2020-04-16 23:00:00                   4   \n",
       "2032672                 5 2019-04-29 04:00:00                   6   \n",
       "2032673                 5 2019-07-23 03:00:00                   6   \n",
       "2032674                 5 2019-12-03 18:00:00                   6   \n",
       "2032675                 5 2020-01-05 19:00:00                   6   \n",
       "...                   ...                 ...                 ...   \n",
       "2502772                39 2020-09-30 09:00:00                   6   \n",
       "2502773                39 2020-09-30 10:00:00                   6   \n",
       "2502774                39 2020-09-30 11:00:00                   6   \n",
       "2502775                40 2020-07-28 14:00:00                   5   \n",
       "2502776                40 2020-07-28 17:00:00                   9   \n",
       "\n",
       "         volume_newapi  volume_oldapi  volume_csv  \n",
       "2032671            NaN            NaN         1.0  \n",
       "2032672            NaN            NaN         1.0  \n",
       "2032673            NaN            NaN         1.0  \n",
       "2032674            NaN            NaN         5.0  \n",
       "2032675            NaN            NaN         1.0  \n",
       "...                ...            ...         ...  \n",
       "2502772            NaN            NaN       335.0  \n",
       "2502773            NaN            NaN       336.0  \n",
       "2502774            NaN            NaN       481.0  \n",
       "2502775            NaN            NaN         1.0  \n",
       "2502776            NaN            NaN         1.0  \n",
       "\n",
       "[470106 rows x 6 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[df['volume_newapi'].isna(), :]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Turns out we're missing a ton of data. This is because `intersection_tmc.py` [checks](https://github.com/CityofToronto/bdit_data-sources/blob/master/volumes/miovision/api/intersection_tmc.py#L312-L329) that the activation date is before the start of the pull period, and the decommission date is before the current day (that should really be the last timestamp of the pull...). In total, 563,109 rows of data are missing.\n",
    "\n",
    "There's also data missing in the CSV pull. For almost all `intersection_uid`s the number of missing rows is less than 10. The exceptions are King / Jarvis (UID 20) and Queen / Jarvis (25)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "20    4281\n",
       "25     272\n",
       "31       4\n",
       "4        3\n",
       "7        3\n",
       "6        2\n",
       "22       2\n",
       "1        2\n",
       "28       2\n",
       "12       2\n",
       "18       1\n",
       "10       1\n",
       "2        1\n",
       "37       1\n",
       "23       1\n",
       "29       1\n",
       "40       1\n",
       "24       1\n",
       "33       1\n",
       "Name: intersection_uid, dtype: int64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[df['volume_csv'].isna(), 'intersection_uid'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_20missing = df.loc[df['volume_csv'].isna() &\n",
    "                      (df['intersection_uid'] == 20), :].sort_values(['count_date', 'classification_uid'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2020-09-01    4130\n",
       "2020-08-01     148\n",
       "2020-05-01       1\n",
       "2020-07-01       1\n",
       "2020-04-01       1\n",
       "Name: count_date, dtype: int64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(df_20missing['count_date'].dt.date + pd.offsets.MonthBegin(-1)).value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_25missing = df.loc[df['volume_csv'].isna() &\n",
    "                      (df['intersection_uid'] == 25), :].sort_values(['count_date', 'classification_uid'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2020-08-01    137\n",
       "2020-09-01    133\n",
       "2019-09-01      1\n",
       "2019-01-01      1\n",
       "Name: count_date, dtype: int64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(df_25missing['count_date'].dt.date + pd.offsets.MonthBegin(-1)).value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So some data is missing in August and September from 20 and 25.\n",
    "\n",
    "What of the data that isn't missing? These could be actual differences in minute-by-minute counts, or (much more likely) because we have missing rows of data in one dataset or the other."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_both = pd.merge(df_api, df_csv, how='inner', on=('intersection_uid', 'count_date', 'classification_uid'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_comparison_plot(intersect_uid=26, class_uid=1):\n",
    "\n",
    "    dfc = df_both.loc[(df_both['intersection_uid'] == intersect_uid) &\n",
    "                      (df_both['classification_uid'] == class_uid), :]\n",
    "    dfc = dfc.set_index('count_date').drop(columns=['intersection_uid', 'classification_uid'])\n",
    "\n",
    "    fig = go.Figure()\n",
    "\n",
    "    fig.add_trace(go.Scatter(\n",
    "        x=dfc['volume_newapi'].index,\n",
    "        y=dfc['volume_csv'] - dfc['volume_newapi'],\n",
    "        mode='lines',\n",
    "        name='CSV - New API'))\n",
    "\n",
    "    fig.add_trace(go.Scatter(\n",
    "        x=dfc['volume_newapi'].index,\n",
    "        y=dfc['volume_csv'] - dfc['volume_oldapi'],\n",
    "        mode='lines',\n",
    "        name='CSV - Old API'))\n",
    "\n",
    "    fig.add_trace(go.Scatter(\n",
    "        x=dfc['volume_oldapi'].index,\n",
    "        y=dfc['volume_newapi'] - dfc['volume_oldapi'],\n",
    "        mode='lines',\n",
    "        name='New - Old API'))\n",
    "    \n",
    "    fig.update_layout(\n",
    "        title={\n",
    "            'text': (\"Volumes Differences for \"\n",
    "                     \"intersection_uid = {0}; class = {1}\").format(intersect_uid,\n",
    "                                                                   class_uid),\n",
    "            'font_size': 14\n",
    "        },\n",
    "        xaxis_title=\"Date\",\n",
    "        yaxis_title=\"Volume Differences\",\n",
    "        xaxis_rangeslider_visible=True,\n",
    "        margin=dict(l=40, r=40, t=80, b=40),\n",
    "    )\n",
    "  \n",
    "    nonzero_diff = (dfc['volume_csv'] - dfc['volume_newapi']).values\n",
    "    nonzero_diff = nonzero_diff[nonzero_diff != 0]\n",
    "\n",
    "    fig2 = go.Figure()\n",
    "\n",
    "    fig2.add_trace(\n",
    "        go.Histogram(\n",
    "            histfunc=\"count\",\n",
    "            x=nonzero_diff,\n",
    "            nbinsx=30,\n",
    "        )\n",
    "    )\n",
    "\n",
    "    fig2.update_layout(\n",
    "        title={\n",
    "            'text': (\"Histogram of Nonzero CSV - New API Differences (N_hours = {0})\"\n",
    "                     .format(nonzero_diff.shape[0])),\n",
    "            'font_size': 14\n",
    "        },\n",
    "        xaxis_title=\"CSV - New\",\n",
    "        yaxis_title=\"Number of Hours\",\n",
    "        height=200,\n",
    "        margin=dict(l=40, r=40, t=50, b=40),\n",
    "    )\n",
    "    \n",
    "    fig.show()\n",
    "    fig2.show();"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "5372d0474c3147f1acae74f0f5e4652e",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "interactive(children=(Dropdown(description='intersect_uid', index=19, options=(1, 2, 3, 4, 5, 6, 7, 8, 10, 12,…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "intnames = sorted(list(df_both['intersection_uid'].unique()))\n",
    "class_uids = sorted(list(df_both['classification_uid'].unique()))\n",
    "\n",
    "interact(get_comparison_plot, intersect_uid=intnames,\n",
    "         class_uid=class_uids);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>intersection_uid</th>\n",
       "      <th>count_date</th>\n",
       "      <th>classification_uid</th>\n",
       "      <th>volume_newapi</th>\n",
       "      <th>volume_oldapi</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1937648</th>\n",
       "      <td>33</td>\n",
       "      <td>2020-06-23 15:00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>1255</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1937649</th>\n",
       "      <td>33</td>\n",
       "      <td>2020-06-23 15:00:00</td>\n",
       "      <td>2</td>\n",
       "      <td>42</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1937650</th>\n",
       "      <td>33</td>\n",
       "      <td>2020-06-23 15:00:00</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1937651</th>\n",
       "      <td>33</td>\n",
       "      <td>2020-06-23 15:00:00</td>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1937652</th>\n",
       "      <td>33</td>\n",
       "      <td>2020-06-23 15:00:00</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1951778</th>\n",
       "      <td>33</td>\n",
       "      <td>2020-09-30 23:00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>923</td>\n",
       "      <td>923.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1951779</th>\n",
       "      <td>33</td>\n",
       "      <td>2020-09-30 23:00:00</td>\n",
       "      <td>2</td>\n",
       "      <td>29</td>\n",
       "      <td>29.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1951780</th>\n",
       "      <td>33</td>\n",
       "      <td>2020-09-30 23:00:00</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1951781</th>\n",
       "      <td>33</td>\n",
       "      <td>2020-09-30 23:00:00</td>\n",
       "      <td>4</td>\n",
       "      <td>14</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1951782</th>\n",
       "      <td>33</td>\n",
       "      <td>2020-09-30 23:00:00</td>\n",
       "      <td>6</td>\n",
       "      <td>92</td>\n",
       "      <td>92.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>14135 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         intersection_uid          count_date  classification_uid  \\\n",
       "1937648                33 2020-06-23 15:00:00                   1   \n",
       "1937649                33 2020-06-23 15:00:00                   2   \n",
       "1937650                33 2020-06-23 15:00:00                   3   \n",
       "1937651                33 2020-06-23 15:00:00                   4   \n",
       "1937652                33 2020-06-23 15:00:00                   5   \n",
       "...                   ...                 ...                 ...   \n",
       "1951778                33 2020-09-30 23:00:00                   1   \n",
       "1951779                33 2020-09-30 23:00:00                   2   \n",
       "1951780                33 2020-09-30 23:00:00                   3   \n",
       "1951781                33 2020-09-30 23:00:00                   4   \n",
       "1951782                33 2020-09-30 23:00:00                   6   \n",
       "\n",
       "         volume_newapi  volume_oldapi  \n",
       "1937648           1255            NaN  \n",
       "1937649             42            NaN  \n",
       "1937650              2            NaN  \n",
       "1937651             13            NaN  \n",
       "1937652              1            NaN  \n",
       "...                ...            ...  \n",
       "1951778            923          923.0  \n",
       "1951779             29           29.0  \n",
       "1951780              2            2.0  \n",
       "1951781             14           14.0  \n",
       "1951782             92           92.0  \n",
       "\n",
       "[14135 rows x 5 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_api.loc[df_api['intersection_uid'] == 33]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Adelaide / Jarvis\n",
    "\n",
    "As discussed [here](https://github.com/CityofToronto/bdit_data-sources/issues/331#issuecomment-714705192), when looking at the difference in volume between CSV and new or old API pulls, we see 0 for the most part, punctuated by upward and downward spikes. The upward spikes are where the CSV dump has a higher volume, and the downward spikes are where the API pull has more.\n",
    "\n",
    "Differences in volume are due to either\n",
    "- Differences in the raw minute-bin data or\n",
    "- Missing minutes that result in a lower volume when aggregating up to the nearest hour.\n",
    "\n",
    "To investigate further, we look at a negative spike (CSV has less data) on 2020-08-08 10:00-12:00 and a positive spike (CSV has more data) on 2019-11-03 01:00 and one on 2019-06-11 21:00:00."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql_query = \"\"\"SELECT datetime_bin,\n",
    "\t   leg,\n",
    "\t   movement_uid,\n",
    "\t   volume_csv,\n",
    "\t   volume_api\n",
    "FROM (\n",
    "\tSELECT datetime_bin,\n",
    "            leg,\n",
    "\t\t\tmovement_uid,\n",
    "\t\t\tvolume volume_csv\n",
    "\tFROM miovision_csv.volumes_2020\n",
    "\tWHERE intersection_uid = 4 AND classification_uid = 1\n",
    "\t\tAND datetime_bin BETWEEN '2020-08-08 10:50:00' AND '2020-08-08 11:05:00'\n",
    ") a\n",
    "FULL OUTER JOIN (\n",
    "\tSELECT datetime_bin,\n",
    "\t       leg,\n",
    "\t       movement_uid,\n",
    "\t       volume volume_api\n",
    "\tFROM miovision_api.volumes\n",
    "\tWHERE intersection_uid = 4 AND classification_uid = 1\n",
    "\t\tAND datetime_bin BETWEEN '2020-08-08 10:50:00' AND '2020-08-08 11:05:00'\n",
    ") b USING (datetime_bin, leg, movement_uid)\n",
    "WHERE (volume_csv IS NULL) OR (volume_api IS NULL) OR (volume_api != volume_csv)\n",
    "ORDER BY datetime_bin, leg, movement_uid\"\"\"\n",
    "\n",
    "with psycopg2.connect(database='bigdata', **postgres_settings) as conn:\n",
    "    df_20200808_nspike = pd.read_sql(sql_query, con=conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>datetime_bin</th>\n",
       "      <th>leg</th>\n",
       "      <th>movement_uid</th>\n",
       "      <th>volume_csv</th>\n",
       "      <th>volume_api</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-08-08 10:51:00</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>11.0</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-08-08 10:51:00</td>\n",
       "      <td>N</td>\n",
       "      <td>2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-08-08 10:51:00</td>\n",
       "      <td>S</td>\n",
       "      <td>1</td>\n",
       "      <td>5.0</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-08-08 10:51:00</td>\n",
       "      <td>W</td>\n",
       "      <td>1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-08-08 10:51:00</td>\n",
       "      <td>W</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2020-08-08 10:52:00</td>\n",
       "      <td>W</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2020-08-08 10:52:00</td>\n",
       "      <td>W</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2020-08-08 10:52:00</td>\n",
       "      <td>W</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2020-08-08 10:53:00</td>\n",
       "      <td>S</td>\n",
       "      <td>1</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2020-08-08 10:53:00</td>\n",
       "      <td>W</td>\n",
       "      <td>1</td>\n",
       "      <td>5.0</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2020-08-08 10:53:00</td>\n",
       "      <td>W</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2020-08-08 10:54:00</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2020-08-08 10:54:00</td>\n",
       "      <td>N</td>\n",
       "      <td>2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2020-08-08 10:54:00</td>\n",
       "      <td>S</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2020-08-08 10:55:00</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>11.0</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2020-08-08 10:56:00</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2020-08-08 10:56:00</td>\n",
       "      <td>S</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2020-08-08 10:56:00</td>\n",
       "      <td>W</td>\n",
       "      <td>1</td>\n",
       "      <td>8.0</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2020-08-08 10:56:00</td>\n",
       "      <td>W</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2020-08-08 10:57:00</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>2020-08-08 10:57:00</td>\n",
       "      <td>N</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>2020-08-08 10:57:00</td>\n",
       "      <td>S</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>2020-08-08 10:57:00</td>\n",
       "      <td>W</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>2020-08-08 10:57:00</td>\n",
       "      <td>W</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>2020-08-08 10:58:00</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>3.0</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>2020-08-08 10:58:00</td>\n",
       "      <td>N</td>\n",
       "      <td>2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>2020-08-08 10:58:00</td>\n",
       "      <td>S</td>\n",
       "      <td>1</td>\n",
       "      <td>5.0</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>2020-08-08 10:58:00</td>\n",
       "      <td>W</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>2020-08-08 11:00:00</td>\n",
       "      <td>W</td>\n",
       "      <td>1</td>\n",
       "      <td>6.0</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>2020-08-08 11:00:00</td>\n",
       "      <td>W</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>2020-08-08 11:00:00</td>\n",
       "      <td>W</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>2020-08-08 11:01:00</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>2020-08-08 11:01:00</td>\n",
       "      <td>N</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>2020-08-08 11:01:00</td>\n",
       "      <td>S</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>2020-08-08 11:01:00</td>\n",
       "      <td>W</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>2020-08-08 11:01:00</td>\n",
       "      <td>W</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>2020-08-08 11:02:00</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>4.0</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>2020-08-08 11:02:00</td>\n",
       "      <td>N</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>2020-08-08 11:02:00</td>\n",
       "      <td>W</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>2020-08-08 11:04:00</td>\n",
       "      <td>W</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>2020-08-08 11:04:00</td>\n",
       "      <td>W</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>2020-08-08 11:05:00</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>2020-08-08 11:05:00</td>\n",
       "      <td>S</td>\n",
       "      <td>1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>2020-08-08 11:05:00</td>\n",
       "      <td>W</td>\n",
       "      <td>1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          datetime_bin leg  movement_uid  volume_csv  volume_api\n",
       "0  2020-08-08 10:51:00   N             1        11.0          12\n",
       "1  2020-08-08 10:51:00   N             2         1.0           2\n",
       "2  2020-08-08 10:51:00   S             1         5.0          10\n",
       "3  2020-08-08 10:51:00   W             1         2.0           5\n",
       "4  2020-08-08 10:51:00   W             3         NaN           1\n",
       "5  2020-08-08 10:52:00   W             1         NaN          13\n",
       "6  2020-08-08 10:52:00   W             2         NaN           2\n",
       "7  2020-08-08 10:52:00   W             3         NaN           2\n",
       "8  2020-08-08 10:53:00   S             1         4.0           5\n",
       "9  2020-08-08 10:53:00   W             1         5.0          13\n",
       "10 2020-08-08 10:53:00   W             2         NaN           1\n",
       "11 2020-08-08 10:54:00   N             1         NaN           7\n",
       "12 2020-08-08 10:54:00   N             2         1.0           4\n",
       "13 2020-08-08 10:54:00   S             1         NaN           8\n",
       "14 2020-08-08 10:55:00   N             1        11.0          13\n",
       "15 2020-08-08 10:56:00   N             1         NaN           4\n",
       "16 2020-08-08 10:56:00   S             1         1.0           2\n",
       "17 2020-08-08 10:56:00   W             1         8.0          11\n",
       "18 2020-08-08 10:56:00   W             3         NaN           1\n",
       "19 2020-08-08 10:57:00   N             1         NaN           8\n",
       "20 2020-08-08 10:57:00   N             2         NaN           1\n",
       "21 2020-08-08 10:57:00   S             1         NaN           3\n",
       "22 2020-08-08 10:57:00   W             1         NaN          10\n",
       "23 2020-08-08 10:57:00   W             3         NaN           1\n",
       "24 2020-08-08 10:58:00   N             1         3.0           9\n",
       "25 2020-08-08 10:58:00   N             2         1.0           3\n",
       "26 2020-08-08 10:58:00   S             1         5.0           8\n",
       "27 2020-08-08 10:58:00   W             1         1.0           3\n",
       "28 2020-08-08 11:00:00   W             1         6.0          13\n",
       "29 2020-08-08 11:00:00   W             2         NaN           1\n",
       "30 2020-08-08 11:00:00   W             3         NaN           1\n",
       "31 2020-08-08 11:01:00   N             1         NaN          13\n",
       "32 2020-08-08 11:01:00   N             2         NaN           1\n",
       "33 2020-08-08 11:01:00   S             1         NaN           8\n",
       "34 2020-08-08 11:01:00   W             1         NaN          11\n",
       "35 2020-08-08 11:01:00   W             3         NaN           1\n",
       "36 2020-08-08 11:02:00   N             1         4.0           9\n",
       "37 2020-08-08 11:02:00   N             2         NaN           1\n",
       "38 2020-08-08 11:02:00   W             1         NaN           7\n",
       "39 2020-08-08 11:04:00   W             1         NaN           7\n",
       "40 2020-08-08 11:04:00   W             2         NaN           2\n",
       "41 2020-08-08 11:05:00   N             1         NaN          11\n",
       "42 2020-08-08 11:05:00   S             1         2.0           8\n",
       "43 2020-08-08 11:05:00   W             1         2.0           3"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_20200808_nspike"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Meanwhile, the 2019-11-03 is due to duplicate timestamps from Daylight Savings Time."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>datetime_bin</th>\n",
       "      <th>leg</th>\n",
       "      <th>movement_uid</th>\n",
       "      <th>volume_csv</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-11-03 01:00:00</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-11-03 01:00:00</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-11-03 01:00:00</td>\n",
       "      <td>N</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-11-03 01:00:00</td>\n",
       "      <td>N</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-11-03 01:00:00</td>\n",
       "      <td>S</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>2019-11-03 01:10:00</td>\n",
       "      <td>S</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>2019-11-03 01:10:00</td>\n",
       "      <td>S</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>2019-11-03 01:10:00</td>\n",
       "      <td>W</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>2019-11-03 01:10:00</td>\n",
       "      <td>W</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>2019-11-03 01:10:00</td>\n",
       "      <td>W</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>104 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           datetime_bin leg  movement_uid  volume_csv\n",
       "0   2019-11-03 01:00:00   N             1          10\n",
       "1   2019-11-03 01:00:00   N             1          17\n",
       "2   2019-11-03 01:00:00   N             2           3\n",
       "3   2019-11-03 01:00:00   N             2           1\n",
       "4   2019-11-03 01:00:00   S             1           4\n",
       "..                  ...  ..           ...         ...\n",
       "99  2019-11-03 01:10:00   S             1           4\n",
       "100 2019-11-03 01:10:00   S             1           5\n",
       "101 2019-11-03 01:10:00   W             1           6\n",
       "102 2019-11-03 01:10:00   W             1           4\n",
       "103 2019-11-03 01:10:00   W             2           1\n",
       "\n",
       "[104 rows x 4 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_query = \"\"\"SELECT datetime_bin,\n",
    "             leg,\n",
    "             movement_uid,\n",
    "             volume volume_csv\n",
    "\tFROM miovision_csv.volumes_2020\n",
    "\tWHERE intersection_uid = 4 AND classification_uid = 1\n",
    "\t\tAND datetime_bin BETWEEN '2019-11-03 01:00:00' AND '2019-11-03 01:10:00'\n",
    "\tORDER BY 1, 2, 3\"\"\"\n",
    "\n",
    "with psycopg2.connect(database='bigdata', **postgres_settings) as conn:\n",
    "    df_csv_20191103 = pd.read_sql(sql_query, con=conn)\n",
    "    \n",
    "df_csv_20191103"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, for 2019-06-11 21:00:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>datetime_bin</th>\n",
       "      <th>leg</th>\n",
       "      <th>movement_uid</th>\n",
       "      <th>volume_csv</th>\n",
       "      <th>volume_api</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-06-11 21:17:00</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-06-11 21:17:00</td>\n",
       "      <td>S</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-06-11 21:17:00</td>\n",
       "      <td>W</td>\n",
       "      <td>1</td>\n",
       "      <td>16</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-06-11 21:17:00</td>\n",
       "      <td>W</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-06-11 21:17:00</td>\n",
       "      <td>W</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>2019-06-11 21:32:00</td>\n",
       "      <td>S</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>2019-06-11 21:32:00</td>\n",
       "      <td>W</td>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td>2019-06-11 21:32:00</td>\n",
       "      <td>W</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>2019-06-11 21:32:00</td>\n",
       "      <td>W</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>2019-06-11 21:33:00</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>80 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          datetime_bin leg  movement_uid  volume_csv  volume_api\n",
       "0  2019-06-11 21:17:00   N             1           2         NaN\n",
       "1  2019-06-11 21:17:00   S             1           5         NaN\n",
       "2  2019-06-11 21:17:00   W             1          16         NaN\n",
       "3  2019-06-11 21:17:00   W             2           2         NaN\n",
       "4  2019-06-11 21:17:00   W             3           3         NaN\n",
       "..                 ...  ..           ...         ...         ...\n",
       "75 2019-06-11 21:32:00   S             1           1         NaN\n",
       "76 2019-06-11 21:32:00   W             1          15         NaN\n",
       "77 2019-06-11 21:32:00   W             2           2         NaN\n",
       "78 2019-06-11 21:32:00   W             3           2         NaN\n",
       "79 2019-06-11 21:33:00   N             1          10         8.0\n",
       "\n",
       "[80 rows x 5 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_query = \"\"\"SELECT datetime_bin,\n",
    "\t   leg,\n",
    "\t   movement_uid,\n",
    "\t   volume_csv,\n",
    "\t   volume_api\n",
    "FROM (\n",
    "\tSELECT datetime_bin,\n",
    "            leg,\n",
    "\t\t\tmovement_uid,\n",
    "\t\t\tvolume volume_csv\n",
    "\tFROM miovision_csv.volumes_2020\n",
    "\tWHERE intersection_uid = 4 AND classification_uid = 1\n",
    "\t\tAND datetime_bin BETWEEN '2019-06-11 21:15:00' AND '2019-06-11 21:35:00'\n",
    ") a\n",
    "FULL OUTER JOIN (\n",
    "\tSELECT datetime_bin,\n",
    "\t       leg,\n",
    "\t       movement_uid,\n",
    "\t       volume volume_api\n",
    "\tFROM miovision_api.volumes\n",
    "\tWHERE intersection_uid = 4 AND classification_uid = 1\n",
    "\t\tAND datetime_bin BETWEEN '2019-06-11 21:15:00' AND '2019-06-11 21:35:00'\n",
    ") b USING (datetime_bin, leg, movement_uid)\n",
    "WHERE (volume_csv IS NULL) OR (volume_api IS NULL) OR (volume_api != volume_csv)\n",
    "ORDER BY datetime_bin, leg, movement_uid\"\"\"\n",
    "\n",
    "with psycopg2.connect(database='bigdata', **postgres_settings) as conn:\n",
    "    df_20190611_pspike = pd.read_sql(sql_query, con=conn)\n",
    "\n",
    "df_20190611_pspike"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here we're mainly just missing 15 minutes of data on the API side."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Richmond / Bathurst\n",
    "\n",
    "This is discussed [here]().\n",
    "\n",
    "We look at 2019-11-09 08:00 - 13:00 and 2019-06-11 21:00 (again)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>datetime_bin</th>\n",
       "      <th>leg</th>\n",
       "      <th>movement_uid</th>\n",
       "      <th>volume_csv</th>\n",
       "      <th>volume_api</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-11-09 08:16:00</td>\n",
       "      <td>E</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-11-09 08:16:00</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-11-09 08:16:00</td>\n",
       "      <td>S</td>\n",
       "      <td>1</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-11-09 08:17:00</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>9.0</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-11-09 09:12:00</td>\n",
       "      <td>E</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>142</th>\n",
       "      <td>2019-11-09 12:21:00</td>\n",
       "      <td>W</td>\n",
       "      <td>3</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143</th>\n",
       "      <td>2019-11-09 12:22:00</td>\n",
       "      <td>E</td>\n",
       "      <td>2</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>144</th>\n",
       "      <td>2019-11-09 12:22:00</td>\n",
       "      <td>E</td>\n",
       "      <td>3</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>145</th>\n",
       "      <td>2019-11-09 12:22:00</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>11.0</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146</th>\n",
       "      <td>2019-11-09 12:22:00</td>\n",
       "      <td>W</td>\n",
       "      <td>3</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>147 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           datetime_bin leg  movement_uid  volume_csv  volume_api\n",
       "0   2019-11-09 08:16:00   E             2         NaN           1\n",
       "1   2019-11-09 08:16:00   N             1         NaN           6\n",
       "2   2019-11-09 08:16:00   S             1         3.0           5\n",
       "3   2019-11-09 08:17:00   N             1         9.0          11\n",
       "4   2019-11-09 09:12:00   E             2         NaN           1\n",
       "..                  ...  ..           ...         ...         ...\n",
       "142 2019-11-09 12:21:00   W             3         1.0           3\n",
       "143 2019-11-09 12:22:00   E             2         2.0           3\n",
       "144 2019-11-09 12:22:00   E             3         2.0           3\n",
       "145 2019-11-09 12:22:00   N             1        11.0          15\n",
       "146 2019-11-09 12:22:00   W             3         1.0           2\n",
       "\n",
       "[147 rows x 5 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_query = \"\"\"SELECT datetime_bin,\n",
    "\t   leg,\n",
    "\t   movement_uid,\n",
    "\t   volume_csv,\n",
    "\t   volume_api\n",
    "FROM (\n",
    "\tSELECT datetime_bin,\n",
    "            leg,\n",
    "\t\t\tmovement_uid,\n",
    "\t\t\tvolume volume_csv\n",
    "\tFROM miovision_csv.volumes_2020\n",
    "\tWHERE intersection_uid = 26 AND classification_uid = 1\n",
    "\t\tAND datetime_bin BETWEEN '2019-11-09 08:00:00' AND '2019-11-09 13:00:00'\n",
    ") a\n",
    "FULL OUTER JOIN (\n",
    "\tSELECT datetime_bin,\n",
    "\t       leg,\n",
    "\t       movement_uid,\n",
    "\t       volume volume_api\n",
    "\tFROM miovision_api.volumes\n",
    "\tWHERE intersection_uid = 26 AND classification_uid = 1\n",
    "\t\tAND datetime_bin BETWEEN '2019-11-09 08:00:00' AND '2019-11-09 13:00:00'\n",
    ") b USING (datetime_bin, leg, movement_uid)\n",
    "WHERE (volume_csv IS NULL) OR (volume_api IS NULL) OR (volume_api != volume_csv)\n",
    "ORDER BY datetime_bin, leg, movement_uid\"\"\"\n",
    "\n",
    "with psycopg2.connect(database='bigdata', **postgres_settings) as conn:\n",
    "    df_20191109_nspike = pd.read_sql(sql_query, con=conn)\n",
    "\n",
    "df_20191109_nspike"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>datetime_bin</th>\n",
       "      <th>leg</th>\n",
       "      <th>movement_uid</th>\n",
       "      <th>volume_csv</th>\n",
       "      <th>volume_api</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-06-11 21:18:00</td>\n",
       "      <td>E</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-06-11 21:18:00</td>\n",
       "      <td>E</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-06-11 21:18:00</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-06-11 21:18:00</td>\n",
       "      <td>S</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-06-11 21:19:00</td>\n",
       "      <td>E</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>2019-06-11 21:31:00</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>2019-06-11 21:31:00</td>\n",
       "      <td>S</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>2019-06-11 21:32:00</td>\n",
       "      <td>E</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>2019-06-11 21:32:00</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>2019-06-11 21:32:00</td>\n",
       "      <td>S</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>62 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          datetime_bin leg  movement_uid  volume_csv  volume_api\n",
       "0  2019-06-11 21:18:00   E             2           3         NaN\n",
       "1  2019-06-11 21:18:00   E             3          10         NaN\n",
       "2  2019-06-11 21:18:00   N             1          12         NaN\n",
       "3  2019-06-11 21:18:00   S             1           2         NaN\n",
       "4  2019-06-11 21:19:00   E             2           2         NaN\n",
       "..                 ...  ..           ...         ...         ...\n",
       "57 2019-06-11 21:31:00   N             1           4         NaN\n",
       "58 2019-06-11 21:31:00   S             1           6         NaN\n",
       "59 2019-06-11 21:32:00   E             3           7         3.0\n",
       "60 2019-06-11 21:32:00   N             1           8         NaN\n",
       "61 2019-06-11 21:32:00   S             1           8         NaN\n",
       "\n",
       "[62 rows x 5 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_query = \"\"\"SELECT datetime_bin,\n",
    "\t   leg,\n",
    "\t   movement_uid,\n",
    "\t   volume_csv,\n",
    "\t   volume_api\n",
    "FROM (\n",
    "\tSELECT datetime_bin,\n",
    "            leg,\n",
    "\t\t\tmovement_uid,\n",
    "\t\t\tvolume volume_csv\n",
    "\tFROM miovision_csv.volumes_2020\n",
    "\tWHERE intersection_uid = 26 AND classification_uid = 1\n",
    "\t\tAND datetime_bin BETWEEN '2019-06-11 21:00:00' AND '2019-06-11 21:59:00'\n",
    ") a\n",
    "FULL OUTER JOIN (\n",
    "\tSELECT datetime_bin,\n",
    "\t       leg,\n",
    "\t       movement_uid,\n",
    "\t       volume volume_api\n",
    "\tFROM miovision_api.volumes\n",
    "\tWHERE intersection_uid = 26 AND classification_uid = 1\n",
    "\t\tAND datetime_bin BETWEEN '2019-06-11 21:00:00' AND '2019-06-11 21:59:00'\n",
    ") b USING (datetime_bin, leg, movement_uid)\n",
    "WHERE (volume_csv IS NULL) OR (volume_api IS NULL) OR (volume_api != volume_csv)\n",
    "ORDER BY datetime_bin, leg, movement_uid\"\"\"\n",
    "\n",
    "with psycopg2.connect(database='bigdata', **postgres_settings) as conn:\n",
    "    df_20190611_pspike = pd.read_sql(sql_query, con=conn)\n",
    "\n",
    "df_20190611_pspike"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "These results are pretty similar to Adelaide / Jarvis - in the case where the CSV hourly volume is smaller than the API one, we see missing rows of data and some rows that don't agree. In the case where the API hourly volume is smaller, we mainly see missing rows in the API data.\n",
    "\n",
    "## Conclusions\n",
    "\n",
    "- We're missing a ton of data in `miovision_api.volumes` because we ran `intersection_tmc.py` for entire year blocks, and that script [only includes intersections that were activated before the start of the block and are still active](https://github.com/CityofToronto/bdit_data-sources/blob/master/volumes/miovision/api/intersection_tmc.py#L312-L329).\n",
    "- There are still differences between the CSV and API raw data. In some cases data is missing (from either CSV or API) and in other cases the datetime_bin, leg and movement_uid are the same but the volumes are different. No idea why that's happening."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.6.9 64-bit ('bditto': venv)",
   "language": "python",
   "name": "python36964bitbdittovenvd56842b7ca0c4929925d70f8c4c7f13a"
  },
  "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.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}