Shows how to Use the Data Alan Created and Use Utility Functions


In [1]:
import pickle
from collections import namedtuple
import pandas as pd
import numpy as np
from importlib import reload
import sys

# 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

Unpickle the Dataframe for Preprocessed Utility Data & Make Utility Function Object


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(bench_util)     

# 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 = bench_util.Util(df_raw, '../data/Other_Building_Data.xlsx')

Temporary testing section


In [13]:
df.query('site_id=="ASLC21" and fiscal_year==2017')


Out[13]:
site_id service_type cal_year cal_mo item_desc units cost days_served usage fiscal_year fiscal_mo mmbtu
32191 ASLC21 Sewer 2016 7 Other Charge - 59.200312 31.0 NaN 2017 1 NaN
32192 ASLC21 Sewer 2016 7 Sewer Usage (Gallons) Gallons 511.308437 31.0 42502.890625 2017 1 0.0
32193 ASLC21 Sewer 2016 8 Other Charge - 53.054253 31.0 NaN 2017 2 NaN
32194 ASLC21 Sewer 2016 8 Sewer Usage (Gallons) Gallons 386.662535 31.0 32142.164931 2017 2 0.0
32195 ASLC21 Sewer 2016 9 Other Charge - 9.902778 30.0 NaN 2017 3 NaN
32196 ASLC21 Sewer 2016 9 Sewer Usage (Gallons) - 0.000000 17.5 0.000000 2017 3 0.0
32197 ASLC21 Sewer 2016 9 Sewer Usage (Gallons) Gallons 129.027778 12.5 10725.694444 2017 3 0.0
32198 ASLC21 Sewer 2016 10 Other Charge - 0.000000 31.0 NaN 2017 4 NaN
32199 ASLC21 Sewer 2016 10 Sewer Usage (Gallons) - 0.000000 31.0 0.000000 2017 4 0.0
32200 ASLC21 Sewer 2016 11 Other Charge - 0.000000 30.0 NaN 2017 5 NaN
32201 ASLC21 Sewer 2016 11 Sewer Usage (Gallons) - 0.000000 30.0 0.000000 2017 5 0.0
32202 ASLC21 Sewer 2016 12 Other Charge - 0.000000 31.0 NaN 2017 6 NaN
32203 ASLC21 Sewer 2016 12 Sewer Usage (Gallons) - 0.000000 31.0 0.000000 2017 6 0.0
32204 ASLC21 Sewer 2017 1 Other Charge - 0.000000 31.0 NaN 2017 7 NaN
32205 ASLC21 Sewer 2017 1 Sewer Usage (Gallons) - 0.000000 31.0 0.000000 2017 7 0.0
32206 ASLC21 Sewer 2017 2 Other Charge - 0.000000 28.0 NaN 2017 8 NaN
32207 ASLC21 Sewer 2017 2 Sewer Usage (Gallons) - 0.000000 28.0 0.000000 2017 8 0.0
32208 ASLC21 Sewer 2017 3 Other Charge - 0.000000 31.0 NaN 2017 9 NaN
32209 ASLC21 Sewer 2017 3 Sewer Usage (Gallons) - 0.000000 31.0 0.000000 2017 9 0.0
32210 ASLC21 Sewer 2017 4 Other Charge - 0.000000 30.0 NaN 2017 10 NaN
32211 ASLC21 Sewer 2017 4 Sewer Usage (Gallons) - 0.000000 30.0 0.000000 2017 10 0.0
32212 ASLC21 Sewer 2017 5 Other Charge - 0.000000 31.0 NaN 2017 11 NaN
32213 ASLC21 Sewer 2017 5 Sewer Usage (Gallons) - 0.000000 31.0 0.000000 2017 11 0.0
32214 ASLC21 Sewer 2017 6 Other Charge - 127.158750 30.0 NaN 2017 12 NaN
32215 ASLC21 Sewer 2017 6 Sewer Usage (Gallons) - 0.000000 6.5 0.000000 2017 12 0.0
32216 ASLC21 Sewer 2017 6 Sewer Usage (Gallons) Gallons 899.671786 23.5 74785.964286 2017 12 0.0
32385 ASLC21 Water 2016 7 Other Charge - 37.670000 31.0 NaN 2017 1 NaN
32386 ASLC21 Water 2016 7 Water Usage (Gallons) Gallons 423.753438 31.0 42502.890625 2017 1 0.0
32387 ASLC21 Water 2016 8 Other Charge - 34.159878 31.0 NaN 2017 2 NaN
32388 ASLC21 Water 2016 8 Water Usage (Gallons) Gallons 320.452344 31.0 32142.164931 2017 2 0.0
32389 ASLC21 Water 2016 9 Other Charge - 6.152778 30.0 NaN 2017 3 NaN
32390 ASLC21 Water 2016 9 Water Usage (Gallons) - 0.000000 17.5 0.000000 2017 3 0.0
32391 ASLC21 Water 2016 9 Water Usage (Gallons) Gallons 106.937500 12.5 10725.694444 2017 3 0.0
32392 ASLC21 Water 2016 10 Other Charge - 0.000000 31.0 NaN 2017 4 NaN
32393 ASLC21 Water 2016 10 Water Usage (Gallons) - 0.000000 31.0 0.000000 2017 4 0.0
32394 ASLC21 Water 2016 11 Other Charge - 0.000000 30.0 NaN 2017 5 NaN
32395 ASLC21 Water 2016 11 Water Usage (Gallons) - 0.000000 30.0 0.000000 2017 5 0.0
32396 ASLC21 Water 2016 12 Other Charge - 0.000000 31.0 NaN 2017 6 NaN
32397 ASLC21 Water 2016 12 Water Usage (Gallons) - 0.000000 31.0 0.000000 2017 6 0.0
32398 ASLC21 Water 2017 1 Other Charge - 0.000000 31.0 NaN 2017 7 NaN
32399 ASLC21 Water 2017 1 Water Usage (Gallons) - 0.000000 31.0 0.000000 2017 7 0.0
32400 ASLC21 Water 2017 2 Other Charge - 0.000000 28.0 NaN 2017 8 NaN
32401 ASLC21 Water 2017 2 Water Usage (Gallons) - 0.000000 28.0 0.000000 2017 8 0.0
32402 ASLC21 Water 2017 3 Other Charge - 0.000000 31.0 NaN 2017 9 NaN
32403 ASLC21 Water 2017 3 Water Usage (Gallons) - 0.000000 31.0 0.000000 2017 9 0.0
32404 ASLC21 Water 2017 4 Other Charge - 0.000000 30.0 NaN 2017 10 NaN
32405 ASLC21 Water 2017 4 Water Usage (Gallons) - 0.000000 30.0 0.000000 2017 10 0.0
32406 ASLC21 Water 2017 5 Other Charge - 0.000000 31.0 NaN 2017 11 NaN
32407 ASLC21 Water 2017 5 Water Usage (Gallons) - 0.000000 31.0 0.000000 2017 11 0.0
32408 ASLC21 Water 2017 6 Other Charge - 55.688929 30.0 NaN 2017 12 NaN
32409 ASLC21 Water 2017 6 Water Usage (Gallons) - 0.000000 6.5 0.000000 2017 12 0.0
32410 ASLC21 Water 2017 6 Water Usage (Gallons) Gallons 745.618393 23.5 74785.964286 2017 12 0.0

