Alan's Benchmarking Report Pages

In [1]:

    

import pickle
import pandas as pd
import numpy as np
from importlib import reload
import sys
from datetime import datetime
import yaml

import matplotlib.pyplot as plt
%matplotlib inline

# bench_util module is in the parent directory, because it will be part of
# the production code.  Add the parent directory to the Path so Python can
# import from there
sys.path.insert(0, '../')   
import bench_util as bu
import graph_util as gu


    

In [2]:

    

# Unpickle the pre-processed DataFrame
df = pickle.load(open('dfu3.pkl', 'rb'))

# Unpickle the raw utility bill DataFrame, which is needed below to make
# the utility function object.
df_raw = pickle.load(open('df_raw.pkl', 'rb'))

df.head()  # the processed data


    

    
Out[2]:







  

    

      

      
site_id
      
service_type
      
cal_year
      
cal_mo
      
item_desc
      
units
      
cost
      
days_served
      
usage
      
fiscal_year
      
fiscal_mo
      
mmbtu
    
  
  

    

      
0
      
03
      
Electricity
      
2005
      
12
      
Electricity charge
      
kWh
      
1904.657880
      
49.5
      
14790.748577
      
2006
      
6
      
50.466034
    
    

      
1
      
03
      
Electricity
      
2006
      
1
      
Electricity charge
      
kWh
      
5430.493797
      
93.0
      
42665.790911
      
2006
      
7
      
145.575679
    
    

      
2
      
03
      
Electricity
      
2006
      
2
      
Electricity charge
      
kWh
      
5764.406730
      
84.0
      
45010.439348
      
2006
      
8
      
153.575619
    
    

      
3
      
03
      
Electricity
      
2006
      
3
      
Electricity charge
      
kWh
      
6349.255299
      
93.0
      
46311.547557
      
2006
      
9
      
158.015000
    
    

      
4
      
03
      
Electricity
      
2006
      
4
      
Electricity charge
      
kWh
      
5529.385224
      
90.0
      
40392.812893
      
2006
      
10
      
137.820278
    
  

In [3]:

    

# this is only needed to update any code changes I may have made
# since last importing the module above.
reload(bu)  
reload(gu)

# Make an object that has the various utility functions.
# The object needs access to the raw utility bill DataFrame and the spreadsheet
# containing other application data.
ut = bu.Util(df_raw, '../data/Other_Building_Data.xlsx')
pickle.dump(ut, open('util_obj.pkl', 'wb'))


    

In [4]:

    

new_ut = pickle.load(open('util_obj.pkl', 'rb'))
new_ut.building_info('ANSBG1')


    

    
Out[4]:





{'acct_elec': '31850',
 'acct_nat_gas': '10282  (2408 DAVIS)',
 'acct_oil': '25465',
 'acct_refuse': '',
 'acct_sewer': '1311001100',
 'acct_steam': '',
 'acct_water': '1311001100',
 'address': '2408 Davis Road',
 'city': 'Fairbanks',
 'dd_site': 'PAFA',
 'onsite_gen': nan,
 'primary_func': 'Animal Shelter',
 'site_category': 'Other',
 'site_id': 'ANSBG1',
 'site_name': 'FNSB-Animal Control',
 'source_elec': 'Golden Valley Electric',
 'source_nat_gas': 'Fairbanks Natural Gas',
 'source_oil': 'Sourdough Fuel  (Petro Star)',
 'source_refuse': '',
 'source_sewer': 'Golden Heart Utilities',
 'source_steam': '',
 'source_water': 'Golden Heart Utilities',
 'sq_ft': 6718.0,
 'year_built': nan}

In [60]:

    

df_raw.head()


    

    
Out[60]:







  

    

      

      
Site ID
      
Site Name
      
Vendor Code
      
Vendor Name
      
Account Number
      
Bill Date
      
Due Date
      
Entry Date
      
Invoice #
      
Voucher #
      
From
      
Thru
      
Service Name
      
Item Description
      
Meter Number
      
Usage
      
Cost
      
Units
      
Account Financial Code
      
Site Financial Code
    
  
  

    

      
0
      
TRGR
      
FNSB-Transit Garage
      
VP287678
      
Sourdough Fuel  (Petro Star)
      
00013297 (closed)
      
09/28/2010
      
09/28/2010
      
01/26/2011
      
NaN
      
NaN
      
2008-11-19
      
2010-09-28
      
Oil #1
      
FED LUS TX
      
NaN
      
NaN
      
3.00
      
NaN
      
NaN
      
NaN
    
    

      
1
      
TRGR
      
FNSB-Transit Garage
      
VP287678
      
Sourdough Fuel  (Petro Star)
      
00013297 (closed)
      
09/28/2010
      
09/28/2010
      
01/26/2011
      
NaN
      
NaN
      
2008-11-19
      
2010-09-28
      
Oil #1
      
Fuel Oil #1 (Gallons)
      
NaN
      
3000.0
      
7950.00
      
Gallons
      
NaN
      
NaN
    
    

      
2
      
TRGR
      
FNSB-Transit Garage
      
VP287678
      
Sourdough Fuel  (Petro Star)
      
00013297 (closed)
      
09/30/2010
      
09/30/2010
      
01/26/2011
      
NaN
      
NaN
      
2010-09-28
      
2010-09-30
      
Oil #1
      
FED LUS TX
      
NaN
      
NaN
      
1.31
      
NaN
      
NaN
      
NaN
    
    

      
3
      
TRGR
      
FNSB-Transit Garage
      
VP287678
      
Sourdough Fuel  (Petro Star)
      
00013297 (closed)
      
09/30/2010
      
09/30/2010
      
01/26/2011
      
NaN
      
NaN
      
2010-09-28
      
2010-09-30
      
Oil #1
      
Fuel Oil #1 (Gallons)
      
NaN
      
1307.0
      
3463.82
      
Gallons
      
NaN
      
NaN
    
    

      
4
      
TRGR
      
FNSB-Transit Garage
      
VP287678
      
Sourdough Fuel  (Petro Star)
      
00013297 (closed)
      
01/14/2011
      
01/14/2011
      
07/28/2014
      
NaN
      
NaN
      
2010-09-30
      
2011-01-14
      
Oil #1
      
Fuel Oil #1 (Gallons)
      
NaN
      
1880.0
      
5545.41
      
Gallons
      
NaN
      
NaN
    
  

In [68]:

    

# df_raw[(df_raw['Site ID']=='05') & (df_raw.Usage > 0) & (df_raw.Units == 'kW')].to_csv('test.csv')


    

In [5]:

    

site = 'ANSBG1'


    

In [6]:

    

template_data = {}


    

In [7]:

    

# This function returns all the needed info for the report, except
# the date updated
info = ut.building_info(site)
info


    

    
Out[7]:





{'acct_elec': '31850',
 'acct_nat_gas': '10282  (2408 DAVIS)',
 'acct_oil': '25465',
 'acct_refuse': '',
 'acct_sewer': '1311001100',
 'acct_steam': '',
 'acct_water': '1311001100',
 'address': '2408 Davis Road',
 'city': 'Fairbanks',
 'dd_site': 'PAFA',
 'onsite_gen': nan,
 'primary_func': 'Animal Shelter',
 'site_category': 'Other',
 'site_id': 'ANSBG1',
 'site_name': 'FNSB-Animal Control',
 'source_elec': 'Golden Valley Electric',
 'source_nat_gas': 'Fairbanks Natural Gas',
 'source_oil': 'Sourdough Fuel  (Petro Star)',
 'source_refuse': '',
 'source_sewer': 'Golden Heart Utilities',
 'source_steam': '',
 'source_water': 'Golden Heart Utilities',
 'sq_ft': 6718.0,
 'year_built': nan}

In [8]:

    

template_data['building_info'] = {
    'date_updated': datetime.now().strftime('%B %d, %Y %I:%M %p'),
    'bldg': info
}
template_data


    

    
Out[8]:





{'building_info': {'bldg': {'acct_elec': '31850',
   'acct_nat_gas': '10282  (2408 DAVIS)',
   'acct_oil': '25465',
   'acct_refuse': '',
   'acct_sewer': '1311001100',
   'acct_steam': '',
   'acct_water': '1311001100',
   'address': '2408 Davis Road',
   'city': 'Fairbanks',
   'dd_site': 'PAFA',
   'onsite_gen': nan,
   'primary_func': 'Animal Shelter',
   'site_category': 'Other',
   'site_id': 'ANSBG1',
   'site_name': 'FNSB-Animal Control',
   'source_elec': 'Golden Valley Electric',
   'source_nat_gas': 'Fairbanks Natural Gas',
   'source_oil': 'Sourdough Fuel  (Petro Star)',
   'source_refuse': '',
   'source_sewer': 'Golden Heart Utilities',
   'source_steam': '',
   'source_water': 'Golden Heart Utilities',
   'sq_ft': 6718.0,
   'year_built': nan},
  'date_updated': 'October 17, 2017 03:15 PM'}}

In [9]:

    

# Need to have this constant at the top of the script in order to know what
# Fiscal Year to use in the Report.
FISCAL_YEAR_FOR_ONE_YEAR_REPORT = 2016


    

In [10]:

    

# Determine month count by year for Electricity to determine the latest
# complete year.
electric_only = df.query("service_type == 'Electricity'")
electric_months_present = bu.months_present(electric_only)
electric_mo_count = bu.month_count(electric_months_present)
last_complete_year = max(electric_mo_count[electric_mo_count==12].index)
last_complete_year


    

    
Out[10]:





2017

In [11]:

    

# Filter down to just this site's bills and only services that
# are energy services.
energy_services = bu.missing_energy_services([])
df1 = df.query('site_id==@site and service_type==@energy_services')
print(df1.site_id.unique(), df1.service_type.unique())  # check to see if it worked
df1.head()


    

    




['ANSBG1'] ['Electricity' 'Natural Gas' 'Oil #1']






    
Out[11]:







  

    

      

      
site_id
      
service_type
      
cal_year
      
cal_mo
      
item_desc
      
units
      
cost
      
days_served
      
usage
      
fiscal_year
      
fiscal_mo
      
mmbtu
    
  
  

    

      
30353
      
ANSBG1
      
Electricity
      
2006
      
6
      
Energy charge
      
kWh
      
739.280
      
16.5
      
7245.0
      
2006
      
12
      
24.71994
    
    

      
30354
      
ANSBG1
      
Electricity
      
2006
      
6
      
KW Charge
      
kW
      
0.000
      
16.5
      
14.5
      
2006
      
12
      
0.00000
    
    

      
30355
      
ANSBG1
      
Electricity
      
2006
      
6
      
Other Charge
      
-
      
183.065
      
16.5
      
NaN
      
2006
      
12
      
NaN
    
    

      
30356
      
ANSBG1
      
Electricity
      
2006
      
7
      
Energy charge
      
kWh
      
1414.275
      
31.0
      
13860.0
      
2007
      
1
      
47.29032
    
    

      
30357
      
ANSBG1
      
Electricity
      
2006
      
7
      
KW Charge
      
kW
      
0.000
      
31.0
      
29.5
      
2007
      
1
      
0.00000
    
  

In [12]:

    

# Sum Energy Costs and Usage
df2 = pd.pivot_table(df1, index='fiscal_year', values=['cost', 'mmbtu'], aggfunc=np.sum)
df2


    

    
Out[12]:







  

    

      

      
cost
      
mmbtu
    
    

      
fiscal_year
      

      

    
  
  

    

      
2006
      
922.345000
      
24.719940
    
    

      
2007
      
24319.055469
      
630.537600
    
    

      
2008
      
33274.530938
      
708.924675
    
    

      
2009
      
134796.852897
      
5393.548739
    
    

      
2010
      
132430.496387
      
5057.719531
    
    

      
2011
      
152615.091719
      
5867.180376
    
    

      
2012
      
136484.940212
      
4866.620224
    
    

      
2013
      
116931.780788
      
4146.071185
    
    

      
2014
      
84810.964736
      
2780.230563
    
    

      
2015
      
85146.096425
      
2747.412607
    
    

      
2016
      
78038.300113
      
2746.880039
    
    

      
2017
      
96117.046896
      
2992.645327
    
    

      
2018
      
11036.048421
      
300.104750
    
  

In [13]:

    

# Add a column showing number of months present in each fiscal year.
bu.add_month_count_column(df2, df1)
df2


    

    
Out[13]:







  

    

      

      
cost
      
mmbtu
      
month_count
    
    

      
fiscal_year
      

      

      

    
  
  

    

      
2006
      
922.345000
      
24.719940
      
1
    
    

      
2007
      
24319.055469
      
630.537600
      
12
    
    

      
2008
      
33274.530938
      
708.924675
      
12
    
    

      
2009
      
134796.852897
      
5393.548739
      
12
    
    

      
2010
      
132430.496387
      
5057.719531
      
12
    
    

      
2011
      
152615.091719
      
5867.180376
      
12
    
    

      
2012
      
136484.940212
      
4866.620224
      
12
    
    

      
2013
      
116931.780788
      
4146.071185
      
12
    
    

      
2014
      
84810.964736
      
2780.230563
      
12
    
    

      
2015
      
85146.096425
      
2747.412607
      
12
    
    

      
2016
      
78038.300113
      
2746.880039
      
12
    
    

      
2017
      
96117.046896
      
2992.645327
      
12
    
    

      
2018
      
11036.048421
      
300.104750
      
3
    
  

In [14]:

    

# Make a column with just the Heat MMBtu
df2['heat_mmbtu'] = df2.mmbtu - df1.query("service_type=='Electricity'").groupby('fiscal_year').sum()['mmbtu']
df2


    

    
Out[14]:







  

    

      

      
cost
      
mmbtu
      
month_count
      
heat_mmbtu
    
    

      
fiscal_year
      

      

      

      

    
  
  

    

      
2006
      
922.345000
      
24.719940
      
1
      
0.000000
    
    

      
2007
      
24319.055469
      
630.537600
      
12
      
0.000000
    
    

      
2008
      
33274.530938
      
