Pertussis Incidence Report.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Extracting montly Measles and Pertussis incidence data by state (1951-Present):\n",
    "## 1996-2017:\n",
    "Weekly incidence data from 1996 up to present are available from CDC WONDER's MMWR tables.<br>\n",
    "CDC WONDER is a query system for the analysis of public health data.<br>\n",
    "MMWR (Morbidity and Mortality Weekly Report) is based on weekly reports to CDC by state health departments.<br>\n",
    "Extarcting data from MMWR could be challenging because of the format inconsistencies:\n",
    "- URL links to tables do not have similar format.\n",
    "- Pertussis incidence data from 2002 to 2005 are reported as cumulative numbers.\n",
    "- Data files have typo, \"Cumulative\" has been misspelled as \"Cummulative\".<br>\n",
    "\n",
    "## 1951-1995\n",
    "After reaching out to CDC INFO, it was revealed that they do not have the data prior to 1996 and they suggested to contact states directly."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pertussis Incidence Report Extracted From CDC Wonder"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Scraping CDC WONDER to extract the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": true,
    "hide_input": false
   },
   "outputs": [],
   "source": [
    "import os\n",
    "import openpyxl\n",
    "import numpy as np\n",
    "from lxml import html\n",
    "import requests\n",
    "import sqlite3\n",
    "import csv\n",
    "import pandas as pd\n",
    "from io import StringIO\n",
    "import urllib\n",
    "from bs4 import BeautifulSoup\n",
    "import re\n",
    "from datetime import date,timedelta"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Running the above cell revealed that:\n",
    "\\begin{itemize}\n",
    "  \\item Pertussis stats. is available for all the years from 1996 to 2017\n",
    "  \\item Measles stats. is only available as a separate link from 1996 to 2002\n",
    "\\end{itemize}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def HyperHunter(URL,String,Tag): ### Returns the hyperlink that contains 'String' on URL page  \n",
    "    page = requests.get(URL)\n",
    "    soup = BeautifulSoup(page.text, 'html.parser')\n",
    "    tag_value=soup.find(Tag,text=re.compile(String))\n",
    "    return tag_value "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def MMWRWeekRange(Year):\n",
    "     ##Returns a panda PeriodIndex containing MMWR weeks of the given year\n",
    "     ## MMWR week definition: https://wwwn.cdc.gov/nndss/document/MMWR_week_overview.pdf\n",
    "     ## The first day of any MMWR week is Sunday. MMWR week numbering is sequential beginning with 1 and\n",
    "     ## incrementing with each week to a maximum of 52 or 53. MMWR week #1 of an MMWR year is the first week of\n",
    "     ## the year that has at least four days in the calendar year. For example, if January 1 occurs on a Sunday, Monday,\n",
    "     ## Tuesday or Wednesday, the calendar week that includes January 1 would be MMWR week #1. If January 1\n",
    "     ## occurs on a Thursday, Friday, or Saturday, the calendar week that includes January 1 would be the last MMWR\n",
    "     ## week of the previous year (#52 or #53). Because of this rule, December 29, 30, and 31 could potentially fall into\n",
    "     ## MMWR week #1 of the following MMWR year.  \n",
    "    start=date(Year,1,1)\n",
    "    end=date(Year,12,31)\n",
    "    Jan1st_of_year=start.weekday()\n",
    "    Jan1st_of_next_year=date(Year+1,1,1).weekday()\n",
    "    rng = pd.period_range(start,end,freq='W-SAT')\n",
    "    if Jan1st_of_year not in [0,1,2,6]:\n",
    "        rng=rng[1:]\n",
    "    if Jan1st_of_next_year in [0,1,2]:\n",
    "        rng=rng[0:-1]\n",
    "    return rng\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def MMWRParser(Year,Week,Disease):  \n",
    "### Returns a Pandas dataframe containing cummulative incidence data for each state \n",
    "        reporting_date=date(Year-1,12,31)+timedelta(days=Week*7)\n",
    "        str_Week=str(Week)\n",
    "        if Week<10:\n",
    "            str_Week='0'+str_Week\n",
    "        url='https://wonder.cdc.gov/nndss/nndss_weekly_tables_menu.asp?mmwr_year='+str(Year)+'&mmwr_week='+str_Week  ### str(Year+1) was used to extract the cum data from the next year data \n",
    "        submit_hyperlink=HyperHunter(url,Disease,'td')\n",
    "        Export_hyperlink='https://wonder.cdc.gov/nndss/'+submit_hyperlink.find_next_sibling().find('a').attrs['href']\n",
    "        MMWR_txt = requests.get(Export_hyperlink, allow_redirects=True)\n",
    "        if MMWR_txt.status_code>300:\n",
    "            Export_hyperlink='https://wonder.cdc.gov/'+submit_hyperlink.find_next_sibling().find('a').attrs['href']\n",
    "            MMWR_txt = requests.get(Export_hyperlink, allow_redirects=True)\n",
    "        content=MMWR_txt.text\n",
    "        c=content.split(\"tab delimited data:\")\n",
    "        headersString=c[0].split(\"column labels in same order that data fields appears in each record below:\")\n",
    "        headersString=headersString[1]\n",
    "        headers=headersString.splitlines()\n",
    "        headers=headers[2:-1]\n",
    "        c=c[1].split('\\r\\n\\r\\n')\n",
    "        t=pd.read_table(StringIO(c[0]),sep='\\t',header=None,index_col=0)\n",
    "        t=t.iloc[:,0:len(headers)]  ### to get rid of the extar NaN columns\n",
    "        t = t[~t.index.duplicated(keep='first')]\n",
    "        t.columns=headers\n",
    "        idx=t.index\n",
    "        #reg='Pertussis.*cum.*'+str(Year)\n",
    "        reg='Pertussis.*current.*'\n",
    "        t=t.filter(regex=reg)\n",
    "        t=t.T\n",
    "        t.columns=idx\n",
    "        t.index=[Week]\n",
    "        t.index.name='Week'\n",
    "        weeks_rng=MMWRWeekRange(Year)\n",
    "        week_period=weeks_rng[Week-1]\n",
    "        Week_Start=week_period.start_time.strftime('%Y-%m-%d')\n",
    "        Week_end=week_period.end_time.strftime('%Y-%m-%d')\n",
    "        t.insert(0,'Start', Week_Start, allow_duplicates=False)\n",
    "        t.insert(1,'End', Week_end, allow_duplicates=False)\n",
    "        t=t.replace('-',0)\n",
    "        t=t.replace('U',np.nan)\n",
    "        t=t.apply(pd.to_numeric,errors='ignore',downcast='float')\n",
    "        return t"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "code_folding": [],
    "run_control": {
     "marked": false
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1996\n",
      "2\n",
      "3\n",
      "4\n",
      "5\n",
      "6\n",
      "7\n",
      "8\n",
      "9\n",
      "10\n",
      "11\n",
      "12\n",
      "13\n",
      "14\n",
      "15\n",
      "16\n",
      "17\n",
      "18\n",
      "19\n",
      "20\n",
      "21\n",
      "22\n",
      "23\n",
      "24\n",
      "25\n",
      "26\n",
      "27\n",
      "28\n",
      "29\n",
      "30\n",
      "31\n",
      "32\n",
      "33\n",
      "34\n",
      "35\n",
      "36\n",
      "37\n",
      "38\n",
      "39\n",
      "40\n",
      "41\n",
      "42\n",
      "43\n",
      "44\n",
      "45\n",
      "46\n",
      "47\n",
      "48\n",
      "49\n",
      "50\n",
      "51\n",
      "52\n",
      "1997\n",
      "2\n",
      "3\n",
      "4\n",
      "5\n",
      "6\n",
      "7\n",
      "8\n",
      "9\n",
      "10\n",
      "11\n",
      "12\n",
      "13\n",
      "14\n",
      "15\n",
      "16\n",
      "17\n",
      "18\n",
      "19\n",
      "20\n",
      "21\n",
      "22\n",
      "23\n",
      "24\n",
      "25\n",
      "26\n",
      "27\n",
      "28\n",
      "29\n",
      "30\n",
      "31\n",
      "32\n",
      "33\n",
      "34\n",
      "35\n",
      "36\n",
      "37\n",
      "38\n",
      "39\n",
      "40\n",
      "41\n",
      "42\n",
      "43\n",
      "44\n",
      "45\n",
      "46\n",
      "47\n",
      "48\n",
      "49\n",
      "50\n",
      "51\n",
      "52\n",
      "53\n",
      "1998\n",
      "2\n",
      "3\n",
      "4\n",
      "5\n",
      "6\n",
      "7\n",
      "8\n",
      "9\n",
      "10\n",
      "11\n",
      "12\n",
      "13\n",
      "14\n",
      "15\n",
      "16\n",
      "17\n",
      "18\n",
      "19\n",
      "20\n",
      "21\n",
      "22\n",
      "23\n",
      "24\n",
      "25\n",
      "26\n",
      "27\n",
      "28\n",
      "29\n",
      "30\n",
      "31\n",
      "32\n",
      "33\n",
      "34\n",
      "35\n",
      "36\n",
      "37\n",
      "38\n",
      "39\n",
      "40\n",
      "41\n",
      "42\n",
      "43\n",
      "44\n",
      "45\n",
      "46\n",
      "47\n",
      "48\n",
      "49\n",
      "50\n",
      "51\n",
      "52\n",
      "1999\n",
      "2\n",
      "3\n",
      "4\n",
      "5\n",
      "6\n",
      "7\n",
      "8\n",
      "9\n",
      "10\n",
      "11\n",
      "12\n",
      "13\n",
      "14\n",
      "15\n",
      "16\n",
      "17\n",
      "18\n",
      "19\n",
      "20\n",
      "21\n",
      "22\n",
      "23\n",
      "24\n",
      "25\n",
      "26\n",
      "27\n",
      "28\n",
      "29\n",
      "30\n",
      "31\n",
      "32\n",
      "33\n",
      "34\n",
      "35\n",
      "36\n",
      "37\n",
      "38\n",
      "39\n",
      "40\n",
      "41\n",
      "42\n",
      "43\n",
      "44\n",
      "45\n",
      "46\n",
      "47\n",
      "48\n",
      "49\n",
      "50\n",
      "51\n",
      "52\n",
      "2000\n",
      "2\n",
      "3\n",
      "4\n",
      "5\n",
      "6\n",
      "7\n",
      "8\n",
      "9\n",
      "10\n",
      "11\n",
      "12\n",
      "13\n",
      "14\n",
      "15\n",
      "16\n",
      "17\n",
      "18\n",
      "19\n",
      "20\n",
      "21\n",
      "22\n",
      "23\n",
      "24\n",
      "25\n",
      "26\n",
      "27\n",
      "28\n",
      "29\n",
      "30\n",
      "31\n",
      "32\n",
      "33\n",
      "34\n",
      "35\n",
      "36\n",
      "37\n",
      "38\n",
      "39\n",
      "40\n",
      "41\n",
      "42\n",
      "43\n",
      "44\n",
      "45\n",
      "46\n",
      "47\n",
      "48\n",
      "49\n",
      "50\n",
      "51\n",
      "52\n",
      "2001\n",
      "2\n",
      "3\n",
      "4\n",
      "5\n",
      "6\n",
      "7\n",
      "8\n",
      "9\n",
      "10\n",
      "11\n",
      "12\n",
      "13\n",
      "14\n",
      "15\n",
      "16\n",
      "17\n",
      "18\n",
      "19\n",
      "20\n",
      "21\n",
      "22\n",
      "23\n",
      "24\n",
      "25\n",
      "26\n",
      "27\n",
      "28\n",
      "29\n",
      "30\n",
      "31\n",
      "32\n",
      "33\n",
      "34\n",
      "35\n",
      "36\n",
      "37\n",
      "38\n",
      "39\n",
      "40\n",
      "41\n",
      "42\n",
      "43\n",
      "44\n",
      "45\n",
      "46\n",
      "47\n",
      "48\n",
      "49\n",
      "50\n",
      "51\n",
      "52\n",
      "2006\n",
      "2\n",
      "3\n",
      "4\n",
      "5\n",
      "6\n",
      "7\n",
      "8\n",
      "9\n",
      "10\n",
      "11\n",
      "12\n",
      "13\n",
      "14\n",
      "15\n",
      "16\n",
      "17\n",
      "18\n",
      "19\n",
      "20\n",
      "21\n",
      "22\n",
      "23\n",
      "24\n",
      "25\n",
      "26\n",
      "27\n",
      "28\n",
      "29\n",
      "30\n",
      "31\n",
      "32\n",
      "33\n",
      "34\n",
      "35\n",
      "36\n",
      "37\n",
      "38\n",
      "39\n",
      "40\n",
      "41\n",
      "42\n",
      "43\n",
      "44\n",
      "45\n",
      "46\n",
      "47\n",
      "48\n",
      "49\n",
      "50\n",
      "51\n",
      "52\n",
      "2007\n",
      "2\n",
      "3\n",
      "4\n",
      "5\n",
      "6\n",
      "7\n",
      "8\n",
      "9\n",
      "10\n",
      "11\n",
      "12\n",
      "13\n",
      "14\n",
      "15\n",
      "16\n",
      "17\n",
      "18\n",
      "19\n",
      "20\n",
      "21\n",
      "22\n",
      "23\n",
      "24\n",
      "25\n",
      "26\n",
      "27\n",
      "28\n",
      "29\n",
      "30\n",
      "31\n",
      "32\n",
      "33\n",
      "34\n",
      "35\n",
      "36\n",
      "37\n",
      "38\n",
      "39\n",
      "40\n",
      "41\n",
      "42\n",
      "43\n",
      "44\n",
      "45\n",
      "46\n",
      "47\n",
      "48\n",
      "49\n",
      "50\n",
      "51\n",
      "52\n",
      "2008\n",
      "2\n",
      "3\n",
      "4\n",
      "5\n",
      "6\n",
      "7\n",
      "8\n",
      "9\n",
      "10\n",
      "11\n",
      "12\n",
      "13\n",
      "14\n",
      "15\n",
      "16\n",
      "17\n",
      "18\n",
      "19\n",
      "20\n",
      "21\n",
      "22\n",
      "23\n",
      "24\n",
      "25\n",
      "26\n",
      "27\n",
      "28\n",
      "29\n",
      "30\n",
      "31\n",
      "32\n",
      "33\n",
      "34\n",
      "35\n",
      "36\n",
      "37\n",
      "38\n",
      "39\n",
      "40\n",
      "41\n",
      "42\n",
      "43\n",
      "44\n",
      "45\n",
      "46\n",
      "47\n",
      "48\n",
      "49\n",
      "50\n",
      "51\n",
      "52\n",
      "53\n",
      "2009\n",
      "2\n",
      "3\n",
      "4\n",
      "5\n",
      "6\n",
      "7\n",
      "8\n",
      "9\n",
      "10\n",
      "11\n",
      "12\n",
      "13\n",
      "14\n",
      "15\n",
      "16\n",
      "17\n",
      "18\n",
      "19\n",
      "20\n",
      "21\n",
      "22\n",
      "23\n",
      "24\n",
      "25\n",
      "26\n",
      "27\n",
      "28\n",
      "29\n",
      "30\n",
      "31\n",
      "32\n",
      "33\n",
      "34\n",
      "35\n",
      "36\n",
      "37\n",
      "38\n",
      "39\n",
      "40\n",
      "41\n",
      "42\n",
      "43\n",
      "44\n",
      "45\n",
      "46\n",
      "47\n",
      "48\n",
      "49\n",
      "50\n",
      "51\n",
      "52\n",
      "2010\n",
      "2\n",
      "3\n",
      "4\n",
      "5\n",
      "6\n",
      "7\n",
      "8\n",
      "9\n",
      "10\n",
      "11\n",
      "12\n",
      "13\n",
      "14\n",
      "15\n",
      "16\n",
      "17\n",
      "18\n",
      "19\n",
      "20\n",
      "21\n",
      "22\n",
      "23\n",
      "24\n",
      "25\n",
      "26\n",
      "27\n",
      "28\n",
      "29\n",
      "30\n",
      "31\n",
      "32\n",
      "33\n",
      "34\n",
      "35\n",
      "36\n",
      "37\n",
      "38\n",
      "39\n",
      "40\n",
      "41\n",
      "42\n",
      "43\n",
      "44\n",
      "45\n",
      "46\n",
      "47\n",
      "48\n",
      "49\n",
      "50\n",
      "51\n",
      "52\n",
      "2011\n",
      "2\n",
      "3\n",
      "4\n",
      "5\n",
      "6\n",
      "7\n",
      "8\n",
      "9\n",
      "10\n",
      "11\n",
      "12\n",
      "13\n",
      "14\n",
      "15\n",
      "16\n",
      "17\n",
      "18\n",
      "19\n",
      "20\n",
      "21\n",
      "22\n",
      "23\n",
      "24\n",
      "25\n",
      "26\n",
      "27\n",
      "28\n",
      "29\n",
      "30\n",
      "31\n",
      "32\n",
      "33\n",
      "34\n",
      "35\n",
      "36\n",
      "37\n",
      "38\n",
      "39\n",
      "40\n",
      "41\n",
      "42\n",
      "43\n",
      "44\n",
      "45\n",
      "46\n",
      "47\n",
      "48\n",
      "49\n",
      "50\n",
      "51\n",
      "52\n",
      "2012\n",
      "2\n",
      "3\n",
      "4\n",
      "5\n",
      "6\n",
      "7\n",
      "8\n",
      "9\n",
      "10\n",
      "11\n",
      "12\n",
      "13\n",
      "14\n",
      "15\n",
      "16\n",
      "17\n",
      "18\n",
      "19\n",
      "20\n",
      "21\n",
      "22\n",
      "23\n",
      "24\n",
      "25\n",
      "26\n",
      "27\n",
      "28\n",
      "29\n",
      "30\n",
      "31\n",
      "32\n",
      "33\n",
      "34\n",
      "35\n",
      "36\n",
      "37\n",
      "38\n",
      "39\n",
      "40\n",
      "41\n",
      "42\n",
      "43\n",
      "44\n",
      "45\n",
      "46\n",
      "47\n",
      "48\n",
      "49\n",
      "50\n",
      "51\n",
      "52\n"
     ]
    }
   ],
   "source": [
    "Disease='Pertussis'\n",
    "#file_name='Pertussis Cumulative Incidence Data by State 1995-2016 (cumulative data from the next year table).xlsx'\n",
    "file_name='Pertussis Current Incidence Data by State 1996-2001, 2006-2017.xlsx'\n",
    "writer = pd.ExcelWriter(file_name)\n",
    "#writer = pd.ExcelWriter(file_name, engine='openpyxl')\n",
    "#book = openpyxl.load_workbook(file_name)\n",
    "#writer.book = book\n",
    "Years=list(range(1996,2002))+list(range(2006,2013))  ### Years range for the years containg current week data\n",
    "for Year in Years:   \n",
    "    print(Year)\n",
    "    Week_1=MMWRParser(Year,1,Disease)\n",
    "    #cum_table=Week_1.copy()\n",
    "    current_table=Week_1.copy()\n",
    "    number_of_weeks=len(MMWRWeekRange(Year))\n",
    "    for Week in range(2,number_of_weeks+1):\n",
    "        print(Week)\n",
    "        temp=MMWRParser(Year,Week,Disease)\n",
    "        #cum_table=pd.concat([cum_table, temp], axis=0)\n",
    "        current_table=pd.concat([current_table, temp], axis=0)\n",
    "    #cum_table.to_excel(writer,str(Year))\n",
    "    current_table.to_excel(writer,str(Year))\n",
    "    writer.save()\n",
    "    writer = pd.ExcelWriter(file_name, engine='openpyxl')\n",
    "    book = openpyxl.load_workbook(file_name)\n",
    "    writer.book = book  \n",
    "\n",
    "writer.save()\n",
    "writer.close()"
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Edit Metadata",
  "hide_input": false,
  "kernelspec": {
   "display_name": "Python [default]",
   "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.6.3"
  },
  "latex_envs": {
   "LaTeX_envs_menu_present": true,
   "autoclose": false,
   "autocomplete": true,
   "bibliofile": "biblio.bib",
   "cite_by": "apalike",
   "current_citInitial": 1,
   "eqLabelWithNumbers": true,
   "eqNumInitial": 1,
   "hotkeys": {
    "equation": "Ctrl-E",
    "itemize": "Ctrl-I"
   },
   "labels_anchors": false,
   "latex_user_defs": false,
   "report_style_numbering": false,
   "user_envs_cfg": false
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": false,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}