ScatterPlot_dm.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import bql\n",
    "import bqplot as bqp\n",
    "import pandas as pd\n",
    "\n",
    "bq = bql.Service()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "Members = bq.univ.members('LUACTRUU Index')\n",
    "\n",
    "dates = bq.func.range('2019-12-31','2020-04-10')\n",
    "\n",
    "spread = bq.func.dropna(bq.data.spread(dates=dates))\n",
    "current_spread = bq.func.last(spread)\n",
    "long_comp_name = bq.data.long_comp_name()\n",
    "\n",
    "z_score = bq.func.z_score(spread)\n",
    "current_z_score = bq.func.last(z_score)\n",
    "\n",
    "fields = {'Name': long_comp_name, 'Current Spread': current_spread, 'Current Z Score': current_z_score}\n",
    "\n",
    "rank = bq.func.grouprank(current_z_score)\n",
    "\n",
    "Filtered_Members = bq.univ.filter(Members,rank<=200)\n",
    "\n",
    "request = bql.Request(Filtered_Members,fields)\n",
    "response = bq.execute(request)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Examine `response` object**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                                                            Name\n",
      "ID                                                              \n",
      "AN267754 Corp  National Rural Utilities Cooperative Finance Corp\n",
      "EH502090 Corp                      Public Service Co of Colorado\n",
      "EI080338 Corp                             Boston Scientific Corp\n",
      "EJ409739 Corp                                CommonSpirit Health\n",
      "EK276667 Corp                     Corporate Office Properties LP\n",
      "                    DATE  Current Spread\n",
      "ID                                      \n",
      "AN267754 Corp 2020-04-09      235.445679\n",
      "EH502090 Corp 2020-04-09      295.915192\n",
      "EI080338 Corp 2020-04-09      282.963989\n",
      "EJ409739 Corp 2020-04-09      422.861877\n",
      "EK276667 Corp 2020-04-09      253.987640\n",
      "                    DATE  Current Z Score\n",
      "ID                                       \n",
      "AN267754 Corp 2020-04-09         2.197980\n",
      "EH502090 Corp 2020-04-09         2.326671\n",
      "EI080338 Corp 2020-04-09         2.130137\n",
      "EJ409739 Corp 2020-04-09         2.670720\n",
      "EK276667 Corp 2020-04-09         2.510107\n"
     ]
    }
   ],
   "source": [
    "#the full df for each field\n",
    "for res in response:\n",
    "    print(res.df().head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### DF Merge Ninjitsu method 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>Name</th>\n",
       "      <th>Current Spread</th>\n",
       "      <th>Current Z Score</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>AN267754 Corp</th>\n",
       "      <td>National Rural Utilities Cooperative Finance Corp</td>\n",
       "      <td>235.445679</td>\n",
       "      <td>2.197980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EH502090 Corp</th>\n",
       "      <td>Public Service Co of Colorado</td>\n",
       "      <td>295.915192</td>\n",
       "      <td>2.326671</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EI080338 Corp</th>\n",
       "      <td>Boston Scientific Corp</td>\n",
       "      <td>282.963989</td>\n",
       "      <td>2.130137</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EJ409739 Corp</th>\n",
       "      <td>CommonSpirit Health</td>\n",
       "      <td>422.861877</td>\n",
       "      <td>2.670720</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EK276667 Corp</th>\n",
       "      <td>Corporate Office Properties LP</td>\n",
       "      <td>253.987640</td>\n",
       "      <td>2.510107</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                            Name  \\\n",
       "ID                                                                 \n",
       "AN267754 Corp  National Rural Utilities Cooperative Finance Corp   \n",
       "EH502090 Corp                      Public Service Co of Colorado   \n",
       "EI080338 Corp                             Boston Scientific Corp   \n",
       "EJ409739 Corp                                CommonSpirit Health   \n",
       "EK276667 Corp                     Corporate Office Properties LP   \n",
       "\n",
       "               Current Spread  Current Z Score  \n",
       "ID                                              \n",
       "AN267754 Corp      235.445679         2.197980  \n",
       "EH502090 Corp      295.915192         2.326671  \n",
       "EI080338 Corp      282.963989         2.130137  \n",
       "EJ409739 Corp      422.861877         2.670720  \n",
       "EK276667 Corp      253.987640         2.510107  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#combine the multi-dataframe response into one df\n",
    "tbl = pd.DataFrame({r.name:r.df()[r.name] for r in response})\n",
    "tbl.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### DF Merge Ninjitsu method 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Function to convert the result of a BQL query where meta data for each field isn't uniform\n",
    "from functools import reduce\n",
    "import pandas as pd\n",
    "\n",
    "def merge_dfs(response):\n",
    "    df_l = [x.df().reset_index() for x in list(response)]\n",
    "    newdf = reduce(lambda left,right: pd.merge(left,right,on=['ID']), df_l)\n",
    "    return newdf\n",
    "\n",
    "data = merge_dfs(response)"
   ]
  },
  {
   "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>ID</th>\n",
       "      <th>Name</th>\n",
       "      <th>DATE_x</th>\n",
       "      <th>Current Spread</th>\n",
       "      <th>DATE_y</th>\n",
       "      <th>Current Z Score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>UV388933 Corp</td>\n",
       "      <td>Spirit Airlines Pass Through Trust 2015-1A</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>721.310059</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>2.947659</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>DD101122 Corp</td>\n",
       "      <td>Weyerhaeuser Co</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>336.082001</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>2.234621</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>EJ618909 Corp</td>\n",
       "      <td>Essex Portfolio LP</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>354.798737</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>3.584018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>AM969171 Corp</td>\n",
       "      <td>Ventas Realty LP</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>290.914551</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>2.649100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149</th>\n",
       "      <td>AO296348 Corp</td>\n",
       "      <td>ORIX Corp</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>318.270996</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>2.514680</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>AQ214314 Corp</td>\n",
       "      <td>Healthcare Realty Trust Inc</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>344.050476</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>2.433353</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>128</th>\n",
       "      <td>QJ131551 Corp</td>\n",
       "      <td>Kimco Realty Corp</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>274.124176</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>2.548359</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>195</th>\n",
       "      <td>ED939561 Corp</td>\n",
       "      <td>El Paso Electric Co</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>300.447235</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>4.145675</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>EK796000 Corp</td>\n",
       "      <td>BorgWarner Inc</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>406.252441</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>2.063051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>170</th>\n",
       "      <td>EK600613 Corp</td>\n",
       "      <td>Duke Realty LP</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>352.726868</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>2.881579</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>178</th>\n",
       "      <td>AN870034 Corp</td>\n",
       "      <td>Tapestry Inc</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>758.073975</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>2.323347</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>EI080338 Corp</td>\n",
       "      <td>Boston Scientific Corp</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>282.963989</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>2.130137</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>162</th>\n",
       "      <td>EG312717 Corp</td>\n",
       "      <td>Continental Airlines 2007-1 Class A Pass Throu...</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>602.668396</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>3.346780</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>EI690570 Corp</td>\n",
       "      <td>Reinsurance Group of America Inc</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>281.027618</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>2.413111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>EJ409739 Corp</td>\n",
       "      <td>CommonSpirit Health</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>422.861877</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>2.670720</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141</th>\n",
       "      <td>LW650412 Corp</td>\n",
       "      <td>ITC Holdings Corp</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>251.885483</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>2.179791</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105</th>\n",
       "      <td>EI548514 Corp</td>\n",
       "      <td>ONEOK Partners LP</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>726.607971</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>2.070387</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>EK798767 Corp</td>\n",
       "      <td>Essex Portfolio LP</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>230.901993</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>2.516624</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td>AT980547 Corp</td>\n",
       "      <td>National Fuel Gas Co</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>579.994568</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>3.037283</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>114</th>\n",
       "      <td>QZ193035 Corp</td>\n",
       "      <td>Kimco Realty Corp</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>290.661652</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>2.143201</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                ID                                               Name  \\\n",
       "68   UV388933 Corp         Spirit Airlines Pass Through Trust 2015-1A   \n",
       "13   DD101122 Corp                                    Weyerhaeuser Co   \n",
       "38   EJ618909 Corp                                 Essex Portfolio LP   \n",
       "61   AM969171 Corp                                   Ventas Realty LP   \n",
       "149  AO296348 Corp                                          ORIX Corp   \n",
       "28   AQ214314 Corp                        Healthcare Realty Trust Inc   \n",
       "128  QJ131551 Corp                                  Kimco Realty Corp   \n",
       "195  ED939561 Corp                                El Paso Electric Co   \n",
       "62   EK796000 Corp                                     BorgWarner Inc   \n",
       "170  EK600613 Corp                                     Duke Realty LP   \n",
       "178  AN870034 Corp                                       Tapestry Inc   \n",
       "2    EI080338 Corp                             Boston Scientific Corp   \n",
       "162  EG312717 Corp  Continental Airlines 2007-1 Class A Pass Throu...   \n",
       "100  EI690570 Corp                   Reinsurance Group of America Inc   \n",
       "3    EJ409739 Corp                                CommonSpirit Health   \n",
       "141  LW650412 Corp                                  ITC Holdings Corp   \n",
       "105  EI548514 Corp                                  ONEOK Partners LP   \n",
       "63   EK798767 Corp                                 Essex Portfolio LP   \n",
       "77   AT980547 Corp                               National Fuel Gas Co   \n",
       "114  QZ193035 Corp                                  Kimco Realty Corp   \n",
       "\n",
       "        DATE_x  Current Spread     DATE_y  Current Z Score  \n",
       "68  2020-04-09      721.310059 2020-04-09         2.947659  \n",
       "13  2020-04-09      336.082001 2020-04-09         2.234621  \n",
       "38  2020-04-09      354.798737 2020-04-09         3.584018  \n",
       "61  2020-04-09      290.914551 2020-04-09         2.649100  \n",
       "149 2020-04-09      318.270996 2020-04-09         2.514680  \n",
       "28  2020-04-09      344.050476 2020-04-09         2.433353  \n",
       "128 2020-04-09      274.124176 2020-04-09         2.548359  \n",
       "195 2020-04-09      300.447235 2020-04-09         4.145675  \n",
       "62  2020-04-09      406.252441 2020-04-09         2.063051  \n",
       "170 2020-04-09      352.726868 2020-04-09         2.881579  \n",
       "178 2020-04-09      758.073975 2020-04-09         2.323347  \n",
       "2   2020-04-09      282.963989 2020-04-09         2.130137  \n",
       "162 2020-04-09      602.668396 2020-04-09         3.346780  \n",
       "100 2020-04-09      281.027618 2020-04-09         2.413111  \n",
       "3   2020-04-09      422.861877 2020-04-09         2.670720  \n",
       "141 2020-04-09      251.885483 2020-04-09         2.179791  \n",
       "105 2020-04-09      726.607971 2020-04-09         2.070387  \n",
       "63  2020-04-09      230.901993 2020-04-09         2.516624  \n",
       "77  2020-04-09      579.994568 2020-04-09         3.037283  \n",
       "114 2020-04-09      290.661652 2020-04-09         2.143201  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#dataframe = bql.combined_df(response)\n",
    "data.sample(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>ID</th>\n",
       "      <th>Name</th>\n",
       "      <th>DATE</th>\n",
       "      <th>Current Spread</th>\n",
       "      <th>Current Z Score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AN267754 Corp</td>\n",
       "      <td>National Rural Utilities Cooperative Finance Corp</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>235.445679</td>\n",
       "      <td>2.197980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>EH502090 Corp</td>\n",
       "      <td>Public Service Co of Colorado</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>295.915192</td>\n",
       "      <td>2.326671</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>EI080338 Corp</td>\n",
       "      <td>Boston Scientific Corp</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>282.963989</td>\n",
       "      <td>2.130137</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>EJ409739 Corp</td>\n",
       "      <td>CommonSpirit Health</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>422.861877</td>\n",
       "      <td>2.670720</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>EK276667 Corp</td>\n",
       "      <td>Corporate Office Properties LP</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>253.987640</td>\n",
       "      <td>2.510107</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              ID                                               Name  \\\n",
       "0  AN267754 Corp  National Rural Utilities Cooperative Finance Corp   \n",
       "1  EH502090 Corp                      Public Service Co of Colorado   \n",
       "2  EI080338 Corp                             Boston Scientific Corp   \n",
       "3  EJ409739 Corp                                CommonSpirit Health   \n",
       "4  EK276667 Corp                     Corporate Office Properties LP   \n",
       "\n",
       "        DATE  Current Spread  Current Z Score  \n",
       "0 2020-04-09      235.445679         2.197980  \n",
       "1 2020-04-09      295.915192         2.326671  \n",
       "2 2020-04-09      282.963989         2.130137  \n",
       "3 2020-04-09      422.861877         2.670720  \n",
       "4 2020-04-09      253.987640         2.510107  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.drop(['DATE_y'], axis=1, inplace=True)\n",
    "data.rename(columns = {'DATE_x': 'DATE'}, inplace=True)\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "#dataframe.reset_index(inplace=True)\n",
    "\n",
    "#dataframe.sample(n=20)\n",
    "dataframe = data\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### <span style='color:orange'> Snippet </span> _(h/t Sal Failla)_"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>ID</th>\n",
       "      <th>Name</th>\n",
       "      <th>DATE</th>\n",
       "      <th>Current Spread</th>\n",
       "      <th>Current Z Score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>182</th>\n",
       "      <td>AZ327079 Corp</td>\n",
       "      <td>UDR Inc</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>292.856598</td>\n",
       "      <td>2.114133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>AM201825 Corp</td>\n",
       "      <td>Regency Centers LP</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>378.631470</td>\n",
       "      <td>2.125270</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>AX754628 Corp</td>\n",
       "      <td>Ingersoll-Rand Luxembourg Finance SA</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>255.582870</td>\n",
       "      <td>2.170313</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>AZ132511 Corp</td>\n",
       "      <td>American Campus Communities Operating Partners...</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>332.856689</td>\n",
       "      <td>2.429392</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>LW430737 Corp</td>\n",
       "      <td>Evergy Kansas Central Inc</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>149.812500</td>\n",
       "      <td>2.419672</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>BG022085 Corp</td>\n",
       "      <td>Oshkosh Corp</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>347.440735</td>\n",
       "      <td>2.209044</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>151</th>\n",
       "      <td>AM276086 Corp</td>\n",
       "      <td>Children's Hospital Corp/The</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>215.947159</td>\n",
       "      <td>2.179554</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>AL636052 Corp</td>\n",
       "      <td>EPR Properties</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>669.801147</td>\n",
       "      <td>2.292993</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166</th>\n",
       "      <td>EK744869 Corp</td>\n",
       "      <td>MPLX LP</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>919.466980</td>\n",
       "      <td>2.270598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>AX316775 Corp</td>\n",
       "      <td>Ventas Realty LP</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>430.173279</td>\n",
       "      <td>2.194352</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                ID                                               Name  \\\n",
       "182  AZ327079 Corp                                            UDR Inc   \n",
       "121  AM201825 Corp                                 Regency Centers LP   \n",
       "83   AX754628 Corp               Ingersoll-Rand Luxembourg Finance SA   \n",
       "65   AZ132511 Corp  American Campus Communities Operating Partners...   \n",
       "53   LW430737 Corp                          Evergy Kansas Central Inc   \n",
       "98   BG022085 Corp                                       Oshkosh Corp   \n",
       "151  AM276086 Corp                       Children's Hospital Corp/The   \n",
       "123  AL636052 Corp                                     EPR Properties   \n",
       "166  EK744869 Corp                                            MPLX LP   \n",
       "54   AX316775 Corp                                   Ventas Realty LP   \n",
       "\n",
       "          DATE  Current Spread  Current Z Score  \n",
       "182 2020-04-09      292.856598         2.114133  \n",
       "121 2020-04-09      378.631470         2.125270  \n",
       "83  2020-04-09      255.582870         2.170313  \n",
       "65  2020-04-09      332.856689         2.429392  \n",
       "53  2020-04-09      149.812500         2.419672  \n",
       "98  2020-04-09      347.440735         2.209044  \n",
       "151 2020-04-09      215.947159         2.179554  \n",
       "123 2020-04-09      669.801147         2.292993  \n",
       "166 2020-04-09      919.466980         2.270598  \n",
       "54  2020-04-09      430.173279         2.194352  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataframe.sample(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "f787a52b08774fdda29a712035337ca9",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox(children=(Figure(animation_duration=500, axes=[Axis(label='Current Spread', scale=LinearScale()), Axis(la…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import bqplot as bqp\n",
    "from bqplot.interacts import BrushSelector\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from ipywidgets import Dropdown, HBox, VBox, HTML\n",
    "import bqwidgets as bqw\n",
    "\n",
    "\n",
    "# Create scales\n",
    "scale_x = bqp.LinearScale()\n",
    "scale_y = bqp.LinearScale()\n",
    "\n",
    "# Create marks\n",
    "mark_scatter = bqp.Scatter(x=dataframe[dataframe.columns[3]],\n",
    "                           y=dataframe[dataframe.columns[4]],\n",
    "                           scales={'x': scale_x, 'y': scale_y},\n",
    "                           default_size=48,\n",
    "                           colors=['#1B84ED'])\n",
    "\n",
    "# Create Axes\n",
    "axis_x = bqp.Axis(scale=scale_x, label=dataframe.columns[3])\n",
    "axis_y = bqp.Axis(scale=scale_y,\n",
    "                  orientation='vertical',\n",
    "                  tick_format='0.0f',\n",
    "                  label=dataframe.columns[4])\n",
    "\n",
    "# Create selector\n",
    "selector = BrushSelector(x_scale=scale_x,\n",
    "                         y_scale=scale_y,\n",
    "                         marks=[mark_scatter])\n",
    "\n",
    "# Create Figure\n",
    "figure = bqp.Figure(marks=[mark_scatter],\n",
    "                    axes=[axis_x, axis_y],\n",
    "                    animation_duration=500,\n",
    "                    layout={'width':'99%', 'height':'400px'},\n",
    "                    padding_x=0.05,\n",
    "                    title='Interactive scatter plot with linked datagrid',\n",
    "                    title_style={'font-size': '22px'},\n",
    "                    padding_y=0.05,\n",
    "                    interaction=selector,\n",
    "                    fig_margin={'top': 50, 'bottom': 60,\n",
    "                                'left': 50, 'right':30})\n",
    "\n",
    "# Create dropown widgets\n",
    "dropdown_x = Dropdown(description='X axis',\n",
    "                      options=dataframe.columns,\n",
    "                      value=dataframe.columns[3])\n",
    "dropdown_y = Dropdown(description='Y axis',\n",
    "                      options=dataframe.columns,\n",
    "                      value=dataframe.columns[4])\n",
    "\n",
    "# Define callback function for dropdown widgets\n",
    "def update_plot(evt):\n",
    "    if evt is not None:\n",
    "        new_value = evt['new']\n",
    "        if evt['owner'] == dropdown_x:\n",
    "            mark_scatter.x = dataframe[new_value]\n",
    "            axis_x.label = new_value\n",
    "        elif evt['owner'] == dropdown_y:\n",
    "            mark_scatter.y = dataframe[new_value]\n",
    "            axis_y.label = new_value\n",
    "\n",
    "\n",
    "# Define callback function for selections\n",
    "def on_select(evt):\n",
    "    if evt is not None and evt['new'] is not None:\n",
    "        indices = evt['new']\n",
    "        datagrid.data = dataframe.iloc[indices].reset_index()\n",
    "\n",
    "# Bind callback to the dropdown widgets\n",
    "dropdown_x.observe(update_plot, names=['value'])\n",
    "dropdown_y.observe(update_plot, names=['value'])\n",
    "mark_scatter.observe(on_select, names=['selected'])\n",
    "\n",
    "# Create datagrid\n",
    "datagrid = bqw.DataGrid(data=dataframe, layout={'height': '300px'})\n",
    "\n",
    "# Create Box containers\n",
    "widget_box = HBox([dropdown_x, dropdown_y], layout={'margin': '10px'})\n",
    "app_container = VBox([figure, widget_box, datagrid],\n",
    "                     layout={'width':'100%'})\n",
    "\n",
    "# Display the visualization\n",
    "app_container\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (sandboxed)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}