708.924675
      
12
      
0.000000
    
    

      
2009
      
134796.852897
      
5393.548739
      
12
      
4736.762100
    
    

      
2010
      
132430.496387
      
5057.719531
      
12
      
4416.021590
    
    

      
2011
      
152615.091719
      
5867.180376
      
12
      
5203.093510
    
    

      
2012
      
136484.940212
      
4866.620224
      
12
      
4183.582316
    
    

      
2013
      
116931.780788
      
4146.071185
      
12
      
3545.521645
    
    

      
2014
      
84810.964736
      
2780.230563
      
12
      
2163.879000
    
    

      
2015
      
85146.096425
      
2747.412607
      
12
      
2082.877839
    
    

      
2016
      
78038.300113
      
2746.880039
      
12
      
2151.683625
    
    

      
2017
      
96117.046896
      
2992.645327
      
12
      
2112.299698
    
    

      
2018
      
11036.048421
      
300.104750
      
3
      
186.531677
    
  

In [15]:

    

# Add in degree days to DataFrame
months_present = bu.months_present(df1)
deg_days = ut.degree_days_yearly(months_present, site)
df2['hdd'] = deg_days
df2


    

    
Out[15]:







  

    

      

      
cost
      
mmbtu
      
month_count
      
heat_mmbtu
      
hdd
    
    

      
fiscal_year
      

      

      

      

      

    
  
  

    

      
2006
      
922.345000
      
24.719940
      
1
      
0.000000
      
206
    
    

      
2007
      
24319.055469
      
630.537600
      
12
      
0.000000
      
14075
    
    

      
2008
      
33274.530938
      
708.924675
      
12
      
0.000000
      
13382
    
    

      
2009
      
134796.852897
      
5393.548739
      
12
      
4736.762100
      
14471
    
    

      
2010
      
132430.496387
      
5057.719531
      
12
      
4416.021590
      
13119
    
    

      
2011
      
152615.091719
      
5867.180376
      
12
      
5203.093510
      
13667
    
    

      
2012
      
136484.940212
      
4866.620224
      
12
      
4183.582316
      
13960
    
    

      
2013
      
116931.780788
      
4146.071185
      
12
      
3545.521645
      
14939
    
    

      
2014
      
84810.964736
      
2780.230563
      
12
      
2163.879000
      
12531
    
    

      
2015
      
85146.096425
      
2747.412607
      
12
      
2082.877839
      
12345
    
    

      
2016
      
78038.300113
      
2746.880039
      
12
      
2151.683625
      
12017
    
    

      
2017
      
96117.046896
      
2992.645327
      
12
      
2112.299698
      
13802
    
    

      
2018
      
11036.048421
      
300.104750
      
3
      
186.531677
      
858
    
  

In [16]:

    

# Get building square footage and calculate EUIs and ECI.
sq_ft = ut.building_info(site)['sq_ft']
df2['eui'] = df2.mmbtu * 1e3 / sq_ft
df2['eci'] = df2.cost / sq_ft
df2['specific_eui'] = df2.heat_mmbtu * 1e6 / df2.hdd / sq_ft
df2


    

    
Out[16]:







  

    

      

      
cost
      
mmbtu
      
month_count
      
heat_mmbtu
      
hdd
      
eui
      
eci
      
specific_eui
    
    

      
fiscal_year
      

      

      

      

      

      

      

      

    
  
  

    

      
2006
      
922.345000
      
24.719940
      
1
      
0.000000
      
206
      
3.679658
      
0.137295
      
0.000000
    
    

      
2007
      
24319.055469
      
630.537600
      
12
      
0.000000
      
14075
      
93.857934
      
3.619984
      
0.000000
    
    

      
2008
      
33274.530938
      
708.924675
      
12
      
0.000000
      
13382
      
105.526150
      
4.953041
      
0.000000
    
    

      
2009
      
134796.852897
      
5393.548739
      
12
      
4736.762100
      
14471
      
802.850363
      
20.065027
      
48.724011
    
    

      
2010
      
132430.496387
      
5057.719531
      
12
      
4416.021590
      
13119
      
752.860901
      
19.712786
      
50.106083
    
    

      
2011
      
152615.091719
      
5867.180376
      
12
      
5203.093510
      
13667
      
873.352244
      
22.717340
      
56.669377
    
    

      
2012
      
136484.940212
      
4866.620224
      
12
      
4183.582316
      
13960
      
724.415038
      
20.316305
      
44.609042
    
    

      
2013
      
116931.780788
      
4146.071185
      
12
      
3545.521645
      
14939
      
617.158557
      
17.405743
      
35.327964
    
    

      
2014
      
84810.964736
      
2780.230563
      
12
      
2163.879000
      
12531
      
413.847955
      
12.624437
      
25.704386
    
    

      
2015
      
85146.096425
      
2747.412607
      
12
      
2082.877839
      
12345
      
408.962877
      
12.674322
      
25.114972
    
    

      
2016
      
78038.300113
      
2746.880039
      
12
      
2151.683625
      
12017
      
408.883602
      
11.616300
      
26.652770
    
    

      
2017
      
96117.046896
      
2992.645327
      
12
      
2112.299698
      
13802
      
445.466705
      
14.307390
      
22.781038
    
    

      
2018
      
11036.048421
      
300.104750
      
3
      
186.531677
      
858
      
44.671740
      
1.642758
      
32.361251
    
  

In [17]:

    

# Restrict to full years
df2 = df2.query("month_count == 12")
df2


    

    
Out[17]:







  

    

      

      
cost
      
mmbtu
      
month_count
      
heat_mmbtu
      
hdd
      
eui
      
eci
      
specific_eui
    
    

      
fiscal_year
      

      

      

      

      

      

      

      

    
  
  

    

      
2007
      
24319.055469
      
630.537600
      
12
      
0.000000
      
14075
      
93.857934
      
3.619984
      
0.000000
    
    

      
2008
      
33274.530938
      
708.924675
      
12
      
0.000000
      
13382
      
105.526150
      
4.953041
      
0.000000
    
    

      
2009
      
134796.852897
      
5393.548739
      
12
      
4736.762100
      
14471
      
802.850363
      
20.065027
      
48.724011
    
    

      
2010
      
132430.496387
      
5057.719531
      
12
      
4416.021590
      
13119
      
752.860901
      
19.712786
      
50.106083
    
    

      
2011
      
152615.091719
      
5867.180376
      
12
      
5203.093510
      
13667
      
873.352244
      
22.717340
      
56.669377
    
    

      
2012
      
136484.940212
      
4866.620224
      
12
      
4183.582316
      
13960
      
724.415038
      
20.316305
      
44.609042
    
    

      
2013
      
116931.780788
      
4146.071185
      
12
      
3545.521645
      
14939
      
617.158557
      
17.405743
      
35.327964
    
    

      
2014
      
84810.964736
      
2780.230563
      
12
      
2163.879000
      
12531
      
413.847955
      
12.624437
      
25.704386
    
    

      
2015
      
85146.096425
      
2747.412607
      
12
      
2082.877839
      
12345
      
408.962877
      
12.674322
      
25.114972
    
    

      
2016
      
78038.300113
      
2746.880039
      
12
      
2151.683625
      
12017
      
408.883602
      
11.616300
      
26.652770
    
    

      
2017
      
96117.046896
      
2992.645327
      
12
      
2112.299698
      
13802
      
445.466705
      
14.307390
      
22.781038
    
  

In [18]:

    

# get the rows as a list of dictionaries and put into
# final template data dictionary.
template_data['energy_index_comparison'] = {
    'yearly_table': {'rows': bu.df_to_dictionaries(df2)}
}
template_data


    

    
Out[18]:





{'building_info': {'bldg': {'acct_elec': '31850',
   'acct_nat_gas': '10282  (2408 DAVIS)',
   'acct_oil': '25465',
   'acct_refuse': '',
   'acct_sewer': '1311001100',
   'acct_steam': '',
   'acct_water': '1311001100',
   'address': '2408 Davis Road',
   'city': 'Fairbanks',
   'dd_site': 'PAFA',
   'onsite_gen': nan,
   'primary_func': 'Animal Shelter',
   'site_category': 'Other',
   'site_id': 'ANSBG1',
   'site_name': 'FNSB-Animal Control',
   'source_elec': 'Golden Valley Electric',
   'source_nat_gas': 'Fairbanks Natural Gas',
   'source_oil': 'Sourdough Fuel  (Petro Star)',
   'source_refuse': '',
   'source_sewer': 'Golden Heart Utilities',
   'source_steam': '',
   'source_water': 'Golden Heart Utilities',
   'sq_ft': 6718.0,
   'year_built': nan},
  'date_updated': 'October 17, 2017 03:15 PM'},
 'energy_index_comparison': {'yearly_table': {'rows': [{'cost': 24319.055468749997,
     'eci': 3.6199844401235484,
     'eui': 93.857933908901472,
     'fiscal_year': 2007,
     'hdd': 14075.0,
     'heat_mmbtu': 0.0,
     'mmbtu': 630.53760000000011,
     'month_count': 12.0,
     'specific_eui': 0.0},
    {'cost': 33274.530937500007,
     'eci': 4.9530412232063128,
     'eui': 105.52614985858885,
     'fiscal_year': 2008,
     'hdd': 13382.0,
     'heat_mmbtu': 0.0,
     'mmbtu': 708.92467474999989,
     'month_count': 12.0,
     'specific_eui': 0.0},
    {'cost': 134796.8528967803,
     'eci': 20.065027224885426,
     'eui': 802.85036311875831,
     'fiscal_year': 2009,
     'hdd': 14471.0,
     'heat_mmbtu': 4736.7620999999999,
     'mmbtu': 5393.5487394318179,
     'month_count': 12.0,
     'specific_eui': 48.724010729983647},
    {'cost': 132430.49638662484,
     'eci': 19.712786005749457,
     'eui': 752.86090078495897,
     'fiscal_year': 2010,
     'hdd': 13119.0,
     'heat_mmbtu': 4416.0215896551726,
     'mmbtu': 5057.7195314733544,
     'month_count': 12.0,
     'specific_eui': 50.106083337300866},
    {'cost': 152615.09171943573,
     'eci': 22.717340238082127,
     'eui': 873.35224409040973,
     'fiscal_year': 2011,
     'hdd': 13667.0,
     'heat_mmbtu': 5203.0935103448273,
     'mmbtu': 5867.180375799373,
     'month_count': 12.0,
     'specific_eui': 56.669376869424966},
    {'cost': 136484.94021187679,
     'eci': 20.316305479588685,
     'eui': 724.41503787205818,
     'fiscal_year': 2012,
     'hdd': 13960.0,
     'heat_mmbtu': 4183.5823161290327,
     'mmbtu': 4866.6202244244869,
     'month_count': 12.0,
     'specific_eui': 44.609042423436598},
    {'cost': 116931.78078837601,
     'eci': 17.405742897942247,
     'eui': 617.1585567899283,
     'fiscal_year': 2013,
     'hdd': 14939.0,
     'heat_mmbtu': 3545.5216451612896,
     'mmbtu': 4146.071184514738,
     'month_count': 12.0,
     'specific_eui': 35.327964417222773},
    {'cost': 84810.964735817572,
     'eci': 12.624436548945754,
     'eui': 413.84795517959998,
     'fiscal_year': 2014,
     'hdd': 12531.0,
     'heat_mmbtu': 2163.8790000000008,
     'mmbtu': 2780.2305628965523,
     'month_count': 12.0,
     'specific_eui': 25.704386494521287},
    {'cost': 85146.096424731208,
     'eci': 12.67432218290134,
     'eui': 408.9628768546707,
     'fiscal_year': 2015,
     'hdd': 12345.0,
     'heat_mmbtu': 2082.8778387096777,
     'mmbtu': 2747.4126067096777,
     'month_count': 12.0,
     'specific_eui': 25.114972412420443},
    {'cost': 78038.30011287269,
     'eci': 11.616299510698525,
     'eui': 408.88360214078864,
     'fiscal_year': 2016,
     'hdd': 12017.0,
     'heat_mmbtu': 2151.6836250000001,
     'mmbtu': 2746.8800391818181,
     'month_count': 12.0,
     'specific_eui': 26.652770153961953},
    {'cost': 96117.046895924927,
     'eci': 14.307390130384777,
     'eui': 445.46670536938893,
     'fiscal_year': 2017,
     'hdd': 13802.0,
     'heat_mmbtu': 2112.2996975806454,
     'mmbtu': 2992.6453266715548,
     'month_count': 12.0,
     'specific_eui': 22.781038304511629}]}}}

In [19]:

    

# Filter down to just the records of the targeted fiscal year
df1 = df.query('fiscal_year == @last_complete_year')
df1.head()


    

    
Out[19]:







  

    

      

      
site_id
      
service_type
      
cal_year
      
cal_mo
      
item_desc
      
units
      
cost
      
days_served
      
usage
      
fiscal_year
      
fiscal_mo
      
mmbtu
    
  
  

    

      
158
      
03
      
Electricity
      
2016
      
7
      
Electricity charge
      
-
      
58.940738
      
62.0
      
0.000000
      
2017
      
1
      
0.000000
    
    

      
159
      
03
      
Electricity
      
2016
      
7
      
Electricity charge
      
kWh
      
8469.632126
      
31.0
      
19429.262673
      
2017
      
1
      
66.292644
    
    

      
160
      
03
      
Electricity
      
2016
      
8
      
Electricity charge
      
-
      
15.000000
      
21.0
      
0.000000
      
2017
      
2
      
0.000000
    
    

      
161
      
03
      
Electricity
      
2016
      
8
      
Electricity charge
      
kWh
      
5109.125683
      
72.0
      
23571.373950
      
2017
      
2
      
80.425528
    
    

      
162
      
03
      
Electricity
      
2016
      
9
      
Electricity charge
      
kWh
      
6077.898905
      
90.0
      
30614.394523
      
2017
      
3
      
104.456314
    
  

In [20]:

    

