Snippets

Arlington County Webmaster Summarize EnergyCAP BL30A reports

Created by Dylan Barlett last modified
#!/usr/bin/env python
"""Summarize EnergyCAP BL30A reports by building and year.

Usage:
1. Run BL30A report with the following parameters:
     Base Year Equals 2015
     Commodity Code One of NATURALGAS:ELECTRIC
     First Month (01-12) Equals 1
     Num of Months (1-12) Equals 12
2. Export as BL30A_2015.csv to the same folder as this file
3. Repeat steps 1-2 for additional years
4. python summarize_BL30A.py
"""

import collections
import glob
import csv
import locale

__author__ = "Dylan Barlett"
__copyright__ = "Arlington County Government"
__license__ = "GPLv3"
__version__ = "1.0"
__maintainer__ = "Dylan Barlett"
__email__ = "dbarlett@arlingtonva.us"
__status__ = "Production"

MONTHS = {
    "Jan": 1,
    "Feb": 2,
    "Mar": 3,
    "Apr": 4,
    "May": 5,
    "Jun": 6,
    "Jul": 7,
    "Aug": 8,
    "Sep": 9,
    "Oct": 10,
    "Nov": 11,
    "Dec": 12,
}


def update_nested(d, u):
    """
    Update a nested dictionary.
    From http://stackoverflow.com/a/3233356.
    """
    for k, v in u.iteritems():
        if isinstance(v, collections.Mapping):
            r = update_nested(d.get(k, {}), v)
            d[k] = r
        else:
            d[k] = u[k]
    return d


def site_energy_intensity(square_feet, electric_kwh, natural_gas_therm):
    """Calculate site energy intensity (in BTU/sq. ft.).
    """
    if not square_feet:
        return None
    if not electric_kwh:
        electric_kwh = 0
    if not natural_gas_therm:
        natural_gas_therm = 0
    return ((electric_kwh * 3412) + (natural_gas_therm * 100000)) / square_feet


def source_energy_intensity(square_feet, electric_kwh, natural_gas_therm):
    """Calculate source energy intensity (in BTU/sq. ft.).
    """
    if not square_feet:
        return None
    if not electric_kwh:
        electric_kwh = 0
    if not natural_gas_therm:
        natural_gas_therm = 0
    return ((electric_kwh * 3412 * 3.34) + (natural_gas_therm * 100000 * 1.047)) / square_feet


def carbon_footprint(electric_kwh, natural_gas_therm):
    """Calculate carbon footprint (in metric tons CO2 equivalent).
    """
    if not electric_kwh:
        electric_kwh = 0
    if not natural_gas_therm:
        natural_gas_therm = 0
    return ((electric_kwh * 1.19) + (natural_gas_therm * 11.7)) / 2205


def format_address(address):
    """Format an address.
    "1001 N Quincy St,,Arlington,VA" -> "1001 N Quincy St, Arlington, VA"
    """
    if address:
        parts = [i for i in address.split(",") if i]
        return ", ".join(parts)
    else:
        return None

if __name__ == '__main__':
    locale.setlocale(locale.LC_ALL, "")
    output = [[
        "Place Code",
        "Place Name",
        "Address",
        "Year",
        "Complete Year",
        "Floor Area (square feet)",
        "Electric (kWh)",
        "Natural Gas (Therm)",
        "Site Energy Intensity (BTU/sq. ft.)",
        "Source Energy Intensity (BTU/sq. ft.)",
        "Carbon Footprint (Metric tons CO2 equivalent)",
    ]]

    usage = {}

    for filename in glob.glob("BL30A_*.csv"):
        with open(filename, "rb") as fp:
            reader = csv.reader(fp)
            for row in reader:
                if row[16]:  # Quantity
                    place_code = row[6].split(" ", 1)[0]
                    place_name = row[6].split(" ", 1)[1]
                    address = format_address(row[7])
                    try:
                        floor_area = int(locale.atof(row[8].split(" ", 1)[0]))
                    except ValueError:
                        floor_area = None
                    commodity = row[11]
                    unit = row[13]
                    month = MONTHS[row[14]]
                    year = int(locale.atof(row[15]))
                    quantity = locale.atof(row[16])
                    update_nested(usage, {
                        place_code: {
                            "Place Name": place_name,
                            "Address": address,
                            "Years": {
                                year: {
                                    "Floor Area": floor_area,
                                    month: {
                                        commodity: quantity,
                                    },
                                },
                            },
                        },
                    })

    for place_code, place_usage in usage.items():
        for year, year_usage in place_usage["Years"].items():
            floor_area = None
            electric = None
            natural_gas = None
            month_count = 0
            for month, month_usage in year_usage.items():
                if month == "Floor Area":
                    floor_area = month_usage
                else:
                    month_count += 1
                    if "Electric" in month_usage:
                        if electric:
                            electric += month_usage["Electric"]
                        else:
                            electric = month_usage["Electric"]
                    if "Natural Gas" in month_usage:
                        if natural_gas:
                            natural_gas += month_usage["Natural Gas"]
                        else:
                            natural_gas = month_usage["Natural Gas"]
            if month_count == 12:
                complete_year = True
            else:
                complete_year = False
            site_intensity = site_energy_intensity(floor_area, electric, natural_gas)
            source_intensity = source_energy_intensity(floor_area, electric, natural_gas)
            footprint = carbon_footprint(electric, natural_gas)
            row = [
                place_code,
                place_usage["Place Name"],
                place_usage["Address"],
                year,
                complete_year,
                floor_area,
                electric,
                natural_gas,
            ]
            # One decimal place for CSV output
            for i in (site_intensity, source_intensity, footprint):
                if i:
                    row.append("{:0.1f}".format(i))
                else:
                    row.append(None)
            output.append(row)

    with open("annual_usage.csv", "wb") as fp:
        writer = csv.writer(fp)
        writer.writerows(output)

Comments (0)