2018-08 ndt things.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Using matplotlib backend: MacOSX\n"
     ]
    }
   ],
   "source": [
    "# Enables figures loading outside of browser.\n",
    "# If not run, figures will load inline.\n",
    "%matplotlib\n",
    "\n",
    "import os\n",
    "import math\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.dates as dates\n",
    "import matplotlib.pyplot as plt\n",
    "import matplotlib.ticker\n",
    "import datetime\n",
    "import collections\n",
    "import itertools\n",
    "\n",
    "from scipy import stats\n",
    "\n",
    "# Depends on: pip install sklearn\n",
    "from sklearn.model_selection import train_test_split\n",
    "\n",
    "# Some matplotlib features are version dependent.\n",
    "assert(matplotlib.__version__ >= '2.1.2')\n",
    "\n",
    "# Depends on: pip install --upgrade google-cloud-bigquery\n",
    "from google.cloud import bigquery\n",
    "\n",
    "def run_query(query, project='mlab-sandbox'):\n",
    "    client = bigquery.Client(project=project)\n",
    "    job = client.query(query)\n",
    "\n",
    "    results = collections.defaultdict(list)\n",
    "    for row in job.result(timeout=3000):\n",
    "        for key in row.keys():\n",
    "            results[key].append(row.get(key))\n",
    "\n",
    "    return pd.DataFrame(results)\n",
    "\n",
    "def unlog(x, pos):\n",
    "    \"\"\"Formats the x axis for histograms taken on the log of values.\"\"\"\n",
    "    v = math.pow(10, x)\n",
    "    frac, whole = math.modf(v)\n",
    "    if frac > 0:\n",
    "        return '%.1f' % v\n",
    "    else:\n",
    "        return '%d' % whole\n",
    "    \n",
    "    \n",
    "def hist(vals, bin_count, log=True, cdf=False):\n",
    "    \"\"\"Produces hist or cdf values for smooth plots.\"\"\"\n",
    "    if log:\n",
    "        r = [math.log10(x) for x in vals]\n",
    "    else:\n",
    "        r = vals\n",
    "        \n",
    "    m, bins = np.histogram(r, bin_count, normed=True)\n",
    "    m = m.astype(float)\n",
    "\n",
    "    tops = m\n",
    "    if cdf:\n",
    "        tops = np.cumsum(m)\n",
    "        total = sum(m)\n",
    "        tops = [float(t) / total for t in tops ]\n",
    "    \n",
    "    return tops, bins\n",
    "\n",
    "\n",
    "logFormatter = matplotlib.ticker.FuncFormatter(unlog)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "def plot_df(\n",
    "    df, xname='', yname='',\n",
    "    cname='', bins=None, cdf=False,\n",
    "    fig_by='', axes_by='', group_by='',\n",
    "    figsize=(6,8), axes=(1,1),\n",
    "    label='{group}',\n",
    "    xlabel='', ylabel='',\n",
    "    xlim=(), ylim=(),\n",
    "    fx=list, fy=list,\n",
    "    xlog=False, ylog=False,\n",
    "    suptitle='', title='', legend={}, figmap=None, log=None, fxn=None):\n",
    "    \"\"\"\n",
    "    Args:\n",
    "        df: pandas.DataFrame,\n",
    "        xname: str, name of column to use as x-axis.\n",
    "        yname: str, name of column to use as y-axis.\n",
    "        cname: str, name of column to use as data source.\n",
    "        cdf: bool,\n",
    "        bins: int or callable,\n",
    "        fig_by: str, name of column to split data into multiple figures.\n",
    "        axes_by: str, name of column to arrange into a single panel.\n",
    "        group_by: str, name of column to plot common split_by and group_by columns.\n",
    "        figsize: (int, int), dimensions of figure.\n",
    "        axes: (int, int), arrangement of axes within figure.\n",
    "        label: str,\n",
    "        xlabel: str,\n",
    "        ylabel: str,\n",
    "        xlim: (xmin, xmax),\n",
    "        ylim: (ymin, ymax),\n",
    "        fx: func,\n",
    "        fy: func,\n",
    "        xlog: bool,\n",
    "        ylog: bool,\n",
    "        suptitle: str,\n",
    "        title: str,\n",
    "        legend: **legend_args,\n",
    "        figmap: returned from a previous run of plot_df, used to overlay values\n",
    "            from multiple data frames. Must use the same fig_by, axes_by, and\n",
    "            group_by values.\n",
    "        log: bool,\n",
    "        f: callable,\n",
    "    Returns:\n",
    "      dict of str to (figures, axes) tuples\n",
    "    \"\"\"\n",
    "    def info(f):\n",
    "        if log:\n",
    "            print f\n",
    "\n",
    "    if figmap is None:\n",
    "        info('new figmap')\n",
    "        figmap = {}\n",
    "    scatter = None\n",
    "    if (xname and yname):\n",
    "        scatter = True\n",
    "    if cname:\n",
    "        scatter = False\n",
    "    if scatter is None:\n",
    "        raise Exception('Provide xname and yname or cname')\n",
    "    \n",
    "    for f in sorted(set(['default'] if not fig_by else df[fig_by])):\n",
    "        if f in figmap:\n",
    "            info('reusing figmap for %s' % f)\n",
    "            fig, ax = figmap[f]\n",
    "        else:\n",
    "            fig = plt.figure(figsize=figsize)\n",
    "            ax = fig.subplots(axes[0], axes[1], squeeze=False)\n",
    "            info('saving figmap for %s' % f)\n",
    "            figmap[f] = (fig, ax)\n",
    "        ax_index = list(itertools.product(range(axes[0]), range(axes[1])))\n",
    "        \n",
    "        df_fig = df if f == 'default' else df[df[fig_by] == f]\n",
    "        for p, a in enumerate(sorted(set(['default'] if not axes_by else df_fig[axes_by]))):\n",
    "\n",
    "            df_axes = df_fig if a == 'default' else df_fig[df_fig[axes_by] == a]\n",
    "            if p >= len(ax_index):\n",
    "                print 'SKIPPING', p, f, a, 'too few axes positions'\n",
    "                continue\n",
    "                \n",
    "            i, j = ax_index[p]\n",
    "            for g in sorted(set(['default'] if not group_by else df_axes[group_by])):\n",
    "                df_g = df_axes if g == 'default' else df_axes[df_axes[group_by] == g]\n",
    "\n",
    "                if scatter:\n",
    "                    x = fx(df_g[xname])\n",
    "                    y = fy(df_g[yname])\n",
    "                    l = label.format(figure=f, axis=a, group=g)\n",
    "                    ax[i][j].scatter(x, y, s=1, label=l)\n",
    "                else:\n",
    "                    r = df_g[cname]\n",
    "                    if bins is None:\n",
    "                        size = int(math.sqrt(len(r)))\n",
    "                    else:\n",
    "                        size = bins(r)\n",
    "                    if fxn:\n",
    "                        fxn(r, figure=f, axis=a, group=g, size=size)\n",
    "                    info(\"%s %s %s %s %s\" % (f, a, g, size, len(r)))\n",
    "                    h_tops, h_bins = hist(r, size, log=xlog , cdf=cdf)\n",
    "                    l = label.format(figure=f, axis=a, group=g, size=size)\n",
    "                    ax[i][j].plot(h_bins[:-1], h_tops, label=l)\n",
    "\n",
    "            if i != len(ax)-1:\n",
    "                ax[i][j].set_xticklabels([])\n",
    "\n",
    "            if title:\n",
    "                ax[i][j].set_title(title.format(figure=f, axis=a, group=g))\n",
    "            if ylabel:\n",
    "                ax[i][j].set_ylabel(ylabel.format(figure=f, axis=a, group=g))\n",
    "            if xlabel:\n",
    "                ax[i][j].set_xlabel(xlabel.format(figure=f, axis=a, group=g))\n",
    "\n",
    "            if xlim:\n",
    "                ax[i][j].set_xlim(xlim)\n",
    "            if ylim:\n",
    "                ax[i][j].set_ylim(ylim)\n",
    "\n",
    "            ax[i][j].grid(color='#dddddd')\n",
    "            ax[i][j].legend(fontsize='x-small', **legend)\n",
    "            if scatter:\n",
    "                ax[i][j].tick_params(axis='x', labelrotation=-90)\n",
    "            if xlog:\n",
    "                ax[i][j].xaxis.set_major_formatter(logFormatter)\n",
    "            if ylog:\n",
    "                ax[i][j].semilogy()\n",
    "\n",
    "        if suptitle:\n",
    "            fig.suptitle(suptitle.format(figure=f))\n",
    "        fig.tight_layout(rect=[0, 0.03, 1, 0.95])\n",
    "\n",
    "    return figmap\n",
    "\n",
    "        \n",
    "def plot_scatter(df, xname, yname, **kwargs):\n",
    "    return plot_df(df, xname=xname, yname=yname, **kwargs)\n",
    "\n",
    "    \n",
    "def plot_hist(df, cname, bins=None, **kwargs):\n",
    "    return plot_df(df, cname=cname, bins=bins, **kwargs)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# UPLINK UTILIZATION OVER TIME"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING:google.auth._default:No project ID could be determined. Consider running `gcloud config set project` or setting the GOOGLE_CLOUD_PROJECT environment variable\n"
     ]
    }
   ],
   "source": [
    "df_disco_pct = run_query(\"\"\"\n",
    "#standardSQL\n",
    "SELECT\n",
    "\n",
    "  UPPER(REGEXP_EXTRACT(hostname, r'mlab1.([a-z]{3})[0-9]{2}.*')) AS metro,\n",
    "  REGEXP_EXTRACT(hostname, r'mlab1.([a-z]{3}[0-9]{2}).*') AS site,\n",
    "  FORMAT_TIMESTAMP(\"%Y-%m-%d\", TIMESTAMP_TRUNC(sts, DAY)) AS day,\n",
    "  UNIX_SECONDS(TIMESTAMP_TRUNC(sts, DAY)) AS ts,  \n",
    "  0.8 * APPROX_QUANTILES(value, 101)[ORDINAL(50)] as bytes_50th,\n",
    "  APPROX_QUANTILES(value, 101)[ORDINAL(90)] as bytes_90th\n",
    "\n",
    "FROM (\n",
    "  SELECT\n",
    "    metric,\n",
    "    REGEXP_EXTRACT(hostname, r'(mlab1.[a-z]{3}[0-9]{2}).*') AS hostname,\n",
    "    sample.timestamp AS sts,\n",
    "    sample.value AS value\n",
    "  FROM\n",
    "    `measurement-lab.base_tables.switch*`,\n",
    "    UNNEST(sample) AS sample\n",
    "  WHERE\n",
    "    metric LIKE 'switch.octets.uplink.tx'\n",
    "    AND REGEXP_CONTAINS(hostname, r\"mlab1.(dfw|lga|nuq)\\d\\d\")\n",
    "  GROUP BY\n",
    "    hostname, metric, sts, value\n",
    ")\n",
    "WHERE\n",
    "  hostname IS NOT NULL\n",
    "GROUP BY\n",
    "  hostname, day, ts\n",
    "ORDER BY\n",
    "  hostname, day, ts\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'default': (<Figure size 600x800 with 3 Axes>,\n",
       "  array([[<matplotlib.axes._subplots.AxesSubplot object at 0x7f8300c912d0>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x7f8301b1e050>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x7f8301b36310>]],\n",
       "        dtype=object))}"
      ]
     },
     "execution_count": 137,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "plot_scatter(\n",
    "    df_disco_pct, 'ts', 'bytes_50th',\n",
    "    axes_by='metro', group_by='site', axes=(3, 1),\n",
    "    suptitle='Daily Median Uplink Utilization',\n",
    "    ylabel=\"Mbps\",\n",
    "    title='{axis}',\n",
    "    xlim=(pd.to_datetime(\"2016-05-31\"), pd.to_datetime(\"2018-08-01\")),\n",
    "    ylim=(1e4, 1e9),\n",
    "    fx=lambda l: [pd.to_datetime(t, unit='s') for t in l],\n",
    "    legend={'loc':3, 'ncol':7, 'columnspacing':1},\n",
    "    ylog=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Daily DISCO discard ratios"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING:google.auth._default:No project ID could be determined. Consider running `gcloud config set project` or setting the GOOGLE_CLOUD_PROJECT environment variable\n"
     ]
    }
   ],
   "source": [
    "df_disco_ratio = run_query(\"\"\"\n",
    "WITH measurementlab_switch_dedup AS (\n",
    "  SELECT\n",
    "    metric,\n",
    "    REGEXP_EXTRACT(hostname, r'(mlab[1-4].[a-z]{3}[0-9]{2}).*') AS hostname,\n",
    "    sample.timestamp AS sts,\n",
    "    sample.value AS value\n",
    "  FROM\n",
    "    `measurement-lab.base_tables.switch*`,\n",
    "    UNNEST(sample) AS sample\n",
    "  WHERE\n",
    "    (metric LIKE 'switch.discards.uplink.tx' OR metric LIKE 'switch.unicast.uplink.tx')\n",
    "    AND REGEXP_CONTAINS(hostname, r\"mlab1.(dfw|lga|nuq)\\d\\d\")\n",
    "  GROUP BY\n",
    "    hostname, metric, sts, value\n",
    ")\n",
    "\n",
    "SELECT\n",
    "  UPPER(REGEXP_EXTRACT(hostname, r'mlab1.([a-z]{3})[0-9]{2}.*')) AS metro,\n",
    "  REGEXP_EXTRACT(hostname, r'mlab1.([a-z]{3}[0-9]{2}).*') AS site,\n",
    "  hostname,\n",
    "  day,\n",
    "  ts,\n",
    "  IF(total > 0, discards / total, 0) as ratio\n",
    "FROM (\n",
    "SELECT\n",
    "  hostname,\n",
    "  FORMAT_TIMESTAMP(\"%Y-%m-%d\", TIMESTAMP_TRUNC(sts, DAY)) AS day,\n",
    "  UNIX_SECONDS(TIMESTAMP_TRUNC(sts, DAY)) AS ts,\n",
    "  SUM(IF(metric = \"switch.discards.uplink.tx\", value, 0)) AS discards,\n",
    "  SUM(IF(metric = \"switch.unicast.uplink.tx\", value, 0)) AS total\n",
    "FROM\n",
    "  measurementlab_switch_dedup\n",
    "WHERE\n",
    "  hostname IS NOT NULL\n",
    "GROUP BY\n",
    "  hostname, day, ts\n",
    "HAVING\n",
    "  discards < total\n",
    "ORDER BY\n",
    "  hostname, day, ts\n",
    ")\n",
    "GROUP BY\n",
    "  hostname, day, ts, ratio\n",
    "HAVING\n",
    "  ratio < 0.01\n",
    "ORDER BY\n",
    "  hostname, day, ts\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'default': (<Figure size 600x800 with 3 Axes>,\n",
       "  array([[<matplotlib.axes._subplots.AxesSubplot object at 0x7f82e283ee90>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x7f8307a3cd10>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x7f830211d990>]],\n",
       "        dtype=object))}"
      ]
     },
     "execution_count": 141,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "plot_scatter(\n",
    "    df_disco_ratio, 'ts', 'ratio', axes_by='metro', group_by='site', axes=(3, 1),\n",
    "    suptitle='Daily Packet Loss Ratio (discards / unicast)',\n",
    "    ylabel=\"Ratio\",\n",
    "    title='{axis}',\n",
    "    xlim=(pd.to_datetime(\"2016-05-31\"), pd.to_datetime(\"2018-08-01\")),\n",
    "    ylim=(1e-6, 1e-1),\n",
    "    fx=lambda l: [pd.to_datetime(t, unit='s') for t in l],\n",
    "    legend={'loc':2},\n",
    "    ylog=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# NDT Median Download Rates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_ndt_all = run_query(\"\"\"\n",
    "WITH mlab_ndt AS (\n",
    "  SELECT\n",
    "    UPPER(REGEXP_EXTRACT(connection_spec.server_hostname, r\"([a-z]{3})[0-9]{2}\")) as metro,\n",
    "    REGEXP_EXTRACT(connection_spec.server_hostname, r\"([a-z]{3}[0-9]{2})\") as site,\n",
    "    web100_log_entry.connection_spec.remote_ip as remote_ip,\n",
    "    log_time,\n",
    "    (8 * (web100_log_entry.snap.HCThruOctetsAcked / (web100_log_entry.snap.SndLimTimeRwin + web100_log_entry.snap.SndLimTimeCwnd + web100_log_entry.snap.SndLimTimeSnd))) AS download_mbps\n",
    "\n",
    "  FROM\n",
    "    `measurement-lab.release.ndt_all`\n",
    "\n",
    "  WHERE\n",
    "        REGEXP_CONTAINS(connection_spec.server_hostname, r\"(lga|dfw|nuq|mia|atl|iad|ord|den|lax|sea|dub|lhr|ams|lba|par|ham|prg|lju|mil|vie)\\d\\d\")\n",
    "    AND web100_log_entry.snap.HCThruOctetsAcked >= 1000000\n",
    "    AND (web100_log_entry.snap.SndLimTimeRwin + web100_log_entry.snap.SndLimTimeCwnd + web100_log_entry.snap.SndLimTimeSnd) >= 9000000\n",
    "    AND (web100_log_entry.snap.SndLimTimeRwin + web100_log_entry.snap.SndLimTimeCwnd + web100_log_entry.snap.SndLimTimeSnd) < 600000000\n",
    "    AND connection_spec.data_direction = 1\n",
    "    AND web100_log_entry.connection_spec.remote_ip != \"45.56.98.222\"\n",
    "    AND web100_log_entry.connection_spec.remote_ip != \"2600:3c03::f03c:91ff:fe33:819\"\n",
    "    AND web100_log_entry.connection_spec.remote_ip != \"35.225.75.192\"\n",
    "    AND web100_log_entry.connection_spec.remote_ip != \"35.192.37.249\"\n",
    "    AND web100_log_entry.connection_spec.remote_ip != \"35.193.254.117\"\n",
    "    --AND anomalies.no_meta is not true\n",
    "    AND log_time >= TIMESTAMP(\"2013-01-01\")\n",
    "  \n",
    "  GROUP BY\n",
    "    connection_spec.server_hostname,\n",
    "    log_time,\n",
    "    web100_log_entry.connection_spec.remote_ip,\n",
    "    web100_log_entry.connection_spec.local_ip,\n",
    "    web100_log_entry.connection_spec.remote_port,\n",
    "    web100_log_entry.connection_spec.local_port,\n",
    "    download_mbps\n",
    ")\n",
    "    \n",
    "SELECT\n",
    "  metro,\n",
    "  site,\n",
    "  day,\n",
    " --  AVG(download_mbps) as download_mbps,\n",
    "  APPROX_QUANTILES(download_mbps, 101)[ORDINAL(50)] as download_mbps,\n",
    "  count(*) as count\n",
    "FROM\n",
    "(\n",
    "  SELECT\n",
    "    metro,\n",
    "    site,\n",
    "    TIMESTAMP_TRUNC(log_time, DAY) as day,\n",
    "    -- APPROX_QUANTILES(download_mbps, 101)[ORDINAL(50)] as download_mbps\n",
    "    MAX(download_mbps) as download_mbps\n",
    "  FROM\n",
    "    mlab_ndt\n",
    "\n",
    "  GROUP BY\n",
    "    metro, site, day, remote_ip\n",
    ")\n",
    "\n",
    "GROUP BY\n",
    "  metro, site, day\n",
    "\n",
    "ORDER BY\n",
    "  day\n",
    "\"\"\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'default': (<matplotlib.figure.Figure at 0x1a75b39390>,\n",
       "  array([[<matplotlib.axes._subplots.AxesSubplot object at 0x1a75b39c50>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a762d7290>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a76319a50>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a7635ae90>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x1a763ac310>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a763e1850>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a7642f210>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a7644ded0>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x1a764bd550>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a7647cd50>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a765480d0>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a765889d0>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x1a765d4290>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a76616c90>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a76651d50>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a7669e790>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x1a766ca650>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a7672fad0>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a766b5290>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a767b6550>]],\n",
       "        dtype=object))}"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "plot_scatter(\n",
    "    df_ndt_all, 'day', 'download_mbps',\n",
    "    axes_by='metro', group_by='site',\n",
    "    suptitle='Median NDT Download Rates (w/no-meta)',\n",
    "    ylabel=\"Mbps\",\n",
    "    title='{axis}',\n",
    "    axes=(5, 4),\n",
    "    figsize=(25,12),\n",
    "    xlim=(pd.to_datetime(\"2013-01-01\"), pd.to_datetime(\"2018-08-01\")),\n",
    "    ylim=(0, 50),\n",
    "    fx=lambda l: [pd.to_datetime(t) for t in l],\n",
    "    legend={'loc':2, 'ncol':7, 'columnspacing':1})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "# NDT Client Counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_ndt_clients = run_query(\"\"\"\n",
    "WITH mlab_ndt AS (\n",
    "  SELECT\n",
    "    UPPER(REGEXP_EXTRACT(connection_spec.server_hostname, r\"([a-z]{3})[0-9]{2}\")) as metro,\n",
    "    REGEXP_EXTRACT(connection_spec.server_hostname, r\"([a-z]{3}[0-9]{2})\") as site,\n",
    "    web100_log_entry.connection_spec.remote_ip as remote_ip,\n",
    "    log_time\n",
    "\n",
    "  FROM\n",
    "    `measurement-lab.release.ndt_all`\n",
    "\n",
    "  WHERE\n",
    "        REGEXP_CONTAINS(connection_spec.server_hostname, r\"(lga|dfw|nuq|mia|atl|iad|ord|den|lax|sea|dub|lhr|ams|lba|par|ham|prg|lju|mil|vie)\\d\\d\")\n",
    "    AND web100_log_entry.snap.HCThruOctetsAcked >= 1000000\n",
    "    AND (web100_log_entry.snap.SndLimTimeRwin + web100_log_entry.snap.SndLimTimeCwnd + web100_log_entry.snap.SndLimTimeSnd) >= 9000000\n",
    "    AND (web100_log_entry.snap.SndLimTimeRwin + web100_log_entry.snap.SndLimTimeCwnd + web100_log_entry.snap.SndLimTimeSnd) < 600000000\n",
    "    AND connection_spec.data_direction = 1\n",
    "    AND web100_log_entry.connection_spec.remote_ip != \"45.56.98.222\"\n",
    "    AND web100_log_entry.connection_spec.remote_ip != \"2600:3c03::f03c:91ff:fe33:819\"\n",
    "    AND web100_log_entry.connection_spec.remote_ip != \"35.225.75.192\"\n",
    "    AND web100_log_entry.connection_spec.remote_ip != \"35.192.37.249\"\n",
    "    AND web100_log_entry.connection_spec.remote_ip != \"35.193.254.117\"\n",
    "    AND log_time >= TIMESTAMP(\"2013-01-01\")\n",
    "  \n",
    "  GROUP BY\n",
    "    connection_spec.server_hostname,\n",
    "    log_time,\n",
    "    web100_log_entry.connection_spec.remote_ip,\n",
    "    web100_log_entry.connection_spec.local_ip,\n",
    "    web100_log_entry.connection_spec.remote_port,\n",
    "    web100_log_entry.connection_spec.local_port\n",
    ")\n",
    "    \n",
    "SELECT\n",
    "    metro,\n",
    "    site,\n",
    "    TIMESTAMP_TRUNC(log_time, DAY) as day,\n",
    "    COUNT(remote_ip) as total_count,\n",
    "    COUNT(DISTINCT remote_ip) as unique_count\n",
    "\n",
    "FROM\n",
    "  mlab_ndt\n",
    "\n",
    "GROUP BY\n",
    "  metro, site, day\n",
    "\n",
    "--ORDER BY\n",
    "--  day\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SKIPPING 6 AMS ams07 too few axes positions\n",
      "SKIPPING 6 DFW dfw07 too few axes positions\n",
      "SKIPPING 6 LGA lga07 too few axes positions\n",
      "SKIPPING 6 NUQ nuq07 too few axes positions\n",
      "SKIPPING 6 SEA sea07 too few axes positions\n",
      "SKIPPING 6 AMS ams07 too few axes positions\n",
      "SKIPPING 6 DFW dfw07 too few axes positions\n",
      "SKIPPING 6 LGA lga07 too few axes positions\n",
      "SKIPPING 6 NUQ nuq07 too few axes positions\n",
      "SKIPPING 6 SEA sea07 too few axes positions\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "{u'AMS': (<matplotlib.figure.Figure at 0x1a46dda790>,\n",
       "  array([[<matplotlib.axes._subplots.AxesSubplot object at 0x1a46dd7090>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a465df3d0>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x1a46621810>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a46667c50>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x1a466b9050>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a466f7050>]],\n",
       "        dtype=object)),\n",
       " u'ATL': (<matplotlib.figure.Figure at 0x1a467428d0>,\n",
       "  array([[<matplotlib.axes._subplots.AxesSubplot object at 0x1a4682b690>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a468716d0>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x1a46892190>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a468fcf50>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x1a468ce450>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a469825d0>]],\n",
       "        dtype=object)),\n",
       " u'DEN': (<matplotlib.figure.Figure at 0x1a46a25490>,\n",
       "  array([[<matplotlib.axes._subplots.AxesSubplot object at 0x1a46a96450>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a46aeddd0>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x1a46b19c90>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a46b76dd0>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x1a46bc4810>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a46bec5d0>]],\n",
       "        dtype=object)),\n",
       " u'DFW': (<matplotlib.figure.Figure at 0x1a46d0e750>,\n",
       "  array([[<matplotlib.axes._subplots.AxesSubplot object at 0x1a46d24f10>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a46d68cd0>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x1a46da0e90>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a46e31dd0>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x1a46e7ed90>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a46ecb750>]],\n",
       "        dtype=object)),\n",
       " u'DUB': (<matplotlib.figure.Figure at 0x1a46fea590>,\n",
       "  array([[<matplotlib.axes._subplots.AxesSubplot object at 0x1a47001a10>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a470454d0>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x1a47085ed0>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a46fe2d50>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x1a47103f10>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a471539d0>]],\n",
       "        dtype=object)),\n",
       " u'HAM': (<matplotlib.figure.Figure at 0x1a471b0150>,\n",
       "  array([[<matplotlib.axes._subplots.AxesSubplot object at 0x1a471cb190>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a482e1fd0>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x1a48327450>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a48367dd0>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x1a483b4d10>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a484046d0>]],\n",
       "        dtype=object)),\n",
       " u'IAD': (<matplotlib.figure.Figure at 0x1a46ca7f50>,\n",
       "  array([[<matplotlib.axes._subplots.AxesSubplot object at 0x1a471eb810>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a4853aed0>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x1a48588890>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a485d5250>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x1a48623210>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a48652d90>]],\n",
       "        dtype=object)),\n",
       " u'LAX': (<matplotlib.figure.Figure at 0x1a486d0a10>,\n",
       "  array([[<matplotlib.axes._subplots.AxesSubplot object at 0x1a4873ded0>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a487b5390>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x1a487f12d0>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a48832d50>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x1a4887f690>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a488bdf10>]],\n",
       "        dtype=object)),\n",
       " u'LBA': (<matplotlib.figure.Figure at 0x1a489c6d90>,\n",
       "  array([[<matplotlib.axes._subplots.AxesSubplot object at 0x1a48a12710>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a48a4ab10>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x1a48ac2550>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a48b04ed0>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x1a48b41990>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a48b8e3d0>]],\n",
       "        dtype=object)),\n",
       " u'LGA': (<matplotlib.figure.Figure at 0x1a48bad250>,\n",
       "  array([[<matplotlib.axes._subplots.AxesSubplot object at 0x1a48c64c10>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a48cb1a90>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x1a48d27450>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a48d64290>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x1a48daf1d0>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a48df2b50>]],\n",
       "        dtype=object)),\n",
       " u'LHR': (<matplotlib.figure.Figure at 0x1a48668390>,\n",
       "  array([[<matplotlib.axes._subplots.AxesSubplot object at 0x1a41557990>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a3ce3e0d0>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x1a39919a10>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a31b6dd50>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x1a2417da50>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a3219d9d0>]],\n",
       "        dtype=object)),\n",
       " u'LJU': (<matplotlib.figure.Figure at 0x1a2291c790>,\n",
       "  array([[<matplotlib.axes._subplots.AxesSubplot object at 0x1a48e6b9d0>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a48f3ea90>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x1a48f68790>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a48fcfd50>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x1a48f4c790>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a4904de10>]],\n",
       "        dtype=object)),\n",
       " u'MIA': (<matplotlib.figure.Figure at 0x1a490fe090>,\n",
       "  array([[<matplotlib.axes._subplots.AxesSubplot object at 0x1a491c2a10>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a491e3450>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x1a51f9ed50>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a51fd6d10>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x1a52025750>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a52076110>]],\n",
       "        dtype=object)),\n",
       " u'MIL': (<matplotlib.figure.Figure at 0x1a491579d0>,\n",
       "  array([[<matplotlib.axes._subplots.AxesSubplot object at 0x1a5216c9d0>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a521db650>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x1a52227390>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a52246cd0>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x1a522b85d0>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a522f3510>]],\n",
       "        dtype=object)),\n",
       " u'NUQ': (<matplotlib.figure.Figure at 0x1a523b7a50>,\n",
       "  array([[<matplotlib.axes._subplots.AxesSubplot object at 0x1a523c0e10>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a52456750>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x1a523d40d0>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a524d2910>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x1a52521850>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a5256f210>]],\n",
       "        dtype=object)),\n",
       " u'ORD': (<matplotlib.figure.Figure at 0x1a525d4050>,\n",
       "  array([[<matplotlib.axes._subplots.AxesSubplot object at 0x1a526c4f50>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a52ea4890>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x1a52ece750>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a526b1c10>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x1a52f71390>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a52fb1e10>]],\n",
       "        dtype=object)),\n",
       " u'PAR': (<matplotlib.figure.Figure at 0x1a530ca090>,\n",
       "  array([[<matplotlib.axes._subplots.AxesSubplot object at 0x1a530d5cd0>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a53143c90>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x1a53191650>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a531d1ed0>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x1a531f3110>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a5325c750>]],\n",
       "        dtype=object)),\n",
       " u'PRG': (<matplotlib.figure.Figure at 0x1a53312f10>,\n",
       "  array([[<matplotlib.axes._subplots.AxesSubplot object at 0x1a533480d0>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a533d4910>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x1a533fec90>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a53451650>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x1a5349f590>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a534e2f10>]],\n",
       "        dtype=object)),\n",
       " u'SEA': (<matplotlib.figure.Figure at 0x1a5359c750>,\n",
       "  array([[<matplotlib.axes._subplots.AxesSubplot object at 0x1a535dd810>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a53600890>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x1a54c91e50>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a54d29810>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x1a54cba0d0>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a54db1410>]],\n",
       "        dtype=object)),\n",
       " u'VIE': (<matplotlib.figure.Figure at 0x1a54e37e50>,\n",
       "  array([[<matplotlib.axes._subplots.AxesSubplot object at 0x1a52608050>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a54f41210>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x1a54f60ad0>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a55768dd0>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x1a557b7810>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a557e15d0>]],\n",
       "        dtype=object))}"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "f = plot_scatter(\n",
    "    df_ndt_clients, 'day', 'total_count',\n",
    "    fig_by='metro', axes_by='site',\n",
    "    suptitle='NDT Download Unique Client Counts',\n",
    "    ylabel=\"Count\",\n",
    "    title='{axis}',\n",
    "    label='total-{group}',\n",
    "    axes=(3, 2),\n",
    "    figsize=(12, 8),\n",
    "    xlim=(pd.to_datetime(\"2013-01-01\"), pd.to_datetime(\"2018-08-01\")),\n",
    "    fx=lambda l: [pd.to_datetime(t) for t in l],\n",
    "    legend={'loc':2, 'ncol':7, 'columnspacing':1})\n",
    "\n",
    "plot_scatter(\n",
    "    df_ndt_clients, 'day', 'unique_count',\n",
    "    fig_by='metro', axes_by='site',\n",
    "    label='unique-{group}',\n",
    "    axes=(3, 2),\n",
    "    xlim=(pd.to_datetime(\"2013-01-01\"), pd.to_datetime(\"2018-08-01\")),\n",
    "    #ylim=(0, 50),\n",
    "    fx=lambda l: [pd.to_datetime(t) for t in l],\n",
    "    legend={'loc':2, 'ncol':7, 'columnspacing':1},\n",
    "    figmap=f)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# NDT Segs Retrans"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# NOT ENOUGH HISTORICAL NDT DATA TO GET FULL TIMELINE."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_ndt_retrans = run_query(\"\"\"\n",
    "WITH mlab_ndt AS (\n",
    "  SELECT\n",
    "    connection_spec.server_hostname as hostname,\n",
    "    web100_log_entry.connection_spec.remote_ip as remote_ip,\n",
    "    log_time,\n",
    "    web100_log_entry.snap.SegsRetrans as SegsRetrans,\n",
    "    web100_log_entry.snap.SegsOut as SegsOut\n",
    "\n",
    "  FROM\n",
    "    `measurement-lab.release.ndt_all`\n",
    "\n",
    "  WHERE\n",
    "        REGEXP_CONTAINS(connection_spec.server_hostname, r\"(lga|dfw|nuq|mia|atl|iad|ord|den|lax|sea|dub|lhr|ams|lba|par|ham|prg|lju|mil|vie)\\d\\d\")\n",
    "    AND web100_log_entry.snap.HCThruOctetsAcked >= 1000000\n",
    "    AND (web100_log_entry.snap.SndLimTimeRwin + web100_log_entry.snap.SndLimTimeCwnd + web100_log_entry.snap.SndLimTimeSnd) >= 9000000\n",
    "    AND (web100_log_entry.snap.SndLimTimeRwin + web100_log_entry.snap.SndLimTimeCwnd + web100_log_entry.snap.SndLimTimeSnd) < 600000000\n",
    "    AND connection_spec.data_direction = 1\n",
    "    AND web100_log_entry.connection_spec.remote_ip != \"45.56.98.222\"\n",
    "    AND web100_log_entry.connection_spec.remote_ip != \"2600:3c03::f03c:91ff:fe33:819\"\n",
    "    AND web100_log_entry.connection_spec.remote_ip != \"35.225.75.192\"\n",
    "    AND web100_log_entry.connection_spec.remote_ip != \"35.192.37.249\"\n",
    "    AND web100_log_entry.connection_spec.remote_ip != \"35.193.254.117\"\n",
    "    AND log_time >= TIMESTAMP(\"2013-01-01\")\n",
    "  \n",
    "  GROUP BY\n",
    "    connection_spec.server_hostname,\n",
    "    log_time,\n",
    "    web100_log_entry.connection_spec.remote_ip,\n",
    "    web100_log_entry.connection_spec.local_ip,\n",
    "    web100_log_entry.connection_spec.remote_port,\n",
    "    web100_log_entry.connection_spec.local_port,\n",
    "    SegsRetrans,\n",
    "    SegsOut\n",
    ")\n",
    "    \n",
    "SELECT\n",
    "  UPPER(REGEXP_EXTRACT(hostname, r\"([a-z]{3})[0-9]{2}\")) as metro,\n",
    "  REGEXP_EXTRACT(hostname, r\"([a-z]{3}[0-9]{2})\") as site,\n",
    "  day,\n",
    "  APPROX_QUANTILES(ratio, 101)[ORDINAL(50)] AS median_ratio,\n",
    "  count(*) as count\n",
    "FROM\n",
    "(\n",
    "  SELECT\n",
    "    hostname,\n",
    "    TIMESTAMP_TRUNC(log_time, DAY) as day,\n",
    "    MAX(SAFE_DIVIDE(SegsRetrans, SegsOut)) as ratio\n",
    "\n",
    "  FROM\n",
    "    mlab_ndt\n",
    "\n",
    "  GROUP BY\n",
    "    hostname,\n",
    "    day,\n",
    "    remote_ip\n",
    ")\n",
    "\n",
    "GROUP BY\n",
    "  hostname, day\n",
    "\n",
    "ORDER BY\n",
    "  day\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'default': (<matplotlib.figure.Figure at 0x1a5699e790>,\n",
       "  array([[<matplotlib.axes._subplots.AxesSubplot object at 0x1a5699ed90>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a569ef250>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a56a31890>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a56a79bd0>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x1a56ac7650>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a56b29d90>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a6271b210>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a6275f650>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x1a6277f950>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a56b4a710>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a62829550>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a6286db10>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x1a628b3f50>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a629063d0>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a62946050>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a6298a490>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x1a629cf8d0>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a629f0f90>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a62998f50>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a62a99850>]],\n",
       "        dtype=object))}"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "plot_scatter(\n",
    "    df_ndt_retrans, 'day', 'median_ratio', axes_by='metro', group_by='site',\n",
    "    suptitle='Median NDT Retransmission Ratio - (SegsRetran / SegsOut)',\n",
    "    ylabel=\"Ratio\",\n",
    "    title='{axis}',\n",
    "    axes=(5, 4),\n",
    "    figsize=(25,12),\n",
    "    xlim=(pd.to_datetime(\"2013-01-01\"), pd.to_datetime(\"2018-08-01\")),\n",
    "    ylim=(1e-6, 1e-1),\n",
    "    fx=lambda l: [pd.to_datetime(t) for t in l],\n",
    "    legend={'loc':3, 'ncol':7, 'columnspacing':1},\n",
    "    ylog=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## COMBINED SegsRetrans & Switch Discards\n",
    "\n",
    "sites = [\n",
    "    ['dfw'],\n",
    "    ['lga'],\n",
    "    ['nuq'],\n",
    "]\n",
    "\n",
    "axes = [\n",
    "    [None],\n",
    "    [None],\n",
    "    [None],\n",
    "    [None],\n",
    "    [None],\n",
    "    [None],\n",
    "]\n",
    "def box(x, y, text):\n",
    "    plt.text(x, y, text,\n",
    "        bbox=dict(boxstyle=\"round\",\n",
    "              ec=(.5, 0.5, 1., 0.25),\n",
    "              fc=(.5, 0.8, 1., 0.25),\n",
    "        )\n",
    "    )\n",
    "print len(df_ndt_retrans)\n",
    "\n",
    "prop_cycle = plt.rcParams['axes.prop_cycle']\n",
    "colors = prop_cycle.by_key()['color']\n",
    "\n",
    "fig = plt.figure(figsize=(6, 8))\n",
    "\n",
    "for i, site_row in enumerate(sites):\n",
    "    for j, site in enumerate(site_row):\n",
    "        axes[i][j] = plt.subplot2grid((3, 1), (i, j))\n",
    "        axes[i][j].set_ylabel('Ratio ' + site.upper())\n",
    "        if i != len(sites)-1:\n",
    "            axes[i][j].set_xticklabels([])\n",
    "\n",
    "        c = 0\n",
    "        for s in sorted(set(df_ndt_retrans['site'])):\n",
    "            if site in s:\n",
    "                ds = df_ndt_retrans[ (df_ndt_retrans['site'] == s) ]\n",
    "                d = [pd.to_datetime(t) for t in ds['day']]\n",
    "                axes[i][j].scatter(d, ds['median_ratio'], s=1, label=s, c=colors[c])\n",
    "                c += 1\n",
    "\n",
    "        axes[i][j].set_ylim(1e-6, 1e-1)\n",
    "        axes[i][j].set_xlim(pd.to_datetime(\"2016-05-31\"), pd.to_datetime(\"2018-08-01\"))\n",
    "        axes[i][j].tick_params(axis='x', labelrotation=-90)\n",
    "        axes[i][j].grid(color='#dddddd')\n",
    "        axes[i][j].legend(loc=2, fontsize='x-small')\n",
    "        axes[i][j].semilogy()\n",
    "\n",
    "for i, site_row in enumerate(sites):\n",
    "    for j, site in enumerate(site_row):       \n",
    "\n",
    "        if i != len(sites)-1:\n",
    "            axes[i][j].set_xticklabels([])\n",
    "        c = 0\n",
    "        for h in set(df_disco_ratio['hostname']):\n",
    "            if ('mlab1.' + site) in h:\n",
    "                ds = df_disco_ratio[ (df_disco_ratio['hostname'] == h) ]\n",
    "                d = [pd.to_datetime(t, unit='s') for t in ds['ts']]\n",
    "                axes[i][j].scatter(d, ds['ratio'], s=1, label=h[6:11], c=colors[c])\n",
    "                c += 1\n",
    "\n",
    "box(pd.to_datetime(\"2016-10-30\"), 5e-3, u\"Segs Retransmit ↘\")\n",
    "box(pd.to_datetime(\"2016-10-30\"), 9e-6, u\"Switch Discards ↗\")\n",
    "                \n",
    "fig.suptitle('Retrans & Switch Discard Rates')\n",
    "fig.tight_layout(rect=[0, 0.03, 1, 0.95])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "# NDT test distributions - Before & After"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING:google.auth._default:No project ID could be determined. Consider running `gcloud config set project` or setting the GOOGLE_CLOUD_PROJECT environment variable\n"
     ]
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "print df_ndt_dist.keys()\n",
    "print len(df_ndt_dist)\n",
    "\n",
    "def hist(vals, bin_count, log=True, cdf=False):\n",
    "    \"\"\"Produces hist or cdf values for smooth plots.\"\"\"\n",
    "    if log:\n",
    "        r = [math.log10(x) for x in vals]\n",
    "    else:\n",
    "        r = vals\n",
    "        \n",
    "    m, bins = np.histogram(r, bin_count, normed=True)\n",
    "    m = m.astype(float)\n",
    "\n",
    "    tops = m\n",
    "    if cdf:\n",
    "        tops = np.cumsum(m)\n",
    "        total = sum(m)\n",
    "        tops = [float(t) / total for t in tops ]\n",
    "    \n",
    "    return tops, bins\n",
    "\n",
    "seq = [(0, 0), (0, 1), (1, 0), (1, 1), (2, 0), (2, 1)]\n",
    "\n",
    "for site in set([v[6:9] for v in set(df_ndt_dist['name'])]):\n",
    "    fig, axes = plt.subplots(nrows=3, ncols=2, figsize=(12, 6))\n",
    "    for p, h in enumerate(sorted([h for h in set(df_ndt_dist['name']) if site in h])):\n",
    "        before = None\n",
    "        r_before = None\n",
    "\n",
    "        for day in ['before-2w', 'after-2w']:\n",
    "            ds = df_ndt_dist[ (df_ndt_dist['name'] == h) & (df_ndt_dist['period'] == day) ]\n",
    "            r = ds['download_mbps']\n",
    "            #print h, len(r)\n",
    "            if not len(r):\n",
    "                continue\n",
    "\n",
    "            size = int(math.sqrt(len(r)))\n",
    "            \n",
    "            if day == 'after-2w':\n",
    "                size = before                 # Test before vs after\n",
    "                result = stats.ks_2samp(r, r_before)\n",
    "                #if result.pvalue < 0.01:     # print 'diff', h, result  # Test itself.\n",
    "                a, b = train_test_split(r, test_size=0.5)\n",
    "                result = stats.ks_2samp(a, b)\n",
    "                #if result.pvalue < 0.01:\n",
    "                    #print 'same', h, result\n",
    "                    #print '================================='\n",
    "\n",
    "            else:\n",
    "                before = size\n",
    "                r_before = r\n",
    "            \n",
    "                \n",
    "            #tops, bins = hist(r, int(1.8 * math.sqrt(len(r))), log=True , cdf=True)\n",
    "            #tops, bins = hist(r, int(math.sqrt(len(r))), log=True , cdf=True)\n",
    "            #print size, h, day\n",
    "            #tops, bins = hist(r, size, log=True , cdf=True)\n",
    "            tops, bins = hist(r, size, log=True , cdf=True)\n",
    "            #tops, bins = hist(r, int(1.8 * math.sqrt(len(r))), log=False , cdf=True)           \n",
    "            #tops, bins = hist(r, len(r), log=False , cdf=True)            \n",
    "            \n",
    "\n",
    "            #tops_a, bins_a = hist(a, int(1 * math.sqrt(len(a))), log=True, cdf=True)\n",
    "            #tops_b, bins_b = hist(b, int(1 * math.sqrt(len(b))), log=True, cdf=True)\n",
    "            if p > len(seq)-1:\n",
    "                print 'skipping', h\n",
    "                continue\n",
    "            i, j = seq[p]\n",
    "            #print h, len(bins), len(tops)\n",
    "            axes[i, j].plot(bins[:-1], tops, label='cdf-'+h[6:11] + '-' + str(day))\n",
    "            #axes[i, j].plot(bins_a[:-1], tops_a, label=h[6:11] + '-' + str(day)+'-a')\n",
    "            #axes[i, j].plot(bins_b[:-1], tops_b, label=h[6:11] + '-' + str(day)+'-b')\n",
    "            axes[i, j].set_title(h[6:11])\n",
    "            #axes[i, j].set_xlim(-10, 1000)\n",
    "            #axes[i, j].set_xlim(math.log10(.25), math.log10(1000))\n",
    "            axes[i, j].set_xlim(math.log10(.1), math.log10(1000))\n",
    "            axes[i, j].grid(color='#dddddd')\n",
    "            axes[i, j].legend(loc=2, fontsize='x-small')\n",
    "            #axes[i, j].set_ylim(-0.1, 1.1)\n",
    "            axes[i, j].xaxis.set_major_formatter(logFormatter)\n",
    "    fig.subplots_adjust(hspace=0.3, wspace=0.4)\n",
    "    fig.suptitle('NDT Download Distributions')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING:google.auth._default:No project ID could be determined. Consider running `gcloud config set project` or setting the GOOGLE_CLOUD_PROJECT environment variable\n"
     ]
    }
   ],
   "source": [
    "df_ndt_variance = run_query(\"\"\"\n",
    "WITH mlab_ndt AS (\n",
    "  SELECT\n",
    "    connection_spec.server_hostname as server_hostname,\n",
    "    log_time,\n",
    "    web100_log_entry.connection_spec.remote_ip AS remote_ip,\n",
    "    (8 * (web100_log_entry.snap.HCThruOctetsAcked / (web100_log_entry.snap.SndLimTimeRwin + web100_log_entry.snap.SndLimTimeCwnd + web100_log_entry.snap.SndLimTimeSnd))) AS download_mbps\n",
    "  FROM\n",
    "    `measurement-lab.base_tables.ndt*`\n",
    "  WHERE\n",
    "\n",
    "  (    TIMESTAMP_TRUNC(log_time, DAY) BETWEEN TIMESTAMP(\"2018-02-11\") AND TIMESTAMP(\"2018-02-25\")\n",
    "    OR TIMESTAMP_TRUNC(log_time, DAY) BETWEEN TIMESTAMP(\"2018-03-11\") AND TIMESTAMP(\"2018-03-25\"))\n",
    "  AND REGEXP_CONTAINS(connection_spec.server_hostname, r\"mlab1.(dfw|lga|nuq)\\d\\d\")\n",
    "  AND web100_log_entry.snap.HCThruOctetsAcked >= 1000000\n",
    "  AND (web100_log_entry.snap.SndLimTimeRwin + web100_log_entry.snap.SndLimTimeCwnd + web100_log_entry.snap.SndLimTimeSnd) >= 9000000\n",
    "  AND (web100_log_entry.snap.SndLimTimeRwin + web100_log_entry.snap.SndLimTimeCwnd + web100_log_entry.snap.SndLimTimeSnd) < 600000000\n",
    "  AND connection_spec.data_direction = 1\n",
    "  \n",
    "  GROUP BY\n",
    "    server_hostname,\n",
    "    log_time,\n",
    "    web100_log_entry.connection_spec.remote_ip,\n",
    "    web100_log_entry.connection_spec.local_ip,\n",
    "    web100_log_entry.connection_spec.remote_port,\n",
    "    web100_log_entry.connection_spec.local_port,\n",
    "    download_mbps)\n",
    "\n",
    "\n",
    "SELECT\n",
    "  UPPER(REGEXP_EXTRACT(server_hostname, r'mlab[1-4].([a-z]{3})[0-9]{2}.*')) AS metro,\n",
    "  REGEXP_EXTRACT(server_hostname, r'mlab[1-4].([a-z]{3}[0-9]{2}).*') AS site,\n",
    "  REGEXP_EXTRACT(server_hostname, r'(mlab[1-4].[a-z]{3}[0-9]{2}).*') AS hostname,\n",
    "  CASE\n",
    "    WHEN TIMESTAMP_TRUNC(log_time, DAY) BETWEEN TIMESTAMP(\"2018-02-11\") AND TIMESTAMP(\"2018-02-25\") THEN 'before-2w'\n",
    "    WHEN TIMESTAMP_TRUNC(log_time, DAY) BETWEEN TIMESTAMP(\"2018-03-11\") AND TIMESTAMP(\"2018-03-25\") THEN 'after-2w'\n",
    "    ELSE 'what'\n",
    "  END AS period,\n",
    "  remote_ip,\n",
    "  STDDEV(download_mbps) AS download_stddev,\n",
    "  (STDDEV(download_mbps) / AVG(download_mbps)) AS download_cv,\n",
    "  MAX(download_mbps) AS download_max,\n",
    "  MIN(download_mbps) AS download_min,\n",
    "  AVG(download_mbps) AS download_avg\n",
    "\n",
    "FROM\n",
    "  mlab_ndt\n",
    "WHERE\n",
    "  remote_ip IN(\n",
    "    SELECT\n",
    "      remote_ip\n",
    "    FROM (\n",
    "      SELECT\n",
    "        remote_ip, count(*) as c1\n",
    "      FROM\n",
    "        mlab_ndt\n",
    "      WHERE\n",
    "        TIMESTAMP_TRUNC(log_time, DAY) BETWEEN TIMESTAMP(\"2018-02-11\") AND TIMESTAMP(\"2018-02-25\")\n",
    "      GROUP BY\n",
    "        remote_ip\n",
    "      HAVING c1 > 5\n",
    "    ) INNER JOIN (\n",
    "      SELECT\n",
    "        remote_ip AS remote_ip, count(*) as c2\n",
    "      FROM\n",
    "        mlab_ndt\n",
    "      WHERE\n",
    "        TIMESTAMP_TRUNC(log_time, DAY) BETWEEN TIMESTAMP(\"2018-03-11\") AND TIMESTAMP(\"2018-03-25\")\n",
    "      GROUP BY\n",
    "        remote_ip\n",
    "      HAVING c2 > 5\n",
    "    ) USING (remote_ip)) \n",
    "GROUP BY\n",
    "  server_hostname,\n",
    "  period,\n",
    "  remote_ip\n",
    "  --download_mbps\n",
    "\n",
    "HAVING download_stddev is not NULL\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "f = plot_hist(\n",
    "    df_ndt_variance, 'download_max', lambda r: int(math.sqrt(len(r))),\n",
    "    fig_by='metro', axes_by='site', group_by='period',\n",
    "    suptitle='Distribution of NDT Downloads - MAX(per remote_ip)',\n",
    "    label='{group} ({size})',\n",
    "    title='{axis}', axes=(3, 2),\n",
    "    xlim=(math.log10(.01), math.log10(1000)),\n",
    "    cdf=False, xlog=True, figsize=(9, 7))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "f = plot_hist(\n",
    "    df_ndt_variance, 'download_avg', lambda r: int(math.sqrt(len(r))),\n",
    "    fig_by='metro', axes_by='site', group_by='period',\n",
    "    suptitle='Distribution of NDT Downloads - AVERAGE(per remote_ip)',\n",
    "    label='{group} ({size})',\n",
    "    title='{axis}', axes=(3, 2),\n",
    "    xlim=(math.log10(.01), math.log10(1000)),\n",
    "    cdf=False, xlog=True, figsize=(9, 7))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "if False:\n",
    "    df_test_counts = run_query(\"\"\"\n",
    "CREATE TEMPORARY FUNCTION\n",
    "  timeBin(ts_usec INT64,\n",
    "    size INT64) AS ( CAST(TRUNC(ts_usec / 1e6 / 10) * 10 AS INT64) );\n",
    "\n",
    "WITH\n",
    "  mlab_ndt_dedup AS (\n",
    "  SELECT\n",
    "    test_id,\n",
    "    log_time,\n",
    "    connection_spec.server_hostname AS hostname,\n",
    "    web100_log_entry.snap.StartTimeStamp as StartTimeStamp,\n",
    "    (8 * (web100_log_entry.snap.HCThruOctetsAcked / (web100_log_entry.snap.SndLimTimeRwin + web100_log_entry.snap.SndLimTimeCwnd + web100_log_entry.snap.SndLimTimeSnd))) AS download_mbps\n",
    "\n",
    "  FROM\n",
    "    `measurement-lab.base_tables.ndt*`\n",
    "  WHERE\n",
    "          (web100_log_entry.snap.SndLimTimeRwin + web100_log_entry.snap.SndLimTimeCwnd + web100_log_entry.snap.SndLimTimeSnd) >= 9000000\n",
    "      AND (web100_log_entry.snap.SndLimTimeRwin + web100_log_entry.snap.SndLimTimeCwnd + web100_log_entry.snap.SndLimTimeSnd) < 600000000\n",
    "      AND web100_log_entry.snap.HCThruOctetsAcked >= 1000000\n",
    "      AND connection_spec.data_direction = 1\n",
    "      AND REGEXP_CONTAINS(connection_spec.server_hostname, r\"mlab1.(dfw02)\")\n",
    "      AND log_time BETWEEN TIMESTAMP(\"2017-06-01\") AND TIMESTAMP(\"2018-08-01\")\n",
    "\n",
    "  GROUP BY\n",
    "    test_id,\n",
    "    log_time,\n",
    "    hostname,\n",
    "    StartTimeStamp,\n",
    "    web100_log_entry.connection_spec.remote_ip,\n",
    "    web100_log_entry.connection_spec.local_ip,\n",
    "    web100_log_entry.connection_spec.remote_port,\n",
    "    web100_log_entry.connection_spec.local_port,\n",
    "    download_mbps)\n",
    "    \n",
    "  ,ndt_test_ids_with_discards AS (\n",
    "  SELECT\n",
    "    ndt.test_id as test_id,\n",
    "    ndt.hostname as hostname,\n",
    "    ndt.day as day,\n",
    "    SUM(disco.discards) AS discards,\n",
    "    ndt.download_mbps as download_mbps\n",
    "  FROM (\n",
    "    SELECT\n",
    "      hostname,\n",
    "      UNIX_SECONDS(sample.timestamp) - 10 AS tstart,\n",
    "      UNIX_SECONDS(sample.timestamp) AS tend,\n",
    "      sample.value AS discards\n",
    "    FROM\n",
    "      `measurement-lab.base_tables.switch*`,\n",
    "      UNNEST(sample) AS sample\n",
    "    WHERE\n",
    "      metric LIKE 'switch.discards.uplink.tx'\n",
    "      AND sample.timestamp BETWEEN TIMESTAMP(\"2017-06-01\") AND TIMESTAMP(\"2018-08-01\")\n",
    "      AND REGEXP_CONTAINS(hostname, r\"mlab1.(dfw02)\")\n",
    "    GROUP BY\n",
    "      hostname,\n",
    "      tstart,\n",
    "      tend,\n",
    "      discards\n",
    "    HAVING\n",
    "      discards > 0\n",
    "  ) AS disco\n",
    "  JOIN (\n",
    "    SELECT\n",
    "      test_id,\n",
    "      connection_spec.server_hostname as hostname,\n",
    "      TIMESTAMP_TRUNC(log_time, DAY) as day,\n",
    "      timeBin(web100_log_entry.snap.StartTimeStamp, 10) AS tstart,\n",
    "      timeBin(web100_log_entry.snap.StartTimeStamp, 10) + 20 AS tend,\n",
    "      (8 * (web100_log_entry.snap.HCThruOctetsAcked / (web100_log_entry.snap.SndLimTimeRwin + web100_log_entry.snap.SndLimTimeCwnd + web100_log_entry.snap.SndLimTimeSnd))) AS download_mbps\n",
    "\n",
    "  FROM\n",
    "    `measurement-lab.base_tables.ndt*`\n",
    "  WHERE\n",
    "          (web100_log_entry.snap.SndLimTimeRwin + web100_log_entry.snap.SndLimTimeCwnd + web100_log_entry.snap.SndLimTimeSnd) >= 9000000\n",
    "      AND (web100_log_entry.snap.SndLimTimeRwin + web100_log_entry.snap.SndLimTimeCwnd + web100_log_entry.snap.SndLimTimeSnd) < 600000000\n",
    "      AND web100_log_entry.snap.HCThruOctetsAcked >= 1000000\n",
    "      AND connection_spec.data_direction = 1\n",
    "      AND REGEXP_CONTAINS(connection_spec.server_hostname, r\"mlab1.(dfw02)\")\n",
    "      AND log_time BETWEEN TIMESTAMP(\"2017-06-01\") AND TIMESTAMP(\"2018-08-01\")\n",
    "    GROUP BY\n",
    "      test_id,\n",
    "      hostname,\n",
    "      day,\n",
    "      tstart,\n",
    "      tend,\n",
    "      download_mbps ) AS ndt\n",
    "  ON (disco.hostname = ndt.hostname\n",
    "      AND (disco.tstart = ndt.tstart OR disco.tend = ndt.tend))\n",
    "  GROUP BY\n",
    "    day, hostname, test_id, download_mbps\n",
    "  )\n",
    "\n",
    "\n",
    "-- Split the two timebins into separate periods: before-2w and after-2w. Select clients (remote_ips) with more than 5 tests in both periods.\n",
    "-- All tests from the before-2w period will have a test_id found in ndt_test_ids_with_discards.\n",
    "SELECT\n",
    "  day, metro, site, hostname, discards, COUNT(*) as count\n",
    "FROM\n",
    "(\n",
    "  SELECT\n",
    "    TIMESTAMP_TRUNC(log_time, DAY) as day,\n",
    "    UPPER(REGEXP_EXTRACT(hostname, r'mlab[1-4].([a-z]{3})[0-9]{2}.*')) AS metro,\n",
    "    REGEXP_EXTRACT(hostname, r'mlab[1-4].([a-z]{3}[0-9]{2}).*') AS site,\n",
    "    REGEXP_EXTRACT(hostname, r'(mlab[1-4].[a-z]{3}[0-9]{2}).*') AS hostname,\n",
    "    CASE\n",
    "      WHEN test_id IN(SELECT test_id from ndt_test_ids_with_discards) THEN 'discards'\n",
    "      WHEN test_id NOT IN(SELECT test_id from ndt_test_ids_with_discards) THEN 'without'\n",
    "      ELSE 'what'\n",
    "    END as discards\n",
    "\n",
    "  FROM\n",
    "    mlab_ndt_dedup\n",
    "  )\n",
    "GROUP BY\n",
    "  day, metro, site, hostname, discards\n",
    "\"\"\")\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "running query dfw01 Tue Aug 28 23:26:57 2018\n",
      "running query dfw02 Tue Aug 28 23:27:28 2018\n",
      "running query dfw03 Tue Aug 28 23:30:34 2018\n",
      "running query dfw04 Tue Aug 28 23:31:38 2018\n",
      "running query dfw05 Tue Aug 28 23:34:11 2018\n",
      "running query dfw06 Tue Aug 28 23:36:14 2018\n",
      "running query lga02 Tue Aug 28 23:36:58 2018\n",
      "running query lga03 Tue Aug 28 23:37:40 2018\n",
      "running query lga04 Tue Aug 28 23:39:19 2018\n",
      "running query lga05 Tue Aug 28 23:39:47 2018\n",
      "running query lga06 Tue Aug 28 23:40:20 2018\n",
      "running query lga07 Tue Aug 28 23:41:54 2018\n"
     ]
    }
   ],
   "source": [
    "import time\n",
    "def query(site):\n",
    "    print 'running query', site, time.ctime()\n",
    "    return \"\"\"\n",
    "CREATE TEMPORARY FUNCTION\n",
    "  timeBin(ts_usec INT64,\n",
    "    size INT64) AS ( CAST(TRUNC(ts_usec / 1e6 / 10) * 10 AS INT64) );\n",
    "\n",
    "WITH\n",
    "  mlab_ndt_dedup AS (\n",
    "  SELECT\n",
    "    test_id,\n",
    "    log_time,\n",
    "    connection_spec.server_hostname AS hostname,\n",
    "    web100_log_entry.snap.StartTimeStamp as StartTimeStamp,\n",
    "    (8 * (web100_log_entry.snap.HCThruOctetsAcked / (web100_log_entry.snap.SndLimTimeRwin + web100_log_entry.snap.SndLimTimeCwnd + web100_log_entry.snap.SndLimTimeSnd))) AS download_mbps\n",
    "\n",
    "  FROM\n",
    "    `measurement-lab.release.ndt_all`\n",
    "  WHERE\n",
    "          log_time BETWEEN TIMESTAMP(\"2016-06-01\") AND TIMESTAMP(\"2018-08-01\")\n",
    "      AND (connection_spec.server_hostname = \"mlab1.\"\"\"+site+\"\"\".measurement-lab.org\" OR connection_spec.server_hostname = \"ndt.iupui.mlab1.\"\"\"+site+\"\"\".measurement-lab.org\")\n",
    "      AND connection_spec.data_direction = 1\n",
    "      AND (web100_log_entry.snap.SndLimTimeRwin + web100_log_entry.snap.SndLimTimeCwnd + web100_log_entry.snap.SndLimTimeSnd) >= 9000000\n",
    "      AND (web100_log_entry.snap.SndLimTimeRwin + web100_log_entry.snap.SndLimTimeCwnd + web100_log_entry.snap.SndLimTimeSnd) < 600000000\n",
    "      AND web100_log_entry.snap.HCThruOctetsAcked >= 1000000\n",
    "\n",
    "\n",
    "  GROUP BY\n",
    "    test_id,\n",
    "    log_time,\n",
    "    hostname,\n",
    "    StartTimeStamp,\n",
    "    web100_log_entry.connection_spec.remote_ip,\n",
    "    web100_log_entry.connection_spec.local_ip,\n",
    "    web100_log_entry.connection_spec.remote_port,\n",
    "    web100_log_entry.connection_spec.local_port,\n",
    "    download_mbps)\n",
    "    \n",
    "  ,ndt_test_ids_with_discards AS (\n",
    "  SELECT\n",
    "    ndt.test_id as test_id,\n",
    "    ndt.hostname as hostname,\n",
    "    ndt.day as day,\n",
    "    SUM(disco.discards) AS discards,\n",
    "    ndt.download_mbps as download_mbps\n",
    "  FROM (\n",
    "    SELECT\n",
    "      hostname,\n",
    "      UNIX_SECONDS(sample.timestamp) - 10 AS tstart,\n",
    "      UNIX_SECONDS(sample.timestamp) AS tend,\n",
    "      sample.value AS discards\n",
    "    FROM\n",
    "      `measurement-lab.base_tables.switch*`,\n",
    "      UNNEST(sample) AS sample\n",
    "    WHERE\n",
    "      metric = 'switch.discards.uplink.tx'\n",
    "      AND sample.timestamp BETWEEN TIMESTAMP(\"2016-06-01\") AND TIMESTAMP(\"2018-08-01\")\n",
    "      AND hostname = \"mlab1.\"\"\"+site+\"\"\".measurement-lab.org\"\n",
    "    GROUP BY\n",
    "      hostname,\n",
    "      tstart,\n",
    "      tend,\n",
    "      discards\n",
    "    HAVING\n",
    "      discards > 0\n",
    "  ) AS disco\n",
    "  JOIN (\n",
    "    SELECT\n",
    "      test_id,\n",
    "      REGEXP_EXTRACT(connection_spec.server_hostname, r\"(mlab1.\"\"\"+site+\"\"\".measurement-lab.org)\") as hostname,\n",
    "      TIMESTAMP_TRUNC(log_time, DAY) as day,\n",
    "      timeBin(web100_log_entry.snap.StartTimeStamp, 10) AS tstart,\n",
    "      timeBin(web100_log_entry.snap.StartTimeStamp, 10) + 20 AS tend,\n",
    "      (8 * (web100_log_entry.snap.HCThruOctetsAcked / (web100_log_entry.snap.SndLimTimeRwin + web100_log_entry.snap.SndLimTimeCwnd + web100_log_entry.snap.SndLimTimeSnd))) AS download_mbps\n",
    "\n",
    "  FROM\n",
    "    `measurement-lab.release.ndt_all`\n",
    "    --`measurement-lab.base_tables.ndt*`\n",
    "  WHERE\n",
    "          log_time BETWEEN TIMESTAMP(\"2016-06-01\") AND TIMESTAMP(\"2018-08-01\")\n",
    "      AND connection_spec.data_direction = 1\n",
    "      AND (connection_spec.server_hostname = \"mlab1.\"\"\"+site+\"\"\".measurement-lab.org\" OR connection_spec.server_hostname = \"ndt.iupui.mlab1.\"\"\"+site+\"\"\".measurement-lab.org\")\n",
    "      AND (web100_log_entry.snap.SndLimTimeRwin + web100_log_entry.snap.SndLimTimeCwnd + web100_log_entry.snap.SndLimTimeSnd) >= 9000000\n",
    "      AND (web100_log_entry.snap.SndLimTimeRwin + web100_log_entry.snap.SndLimTimeCwnd + web100_log_entry.snap.SndLimTimeSnd) < 600000000\n",
    "      AND web100_log_entry.snap.HCThruOctetsAcked >= 1000000\n",
    "\n",
    "    GROUP BY\n",
    "      test_id,\n",
    "      hostname,\n",
    "      day,\n",
    "      tstart,\n",
    "      tend,\n",
    "      download_mbps ) AS ndt\n",
    "  ON (disco.hostname = ndt.hostname\n",
    "      AND (disco.tstart = ndt.tstart OR disco.tend = ndt.tend))\n",
    "  GROUP BY\n",
    "    day, hostname, test_id, download_mbps\n",
    "  )\n",
    "\n",
    "\n",
    "SELECT\n",
    "  day, metro, site, hostname, discards, COUNT(*) as count\n",
    "FROM\n",
    "(\n",
    "  SELECT\n",
    "    TIMESTAMP_TRUNC(log_time, DAY) as day,\n",
    "    UPPER(REGEXP_EXTRACT(connection_spec.server_hostname, r'mlab[1-4].([a-z]{3})[0-9]{2}.*')) AS metro,\n",
    "    REGEXP_EXTRACT(connection_spec.server_hostname, r'mlab[1-4].([a-z]{3}[0-9]{2}).*') AS site,\n",
    "    REGEXP_EXTRACT(connection_spec.server_hostname, r'(mlab[1-4].[a-z]{3}[0-9]{2}).*') AS hostname,\n",
    "    CASE\n",
    "      WHEN test_id IN(SELECT test_id from ndt_test_ids_with_discards) THEN 'non-zero'\n",
    "      ELSE 'zero'\n",
    "--      WHEN test_id NOT IN(SELECT test_id from ndt_test_ids_with_discards) THEN 'without'\n",
    "--      ELSE 'what'\n",
    "    END as discards\n",
    "\n",
    "  FROM\n",
    "    --`measurement-lab.base_tables.ndt*`\n",
    "    `measurement-lab.release.ndt_all`\n",
    "  WHERE\n",
    "        log_time BETWEEN TIMESTAMP(\"2016-06-01\") AND TIMESTAMP(\"2018-08-01\")\n",
    "    AND connection_spec.data_direction = 1\n",
    "    AND (connection_spec.server_hostname = \"mlab1.\"\"\"+site+\"\"\".measurement-lab.org\" OR connection_spec.server_hostname = \"ndt.iupui.mlab1.\"\"\"+site+\"\"\".measurement-lab.org\")\n",
    "    AND (web100_log_entry.snap.SndLimTimeRwin + web100_log_entry.snap.SndLimTimeCwnd + web100_log_entry.snap.SndLimTimeSnd) >= 9000000\n",
    "    AND (web100_log_entry.snap.SndLimTimeRwin + web100_log_entry.snap.SndLimTimeCwnd + web100_log_entry.snap.SndLimTimeSnd) < 600000000\n",
    "    AND web100_log_entry.snap.HCThruOctetsAcked >= 1000000\n",
    "\n",
    "  )\n",
    "GROUP BY\n",
    "  day, metro, site, hostname, discards\n",
    "\"\"\"\n",
    "                           \n",
    "df_test_counts = pd.concat([\n",
    "    run_query(query(\"dfw01\")),\n",
    "    run_query(query(\"dfw02\")),\n",
    "    run_query(query(\"dfw03\")),\n",
    "    run_query(query(\"dfw04\")),\n",
    "    run_query(query(\"dfw05\")),\n",
    "    run_query(query(\"dfw06\")),\n",
    "    run_query(query(\"lga02\")),\n",
    "    run_query(query(\"lga03\")),\n",
    "    run_query(query(\"lga04\")),\n",
    "    run_query(query(\"lga05\")),\n",
    "    run_query(query(\"lga06\")),\n",
    "    run_query(query(\"lga07\")),\n",
    "])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index([u'count', u'day', u'discards', u'hostname', u'metro', u'site'], dtype='object')\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "{u'DFW': (<matplotlib.figure.Figure at 0x1a3d1289d0>,\n",
       "  array([[<matplotlib.axes._subplots.AxesSubplot object at 0x1a3d12a190>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a23d60590>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x1a3d9869d0>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a3d9e12d0>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x1a3da30dd0>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a3d8b4c90>]],\n",
       "        dtype=object)),\n",
       " u'LGA': (<matplotlib.figure.Figure at 0x1a28746310>,\n",
       "  array([[<matplotlib.axes._subplots.AxesSubplot object at 0x1a287ee850>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a28834810>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x1a2885f1d0>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a287e6790>],\n",
       "         [<matplotlib.axes._subplots.AxesSubplot object at 0x1a288f8550>,\n",
       "          <matplotlib.axes._subplots.AxesSubplot object at 0x1a28946090>]],\n",
       "        dtype=object))}"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# NOTE: does not preserve binsize across group_by. Each line re-calculates the bin size.\n",
    "print df_test_counts.keys()\n",
    "plot_scatter(\n",
    "    df_test_counts, 'day', 'count',\n",
    "    fig_by='metro', axes_by='site', group_by='discards',\n",
    "    suptitle='NDT Test Counts (with or without discards)',\n",
    "    label='{group}',\n",
    "    title='{axis}',\n",
    "    axes=(3, 2), figsize=(12, 10),\n",
    "    ylim=(-200, 30000),\n",
    "    xlim=(pd.to_datetime(\"2016-05-31\"), pd.to_datetime(\"2018-08-01\")),\n",
    "    fx=lambda l: [pd.to_datetime(t) for t in l])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index([u'count', u'day', u'discards', u'hostname', u'metro', u'site'], dtype='object')\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "{u'DFW': (<matplotlib.figure.Figure at 0x1a3b3e9310>,\n",
       "  array([[<matplotlib.axes._subplots.AxesSubplot object at 0x1a38c29550>]],\n",
       "        dtype=object)),\n",
       " u'LGA': (<matplotlib.figure.Figure at 0x1a38bd2cd0>,\n",
       "  array([[<matplotlib.axes._subplots.AxesSubplot object at 0x1a3b457050>]],\n",
       "        dtype=object))}"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# NOTE: does not preserve binsize across group_by. Each line re-calculates the bin size.\n",
    "print df_test_counts.keys()\n",
    "plot_scatter(\n",
    "    df_test_counts_lga, 'day', 'count',\n",
    "    fig_by='metro', group_by='discards',\n",
    "    suptitle='NDT Test Counts (with or without discards)',\n",
    "    label='{group}',\n",
    "    title='{figure}',\n",
    "    axes=(1, 1), figsize=(12, 10),\n",
    "    ylim=(0, 30000),\n",
    "    xlim=(pd.to_datetime(\"2016-05-31\"), pd.to_datetime(\"2018-08-01\")),\n",
    "    fx=lambda l: [pd.to_datetime(t) for t in l])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.14"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}