# Get Total Utility cost by building. This includes non-energy utilities as well.
df2 = df1.pivot_table(index='site_id', values=['cost'], aggfunc=np.sum)
df2.columns = ['total_cost']
df2.head()


    

    
Out[20]:







  

    

      

      
total_cost
    
    

      
site_id
      

    
  
  

    

      
03
      
99176.042407
    
    

      
04
      
105988.237749
    
    

      
05
      
546259.617195
    
    

      
06
      
100363.785597
    
    

      
07
      
107438.320632
    
  

In [21]:

    

# Save this into the Final DataFrame that we will build up as we go.
df_final = df2.copy()


    

In [22]:

    

# Get a list of the Energy Services and restrict the data to
# just these services
energy_svcs = bu.missing_energy_services([])
df2 = df1.query('service_type == @energy_svcs')
df2.service_type.unique()


    

    
Out[22]:





array(['Electricity', 'Natural Gas', 'Oil #1', 'Steam'], dtype=object)

In [23]:

    

# Summarize Cost by Service Type
df3 = pd.pivot_table(df2, index='site_id', columns='service_type', values='cost', aggfunc=np.sum)
df3.head()


    

    
Out[23]:







  

    

      
service_type
      
Electricity
      
Natural Gas
      
Oil #1
      
Steam
    
    

      
site_id
      

      

      

      

    
  
  

    

      
03
      
75242.720323
      
517.528982
      
17433.66
      
NaN
    
    

      
04
      
84839.201398
      
1016.329788
      
9308.85
      
NaN
    
    

      
05
      
361988.694070
      
NaN
      
NaN
      
132582.139643
    
    

      
06
      
76683.320968
      
588.228982
      
8094.94
      
NaN
    
    

      
07
      
87312.574743
      
637.478982
      
4187.77
      
NaN
    
  

In [24]:

    

# Change column names
cols = ['{}_cost'.format(bu.change_name(col)) for col in df3.columns]
df3.columns = cols
df3.head()


    

    
Out[24]:







  

    

      

      
electricity_cost
      
natural_gas_cost
      
fuel_oil_cost
      
district_heat_cost
    
    

      
site_id
      

      

      

      

    
  
  

    

      
03
      
75242.720323
      
517.528982
      
17433.66
      
NaN
    
    

      
04
      
84839.201398
      
1016.329788
      
9308.85
      
NaN
    
    

      
05
      
361988.694070
      
NaN
      
NaN
      
132582.139643
    
    

      
06
      
76683.320968
      
588.228982
      
8094.94
      
NaN
    
    

      
07
      
87312.574743
      
637.478982
      
4187.77
      
NaN
    
  

In [25]:

    

# Add a total energy cost column
df3['total_energy_cost'] = df3.sum(axis=1)
df3.head()


    

    
Out[25]:







  

    

      

      
electricity_cost
      
natural_gas_cost
      
fuel_oil_cost
      
district_heat_cost
      
total_energy_cost
    
    

      
site_id
      

      

      

      

      

    
  
  

    

      
03
      
75242.720323
      
517.528982
      
17433.66
      
NaN
      
93193.909304
    
    

      
04
      
84839.201398
      
1016.329788
      
9308.85
      
NaN
      
95164.381186
    
    

      
05
      
361988.694070
      
NaN
      
NaN
      
132582.139643
      
494570.833713
    
    

      
06
      
76683.320968
      
588.228982
      
8094.94
      
NaN
      
85366.489950
    
    

      
07
      
87312.574743
      
637.478982
      
4187.77
      
NaN
      
92137.823725
    
  

In [26]:

    

# Add a total Heat Cost Column
df3['total_heat_cost'] = df3.total_energy_cost.fillna(0.0) - df3.electricity_cost.fillna(0.0)
df3.head()


    

    
Out[26]:







  

    

      

      
electricity_cost
      
natural_gas_cost
      
fuel_oil_cost
      
district_heat_cost
      
total_energy_cost
      
total_heat_cost
    
    

      
site_id
      

      

      

      

      

      

    
  
  

    

      
03
      
75242.720323
      
517.528982
      
17433.66
      
NaN
      
93193.909304
      
17951.188982
    
    

      
04
      
84839.201398
      
1016.329788
      
9308.85
      
NaN
      
95164.381186
      
10325.179788
    
    

      
05
      
361988.694070
      
NaN
      
NaN
      
132582.139643
      
494570.833713
      
132582.139643
    
    

      
06
      
76683.320968
      
588.228982
      
8094.94
      
NaN
      
85366.489950
      
8683.168982
    
    

      
07
      
87312.574743
      
637.478982
      
4187.77
      
NaN
      
92137.823725
      
4825.248982
    
  

In [27]:

    

# Add this to the final DataFrame
df_final = pd.concat([df_final, df3], axis=1)
df_final.head()


    

    
Out[27]:







  

    

      

      
total_cost
      
electricity_cost
      
natural_gas_cost
      
fuel_oil_cost
      
district_heat_cost
      
total_energy_cost
      
total_heat_cost
    
  
  

    

      
03
      
99176.042407
      
75242.720323
      
517.528982
      
17433.66
      
NaN
      
93193.909304
      
17951.188982
    
    

      
04
      
105988.237749
      
84839.201398
      
1016.329788
      
9308.85
      
NaN
      
95164.381186
      
10325.179788
    
    

      
05
      
546259.617195
      
361988.694070
      
NaN
      
NaN
      
132582.139643
      
494570.833713
      
132582.139643
    
    

      
06
      
100363.785597
      
76683.320968
      
588.228982
      
8094.94
      
NaN
      
85366.489950
      
8683.168982
    
    

      
07
      
107438.320632
      
87312.574743
      
637.478982
      
4187.77
      
NaN
      
92137.823725
      
4825.248982
    
  

In [28]:

    

# Summarize MMBtu by Service Type
df3 = pd.pivot_table(df2, index='site_id', columns='service_type', values='mmbtu', aggfunc=np.sum)
df3.head()


    

    
Out[28]:







  

    

      
service_type
      
Electricity
      
Natural Gas
      
Oil #1
      
Steam
    
    

      
site_id
      

      

      

      

    
  
  

    

      
03
      
1007.401282
      
0.000
      
1280.880
      
NaN
    
    

      
04
      
1364.770649
      
23.766
      
681.345
      
NaN
    
    

      
05
      
5576.480071
      
NaN
      
NaN
      
9357.875
    
    

      
06
      
1235.762563
      
0.816
      
985.095
      
NaN
    
    

      
07
      
1379.274312
      
0.306
      
302.670
      
NaN
    
  

In [29]:

    

# Change column names
cols = ['{}_mmbtu'.format(bu.change_name(col)) for col in df3.columns]
df3.columns = cols
df3.head()


    

    
Out[29]:







  

    

      

      
electricity_mmbtu
      
natural_gas_mmbtu
      
fuel_oil_mmbtu
      
district_heat_mmbtu
    
    

      
site_id
      

      

      

      

    
  
  

    

      
03
      
1007.401282
      
0.000
      
1280.880
      
NaN
    
    

      
04
      
1364.770649
      
23.766
      
681.345
      
NaN
    
    

      
05
      
5576.480071
      
NaN
      
NaN
      
9357.875
    
    

      
06
      
1235.762563
      
0.816
      
985.095
      
NaN
    
    

      
07
      
1379.274312
      
0.306
      
302.670
      
NaN
    
  

In [30]:

    

# Add a total mmbtu column
df3['total_mmbtu'] = df3.sum(axis=1)
df3.head()


    

    
Out[30]:







  

    

      

      
electricity_mmbtu
      
natural_gas_mmbtu
      
fuel_oil_mmbtu
      
district_heat_mmbtu
      
total_mmbtu
    
    

      
site_id
      

      

      

      

      

    
  
  

    

      
03
      
1007.401282
      
0.000
      
1280.880
      
NaN
      
2288.281282
    
    

      
04
      
1364.770649
      
23.766
      
681.345
      
NaN
      
2069.881649
    
    

      
05
      
5576.480071
      
NaN
      
NaN
      
9357.875
      
14934.355071
    
    

      
06
      
1235.762563
      
0.816
      
985.095
      
NaN
      
2221.673563
    
    

      
07
      
1379.274312
      
0.306
      
302.670
      
NaN
      
1682.250312
    
  

In [31]:

    

# Add a total Heat mmbtu Column
df3['total_heat_mmbtu'] = df3.total_mmbtu.fillna(0.0) - df3.electricity_mmbtu.fillna(0.0)
df3.head()


    

    
Out[31]:







  

    

      

      
electricity_mmbtu
      
natural_gas_mmbtu
      
fuel_oil_mmbtu
      
district_heat_mmbtu
      
total_mmbtu
      
total_heat_mmbtu
    
    

      
site_id
      

      

      

      

      

      

    
  
  

    

      
03
      
1007.401282
      
0.000
      
1280.880
      
NaN
      
2288.281282
      
1280.880
    
    

      
04
      
1364.770649
      
23.766
      
681.345
      
NaN
      
2069.881649
      
705.111
    
    

      
05
      
5576.480071
      
NaN
      
NaN
      
9357.875
      
14934.355071
      
9357.875
    
    

      
06
      
1235.762563
      
0.816
      
985.095
      
NaN
      
2221.673563
      
985.911
    
    

      
07
      
1379.274312
      
0.306
      
302.670
      
NaN
      
1682.250312
      
302.976
    
  

In [32]:

    

# Add this to the final DataFrame
df_final = pd.concat([df_final, df3], axis=1)
df_final.head()


    

    
Out[32]:







  

    

      

      
total_cost
      
electricity_cost
      
natural_gas_cost
      
fuel_oil_cost
      
district_heat_cost
      
total_energy_cost
      
total_heat_cost
      
electricity_mmbtu
      
natural_gas_mmbtu
      
fuel_oil_mmbtu
      
district_heat_mmbtu
      
total_mmbtu
      
total_heat_mmbtu
    
  
  

    

      
03
      
99176.042407
      
75242.720323
      
517.528982
      
17433.66
      
NaN
      
93193.909304
      
17951.188982
      
1007.401282
      
0.000
      
1280.880
      
NaN
      
2288.281282
      
1280.880
    
    

      
04
      
105988.237749
      
84839.201398
      
1016.329788
      
9308.85
      
NaN
      
95164.381186
      
10325.179788
      
1364.770649
      
23.766
      
681.345
      
NaN
      
2069.881649
      
705.111
    
    

      
05
      
546259.617195
      
361988.694070
      
NaN
      
NaN
      
132582.139643
      
494570.833713
      
132582.139643
      
5576.480071
      
NaN
      
NaN
      
9357.875
      
14934.355071
      
9357.875
    
    

      
06
      
100363.785597
      
76683.320968
      
588.228982
      
8094.94
      
NaN
      
85366.489950
      
8683.168982
      
1235.762563
      
0.816
      
985.095
      
NaN
      
2221.673563
      
985.911
    
    

      
07
      
107438.320632
      
87312.574743
      
637.478982
      
4187.77
      
NaN
      
92137.823725
      
4825.248982
      
1379.274312
      
0.306
      
302.670
      
NaN
      
1682.250312
      
302.976
    
  

In [33]:

    

# Electricity kWh summed by building
df3 = pd.pivot_table(df2.query('units == "kWh"'), index='site_id', values='usage', aggfunc=np.sum)
df3.columns = ['electricity_kwh']
df3.head()


    

    
Out[33]:







  

    

      

      
electricity_kwh
    
    

      
site_id
      

    
  
  

    

      
03
      
2.952524e+05
    
    

      
04
      
3.999914e+05
    
    

      
05
      
1.634373e+06
    
    

      
06
      
3.621813e+05
    
    

      
07
      
4.042422e+05
    
  

In [34]:

    

# Include in Final DF
df_final = pd.concat([df_final, df3], axis=1)
df_final.head()


    

    
Out[34]:







  

    

      

      
total_cost
      
electricity_cost
      
natural_gas_cost
      
fuel_oil_cost
      
district_heat_cost
      
total_energy_cost
      
total_heat_cost
      
electricity_mmbtu
      
natural_gas_mmbtu
      
fuel_oil_mmbtu
      
district_heat_mmbtu
      
total_mmbtu
      
total_heat_mmbtu
      
electricity_kwh
    
  
  

    

      
03
      
99176.042407
      
75242.720323
      
517.528982
      
17433.66
      
NaN
      
93193.909304
      
17951.188982
      
1007.401282
      
0.000
      
1280.880
      
NaN
      
2288.281282
      
1280.880
      
2.952524e+05
    
    

      
04
      
105988.237749
      
84839.201398
      
1016.329788
      
9308.85
      
NaN
      
95164.381186
      
10325.179788
      
1364.770649
      
23.766
      
681.345
      
NaN
      
2069.881649
      
705.111
      
3.999914e+05
    
    

      
05
      
546259.617195
      
361988.694070
      
NaN
      
NaN
      
132582.139643
      
494570.833713
      
132582.139643
      
5576.480071
      
NaN
      
NaN
      
9357.875
      
14934.355071
      
9357.875
      
1.634373e+06
    
    

      
06
      
100363.785597
      
76683.320968
      
588.228982
      
8094.94
      
NaN
      
85366.489950
      
8683.168982
      
1235.762563
      
0.816
      
985.095
      
NaN
      
2221.673563
      
985.911
      
3.621813e+05
    
    

      
07
      
107438.320632
      
87312.574743
      
637.478982
      
4187.77
      
NaN
      
92137.823725
      
4825.248982
      
1379.274312
      
0.306
      
302.670
      
NaN
      
1682.250312
      
302.976
      
4.042422e+05
    
  

In [35]:

    

# Electricity kW, both Average and Max by building
df3 = pd.pivot_table(df2.query('units == "kW"'), index='site_id', values='usage', aggfunc=[np.mean, np.max])
df3.columns = ['electricity_kw_average', 'electricity_kw_max']
df3.head()


    

    
Out[35]:







  

    

      

      
electricity_kw_average
      
electricity_kw_max
    
    

      
site_id
      

      

    
  
  

    

      
ASLELC1
      
