Medications_For_Cohort_Dataset.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas\n",
    "import os\n",
    "\n",
    "# This query represents dataset \"D0--Ibuprofen_Dataset\" for domain \"drug\" and was generated for All of Us Registered Tier Dataset v7\n",
    "dataset_69704064_drug_sql = \"\"\"\n",
    "    SELECT\n",
    "        d_exposure.person_id,\n",
    "        d_exposure.drug_concept_id,\n",
    "        d_standard_concept.concept_name as standard_concept_name,\n",
    "        d_standard_concept.concept_code as standard_concept_code,\n",
    "        d_standard_concept.vocabulary_id as standard_vocabulary,\n",
    "        d_exposure.drug_exposure_start_datetime,\n",
    "        d_exposure.drug_exposure_end_datetime,\n",
    "        d_exposure.verbatim_end_date,\n",
    "        d_exposure.drug_type_concept_id,\n",
    "        d_type.concept_name as drug_type_concept_name,\n",
    "        d_exposure.stop_reason,\n",
    "        d_exposure.refills,\n",
    "        d_exposure.quantity,\n",
    "        d_exposure.days_supply,\n",
    "        d_exposure.sig,\n",
    "        d_exposure.route_concept_id,\n",
    "        d_route.concept_name as route_concept_name,\n",
    "        d_exposure.lot_number,\n",
    "        d_exposure.visit_occurrence_id,\n",
    "        d_visit.concept_name as visit_occurrence_concept_name,\n",
    "        d_exposure.drug_source_value,\n",
    "        d_exposure.drug_source_concept_id,\n",
    "        d_source_concept.concept_name as source_concept_name,\n",
    "        d_source_concept.concept_code as source_concept_code,\n",
    "        d_source_concept.vocabulary_id as source_vocabulary,\n",
    "        d_exposure.route_source_value,\n",
    "        d_exposure.dose_unit_source_value \n",
    "    FROM\n",
    "        ( SELECT\n",
    "            * \n",
    "        FROM\n",
    "            `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".drug_exposure` d_exposure \n",
    "        WHERE\n",
    "            (\n",
    "                drug_concept_id IN  (\n",
    "                    SELECT\n",
    "                        DISTINCT ca.descendant_id \n",
    "                    FROM\n",
    "                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria_ancestor` ca \n",
    "                    JOIN\n",
    "                        (\n",
    "                            select\n",
    "                                distinct c.concept_id \n",
    "                            FROM\n",
    "                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` c \n",
    "                            JOIN\n",
    "                                (\n",
    "                                    select\n",
    "                                        cast(cr.id as string) as id \n",
    "                                    FROM\n",
    "                                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` cr \n",
    "                                    WHERE\n",
    "                                        concept_id IN (\n",
    "                                            1177480\n",
    "                                        ) \n",
    "                                        AND full_text LIKE '%_rank1]%'\n",
    "                                ) a \n",
    "                                    ON (\n",
    "                                        c.path LIKE CONCAT('%.',\n",
    "                                    a.id,\n",
    "                                    '.%') \n",
    "                                    OR c.path LIKE CONCAT('%.',\n",
    "                                    a.id) \n",
    "                                    OR c.path LIKE CONCAT(a.id,\n",
    "                                    '.%') \n",
    "                                    OR c.path = a.id) \n",
    "                                WHERE\n",
    "                                    is_standard = 1 \n",
    "                                    AND is_selectable = 1\n",
    "                                ) b \n",
    "                                    ON (\n",
    "                                        ca.ancestor_id = b.concept_id\n",
    "                                    )\n",
    "                            )\n",
    "                        )  \n",
    "                        AND (\n",
    "                            d_exposure.PERSON_ID IN (\n",
    "                                SELECT\n",
    "                                    distinct person_id  \n",
    "                            FROM\n",
    "                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_search_person` cb_search_person  \n",
    "                            WHERE\n",
    "                                cb_search_person.person_id IN (\n",
    "                                    SELECT\n",
    "                                        criteria.person_id \n",
    "                                    FROM\n",
    "                                        (SELECT\n",
    "                                            DISTINCT person_id,\n",
    "                                            entry_date,\n",
    "                                            concept_id \n",
    "                                        FROM\n",
    "                                            `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_search_all_events` \n",
    "                                        WHERE\n",
    "                                            (\n",
    "                                                concept_id IN (\n",
    "                                                    SELECT\n",
    "                                                        DISTINCT ca.descendant_id \n",
    "                                                    FROM\n",
    "                                                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria_ancestor` ca \n",
    "                                                    JOIN\n",
    "                                                        (\n",
    "                                                            select\n",
    "                                                                distinct c.concept_id \n",
    "                                                            FROM\n",
    "                                                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` c \n",
    "                                                            JOIN\n",
    "                                                                (\n",
    "                                                                    select\n",
    "                                                                        cast(cr.id as string) as id \n",
    "                                                                    FROM\n",
    "                                                                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` cr \n",
    "                                                                    WHERE\n",
    "                                                                        concept_id IN (21604254) \n",
    "                                                                        AND full_text LIKE '%_rank1]%'\n",
    "                                                                ) a \n",
    "                                                                    ON (\n",
    "                                                                        c.path LIKE CONCAT('%.',\n",
    "                                                                    a.id,\n",
    "                                                                    '.%') \n",
    "                                                                    OR c.path LIKE CONCAT('%.',\n",
    "                                                                    a.id) \n",
    "                                                                    OR c.path LIKE CONCAT(a.id,\n",
    "                                                                    '.%') \n",
    "                                                                    OR c.path = a.id) \n",
    "                                                                WHERE\n",
    "                                                                    is_standard = 1 \n",
    "                                                                    AND is_selectable = 1\n",
    "                                                                ) b \n",
    "                                                                    ON (\n",
    "                                                                        ca.ancestor_id = b.concept_id\n",
    "                                                                    )\n",
    "                                                            ) \n",
    "                                                            AND is_standard = 1\n",
    "                                                        )\n",
    "                                                ) criteria \n",
    "                                            ) \n",
    "                                            AND cb_search_person.person_id NOT IN (\n",
    "                                                SELECT\n",
    "                                                    criteria.person_id \n",
    "                                            FROM\n",
    "                                                (SELECT\n",
    "                                                    DISTINCT person_id,\n",
    "                                                    entry_date,\n",
    "                                                    concept_id \n",
    "                                                FROM\n",
    "                                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_search_all_events` \n",
    "                                                WHERE\n",
    "                                                    (\n",
    "                                                        concept_id IN (\n",
    "                                                            SELECT\n",
    "                                                                DISTINCT c.concept_id \n",
    "                                                            FROM\n",
    "                                                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` c \n",
    "                                                            JOIN\n",
    "                                                                (\n",
    "                                                                    select\n",
    "                                                                        cast(cr.id as string) as id \n",
    "                                                                    FROM\n",
    "                                                                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` cr \n",
    "                                                                    WHERE\n",
    "                                                                        concept_id IN (40493428, 252840, 4092524, 4178964, 76349, 4177243, 4313083, 4241905, 46273652, 196925, 4180791, 436045, 200962, 4091466, 4162860, 4294416, 4092212, 444411, 4154630, 442139, 4309225, 4298032, 40650479, 197807, 4091465, 40490994, 4216273, 133424, 4247726, 4247822, 4181483, 4162994, 4309851, 436671, 4180780, 4300555, 4160780, 4180910, 4247238, 4155297, 4178971, 443568, 75210, 442183, 40492021, 78987, 443388, 195483, 4155285, 4181488, 25189, 4180902, 36676291, 376918, 4297666, 4157332, 40488919, 380661, 256633, 133711, 200348, 4174593, 433143, 4178977, 4178959, 443387, 40480128, 432263, 4312698, 4315805, 4112853, 4297185, 4181333, 442181, 46270738, 4178974, 4246450, 442131, 443391, 444410, 137809, 4181330, 4313916, 40487047, 192581, 4314047, 4092217, 4181351, 37397344, 4112745, 257503, 438693, 139750, 4181338, 198091, 436357, 196359, 432257, 4246451, 258375, 200052, 40488964, 256646, 436353, 438094, 4312023, 31509, 201238, 37310458, 4095312, 138996, 4162253, 378087, 435484, 374874, 36684472, 4314071, 4312288, 261514, 4181332, 44806773, 196360, 4188544, 4298026, 443392, 4295624, 432845, 45769720, 197507, 4233629, 4241917, 199754, 4160276, 440649, 443588, 4089655, 4092511, 439751, 432837, 4095740, 197808, 4162251, 200051, 4310566, 4187850, 26052, 4187849, 4311637, 4246017, 440658, 4247719, 4312685, 4241904, 4181480, 4311342, 4116084, 4111921, 36683531, 441805, 4298028, 437498, 4054503, 4298033, 192568, 4246127, 4312022, 4081044, 197500, 373425, 4338758, 443398, 376647, 4308621, 198988, 40491001, 40492037, 40490993, 4180793, 4181484, 4247338, 4297665, 441515, 74582, 442132, 261236, 4188545, 4180790, 4247331, 133726, 433716, 258369, 136917, 440345, 200959, 320342, 434298, 4334322, 4181350, 40492932, 4178968, 198700, 380055, 440956, 4111776, 4311619, 4247836, 4155171, 432833, 4111805, 433435, 201519, 438386, 4178976, 192855, 4157457, 75512, 4311617, 4157456, 24897, 45770892, 4187848, 80045, 4116241, 45768522, 4311499, 72266, 255507, 193144, 30346, 4315806, 443561, 28083, 4001666, 4089665, 4089756, 439404, 435755, 42536893, 4312944, 443390, 40493020, 4187851, 441233, 4248061, 4247357, 4157454, 4313482, 438699, 318096, 4112974, 432254, 4158563, 4091464, 436635, 198985, 40482750, 254591, 4181343, 197804, 4151250, 40487143, 443386, 46270513, 4157449, 4181354, 4181339, 133147, 443389, 4092382, 45757101, 4313634, 4307263, 4092512, 194589, 253717, 136354, 764225, 195513, 4153882, 4157331, 4214901, 4181477) \n",
    "                                                                        AND full_text LIKE '%_rank1]%'\n",
    "                                                                ) a \n",
    "                                                                    ON (\n",
    "                                                                        c.path LIKE CONCAT('%.',\n",
    "                                                                    a.id,\n",
    "                                                                    '.%') \n",
    "                                                                    OR c.path LIKE CONCAT('%.',\n",
    "                                                                    a.id) \n",
    "                                                                    OR c.path LIKE CONCAT(a.id,\n",
    "                                                                    '.%') \n",
    "                                                                    OR c.path = a.id) \n",
    "                                                                WHERE\n",
    "                                                                    is_standard = 1 \n",
    "                                                                    AND is_selectable = 1\n",
    "                                                                ) \n",
    "                                                                AND is_standard = 1 \n",
    "                                                        )\n",
    "                                                    ) criteria \n",
    "                                                ) ))\n",
    "                                    ) d_exposure \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_standard_concept \n",
    "                                        ON d_exposure.drug_concept_id = d_standard_concept.concept_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_type \n",
    "                                        ON d_exposure.drug_type_concept_id = d_type.concept_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_route \n",
    "                                        ON d_exposure.route_concept_id = d_route.concept_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".visit_occurrence` v \n",
    "                                        ON d_exposure.visit_occurrence_id = v.visit_occurrence_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_visit \n",
    "                                        ON v.visit_concept_id = d_visit.concept_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_source_concept \n",
    "                                        ON d_exposure.drug_source_concept_id = d_source_concept.concept_id\"\"\"\n",
    "\n",
    "dataset_69704064_drug_df = pandas.read_gbq(\n",
    "    dataset_69704064_drug_sql,\n",
    "    dialect=\"standard\",\n",
    "    use_bqstorage_api=(\"BIGQUERY_STORAGE_API_ENABLED\" in os.environ),\n",
    "    progress_bar_type=\"tqdm_notebook\")\n",
    "\n",
    "dataset_69704064_drug_df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas\n",
    "import os\n",
    "\n",
    "# This query represents dataset \"D1_Buprenorphine_Data_Set\" for domain \"drug\" and was generated for All of Us Registered Tier Dataset v7\n",
    "dataset_14776048_drug_sql = \"\"\"\n",
    "    SELECT\n",
    "        d_exposure.person_id,\n",
    "        d_exposure.drug_concept_id,\n",
    "        d_standard_concept.concept_name as standard_concept_name,\n",
    "        d_standard_concept.concept_code as standard_concept_code,\n",
    "        d_standard_concept.vocabulary_id as standard_vocabulary,\n",
    "        d_exposure.drug_exposure_start_datetime,\n",
    "        d_exposure.drug_exposure_end_datetime,\n",
    "        d_exposure.verbatim_end_date,\n",
    "        d_exposure.drug_type_concept_id,\n",
    "        d_type.concept_name as drug_type_concept_name,\n",
    "        d_exposure.stop_reason,\n",
    "        d_exposure.refills,\n",
    "        d_exposure.quantity,\n",
    "        d_exposure.days_supply,\n",
    "        d_exposure.sig,\n",
    "        d_exposure.route_concept_id,\n",
    "        d_route.concept_name as route_concept_name,\n",
    "        d_exposure.lot_number,\n",
    "        d_exposure.visit_occurrence_id,\n",
    "        d_visit.concept_name as visit_occurrence_concept_name,\n",
    "        d_exposure.drug_source_value,\n",
    "        d_exposure.drug_source_concept_id,\n",
    "        d_source_concept.concept_name as source_concept_name,\n",
    "        d_source_concept.concept_code as source_concept_code,\n",
    "        d_source_concept.vocabulary_id as source_vocabulary,\n",
    "        d_exposure.route_source_value,\n",
    "        d_exposure.dose_unit_source_value \n",
    "    FROM\n",
    "        ( SELECT\n",
    "            * \n",
    "        FROM\n",
    "            `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".drug_exposure` d_exposure \n",
    "        WHERE\n",
    "            (\n",
    "                drug_concept_id IN  (\n",
    "                    SELECT\n",
    "                        DISTINCT ca.descendant_id \n",
    "                    FROM\n",
    "                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria_ancestor` ca \n",
    "                    JOIN\n",
    "                        (\n",
    "                            select\n",
    "                                distinct c.concept_id \n",
    "                            FROM\n",
    "                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` c \n",
    "                            JOIN\n",
    "                                (\n",
    "                                    select\n",
    "                                        cast(cr.id as string) as id \n",
    "                                    FROM\n",
    "                                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` cr \n",
    "                                    WHERE\n",
    "                                        concept_id IN (\n",
    "                                            1133201\n",
    "                                        ) \n",
    "                                        AND full_text LIKE '%_rank1]%'\n",
    "                                ) a \n",
    "                                    ON (\n",
    "                                        c.path LIKE CONCAT('%.',\n",
    "                                    a.id,\n",
    "                                    '.%') \n",
    "                                    OR c.path LIKE CONCAT('%.',\n",
    "                                    a.id) \n",
    "                                    OR c.path LIKE CONCAT(a.id,\n",
    "                                    '.%') \n",
    "                                    OR c.path = a.id) \n",
    "                                WHERE\n",
    "                                    is_standard = 1 \n",
    "                                    AND is_selectable = 1\n",
    "                                ) b \n",
    "                                    ON (\n",
    "                                        ca.ancestor_id = b.concept_id\n",
    "                                    )\n",
    "                            )\n",
    "                        )  \n",
    "                        AND (\n",
    "                            d_exposure.PERSON_ID IN (\n",
    "                                SELECT\n",
    "                                    distinct person_id  \n",
    "                            FROM\n",
    "                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_search_person` cb_search_person  \n",
    "                            WHERE\n",
    "                                cb_search_person.person_id IN (\n",
    "                                    SELECT\n",
    "                                        criteria.person_id \n",
    "                                    FROM\n",
    "                                        (SELECT\n",
    "                                            DISTINCT person_id,\n",
    "                                            entry_date,\n",
    "                                            concept_id \n",
    "                                        FROM\n",
    "                                            `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_search_all_events` \n",
    "                                        WHERE\n",
    "                                            (\n",
    "                                                concept_id IN (\n",
    "                                                    SELECT\n",
    "                                                        DISTINCT ca.descendant_id \n",
    "                                                    FROM\n",
    "                                                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria_ancestor` ca \n",
    "                                                    JOIN\n",
    "                                                        (\n",
    "                                                            select\n",
    "                                                                distinct c.concept_id \n",
    "                                                            FROM\n",
    "                                                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` c \n",
    "                                                            JOIN\n",
    "                                                                (\n",
    "                                                                    select\n",
    "                                                                        cast(cr.id as string) as id \n",
    "                                                                    FROM\n",
    "                                                                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` cr \n",
    "                                                                    WHERE\n",
    "                                                                        concept_id IN (21604254) \n",
    "                                                                        AND full_text LIKE '%_rank1]%'\n",
    "                                                                ) a \n",
    "                                                                    ON (\n",
    "                                                                        c.path LIKE CONCAT('%.',\n",
    "                                                                    a.id,\n",
    "                                                                    '.%') \n",
    "                                                                    OR c.path LIKE CONCAT('%.',\n",
    "                                                                    a.id) \n",
    "                                                                    OR c.path LIKE CONCAT(a.id,\n",
    "                                                                    '.%') \n",
    "                                                                    OR c.path = a.id) \n",
    "                                                                WHERE\n",
    "                                                                    is_standard = 1 \n",
    "                                                                    AND is_selectable = 1\n",
    "                                                                ) b \n",
    "                                                                    ON (\n",
    "                                                                        ca.ancestor_id = b.concept_id\n",
    "                                                                    )\n",
    "                                                            ) \n",
    "                                                            AND is_standard = 1\n",
    "                                                        )\n",
    "                                                ) criteria \n",
    "                                            ) \n",
    "                                            AND cb_search_person.person_id NOT IN (\n",
    "                                                SELECT\n",
    "                                                    criteria.person_id \n",
    "                                            FROM\n",
    "                                                (SELECT\n",
    "                                                    DISTINCT person_id,\n",
    "                                                    entry_date,\n",
    "                                                    concept_id \n",
    "                                                FROM\n",
    "                                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_search_all_events` \n",
    "                                                WHERE\n",
    "                                                    (\n",
    "                                                        concept_id IN (\n",
    "                                                            SELECT\n",
    "                                                                DISTINCT c.concept_id \n",
    "                                                            FROM\n",
    "                                                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` c \n",
    "                                                            JOIN\n",
    "                                                                (\n",
    "                                                                    select\n",
    "                                                                        cast(cr.id as string) as id \n",
    "                                                                    FROM\n",
    "                                                                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` cr \n",
    "                                                                    WHERE\n",
    "                                                                        concept_id IN (40493428, 252840, 4092524, 4178964, 76349, 4177243, 4313083, 4241905, 46273652, 196925, 4180791, 436045, 200962, 4091466, 4162860, 4294416, 4092212, 444411, 4154630, 442139, 4309225, 4298032, 40650479, 197807, 4091465, 40490994, 4216273, 133424, 4247726, 4247822, 4181483, 4162994, 4309851, 436671, 4180780, 4300555, 4160780, 4180910, 4247238, 4155297, 4178971, 443568, 75210, 442183, 40492021, 78987, 443388, 195483, 4155285, 4181488, 25189, 4180902, 36676291, 376918, 4297666, 4157332, 40488919, 380661, 256633, 133711, 200348, 4174593, 433143, 4178977, 4178959, 443387, 40480128, 432263, 4312698, 4315805, 4112853, 4297185, 4181333, 442181, 46270738, 4178974, 4246450, 442131, 443391, 444410, 137809, 4181330, 4313916, 40487047, 192581, 4314047, 4092217, 4181351, 37397344, 4112745, 257503, 438693, 139750, 4181338, 198091, 436357, 196359, 432257, 4246451, 258375, 200052, 40488964, 256646, 436353, 438094, 4312023, 31509, 201238, 37310458, 4095312, 138996, 4162253, 378087, 435484, 374874, 36684472, 4314071, 4312288, 261514, 4181332, 44806773, 196360, 4188544, 4298026, 443392, 4295624, 432845, 45769720, 197507, 4233629, 4241917, 199754, 4160276, 440649, 443588, 4089655, 4092511, 439751, 432837, 4095740, 197808, 4162251, 200051, 4310566, 4187850, 26052, 4187849, 4311637, 4246017, 440658, 4247719, 4312685, 4241904, 4181480, 4311342, 4116084, 4111921, 36683531, 441805, 4298028, 437498, 4054503, 4298033, 192568, 4246127, 4312022, 4081044, 197500, 373425, 4338758, 443398, 376647, 4308621, 198988, 40491001, 40492037, 40490993, 4180793, 4181484, 4247338, 4297665, 441515, 74582, 442132, 261236, 4188545, 4180790, 4247331, 133726, 433716, 258369, 136917, 440345, 200959, 320342, 434298, 4334322, 4181350, 40492932, 4178968, 198700, 380055, 440956, 4111776, 4311619, 4247836, 4155171, 432833, 4111805, 433435, 201519, 438386, 4178976, 192855, 4157457, 75512, 4311617, 4157456, 24897, 45770892, 4187848, 80045, 4116241, 45768522, 4311499, 72266, 255507, 193144, 30346, 4315806, 443561, 28083, 4001666, 4089665, 4089756, 439404, 435755, 42536893, 4312944, 443390, 40493020, 4187851, 441233, 4248061, 4247357, 4157454, 4313482, 438699, 318096, 4112974, 432254, 4158563, 4091464, 436635, 198985, 40482750, 254591, 4181343, 197804, 4151250, 40487143, 443386, 46270513, 4157449, 4181354, 4181339, 133147, 443389, 4092382, 45757101, 4313634, 4307263, 4092512, 194589, 253717, 136354, 764225, 195513, 4153882, 4157331, 4214901, 4181477) \n",
    "                                                                        AND full_text LIKE '%_rank1]%'\n",
    "                                                                ) a \n",
    "                                                                    ON (\n",
    "                                                                        c.path LIKE CONCAT('%.',\n",
    "                                                                    a.id,\n",
    "                                                                    '.%') \n",
    "                                                                    OR c.path LIKE CONCAT('%.',\n",
    "                                                                    a.id) \n",
    "                                                                    OR c.path LIKE CONCAT(a.id,\n",
    "                                                                    '.%') \n",
    "                                                                    OR c.path = a.id) \n",
    "                                                                WHERE\n",
    "                                                                    is_standard = 1 \n",
    "                                                                    AND is_selectable = 1\n",
    "                                                                ) \n",
    "                                                                AND is_standard = 1 \n",
    "                                                        )\n",
    "                                                    ) criteria \n",
    "                                                ) ))\n",
    "                                    ) d_exposure \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_standard_concept \n",
    "                                        ON d_exposure.drug_concept_id = d_standard_concept.concept_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_type \n",
    "                                        ON d_exposure.drug_type_concept_id = d_type.concept_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_route \n",
    "                                        ON d_exposure.route_concept_id = d_route.concept_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".visit_occurrence` v \n",
    "                                        ON d_exposure.visit_occurrence_id = v.visit_occurrence_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_visit \n",
    "                                        ON v.visit_concept_id = d_visit.concept_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_source_concept \n",
    "                                        ON d_exposure.drug_source_concept_id = d_source_concept.concept_id\"\"\"\n",
    "\n",
    "dataset_14776048_drug_df = pandas.read_gbq(\n",
    "    dataset_14776048_drug_sql,\n",
    "    dialect=\"standard\",\n",
    "    use_bqstorage_api=(\"BIGQUERY_STORAGE_API_ENABLED\" in os.environ),\n",
    "    progress_bar_type=\"tqdm_notebook\")\n",
    "\n",
    "dataset_14776048_drug_df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas\n",
    "import os\n",
    "\n",
    "# This query represents dataset \"D2--Fentanyl_Data_Set\" for domain \"drug\" and was generated for All of Us Registered Tier Dataset v7\n",
    "dataset_48873884_drug_sql = \"\"\"\n",
    "    SELECT\n",
    "        d_exposure.person_id,\n",
    "        d_exposure.drug_concept_id,\n",
    "        d_standard_concept.concept_name as standard_concept_name,\n",
    "        d_standard_concept.concept_code as standard_concept_code,\n",
    "        d_standard_concept.vocabulary_id as standard_vocabulary,\n",
    "        d_exposure.drug_exposure_start_datetime,\n",
    "        d_exposure.drug_exposure_end_datetime,\n",
    "        d_exposure.verbatim_end_date,\n",
    "        d_exposure.drug_type_concept_id,\n",
    "        d_type.concept_name as drug_type_concept_name,\n",
    "        d_exposure.stop_reason,\n",
    "        d_exposure.refills,\n",
    "        d_exposure.quantity,\n",
    "        d_exposure.days_supply,\n",
    "        d_exposure.sig,\n",
    "        d_exposure.route_concept_id,\n",
    "        d_route.concept_name as route_concept_name,\n",
    "        d_exposure.lot_number,\n",
    "        d_exposure.visit_occurrence_id,\n",
    "        d_visit.concept_name as visit_occurrence_concept_name,\n",
    "        d_exposure.drug_source_value,\n",
    "        d_exposure.drug_source_concept_id,\n",
    "        d_source_concept.concept_name as source_concept_name,\n",
    "        d_source_concept.concept_code as source_concept_code,\n",
    "        d_source_concept.vocabulary_id as source_vocabulary,\n",
    "        d_exposure.route_source_value,\n",
    "        d_exposure.dose_unit_source_value \n",
    "    FROM\n",
    "        ( SELECT\n",
    "            * \n",
    "        FROM\n",
    "            `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".drug_exposure` d_exposure \n",
    "        WHERE\n",
    "            (\n",
    "                drug_concept_id IN  (\n",
    "                    SELECT\n",
    "                        DISTINCT ca.descendant_id \n",
    "                    FROM\n",
    "                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria_ancestor` ca \n",
    "                    JOIN\n",
    "                        (\n",
    "                            select\n",
    "                                distinct c.concept_id \n",
    "                            FROM\n",
    "                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` c \n",
    "                            JOIN\n",
    "                                (\n",
    "                                    select\n",
    "                                        cast(cr.id as string) as id \n",
    "                                    FROM\n",
    "                                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` cr \n",
    "                                    WHERE\n",
    "                                        concept_id IN (\n",
    "                                            1154029\n",
    "                                        ) \n",
    "                                        AND full_text LIKE '%_rank1]%'\n",
    "                                ) a \n",
    "                                    ON (\n",
    "                                        c.path LIKE CONCAT('%.',\n",
    "                                    a.id,\n",
    "                                    '.%') \n",
    "                                    OR c.path LIKE CONCAT('%.',\n",
    "                                    a.id) \n",
    "                                    OR c.path LIKE CONCAT(a.id,\n",
    "                                    '.%') \n",
    "                                    OR c.path = a.id) \n",
    "                                WHERE\n",
    "                                    is_standard = 1 \n",
    "                                    AND is_selectable = 1\n",
    "                                ) b \n",
    "                                    ON (\n",
    "                                        ca.ancestor_id = b.concept_id\n",
    "                                    )\n",
    "                            )\n",
    "                        )  \n",
    "                        AND (\n",
    "                            d_exposure.PERSON_ID IN (\n",
    "                                SELECT\n",
    "                                    distinct person_id  \n",
    "                            FROM\n",
    "                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_search_person` cb_search_person  \n",
    "                            WHERE\n",
    "                                cb_search_person.person_id IN (\n",
    "                                    SELECT\n",
    "                                        criteria.person_id \n",
    "                                    FROM\n",
    "                                        (SELECT\n",
    "                                            DISTINCT person_id,\n",
    "                                            entry_date,\n",
    "                                            concept_id \n",
    "                                        FROM\n",
    "                                            `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_search_all_events` \n",
    "                                        WHERE\n",
    "                                            (\n",
    "                                                concept_id IN (\n",
    "                                                    SELECT\n",
    "                                                        DISTINCT ca.descendant_id \n",
    "                                                    FROM\n",
    "                                                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria_ancestor` ca \n",
    "                                                    JOIN\n",
    "                                                        (\n",
    "                                                            select\n",
    "                                                                distinct c.concept_id \n",
    "                                                            FROM\n",
    "                                                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` c \n",
    "                                                            JOIN\n",
    "                                                                (\n",
    "                                                                    select\n",
    "                                                                        cast(cr.id as string) as id \n",
    "                                                                    FROM\n",
    "                                                                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` cr \n",
    "                                                                    WHERE\n",
    "                                                                        concept_id IN (21604254) \n",
    "                                                                        AND full_text LIKE '%_rank1]%'\n",
    "                                                                ) a \n",
    "                                                                    ON (\n",
    "                                                                        c.path LIKE CONCAT('%.',\n",
    "                                                                    a.id,\n",
    "                                                                    '.%') \n",
    "                                                                    OR c.path LIKE CONCAT('%.',\n",
    "                                                                    a.id) \n",
    "                                                                    OR c.path LIKE CONCAT(a.id,\n",
    "                                                                    '.%') \n",
    "                                                                    OR c.path = a.id) \n",
    "                                                                WHERE\n",
    "                                                                    is_standard = 1 \n",
    "                                                                    AND is_selectable = 1\n",
    "                                                                ) b \n",
    "                                                                    ON (\n",
    "                                                                        ca.ancestor_id = b.concept_id\n",
    "                                                                    )\n",
    "                                                            ) \n",
    "                                                            AND is_standard = 1\n",
    "                                                        )\n",
    "                                                ) criteria \n",
    "                                            ) \n",
    "                                            AND cb_search_person.person_id NOT IN (\n",
    "                                                SELECT\n",
    "                                                    criteria.person_id \n",
    "                                            FROM\n",
    "                                                (SELECT\n",
    "                                                    DISTINCT person_id,\n",
    "                                                    entry_date,\n",
    "                                                    concept_id \n",
    "                                                FROM\n",
    "                                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_search_all_events` \n",
    "                                                WHERE\n",
    "                                                    (\n",
    "                                                        concept_id IN (\n",
    "                                                            SELECT\n",
    "                                                                DISTINCT c.concept_id \n",
    "                                                            FROM\n",
    "                                                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` c \n",
    "                                                            JOIN\n",
    "                                                                (\n",
    "                                                                    select\n",
    "                                                                        cast(cr.id as string) as id \n",
    "                                                                    FROM\n",
    "                                                                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` cr \n",
    "                                                                    WHERE\n",
    "                                                                        concept_id IN (40493428, 252840, 4092524, 4178964, 76349, 4177243, 4313083, 4241905, 46273652, 196925, 4180791, 436045, 200962, 4091466, 4162860, 4294416, 4092212, 444411, 4154630, 442139, 4309225, 4298032, 40650479, 197807, 4091465, 40490994, 4216273, 133424, 4247726, 4247822, 4181483, 4162994, 4309851, 436671, 4180780, 4300555, 4160780, 4180910, 4247238, 4155297, 4178971, 443568, 75210, 442183, 40492021, 78987, 443388, 195483, 4155285, 4181488, 25189, 4180902, 36676291, 376918, 4297666, 4157332, 40488919, 380661, 256633, 133711, 200348, 4174593, 433143, 4178977, 4178959, 443387, 40480128, 432263, 4312698, 4315805, 4112853, 4297185, 4181333, 442181, 46270738, 4178974, 4246450, 442131, 443391, 444410, 137809, 4181330, 4313916, 40487047, 192581, 4314047, 4092217, 4181351, 37397344, 4112745, 257503, 438693, 139750, 4181338, 198091, 436357, 196359, 432257, 4246451, 258375, 200052, 40488964, 256646, 436353, 438094, 4312023, 31509, 201238, 37310458, 4095312, 138996, 4162253, 378087, 435484, 374874, 36684472, 4314071, 4312288, 261514, 4181332, 44806773, 196360, 4188544, 4298026, 443392, 4295624, 432845, 45769720, 197507, 4233629, 4241917, 199754, 4160276, 440649, 443588, 4089655, 4092511, 439751, 432837, 4095740, 197808, 4162251, 200051, 4310566, 4187850, 26052, 4187849, 4311637, 4246017, 440658, 4247719, 4312685, 4241904, 4181480, 4311342, 4116084, 4111921, 36683531, 441805, 4298028, 437498, 4054503, 4298033, 192568, 4246127, 4312022, 4081044, 197500, 373425, 4338758, 443398, 376647, 4308621, 198988, 40491001, 40492037, 40490993, 4180793, 4181484, 4247338, 4297665, 441515, 74582, 442132, 261236, 4188545, 4180790, 4247331, 133726, 433716, 258369, 136917, 440345, 200959, 320342, 434298, 4334322, 4181350, 40492932, 4178968, 198700, 380055, 440956, 4111776, 4311619, 4247836, 4155171, 432833, 4111805, 433435, 201519, 438386, 4178976, 192855, 4157457, 75512, 4311617, 4157456, 24897, 45770892, 4187848, 80045, 4116241, 45768522, 4311499, 72266, 255507, 193144, 30346, 4315806, 443561, 28083, 4001666, 4089665, 4089756, 439404, 435755, 42536893, 4312944, 443390, 40493020, 4187851, 441233, 4248061, 4247357, 4157454, 4313482, 438699, 318096, 4112974, 432254, 4158563, 4091464, 436635, 198985, 40482750, 254591, 4181343, 197804, 4151250, 40487143, 443386, 46270513, 4157449, 4181354, 4181339, 133147, 443389, 4092382, 45757101, 4313634, 4307263, 4092512, 194589, 253717, 136354, 764225, 195513, 4153882, 4157331, 4214901, 4181477) \n",
    "                                                                        AND full_text LIKE '%_rank1]%'\n",
    "                                                                ) a \n",
    "                                                                    ON (\n",
    "                                                                        c.path LIKE CONCAT('%.',\n",
    "                                                                    a.id,\n",
    "                                                                    '.%') \n",
    "                                                                    OR c.path LIKE CONCAT('%.',\n",
    "                                                                    a.id) \n",
    "                                                                    OR c.path LIKE CONCAT(a.id,\n",
    "                                                                    '.%') \n",
    "                                                                    OR c.path = a.id) \n",
    "                                                                WHERE\n",
    "                                                                    is_standard = 1 \n",
    "                                                                    AND is_selectable = 1\n",
    "                                                                ) \n",
    "                                                                AND is_standard = 1 \n",
    "                                                        )\n",
    "                                                    ) criteria \n",
    "                                                ) ))\n",
    "                                    ) d_exposure \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_standard_concept \n",
    "                                        ON d_exposure.drug_concept_id = d_standard_concept.concept_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_type \n",
    "                                        ON d_exposure.drug_type_concept_id = d_type.concept_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_route \n",
    "                                        ON d_exposure.route_concept_id = d_route.concept_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".visit_occurrence` v \n",
    "                                        ON d_exposure.visit_occurrence_id = v.visit_occurrence_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_visit \n",
    "                                        ON v.visit_concept_id = d_visit.concept_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_source_concept \n",
    "                                        ON d_exposure.drug_source_concept_id = d_source_concept.concept_id\"\"\"\n",
    "\n",
    "dataset_48873884_drug_df = pandas.read_gbq(\n",
    "    dataset_48873884_drug_sql,\n",
    "    dialect=\"standard\",\n",
    "    use_bqstorage_api=(\"BIGQUERY_STORAGE_API_ENABLED\" in os.environ),\n",
    "    progress_bar_type=\"tqdm_notebook\")\n",
    "\n",
    "dataset_48873884_drug_df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas\n",
    "import os\n",
    "\n",
    "# This query represents dataset \"D3--Morphine_Data_Set\" for domain \"drug\" and was generated for All of Us Registered Tier Dataset v7\n",
    "dataset_31655263_drug_sql = \"\"\"\n",
    "    SELECT\n",
    "        d_exposure.person_id,\n",
    "        d_exposure.drug_concept_id,\n",
    "        d_standard_concept.concept_name as standard_concept_name,\n",
    "        d_standard_concept.concept_code as standard_concept_code,\n",
    "        d_standard_concept.vocabulary_id as standard_vocabulary,\n",
    "        d_exposure.drug_exposure_start_datetime,\n",
    "        d_exposure.drug_exposure_end_datetime,\n",
    "        d_exposure.verbatim_end_date,\n",
    "        d_exposure.drug_type_concept_id,\n",
    "        d_type.concept_name as drug_type_concept_name,\n",
    "        d_exposure.stop_reason,\n",
    "        d_exposure.refills,\n",
    "        d_exposure.quantity,\n",
    "        d_exposure.days_supply,\n",
    "        d_exposure.sig,\n",
    "        d_exposure.route_concept_id,\n",
    "        d_route.concept_name as route_concept_name,\n",
    "        d_exposure.lot_number,\n",
    "        d_exposure.visit_occurrence_id,\n",
    "        d_visit.concept_name as visit_occurrence_concept_name,\n",
    "        d_exposure.drug_source_value,\n",
    "        d_exposure.drug_source_concept_id,\n",
    "        d_source_concept.concept_name as source_concept_name,\n",
    "        d_source_concept.concept_code as source_concept_code,\n",
    "        d_source_concept.vocabulary_id as source_vocabulary,\n",
    "        d_exposure.route_source_value,\n",
    "        d_exposure.dose_unit_source_value \n",
    "    FROM\n",
    "        ( SELECT\n",
    "            * \n",
    "        FROM\n",
    "            `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".drug_exposure` d_exposure \n",
    "        WHERE\n",
    "            (\n",
    "                drug_concept_id IN  (\n",
    "                    SELECT\n",
    "                        DISTINCT ca.descendant_id \n",
    "                    FROM\n",
    "                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria_ancestor` ca \n",
    "                    JOIN\n",
    "                        (\n",
    "                            select\n",
    "                                distinct c.concept_id \n",
    "                            FROM\n",
    "                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` c \n",
    "                            JOIN\n",
    "                                (\n",
    "                                    select\n",
    "                                        cast(cr.id as string) as id \n",
    "                                    FROM\n",
    "                                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` cr \n",
    "                                    WHERE\n",
    "                                        concept_id IN (\n",
    "                                            1110410\n",
    "                                        ) \n",
    "                                        AND full_text LIKE '%_rank1]%'\n",
    "                                ) a \n",
    "                                    ON (\n",
    "                                        c.path LIKE CONCAT('%.',\n",
    "                                    a.id,\n",
    "                                    '.%') \n",
    "                                    OR c.path LIKE CONCAT('%.',\n",
    "                                    a.id) \n",
    "                                    OR c.path LIKE CONCAT(a.id,\n",
    "                                    '.%') \n",
    "                                    OR c.path = a.id) \n",
    "                                WHERE\n",
    "                                    is_standard = 1 \n",
    "                                    AND is_selectable = 1\n",
    "                                ) b \n",
    "                                    ON (\n",
    "                                        ca.ancestor_id = b.concept_id\n",
    "                                    )\n",
    "                            )\n",
    "                        )  \n",
    "                        AND (\n",
    "                            d_exposure.PERSON_ID IN (\n",
    "                                SELECT\n",
    "                                    distinct person_id  \n",
    "                            FROM\n",
    "                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_search_person` cb_search_person  \n",
    "                            WHERE\n",
    "                                cb_search_person.person_id IN (\n",
    "                                    SELECT\n",
    "                                        criteria.person_id \n",
    "                                    FROM\n",
    "                                        (SELECT\n",
    "                                            DISTINCT person_id,\n",
    "                                            entry_date,\n",
    "                                            concept_id \n",
    "                                        FROM\n",
    "                                            `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_search_all_events` \n",
    "                                        WHERE\n",
    "                                            (\n",
    "                                                concept_id IN (\n",
    "                                                    SELECT\n",
    "                                                        DISTINCT ca.descendant_id \n",
    "                                                    FROM\n",
    "                                                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria_ancestor` ca \n",
    "                                                    JOIN\n",
    "                                                        (\n",
    "                                                            select\n",
    "                                                                distinct c.concept_id \n",
    "                                                            FROM\n",
    "                                                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` c \n",
    "                                                            JOIN\n",
    "                                                                (\n",
    "                                                                    select\n",
    "                                                                        cast(cr.id as string) as id \n",
    "                                                                    FROM\n",
    "                                                                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` cr \n",
    "                                                                    WHERE\n",
    "                                                                        concept_id IN (21604254) \n",
    "                                                                        AND full_text LIKE '%_rank1]%'\n",
    "                                                                ) a \n",
    "                                                                    ON (\n",
    "                                                                        c.path LIKE CONCAT('%.',\n",
    "                                                                    a.id,\n",
    "                                                                    '.%') \n",
    "                                                                    OR c.path LIKE CONCAT('%.',\n",
    "                                                                    a.id) \n",
    "                                                                    OR c.path LIKE CONCAT(a.id,\n",
    "                                                                    '.%') \n",
    "                                                                    OR c.path = a.id) \n",
    "                                                                WHERE\n",
    "                                                                    is_standard = 1 \n",
    "                                                                    AND is_selectable = 1\n",
    "                                                                ) b \n",
    "                                                                    ON (\n",
    "                                                                        ca.ancestor_id = b.concept_id\n",
    "                                                                    )\n",
    "                                                            ) \n",
    "                                                            AND is_standard = 1\n",
    "                                                        )\n",
    "                                                ) criteria \n",
    "                                            ) \n",
    "                                            AND cb_search_person.person_id NOT IN (\n",
    "                                                SELECT\n",
    "                                                    criteria.person_id \n",
    "                                            FROM\n",
    "                                                (SELECT\n",
    "                                                    DISTINCT person_id,\n",
    "                                                    entry_date,\n",
    "                                                    concept_id \n",
    "                                                FROM\n",
    "                                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_search_all_events` \n",
    "                                                WHERE\n",
    "                                                    (\n",
    "                                                        concept_id IN (\n",
    "                                                            SELECT\n",
    "                                                                DISTINCT c.concept_id \n",
    "                                                            FROM\n",
    "                                                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` c \n",
    "                                                            JOIN\n",
    "                                                                (\n",
    "                                                                    select\n",
    "                                                                        cast(cr.id as string) as id \n",
    "                                                                    FROM\n",
    "                                                                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` cr \n",
    "                                                                    WHERE\n",
    "                                                                        concept_id IN (40493428, 252840, 4092524, 4178964, 76349, 4177243, 4313083, 4241905, 46273652, 196925, 4180791, 436045, 200962, 4091466, 4162860, 4294416, 4092212, 444411, 4154630, 442139, 4309225, 4298032, 40650479, 197807, 4091465, 40490994, 4216273, 133424, 4247726, 4247822, 4181483, 4162994, 4309851, 436671, 4180780, 4300555, 4160780, 4180910, 4247238, 4155297, 4178971, 443568, 75210, 442183, 40492021, 78987, 443388, 195483, 4155285, 4181488, 25189, 4180902, 36676291, 376918, 4297666, 4157332, 40488919, 380661, 256633, 133711, 200348, 4174593, 433143, 4178977, 4178959, 443387, 40480128, 432263, 4312698, 4315805, 4112853, 4297185, 4181333, 442181, 46270738, 4178974, 4246450, 442131, 443391, 444410, 137809, 4181330, 4313916, 40487047, 192581, 4314047, 4092217, 4181351, 37397344, 4112745, 257503, 438693, 139750, 4181338, 198091, 436357, 196359, 432257, 4246451, 258375, 200052, 40488964, 256646, 436353, 438094, 4312023, 31509, 201238, 37310458, 4095312, 138996, 4162253, 378087, 435484, 374874, 36684472, 4314071, 4312288, 261514, 4181332, 44806773, 196360, 4188544, 4298026, 443392, 4295624, 432845, 45769720, 197507, 4233629, 4241917, 199754, 4160276, 440649, 443588, 4089655, 4092511, 439751, 432837, 4095740, 197808, 4162251, 200051, 4310566, 4187850, 26052, 4187849, 4311637, 4246017, 440658, 4247719, 4312685, 4241904, 4181480, 4311342, 4116084, 4111921, 36683531, 441805, 4298028, 437498, 4054503, 4298033, 192568, 4246127, 4312022, 4081044, 197500, 373425, 4338758, 443398, 376647, 4308621, 198988, 40491001, 40492037, 40490993, 4180793, 4181484, 4247338, 4297665, 441515, 74582, 442132, 261236, 4188545, 4180790, 4247331, 133726, 433716, 258369, 136917, 440345, 200959, 320342, 434298, 4334322, 4181350, 40492932, 4178968, 198700, 380055, 440956, 4111776, 4311619, 4247836, 4155171, 432833, 4111805, 433435, 201519, 438386, 4178976, 192855, 4157457, 75512, 4311617, 4157456, 24897, 45770892, 4187848, 80045, 4116241, 45768522, 4311499, 72266, 255507, 193144, 30346, 4315806, 443561, 28083, 4001666, 4089665, 4089756, 439404, 435755, 42536893, 4312944, 443390, 40493020, 4187851, 441233, 4248061, 4247357, 4157454, 4313482, 438699, 318096, 4112974, 432254, 4158563, 4091464, 436635, 198985, 40482750, 254591, 4181343, 197804, 4151250, 40487143, 443386, 46270513, 4157449, 4181354, 4181339, 133147, 443389, 4092382, 45757101, 4313634, 4307263, 4092512, 194589, 253717, 136354, 764225, 195513, 4153882, 4157331, 4214901, 4181477) \n",
    "                                                                        AND full_text LIKE '%_rank1]%'\n",
    "                                                                ) a \n",
    "                                                                    ON (\n",
    "                                                                        c.path LIKE CONCAT('%.',\n",
    "                                                                    a.id,\n",
    "                                                                    '.%') \n",
    "                                                                    OR c.path LIKE CONCAT('%.',\n",
    "                                                                    a.id) \n",
    "                                                                    OR c.path LIKE CONCAT(a.id,\n",
    "                                                                    '.%') \n",
    "                                                                    OR c.path = a.id) \n",
    "                                                                WHERE\n",
    "                                                                    is_standard = 1 \n",
    "                                                                    AND is_selectable = 1\n",
    "                                                                ) \n",
    "                                                                AND is_standard = 1 \n",
    "                                                        )\n",
    "                                                    ) criteria \n",
    "                                                ) ))\n",
    "                                    ) d_exposure \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_standard_concept \n",
    "                                        ON d_exposure.drug_concept_id = d_standard_concept.concept_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_type \n",
    "                                        ON d_exposure.drug_type_concept_id = d_type.concept_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_route \n",
    "                                        ON d_exposure.route_concept_id = d_route.concept_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".visit_occurrence` v \n",
    "                                        ON d_exposure.visit_occurrence_id = v.visit_occurrence_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_visit \n",
    "                                        ON v.visit_concept_id = d_visit.concept_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_source_concept \n",
    "                                        ON d_exposure.drug_source_concept_id = d_source_concept.concept_id\"\"\"\n",
    "\n",
    "dataset_31655263_drug_df = pandas.read_gbq(\n",
    "    dataset_31655263_drug_sql,\n",
    "    dialect=\"standard\",\n",
    "    use_bqstorage_api=(\"BIGQUERY_STORAGE_API_ENABLED\" in os.environ),\n",
    "    progress_bar_type=\"tqdm_notebook\")\n",
    "\n",
    "dataset_31655263_drug_df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas\n",
    "import os\n",
    "\n",
    "# This query represents dataset \"D4--Oxycodone_Data_Set\" for domain \"drug\" and was generated for All of Us Registered Tier Dataset v7\n",
    "dataset_07881621_drug_sql = \"\"\"\n",
    "    SELECT\n",
    "        d_exposure.person_id,\n",
    "        d_exposure.drug_concept_id,\n",
    "        d_standard_concept.concept_name as standard_concept_name,\n",
    "        d_standard_concept.concept_code as standard_concept_code,\n",
    "        d_standard_concept.vocabulary_id as standard_vocabulary,\n",
    "        d_exposure.drug_exposure_start_datetime,\n",
    "        d_exposure.drug_exposure_end_datetime,\n",
    "        d_exposure.verbatim_end_date,\n",
    "        d_exposure.drug_type_concept_id,\n",
    "        d_type.concept_name as drug_type_concept_name,\n",
    "        d_exposure.stop_reason,\n",
    "        d_exposure.refills,\n",
    "        d_exposure.quantity,\n",
    "        d_exposure.days_supply,\n",
    "        d_exposure.sig,\n",
    "        d_exposure.route_concept_id,\n",
    "        d_route.concept_name as route_concept_name,\n",
    "        d_exposure.lot_number,\n",
    "        d_exposure.visit_occurrence_id,\n",
    "        d_visit.concept_name as visit_occurrence_concept_name,\n",
    "        d_exposure.drug_source_value,\n",
    "        d_exposure.drug_source_concept_id,\n",
    "        d_source_concept.concept_name as source_concept_name,\n",
    "        d_source_concept.concept_code as source_concept_code,\n",
    "        d_source_concept.vocabulary_id as source_vocabulary,\n",
    "        d_exposure.route_source_value,\n",
    "        d_exposure.dose_unit_source_value \n",
    "    FROM\n",
    "        ( SELECT\n",
    "            * \n",
    "        FROM\n",
    "            `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".drug_exposure` d_exposure \n",
    "        WHERE\n",
    "            (\n",
    "                drug_concept_id IN  (\n",
    "                    SELECT\n",
    "                        DISTINCT ca.descendant_id \n",
    "                    FROM\n",
    "                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria_ancestor` ca \n",
    "                    JOIN\n",
    "                        (\n",
    "                            select\n",
    "                                distinct c.concept_id \n",
    "                            FROM\n",
    "                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` c \n",
    "                            JOIN\n",
    "                                (\n",
    "                                    select\n",
    "                                        cast(cr.id as string) as id \n",
    "                                    FROM\n",
    "                                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` cr \n",
    "                                    WHERE\n",
    "                                        concept_id IN (\n",
    "                                            1124957\n",
    "                                        ) \n",
    "                                        AND full_text LIKE '%_rank1]%'\n",
    "                                ) a \n",
    "                                    ON (\n",
    "                                        c.path LIKE CONCAT('%.',\n",
    "                                    a.id,\n",
    "                                    '.%') \n",
    "                                    OR c.path LIKE CONCAT('%.',\n",
    "                                    a.id) \n",
    "                                    OR c.path LIKE CONCAT(a.id,\n",
    "                                    '.%') \n",
    "                                    OR c.path = a.id) \n",
    "                                WHERE\n",
    "                                    is_standard = 1 \n",
    "                                    AND is_selectable = 1\n",
    "                                ) b \n",
    "                                    ON (\n",
    "                                        ca.ancestor_id = b.concept_id\n",
    "                                    )\n",
    "                            )\n",
    "                        )  \n",
    "                        AND (\n",
    "                            d_exposure.PERSON_ID IN (\n",
    "                                SELECT\n",
    "                                    distinct person_id  \n",
    "                            FROM\n",
    "                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_search_person` cb_search_person  \n",
    "                            WHERE\n",
    "                                cb_search_person.person_id IN (\n",
    "                                    SELECT\n",
    "                                        criteria.person_id \n",
    "                                    FROM\n",
    "                                        (SELECT\n",
    "                                            DISTINCT person_id,\n",
    "                                            entry_date,\n",
    "                                            concept_id \n",
    "                                        FROM\n",
    "                                            `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_search_all_events` \n",
    "                                        WHERE\n",
    "                                            (\n",
    "                                                concept_id IN (\n",
    "                                                    SELECT\n",
    "                                                        DISTINCT ca.descendant_id \n",
    "                                                    FROM\n",
    "                                                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria_ancestor` ca \n",
    "                                                    JOIN\n",
    "                                                        (\n",
    "                                                            select\n",
    "                                                                distinct c.concept_id \n",
    "                                                            FROM\n",
    "                                                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` c \n",
    "                                                            JOIN\n",
    "                                                                (\n",
    "                                                                    select\n",
    "                                                                        cast(cr.id as string) as id \n",
    "                                                                    FROM\n",
    "                                                                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` cr \n",
    "                                                                    WHERE\n",
    "                                                                        concept_id IN (21604254) \n",
    "                                                                        AND full_text LIKE '%_rank1]%'\n",
    "                                                                ) a \n",
    "                                                                    ON (\n",
    "                                                                        c.path LIKE CONCAT('%.',\n",
    "                                                                    a.id,\n",
    "                                                                    '.%') \n",
    "                                                                    OR c.path LIKE CONCAT('%.',\n",
    "                                                                    a.id) \n",
    "                                                                    OR c.path LIKE CONCAT(a.id,\n",
    "                                                                    '.%') \n",
    "                                                                    OR c.path = a.id) \n",
    "                                                                WHERE\n",
    "                                                                    is_standard = 1 \n",
    "                                                                    AND is_selectable = 1\n",
    "                                                                ) b \n",
    "                                                                    ON (\n",
    "                                                                        ca.ancestor_id = b.concept_id\n",
    "                                                                    )\n",
    "                                                            ) \n",
    "                                                            AND is_standard = 1\n",
    "                                                        )\n",
    "                                                ) criteria \n",
    "                                            ) \n",
    "                                            AND cb_search_person.person_id NOT IN (\n",
    "                                                SELECT\n",
    "                                                    criteria.person_id \n",
    "                                            FROM\n",
    "                                                (SELECT\n",
    "                                                    DISTINCT person_id,\n",
    "                                                    entry_date,\n",
    "                                                    concept_id \n",
    "                                                FROM\n",
    "                                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_search_all_events` \n",
    "                                                WHERE\n",
    "                                                    (\n",
    "                                                        concept_id IN (\n",
    "                                                            SELECT\n",
    "                                                                DISTINCT c.concept_id \n",
    "                                                            FROM\n",
    "                                                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` c \n",
    "                                                            JOIN\n",
    "                                                                (\n",
    "                                                                    select\n",
    "                                                                        cast(cr.id as string) as id \n",
    "                                                                    FROM\n",
    "                                                                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` cr \n",
    "                                                                    WHERE\n",
    "                                                                        concept_id IN (40493428, 252840, 4092524, 4178964, 76349, 4177243, 4313083, 4241905, 46273652, 196925, 4180791, 436045, 200962, 4091466, 4162860, 4294416, 4092212, 444411, 4154630, 442139, 4309225, 4298032, 40650479, 197807, 4091465, 40490994, 4216273, 133424, 4247726, 4247822, 4181483, 4162994, 4309851, 436671, 4180780, 4300555, 4160780, 4180910, 4247238, 4155297, 4178971, 443568, 75210, 442183, 40492021, 78987, 443388, 195483, 4155285, 4181488, 25189, 4180902, 36676291, 376918, 4297666, 4157332, 40488919, 380661, 256633, 133711, 200348, 4174593, 433143, 4178977, 4178959, 443387, 40480128, 432263, 4312698, 4315805, 4112853, 4297185, 4181333, 442181, 46270738, 4178974, 4246450, 442131, 443391, 444410, 137809, 4181330, 4313916, 40487047, 192581, 4314047, 4092217, 4181351, 37397344, 4112745, 257503, 438693, 139750, 4181338, 198091, 436357, 196359, 432257, 4246451, 258375, 200052, 40488964, 256646, 436353, 438094, 4312023, 31509, 201238, 37310458, 4095312, 138996, 4162253, 378087, 435484, 374874, 36684472, 4314071, 4312288, 261514, 4181332, 44806773, 196360, 4188544, 4298026, 443392, 4295624, 432845, 45769720, 197507, 4233629, 4241917, 199754, 4160276, 440649, 443588, 4089655, 4092511, 439751, 432837, 4095740, 197808, 4162251, 200051, 4310566, 4187850, 26052, 4187849, 4311637, 4246017, 440658, 4247719, 4312685, 4241904, 4181480, 4311342, 4116084, 4111921, 36683531, 441805, 4298028, 437498, 4054503, 4298033, 192568, 4246127, 4312022, 4081044, 197500, 373425, 4338758, 443398, 376647, 4308621, 198988, 40491001, 40492037, 40490993, 4180793, 4181484, 4247338, 4297665, 441515, 74582, 442132, 261236, 4188545, 4180790, 4247331, 133726, 433716, 258369, 136917, 440345, 200959, 320342, 434298, 4334322, 4181350, 40492932, 4178968, 198700, 380055, 440956, 4111776, 4311619, 4247836, 4155171, 432833, 4111805, 433435, 201519, 438386, 4178976, 192855, 4157457, 75512, 4311617, 4157456, 24897, 45770892, 4187848, 80045, 4116241, 45768522, 4311499, 72266, 255507, 193144, 30346, 4315806, 443561, 28083, 4001666, 4089665, 4089756, 439404, 435755, 42536893, 4312944, 443390, 40493020, 4187851, 441233, 4248061, 4247357, 4157454, 4313482, 438699, 318096, 4112974, 432254, 4158563, 4091464, 436635, 198985, 40482750, 254591, 4181343, 197804, 4151250, 40487143, 443386, 46270513, 4157449, 4181354, 4181339, 133147, 443389, 4092382, 45757101, 4313634, 4307263, 4092512, 194589, 253717, 136354, 764225, 195513, 4153882, 4157331, 4214901, 4181477) \n",
    "                                                                        AND full_text LIKE '%_rank1]%'\n",
    "                                                                ) a \n",
    "                                                                    ON (\n",
    "                                                                        c.path LIKE CONCAT('%.',\n",
    "                                                                    a.id,\n",
    "                                                                    '.%') \n",
    "                                                                    OR c.path LIKE CONCAT('%.',\n",
    "                                                                    a.id) \n",
    "                                                                    OR c.path LIKE CONCAT(a.id,\n",
    "                                                                    '.%') \n",
    "                                                                    OR c.path = a.id) \n",
    "                                                                WHERE\n",
    "                                                                    is_standard = 1 \n",
    "                                                                    AND is_selectable = 1\n",
    "                                                                ) \n",
    "                                                                AND is_standard = 1 \n",
    "                                                        )\n",
    "                                                    ) criteria \n",
    "                                                ) ))\n",
    "                                    ) d_exposure \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_standard_concept \n",
    "                                        ON d_exposure.drug_concept_id = d_standard_concept.concept_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_type \n",
    "                                        ON d_exposure.drug_type_concept_id = d_type.concept_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_route \n",
    "                                        ON d_exposure.route_concept_id = d_route.concept_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".visit_occurrence` v \n",
    "                                        ON d_exposure.visit_occurrence_id = v.visit_occurrence_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_visit \n",
    "                                        ON v.visit_concept_id = d_visit.concept_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_source_concept \n",
    "                                        ON d_exposure.drug_source_concept_id = d_source_concept.concept_id\"\"\"\n",
    "\n",
    "dataset_07881621_drug_df = pandas.read_gbq(\n",
    "    dataset_07881621_drug_sql,\n",
    "    dialect=\"standard\",\n",
    "    use_bqstorage_api=(\"BIGQUERY_STORAGE_API_ENABLED\" in os.environ),\n",
    "    progress_bar_type=\"tqdm_notebook\")\n",
    "\n",
    "dataset_07881621_drug_df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas\n",
    "import os\n",
    "\n",
    "# This query represents dataset \"D5--Hydromorphone_Data_Set\" for domain \"drug\" and was generated for All of Us Registered Tier Dataset v7\n",
    "dataset_50368594_drug_sql = \"\"\"\n",
    "    SELECT\n",
    "        d_exposure.person_id,\n",
    "        d_exposure.drug_concept_id,\n",
    "        d_standard_concept.concept_name as standard_concept_name,\n",
    "        d_standard_concept.concept_code as standard_concept_code,\n",
    "        d_standard_concept.vocabulary_id as standard_vocabulary,\n",
    "        d_exposure.drug_exposure_start_datetime,\n",
    "        d_exposure.drug_exposure_end_datetime,\n",
    "        d_exposure.verbatim_end_date,\n",
    "        d_exposure.drug_type_concept_id,\n",
    "        d_type.concept_name as drug_type_concept_name,\n",
    "        d_exposure.stop_reason,\n",
    "        d_exposure.refills,\n",
    "        d_exposure.quantity,\n",
    "        d_exposure.days_supply,\n",
    "        d_exposure.sig,\n",
    "        d_exposure.route_concept_id,\n",
    "        d_route.concept_name as route_concept_name,\n",
    "        d_exposure.lot_number,\n",
    "        d_exposure.visit_occurrence_id,\n",
    "        d_visit.concept_name as visit_occurrence_concept_name,\n",
    "        d_exposure.drug_source_value,\n",
    "        d_exposure.drug_source_concept_id,\n",
    "        d_source_concept.concept_name as source_concept_name,\n",
    "        d_source_concept.concept_code as source_concept_code,\n",
    "        d_source_concept.vocabulary_id as source_vocabulary,\n",
    "        d_exposure.route_source_value,\n",
    "        d_exposure.dose_unit_source_value \n",
    "    FROM\n",
    "        ( SELECT\n",
    "            * \n",
    "        FROM\n",
    "            `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".drug_exposure` d_exposure \n",
    "        WHERE\n",
    "            (\n",
    "                drug_concept_id IN  (\n",
    "                    SELECT\n",
    "                        DISTINCT ca.descendant_id \n",
    "                    FROM\n",
    "                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria_ancestor` ca \n",
    "                    JOIN\n",
    "                        (\n",
    "                            select\n",
    "                                distinct c.concept_id \n",
    "                            FROM\n",
    "                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` c \n",
    "                            JOIN\n",
    "                                (\n",
    "                                    select\n",
    "                                        cast(cr.id as string) as id \n",
    "                                    FROM\n",
    "                                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` cr \n",
    "                                    WHERE\n",
    "                                        concept_id IN (\n",
    "                                            1126658\n",
    "                                        ) \n",
    "                                        AND full_text LIKE '%_rank1]%'\n",
    "                                ) a \n",
    "                                    ON (\n",
    "                                        c.path LIKE CONCAT('%.',\n",
    "                                    a.id,\n",
    "                                    '.%') \n",
    "                                    OR c.path LIKE CONCAT('%.',\n",
    "                                    a.id) \n",
    "                                    OR c.path LIKE CONCAT(a.id,\n",
    "                                    '.%') \n",
    "                                    OR c.path = a.id) \n",
    "                                WHERE\n",
    "                                    is_standard = 1 \n",
    "                                    AND is_selectable = 1\n",
    "                                ) b \n",
    "                                    ON (\n",
    "                                        ca.ancestor_id = b.concept_id\n",
    "                                    )\n",
    "                            )\n",
    "                        )  \n",
    "                        AND (\n",
    "                            d_exposure.PERSON_ID IN (\n",
    "                                SELECT\n",
    "                                    distinct person_id  \n",
    "                            FROM\n",
    "                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_search_person` cb_search_person  \n",
    "                            WHERE\n",
    "                                cb_search_person.person_id IN (\n",
    "                                    SELECT\n",
    "                                        criteria.person_id \n",
    "                                    FROM\n",
    "                                        (SELECT\n",
    "                                            DISTINCT person_id,\n",
    "                                            entry_date,\n",
    "                                            concept_id \n",
    "                                        FROM\n",
    "                                            `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_search_all_events` \n",
    "                                        WHERE\n",
    "                                            (\n",
    "                                                concept_id IN (\n",
    "                                                    SELECT\n",
    "                                                        DISTINCT ca.descendant_id \n",
    "                                                    FROM\n",
    "                                                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria_ancestor` ca \n",
    "                                                    JOIN\n",
    "                                                        (\n",
    "                                                            select\n",
    "                                                                distinct c.concept_id \n",
    "                                                            FROM\n",
    "                                                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` c \n",
    "                                                            JOIN\n",
    "                                                                (\n",
    "                                                                    select\n",
    "                                                                        cast(cr.id as string) as id \n",
    "                                                                    FROM\n",
    "                                                                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` cr \n",
    "                                                                    WHERE\n",
    "                                                                        concept_id IN (21604254) \n",
    "                                                                        AND full_text LIKE '%_rank1]%'\n",
    "                                                                ) a \n",
    "                                                                    ON (\n",
    "                                                                        c.path LIKE CONCAT('%.',\n",
    "                                                                    a.id,\n",
    "                                                                    '.%') \n",
    "                                                                    OR c.path LIKE CONCAT('%.',\n",
    "                                                                    a.id) \n",
    "                                                                    OR c.path LIKE CONCAT(a.id,\n",
    "                                                                    '.%') \n",
    "                                                                    OR c.path = a.id) \n",
    "                                                                WHERE\n",
    "                                                                    is_standard = 1 \n",
    "                                                                    AND is_selectable = 1\n",
    "                                                                ) b \n",
    "                                                                    ON (\n",
    "                                                                        ca.ancestor_id = b.concept_id\n",
    "                                                                    )\n",
    "                                                            ) \n",
    "                                                            AND is_standard = 1\n",
    "                                                        )\n",
    "                                                ) criteria \n",
    "                                            ) \n",
    "                                            AND cb_search_person.person_id NOT IN (\n",
    "                                                SELECT\n",
    "                                                    criteria.person_id \n",
    "                                            FROM\n",
    "                                                (SELECT\n",
    "                                                    DISTINCT person_id,\n",
    "                                                    entry_date,\n",
    "                                                    concept_id \n",
    "                                                FROM\n",
    "                                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_search_all_events` \n",
    "                                                WHERE\n",
    "                                                    (\n",
    "                                                        concept_id IN (\n",
    "                                                            SELECT\n",
    "                                                                DISTINCT c.concept_id \n",
    "                                                            FROM\n",
    "                                                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` c \n",
    "                                                            JOIN\n",
    "                                                                (\n",
    "                                                                    select\n",
    "                                                                        cast(cr.id as string) as id \n",
    "                                                                    FROM\n",
    "                                                                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` cr \n",
    "                                                                    WHERE\n",
    "                                                                        concept_id IN (40493428, 252840, 4092524, 4178964, 76349, 4177243, 4313083, 4241905, 46273652, 196925, 4180791, 436045, 200962, 4091466, 4162860, 4294416, 4092212, 444411, 4154630, 442139, 4309225, 4298032, 40650479, 197807, 4091465, 40490994, 4216273, 133424, 4247726, 4247822, 4181483, 4162994, 4309851, 436671, 4180780, 4300555, 4160780, 4180910, 4247238, 4155297, 4178971, 443568, 75210, 442183, 40492021, 78987, 443388, 195483, 4155285, 4181488, 25189, 4180902, 36676291, 376918, 4297666, 4157332, 40488919, 380661, 256633, 133711, 200348, 4174593, 433143, 4178977, 4178959, 443387, 40480128, 432263, 4312698, 4315805, 4112853, 4297185, 4181333, 442181, 46270738, 4178974, 4246450, 442131, 443391, 444410, 137809, 4181330, 4313916, 40487047, 192581, 4314047, 4092217, 4181351, 37397344, 4112745, 257503, 438693, 139750, 4181338, 198091, 436357, 196359, 432257, 4246451, 258375, 200052, 40488964, 256646, 436353, 438094, 4312023, 31509, 201238, 37310458, 4095312, 138996, 4162253, 378087, 435484, 374874, 36684472, 4314071, 4312288, 261514, 4181332, 44806773, 196360, 4188544, 4298026, 443392, 4295624, 432845, 45769720, 197507, 4233629, 4241917, 199754, 4160276, 440649, 443588, 4089655, 4092511, 439751, 432837, 4095740, 197808, 4162251, 200051, 4310566, 4187850, 26052, 4187849, 4311637, 4246017, 440658, 4247719, 4312685, 4241904, 4181480, 4311342, 4116084, 4111921, 36683531, 441805, 4298028, 437498, 4054503, 4298033, 192568, 4246127, 4312022, 4081044, 197500, 373425, 4338758, 443398, 376647, 4308621, 198988, 40491001, 40492037, 40490993, 4180793, 4181484, 4247338, 4297665, 441515, 74582, 442132, 261236, 4188545, 4180790, 4247331, 133726, 433716, 258369, 136917, 440345, 200959, 320342, 434298, 4334322, 4181350, 40492932, 4178968, 198700, 380055, 440956, 4111776, 4311619, 4247836, 4155171, 432833, 4111805, 433435, 201519, 438386, 4178976, 192855, 4157457, 75512, 4311617, 4157456, 24897, 45770892, 4187848, 80045, 4116241, 45768522, 4311499, 72266, 255507, 193144, 30346, 4315806, 443561, 28083, 4001666, 4089665, 4089756, 439404, 435755, 42536893, 4312944, 443390, 40493020, 4187851, 441233, 4248061, 4247357, 4157454, 4313482, 438699, 318096, 4112974, 432254, 4158563, 4091464, 436635, 198985, 40482750, 254591, 4181343, 197804, 4151250, 40487143, 443386, 46270513, 4157449, 4181354, 4181339, 133147, 443389, 4092382, 45757101, 4313634, 4307263, 4092512, 194589, 253717, 136354, 764225, 195513, 4153882, 4157331, 4214901, 4181477) \n",
    "                                                                        AND full_text LIKE '%_rank1]%'\n",
    "                                                                ) a \n",
    "                                                                    ON (\n",
    "                                                                        c.path LIKE CONCAT('%.',\n",
    "                                                                    a.id,\n",
    "                                                                    '.%') \n",
    "                                                                    OR c.path LIKE CONCAT('%.',\n",
    "                                                                    a.id) \n",
    "                                                                    OR c.path LIKE CONCAT(a.id,\n",
    "                                                                    '.%') \n",
    "                                                                    OR c.path = a.id) \n",
    "                                                                WHERE\n",
    "                                                                    is_standard = 1 \n",
    "                                                                    AND is_selectable = 1\n",
    "                                                                ) \n",
    "                                                                AND is_standard = 1 \n",
    "                                                        )\n",
    "                                                    ) criteria \n",
    "                                                ) ))\n",
    "                                    ) d_exposure \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_standard_concept \n",
    "                                        ON d_exposure.drug_concept_id = d_standard_concept.concept_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_type \n",
    "                                        ON d_exposure.drug_type_concept_id = d_type.concept_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_route \n",
    "                                        ON d_exposure.route_concept_id = d_route.concept_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".visit_occurrence` v \n",
    "                                        ON d_exposure.visit_occurrence_id = v.visit_occurrence_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_visit \n",
    "                                        ON v.visit_concept_id = d_visit.concept_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_source_concept \n",
    "                                        ON d_exposure.drug_source_concept_id = d_source_concept.concept_id\"\"\"\n",
    "\n",
    "dataset_50368594_drug_df = pandas.read_gbq(\n",
    "    dataset_50368594_drug_sql,\n",
    "    dialect=\"standard\",\n",
    "    use_bqstorage_api=(\"BIGQUERY_STORAGE_API_ENABLED\" in os.environ),\n",
    "    progress_bar_type=\"tqdm_notebook\")\n",
    "\n",
    "dataset_50368594_drug_df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas\n",
    "import os\n",
    "\n",
    "# This query represents dataset \"D6--Aspirin_Data_Set\" for domain \"drug\" and was generated for All of Us Registered Tier Dataset v7\n",
    "dataset_12257206_drug_sql = \"\"\"\n",
    "    SELECT\n",
    "        d_exposure.person_id,\n",
    "        d_exposure.drug_concept_id,\n",
    "        d_standard_concept.concept_name as standard_concept_name,\n",
    "        d_standard_concept.concept_code as standard_concept_code,\n",
    "        d_standard_concept.vocabulary_id as standard_vocabulary,\n",
    "        d_exposure.drug_exposure_start_datetime,\n",
    "        d_exposure.drug_exposure_end_datetime,\n",
    "        d_exposure.verbatim_end_date,\n",
    "        d_exposure.drug_type_concept_id,\n",
    "        d_type.concept_name as drug_type_concept_name,\n",
    "        d_exposure.stop_reason,\n",
    "        d_exposure.refills,\n",
    "        d_exposure.quantity,\n",
    "        d_exposure.days_supply,\n",
    "        d_exposure.sig,\n",
    "        d_exposure.route_concept_id,\n",
    "        d_route.concept_name as route_concept_name,\n",
    "        d_exposure.lot_number,\n",
    "        d_exposure.visit_occurrence_id,\n",
    "        d_visit.concept_name as visit_occurrence_concept_name,\n",
    "        d_exposure.drug_source_value,\n",
    "        d_exposure.drug_source_concept_id,\n",
    "        d_source_concept.concept_name as source_concept_name,\n",
    "        d_source_concept.concept_code as source_concept_code,\n",
    "        d_source_concept.vocabulary_id as source_vocabulary,\n",
    "        d_exposure.route_source_value,\n",
    "        d_exposure.dose_unit_source_value \n",
    "    FROM\n",
    "        ( SELECT\n",
    "            * \n",
    "        FROM\n",
    "            `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".drug_exposure` d_exposure \n",
    "        WHERE\n",
    "            (\n",
    "                drug_concept_id IN  (\n",
    "                    SELECT\n",
    "                        DISTINCT ca.descendant_id \n",
    "                    FROM\n",
    "                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria_ancestor` ca \n",
    "                    JOIN\n",
    "                        (\n",
    "                            select\n",
    "                                distinct c.concept_id \n",
    "                            FROM\n",
    "                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` c \n",
    "                            JOIN\n",
    "                                (\n",
    "                                    select\n",
    "                                        cast(cr.id as string) as id \n",
    "                                    FROM\n",
    "                                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` cr \n",
    "                                    WHERE\n",
    "                                        concept_id IN (\n",
    "                                            1112807\n",
    "                                        ) \n",
    "                                        AND full_text LIKE '%_rank1]%'\n",
    "                                ) a \n",
    "                                    ON (\n",
    "                                        c.path LIKE CONCAT('%.',\n",
    "                                    a.id,\n",
    "                                    '.%') \n",
    "                                    OR c.path LIKE CONCAT('%.',\n",
    "                                    a.id) \n",
    "                                    OR c.path LIKE CONCAT(a.id,\n",
    "                                    '.%') \n",
    "                                    OR c.path = a.id) \n",
    "                                WHERE\n",
    "                                    is_standard = 1 \n",
    "                                    AND is_selectable = 1\n",
    "                                ) b \n",
    "                                    ON (\n",
    "                                        ca.ancestor_id = b.concept_id\n",
    "                                    )\n",
    "                            )\n",
    "                        )  \n",
    "                        AND (\n",
    "                            d_exposure.PERSON_ID IN (\n",
    "                                SELECT\n",
    "                                    distinct person_id  \n",
    "                            FROM\n",
    "                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_search_person` cb_search_person  \n",
    "                            WHERE\n",
    "                                cb_search_person.person_id IN (\n",
    "                                    SELECT\n",
    "                                        criteria.person_id \n",
    "                                    FROM\n",
    "                                        (SELECT\n",
    "                                            DISTINCT person_id,\n",
    "                                            entry_date,\n",
    "                                            concept_id \n",
    "                                        FROM\n",
    "                                            `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_search_all_events` \n",
    "                                        WHERE\n",
    "                                            (\n",
    "                                                concept_id IN (\n",
    "                                                    SELECT\n",
    "                                                        DISTINCT ca.descendant_id \n",
    "                                                    FROM\n",
    "                                                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria_ancestor` ca \n",
    "                                                    JOIN\n",
    "                                                        (\n",
    "                                                            select\n",
    "                                                                distinct c.concept_id \n",
    "                                                            FROM\n",
    "                                                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` c \n",
    "                                                            JOIN\n",
    "                                                                (\n",
    "                                                                    select\n",
    "                                                                        cast(cr.id as string) as id \n",
    "                                                                    FROM\n",
    "                                                                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` cr \n",
    "                                                                    WHERE\n",
    "                                                                        concept_id IN (21604254) \n",
    "                                                                        AND full_text LIKE '%_rank1]%'\n",
    "                                                                ) a \n",
    "                                                                    ON (\n",
    "                                                                        c.path LIKE CONCAT('%.',\n",
    "                                                                    a.id,\n",
    "                                                                    '.%') \n",
    "                                                                    OR c.path LIKE CONCAT('%.',\n",
    "                                                                    a.id) \n",
    "                                                                    OR c.path LIKE CONCAT(a.id,\n",
    "                                                                    '.%') \n",
    "                                                                    OR c.path = a.id) \n",
    "                                                                WHERE\n",
    "                                                                    is_standard = 1 \n",
    "                                                                    AND is_selectable = 1\n",
    "                                                                ) b \n",
    "                                                                    ON (\n",
    "                                                                        ca.ancestor_id = b.concept_id\n",
    "                                                                    )\n",
    "                                                            ) \n",
    "                                                            AND is_standard = 1\n",
    "                                                        )\n",
    "                                                ) criteria \n",
    "                                            ) \n",
    "                                            AND cb_search_person.person_id NOT IN (\n",
    "                                                SELECT\n",
    "                                                    criteria.person_id \n",
    "                                            FROM\n",
    "                                                (SELECT\n",
    "                                                    DISTINCT person_id,\n",
    "                                                    entry_date,\n",
    "                                                    concept_id \n",
    "                                                FROM\n",
    "                                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_search_all_events` \n",
    "                                                WHERE\n",
    "                                                    (\n",
    "                                                        concept_id IN (\n",
    "                                                            SELECT\n",
    "                                                                DISTINCT c.concept_id \n",
    "                                                            FROM\n",
    "                                                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` c \n",
    "                                                            JOIN\n",
    "                                                                (\n",
    "                                                                    select\n",
    "                                                                        cast(cr.id as string) as id \n",
    "                                                                    FROM\n",
    "                                                                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` cr \n",
    "                                                                    WHERE\n",
    "                                                                        concept_id IN (40493428, 252840, 4092524, 4178964, 76349, 4177243, 4313083, 4241905, 46273652, 196925, 4180791, 436045, 200962, 4091466, 4162860, 4294416, 4092212, 444411, 4154630, 442139, 4309225, 4298032, 40650479, 197807, 4091465, 40490994, 4216273, 133424, 4247726, 4247822, 4181483, 4162994, 4309851, 436671, 4180780, 4300555, 4160780, 4180910, 4247238, 4155297, 4178971, 443568, 75210, 442183, 40492021, 78987, 443388, 195483, 4155285, 4181488, 25189, 4180902, 36676291, 376918, 4297666, 4157332, 40488919, 380661, 256633, 133711, 200348, 4174593, 433143, 4178977, 4178959, 443387, 40480128, 432263, 4312698, 4315805, 4112853, 4297185, 4181333, 442181, 46270738, 4178974, 4246450, 442131, 443391, 444410, 137809, 4181330, 4313916, 40487047, 192581, 4314047, 4092217, 4181351, 37397344, 4112745, 257503, 438693, 139750, 4181338, 198091, 436357, 196359, 432257, 4246451, 258375, 200052, 40488964, 256646, 436353, 438094, 4312023, 31509, 201238, 37310458, 4095312, 138996, 4162253, 378087, 435484, 374874, 36684472, 4314071, 4312288, 261514, 4181332, 44806773, 196360, 4188544, 4298026, 443392, 4295624, 432845, 45769720, 197507, 4233629, 4241917, 199754, 4160276, 440649, 443588, 4089655, 4092511, 439751, 432837, 4095740, 197808, 4162251, 200051, 4310566, 4187850, 26052, 4187849, 4311637, 4246017, 440658, 4247719, 4312685, 4241904, 4181480, 4311342, 4116084, 4111921, 36683531, 441805, 4298028, 437498, 4054503, 4298033, 192568, 4246127, 4312022, 4081044, 197500, 373425, 4338758, 443398, 376647, 4308621, 198988, 40491001, 40492037, 40490993, 4180793, 4181484, 4247338, 4297665, 441515, 74582, 442132, 261236, 4188545, 4180790, 4247331, 133726, 433716, 258369, 136917, 440345, 200959, 320342, 434298, 4334322, 4181350, 40492932, 4178968, 198700, 380055, 440956, 4111776, 4311619, 4247836, 4155171, 432833, 4111805, 433435, 201519, 438386, 4178976, 192855, 4157457, 75512, 4311617, 4157456, 24897, 45770892, 4187848, 80045, 4116241, 45768522, 4311499, 72266, 255507, 193144, 30346, 4315806, 443561, 28083, 4001666, 4089665, 4089756, 439404, 435755, 42536893, 4312944, 443390, 40493020, 4187851, 441233, 4248061, 4247357, 4157454, 4313482, 438699, 318096, 4112974, 432254, 4158563, 4091464, 436635, 198985, 40482750, 254591, 4181343, 197804, 4151250, 40487143, 443386, 46270513, 4157449, 4181354, 4181339, 133147, 443389, 4092382, 45757101, 4313634, 4307263, 4092512, 194589, 253717, 136354, 764225, 195513, 4153882, 4157331, 4214901, 4181477) \n",
    "                                                                        AND full_text LIKE '%_rank1]%'\n",
    "                                                                ) a \n",
    "                                                                    ON (\n",
    "                                                                        c.path LIKE CONCAT('%.',\n",
    "                                                                    a.id,\n",
    "                                                                    '.%') \n",
    "                                                                    OR c.path LIKE CONCAT('%.',\n",
    "                                                                    a.id) \n",
    "                                                                    OR c.path LIKE CONCAT(a.id,\n",
    "                                                                    '.%') \n",
    "                                                                    OR c.path = a.id) \n",
    "                                                                WHERE\n",
    "                                                                    is_standard = 1 \n",
    "                                                                    AND is_selectable = 1\n",
    "                                                                ) \n",
    "                                                                AND is_standard = 1 \n",
    "                                                        )\n",
    "                                                    ) criteria \n",
    "                                                ) ))\n",
    "                                    ) d_exposure \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_standard_concept \n",
    "                                        ON d_exposure.drug_concept_id = d_standard_concept.concept_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_type \n",
    "                                        ON d_exposure.drug_type_concept_id = d_type.concept_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_route \n",
    "                                        ON d_exposure.route_concept_id = d_route.concept_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".visit_occurrence` v \n",
    "                                        ON d_exposure.visit_occurrence_id = v.visit_occurrence_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_visit \n",
    "                                        ON v.visit_concept_id = d_visit.concept_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_source_concept \n",
    "                                        ON d_exposure.drug_source_concept_id = d_source_concept.concept_id\"\"\"\n",
    "\n",
    "dataset_12257206_drug_df = pandas.read_gbq(\n",
    "    dataset_12257206_drug_sql,\n",
    "    dialect=\"standard\",\n",
    "    use_bqstorage_api=(\"BIGQUERY_STORAGE_API_ENABLED\" in os.environ),\n",
    "    progress_bar_type=\"tqdm_notebook\")\n",
    "\n",
    "dataset_12257206_drug_df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas\n",
    "import os\n",
    "\n",
    "# This query represents dataset \"D7--Codeine_Data_Set\" for domain \"drug\" and was generated for All of Us Registered Tier Dataset v7\n",
    "dataset_69583946_drug_sql = \"\"\"\n",
    "    SELECT\n",
    "        d_exposure.person_id,\n",
    "        d_exposure.drug_concept_id,\n",
    "        d_standard_concept.concept_name as standard_concept_name,\n",
    "        d_standard_concept.concept_code as standard_concept_code,\n",
    "        d_standard_concept.vocabulary_id as standard_vocabulary,\n",
    "        d_exposure.drug_exposure_start_datetime,\n",
    "        d_exposure.drug_exposure_end_datetime,\n",
    "        d_exposure.verbatim_end_date,\n",
    "        d_exposure.drug_type_concept_id,\n",
    "        d_type.concept_name as drug_type_concept_name,\n",
    "        d_exposure.stop_reason,\n",
    "        d_exposure.refills,\n",
    "        d_exposure.quantity,\n",
    "        d_exposure.days_supply,\n",
    "        d_exposure.sig,\n",
    "        d_exposure.route_concept_id,\n",
    "        d_route.concept_name as route_concept_name,\n",
    "        d_exposure.lot_number,\n",
    "        d_exposure.visit_occurrence_id,\n",
    "        d_visit.concept_name as visit_occurrence_concept_name,\n",
    "        d_exposure.drug_source_value,\n",
    "        d_exposure.drug_source_concept_id,\n",
    "        d_source_concept.concept_name as source_concept_name,\n",
    "        d_source_concept.concept_code as source_concept_code,\n",
    "        d_source_concept.vocabulary_id as source_vocabulary,\n",
    "        d_exposure.route_source_value,\n",
    "        d_exposure.dose_unit_source_value \n",
    "    FROM\n",
    "        ( SELECT\n",
    "            * \n",
    "        FROM\n",
    "            `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".drug_exposure` d_exposure \n",
    "        WHERE\n",
    "            (\n",
    "                drug_concept_id IN  (\n",
    "                    SELECT\n",
    "                        DISTINCT ca.descendant_id \n",
    "                    FROM\n",
    "                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria_ancestor` ca \n",
    "                    JOIN\n",
    "                        (\n",
    "                            select\n",
    "                                distinct c.concept_id \n",
    "                            FROM\n",
    "                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` c \n",
    "                            JOIN\n",
    "                                (\n",
    "                                    select\n",
    "                                        cast(cr.id as string) as id \n",
    "                                    FROM\n",
    "                                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` cr \n",
    "                                    WHERE\n",
    "                                        concept_id IN (\n",
    "                                            1201620\n",
    "                                        ) \n",
    "                                        AND full_text LIKE '%_rank1]%'\n",
    "                                ) a \n",
    "                                    ON (\n",
    "                                        c.path LIKE CONCAT('%.',\n",
    "                                    a.id,\n",
    "                                    '.%') \n",
    "                                    OR c.path LIKE CONCAT('%.',\n",
    "                                    a.id) \n",
    "                                    OR c.path LIKE CONCAT(a.id,\n",
    "                                    '.%') \n",
    "                                    OR c.path = a.id) \n",
    "                                WHERE\n",
    "                                    is_standard = 1 \n",
    "                                    AND is_selectable = 1\n",
    "                                ) b \n",
    "                                    ON (\n",
    "                                        ca.ancestor_id = b.concept_id\n",
    "                                    )\n",
    "                            )\n",
    "                        )  \n",
    "                        AND (\n",
    "                            d_exposure.PERSON_ID IN (\n",
    "                                SELECT\n",
    "                                    distinct person_id  \n",
    "                            FROM\n",
    "                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_search_person` cb_search_person  \n",
    "                            WHERE\n",
    "                                cb_search_person.person_id IN (\n",
    "                                    SELECT\n",
    "                                        criteria.person_id \n",
    "                                    FROM\n",
    "                                        (SELECT\n",
    "                                            DISTINCT person_id,\n",
    "                                            entry_date,\n",
    "                                            concept_id \n",
    "                                        FROM\n",
    "                                            `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_search_all_events` \n",
    "                                        WHERE\n",
    "                                            (\n",
    "                                                concept_id IN (\n",
    "                                                    SELECT\n",
    "                                                        DISTINCT ca.descendant_id \n",
    "                                                    FROM\n",
    "                                                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria_ancestor` ca \n",
    "                                                    JOIN\n",
    "                                                        (\n",
    "                                                            select\n",
    "                                                                distinct c.concept_id \n",
    "                                                            FROM\n",
    "                                                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` c \n",
    "                                                            JOIN\n",
    "                                                                (\n",
    "                                                                    select\n",
    "                                                                        cast(cr.id as string) as id \n",
    "                                                                    FROM\n",
    "                                                                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` cr \n",
    "                                                                    WHERE\n",
    "                                                                        concept_id IN (21604254) \n",
    "                                                                        AND full_text LIKE '%_rank1]%'\n",
    "                                                                ) a \n",
    "                                                                    ON (\n",
    "                                                                        c.path LIKE CONCAT('%.',\n",
    "                                                                    a.id,\n",
    "                                                                    '.%') \n",
    "                                                                    OR c.path LIKE CONCAT('%.',\n",
    "                                                                    a.id) \n",
    "                                                                    OR c.path LIKE CONCAT(a.id,\n",
    "                                                                    '.%') \n",
    "                                                                    OR c.path = a.id) \n",
    "                                                                WHERE\n",
    "                                                                    is_standard = 1 \n",
    "                                                                    AND is_selectable = 1\n",
    "                                                                ) b \n",
    "                                                                    ON (\n",
    "                                                                        ca.ancestor_id = b.concept_id\n",
    "                                                                    )\n",
    "                                                            ) \n",
    "                                                            AND is_standard = 1\n",
    "                                                        )\n",
    "                                                ) criteria \n",
    "                                            ) \n",
    "                                            AND cb_search_person.person_id NOT IN (\n",
    "                                                SELECT\n",
    "                                                    criteria.person_id \n",
    "                                            FROM\n",
    "                                                (SELECT\n",
    "                                                    DISTINCT person_id,\n",
    "                                                    entry_date,\n",
    "                                                    concept_id \n",
    "                                                FROM\n",
    "                                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_search_all_events` \n",
    "                                                WHERE\n",
    "                                                    (\n",
    "                                                        concept_id IN (\n",
    "                                                            SELECT\n",
    "                                                                DISTINCT c.concept_id \n",
    "                                                            FROM\n",
    "                                                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` c \n",
    "                                                            JOIN\n",
    "                                                                (\n",
    "                                                                    select\n",
    "                                                                        cast(cr.id as string) as id \n",
    "                                                                    FROM\n",
    "                                                                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` cr \n",
    "                                                                    WHERE\n",
    "                                                                        concept_id IN (40493428, 252840, 4092524, 4178964, 76349, 4177243, 4313083, 4241905, 46273652, 196925, 4180791, 436045, 200962, 4091466, 4162860, 4294416, 4092212, 444411, 4154630, 442139, 4309225, 4298032, 40650479, 197807, 4091465, 40490994, 4216273, 133424, 4247726, 4247822, 4181483, 4162994, 4309851, 436671, 4180780, 4300555, 4160780, 4180910, 4247238, 4155297, 4178971, 443568, 75210, 442183, 40492021, 78987, 443388, 195483, 4155285, 4181488, 25189, 4180902, 36676291, 376918, 4297666, 4157332, 40488919, 380661, 256633, 133711, 200348, 4174593, 433143, 4178977, 4178959, 443387, 40480128, 432263, 4312698, 4315805, 4112853, 4297185, 4181333, 442181, 46270738, 4178974, 4246450, 442131, 443391, 444410, 137809, 4181330, 4313916, 40487047, 192581, 4314047, 4092217, 4181351, 37397344, 4112745, 257503, 438693, 139750, 4181338, 198091, 436357, 196359, 432257, 4246451, 258375, 200052, 40488964, 256646, 436353, 438094, 4312023, 31509, 201238, 37310458, 4095312, 138996, 4162253, 378087, 435484, 374874, 36684472, 4314071, 4312288, 261514, 4181332, 44806773, 196360, 4188544, 4298026, 443392, 4295624, 432845, 45769720, 197507, 4233629, 4241917, 199754, 4160276, 440649, 443588, 4089655, 4092511, 439751, 432837, 4095740, 197808, 4162251, 200051, 4310566, 4187850, 26052, 4187849, 4311637, 4246017, 440658, 4247719, 4312685, 4241904, 4181480, 4311342, 4116084, 4111921, 36683531, 441805, 4298028, 437498, 4054503, 4298033, 192568, 4246127, 4312022, 4081044, 197500, 373425, 4338758, 443398, 376647, 4308621, 198988, 40491001, 40492037, 40490993, 4180793, 4181484, 4247338, 4297665, 441515, 74582, 442132, 261236, 4188545, 4180790, 4247331, 133726, 433716, 258369, 136917, 440345, 200959, 320342, 434298, 4334322, 4181350, 40492932, 4178968, 198700, 380055, 440956, 4111776, 4311619, 4247836, 4155171, 432833, 4111805, 433435, 201519, 438386, 4178976, 192855, 4157457, 75512, 4311617, 4157456, 24897, 45770892, 4187848, 80045, 4116241, 45768522, 4311499, 72266, 255507, 193144, 30346, 4315806, 443561, 28083, 4001666, 4089665, 4089756, 439404, 435755, 42536893, 4312944, 443390, 40493020, 4187851, 441233, 4248061, 4247357, 4157454, 4313482, 438699, 318096, 4112974, 432254, 4158563, 4091464, 436635, 198985, 40482750, 254591, 4181343, 197804, 4151250, 40487143, 443386, 46270513, 4157449, 4181354, 4181339, 133147, 443389, 4092382, 45757101, 4313634, 4307263, 4092512, 194589, 253717, 136354, 764225, 195513, 4153882, 4157331, 4214901, 4181477) \n",
    "                                                                        AND full_text LIKE '%_rank1]%'\n",
    "                                                                ) a \n",
    "                                                                    ON (\n",
    "                                                                        c.path LIKE CONCAT('%.',\n",
    "                                                                    a.id,\n",
    "                                                                    '.%') \n",
    "                                                                    OR c.path LIKE CONCAT('%.',\n",
    "                                                                    a.id) \n",
    "                                                                    OR c.path LIKE CONCAT(a.id,\n",
    "                                                                    '.%') \n",
    "                                                                    OR c.path = a.id) \n",
    "                                                                WHERE\n",
    "                                                                    is_standard = 1 \n",
    "                                                                    AND is_selectable = 1\n",
    "                                                                ) \n",
    "                                                                AND is_standard = 1 \n",
    "                                                        )\n",
    "                                                    ) criteria \n",
    "                                                ) ))\n",
    "                                    ) d_exposure \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_standard_concept \n",
    "                                        ON d_exposure.drug_concept_id = d_standard_concept.concept_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_type \n",
    "                                        ON d_exposure.drug_type_concept_id = d_type.concept_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_route \n",
    "                                        ON d_exposure.route_concept_id = d_route.concept_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".visit_occurrence` v \n",
    "                                        ON d_exposure.visit_occurrence_id = v.visit_occurrence_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_visit \n",
    "                                        ON v.visit_concept_id = d_visit.concept_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_source_concept \n",
    "                                        ON d_exposure.drug_source_concept_id = d_source_concept.concept_id\"\"\"\n",
    "\n",
    "dataset_69583946_drug_df = pandas.read_gbq(\n",
    "    dataset_69583946_drug_sql,\n",
    "    dialect=\"standard\",\n",
    "    use_bqstorage_api=(\"BIGQUERY_STORAGE_API_ENABLED\" in os.environ),\n",
    "    progress_bar_type=\"tqdm_notebook\")\n",
    "\n",
    "dataset_69583946_drug_df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas\n",
    "import os\n",
    "\n",
    "# This query represents dataset \"D8--Tramadol_Data_Set\" for domain \"drug\" and was generated for All of Us Registered Tier Dataset v7\n",
    "dataset_34872773_drug_sql = \"\"\"\n",
    "    SELECT\n",
    "        d_exposure.person_id,\n",
    "        d_exposure.drug_concept_id,\n",
    "        d_standard_concept.concept_name as standard_concept_name,\n",
    "        d_standard_concept.concept_code as standard_concept_code,\n",
    "        d_standard_concept.vocabulary_id as standard_vocabulary,\n",
    "        d_exposure.drug_exposure_start_datetime,\n",
    "        d_exposure.drug_exposure_end_datetime,\n",
    "        d_exposure.verbatim_end_date,\n",
    "        d_exposure.drug_type_concept_id,\n",
    "        d_type.concept_name as drug_type_concept_name,\n",
    "        d_exposure.stop_reason,\n",
    "        d_exposure.refills,\n",
    "        d_exposure.quantity,\n",
    "        d_exposure.days_supply,\n",
    "        d_exposure.sig,\n",
    "        d_exposure.route_concept_id,\n",
    "        d_route.concept_name as route_concept_name,\n",
    "        d_exposure.lot_number,\n",
    "        d_exposure.visit_occurrence_id,\n",
    "        d_visit.concept_name as visit_occurrence_concept_name,\n",
    "        d_exposure.drug_source_value,\n",
    "        d_exposure.drug_source_concept_id,\n",
    "        d_source_concept.concept_name as source_concept_name,\n",
    "        d_source_concept.concept_code as source_concept_code,\n",
    "        d_source_concept.vocabulary_id as source_vocabulary,\n",
    "        d_exposure.route_source_value,\n",
    "        d_exposure.dose_unit_source_value \n",
    "    FROM\n",
    "        ( SELECT\n",
    "            * \n",
    "        FROM\n",
    "            `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".drug_exposure` d_exposure \n",
    "        WHERE\n",
    "            (\n",
    "                drug_concept_id IN  (\n",
    "                    SELECT\n",
    "                        DISTINCT ca.descendant_id \n",
    "                    FROM\n",
    "                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria_ancestor` ca \n",
    "                    JOIN\n",
    "                        (\n",
    "                            select\n",
    "                                distinct c.concept_id \n",
    "                            FROM\n",
    "                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` c \n",
    "                            JOIN\n",
    "                                (\n",
    "                                    select\n",
    "                                        cast(cr.id as string) as id \n",
    "                                    FROM\n",
    "                                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` cr \n",
    "                                    WHERE\n",
    "                                        concept_id IN (\n",
    "                                            1103314\n",
    "                                        ) \n",
    "                                        AND full_text LIKE '%_rank1]%'\n",
    "                                ) a \n",
    "                                    ON (\n",
    "                                        c.path LIKE CONCAT('%.',\n",
    "                                    a.id,\n",
    "                                    '.%') \n",
    "                                    OR c.path LIKE CONCAT('%.',\n",
    "                                    a.id) \n",
    "                                    OR c.path LIKE CONCAT(a.id,\n",
    "                                    '.%') \n",
    "                                    OR c.path = a.id) \n",
    "                                WHERE\n",
    "                                    is_standard = 1 \n",
    "                                    AND is_selectable = 1\n",
    "                                ) b \n",
    "                                    ON (\n",
    "                                        ca.ancestor_id = b.concept_id\n",
    "                                    )\n",
    "                            )\n",
    "                        )  \n",
    "                        AND (\n",
    "                            d_exposure.PERSON_ID IN (\n",
    "                                SELECT\n",
    "                                    distinct person_id  \n",
    "                            FROM\n",
    "                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_search_person` cb_search_person  \n",
    "                            WHERE\n",
    "                                cb_search_person.person_id IN (\n",
    "                                    SELECT\n",
    "                                        criteria.person_id \n",
    "                                    FROM\n",
    "                                        (SELECT\n",
    "                                            DISTINCT person_id,\n",
    "                                            entry_date,\n",
    "                                            concept_id \n",
    "                                        FROM\n",
    "                                            `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_search_all_events` \n",
    "                                        WHERE\n",
    "                                            (\n",
    "                                                concept_id IN (\n",
    "                                                    SELECT\n",
    "                                                        DISTINCT ca.descendant_id \n",
    "                                                    FROM\n",
    "                                                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria_ancestor` ca \n",
    "                                                    JOIN\n",
    "                                                        (\n",
    "                                                            select\n",
    "                                                                distinct c.concept_id \n",
    "                                                            FROM\n",
    "                                                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` c \n",
    "                                                            JOIN\n",
    "                                                                (\n",
    "                                                                    select\n",
    "                                                                        cast(cr.id as string) as id \n",
    "                                                                    FROM\n",
    "                                                                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` cr \n",
    "                                                                    WHERE\n",
    "                                                                        concept_id IN (21604254) \n",
    "                                                                        AND full_text LIKE '%_rank1]%'\n",
    "                                                                ) a \n",
    "                                                                    ON (\n",
    "                                                                        c.path LIKE CONCAT('%.',\n",
    "                                                                    a.id,\n",
    "                                                                    '.%') \n",
    "                                                                    OR c.path LIKE CONCAT('%.',\n",
    "                                                                    a.id) \n",
    "                                                                    OR c.path LIKE CONCAT(a.id,\n",
    "                                                                    '.%') \n",
    "                                                                    OR c.path = a.id) \n",
    "                                                                WHERE\n",
    "                                                                    is_standard = 1 \n",
    "                                                                    AND is_selectable = 1\n",
    "                                                                ) b \n",
    "                                                                    ON (\n",
    "                                                                        ca.ancestor_id = b.concept_id\n",
    "                                                                    )\n",
    "                                                            ) \n",
    "                                                            AND is_standard = 1\n",
    "                                                        )\n",
    "                                                ) criteria \n",
    "                                            ) \n",
    "                                            AND cb_search_person.person_id NOT IN (\n",
    "                                                SELECT\n",
    "                                                    criteria.person_id \n",
    "                                            FROM\n",
    "                                                (SELECT\n",
    "                                                    DISTINCT person_id,\n",
    "                                                    entry_date,\n",
    "                                                    concept_id \n",
    "                                                FROM\n",
    "                                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_search_all_events` \n",
    "                                                WHERE\n",
    "                                                    (\n",
    "                                                        concept_id IN (\n",
    "                                                            SELECT\n",
    "                                                                DISTINCT c.concept_id \n",
    "                                                            FROM\n",
    "                                                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` c \n",
    "                                                            JOIN\n",
    "                                                                (\n",
    "                                                                    select\n",
    "                                                                        cast(cr.id as string) as id \n",
    "                                                                    FROM\n",
    "                                                                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` cr \n",
    "                                                                    WHERE\n",
    "                                                                        concept_id IN (40493428, 252840, 4092524, 4178964, 76349, 4177243, 4313083, 4241905, 46273652, 196925, 4180791, 436045, 200962, 4091466, 4162860, 4294416, 4092212, 444411, 4154630, 442139, 4309225, 4298032, 40650479, 197807, 4091465, 40490994, 4216273, 133424, 4247726, 4247822, 4181483, 4162994, 4309851, 436671, 4180780, 4300555, 4160780, 4180910, 4247238, 4155297, 4178971, 443568, 75210, 442183, 40492021, 78987, 443388, 195483, 4155285, 4181488, 25189, 4180902, 36676291, 376918, 4297666, 4157332, 40488919, 380661, 256633, 133711, 200348, 4174593, 433143, 4178977, 4178959, 443387, 40480128, 432263, 4312698, 4315805, 4112853, 4297185, 4181333, 442181, 46270738, 4178974, 4246450, 442131, 443391, 444410, 137809, 4181330, 4313916, 40487047, 192581, 4314047, 4092217, 4181351, 37397344, 4112745, 257503, 438693, 139750, 4181338, 198091, 436357, 196359, 432257, 4246451, 258375, 200052, 40488964, 256646, 436353, 438094, 4312023, 31509, 201238, 37310458, 4095312, 138996, 4162253, 378087, 435484, 374874, 36684472, 4314071, 4312288, 261514, 4181332, 44806773, 196360, 4188544, 4298026, 443392, 4295624, 432845, 45769720, 197507, 4233629, 4241917, 199754, 4160276, 440649, 443588, 4089655, 4092511, 439751, 432837, 4095740, 197808, 4162251, 200051, 4310566, 4187850, 26052, 4187849, 4311637, 4246017, 440658, 4247719, 4312685, 4241904, 4181480, 4311342, 4116084, 4111921, 36683531, 441805, 4298028, 437498, 4054503, 4298033, 192568, 4246127, 4312022, 4081044, 197500, 373425, 4338758, 443398, 376647, 4308621, 198988, 40491001, 40492037, 40490993, 4180793, 4181484, 4247338, 4297665, 441515, 74582, 442132, 261236, 4188545, 4180790, 4247331, 133726, 433716, 258369, 136917, 440345, 200959, 320342, 434298, 4334322, 4181350, 40492932, 4178968, 198700, 380055, 440956, 4111776, 4311619, 4247836, 4155171, 432833, 4111805, 433435, 201519, 438386, 4178976, 192855, 4157457, 75512, 4311617, 4157456, 24897, 45770892, 4187848, 80045, 4116241, 45768522, 4311499, 72266, 255507, 193144, 30346, 4315806, 443561, 28083, 4001666, 4089665, 4089756, 439404, 435755, 42536893, 4312944, 443390, 40493020, 4187851, 441233, 4248061, 4247357, 4157454, 4313482, 438699, 318096, 4112974, 432254, 4158563, 4091464, 436635, 198985, 40482750, 254591, 4181343, 197804, 4151250, 40487143, 443386, 46270513, 4157449, 4181354, 4181339, 133147, 443389, 4092382, 45757101, 4313634, 4307263, 4092512, 194589, 253717, 136354, 764225, 195513, 4153882, 4157331, 4214901, 4181477) \n",
    "                                                                        AND full_text LIKE '%_rank1]%'\n",
    "                                                                ) a \n",
    "                                                                    ON (\n",
    "                                                                        c.path LIKE CONCAT('%.',\n",
    "                                                                    a.id,\n",
    "                                                                    '.%') \n",
    "                                                                    OR c.path LIKE CONCAT('%.',\n",
    "                                                                    a.id) \n",
    "                                                                    OR c.path LIKE CONCAT(a.id,\n",
    "                                                                    '.%') \n",
    "                                                                    OR c.path = a.id) \n",
    "                                                                WHERE\n",
    "                                                                    is_standard = 1 \n",
    "                                                                    AND is_selectable = 1\n",
    "                                                                ) \n",
    "                                                                AND is_standard = 1 \n",
    "                                                        )\n",
    "                                                    ) criteria \n",
    "                                                ) ))\n",
    "                                    ) d_exposure \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_standard_concept \n",
    "                                        ON d_exposure.drug_concept_id = d_standard_concept.concept_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_type \n",
    "                                        ON d_exposure.drug_type_concept_id = d_type.concept_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_route \n",
    "                                        ON d_exposure.route_concept_id = d_route.concept_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".visit_occurrence` v \n",
    "                                        ON d_exposure.visit_occurrence_id = v.visit_occurrence_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_visit \n",
    "                                        ON v.visit_concept_id = d_visit.concept_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_source_concept \n",
    "                                        ON d_exposure.drug_source_concept_id = d_source_concept.concept_id\"\"\"\n",
    "\n",
    "dataset_34872773_drug_df = pandas.read_gbq(\n",
    "    dataset_34872773_drug_sql,\n",
    "    dialect=\"standard\",\n",
    "    use_bqstorage_api=(\"BIGQUERY_STORAGE_API_ENABLED\" in os.environ),\n",
    "    progress_bar_type=\"tqdm_notebook\")\n",
    "\n",
    "dataset_34872773_drug_df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas\n",
    "import os\n",
    "\n",
    "# This query represents dataset \"D9--Nalbuphine_Data_Set\" for domain \"drug\" and was generated for All of Us Registered Tier Dataset v7\n",
    "dataset_43182103_drug_sql = \"\"\"\n",
    "    SELECT\n",
    "        d_exposure.person_id,\n",
    "        d_exposure.drug_concept_id,\n",
    "        d_standard_concept.concept_name as standard_concept_name,\n",
    "        d_standard_concept.concept_code as standard_concept_code,\n",
    "        d_standard_concept.vocabulary_id as standard_vocabulary,\n",
    "        d_exposure.drug_exposure_start_datetime,\n",
    "        d_exposure.drug_exposure_end_datetime,\n",
    "        d_exposure.verbatim_end_date,\n",
    "        d_exposure.drug_type_concept_id,\n",
    "        d_type.concept_name as drug_type_concept_name,\n",
    "        d_exposure.stop_reason,\n",
    "        d_exposure.refills,\n",
    "        d_exposure.quantity,\n",
    "        d_exposure.days_supply,\n",
    "        d_exposure.sig,\n",
    "        d_exposure.route_concept_id,\n",
    "        d_route.concept_name as route_concept_name,\n",
    "        d_exposure.lot_number,\n",
    "        d_exposure.visit_occurrence_id,\n",
    "        d_visit.concept_name as visit_occurrence_concept_name,\n",
    "        d_exposure.drug_source_value,\n",
    "        d_exposure.drug_source_concept_id,\n",
    "        d_source_concept.concept_name as source_concept_name,\n",
    "        d_source_concept.concept_code as source_concept_code,\n",
    "        d_source_concept.vocabulary_id as source_vocabulary,\n",
    "        d_exposure.route_source_value,\n",
    "        d_exposure.dose_unit_source_value \n",
    "    FROM\n",
    "        ( SELECT\n",
    "            * \n",
    "        FROM\n",
    "            `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".drug_exposure` d_exposure \n",
    "        WHERE\n",
    "            (\n",
    "                drug_concept_id IN  (\n",
    "                    SELECT\n",
    "                        DISTINCT ca.descendant_id \n",
    "                    FROM\n",
    "                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria_ancestor` ca \n",
    "                    JOIN\n",
    "                        (\n",
    "                            select\n",
    "                                distinct c.concept_id \n",
    "                            FROM\n",
    "                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` c \n",
    "                            JOIN\n",
    "                                (\n",
    "                                    select\n",
    "                                        cast(cr.id as string) as id \n",
    "                                    FROM\n",
    "                                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` cr \n",
    "                                    WHERE\n",
    "                                        concept_id IN (\n",
    "                                            1114122\n",
    "                                        ) \n",
    "                                        AND full_text LIKE '%_rank1]%'\n",
    "                                ) a \n",
    "                                    ON (\n",
    "                                        c.path LIKE CONCAT('%.',\n",
    "                                    a.id,\n",
    "                                    '.%') \n",
    "                                    OR c.path LIKE CONCAT('%.',\n",
    "                                    a.id) \n",
    "                                    OR c.path LIKE CONCAT(a.id,\n",
    "                                    '.%') \n",
    "                                    OR c.path = a.id) \n",
    "                                WHERE\n",
    "                                    is_standard = 1 \n",
    "                                    AND is_selectable = 1\n",
    "                                ) b \n",
    "                                    ON (\n",
    "                                        ca.ancestor_id = b.concept_id\n",
    "                                    )\n",
    "                            )\n",
    "                        )  \n",
    "                        AND (\n",
    "                            d_exposure.PERSON_ID IN (\n",
    "                                SELECT\n",
    "                                    distinct person_id  \n",
    "                            FROM\n",
    "                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_search_person` cb_search_person  \n",
    "                            WHERE\n",
    "                                cb_search_person.person_id IN (\n",
    "                                    SELECT\n",
    "                                        criteria.person_id \n",
    "                                    FROM\n",
    "                                        (SELECT\n",
    "                                            DISTINCT person_id,\n",
    "                                            entry_date,\n",
    "                                            concept_id \n",
    "                                        FROM\n",
    "                                            `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_search_all_events` \n",
    "                                        WHERE\n",
    "                                            (\n",
    "                                                concept_id IN (\n",
    "                                                    SELECT\n",
    "                                                        DISTINCT ca.descendant_id \n",
    "                                                    FROM\n",
    "                                                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria_ancestor` ca \n",
    "                                                    JOIN\n",
    "                                                        (\n",
    "                                                            select\n",
    "                                                                distinct c.concept_id \n",
    "                                                            FROM\n",
    "                                                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` c \n",
    "                                                            JOIN\n",
    "                                                                (\n",
    "                                                                    select\n",
    "                                                                        cast(cr.id as string) as id \n",
    "                                                                    FROM\n",
    "                                                                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` cr \n",
    "                                                                    WHERE\n",
    "                                                                        concept_id IN (21604254) \n",
    "                                                                        AND full_text LIKE '%_rank1]%'\n",
    "                                                                ) a \n",
    "                                                                    ON (\n",
    "                                                                        c.path LIKE CONCAT('%.',\n",
    "                                                                    a.id,\n",
    "                                                                    '.%') \n",
    "                                                                    OR c.path LIKE CONCAT('%.',\n",
    "                                                                    a.id) \n",
    "                                                                    OR c.path LIKE CONCAT(a.id,\n",
    "                                                                    '.%') \n",
    "                                                                    OR c.path = a.id) \n",
    "                                                                WHERE\n",
    "                                                                    is_standard = 1 \n",
    "                                                                    AND is_selectable = 1\n",
    "                                                                ) b \n",
    "                                                                    ON (\n",
    "                                                                        ca.ancestor_id = b.concept_id\n",
    "                                                                    )\n",
    "                                                            ) \n",
    "                                                            AND is_standard = 1\n",
    "                                                        )\n",
    "                                                ) criteria \n",
    "                                            ) \n",
    "                                            AND cb_search_person.person_id NOT IN (\n",
    "                                                SELECT\n",
    "                                                    criteria.person_id \n",
    "                                            FROM\n",
    "                                                (SELECT\n",
    "                                                    DISTINCT person_id,\n",
    "                                                    entry_date,\n",
    "                                                    concept_id \n",
    "                                                FROM\n",
    "                                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_search_all_events` \n",
    "                                                WHERE\n",
    "                                                    (\n",
    "                                                        concept_id IN (\n",
    "                                                            SELECT\n",
    "                                                                DISTINCT c.concept_id \n",
    "                                                            FROM\n",
    "                                                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` c \n",
    "                                                            JOIN\n",
    "                                                                (\n",
    "                                                                    select\n",
    "                                                                        cast(cr.id as string) as id \n",
    "                                                                    FROM\n",
    "                                                                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` cr \n",
    "                                                                    WHERE\n",
    "                                                                        concept_id IN (40493428, 252840, 4092524, 4178964, 76349, 4177243, 4313083, 4241905, 46273652, 196925, 4180791, 436045, 200962, 4091466, 4162860, 4294416, 4092212, 444411, 4154630, 442139, 4309225, 4298032, 40650479, 197807, 4091465, 40490994, 4216273, 133424, 4247726, 4247822, 4181483, 4162994, 4309851, 436671, 4180780, 4300555, 4160780, 4180910, 4247238, 4155297, 4178971, 443568, 75210, 442183, 40492021, 78987, 443388, 195483, 4155285, 4181488, 25189, 4180902, 36676291, 376918, 4297666, 4157332, 40488919, 380661, 256633, 133711, 200348, 4174593, 433143, 4178977, 4178959, 443387, 40480128, 432263, 4312698, 4315805, 4112853, 4297185, 4181333, 442181, 46270738, 4178974, 4246450, 442131, 443391, 444410, 137809, 4181330, 4313916, 40487047, 192581, 4314047, 4092217, 4181351, 37397344, 4112745, 257503, 438693, 139750, 4181338, 198091, 436357, 196359, 432257, 4246451, 258375, 200052, 40488964, 256646, 436353, 438094, 4312023, 31509, 201238, 37310458, 4095312, 138996, 4162253, 378087, 435484, 374874, 36684472, 4314071, 4312288, 261514, 4181332, 44806773, 196360, 4188544, 4298026, 443392, 4295624, 432845, 45769720, 197507, 4233629, 4241917, 199754, 4160276, 440649, 443588, 4089655, 4092511, 439751, 432837, 4095740, 197808, 4162251, 200051, 4310566, 4187850, 26052, 4187849, 4311637, 4246017, 440658, 4247719, 4312685, 4241904, 4181480, 4311342, 4116084, 4111921, 36683531, 441805, 4298028, 437498, 4054503, 4298033, 192568, 4246127, 4312022, 4081044, 197500, 373425, 4338758, 443398, 376647, 4308621, 198988, 40491001, 40492037, 40490993, 4180793, 4181484, 4247338, 4297665, 441515, 74582, 442132, 261236, 4188545, 4180790, 4247331, 133726, 433716, 258369, 136917, 440345, 200959, 320342, 434298, 4334322, 4181350, 40492932, 4178968, 198700, 380055, 440956, 4111776, 4311619, 4247836, 4155171, 432833, 4111805, 433435, 201519, 438386, 4178976, 192855, 4157457, 75512, 4311617, 4157456, 24897, 45770892, 4187848, 80045, 4116241, 45768522, 4311499, 72266, 255507, 193144, 30346, 4315806, 443561, 28083, 4001666, 4089665, 4089756, 439404, 435755, 42536893, 4312944, 443390, 40493020, 4187851, 441233, 4248061, 4247357, 4157454, 4313482, 438699, 318096, 4112974, 432254, 4158563, 4091464, 436635, 198985, 40482750, 254591, 4181343, 197804, 4151250, 40487143, 443386, 46270513, 4157449, 4181354, 4181339, 133147, 443389, 4092382, 45757101, 4313634, 4307263, 4092512, 194589, 253717, 136354, 764225, 195513, 4153882, 4157331, 4214901, 4181477) \n",
    "                                                                        AND full_text LIKE '%_rank1]%'\n",
    "                                                                ) a \n",
    "                                                                    ON (\n",
    "                                                                        c.path LIKE CONCAT('%.',\n",
    "                                                                    a.id,\n",
    "                                                                    '.%') \n",
    "                                                                    OR c.path LIKE CONCAT('%.',\n",
    "                                                                    a.id) \n",
    "                                                                    OR c.path LIKE CONCAT(a.id,\n",
    "                                                                    '.%') \n",
    "                                                                    OR c.path = a.id) \n",
    "                                                                WHERE\n",
    "                                                                    is_standard = 1 \n",
    "                                                                    AND is_selectable = 1\n",
    "                                                                ) \n",
    "                                                                AND is_standard = 1 \n",
    "                                                        )\n",
    "                                                    ) criteria \n",
    "                                                ) ))\n",
    "                                    ) d_exposure \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_standard_concept \n",
    "                                        ON d_exposure.drug_concept_id = d_standard_concept.concept_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_type \n",
    "                                        ON d_exposure.drug_type_concept_id = d_type.concept_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_route \n",
    "                                        ON d_exposure.route_concept_id = d_route.concept_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".visit_occurrence` v \n",
    "                                        ON d_exposure.visit_occurrence_id = v.visit_occurrence_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_visit \n",
    "                                        ON v.visit_concept_id = d_visit.concept_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_source_concept \n",
    "                                        ON d_exposure.drug_source_concept_id = d_source_concept.concept_id\"\"\"\n",
    "\n",
    "dataset_43182103_drug_df = pandas.read_gbq(\n",
    "    dataset_43182103_drug_sql,\n",
    "    dialect=\"standard\",\n",
    "    use_bqstorage_api=(\"BIGQUERY_STORAGE_API_ENABLED\" in os.environ),\n",
    "    progress_bar_type=\"tqdm_notebook\")\n",
    "\n",
    "dataset_43182103_drug_df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas\n",
    "import os\n",
    "\n",
    "# This query represents dataset \"D10--Mepiridine_Data_Set\" for domain \"drug\" and was generated for All of Us Registered Tier Dataset v7\n",
    "dataset_11043351_drug_sql = \"\"\"\n",
    "    SELECT\n",
    "        d_exposure.person_id,\n",
    "        d_exposure.drug_concept_id,\n",
    "        d_standard_concept.concept_name as standard_concept_name,\n",
    "        d_standard_concept.concept_code as standard_concept_code,\n",
    "        d_standard_concept.vocabulary_id as standard_vocabulary,\n",
    "        d_exposure.drug_exposure_start_datetime,\n",
    "        d_exposure.drug_exposure_end_datetime,\n",
    "        d_exposure.verbatim_end_date,\n",
    "        d_exposure.drug_type_concept_id,\n",
    "        d_type.concept_name as drug_type_concept_name,\n",
    "        d_exposure.stop_reason,\n",
    "        d_exposure.refills,\n",
    "        d_exposure.quantity,\n",
    "        d_exposure.days_supply,\n",
    "        d_exposure.sig,\n",
    "        d_exposure.route_concept_id,\n",
    "        d_route.concept_name as route_concept_name,\n",
    "        d_exposure.lot_number,\n",
    "        d_exposure.visit_occurrence_id,\n",
    "        d_visit.concept_name as visit_occurrence_concept_name,\n",
    "        d_exposure.drug_source_value,\n",
    "        d_exposure.drug_source_concept_id,\n",
    "        d_source_concept.concept_name as source_concept_name,\n",
    "        d_source_concept.concept_code as source_concept_code,\n",
    "        d_source_concept.vocabulary_id as source_vocabulary,\n",
    "        d_exposure.route_source_value,\n",
    "        d_exposure.dose_unit_source_value \n",
    "    FROM\n",
    "        ( SELECT\n",
    "            * \n",
    "        FROM\n",
    "            `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".drug_exposure` d_exposure \n",
    "        WHERE\n",
    "            (\n",
    "                drug_concept_id IN  (\n",
    "                    SELECT\n",
    "                        DISTINCT ca.descendant_id \n",
    "                    FROM\n",
    "                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria_ancestor` ca \n",
    "                    JOIN\n",
    "                        (\n",
    "                            select\n",
    "                                distinct c.concept_id \n",
    "                            FROM\n",
    "                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` c \n",
    "                            JOIN\n",
    "                                (\n",
    "                                    select\n",
    "                                        cast(cr.id as string) as id \n",
    "                                    FROM\n",
    "                                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` cr \n",
    "                                    WHERE\n",
    "                                        concept_id IN (\n",
    "                                            1102527\n",
    "                                        ) \n",
    "                                        AND full_text LIKE '%_rank1]%'\n",
    "                                ) a \n",
    "                                    ON (\n",
    "                                        c.path LIKE CONCAT('%.',\n",
    "                                    a.id,\n",
    "                                    '.%') \n",
    "                                    OR c.path LIKE CONCAT('%.',\n",
    "                                    a.id) \n",
    "                                    OR c.path LIKE CONCAT(a.id,\n",
    "                                    '.%') \n",
    "                                    OR c.path = a.id) \n",
    "                                WHERE\n",
    "                                    is_standard = 1 \n",
    "                                    AND is_selectable = 1\n",
    "                                ) b \n",
    "                                    ON (\n",
    "                                        ca.ancestor_id = b.concept_id\n",
    "                                    )\n",
    "                            )\n",
    "                        )  \n",
    "                        AND (\n",
    "                            d_exposure.PERSON_ID IN (\n",
    "                                SELECT\n",
    "                                    distinct person_id  \n",
    "                            FROM\n",
    "                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_search_person` cb_search_person  \n",
    "                            WHERE\n",
    "                                cb_search_person.person_id IN (\n",
    "                                    SELECT\n",
    "                                        criteria.person_id \n",
    "                                    FROM\n",
    "                                        (SELECT\n",
    "                                            DISTINCT person_id,\n",
    "                                            entry_date,\n",
    "                                            concept_id \n",
    "                                        FROM\n",
    "                                            `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_search_all_events` \n",
    "                                        WHERE\n",
    "                                            (\n",
    "                                                concept_id IN (\n",
    "                                                    SELECT\n",
    "                                                        DISTINCT ca.descendant_id \n",
    "                                                    FROM\n",
    "                                                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria_ancestor` ca \n",
    "                                                    JOIN\n",
    "                                                        (\n",
    "                                                            select\n",
    "                                                                distinct c.concept_id \n",
    "                                                            FROM\n",
    "                                                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` c \n",
    "                                                            JOIN\n",
    "                                                                (\n",
    "                                                                    select\n",
    "                                                                        cast(cr.id as string) as id \n",
    "                                                                    FROM\n",
    "                                                                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` cr \n",
    "                                                                    WHERE\n",
    "                                                                        concept_id IN (21604254) \n",
    "                                                                        AND full_text LIKE '%_rank1]%'\n",
    "                                                                ) a \n",
    "                                                                    ON (\n",
    "                                                                        c.path LIKE CONCAT('%.',\n",
    "                                                                    a.id,\n",
    "                                                                    '.%') \n",
    "                                                                    OR c.path LIKE CONCAT('%.',\n",
    "                                                                    a.id) \n",
    "                                                                    OR c.path LIKE CONCAT(a.id,\n",
    "                                                                    '.%') \n",
    "                                                                    OR c.path = a.id) \n",
    "                                                                WHERE\n",
    "                                                                    is_standard = 1 \n",
    "                                                                    AND is_selectable = 1\n",
    "                                                                ) b \n",
    "                                                                    ON (\n",
    "                                                                        ca.ancestor_id = b.concept_id\n",
    "                                                                    )\n",
    "                                                            ) \n",
    "                                                            AND is_standard = 1\n",
    "                                                        )\n",
    "                                                ) criteria \n",
    "                                            ) \n",
    "                                            AND cb_search_person.person_id NOT IN (\n",
    "                                                SELECT\n",
    "                                                    criteria.person_id \n",
    "                                            FROM\n",
    "                                                (SELECT\n",
    "                                                    DISTINCT person_id,\n",
    "                                                    entry_date,\n",
    "                                                    concept_id \n",
    "                                                FROM\n",
    "                                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_search_all_events` \n",
    "                                                WHERE\n",
    "                                                    (\n",
    "                                                        concept_id IN (\n",
    "                                                            SELECT\n",
    "                                                                DISTINCT c.concept_id \n",
    "                                                            FROM\n",
    "                                                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` c \n",
    "                                                            JOIN\n",
    "                                                                (\n",
    "                                                                    select\n",
    "                                                                        cast(cr.id as string) as id \n",
    "                                                                    FROM\n",
    "                                                                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` cr \n",
    "                                                                    WHERE\n",
    "                                                                        concept_id IN (40493428, 252840, 4092524, 4178964, 76349, 4177243, 4313083, 4241905, 46273652, 196925, 4180791, 436045, 200962, 4091466, 4162860, 4294416, 4092212, 444411, 4154630, 442139, 4309225, 4298032, 40650479, 197807, 4091465, 40490994, 4216273, 133424, 4247726, 4247822, 4181483, 4162994, 4309851, 436671, 4180780, 4300555, 4160780, 4180910, 4247238, 4155297, 4178971, 443568, 75210, 442183, 40492021, 78987, 443388, 195483, 4155285, 4181488, 25189, 4180902, 36676291, 376918, 4297666, 4157332, 40488919, 380661, 256633, 133711, 200348, 4174593, 433143, 4178977, 4178959, 443387, 40480128, 432263, 4312698, 4315805, 4112853, 4297185, 4181333, 442181, 46270738, 4178974, 4246450, 442131, 443391, 444410, 137809, 4181330, 4313916, 40487047, 192581, 4314047, 4092217, 4181351, 37397344, 4112745, 257503, 438693, 139750, 4181338, 198091, 436357, 196359, 432257, 4246451, 258375, 200052, 40488964, 256646, 436353, 438094, 4312023, 31509, 201238, 37310458, 4095312, 138996, 4162253, 378087, 435484, 374874, 36684472, 4314071, 4312288, 261514, 4181332, 44806773, 196360, 4188544, 4298026, 443392, 4295624, 432845, 45769720, 197507, 4233629, 4241917, 199754, 4160276, 440649, 443588, 4089655, 4092511, 439751, 432837, 4095740, 197808, 4162251, 200051, 4310566, 4187850, 26052, 4187849, 4311637, 4246017, 440658, 4247719, 4312685, 4241904, 4181480, 4311342, 4116084, 4111921, 36683531, 441805, 4298028, 437498, 4054503, 4298033, 192568, 4246127, 4312022, 4081044, 197500, 373425, 4338758, 443398, 376647, 4308621, 198988, 40491001, 40492037, 40490993, 4180793, 4181484, 4247338, 4297665, 441515, 74582, 442132, 261236, 4188545, 4180790, 4247331, 133726, 433716, 258369, 136917, 440345, 200959, 320342, 434298, 4334322, 4181350, 40492932, 4178968, 198700, 380055, 440956, 4111776, 4311619, 4247836, 4155171, 432833, 4111805, 433435, 201519, 438386, 4178976, 192855, 4157457, 75512, 4311617, 4157456, 24897, 45770892, 4187848, 80045, 4116241, 45768522, 4311499, 72266, 255507, 193144, 30346, 4315806, 443561, 28083, 4001666, 4089665, 4089756, 439404, 435755, 42536893, 4312944, 443390, 40493020, 4187851, 441233, 4248061, 4247357, 4157454, 4313482, 438699, 318096, 4112974, 432254, 4158563, 4091464, 436635, 198985, 40482750, 254591, 4181343, 197804, 4151250, 40487143, 443386, 46270513, 4157449, 4181354, 4181339, 133147, 443389, 4092382, 45757101, 4313634, 4307263, 4092512, 194589, 253717, 136354, 764225, 195513, 4153882, 4157331, 4214901, 4181477) \n",
    "                                                                        AND full_text LIKE '%_rank1]%'\n",
    "                                                                ) a \n",
    "                                                                    ON (\n",
    "                                                                        c.path LIKE CONCAT('%.',\n",
    "                                                                    a.id,\n",
    "                                                                    '.%') \n",
    "                                                                    OR c.path LIKE CONCAT('%.',\n",
    "                                                                    a.id) \n",
    "                                                                    OR c.path LIKE CONCAT(a.id,\n",
    "                                                                    '.%') \n",
    "                                                                    OR c.path = a.id) \n",
    "                                                                WHERE\n",
    "                                                                    is_standard = 1 \n",
    "                                                                    AND is_selectable = 1\n",
    "                                                                ) \n",
    "                                                                AND is_standard = 1 \n",
    "                                                        )\n",
    "                                                    ) criteria \n",
    "                                                ) ))\n",
    "                                    ) d_exposure \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_standard_concept \n",
    "                                        ON d_exposure.drug_concept_id = d_standard_concept.concept_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_type \n",
    "                                        ON d_exposure.drug_type_concept_id = d_type.concept_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_route \n",
    "                                        ON d_exposure.route_concept_id = d_route.concept_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".visit_occurrence` v \n",
    "                                        ON d_exposure.visit_occurrence_id = v.visit_occurrence_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_visit \n",
    "                                        ON v.visit_concept_id = d_visit.concept_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_source_concept \n",
    "                                        ON d_exposure.drug_source_concept_id = d_source_concept.concept_id\"\"\"\n",
    "\n",
    "dataset_11043351_drug_df = pandas.read_gbq(\n",
    "    dataset_11043351_drug_sql,\n",
    "    dialect=\"standard\",\n",
    "    use_bqstorage_api=(\"BIGQUERY_STORAGE_API_ENABLED\" in os.environ),\n",
    "    progress_bar_type=\"tqdm_notebook\")\n",
    "\n",
    "dataset_11043351_drug_df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas\n",
    "import os\n",
    "\n",
    "# This query represents dataset \"D11--Naltrexone_Data_Set\" for domain \"drug\" and was generated for All of Us Registered Tier Dataset v7\n",
    "dataset_02832134_drug_sql = \"\"\"\n",
    "    SELECT\n",
    "        d_exposure.person_id,\n",
    "        d_exposure.drug_concept_id,\n",
    "        d_standard_concept.concept_name as standard_concept_name,\n",
    "        d_standard_concept.concept_code as standard_concept_code,\n",
    "        d_standard_concept.vocabulary_id as standard_vocabulary,\n",
    "        d_exposure.drug_exposure_start_datetime,\n",
    "        d_exposure.drug_exposure_end_datetime,\n",
    "        d_exposure.verbatim_end_date,\n",
    "        d_exposure.drug_type_concept_id,\n",
    "        d_type.concept_name as drug_type_concept_name,\n",
    "        d_exposure.stop_reason,\n",
    "        d_exposure.refills,\n",
    "        d_exposure.quantity,\n",
    "        d_exposure.days_supply,\n",
    "        d_exposure.sig,\n",
    "        d_exposure.route_concept_id,\n",
    "        d_route.concept_name as route_concept_name,\n",
    "        d_exposure.lot_number,\n",
    "        d_exposure.visit_occurrence_id,\n",
    "        d_visit.concept_name as visit_occurrence_concept_name,\n",
    "        d_exposure.drug_source_value,\n",
    "        d_exposure.drug_source_concept_id,\n",
    "        d_source_concept.concept_name as source_concept_name,\n",
    "        d_source_concept.concept_code as source_concept_code,\n",
    "        d_source_concept.vocabulary_id as source_vocabulary,\n",
    "        d_exposure.route_source_value,\n",
    "        d_exposure.dose_unit_source_value \n",
    "    FROM\n",
    "        ( SELECT\n",
    "            * \n",
    "        FROM\n",
    "            `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".drug_exposure` d_exposure \n",
    "        WHERE\n",
    "            (\n",
    "                drug_concept_id IN  (\n",
    "                    SELECT\n",
    "                        DISTINCT ca.descendant_id \n",
    "                    FROM\n",
    "                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria_ancestor` ca \n",
    "                    JOIN\n",
    "                        (\n",
    "                            select\n",
    "                                distinct c.concept_id \n",
    "                            FROM\n",
    "                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` c \n",
    "                            JOIN\n",
    "                                (\n",
    "                                    select\n",
    "                                        cast(cr.id as string) as id \n",
    "                                    FROM\n",
    "                                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` cr \n",
    "                                    WHERE\n",
    "                                        concept_id IN (\n",
    "                                            1714319\n",
    "                                        ) \n",
    "                                        AND full_text LIKE '%_rank1]%'\n",
    "                                ) a \n",
    "                                    ON (\n",
    "                                        c.path LIKE CONCAT('%.',\n",
    "                                    a.id,\n",
    "                                    '.%') \n",
    "                                    OR c.path LIKE CONCAT('%.',\n",
    "                                    a.id) \n",
    "                                    OR c.path LIKE CONCAT(a.id,\n",
    "                                    '.%') \n",
    "                                    OR c.path = a.id) \n",
    "                                WHERE\n",
    "                                    is_standard = 1 \n",
    "                                    AND is_selectable = 1\n",
    "                                ) b \n",
    "                                    ON (\n",
    "                                        ca.ancestor_id = b.concept_id\n",
    "                                    )\n",
    "                            )\n",
    "                        )  \n",
    "                        AND (\n",
    "                            d_exposure.PERSON_ID IN (\n",
    "                                SELECT\n",
    "                                    distinct person_id  \n",
    "                            FROM\n",
    "                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_search_person` cb_search_person  \n",
    "                            WHERE\n",
    "                                cb_search_person.person_id IN (\n",
    "                                    SELECT\n",
    "                                        criteria.person_id \n",
    "                                    FROM\n",
    "                                        (SELECT\n",
    "                                            DISTINCT person_id,\n",
    "                                            entry_date,\n",
    "                                            concept_id \n",
    "                                        FROM\n",
    "                                            `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_search_all_events` \n",
    "                                        WHERE\n",
    "                                            (\n",
    "                                                concept_id IN (\n",
    "                                                    SELECT\n",
    "                                                        DISTINCT ca.descendant_id \n",
    "                                                    FROM\n",
    "                                                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria_ancestor` ca \n",
    "                                                    JOIN\n",
    "                                                        (\n",
    "                                                            select\n",
    "                                                                distinct c.concept_id \n",
    "                                                            FROM\n",
    "                                                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` c \n",
    "                                                            JOIN\n",
    "                                                                (\n",
    "                                                                    select\n",
    "                                                                        cast(cr.id as string) as id \n",
    "                                                                    FROM\n",
    "                                                                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` cr \n",
    "                                                                    WHERE\n",
    "                                                                        concept_id IN (21604254) \n",
    "                                                                        AND full_text LIKE '%_rank1]%'\n",
    "                                                                ) a \n",
    "                                                                    ON (\n",
    "                                                                        c.path LIKE CONCAT('%.',\n",
    "                                                                    a.id,\n",
    "                                                                    '.%') \n",
    "                                                                    OR c.path LIKE CONCAT('%.',\n",
    "                                                                    a.id) \n",
    "                                                                    OR c.path LIKE CONCAT(a.id,\n",
    "                                                                    '.%') \n",
    "                                                                    OR c.path = a.id) \n",
    "                                                                WHERE\n",
    "                                                                    is_standard = 1 \n",
    "                                                                    AND is_selectable = 1\n",
    "                                                                ) b \n",
    "                                                                    ON (\n",
    "                                                                        ca.ancestor_id = b.concept_id\n",
    "                                                                    )\n",
    "                                                            ) \n",
    "                                                            AND is_standard = 1\n",
    "                                                        )\n",
    "                                                ) criteria \n",
    "                                            ) \n",
    "                                            AND cb_search_person.person_id NOT IN (\n",
    "                                                SELECT\n",
    "                                                    criteria.person_id \n",
    "                                            FROM\n",
    "                                                (SELECT\n",
    "                                                    DISTINCT person_id,\n",
    "                                                    entry_date,\n",
    "                                                    concept_id \n",
    "                                                FROM\n",
    "                                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_search_all_events` \n",
    "                                                WHERE\n",
    "                                                    (\n",
    "                                                        concept_id IN (\n",
    "                                                            SELECT\n",
    "                                                                DISTINCT c.concept_id \n",
    "                                                            FROM\n",
    "                                                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` c \n",
    "                                                            JOIN\n",
    "                                                                (\n",
    "                                                                    select\n",
    "                                                                        cast(cr.id as string) as id \n",
    "                                                                    FROM\n",
    "                                                                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` cr \n",
    "                                                                    WHERE\n",
    "                                                                        concept_id IN (40493428, 252840, 4092524, 4178964, 76349, 4177243, 4313083, 4241905, 46273652, 196925, 4180791, 436045, 200962, 4091466, 4162860, 4294416, 4092212, 444411, 4154630, 442139, 4309225, 4298032, 40650479, 197807, 4091465, 40490994, 4216273, 133424, 4247726, 4247822, 4181483, 4162994, 4309851, 436671, 4180780, 4300555, 4160780, 4180910, 4247238, 4155297, 4178971, 443568, 75210, 442183, 40492021, 78987, 443388, 195483, 4155285, 4181488, 25189, 4180902, 36676291, 376918, 4297666, 4157332, 40488919, 380661, 256633, 133711, 200348, 4174593, 433143, 4178977, 4178959, 443387, 40480128, 432263, 4312698, 4315805, 4112853, 4297185, 4181333, 442181, 46270738, 4178974, 4246450, 442131, 443391, 444410, 137809, 4181330, 4313916, 40487047, 192581, 4314047, 4092217, 4181351, 37397344, 4112745, 257503, 438693, 139750, 4181338, 198091, 436357, 196359, 432257, 4246451, 258375, 200052, 40488964, 256646, 436353, 438094, 4312023, 31509, 201238, 37310458, 4095312, 138996, 4162253, 378087, 435484, 374874, 36684472, 4314071, 4312288, 261514, 4181332, 44806773, 196360, 4188544, 4298026, 443392, 4295624, 432845, 45769720, 197507, 4233629, 4241917, 199754, 4160276, 440649, 443588, 4089655, 4092511, 439751, 432837, 4095740, 197808, 4162251, 200051, 4310566, 4187850, 26052, 4187849, 4311637, 4246017, 440658, 4247719, 4312685, 4241904, 4181480, 4311342, 4116084, 4111921, 36683531, 441805, 4298028, 437498, 4054503, 4298033, 192568, 4246127, 4312022, 4081044, 197500, 373425, 4338758, 443398, 376647, 4308621, 198988, 40491001, 40492037, 40490993, 4180793, 4181484, 4247338, 4297665, 441515, 74582, 442132, 261236, 4188545, 4180790, 4247331, 133726, 433716, 258369, 136917, 440345, 200959, 320342, 434298, 4334322, 4181350, 40492932, 4178968, 198700, 380055, 440956, 4111776, 4311619, 4247836, 4155171, 432833, 4111805, 433435, 201519, 438386, 4178976, 192855, 4157457, 75512, 4311617, 4157456, 24897, 45770892, 4187848, 80045, 4116241, 45768522, 4311499, 72266, 255507, 193144, 30346, 4315806, 443561, 28083, 4001666, 4089665, 4089756, 439404, 435755, 42536893, 4312944, 443390, 40493020, 4187851, 441233, 4248061, 4247357, 4157454, 4313482, 438699, 318096, 4112974, 432254, 4158563, 4091464, 436635, 198985, 40482750, 254591, 4181343, 197804, 4151250, 40487143, 443386, 46270513, 4157449, 4181354, 4181339, 133147, 443389, 4092382, 45757101, 4313634, 4307263, 4092512, 194589, 253717, 136354, 764225, 195513, 4153882, 4157331, 4214901, 4181477) \n",
    "                                                                        AND full_text LIKE '%_rank1]%'\n",
    "                                                                ) a \n",
    "                                                                    ON (\n",
    "                                                                        c.path LIKE CONCAT('%.',\n",
    "                                                                    a.id,\n",
    "                                                                    '.%') \n",
    "                                                                    OR c.path LIKE CONCAT('%.',\n",
    "                                                                    a.id) \n",
    "                                                                    OR c.path LIKE CONCAT(a.id,\n",
    "                                                                    '.%') \n",
    "                                                                    OR c.path = a.id) \n",
    "                                                                WHERE\n",
    "                                                                    is_standard = 1 \n",
    "                                                                    AND is_selectable = 1\n",
    "                                                                ) \n",
    "                                                                AND is_standard = 1 \n",
    "                                                        )\n",
    "                                                    ) criteria \n",
    "                                                ) ))\n",
    "                                    ) d_exposure \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_standard_concept \n",
    "                                        ON d_exposure.drug_concept_id = d_standard_concept.concept_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_type \n",
    "                                        ON d_exposure.drug_type_concept_id = d_type.concept_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_route \n",
    "                                        ON d_exposure.route_concept_id = d_route.concept_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".visit_occurrence` v \n",
    "                                        ON d_exposure.visit_occurrence_id = v.visit_occurrence_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_visit \n",
    "                                        ON v.visit_concept_id = d_visit.concept_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_source_concept \n",
    "                                        ON d_exposure.drug_source_concept_id = d_source_concept.concept_id\"\"\"\n",
    "\n",
    "dataset_02832134_drug_df = pandas.read_gbq(\n",
    "    dataset_02832134_drug_sql,\n",
    "    dialect=\"standard\",\n",
    "    use_bqstorage_api=(\"BIGQUERY_STORAGE_API_ENABLED\" in os.environ),\n",
    "    progress_bar_type=\"tqdm_notebook\")\n",
    "\n",
    "dataset_02832134_drug_df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas\n",
    "import os\n",
    "\n",
    "# This query represents dataset \"D12--Acetaminophen_Data_Set\" for domain \"drug\" and was generated for All of Us Registered Tier Dataset v7\n",
    "dataset_35513565_drug_sql = \"\"\"\n",
    "    SELECT\n",
    "        d_exposure.person_id,\n",
    "        d_exposure.drug_concept_id,\n",
    "        d_standard_concept.concept_name as standard_concept_name,\n",
    "        d_standard_concept.concept_code as standard_concept_code,\n",
    "        d_standard_concept.vocabulary_id as standard_vocabulary,\n",
    "        d_exposure.drug_exposure_start_datetime,\n",
    "        d_exposure.drug_exposure_end_datetime,\n",
    "        d_exposure.verbatim_end_date,\n",
    "        d_exposure.drug_type_concept_id,\n",
    "        d_type.concept_name as drug_type_concept_name,\n",
    "        d_exposure.stop_reason,\n",
    "        d_exposure.refills,\n",
    "        d_exposure.quantity,\n",
    "        d_exposure.days_supply,\n",
    "        d_exposure.sig,\n",
    "        d_exposure.route_concept_id,\n",
    "        d_route.concept_name as route_concept_name,\n",
    "        d_exposure.lot_number,\n",
    "        d_exposure.visit_occurrence_id,\n",
    "        d_visit.concept_name as visit_occurrence_concept_name,\n",
    "        d_exposure.drug_source_value,\n",
    "        d_exposure.drug_source_concept_id,\n",
    "        d_source_concept.concept_name as source_concept_name,\n",
    "        d_source_concept.concept_code as source_concept_code,\n",
    "        d_source_concept.vocabulary_id as source_vocabulary,\n",
    "        d_exposure.route_source_value,\n",
    "        d_exposure.dose_unit_source_value \n",
    "    FROM\n",
    "        ( SELECT\n",
    "            * \n",
    "        FROM\n",
    "            `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".drug_exposure` d_exposure \n",
    "        WHERE\n",
    "            (\n",
    "                drug_concept_id IN  (\n",
    "                    SELECT\n",
    "                        DISTINCT ca.descendant_id \n",
    "                    FROM\n",
    "                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria_ancestor` ca \n",
    "                    JOIN\n",
    "                        (\n",
    "                            select\n",
    "                                distinct c.concept_id \n",
    "                            FROM\n",
    "                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` c \n",
    "                            JOIN\n",
    "                                (\n",
    "                                    select\n",
    "                                        cast(cr.id as string) as id \n",
    "                                    FROM\n",
    "                                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` cr \n",
    "                                    WHERE\n",
    "                                        concept_id IN (\n",
    "                                            1125315\n",
    "                                        ) \n",
    "                                        AND full_text LIKE '%_rank1]%'\n",
    "                                ) a \n",
    "                                    ON (\n",
    "                                        c.path LIKE CONCAT('%.',\n",
    "                                    a.id,\n",
    "                                    '.%') \n",
    "                                    OR c.path LIKE CONCAT('%.',\n",
    "                                    a.id) \n",
    "                                    OR c.path LIKE CONCAT(a.id,\n",
    "                                    '.%') \n",
    "                                    OR c.path = a.id) \n",
    "                                WHERE\n",
    "                                    is_standard = 1 \n",
    "                                    AND is_selectable = 1\n",
    "                                ) b \n",
    "                                    ON (\n",
    "                                        ca.ancestor_id = b.concept_id\n",
    "                                    )\n",
    "                            )\n",
    "                        )  \n",
    "                        AND (\n",
    "                            d_exposure.PERSON_ID IN (\n",
    "                                SELECT\n",
    "                                    distinct person_id  \n",
    "                            FROM\n",
    "                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_search_person` cb_search_person  \n",
    "                            WHERE\n",
    "                                cb_search_person.person_id IN (\n",
    "                                    SELECT\n",
    "                                        criteria.person_id \n",
    "                                    FROM\n",
    "                                        (SELECT\n",
    "                                            DISTINCT person_id,\n",
    "                                            entry_date,\n",
    "                                            concept_id \n",
    "                                        FROM\n",
    "                                            `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_search_all_events` \n",
    "                                        WHERE\n",
    "                                            (\n",
    "                                                concept_id IN (\n",
    "                                                    SELECT\n",
    "                                                        DISTINCT ca.descendant_id \n",
    "                                                    FROM\n",
    "                                                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria_ancestor` ca \n",
    "                                                    JOIN\n",
    "                                                        (\n",
    "                                                            select\n",
    "                                                                distinct c.concept_id \n",
    "                                                            FROM\n",
    "                                                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` c \n",
    "                                                            JOIN\n",
    "                                                                (\n",
    "                                                                    select\n",
    "                                                                        cast(cr.id as string) as id \n",
    "                                                                    FROM\n",
    "                                                                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` cr \n",
    "                                                                    WHERE\n",
    "                                                                        concept_id IN (21604254) \n",
    "                                                                        AND full_text LIKE '%_rank1]%'\n",
    "                                                                ) a \n",
    "                                                                    ON (\n",
    "                                                                        c.path LIKE CONCAT('%.',\n",
    "                                                                    a.id,\n",
    "                                                                    '.%') \n",
    "                                                                    OR c.path LIKE CONCAT('%.',\n",
    "                                                                    a.id) \n",
    "                                                                    OR c.path LIKE CONCAT(a.id,\n",
    "                                                                    '.%') \n",
    "                                                                    OR c.path = a.id) \n",
    "                                                                WHERE\n",
    "                                                                    is_standard = 1 \n",
    "                                                                    AND is_selectable = 1\n",
    "                                                                ) b \n",
    "                                                                    ON (\n",
    "                                                                        ca.ancestor_id = b.concept_id\n",
    "                                                                    )\n",
    "                                                            ) \n",
    "                                                            AND is_standard = 1\n",
    "                                                        )\n",
    "                                                ) criteria \n",
    "                                            ) \n",
    "                                            AND cb_search_person.person_id NOT IN (\n",
    "                                                SELECT\n",
    "                                                    criteria.person_id \n",
    "                                            FROM\n",
    "                                                (SELECT\n",
    "                                                    DISTINCT person_id,\n",
    "                                                    entry_date,\n",
    "                                                    concept_id \n",
    "                                                FROM\n",
    "                                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_search_all_events` \n",
    "                                                WHERE\n",
    "                                                    (\n",
    "                                                        concept_id IN (\n",
    "                                                            SELECT\n",
    "                                                                DISTINCT c.concept_id \n",
    "                                                            FROM\n",
    "                                                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` c \n",
    "                                                            JOIN\n",
    "                                                                (\n",
    "                                                                    select\n",
    "                                                                        cast(cr.id as string) as id \n",
    "                                                                    FROM\n",
    "                                                                        `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".cb_criteria` cr \n",
    "                                                                    WHERE\n",
    "                                                                        concept_id IN (40493428, 252840, 4092524, 4178964, 76349, 4177243, 4313083, 4241905, 46273652, 196925, 4180791, 436045, 200962, 4091466, 4162860, 4294416, 4092212, 444411, 4154630, 442139, 4309225, 4298032, 40650479, 197807, 4091465, 40490994, 4216273, 133424, 4247726, 4247822, 4181483, 4162994, 4309851, 436671, 4180780, 4300555, 4160780, 4180910, 4247238, 4155297, 4178971, 443568, 75210, 442183, 40492021, 78987, 443388, 195483, 4155285, 4181488, 25189, 4180902, 36676291, 376918, 4297666, 4157332, 40488919, 380661, 256633, 133711, 200348, 4174593, 433143, 4178977, 4178959, 443387, 40480128, 432263, 4312698, 4315805, 4112853, 4297185, 4181333, 442181, 46270738, 4178974, 4246450, 442131, 443391, 444410, 137809, 4181330, 4313916, 40487047, 192581, 4314047, 4092217, 4181351, 37397344, 4112745, 257503, 438693, 139750, 4181338, 198091, 436357, 196359, 432257, 4246451, 258375, 200052, 40488964, 256646, 436353, 438094, 4312023, 31509, 201238, 37310458, 4095312, 138996, 4162253, 378087, 435484, 374874, 36684472, 4314071, 4312288, 261514, 4181332, 44806773, 196360, 4188544, 4298026, 443392, 4295624, 432845, 45769720, 197507, 4233629, 4241917, 199754, 4160276, 440649, 443588, 4089655, 4092511, 439751, 432837, 4095740, 197808, 4162251, 200051, 4310566, 4187850, 26052, 4187849, 4311637, 4246017, 440658, 4247719, 4312685, 4241904, 4181480, 4311342, 4116084, 4111921, 36683531, 441805, 4298028, 437498, 4054503, 4298033, 192568, 4246127, 4312022, 4081044, 197500, 373425, 4338758, 443398, 376647, 4308621, 198988, 40491001, 40492037, 40490993, 4180793, 4181484, 4247338, 4297665, 441515, 74582, 442132, 261236, 4188545, 4180790, 4247331, 133726, 433716, 258369, 136917, 440345, 200959, 320342, 434298, 4334322, 4181350, 40492932, 4178968, 198700, 380055, 440956, 4111776, 4311619, 4247836, 4155171, 432833, 4111805, 433435, 201519, 438386, 4178976, 192855, 4157457, 75512, 4311617, 4157456, 24897, 45770892, 4187848, 80045, 4116241, 45768522, 4311499, 72266, 255507, 193144, 30346, 4315806, 443561, 28083, 4001666, 4089665, 4089756, 439404, 435755, 42536893, 4312944, 443390, 40493020, 4187851, 441233, 4248061, 4247357, 4157454, 4313482, 438699, 318096, 4112974, 432254, 4158563, 4091464, 436635, 198985, 40482750, 254591, 4181343, 197804, 4151250, 40487143, 443386, 46270513, 4157449, 4181354, 4181339, 133147, 443389, 4092382, 45757101, 4313634, 4307263, 4092512, 194589, 253717, 136354, 764225, 195513, 4153882, 4157331, 4214901, 4181477) \n",
    "                                                                        AND full_text LIKE '%_rank1]%'\n",
    "                                                                ) a \n",
    "                                                                    ON (\n",
    "                                                                        c.path LIKE CONCAT('%.',\n",
    "                                                                    a.id,\n",
    "                                                                    '.%') \n",
    "                                                                    OR c.path LIKE CONCAT('%.',\n",
    "                                                                    a.id) \n",
    "                                                                    OR c.path LIKE CONCAT(a.id,\n",
    "                                                                    '.%') \n",
    "                                                                    OR c.path = a.id) \n",
    "                                                                WHERE\n",
    "                                                                    is_standard = 1 \n",
    "                                                                    AND is_selectable = 1\n",
    "                                                                ) \n",
    "                                                                AND is_standard = 1 \n",
    "                                                        )\n",
    "                                                    ) criteria \n",
    "                                                ) ))\n",
    "                                    ) d_exposure \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_standard_concept \n",
    "                                        ON d_exposure.drug_concept_id = d_standard_concept.concept_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_type \n",
    "                                        ON d_exposure.drug_type_concept_id = d_type.concept_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_route \n",
    "                                        ON d_exposure.route_concept_id = d_route.concept_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".visit_occurrence` v \n",
    "                                        ON d_exposure.visit_occurrence_id = v.visit_occurrence_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_visit \n",
    "                                        ON v.visit_concept_id = d_visit.concept_id \n",
    "                                LEFT JOIN\n",
    "                                    `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` d_source_concept \n",
    "                                        ON d_exposure.drug_source_concept_id = d_source_concept.concept_id\"\"\"\n",
    "\n",
    "dataset_35513565_drug_df = pandas.read_gbq(\n",
    "    dataset_35513565_drug_sql,\n",
    "    dialect=\"standard\",\n",
    "    use_bqstorage_api=(\"BIGQUERY_STORAGE_API_ENABLED\" in os.environ),\n",
    "    progress_bar_type=\"tqdm_notebook\")\n",
    "\n",
    "dataset_35513565_drug_df.head(5)"
   ]
  }
 ],
 "metadata": {
  "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.7.12"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}