Make Function for adding missing columns


In [14]:
def add_missing_columns(df_in, required_columns, fill_val=0.0):
    """Adds columns to the DataFrame 'df' if it does not contain all of the
    columns in the list 'required_columns'.  'fill_val' is the value that is used
    to fill the new columns.
    """
    missing_cols = set(required_columns) - set(df_in.columns)
    for col in missing_cols:
        df_in[col] = fill_val
    
    return df_in

In [15]:
df_test = df.query('site_id == "ASLC47"')
df_test = pd.pivot_table(df_test, index='fiscal_year', columns='service_type', values='cost', aggfunc=np.sum)
df_test


Out[15]:
service_type Oil #1
fiscal_year
2006 481.194375
2007 1758.729770
2008 2143.548269
2009 2024.759272
2010 1606.976890
2011 3129.608622
2012 3034.134426
2013 3075.789030
2014 2333.371572
2015 1636.574890
2016 1190.271430
2017 997.141453

In [16]:
add_missing_columns(df_test, ['Electricity', 'Water'])


Out[16]:
service_type Oil #1 Electricity Water
fiscal_year
2006 481.194375 0.0 0.0
2007 1758.729770 0.0 0.0
2008 2143.548269 0.0 0.0
2009 2024.759272 0.0 0.0
2010 1606.976890 0.0 0.0
2011 3129.608622 0.0 0.0
2012 3034.134426 0.0 0.0
2013 3075.789030 0.0 0.0
2014 2333.371572 0.0 0.0
2015 1636.574890 0.0 0.0
2016 1190.271430 0.0 0.0
2017 997.141453 0.0 0.0

In [92]:
# Check Totals
print(df_raw.Cost.sum(), df.cost.sum())
print(df_raw.Usage.sum(), df.usage.sum())


109128189.09000163 109128189.08999836
958777495.4999995 958777495.5000011

DataFrame for "Utility Cost Overview" Report Page


In [68]:
site = 'ANSBG1'
# 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[68]:
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 [69]:
df2 = pd.pivot_table(
    df1, 
    values='cost', 
    index=['fiscal_year'], 
    columns=['service_type'],
    aggfunc=np.sum
)
df2


Out[69]:
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 [70]:
# Add in columns for the missing services
missing_services = bench_util.missing_services(df2.columns)
bench_util.add_columns(df2, missing_services)
df2


Out[70]:
service_type Electricity Natural Gas Oil #1 Sewer Water Refuse Steam
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 [71]:
# Add a Total column that sums the other columns
df2['Total'] = df2.sum(axis=1)
df2


Out[71]:
service_type Electricity Natural Gas Oil #1 Sewer Water Refuse Steam 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 [72]:
# Add a percent change column
df2['pct_change'] = df2.Total.pct_change()
df2


Out[72]:
service_type Electricity Natural Gas Oil #1 Sewer Water Refuse Steam 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 [73]:
# Add in degree days
months_present = bench_util.months_present(df1)
deg_days = ut.degree_days_yearly(months_present, site)
df2['hdd'] = deg_days
df2


Out[73]:
service_type Electricity Natural Gas Oil #1 Sewer Water Refuse Steam Total pct_change hdd
fiscal_year
2006 922.345000 NaN NaN NaN NaN 0.0 0.0 922.345000 NaN NaN
2007 24319.055469 NaN NaN 3886.897857 3224.555714 0.0 0.0 31430.509040 33.076738 NaN
2008 33274.530938 NaN NaN 5401.766996 5085.043697 0.0 0.0 43761.341631 0.392320 13802.0
2009 30307.350563 100896.902333 3592.6 5823.032433 5472.173588 0.0 0.0 146092.058918 2.338382 12393.0
2010 32688.293030 99742.203356 NaN 5074.845929 4707.815036 0.0 0.0 142213.157351 -0.026551 12171.0
2011 35138.080909 117477.010810 NaN 4971.323638 4640.693631 0.0 0.0 162227.108988 0.140732 13802.0
2012 41994.697841 94490.242371 NaN 5484.028148 5090.420833 0.0 0.0 147059.389193 -0.093497 12393.0
2013 36961.451595 79970.329194 NaN 5169.324500 4783.854500 0.0 0.0 126884.959788 -0.137186 12171.0
2014 35843.822155 48967.142581 NaN 4840.116833 4375.768167 0.0 0.0 94026.849736 -0.258960 13802.0
2015 38017.419167 47128.677258 NaN 4593.016167 4156.942333 0.0 0.0 93896.054925 -0.001391 12393.0
2016 32391.098485 45647.201628 NaN 6108.227500 5113.362500 0.0 0.0 89259.890113 -0.049376 12171.0
2017 54161.057879 41955.989017 NaN 4978.546250 4323.157679 0.0 0.0 105418.750824 0.181032 13802.0
2018 7256.446970 3779.601452 NaN 1236.853750 1046.692321 0.0 0.0 13319.594493 -0.873651 NaN

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