96.774383
      
125.285595
    
    

      
ASLELC2
      
678.454329
      
7793.255542
    
    

      
BALHHW
      
78.843710
      
101.410714
    
    

      
BAOBG1
      
161.955333
      
218.038866
    
    

      
CACBG1
      
394.247915
      
496.671907
    
  

In [36]:

    

# Add into Final Frame
df_final = pd.concat([df_final, df3], axis=1)
df_final.head()


    

    
Out[36]:







  

    

      

      
total_cost
      
electricity_cost
      
natural_gas_cost
      
fuel_oil_cost
      
district_heat_cost
      
total_energy_cost
      
total_heat_cost
      
electricity_mmbtu
      
natural_gas_mmbtu
      
fuel_oil_mmbtu
      
district_heat_mmbtu
      
total_mmbtu
      
total_heat_mmbtu
      
electricity_kwh
      
electricity_kw_average
      
electricity_kw_max
    
  
  

    

      
03
      
99176.042407
      
75242.720323
      
517.528982
      
17433.66
      
NaN
      
93193.909304
      
17951.188982
      
1007.401282
      
0.000
      
1280.880
      
NaN
      
2288.281282
      
1280.880
      
2.952524e+05
      
NaN
      
NaN
    
    

      
04
      
105988.237749
      
84839.201398
      
1016.329788
      
9308.85
      
NaN
      
95164.381186
      
10325.179788
      
1364.770649
      
23.766
      
681.345
      
NaN
      
2069.881649
      
705.111
      
3.999914e+05
      
NaN
      
NaN
    
    

      
05
      
546259.617195
      
361988.694070
      
NaN
      
NaN
      
132582.139643
      
494570.833713
      
132582.139643
      
5576.480071
      
NaN
      
NaN
      
9357.875
      
14934.355071
      
9357.875
      
1.634373e+06
      
NaN
      
NaN
    
    

      
06
      
100363.785597
      
76683.320968
      
588.228982
      
8094.94
      
NaN
      
85366.489950
      
8683.168982
      
1235.762563
      
0.816
      
985.095
      
NaN
      
2221.673563
      
985.911
      
3.621813e+05
      
NaN
      
NaN
    
    

      
07
      
107438.320632
      
87312.574743
      
637.478982
      
4187.77
      
NaN
      
92137.823725
      
4825.248982
      
1379.274312
      
0.306
      
302.670
      
NaN
      
1682.250312
      
302.976
      
4.042422e+05
      
NaN
      
NaN
    
  

In [37]:

    

# Check a building that has a kW charge
df_final.loc['BALHHW']


    

    
Out[37]:





total_cost                151479.203004
electricity_cost          140264.698548
natural_gas_cost                    NaN
fuel_oil_cost               8512.862536
district_heat_cost                  NaN
total_energy_cost         148777.561084
total_heat_cost             8512.862536
electricity_mmbtu           2351.479959
natural_gas_mmbtu                   NaN
fuel_oil_mmbtu               605.109668
district_heat_mmbtu                 NaN
total_mmbtu                 2956.589627
total_heat_mmbtu             605.109668
electricity_kwh           689179.354839
electricity_kw_average        78.843710
electricity_kw_max           101.410714
Name: BALHHW, dtype: float64

In [38]:

    

# Add in Square footage info
df_bldg = ut.building_info_df()[['sq_ft']]
# Add into Final Frame.  I do a merge here so as not to bring
# in buildings from the building info spreadsheet that are not in this
# dataset; this dataset has been restricted to one year.

df_final = pd.merge(df_final, df_bldg, how='left', left_index=True, right_index=True)
df_final.head()


    

    
Out[38]:







  

    

      

      
total_cost
      
electricity_cost
      
natural_gas_cost
      
fuel_oil_cost
      
district_heat_cost
      
total_energy_cost
      
total_heat_cost
      
electricity_mmbtu
      
natural_gas_mmbtu
      
fuel_oil_mmbtu
      
district_heat_mmbtu
      
total_mmbtu
      
total_heat_mmbtu
      
electricity_kwh
      
electricity_kw_average
      
electricity_kw_max
      
sq_ft
    
  
  

    

      
03
      
99176.042407
      
75242.720323
      
517.528982
      
17433.66
      
NaN
      
93193.909304
      
17951.188982
      
1007.401282
      
0.000
      
1280.880
      
NaN
      
2288.281282
      
1280.880
      
2.952524e+05
      
NaN
      
NaN
      
49210.0
    
    

      
04
      
105988.237749
      
84839.201398
      
1016.329788
      
9308.85
      
NaN
      
95164.381186
      
10325.179788
      
1364.770649
      
23.766
      
681.345
      
NaN
      
2069.881649
      
705.111
      
3.999914e+05
      
NaN
      
NaN
      
49210.0
    
    

      
05
      
546259.617195
      
361988.694070
      
NaN
      
NaN
      
132582.139643
      
494570.833713
      
132582.139643
      
5576.480071
      
NaN
      
NaN
      
9357.875
      
14934.355071
      
9357.875
      
1.634373e+06
      
NaN
      
NaN
      
234412.0
    
    

      
06
      
100363.785597
      
76683.320968
      
588.228982
      
8094.94
      
NaN
      
85366.489950
      
8683.168982
      
1235.762563
      
0.816
      
985.095
      
NaN
      
2221.673563
      
985.911
      
3.621813e+05
      
NaN
      
NaN
      
57047.0
    
    

      
07
      
107438.320632
      
87312.574743
      
637.478982
      
4187.77
      
NaN
      
92137.823725
      
4825.248982
      
1379.274312
      
0.306
      
302.670
      
NaN
      
1682.250312
      
302.976
      
4.042422e+05
      
NaN
      
NaN
      
64699.0
    
  

In [39]:

    

# Build a DataFrame that has monthly degree days for each site/year/month
# combination.
combos = set(zip(df1.site_id, df1.fiscal_year, df1.fiscal_mo))
df_dd = pd.DataFrame(data=list(combos), columns=['site_id', 'fiscal_year', 'fiscal_mo'])
ut.add_degree_days_col(df_dd)

# Add up the degree days by site (we've already filtered down to one year or less
# of data.)
dd_series = df_dd.groupby('site_id').sum()['degree_days']
# Put in final DataFrame
df_final = pd.concat([df_final, dd_series], axis=1)
df_final.head()


    

    
Out[39]:







  

    

      

      
total_cost
      
electricity_cost
      
natural_gas_cost
      
fuel_oil_cost
      
district_heat_cost
      
total_energy_cost
      
total_heat_cost
      
electricity_mmbtu
      
natural_gas_mmbtu
      
fuel_oil_mmbtu
      
district_heat_mmbtu
      
total_mmbtu
      
total_heat_mmbtu
      
electricity_kwh
      
electricity_kw_average
      
electricity_kw_max
      
sq_ft
      
degree_days
    
    

      
site_id
      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    
  
  

    

      
03
      
99176.042407
      
75242.720323
      
517.528982
      
17433.66
      
NaN
      
93193.909304
      
17951.188982
      
1007.401282
      
0.000
      
1280.880
      
NaN
      
2288.281282
      
1280.880
      
2.952524e+05
      
NaN
      
NaN
      
49210.0
      
13802
    
    

      
04
      
105988.237749
      
84839.201398
      
1016.329788
      
9308.85
      
NaN
      
95164.381186
      
10325.179788
      
1364.770649
      
23.766
      
681.345
      
NaN
      
2069.881649
      
705.111
      
3.999914e+05
      
NaN
      
NaN
      
49210.0
      
13802
    
    

      
05
      
546259.617195
      
361988.694070
      
NaN
      
NaN
      
132582.139643
      
494570.833713
      
132582.139643
      
5576.480071
      
NaN
      
NaN
      
9357.875
      
14934.355071
      
9357.875
      
1.634373e+06
      
NaN
      
NaN
      
234412.0
      
13802
    
    

      
06
      
100363.785597
      
76683.320968
      
588.228982
      
8094.94
      
NaN
      
85366.489950
      
8683.168982
      
1235.762563
      
0.816
      
985.095
      
NaN
      
2221.673563
      
985.911
      
3.621813e+05
      
NaN
      
NaN
      
57047.0
      
13802
    
    

      
07
      
107438.320632
      
87312.574743
      
637.478982
      
4187.77
      
NaN
      
92137.823725
      
4825.248982
      
1379.274312
      
0.306
      
302.670
      
NaN
      
1682.250312
      
302.976
      
4.042422e+05
      
NaN
      
NaN
      
64699.0
      
13802
    
  

In [40]:

    

# Calculate per square foot values for each building.
df_final['eui'] = df_final.total_mmbtu * 1e3 / df_final.sq_ft
df_final['eci'] = df_final.total_energy_cost / df_final.sq_ft
df_final['hdd_eui'] = df_final.total_heat_mmbtu * 1e6 / df_final.sq_ft / df_final.degree_days
df_final.to_csv('df_final.csv')
df_final.head()


    

    
Out[40]:







  

    

      

      
total_cost
      
electricity_cost
      
natural_gas_cost
      
fuel_oil_cost
      
district_heat_cost
      
total_energy_cost
      
total_heat_cost
      
electricity_mmbtu
      
natural_gas_mmbtu
      
fuel_oil_mmbtu
      
...
      
total_mmbtu
      
total_heat_mmbtu
      
electricity_kwh
      
electricity_kw_average
      
electricity_kw_max
      
sq_ft
      
degree_days
      
eui
      
eci
      
hdd_eui
    
    

      
site_id
      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    
  
  

    

      
03
      
99176.042407
      
75242.720323
      
517.528982
      
17433.66
      
NaN
      
93193.909304
      
17951.188982
      
1007.401282
      
0.000
      
1280.880
      
...
      
2288.281282
      
1280.880
      
2.952524e+05
      
NaN
      
NaN
      
49210.0
      
13802
      
46.500331
      
1.893800
      
1.885876
    
    

      
04
      
105988.237749
      
84839.201398
      
1016.329788
      
9308.85
      
NaN
      
95164.381186
      
10325.179788
      
1364.770649
      
23.766
      
681.345
      
...
      
2069.881649
      
705.111
      
3.999914e+05
      
NaN
      
NaN
      
49210.0
      
13802
      
42.062216
      
1.933842
      
1.038155
    
    

      
05
      
546259.617195
      
361988.694070
      
NaN
      
NaN
      
132582.139643
      
494570.833713
      
132582.139643
      
5576.480071
      
NaN
      
NaN
      
...
      
14934.355071
      
9357.875
      
1.634373e+06
      
NaN
      
NaN
      
234412.0
      
13802
      
63.709857
      
2.109836
      
2.892380
    
    

      
06
      
100363.785597
      
76683.320968
      
588.228982
      
8094.94
      
NaN
      
85366.489950
      
8683.168982
      
1235.762563
      
0.816
      
985.095
      
...
      
2221.673563
      
985.911
      
3.621813e+05
      
NaN
      
NaN
      
57047.0
      
13802
      
38.944617
      
1.496424
      
1.252169
    
    

      
07
      
107438.320632
      
87312.574743
      
637.478982
      
4187.77
      
NaN
      
92137.823725
      
4825.248982
      
1379.274312
      
0.306
      
302.670
      
...
      
1682.250312
      
302.976
      
4.042422e+05
      
NaN
      
NaN
      
64699.0
      
13802
      
26.001179
      
1.424100
      
0.339288
    
  

5 rows × 21 columns

In [41]:

    

# Get the totals across all buildings
totals_all_bldgs = df_final.sum()
# Total Degree-Days are not relevant
totals_all_bldgs.drop(['degree_days'], inplace=True)  # not relevant as it includes buildings that don't use heat

# Only use the set of buildings that have some energy use and non-zero
# square footage to determine EUI's and ECI's
energy_bldgs = df_final.query("total_mmbtu > 0 and sq_ft > 0")

# Get total square feet, energy use, and energy cost for these buildings
# and calculate EUI and ECI
sq_ft_energy_bldgs = energy_bldgs.sq_ft.sum()
energy_in_energy_bldgs = energy_bldgs.total_mmbtu.sum()
energy_cost_in_energy_bldgs = energy_bldgs.total_energy_cost.sum()
totals_all_bldgs['eui'] = energy_in_energy_bldgs * 1e3 / sq_ft_energy_bldgs
totals_all_bldgs['eci'] = energy_cost_in_energy_bldgs / sq_ft_energy_bldgs

# For calculating heating specific EUI, further filter the set of
# buildings down to those that have heating fuel use.
# Get separate square footage total and weighted average degree-day for these.
heat_bldgs = energy_bldgs.query("total_heat_mmbtu > 0")
heat_bldgs_sq_ft = heat_bldgs.sq_ft.sum()
heat_bldgs_heat_mmbtu = heat_bldgs.total_heat_mmbtu.sum()
heat_bldgs_degree_days = (heat_bldgs.total_heat_mmbtu * heat_bldgs.degree_days).sum() / heat_bldgs.total_heat_mmbtu.sum()
totals_all_bldgs['hdd_eui'] = heat_bldgs_heat_mmbtu * 1e6 / heat_bldgs_sq_ft / heat_bldgs_degree_days
totals_all_bldgs


    

    
Out[41]:





total_cost                8.819777e+06
electricity_cost          6.223575e+06
natural_gas_cost          5.584942e+05
fuel_oil_cost             5.823540e+05
district_heat_cost        6.393009e+05
total_energy_cost         8.003724e+06
total_heat_cost           1.780149e+06
electricity_mmbtu         9.933530e+04
natural_gas_mmbtu         3.025849e+04
fuel_oil_mmbtu            4.222940e+04
district_heat_mmbtu       3.755807e+04
total_mmbtu               2.093813e+05
total_heat_mmbtu          1.100460e+05
electricity_kwh           2.911351e+07
electricity_kw_average    2.586110e+03
electricity_kw_max        1.433775e+04
sq_ft                     3.132384e+06
eui                       6.490046e+01
eci                       2.439388e+00
hdd_eui                   2.554333e+00
dtype: float64

