Understanding_Workbench.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas\n",
    "import os\n",
    "\n",
    "# This query represents dataset \"test\" for domain \"condition\" and was generated for All of Us Registered Tier Dataset v7\n",
    "dataset_19328041_condition_sql = \"\"\"\n",
    "    SELECT\n",
    "        c_occurrence.person_id,\n",
    "        c_occurrence.condition_concept_id,\n",
    "        c_standard_concept.concept_name as standard_concept_name,\n",
    "        c_standard_concept.concept_code as standard_concept_code,\n",
    "        c_standard_concept.vocabulary_id as standard_vocabulary,\n",
    "        c_occurrence.condition_start_datetime,\n",
    "        c_occurrence.condition_end_datetime,\n",
    "        c_occurrence.condition_type_concept_id,\n",
    "        c_type.concept_name as condition_type_concept_name,\n",
    "        c_occurrence.stop_reason,\n",
    "        c_occurrence.visit_occurrence_id,\n",
    "        visit.concept_name as visit_occurrence_concept_name,\n",
    "        c_occurrence.condition_source_value,\n",
    "        c_occurrence.condition_source_concept_id,\n",
    "        c_source_concept.concept_name as source_concept_name,\n",
    "        c_source_concept.concept_code as source_concept_code,\n",
    "        c_source_concept.vocabulary_id as source_vocabulary,\n",
    "        c_occurrence.condition_status_source_value,\n",
    "        c_occurrence.condition_status_concept_id,\n",
    "        c_status.concept_name as condition_status_concept_name \n",
    "    FROM\n",
    "        ( SELECT\n",
    "            * \n",
    "        FROM\n",
    "            `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".condition_occurrence` c_occurrence \n",
    "        WHERE\n",
    "            (\n",
    "                condition_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 (\n",
    "                                    316866, 440383, 443392\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",
    "                        )\n",
    "                )  \n",
    "                AND (\n",
    "                    c_occurrence.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",
    "                                ) c_occurrence \n",
    "                            LEFT JOIN\n",
    "                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` c_standard_concept \n",
    "                                    ON c_occurrence.condition_concept_id = c_standard_concept.concept_id \n",
    "                            LEFT JOIN\n",
    "                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` c_type \n",
    "                                    ON c_occurrence.condition_type_concept_id = c_type.concept_id \n",
    "                            LEFT JOIN\n",
    "                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".visit_occurrence` v \n",
    "                                    ON c_occurrence.visit_occurrence_id = v.visit_occurrence_id \n",
    "                            LEFT JOIN\n",
    "                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` visit \n",
    "                                    ON v.visit_concept_id = visit.concept_id \n",
    "                            LEFT JOIN\n",
    "                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` c_source_concept \n",
    "                                    ON c_occurrence.condition_source_concept_id = c_source_concept.concept_id \n",
    "                            LEFT JOIN\n",
    "                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` c_status \n",
    "                                    ON c_occurrence.condition_status_concept_id = c_status.concept_id\"\"\"\n",
    "\n",
    "dataset_19328041_condition_df = pandas.read_gbq(\n",
    "    dataset_19328041_condition_sql,\n",
    "    dialect=\"standard\",\n",
    "    use_bqstorage_api=(\"BIGQUERY_STORAGE_API_ENABLED\" in os.environ),\n",
    "    progress_bar_type=\"tqdm_notebook\")\n",
    "\n",
    "dataset_19328041_condition_df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas\n",
    "import os\n",
    "\n",
    "# This query represents dataset \"cohort_with_all_conditions\" for domain \"condition\" and was generated for All of Us Registered Tier Dataset v7\n",
    "dataset_34846739_condition_sql = \"\"\"\n",
    "    SELECT\n",
    "        c_occurrence.person_id,\n",
    "        c_occurrence.condition_concept_id,\n",
    "        c_standard_concept.concept_name as standard_concept_name,\n",
    "        c_standard_concept.concept_code as standard_concept_code,\n",
    "        c_standard_concept.vocabulary_id as standard_vocabulary,\n",
    "        c_occurrence.condition_start_datetime,\n",
    "        c_occurrence.condition_end_datetime,\n",
    "        c_occurrence.condition_type_concept_id,\n",
    "        c_type.concept_name as condition_type_concept_name,\n",
    "        c_occurrence.stop_reason,\n",
    "        c_occurrence.visit_occurrence_id,\n",
    "        visit.concept_name as visit_occurrence_concept_name,\n",
    "        c_occurrence.condition_source_value,\n",
    "        c_occurrence.condition_source_concept_id,\n",
    "        c_source_concept.concept_name as source_concept_name,\n",
    "        c_source_concept.concept_code as source_concept_code,\n",
    "        c_source_concept.vocabulary_id as source_vocabulary,\n",
    "        c_occurrence.condition_status_source_value,\n",
    "        c_occurrence.condition_status_concept_id,\n",
    "        c_status.concept_name as condition_status_concept_name \n",
    "    FROM\n",
    "        ( SELECT\n",
    "            * \n",
    "        FROM\n",
    "            `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".condition_occurrence` c_occurrence \n",
    "        WHERE\n",
    "            (\n",
    "                condition_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 (\n",
    "                                    137193, 137977, 3663207, 37311320, 4011630, 4021667, 4024705, 4027304, 4041287, 4042140, 40480457, 4070767, 4089228, 4094294, 4132559, 4167864, 4168498, 4172024, 4179304, 4196732, 4213737, 4274025, 4275722, 4297708, 43021226, 4303401, 432453, 433595, 437312, 437382\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",
    "                        )\n",
    "                )  \n",
    "                AND (\n",
    "                    c_occurrence.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",
    "                                ) c_occurrence \n",
    "                            LEFT JOIN\n",
    "                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` c_standard_concept \n",
    "                                    ON c_occurrence.condition_concept_id = c_standard_concept.concept_id \n",
    "                            LEFT JOIN\n",
    "                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` c_type \n",
    "                                    ON c_occurrence.condition_type_concept_id = c_type.concept_id \n",
    "                            LEFT JOIN\n",
    "                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".visit_occurrence` v \n",
    "                                    ON c_occurrence.visit_occurrence_id = v.visit_occurrence_id \n",
    "                            LEFT JOIN\n",
    "                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` visit \n",
    "                                    ON v.visit_concept_id = visit.concept_id \n",
    "                            LEFT JOIN\n",
    "                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` c_source_concept \n",
    "                                    ON c_occurrence.condition_source_concept_id = c_source_concept.concept_id \n",
    "                            LEFT JOIN\n",
    "                                `\"\"\" + os.environ[\"WORKSPACE_CDR\"] + \"\"\".concept` c_status \n",
    "                                    ON c_occurrence.condition_status_concept_id = c_status.concept_id\"\"\"\n",
    "\n",
    "dataset_34846739_condition_df = pandas.read_gbq(\n",
    "    dataset_34846739_condition_sql,\n",
    "    dialect=\"standard\",\n",
    "    use_bqstorage_api=(\"BIGQUERY_STORAGE_API_ENABLED\" in os.environ),\n",
    "    progress_bar_type=\"tqdm_notebook\")\n",
    "\n",
    "dataset_34846739_condition_df.head(50)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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
}