Out[74]:
service_type Electricity Natural Gas Oil #1 Sewer Water Refuse Steam Total pct_change hdd month_count
fiscal_year
2006 922.345000 NaN NaN NaN NaN 0.0 0.0 922.345000 NaN NaN 1
2007 24319.055469 NaN NaN 3886.897857 3224.555714 0.0 0.0 31430.509040 33.076738 NaN 12
2008 33274.530938 NaN NaN 5401.766996 5085.043697 0.0 0.0 43761.341631 0.392320 13802.0 12
2009 30307.350563 100896.902333 3592.6 5823.032433 5472.173588 0.0 0.0 146092.058918 2.338382 12393.0 12
2010 32688.293030 99742.203356 NaN 5074.845929 4707.815036 0.0 0.0 142213.157351 -0.026551 12171.0 12
2011 35138.080909 117477.010810 NaN 4971.323638 4640.693631 0.0 0.0 162227.108988 0.140732 13802.0 12
2012 41994.697841 94490.242371 NaN 5484.028148 5090.420833 0.0 0.0 147059.389193 -0.093497 12393.0 12
2013 36961.451595 79970.329194 NaN 5169.324500 4783.854500 0.0 0.0 126884.959788 -0.137186 12171.0 12
2014 35843.822155 48967.142581 NaN 4840.116833 4375.768167 0.0 0.0 94026.849736 -0.258960 13802.0 12
2015 38017.419167 47128.677258 NaN 4593.016167 4156.942333 0.0 0.0 93896.054925 -0.001391 12393.0 12
2016 32391.098485 45647.201628 NaN 6108.227500 5113.362500 0.0 0.0 89259.890113 -0.049376 12171.0 12
2017 54161.057879 41955.989017 NaN 4978.546250 4323.157679 0.0 0.0 105418.750824 0.181032 13802.0 12
2018 7256.446970 3779.601452 NaN 1236.853750 1046.692321 0.0 0.0 13319.594493 -0.873651 NaN 3

In [75]:
# Reverse the DataFrame
df2.sort_index(ascending=False, inplace=True)
df2


Out[75]:
service_type Electricity Natural Gas Oil #1 Sewer Water Refuse Steam Total pct_change hdd month_count
fiscal_year
2018 7256.446970 3779.601452 NaN 1236.853750 1046.692321 0.0 0.0 13319.594493 -0.873651 NaN 3
2017 54161.057879 41955.989017 NaN 4978.546250 4323.157679 0.0 0.0 105418.750824 0.181032 13802.0 12
2016 32391.098485 45647.201628 NaN 6108.227500 5113.362500 0.0 0.0 89259.890113 -0.049376 12171.0 12
2015 38017.419167 47128.677258 NaN 4593.016167 4156.942333 0.0 0.0 93896.054925 -0.001391 12393.0 12
2014 35843.822155 48967.142581 NaN 4840.116833 4375.768167 0.0 0.0 94026.849736 -0.258960 13802.0 12
2013 36961.451595 79970.329194 NaN 5169.324500 4783.854500 0.0 0.0 126884.959788 -0.137186 12171.0 12
2012 41994.697841 94490.242371 NaN 5484.028148 5090.420833 0.0 0.0 147059.389193 -0.093497 12393.0 12
2011 35138.080909 117477.010810 NaN 4971.323638 4640.693631 0.0 0.0 162227.108988 0.140732 13802.0 12
2010 32688.293030 99742.203356 NaN 5074.845929 4707.815036 0.0 0.0 142213.157351 -0.026551 12171.0 12
2009 30307.350563 100896.902333 3592.6 5823.032433 5472.173588 0.0 0.0 146092.058918 2.338382 12393.0 12
2008 33274.530938 NaN NaN 5401.766996 5085.043697 0.0 0.0 43761.341631 0.392320 13802.0 12
2007 24319.055469 NaN NaN 3886.897857 3224.555714 0.0 0.0 31430.509040 33.076738 NaN 12
2006 922.345000 NaN NaN NaN NaN 0.0 0.0 922.345000 NaN NaN 1

In [76]:
# Export the rows of the DataFrame into a list of dictionaries for use
# in the template.  Rename columns to match template variables.
row_dicts = bench_util.df_to_dictionaries(df2, change_names={'Natural Gas': 'natural_gas', 'Oil #1': 'fuel_oil'})
row_dicts


