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')

#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