get_demo_codes.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "id": "initial_id",
   "metadata": {
    "collapsed": true,
    "ExecuteTime": {
     "end_time": "2025-02-20T20:24:03.932671Z",
     "start_time": "2025-02-20T20:23:57.034146Z"
    }
   },
   "source": [
    "import pandas as pd\n",
    "from sqlalchemy import create_engine\n",
    "from dotenv import load_dotenv\n",
    "import os\n",
    "\n",
    "load_dotenv('.env')\n",
    "engine = create_engine(os.environ.get(\"ALAB_DB_URL\"))"
   ],
   "outputs": [],
   "execution_count": 1
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-02-20T20:34:07.724877Z",
     "start_time": "2025-02-20T20:34:01.892693Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# select the vocabulary_id and concept_code for ten codes in each vocabulary\n",
    "\n",
    "codes = pd.DataFrame()\n",
    "\n",
    "with engine.connect() as conn:\n",
    "    result = conn.execute(\"\"\"SELECT vocabulary_id\n",
    "    FROM vocab.vocabulary\n",
    "    WHERE NOT vocabulary_reference\n",
    "    IN ('https://github.com/OHDSI/CommonDataModel','OMOP generated')\n",
    "    AND NOT vocabulary_id = 'CMS Place of Service'\"\"\").fetchall()\n",
    "    vocabularies = [row[0] for row in result]\n",
    "\n",
    "for vocab in vocabularies:\n",
    "    sql = f\"\"\"\n",
    "    SELECT vocabulary_id, concept_code\n",
    "    FROM vocab.concept\n",
    "    WHERE vocabulary_id = '{vocab}'\n",
    "    LIMIT 10\n",
    "    \"\"\"\n",
    "    codes = pd.concat([codes, pd.read_sql(sql, engine)])"
   ],
   "id": "c59ac8a05b60f9b3",
   "outputs": [],
   "execution_count": 17
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-02-20T20:34:30.027250Z",
     "start_time": "2025-02-20T20:34:30.020714Z"
    }
   },
   "cell_type": "code",
   "source": [
    "codes = codes.loc[~codes.concept_code.str.startswith('OMOP')]\n",
    "\n",
    "codes['stringrep'] = codes['vocabulary_id'] + ':' + codes['concept_code']\n",
    "codes_list = codes['stringrep'].tolist()"
   ],
   "id": "7a5fecb782ad770a",
   "outputs": [],
   "execution_count": 19
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-02-20T20:35:28.660122Z",
     "start_time": "2025-02-20T20:35:28.654960Z"
    }
   },
   "cell_type": "code",
   "source": "codes_list",
   "id": "abbf831154162626",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['ATC:P02CX03',\n",
       " 'ATC:L01AD08',\n",
       " 'ATC:S01AA28',\n",
       " 'ATC:N05CD14',\n",
       " 'ATC:L01XE52',\n",
       " 'ATC:A03FA09',\n",
       " 'ATC:A10BK05',\n",
       " 'ATC:A07XA06',\n",
       " 'ATC:A10BK06',\n",
       " 'ATC:V04CX06',\n",
       " 'CPT4:87635',\n",
       " 'CPT4:0174A',\n",
       " 'CPT4:1035646',\n",
       " 'CPT4:1035664',\n",
       " 'CPT4:1035672',\n",
       " 'CPT4:1035653',\n",
       " 'CPT4:1035654',\n",
       " 'CPT4:1035663',\n",
       " 'CPT4:1035662',\n",
       " 'CPT4:QA',\n",
       " 'Currency:AED',\n",
       " 'Currency:AFN',\n",
       " 'Currency:AMD',\n",
       " 'Currency:ANG',\n",
       " 'Currency:AOA',\n",
       " 'Currency:ARS',\n",
       " 'Currency:AUD',\n",
       " 'Currency:AWG',\n",
       " 'Currency:AZN',\n",
       " 'Currency:BAM',\n",
       " 'CVX:204',\n",
       " 'CVX:214',\n",
       " 'CVX:500',\n",
       " 'CVX:501',\n",
       " 'CVX:502',\n",
       " 'CVX:504',\n",
       " 'CVX:505',\n",
       " 'CVX:506',\n",
       " 'CVX:507',\n",
       " 'CVX:509',\n",
       " 'HCPCS:G2024',\n",
       " 'HCPCS:G2023',\n",
       " 'HCPCS:C9803',\n",
       " 'HCPCS:U0004',\n",
       " 'HCPCS:U0003',\n",
       " 'HCPCS:Q0239',\n",
       " 'HCPCS:M0239',\n",
       " 'HCPCS:Q0243',\n",
       " 'HCPCS:M0243',\n",
       " 'HCPCS:C1834',\n",
       " 'ICD10:A92.5',\n",
       " 'ICD10:B48.5',\n",
       " 'ICD10:G83.6',\n",
       " 'ICD10:G90.5',\n",
       " 'ICD10:G90.6',\n",
       " 'ICD10:G90.7',\n",
       " 'ICD10:G94.3',\n",
       " 'ICD10:J98.7',\n",
       " 'ICD10:K55.3',\n",
       " 'ICD10:K58.1',\n",
       " 'ICD10CM:J11.1',\n",
       " 'ICD10CM:M76.9',\n",
       " 'ICD10CM:O33.7',\n",
       " 'ICD10CM:Y07.9',\n",
       " 'ICD10CM:Y09',\n",
       " 'ICD10CM:Y82.8',\n",
       " 'ICD10CM:M1A.00',\n",
       " 'ICD10CM:M1A.011',\n",
       " 'ICD10CM:M1A.012',\n",
       " 'ICD10CM:M1A.019',\n",
       " 'ICD10PCS:X27K38',\n",
       " 'ICD10PCS:X27P3C',\n",
       " 'ICD10PCS:09UB0K',\n",
       " 'ICD10PCS:09UU0J',\n",
       " 'ICD10PCS:02H04Y',\n",
       " 'ICD10PCS:0D1887',\n",
       " 'ICD10PCS:03VG3H',\n",
       " 'ICD10PCS:0WC50',\n",
       " 'ICD10PCS:09UT3',\n",
       " 'ICD10PCS:X27S38',\n",
       " 'ICD9CM:003.20',\n",
       " 'ICD9CM:005.1',\n",
       " 'ICD9CM:005.81',\n",
       " 'ICD9CM:007',\n",
       " 'ICD9CM:008.04',\n",
       " 'ICD9CM:009.1',\n",
       " 'ICD9CM:009.2',\n",
       " 'ICD9CM:010.01',\n",
       " 'ICD9CM:010.05',\n",
       " 'ICD9CM:010.06',\n",
       " 'ICD9Proc:00',\n",
       " 'ICD9Proc:00.0',\n",
       " 'ICD9Proc:00.01',\n",
       " 'ICD9Proc:00.02',\n",
       " 'ICD9Proc:00.03',\n",
       " 'ICD9Proc:00.09',\n",
       " 'ICD9Proc:00.1',\n",
       " 'ICD9Proc:00.10',\n",
       " 'ICD9Proc:00.11',\n",
       " 'ICD9Proc:00.12',\n",
       " 'LOINC:94762-2',\n",
       " 'LOINC:94763-0',\n",
       " 'LOINC:94764-8',\n",
       " 'LOINC:94765-5',\n",
       " 'LOINC:94767-1',\n",
       " 'LOINC:94766-3',\n",
       " 'LOINC:94768-9',\n",
       " 'LOINC:94769-7',\n",
       " 'LOINC:94758-0',\n",
       " 'LOINC:94756-4',\n",
       " 'Medicare Specialty:23',\n",
       " 'Medicare Specialty:21',\n",
       " 'Medicare Specialty:C0',\n",
       " 'Medicare Specialty:C3',\n",
       " 'Medicare Specialty:C5',\n",
       " 'Medicare Specialty:C6',\n",
       " 'Medicare Specialty:C7',\n",
       " 'Medicare Specialty:C8',\n",
       " 'Medicare Specialty:C9',\n",
       " 'Medicare Specialty:1',\n",
       " 'MeSH:D000068817',\n",
       " 'MeSH:D000068818',\n",
       " 'MeSH:D000068836',\n",
       " 'MeSH:D000068878',\n",
       " 'MeSH:D000069450',\n",
       " 'MeSH:D000069544',\n",
       " 'MeSH:D000069547',\n",
       " 'MeSH:D000069583',\n",
       " 'MeSH:D000069594',\n",
       " 'MeSH:D000069603',\n",
       " 'NDC:00093322550',\n",
       " 'NDC:00378064101',\n",
       " 'NDC:00378064110',\n",
       " 'NDC:00378064201',\n",
       " 'NDC:00378064205',\n",
       " 'NDC:00378064210',\n",
       " 'NDC:00378807593',\n",
       " 'NDC:00409233601',\n",
       " 'NDC:00409233715',\n",
       " 'NDC:00409508211',\n",
       " 'NDFRT:N0000192483',\n",
       " 'NDFRT:N0000192452',\n",
       " 'NDFRT:N0000192469',\n",
       " 'NDFRT:N0000192453',\n",
       " 'NDFRT:N0000192479',\n",
       " 'NDFRT:N0000192458',\n",
       " 'NDFRT:N0000192490',\n",
       " 'NDFRT:N0000192451',\n",
       " 'NDFRT:N0000192467',\n",
       " 'NDFRT:N0000192468',\n",
       " 'NUCC:106E00000X',\n",
       " 'NUCC:106S00000X',\n",
       " 'NUCC:125Q00000X',\n",
       " 'NUCC:175T00000X',\n",
       " 'NUCC:1835C0205X',\n",
       " 'NUCC:1835P0200X',\n",
       " 'NUCC:1835P2201X',\n",
       " 'NUCC:207RA0001X',\n",
       " 'NUCC:207WX0009X',\n",
       " 'NUCC:207WX0107X',\n",
       " 'OMOP Invest Drug:SXQ3I65R57',\n",
       " 'OMOP Invest Drug:4L066368AS',\n",
       " 'OMOP Invest Drug:4U7K4N52ZM',\n",
       " 'OMOP Invest Drug:324268A537',\n",
       " 'OMOP Invest Drug:8JY6P22UVD',\n",
       " 'OMOP Invest Drug:84438Y4KT6',\n",
       " 'OMOP Invest Drug:0Y2S3XUQ5H',\n",
       " 'OMOP Invest Drug:L0P1807EUY',\n",
       " 'OMOP Invest Drug:01N552709G',\n",
       " 'OMOP Invest Drug:T2I226K69M',\n",
       " 'OSM:80500',\n",
       " 'OSM:2559345',\n",
       " 'OSM:2354197',\n",
       " 'OSM:2177207',\n",
       " 'OSM:82636',\n",
       " 'OSM:3225677',\n",
       " 'OSM:2177227',\n",
       " 'OSM:2357330',\n",
       " 'OSM:2316593',\n",
       " 'OSM:2574988',\n",
       " 'Race:2',\n",
       " 'Race:3',\n",
       " 'Race:9',\n",
       " 'Race:5',\n",
       " 'Race:UNK',\n",
       " 'Race:4',\n",
       " 'Race:1',\n",
       " 'Race:RAC100',\n",
       " 'Race:1.01',\n",
       " 'Race:1.02',\n",
       " 'Revenue Code:0001',\n",
       " 'Revenue Code:0022',\n",
       " 'Revenue Code:0023',\n",
       " 'Revenue Code:0024',\n",
       " 'Revenue Code:0100',\n",
       " 'Revenue Code:0101',\n",
       " 'Revenue Code:0110',\n",
       " 'Revenue Code:0111',\n",
       " 'Revenue Code:0112',\n",
       " 'Revenue Code:0113',\n",
       " 'RxNorm:26744',\n",
       " 'RxNorm:353465',\n",
       " 'RxNorm:412674',\n",
       " 'RxNorm:412675',\n",
       " 'RxNorm:825138',\n",
       " 'RxNorm:825139',\n",
       " 'RxNorm:825145',\n",
       " 'RxNorm:825146',\n",
       " 'RxNorm:825149',\n",
       " 'RxNorm:825152',\n",
       " 'SNOMED:32485007',\n",
       " 'SNOMED:73425007',\n",
       " 'SNOMED:41228009',\n",
       " 'SNOMED:261665005',\n",
       " 'SNOMED:18632008',\n",
       " 'SNOMED:74964007',\n",
       " 'SNOMED:55561003',\n",
       " 'SNOMED:281321000',\n",
       " 'SNOMED:260385009',\n",
       " 'SNOMED:260415000',\n",
       " 'SOPT:1',\n",
       " 'SOPT:11',\n",
       " 'SOPT:111',\n",
       " 'SOPT:112',\n",
       " 'SOPT:113',\n",
       " 'SOPT:12',\n",
       " 'SOPT:121',\n",
       " 'SOPT:129',\n",
       " 'SOPT:14',\n",
       " 'SOPT:2',\n",
       " 'SPL:497e462a-a295-4db1-9002-d2633efbd676',\n",
       " 'SPL:828e30fa-7bb1-486b-80ee-806fce45e4e4',\n",
       " 'SPL:1d1d27e7-7ef9-4c74-809a-695111208430',\n",
       " 'SPL:ee11db7b-91bd-4231-8783-889d98e4fa32',\n",
       " 'SPL:9e237635-6975-4930-9ce1-051d9e3bf615',\n",
       " 'SPL:2c8c977f-6acf-40f9-8a5b-253eb0188ed2',\n",
       " 'SPL:06b44e53-c6e7-4e12-a8fd-572d092cfa70',\n",
       " 'SPL:78345fc1-b1f5-4683-a917-bac0c7b47d95',\n",
       " 'SPL:1767d606-69b8-419e-840f-36550ed91b8f',\n",
       " 'SPL:4b1b7294-aca6-45fa-a008-c1869131b2a3',\n",
       " 'UB04 Point of Origin:1',\n",
       " 'UB04 Point of Origin:2',\n",
       " 'UB04 Point of Origin:4',\n",
       " 'UB04 Point of Origin:5',\n",
       " 'UB04 Point of Origin:6',\n",
       " 'UB04 Point of Origin:8',\n",
       " 'UB04 Point of Origin:9',\n",
       " 'UB04 Point of Origin:D',\n",
       " 'UB04 Point of Origin:E',\n",
       " 'UB04 Point of Origin:F',\n",
       " 'UB04 Pri Typ of Adm:1',\n",
       " 'UB04 Pri Typ of Adm:2',\n",
       " 'UB04 Pri Typ of Adm:3',\n",
       " 'UB04 Pri Typ of Adm:4',\n",
       " 'UB04 Pri Typ of Adm:5',\n",
       " 'UB04 Pri Typ of Adm:9',\n",
       " 'UB04 Pt dis status:0',\n",
       " 'UB04 Pt dis status:01',\n",
       " 'UB04 Pt dis status:02',\n",
       " 'UB04 Pt dis status:03',\n",
       " 'UB04 Pt dis status:04',\n",
       " 'UB04 Pt dis status:05',\n",
       " 'UB04 Pt dis status:06',\n",
       " 'UB04 Pt dis status:07',\n",
       " 'UB04 Pt dis status:09',\n",
       " 'UB04 Pt dis status:20',\n",
       " 'UB04 Typ bill:342',\n",
       " 'UB04 Typ bill:343',\n",
       " 'UB04 Typ bill:344',\n",
       " 'UB04 Typ bill:347',\n",
       " 'UB04 Typ bill:349',\n",
       " 'UB04 Typ bill:34I',\n",
       " 'UB04 Typ bill:34X',\n",
       " 'UB04 Typ bill:431',\n",
       " 'UB04 Typ bill:650',\n",
       " 'UB04 Typ bill:651',\n",
       " 'UCUM:{ai}',\n",
       " 'UCUM:cP',\n",
       " 'UCUM:[EU]',\n",
       " 'UCUM:{EV}',\n",
       " 'UCUM:pH',\n",
       " 'UCUM:{counts}/min',\n",
       " 'UCUM:{CD59-deficient cells}',\n",
       " 'UCUM:{DNR}',\n",
       " 'UCUM:{EOS}',\n",
       " \"UCUM:[GPS'U]\",\n",
       " 'US Census:0200000US1',\n",
       " 'US Census:0200000US2',\n",
       " 'US Census:0200000US4',\n",
       " 'US Census:0200000US3',\n",
       " 'US Census:0300000US1',\n",
       " 'US Census:0300000US2',\n",
       " 'US Census:0300000US3',\n",
       " 'US Census:0300000US4',\n",
       " 'US Census:0300000US6',\n",
       " 'US Census:0300000US7',\n",
       " 'VA Class:XA202',\n",
       " 'VA Class:XA200',\n",
       " 'VA Class:AM120',\n",
       " 'VA Class:XA605',\n",
       " 'VA Class:XA108',\n",
       " 'VA Class:XA516',\n",
       " 'VA Class:XA603',\n",
       " 'VA Class:XA855',\n",
       " 'VA Class:XA205',\n",
       " 'VA Class:XA606']"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 22
  },
  {
   "metadata": {},
   "cell_type": "code",
   "outputs": [],
   "execution_count": null,
   "source": "",
   "id": "7d94b996ae5e7d56"
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}