extract-price-data-from-rxnorm.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'\\nwith pill_leaves as (with recursive kids(name) as (\\n select distinct rxcui1 from rxnrel where rela=\\'doseformgroup_of\\' and rxcui2=\"1151133\"\\n    union\\n select rxcui2 from kids, rxnrel where rxnrel.rela=\"isa\" and rxnrel.rxcui1=kids.name\\n) select distinct name from kids)\\nselect count(*), tty from rxnconso where rxcui in (select name from pill_leaves) group by tty order by count(*);\\n\\n\\nwith pill_leaves as (with recursive kids(name) as (\\n select distinct rxcui1 from rxnrel where rela=\\'doseformgroup_of\\' and rxcui2=\"1151133\"\\n    union\\n select rxcui2 from kids, rxnrel where rxnrel.rela=\"isa\" and rxnrel.rxcui1=kids.name\\n) select distinct name from kids)\\nselect * from rxnconso where rxcui in (select name from pill_leaves) limit 200;\\n\\n'"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import sqlite3\n",
    "from frozendict import frozendict\n",
    "db = sqlite3.connect('/tmp/rxnorm.db')\n",
    "def dict_factory(cursor, row):\n",
    "    d = {}\n",
    "    for idx, col in enumerate(cursor.description):\n",
    "        d[col[0]] = row[idx]\n",
    "    return d\n",
    "\n",
    "db.row_factory = dict_factory\n",
    "c = db.cursor()\n",
    "from collections import defaultdict \n",
    "\n",
    "\n",
    "\"\"\"\n",
    "with pill_leaves as (with recursive kids(name) as (\n",
    " select distinct rxcui1 from rxnrel where rela='doseformgroup_of' and rxcui2=\"1151133\"\n",
    "    union\n",
    " select rxcui2 from kids, rxnrel where rxnrel.rela=\"isa\" and rxnrel.rxcui1=kids.name\n",
    ") select distinct name from kids)\n",
    "select count(*), tty from rxnconso where rxcui in (select name from pill_leaves) group by tty order by count(*);\n",
    "\n",
    "\n",
    "with pill_leaves as (with recursive kids(name) as (\n",
    " select distinct rxcui1 from rxnrel where rela='doseformgroup_of' and rxcui2=\"1151133\"\n",
    "    union\n",
    " select rxcui2 from kids, rxnrel where rxnrel.rela=\"isa\" and rxnrel.rxcui1=kids.name\n",
    ") select distinct name from kids)\n",
    "select * from rxnconso where rxcui in (select name from pill_leaves) limit 200;\n",
    "\n",
    "\"\"\"\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "4068\n"
     ]
    }
   ],
   "source": [
    "c.execute(\"\"\"\n",
    "select * from rxnconso where \n",
    "cvf='4096' and \n",
    "rxcui in\n",
    "  (select distinct rxcui1 from rxnrel where\n",
    "      rela='doseformgroup_of' and \n",
    "      rxcui2=\"1151133\")\n",
    "and tty in ('SCDG');\n",
    "\"\"\")\n",
    "generic_drugs = c.fetchall()\n",
    "\n",
    "c.execute(\"\"\"\n",
    "select * from rxnconso where \n",
    "cvf='4096' and \n",
    "rxcui in\n",
    "  (select distinct rxcui1 from rxnrel where\n",
    "      rela='doseformgroup_of' and \n",
    "      rxcui2=\"1151133\")\n",
    "and tty in ('SBDG');\n",
    "\"\"\")\n",
    "brand_drugs = c.fetchall()\n",
    "\n",
    "print len(generic_drugs) + len(brand_drugs)\n",
    "\n",
    "c.execute(\"\"\"\n",
    "select * from rxnconso where \n",
    "cvf='4096' and tty='PSN';\n",
    "\"\"\")\n",
    "preferred_terms = {x['RXCUI']: x for x in c.fetchall()}\n",
    "\n",
    "c.execute(\"\"\"\n",
    "select * from rxnconso where \n",
    "cvf='4096'\n",
    "and tty in ('SBDG', 'SCDG', 'SBD', 'SCD', 'SCDC', 'SBDC');\n",
    "\"\"\")\n",
    "all_drugs = {x['RXCUI']: x for x in c.fetchall()}\n",
    "for (k,v) in preferred_terms.iteritems():\n",
    "    if k not in all_drugs: continue\n",
    "    all_drugs[k]['PREFERRED'] = v['STR']\n",
    "\n",
    "pill_to_component_sets = defaultdict(set)\n",
    "parts_to_prescribables = defaultdict(set)\n",
    "prescribable_to_ingredients = defaultdict(set)\n",
    "sbd_to_scd = defaultdict(set)\n",
    "\n",
    "def to_type(sources, target):\n",
    "    subs = [x for x in sources]\n",
    "    subs.extend([target])\n",
    "    c = db.cursor()\n",
    "    q = \"select str, rxcui from rxnconso where rxcui in (select rxcui1 from rxnrel where rxcui2 in (%s) ) and sab='RXNORM' and tty= ? and cvf='4096' \"%\", \".join(\"?\" * (len(subs) - 1))\n",
    "    #print q\n",
    "    #print subs\n",
    "    c.execute(q, subs)\n",
    "    return [v['RXCUI'] for v in c.fetchall()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'CODE': u'863856',\n",
       " 'CVF': u'4096',\n",
       " 'ISPREF': u'',\n",
       " 'LAT': u'ENG',\n",
       " 'LUI': u'',\n",
       " 'PREFERRED': u'morphine sulfate 80 MG / naltrexone HCl 3.2 MG Extended Release Oral Capsule',\n",
       " 'RXAUI': u'6346936',\n",
       " 'RXCUI': u'863856',\n",
       " 'SAB': u'RXNORM',\n",
       " 'SAUI': u'6346936',\n",
       " 'SCUI': u'863856',\n",
       " 'SDUI': u'',\n",
       " 'SRL': u'',\n",
       " 'STR': u'Morphine Sulfate 80 MG / Naltrexone hydrochloride 3.2 MG Extended Release Oral Capsule',\n",
       " 'STT': u'',\n",
       " 'SUI': u'',\n",
       " 'SUPPRESS': u'N',\n",
       " 'TS': u'',\n",
       " 'TTY': u'SCD'}"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_drugs['863856']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# scdg -[inverse_isa]-> scd -[consists_of]-> scdc\n",
    "\n",
    "total = 0\n",
    "for d in [g['RXCUI'] for g in generic_drugs]:\n",
    "    scds = to_type([d], \"SCD\")\n",
    "    for scd in scds:\n",
    "        parts = to_type([scd], \"SCDC\")\n",
    "        partskey = frozenset(parts)\n",
    "        parts_to_prescribables[partskey].add(scd)\n",
    "        pill_to_component_sets[d].add(partskey)\n",
    "        mins = to_type(['863856'], \"MIN\")\n",
    "        assert(all_drugs[scd]['TTY'] == \"SCD\")\n",
    "        total += 1\n",
    "\n",
    "        if len(mins) > 0:\n",
    "            prescribable_to_ingredients[scd].update(mins)\n",
    "        else:\n",
    "            scdc= to_type(['209387'], \"SCDC\")\n",
    "            ins = to_type(scdc, \"IN\")\n",
    "            prescribable_to_ingredients[scd].update(ins)\n",
    "\n",
    "\n",
    "\n",
    "for d in [g['RXCUI'] for g in brand_drugs]:\n",
    "    sbds = to_type([d], \"SBD\")\n",
    "    for sbd in sbds:\n",
    "        parts = to_type([sbd], \"SBDC\")\n",
    "        partskey = frozenset(parts)\n",
    "        parts_to_prescribables[partskey].add(sbd)\n",
    "        pill_to_component_sets[d].add(partskey)\n",
    "        sbd_to_scd[sbd] = to_type([sbd], \"SCD\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n"
     ]
    }
   ],
   "source": [
    "print len(filter(lambda x: x==0, [len(pill_to_component_sets[v['RXCUI']]) for v in brand_drugs]))\n",
    "\n",
    "for v in brand_drugs:\n",
    "    if len(pill_to_component_sets[v['RXCUI']]) == 0:\n",
    "        print v['STR']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "json_db = {\n",
    "    'cuiToName': {},\n",
    "    'pillToComponentSets': {},\n",
    "    'componentSetsToPrescribables': {}\n",
    "}\n",
    "\n",
    "def name_of(cui):\n",
    "    r = all_drugs[cui]\n",
    "    #if \"PREFERRED\" in r:\n",
    "    #    return r[\"PREFERRED\"]\n",
    "    return r[\"STR\"]\n",
    "\n",
    "def sort_cuis(s):\n",
    "    return sorted(s, lambda a, b:  -1 if all_drugs[a]['STR'] < all_drugs[b]['STR'] else 1)\n",
    "\n",
    "def quote_component_set(s):\n",
    "    return \",\".join(sort_cuis(s))\n",
    "\n",
    "for (pill, component_sets) in pill_to_component_sets.iteritems():\n",
    "    json_db['cuiToName'][pill] = name_of(pill)\n",
    "    json_db['pillToComponentSets'][pill] = [sort_cuis(cs) for cs in component_sets]\n",
    "    for components in component_sets:\n",
    "        for c in components:\n",
    "            json_db['cuiToName'][c] = name_of(c)\n",
    "\n",
    "for (component_set, prescribables) in parts_to_prescribables.iteritems():\n",
    "    for p in prescribables:\n",
    "        json_db['cuiToName'][p] = name_of(p)\n",
    "    json_db['componentSetsToPrescribables'][quote_component_set(component_set)] = list(prescribables)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "3799\n"
     ]
    }
   ],
   "source": [
    "price_service_db = {\n",
    "    'genericPrices': {},\n",
    "    'brandToGeneric': {}\n",
    "}\n",
    "\n",
    "for b in sbd_to_scd:\n",
    "    price_service_db['brandToGeneric'][b] = list(sbd_to_scd[b])[0]\n",
    "    assert len(sbd_to_scd[b]) == 1\n",
    "\n",
    "print  len(price_service_db['brandToGeneric'].keys())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n"
     ]
    }
   ],
   "source": [
    "def get_prices(scd):\n",
    "    ingredient = prescribable_to_ingredients[scd]\n",
    "    assert len(ingredient) == 1\n",
    "    c = db.cursor()\n",
    "    q = \"select and sab='RXNORM' and tty= ? and cvf='4096' \"%\", \".join(\"?\" * (len(subs) - 1))\n",
    "    #print q\n",
    "    #print subs\n",
    "    c.execute(q, subs)\n",
    "\n",
    "for d in prescribable_to_ingredients.keys():\n",
    "    get_prices(d)\n",
    "\n",
    "print failures"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1709732"
      ]
     },
     "execution_count": 126,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import json\n",
    "extract = json.dumps(json_db)\n",
    "len(extract)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "extract = [{'str': d['STR'][:-5], 'cui': d['RXCUI']} for d in  drugs]\n",
    "print len(extract), extract[100]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import json\n",
    "dump = json.dumps(json_db, indent=2)\n",
    "with open('/tmp/rxnorm.js', 'w') as out:\n",
    "    out.write(\"var db = %s;\\nexport default db;\"%dump)\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "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.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}