notebooks/eda_top_conditions.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Top 3 Conditions by Current Temperature Interval\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Init"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os\n",
    "import duckdb   \n",
    "\n",
    "from dotenv import load_dotenv, find_dotenv\n",
    "load_dotenv('/workspaces/patient_conditions_weather/src/.env')\n",
    "os.environ['GOOGLE_APPLICATION_CREDENTIALS'] = '/workspaces/patient_conditions_weather/airflow/credentials/google_credentials.json'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load EMR Records\n",
    "\n",
    "Currently loading from RAW and doing quick analytics features\n",
    "This should be replaced by a raw - stg transformation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "emr_path = f\"gs://{os.getenv('EMR_RAW_BUCKET')}/{os.getenv('EMR_RAW_PATH')}\"\n",
    "encounters_path = emr_path +'/encounters.csv'\n",
    "organizations_path = emr_path + '/organizations.csv'\n",
    "conditions_path = emr_path + '/conditions.csv'\n",
    "\n",
    "encounters = pd.read_csv(encounters_path, delimiter=\",\")\n",
    "conditions = pd.read_csv(conditions_path, delimiter=\",\")\n",
    "organizations = pd.read_csv(organizations_path, delimiter=\",\")\n",
    "\n",
    "import re\n",
    "rexp = r'\\((.*?)\\)'\n",
    "conditions['condition_name'] = conditions['DESCRIPTION'].apply(lambda x: re.sub(r' \\([^)]*\\)', '', x))\n",
    "conditions['condition_type'] = conditions['DESCRIPTION'].apply(lambda x: re.search(rexp, x).group(1) if re.search(rexp, x) else '')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load Daily Weather\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "weather_daily_data_path = f\"gs://{os.getenv('WEATHER_BUCKET')}/{os.getenv('WEATHER_DATA_DAILY_PATH')}\"\n",
    "weather_daily_data = pd.read_parquet(weather_daily_data_path, engine=\"pyarrow\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Register dckdb tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<duckdb.duckdb.DuckDBPyConnection at 0x7f462ac67470>"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a DuckDB connection\n",
    "con = duckdb.connect()\n",
    "con.register('encounters', encounters)\n",
    "con.register('conditions', conditions)\n",
    "con.register('organizations', organizations)\n",
    "con.register('weather_daily_data', weather_daily_data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create table `diagnoses` "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<duckdb.duckdb.DuckDBPyConnection at 0x7f462ac67470>"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "starting_year = 1980\n",
    "query = f\"\"\"\n",
    "with events as (\n",
    "    select \n",
    "        CAST(e.stop AS DATE) diagnosis_date,\n",
    "        o.name, o.city,\n",
    "        c.code,\n",
    "        c.condition_name,\n",
    "        c.condition_type,\n",
    "        round(o.LAT,1) as latitude,\n",
    "        round(o.LON,1) as longitude\n",
    "    from encounters e\n",
    "    inner join organizations o on e.ORGANIZATION = o.id\n",
    "    inner join conditions c on e.id = c.ENCOUNTER\n",
    ")\n",
    "select *\n",
    "from events\n",
    "where extract('year' from diagnosis_date) >= {starting_year}\n",
    "and condition_type in ('disorder', '')\n",
    "order by 1\n",
    "\"\"\"\n",
    "diagnoses = con.execute(query).fetchdf()\n",
    "con.register('diagnoses', diagnoses)"
   ]
  },
  {
   "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>diagnosis_date</th>\n",
       "      <th>CODE</th>\n",
       "      <th>condition_name</th>\n",
       "      <th>condition_type</th>\n",
       "      <th>daylight_duration</th>\n",
       "      <th>sunshine_duration</th>\n",
       "      <th>apparent_temperature_mean</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1983-05-11</td>\n",
       "      <td>444814009</td>\n",
       "      <td>Viral sinusitis</td>\n",
       "      <td>disorder</td>\n",
       "      <td>52027.80</td>\n",
       "      <td>39295.40</td>\n",
       "      <td>3.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1983-12-13</td>\n",
       "      <td>22298006</td>\n",
       "      <td>Myocardial Infarction</td>\n",
       "      <td></td>\n",
       "      <td>32750.79</td>\n",
       "      <td>0.00</td>\n",
       "      <td>5.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1983-04-01</td>\n",
       "      <td>40055000</td>\n",
       "      <td>Chronic sinusitis</td>\n",
       "      <td>disorder</td>\n",
       "      <td>45699.07</td>\n",
       "      <td>39281.17</td>\n",
       "      <td>-0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1983-04-05</td>\n",
       "      <td>239873007</td>\n",
       "      <td>Osteoarthritis of knee</td>\n",
       "      <td></td>\n",
       "      <td>46387.60</td>\n",
       "      <td>32649.29</td>\n",
       "      <td>3.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1983-05-24</td>\n",
       "      <td>53741008</td>\n",
       "      <td>Coronary Heart Disease</td>\n",
       "      <td></td>\n",
       "      <td>53579.58</td>\n",
       "      <td>23740.59</td>\n",
       "      <td>15.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>265</th>\n",
       "      <td>1981-07-31</td>\n",
       "      <td>10509002</td>\n",
       "      <td>Acute bronchitis</td>\n",
       "      <td>disorder</td>\n",
       "      <td>52088.85</td>\n",
       "      <td>48600.56</td>\n",
       "      <td>22.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>266</th>\n",
       "      <td>1981-11-29</td>\n",
       "      <td>15777000</td>\n",
       "      <td>Prediabetes</td>\n",
       "      <td></td>\n",
       "      <td>33809.44</td>\n",
       "      <td>21014.01</td>\n",
       "      <td>-6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>267</th>\n",
       "      <td>1981-11-29</td>\n",
       "      <td>271737000</td>\n",
       "      <td>Anemia</td>\n",
       "      <td>disorder</td>\n",
       "      <td>33809.44</td>\n",
       "      <td>21014.01</td>\n",
       "      <td>-6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268</th>\n",
       "      <td>1981-10-09</td>\n",
       "      <td>59621000</td>\n",
       "      <td>Hypertension</td>\n",
       "      <td></td>\n",
       "      <td>40976.31</td>\n",
       "      <td>34542.30</td>\n",
       "      <td>4.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>269</th>\n",
       "      <td>1981-11-29</td>\n",
       "      <td>15777000</td>\n",
       "      <td>Prediabetes</td>\n",
       "      <td></td>\n",
       "      <td>33809.44</td>\n",
       "      <td>21014.01</td>\n",
       "      <td>-6.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>270 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    diagnosis_date       CODE          condition_name condition_type  \\\n",
       "0       1983-05-11  444814009         Viral sinusitis       disorder   \n",
       "1       1983-12-13   22298006   Myocardial Infarction                  \n",
       "2       1983-04-01   40055000       Chronic sinusitis       disorder   \n",
       "3       1983-04-05  239873007  Osteoarthritis of knee                  \n",
       "4       1983-05-24   53741008  Coronary Heart Disease                  \n",
       "..             ...        ...                     ...            ...   \n",
       "265     1981-07-31   10509002        Acute bronchitis       disorder   \n",
       "266     1981-11-29   15777000             Prediabetes                  \n",
       "267     1981-11-29  271737000                  Anemia       disorder   \n",
       "268     1981-10-09   59621000            Hypertension                  \n",
       "269     1981-11-29   15777000             Prediabetes                  \n",
       "\n",
       "     daylight_duration  sunshine_duration  apparent_temperature_mean  \n",
       "0             52027.80           39295.40                        3.7  \n",
       "1             32750.79               0.00                        5.1  \n",
       "2             45699.07           39281.17                       -0.0  \n",
       "3             46387.60           32649.29                        3.3  \n",
       "4             53579.58           23740.59                       15.3  \n",
       "..                 ...                ...                        ...  \n",
       "265           52088.85           48600.56                       22.2  \n",
       "266           33809.44           21014.01                       -6.0  \n",
       "267           33809.44           21014.01                       -6.0  \n",
       "268           40976.31           34542.30                        4.7  \n",
       "269           33809.44           21014.01                       -6.0  \n",
       "\n",
       "[270 rows x 7 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "with weather as (\n",
    "    select \n",
    "        cast(date as date) as weather_date,\n",
    "        weather_code,\n",
    "        temperature_2m_mean,\tapparent_temperature_mean,\tdaylight_duration,\tsunshine_duration,\tlatitude,\tlongitude\n",
    "    from weather_daily_data     \n",
    ")\n",
    "select \n",
    "    d.diagnosis_date,\n",
    "    d.code,\n",
    "    d.condition_name,\n",
    "    d.condition_type,\n",
    "    w.daylight_duration,\n",
    "    w.sunshine_duration,\n",
    "    w.apparent_temperature_mean    \n",
    "from diagnoses d \n",
    "inner join weather w on \n",
    "    w.latitude = d.latitude\n",
    "    and w.longitude = d.longitude \n",
    "    and w.weather_date = d.diagnosis_date\n",
    "\"\"\"\n",
    "diagnoses_with_weather = con.execute(query).fetchdf()\n",
    "\n",
    "diagnoses_with_weather"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>diagnosis_date</th>\n",
       "      <th>CODE</th>\n",
       "      <th>condition_name</th>\n",
       "      <th>condition_type</th>\n",
       "      <th>daylight_duration</th>\n",
       "      <th>sunshine_duration</th>\n",
       "      <th>apparent_temperature_mean</th>\n",
       "      <th>apparent_temperature_mean_bins</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1983-05-11</td>\n",
       "      <td>444814009</td>\n",
       "      <td>Viral sinusitis</td>\n",
       "      <td>disorder</td>\n",
       "      <td>52027.80</td>\n",
       "      <td>39295.40</td>\n",
       "      <td>3.7</td>\n",
       "      <td>(3.5, 11.967]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1983-12-13</td>\n",
       "      <td>22298006</td>\n",
       "      <td>Myocardial Infarction</td>\n",
       "      <td></td>\n",
       "      <td>32750.79</td>\n",
       "      <td>0.00</td>\n",
       "      <td>5.1</td>\n",
       "      <td>(3.5, 11.967]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1983-04-01</td>\n",
       "      <td>40055000</td>\n",
       "      <td>Chronic sinusitis</td>\n",
       "      <td>disorder</td>\n",
       "      <td>45699.07</td>\n",
       "      <td>39281.17</td>\n",
       "      <td>-0.0</td>\n",
       "      <td>(-4.967, 3.5]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1983-04-05</td>\n",
       "      <td>239873007</td>\n",
       "      <td>Osteoarthritis of knee</td>\n",
       "      <td></td>\n",
       "      <td>46387.60</td>\n",
       "      <td>32649.29</td>\n",
       "      <td>3.3</td>\n",
       "      <td>(-4.967, 3.5]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1983-05-24</td>\n",
       "      <td>53741008</td>\n",
       "      <td>Coronary Heart Disease</td>\n",
       "      <td></td>\n",
       "      <td>53579.58</td>\n",
       "      <td>23740.59</td>\n",
       "      <td>15.3</td>\n",
       "      <td>(11.967, 20.433]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>265</th>\n",
       "      <td>1981-07-31</td>\n",
       "      <td>10509002</td>\n",
       "      <td>Acute bronchitis</td>\n",
       "      <td>disorder</td>\n",
       "      <td>52088.85</td>\n",
       "      <td>48600.56</td>\n",
       "      <td>22.2</td>\n",
       "      <td>(20.433, 28.9]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>266</th>\n",
       "      <td>1981-11-29</td>\n",
       "      <td>15777000</td>\n",
       "      <td>Prediabetes</td>\n",
       "      <td></td>\n",
       "      <td>33809.44</td>\n",
       "      <td>21014.01</td>\n",
       "      <td>-6.0</td>\n",
       "      <td>(-13.433, -4.967]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>267</th>\n",
       "      <td>1981-11-29</td>\n",
       "      <td>271737000</td>\n",
       "      <td>Anemia</td>\n",
       "      <td>disorder</td>\n",
       "      <td>33809.44</td>\n",
       "      <td>21014.01</td>\n",
       "      <td>-6.0</td>\n",
       "      <td>(-13.433, -4.967]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268</th>\n",
       "      <td>1981-10-09</td>\n",
       "      <td>59621000</td>\n",
       "      <td>Hypertension</td>\n",
       "      <td></td>\n",
       "      <td>40976.31</td>\n",
       "      <td>34542.30</td>\n",
       "      <td>4.7</td>\n",
       "      <td>(3.5, 11.967]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>269</th>\n",
       "      <td>1981-11-29</td>\n",
       "      <td>15777000</td>\n",
       "      <td>Prediabetes</td>\n",
       "      <td></td>\n",
       "      <td>33809.44</td>\n",
       "      <td>21014.01</td>\n",
       "      <td>-6.0</td>\n",
       "      <td>(-13.433, -4.967]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>270 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    diagnosis_date       CODE          condition_name condition_type  \\\n",
       "0       1983-05-11  444814009         Viral sinusitis       disorder   \n",
       "1       1983-12-13   22298006   Myocardial Infarction                  \n",
       "2       1983-04-01   40055000       Chronic sinusitis       disorder   \n",
       "3       1983-04-05  239873007  Osteoarthritis of knee                  \n",
       "4       1983-05-24   53741008  Coronary Heart Disease                  \n",
       "..             ...        ...                     ...            ...   \n",
       "265     1981-07-31   10509002        Acute bronchitis       disorder   \n",
       "266     1981-11-29   15777000             Prediabetes                  \n",
       "267     1981-11-29  271737000                  Anemia       disorder   \n",
       "268     1981-10-09   59621000            Hypertension                  \n",
       "269     1981-11-29   15777000             Prediabetes                  \n",
       "\n",
       "     daylight_duration  sunshine_duration  apparent_temperature_mean  \\\n",
       "0             52027.80           39295.40                        3.7   \n",
       "1             32750.79               0.00                        5.1   \n",
       "2             45699.07           39281.17                       -0.0   \n",
       "3             46387.60           32649.29                        3.3   \n",
       "4             53579.58           23740.59                       15.3   \n",
       "..                 ...                ...                        ...   \n",
       "265           52088.85           48600.56                       22.2   \n",
       "266           33809.44           21014.01                       -6.0   \n",
       "267           33809.44           21014.01                       -6.0   \n",
       "268           40976.31           34542.30                        4.7   \n",
       "269           33809.44           21014.01                       -6.0   \n",
       "\n",
       "    apparent_temperature_mean_bins  \n",
       "0                    (3.5, 11.967]  \n",
       "1                    (3.5, 11.967]  \n",
       "2                    (-4.967, 3.5]  \n",
       "3                    (-4.967, 3.5]  \n",
       "4                 (11.967, 20.433]  \n",
       "..                             ...  \n",
       "265                 (20.433, 28.9]  \n",
       "266              (-13.433, -4.967]  \n",
       "267              (-13.433, -4.967]  \n",
       "268                  (3.5, 11.967]  \n",
       "269              (-13.433, -4.967]  \n",
       "\n",
       "[270 rows x 8 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#diagnoses_with_weather['apparent_temperature_mean_q_bins'] = pd.qcut(diagnoses_with_weather['apparent_temperature_mean'], q=6)\n",
    "diagnoses_with_weather['apparent_temperature_mean_bins'] = pd.cut(diagnoses_with_weather['apparent_temperature_mean'], bins=6, labels=None)\n",
    "#diagnoses_with_weather['apparent_temperature_mean_bins'].unique()\n",
    "diagnoses_with_weather"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "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>diagnosis_date</th>\n",
       "      <th>CODE</th>\n",
       "      <th>condition_name</th>\n",
       "      <th>condition_type</th>\n",
       "      <th>daylight_duration</th>\n",
       "      <th>sunshine_duration</th>\n",
       "      <th>apparent_temperature_mean</th>\n",
       "      <th>apparent_temperature_mean_bins</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1983-05-11</td>\n",
       "      <td>444814009</td>\n",
       "      <td>Viral sinusitis</td>\n",
       "      <td>disorder</td>\n",
       "      <td>52027.80</td>\n",
       "      <td>39295.40</td>\n",
       "      <td>3.7</td>\n",
       "      <td>(3.5, 11.967]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1983-12-13</td>\n",
       "      <td>22298006</td>\n",
       "      <td>Myocardial Infarction</td>\n",
       "      <td></td>\n",
       "      <td>32750.79</td>\n",
       "      <td>0.00</td>\n",
       "      <td>5.1</td>\n",
       "      <td>(3.5, 11.967]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1983-04-01</td>\n",
       "      <td>40055000</td>\n",
       "      <td>Chronic sinusitis</td>\n",
       "      <td>disorder</td>\n",
       "      <td>45699.07</td>\n",
       "      <td>39281.17</td>\n",
       "      <td>-0.0</td>\n",
       "      <td>(-4.967, 3.5]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1983-04-05</td>\n",
       "      <td>239873007</td>\n",
       "      <td>Osteoarthritis of knee</td>\n",
       "      <td></td>\n",
       "      <td>46387.60</td>\n",
       "      <td>32649.29</td>\n",
       "      <td>3.3</td>\n",
       "      <td>(-4.967, 3.5]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1983-05-24</td>\n",
       "      <td>53741008</td>\n",
       "      <td>Coronary Heart Disease</td>\n",
       "      <td></td>\n",
       "      <td>53579.58</td>\n",
       "      <td>23740.59</td>\n",
       "      <td>15.3</td>\n",
       "      <td>(11.967, 20.433]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>265</th>\n",
       "      <td>1981-07-31</td>\n",
       "      <td>10509002</td>\n",
       "      <td>Acute bronchitis</td>\n",
       "      <td>disorder</td>\n",
       "      <td>52088.85</td>\n",
       "      <td>48600.56</td>\n",
       "      <td>22.2</td>\n",
       "      <td>(20.433, 28.9]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>266</th>\n",
       "      <td>1981-11-29</td>\n",
       "      <td>15777000</td>\n",
       "      <td>Prediabetes</td>\n",
       "      <td></td>\n",
       "      <td>33809.44</td>\n",
       "      <td>21014.01</td>\n",
       "      <td>-6.0</td>\n",
       "      <td>(-13.433, -4.967]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>267</th>\n",
       "      <td>1981-11-29</td>\n",
       "      <td>271737000</td>\n",
       "      <td>Anemia</td>\n",
       "      <td>disorder</td>\n",
       "      <td>33809.44</td>\n",
       "      <td>21014.01</td>\n",
       "      <td>-6.0</td>\n",
       "      <td>(-13.433, -4.967]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268</th>\n",
       "      <td>1981-10-09</td>\n",
       "      <td>59621000</td>\n",
       "      <td>Hypertension</td>\n",
       "      <td></td>\n",
       "      <td>40976.31</td>\n",
       "      <td>34542.30</td>\n",
       "      <td>4.7</td>\n",
       "      <td>(3.5, 11.967]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>269</th>\n",
       "      <td>1981-11-29</td>\n",
       "      <td>15777000</td>\n",
       "      <td>Prediabetes</td>\n",
       "      <td></td>\n",
       "      <td>33809.44</td>\n",
       "      <td>21014.01</td>\n",
       "      <td>-6.0</td>\n",
       "      <td>(-13.433, -4.967]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>270 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    diagnosis_date       CODE          condition_name condition_type  \\\n",
       "0       1983-05-11  444814009         Viral sinusitis       disorder   \n",
       "1       1983-12-13   22298006   Myocardial Infarction                  \n",
       "2       1983-04-01   40055000       Chronic sinusitis       disorder   \n",
       "3       1983-04-05  239873007  Osteoarthritis of knee                  \n",
       "4       1983-05-24   53741008  Coronary Heart Disease                  \n",
       "..             ...        ...                     ...            ...   \n",
       "265     1981-07-31   10509002        Acute bronchitis       disorder   \n",
       "266     1981-11-29   15777000             Prediabetes                  \n",
       "267     1981-11-29  271737000                  Anemia       disorder   \n",
       "268     1981-10-09   59621000            Hypertension                  \n",
       "269     1981-11-29   15777000             Prediabetes                  \n",
       "\n",
       "     daylight_duration  sunshine_duration  apparent_temperature_mean  \\\n",
       "0             52027.80           39295.40                        3.7   \n",
       "1             32750.79               0.00                        5.1   \n",
       "2             45699.07           39281.17                       -0.0   \n",
       "3             46387.60           32649.29                        3.3   \n",
       "4             53579.58           23740.59                       15.3   \n",
       "..                 ...                ...                        ...   \n",
       "265           52088.85           48600.56                       22.2   \n",
       "266           33809.44           21014.01                       -6.0   \n",
       "267           33809.44           21014.01                       -6.0   \n",
       "268           40976.31           34542.30                        4.7   \n",
       "269           33809.44           21014.01                       -6.0   \n",
       "\n",
       "    apparent_temperature_mean_bins  \n",
       "0                    (3.5, 11.967]  \n",
       "1                    (3.5, 11.967]  \n",
       "2                    (-4.967, 3.5]  \n",
       "3                    (-4.967, 3.5]  \n",
       "4                 (11.967, 20.433]  \n",
       "..                             ...  \n",
       "265                 (20.433, 28.9]  \n",
       "266              (-13.433, -4.967]  \n",
       "267              (-13.433, -4.967]  \n",
       "268                  (3.5, 11.967]  \n",
       "269              (-13.433, -4.967]  \n",
       "\n",
       "[270 rows x 8 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "diagnoses_with_weather"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "diagnosis_date                    datetime64[us]\n",
       "CODE                                       int64\n",
       "condition_name                            object\n",
       "condition_type                            object\n",
       "daylight_duration                        float64\n",
       "sunshine_duration                        float64\n",
       "apparent_temperature_mean                float64\n",
       "apparent_temperature_mean_bins          category\n",
       "dtype: object"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "diagnoses_with_weather.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "ename": "RuntimeError",
     "evalue": "Unable to cast Python instance of type <class 'numpy.ndarray'> to C++ type '?' (#define PYBIND11_DETAILED_ERROR_MESSAGES or compile in debug mode for details)",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mRuntimeError\u001b[0m                              Traceback (most recent call last)",
      "Cell \u001b[0;32mIn[16], line 1\u001b[0m\n\u001b[0;32m----> 1\u001b[0m \u001b[43mcon\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mregister\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mdiagnoses_with_weather\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mdiagnoses_with_weather\u001b[49m\u001b[43m)\u001b[49m\n",
      "\u001b[0;31mRuntimeError\u001b[0m: Unable to cast Python instance of type <class 'numpy.ndarray'> to C++ type '?' (#define PYBIND11_DETAILED_ERROR_MESSAGES or compile in debug mode for details)"
     ]
    }
   ],
   "source": [
    "con.register('diagnoses_with_weather', diagnoses_with_weather)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**THE BINS ARE PROVIDED IN A FORMAT THAT DUCKDB REFUSES**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "ename": "RuntimeError",
     "evalue": "Unable to cast Python instance of type <class 'numpy.ndarray'> to C++ type '?' (#define PYBIND11_DETAILED_ERROR_MESSAGES or compile in debug mode for details)",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mRuntimeError\u001b[0m                              Traceback (most recent call last)",
      "Cell \u001b[0;32mIn[51], line 1\u001b[0m\n\u001b[0;32m----> 1\u001b[0m \u001b[43mcon\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mregister\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mdiagnoses_with_weather\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mdiagnoses_with_weather\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m      3\u001b[0m query \u001b[38;5;241m=\u001b[39m \u001b[38;5;124m\"\"\"\u001b[39m\n\u001b[1;32m      4\u001b[0m \u001b[38;5;124m    select *\u001b[39m\n\u001b[1;32m      5\u001b[0m \u001b[38;5;124m    from diagnoses_with_weather \u001b[39m\n\u001b[1;32m      6\u001b[0m \u001b[38;5;124m\"\"\"\u001b[39m\n\u001b[1;32m      7\u001b[0m a_df \u001b[38;5;241m=\u001b[39m con\u001b[38;5;241m.\u001b[39mexecute(query)\u001b[38;5;241m.\u001b[39mfetchdf()\n",
      "\u001b[0;31mRuntimeError\u001b[0m: Unable to cast Python instance of type <class 'numpy.ndarray'> to C++ type '?' (#define PYBIND11_DETAILED_ERROR_MESSAGES or compile in debug mode for details)"
     ]
    }
   ],
   "source": [
    "\n",
    "\n",
    "query = \"\"\"\n",
    "    select *\n",
    "    from diagnoses_with_weather \n",
    "\"\"\"\n",
    "a_df = con.execute(query).fetchdf()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "ename": "Error",
     "evalue": "Unable to cast Python instance of type <class 'numpy.ndarray'> to C++ type '?' (#define PYBIND11_DETAILED_ERROR_MESSAGES or compile in debug mode for details)",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mError\u001b[0m                                     Traceback (most recent call last)",
      "Cell \u001b[0;32mIn[45], line 5\u001b[0m\n\u001b[1;32m      1\u001b[0m query \u001b[38;5;241m=\u001b[39m \u001b[38;5;124m\"\"\"\u001b[39m\n\u001b[1;32m      2\u001b[0m \u001b[38;5;124m    select *\u001b[39m\n\u001b[1;32m      3\u001b[0m \u001b[38;5;124m    from diagnoses_with_weather \u001b[39m\n\u001b[1;32m      4\u001b[0m \u001b[38;5;124m\"\"\"\u001b[39m\n\u001b[0;32m----> 5\u001b[0m a_df \u001b[38;5;241m=\u001b[39m \u001b[43mcon\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mexecute\u001b[49m\u001b[43m(\u001b[49m\u001b[43mquery\u001b[49m\u001b[43m)\u001b[49m\u001b[38;5;241m.\u001b[39mfetchdf()\n\u001b[1;32m      6\u001b[0m a_df\n",
      "\u001b[0;31mError\u001b[0m: Unable to cast Python instance of type <class 'numpy.ndarray'> to C++ type '?' (#define PYBIND11_DETAILED_ERROR_MESSAGES or compile in debug mode for details)"
     ]
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "    select *\n",
    "    from diagnoses_with_weather \n",
    "\"\"\"\n",
    "a_df = con.execute(query).fetchdf()\n",
    "a_df"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.11.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}