data_engineering_tutorial.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Engineering\n",
    "This notebook steps through a Data Engineering exercise that joins several FHIR resources and converts them at scale into a timeseries-like data model. This notebook should be run after the getting_started notebook. \n",
    "\n",
    "First let's import some needed methods and create our Spark session."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql.functions import col, lit\n",
    "\n",
    "import matplotlib\n",
    "%matplotlib inline\n",
    "\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "from pyspark.sql import SparkSession\n",
    "\n",
    "# Create a Spark session that uses local execution, warehouse, and metadata,\n",
    "# with Hive support to save to tables.\n",
    "spark = SparkSession.builder \\\n",
    "                    .config('hive.exec.dynamic.partition.mode', 'nonstrict') \\\n",
    "                    .enableHiveSupport() \\\n",
    "                    .getOrCreate()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we will make sure the imported data exists:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "scrolled": true
   },
   "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>database</th>\n",
       "      <th>tableName</th>\n",
       "      <th>isTemporary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>tutorial_small</td>\n",
       "      <td>allergyintolerance</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>tutorial_small</td>\n",
       "      <td>careplan</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>tutorial_small</td>\n",
       "      <td>claim</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>tutorial_small</td>\n",
       "      <td>condition</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>tutorial_small</td>\n",
       "      <td>encounter</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>tutorial_small</td>\n",
       "      <td>immunization</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>tutorial_small</td>\n",
       "      <td>medication</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>tutorial_small</td>\n",
       "      <td>medicationrequest</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>tutorial_small</td>\n",
       "      <td>observation</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>tutorial_small</td>\n",
       "      <td>organization</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>tutorial_small</td>\n",
       "      <td>patient</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>tutorial_small</td>\n",
       "      <td>procedure</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          database           tableName  isTemporary\n",
       "0   tutorial_small  allergyintolerance        False\n",
       "1   tutorial_small            careplan        False\n",
       "2   tutorial_small               claim        False\n",
       "3   tutorial_small           condition        False\n",
       "4   tutorial_small           encounter        False\n",
       "5   tutorial_small        immunization        False\n",
       "6   tutorial_small          medication        False\n",
       "7   tutorial_small   medicationrequest        False\n",
       "8   tutorial_small         observation        False\n",
       "9   tutorial_small        organization        False\n",
       "10  tutorial_small             patient        False\n",
       "11  tutorial_small           procedure        False"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spark.sql('use tutorial_small')\n",
    "spark.sql('show tables').toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Valuesets\n",
    "Common code systems or valuesets, such as those [defined by the VSAC](https://vsac.nlm.nih.gov/valuesets) can be used in the cluster. Here we mostly use user-provided constants to keep the example simple."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "from bunsen.stu3.valuesets import push_valuesets, valueset\n",
    "\n",
    "# Typically these would the isa_loinc or isa_snomed functions, but\n",
    "# we didn't want the tutorial to require downloading those ontologies.\n",
    "push_valuesets(spark, \n",
    "               {'ldl'               : [('http://loinc.org', '18262-6')],                \n",
    "                'hdl'               : [('http://loinc.org', '2085-9')],\n",
    "                'triglycerides'     : [('http://loinc.org', '2571-8')],\n",
    "                'hba1c'             : [('http://loinc.org', '4548-4')], \n",
    "                'chd'               : [('http://snomed.info/sct', '53741008')],\n",
    "                'hypertension'      : [('http://snomed.info/sct', '38341003')],\n",
    "                'mi'                : [('http://snomed.info/sct', '22298006')], \n",
    "                # The following were loaded in the getting started notebook.\n",
    "                'diabetes_risks'    : valueset('http://engineering.cerner.com/bunsen/example', '201806001'),                \n",
    "                'example'           : valueset('http://hl7.org/fhir/ValueSet/example-extensional', '20150622')},\n",
    "               database='tutorial_ontologies');   "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Projected onto simple tables\n",
    "As seen in the Getting Started notebook, we can interactively query FHIR datasets and project them onto simple tables. Notice the use of the in_valueset UDF that references the valueset names seen above."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>reference</th>\n",
       "      <th>system</th>\n",
       "      <th>code</th>\n",
       "      <th>onsetDateTime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>urn:uuid:f88c719e-9875-446d-852b-cddb79fe4998</td>\n",
       "      <td>http://snomed.info/sct</td>\n",
       "      <td>53741008</td>\n",
       "      <td>2014-09-14T07:45:47-05:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>urn:uuid:d9ac6b14-7ffd-46cf-9d2f-b4b982b4f1dc</td>\n",
       "      <td>http://snomed.info/sct</td>\n",
       "      <td>53741008</td>\n",
       "      <td>2017-05-22T06:56:19-05:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>urn:uuid:7460ce37-9f41-4543-b906-7308e9932d21</td>\n",
       "      <td>http://snomed.info/sct</td>\n",
       "      <td>53741008</td>\n",
       "      <td>1974-08-06T06:50:32-05:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>urn:uuid:5a288b89-ba8d-48f4-b99a-ca6bdd67c2e6</td>\n",
       "      <td>http://snomed.info/sct</td>\n",
       "      <td>53741008</td>\n",
       "      <td>2015-08-28T01:17:20-05:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>urn:uuid:214ff775-6924-4ecf-aedd-9847146fe66b</td>\n",
       "      <td>http://snomed.info/sct</td>\n",
       "      <td>53741008</td>\n",
       "      <td>2013-01-31T00:59:02-06:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>urn:uuid:0dbb75c9-3524-401b-988a-1bb271958e83</td>\n",
       "      <td>http://snomed.info/sct</td>\n",
       "      <td>53741008</td>\n",
       "      <td>2003-01-22T21:39:48-06:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>urn:uuid:7f4dc74b-16f4-4a55-a1ad-8a42e050488c</td>\n",
       "      <td>http://snomed.info/sct</td>\n",
       "      <td>53741008</td>\n",
       "      <td>1997-10-01T23:25:01-05:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>urn:uuid:98a6cbbb-585f-447e-8a49-2ea746d8225e</td>\n",
       "      <td>http://snomed.info/sct</td>\n",
       "      <td>53741008</td>\n",
       "      <td>2008-10-08T11:30:48-05:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>urn:uuid:5c9ab509-de8a-4bdb-90f0-29244b4fb7a5</td>\n",
       "      <td>http://snomed.info/sct</td>\n",
       "      <td>53741008</td>\n",
       "      <td>1999-05-31T01:13:28-05:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>urn:uuid:00d3a53e-0b83-4769-b010-4e23f5db8987</td>\n",
       "      <td>http://snomed.info/sct</td>\n",
       "      <td>53741008</td>\n",
       "      <td>2009-03-28T12:13:51-05:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       reference                  system  \\\n",
       "0  urn:uuid:f88c719e-9875-446d-852b-cddb79fe4998  http://snomed.info/sct   \n",
       "1  urn:uuid:d9ac6b14-7ffd-46cf-9d2f-b4b982b4f1dc  http://snomed.info/sct   \n",
       "2  urn:uuid:7460ce37-9f41-4543-b906-7308e9932d21  http://snomed.info/sct   \n",
       "3  urn:uuid:5a288b89-ba8d-48f4-b99a-ca6bdd67c2e6  http://snomed.info/sct   \n",
       "4  urn:uuid:214ff775-6924-4ecf-aedd-9847146fe66b  http://snomed.info/sct   \n",
       "5  urn:uuid:0dbb75c9-3524-401b-988a-1bb271958e83  http://snomed.info/sct   \n",
       "6  urn:uuid:7f4dc74b-16f4-4a55-a1ad-8a42e050488c  http://snomed.info/sct   \n",
       "7  urn:uuid:98a6cbbb-585f-447e-8a49-2ea746d8225e  http://snomed.info/sct   \n",
       "8  urn:uuid:5c9ab509-de8a-4bdb-90f0-29244b4fb7a5  http://snomed.info/sct   \n",
       "9  urn:uuid:00d3a53e-0b83-4769-b010-4e23f5db8987  http://snomed.info/sct   \n",
       "\n",
       "       code              onsetDateTime  \n",
       "0  53741008  2014-09-14T07:45:47-05:00  \n",
       "1  53741008  2017-05-22T06:56:19-05:00  \n",
       "2  53741008  1974-08-06T06:50:32-05:00  \n",
       "3  53741008  2015-08-28T01:17:20-05:00  \n",
       "4  53741008  2013-01-31T00:59:02-06:00  \n",
       "5  53741008  2003-01-22T21:39:48-06:00  \n",
       "6  53741008  1997-10-01T23:25:01-05:00  \n",
       "7  53741008  2008-10-08T11:30:48-05:00  \n",
       "8  53741008  1999-05-31T01:13:28-05:00  \n",
       "9  53741008  2009-03-28T12:13:51-05:00  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spark.sql(\"\"\"\n",
    "select subject.reference,\n",
    "       code.coding[0].system system, \n",
    "       code.coding[0].code code,\n",
    "       onsetDateTime\n",
    "from condition\n",
    "where in_valueset(code, 'chd')\n",
    "\"\"\").limit(10).toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A similar example for the [FHIR Observation Model](https://www.hl7.org/fhir/observation.html). "
   ]
  },
  {
   "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>reference</th>\n",
       "      <th>code</th>\n",
       "      <th>value</th>\n",
       "      <th>effectiveDateTime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>urn:uuid:d9ac6b14-7ffd-46cf-9d2f-b4b982b4f1dc</td>\n",
       "      <td>18262-6</td>\n",
       "      <td>76.1167</td>\n",
       "      <td>2017-05-22T06:56:19-05:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>urn:uuid:5a288b89-ba8d-48f4-b99a-ca6bdd67c2e6</td>\n",
       "      <td>18262-6</td>\n",
       "      <td>146.9537</td>\n",
       "      <td>2017-09-01T01:17:20-05:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>urn:uuid:00d3a53e-0b83-4769-b010-4e23f5db8987</td>\n",
       "      <td>18262-6</td>\n",
       "      <td>70.7331</td>\n",
       "      <td>2017-04-08T12:13:51-05:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>urn:uuid:aa2be051-d228-419d-9a60-18b64e10ea81</td>\n",
       "      <td>18262-6</td>\n",
       "      <td>134.6488</td>\n",
       "      <td>2017-03-15T12:52:10-05:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>urn:uuid:f20b8e92-5088-4c29-bcb6-34a2953f404e</td>\n",
       "      <td>18262-6</td>\n",
       "      <td>76.0055</td>\n",
       "      <td>2017-08-10T22:44:24-05:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>urn:uuid:75d2e5a2-05b8-4192-8021-315683f46f57</td>\n",
       "      <td>18262-6</td>\n",
       "      <td>93.5933</td>\n",
       "      <td>2017-10-08T07:25:26-05:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>urn:uuid:6f58dbea-7532-4090-97a8-79982bab98f5</td>\n",
       "      <td>18262-6</td>\n",
       "      <td>94.8853</td>\n",
       "      <td>2017-05-28T14:27:57-05:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>urn:uuid:aa251e83-9a9b-446f-ba2f-87e2da7c4d34</td>\n",
       "      <td>18262-6</td>\n",
       "      <td>76.6896</td>\n",
       "      <td>2017-03-06T01:40:30-06:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>urn:uuid:e33851db-e468-44cc-85f5-2a322dac7717</td>\n",
       "      <td>18262-6</td>\n",
       "      <td>74.4160</td>\n",
       "      <td>2017-08-09T06:37:22-05:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>urn:uuid:a24074d9-8d2f-4a15-9e82-0a633bba7d39</td>\n",
       "      <td>18262-6</td>\n",
       "      <td>131.7946</td>\n",
       "      <td>2017-07-21T14:07:12-05:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       reference     code     value  \\\n",
       "0  urn:uuid:d9ac6b14-7ffd-46cf-9d2f-b4b982b4f1dc  18262-6   76.1167   \n",
       "1  urn:uuid:5a288b89-ba8d-48f4-b99a-ca6bdd67c2e6  18262-6  146.9537   \n",
       "2  urn:uuid:00d3a53e-0b83-4769-b010-4e23f5db8987  18262-6   70.7331   \n",
       "3  urn:uuid:aa2be051-d228-419d-9a60-18b64e10ea81  18262-6  134.6488   \n",
       "4  urn:uuid:f20b8e92-5088-4c29-bcb6-34a2953f404e  18262-6   76.0055   \n",
       "5  urn:uuid:75d2e5a2-05b8-4192-8021-315683f46f57  18262-6   93.5933   \n",
       "6  urn:uuid:6f58dbea-7532-4090-97a8-79982bab98f5  18262-6   94.8853   \n",
       "7  urn:uuid:aa251e83-9a9b-446f-ba2f-87e2da7c4d34  18262-6   76.6896   \n",
       "8  urn:uuid:e33851db-e468-44cc-85f5-2a322dac7717  18262-6   74.4160   \n",
       "9  urn:uuid:a24074d9-8d2f-4a15-9e82-0a633bba7d39  18262-6  131.7946   \n",
       "\n",
       "           effectiveDateTime  \n",
       "0  2017-05-22T06:56:19-05:00  \n",
       "1  2017-09-01T01:17:20-05:00  \n",
       "2  2017-04-08T12:13:51-05:00  \n",
       "3  2017-03-15T12:52:10-05:00  \n",
       "4  2017-08-10T22:44:24-05:00  \n",
       "5  2017-10-08T07:25:26-05:00  \n",
       "6  2017-05-28T14:27:57-05:00  \n",
       "7  2017-03-06T01:40:30-06:00  \n",
       "8  2017-08-09T06:37:22-05:00  \n",
       "9  2017-07-21T14:07:12-05:00  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ldl_values = spark.sql(\"\"\"\n",
    "select subject.reference, \n",
    "       code.coding[0].code code,\n",
    "       valueQuantity.value,\n",
    "       effectiveDateTime\n",
    "from observation \n",
    "where in_valueset(code, 'ldl') and year(effectiveDateTime) = 2017\n",
    "\"\"\")\n",
    "\n",
    "ldl_values.limit(10).toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data analysis\n",
    "Now that we have data in a tabular format, we can easily calculate summary statistics or plot its distribution. (Of course, in a real system the counts would be much higher, and the distribution much smoother.)"
   ]
  },
  {
   "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>summary</th>\n",
       "      <th>reference</th>\n",
       "      <th>code</th>\n",
       "      <th>value</th>\n",
       "      <th>effectiveDateTime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>count</td>\n",
       "      <td>62</td>\n",
       "      <td>62</td>\n",
       "      <td>62</td>\n",
       "      <td>62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>mean</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>111.52852903</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>stddev</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>30.612884121507545</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>min</td>\n",
       "      <td>urn:uuid:00d3a53e-0b83-4769-b010-4e23f5db8987</td>\n",
       "      <td>18262-6</td>\n",
       "      <td>57.6543</td>\n",
       "      <td>2017-01-11T07:33:23-06:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>max</td>\n",
       "      <td>urn:uuid:fd8e392e-661c-41de-aae9-b344fc2c1a9f</td>\n",
       "      <td>18262-6</td>\n",
       "      <td>182.9637</td>\n",
       "      <td>2017-12-27T17:29:37-06:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  summary                                      reference     code  \\\n",
       "0   count                                             62       62   \n",
       "1    mean                                           None     None   \n",
       "2  stddev                                           None     None   \n",
       "3     min  urn:uuid:00d3a53e-0b83-4769-b010-4e23f5db8987  18262-6   \n",
       "4     max  urn:uuid:fd8e392e-661c-41de-aae9-b344fc2c1a9f  18262-6   \n",
       "\n",
       "                value          effectiveDateTime  \n",
       "0                  62                         62  \n",
       "1        111.52852903                       None  \n",
       "2  30.612884121507545                       None  \n",
       "3             57.6543  2017-01-11T07:33:23-06:00  \n",
       "4            182.9637  2017-12-27T17:29:37-06:00  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ldl_values.describe().toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Text(0.5,0,'value')"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAtEAAAFACAYAAAB6GyB8AAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDIuMS4yLCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvNQv5yAAAFftJREFUeJzt3XuwZWdZJ+DfSxoIYYBApbmFNA0WxgkpkNBSICO3kDIShsgUaiicicjQIzrc5iIdYYSpGquioFyGGTVICGCGCBEQDQqBUaipkmBuQkKCoSAmTYIJg4JcJATe+ePsHk46fZL9ne691+nTz1N16qz17bX393Z/e53zO2t/a63q7gAAAPO7y9QFAADAwUaIBgCAQUI0AAAMEqIBAGCQEA0AAIOEaAAAGCREAwDAICEaAAAGCdEAADBoy9QFzOOoo47q7du3T10GAACb3CWXXPLl7t56Z9sdFCF6+/btufjii6cuAwCATa6q/nae7UznAACAQUI0AAAMEqIBAGCQEA0AAIOEaAAAGCREAwDAICEaAAAGCdEAADBoYSG6qs6uqpuq6opVba+tqqur6lNV9b6qOnJR/QMAwKIs8kj0OUlO3qvtwiTHd/ejkvxNkjMW2D8AACzEwkJ0d388yVf2avtwd986W/1Ekocsqn8AAFiULRP2/fNJ/mCtB6tqZ5KdSbJt27Zl1QSwaWzfdcHUJSzdtWeeMnUJwCFikhMLq+qVSW5Ncu5a23T3Wd29o7t3bN26dXnFAQDAnVj6keiqOj3JM5Oc2N297P4BAGB/LTVEV9XJSV6R5Mnd/c1l9g0AAAfKIi9x964kf5nk2KraXVUvSPLmJPdKcmFVXV5Vv7Oo/gEAYFEWdiS6u5+7j+a3Lqo/AABYFncsBACAQUI0AAAMEqIBAGCQEA0AAIOEaAAAGCREAwDAICEaAAAGCdEAADBIiAYAgEFCNAAADBKiAQBgkBANAACDhGgAABgkRAMAwCAhGgAABgnRAAAwSIgGAIBBQjQAAAwSogEAYJAQDQAAg4RoAAAYJEQDAMAgIRoAAAYJ0QAAMEiIBgCAQUI0AAAMEqIBAGCQEA0AAIOEaAAAGCREAwDAICEaAAAGCdEAADBIiAYAgEFCNAAADFpYiK6qs6vqpqq6YlXb/arqwqq6Zvb9vovqHwAAFmWRR6LPSXLyXm27kny0ux+R5KOzdQAAOKgsLER398eTfGWv5lOTvH22/PYkP7mo/gEAYFG2LLm/B3T3jUnS3TdW1f3X2rCqdibZmSTbtm1bUnls33XB1CUs3bVnnjJ1CcABcqj9DPPzC6azYU8s7O6zuntHd+/YunXr1OUAAMD/t+wQ/XdV9aAkmX2/acn9AwDAflt2iP5AktNny6cn+aMl9w8AAPttkZe4e1eSv0xybFXtrqoXJDkzyUlVdU2Sk2brAABwUFnYiYXd/dw1HjpxUX0CAMAybNgTCwEAYKMSogEAYJAQDQAAg4RoAAAYJEQDAMAgIRoAAAYJ0QAAMEiIBgCAQUI0AAAMEqIBAGCQEA0AAIOEaAAAGCREAwDAICEaAAAGCdEAADBIiAYAgEFbpi4AWL7tuy6YuoSlu/bMU6YuAYBNxJFoAAAYJEQDAMAgIRoAAAYJ0QAAMEiIBgCAQUI0AAAMEqIBAGCQEA0AAIOEaAAAGCREAwDAICEaAAAGCdEAADBIiAYAgEFCNAAADBKiAQBgkBANAACDhGgAABg0SYiuqpdX1ZVVdUVVvauqDp+iDgAAWI+lh+iqOjrJS5Ls6O7jkxyW5LRl1wEAAOs11XSOLUnuUVVbkhyR5IaJ6gAAgGFLD9Hd/cUkr0tyXZIbk3y1uz+893ZVtbOqLq6qi2+++eZllwkAAGuaYjrHfZOcmuRhSR6c5J5V9bN7b9fdZ3X3ju7esXXr1mWXCQAAa5piOsfTk3yhu2/u7u8keW+SH52gDgAAWJcpQvR1SR5fVUdUVSU5MclVE9QBAADrMsWc6IuSnJ/k0iSfntVw1rLrAACA9doyRafd/eokr56ibwAA2F/uWAgAAIOEaAAAGCREAwDAICEaAAAGCdEAADBIiAYAgEFCNAAADJorRFfV8YsuBAAADhbzHon+nar6ZFX9YlUdudCKAABgg5srRHf3v0jyvCTHJLm4qv5XVZ200MoAAGCDmntOdHdfk+RVSV6R5MlJ3lRVV1fVv1pUcQAAsBHNOyf6UVX1+iRXJXlakn/Z3f98tvz6BdYHAAAbzpY5t3tzkrck+ZXu/taexu6+oapetZDKAABgg5o3RD8jybe6+7tJUlV3SXJ4d3+zu9+5sOoAAGADmndO9EeS3GPV+hGzNgAAOOTMG6IP7+6v71mZLR+xmJIAAGBjmzdEf6OqTtizUlWPTfKtO9geAAA2rXnnRL8syXuq6obZ+oOS/MxiSgIAgI1trhDd3X9VVT+U5NgkleTq7v7OQisDAIANat4j0UnyI0m2z57zmKpKd79jIVUBAMAGNleIrqp3JvmBJJcn+e6suZMI0QAAHHLmPRK9I8lx3d2LLAYAAA4G816d44okD1xkIQAAcLCY90j0UUk+U1WfTPLtPY3d/ayFVAUAABvYvCH6NYssAgAADibzXuLuY1X10CSP6O6PVNURSQ5bbGkAALAxzTUnuqpemOT8JL87azo6yfsXVRQAAGxk855Y+EtJnpjka0nS3dckuf+iigIAgI1s3hD97e6+Zc9KVW3JynWiAQDgkDNviP5YVf1KkntU1UlJ3pPkjxdXFgAAbFzzhuhdSW5O8ukk/y7JB5O8alFFAQDARjbv1Tm+l+Qtsy8AADikzRWiq+oL2ccc6O5++AGvCAAANrh5b7ayY9Xy4Ul+Ksn9Dnw5AACw8c01J7q7/++qry929xuSPG29nVbVkVV1flVdXVVXVdUT1vtaAACwbPNO5zhh1epdsnJk+l770e8bk/xZdz+nqu6W5Ij9eC0AAFiqeadz/Oaq5VuTXJvkp9fTYVXdO8mTkvxcksyuP33LHT0HAAA2knmvzvHUA9jnw7Nyuby3VdWjk1yS5KXd/Y3VG1XVziQ7k2Tbtm0HsHu4re27Lpi6BADgIDPvdI7/cEePd/dvDfZ5QpIXd/dFVfXGrFyH+r/s9ZpnJTkrSXbs2OHuiAAAbBjz3mxlR5IXJTl69vULSY7Lyrzo0bnRu5Ps7u6LZuvnZyVUAwDAQWHeOdFHJTmhu/8xSarqNUne093/drTD7v5SVV1fVcd292eTnJjkM6OvAwAAU5k3RG/LbU/+uyXJ9v3o98VJzp1dmePzSZ6/H68FAABLNW+IfmeST1bV+7Jy58JnJ3nHejvt7stz2xu4AADAQWPeq3P8WlX9aZIfmzU9v7svW1xZAACwcc17YmGyckOUr3X3G5PsrqqHLagmAADY0OYK0VX16iSvSHLGrOmuSX5/UUUBAMBGNu+R6GcneVaSbyRJd9+Q/bvtNwAAHLTmDdG3dHdn5aTCVNU9F1cSAABsbPOG6HdX1e8mObKqXpjkI0nesriyAABg45r36hyvq6qTknwtybFJfrW7L1xoZQAAsEHdaYiuqsOSfKi7n55EcAYA4JB3p9M5uvu7Sb5ZVfdZQj0AALDhzXvHwn9K8umqujCzK3QkSXe/ZCFVAQDABjZviL5g9gUAAIe8OwzRVbWtu6/r7rcvqyAAANjo7mxO9Pv3LFTVHy64FgAAOCjcWYiuVcsPX2QhAABwsLizEN1rLAMAwCHrzk4sfHRVfS0rR6TvMVvObL27+94LrQ4AADagOwzR3X3YsgrZiLbvckESAABu705vtgIAANyWEA0AAIOEaAAAGCREAwDAICEaAAAGCdEAADBIiAYAgEFCNAAADBKiAQBgkBANAACDhGgAABgkRAMAwCAhGgAABgnRAAAwSIgGAIBBQjQAAAwSogEAYNBkIbqqDquqy6rqT6aqAQAA1mPKI9EvTXLVhP0DAMC6TBKiq+ohSU5J8ntT9A8AAPtjqiPRb0jyy0m+N1H/AACwbluW3WFVPTPJTd19SVU95Q6225lkZ5Js27ZtSdUBwMFj+64Lpi5h6a4985SpS4Ak0xyJfmKSZ1XVtUnOS/K0qvr9vTfq7rO6e0d379i6deuyawQAgDUtPUR39xnd/ZDu3p7ktCT/u7t/dtl1AADAerlONAAADFr6nOjVuvsvkvzFlDUAAMAoR6IBAGCQEA0AAIOEaAAAGCREAwDAICEaAAAGCdEAADBIiAYAgEFCNAAADBKiAQBgkBANAACDhGgAABgkRAMAwCAhGgAABgnRAAAwSIgGAIBBQjQAAAzaMnUBAMuwfdcFU5cAwCbiSDQAAAwSogEAYJAQDQAAg4RoAAAYJEQDAMAgIRoAAAYJ0QAAMEiIBgCAQUI0AAAMEqIBAGCQEA0AAIOEaAAAGCREAwDAICEaAAAGCdEAADBIiAYAgEFCNAAADFp6iK6qY6rqz6vqqqq6sqpeuuwaAABgf2yZoM9bk/zH7r60qu6V5JKqurC7PzNBLQAAMGzpR6K7+8buvnS2/I9Jrkpy9LLrAACA9Zp0TnRVbU/ymCQXTVkHAACMmGI6R5Kkqv5Zkj9M8rLu/to+Ht+ZZGeSbNu2bcnVAQAb0fZdF0xdwtJde+YpU5fAPkxyJLqq7pqVAH1ud793X9t091ndvaO7d2zdunW5BQIAwB2Y4uocleStSa7q7t9adv8AALC/pjgS/cQk/zrJ06rq8tnXMyaoAwAA1mXpc6K7+/8kqWX3CwAAB4o7FgIAwCAhGgAABgnRAAAwSIgGAIBBQjQAAAwSogEAYJAQDQAAg4RoAAAYJEQDAMAgIRoAAAYJ0QAAMEiIBgCAQUI0AAAMEqIBAGCQEA0AAIOEaAAAGLRl6gIAAFjb9l0XTF3C0l175ilTl3CnHIkGAIBBQjQAAAwSogEAYJAQDQAAg4RoAAAYJEQDAMAgIRoAAAYJ0QAAMEiIBgCAQUI0AAAMEqIBAGCQEA0AAIOEaAAAGCREAwDAICEaAAAGCdEAADBIiAYAgEGThOiqOrmqPltVn6uqXVPUAAAA67X0EF1VhyX5H0l+IslxSZ5bVcctuw4AAFivKY5EPy7J57r78919S5Lzkpw6QR0AALAuU4Too5Ncv2p996wNAAAOClsm6LP20da326hqZ5Kds9WvV9VnF1rVgXFUki9PXcQhzhhMzxhMzxhMzxhsDMZheusag/r1BVQyv4fOs9EUIXp3kmNWrT8kyQ17b9TdZyU5a1lFHQhVdXF375i6jkOZMZieMZieMZieMdgYjMP0NvMYTDGd46+SPKKqHlZVd0tyWpIPTFAHAACsy9KPRHf3rVX175N8KMlhSc7u7iuXXQcAAKzXFNM50t0fTPLBKfpesINq+skmZQymZwymZwymZww2BuMwvU07BtV9u3P6AACAO+C23wAAMEiIBgCAQUL0OlXVkVV1flVdXVVXVdUTqup+VXVhVV0z+37fqevczKrq5VV1ZVVdUVXvqqrDZ1d9uWg2Bn8wuwIMB1BVnV1VN1XVFava9vnerxVvqqrPVdWnquqE6SrfPNYYg9fOfh59qqreV1VHrnrsjNkYfLaqfnyaqjeXfY3Bqsf+U1V1VR01W7cfLMBaY1BVL56916+sqt9Y1W4/OMDW+Fn0w1X1iaq6vKourqrHzdo33X4gRK/fG5P8WXf/UJJHJ7kqya4kH+3uRyT56GydBaiqo5O8JMmO7j4+K1d6OS3Jryd5/WwM/j7JC6arctM6J8nJe7Wt9d7/iSSPmH3tTPLbS6pxszsntx+DC5Mc392PSvI3Sc5Ikqo6Liv7xiNnz/mfVXXY8krdtM7J7ccgVXVMkpOSXLeq2X6wGOdkrzGoqqcmOTXJo7r7kUleN2u3HyzGObn9fvAbSf5rd/9wkl+drSebcD8Qotehqu6d5ElJ3pok3X1Ld/9DVnbct882e3uSn5ymwkPGliT3qKotSY5IcmOSpyU5f/a4MViA7v54kq/s1bzWe//UJO/oFZ9IcmRVPWg5lW5e+xqD7v5wd986W/1EVm5klayMwXnd/e3u/kKSzyV53NKK3aTW2A+S5PVJfjm3vROv/WAB1hiDFyU5s7u/Pdvmplm7/WAB1hiDTnLv2fJ98v0b6m26/UCIXp+HJ7k5yduq6rKq+r2qumeSB3T3jUky+37/KYvczLr7i1k5wnBdVsLzV5NckuQfVgWJ3UmOnqbCQ85a7/2jk1y/ajtjshw/n+RPZ8vGYEmq6llJvtjdf73XQ8ZgeX4wyY/NpvV9rKp+ZNZuDJbnZUleW1XXZ+X39Bmz9k03BkL0+mxJckKS3+7uxyT5RkzdWKrZnNtTkzwsyYOT3DMrHxXtzTUcp1X7aDMmC1RVr0xya5Jz9zTtYzNjcIBV1RFJXpmVj69v9/A+2ozBYmxJct8kj0/yn5O8u6oqxmCZXpTk5d19TJKXZ/apfTbhGAjR67M7ye7uvmi2fn5WQvXf7floYvb9pjWez/57epIvdPfN3f2dJO9N8qNZ+Xhoz02EHpLvf4zEYq313t+d5JhV2xmTBaqq05M8M8nz+vs3ATAGy/EDWfmj/q+r6tqs/D9fWlUPjDFYpt1J3jubMvDJJN9LclSMwTKdnpXfyUnynnx/2symGwMheh26+0tJrq+qY2dNJyb5TJIPZOXNk9n3P5qgvEPFdUkeX1VHzI4y7BmDP0/ynNk2xmB51nrvfyDJv5mdlf34JF/dM+2DA6uqTk7yiiTP6u5vrnroA0lOq6q7V9XDsnJSzyenqHEz6+5Pd/f9u3t7d2/PSmA4Yfb7wn6wPO/PyrkxqaofTHK3JF+O/WCZbkjy5Nny05JcM1vedPvBJLf93iRenOTcWrmE2ueTPD8rf5S8u6pekJWQ91MT1repdfdFVXV+kkuz8tH1ZVm5tegFSc6rqv82a3vr2q/CelTVu5I8JclRVbU7yauTnJl9v/c/mOQZWTmJ55tZ2U/YT2uMwRlJ7p7kwpW/K/OJ7v6F7r6yqt6dlT8yb03yS9393Wkq3zz2NQbdvdbPG/vBAqyxH5yd5OzZJdduSXL67FMZ+8ECrDEGL0zyxtmnwv+UlStxJJtwP3DbbwAAGGQ6BwAADBKiAQBgkBANAACDhGgAABgkRAMAwCAhGmCTqaqvT10DwGYnRAMAwCAhGmCDq6pfr6pfXLX+mqp6dVV9tKourapPV9Wp+3jeU6rqT1atv7mqfm62/Niq+lhVXVJVH9pz23YA5iNEA2x85yX5mVXrP53kbUme3d0nJHlqkt+s2a0K70xV3TXJf0/ynO5+bFbu8vZrB7ZkgM3Nbb8BNrjuvqyq7l9VD06yNcnfJ7kxyeur6klJvpfk6CQPSPKlOV7y2CTH5/u3CD9s9noAzEmIBjg4nJ/kOUkemJUj08/LSqB+bHd/p6quTXL4Xs+5Nbf9xHHP45Xkyu5+wkIrBtjETOcAODicl+S0rATp85PcJ8lNswD91CQP3cdz/jbJcVV196q6T5ITZ+2fTbK1qp6QrEzvqKpHLvxfALCJOBINcBDo7iur6l5JvtjdN1bVuUn+uKouTnJ5kqv38Zzrq+rdST6V5Jokl83ab6mq5yR50yxcb0nyhiRXLumfA3DQq+6eugYAADiomM4BAACDhGgAABgkRAMAwCAhGgAABgnRAAAwSIgGAIBBQjQAAAz6f0xn9+kf3Fa8AAAAAElFTkSuQmCC\n",
      "text/plain": [
       "<matplotlib.figure.Figure at 0x7f9aea16fcf8>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "ldl_values.select(col('value').cast('float')) \\\n",
    "          .toPandas() \\\n",
    "          .plot(kind='hist', \n",
    "                bins=10, \n",
    "                figsize=(12,5), \n",
    "                legend=False) \\\n",
    "          .set_xlabel(\"value\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Building Cohorts\n",
    "Let's track down a group of people we want to analyze. In this case, we're interested in people:\n",
    "\n",
    "* With at least one of the following:\n",
    " * A diabetes condition\n",
    " * A pre-diabetes condition\n",
    " * An elevated HbA1c value \n",
    "* And who haven't had a wellnes visit in some time\n",
    "\n",
    "We can achieve this by simple creating dataframes with the desired subset of people. In this example, we have \"diabetes_risks\" valueset that contains diabetes-related conditions, so we just query it:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>condition_id</th>\n",
       "      <th>person_ref</th>\n",
       "      <th>system</th>\n",
       "      <th>code</th>\n",
       "      <th>display</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>urn:uuid:9c72cc16-2191-461f-a734-43b2c3e146d3</td>\n",
       "      <td>urn:uuid:5a288b89-ba8d-48f4-b99a-ca6bdd67c2e6</td>\n",
       "      <td>http://snomed.info/sct</td>\n",
       "      <td>44054006</td>\n",
       "      <td>Diabetes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>urn:uuid:56d5d265-3352-40df-8ad4-ed2ef8c5f348</td>\n",
       "      <td>urn:uuid:214ff775-6924-4ecf-aedd-9847146fe66b</td>\n",
       "      <td>http://snomed.info/sct</td>\n",
       "      <td>15777000</td>\n",
       "      <td>Prediabetes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>urn:uuid:69de8a40-c511-4635-9c8c-8e5f4377c8cf</td>\n",
       "      <td>urn:uuid:7f4dc74b-16f4-4a55-a1ad-8a42e050488c</td>\n",
       "      <td>http://snomed.info/sct</td>\n",
       "      <td>15777000</td>\n",
       "      <td>Prediabetes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>urn:uuid:e919cd87-9bc9-4ea9-ab97-342ab94730da</td>\n",
       "      <td>urn:uuid:3769d894-6c38-4d2f-a0a6-f0ff5e098a58</td>\n",
       "      <td>http://snomed.info/sct</td>\n",
       "      <td>44054006</td>\n",
       "      <td>Diabetes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>urn:uuid:a13c7861-750b-447b-9e06-e7cf009f7a85</td>\n",
       "      <td>urn:uuid:2d5049e5-39e5-4209-8f67-90d512d495c7</td>\n",
       "      <td>http://snomed.info/sct</td>\n",
       "      <td>15777000</td>\n",
       "      <td>Prediabetes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>urn:uuid:8c652cb2-2262-4215-ae7d-f80b6916a229</td>\n",
       "      <td>urn:uuid:75d2e5a2-05b8-4192-8021-315683f46f57</td>\n",
       "      <td>http://snomed.info/sct</td>\n",
       "      <td>15777000</td>\n",
       "      <td>Prediabetes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>urn:uuid:42eb38f9-1c13-4688-baf8-5123f741a697</td>\n",
       "      <td>urn:uuid:6f58dbea-7532-4090-97a8-79982bab98f5</td>\n",
       "      <td>http://snomed.info/sct</td>\n",
       "      <td>15777000</td>\n",
       "      <td>Prediabetes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>urn:uuid:6df0d5b9-b330-4653-a67c-4da2e3713643</td>\n",
       "      <td>urn:uuid:aa251e83-9a9b-446f-ba2f-87e2da7c4d34</td>\n",
       "      <td>http://snomed.info/sct</td>\n",
       "      <td>15777000</td>\n",
       "      <td>Prediabetes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>urn:uuid:c0574786-0be8-4a19-b986-36bc880094d8</td>\n",
       "      <td>urn:uuid:bb98a0cc-7990-4845-977d-8c17e1811bb1</td>\n",
       "      <td>http://snomed.info/sct</td>\n",
       "      <td>15777000</td>\n",
       "      <td>Prediabetes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>urn:uuid:5add6b55-e4c0-4f11-92ee-cab7fc3e0099</td>\n",
       "      <td>urn:uuid:1f07abb1-76ec-4af4-9bf5-92b595434384</td>\n",
       "      <td>http://snomed.info/sct</td>\n",
       "      <td>15777000</td>\n",
       "      <td>Prediabetes</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    condition_id  \\\n",
       "0  urn:uuid:9c72cc16-2191-461f-a734-43b2c3e146d3   \n",
       "1  urn:uuid:56d5d265-3352-40df-8ad4-ed2ef8c5f348   \n",
       "2  urn:uuid:69de8a40-c511-4635-9c8c-8e5f4377c8cf   \n",
       "3  urn:uuid:e919cd87-9bc9-4ea9-ab97-342ab94730da   \n",
       "4  urn:uuid:a13c7861-750b-447b-9e06-e7cf009f7a85   \n",
       "5  urn:uuid:8c652cb2-2262-4215-ae7d-f80b6916a229   \n",
       "6  urn:uuid:42eb38f9-1c13-4688-baf8-5123f741a697   \n",
       "7  urn:uuid:6df0d5b9-b330-4653-a67c-4da2e3713643   \n",
       "8  urn:uuid:c0574786-0be8-4a19-b986-36bc880094d8   \n",
       "9  urn:uuid:5add6b55-e4c0-4f11-92ee-cab7fc3e0099   \n",
       "\n",
       "                                      person_ref                  system  \\\n",
       "0  urn:uuid:5a288b89-ba8d-48f4-b99a-ca6bdd67c2e6  http://snomed.info/sct   \n",
       "1  urn:uuid:214ff775-6924-4ecf-aedd-9847146fe66b  http://snomed.info/sct   \n",
       "2  urn:uuid:7f4dc74b-16f4-4a55-a1ad-8a42e050488c  http://snomed.info/sct   \n",
       "3  urn:uuid:3769d894-6c38-4d2f-a0a6-f0ff5e098a58  http://snomed.info/sct   \n",
       "4  urn:uuid:2d5049e5-39e5-4209-8f67-90d512d495c7  http://snomed.info/sct   \n",
       "5  urn:uuid:75d2e5a2-05b8-4192-8021-315683f46f57  http://snomed.info/sct   \n",
       "6  urn:uuid:6f58dbea-7532-4090-97a8-79982bab98f5  http://snomed.info/sct   \n",
       "7  urn:uuid:aa251e83-9a9b-446f-ba2f-87e2da7c4d34  http://snomed.info/sct   \n",
       "8  urn:uuid:bb98a0cc-7990-4845-977d-8c17e1811bb1  http://snomed.info/sct   \n",
       "9  urn:uuid:1f07abb1-76ec-4af4-9bf5-92b595434384  http://snomed.info/sct   \n",
       "\n",
       "       code      display  \n",
       "0  44054006     Diabetes  \n",
       "1  15777000  Prediabetes  \n",
       "2  15777000  Prediabetes  \n",
       "3  44054006     Diabetes  \n",
       "4  15777000  Prediabetes  \n",
       "5  15777000  Prediabetes  \n",
       "6  15777000  Prediabetes  \n",
       "7  15777000  Prediabetes  \n",
       "8  15777000  Prediabetes  \n",
       "9  15777000  Prediabetes  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "diabetes_conditions = spark.sql(\"\"\"\n",
    "select id condition_id, \n",
    "       subject.reference person_ref, \n",
    "       coding.system,\n",
    "       coding.code,\n",
    "       coding.display\n",
    "from condition \n",
    "     lateral view explode(code.coding) nested as coding\n",
    "where in_valueset(code, 'diabetes_risks')\n",
    "\"\"\")\n",
    "\n",
    "diabetes_conditions.limit(10).toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that we can add further qualifications or explore the entire dataset interactively to make sure our query is producing the expected results.\n",
    "\n",
    "Now let's find people with a high hba1c:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>observation_id</th>\n",
       "      <th>person_ref</th>\n",
       "      <th>value</th>\n",
       "      <th>unit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>urn:uuid:9af19207-e1b3-4ecc-adb7-b79175ba3066</td>\n",
       "      <td>urn:uuid:5a288b89-ba8d-48f4-b99a-ca6bdd67c2e6</td>\n",
       "      <td>10.5958</td>\n",
       "      <td>%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>urn:uuid:2ef55eab-33ff-434c-b9c3-189af43d41ac</td>\n",
       "      <td>urn:uuid:5a288b89-ba8d-48f4-b99a-ca6bdd67c2e6</td>\n",
       "      <td>8.5958</td>\n",
       "      <td>%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>urn:uuid:22a1202d-5f41-49f9-b8cc-3db27e128eaa</td>\n",
       "      <td>urn:uuid:5a288b89-ba8d-48f4-b99a-ca6bdd67c2e6</td>\n",
       "      <td>8.5958</td>\n",
       "      <td>%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>urn:uuid:5c1fcf57-bb3f-48f8-84be-5c8245810f4d</td>\n",
       "      <td>urn:uuid:5a288b89-ba8d-48f4-b99a-ca6bdd67c2e6</td>\n",
       "      <td>9.4222</td>\n",
       "      <td>%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>urn:uuid:a1089c2d-ecf8-4a25-b471-d418e6c57fb3</td>\n",
       "      <td>urn:uuid:5a288b89-ba8d-48f4-b99a-ca6bdd67c2e6</td>\n",
       "      <td>9.4222</td>\n",
       "      <td>%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>urn:uuid:6906acaa-f312-4c13-b24d-e4f0f9bdbc63</td>\n",
       "      <td>urn:uuid:5a288b89-ba8d-48f4-b99a-ca6bdd67c2e6</td>\n",
       "      <td>9.1880</td>\n",
       "      <td>%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>urn:uuid:795048d1-d0b7-4c01-b59c-3d66ea441967</td>\n",
       "      <td>urn:uuid:5a288b89-ba8d-48f4-b99a-ca6bdd67c2e6</td>\n",
       "      <td>9.1880</td>\n",
       "      <td>%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>urn:uuid:864bcc8b-df09-4dda-9220-a2725cbe4eba</td>\n",
       "      <td>urn:uuid:5a288b89-ba8d-48f4-b99a-ca6bdd67c2e6</td>\n",
       "      <td>9.4590</td>\n",
       "      <td>%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>urn:uuid:fc51eb62-51b6-4035-a9e7-c930081aaf80</td>\n",
       "      <td>urn:uuid:5a288b89-ba8d-48f4-b99a-ca6bdd67c2e6</td>\n",
       "      <td>9.4590</td>\n",
       "      <td>%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>urn:uuid:62e7616c-0f18-4b55-bac9-aae5d2e4aab3</td>\n",
       "      <td>urn:uuid:aa2be051-d228-419d-9a60-18b64e10ea81</td>\n",
       "      <td>7.9565</td>\n",
       "      <td>%</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  observation_id  \\\n",
       "0  urn:uuid:9af19207-e1b3-4ecc-adb7-b79175ba3066   \n",
       "1  urn:uuid:2ef55eab-33ff-434c-b9c3-189af43d41ac   \n",
       "2  urn:uuid:22a1202d-5f41-49f9-b8cc-3db27e128eaa   \n",
       "3  urn:uuid:5c1fcf57-bb3f-48f8-84be-5c8245810f4d   \n",
       "4  urn:uuid:a1089c2d-ecf8-4a25-b471-d418e6c57fb3   \n",
       "5  urn:uuid:6906acaa-f312-4c13-b24d-e4f0f9bdbc63   \n",
       "6  urn:uuid:795048d1-d0b7-4c01-b59c-3d66ea441967   \n",
       "7  urn:uuid:864bcc8b-df09-4dda-9220-a2725cbe4eba   \n",
       "8  urn:uuid:fc51eb62-51b6-4035-a9e7-c930081aaf80   \n",
       "9  urn:uuid:62e7616c-0f18-4b55-bac9-aae5d2e4aab3   \n",
       "\n",
       "                                      person_ref    value unit  \n",
       "0  urn:uuid:5a288b89-ba8d-48f4-b99a-ca6bdd67c2e6  10.5958    %  \n",
       "1  urn:uuid:5a288b89-ba8d-48f4-b99a-ca6bdd67c2e6   8.5958    %  \n",
       "2  urn:uuid:5a288b89-ba8d-48f4-b99a-ca6bdd67c2e6   8.5958    %  \n",
       "3  urn:uuid:5a288b89-ba8d-48f4-b99a-ca6bdd67c2e6   9.4222    %  \n",
       "4  urn:uuid:5a288b89-ba8d-48f4-b99a-ca6bdd67c2e6   9.4222    %  \n",
       "5  urn:uuid:5a288b89-ba8d-48f4-b99a-ca6bdd67c2e6   9.1880    %  \n",
       "6  urn:uuid:5a288b89-ba8d-48f4-b99a-ca6bdd67c2e6   9.1880    %  \n",
       "7  urn:uuid:5a288b89-ba8d-48f4-b99a-ca6bdd67c2e6   9.4590    %  \n",
       "8  urn:uuid:5a288b89-ba8d-48f4-b99a-ca6bdd67c2e6   9.4590    %  \n",
       "9  urn:uuid:aa2be051-d228-419d-9a60-18b64e10ea81   7.9565    %  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "high_hba1c = spark.sql(\"\"\"\n",
    "select id observation_id,\n",
    "       subject.reference person_ref,\n",
    "       valueQuantity.value,\n",
    "       valueQuantity.unit\n",
    "from observation \n",
    "     lateral view explode(code.coding) nested as coding\n",
    "where in_valueset(code, 'hba1c') and\n",
    "      valueQuantity.value >= 6.5 and\n",
    "      status = 'final'\n",
    "\"\"\")\n",
    "\n",
    "high_hba1c.limit(10).toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can just select the person IDs from both of the above dataframes and union them to get our complete list of people who are at risk:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>person_ref</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>urn:uuid:1d6fb98a-1716-4313-a3f9-273b5f89fea3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>urn:uuid:72d4853d-0c27-4f5c-8274-4e301e593b31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>urn:uuid:7f4dc74b-16f4-4a55-a1ad-8a42e050488c</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>urn:uuid:17c10896-7229-45af-ba70-6975eb174a43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>urn:uuid:f79409d8-aefa-4425-a41b-f30057147f37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>urn:uuid:bb98a0cc-7990-4845-977d-8c17e1811bb1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>urn:uuid:209de376-91bd-4864-878b-183d54f994d1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>urn:uuid:5dbece6f-58b5-482f-b62c-7a18ff0749cd</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>urn:uuid:214ff775-6924-4ecf-aedd-9847146fe66b</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>urn:uuid:aa251e83-9a9b-446f-ba2f-87e2da7c4d34</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      person_ref\n",
       "0  urn:uuid:1d6fb98a-1716-4313-a3f9-273b5f89fea3\n",
       "1  urn:uuid:72d4853d-0c27-4f5c-8274-4e301e593b31\n",
       "2  urn:uuid:7f4dc74b-16f4-4a55-a1ad-8a42e050488c\n",
       "3  urn:uuid:17c10896-7229-45af-ba70-6975eb174a43\n",
       "4  urn:uuid:f79409d8-aefa-4425-a41b-f30057147f37\n",
       "5  urn:uuid:bb98a0cc-7990-4845-977d-8c17e1811bb1\n",
       "6  urn:uuid:209de376-91bd-4864-878b-183d54f994d1\n",
       "7  urn:uuid:5dbece6f-58b5-482f-b62c-7a18ff0749cd\n",
       "8  urn:uuid:214ff775-6924-4ecf-aedd-9847146fe66b\n",
       "9  urn:uuid:aa251e83-9a9b-446f-ba2f-87e2da7c4d34"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "diabetes_risk = high_hba1c.select('person_ref') \\\n",
    "                          .union(diabetes_conditions.select('person_ref')) \\\n",
    "                          .distinct()\n",
    "        \n",
    "diabetes_risk.limit(10).toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's find all of the wellness visits in the last couple years, which we use to exclude people from our cohort:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>person_ref</th>\n",
       "      <th>encounter_start</th>\n",
       "      <th>encounter_start</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>urn:uuid:f88c719e-9875-446d-852b-cddb79fe4998</td>\n",
       "      <td>2016-08-21T07:45:47-05:00</td>\n",
       "      <td>2016-08-21T07:45:47-05:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>urn:uuid:f88c719e-9875-446d-852b-cddb79fe4998</td>\n",
       "      <td>2017-08-27T07:45:47-05:00</td>\n",
       "      <td>2017-08-27T07:45:47-05:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>urn:uuid:d9ac6b14-7ffd-46cf-9d2f-b4b982b4f1dc</td>\n",
       "      <td>2016-05-16T06:56:19-05:00</td>\n",
       "      <td>2016-05-16T06:56:19-05:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>urn:uuid:d9ac6b14-7ffd-46cf-9d2f-b4b982b4f1dc</td>\n",
       "      <td>2017-05-22T06:56:19-05:00</td>\n",
       "      <td>2017-05-22T06:56:19-05:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>urn:uuid:5a288b89-ba8d-48f4-b99a-ca6bdd67c2e6</td>\n",
       "      <td>2017-09-01T01:17:20-05:00</td>\n",
       "      <td>2017-09-01T01:17:20-05:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>urn:uuid:214ff775-6924-4ecf-aedd-9847146fe66b</td>\n",
       "      <td>2016-02-04T00:59:02-06:00</td>\n",
       "      <td>2016-02-04T00:59:02-06:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>urn:uuid:00d3a53e-0b83-4769-b010-4e23f5db8987</td>\n",
       "      <td>2016-04-02T12:13:51-05:00</td>\n",
       "      <td>2016-04-02T12:13:51-05:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>urn:uuid:00d3a53e-0b83-4769-b010-4e23f5db8987</td>\n",
       "      <td>2017-04-08T12:13:51-05:00</td>\n",
       "      <td>2017-04-08T12:13:51-05:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>urn:uuid:00d3a53e-0b83-4769-b010-4e23f5db8987</td>\n",
       "      <td>2018-04-14T12:13:51-05:00</td>\n",
       "      <td>2018-04-14T12:13:51-05:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>urn:uuid:aa2be051-d228-419d-9a60-18b64e10ea81</td>\n",
       "      <td>2016-03-09T11:52:10-06:00</td>\n",
       "      <td>2016-03-09T11:52:10-06:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      person_ref            encounter_start  \\\n",
       "0  urn:uuid:f88c719e-9875-446d-852b-cddb79fe4998  2016-08-21T07:45:47-05:00   \n",
       "1  urn:uuid:f88c719e-9875-446d-852b-cddb79fe4998  2017-08-27T07:45:47-05:00   \n",
       "2  urn:uuid:d9ac6b14-7ffd-46cf-9d2f-b4b982b4f1dc  2016-05-16T06:56:19-05:00   \n",
       "3  urn:uuid:d9ac6b14-7ffd-46cf-9d2f-b4b982b4f1dc  2017-05-22T06:56:19-05:00   \n",
       "4  urn:uuid:5a288b89-ba8d-48f4-b99a-ca6bdd67c2e6  2017-09-01T01:17:20-05:00   \n",
       "5  urn:uuid:214ff775-6924-4ecf-aedd-9847146fe66b  2016-02-04T00:59:02-06:00   \n",
       "6  urn:uuid:00d3a53e-0b83-4769-b010-4e23f5db8987  2016-04-02T12:13:51-05:00   \n",
       "7  urn:uuid:00d3a53e-0b83-4769-b010-4e23f5db8987  2017-04-08T12:13:51-05:00   \n",
       "8  urn:uuid:00d3a53e-0b83-4769-b010-4e23f5db8987  2018-04-14T12:13:51-05:00   \n",
       "9  urn:uuid:aa2be051-d228-419d-9a60-18b64e10ea81  2016-03-09T11:52:10-06:00   \n",
       "\n",
       "             encounter_start  \n",
       "0  2016-08-21T07:45:47-05:00  \n",
       "1  2017-08-27T07:45:47-05:00  \n",
       "2  2016-05-16T06:56:19-05:00  \n",
       "3  2017-05-22T06:56:19-05:00  \n",
       "4  2017-09-01T01:17:20-05:00  \n",
       "5  2016-02-04T00:59:02-06:00  \n",
       "6  2016-04-02T12:13:51-05:00  \n",
       "7  2017-04-08T12:13:51-05:00  \n",
       "8  2018-04-14T12:13:51-05:00  \n",
       "9  2016-03-09T11:52:10-06:00  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wellness_visits = spark.sql(\"\"\"\n",
    "select subject.reference person_ref, \n",
    "       period.start encounter_start,\n",
    "       period.end encounter_start\n",
    "from encounter \n",
    "where class.code = 'WELLNESS' and\n",
    "      period.start > '2016'\n",
    "\"\"\")\n",
    "\n",
    "wellness_visits.limit(10).toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, to exclude people who have had wellness visits, we will do an anti-join between our at-risk group and the visits dataframe we created. The end result is a simple table with the required people!"
   ]
  },
  {
   "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>person_ref</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>urn:uuid:1d6fb98a-1716-4313-a3f9-273b5f89fea3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>urn:uuid:72d4853d-0c27-4f5c-8274-4e301e593b31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>urn:uuid:7f4dc74b-16f4-4a55-a1ad-8a42e050488c</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>urn:uuid:17c10896-7229-45af-ba70-6975eb174a43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>urn:uuid:bb98a0cc-7990-4845-977d-8c17e1811bb1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>urn:uuid:cfdd0496-7fcd-49d1-be6c-b63a70b443d3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>urn:uuid:2d5049e5-39e5-4209-8f67-90d512d495c7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>urn:uuid:1f07abb1-76ec-4af4-9bf5-92b595434384</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>urn:uuid:939da57c-e485-4063-8fae-e03cb47330a2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>urn:uuid:3769d894-6c38-4d2f-a0a6-f0ff5e098a58</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      person_ref\n",
       "0  urn:uuid:1d6fb98a-1716-4313-a3f9-273b5f89fea3\n",
       "1  urn:uuid:72d4853d-0c27-4f5c-8274-4e301e593b31\n",
       "2  urn:uuid:7f4dc74b-16f4-4a55-a1ad-8a42e050488c\n",
       "3  urn:uuid:17c10896-7229-45af-ba70-6975eb174a43\n",
       "4  urn:uuid:bb98a0cc-7990-4845-977d-8c17e1811bb1\n",
       "5  urn:uuid:cfdd0496-7fcd-49d1-be6c-b63a70b443d3\n",
       "6  urn:uuid:2d5049e5-39e5-4209-8f67-90d512d495c7\n",
       "7  urn:uuid:1f07abb1-76ec-4af4-9bf5-92b595434384\n",
       "8  urn:uuid:939da57c-e485-4063-8fae-e03cb47330a2\n",
       "9  urn:uuid:3769d894-6c38-4d2f-a0a6-f0ff5e098a58"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "diabetes_without_wellness = diabetes_risk.join(wellness_visits, \n",
    "                                               ['person_ref'], \n",
    "                                               'left_anti')\n",
    "\n",
    "diabetes_without_wellness.limit(10).toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Building a Time Series\n",
    "We've looked at some general queries, but a number of machine learning and other analysis works best if we can create a time series from our data. We'll start with some (arbitrary) observation values by month.\n",
    "\n",
    "Except for the *in_valueset* method, all of this is standard SQL, leverage Apache Spark's support of nested structures. The \"group by\" clause defines the time periods to aggregate by, and aggregation functions like *avg* or *max* will conditionally include values that satisfy the nested *if* expression.\n",
    "\n",
    "Advanced SQL users can also leverage window functions over this data. See the Spark documentation for details."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "observations = spark.sql(\"\"\"\n",
    "select subject.reference patient_id,\n",
    "       year(effectiveDateTime) year,\n",
    "       month(effectiveDateTime) month,\n",
    "       \n",
    "       avg(if(in_valueset(code, 'hba1c'), \n",
    "              valueQuantity.value, \n",
    "              null)) avg_hba1c_level,       \n",
    "\n",
    "       avg(if(in_valueset(code, 'ldl'), \n",
    "              valueQuantity.value, \n",
    "              null)) avg_ldl,\n",
    "\n",
    "       avg(if(in_valueset(code, 'hdl'), \n",
    "              valueQuantity.value, \n",
    "              null)) avg_hdl,\n",
    "                                          \n",
    "       max(if(in_valueset(code, 'triglycerides'), \n",
    "              valueQuantity.value, \n",
    "              null)) max_triglycerides\n",
    "              \n",
    "from observation\n",
    "where effectiveDateTime >= '2013-01-01' and\n",
    "      effectiveDateTime < '2018-01-01'\n",
    "group by subject.reference, \n",
    "         year(effectiveDateTime), \n",
    "         month(effectiveDateTime)\n",
    "order by patient_id, year, month \n",
    "\"\"\").cache()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>patient_id</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>avg_hba1c_level</th>\n",
       "      <th>avg_ldl</th>\n",
       "      <th>avg_hdl</th>\n",
       "      <th>max_triglycerides</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>urn:uuid:00d3a53e-0b83-4769-b010-4e23f5db8987</td>\n",
       "      <td>2013</td>\n",
       "      <td>3</td>\n",
       "      <td>6.03680000</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>urn:uuid:00d3a53e-0b83-4769-b010-4e23f5db8987</td>\n",
       "      <td>2014</td>\n",
       "      <td>3</td>\n",
       "      <td>6.25720000</td>\n",
       "      <td>80.19360000</td>\n",
       "      <td>75.33900000</td>\n",
       "      <td>121.3708</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>urn:uuid:00d3a53e-0b83-4769-b010-4e23f5db8987</td>\n",
       "      <td>2015</td>\n",
       "      <td>3</td>\n",
       "      <td>6.17500000</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>urn:uuid:00d3a53e-0b83-4769-b010-4e23f5db8987</td>\n",
       "      <td>2015</td>\n",
       "      <td>11</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>urn:uuid:00d3a53e-0b83-4769-b010-4e23f5db8987</td>\n",
       "      <td>2016</td>\n",
       "      <td>4</td>\n",
       "      <td>6.15090000</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>urn:uuid:00d3a53e-0b83-4769-b010-4e23f5db8987</td>\n",
       "      <td>2017</td>\n",
       "      <td>4</td>\n",
       "      <td>6.27880000</td>\n",
       "      <td>70.73310000</td>\n",
       "      <td>64.90330000</td>\n",
       "      <td>123.0671</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>urn:uuid:015da593-16ae-44fb-861d-8b2b891404e2</td>\n",
       "      <td>2013</td>\n",
       "      <td>5</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>urn:uuid:015da593-16ae-44fb-861d-8b2b891404e2</td>\n",
       "      <td>2014</td>\n",
       "      <td>11</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>urn:uuid:11c02cdc-8ea8-4231-9d05-2b34adc62c83</td>\n",
       "      <td>2013</td>\n",
       "      <td>4</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>urn:uuid:11c02cdc-8ea8-4231-9d05-2b34adc62c83</td>\n",
       "      <td>2014</td>\n",
       "      <td>5</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      patient_id  year  month avg_hba1c_level  \\\n",
       "0  urn:uuid:00d3a53e-0b83-4769-b010-4e23f5db8987  2013      3      6.03680000   \n",
       "1  urn:uuid:00d3a53e-0b83-4769-b010-4e23f5db8987  2014      3      6.25720000   \n",
       "2  urn:uuid:00d3a53e-0b83-4769-b010-4e23f5db8987  2015      3      6.17500000   \n",
       "3  urn:uuid:00d3a53e-0b83-4769-b010-4e23f5db8987  2015     11            None   \n",
       "4  urn:uuid:00d3a53e-0b83-4769-b010-4e23f5db8987  2016      4      6.15090000   \n",
       "5  urn:uuid:00d3a53e-0b83-4769-b010-4e23f5db8987  2017      4      6.27880000   \n",
       "6  urn:uuid:015da593-16ae-44fb-861d-8b2b891404e2  2013      5            None   \n",
       "7  urn:uuid:015da593-16ae-44fb-861d-8b2b891404e2  2014     11            None   \n",
       "8  urn:uuid:11c02cdc-8ea8-4231-9d05-2b34adc62c83  2013      4            None   \n",
       "9  urn:uuid:11c02cdc-8ea8-4231-9d05-2b34adc62c83  2014      5            None   \n",
       "\n",
       "       avg_ldl      avg_hdl max_triglycerides  \n",
       "0         None         None              None  \n",
       "1  80.19360000  75.33900000          121.3708  \n",
       "2         None         None              None  \n",
       "3         None         None              None  \n",
       "4         None         None              None  \n",
       "5  70.73310000  64.90330000          123.0671  \n",
       "6         None         None              None  \n",
       "7         None         None              None  \n",
       "8         None         None              None  \n",
       "9         None         None              None  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "observations.limit(10).toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here is a similar aggregation for conditions. We can include an arbitrary number of FHIR resources using this pattern."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "conditions = spark.sql(\"\"\"\n",
    "select subject.reference patient_id,\n",
    "       year(onsetDateTime) year,\n",
    "       month(onsetDateTime) month,\n",
    "\n",
    "       max(if(in_valueset(code, 'hypertension'), \n",
    "              true, \n",
    "              false)) hypertension,\n",
    "              \n",
    "       max(if(in_valueset(code, 'mi'), \n",
    "              true, \n",
    "              false)) mi,\n",
    "              \n",
    "       max(if(in_valueset(code, 'chd'), \n",
    "              true, \n",
    "              false)) chd             \n",
    "              \n",
    "from condition\n",
    "where onsetDateTime >= '2013-01-01' and\n",
    "      onsetDateTime < '2018-01-01'\n",
    "group by subject.reference, \n",
    "         year(onsetDateTime), \n",
    "         month(onsetDateTime)\n",
    "order by patient_id, year, month         \n",
    "\"\"\").cache()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, we grab some demographics data about hte patient and join it to the observations and conditions data frames loaded above."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "patients = spark.sql(\"\"\"\n",
    "select id patient_id, birthDate from patient\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "joined_patient = patients \\\n",
    "                 .join(observations, 'patient_id', 'left_outer') \\\n",
    "                 .join(conditions, ['patient_id', 'year', 'month'], 'left_outer') \\\n",
    "                 .where(col('year').isNotNull())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "That's it! Let's take a look at our handiwork:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>patient_id</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>birthDate</th>\n",
       "      <th>avg_hba1c_level</th>\n",
       "      <th>avg_ldl</th>\n",
       "      <th>avg_hdl</th>\n",
       "      <th>max_triglycerides</th>\n",
       "      <th>hypertension</th>\n",
       "      <th>mi</th>\n",
       "      <th>chd</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>urn:uuid:f88c719e-9875-446d-852b-cddb79fe4998</td>\n",
       "      <td>2017</td>\n",
       "      <td>8</td>\n",
       "      <td>1943-10-03</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>urn:uuid:f88c719e-9875-446d-852b-cddb79fe4998</td>\n",
       "      <td>2016</td>\n",
       "      <td>8</td>\n",
       "      <td>1943-10-03</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>urn:uuid:f88c719e-9875-446d-852b-cddb79fe4998</td>\n",
       "      <td>2015</td>\n",
       "      <td>9</td>\n",
       "      <td>1943-10-03</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>urn:uuid:f88c719e-9875-446d-852b-cddb79fe4998</td>\n",
       "      <td>2014</td>\n",
       "      <td>9</td>\n",
       "      <td>1943-10-03</td>\n",
       "      <td>None</td>\n",
       "      <td>71.73480000</td>\n",
       "      <td>62.99910000</td>\n",
       "      <td>131.3209</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>urn:uuid:f88c719e-9875-446d-852b-cddb79fe4998</td>\n",
       "      <td>2013</td>\n",
       "      <td>9</td>\n",
       "      <td>1943-10-03</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>urn:uuid:d9ac6b14-7ffd-46cf-9d2f-b4b982b4f1dc</td>\n",
       "      <td>2017</td>\n",
       "      <td>5</td>\n",
       "      <td>1947-01-13</td>\n",
       "      <td>None</td>\n",
       "      <td>76.11670000</td>\n",
       "      <td>60.94370000</td>\n",
       "      <td>119.8152</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>urn:uuid:d9ac6b14-7ffd-46cf-9d2f-b4b982b4f1dc</td>\n",
       "      <td>2016</td>\n",
       "      <td>5</td>\n",
       "      <td>1947-01-13</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>urn:uuid:d9ac6b14-7ffd-46cf-9d2f-b4b982b4f1dc</td>\n",
       "      <td>2015</td>\n",
       "      <td>5</td>\n",
       "      <td>1947-01-13</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>urn:uuid:d9ac6b14-7ffd-46cf-9d2f-b4b982b4f1dc</td>\n",
       "      <td>2014</td>\n",
       "      <td>5</td>\n",
       "      <td>1947-01-13</td>\n",
       "      <td>None</td>\n",
       "      <td>71.63320000</td>\n",
       "      <td>70.57590000</td>\n",
       "      <td>127.3699</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>urn:uuid:d9ac6b14-7ffd-46cf-9d2f-b4b982b4f1dc</td>\n",
       "      <td>2013</td>\n",
       "      <td>7</td>\n",
       "      <td>1947-01-13</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      patient_id  year  month   birthDate  \\\n",
       "0  urn:uuid:f88c719e-9875-446d-852b-cddb79fe4998  2017      8  1943-10-03   \n",
       "1  urn:uuid:f88c719e-9875-446d-852b-cddb79fe4998  2016      8  1943-10-03   \n",
       "2  urn:uuid:f88c719e-9875-446d-852b-cddb79fe4998  2015      9  1943-10-03   \n",
       "3  urn:uuid:f88c719e-9875-446d-852b-cddb79fe4998  2014      9  1943-10-03   \n",
       "4  urn:uuid:f88c719e-9875-446d-852b-cddb79fe4998  2013      9  1943-10-03   \n",
       "5  urn:uuid:d9ac6b14-7ffd-46cf-9d2f-b4b982b4f1dc  2017      5  1947-01-13   \n",
       "6  urn:uuid:d9ac6b14-7ffd-46cf-9d2f-b4b982b4f1dc  2016      5  1947-01-13   \n",
       "7  urn:uuid:d9ac6b14-7ffd-46cf-9d2f-b4b982b4f1dc  2015      5  1947-01-13   \n",
       "8  urn:uuid:d9ac6b14-7ffd-46cf-9d2f-b4b982b4f1dc  2014      5  1947-01-13   \n",
       "9  urn:uuid:d9ac6b14-7ffd-46cf-9d2f-b4b982b4f1dc  2013      7  1947-01-13   \n",
       "\n",
       "  avg_hba1c_level      avg_ldl      avg_hdl max_triglycerides hypertension  \\\n",
       "0            None         None         None              None         None   \n",
       "1            None         None         None              None        False   \n",
       "2            None         None         None              None         None   \n",
       "3            None  71.73480000  62.99910000          131.3209        False   \n",
       "4            None         None         None              None         None   \n",
       "5            None  76.11670000  60.94370000          119.8152        False   \n",
       "6            None         None         None              None         None   \n",
       "7            None         None         None              None         None   \n",
       "8            None  71.63320000  70.57590000          127.3699         None   \n",
       "9            None         None         None              None        False   \n",
       "\n",
       "      mi    chd  \n",
       "0   None   None  \n",
       "1  False  False  \n",
       "2   None   None  \n",
       "3  False   True  \n",
       "4   None   None  \n",
       "5  False   True  \n",
       "6   None   None  \n",
       "7   None   None  \n",
       "8   None   None  \n",
       "9  False  False  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "joined_patient.limit(10).toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Save to a table \n",
    "Now we will save the results of our engineering exercise to a table for others to use. This may be handing off our dataset to an analyst or data scientist for a specific use case, or just keeping a copy for ourselves so we don't need to reproduce it in the future."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "spark.sql('create database if not exists my_analysis_work')\n",
    "\n",
    "# Only needed so this notebook can be run repeatedly\n",
    "spark.sql('drop table if exists my_analysis_work.example_timeseries')\n",
    "\n",
    "joined_patient.write.saveAsTable('my_analysis_work.example_timeseries')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can query the table we just saved and see the expected results:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>patient_id</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>birthDate</th>\n",
       "      <th>avg_hba1c_level</th>\n",
       "      <th>avg_ldl</th>\n",
       "      <th>avg_hdl</th>\n",
       "      <th>max_triglycerides</th>\n",
       "      <th>hypertension</th>\n",
       "      <th>mi</th>\n",
       "      <th>chd</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>urn:uuid:cbd0071f-d976-479b-b008-e1d28d91c533</td>\n",
       "      <td>2017</td>\n",
       "      <td>11</td>\n",
       "      <td>1957-08-27</td>\n",
       "      <td>4.71910000</td>\n",
       "      <td>120.18090000</td>\n",
       "      <td>58.64480000</td>\n",
       "      <td>160.0553</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>urn:uuid:cbd0071f-d976-479b-b008-e1d28d91c533</td>\n",
       "      <td>2017</td>\n",
       "      <td>12</td>\n",
       "      <td>1957-08-27</td>\n",
       "      <td>4.71910000</td>\n",
       "      <td>139.63350000</td>\n",
       "      <td>42.80810000</td>\n",
       "      <td>163.5286</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>urn:uuid:d177cec5-47ee-4758-bf58-69f5ec3181c2</td>\n",
       "      <td>2013</td>\n",
       "      <td>11</td>\n",
       "      <td>1944-07-24</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>urn:uuid:5dbece6f-58b5-482f-b62c-7a18ff0749cd</td>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1959-02-15</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>urn:uuid:5df6ecb0-f5d7-4d75-b4f4-25c21c9a4c5f</td>\n",
       "      <td>2013</td>\n",
       "      <td>1</td>\n",
       "      <td>1932-08-14</td>\n",
       "      <td>2.10000000</td>\n",
       "      <td>158.86630000</td>\n",
       "      <td>43.97930000</td>\n",
       "      <td>154.6200</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>urn:uuid:5df6ecb0-f5d7-4d75-b4f4-25c21c9a4c5f</td>\n",
       "      <td>2013</td>\n",
       "      <td>2</td>\n",
       "      <td>1932-08-14</td>\n",
       "      <td>2.10000000</td>\n",
       "      <td>132.38610000</td>\n",
       "      <td>55.50350000</td>\n",
       "      <td>150.2437</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>urn:uuid:17cf8465-1a05-4b6e-ac14-9607214c20ef</td>\n",
       "      <td>2017</td>\n",
       "      <td>9</td>\n",
       "      <td>2004-08-06</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>urn:uuid:209de376-91bd-4864-878b-183d54f994d1</td>\n",
       "      <td>2013</td>\n",
       "      <td>1</td>\n",
       "      <td>1959-02-27</td>\n",
       "      <td>6.99310000</td>\n",
       "      <td>135.35910000</td>\n",
       "      <td>54.30050000</td>\n",
       "      <td>172.2309</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>urn:uuid:209de376-91bd-4864-878b-183d54f994d1</td>\n",
       "      <td>2014</td>\n",
       "      <td>1</td>\n",
       "      <td>1959-02-27</td>\n",
       "      <td>6.99310000</td>\n",
       "      <td>120.40610000</td>\n",
       "      <td>48.18740000</td>\n",
       "      <td>169.1451</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>urn:uuid:3d923883-d4f9-4237-838d-ff3ec3190950</td>\n",
       "      <td>2017</td>\n",
       "      <td>11</td>\n",
       "      <td>1947-07-14</td>\n",
       "      <td>1.87330000</td>\n",
       "      <td>104.12960000</td>\n",
       "      <td>72.91530000</td>\n",
       "      <td>112.2814</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      patient_id  year  month   birthDate  \\\n",
       "0  urn:uuid:cbd0071f-d976-479b-b008-e1d28d91c533  2017     11  1957-08-27   \n",
       "1  urn:uuid:cbd0071f-d976-479b-b008-e1d28d91c533  2017     12  1957-08-27   \n",
       "2  urn:uuid:d177cec5-47ee-4758-bf58-69f5ec3181c2  2013     11  1944-07-24   \n",
       "3  urn:uuid:5dbece6f-58b5-482f-b62c-7a18ff0749cd  2017      1  1959-02-15   \n",
       "4  urn:uuid:5df6ecb0-f5d7-4d75-b4f4-25c21c9a4c5f  2013      1  1932-08-14   \n",
       "5  urn:uuid:5df6ecb0-f5d7-4d75-b4f4-25c21c9a4c5f  2013      2  1932-08-14   \n",
       "6  urn:uuid:17cf8465-1a05-4b6e-ac14-9607214c20ef  2017      9  2004-08-06   \n",
       "7  urn:uuid:209de376-91bd-4864-878b-183d54f994d1  2013      1  1959-02-27   \n",
       "8  urn:uuid:209de376-91bd-4864-878b-183d54f994d1  2014      1  1959-02-27   \n",
       "9  urn:uuid:3d923883-d4f9-4237-838d-ff3ec3190950  2017     11  1947-07-14   \n",
       "\n",
       "  avg_hba1c_level       avg_ldl      avg_hdl max_triglycerides hypertension  \\\n",
       "0      4.71910000  120.18090000  58.64480000          160.0553         None   \n",
       "1      4.71910000  139.63350000  42.80810000          163.5286         None   \n",
       "2            None          None         None              None        False   \n",
       "3            None          None         None              None         None   \n",
       "4      2.10000000  158.86630000  43.97930000          154.6200         None   \n",
       "5      2.10000000  132.38610000  55.50350000          150.2437        False   \n",
       "6            None          None         None              None        False   \n",
       "7      6.99310000  135.35910000  54.30050000          172.2309        False   \n",
       "8      6.99310000  120.40610000  48.18740000          169.1451        False   \n",
       "9      1.87330000  104.12960000  72.91530000          112.2814         None   \n",
       "\n",
       "      mi    chd  \n",
       "0   None   None  \n",
       "1   None   None  \n",
       "2  False   True  \n",
       "3   None   None  \n",
       "4   None   None  \n",
       "5  False  False  \n",
       "6  False   True  \n",
       "7  False  False  \n",
       "8  False   True  \n",
       "9   None   None  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spark.sql('select * from my_analysis_work.example_timeseries limit 10').toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## What's Next?\n",
    "Such a simple, tabular model suited for our use case is easily consumed for a variety of analysis. Let's take a look at a few:\n",
    "\n",
    "### Descriptive Statistics\n",
    "Machine Learning gets all of the attention, but a lot of problems can be solved with descriptive statistics. Fortunately Apache Spark offers a rich package for this. Here's a single line to compute the correlation between LDL and triglycerides in our dataset:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.8177939987318618"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "joined_patient.corr('avg_ldl', 'max_triglycerides')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Windowing Functions\n",
    "Spark [Windowing Functions](https://databricks.com/blog/2015/07/15/introducing-window-functions-in-spark-sql.html) can be easily used with this format to simplify making predictions about future state. \n",
    "\n",
    "Here we simplly add a column indicating whether the next time slot for a patient has a Coronary Heart Disease diagnosis:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>patient_id</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>birthDate</th>\n",
       "      <th>avg_hba1c_level</th>\n",
       "      <th>avg_ldl</th>\n",
       "      <th>avg_hdl</th>\n",
       "      <th>max_triglycerides</th>\n",
       "      <th>hypertension</th>\n",
       "      <th>mi</th>\n",
       "      <th>chd</th>\n",
       "      <th>next_chd</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>urn:uuid:90a7ded5-a5ce-43df-b973-7bc7ce7a3011</td>\n",
       "      <td>2013</td>\n",
       "      <td>4</td>\n",
       "      <td>1974-11-23</td>\n",
       "      <td>None</td>\n",
       "      <td>86.87340000</td>\n",
       "      <td>73.86550000</td>\n",
       "      <td>142.3448</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>urn:uuid:90a7ded5-a5ce-43df-b973-7bc7ce7a3011</td>\n",
       "      <td>2015</td>\n",
       "      <td>4</td>\n",
       "      <td>1974-11-23</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>urn:uuid:90a7ded5-a5ce-43df-b973-7bc7ce7a3011</td>\n",
       "      <td>2017</td>\n",
       "      <td>4</td>\n",
       "      <td>1974-11-23</td>\n",
       "      <td>None</td>\n",
       "      <td>104.09860000</td>\n",
       "      <td>59.92160000</td>\n",
       "      <td>129.4962</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>urn:uuid:72f33006-cb57-4a08-bc4e-cce1f328f1b0</td>\n",
       "      <td>2013</td>\n",
       "      <td>3</td>\n",
       "      <td>1938-05-18</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>urn:uuid:72f33006-cb57-4a08-bc4e-cce1f328f1b0</td>\n",
       "      <td>2014</td>\n",
       "      <td>4</td>\n",
       "      <td>1938-05-18</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>urn:uuid:72f33006-cb57-4a08-bc4e-cce1f328f1b0</td>\n",
       "      <td>2014</td>\n",
       "      <td>9</td>\n",
       "      <td>1938-05-18</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>urn:uuid:72f33006-cb57-4a08-bc4e-cce1f328f1b0</td>\n",
       "      <td>2015</td>\n",
       "      <td>9</td>\n",
       "      <td>1938-05-18</td>\n",
       "      <td>None</td>\n",
       "      <td>77.24200000</td>\n",
       "      <td>77.94560000</td>\n",
       "      <td>106.8841</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>urn:uuid:72f33006-cb57-4a08-bc4e-cce1f328f1b0</td>\n",
       "      <td>2016</td>\n",
       "      <td>9</td>\n",
       "      <td>1938-05-18</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>urn:uuid:72f33006-cb57-4a08-bc4e-cce1f328f1b0</td>\n",
       "      <td>2017</td>\n",
       "      <td>10</td>\n",
       "      <td>1938-05-18</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>urn:uuid:75c9acfa-a7d4-44aa-aec0-70aa0a7affcd</td>\n",
       "      <td>2013</td>\n",
       "      <td>12</td>\n",
       "      <td>2003-12-10</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      patient_id  year  month   birthDate  \\\n",
       "0  urn:uuid:90a7ded5-a5ce-43df-b973-7bc7ce7a3011  2013      4  1974-11-23   \n",
       "1  urn:uuid:90a7ded5-a5ce-43df-b973-7bc7ce7a3011  2015      4  1974-11-23   \n",
       "2  urn:uuid:90a7ded5-a5ce-43df-b973-7bc7ce7a3011  2017      4  1974-11-23   \n",
       "3  urn:uuid:72f33006-cb57-4a08-bc4e-cce1f328f1b0  2013      3  1938-05-18   \n",
       "4  urn:uuid:72f33006-cb57-4a08-bc4e-cce1f328f1b0  2014      4  1938-05-18   \n",
       "5  urn:uuid:72f33006-cb57-4a08-bc4e-cce1f328f1b0  2014      9  1938-05-18   \n",
       "6  urn:uuid:72f33006-cb57-4a08-bc4e-cce1f328f1b0  2015      9  1938-05-18   \n",
       "7  urn:uuid:72f33006-cb57-4a08-bc4e-cce1f328f1b0  2016      9  1938-05-18   \n",
       "8  urn:uuid:72f33006-cb57-4a08-bc4e-cce1f328f1b0  2017     10  1938-05-18   \n",
       "9  urn:uuid:75c9acfa-a7d4-44aa-aec0-70aa0a7affcd  2013     12  2003-12-10   \n",
       "\n",
       "  avg_hba1c_level       avg_ldl      avg_hdl max_triglycerides hypertension  \\\n",
       "0            None   86.87340000  73.86550000          142.3448         None   \n",
       "1            None          None         None              None         None   \n",
       "2            None  104.09860000  59.92160000          129.4962         None   \n",
       "3            None          None         None              None         None   \n",
       "4            None          None         None              None        False   \n",
       "5            None          None         None              None        False   \n",
       "6            None   77.24200000  77.94560000          106.8841         None   \n",
       "7            None          None         None              None         None   \n",
       "8            None          None         None              None         None   \n",
       "9            None          None         None              None         None   \n",
       "\n",
       "      mi    chd next_chd  \n",
       "0   None   None     None  \n",
       "1   None   None     None  \n",
       "2   None   None     None  \n",
       "3   None   None     True  \n",
       "4  False   True    False  \n",
       "5  False  False     None  \n",
       "6   None   None     None  \n",
       "7   None   None     None  \n",
       "8   None   None     None  \n",
       "9   None   None     True  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from pyspark.sql.window import Window\n",
    "import pyspark.sql.functions as func\n",
    "\n",
    "window = Window.partitionBy('patient_id').orderBy('year', 'month')\n",
    "\n",
    "predict_chd = joined_patient.withColumn('next_chd', func.lead('chd').over(window))\n",
    "\n",
    "predict_chd.limit(10).toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Machine Learning\n",
    "Finally, these data engineering tasks create a solid foundation for Machine Learning. We don't have time in this tutorial to go into depth here, but [Spark ML](https://spark.apache.org/docs/2.3.0/ml-pipeline.html) has a number of great references and will be very familiar to anyone familiar with Python's scikit-learn library. Users can also save our the tabular dataset and load it into any external ML tool that better fits their needs. \n",
    "\n",
    "```python\n",
    "from pyspark.ml.classification import LogisticRegression\n",
    "from pyspark.ml import Pipeline\n",
    "\n",
    "# Predict CHD based on features extracted from the pipeline\n",
    "regression = LogisticRegression(featuresCol='features', \n",
    "                                labelCol='next_chd',\n",
    "                                maxIter=10)\n",
    "\n",
    "# The pipeline contains several stages that extract and normalize features\n",
    "# from our tabluar dataset\n",
    "pipeline = Pipeline(stages=[convert_to_numeric, \n",
    "                            extract_lab_features,\n",
    "                            scaled_lab_values,\n",
    "                            assemble_feature_vector,\n",
    "                            regression])\n",
    "\n",
    "```\n",
    "Now we can train the pipeline with the table we created above:\n",
    "\n",
    "```python\n",
    "# Split our table between training and test data\n",
    "(train, test) = predict_chd.randomSplit([0.7, 0.3])\n",
    "\n",
    "model = pipeline.fit(train)\n",
    "```"
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}