MMWR Cumulative to Current Converter.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 174,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "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": "code",
   "execution_count": 175,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def MMWRCumtoCurrent(file_name,Sheet_name,Year,header_row,index_column,NaN_value):\n",
    "    ### converts MMWR cumulative incidence tables to incidence tables  \n",
    "    df=pd.read_excel(file_name,Sheet_name,header=header_row,index_col=index_column,na_values=NaN_value,thousands=',')\n",
    "    df=df.replace('--',0)\n",
    "    df=df.apply(pd.to_numeric,errors='ignore',downcast='float')\n",
    "    ### Find and interpolate weeks with cum_count=0 whose previous weeks have cum_count >0\n",
    "    for i in range(1,52):\n",
    "        df=df.mask((df==0)&(df.shift(i)>0))\n",
    "    df.interpolate(method='linear', axis=0, limit=None, inplace=True)\n",
    "    ########\n",
    "    df2=df.iloc[:,2:].copy()\n",
    "    df2.drop(df2.index[-1],inplace=True)\n",
    "    df2.index+=1\n",
    "    df2.loc[1]=[0.0]*df2.shape[1]\n",
    "    df2=df2.sort_index(axis=0)\n",
    "    df.iloc[:,2:]=df.iloc[:,2:].subtract(df2,fill_value=None)\n",
    "    CurrentWeekTable=df.round()\n",
    "    #CurrentWeekTable[CurrentWeekTable<0]=0\n",
    "    return CurrentWeekTable"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1995\n",
      "1996\n",
      "1997\n",
      "1998\n",
      "1999\n",
      "2000\n",
      "2001\n",
      "2002\n",
      "2003\n",
      "2004\n",
      "2005\n",
      "2006\n",
      "2007\n",
      "2008\n",
      "2009\n",
      "2010\n",
      "2011\n",
      "2012\n",
      "2013\n",
      "2014\n",
      "2015\n",
      "2016\n"
     ]
    }
   ],
   "source": [
    "output_file_name='Pertussis weekly Incidence Data by State 1996-2017(from the cumulative data for the next year table).xlsx'\n",
    "cum_file_name='Pertussis Cumulative Incidence Data by State 1995-2016 (cumulative data from the next year table).xlsx'\n",
    "header_row=0\n",
    "index_column=0\n",
    "NaN_value=['']\n",
    "\n",
    "\n",
    "\n",
    "writer = pd.ExcelWriter(output_file_name,engine='xlsxwriter')\n",
    "#for Year in range(1996,2018): ## This line was used for the data that were extracted from the cum daa of the SAME year table\n",
    "for Year in range(1995,2017): ## for the data that were extracted from the cum daa of the same NEXT table\n",
    "    Sheet_name=str(Year)\n",
    "    print(Year)\n",
    "    CurrentWeekTable=MMWRCumtoCurrent(cum_file_name,Sheet_name,Year,header_row,index_column,NaN_value)\n",
    "    CurrentWeekTable.to_excel(writer,Sheet_name)\n",
    "    \n",
    "writer.save()               \n",
    "writer.close()\n"
   ]
  }
 ],
 "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.6.3"
  },
  "latex_envs": {
   "LaTeX_envs_menu_present": true,
   "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
  },
  "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
}