In [42]:

    

# calculate a rank DataFrame
df_rank = pd.DataFrame()
for col in df_final.columns:
    df_rank[col] = df_final[col].rank(ascending=False)
df_rank.head()


    

    
Out[42]:







  

    

      

      
total_cost
      
electricity_cost
      
natural_gas_cost
      
fuel_oil_cost
      
district_heat_cost
      
total_energy_cost
      
total_heat_cost
      
electricity_mmbtu
      
natural_gas_mmbtu
      
fuel_oil_mmbtu
      
...
      
total_mmbtu
      
total_heat_mmbtu
      
electricity_kwh
      
electricity_kw_average
      
electricity_kw_max
      
sq_ft
      
degree_days
      
eui
      
eci
      
hdd_eui
    
    

      
site_id
      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    
  
  

    

      
03
      
35.0
      
35.0
      
21.0
      
8.0
      
NaN
      
33.0
      
26.0
      
36.0
      
20.5
      
8.0
      
...
      
28.0
      
25.0
      
36.0
      
NaN
      
NaN
      
28.5
      
40.0
      
76.0
      
70.0
      
42.0
    
    

      
04
      
31.0
      
30.0
      
14.0
      
21.0
      
NaN
      
32.0
      
36.0
      
28.0
      
14.0
      
21.0
      
...
      
32.0
      
36.0
      
28.0
      
NaN
      
NaN
      
28.5
      
40.0
      
80.0
      
67.0
      
48.0
    
    

      
05
      
2.0
      
2.0
      
NaN
      
NaN
      
1.0
      
2.0
      
2.0
      
2.0
      
NaN
      
NaN
      
...
      
1.0
      
1.0
      
2.0
      
NaN
      
NaN
      
1.0
      
40.0
      
65.0
      
63.0
      
31.0
    
    

      
06
      
33.0
      
34.0
      
20.0
      
26.0
      
NaN
      
36.0
      
41.0
      
33.0
      
18.0
      
14.0
      
...
      
30.0
      
30.0
      
33.0
      
NaN
      
NaN
      
27.0
      
40.0
      
83.0
      
88.0
      
46.0
    
    

      
07
      
30.0
      
28.0
      
17.0
      
38.0
      
NaN
      
35.0
      
53.0
      
27.0
      
19.0
      
38.0
      
...
      
39.0
      
52.0
      
27.0
      
NaN
      
NaN
      
16.0
      
40.0
      
97.0
      
91.0
      
63.0
    
  

5 rows × 21 columns

In [43]:

    

if site in df_final.index:
    # The site exists in the DataFrame
    site_info = df_final.loc[site]
    site_pct = site_info / totals_all_bldgs
    site_rank = df_rank.loc[site]
else:
    # Site is not there, probabaly because not present in this year.
    # Make variables with NaN values for all elements.
    site_info = df_final.iloc[0].copy()   # Just grab the first row to start with
    site_info[:] = np.NaN                 # Put 
    site_pct = site_info.copy()
    site_rank = sit_info.copy()
print(site_info, '\n')
print(site_pct, '\n')
print(site_rank, '\n')


    

    




total_cost                105418.750824
electricity_cost           54161.057879
natural_gas_cost           41955.989017
fuel_oil_cost                       NaN
district_heat_cost                  NaN
total_energy_cost          96117.046896
total_heat_cost            41955.989017
electricity_mmbtu            880.345629
natural_gas_mmbtu           2112.299698
fuel_oil_mmbtu                      NaN
district_heat_mmbtu                 NaN
total_mmbtu                 2992.645327
total_heat_mmbtu            2112.299698
electricity_kwh           258014.545455
electricity_kw_average              NaN
electricity_kw_max                  NaN
sq_ft                       6718.000000
degree_days                13802.000000
eui                          445.466705
eci                           14.307390
hdd_eui                       22.781038
Name: ANSBG1, dtype: float64 

degree_days                    NaN
district_heat_cost             NaN
district_heat_mmbtu            NaN
eci                       5.865155
electricity_cost          0.008703
electricity_kw_average         NaN
electricity_kw_max             NaN
electricity_kwh           0.008862
electricity_mmbtu         0.008862
eui                       6.863845
fuel_oil_cost                  NaN
fuel_oil_mmbtu                 NaN
hdd_eui                   8.918585
natural_gas_cost          0.075123
natural_gas_mmbtu         0.069809
sq_ft                     0.002145
total_cost                0.011953
total_energy_cost         0.012009
total_heat_cost           0.023569
total_heat_mmbtu          0.019195
total_mmbtu               0.014293
dtype: float64 

total_cost                32.0
electricity_cost          40.0
natural_gas_cost           7.0
fuel_oil_cost              NaN
district_heat_cost         NaN
total_energy_cost         31.0
total_heat_cost           17.0
electricity_mmbtu         38.0
natural_gas_mmbtu          8.0
fuel_oil_mmbtu             NaN
district_heat_mmbtu        NaN
total_mmbtu               22.0
total_heat_mmbtu          17.0
electricity_kwh           38.0
electricity_kw_average     NaN
electricity_kw_max         NaN
sq_ft                     53.0
degree_days               40.0
eui                       38.0
eci                       42.0
hdd_eui                    3.0
Name: ANSBG1, dtype: float64 

In [44]:

    

# Make a final dictioary to hold all the results for this table
tbl2_data = {
    'fiscal_year': 'FY {}'.format(last_complete_year),
    'bldg': site_info.to_dict(),
    'all': totals_all_bldgs.to_dict(),
    'pct': site_pct.to_dict(),
    'rank': site_rank.to_dict()
}
template_data['energy_index_comparison']['details_table'] = tbl2_data
template_data


    

    
Out[44]:





{'building_info': {'bldg': {'acct_elec': '31850',
   'acct_nat_gas': '10282  (2408 DAVIS)',
   'acct_oil': '25465',
   'acct_refuse': '',
   'acct_sewer': '1311001100',
   'acct_steam': '',
   'acct_water': '1311001100',
   'address': '2408 Davis Road',
   'city': 'Fairbanks',
   'dd_site': 'PAFA',
   'onsite_gen': nan,
   'primary_func': 'Animal Shelter',
   'site_category': 'Other',
   'site_id': 'ANSBG1',
   'site_name': 'FNSB-Animal Control',
   'source_elec': 'Golden Valley Electric',
   'source_nat_gas': 'Fairbanks Natural Gas',
   'source_oil': 'Sourdough Fuel  (Petro Star)',
   'source_refuse': '',
   'source_sewer': 'Golden Heart Utilities',
   'source_steam': '',
   'source_water': 'Golden Heart Utilities',
   'sq_ft': 6718.0,
   'year_built': nan},
  'date_updated': 'October 17, 2017 03:15 PM'},
 'energy_index_comparison': {'details_table': {'all': {'district_heat_cost': 639300.85440784309,
    'district_heat_mmbtu': 37558.0730729114,
    'eci': 2.439388445990891,
    'electricity_cost': 6223574.8791972268,
    'electricity_kw_average': 2586.1100328687585,
    'electricity_kw_max': 14337.74838526623,
    'electricity_kwh': 29113512.124528244,
    'electricity_mmbtu': 99335.303368890352,
    'eui': 64.900457556520806,
    'fuel_oil_cost': 582353.99678922666,
    'fuel_oil_mmbtu': 42229.404216859388,
    'hdd_eui': 2.5543331314934399,
    'natural_gas_cost': 558494.1944657258,
    'natural_gas_mmbtu': 30258.488525201614,
    'sq_ft': 3132384.0,
    'total_cost': 8819776.9245495666,
    'total_energy_cost': 8003723.9248600267,
    'total_heat_cost': 1780149.0456627952,
    'total_heat_mmbtu': 110045.96581497238,
    'total_mmbtu': 209381.26918386272},
   'bldg': {'degree_days': 13802.0,
    'district_heat_cost': nan,
    'district_heat_mmbtu': nan,
    'eci': 14.30739013038478,
    'electricity_cost': 54161.057878787855,
    'electricity_kw_average': nan,
    'electricity_kw_max': nan,
    'electricity_kwh': 258014.54545454547,
    'electricity_mmbtu': 880.34562909090914,
    'eui': 445.46670536938882,
    'fuel_oil_cost': nan,
    'fuel_oil_mmbtu': nan,
    'hdd_eui': 22.781038304511632,
    'natural_gas_cost': 41955.989017137101,
    'natural_gas_mmbtu': 2112.299697580645,
    'sq_ft': 6718.0,
    'total_cost': 105418.75082449635,
    'total_energy_cost': 96117.046895924956,
    'total_heat_cost': 41955.989017137101,
    'total_heat_mmbtu': 2112.2996975806454,
    'total_mmbtu': 2992.6453266715544},
   'fiscal_year': 'FY 2017',
   'pct': {'degree_days': nan,
    'district_heat_cost': nan,
    'district_heat_mmbtu': nan,
    'eci': 5.865154503744094,
    'electricity_cost': 0.0087025638688505731,
    'electricity_kw_average': nan,
    'electricity_kw_max': nan,
    'electricity_kwh': 0.0088623641266959083,
    'electricity_mmbtu': 0.0088623641266959083,
    'eui': 6.8638453739935308,
    'fuel_oil_cost': nan,
    'fuel_oil_mmbtu': nan,
    'hdd_eui': 8.9185854513785614,
    'natural_gas_cost': 0.075123411188317904,
    'natural_gas_mmbtu': 0.069808500045247079,
    'sq_ft': 0.0021446923493415879,
    'total_cost': 0.011952541626202205,
    'total_energy_cost': 0.012009040766308777,
    'total_heat_cost': 0.023568806847583827,
    'total_heat_mmbtu': 0.019194703612599444,
    'total_mmbtu': 0.014292803450549537},
   'rank': {'degree_days': 40.0,
    'district_heat_cost': nan,
    'district_heat_mmbtu': nan,
    'eci': 42.0,
    'electricity_cost': 40.0,
    'electricity_kw_average': nan,
    'electricity_kw_max': nan,
    'electricity_kwh': 38.0,
    'electricity_mmbtu': 38.0,
    'eui': 38.0,
    'fuel_oil_cost': nan,
    'fuel_oil_mmbtu': nan,
    'hdd_eui': 3.0,
    'natural_gas_cost': 7.0,
    'natural_gas_mmbtu': 8.0,
    'sq_ft': 53.0,
    'total_cost': 32.0,
    'total_energy_cost': 31.0,
    'total_heat_cost': 17.0,
    'total_heat_mmbtu': 17.0,
    'total_mmbtu': 22.0}},
  'yearly_table': {'rows': [{'cost': 24319.055468749997,
     'eci': 3.6199844401235484,
     'eui': 93.857933908901472,
     'fiscal_year': 2007,
     'hdd': 14075.0,
     'heat_mmbtu': 0.0,
     'mmbtu': 630.53760000000011,
     'month_count': 12.0,
     'specific_eui': 0.0},
    {'cost': 33274.530937500007,
     'eci': 4.9530412232063128,
     'eui': 105.52614985858885,
     'fiscal_year': 2008,
     'hdd': 13382.0,
     'heat_mmbtu': 0.0,
     'mmbtu': 708.92467474999989,
     'month_count': 12.0,
     'specific_eui': 0.0},
    {'cost': 134796.8528967803,
     'eci': 20.065027224885426,
     'eui': 802.85036311875831,
     'fiscal_year': 2009,
     'hdd': 14471.0,
     'heat_mmbtu': 4736.7620999999999,
     'mmbtu': 5393.5487394318179,
     'month_count': 12.0,
     'specific_eui': 48.724010729983647},
    {'cost': 132430.49638662484,
     'eci': 19.712786005749457,
     'eui': 752.86090078495897,
     'fiscal_year': 2010,
     'hdd': 13119.0,
     'heat_mmbtu': 4416.0215896551726,
     'mmbtu': 5057.7195314733544,
     'month_count': 12.0,
     'specific_eui': 50.106083337300866},
    {'cost': 152615.09171943573,
     'eci': 22.717340238082127,
     'eui': 873.35224409040973,
     'fiscal_year': 2011,
     'hdd': 13667.0,
     'heat_mmbtu': 5203.0935103448273,
     'mmbtu': 5867.180375799373,
     'month_count': 12.0,
     'specific_eui': 56.669376869424966},
    {'cost': 136484.94021187679,
     'eci': 20.316305479588685,
     'eui': 724.41503787205818,
     'fiscal_year': 2012,
     'hdd': 13960.0,
     'heat_mmbtu': 4183.5823161290327,
     'mmbtu': 4866.6202244244869,
     'month_count': 12.0,
     'specific_eui': 44.609042423436598},
    {'cost': 116931.78078837601,
     'eci': 17.405742897942247,
     'eui': 617.1585567899283,
     'fiscal_year': 2013,
     'hdd': 14939.0,
     'heat_mmbtu': 3545.5216451612896,
     'mmbtu': 4146.071184514738,
     'month_count': 12.0,
     'specific_eui': 35.327964417222773},
    {'cost': 84810.964735817572,
     'eci': 12.624436548945754,
     'eui': 413.84795517959998,
     'fiscal_year': 2014,
     'hdd': 12531.0,
     'heat_mmbtu': 2163.8790000000008,
     'mmbtu': 2780.2305628965523,
     'month_count': 12.0,
     'specific_eui': 25.704386494521287},
    {'cost': 85146.096424731208,
     'eci': 12.67432218290134,
     'eui': 408.9628768546707,
     'fiscal_year': 2015,
     'hdd': 12345.0,
     'heat_mmbtu': 2082.8778387096777,
     'mmbtu': 2747.4126067096777,
     'month_count': 12.0,
     'specific_eui': 25.114972412420443},
    {'cost': 78038.30011287269,
     'eci': 11.616299510698525,
     'eui': 408.88360214078864,
     'fiscal_year': 2016,
     'hdd': 12017.0,
     'heat_mmbtu': 2151.6836250000001,
     'mmbtu': 2746.8800391818181,
     'month_count': 12.0,
     'specific_eui': 26.652770153961953},
    {'cost': 96117.046895924927,
     'eci': 14.307390130384777,
     'eui': 445.46670536938893,
     'fiscal_year': 2017,
     'hdd': 13802.0,
     'heat_mmbtu': 2112.2996975806454,
     'mmbtu': 2992.6453266715548,
     'month_count': 12.0,
     'specific_eui': 22.781038304511629}]}}}

