In [1]:

    

import fiona
c = fiona.open("../../data/tl_2013_us_zcta510/tl_2013_us_zcta510.shp","r")


    

In [2]:

    

import pandas as pd


    

In [21]:

    

energy_df = pd.read_excel("../../data/data_upload/EN.2.a.1.EnergyUse_Nonresidential_electricity.xlsx","Sheet1")
energy_df.head(5)


    

    
Out[21]:






  

    

      

      
ZIPCODE
      
Annual Therms (natural gas) 
      
Annual Kilowatts (electricity) used
      
Natural Gas emissions CO2 (Metric tons)
      
Eletricity Emissions CO2 (metric tons)
    
  
  

    

      
0
      
   ZIP
      
 THERM_AN
      
       KWH_AN
      
 NATGAS_CO2
      
 ELEC_CO2
    
    

      
1
      
 94101
      
  5046913
      
     836857.4
      
   26799.11
      
 167.7799
    
    

      
2
      
 94102
      
  6885680
      
 2.237528e+08
      
   36562.96
      
 44859.77
    
    

      
3
      
 94103
      
 20204698
      
 4.068479e+08
      
   107286.9
      
 81568.14
    
    

      
4
      
 94104
      
  1627749
      
 1.747286e+08
      
   8643.347
      
    35031
    
  

5 rows × 5 columns

In [15]:

    

zip_list = []
small_list = list(c)[:10]
for geog in list(c):
    cord = geog['geometry']['coordinates']
    zipcode = geog['properties']['ZCTA5CE10']
    dict1 = {'cord':cord,'zipcode':zipcode}
    zip_list.append(dict1)


    

In [20]:

    

zip_df = pd.DataFrame(zip_list)


    

In [22]:

    

merged_df = pd.merge(zip_df,energy_df,left_on='zipcode',right_on='ZIPCODE')


    

In [23]:

    

merged_df.head(5)


    

    
Out[23]:






  

    

      

      
cord
      
zipcode
      
ZIPCODE
      
Annual Therms (natural gas) 
      
Annual Kilowatts (electricity) used
      
Natural Gas emissions CO2 (Metric tons)
      
Eletricity Emissions CO2 (metric tons)
    
  
  

    

      
0
      
 [[(-122.463581, 37.788806), (-122.459925, 37.7...
      
 94118
      
 94118
      
 3126446
      
 40913271
      
 16601
      
  8203
    
    

      
1
      
 [[(-122.509854, 37.784794), (-122.509808, 37.7...
      
 94121
      
 94121
      
 3126446
      
 40913271
      
 16601
      
  8203
    
    

      
2
      
 [[(-122.506191, 37.764166), (-122.50609, 37.76...
      
 94122
      
 94122
      
 3126446
      
 40913271
      
 16601
      
  8203
    
    

      
3
      
 [[(-122.431874, 37.804245), (-122.431787, 37.8...
      
 94123
      
 94123
      
 2000632
      
 38137908
      
 10623
      
  7646
    
    

      
4
      
 [[(-122.407898, 37.738), (-122.407971, 37.7386...
      
 94124
      
 94124
      
 1803169
      
 63232332
      
  9575
      
 12677
    
  

5 rows × 7 columns

In [ ]: