process_machine_readable_puf.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 195,
   "metadata": {
    "collapsed": true,
    "level": 7
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {
    "collapsed": false,
    "level": 7,
    "run_control": {
     "breakpoint": false
    }
   },
   "outputs": [],
   "source": [
    "import shutil\n",
    "import zipfile\n",
    "import pandas as pd\n",
    "# import xlrd  # library for reading xlsx, but can use pandas.read_excel() \n",
    "import json\n",
    "import requests\n",
    "\n",
    "\n",
    "\n",
    "def read_json_url(url):\n",
    "    \n",
    "    global error\n",
    "    error = None\n",
    "    \n",
    "    try:\n",
    "        r = requests.get(url, allow_redirects=True)\n",
    "    except requests.exceptions.RequestException as error:    # This is the correct syntax\n",
    "        print (error)\n",
    "        return None\n",
    "\n",
    "    if r.status_code == 200 and 'json' in r.headers.get('content-type',''):\n",
    "        if debug: print(\"Good!\")\n",
    "        json_data = json.loads(r.text)\n",
    "        return json_data\n",
    "    else:\n",
    "        if debug: print(\"Not good! Status code:\" + str(r.status_code) + \"    Content-type: \" + r.headers.get('content-type',''))\n",
    "        return None\n",
    "    return\n",
    "\n",
    "\n",
    " \n",
    "def load_zip_to_df(full_dst):\n",
    "    zfile = zipfile.ZipFile(full_dst)\n",
    "    zfile.extractall()\n",
    "    \n",
    "    global df\n",
    "\n",
    "    # Read into Pandas DF\n",
    "    for file_name in zfile.namelist():\n",
    "        print(\"Loading: \"+file_name)\n",
    "        df = pd.read_excel(file_name)\n",
    "        df.as_matrix()\n",
    "        print(\"Records loaded: \"+str(len(df)))\n",
    "        break\n",
    "        \n",
    "    return\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "def count_url_items(json_url, max_depth=1):\n",
    "\n",
    "    if debug: print(\"\\n\\n\\n--------------------------\")\n",
    "    if debug: print(\"index  = \"+str(index))\n",
    "    if debug: print(\"json_url  = \"+str(json_url))\n",
    "    if debug: print(\"max_depth = \"+str(max_depth))\n",
    "    \n",
    "    if max_depth == 0:\n",
    "        if debug: print(\"Exiting\")\n",
    "        return 0\n",
    "    \n",
    "    max_depth -= 1\n",
    "    \n",
    "    json_data = read_json_url(json_url)\n",
    "    if json_data == None:\n",
    "        if debug: print(\"--- Nonetype!\")\n",
    "        return 0\n",
    "    \n",
    "    if debug: print(\"type(json_data) = \"+str(type(json_data)))\n",
    "    \n",
    "    if isinstance(json_data, dict):\n",
    "        for dict_key, dict_value in json_data.items():\n",
    "            if isinstance(dict_value, list):\n",
    "                count = len(dict_value)\n",
    "                if debug: print(\"# of items for \"+ dict_key+\" : \" + str(count))\n",
    "                df.loc[index, dict_key] = count\n",
    "                if max_depth >= 1:\n",
    "                    for list_key, list_value in enumerate(dict_value):\n",
    "                        count = count_url_items(list_value,max_depth)\n",
    "                        #if debug: print(list_value)\n",
    "            else:\n",
    "                count = 1\n",
    "                if debug: print(\"# of items for \"+ dict_key+\" : 1\")\n",
    "                df.loc[index, dict_key] = count\n",
    "                if max_depth >= 1:\n",
    "                    count = count_url_items(dict_value,max_depth)\n",
    "                    return count\n",
    "                #if debug: print(dict_value)          \n",
    "    elif isinstance(json_data, list):\n",
    "        count = len(json_data)\n",
    "        if debug: print(\"# of items for : \" + str(count))\n",
    "\n",
    "    else:\n",
    "        if debug: print(\"Error: json is not dictionary\")\n",
    "        return 0\n",
    "    return\n",
    "        \n",
    "\n",
    "\n",
    "# This is a custom alternative to use of parse.urlparse(url)\n",
    "# because sometimes it doesn't work well\n",
    "# Simple custom rules for checking validity\n",
    "def validate_url_custom(url):\n",
    "    url = url.strip()\n",
    "    returned_url = ''\n",
    "    if url.find(' ')==-1 and url.find('.')>0 and url.find('.')<len(url)-1: \n",
    "        if not url.lower().find('http')==0:\n",
    "            # Protocol not specified, but no spaces and with periods.  Try adding http://\"\n",
    "            url = 'http://' + url\n",
    "    else:\n",
    "        # Bad! Spaces or no periods\"\n",
    "        url = ''\n",
    "        \n",
    "    return url\n",
    "        \n",
    "\n",
    "def save_to_excel():    \n",
    "    writer = pd.ExcelWriter('output.xlsx', engine='xlsxwriter')\n",
    "    df.to_excel(writer,'Sheet1')\n",
    "    writer.save()\n",
    "    return\n",
    "\n",
    "\n",
    "from IPython.display import display, clear_output\n",
    "import sys\n",
    "\n",
    "\n",
    "def print_same_line(print_str):\n",
    "    \n",
    "    '''\n",
    "    #=== stdout method ===\n",
    "    sys.stdout.write('\\r'+print_str)\n",
    "    sys.stdout.flush()\n",
    "    ''' \n",
    "    \n",
    "    #=== iPython method ===\n",
    "    clear_output(wait=True)\n",
    "    print(print_str+\"\\n\")\n",
    "    sys.stdout.flush()\n",
    "    \n",
    "    return\n",
    "    \n",
    "    \n",
    "def main(start_row=0 , end_row=9):\n",
    "    global df\n",
    "    global index\n",
    "    global error\n",
    "    # Test\n",
    "    src = '../DDOD-HealthData.gov/snapshots/'\n",
    "    src += 'machine-readable-url-puf_2016-02-11.zip'\n",
    "    dst = '.'\n",
    "    print(src)\n",
    "    print(dst)\n",
    "    full_dst = shutil.copy2(src, dst)  # copy2 preserves metadata\n",
    "    load_zip_to_df(full_dst)\n",
    "    #url_dict = pd.DataFrame.to_dict(df,'records')\n",
    "\n",
    "    print(\"Reading\")\n",
    "    \n",
    "\n",
    "    # Loop through all rows\n",
    "    #=====  Dataframe version ======\n",
    "    for index, row in df.loc[start_row:end_row].iterrows():\n",
    "\n",
    "        print_same_line(\"Reading row:  \"+str(index)+\"\\n\")\n",
    "        \n",
    "        #print(index)\n",
    "        #print(row)\n",
    "        url_submit = row['URL Submitted']\n",
    "        url_clean = validate_url_custom(url_submit)\n",
    "        if url_clean:\n",
    "            row_status = count_url_items(url_clean,1)\n",
    "            df.loc[index, 'Status'] = 'Read' if (error == None) else error  # Can't assign dict\n",
    "        else:\n",
    "            df.loc[index, 'Status'] = \"Missing\"\n",
    "        if index >= end_row: break\n",
    "\n",
    "    save_to_excel()\n",
    "    return\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {
    "collapsed": false,
    "level": 7,
    "run_control": {
     "breakpoint": false
    }
   },
   "outputs": [],
   "source": [
    "from IPython.html.widgets import FloatProgress\n",
    "from IPython.display import display\n",
    "from time import sleep\n",
    "f = FloatProgress(min=0, max=100)\n",
    "display(f)\n",
    "for i in range(100):\n",
    "   sleep(0.1)\n",
    "   f.value = i"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {
    "collapsed": false,
    "level": 7,
    "run_control": {
     "breakpoint": false
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "abc\n",
      "bad stuff\n",
      "xyz"
     ]
    }
   ],
   "source": [
    "import sys\n",
    "import time\n",
    "\n",
    "def print_same_line(print_str):\n",
    "    sys.stdout.write('\\r'+print_str)\n",
    "    sys.stdout.flush()\n",
    "'''\n",
    "sys.stdout.write('some data')\n",
    "sys.stdout.flush()\n",
    "print(\"stuff\")\n",
    "restart_line()\n",
    "sys.stdout.write('other different data')\n",
    "sys.stdout.flush()\n",
    "print(\"stuff2\")\n",
    "\n",
    "'''\n",
    "print_same_line(\"abc\")\n",
    "print(\"\\nbad stuff\")\n",
    "time.sleep(2) # wait 2 seconds...\n",
    "print_same_line(\"xyz\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {
    "collapsed": false,
    "level": 7,
    "run_control": {
     "breakpoint": false
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "9\n"
     ]
    }
   ],
   "source": [
    "for i in range(10):\n",
    "    time.sleep(0.25)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {
    "collapsed": false,
    "run_control": {
     "breakpoint": false
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "../DDOD-HealthData.gov/snapshots/machine-readable-url-puf_2016-02-11.zip\n",
      ".\n",
      "Loading: Machine_Readable_PUF_2015-12-21.xlsx\n",
      "Records loaded: 450\n",
      "Reading\n",
      "Reading row90\n",
      "Reading row91\n",
      "Reading row92\n",
      "[SSL: UNSUPPORTED_PROTOCOL] unsupported protocol (_ssl.c:645)\n",
      "Reading row93\n",
      "Reading row94\n",
      "Reading row95\n",
      "Reading row96\n",
      "Reading row97\n",
      "Reading row98\n",
      "Reading row99\n",
      "Reading row100\n",
      "Reading row101\n",
      "Reading row102\n",
      "Reading row103\n",
      "Reading row104\n",
      "Reading row105\n",
      "Reading row106\n",
      "Reading row107\n",
      "Reading row108\n",
      "Reading row109\n",
      "Reading row110\n",
      "Reading row111\n",
      "Reading row112\n",
      "Reading row113\n",
      "Reading row114\n",
      "Reading row115\n",
      "Reading row116\n",
      "HTTPSConnectionPool(host='siho.org', port=443): Max retries exceeded with url: /marketplace/cms-data-index.json (Caused by NewConnectionError('<requests.packages.urllib3.connection.VerifiedHTTPSConnection object at 0x1067302b0>: Failed to establish a new connection: [Errno 60] Operation timed out',))\n",
      "Reading row117\n",
      "Reading row118\n",
      "Reading row119\n",
      "Reading row120\n",
      "Reading row121\n",
      "Reading row122\n",
      "Reading row123\n",
      "Reading row124\n",
      "Reading row125\n",
      "Reading row126\n",
      "Reading row127\n",
      "Reading row128\n",
      "Reading row129\n",
      "Reading row130\n",
      "Reading row131\n",
      "Reading row132\n",
      "Reading row133\n",
      "Reading row134\n",
      "Reading row135\n",
      "Reading row136\n",
      "Reading row137\n",
      "Reading row138\n",
      "Reading row139\n",
      "Reading row140\n",
      "Reading row141\n",
      "Reading row142\n",
      "('Connection aborted.', ConnectionResetError(54, 'Connection reset by peer'))\n",
      "Reading row143\n",
      "Reading row144\n",
      "Reading row145\n",
      "Reading row146\n",
      "Reading row147\n",
      "HTTPConnectionPool(host='www.phpmichigan.com', port=80): Read timed out. (read timeout=None)\n",
      "Reading row148\n",
      "Reading row149\n",
      "Reading row150\n",
      "Reading row151\n",
      "Reading row152\n",
      "Reading row153\n",
      "Reading row154\n",
      "Reading row155\n",
      "Reading row156\n",
      "Reading row157\n",
      "Reading row158\n",
      "Reading row159\n",
      "Reading row160\n",
      "Reading row161\n",
      "Reading row162\n",
      "Reading row163\n",
      "Reading row164\n",
      "Reading row165\n",
      "Reading row166\n",
      "Reading row167\n",
      "Reading row168\n",
      "Reading row169\n",
      "Reading row170\n",
      "Reading row171\n",
      "Reading row172\n",
      "Reading row173\n",
      "Reading row174\n",
      "Reading row175\n",
      "Reading row176\n",
      "Reading row177\n",
      "Reading row178\n",
      "Reading row179\n",
      "Reading row180\n",
      "Reading row181\n",
      "Reading row182\n",
      "Reading row183\n",
      "Reading row184\n",
      "Reading row185\n",
      "Reading row186\n",
      "Reading row187\n",
      "Reading row188\n",
      "Reading row189\n",
      "Reading row190\n",
      "Reading row191\n",
      "Reading row192\n",
      "Reading row193\n",
      "Reading row194\n",
      "Reading row195\n",
      "Reading row196\n",
      "Reading row197\n",
      "[SSL: UNSUPPORTED_PROTOCOL] unsupported protocol (_ssl.c:645)\n",
      "Reading row198\n",
      "Reading row199\n",
      "Reading row200\n",
      "Reading row201\n",
      "Reading row202\n",
      "Reading row203\n",
      "[SSL: UNSUPPORTED_PROTOCOL] unsupported protocol (_ssl.c:645)\n",
      "Reading row204\n",
      "Reading row205\n",
      "Reading row206\n",
      "Reading row207\n",
      "Reading row208\n",
      "Reading row209\n",
      "Reading row210\n",
      "Reading row211\n",
      "Reading row212\n",
      "Reading row213\n",
      "Reading row214\n",
      "Reading row215\n",
      "Reading row216\n",
      "Reading row217\n",
      "Reading row218\n",
      "Reading row219\n",
      "hostname 'static.hioscar.com' doesn't match either of 'cloudfront.net', '*.cloudfront.net'\n",
      "Reading row220\n",
      "Reading row221\n",
      "Reading row222\n",
      "Reading row223\n",
      "Reading row224\n",
      "Reading row225\n",
      "Reading row226\n",
      "Reading row227\n",
      "Reading row228\n",
      "Reading row229\n",
      "Reading row230\n",
      "Reading row231\n",
      "Reading row232\n",
      "Reading row233\n",
      "Reading row234\n",
      "Reading row235\n",
      "Reading row236\n",
      "Reading row237\n",
      "Reading row238\n",
      "Reading row239\n",
      "Reading row240\n",
      "Reading row241\n",
      "Reading row242\n",
      "Reading row243\n",
      "Reading row244\n",
      "Reading row245\n",
      "Reading row246\n",
      "Reading row247\n",
      "Reading row248\n",
      "Reading row249\n",
      "Reading row250\n",
      "Reading row251\n",
      "Reading row252\n",
      "Reading row253\n",
      "Reading row254\n",
      "Reading row255\n",
      "Reading row256\n",
      "Reading row257\n",
      "Reading row258\n",
      "Reading row259\n",
      "Reading row260\n",
      "Reading row261\n",
      "Reading row262\n",
      "Reading row263\n",
      "Reading row264\n",
      "Reading row265\n",
      "Reading row266\n",
      "Reading row267\n",
      "Reading row268\n",
      "Reading row269\n",
      "Reading row270\n",
      "Reading row271\n",
      "Reading row272\n",
      "Reading row273\n",
      "Reading row274\n",
      "Reading row275\n",
      "Reading row276\n",
      "Reading row277\n",
      "Reading row278\n",
      "Reading row279\n",
      "Reading row280\n",
      "Reading row281\n",
      "Reading row282\n",
      "Reading row283\n",
      "Reading row284\n",
      "Reading row285\n",
      "Reading row286\n",
      "Reading row287\n",
      "Reading row288\n",
      "Reading row289\n",
      "Reading row290\n",
      "Reading row291\n",
      "Reading row292\n",
      "Reading row293\n",
      "Reading row294\n",
      "Reading row295\n",
      "Reading row296\n",
      "Reading row297\n",
      "Reading row298\n",
      "Reading row299\n",
      "Reading row300\n",
      "Reading row301\n",
      "Reading row302\n",
      "Reading row303\n",
      "Reading row304\n",
      "Reading row305\n",
      "Reading row306\n",
      "Reading row307\n",
      "Reading row308\n",
      "Reading row309\n",
      "Reading row310\n",
      "Reading row311\n",
      "Reading row312\n",
      "Reading row313\n",
      "Reading row314\n",
      "Reading row315\n",
      "Reading row316\n",
      "Reading row317\n",
      "Reading row318\n",
      "Reading row319\n",
      "Reading row320\n",
      "Reading row321\n",
      "Reading row322\n",
      "Reading row323\n",
      "Reading row324\n",
      "Reading row325\n",
      "hostname 'static.hioscar.com' doesn't match either of 'cloudfront.net', '*.cloudfront.net'\n",
      "Reading row326\n",
      "Reading row327\n",
      "Reading row328\n",
      "Reading row329\n",
      "Reading row330\n",
      "Reading row331\n",
      "Reading row332\n",
      "Reading row333\n",
      "Reading row334\n",
      "Reading row335\n",
      "Reading row336\n",
      "Reading row337\n",
      "Reading row338\n",
      "Reading row339\n",
      "Reading row340\n",
      "Reading row341\n",
      "Reading row342\n",
      "Reading row343\n",
      "Reading row344\n",
      "Reading row345\n",
      "Reading row346\n",
      "Reading row347\n",
      "Reading row348\n",
      "Reading row349\n",
      "Reading row350\n",
      "Reading row351\n",
      "hostname 'allegianchoice.com' doesn't match either of 'allegianadvantage.com', 'www.allegianadvantage.com'\n",
      "Reading row352\n",
      "Reading row353\n",
      "Reading row354\n",
      "Reading row355\n",
      "Reading row356\n",
      "Reading row357\n",
      "Reading row358\n",
      "Reading row359\n",
      "Reading row360\n",
      "Reading row361\n",
      "Reading row362\n",
      "Reading row363\n",
      "Reading row364\n",
      "Reading row365\n",
      "Reading row366\n",
      "Reading row367\n",
      "Reading row368\n",
      "Reading row369\n",
      "Reading row370\n",
      "Reading row371\n",
      "Reading row372\n",
      "Reading row373\n",
      "Reading row374\n",
      "Reading row375\n",
      "Reading row376\n",
      "Reading row377\n",
      "Reading row378\n",
      "Reading row379\n",
      "Reading row380\n",
      "Reading row381\n",
      "Reading row382\n",
      "Reading row383\n",
      "Reading row384\n",
      "Reading row385\n",
      "Reading row386\n",
      "Reading row387\n",
      "Reading row388\n",
      "Reading row389\n",
      "Reading row390\n",
      "Reading row391\n",
      "Reading row392\n",
      "Reading row393\n",
      "Reading row394\n",
      "Reading row395\n",
      "Reading row396\n",
      "Reading row397\n",
      "Reading row398\n",
      "Reading row399\n",
      "Reading row400\n",
      "Reading row401\n",
      "Reading row402\n",
      "Reading row403\n",
      "Reading row404\n",
      "Reading row405\n",
      "Reading row406\n",
      "Reading row407\n",
      "Reading row408\n",
      "Reading row409\n",
      "Reading row410\n",
      "Reading row411\n",
      "Reading row412\n",
      "Reading row413\n",
      "Reading row414\n",
      "Reading row415\n",
      "Reading row416\n",
      "Reading row417\n",
      "Reading row418\n",
      "Reading row419\n",
      "Reading row420\n",
      "Reading row421\n",
      "Reading row422\n",
      "Reading row423\n",
      "Reading row424\n",
      "[SSL: UNSUPPORTED_PROTOCOL] unsupported protocol (_ssl.c:645)\n",
      "Reading row425\n",
      "Reading row426\n",
      "Reading row427\n",
      "Reading row428\n",
      "Reading row429\n",
      "Reading row430\n",
      "Reading row431\n",
      "Reading row432\n",
      "Reading row433\n",
      "Reading row434\n",
      "Reading row435\n",
      "Reading row436\n",
      "Reading row437\n",
      "Reading row438\n",
      "Reading row439\n",
      "Reading row440\n",
      "Reading row441\n",
      "Reading row442\n",
      "Reading row443\n",
      "Reading row444\n",
      "Reading row445\n",
      "Reading row446\n",
      "Reading row447\n",
      "Reading row448\n",
      "Reading row449\n"
     ]
    }
   ],
   "source": [
    "debug = False\n",
    "main(90,500)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "collapsed": false,
    "run_control": {
     "breakpoint": false
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://esbgatewaypub.medica.com:443/rest/QHP/cms-data-index.json?HIOSID=93078&fmt=json\n"
     ]
    }
   ],
   "source": [
    "#r = requests.get(url, allow_redirects=True)\n",
    "#df[91]\n",
    "#df.loc[[90:92]]\n",
    "df.loc[90:93,'URL Submitted']\n",
    "print(df.loc[92,'URL Submitted']) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "collapsed": false,
    "run_control": {
     "breakpoint": false
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Reading row5\n",
      "Reading row6\n",
      "Reading row7\n",
      "Reading row8\n",
      "Reading row9\n",
      "Reading row10\n"
     ]
    }
   ],
   "source": [
    "type(df)\n",
    "start = 5\n",
    "end = 10\n",
    "type(df.loc[start:end])\n",
    "for index, row in df.loc[start:end].iterrows():\n",
    "    print(\"Reading row\"+str(index))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "collapsed": false,
    "run_control": {
     "breakpoint": false
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[SSL: UNSUPPORTED_PROTOCOL] unsupported protocol (_ssl.c:645)\n"
     ]
    }
   ],
   "source": [
    "import ssl\n",
    "url='https://esbgatewaypub.medica.com:443/rest/QHP/cms-data-index.json?HIOSID=93078&fmt=json'\n",
    "try:\n",
    "    r = requests.get(url, allow_redirects=True)\n",
    "except requests.exceptions.RequestException as e:    # This is the correct syntax\n",
    "    print (e)\n",
    "#    sys.exit(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false,
    "level": 7,
    "run_control": {
     "breakpoint": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Marketplace Category</th>\n",
       "      <th>State</th>\n",
       "      <th>Issuer ID</th>\n",
       "      <th>Issuer Name (Legal Name)</th>\n",
       "      <th>URL Submitted</th>\n",
       "      <th>Tech POC Email</th>\n",
       "      <th>Status</th>\n",
       "      <th>formulary_urls</th>\n",
       "      <th>provider_urls</th>\n",
       "      <th>plan_urls</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>FFM</td>\n",
       "      <td>AL</td>\n",
       "      <td>17540</td>\n",
       "      <td>Guardian Life Insurance Company of America</td>\n",
       "      <td>NOT SUBMITTED</td>\n",
       "      <td>NOT SUBMITTED</td>\n",
       "      <td>Missing</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>FFM</td>\n",
       "      <td>AK</td>\n",
       "      <td>38344</td>\n",
       "      <td>Premera Blue Cross Blue Shield of Alaska</td>\n",
       "      <td>https://fm.formularynavigator.com/jsonFiles/pu...</td>\n",
       "      <td>jim.tedford@premera.com</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>FFM</td>\n",
       "      <td>AK</td>\n",
       "      <td>73836</td>\n",
       "      <td>Moda Health Plan, Inc.</td>\n",
       "      <td>NOT SUBMITTED</td>\n",
       "      <td>NOT SUBMITTED</td>\n",
       "      <td>Missing</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>FFM</td>\n",
       "      <td>AL</td>\n",
       "      <td>44580</td>\n",
       "      <td>Humana Insurance Company</td>\n",
       "      <td>https://api.humana.com/v1/cms/index.json</td>\n",
       "      <td>pboulet@humana.com</td>\n",
       "      <td>None</td>\n",
       "      <td>99</td>\n",
       "      <td>1635</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>FFM</td>\n",
       "      <td>AL</td>\n",
       "      <td>46944</td>\n",
       "      <td>Blue Cross and Blue Shield of Alabama</td>\n",
       "      <td>https://www.bcbsal.org/cms/cms-data-index.json</td>\n",
       "      <td>HIMMachineReadableQuestions@bcbsal.org</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Marketplace Category State  Issuer ID  \\\n",
       "0                  FFM    AL      17540   \n",
       "1                  FFM    AK      38344   \n",
       "2                  FFM    AK      73836   \n",
       "3                  FFM    AL      44580   \n",
       "4                  FFM    AL      46944   \n",
       "\n",
       "                     Issuer Name (Legal Name)  \\\n",
       "0  Guardian Life Insurance Company of America   \n",
       "1    Premera Blue Cross Blue Shield of Alaska   \n",
       "2                      Moda Health Plan, Inc.   \n",
       "3                    Humana Insurance Company   \n",
       "4       Blue Cross and Blue Shield of Alabama   \n",
       "\n",
       "                                       URL Submitted  \\\n",
       "0                                      NOT SUBMITTED   \n",
       "1  https://fm.formularynavigator.com/jsonFiles/pu...   \n",
       "2                                      NOT SUBMITTED   \n",
       "3           https://api.humana.com/v1/cms/index.json   \n",
       "4     https://www.bcbsal.org/cms/cms-data-index.json   \n",
       "\n",
       "                           Tech POC Email   Status  formulary_urls  \\\n",
       "0                           NOT SUBMITTED  Missing             NaN   \n",
       "1                 jim.tedford@premera.com     None               1   \n",
       "2                           NOT SUBMITTED  Missing             NaN   \n",
       "3                      pboulet@humana.com     None              99   \n",
       "4  HIMMachineReadableQuestions@bcbsal.org     None               1   \n",
       "\n",
       "   provider_urls  plan_urls  \n",
       "0            NaN        NaN  \n",
       "1              1          1  \n",
       "2            NaN        NaN  \n",
       "3           1635          2  \n",
       "4              3          1  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false,
    "run_control": {
     "breakpoint": false
    }
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 296,
   "metadata": {
    "collapsed": false,
    "level": 7,
    "run_control": {
     "breakpoint": false
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Status code for http://ddod.us: 200\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "{'_diff_url': False,\n",
       " 'elapsed': datetime.timedelta(0, 0, 159788),\n",
       " 'headers': {'X-Powered-By': 'PHP/5.5.30', 'Connection': 'Keep-Alive', 'X-Frame-Options': 'SAMEORIGIN', 'X-Mod-Pagespeed': '1.9.32.3-4523', 'Date': 'Tue, 16 Feb 2016 23:52:16 GMT', 'Content-Encoding': 'gzip', 'Content-Length': '10225', 'Content-language': 'en', 'Server': 'Apache', 'Vary': 'Accept-Encoding,Cookie', 'Cache-Control': 'private, must-revalidate, max-age=0, max-age=0, no-cache', 'Expires': 'Thu, 01 Jan 1970 00:00:00 GMT', 'X-UA-Compatible': 'IE=Edge', 'Keep-Alive': 'timeout=2, max=100', 'X-Content-Type-Options': 'nosniff', 'Content-Type': 'text/html; charset=UTF-8'},\n",
       " 'history': [<Response [301]>],\n",
       " 'status_code': 200,\n",
       " 'total_secs': 0.159788,\n",
       " 'url': 'http://ddod.healthdata.gov/wiki/Main_Page'}"
      ]
     },
     "execution_count": 296,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def analyze_url(url):\n",
    "    r = requests.get(url, allow_redirects=True)\n",
    "    #r = requests.head(url, allow_redirects=True)\n",
    "    print(\"Status code for \"+url+\": \"+str(r.status_code))\n",
    "    r_save = {}\n",
    "    r_save['status_code'] = r.status_code\n",
    "    r_save['history']     = r.history\n",
    "    r_save['url']         = r.url\n",
    "    r_save['_diff_url']   = (r.url == url)\n",
    "    r_save['headers']     = r.headers\n",
    "    r_save['elapsed']     = r.elapsed\n",
    "    r_save['total_secs']  = r.elapsed.total_seconds()\n",
    "\n",
    "    return r_save\n",
    "\n",
    "#Test\n",
    "analyze_url(\"http://ddod.us\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 293,
   "metadata": {
    "collapsed": false,
    "level": 7,
    "run_control": {
     "breakpoint": false
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Status code for NOT DEFINED: 200\n",
      "[<Response [301]>]\n",
      "http://ddod.healthdata.gov/wiki/Main_Page\n"
     ]
    }
   ],
   "source": [
    "r = requests.get(\"http://ddod.us\", allow_redirects=True)\n",
    "#r = requests.head(url, allow_redirects=True)\n",
    "print(\"Status code for \"+url+\": \"+str(r.status_code))\n",
    "print(r.history)\n",
    "print(r.url)\n",
    "\n",
    "r_save = {}\n",
    "r_save['status_code'] = r.status_code\n",
    "r_save['history']     = r.history\n",
    "r_save['url']         = r.url\n",
    "r_save['_diff_url']   = (r.url == url)\n",
    "r_save['headers']     = r.headers\n",
    "r_save['elapsed']     = r.elapsed\n",
    "r_save['total_secs']  = r.elapsed.total_seconds()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 294,
   "metadata": {
    "collapsed": false,
    "level": 7,
    "run_control": {
     "breakpoint": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'diff_url': False,\n",
       " 'elapsed': datetime.timedelta(0, 0, 181345),\n",
       " 'headers': {'X-Powered-By': 'PHP/5.5.30', 'Connection': 'Keep-Alive', 'X-Frame-Options': 'SAMEORIGIN', 'X-Mod-Pagespeed': '1.9.32.3-4523', 'Date': 'Tue, 16 Feb 2016 23:50:12 GMT', 'Content-Encoding': 'gzip', 'Content-Length': '10233', 'Content-language': 'en', 'Server': 'Apache', 'Vary': 'Accept-Encoding,Cookie', 'Cache-Control': 'private, must-revalidate, max-age=0, max-age=0, no-cache', 'Expires': 'Thu, 01 Jan 1970 00:00:00 GMT', 'X-UA-Compatible': 'IE=Edge', 'Keep-Alive': 'timeout=2, max=100', 'X-Content-Type-Options': 'nosniff', 'Content-Type': 'text/html; charset=UTF-8'},\n",
       " 'history': [<Response [301]>],\n",
       " 'status_code': 200,\n",
       " 'total_secs': 0.181345,\n",
       " 'url': 'http://ddod.healthdata.gov/wiki/Main_Page'}"
      ]
     },
     "execution_count": 294,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "r_save"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 285,
   "metadata": {
    "collapsed": false,
    "level": 7,
    "run_control": {
     "breakpoint": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'X-Powered-By': 'PHP/5.5.30', 'Connection': 'Keep-Alive', 'X-Frame-Options': 'SAMEORIGIN', 'X-Mod-Pagespeed': '1.9.32.3-4523', 'Date': 'Tue, 16 Feb 2016 23:35:40 GMT', 'Content-Encoding': 'gzip', 'Content-Length': '10262', 'Content-language': 'en', 'Server': 'Apache', 'Vary': 'Accept-Encoding,Cookie', 'Cache-Control': 'private, must-revalidate, max-age=0, max-age=0, no-cache', 'Expires': 'Thu, 01 Jan 1970 00:00:00 GMT', 'X-UA-Compatible': 'IE=Edge', 'Keep-Alive': 'timeout=2, max=100', 'X-Content-Type-Options': 'nosniff', 'Content-Type': 'text/html; charset=UTF-8'}"
      ]
     },
     "execution_count": 285,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "\n",
    "\n",
    "\n",
    "r.headers['Content-Type']\n",
    "\n",
    "'''\n",
    "{'X-Powered-By': 'PHP/5.5.30', 'Connection': 'Keep-Alive', 'X-Frame-Options': 'SAMEORIGIN', \n",
    "'X-Mod-Pagespeed': '1.9.32.3-4523', 'Date': 'Tue, 16 Feb 2016 23:35:40 GMT', \n",
    "'Content-Encoding': 'gzip', 'Content-Length': '10262', 'Content-language': 'en', \n",
    "'Server': 'Apache', 'Vary': 'Accept-Encoding,Cookie', 'Cache-Control': 'private, must-revalidate, \n",
    "max-age=0, max-age=0, no-cache', 'Expires': 'Thu, 01 Jan 1970 00:00:00 GMT', 'X-UA-Compatible': 'IE=Edge', \n",
    "'Keep-Alive': 'timeout=2, max=100', 'X-Content-Type-Options': 'nosniff', 'Content-Type': 'text/html; charset=UTF-8'}\n",
    "'''\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 288,
   "metadata": {
    "collapsed": false,
    "level": 7,
    "run_control": {
     "breakpoint": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'UTF-8'"
      ]
     },
     "execution_count": 288,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "r.elapsed\n",
    "# datetime.timedelta(0, 0, 164833)\n",
    "r.elapsed.total_seconds()  # 0.164833\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 298,
   "metadata": {
    "collapsed": false,
    "level": 7,
    "run_control": {
     "breakpoint": false
    }
   },
   "outputs": [
    {
     "ename": "ValueError",
     "evalue": "Length of values does not match length of index",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mValueError\u001b[0m                                Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-298-dac673ee1922>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mdf\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'Response'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m{\u001b[0m\u001b[0;34m}\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m/Users/dportnoy/anaconda3/lib/python3.5/site-packages/pandas/core/frame.py\u001b[0m in \u001b[0;36m__setitem__\u001b[0;34m(self, key, value)\u001b[0m\n\u001b[1;32m   2297\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2298\u001b[0m             \u001b[0;31m# set column\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2299\u001b[0;31m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_set_item\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   2300\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2301\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m_setitem_slice\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mkey\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/dportnoy/anaconda3/lib/python3.5/site-packages/pandas/core/frame.py\u001b[0m in \u001b[0;36m_set_item\u001b[0;34m(self, key, value)\u001b[0m\n\u001b[1;32m   2364\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2365\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_ensure_valid_index\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mvalue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2366\u001b[0;31m         \u001b[0mvalue\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_sanitize_column\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   2367\u001b[0m         \u001b[0mNDFrame\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_set_item\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mkey\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2368\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/dportnoy/anaconda3/lib/python3.5/site-packages/pandas/core/frame.py\u001b[0m in \u001b[0;36m_sanitize_column\u001b[0;34m(self, key, value)\u001b[0m\n\u001b[1;32m   2522\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2523\u001b[0m             \u001b[0;31m# turn me into an ndarray\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2524\u001b[0;31m             \u001b[0mvalue\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0m_sanitize_index\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mvalue\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mindex\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcopy\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mFalse\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   2525\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0misinstance\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mvalue\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m(\u001b[0m\u001b[0mnp\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mndarray\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mIndex\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2526\u001b[0m                 \u001b[0;32mif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mvalue\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mlist\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mand\u001b[0m \u001b[0mlen\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mvalue\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;34m>\u001b[0m \u001b[0;36m0\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/dportnoy/anaconda3/lib/python3.5/site-packages/pandas/core/series.py\u001b[0m in \u001b[0;36m_sanitize_index\u001b[0;34m(data, index, copy)\u001b[0m\n\u001b[1;32m   2739\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2740\u001b[0m     \u001b[0;32mif\u001b[0m \u001b[0mlen\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mdata\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;34m!=\u001b[0m \u001b[0mlen\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mindex\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2741\u001b[0;31m         raise ValueError('Length of values does not match length of '\n\u001b[0m\u001b[1;32m   2742\u001b[0m                          'index')\n\u001b[1;32m   2743\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mValueError\u001b[0m: Length of values does not match length of index"
     ]
    }
   ],
   "source": [
    "df['Response']={}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "level": 7,
    "run_control": {
     "breakpoint": false
    }
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 326,
   "metadata": {
    "collapsed": false,
    "level": 7,
    "run_control": {
     "breakpoint": false
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'Issuer Name (Legal Name)': 'Guardian Life Insurance Company of America', 'URL Submitted': 'NOT SUBMITTED', 'Status': 'Missing', 'Tech POC Email': 'NOT SUBMITTED', 'State': 'AL', 'Issuer ID': 17540, 'Marketplace Category': 'FFM'}\n",
      "\n",
      "------\n",
      "\n",
      "{'Issuer Name (Legal Name)': 'Premera Blue Cross Blue Shield of Alaska', 'URL Submitted': 'https://fm.formularynavigator.com/jsonFiles/publish/11/47/cms-data-index.json', 'Status': '{\\'elapsed\\': datetime.timedelta(0, 0, 964503), \\'_diff_url\\': True, \\'headers\\': {\\'Server\\': \\'Microsoft-IIS/8.0\\', \\'Date\\': \\'Wed, 17 Feb 2016 00:21:10 GMT\\', \\'X-Powered-By\\': \\'ASP.NET\\', \\'Set-Cookie\\': \\'BIGipServerProduction-Farm-Pool-FormularyNavigator=1734803978.20480.0000; path=/\\', \\'Last-Modified\\': \\'Tue, 16 Feb 2016 23:53:50 GMT\\', \\'Content-Type\\': \\'application/json\\', \\'ETag\\': \\'\"e7a2a5481569d11:0\"\\', \\'Content-Length\\': \\'313\\', \\'Accept-Ranges\\': \\'bytes\\'}, \\'status_code\\': 200, \\'url\\': \\'https://fm.formularynavigator.com/jsonFiles/publish/11/47/cms-data-index.json\\', \\'total_secs\\': 0.964503, \\'history\\': []}', 'Tech POC Email': 'jim.tedford@premera.com', 'State': 'AK', 'Issuer ID': 38344, 'Marketplace Category': 'FFM'}\n",
      "\n",
      "------\n",
      "\n",
      "{'Issuer Name (Legal Name)': 'Moda Health Plan, Inc.', 'URL Submitted': 'NOT SUBMITTED', 'Status': 'Missing', 'Tech POC Email': 'NOT SUBMITTED', 'State': 'AK', 'Issuer ID': 73836, 'Marketplace Category': 'FFM'}\n",
      "\n",
      "------\n",
      "\n",
      "{'Issuer Name (Legal Name)': 'Humana Insurance Company', 'URL Submitted': 'https://api.humana.com/v1/cms/index.json', 'Status': \"{'elapsed': datetime.timedelta(0, 0, 911839), '_diff_url': True, 'headers': {'transfer-encoding': 'chunked', 'Access-Control-Max-Age': '3628800', 'Connection': 'keep-alive', 'Access-Control-Allow-Headers': 'origin, x-requested-with, accept, content-type, x-humana-api-key', 'Server': 'Apigee Router', 'Date': 'Wed, 17 Feb 2016 00:21:10 GMT', 'Access-Control-Allow-Methods': 'GET, PUT, POST, DELETE', 'X-Client-IP': '133.27.0.2,54.208.142.238', 'Access-Control-Allow-Origin': '*', 'X-Backside-Transport': 'OK OK,OK OK,FAIL FAIL', 'X-Archived-Client-IP': '133.27.0.2', 'Content-Type': 'application/json; charset=utf-8'}, 'status_code': 200, 'url': 'https://api.humana.com/v1/cms/index.json', 'total_secs': 0.911839, 'history': []}\", 'Tech POC Email': 'pboulet@humana.com', 'State': 'AL', 'Issuer ID': 44580, 'Marketplace Category': 'FFM'}\n",
      "\n",
      "------\n",
      "\n",
      "{'Issuer Name (Legal Name)': 'Blue Cross and Blue Shield of Alabama', 'URL Submitted': 'https://www.bcbsal.org/cms/cms-data-index.json', 'Status': '{\\'elapsed\\': datetime.timedelta(0, 1, 37804), \\'_diff_url\\': True, \\'headers\\': {\\'Server\\': \\'Microsoft-IIS/7.5\\', \\'Date\\': \\'Wed, 17 Feb 2016 00:20:57 GMT\\', \\'X-Powered-By\\': \\'Middleware\\', \\'Set-Cookie\\': \\'BIGipServerbcbsal-http=958185482.35091.0000; path=/, TLTSID=C4EC4B0658B18B814ACB334FA5190256;path=/;domain=.bcbsal.org;, TLTUID=FC10AE6F94EB690738361A0DD30875FD;expires=Mon, 09-Oct-2017 00:21:12 GMT;path=/;domain=.bcbsal.org;\\', \\'Last-Modified\\': \\'Fri, 25 Sep 2015 15:22:32 GMT\\', \\'Content-Type\\': \\'application/json\\', \\'ETag\\': \\'\"4239d3ffa5f7d01:0\"\\', \\'Content-Length\\': \\'393\\', \\'Accept-Ranges\\': \\'bytes\\'}, \\'status_code\\': 200, \\'url\\': \\'https://www.bcbsal.org/cms/cms-data-index.json\\', \\'total_secs\\': 1.037804, \\'history\\': []}', 'Tech POC Email': 'HIMMachineReadableQuestions@bcbsal.org', 'State': 'AL', 'Issuer ID': 46944, 'Marketplace Category': 'FFM'}\n",
      "\n",
      "------\n",
      "\n",
      "{'Issuer Name (Legal Name)': 'Metropolitan Life Insurance Company', 'URL Submitted': 'NOT SUBMITTED', 'Status': 'Missing', 'Tech POC Email': 'NOT SUBMITTED', 'State': 'MI', 'Issuer ID': 11083, 'Marketplace Category': 'SPM'}\n",
      "\n",
      "------\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Display results\n",
    "for index,row in enumerate(url_dict):\n",
    "    print(row)\n",
    "    print(\"\\n------\\n\")\n",
    "    if index == 5: break\n",
    " "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 325,
   "metadata": {
    "collapsed": false,
    "level": 7,
    "run_control": {
     "breakpoint": false
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Status code for https://fm.formularynavigator.com/jsonFiles/publish/11/47/cms-data-index.json: 200\n",
      "Status code for https://api.humana.com/v1/cms/index.json: 200\n",
      "Status code for https://www.bcbsal.org/cms/cms-data-index.json: 200\n"
     ]
    }
   ],
   "source": [
    "# Loop through all rows\n",
    "#=====  Array of dictionary version ======\n",
    "\n",
    "for index,row in enumerate(url_dict):\n",
    "    url_submit = row['URL Submitted']\n",
    "    url_clean = validate_url_custom(url_submit)\n",
    "    if url_clean:\n",
    "        row['Status'] = analyze_url(url_clean)\n",
    "    else:\n",
    "        row['Status'] = \"Missing\"\n",
    "    if index == 5: break\n",
    "   "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": false,
    "level": 1,
    "run_control": {
     "breakpoint": false
    }
   },
   "source": [
    "# Example of results #\n",
    "\n",
    "Another\n",
    "---\n",
    "**Status code for https://fm.formularynavigator.com/jsonFiles/publish/11/47/cms-data-index.json: 200**\n",
    "\n",
    "    <s>Status Code:500 Internal Server Error\n",
    "    Content-Type:text/html</s>\n",
    "    \n",
    "    ```javascript\n",
    "    {\n",
    "      \"provider_urls\": [\n",
    "        \"http://fm.formularynavigator.com/jsonFiles/publish/11/47/providers.json\"\n",
    "      ],\n",
    "                  ==> \"npi\":\n",
    "      \"formulary_urls\": [\n",
    "                  ==> \"rxnorm_id\":\n",
    "        \"http://fm.formularynavigator.com/jsonFiles/publish/11/47/drugs.json\"\n",
    "      ],\n",
    "      \"plan_urls\": [\n",
    "        \"http://fm.formularynavigator.com/jsonFiles/publish/11/47/plans.json\"\n",
    "                  ==> \"plan_id\":  \n",
    "      ]\n",
    "    }\n",
    "    ```\n",
    "\n",
    "**Status code for https://api.humana.com/v1/cms/index.json: 200**\n",
    "\n",
    "    Status Code:200 OK\n",
    "    Content-Type:application/json; charset=utf-8\n",
    "    \n",
    "    \n",
    "    #!/usr/bin/javascript\n",
    "    {\"provider_urls\":[\"https://api.humana.com/v1/cms/pharma_providers-0.json\",\n",
    "                     ,\"https://api.humana.com/v1/cms/pharma_providers-558.json\"\n",
    "                     ],\n",
    "     \"formulary_urls\":[\"https://api.humana.com/v1/cms/drugs-0.json\",\n",
    "                      ,\"https://api.humana.com/v1/cms/drugs-96.json\"]\n",
    "    ,\"plan_urls\":[\"https://api.humana.com/v1/cms/plans-0.json\",\n",
    "                  \"https://api.humana.com/v1/cms/plansDental.json\"]}\n",
    "    \n",
    "    \n",
    "\n",
    "**Status code for https://www.bcbsal.org/cms/cms-data-index.json: 200**\n",
    "\n",
    "    #!/usr/bin/json\n",
    "    {\n",
    "        \"provider_urls\": [\n",
    "          \"https://www.bcbsal.org/cms/data/providers_platinum_gold.json\",\n",
    "          \"https://www.bcbsal.org/cms/data/providers_silver_bronze.json\",\n",
    "          \"https://www.bcbsal.org/cms/data/providers_bcbsal.json\"\n",
    "        ],\n",
    "        \"formulary_urls\": [\n",
    "          \"https://www.bcbsal.org/cms/data/drugs.json\"\n",
    "        ],\n",
    "        \"plan_urls\": [\n",
    "          \"https://www.bcbsal.org/cms/data/plans.json\"\n",
    "        ]\n",
    "    }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 299,
   "metadata": {
    "collapsed": false,
    "level": 7,
    "run_control": {
     "breakpoint": false
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Status code for https://fm.formularynavigator.com/jsonFiles/publish/11/47/cms-data-index.json: 200\n",
      "Status code for https://api.humana.com/v1/cms/index.json: 200\n",
      "Status code for https://www.bcbsal.org/cms/cms-data-index.json: 200\n"
     ]
    }
   ],
   "source": [
    "   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "level": 7,
    "run_control": {
     "breakpoint": false
    }
   },
   "outputs": [],
   "source": [
    "'''\n",
    "Iterate over these collections of URLs:\n",
    "    \"provider_urls\": [\n",
    "    \"formulary_urls\": [\n",
    "    \"plan_urls\": [\n",
    "\n",
    "Count # of elements in each:\n",
    "          ==> \"npi\":\n",
    "          ==> \"rxnorm_id\":\n",
    "          ==> \"plan_id\":  \n",
    "          \n",
    "          \n",
    "FIXME: But still need to apply schema validation          \n",
    "'''\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "level": 7,
    "run_control": {
     "breakpoint": false
    }
   },
   "outputs": [],
   "source": [
    "def analyze_count_url(url):\n",
    "    r = requests.get(url, allow_redirects=True)\n",
    "    #r = requests.head(url, allow_redirects=True)\n",
    "    print(\"Status code for \"+url+\": \"+str(r.status_code))\n",
    "    r_save = {}\n",
    "    r_save['status_code'] = r.status_code\n",
    "    r_save['history']     = r.history\n",
    "    r_save['url']         = r.url\n",
    "    r_save['_diff_url']   = (r.url == url)\n",
    "    r_save['headers']     = r.headers\n",
    "    r_save['elapsed']     = r.elapsed\n",
    "    r_save['total_secs']  = r.elapsed.total_seconds()\n",
    "\n",
    "    return r_save\n",
    "\n",
    "#Test\n",
    "analyze_count_url(\"http://ddod.us\")\n",
    "url=\"https://fm.formularynavigator.com/jsonFiles/publish/11/47/cms-data-index.json\"\n",
    "url=\"https://api.humana.com/v1/cms/index.json\"\n",
    "url=\"https://www.bcbsal.org/cms/cms-data-index.json\"\n",
    "\"\"\"\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 328,
   "metadata": {
    "collapsed": true,
    "level": 7,
    "run_control": {
     "breakpoint": false
    }
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "level": 7,
    "run_control": {
     "breakpoint": false
    }
   },
   "outputs": [],
   "source": [
    "import requests\n",
    "\n",
    "url = 'https://api.humana.com/v1/cms/index.json'\n",
    "url = 'http://www.healthdata.gov'\n",
    "url = 'http://gray.ddod.us'\n",
    "url = 'NOT SUBMITTED'\n",
    "#==== URLs for testing resonse codes ====\n",
    "url = 'http://httpbin.org/status/404'\n",
    "url = 'http://httpbin.org/status/200'\n",
    "url = 'http://httpbin.org/status/301'\n",
    "url = 'healthdata.gov'\n",
    "\n",
    "r = requests.get(url, allow_redirects=True)\n",
    "#r = requests.head(url, allow_redirects=True)\n",
    "print(\"Status code for \"+url+\": \"+str(r.status_code))\n",
    "print(r.history)\n",
    "print(r.url)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false,
    "level": 7,
    "run_control": {
     "breakpoint": false
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Not good! Status code:200    Content-type: text/html; charset=utf-8\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "\"\\nr_save = {}\\nr_save['status_code'] = r.status_code\\nr_save['history']     = r.history\\nr_save['url']         = r.url\\nr_save['_diff_url']   = (r.url == url)\\nr_save['headers']     = r.headers\\nr_save['elapsed']     = r.elapsed\\nr_save['total_secs']  = r.elapsed.total_seconds()\\n\""
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "   \n",
    "    \n",
    "    \n",
    "url = 'https://api.humana.com/v1/cms/index.json'\n",
    "url = \"https://fm.formularynavigator.com/jsonFiles/publish/11/47/cms-data-index.json\"\n",
    "url = \"https://api.humana.com/v1/cms/index.json\"  # Big index.json\n",
    "url = 'http://healthdata.gov'\n",
    "data_json = read_json_url(url)\n",
    "\n",
    "#response = requests.get(...)\n",
    "#if json_data \n",
    "'''\n",
    "r_save = {}\n",
    "r_save['status_code'] = r.status_code\n",
    "r_save['history']     = r.history\n",
    "r_save['url']         = r.url\n",
    "r_save['_diff_url']   = (r.url == url)\n",
    "r_save['headers']     = r.headers\n",
    "r_save['elapsed']     = r.elapsed\n",
    "r_save['total_secs']  = r.elapsed.total_seconds()\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": false,
    "level": 7,
    "run_control": {
     "breakpoint": false
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\n",
      "--------------------------\n",
      "json_url  = https://fm.formularynavigator.com/jsonFiles/publish/11/47/cms-data-index.json\n",
      "max_depth = 2\n",
      "Good!\n",
      "type(json_data) = <class 'dict'>\n",
      "# of items for provider_urls : 1\n",
      "\n",
      "\n",
      "\n",
      "--------------------------\n",
      "json_url  = http://fm.formularynavigator.com/jsonFiles/publish/11/47/providers.json\n",
      "max_depth = 1\n",
      "Good!\n",
      "type(json_data) = <class 'list'>\n",
      "# of items for : 3256\n",
      "# of items for formulary_urls : 1\n",
      "\n",
      "\n",
      "\n",
      "--------------------------\n",
      "json_url  = http://fm.formularynavigator.com/jsonFiles/publish/11/47/drugs.json\n",
      "max_depth = 1\n",
      "Good!\n",
      "type(json_data) = <class 'list'>\n",
      "# of items for : 4190\n",
      "# of items for plan_urls : 1\n",
      "\n",
      "\n",
      "\n",
      "--------------------------\n",
      "json_url  = http://fm.formularynavigator.com/jsonFiles/publish/11/47/plans.json\n",
      "max_depth = 1\n",
      "Good!\n",
      "type(json_data) = <class 'list'>\n",
      "# of items for : 9\n"
     ]
    }
   ],
   "source": [
    "        \n",
    "        \n",
    "# Test\n",
    "url = 'https://api.humana.com/v1/cms/index.json'\n",
    "url = \"https://fm.formularynavigator.com/jsonFiles/publish/11/47/cms-data-index.json\"\n",
    "#url = \"https://api.humana.com/v1/cms/index.json\"  # Big index.json\n",
    "#url = 'http://healthdata.gov'\n",
    "count_url_items(url, 2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false,
    "run_control": {
     "breakpoint": false
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n"
     ]
    }
   ],
   "source": [
    "if 2 >= 1: print(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "run_control": {
     "breakpoint": false
    }
   },
   "outputs": [],
   "source": [
    "Good!\n",
    "# of items for provider_urls : 1799\n",
    "# of items for formulary_urls : 97\n",
    "# of items for plan_urls : 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false,
    "run_control": {
     "breakpoint": false
    }
   },
   "outputs": [],
   "source": [
    "def process_url(url):\n",
    "    return"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false,
    "run_control": {
     "breakpoint": false
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n"
     ]
    }
   ],
   "source": [
    "x = 1\n",
    "x -= 1\n",
    "print(x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 243,
   "metadata": {
    "collapsed": false,
    "level": 7,
    "run_control": {
     "breakpoint": false
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ParseResult(scheme='http', netloc='rand.omst.uff', path='', params='', query='', fragment='')\n",
      "ParseResult(scheme='http', netloc='NOT SUBMITTED', path='', params='', query='', fragment='')\n",
      "NOT SUBMITTED\n"
     ]
    }
   ],
   "source": [
    "from urllib import parse\n",
    "\n",
    "url = 'http://www.healthdata.gov'\n",
    "url = 'http://gray.ddod.us'\n",
    "url = 'https://api.humana.com/v1/cms/index.json'\n",
    "url = 'gray.ddod.us'  # Unfortunetly this shows up in \"path\" but is still usable\n",
    "url = 'NOT SUBMITTED'\n",
    "\n",
    "\n",
    "def validate_url(url):\n",
    "\n",
    "    url = url.strip()\n",
    "    \n",
    "    # Add protocol manually if needed\n",
    "    if not (url.lower().find('http') == 0):\n",
    "        url = 'http://' + url\n",
    "\n",
    "\n",
    "        \n",
    "    if p.netloc=='':\n",
    "        print(\"Bad URL!\")\n",
    "        #try_url = validate_url('http://'+url)\n",
    "        #if try_url=='': try_url = validate_url('http://'+url)\n",
    "        #-----------------------------\n",
    "        # But is it possible that protocol was accidentally left out?\n",
    "        # NEXT: check p.path for no spaces and periods\n",
    "        #       Then try with http:// and https://\n",
    "        #-----------------------------\n",
    "        return ''\n",
    "\n",
    "    \n",
    "    print(p)\n",
    "\n",
    "    \n",
    "    return url\n",
    "\n",
    "\n",
    "# Test\n",
    "url = 'rand.omst.uff'\n",
    "validate_url(url)\n",
    "url = 'NOT SUBMITTED'\n",
    "validate_url(url)\n",
    "print(url)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "level": 7,
    "run_control": {
     "breakpoint": false
    }
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 271,
   "metadata": {
    "collapsed": false,
    "level": 7,
    "run_control": {
     "breakpoint": false
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "url: https://healthdata.gov\n",
      "url: \n"
     ]
    }
   ],
   "source": [
    "\n",
    "        \n",
    "# Test\n",
    "url = 'https://healthdata.gov'\n",
    "url2 = validate_url_custom(url)\n",
    "print(\"url: \" + url2)\n",
    "url = 'NOT DEFINED'\n",
    "url3 = validate_url_custom(url)\n",
    "print(\"url: \" + url3)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {
    "collapsed": false,
    "level": 7,
    "run_control": {
     "breakpoint": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "20"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#url.find('.')  #19\n",
    "len(url)  #20"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "collapsed": false,
    "level": 7,
    "run_control": {
     "breakpoint": false
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "http\n",
      "google.com\n"
     ]
    }
   ],
   "source": [
    "print(p.scheme)\n",
    "print(p.netloc)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "collapsed": false,
    "level": 7,
    "run_control": {
     "breakpoint": false
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "200\n",
      "[]\n"
     ]
    }
   ],
   "source": [
    "print(response_head.status_code)\n",
    "print(response_head.history)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "collapsed": false,
    "level": 7,
    "run_control": {
     "breakpoint": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "int"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(response_head.status_code)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": false,
    "level": 7,
    "run_control": {
     "breakpoint": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>URL Submitted</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>State</th>\n",
       "      <th>Marketplace Category</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>AK</th>\n",
       "      <th>FFM</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AL</th>\n",
       "      <th>FFM</th>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AR</th>\n",
       "      <th>SPM</th>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AZ</th>\n",
       "      <th>FFM</th>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DE</th>\n",
       "      <th>SPM</th>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FL</th>\n",
       "      <th>FFM</th>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GA</th>\n",
       "      <th>FFM</th>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IA</th>\n",
       "      <th>SPM</th>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IL</th>\n",
       "      <th>SPM</th>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IN</th>\n",
       "      <th>FFM</th>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>KS</th>\n",
       "      <th>SPM</th>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LA</th>\n",
       "      <th>FFM</th>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ME</th>\n",
       "      <th>SPM</th>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MI</th>\n",
       "      <th>SPM</th>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MO</th>\n",
       "      <th>FFM</th>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MS</th>\n",
       "      <th>FFM</th>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MT</th>\n",
       "      <th>SPM</th>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NC</th>\n",
       "      <th>FFM</th>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ND</th>\n",
       "      <th>FFM</th>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NE</th>\n",
       "      <th>SPM</th>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NH</th>\n",
       "      <th>SPM</th>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NJ</th>\n",
       "      <th>FFM</th>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OH</th>\n",
       "      <th>SPM</th>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OK</th>\n",
       "      <th>FFM</th>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PA</th>\n",
       "      <th>FFM</th>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SC</th>\n",
       "      <th>FFM</th>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SD</th>\n",
       "      <th>SPM</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TN</th>\n",
       "      <th>FFM</th>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TX</th>\n",
       "      <th>FFM</th>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>UT</th>\n",
       "      <th>SPM</th>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VA</th>\n",
       "      <th>SPM</th>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WI</th>\n",
       "      <th>FFM</th>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WV</th>\n",
       "      <th>SPM</th>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WY</th>\n",
       "      <th>FFM</th>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           URL Submitted\n",
       "                                   count\n",
       "State Marketplace Category              \n",
       "AK    FFM                              4\n",
       "AL    FFM                              8\n",
       "AR    SPM                              8\n",
       "AZ    FFM                             20\n",
       "DE    SPM                              7\n",
       "FL    FFM                             25\n",
       "GA    FFM                             20\n",
       "IA    SPM                             12\n",
       "IL    SPM                             18\n",
       "IN    FFM                             14\n",
       "KS    SPM                              9\n",
       "LA    FFM                             10\n",
       "ME    SPM                              6\n",
       "MI    SPM                             25\n",
       "MO    FFM                             13\n",
       "MS    FFM                              7\n",
       "MT    SPM                              6\n",
       "NC    FFM                             10\n",
       "ND    FFM                              6\n",
       "NE    SPM                              8\n",
       "NH    SPM                              9\n",
       "NJ    FFM                             15\n",
       "OH    SPM                             27\n",
       "OK    FFM                             10\n",
       "PA    FFM                             27\n",
       "SC    FFM                              9\n",
       "SD    SPM                              5\n",
       "TN    FFM                             11\n",
       "TX    FFM                             32\n",
       "UT    SPM                             11\n",
       "VA    SPM                             21\n",
       "WI    FFM                             25\n",
       "WV    SPM                              6\n",
       "WY    FFM                              6"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[['State', 'Marketplace Category', 'URL Submitted']].\\\n",
    "groupby(['State', 'Marketplace Category']).agg(['count'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": false,
    "level": 7,
    "run_control": {
     "breakpoint": false
    }
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'last_response' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-31-7c5be74f4b31>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[1;32m     36\u001b[0m \u001b[0;31m# Simulate saving prior response\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     37\u001b[0m \u001b[0mfile_changed\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;32mFalse\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 38\u001b[0;31m \u001b[0;32mfor\u001b[0m \u001b[0mkey\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mlast_value\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mlast_response\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mitems\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     39\u001b[0m     \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mresponse\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mheaders\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m==\u001b[0m \u001b[0mlast_value\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     40\u001b[0m         \u001b[0mfile_changed\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;32mTrue\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mNameError\u001b[0m: name 'last_response' is not defined"
     ]
    }
   ],
   "source": [
    "import requests\n",
    "\n",
    "\n",
    "url_page = 'https://www.cms.gov/cciio/resources/data-resources/marketplace-puf.html'\n",
    "url_file = 'http://download.cms.gov/marketplace-puf/2016/machine-readable-url-puf.zip'\n",
    "\n",
    "response = requests.head(url_page)\n",
    "#print(response.headers)\n",
    "'''\n",
    "{'X-Permitted-Cross-Domain-Policies': 'master-only', 'X-Frame-Options': 'SAMEORIGIN, SAMEORIGIN', \n",
    "'Connection': 'keep-alive', 'Date': 'Fri, 12 Feb 2016 15:30:03 GMT', 'Content-Length': '20', \n",
    "'Content-Encoding': 'gzip', 'Vary': 'Accept-Encoding', 'X-XSS-Protection': '1; \n",
    "mode=block', 'access-control-allow-origin': '*', 'Strict-Transport-Security': \n",
    "'max-age=31536000', 'X-N': 'S', 'Content-Type': 'text/html'}\n",
    "----------------------\n",
    "important items:\n",
    "'''\n",
    "\n",
    "\n",
    "\n",
    "response = requests.head(url_file)\n",
    "#print(response.headers)\n",
    "'''\n",
    "{'Server': 'Apache', 'Date': 'Fri, 12 Feb 2016 15:28:40 GMT', 'Accept-Ranges': 'bytes', \n",
    "'Last-Modified': 'Tue, 22 Dec 2015 22:02:55 GMT', 'Connection': 'keep-alive', '\n",
    "ETag': '\"667dbf1227e0a2ace7c89f4dc143c647:1450821775\"', 'Content-Length': '28623', \n",
    "'Content-Type': 'application/zip'} \n",
    "----------------------\n",
    "important items:\n",
    " 'Content-Type': 'application/zip'\n",
    " 'Content-Length': '28623'\n",
    " 'Last-Modified': 'Tue, 22 Dec 2015 22:02:55 GMT'\n",
    " 'ETag': '\"667dbf1227e0a2ace7c89f4dc143c647:1450821775\"   # MD5 hash of content\n",
    "'''\n",
    "\n",
    "# Simulate saving prior response\n",
    "file_changed = False\n",
    "for key, last_value in last_response.items():\n",
    "    if not response.headers[key] == last_value:\n",
    "        file_changed = True\n",
    "        break  # No need to check any more \n",
    "\n",
    "print(\"---\\nfile_changed: \"+str(file_changed))\n",
    "\n",
    "last_response = {'Last-Modified': response.headers['Last-Modified'],\n",
    "                 'ETag' : response.headers['ETag'],\n",
    "                 'Content-Length': response.headers['Content-Length']\n",
    "                }\n",
    "print(last_response)\n",
    "'''\n",
    "last_response = {'ETag': '\"667dbf1227e0a2ace7c89f4dc143c647:1450821775\"', 'Content-Length': '28623', 'Last-Modified': 'Tue, 22 Dec 2015 22:02:55 GMT'}\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {
    "collapsed": true,
    "level": 7,
    "run_control": {
     "breakpoint": false
    }
   },
   "outputs": [],
   "source": [
    "last_response = {'ETag': '\"a667dbf1227e0a2ace7c89f4d143c647:1450821775\"', 'Content-Length': '28623', 'Last-Modified': 'Tue, 22 Dec 2015 22:02:55 GMT'}\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {
    "collapsed": false,
    "level": 7,
    "run_control": {
     "breakpoint": false
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "---\n",
      "file_changed: False\n",
      "False\n"
     ]
    }
   ],
   "source": [
    "# FIXME: last_response should come from DB\n",
    "def check_file_changed(url_file,last_response):\n",
    "    import requests\n",
    "    \n",
    "    # Check valid URL\n",
    "    # return -1  # Indicates error\n",
    "\n",
    "    response = requests.head(url_file)\n",
    "\n",
    "    # Simulate saving prior response\n",
    "    # For now last_response is global\n",
    "    file_changed = False\n",
    "    for key, last_value in last_response.items():\n",
    "        if not response.headers[key] == last_value:\n",
    "            file_changed = True\n",
    "            break  # No need to check any more \n",
    "\n",
    "    print(\"---\\nfile_changed: \"+str(file_changed))\n",
    "\n",
    "    #Save prior response\n",
    "    last_response = {'Last-Modified': response.headers['Last-Modified'],\n",
    "                     'ETag' : response.headers['ETag'],\n",
    "                     'Content-Length': response.headers['Content-Length']\n",
    "                    }\n",
    "    \n",
    "    if file_changed:\n",
    "        return url_file  # Indicates changed\n",
    "    else:\n",
    "        return False\n",
    "    \n",
    "    \n",
    "    \n",
    "    \n",
    "    \n",
    "#Test\n",
    "last_response = {'ETag': '\"667dbf1227e0a2ace7c89f4dc143c647:1450821775\"', 'Content-Length': '28623', 'Last-Modified': 'Tue, 22 Dec 2015 22:02:55 GMT'}\n",
    "url_file = 'http://download.cms.gov/marketplace-puf/2016/machine-readable-url-puf.zip'\n",
    "print( check_file_changed(url_file,last_response))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 175,
   "metadata": {
    "collapsed": false,
    "level": 7,
    "run_control": {
     "breakpoint": false
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Already exists\n",
      "1\n"
     ]
    }
   ],
   "source": [
    "from pymongo import MongoClient\n",
    "from bson import ObjectId\n",
    "import datetime\n",
    "\n",
    "\n",
    "def check_file_changed(url_file,crawl_log):\n",
    "    # Save last file loaded\n",
    "    if not crawl_log.find_one({'url':url_file,'header': last_response}): \n",
    "        response_doc = {\n",
    "            'url':url_file,\n",
    "            'date': datetime.datetime.utcnow(),\n",
    "            'header': last_response    \n",
    "        }\n",
    "        print(\"---\\nInserting: \"+str(document)) \n",
    "        document_id = collection.insert_one(response_doc).inserted_id\n",
    "        print(document_id)\n",
    "    else:\n",
    "        print('Already exists')\n",
    "    print(collection.count())\n",
    "\n",
    "        \n",
    "    \n",
    "    \n",
    "    \n",
    "        for key, last_value in last_response.items():\n",
    "        if not response.headers[key] == last_value:\n",
    "            file_changed = True\n",
    "            break  # No need to check any more \n",
    "\n",
    "    print(\"---\\nfile_changed: \"+str(file_changed))\n",
    "\n",
    "    #Save prior response\n",
    "    last_response = {'Last-Modified': response.headers['Last-Modified'],\n",
    "                     'ETag' : response.headers['ETag'],\n",
    "                     'Content-Length': response.headers['Content-Length']\n",
    "                    }\n",
    "    \n",
    "\n",
    "    \n",
    "    \n",
    "\n",
    "\n",
    "def main():\n",
    "    client = MongoClient()\n",
    "    db = client.crawler  # Equiv to relational database\n",
    "    crawl_log = db.crawl_log  # Equiv to table\n",
    "    \n",
    "    check_file_changed(url_file,crawl_log)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 194,
   "metadata": {
    "collapsed": false,
    "level": 7,
    "run_control": {
     "breakpoint": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pymongo.cursor.Cursor at 0x108bcc048>"
      ]
     },
     "execution_count": 194,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get last updated\n",
    "collection.find({'url':url_file}).sort([(\"id\", -1)]).limit(1)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "metadata": {
    "collapsed": false,
    "level": 7,
    "run_control": {
     "breakpoint": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'_id': ObjectId('56be057d3650b51ffb96d6d5'),\n",
       " 'date': datetime.datetime(2016, 2, 12, 16, 17, 1, 339000),\n",
       " 'header': {'Content-Length': '28623',\n",
       "  'ETag': '\"667dbf1227e0a2ace7c89f4dc143c647:1450821775\"',\n",
       "  'Last-Modified': 'Tue, 22 Dec 2015 22:02:55 GMT'},\n",
       " 'url': 'http://download.cms.gov/marketplace-puf/2016/machine-readable-url-puf.zip'}"
      ]
     },
     "execution_count": 176,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "collection.find_one({'url':url_file,'header': last_response})\n",
    "#collection.remove({'_id': ObjectId('56be05303650b51ffb96d6d4')})\n",
    "#                   db.inventory.remove( { type : \"food\" }, 1 )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 174,
   "metadata": {
    "collapsed": false,
    "level": 7,
    "run_control": {
     "breakpoint": false
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1\n"
     ]
    }
   ],
   "source": [
    "collection.find_one({\"_id\":ObjectId('56be05303650b51ffb96d6d4')})\n",
    "print(collection.count())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 169,
   "metadata": {
    "collapsed": false,
    "level": 7,
    "run_control": {
     "breakpoint": false
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/dportnoy/anaconda3/lib/python3.5/site-packages/ipykernel/__main__.py:1: DeprecationWarning: remove is deprecated. Use delete_one or delete_many instead.\n",
      "  if __name__ == '__main__':\n"
     ]
    }
   ],
   "source": [
    "collection.remove( {\"_id\": ObjectId(\"4d512b45cc9374271b02ec4f\")});"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "level": 7,
    "run_control": {
     "breakpoint": false
    }
   },
   "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.5.1"
  },
  "toc": {
   "toc_cell": false,
   "toc_number_sections": true,
   "toc_threshold": 4,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}