In [45]:

    

# From the main DataFrame, get only the rows for this site, and only get
# the needed columns for this analysis
df1 = df.query('site_id == @site')[['service_type', 'fiscal_year', 'fiscal_mo', 'cost']]
df1.head()


    

    
Out[45]:







  

    

      

      
service_type
      
fiscal_year
      
fiscal_mo
      
cost
    
  
  

    

      
30353
      
Electricity
      
2006
      
12
      
739.280
    
    

      
30354
      
Electricity
      
2006
      
12
      
0.000
    
    

      
30355
      
Electricity
      
2006
      
12
      
183.065
    
    

      
30356
      
Electricity
      
2007
      
1
      
1414.275
    
    

      
30357
      
Electricity
      
2007
      
1
      
0.000
    
  

In [46]:

    

df2 = pd.pivot_table(
    df1, 
    values='cost', 
    index=['fiscal_year'], 
    columns=['service_type'],
    aggfunc=np.sum
)
df2


    

    
Out[46]:







  

    

      
service_type
      
Electricity
      
Natural Gas
      
Oil #1
      
Sewer
      
Water
    
    

      
fiscal_year
      

      

      

      

      

    
  
  

    

      
2006
      
922.345000
      
NaN
      
NaN
      
NaN
      
NaN
    
    

      
2007
      
24319.055469
      
NaN
      
NaN
      
3886.897857
      
3224.555714
    
    

      
2008
      
33274.530938
      
NaN
      
NaN
      
5401.766996
      
5085.043697
    
    

      
2009
      
30307.350563
      
100896.902333
      
3592.6
      
5823.032433
      
5472.173588
    
    

      
2010
      
32688.293030
      
99742.203356
      
NaN
      
5074.845929
      
4707.815036
    
    

      
2011
      
35138.080909
      
117477.010810
      
NaN
      
4971.323638
      
4640.693631
    
    

      
2012
      
41994.697841
      
94490.242371
      
NaN
      
5484.028148
      
5090.420833
    
    

      
2013
      
36961.451595
      
79970.329194
      
NaN
      
5169.324500
      
4783.854500
    
    

      
2014
      
35843.822155
      
48967.142581
      
NaN
      
4840.116833
      
4375.768167
    
    

      
2015
      
38017.419167
      
47128.677258
      
NaN
      
4593.016167
      
4156.942333
    
    

      
2016
      
32391.098485
      
45647.201628
      
NaN
      
6108.227500
      
5113.362500
    
    

      
2017
      
54161.057879
      
41955.989017
      
NaN
      
4978.546250
      
4323.157679
    
    

      
2018
      
7256.446970
      
3779.601452
      
NaN
      
1236.853750
      
1046.692321
    
  

In [47]:

    

# Add in columns for the missing services
missing_services = bu.missing_services(df2.columns)
bu.add_columns(df2, missing_services)
df2


    

    
Out[47]:







  

    

      
service_type
      
Electricity
      
Natural Gas
      
Oil #1
      
Sewer
      
Water
      
Steam
      
Refuse
    
    

      
fiscal_year
      

      

      

      

      

      

      

    
  
  

    

      
2006
      
922.345000
      
NaN
      
NaN
      
NaN
      
NaN
      
0.0
      
0.0
    
    

      
2007
      
24319.055469
      
NaN
      
NaN
      
3886.897857
      
3224.555714
      
0.0
      
0.0
    
    

      
2008
      
33274.530938
      
NaN
      
NaN
      
5401.766996
      
5085.043697
      
0.0
      
0.0
    
    

      
2009
      
30307.350563
      
100896.902333
      
3592.6
      
5823.032433
      
5472.173588
      
0.0
      
0.0
    
    

      
2010
      
32688.293030
      
99742.203356
      
NaN
      
5074.845929
      
4707.815036
      
0.0
      
0.0
    
    

      
2011
      
35138.080909
      
117477.010810
      
NaN
      
4971.323638
      
4640.693631
      
0.0
      
0.0
    
    

      
2012
      
41994.697841
      
94490.242371
      
NaN
      
5484.028148
      
5090.420833
      
0.0
      
0.0
    
    

      
2013
      
36961.451595
      
79970.329194
      
NaN
      
5169.324500
      
4783.854500
      
0.0
      
0.0
    
    

      
2014
      
35843.822155
      
48967.142581
      
NaN
      
4840.116833
      
4375.768167
      
0.0
      
0.0
    
    

      
2015
      
38017.419167
      
47128.677258
      
NaN
      
4593.016167
      
4156.942333
      
0.0
      
0.0
    
    

      
2016
      
32391.098485
      
45647.201628
      
NaN
      
6108.227500
      
5113.362500
      
0.0
      
0.0
    
    

      
2017
      
54161.057879
      
41955.989017
      
NaN
      
4978.546250
      
4323.157679
      
0.0
      
0.0
    
    

      
2018
      
7256.446970
      
3779.601452
      
NaN
      
1236.853750
      
1046.692321
      
0.0
      
0.0
    
  

In [48]:

    

# Add a Total column that sums the other columns
df2['Total'] = df2.sum(axis=1)
df2


    

    
Out[48]:







  

    

      
service_type
      
Electricity
      
Natural Gas
      
Oil #1
      
Sewer
      
Water
      
Steam
      
Refuse
      
Total
    
    

      
fiscal_year
      

      

      

      

      

      

      

      

    
  
  

    

      
2006
      
922.345000
      
NaN
      
NaN
      
NaN
      
NaN
      
0.0
      
0.0
      
922.345000
    
    

      
2007
      
24319.055469
      
NaN
      
NaN
      
3886.897857
      
3224.555714
      
0.0
      
0.0
      
31430.509040
    
    

      
2008
      
33274.530938
      
NaN
      
NaN
      
5401.766996
      
5085.043697
      
0.0
      
0.0
      
43761.341631
    
    

      
2009
      
30307.350563
      
100896.902333
      
3592.6
      
5823.032433
      
5472.173588
      
0.0
      
0.0
      
146092.058918
    
    

      
2010
      
32688.293030
      
99742.203356
      
NaN
      
5074.845929
      
4707.815036
      
0.0
      
0.0
      
142213.157351
    
    

      
2011
      
35138.080909
      
117477.010810
      
NaN
      
4971.323638
      
4640.693631
      
0.0
      
0.0
      
162227.108988
    
    

      
2012
      
41994.697841
      
94490.242371
      
NaN
      
5484.028148
      
5090.420833
      
0.0
      
0.0
      
147059.389193
    
    

      
2013
      
36961.451595
      
79970.329194
      
NaN
      
5169.324500
      
4783.854500
      
0.0
      
0.0
      
126884.959788
    
    

      
2014
      
35843.822155
      
48967.142581
      
NaN
      
4840.116833
      
4375.768167
      
0.0
      
0.0
      
94026.849736
    
    

      
2015
      
38017.419167
      
47128.677258
      
NaN
      
4593.016167
      
4156.942333
      
0.0
      
0.0
      
93896.054925
    
    

      
2016
      
32391.098485
      
45647.201628
      
NaN
      
6108.227500
      
5113.362500
      
0.0
      
0.0
      
89259.890113
    
    

      
2017
      
54161.057879
      
41955.989017
      
NaN
      
4978.546250
      
4323.157679
      
0.0
      
0.0
      
105418.750824
    
    

      
2018
      
7256.446970
      
3779.601452
      
NaN
      
1236.853750
      
1046.692321
      
0.0
      
0.0
      
13319.594493
    
  

In [49]:

    

# Add a percent change column
df2['pct_change'] = df2.Total.pct_change()
df2


    

    
Out[49]:







  

    

      
service_type
      
Electricity
      
Natural Gas
      
Oil #1
      
Sewer
      
Water
      
Steam
      
Refuse
      
Total
      
pct_change
    
    

      
fiscal_year
      

      

      

      

      

      

      

      

      

    
  
  

    

      
2006
      
922.345000
      
NaN
      
NaN
      
NaN
      
NaN
      
0.0
      
0.0
      
922.345000
      
NaN
    
    

      
2007
      
24319.055469
      
NaN
      
NaN
      
3886.897857
      
3224.555714
      
0.0
      
0.0
      
31430.509040
      
33.076738
    
    

      
2008
      
33274.530938
      
NaN
      
NaN
      
5401.766996
      
5085.043697
      
0.0
      
0.0
      
43761.341631
      
0.392320
    
    

      
2009
      
30307.350563
      
100896.902333
      
3592.6
      
5823.032433
      
5472.173588
      
0.0
      
0.0
      
146092.058918
      
2.338382
    
    

      
2010
      
32688.293030
      
99742.203356
      
NaN
      
5074.845929
      
4707.815036
      
0.0
      
0.0
      
142213.157351
      
-0.026551
    
    

      
2011
      
35138.080909
      
117477.010810
      
NaN
      
4971.323638
      
4640.693631
      
0.0
      
0.0
      
162227.108988
      
0.140732
    
    

      
2012
      
41994.697841
      
94490.242371
      
NaN
      
5484.028148
      
5090.420833
      
0.0
      
0.0
      
147059.389193
      
-0.093497
    
    

      
2013
      
36961.451595
      
79970.329194
      
NaN
      
5169.324500
      
4783.854500
      
0.0
      
0.0
      
126884.959788
      
-0.137186
    
    

      
2014
      
35843.822155
      
48967.142581
      
NaN
      
4840.116833
      
4375.768167
      
0.0
      
0.0
      
94026.849736
      
-0.258960
    
    

      
2015
      
38017.419167
      
47128.677258
      
NaN
      
4593.016167
      
4156.942333
      
0.0
      
0.0
      
93896.054925
      
-0.001391
    
    

      
2016
      
32391.098485
      
45647.201628
      
NaN
      
6108.227500
      
5113.362500
      
0.0
      
0.0
      
89259.890113
      
-0.049376
    
    

      
2017
      
54161.057879
      
41955.989017
      
NaN
      
4978.546250
      
4323.157679
      
0.0
      
0.0
      
105418.750824
      
0.181032
    
    

      
2018
      
7256.446970
      
3779.601452
      
NaN
      
1236.853750
      
1046.692321
      
0.0
      
0.0
      
13319.594493
      
-0.873651
    
  

In [50]:

    

# Add in degree days
months_present = bu.months_present(df1)
deg_days = ut.degree_days_yearly(months_present, site)
df2['hdd'] = deg_days
df2


    

    
Out[50]:







  

    

      
service_type
      
Electricity
      
Natural Gas
      
Oil #1
      
Sewer
      
Water
      
Steam
      
Refuse
      
Total
      
pct_change
      
hdd
    
    

      
fiscal_year
      

      

      

      

      

      

      

      

      

      

    
  
  

    

      
2006
      
922.345000
      
NaN
      
NaN
      
NaN
      
NaN
      
0.0
      
0.0
      
922.345000
      
NaN
      
206
    
    

      
2007
      
24319.055469
      
NaN
      
NaN
      
3886.897857
      
3224.555714
      
0.0
      
0.0
      
31430.509040
      
33.076738
      
14075
    
    

      
2008
      
33274.530938
      
NaN
      
NaN
      
5401.766996
      
5085.043697
      
0.0
      
0.0
      
43761.341631
      
0.392320
      
13382
    
    

      
2009
      
30307.350563
      
100896.902333
      
3592.6
      
5823.032433
      
5472.173588
      
0.0
      
0.0
      
146092.058918
      
2.338382
      
14471
    
    

      
2010
      
32688.293030
      
99742.203356
      
NaN
      
5074.845929
      
4707.815036
      
0.0
      
0.0
      
142213.157351
      
-0.026551
      
13119
    
    

      
2011
      
35138.080909
      
117477.010810
      
NaN
      
4971.323638
      
4640.693631
      
0.0
      
0.0
      
162227.108988
      
0.140732
      
13667
    
    

      
2012
      
41994.697841
      
94490.242371
      
NaN
      
5484.028148
      
5090.420833
      
0.0
      
0.0
      
147059.389193
      
-0.093497
      
13960
    
    

      
2013
      
36961.451595
      
79970.329194
      
NaN
      
5169.324500
      
4783.854500
      
0.0
      
0.0
      
126884.959788
      
-0.137186
      
14939
    
    

      
2014
      
35843.822155
      
48967.142581
      
NaN
      
4840.116833
      
4375.768167
      
0.0
      
0.0
      
94026.849736
      
-0.258960
      
12531
    
    

      
2015
      
38017.419167
      
47128.677258
      
NaN
      
4593.016167
      
4156.942333
      
0.0
      
0.0
      
93896.054925
      
-0.001391
      
12345
    
    

      
2016
      
32391.098485
      
45647.201628
      
NaN
      
6108.227500
      
5113.362500
      
0.0
      
0.0
      
89259.890113
      
-0.049376
      
12017
    
    

      
2017
      
54161.057879
      
41955.989017
      
NaN
      
4978.546250
      
4323.157679
      
0.0
      
0.0
      
105418.750824
      
0.181032
      
13802
    
    

      
2018
      
7256.446970
      
3779.601452
      
NaN
      
1236.853750
      
1046.692321
      
0.0
      
0.0
      
13319.594493
      
-0.873651
      
858
    
  

In [51]:

    

# Add in a column to show the numbers of months present for each year
# This will help to identify partial years.
mo_count = bu.month_count(months_present)
df2['month_count'] = mo_count
df2


    

    
Out[51]:







  

    

      
service_type
      
Electricity
      
Natural Gas
      
Oil #1
      
Sewer
      
Water
      
Steam
      
Refuse
      
Total
      
pct_change
      