Out[76]:
[{'Electricity': 7256.4469696969691,
  'Refuse': 0.0,
  'Sewer': 1236.85375,
  'Steam': 0.0,
  'Total': 13319.594492738443,
  'Water': 1046.6923214285714,
  'fiscal_year': 2018,
  'fuel_oil': nan,
  'hdd': nan,
  'month_count': 3.0,
  'natural_gas': 3779.601451612903,
  'pct_change': -0.87365061349557049},
 {'Electricity': 54161.057878787855,
  'Refuse': 0.0,
  'Sewer': 4978.5462500000003,
  'Steam': 0.0,
  'Total': 105418.75082449638,
  'Water': 4323.1576785714287,
  'fiscal_year': 2017,
  'fuel_oil': nan,
  'hdd': 13802.0,
  'month_count': 12.0,
  'natural_gas': 41955.989017137101,
  'pct_change': 0.18103159987302431},
 {'Electricity': 32391.098484848484,
  'Refuse': 0.0,
  'Sewer': 6108.2274999999991,
  'Steam': 0.0,
  'Total': 89259.890112872687,
  'Water': 5113.3625000000011,
  'fiscal_year': 2016,
  'fuel_oil': nan,
  'hdd': 12171.0,
  'month_count': 12.0,
  'natural_gas': 45647.201628024202,
  'pct_change': -0.049375501617985096},
 {'Electricity': 38017.419166666674,
  'Refuse': 0.0,
  'Sewer': 4593.0161666666672,
  'Steam': 0.0,
  'Total': 93896.054924731172,
  'Water': 4156.9423333333343,
  'fiscal_year': 2015,
  'fuel_oil': nan,
  'hdd': 12393.0,
  'month_count': 12.0,
  'natural_gas': 47128.677258064497,
  'pct_change': -0.0013910368310102728},
 {'Electricity': 35843.822155172413,
  'Refuse': 0.0,
  'Sewer': 4840.1168333333326,
  'Steam': 0.0,
  'Total': 94026.849735817566,
  'Water': 4375.7681666666667,
  'fiscal_year': 2014,
  'fuel_oil': nan,
  'hdd': 13802.0,
  'month_count': 12.0,
  'natural_gas': 48967.142580645166,
  'pct_change': -0.25895984920009862},
 {'Electricity': 36961.451594827595,
  'Refuse': 0.0,
  'Sewer': 5169.3245000000015,
  'Steam': 0.0,
  'Total': 126884.95978837599,
  'Water': 4783.8544999999995,
  'fiscal_year': 2013,
  'fuel_oil': nan,
  'hdd': 12171.0,
  'month_count': 12.0,
  'natural_gas': 79970.329193548387,
  'pct_change': -0.13718559226746396},
 {'Electricity': 41994.697840909095,
  'Refuse': 0.0,
  'Sewer': 5484.0281481481479,
  'Steam': 0.0,
  'Total': 147059.38919335834,
  'Water': 5090.4208333333336,
  'fiscal_year': 2012,
  'fuel_oil': nan,
  'hdd': 12393.0,
  'month_count': 12.0,
  'natural_gas': 94490.242370967753,
  'pct_change': -0.093496826080542217},
 {'Electricity': 35138.080909090917,
  'Refuse': 0.0,
  'Sewer': 4971.3236375661372,
  'Steam': 0.0,
  'Total': 162227.10898795427,
  'Water': 4640.6936309523808,
  'fiscal_year': 2011,
  'fuel_oil': nan,
  'hdd': 13802.0,
  'month_count': 12.0,
  'natural_gas': 117477.01081034481,
  'pct_change': 0.14073206734071286},
 {'Electricity': 32688.29303030303,
  'Refuse': 0.0,
  'Sewer': 5074.8459285714289,
  'Steam': 0.0,
  'Total': 142213.15735091054,
  'Water': 4707.8150357142868,
  'fiscal_year': 2010,
  'fuel_oil': nan,
  'hdd': 12171.0,
  'month_count': 12.0,
  'natural_gas': 99742.203356321814,
  'pct_change': -0.026551077420717273},
 {'Electricity': 30307.350563446973,
  'Refuse': 0.0,
  'Sewer': 5823.0324327731087,
  'Steam': 0.0,
  'Total': 146092.05891778873,
  'Water': 5472.173588235295,
  'fiscal_year': 2009,
  'fuel_oil': 3592.6000000000004,
  'hdd': 12393.0,
  'month_count': 12.0,
  'natural_gas': 100896.90233333336,
  'pct_change': 2.3383816280221699},
 {'Electricity': 33274.530937500007,
  'Refuse': 0.0,
  'Sewer': 5401.7669957983189,
  'Steam': 0.0,
  'Total': 43761.341630777315,
  'Water': 5085.0436974789909,
  'fiscal_year': 2008,
  'fuel_oil': nan,
  'hdd': 13802.0,
  'month_count': 12.0,
  'natural_gas': nan,
  'pct_change': 0.39232048627770788},
 {'Electricity': 24319.055468749997,
  'Refuse': 0.0,
  'Sewer': 3886.897857142857,
  'Steam': 0.0,
  'Total': 31430.509040178567,
  'Water': 3224.5557142857142,
  'fiscal_year': 2007,
  'fuel_oil': nan,
  'hdd': nan,
  'month_count': 12.0,
  'natural_gas': nan,
  'pct_change': 33.076738140477332},
 {'Electricity': 922.34500000000003,
  'Refuse': 0.0,
  'Sewer': nan,
  'Steam': 0.0,
  'Total': 922.34500000000003,
  'Water': nan,
  'fiscal_year': 2006,
  'fuel_oil': nan,
  'hdd': nan,
  'month_count': 1.0,
  'natural_gas': nan,
  'pct_change': nan}]

In [77]:
# Put results into the final report dictionary that will be passed to the Template.
# This assumes you have created a couple graphs with the file names shown below.
pg3 = dict(rows=row_dicts, 
           g1='output/images/{}_pg2_g1.png'.format(site), 
           g2='output/images/{}_pg2_g2.png'.format(site)
      )
pg3


