{
 "metadata": {
  "name": "",
  "signature": "sha256:27f24f5649f5610137701fb535f6426ed3dbae9b155bbac2ab73d6e54b59d85a"
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import shutil\n",
      "import pandas as pd\n",
      "\n",
      "pj = '/Users/danielmsheehan/Dropbox/data/'"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 2
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df = pd.read_csv(pj+'output/all/nycb2010_taxi_2013_stats_bldg_cnt_pctcbbldg.csv', dtype={'geoid':str})\n",
      "      \n",
      "print df.dtypes \n",
      "\n",
      "df['geoid'] = \"'\" + df.geoid.map(str) + \"'\" \n",
      "df.to_csv(pj+'output/all/merge/myjoincsv.csv', index=False, quotechar=\"'\")\n",
      "\n",
      "shutil.copy2(pj+'input/census/nycb2010.dbf', pj+'output/all/merge/myjoinshp.dbf')\n",
      "shutil.copy2(pj+'input/census/nycb2010.prj', pj+'output/all/merge/myjoinshp.prj')\n",
      "shutil.copy2(pj+'input/census/nycb2010.shp', pj+'output/all/merge/myjoinshp.shp')\n",
      "shutil.copy2(pj+'input/census/nycb2010.shp.xml', pj+'output/all/merge/myjoinshp.shp.xml')\n",
      "shutil.copy2(pj+'input/census/nycb2010.shx', pj+'output/all/merge/myjoinshp.shx')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "geoid              object\n",
        "avgbrdist         float64\n",
        "medbrdist         float64\n",
        "stdbrdist         float64\n",
        "count               int64\n",
        "areasqft          float64\n",
        "countbldg         float64\n",
        "bldgareaft        float64\n",
        "pctcbbldg         float64\n",
        "dist_bldg_hght    float64\n",
        "dtype: object\n"
       ]
      }
     ],
     "prompt_number": 20
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "#eric updated this\n",
      "\n",
      "http://gis.stackexchange.com/questions/95746/join-a-csv-file-to-shapefile-using-gdal-ogr\n",
      "    \n",
      "    \n",
      "csvsql --db sqlite:///myjoindb.db --insert myjoincsv.csv \n",
      "ogr2ogr -append -f \"SQLite\" myjoindb.db myjoinshp.shp\n",
      "\n",
      "ogr2ogr -f \"ESRI Shapefile\" -sql \"SELECT csv.*, shp.* FROM myjoinshp shp INNER JOIN myjoincsv csv ON csv.geoid = shp.geoid\" joined_output.shp myjoindb.db\n",
      "\n",
      "\n",
      "#UPDATE ERIC ADDED COMMENT http://gis.stackexchange.com/questions/95746/join-a-csv-file-to-shapefile-using-gdal-ogr\n",
      "\n",
      "#http://gis.stackexchange.com/questions/95746/join-a-csv-file-to-shapefile-using-gdal-ogr\n",
      "\n",
      "\n",
      "#MAYBE TRY THIS TO GET THE GEOID CORRECT\n",
      "\n",
      "ogr2ogr -sql \"select inshape.*, joincsv.* from inshape left join 'joincsv.csv'.joincsv on inshape.GISJOIN = joincsv.GISJOIN\" shape_join.shp inshape.shp\n",
      "\n",
      "ogr2ogr -sql \"select inshape.*, joincsv.* from inshape left join 'joincsv.csv'.joincsv on inshape.geoid = joincsv.geoid\" shape_join.shp inshape.shp\n",
      "\n",
      "##Make filenames super short b/c it precedes with filename.field\n",
      "\n"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "#copy merged shapefile somewhere. \n",
      "shutil.copy2(pj+'output/all/merge/joined_output.dbf',pj+'output/all/nycb2010_taxi_2013_all_stats.dbf')\n",
      "shutil.copy2(pj+'output/all/merge/joined_output.shp',pj+'output/all/nycb2010_taxi_2013_all_stats.shp')\n",
      "shutil.copy2(pj+'output/all/merge/joined_output.shx',pj+'output/all/nycb2010_taxi_2013_all_stats.shx')\n",
      "shutil.copy2(pj+'input/prj/2263.prj',pj+'output/all/nycb2010_taxi_2013_all_stats.prj')\n",
      "\n"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 3
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "#manually zipped the nycb2010_taxi_2013_all_stats.shp files"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    }
   ],
   "metadata": {}
  }
 ]
}