hdd
      
month_count
    
    

      
fiscal_year
      

      

      

      

      

      

      

      

      

      

      

    
  
  

    

      
2006
      
922.345000
      
NaN
      
NaN
      
NaN
      
NaN
      
0.0
      
0.0
      
922.345000
      
NaN
      
206
      
1
    
    

      
2007
      
24319.055469
      
NaN
      
NaN
      
3886.897857
      
3224.555714
      
0.0
      
0.0
      
31430.509040
      
33.076738
      
14075
      
12
    
    

      
2008
      
33274.530938
      
NaN
      
NaN
      
5401.766996
      
5085.043697
      
0.0
      
0.0
      
43761.341631
      
0.392320
      
13382
      
12
    
    

      
2009
      
30307.350563
      
100896.902333
      
3592.6
      
5823.032433
      
5472.173588
      
0.0
      
0.0
      
146092.058918
      
2.338382
      
14471
      
12
    
    

      
2010
      
32688.293030
      
99742.203356
      
NaN
      
5074.845929
      
4707.815036
      
0.0
      
0.0
      
142213.157351
      
-0.026551
      
13119
      
12
    
    

      
2011
      
35138.080909
      
117477.010810
      
NaN
      
4971.323638
      
4640.693631
      
0.0
      
0.0
      
162227.108988
      
0.140732
      
13667
      
12
    
    

      
2012
      
41994.697841
      
94490.242371
      
NaN
      
5484.028148
      
5090.420833
      
0.0
      
0.0
      
147059.389193
      
-0.093497
      
13960
      
12
    
    

      
2013
      
36961.451595
      
79970.329194
      
NaN
      
5169.324500
      
4783.854500
      
0.0
      
0.0
      
126884.959788
      
-0.137186
      
14939
      
12
    
    

      
2014
      
35843.822155
      
48967.142581
      
NaN
      
4840.116833
      
4375.768167
      
0.0
      
0.0
      
94026.849736
      
-0.258960
      
12531
      
12
    
    

      
2015
      
38017.419167
      
47128.677258
      
NaN
      
4593.016167
      
4156.942333
      
0.0
      
0.0
      
93896.054925
      
-0.001391
      
12345
      
12
    
    

      
2016
      
32391.098485
      
45647.201628
      
NaN
      
6108.227500
      
5113.362500
      
0.0
      
0.0
      
89259.890113
      
-0.049376
      
12017
      
12
    
    

      
2017
      
54161.057879
      
41955.989017
      
NaN
      
4978.546250
      
4323.157679
      
0.0
      
0.0
      
105418.750824
      
0.181032
      
13802
      
12
    
    

      
2018
      
7256.446970
      
3779.601452
      
NaN
      
1236.853750
      
1046.692321
      
0.0
      
0.0
      
13319.594493
      
-0.873651
      
858
      
3
    
  

In [52]:

    

# trim out the partial years
df2 = df2.query("month_count == 12").copy()


    

In [53]:

    

# Reverse the DataFrame
df2.sort_index(ascending=False, inplace=True)
df2


    

    
Out[53]:







  

    

      
service_type
      
Electricity
      
Natural Gas
      
Oil #1
      
Sewer
      
Water
      
Steam
      
Refuse
      
Total
      
pct_change
      
hdd
      
month_count
    
    

      
fiscal_year
      

      

      

      

      

      

      

      

      

      

      

    
  
  

    

      
2017
      
54161.057879
      
41955.989017
      
NaN
      
4978.546250
      
4323.157679
      
0.0
      
0.0
      
105418.750824
      
0.181032
      
13802
      
12
    
    

      
2016
      
32391.098485
      
45647.201628
      
NaN
      
6108.227500
      
5113.362500
      
0.0
      
0.0
      
89259.890113
      
-0.049376
      
12017
      
12
    
    

      
2015
      
38017.419167
      
47128.677258
      
NaN
      
4593.016167
      
4156.942333
      
0.0
      
0.0
      
93896.054925
      
-0.001391
      
12345
      
12
    
    

      
2014
      
35843.822155
      
48967.142581
      
NaN
      
4840.116833
      
4375.768167
      
0.0
      
0.0
      
94026.849736
      
-0.258960
      
12531
      
12
    
    

      
2013
      
36961.451595
      
79970.329194
      
NaN
      
5169.324500
      
4783.854500
      
0.0
      
0.0
      
126884.959788
      
-0.137186
      
14939
      
12
    
    

      
2012
      
41994.697841
      
94490.242371
      
NaN
      
5484.028148
      
5090.420833
      
0.0
      
0.0
      
147059.389193
      
-0.093497
      
13960
      
12
    
    

      
2011
      
35138.080909
      
117477.010810
      
NaN
      
4971.323638
      
4640.693631
      
0.0
      
0.0
      
162227.108988
      
0.140732
      
13667
      
12
    
    

      
2010
      
32688.293030
      
99742.203356
      
NaN
      
5074.845929
      
4707.815036
      
0.0
      
0.0
      
142213.157351
      
-0.026551
      
13119
      
12
    
    

      
2009
      
30307.350563
      
100896.902333
      
3592.6
      
5823.032433
      
5472.173588
      
0.0
      
0.0
      
146092.058918
      
2.338382
      
14471
      
12
    
    

      
2008
      
33274.530938
      
NaN
      
NaN
      
5401.766996
      
5085.043697
      
0.0
      
0.0
      
43761.341631
      
0.392320
      
13382
      
12
    
    

      
2007
      
24319.055469
      
NaN
      
NaN
      
3886.897857
      
3224.555714
      
0.0
      
0.0
      
31430.509040
      
33.076738
      
14075
      
12
    
  

In [54]:

    

# Standardize column names
df2.columns = [bu.change_name(col) for col in df2.columns]
df2


    

    
Out[54]:







  

    

      

      
electricity
      
natural_gas
      
fuel_oil
      
sewer
      
water
      
district_heat
      
refuse
      
total
      
pct_change
      
hdd
      
month_count
    
    

      
fiscal_year
      

      

      

      

      

      

      

      

      

      

      

    
  
  

    

      
2017
      
54161.057879
      
41955.989017
      
NaN
      
4978.546250
      
4323.157679
      
0.0
      
0.0
      
105418.750824
      
0.181032
      
13802
      
12
    
    

      
2016
      
32391.098485
      
45647.201628
      
NaN
      
6108.227500
      
5113.362500
      
0.0
      
0.0
      
89259.890113
      
-0.049376
      
12017
      
12
    
    

      
2015
      
38017.419167
      
47128.677258
      
NaN
      
4593.016167
      
4156.942333
      
0.0
      
0.0
      
93896.054925
      
-0.001391
      
12345
      
12
    
    

      
2014
      
35843.822155
      
48967.142581
      
NaN
      
4840.116833
      
4375.768167
      
0.0
      
0.0
      
94026.849736
      
-0.258960
      
12531
      
12
    
    

      
2013
      
36961.451595
      
79970.329194
      
NaN
      
5169.324500
      
4783.854500
      
0.0
      
0.0
      
126884.959788
      
-0.137186
      
14939
      
12
    
    

      
2012
      
41994.697841
      
94490.242371
      
NaN
      
5484.028148
      
5090.420833
      
0.0
      
0.0
      
147059.389193
      
-0.093497
      
13960
      
12
    
    

      
2011
      
35138.080909
      
117477.010810
      
NaN
      
4971.323638
      
4640.693631
      
0.0
      
0.0
      
162227.108988
      
0.140732
      
13667
      
12
    
    

      
2010
      
32688.293030
      
99742.203356
      
NaN
      
5074.845929
      
4707.815036
      
0.0
      
0.0
      
142213.157351
      
-0.026551
      
13119
      
12
    
    

      
2009
      
30307.350563
      
100896.902333
      
3592.6
      
5823.032433
      
5472.173588
      
0.0
      
0.0
      
146092.058918
      
2.338382
      
14471
      
12
    
    

      
2008
      
33274.530938
      
NaN
      
NaN
      
5401.766996
      
5085.043697
      
0.0
      
0.0
      
43761.341631
      
0.392320
      
13382
      
12
    
    

      
2007
      
24319.055469
      
NaN
      
NaN
      
3886.897857
      
3224.555714
      
0.0
      
0.0
      
31430.509040
      
33.076738
      
14075
      
12
    
  

In [55]:

    

# Reset the index so the fiscal year column can be passed to the graphing utility
reset_df2 = df2.reset_index()

g1_fn, g1_url = gu.graph_filename_url(site, 'util_cost_ovw_g1')

utility_list = ['electricity', 'natural_gas', 'fuel_oil', 'sewer', 'water', 'refuse', 'district_heat']

gu.area_cost_distribution(reset_df2, 'fiscal_year', utility_list, g1_fn);


    

In [56]:

    

g2_fn, g2_url = gu.graph_filename_url(site, 'util_cost_ovw_g2')

#gu.create_stacked_bar(reset_df2, 'fiscal_year', utility_list, g2_fn)


    

In [57]:

    

# Export the rows of the DataFrame into a list of dictionaries for use
# in the template.
row_dicts = bu.df_to_dictionaries(df2)
row_dicts


    

    
Out[57]:





[{'district_heat': 0.0,
  'electricity': 54161.057878787855,
  'fiscal_year': 2017,
  'fuel_oil': nan,
  'hdd': 13802.0,
  'month_count': 12.0,
  'natural_gas': 41955.989017137101,
  'pct_change': 0.18103159987302431,
  'refuse': 0.0,
  'sewer': 4978.5462500000003,
  'total': 105418.75082449638,
  'water': 4323.1576785714287},
 {'district_heat': 0.0,
  'electricity': 32391.098484848484,
  'fiscal_year': 2016,
  'fuel_oil': nan,
  'hdd': 12017.0,
  'month_count': 12.0,
  'natural_gas': 45647.201628024202,
  'pct_change': -0.049375501617985096,
  'refuse': 0.0,
  'sewer': 6108.2274999999991,
  'total': 89259.890112872687,
  'water': 5113.3625000000011},
 {'district_heat': 0.0,
  'electricity': 38017.419166666674,
  'fiscal_year': 2015,
  'fuel_oil': nan,
  'hdd': 12345.0,
  'month_count': 12.0,
  'natural_gas': 47128.677258064497,
  'pct_change': -0.0013910368310102728,
  'refuse': 0.0,
  'sewer': 4593.0161666666672,
  'total': 93896.054924731172,
  'water': 4156.9423333333343},
 {'district_heat': 0.0,
  'electricity': 35843.822155172413,
  'fiscal_year': 2014,
  'fuel_oil': nan,
  'hdd': 12531.0,
  'month_count': 12.0,
  'natural_gas': 48967.142580645166,
  'pct_change': -0.25895984920009862,
  'refuse': 0.0,
  'sewer': 4840.1168333333326,
  'total': 94026.849735817566,
  'water': 4375.7681666666667},
 {'district_heat': 0.0,
  'electricity': 36961.451594827595,
  'fiscal_year': 2013,
  'fuel_oil': nan,
  'hdd': 14939.0,
  'month_count': 12.0,
  'natural_gas': 79970.329193548387,
  'pct_change': -0.13718559226746396,
  'refuse': 0.0,
  'sewer': 5169.3245000000015,
  'total': 126884.95978837599,
  'water': 4783.8544999999995},
 {'district_heat': 0.0,
  'electricity': 41994.697840909095,
  'fiscal_year': 2012,
  'fuel_oil': nan,
  'hdd': 13960.0,
  'month_count': 12.0,
  'natural_gas': 94490.242370967753,
  'pct_change': -0.093496826080542217,
  'refuse': 0.0,
  'sewer': 5484.0281481481479,
  'total': 147059.38919335834,
  'water': 5090.4208333333336},
 {'district_heat': 0.0,
  'electricity': 35138.080909090917,
  'fiscal_year': 2011,
  'fuel_oil': nan,
  'hdd': 13667.0,
  'month_count': 12.0,
  'natural_gas': 117477.01081034481,
  'pct_change': 0.14073206734071286,
  'refuse': 0.0,
  'sewer': 4971.3236375661372,
  'total': 162227.10898795427,
  'water': 4640.6936309523808},
 {'district_heat': 0.0,
  'electricity': 32688.29303030303,
  'fiscal_year': 2010,
  'fuel_oil': nan,
  'hdd': 13119.0,
  'month_count': 12.0,
  'natural_gas': 99742.203356321814,
  'pct_change': -0.026551077420717273,
  'refuse': 0.0,
  'sewer': 5074.8459285714289,
  'total': 142213.15735091054,
  'water': 4707.8150357142868},
 {'district_heat': 0.0,
  'electricity': 30307.350563446973,
  'fiscal_year': 2009,
  'fuel_oil': 3592.6000000000004,
  'hdd': 14471.0,
  'month_count': 12.0,
  'natural_gas': 100896.90233333336,
  'pct_change': 2.3383816280221699,
  'refuse': 0.0,
  'sewer': 5823.0324327731087,
  'total': 146092.05891778873,
  'water': 5472.173588235295},
 {'district_heat': 0.0,
  'electricity': 33274.530937500007,
  'fiscal_year': 2008,
  'fuel_oil': nan,
  'hdd': 13382.0,
  'month_count': 12.0,
  'natural_gas': nan,
  'pct_change': 0.39232048627770788,
  'refuse': 0.0,
  'sewer': 5401.7669957983189,
  'total': 43761.341630777315,
  'water': 5085.0436974789909},
 {'district_heat': 0.0,
  'electricity': 24319.055468749997,
  'fiscal_year': 2007,
  'fuel_oil': nan,
  'hdd': 14075.0,
  'month_count': 12.0,
  'natural_gas': nan,
  'pct_change': 33.076738140477332,
  'refuse': 0.0,
  'sewer': 3886.897857142857,
  'total': 31430.509040178567,
  'water': 3224.5557142857142}]

In [58]:

    