Out[77]:
{'g1': 'output/images/ANSBG1_pg2_g1.png',
 'g2': 'output/images/ANSBG1_pg2_g2.png',
 'rows': [{'Electricity': 7256.4469696969691,
   'Refuse': 0.0,
   'Sewer': 1236.85375,
   'Steam': 0.0,
   'Total': 13319.594492738443,
   'Water': 1046.6923214285714,
   'fiscal_year': 2018,
   'fuel_oil': nan,
   'hdd': nan,
   'month_count': 3.0,
   'natural_gas': 3779.601451612903,
   'pct_change': -0.87365061349557049},
  {'Electricity': 54161.057878787855,
   'Refuse': 0.0,
   'Sewer': 4978.5462500000003,
   'Steam': 0.0,
   'Total': 105418.75082449638,
   'Water': 4323.1576785714287,
   'fiscal_year': 2017,
   'fuel_oil': nan,
   'hdd': 13802.0,
   'month_count': 12.0,
   'natural_gas': 41955.989017137101,
   'pct_change': 0.18103159987302431},
  {'Electricity': 32391.098484848484,
   'Refuse': 0.0,
   'Sewer': 6108.2274999999991,
   'Steam': 0.0,
   'Total': 89259.890112872687,
   'Water': 5113.3625000000011,
   'fiscal_year': 2016,
   'fuel_oil': nan,
   'hdd': 12171.0,
   'month_count': 12.0,
   'natural_gas': 45647.201628024202,
   'pct_change': -0.049375501617985096},
  {'Electricity': 38017.419166666674,
   'Refuse': 0.0,
   'Sewer': 4593.0161666666672,
   'Steam': 0.0,
   'Total': 93896.054924731172,
   'Water': 4156.9423333333343,
   'fiscal_year': 2015,
   'fuel_oil': nan,
   'hdd': 12393.0,
   'month_count': 12.0,
   'natural_gas': 47128.677258064497,
   'pct_change': -0.0013910368310102728},
  {'Electricity': 35843.822155172413,
   'Refuse': 0.0,
   'Sewer': 4840.1168333333326,
   'Steam': 0.0,
   'Total': 94026.849735817566,
   'Water': 4375.7681666666667,
   'fiscal_year': 2014,
   'fuel_oil': nan,
   'hdd': 13802.0,
   'month_count': 12.0,
   'natural_gas': 48967.142580645166,
   'pct_change': -0.25895984920009862},
  {'Electricity': 36961.451594827595,
   'Refuse': 0.0,
   'Sewer': 5169.3245000000015,
   'Steam': 0.0,
   'Total': 126884.95978837599,
   'Water': 4783.8544999999995,
   'fiscal_year': 2013,
   'fuel_oil': nan,
   'hdd': 12171.0,
   'month_count': 12.0,
   'natural_gas': 79970.329193548387,
   'pct_change': -0.13718559226746396},
  {'Electricity': 41994.697840909095,
   'Refuse': 0.0,
   'Sewer': 5484.0281481481479,
   'Steam': 0.0,
   'Total': 147059.38919335834,
   'Water': 5090.4208333333336,
   'fiscal_year': 2012,
   'fuel_oil': nan,
   'hdd': 12393.0,
   'month_count': 12.0,
   'natural_gas': 94490.242370967753,
   'pct_change': -0.093496826080542217},
  {'Electricity': 35138.080909090917,
   'Refuse': 0.0,
   'Sewer': 4971.3236375661372,
   'Steam': 0.0,
   'Total': 162227.10898795427,
   'Water': 4640.6936309523808,
   'fiscal_year': 2011,
   'fuel_oil': nan,
   'hdd': 13802.0,
   'month_count': 12.0,
   'natural_gas': 117477.01081034481,
   'pct_change': 0.14073206734071286},
  {'Electricity': 32688.29303030303,
   'Refuse': 0.0,
   'Sewer': 5074.8459285714289,
   'Steam': 0.0,
   'Total': 142213.15735091054,
   'Water': 4707.8150357142868,
   'fiscal_year': 2010,
   'fuel_oil': nan,
   'hdd': 12171.0,
   'month_count': 12.0,
   'natural_gas': 99742.203356321814,
   'pct_change': -0.026551077420717273},
  {'Electricity': 30307.350563446973,
   'Refuse': 0.0,
   'Sewer': 5823.0324327731087,
   'Steam': 0.0,
   'Total': 146092.05891778873,
   'Water': 5472.173588235295,
   'fiscal_year': 2009,
   'fuel_oil': 3592.6000000000004,
   'hdd': 12393.0,
   'month_count': 12.0,
   'natural_gas': 100896.90233333336,
   'pct_change': 2.3383816280221699},
  {'Electricity': 33274.530937500007,
   'Refuse': 0.0,
   'Sewer': 5401.7669957983189,
   'Steam': 0.0,
   'Total': 43761.341630777315,
   'Water': 5085.0436974789909,
   'fiscal_year': 2008,
   'fuel_oil': nan,
   'hdd': 13802.0,
   'month_count': 12.0,
   'natural_gas': nan,
   'pct_change': 0.39232048627770788},
  {'Electricity': 24319.055468749997,
   'Refuse': 0.0,
   'Sewer': 3886.897857142857,
   'Steam': 0.0,
   'Total': 31430.509040178567,
   'Water': 3224.5557142857142,
   'fiscal_year': 2007,
   'fuel_oil': nan,
   'hdd': nan,
   'month_count': 12.0,
   'natural_gas': nan,
   'pct_change': 33.076738140477332},
  {'Electricity': 922.34500000000003,
   'Refuse': 0.0,
   'Sewer': nan,
   'Steam': 0.0,
   'Total': 922.34500000000003,
   'Water': nan,
   'fiscal_year': 2006,
   'fuel_oil': nan,
   'hdd': nan,
   'month_count': 1.0,
   'natural_gas': nan,
   'pct_change': nan}]}

Fiscal Month Abbreviations


In [78]:
bench_util.mo_list


Out[78]:
['Jul',
 'Aug',
 'Sep',
 'Oct',
 'Nov',
 'Dec',
 'Jan',
 'Feb',
 'Mar',
 'Apr',
 'May',
 'Jun']

In [79]:
bench_util.mo_dict


Out[79]:
{1: 'Jul',
 2: 'Aug',
 3: 'Sep',
 4: 'Oct',
 5: 'Nov',
 6: 'Dec',
 7: 'Jan',
 8: 'Feb',
 9: 'Mar',
 10: 'Apr',
 11: 'May',
 12: 'Jun'}

Get Additional Information on a Building


In [80]:
# Request the building using the Site ID
bldg = ut.building_info('ANSBG1')
bldg


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

In [81]:
# Shows some fields of info about the building
bldg.site_name, bldg.sq_ft, bldg.dd_site


Out[81]:
('FNSB-Animal Control', 6718.0, 'PAFA')

In [82]:
bldg._asdict()


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

In [83]:
# Instead of building info for one building, get a DataFrame with
# all the building information
ut.building_info_df().head()


