NHS-hack.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# NHS Table Parser"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Import from CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "class importCSV:\n",
    "    # imports a csv and returns a pandas table\n",
    "    def __init__(self, CSVfile, output):\n",
    "        # given it's Python 2.7 it has to check encoding\n",
    "        rawTable = False\n",
    "        try:\n",
    "            rawTable = [line.strip().decode('latin-1').split(',') for line in open(CSVfile, 'r')]\n",
    "\n",
    "        except UnicodeDecodeError:\n",
    "            rawTable = [line.strip().decode('utf-8').split(',') for line in open(CSVfile, 'r')]\n",
    "\n",
    "        except IOError as e:\n",
    "            print 'Could not open %s' %(CSVfile)\n",
    "\n",
    "        self.data = False\n",
    "        if rawTable:\n",
    "            matrix = []\n",
    "            maximumLen = max(len(row) for row in rawTable)\n",
    "            for i, row in enumerate(rawTable):\n",
    "                if len(row) == maximumLen:\n",
    "                    matrix.append(row)\n",
    "            # data object is now a pandas table\n",
    "            self.data = pd.DataFrame(matrix[1:], columns=matrix[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "csvf = importCSV('07_11_france.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "csvf.data.to_csv()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Import from Excel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import xlrd\n",
    "import pandas as pd\n",
    "from dateutil.parser import parse\n",
    "\n",
    "class importXLS:\n",
    "    \n",
    "    def __init__(self, xlsFile, filePath):\n",
    "        # import an Excel file\n",
    "        \n",
    "        try:\n",
    "            excel = xlrd.open_workbook(xlsFile)\n",
    "            sheet_names = excel.sheet_names()\n",
    "            self.data = [self.parseSheet(excel.sheet_by_name(key), key) for key in excel.sheet_names()]\n",
    "            for table in self.data:\n",
    "                if not filePath.endswith('/') and filePath:\n",
    "                    filePath = filePath.strip() + '/'\n",
    "                dateInFile = self.checkIfDate(xlsFile)\n",
    "                try:\n",
    "                    dataName = table.keys()[0]\n",
    "                    if dateInFile:\n",
    "                        table[dataName]['table'].to_csv(filePath + dataName.replace(' ', '_') + '_' + dateInFile + '.csv')\n",
    "                        continue\n",
    "                    table[dataName]['table'].to_csv(filePath + dataName.replace(' ', '_') + '_' + table[dataName]['date'] + '.csv')\n",
    "                except Exception as e:\n",
    "                    print 'Error writing table to CSV: %s' %(e)\n",
    "        except Exception as e:\n",
    "            print 'Error parsing Excel file: %s' %(e)\n",
    "                \n",
    "    def parseSheet(self, sheet, sheetName):\n",
    "        # parses the excel sheet\n",
    "        \n",
    "        # the data is returned as a dict in order to return the date\n",
    "        dataReturn = {sheetName: {\n",
    "                'date': '',\n",
    "                'table': False\n",
    "            }}\n",
    "        def exists(n):\n",
    "            if n:\n",
    "                if type(n) == str:\n",
    "                    if n.strip():\n",
    "                        return True\n",
    "                    return False\n",
    "                return True\n",
    "                \n",
    "            return False\n",
    "        \n",
    "        try:\n",
    "            values = [[sheet.cell(row, col).value for col in range(0, sheet.ncols)] for row in range(0, sheet.nrows)]\n",
    "            # initial loop to find the table and skip the headers\n",
    "            rowLengths = []\n",
    "            longRowCounter = 0\n",
    "            tableStarts = {'row': 0, 'col': 0}\n",
    "            superfields = False\n",
    "            for i, row in enumerate(values):\n",
    "                realRowLength = len([n for n in row if exists(n)])\n",
    "                if not realRowLength > 0:\n",
    "                    continue\n",
    "                rowLengths.append(realRowLength)\n",
    "                # check if the row could be in the table\n",
    "                if realRowLength == max(rowLengths):\n",
    "                    longRowCounter += 1\n",
    "                # once the lengths are long and consistent then we are reading the table\n",
    "                if longRowCounter > 20:\n",
    "                    # we found the table, now lets find the column it starts in\n",
    "                    tableStarts['col'] = (i for i,v in enumerate(row) if exists(v)).next()\n",
    "                    break\n",
    "            # second loop to find the row number where the table starts\n",
    "            for i, row in enumerate(values):\n",
    "                realRowLength = len([n for n in row if exists(n)])\n",
    "                if realRowLength == max(rowLengths):\n",
    "                    tableStarts['row'] = i\n",
    "                    break\n",
    "            # just to be safe lets check if there are any \"super\" fields\n",
    "            if len(values[tableStarts['row'] - 1]) > min(rowLengths):\n",
    "                # there are indeed \"super\" fields\n",
    "                superfields = True\n",
    "                tableStarts['row'] -= 1\n",
    "            # now return the initial header to find the datestamp\n",
    "            init_header = values[:tableStarts['row']]\n",
    "            dataReturn[sheetName]['date'] = self.findDate(init_header)\n",
    "            # build the table\n",
    "            matrix = []\n",
    "            endReached = False\n",
    "            # clean up table\n",
    "            for i, row in enumerate(values[tableStarts['row']:]):\n",
    "                realRowLength = len([n for n in row if exists(n)])\n",
    "                # check if table finished\n",
    "                if realRowLength < 2:\n",
    "                    emptyRowCount = 0\n",
    "                    nextRowCount = 0\n",
    "                    for nextRow in values[i:]:\n",
    "                        if len([n for n in nextRow if exists(n)]) < 2:\n",
    "                            emptyRowCount += 1\n",
    "                            if emptyRowCount > 20:\n",
    "                                endReached = True\n",
    "                                emptyRowCount = 0\n",
    "                                break\n",
    "                            continue\n",
    "                        nextRowCount += 1\n",
    "                        if nextRowCount > 20:\n",
    "                            break\n",
    "                if endReached:\n",
    "                    break\n",
    "                            \n",
    "                matrix.append(row[tableStarts['col']:])\n",
    "            header = self.sortHeader(matrix, superfields)\n",
    "            # check if there's a totals row - which is most likely\n",
    "            table = pd.DataFrame(matrix[4:], columns=header) if superfields else pd.DataFrame(matrix[3:], columns=header)\n",
    "            table.dropna(axis=1, how='all')\n",
    "            table.dropna(axis=0, how='all')\n",
    "            dataReturn[sheetName]['table'] = table\n",
    "\n",
    "            return dataReturn\n",
    "            \n",
    "        except Exception as e:\n",
    "            print 'Error parsing sheet: %s' %(e)\n",
    "            \n",
    "        return []\n",
    "    \n",
    "    def sortHeader(self, matrix, superfields):\n",
    "        # will sort out the header\n",
    "        \n",
    "        if superfields:\n",
    "            header = matrix[1]\n",
    "            currentSuperField = ''\n",
    "            for col, field in enumerate(header):\n",
    "                if matrix[0][col]:\n",
    "                    currentSuperField = matrix[0][col]\n",
    "                    field = field + ' | ' + currentSuperField\n",
    "                header[col] = field\n",
    "            return header\n",
    "        return matrix[0]\n",
    "    \n",
    "    def checkIfDate(self, fileName):\n",
    "        # checks if the date is in the inital file name\n",
    "        \n",
    "        head, sep_, tail = fileName.partition('/')\n",
    "        fileName = tail\n",
    "        for sep in ['-', '_']:\n",
    "            for bit in fileName.split(sep):\n",
    "                try:\n",
    "                    date = parse(bit)\n",
    "                    return date.strftime('%Y-%m-%d')\n",
    "                except ValueError:\n",
    "                    pass\n",
    "        return False\n",
    "    \n",
    "    def findDate(self, header):\n",
    "        # finds the published date\n",
    "        \n",
    "        for line in header:\n",
    "            for i, value in enumerate(line):\n",
    "                if value:\n",
    "                    if 'period' in value.lower():\n",
    "                        # next cell is the date\n",
    "                        try:\n",
    "                            date = parse(line[i + 1])\n",
    "                            return date.strftime('%Y-%m-%d')\n",
    "                        except ValueError:\n",
    "                            pass\n",
    "        return ''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "excel = importXLS('May-2017-AE-by-provider-4ZNN7.xls', 'output')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "## Loop through all the files and output the CSVs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from os import listdir\n",
    "\n",
    "for fileName in listdir('inputFiles/'):\n",
    "    excel = importXLS('inputFiles/' + fileName, 'output')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Upload to S3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import os\n",
    "\n",
    "import boto\n",
    "import boto.s3\n",
    "from boto.s3.key import Key\n",
    "\n",
    "\n",
    "bucket_name = \"nhspublicdata\"\n",
    "\n",
    "#\n",
    "# Get keys from env\n",
    "#\n",
    "AWS_SECRET_KEY='xxxx'\n",
    "AWS_ACCESS_KEY='xxx'\n",
    "\n",
    "def upload_to_s3( filename, bucket ) :\n",
    "    c = boto.connect_s3(AWS_ACCESS_KEY, AWS_SECRET_KEY)\n",
    "    b = c.get_bucket( bucket_name )\n",
    "\n",
    "    k = Key(b)\n",
    "    k.key = os.path.basename( filename )\n",
    "    k.set_contents_from_filename( filename )\n",
    "\n",
    "\n",
    "if __name__ == \"__main__\" :\n",
    "    for fileName in os.listdir('output'):\n",
    "        upload_to_s3( fileName, bucket_name )\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}