# Put results into the final dictionary that will be passed to the Template.
# This assumes you have created a couple graphs with the file names shown below.
template_data['utility_cost_overview'] = dict(
    graphs=[g1_url, g2_url],
    table={'rows': row_dicts},
)
template_data


    

    
Out[58]:





{'building_info': {'bldg': {'acct_elec': '31850',
   'acct_nat_gas': '10282  (2408 DAVIS)',
   'acct_oil': '25465',
   'acct_refuse': '',
   'acct_sewer': '1311001100',
   'acct_steam': '',
   'acct_water': '1311001100',
   'address': '2408 Davis Road',
   'city': 'Fairbanks',
   'dd_site': 'PAFA',
   'onsite_gen': nan,
   'primary_func': 'Animal Shelter',
   'site_category': 'Other',
   'site_id': 'ANSBG1',
   'site_name': 'FNSB-Animal Control',
   'source_elec': 'Golden Valley Electric',
   'source_nat_gas': 'Fairbanks Natural Gas',
   'source_oil': 'Sourdough Fuel  (Petro Star)',
   'source_refuse': '',
   'source_sewer': 'Golden Heart Utilities',
   'source_steam': '',
   'source_water': 'Golden Heart Utilities',
   'sq_ft': 6718.0,
   'year_built': nan},
  'date_updated': 'October 17, 2017 03:15 PM'},
 'energy_index_comparison': {'details_table': {'all': {'district_heat_cost': 639300.85440784309,
    'district_heat_mmbtu': 37558.0730729114,
    'eci': 2.439388445990891,
    'electricity_cost': 6223574.8791972268,
    'electricity_kw_average': 2586.1100328687585,
    'electricity_kw_max': 14337.74838526623,
    'electricity_kwh': 29113512.124528244,
    'electricity_mmbtu': 99335.303368890352,
    'eui': 64.900457556520806,
    'fuel_oil_cost': 582353.99678922666,
    'fuel_oil_mmbtu': 42229.404216859388,
    'hdd_eui': 2.5543331314934399,
    'natural_gas_cost': 558494.1944657258,
    'natural_gas_mmbtu': 30258.488525201614,
    'sq_ft': 3132384.0,
    'total_cost': 8819776.9245495666,
    'total_energy_cost': 8003723.9248600267,
    'total_heat_cost': 1780149.0456627952,
    'total_heat_mmbtu': 110045.96581497238,
    'total_mmbtu': 209381.26918386272},
   'bldg': {'degree_days': 13802.0,
    'district_heat_cost': nan,
    'district_heat_mmbtu': nan,
    'eci': 14.30739013038478,
    'electricity_cost': 54161.057878787855,
    'electricity_kw_average': nan,
    'electricity_kw_max': nan,
    'electricity_kwh': 258014.54545454547,
    'electricity_mmbtu': 880.34562909090914,
    'eui': 445.46670536938882,
    'fuel_oil_cost': nan,
    'fuel_oil_mmbtu': nan,
    'hdd_eui': 22.781038304511632,
    'natural_gas_cost': 41955.989017137101,
    'natural_gas_mmbtu': 2112.299697580645,
    'sq_ft': 6718.0,
    'total_cost': 105418.75082449635,
    'total_energy_cost': 96117.046895924956,
    'total_heat_cost': 41955.989017137101,
    'total_heat_mmbtu': 2112.2996975806454,
    'total_mmbtu': 2992.6453266715544},
   'fiscal_year': 'FY 2017',
   'pct': {'degree_days': nan,
    'district_heat_cost': nan,
    'district_heat_mmbtu': nan,
    'eci': 5.865154503744094,
    'electricity_cost': 0.0087025638688505731,
    'electricity_kw_average': nan,
    'electricity_kw_max': nan,
    'electricity_kwh': 0.0088623641266959083,
    'electricity_mmbtu': 0.0088623641266959083,
    'eui': 6.8638453739935308,
    'fuel_oil_cost': nan,
    'fuel_oil_mmbtu': nan,
    'hdd_eui': 8.9185854513785614,
    'natural_gas_cost': 0.075123411188317904,
    'natural_gas_mmbtu': 0.069808500045247079,
    'sq_ft': 0.0021446923493415879,
    'total_cost': 0.011952541626202205,
    'total_energy_cost': 0.012009040766308777,
    'total_heat_cost': 0.023568806847583827,
    'total_heat_mmbtu': 0.019194703612599444,
    'total_mmbtu': 0.014292803450549537},
   'rank': {'degree_days': 40.0,
    'district_heat_cost': nan,
    'district_heat_mmbtu': nan,
    'eci': 42.0,
    'electricity_cost': 40.0,
    'electricity_kw_average': nan,
    'electricity_kw_max': nan,
    'electricity_kwh': 38.0,
    'electricity_mmbtu': 38.0,
    'eui': 38.0,
    'fuel_oil_cost': nan,
    'fuel_oil_mmbtu': nan,
    'hdd_eui': 3.0,
    'natural_gas_cost': 7.0,
    'natural_gas_mmbtu': 8.0,
    'sq_ft': 53.0,
    'total_cost': 32.0,
    'total_energy_cost': 31.0,
    'total_heat_cost': 17.0,
    'total_heat_mmbtu': 17.0,
    'total_mmbtu': 22.0}},
  'yearly_table': {'rows': [{'cost': 24319.055468749997,
     'eci': 3.6199844401235484,
     'eui': 93.857933908901472,
     'fiscal_year': 2007,
     'hdd': 14075.0,
     'heat_mmbtu': 0.0,
     'mmbtu': 630.53760000000011,
     'month_count': 12.0,
     'specific_eui': 0.0},
    {'cost': 33274.530937500007,
     'eci': 4.9530412232063128,
     'eui': 105.52614985858885,
     'fiscal_year': 2008,
     'hdd': 13382.0,
     'heat_mmbtu': 0.0,
     'mmbtu': 708.92467474999989,
     'month_count': 12.0,
     'specific_eui': 0.0},
    {'cost': 134796.8528967803,
     'eci': 20.065027224885426,
     'eui': 802.85036311875831,
     'fiscal_year': 2009,
     'hdd': 14471.0,
     'heat_mmbtu': 4736.7620999999999,
     'mmbtu': 5393.5487394318179,
     'month_count': 12.0,
     'specific_eui': 48.724010729983647},
    {'cost': 132430.49638662484,
     'eci': 19.712786005749457,
     'eui': 752.86090078495897,
     'fiscal_year': 2010,
     'hdd': 13119.0,
     'heat_mmbtu': 4416.0215896551726,
     'mmbtu': 5057.7195314733544,
     'month_count': 12.0,
     'specific_eui': 50.106083337300866},
    {'cost': 152615.09171943573,
     'eci': 22.717340238082127,
     'eui': 873.35224409040973,
     'fiscal_year': 2011,
     'hdd': 13667.0,
     'heat_mmbtu': 5203.0935103448273,
     'mmbtu': 5867.180375799373,
     'month_count': 12.0,
     'specific_eui': 56.669376869424966},
    {'cost': 136484.94021187679,
     'eci': 20.316305479588685,
     'eui': 724.41503787205818,
     'fiscal_year': 2012,
     'hdd': 13960.0,
     'heat_mmbtu': 4183.5823161290327,
     'mmbtu': 4866.6202244244869,
     'month_count': 12.0,
     'specific_eui': 44.609042423436598},
    {'cost': 116931.78078837601,
     'eci': 17.405742897942247,
     'eui': 617.1585567899283,
     'fiscal_year': 2013,
     'hdd': 14939.0,
     'heat_mmbtu': 3545.5216451612896,
     'mmbtu': 4146.071184514738,
     'month_count': 12.0,
     'specific_eui': 35.327964417222773},
    {'cost': 84810.964735817572,
     'eci': 12.624436548945754,
     'eui': 413.84795517959998,
     'fiscal_year': 2014,
     'hdd': 12531.0,
     'heat_mmbtu': 2163.8790000000008,
     'mmbtu': 2780.2305628965523,
     'month_count': 12.0,
     'specific_eui': 25.704386494521287},
    {'cost': 85146.096424731208,
     'eci': 12.67432218290134,
     'eui': 408.9628768546707,
     'fiscal_year': 2015,
     'hdd': 12345.0,
     'heat_mmbtu': 2082.8778387096777,
     'mmbtu': 2747.4126067096777,
     'month_count': 12.0,
     'specific_eui': 25.114972412420443},
    {'cost': 78038.30011287269,
     'eci': 11.616299510698525,
     'eui': 408.88360214078864,
     'fiscal_year': 2016,
     'hdd': 12017.0,
     'heat_mmbtu': 2151.6836250000001,
     'mmbtu': 2746.8800391818181,
     'month_count': 12.0,
     'specific_eui': 26.652770153961953},
    {'cost': 96117.046895924927,
     'eci': 14.307390130384777,
     'eui': 445.46670536938893,
     'fiscal_year': 2017,
     'hdd': 13802.0,
     'heat_mmbtu': 2112.2996975806454,
     'mmbtu': 2992.6453266715548,
     'month_count': 12.0,
     'specific_eui': 22.781038304511629}]}},
 'utility_cost_overview': {'graphs': ['images/ANSBG1_util_cost_ovw_g1.png',
   'images/ANSBG1_util_cost_ovw_g2.png'],
  'table': {'rows': [{'district_heat': 0.0,
     'electricity': 54161.057878787855,
     'fiscal_year': 2017,
     'fuel_oil': nan,
     'hdd': 13802.0,
     'month_count': 12.0,
     'natural_gas': 41955.989017137101,
     'pct_change': 0.18103159987302431,
     'refuse': 0.0,
     'sewer': 4978.5462500000003,
     'total': 105418.75082449638,
     'water': 4323.1576785714287},
    {'district_heat': 0.0,
     'electricity': 32391.098484848484,
     'fiscal_year': 2016,
     'fuel_oil': nan,
     'hdd': 12017.0,
     'month_count': 12.0,
     'natural_gas': 45647.201628024202,
     'pct_change': -0.049375501617985096,
     'refuse': 0.0,
     'sewer': 6108.2274999999991,
     'total': 89259.890112872687,
     'water': 5113.3625000000011},
    {'district_heat': 0.0,
     'electricity': 38017.419166666674,
     'fiscal_year': 2015,
     'fuel_oil': nan,
     'hdd': 12345.0,
     'month_count': 12.0,
     'natural_gas': 47128.677258064497,
     'pct_change': -0.0013910368310102728,
     'refuse': 0.0,
     'sewer': 4593.0161666666672,
     'total': 93896.054924731172,
     'water': 4156.9423333333343},
    {'district_heat': 0.0,
     'electricity': 35843.822155172413,
     'fiscal_year': 2014,
     'fuel_oil': nan,
     'hdd': 12531.0,
     'month_count': 12.0,
     'natural_gas': 48967.142580645166,
     'pct_change': -0.25895984920009862,
     'refuse': 0.0,
     'sewer': 4840.1168333333326,
     'total': 94026.849735817566,
     'water': 4375.7681666666667},
    {'district_heat': 0.0,
     'electricity': 36961.451594827595,
     'fiscal_year': 2013,
     'fuel_oil': nan,
     'hdd': 14939.0,
     'month_count': 12.0,
     'natural_gas': 79970.329193548387,
     'pct_change': -0.13718559226746396,
     'refuse': 0.0,
     'sewer': 5169.3245000000015,
     'total': 126884.95978837599,
     'water': 4783.8544999999995},
    {'district_heat': 0.0,
     'electricity': 41994.697840909095,
     'fiscal_year': 2012,
     'fuel_oil': nan,
     'hdd': 13960.0,
     'month_count': 12.0,
     'natural_gas': 94490.242370967753,
     'pct_change': -0.093496826080542217,
     'refuse': 0.0,
     'sewer': 5484.0281481481479,
     'total': 147059.38919335834,
     'water': 5090.4208333333336},
    {'district_heat': 0.0,
     'electricity': 35138.080909090917,
     'fiscal_year': 2011,
     'fuel_oil': nan,
     'hdd': 13667.0,
     'month_count': 12.0,
     'natural_gas': 117477.01081034481,
     'pct_change': 0.14073206734071286,
     'refuse': 0.0,
     'sewer': 4971.3236375661372,
     'total': 162227.10898795427,
     'water': 4640.6936309523808},
    {'district_heat': 0.0,
     'electricity': 32688.29303030303,
     'fiscal_year': 2010,
     'fuel_oil': nan,
     'hdd': 13119.0,
     'month_count': 12.0,
     'natural_gas': 99742.203356321814,
     'pct_change': -0.026551077420717273,
     'refuse': 0.0,
     'sewer': 5074.8459285714289,
     'total': 142213.15735091054,
     'water': 4707.8150357142868},
    {'district_heat': 0.0,
     'electricity': 30307.350563446973,
     'fiscal_year': 2009,
     'fuel_oil': 3592.6000000000004,
     'hdd': 14471.0,
     'month_count': 12.0,
     'natural_gas': 100896.90233333336,
     'pct_change': 2.3383816280221699,
     'refuse': 0.0,
     'sewer': 5823.0324327731087,
     'total': 146092.05891778873,
     'water': 5472.173588235295},
    {'district_heat': 0.0,
     'electricity': 33274.530937500007,
     'fiscal_year': 2008,
     'fuel_oil': nan,
     'hdd': 13382.0,
     'month_count': 12.0,
     'natural_gas': nan,
     'pct_change': 0.39232048627770788,
     'refuse': 0.0,
     'sewer': 5401.7669957983189,
     'total': 43761.341630777315,
     'water': 5085.0436974789909},
    {'district_heat': 0.0,
     'electricity': 24319.055468749997,
     'fiscal_year': 2007,
     'fuel_oil': nan,
     'hdd': 14075.0,
     'month_count': 12.0,
     'natural_gas': nan,
     'pct_change': 33.076738140477332,
     'refuse': 0.0,
     'sewer': 3886.897857142857,
     'total': 31430.509040178567,
     'water': 3224.5557142857142}]}}}

In [59]:

    

# Convert the notebook to a script.
#!jupyter nbconvert --to script alan_report_pages.ipynb