Data-Extraction--CDE-API-fixed-width.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "01100170109835SE  DFAME Public Charter                     Alameda County Office of EducaAlameda             986    777  1020   769  918    781  776  775  105    735  93     712  78     738  728  728  3           8           10                    210    840  220    844  190    872  852  851  16     885  24     857  23     804  849  845  111    706  114    733  105    736  725  725  21     772  17     754  14     797  774  773  501    772  519    757  483    757  762  762  1           0           0                     218    717  422    707  448    735  720  721  404    748  373    722  407    753  741  742  69     629  77     627  81     629  628  628     \n",
    "01100170112607SH  DEnvision Academy for Arts & Technology  Alameda County Office of EducaAlameda             200    643  221    605  226    669  639  639  123    620  104    568  107    635  608  609  2           8           4                     4           4           3                     0           1           2                     60     657  81     641  88     696  665  666  0           2           2                     8           11     569  11     672            1           9           7                     150    630  167    603  180    672  635  636  26     611  52     669  56     647  642  649  19     524  22     431  29     531  495  498     \n",
    "01100170118489SE  DAspire California College Preparatory AcAlameda County Office of EducaAlameda             158    825  265    790  265    792  802  799  64     833  75     797  82     786  805  803  0           0           0                     2           2           3                     5           5           4                     87     817  181    781  174    790  796  792  0           1           0                     0           0           1                     0           1           1                     87     817  209    775  210    777  790  783  46     774  110    716  106    752  747  741  13     591  18     611  22     494  565  558     \n",
    "```\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from lxml import html as htmlparser\n",
    "from os import makedirs\n",
    "from os.path import basename, exists, join, splitext\n",
    "from shutil import unpack_archive\n",
    "import csv\n",
    "import requests\n",
    "RECORD_LAYOUT_URL = 'http://www.cde.ca.gov/ta/ac/ap/reclayoutApiAvg.asp'\n",
    "RAW_DATA_ZIP_URL = 'http://www3.cde.ca.gov/researchfiles/api/14avgtx.zip'\n",
    "RAW_DATA_DIR = join('data', 'schools', 'raw')\n",
    "RAW_DATA_ZIP_FILENAME = join(RAW_DATA_DIR, basename(RAW_DATA_ZIP_URL))\n",
    "# the text file has the same name as the zip, just different extension\n",
    "RAW_DATA_TXT_FILENAME = splitext(RAW_DATA_ZIP_FILENAME)[0] + '.txt'\n",
    "makedirs(RAW_DATA_DIR, exist_ok=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# save and extract the zip file to the raw directory\n",
    "if not exists(RAW_DATA_ZIP_FILENAME):\n",
    "    resp = requests.get(RAW_DATA_ZIP_URL)\n",
    "    with open(RAW_DATA_ZIP_FILENAME, 'wb') as wf:\n",
    "        wf.write(resp.content)\n",
    "\n",
    "if not exists(RAW_DATA_TXT_FILENAME):\n",
    "    unpack_archive(RAW_DATA_ZIP_FILENAME, extract_dir=RAW_DATA_DIR)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# prepare the extracted datafile and directory\n",
    "EXTRACTED_DATA_DIR = join('data', 'schools', 'extracted')\n",
    "# the extracted file has same basename but a .csv extension\n",
    "EXTRACTED_DATA_FILENAME = join(EXTRACTED_DATA_DIR, '{}.csv'.format(splitext(basename(RAW_DATA_TXT_FILENAME))[0]))\n",
    "makedirs(EXTRACTED_DATA_DIR, exist_ok=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Parsing the record layout\n",
    "\n",
    "Now that we have the data downloaded and ready to unpack and parse, let's visit the [Record Layout for the 3-Year Average API Data File](http://www.cde.ca.gov/ta/ac/ap/reclayoutApiAvg.asp) get the metadata behind the fixed-width columns.\n",
    "\n",
    "Here's a screenshot of the page:\n",
    "\n",
    "![image cde-record-layout-page-table.png](images/schools/cde-record-layout-page-table.png)\n",
    "\n",
    "The HTML is pretty straightforward: we need the second and fourth columns -- __Field Name__ and __Width__\n",
    "\n",
    "```html\n",
    "<table width=\"95%\" border=\"1\" cellpadding=\"3\" cellspacing=\"0\">\n",
    "                <tbody><tr align=\"center\" valign=\"middle\">\n",
    "                  <th nowrap=\"\"><div align=\"center\">Field \n",
    "                  #</div></th>\n",
    "                  <th nowrap=\"\"><div align=\"center\">\n",
    "                    <p>Field Name </p>\n",
    "                  </div></th>\n",
    "                  <th nowrap=\"\"><div align=\"center\">Type</div></th>\n",
    "                  <th nowrap=\"\"><div align=\"center\">Width</div></th>\n",
    "                  <th nowrap=\"\"><div align=\"center\">Description</div></th>\n",
    "                </tr>\n",
    "                <tr> \n",
    "                  <td width=\"6%\" align=\"left\" valign=\"top\"> <div align=\"center\">1 \n",
    "                  </div></td>\n",
    "                  <td width=\"13%\" align=\"left\" valign=\"top\">CDS </td>\n",
    "                  <td width=\"7%\" align=\"left\" valign=\"top\">Character </td>\n",
    "                  <td width=\"6%\" align=\"left\" valign=\"top\"> <div align=\"center\">14 \n",
    "                  </div></td>\n",
    "                  <td valign=\"top\" width=\"68%\">County/District/School code </td>\n",
    "                </tr>\n",
    "</tbody></table>\n",
    "```\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# parse the Record Layout webpage\n",
    "resp = requests.get(RECORD_LAYOUT_URL)\n",
    "htmldoc = htmlparser.fromstring(resp.text)\n",
    "# whatever\n",
    "xa, xb = 0,0  # these represent the field boundaries\n",
    "rows = htmldoc.xpath('//tr[td]')\n",
    "field_defs = [tr.xpath('(td[2] | td[4]/div)/text()') for tr in rows]\n",
    "for i, (fieldname, fieldlength) in enumerate(field_defs):\n",
    "    xb = xa + int(fieldlength)\n",
    "    field_defs[i] = (fieldname.strip(), (xa, xb))\n",
    "    xa = xb\n",
    "    \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "wf = open(EXTRACTED_DATA_FILENAME, 'w')\n",
    "cf = csv.DictWriter(wf, fieldnames=[fd[0] for fd in field_defs])\n",
    "cf.writeheader()\n",
    "with open(RAW_DATA_TXT_FILENAME, 'r', encoding='ISO-8859-2') as rf:\n",
    "    for line in rf:\n",
    "        cf.writerow({fieldname: line[x1:x2].strip() for fieldname, (x1, x2) in field_defs})\n",
    "wf.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "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"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}