location_information/generate_location_files.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Overview\n",
    "# This notebook contains code to generate output files from a series of spreadsheets\n",
    "# The overall problem:\n",
    "#\n",
    "# input data contains a data measurement, such as rate, by location, for a particular health condition\n",
    "# so a file named condition_a.csv would have a series of rows\n",
    "# location, rate\n",
    "# location_a, 0.4\n",
    "# location_b, 0.5\n",
    "# ...\n",
    "#\n",
    "# the goal is to output a series of files for each location, with a row containing the condition and health\n",
    "# for example location_a.csv would have a series of rows\n",
    "# condition_a, 0.4\n",
    "# condition_b, 0.6\n",
    "# ...\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import glob\n",
    "import pandas as pd\n",
    "\n",
    "# read all condition files from the input data directory\n",
    "input_files = glob.glob('input_data/*.xls')\n",
    "\n",
    "# create a dictionary that contains the name of the condition (extracted from the file name)\n",
    "# and a dataframe holding the condition and the rate\n",
    "idfs = {}\n",
    "for f in input_files:\n",
    "    idfs[f[11:-4]] = pd.read_csv(f, index_col='county')\n",
    "    \n",
    "# get the location_names from the first condition file\n",
    "# note - this assumes that all files have the same list of locations\n",
    "location_names = idfs['condition_a'].index.tolist()\n",
    "\n",
    "# go through each condition name\n",
    "# create a spreadsheet containing each condition and rate\n",
    "# for that location\n",
    "for n in location_names:\n",
    "    conditions = []\n",
    "    vals = []\n",
    "    for k in idfs.keys():\n",
    "        conditions.append(k)\n",
    "        vals.append(idfs[k].loc[n][0])\n",
    "    df = pd.DataFrame.from_dict({'condition':conditions, 'val':vals})\n",
    "    df.to_excel('output_data/' + n + '.xls', index=False)"
   ]
  },
  {
   "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}