Out[83]:
acct_elec acct_nat_gas acct_oil acct_refuse acct_sewer acct_steam acct_water address city dd_site ... site_name source_elec source_nat_gas source_oil source_refuse source_sewer source_steam source_water sq_ft year_built
site_id
03 172230-5 10283 - DENALI 55010001 AW23-DNL03 1998005610 1998005610, 1998073600 NaN NaN PAFA ... SD-Denali Elementary Golden Valley Electric Fairbanks Natural Gas Sourdough Fuel (Petro Star) Alaska Waste Golden Heart Utilities Golden Heart Utilities 49210.0 NaN
04 172995-3 10283 - NORDALE 55017001 AW16-NDL04 2440271010 2440271010 NaN NaN PAFA ... SD-Nordale Elementary Golden Valley Electric Fairbanks Natural Gas Sourdough Fuel (Petro Star) Alaska Waste Golden Heart Utilities Golden Heart Utilities 49210.0 NaN
05 104270-4, 104271-2, 321383, 357683, 577004 55016001 AW17-LTH05 1918027500 08-112-0, 08-113-0 1918027500 NaN NaN PAFA ... SD-Lathrop High Golden Valley Electric Sourdough Fuel (Petro Star) Alaska Waste Golden Heart Utilities Aurora Energy Golden Heart Utilities 234412.0 NaN
06 112356-1 10283 - HUNTER 55012001 AW21-HNT06 1995007000 1995007000 NaN NaN PAFA ... SD-Hunter Elementary Golden Valley Electric Fairbanks Natural Gas Sourdough Fuel (Petro Star) Alaska Waste Golden Heart Utilities Golden Heart Utilities 57047.0 NaN
07 76471-2 10283-U-PARK 55028001 AW04-UPK07 4012700710 4012700710 NaN NaN PAFA ... SD-University Park Elementary Golden Valley Electric Fairbanks Natural Gas Sourdough Fuel (Petro Star) Alaska Waste College Utilities College Utilities 64699.0 NaN

5 rows × 23 columns


In [84]:
# Get a list of all Site IDs, sorted alphabetically
ut.all_sites()


Out[84]:
['03',
 '04',
 '05',
 '06',
 '07',
 '08',
 '09',
 '10',
 '104',
 '11',
 '12',
 '13',
 '14',
 '15',
 '15A',
 '15B',
 '16',
 '20',
 '22',
 '23',
 '27',
 '28',
 '29',
 '34',
 '36',
 '37',
 '38',
 '39',
 '40',
 '42',
 '43',
 '44',
 '45',
 '46',
 '47',
 '49',
 '76',
 'ADLER',
 'ANSBG1',
 'ASLC18',
 'ASLC21',
 'ASLC47',
 'ASLC52',
 'ASLCHU',
 'ASLCV1',
 'ASLELC1',
 'ASLELC2',
 'ASLGDM',
 'ASLGP2',
 'ASLHIS',
 'ASLPIH',
 'ASLPL1',
 'ASLSEA',
 'ASLSQD',
 'ASLTVR',
 'BALHHW',
 'BAOBG1',
 'BAP001',
 'BENBG1',
 'BHPBHG',
 'BHPBHL',
 'BHPBHW',
 'BHPCCS',
 'BHPSKI1',
 'BHPSKI2',
 'BHPSKI3',
 'BHPSKI4',
 'CACBG1',
 'CBS001',
 'CEC',
 'CLX001',
 'CLX002',
 'CLX003',
 'CLX004',
 'CLXES1',
 'CLXGP2',
 'CLXSM1',
 'CLXSO1',
 'CRB001',
 'CSP001',
 'DIPMP1',
 'DOGPRK',
 'Emer S T',
 'GF001',
 'GFP001',
 'GRP001',
 'GRPLFT',
 'GSWNP',
 'HEMBG1',
 'HEZ001',
 'HSPSWP',
 'KEP001',
 'KIP001',
 'KWP001',
 'LEABG1',
 'LF001',
 'MF001',
 'MNPPRK',
 'MSLL001',
 'MSP001',
 'MSRSWP',
 'MSWBG1',
 'MSWBG2',
 'MSWWAR',
 'MTP001',
 'NBP001',
 'NPL',
 'NPP001',
 'NRP001',
 'NWLBG1',
 'NWLP01',
 'NWP001',
 'PRW',
 'SF001',
 'SHW001',
 'STRBG1',
 'TRANS01',
 'TRANS02',
 'TRANS03',
 'TRANS04',
 'TRANS05',
 'TRANS06',
 'TRANS07',
 'TRANS08',
 'TRANS09',
 'TRANS10',
 'TRGR',
 'TRPAIR',
 'TRPBG1',
 'TRS001',
 'VMP001',
 'WF001',
 'WSPGAR',
 'WSPP01',
 'WSPSWP']

In [95]:
ut.site_categories_and_buildings()


