notebooks/CSV to GeoJSON with Indicator Attributes.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# CSV to GeoJSON with Indicator Attributes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This Jupyter Notebook will walk you through the process of converting a CSV file (exported and cleaned or otherwise transformed from your DevInfo database) into the GeoJSON with full attributes and geometry.\n",
    "\n",
    "To export the resulting GeoJSON files to a File Geodatabase, follow the instructions in the **Export GeoJSON to File Geodatabase** notebook.\n",
    "\n",
    "### **Important**\n",
    "\n",
    "You need to have previously run the **Export DevInfo Access Database Shapefiles to GeoJSON** notebook **before** running this notebook as it will need a folder location of the individual shapefiles. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Import the needed python libraries\n",
    "install the GDAL pythyon library (ogr import below) by opening the anaconda prompt and using `conda install gdal`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import os\n",
    "import sys\n",
    "import json\n",
    "import csv\n",
    "from osgeo import ogr\n",
    "from IPython.display import display"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Set your working directory\n",
    "example: *C:\\users\\me\\working_directory\\my_country*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "output_base = r'C:\\Users\\adam6475\\devinfo\\tanzania'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Other defaults for outputs"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Location of geojson filescreated by running the **Export DevInfo Access Database Shapefiles to GeoJSON** jupyter notebook, see *Important* note above\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "in_geojson_folder = r'C:\\Users\\adam6475\\devinfo\\tanzania\\geojson'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Where do you want to write the output geojson files?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "output_geojson_folder = r'C:\\Users\\adam6475\\devinfo\\tanzania\\geojson_full'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Check for the `Esri Shapefile` driver\n",
    "The DevInfo Access database stores the geometry as a Shapefile. We can use this driver to read that into our script"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "shp_driver_lbl = 'Esri Shapefile'\n",
    "shp_driver = ogr.GetDriverByName(shp_driver_lbl)\n",
    "if shp_driver is None:\n",
    "    print ('{} driver not available.'.format(shp_driver_lbl))\n",
    "else:\n",
    "    print ('{} driver IS available.'.format(shp_driver_lbl))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Specify a CSV to use as your input.\n",
    "\n",
    "This can be the output of the **Export All Data to Single CSV** notebook."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get the path to your CSV file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "csv_file_path = r'C:\\Users\\adam6475\\devinfo\\tanzania\\tz_out_new.csv'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Parse the CSV file and store the rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# set encoding \n",
    "# when reading some CSV files, there may be encoding issues that result in unicode characters appearing in the field names\n",
    "# if you have any unexpected behavior that is related to this, either re-save your file with \"utf-8\" encoding\n",
    "# or try using 'utf-8-sig' as your encoding value below\n",
    "# Stack Overflow reference: https://stackoverflow.com/questions/17912307/u-ufeff-in-python-string/17912811#17912811\n",
    "encoding = 'utf-8'\n",
    "with open(csv_file_path, encoding=encoding) as file:\n",
    "    reader = csv.DictReader(file)\n",
    "    rows = list(reader)\n",
    "#     reader = csv.reader(file)\n",
    "#     rows = list(reader)\n",
    "\n",
    "print ('done reading rows from CSV.')\n",
    "\n",
    "# print the first row of data to validate that the header rows were successfully parsed w/o encoding issues\n",
    "print (rows[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Chunk up the data\n",
    "Chunk up the data by Indicator. This will let us create one layer per Indicator"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "chunks = {}\n",
    "for row in rows:\n",
    "    \n",
    "    ind_id = row['INDICATOR_ID']\n",
    "    parent_id = row['REF_AREA_PARENT_ID']\n",
    "    \n",
    "    if ind_id not in chunks:\n",
    "        chunks[ind_id] = {}\n",
    "    \n",
    "    if parent_id not in chunks[ind_id]:\n",
    "        chunks[ind_id][parent_id] = {}\n",
    "        chunks[ind_id][parent_id]['rows'] = []\n",
    "    \n",
    "    chunks[ind_id][parent_id]['rows'].append(row)\n",
    "\n",
    "print ('done chunking data by indicator, by parent layer')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Setup fields for the output geojson"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "field_map = [\n",
    "    'INDICATOR_ID',\n",
    "    'INDICATOR',\n",
    "    'OBS_VALUE',\n",
    "    'UNIT',\n",
    "    'REF_AREA_ID',\n",
    "    'REF_AREA',\n",
    "    'REF_AREA_PARENT_ID',\n",
    "    'TIME_PERIOD',\n",
    "    'SUBGROUP',\n",
    "    'SOURCE'\n",
    "]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Join the Attribute Data & Spatial Data\n",
    "Finally, we will step through our data to create individual geojson files for each layer, for each layer."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# store the already reference geometry in memory for faster recall\n",
    "geom_cache = {}\n",
    "\n",
    "for c in chunks:\n",
    "    \n",
    "    # test with just one indicator\n",
    "#     if float(c) != 5:\n",
    "#         continue\n",
    "        \n",
    "    ind = c\n",
    "    lyrs = chunks[c]\n",
    "    \n",
    "    for lyr in lyrs:\n",
    "        \n",
    "        feature_collection = {\n",
    "            'type' : 'FeatureCollection',\n",
    "            'features': []\n",
    "        }\n",
    "    \n",
    "        rows = lyrs[lyr]['rows']\n",
    "        for row in rows:\n",
    "            parent_id = row['REF_AREA_PARENT_ID']\n",
    "            area_id = row['REF_AREA_ID']\n",
    "            \n",
    "            # test with just a few layers\n",
    "#             if parent_id not in ['2', '3','4']:\n",
    "#                 continue\n",
    "            \n",
    "            geom = None\n",
    "            if area_id not in geom_cache:\n",
    "                # look for the already created geojson file\n",
    "                gj_file_path = os.path.abspath(os.path.join(in_geojson_folder, 'parent_{}.geojson'.format(parent_id)))\n",
    "\n",
    "                # check to see if we were able to get the geojson file\n",
    "                # TODO: add logging rather than just printing an exception\n",
    "                gj_file = os.path.isfile(gj_file_path)\n",
    "                if not gj_file:\n",
    "                    print ('{} geojson file not found'.format(gj_file_path))\n",
    "                    continue\n",
    "\n",
    "                fc = None\n",
    "                with open(gj_file_path, 'r') as gj_opened:\n",
    "                    fc = json.load(gj_opened)\n",
    "\n",
    "                feature = None\n",
    "\n",
    "                for f in fc['features']:\n",
    "                    if f['properties']['REF_AREA_ID'] == area_id:\n",
    "                        feature = f\n",
    "\n",
    "                if feature is None:\n",
    "                    print ('unable to get feature for parent layer {} :: where area_id is {}'.format(parent_id, area_id))\n",
    "                    continue\n",
    "\n",
    "                geom = feature['geometry']\n",
    "\n",
    "                #store in cache\n",
    "                geom_cache[area_id] = geom\n",
    "\n",
    "            else:\n",
    "                geom = geom_cache[area_id]\n",
    "    \n",
    "            # setup the new feature\n",
    "            feature = {\n",
    "                'type': 'Feature',\n",
    "                'properties': {},\n",
    "                'geometry': geom\n",
    "            }\n",
    "            \n",
    "            # loop through the field_map variable and add each attribute and its value for the row\n",
    "            for f in field_map:\n",
    "                feature['properties'][f] = row[f]\n",
    "                            \n",
    "            # add the feature to the features array of the current layer\n",
    "            feature_collection['features'].append(feature)\n",
    "        \n",
    "        # create filename for output geojson file\n",
    "        new_ind = str(c)\n",
    "        new_ind_id = new_ind.replace('.', '_')\n",
    "        layer_name = 'indicator_{}_layer_{}.geojson'.format(new_ind_id, parent_id)\n",
    "\n",
    "        # full path for the output geojson file\n",
    "        full_path = os.path.abspath(os.path.join(output_geojson_folder, layer_name))    \n",
    "    \n",
    "        print ('writing {} features to {}'.format(len(feature_collection['features']), layer_name))\n",
    "        with open(full_path, 'w') as file:\n",
    "            file.write(json.dumps(feature_collection))\n",
    "\n",
    "del geom_cache\n",
    "print ('done')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "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.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}