[YCMI_CBDS_Summer_Course]_Data_Management_and_Databases_Laboratory_Session_(Answers).ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "[YCMI_CBDS Summer Course] Data Management and Databases - Laboratory Session (Answers)",
      "provenance": [],
      "collapsed_sections": []
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "tyVUG0hixL9y",
        "colab_type": "text"
      },
      "source": [
        "#Lab: Data Management and Databases"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "hSz44cqTxSia",
        "colab_type": "text"
      },
      "source": [
        "#### 1. Import pandas and sqlite3 into this project"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "edcFn2R8x-yv",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "import pandas as pd\n",
        "import sqlite3"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "nfHYSA_gyR0T",
        "colab_type": "text"
      },
      "source": [
        "#### 2. Mount the google drive folder storing the MIMIC III demo csv files"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "DYIxZeepyd4m",
        "colab_type": "code",
        "outputId": "0f71562f-365a-4345-a12f-85fdea3be565",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 122
        }
      },
      "source": [
        "#mount google drive \n",
        "from google.colab import drive\n",
        "drive.mount('/content/gdrive')"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Go to this URL in a browser: https://accounts.google.com/o/oauth2/auth?client_id=947318989803-6bn6qk8qdgf4n4g3pfee6491hc0brc4i.apps.googleusercontent.com&redirect_uri=urn%3aietf%3awg%3aoauth%3a2.0%3aoob&response_type=code&scope=email%20https%3a%2f%2fwww.googleapis.com%2fauth%2fdocs.test%20https%3a%2f%2fwww.googleapis.com%2fauth%2fdrive%20https%3a%2f%2fwww.googleapis.com%2fauth%2fdrive.photos.readonly%20https%3a%2f%2fwww.googleapis.com%2fauth%2fpeopleapi.readonly\n",
            "\n",
            "Enter your authorization code:\n",
            "··········\n",
            "Mounted at /content/gdrive\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "OU1-yrffygtQ",
        "colab_type": "text"
      },
      "source": [
        "#### 3. Create variables that store the following csv files as pandas dataframes (along with any other csvs you might be interested in): \n",
        "\n",
        "*   PATIENTS.csv\n",
        "*   ADMISSIONS.csv\n",
        "*   PRESCRIPTIONS.csv\n",
        "*   SERVICES.csv\n",
        "*   D_ICD_DIAGNOSES.csv\n",
        "*   DIAGNOSES_ICD.csv"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "B0YBr0-s3cwJ",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "patients = pd.read_csv(\"/content/gdrive/My Drive/[YCMI_CBDS Summer Course] Data/mimic_iii/PATIENTS.csv\")\n",
        "admissions = pd.read_csv(\"/content/gdrive/My Drive/[YCMI_CBDS Summer Course] Data/mimic_iii/ADMISSIONS.csv\")\n",
        "prescriptions = pd.read_csv(\"/content/gdrive/My Drive/[YCMI_CBDS Summer Course] Data/mimic_iii/PRESCRIPTIONS.csv\")\n",
        "services = pd.read_csv(\"/content/gdrive/My Drive/[YCMI_CBDS Summer Course] Data/mimic_iii/SERVICES.csv\")\n",
        "diagnosis_icds = pd.read_csv(\"/content/gdrive/My Drive/[YCMI_CBDS Summer Course] Data/mimic_iii/D_ICD_DIAGNOSES.csv\")\n",
        "diagnosis_events = pd.read_csv(\"/content/gdrive/My Drive/[YCMI_CBDS Summer Course] Data/mimic_iii/DIAGNOSES_ICD.csv\")"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "GIj26pKz5Vsl",
        "colab_type": "text"
      },
      "source": [
        "#### 4. Add to the existing variable assignments below to create code that will establish a database connection (name the database \"lab.db\"), and create a database cursor."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "qIDBNaIt5rKm",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "conn = sqlite3.connect('lab.db')\n",
        "cursor = conn.cursor()"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "TQUmLlpj5xOc",
        "colab_type": "text"
      },
      "source": [
        "#### 5. Add the csvs stored as variables in prompt 3 to lab.db using the .to_sql.() method"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "yUnGCOx36DMJ",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "patients.to_sql(\"patients\", conn, if_exists=\"replace\", index = False)\n",
        "admissions.to_sql(\"admissions\", conn, if_exists=\"replace\", index = False)\n",
        "prescriptions.to_sql(\"prescriptions\", conn, if_exists=\"replace\", index = False)\n",
        "services.to_sql(\"services\", conn, if_exists=\"replace\", index = False)\n",
        "diagnosis_icds.to_sql(\"diagnosis_icds\", conn, if_exists=\"replace\", index = False)\n",
        "diagnosis_events.to_sql(\"diagnosis_events\", conn, if_exists=\"replace\", index = False)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "q4Fm1buB7Wj_",
        "colab_type": "text"
      },
      "source": [
        "#### 6. Write an sql query that pulls the icd9_code and short_title columns from the table created from the d_icd_diagnoses csv."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "WP13E8z07Xll",
        "colab_type": "code",
        "outputId": "9155162e-ef70-4776-a7c9-bd52a262f376",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 419
        }
      },
      "source": [
        "pd.read_sql_query('SELECT icd9_code, short_title from diagnosis_icds', conn)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>icd9_code</th>\n",
              "      <th>short_title</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>01716</td>\n",
              "      <td>Erythem nod tb-oth test</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>01720</td>\n",
              "      <td>TB periph lymph-unspec</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>01721</td>\n",
              "      <td>TB periph lymph-no exam</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>01722</td>\n",
              "      <td>TB periph lymph-exam unk</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>01723</td>\n",
              "      <td>TB periph lymph-micro dx</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>14562</th>\n",
              "      <td>V8712</td>\n",
              "      <td>Contact/exposure benzene</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>14563</th>\n",
              "      <td>V8719</td>\n",
              "      <td>Cont/exp haz aromat NEC</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>14564</th>\n",
              "      <td>V872</td>\n",
              "      <td>Cont/exp hazard chem NEC</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>14565</th>\n",
              "      <td>V8731</td>\n",
              "      <td>Contact/exposure mold</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>14566</th>\n",
              "      <td>V8732</td>\n",
              "      <td>Contact/exp algae bloom</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>14567 rows × 2 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "      icd9_code               short_title\n",
              "0         01716   Erythem nod tb-oth test\n",
              "1         01720    TB periph lymph-unspec\n",
              "2         01721   TB periph lymph-no exam\n",
              "3         01722  TB periph lymph-exam unk\n",
              "4         01723  TB periph lymph-micro dx\n",
              "...         ...                       ...\n",
              "14562     V8712  Contact/exposure benzene\n",
              "14563     V8719   Cont/exp haz aromat NEC\n",
              "14564      V872  Cont/exp hazard chem NEC\n",
              "14565     V8731     Contact/exposure mold\n",
              "14566     V8732   Contact/exp algae bloom\n",
              "\n",
              "[14567 rows x 2 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 7
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "n13KUzy97X4A",
        "colab_type": "text"
      },
      "source": [
        "#### 7. Write a query that results in the number of subjects per each generic drug name, and order by route (descending). "
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "zqIth57ljHXq",
        "colab_type": "code",
        "outputId": "13b99f4c-79f5-4e77-8c11-bc267223617f",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 419
        }
      },
      "source": [
        "pd.read_sql_query('''SELECT route, drug_name_generic, COUNT(subject_id) FROM prescriptions \n",
        "WHERE dose_unit_rx = \"mg\"\n",
        "AND dose_val_rx >= 400\n",
        "GROUP BY drug_name_generic\n",
        "ORDER BY route DESC\n",
        ";''', conn)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>route</th>\n",
              "      <th>drug_name_generic</th>\n",
              "      <th>COUNT(subject_id)</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>SC</td>\n",
              "      <td>Enoxaparin Sodium</td>\n",
              "      <td>15</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>SC</td>\n",
              "      <td>Sumatriptan Succinate</td>\n",
              "      <td>2</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>PR</td>\n",
              "      <td>Acetaminophen (Rectal)</td>\n",
              "      <td>16</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>PO/NG</td>\n",
              "      <td>Acetaminophen</td>\n",
              "      <td>43</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>PO/NG</td>\n",
              "      <td>Acetaminophen (Liquid)</td>\n",
              "      <td>18</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>148</th>\n",
              "      <td>IV</td>\n",
              "      <td>Morphine Sulfate (Syringe)</td>\n",
              "      <td>3</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>149</th>\n",
              "      <td>IV</td>\n",
              "      <td>Ondansetron</td>\n",
              "      <td>9</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>150</th>\n",
              "      <td>IV</td>\n",
              "      <td>Prochlorperazine</td>\n",
              "      <td>2</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>151</th>\n",
              "      <td>IV</td>\n",
              "      <td>Vancomycin</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>152</th>\n",
              "      <td>IM</td>\n",
              "      <td>Haloperidol</td>\n",
              "      <td>5</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>153 rows × 3 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "     route           drug_name_generic  COUNT(subject_id)\n",
              "0       SC           Enoxaparin Sodium                 15\n",
              "1       SC       Sumatriptan Succinate                  2\n",
              "2       PR      Acetaminophen (Rectal)                 16\n",
              "3    PO/NG               Acetaminophen                 43\n",
              "4    PO/NG      Acetaminophen (Liquid)                 18\n",
              "..     ...                         ...                ...\n",
              "148     IV  Morphine Sulfate (Syringe)                  3\n",
              "149     IV                 Ondansetron                  9\n",
              "150     IV            Prochlorperazine                  2\n",
              "151     IV                  Vancomycin                  1\n",
              "152     IM                 Haloperidol                  5\n",
              "\n",
              "[153 rows x 3 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 17
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "T3UAsHuMjYA-",
        "colab_type": "text"
      },
      "source": [
        "#### 8. Surgury-related event (hint: surgeries are noted in the services table), find all of the diagonse names (short titles) tied to these events. Display only emergency admission types."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "77pxYNe8lIUc",
        "colab_type": "code",
        "outputId": "91c5a7d5-7dcf-4adb-b5cd-f58815b24821",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 419
        }
      },
      "source": [
        "pd.read_sql_query('''SELECT curr_service, services.subject_id, short_title\n",
        "FROM services\n",
        "LEFT JOIN admissions on services.subject_id = admissions.subject_id\n",
        "LEFT JOIN diagnosis_events on services.subject_id = diagnosis_events.subject_id\n",
        "LEFT JOIN diagnosis_icds on diagnosis_events.icd9_code = diagnosis_icds.icd9_code\n",
        "WHERE services.curr_service LIKE \"%SURG%\" AND admission_type = \"EMERGENCY\";''', conn)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>curr_service</th>\n",
              "      <th>subject_id</th>\n",
              "      <th>short_title</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>SURG</td>\n",
              "      <td>10033</td>\n",
              "      <td>Tox multnod goit no cris</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>SURG</td>\n",
              "      <td>10033</td>\n",
              "      <td>DMII wo cmp nt st uncntr</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>SURG</td>\n",
              "      <td>10033</td>\n",
              "      <td>Pure hypercholesterolem</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>SURG</td>\n",
              "      <td>10033</td>\n",
              "      <td>Hyperpotassemia</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>SURG</td>\n",
              "      <td>10033</td>\n",
              "      <td>Depressive disorder NEC</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4533</th>\n",
              "      <td>SURG</td>\n",
              "      <td>44228</td>\n",
              "      <td>Anemia NOS</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4534</th>\n",
              "      <td>SURG</td>\n",
              "      <td>44228</td>\n",
              "      <td>Phlbts sprfc vn up extrm</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4535</th>\n",
              "      <td>SURG</td>\n",
              "      <td>44228</td>\n",
              "      <td>Pneumonia, organism NOS</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4536</th>\n",
              "      <td>SURG</td>\n",
              "      <td>44228</td>\n",
              "      <td>Septic shock</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4537</th>\n",
              "      <td>SURG</td>\n",
              "      <td>44228</td>\n",
              "      <td>Severe sepsis</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>4538 rows × 3 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "     curr_service  subject_id               short_title\n",
              "0            SURG       10033  Tox multnod goit no cris\n",
              "1            SURG       10033  DMII wo cmp nt st uncntr\n",
              "2            SURG       10033   Pure hypercholesterolem\n",
              "3            SURG       10033           Hyperpotassemia\n",
              "4            SURG       10033   Depressive disorder NEC\n",
              "...           ...         ...                       ...\n",
              "4533         SURG       44228                Anemia NOS\n",
              "4534         SURG       44228  Phlbts sprfc vn up extrm\n",
              "4535         SURG       44228   Pneumonia, organism NOS\n",
              "4536         SURG       44228              Septic shock\n",
              "4537         SURG       44228             Severe sepsis\n",
              "\n",
              "[4538 rows x 3 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 28
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "krggQrjwIYBt",
        "colab_type": "text"
      },
      "source": [
        "9. Write a query that will return diagnoses that appear more than 15 times. "
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "zZZf-rAAIgw0",
        "colab_type": "code",
        "outputId": "d14be350-0cb5-459b-c565-5e6c6f2650e6",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 731
        }
      },
      "source": [
        "pd.read_sql('''SELECT icd9_code, COUNT(icd9_code)\n",
        "FROM diagnosis_events\n",
        "GROUP BY icd9_code\n",
        "HAVING COUNT(icd9_code) > 15''', conn)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>icd9_code</th>\n",
              "      <th>COUNT(icd9_code)</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>0389</td>\n",
              "      <td>24</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2449</td>\n",
              "      <td>23</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>25000</td>\n",
              "      <td>31</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>2724</td>\n",
              "      <td>29</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>2760</td>\n",
              "      <td>17</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>2761</td>\n",
              "      <td>17</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>2762</td>\n",
              "      <td>16</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>2859</td>\n",
              "      <td>25</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>311</td>\n",
              "      <td>16</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>4019</td>\n",
              "      <td>53</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>10</th>\n",
              "      <td>41401</td>\n",
              "      <td>18</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>11</th>\n",
              "      <td>42731</td>\n",
              "      <td>48</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>12</th>\n",
              "      <td>4280</td>\n",
              "      <td>39</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>13</th>\n",
              "      <td>486</td>\n",
              "      <td>26</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>14</th>\n",
              "      <td>5070</td>\n",
              "      <td>16</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>15</th>\n",
              "      <td>51881</td>\n",
              "      <td>31</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>16</th>\n",
              "      <td>53081</td>\n",
              "      <td>16</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>17</th>\n",
              "      <td>5849</td>\n",
              "      <td>45</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>18</th>\n",
              "      <td>5990</td>\n",
              "      <td>27</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>19</th>\n",
              "      <td>78552</td>\n",
              "      <td>16</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>20</th>\n",
              "      <td>99592</td>\n",
              "      <td>25</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>21</th>\n",
              "      <td>V5861</td>\n",
              "      <td>18</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   icd9_code  COUNT(icd9_code)\n",
              "0       0389                24\n",
              "1       2449                23\n",
              "2      25000                31\n",
              "3       2724                29\n",
              "4       2760                17\n",
              "5       2761                17\n",
              "6       2762                16\n",
              "7       2859                25\n",
              "8        311                16\n",
              "9       4019                53\n",
              "10     41401                18\n",
              "11     42731                48\n",
              "12      4280                39\n",
              "13       486                26\n",
              "14      5070                16\n",
              "15     51881                31\n",
              "16     53081                16\n",
              "17      5849                45\n",
              "18      5990                27\n",
              "19     78552                16\n",
              "20     99592                25\n",
              "21     V5861                18"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 31
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "26eyFcJy2Xdb",
        "colab_type": "text"
      },
      "source": [
        "#### 10. Write a query that compares ethnicity and insurance type in some way. Compare your inqueries and results with others in your working group."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "qj5u-xJY3KfU",
        "colab_type": "code",
        "outputId": "58bf0842-04b7-43f3-85d9-d79808bf1c93",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 514
        }
      },
      "source": [
        "#This example compares the distribution of insurance types across represented \n",
        "#ethnicities in the database\n",
        "pd.read_sql('''SELECT ethnicity, insurance, COUNT(insurance)\n",
        "FROM admissions\n",
        "GROUP BY ethnicity, insurance;''', conn)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>ethnicity</th>\n",
              "      <th>insurance</th>\n",
              "      <th>COUNT(insurance)</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>AMERICAN INDIAN/ALASKA NATIVE FEDERALLY RECOGN...</td>\n",
              "      <td>Medicaid</td>\n",
              "      <td>2</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>ASIAN</td>\n",
              "      <td>Medicare</td>\n",
              "      <td>2</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>BLACK/AFRICAN AMERICAN</td>\n",
              "      <td>Medicare</td>\n",
              "      <td>7</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>HISPANIC OR LATINO</td>\n",
              "      <td>Medicare</td>\n",
              "      <td>2</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>HISPANIC/LATINO - PUERTO RICAN</td>\n",
              "      <td>Medicare</td>\n",
              "      <td>15</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>OTHER</td>\n",
              "      <td>Medicare</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>OTHER</td>\n",
              "      <td>Private</td>\n",
              "      <td>2</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>UNABLE TO OBTAIN</td>\n",
              "      <td>Medicare</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>UNKNOWN/NOT SPECIFIED</td>\n",
              "      <td>Medicaid</td>\n",
              "      <td>2</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>UNKNOWN/NOT SPECIFIED</td>\n",
              "      <td>Medicare</td>\n",
              "      <td>4</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>10</th>\n",
              "      <td>UNKNOWN/NOT SPECIFIED</td>\n",
              "      <td>Private</td>\n",
              "      <td>5</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>11</th>\n",
              "      <td>WHITE</td>\n",
              "      <td>Government</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>12</th>\n",
              "      <td>WHITE</td>\n",
              "      <td>Medicaid</td>\n",
              "      <td>2</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>13</th>\n",
              "      <td>WHITE</td>\n",
              "      <td>Medicare</td>\n",
              "      <td>66</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>14</th>\n",
              "      <td>WHITE</td>\n",
              "      <td>Private</td>\n",
              "      <td>17</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                                            ethnicity  ... COUNT(insurance)\n",
              "0   AMERICAN INDIAN/ALASKA NATIVE FEDERALLY RECOGN...  ...                2\n",
              "1                                               ASIAN  ...                2\n",
              "2                              BLACK/AFRICAN AMERICAN  ...                7\n",
              "3                                  HISPANIC OR LATINO  ...                2\n",
              "4                      HISPANIC/LATINO - PUERTO RICAN  ...               15\n",
              "5                                               OTHER  ...                1\n",
              "6                                               OTHER  ...                2\n",
              "7                                    UNABLE TO OBTAIN  ...                1\n",
              "8                               UNKNOWN/NOT SPECIFIED  ...                2\n",
              "9                               UNKNOWN/NOT SPECIFIED  ...                4\n",
              "10                              UNKNOWN/NOT SPECIFIED  ...                5\n",
              "11                                              WHITE  ...                1\n",
              "12                                              WHITE  ...                2\n",
              "13                                              WHITE  ...               66\n",
              "14                                              WHITE  ...               17\n",
              "\n",
              "[15 rows x 3 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 40
        }
      ]
    }
  ]
}