Out[95]:
[{'name': 'Other',
  'sites': [{'id': 'ADLER', 'name': 'FNSB-Adler School building'},
   {'id': 'ANSBG1', 'name': 'FNSB-Animal Control'},
   {'id': 'BAP001', 'name': 'FNSB-Bernice Aldridge Park'},
   {'id': 'DIPMP1', 'name': 'FNSB-Big Dipper'},
   {'id': 'BHPBHG', 'name': 'FNSB-Birch Hill Garage'},
   {'id': 'BHPCCS', 'name': 'FNSB-Birch Hill Ski Building'},
   {'id': 'BHPSKI1', 'name': 'FNSB-Birch Hill Ski Trail #1'},
   {'id': 'BHPSKI2', 'name': 'FNSB-Birch Hill Ski Trail #2'},
   {'id': 'BHPSKI3', 'name': 'FNSB-Birch Hill Ski Trail #3'},
   {'id': 'BHPSKI4', 'name': 'FNSB-Birch Hill Ski Trail #4'},
   {'id': 'BHPBHL', 'name': 'FNSB-Birch Hill Timing Building'},
   {'id': 'BHPBHW', 'name': 'FNSB-Birch Hill Warm-up Building'},
   {'id': 'CLX001', 'name': 'FNSB-CLRA Chena Lakes Rec Area'},
   {'id': 'CLX002', 'name': 'FNSB-CLRA Chena Lakes Rec Park'},
   {'id': 'CLX004', 'name': 'FNSB-CLRA Chena River Lakes Proj A'},
   {'id': 'CLX003', 'name': 'FNSB-CLRA Chena River Lakes Rec Proj'},
   {'id': 'CLXES1', 'name': 'FNSB-CLRA Entrance Station'},
   {'id': 'CLXSM1', 'name': 'FNSB-CLRA Maintenance Bunker'},
   {'id': 'CLXSO1', 'name': 'FNSB-CLRA Office/Shop'},
   {'id': 'CLXGP2', 'name': 'FNSB-CLRA River Park General Area'},
   {'id': 'CACBG1', 'name': 'FNSB-Carlson Center'},
   {'id': 'CBS001', 'name': 'FNSB-Centennial Bridge South'},
   {'id': 'CRB001', 'name': 'FNSB-Chena River Bridge'},
   {'id': 'CSP001', 'name': 'FNSB-Crosson Street Park'},
   {'id': 'GSWNP', 'name': 'FNSB-General Serv Wareh North Pole'},
   {'id': 'GF001', 'name': 'FNSB-Gillam Field'},
   {'id': 'GFP001', 'name': 'FNSB-Griffin Park'},
   {'id': 'GRP001', 'name': 'FNSB-Growden Park'},
   {'id': 'GRPLFT', 'name': 'FNSB-Growden Park Lift Station'},
   {'id': 'HSPSWP', 'name': 'FNSB-Hamme Swimming Pool'},
   {'id': 'HEZ001', 'name': 'FNSB-Hez Ray Sport Complex'},
   {'id': 'BAOBG1', 'name': 'FNSB-JHAC'},
   {'id': 'KEP001', 'name': 'FNSB-Kendall Park'},
   {'id': 'KIP001', 'name': 'FNSB-Kiana Park'},
   {'id': 'KWP001', 'name': 'FNSB-Kiwanis Park'},
   {'id': 'LF001', 'name': 'FNSB-Lions Field'},
   {'id': 'MSWBG1', 'name': 'FNSB-Marika St Maintenance Shop'},
   {'id': 'MSWBG2', 'name': 'FNSB-Marika St Maintenance Storage'},
   {'id': 'MSWWAR', 'name': 'FNSB-Marika St Warehouse-Gen Svcs'},
   {'id': 'MF001', 'name': 'FNSB-Marlin Field'},
   {'id': 'MSRSWP', 'name': 'FNSB-Mary Siah Rec Center'},
   {'id': 'MTP001', 'name': 'FNSB-Mertyle Thomas Park'},
   {'id': 'MSP001', 'name': 'FNSB-Midnight Sun Lions Park'},
   {'id': 'MSLL001', 'name': 'FNSB-Moore Street Little League'},
   {'id': 'MNPPRK', 'name': 'FNSB-Morning Star Park'},
   {'id': 'NWP001', 'name': 'FNSB-Newby Park'},
   {'id': 'NRP001', 'name': 'FNSB-Newby Road Park'},
   {'id': 'NWLP01', 'name': 'FNSB-Noel Wein Library Park'},
   {'id': 'NWLBG1', 'name': 'FNSB-Noel Wien Library'},
   {'id': 'NPL', 'name': 'FNSB-North Pole Library'},
   {'id': 'NPP001', 'name': 'FNSB-North Pole Park'},
   {'id': 'NBP001', 'name': 'FNSB-Nussbaumer Park'},
   {'id': 'PRW', 'name': 'FNSB-Peger Road West - Trans Garag'},
   {'id': 'ASLCHU', 'name': 'FNSB-Pioneer Pk - Office (Cab 19)'},
   {'id': 'ASLC47', 'name': 'FNSB-Pioneer Pk Barn Cabin 47'},
   {'id': 'ASLC52', 'name': 'FNSB-Pioneer Pk Cabin 52/Guard Shack'},
   {'id': 'ASLCV1', 'name': 'FNSB-Pioneer Pk Civic Center'},
   {'id': 'ASLELC1', 'name': 'FNSB-Pioneer Pk East Electrical Serv'},
   {'id': 'ASLGP2', 'name': 'FNSB-Pioneer Pk East Parking Lot'},
   {'id': 'ASLGDM', 'name': 'FNSB-Pioneer Pk Gold Dome'},
   {'id': 'ASLHIS', 'name': 'FNSB-Pioneer Pk Historical Society'},
   {'id': 'ASLSEA', 'name': 'FNSB-Pioneer Pk North Seasonal Water'},
   {'id': 'ASLC18', 'name': 'FNSB-Pioneer Pk Palace Saloon C18'},
   {'id': 'ASLPIH', 'name': 'FNSB-Pioneer Pk Pioneer Hall'},
   {'id': 'ASLTVR', 'name': 'FNSB-Pioneer Pk Railroad Museum'},
   {'id': 'ASLC21', 'name': 'FNSB-Pioneer Pk South Seasonal Water'},
   {'id': 'ASLSQD', 'name': 'FNSB-Pioneer Pk Square Dance Hall'},
   {'id': 'ASLELC2', 'name': 'FNSB-Pioneer Pk West Electrical Serv'},
   {'id': 'ASLPL1', 'name': 'FNSB-Pioneer Pk West Parkng Lot/Sign'},
   {'id': 'SHW001', 'name': 'FNSB-Shoreway Pk-Downtown Walk Brdg'},
   {'id': 'BALHHW', 'name': 'FNSB-Solid Waste Baler/Office/HHW'},
   {'id': 'BENBG1', 'name': 'FNSB-Solid Waste Entrance Scales'},
   {'id': 'HEMBG1', 'name': 'FNSB-Solid Waste Heavy Equip Bldg'},
   {'id': 'LEABG1', 'name': 'FNSB-Solid Waste Leachate Building'},
   {'id': 'STRBG1', 'name': 'FNSB-Solid Waste Storage Shed'},
   {'id': 'DOGPRK', 'name': 'FNSB-South Davis Dog Park Lighting'},
   {'id': 'SF001', 'name': 'FNSB-Stockton Field'},
   {'id': 'TRANS04', 'name': 'FNSB-TS - 18 Mi CHSR - Two Rivers'},
   {'id': 'TRANS03', 'name': 'FNSB-TS - 35 Mi Richardson Hwy'},
   {'id': 'TRANS09', 'name': 'FNSB-TS - Badger and Holmes Rd'},
   {'id': 'TRANS08', 'name': 'FNSB-TS - Farmers Loop Rd East'},
   {'id': 'TRANS01', 'name': 'FNSB-TS - Farmers Loop Rd West'},
   {'id': 'TRANS05', 'name': 'FNSB-TS - Fox Dumpster Site'},
   {'id': 'TRANS10', 'name': 'FNSB-TS - Little Shot Rd Ester'},
   {'id': 'TRANS02', 'name': 'FNSB-TS - Midway'},
   {'id': 'TRANS06', 'name': 'FNSB-TS - North Pole'},
   {'id': 'TRANS07', 'name': 'FNSB-TS - Old Chena Ridge Rd'},
   {'id': 'TRGR', 'name': 'FNSB-Transit Garage'},
   {'id': 'TRPAIR', 'name': 'FNSB-Transit Park/Air Qual Trailer'},
   {'id': 'TRPBG1', 'name': 'FNSB-Transit Park/Downtown Terminal'},
   {'id': 'TRS001', 'name': 'FNSB-Two Rivers Sports Complex'},
   {'id': 'VMP001', 'name': 'FNSB-Veterans Memorial Park'},
   {'id': 'WF001', 'name': 'FNSB-Weeks Field'},
   {'id': 'WSPP01', 'name': 'FNSB-Wendal Street Pedestrian Path'},
   {'id': 'WSPGAR', 'name': 'FNSB-Wescott Garage / Zamboni Rm'},
   {'id': 'WSPSWP', 'name': 'FNSB-Wescott Swimming Pool'},
   {'id': 'CEC', 'name': 'SD-CEC-Star Charter'},
   {'id': 'Emer S T', 'name': 'SD-FNSB Emergency Service Tower'}]},
 {'name': 'School District',
  'sites': [{'id': '11', 'name': 'SD-Admin Center'},
   {'id': '16', 'name': 'SD-Anderson Elementary'},
   {'id': '44', 'name': 'SD-Anne Wien Elementary'},
   {'id': '43', 'name': 'SD-Arctic Light Elementary'},
   {'id': '34', 'name': 'SD-Badger Elementary'},
   {'id': '08', 'name': 'SD-Barnette Magnet'},
   {'id': '20', 'name': 'SD-Ben Eielson JR/SR'},
   {'id': '45', 'name': 'SD-Chinook Charter'},
   {'id': '46', 'name': 'SD-Crawford Elementary'},
   {'id': '03', 'name': 'SD-Denali Elementary'},
   {'id': '40', 'name': 'SD-Effie Kokrine Charter (HlA)'},
   {'id': '15', 'name': 'SD-Facilities Management'},
   {'id': '15B', 'name': 'SD-Grounds'},
   {'id': '06', 'name': 'SD-Hunter Elementary'},
   {'id': '14', 'name': 'SD-Hutchison High'},
   {'id': '09', 'name': 'SD-Joy Elementary'},
   {'id': '15A', 'name': 'SD-Krize Building'},
   {'id': '42', 'name': 'SD-Ladd Elementary'},
   {'id': '05', 'name': 'SD-Lathrop High'},
   {'id': '04', 'name': 'SD-Nordale Elementary'},
   {'id': '12', 'name': 'SD-North Pole Elementary'},
   {'id': '23', 'name': 'SD-North Pole High'},
   {'id': '22', 'name': 'SD-North Pole Middle'},
   {'id': '104', 'name': 'SD-Nutrition Services Center'},
   {'id': '36', 'name': 'SD-Pearl Creek Elementary'},
   {'id': '47', 'name': 'SD-Randy Smith Middle'},
   {'id': '13', 'name': 'SD-Ryan Middle'},
   {'id': '10', 'name': 'SD-Salcha Elementary'},
   {'id': '49', 'name': 'SD-Star of the North Charter'},
   {'id': '27', 'name': 'SD-Tanana Middle'},
   {'id': '39', 'name': 'SD-Ticasuk Brown Elementary'},
   {'id': '37', 'name': 'SD-Two Rivers Elementary'},
   {'id': '07', 'name': 'SD-University Park Elementary'},
   {'id': '76', 'name': 'SD-Watershed Charter'},
   {'id': '38', 'name': 'SD-Weller Elementary'},
   {'id': '28', 'name': 'SD-West Valley High'},
   {'id': '29', 'name': 'SD-Woodriver Elementary'}]}]

