import shutil
import pandas as pd
pj = '/Users/danielmsheehan/Dropbox/data/'
df = pd.read_csv(pj+'output/all/nycb2010_taxi_2013_stats_bldg_cnt_pctcbbldg.csv', dtype={'geoid':str})
print df.dtypes
df['geoid'] = "'" + df.geoid.map(str) + "'"
df.to_csv(pj+'output/all/merge/myjoincsv.csv', index=False, quotechar="'")
shutil.copy2(pj+'input/census/nycb2010.dbf', pj+'output/all/merge/myjoinshp.dbf')
shutil.copy2(pj+'input/census/nycb2010.prj', pj+'output/all/merge/myjoinshp.prj')
shutil.copy2(pj+'input/census/nycb2010.shp', pj+'output/all/merge/myjoinshp.shp')
shutil.copy2(pj+'input/census/nycb2010.shp.xml', pj+'output/all/merge/myjoinshp.shp.xml')
shutil.copy2(pj+'input/census/nycb2010.shx', pj+'output/all/merge/myjoinshp.shx')
geoid object avgbrdist float64 medbrdist float64 stdbrdist float64 count int64 areasqft float64 countbldg float64 bldgareaft float64 pctcbbldg float64 dist_bldg_hght float64 dtype: object
#eric updated this
http://gis.stackexchange.com/questions/95746/join-a-csv-file-to-shapefile-using-gdal-ogr
csvsql --db sqlite:///myjoindb.db --insert myjoincsv.csv
ogr2ogr -append -f "SQLite" myjoindb.db myjoinshp.shp
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
#UPDATE ERIC ADDED COMMENT http://gis.stackexchange.com/questions/95746/join-a-csv-file-to-shapefile-using-gdal-ogr
#http://gis.stackexchange.com/questions/95746/join-a-csv-file-to-shapefile-using-gdal-ogr
#MAYBE TRY THIS TO GET THE GEOID CORRECT
ogr2ogr -sql "select inshape.*, joincsv.* from inshape left join 'joincsv.csv'.joincsv on inshape.GISJOIN = joincsv.GISJOIN" shape_join.shp inshape.shp
ogr2ogr -sql "select inshape.*, joincsv.* from inshape left join 'joincsv.csv'.joincsv on inshape.geoid = joincsv.geoid" shape_join.shp inshape.shp
##Make filenames super short b/c it precedes with filename.field
#copy merged shapefile somewhere.
shutil.copy2(pj+'output/all/merge/joined_output.dbf',pj+'output/all/nycb2010_taxi_2013_all_stats.dbf')
shutil.copy2(pj+'output/all/merge/joined_output.shp',pj+'output/all/nycb2010_taxi_2013_all_stats.shp')
shutil.copy2(pj+'output/all/merge/joined_output.shx',pj+'output/all/nycb2010_taxi_2013_all_stats.shx')
shutil.copy2(pj+'input/prj/2263.prj',pj+'output/all/nycb2010_taxi_2013_all_stats.prj')
#manually zipped the nycb2010_taxi_2013_all_stats.shp files