In [86]:
# Convert the notebook to a script.
# !jupyter nbconvert --to script use_alan_data.ipynb

Deal with Divide by Zero


In [87]:
df_test = df.head().copy()
df_test['div0'] = df_test.mmbtu / 0.0
df_test


Out[87]:
site_id service_type cal_year cal_mo item_desc units cost days_served usage fiscal_year fiscal_mo mmbtu div0
0 03 Electricity 2005 12 Electricity charge kWh 1904.657880 49.5 14790.748577 2006 6 50.466034 inf
1 03 Electricity 2006 1 Electricity charge kWh 5430.493797 93.0 42665.790911 2006 7 145.575679 inf
2 03 Electricity 2006 2 Electricity charge kWh 5764.406730 84.0 45010.439348 2006 8 153.575619 inf
3 03 Electricity 2006 3 Electricity charge kWh 6349.255299 93.0 46311.547557 2006 9 158.015000 inf
4 03 Electricity 2006 4 Electricity charge kWh 5529.385224 90.0 40392.812893 2006 10 137.820278 inf

In [88]:
print(np.isfinite(np.nan))
print(np.isfinite(np.inf))
print(np.isfinite(-np.inf))
print(np.isfinite(2.34))


False
False
False
True

In [97]:
np.isfinite('NaN')


---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-97-4b552f38780f> in <module>()
----> 1 np.isfinite('NaN')

TypeError: ufunc 'isfinite' not supported for the input types, and the inputs could not be safely coerced to any supported types according to the casting rule ''safe''

In [98]:
def is_blank(val):
    return not np.isfinite(val)

In [99]:
print(is_blank(-np.inf))
print(is_blank(np.inf))
print(is_blank(np.nan))
print(is_blank(2.3))


True
True
True
False

How to Return from Graphing Functions


In [1]:
import matplotlib.pyplot as plt
#%matplotlib inline

In [2]:
def test_plot():
    fig, ax = plt.subplots()
    plt.plot([1,2,3], [4,5,6])
    return fig

In [4]:
test_plot()
x = 45