Loading and analyzing pecan street data

[Contains sensitive database access info]


In [6]:
%matplotlib inline

import datetime
import pandas as pd
import sqlalchemy as sa
from sqlalchemy import *
import pandas as pd
import os
import numpy as np
import operator
import math
import random
from pylab import *
import json 
import matplotlib 
import matplotlib.pyplot as pyplt

Month accessors


In [3]:
##for accessing all months
months = ['01','02','03','04','05','06','07','08','09','10','11']
days  = {'01':31,'02':28,'03':31,'04':30,'05':31,'06':30,'07':31,'08':31,'09':30,'10':31,'11':30}

Sensitive db info below!!!


In [7]:
##engine information
user_name = 'USERNAME'
pw='PASSWORD'
host = "db.wiki-energy.org"
port = "5432"
db = "postgres"

Function for getting the all data for group 1 houses for a certain month (ex: month="01" is jan). returns an array of dataframes with all data for 25 houses


In [8]:
def query_month(month):

    # exectute query
    query = """select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_"""+month
    url = "postgresql"+"://"+user_name+":"+pw+"@"+host+":"+port+"/"+db
    eng = create_engine(url)
    eng.echo=True
    eng_object=eng.execute(query)
    
    # get data
    df = pd.DataFrame.from_records(eng_object.fetchall())
    df.columns = eng_object.keys()
    
    # create a dic
    dfs_dict = {}
    dfs=df.groupby('dataid')
    for df in dfs:
        dfs_dict[df[0]]=df[1].drop('dataid',1)
    
    return dfs_dict

In [11]:
#example, takes a couple of minutes to run. fetches january data.

dataframes = query_month("01")


2014-06-25 11:20:38,683 INFO sqlalchemy.engine.base.Engine select version()
INFO:sqlalchemy.engine.base.Engine:select version()
2014-06-25 11:20:38,685 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-25 11:20:38,776 INFO sqlalchemy.engine.base.Engine select current_schema()
INFO:sqlalchemy.engine.base.Engine:select current_schema()
2014-06-25 11:20:38,778 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-25 11:20:38,873 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-06-25 11:20:38,874 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-25 11:20:38,925 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2014-06-25 11:20:38,927 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-25 11:20:39,062 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
INFO:sqlalchemy.engine.base.Engine:show standard_conforming_strings
2014-06-25 11:20:39,063 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-25 11:20:39,156 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_01
INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_01
2014-06-25 11:20:39,158 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
Out[11]:
{936: <class 'pandas.core.frame.DataFrame'>
 Int64Index: 2976 entries, 59520 to 62495
 Data columns (total 73 columns):
 id                      2976  non-null values
 utc_15min               2976  non-null values
 air1                    2976  non-null values
 air2                    2976  non-null values
 air3                    2976  non-null values
 airwindowunit1          2976  non-null values
 aquarium1               2976  non-null values
 bathroom1               2976  non-null values
 bathroom2               2976  non-null values
 bedroom1                2976  non-null values
 bedroom2                2976  non-null values
 bedroom3                2976  non-null values
 bedroom4                2976  non-null values
 bedroom5                2976  non-null values
 car1                    0  non-null values
 clotheswasher1          2976  non-null values
 clotheswasher_dryg1     2976  non-null values
 diningroom1             2976  non-null values
 diningroom2             2976  non-null values
 dishwasher1             2976  non-null values
 disposal1               2976  non-null values
 drye1                   2976  non-null values
 dryg1                   2976  non-null values
 freezer1                2976  non-null values
 furnace1                2976  non-null values
 furnace2                2976  non-null values
 garage1                 2976  non-null values
 garage2                 2976  non-null values
 heater1                 2976  non-null values
 housefan1               2976  non-null values
 icemaker1               2976  non-null values
 jacuzzi1                2976  non-null values
 kitchen1                2976  non-null values
 kitchen2                2976  non-null values
 kitchenapp1             2976  non-null values
 kitchenapp2             2976  non-null values
 lights_plugs1           2976  non-null values
 lights_plugs2           2976  non-null values
 lights_plugs3           2976  non-null values
 lights_plugs4           2976  non-null values
 lights_plugs5           2976  non-null values
 lights_plugs6           2976  non-null values
 livingroom1             2976  non-null values
 livingroom2             2976  non-null values
 microwave1              2976  non-null values
 office1                 2976  non-null values
 outsidelights_plugs1    2976  non-null values
 outsidelights_plugs2    2976  non-null values
 oven1                   2976  non-null values
 oven2                   2976  non-null values
 pool1                   2976  non-null values
 pool2                   2976  non-null values
 poollight1              2976  non-null values
 poolpump1               2976  non-null values
 pump1                   2976  non-null values
 range1                  2976  non-null values
 refrigerator1           2976  non-null values
 refrigerator2           2976  non-null values
 security1               2976  non-null values
 shed1                   2976  non-null values
 sprinkler1              2976  non-null values
 subpanel1               2976  non-null values
 subpanel2               2976  non-null values
 unknown1                2976  non-null values
 unknown2                2976  non-null values
 unknown3                2976  non-null values
 unknown4                2976  non-null values
 use                     2976  non-null values
 utilityroom1            2976  non-null values
 venthood1               2976  non-null values
 waterheater1            2976  non-null values
 waterheater2            2976  non-null values
 winecooler1             2976  non-null values
 dtypes: int64(1), object(72), 1334: <class 'pandas.core.frame.DataFrame'>
 Int64Index: 2976 entries, 19475 to 29759
 Data columns (total 73 columns):
 id                      2976  non-null values
 utc_15min               2976  non-null values
 air1                    2976  non-null values
 air2                    2976  non-null values
 air3                    2976  non-null values
 airwindowunit1          2976  non-null values
 aquarium1               2976  non-null values
 bathroom1               2976  non-null values
 bathroom2               2976  non-null values
 bedroom1                2976  non-null values
 bedroom2                2976  non-null values
 bedroom3                2976  non-null values
 bedroom4                2976  non-null values
 bedroom5                2976  non-null values
 car1                    0  non-null values
 clotheswasher1          2976  non-null values
 clotheswasher_dryg1     2976  non-null values
 diningroom1             2976  non-null values
 diningroom2             2976  non-null values
 dishwasher1             2976  non-null values
 disposal1               2976  non-null values
 drye1                   2976  non-null values
 dryg1                   2976  non-null values
 freezer1                2976  non-null values
 furnace1                2976  non-null values
 furnace2                2976  non-null values
 garage1                 2976  non-null values
 garage2                 2976  non-null values
 heater1                 2976  non-null values
 housefan1               2976  non-null values
 icemaker1               2976  non-null values
 jacuzzi1                2976  non-null values
 kitchen1                2976  non-null values
 kitchen2                2976  non-null values
 kitchenapp1             2976  non-null values
 kitchenapp2             2976  non-null values
 lights_plugs1           2976  non-null values
 lights_plugs2           2976  non-null values
 lights_plugs3           2976  non-null values
 lights_plugs4           2976  non-null values
 lights_plugs5           2976  non-null values
 lights_plugs6           2976  non-null values
 livingroom1             2976  non-null values
 livingroom2             2976  non-null values
 microwave1              2976  non-null values
 office1                 2976  non-null values
 outsidelights_plugs1    2976  non-null values
 outsidelights_plugs2    2976  non-null values
 oven1                   2976  non-null values
 oven2                   2976  non-null values
 pool1                   2976  non-null values
 pool2                   2976  non-null values
 poollight1              2976  non-null values
 poolpump1               2976  non-null values
 pump1                   2976  non-null values
 range1                  2976  non-null values
 refrigerator1           2976  non-null values
 refrigerator2           2976  non-null values
 security1               2976  non-null values
 shed1                   2976  non-null values
 sprinkler1              2976  non-null values
 subpanel1               2976  non-null values
 subpanel2               2976  non-null values
 unknown1                2976  non-null values
 unknown2                2976  non-null values
 unknown3                2976  non-null values
 unknown4                2976  non-null values
 use                     2976  non-null values
 utilityroom1            2976  non-null values
 venthood1               2976  non-null values
 waterheater1            2976  non-null values
 waterheater2            2976  non-null values
 winecooler1             2976  non-null values
 dtypes: int64(1), object(72), 1697: <class 'pandas.core.frame.DataFrame'>
 Int64Index: 2976 entries, 35712 to 38691
 Data columns (total 73 columns):
 id                      2976  non-null values
 utc_15min               2976  non-null values
 air1                    2976  non-null values
 air2                    2976  non-null values
 air3                    2976  non-null values
 airwindowunit1          2976  non-null values
 aquarium1               2976  non-null values
 bathroom1               2976  non-null values
 bathroom2               2976  non-null values
 bedroom1                2976  non-null values
 bedroom2                2976  non-null values
 bedroom3                2976  non-null values
 bedroom4                2976  non-null values
 bedroom5                2976  non-null values
 car1                    0  non-null values
 clotheswasher1          2976  non-null values
 clotheswasher_dryg1     2976  non-null values
 diningroom1             2976  non-null values
 diningroom2             2976  non-null values
 dishwasher1             2976  non-null values
 disposal1               2976  non-null values
 drye1                   2976  non-null values
 dryg1                   2976  non-null values
 freezer1                2976  non-null values
 furnace1                2976  non-null values
 furnace2                2976  non-null values
 garage1                 2976  non-null values
 garage2                 2976  non-null values
 heater1                 2976  non-null values
 housefan1               2976  non-null values
 icemaker1               2976  non-null values
 jacuzzi1                2976  non-null values
 kitchen1                2976  non-null values
 kitchen2                2976  non-null values
 kitchenapp1             2976  non-null values
 kitchenapp2             2976  non-null values
 lights_plugs1           2976  non-null values
 lights_plugs2           2976  non-null values
 lights_plugs3           2976  non-null values
 lights_plugs4           2976  non-null values
 lights_plugs5           2976  non-null values
 lights_plugs6           2976  non-null values
 livingroom1             2976  non-null values
 livingroom2             2976  non-null values
 microwave1              2976  non-null values
 office1                 2976  non-null values
 outsidelights_plugs1    2976  non-null values
 outsidelights_plugs2    2976  non-null values
 oven1                   2976  non-null values
 oven2                   2976  non-null values
 pool1                   2976  non-null values
 pool2                   2976  non-null values
 poollight1              2976  non-null values
 poolpump1               2976  non-null values
 pump1                   2976  non-null values
 range1                  2976  non-null values
 refrigerator1           2976  non-null values
 refrigerator2           2976  non-null values
 security1               2976  non-null values
 shed1                   2976  non-null values
 sprinkler1              2976  non-null values
 subpanel1               2976  non-null values
 subpanel2               2976  non-null values
 unknown1                2976  non-null values
 unknown2                2976  non-null values
 unknown3                2976  non-null values
 unknown4                2976  non-null values
 use                     2976  non-null values
 utilityroom1            2976  non-null values
 venthood1               2976  non-null values
 waterheater1            2976  non-null values
 waterheater2            2976  non-null values
 winecooler1             2976  non-null values
 dtypes: int64(1), object(72), 1714: <class 'pandas.core.frame.DataFrame'>
 Int64Index: 2976 entries, 17856 to 20839
 Data columns (total 73 columns):
 id                      2976  non-null values
 utc_15min               2976  non-null values
 air1                    2976  non-null values
 air2                    2976  non-null values
 air3                    2976  non-null values
 airwindowunit1          2976  non-null values
 aquarium1               2976  non-null values
 bathroom1               2976  non-null values
 bathroom2               2976  non-null values
 bedroom1                2976  non-null values
 bedroom2                2976  non-null values
 bedroom3                2976  non-null values
 bedroom4                2976  non-null values
 bedroom5                2976  non-null values
 car1                    0  non-null values
 clotheswasher1          2976  non-null values
 clotheswasher_dryg1     2976  non-null values
 diningroom1             2976  non-null values
 diningroom2             2976  non-null values
 dishwasher1             2976  non-null values
 disposal1               2976  non-null values
 drye1                   2976  non-null values
 dryg1                   2976  non-null values
 freezer1                2976  non-null values
 furnace1                2976  non-null values
 furnace2                2976  non-null values
 garage1                 2976  non-null values
 garage2                 2976  non-null values
 heater1                 2976  non-null values
 housefan1               2976  non-null values
 icemaker1               2976  non-null values
 jacuzzi1                2976  non-null values
 kitchen1                2976  non-null values
 kitchen2                2976  non-null values
 kitchenapp1             2976  non-null values
 kitchenapp2             2976  non-null values
 lights_plugs1           2976  non-null values
 lights_plugs2           2976  non-null values
 lights_plugs3           2976  non-null values
 lights_plugs4           2976  non-null values
 lights_plugs5           2976  non-null values
 lights_plugs6           2976  non-null values
 livingroom1             2976  non-null values
 livingroom2             2976  non-null values
 microwave1              2976  non-null values
 office1                 2976  non-null values
 outsidelights_plugs1    2976  non-null values
 outsidelights_plugs2    2976  non-null values
 oven1                   2976  non-null values
 oven2                   2976  non-null values
 pool1                   2976  non-null values
 pool2                   2976  non-null values
 poollight1              2976  non-null values
 poolpump1               2976  non-null values
 pump1                   2976  non-null values
 range1                  2976  non-null values
 refrigerator1           2976  non-null values
 refrigerator2           2976  non-null values
 security1               2976  non-null values
 shed1                   2976  non-null values
 sprinkler1              2976  non-null values
 subpanel1               2976  non-null values
 subpanel2               2976  non-null values
 unknown1                2976  non-null values
 unknown2                2976  non-null values
 unknown3                2976  non-null values
 unknown4                2976  non-null values
 use                     2976  non-null values
 utilityroom1            2976  non-null values
 venthood1               2976  non-null values
 waterheater1            2976  non-null values
 waterheater2            2976  non-null values
 winecooler1             2976  non-null values
 dtypes: int64(1), object(72), 2156: <class 'pandas.core.frame.DataFrame'>
 Int64Index: 2976 entries, 50592 to 53567
 Data columns (total 73 columns):
 id                      2976  non-null values
 utc_15min               2976  non-null values
 air1                    2976  non-null values
 air2                    2976  non-null values
 air3                    2976  non-null values
 airwindowunit1          2976  non-null values
 aquarium1               2976  non-null values
 bathroom1               2976  non-null values
 bathroom2               2976  non-null values
 bedroom1                2976  non-null values
 bedroom2                2976  non-null values
 bedroom3                2976  non-null values
 bedroom4                2976  non-null values
 bedroom5                2976  non-null values
 car1                    0  non-null values
 clotheswasher1          2976  non-null values
 clotheswasher_dryg1     2976  non-null values
 diningroom1             2976  non-null values
 diningroom2             2976  non-null values
 dishwasher1             2976  non-null values
 disposal1               2976  non-null values
 drye1                   2976  non-null values
 dryg1                   2976  non-null values
 freezer1                2976  non-null values
 furnace1                2976  non-null values
 furnace2                2976  non-null values
 garage1                 2976  non-null values
 garage2                 2976  non-null values
 heater1                 2976  non-null values
 housefan1               2976  non-null values
 icemaker1               2976  non-null values
 jacuzzi1                2976  non-null values
 kitchen1                2976  non-null values
 kitchen2                2976  non-null values
 kitchenapp1             2976  non-null values
 kitchenapp2             2976  non-null values
 lights_plugs1           2976  non-null values
 lights_plugs2           2976  non-null values
 lights_plugs3           2976  non-null values
 lights_plugs4           2976  non-null values
 lights_plugs5           2976  non-null values
 lights_plugs6           2976  non-null values
 livingroom1             2976  non-null values
 livingroom2             2976  non-null values
 microwave1              2976  non-null values
 office1                 2976  non-null values
 outsidelights_plugs1    2976  non-null values
 outsidelights_plugs2    2976  non-null values
 oven1                   2976  non-null values
 oven2                   2976  non-null values
 pool1                   2976  non-null values
 pool2                   2976  non-null values
 poollight1              2976  non-null values
 poolpump1               2976  non-null values
 pump1                   2976  non-null values
 range1                  2976  non-null values
 refrigerator1           2976  non-null values
 refrigerator2           2976  non-null values
 security1               2976  non-null values
 shed1                   2976  non-null values
 sprinkler1              2976  non-null values
 subpanel1               2976  non-null values
 subpanel2               2976  non-null values
 unknown1                2976  non-null values
 unknown2                2976  non-null values
 unknown3                2976  non-null values
 unknown4                2976  non-null values
 use                     2976  non-null values
 utilityroom1            2976  non-null values
 venthood1               2976  non-null values
 waterheater1            2976  non-null values
 waterheater2            2976  non-null values
 winecooler1             2976  non-null values
 dtypes: int64(1), object(72), 2158: <class 'pandas.core.frame.DataFrame'>
 Int64Index: 2976 entries, 5952 to 8927
 Data columns (total 73 columns):
 id                      2976  non-null values
 utc_15min               2976  non-null values
 air1                    2976  non-null values
 air2                    2976  non-null values
 air3                    2976  non-null values
 airwindowunit1          2976  non-null values
 aquarium1               2976  non-null values
 bathroom1               2976  non-null values
 bathroom2               2976  non-null values
 bedroom1                2976  non-null values
 bedroom2                2976  non-null values
 bedroom3                2976  non-null values
 bedroom4                2976  non-null values
 bedroom5                2976  non-null values
 car1                    0  non-null values
 clotheswasher1          2976  non-null values
 clotheswasher_dryg1     2976  non-null values
 diningroom1             2976  non-null values
 diningroom2             2976  non-null values
 dishwasher1             2976  non-null values
 disposal1               2976  non-null values
 drye1                   2976  non-null values
 dryg1                   2976  non-null values
 freezer1                2976  non-null values
 furnace1                2976  non-null values
 furnace2                2976  non-null values
 garage1                 2976  non-null values
 garage2                 2976  non-null values
 heater1                 2976  non-null values
 housefan1               2976  non-null values
 icemaker1               2976  non-null values
 jacuzzi1                2976  non-null values
 kitchen1                2976  non-null values
 kitchen2                2976  non-null values
 kitchenapp1             2976  non-null values
 kitchenapp2             2976  non-null values
 lights_plugs1           2976  non-null values
 lights_plugs2           2976  non-null values
 lights_plugs3           2976  non-null values
 lights_plugs4           2976  non-null values
 lights_plugs5           2976  non-null values
 lights_plugs6           2976  non-null values
 livingroom1             2976  non-null values
 livingroom2             2976  non-null values
 microwave1              2976  non-null values
 office1                 2976  non-null values
 outsidelights_plugs1    2976  non-null values
 outsidelights_plugs2    2976  non-null values
 oven1                   2976  non-null values
 oven2                   2976  non-null values
 pool1                   2976  non-null values
 pool2                   2976  non-null values
 poollight1              2976  non-null values
 poolpump1               2976  non-null values
 pump1                   2976  non-null values
 range1                  2976  non-null values
 refrigerator1           2976  non-null values
 refrigerator2           2976  non-null values
 security1               2976  non-null values
 shed1                   2976  non-null values
 sprinkler1              2976  non-null values
 subpanel1               2976  non-null values
 subpanel2               2976  non-null values
 unknown1                2976  non-null values
 unknown2                2976  non-null values
 unknown3                2976  non-null values
 unknown4                2976  non-null values
 use                     2976  non-null values
 utilityroom1            2976  non-null values
 venthood1               2976  non-null values
 waterheater1            2976  non-null values
 waterheater2            2976  non-null values
 winecooler1             2976  non-null values
 dtypes: int64(1), object(72), 2829: <class 'pandas.core.frame.DataFrame'>
 Int64Index: 2976 entries, 56544 to 59519
 Data columns (total 73 columns):
 id                      2976  non-null values
 utc_15min               2976  non-null values
 air1                    2976  non-null values
 air2                    2976  non-null values
 air3                    2976  non-null values
 airwindowunit1          2976  non-null values
 aquarium1               2976  non-null values
 bathroom1               2976  non-null values
 bathroom2               2976  non-null values
 bedroom1                2976  non-null values
 bedroom2                2976  non-null values
 bedroom3                2976  non-null values
 bedroom4                2976  non-null values
 bedroom5                2976  non-null values
 car1                    0  non-null values
 clotheswasher1          2976  non-null values
 clotheswasher_dryg1     2976  non-null values
 diningroom1             2976  non-null values
 diningroom2             2976  non-null values
 dishwasher1             2976  non-null values
 disposal1               2976  non-null values
 drye1                   2976  non-null values
 dryg1                   2976  non-null values
 freezer1                2976  non-null values
 furnace1                2976  non-null values
 furnace2                2976  non-null values
 garage1                 2976  non-null values
 garage2                 2976  non-null values
 heater1                 2976  non-null values
 housefan1               2976  non-null values
 icemaker1               2976  non-null values
 jacuzzi1                2976  non-null values
 kitchen1                2976  non-null values
 kitchen2                2976  non-null values
 kitchenapp1             2976  non-null values
 kitchenapp2             2976  non-null values
 lights_plugs1           2976  non-null values
 lights_plugs2           2976  non-null values
 lights_plugs3           2976  non-null values
 lights_plugs4           2976  non-null values
 lights_plugs5           2976  non-null values
 lights_plugs6           2976  non-null values
 livingroom1             2976  non-null values
 livingroom2             2976  non-null values
 microwave1              2976  non-null values
 office1                 2976  non-null values
 outsidelights_plugs1    2976  non-null values
 outsidelights_plugs2    2976  non-null values
 oven1                   2976  non-null values
 oven2                   2976  non-null values
 pool1                   2976  non-null values
 pool2                   2976  non-null values
 poollight1              2976  non-null values
 poolpump1               2976  non-null values
 pump1                   2976  non-null values
 range1                  2976  non-null values
 refrigerator1           2976  non-null values
 refrigerator2           2976  non-null values
 security1               2976  non-null values
 shed1                   2976  non-null values
 sprinkler1              2976  non-null values
 subpanel1               2976  non-null values
 subpanel2               2976  non-null values
 unknown1                2976  non-null values
 unknown2                2976  non-null values
 unknown3                2976  non-null values
 unknown4                2976  non-null values
 use                     2976  non-null values
 utilityroom1            2976  non-null values
 venthood1               2976  non-null values
 waterheater1            2976  non-null values
 waterheater2            2976  non-null values
 winecooler1             2976  non-null values
 dtypes: int64(1), object(72), 4031: <class 'pandas.core.frame.DataFrame'>
 Int64Index: 2976 entries, 29760 to 32740
 Data columns (total 73 columns):
 id                      2976  non-null values
 utc_15min               2976  non-null values
 air1                    2976  non-null values
 air2                    2976  non-null values
 air3                    2976  non-null values
 airwindowunit1          2976  non-null values
 aquarium1               2976  non-null values
 bathroom1               2976  non-null values
 bathroom2               2976  non-null values
 bedroom1                2976  non-null values
 bedroom2                2976  non-null values
 bedroom3                2976  non-null values
 bedroom4                2976  non-null values
 bedroom5                2976  non-null values
 car1                    0  non-null values
 clotheswasher1          2976  non-null values
 clotheswasher_dryg1     2976  non-null values
 diningroom1             2976  non-null values
 diningroom2             2976  non-null values
 dishwasher1             2976  non-null values
 disposal1               2976  non-null values
 drye1                   2976  non-null values
 dryg1                   2976  non-null values
 freezer1                2976  non-null values
 furnace1                2976  non-null values
 furnace2                2976  non-null values
 garage1                 2976  non-null values
 garage2                 2976  non-null values
 heater1                 2976  non-null values
 housefan1               2976  non-null values
 icemaker1               2976  non-null values
 jacuzzi1                2976  non-null values
 kitchen1                2976  non-null values
 kitchen2                2976  non-null values
 kitchenapp1             2976  non-null values
 kitchenapp2             2976  non-null values
 lights_plugs1           2976  non-null values
 lights_plugs2           2976  non-null values
 lights_plugs3           2976  non-null values
 lights_plugs4           2976  non-null values
 lights_plugs5           2976  non-null values
 lights_plugs6           2976  non-null values
 livingroom1             2976  non-null values
 livingroom2             2976  non-null values
 microwave1              2976  non-null values
 office1                 2976  non-null values
 outsidelights_plugs1    2976  non-null values
 outsidelights_plugs2    2976  non-null values
 oven1                   2976  non-null values
 oven2                   2976  non-null values
 pool1                   2976  non-null values
 pool2                   2976  non-null values
 poollight1              2976  non-null values
 poolpump1               2976  non-null values
 pump1                   2976  non-null values
 range1                  2976  non-null values
 refrigerator1           2976  non-null values
 refrigerator2           2976  non-null values
 security1               2976  non-null values
 shed1                   2976  non-null values
 sprinkler1              2976  non-null values
 subpanel1               2976  non-null values
 subpanel2               2976  non-null values
 unknown1                2976  non-null values
 unknown2                2976  non-null values
 unknown3                2976  non-null values
 unknown4                2976  non-null values
 use                     2976  non-null values
 utilityroom1            2976  non-null values
 venthood1               2976  non-null values
 waterheater1            2976  non-null values
 waterheater2            2976  non-null values
 winecooler1             2976  non-null values
 dtypes: int64(1), object(72), 5395: <class 'pandas.core.frame.DataFrame'>
 Int64Index: 2976 entries, 53568 to 56543
 Data columns (total 73 columns):
 id                      2976  non-null values
 utc_15min               2976  non-null values
 air1                    2976  non-null values
 air2                    2976  non-null values
 air3                    2976  non-null values
 airwindowunit1          2976  non-null values
 aquarium1               2976  non-null values
 bathroom1               2976  non-null values
 bathroom2               2976  non-null values
 bedroom1                2976  non-null values
 bedroom2                2976  non-null values
 bedroom3                2976  non-null values
 bedroom4                2976  non-null values
 bedroom5                2976  non-null values
 car1                    0  non-null values
 clotheswasher1          2976  non-null values
 clotheswasher_dryg1     2976  non-null values
 diningroom1             2976  non-null values
 diningroom2             2976  non-null values
 dishwasher1             2976  non-null values
 disposal1               2976  non-null values
 drye1                   2976  non-null values
 dryg1                   2976  non-null values
 freezer1                2976  non-null values
 furnace1                2976  non-null values
 furnace2                2976  non-null values
 garage1                 2976  non-null values
 garage2                 2976  non-null values
 heater1                 2976  non-null values
 housefan1               2976  non-null values
 icemaker1               2976  non-null values
 jacuzzi1                2976  non-null values
 kitchen1                2976  non-null values
 kitchen2                2976  non-null values
 kitchenapp1             2976  non-null values
 kitchenapp2             2976  non-null values
 lights_plugs1           2976  non-null values
 lights_plugs2           2976  non-null values
 lights_plugs3           2976  non-null values
 lights_plugs4           2976  non-null values
 lights_plugs5           2976  non-null values
 lights_plugs6           2976  non-null values
 livingroom1             2976  non-null values
 livingroom2             2976  non-null values
 microwave1              2976  non-null values
 office1                 2976  non-null values
 outsidelights_plugs1    2976  non-null values
 outsidelights_plugs2    2976  non-null values
 oven1                   2976  non-null values
 oven2                   2976  non-null values
 pool1                   2976  non-null values
 pool2                   2976  non-null values
 poollight1              2976  non-null values
 poolpump1               2976  non-null values
 pump1                   2976  non-null values
 range1                  2976  non-null values
 refrigerator1           2976  non-null values
 refrigerator2           2976  non-null values
 security1               2976  non-null values
 shed1                   2976  non-null values
 sprinkler1              2976  non-null values
 subpanel1               2976  non-null values
 subpanel2               2976  non-null values
 unknown1                2976  non-null values
 unknown2                2976  non-null values
 unknown3                2976  non-null values
 unknown4                2976  non-null values
 use                     2976  non-null values
 utilityroom1            2976  non-null values
 venthood1               2976  non-null values
 waterheater1            2976  non-null values
 waterheater2            2976  non-null values
 winecooler1             2976  non-null values
 dtypes: int64(1), object(72), 5746: <class 'pandas.core.frame.DataFrame'>
 Int64Index: 2976 entries, 65472 to 68447
 Data columns (total 73 columns):
 id                      2976  non-null values
 utc_15min               2976  non-null values
 air1                    2976  non-null values
 air2                    2976  non-null values
 air3                    2976  non-null values
 airwindowunit1          2976  non-null values
 aquarium1               2976  non-null values
 bathroom1               2976  non-null values
 bathroom2               2976  non-null values
 bedroom1                2976  non-null values
 bedroom2                2976  non-null values
 bedroom3                2976  non-null values
 bedroom4                2976  non-null values
 bedroom5                2976  non-null values
 car1                    0  non-null values
 clotheswasher1          2976  non-null values
 clotheswasher_dryg1     2976  non-null values
 diningroom1             2976  non-null values
 diningroom2             2976  non-null values
 dishwasher1             2976  non-null values
 disposal1               2976  non-null values
 drye1                   2976  non-null values
 dryg1                   2976  non-null values
 freezer1                2976  non-null values
 furnace1                2976  non-null values
 furnace2                2976  non-null values
 garage1                 2976  non-null values
 garage2                 2976  non-null values
 heater1                 2976  non-null values
 housefan1               2976  non-null values
 icemaker1               2976  non-null values
 jacuzzi1                2976  non-null values
 kitchen1                2976  non-null values
 kitchen2                2976  non-null values
 kitchenapp1             2976  non-null values
 kitchenapp2             2976  non-null values
 lights_plugs1           2976  non-null values
 lights_plugs2           2976  non-null values
 lights_plugs3           2976  non-null values
 lights_plugs4           2976  non-null values
 lights_plugs5           2976  non-null values
 lights_plugs6           2976  non-null values
 livingroom1             2976  non-null values
 livingroom2             2976  non-null values
 microwave1              2976  non-null values
 office1                 2976  non-null values
 outsidelights_plugs1    2976  non-null values
 outsidelights_plugs2    2976  non-null values
 oven1                   2976  non-null values
 oven2                   2976  non-null values
 pool1                   2976  non-null values
 pool2                   2976  non-null values
 poollight1              2976  non-null values
 poolpump1               2976  non-null values
 pump1                   2976  non-null values
 range1                  2976  non-null values
 refrigerator1           2976  non-null values
 refrigerator2           2976  non-null values
 security1               2976  non-null values
 shed1                   2976  non-null values
 sprinkler1              2976  non-null values
 subpanel1               2976  non-null values
 subpanel2               2976  non-null values
 unknown1                2976  non-null values
 unknown2                2976  non-null values
 unknown3                2976  non-null values
 unknown4                2976  non-null values
 use                     2976  non-null values
 utilityroom1            2976  non-null values
 venthood1               2976  non-null values
 waterheater1            2976  non-null values
 waterheater2            2976  non-null values
 winecooler1             2976  non-null values
 dtypes: int64(1), object(72), 5785: <class 'pandas.core.frame.DataFrame'>
 Int64Index: 2976 entries, 8928 to 11903
 Data columns (total 73 columns):
 id                      2976  non-null values
 utc_15min               2976  non-null values
 air1                    2976  non-null values
 air2                    2976  non-null values
 air3                    2976  non-null values
 airwindowunit1          2976  non-null values
 aquarium1               2976  non-null values
 bathroom1               2976  non-null values
 bathroom2               2976  non-null values
 bedroom1                2976  non-null values
 bedroom2                2976  non-null values
 bedroom3                2976  non-null values
 bedroom4                2976  non-null values
 bedroom5                2976  non-null values
 car1                    0  non-null values
 clotheswasher1          2976  non-null values
 clotheswasher_dryg1     2976  non-null values
 diningroom1             2976  non-null values
 diningroom2             2976  non-null values
 dishwasher1             2976  non-null values
 disposal1               2976  non-null values
 drye1                   2976  non-null values
 dryg1                   2976  non-null values
 freezer1                2976  non-null values
 furnace1                2976  non-null values
 furnace2                2976  non-null values
 garage1                 2976  non-null values
 garage2                 2976  non-null values
 heater1                 2976  non-null values
 housefan1               2976  non-null values
 icemaker1               2976  non-null values
 jacuzzi1                2976  non-null values
 kitchen1                2976  non-null values
 kitchen2                2976  non-null values
 kitchenapp1             2976  non-null values
 kitchenapp2             2976  non-null values
 lights_plugs1           2976  non-null values
 lights_plugs2           2976  non-null values
 lights_plugs3           2976  non-null values
 lights_plugs4           2976  non-null values
 lights_plugs5           2976  non-null values
 lights_plugs6           2976  non-null values
 livingroom1             2976  non-null values
 livingroom2             2976  non-null values
 microwave1              2976  non-null values
 office1                 2976  non-null values
 outsidelights_plugs1    2976  non-null values
 outsidelights_plugs2    2976  non-null values
 oven1                   2976  non-null values
 oven2                   2976  non-null values
 pool1                   2976  non-null values
 pool2                   2976  non-null values
 poollight1              2976  non-null values
 poolpump1               2976  non-null values
 pump1                   2976  non-null values
 range1                  2976  non-null values
 refrigerator1           2976  non-null values
 refrigerator2           2976  non-null values
 security1               2976  non-null values
 shed1                   2976  non-null values
 sprinkler1              2976  non-null values
 subpanel1               2976  non-null values
 subpanel2               2976  non-null values
 unknown1                2976  non-null values
 unknown2                2976  non-null values
 unknown3                2976  non-null values
 unknown4                2976  non-null values
 use                     2976  non-null values
 utilityroom1            2976  non-null values
 venthood1               2976  non-null values
 waterheater1            2976  non-null values
 waterheater2            2976  non-null values
 winecooler1             2976  non-null values
 dtypes: int64(1), object(72), 5949: <class 'pandas.core.frame.DataFrame'>
 Int64Index: 2976 entries, 29930 to 35711
 Data columns (total 73 columns):
 id                      2976  non-null values
 utc_15min               2976  non-null values
 air1                    2976  non-null values
 air2                    2976  non-null values
 air3                    2976  non-null values
 airwindowunit1          2976  non-null values
 aquarium1               2976  non-null values
 bathroom1               2976  non-null values
 bathroom2               2976  non-null values
 bedroom1                2976  non-null values
 bedroom2                2976  non-null values
 bedroom3                2976  non-null values
 bedroom4                2976  non-null values
 bedroom5                2976  non-null values
 car1                    0  non-null values
 clotheswasher1          2976  non-null values
 clotheswasher_dryg1     2976  non-null values
 diningroom1             2976  non-null values
 diningroom2             2976  non-null values
 dishwasher1             2976  non-null values
 disposal1               2976  non-null values
 drye1                   2976  non-null values
 dryg1                   2976  non-null values
 freezer1                2976  non-null values
 furnace1                2976  non-null values
 furnace2                2976  non-null values
 garage1                 2976  non-null values
 garage2                 2976  non-null values
 heater1                 2976  non-null values
 housefan1               2976  non-null values
 icemaker1               2976  non-null values
 jacuzzi1                2976  non-null values
 kitchen1                2976  non-null values
 kitchen2                2976  non-null values
 kitchenapp1             2976  non-null values
 kitchenapp2             2976  non-null values
 lights_plugs1           2976  non-null values
 lights_plugs2           2976  non-null values
 lights_plugs3           2976  non-null values
 lights_plugs4           2976  non-null values
 lights_plugs5           2976  non-null values
 lights_plugs6           2976  non-null values
 livingroom1             2976  non-null values
 livingroom2             2976  non-null values
 microwave1              2976  non-null values
 office1                 2976  non-null values
 outsidelights_plugs1    2976  non-null values
 outsidelights_plugs2    2976  non-null values
 oven1                   2976  non-null values
 oven2                   2976  non-null values
 pool1                   2976  non-null values
 pool2                   2976  non-null values
 poollight1              2976  non-null values
 poolpump1               2976  non-null values
 pump1                   2976  non-null values
 range1                  2976  non-null values
 refrigerator1           2976  non-null values
 refrigerator2           2976  non-null values
 security1               2976  non-null values
 shed1                   2976  non-null values
 sprinkler1              2976  non-null values
 subpanel1               2976  non-null values
 subpanel2               2976  non-null values
 unknown1                2976  non-null values
 unknown2                2976  non-null values
 unknown3                2976  non-null values
 unknown4                2976  non-null values
 use                     2976  non-null values
 utilityroom1            2976  non-null values
 venthood1               2976  non-null values
 waterheater1            2976  non-null values
 waterheater2            2976  non-null values
 winecooler1             2976  non-null values
 dtypes: int64(1), object(72), 6673: <class 'pandas.core.frame.DataFrame'>
 Int64Index: 2976 entries, 68448 to 71424
 Data columns (total 73 columns):
 id                      2976  non-null values
 utc_15min               2976  non-null values
 air1                    2976  non-null values
 air2                    2976  non-null values
 air3                    2976  non-null values
 airwindowunit1          2976  non-null values
 aquarium1               2976  non-null values
 bathroom1               2976  non-null values
 bathroom2               2976  non-null values
 bedroom1                2976  non-null values
 bedroom2                2976  non-null values
 bedroom3                2976  non-null values
 bedroom4                2976  non-null values
 bedroom5                2976  non-null values
 car1                    0  non-null values
 clotheswasher1          2976  non-null values
 clotheswasher_dryg1     2976  non-null values
 diningroom1             2976  non-null values
 diningroom2             2976  non-null values
 dishwasher1             2976  non-null values
 disposal1               2976  non-null values
 drye1                   2976  non-null values
 dryg1                   2976  non-null values
 freezer1                2976  non-null values
 furnace1                2976  non-null values
 furnace2                2976  non-null values
 garage1                 2976  non-null values
 garage2                 2976  non-null values
 heater1                 2976  non-null values
 housefan1               2976  non-null values
 icemaker1               2976  non-null values
 jacuzzi1                2976  non-null values
 kitchen1                2976  non-null values
 kitchen2                2976  non-null values
 kitchenapp1             2976  non-null values
 kitchenapp2             2976  non-null values
 lights_plugs1           2976  non-null values
 lights_plugs2           2976  non-null values
 lights_plugs3           2976  non-null values
 lights_plugs4           2976  non-null values
 lights_plugs5           2976  non-null values
 lights_plugs6           2976  non-null values
 livingroom1             2976  non-null values
 livingroom2             2976  non-null values
 microwave1              2976  non-null values
 office1                 2976  non-null values
 outsidelights_plugs1    2976  non-null values
 outsidelights_plugs2    2976  non-null values
 oven1                   2976  non-null values
 oven2                   2976  non-null values
 pool1                   2976  non-null values
 pool2                   2976  non-null values
 poollight1              2976  non-null values
 poolpump1               2976  non-null values
 pump1                   2976  non-null values
 range1                  2976  non-null values
 refrigerator1           2976  non-null values
 refrigerator2           2976  non-null values
 security1               2976  non-null values
 shed1                   2976  non-null values
 sprinkler1              2976  non-null values
 subpanel1               2976  non-null values
 subpanel2               2976  non-null values
 unknown1                2976  non-null values
 unknown2                2976  non-null values
 unknown3                2976  non-null values
 unknown4                2976  non-null values
 use                     2976  non-null values
 utilityroom1            2976  non-null values
 venthood1               2976  non-null values
 waterheater1            2976  non-null values
 waterheater2            2976  non-null values
 winecooler1             2976  non-null values
 dtypes: int64(1), object(72), 6941: <class 'pandas.core.frame.DataFrame'>
 Int64Index: 2976 entries, 47616 to 50591
 Data columns (total 73 columns):
 id                      2976  non-null values
 utc_15min               2976  non-null values
 air1                    2976  non-null values
 air2                    2976  non-null values
 air3                    2976  non-null values
 airwindowunit1          2976  non-null values
 aquarium1               2976  non-null values
 bathroom1               2976  non-null values
 bathroom2               2976  non-null values
 bedroom1                2976  non-null values
 bedroom2                2976  non-null values
 bedroom3                2976  non-null values
 bedroom4                2976  non-null values
 bedroom5                2976  non-null values
 car1                    0  non-null values
 clotheswasher1          2976  non-null values
 clotheswasher_dryg1     2976  non-null values
 diningroom1             2976  non-null values
 diningroom2             2976  non-null values
 dishwasher1             2976  non-null values
 disposal1               2976  non-null values
 drye1                   2976  non-null values
 dryg1                   2976  non-null values
 freezer1                2976  non-null values
 furnace1                2976  non-null values
 furnace2                2976  non-null values
 garage1                 2976  non-null values
 garage2                 2976  non-null values
 heater1                 2976  non-null values
 housefan1               2976  non-null values
 icemaker1               2976  non-null values
 jacuzzi1                2976  non-null values
 kitchen1                2976  non-null values
 kitchen2                2976  non-null values
 kitchenapp1             2976  non-null values
 kitchenapp2             2976  non-null values
 lights_plugs1           2976  non-null values
 lights_plugs2           2976  non-null values
 lights_plugs3           2976  non-null values
 lights_plugs4           2976  non-null values
 lights_plugs5           2976  non-null values
 lights_plugs6           2976  non-null values
 livingroom1             2976  non-null values
 livingroom2             2976  non-null values
 microwave1              2976  non-null values
 office1                 2976  non-null values
 outsidelights_plugs1    2976  non-null values
 outsidelights_plugs2    2976  non-null values
 oven1                   2976  non-null values
 oven2                   2976  non-null values
 pool1                   2976  non-null values
 pool2                   2976  non-null values
 poollight1              2976  non-null values
 poolpump1               2976  non-null values
 pump1                   2976  non-null values
 range1                  2976  non-null values
 refrigerator1           2976  non-null values
 refrigerator2           2976  non-null values
 security1               2976  non-null values
 shed1                   2976  non-null values
 sprinkler1              2976  non-null values
 subpanel1               2976  non-null values
 subpanel2               2976  non-null values
 unknown1                2976  non-null values
 unknown2                2976  non-null values
 unknown3                2976  non-null values
 unknown4                2976  non-null values
 use                     2976  non-null values
 utilityroom1            2976  non-null values
 venthood1               2976  non-null values
 waterheater1            2976  non-null values
 waterheater2            2976  non-null values
 winecooler1             2976  non-null values
 dtypes: int64(1), object(72), 7731: <class 'pandas.core.frame.DataFrame'>
 Int64Index: 2976 entries, 2976 to 5951
 Data columns (total 73 columns):
 id                      2976  non-null values
 utc_15min               2976  non-null values
 air1                    2976  non-null values
 air2                    2976  non-null values
 air3                    2976  non-null values
 airwindowunit1          2976  non-null values
 aquarium1               2976  non-null values
 bathroom1               2976  non-null values
 bathroom2               2976  non-null values
 bedroom1                2976  non-null values
 bedroom2                2976  non-null values
 bedroom3                2976  non-null values
 bedroom4                2976  non-null values
 bedroom5                2976  non-null values
 car1                    0  non-null values
 clotheswasher1          2976  non-null values
 clotheswasher_dryg1     2976  non-null values
 diningroom1             2976  non-null values
 diningroom2             2976  non-null values
 dishwasher1             2976  non-null values
 disposal1               2976  non-null values
 drye1                   2976  non-null values
 dryg1                   2976  non-null values
 freezer1                2976  non-null values
 furnace1                2976  non-null values
 furnace2                2976  non-null values
 garage1                 2976  non-null values
 garage2                 2976  non-null values
 heater1                 2976  non-null values
 housefan1               2976  non-null values
 icemaker1               2976  non-null values
 jacuzzi1                2976  non-null values
 kitchen1                2976  non-null values
 kitchen2                2976  non-null values
 kitchenapp1             2976  non-null values
 kitchenapp2             2976  non-null values
 lights_plugs1           2976  non-null values
 lights_plugs2           2976  non-null values
 lights_plugs3           2976  non-null values
 lights_plugs4           2976  non-null values
 lights_plugs5           2976  non-null values
 lights_plugs6           2976  non-null values
 livingroom1             2976  non-null values
 livingroom2             2976  non-null values
 microwave1              2976  non-null values
 office1                 2976  non-null values
 outsidelights_plugs1    2976  non-null values
 outsidelights_plugs2    2976  non-null values
 oven1                   2976  non-null values
 oven2                   2976  non-null values
 pool1                   2976  non-null values
 pool2                   2976  non-null values
 poollight1              2976  non-null values
 poolpump1               2976  non-null values
 pump1                   2976  non-null values
 range1                  2976  non-null values
 refrigerator1           2976  non-null values
 refrigerator2           2976  non-null values
 security1               2976  non-null values
 shed1                   2976  non-null values
 sprinkler1              2976  non-null values
 subpanel1               2976  non-null values
 subpanel2               2976  non-null values
 unknown1                2976  non-null values
 unknown2                2976  non-null values
 unknown3                2976  non-null values
 unknown4                2976  non-null values
 use                     2976  non-null values
 utilityroom1            2976  non-null values
 venthood1               2976  non-null values
 waterheater1            2976  non-null values
 waterheater2            2976  non-null values
 winecooler1             2976  non-null values
 dtypes: int64(1), object(72), 7989: <class 'pandas.core.frame.DataFrame'>
 Int64Index: 2976 entries, 44640 to 47615
 Data columns (total 73 columns):
 id                      2976  non-null values
 utc_15min               2976  non-null values
 air1                    2976  non-null values
 air2                    2976  non-null values
 air3                    2976  non-null values
 airwindowunit1          2976  non-null values
 aquarium1               2976  non-null values
 bathroom1               2976  non-null values
 bathroom2               2976  non-null values
 bedroom1                2976  non-null values
 bedroom2                2976  non-null values
 bedroom3                2976  non-null values
 bedroom4                2976  non-null values
 bedroom5                2976  non-null values
 car1                    0  non-null values
 clotheswasher1          2976  non-null values
 clotheswasher_dryg1     2976  non-null values
 diningroom1             2976  non-null values
 diningroom2             2976  non-null values
 dishwasher1             2976  non-null values
 disposal1               2976  non-null values
 drye1                   2976  non-null values
 dryg1                   2976  non-null values
 freezer1                2976  non-null values
 furnace1                2976  non-null values
 furnace2                2976  non-null values
 garage1                 2976  non-null values
 garage2                 2976  non-null values
 heater1                 2976  non-null values
 housefan1               2976  non-null values
 icemaker1               2976  non-null values
 jacuzzi1                2976  non-null values
 kitchen1                2976  non-null values
 kitchen2                2976  non-null values
 kitchenapp1             2976  non-null values
 kitchenapp2             2976  non-null values
 lights_plugs1           2976  non-null values
 lights_plugs2           2976  non-null values
 lights_plugs3           2976  non-null values
 lights_plugs4           2976  non-null values
 lights_plugs5           2976  non-null values
 lights_plugs6           2976  non-null values
 livingroom1             2976  non-null values
 livingroom2             2976  non-null values
 microwave1              2976  non-null values
 office1                 2976  non-null values
 outsidelights_plugs1    2976  non-null values
 outsidelights_plugs2    2976  non-null values
 oven1                   2976  non-null values
 oven2                   2976  non-null values
 pool1                   2976  non-null values
 pool2                   2976  non-null values
 poollight1              2976  non-null values
 poolpump1               2976  non-null values
 pump1                   2976  non-null values
 range1                  2976  non-null values
 refrigerator1           2976  non-null values
 refrigerator2           2976  non-null values
 security1               2976  non-null values
 shed1                   2976  non-null values
 sprinkler1              2976  non-null values
 subpanel1               2976  non-null values
 subpanel2               2976  non-null values
 unknown1                2976  non-null values
 unknown2                2976  non-null values
 unknown3                2976  non-null values
 unknown4                2976  non-null values
 use                     2976  non-null values
 utilityroom1            2976  non-null values
 venthood1               2976  non-null values
 waterheater1            2976  non-null values
 waterheater2            2976  non-null values
 winecooler1             2976  non-null values
 dtypes: int64(1), object(72), 8197: <class 'pandas.core.frame.DataFrame'>
 Int64Index: 2976 entries, 11904 to 14883
 Data columns (total 73 columns):
 id                      2976  non-null values
 utc_15min               2976  non-null values
 air1                    2976  non-null values
 air2                    2976  non-null values
 air3                    2976  non-null values
 airwindowunit1          2976  non-null values
 aquarium1               2976  non-null values
 bathroom1               2976  non-null values
 bathroom2               2976  non-null values
 bedroom1                2976  non-null values
 bedroom2                2976  non-null values
 bedroom3                2976  non-null values
 bedroom4                2976  non-null values
 bedroom5                2976  non-null values
 car1                    0  non-null values
 clotheswasher1          2976  non-null values
 clotheswasher_dryg1     2976  non-null values
 diningroom1             2976  non-null values
 diningroom2             2976  non-null values
 dishwasher1             2976  non-null values
 disposal1               2976  non-null values
 drye1                   2976  non-null values
 dryg1                   2976  non-null values
 freezer1                2976  non-null values
 furnace1                2976  non-null values
 furnace2                2976  non-null values
 garage1                 2976  non-null values
 garage2                 2976  non-null values
 heater1                 2976  non-null values
 housefan1               2976  non-null values
 icemaker1               2976  non-null values
 jacuzzi1                2976  non-null values
 kitchen1                2976  non-null values
 kitchen2                2976  non-null values
 kitchenapp1             2976  non-null values
 kitchenapp2             2976  non-null values
 lights_plugs1           2976  non-null values
 lights_plugs2           2976  non-null values
 lights_plugs3           2976  non-null values
 lights_plugs4           2976  non-null values
 lights_plugs5           2976  non-null values
 lights_plugs6           2976  non-null values
 livingroom1             2976  non-null values
 livingroom2             2976  non-null values
 microwave1              2976  non-null values
 office1                 2976  non-null values
 outsidelights_plugs1    2976  non-null values
 outsidelights_plugs2    2976  non-null values
 oven1                   2976  non-null values
 oven2                   2976  non-null values
 pool1                   2976  non-null values
 pool2                   2976  non-null values
 poollight1              2976  non-null values
 poolpump1               2976  non-null values
 pump1                   2976  non-null values
 range1                  2976  non-null values
 refrigerator1           2976  non-null values
 refrigerator2           2976  non-null values
 security1               2976  non-null values
 shed1                   2976  non-null values
 sprinkler1              2976  non-null values
 subpanel1               2976  non-null values
 subpanel2               2976  non-null values
 unknown1                2976  non-null values
 unknown2                2976  non-null values
 unknown3                2976  non-null values
 unknown4                2976  non-null values
 use                     2976  non-null values
 utilityroom1            2976  non-null values
 venthood1               2976  non-null values
 waterheater1            2976  non-null values
 waterheater2            2976  non-null values
 winecooler1             2976  non-null values
 dtypes: int64(1), object(72), 8669: <class 'pandas.core.frame.DataFrame'>
 Int64Index: 2976 entries, 18343 to 23811
 Data columns (total 73 columns):
 id                      2976  non-null values
 utc_15min               2976  non-null values
 air1                    2976  non-null values
 air2                    2976  non-null values
 air3                    2976  non-null values
 airwindowunit1          2976  non-null values
 aquarium1               2976  non-null values
 bathroom1               2976  non-null values
 bathroom2               2976  non-null values
 bedroom1                2976  non-null values
 bedroom2                2976  non-null values
 bedroom3                2976  non-null values
 bedroom4                2976  non-null values
 bedroom5                2976  non-null values
 car1                    0  non-null values
 clotheswasher1          2976  non-null values
 clotheswasher_dryg1     2976  non-null values
 diningroom1             2976  non-null values
 diningroom2             2976  non-null values
 dishwasher1             2976  non-null values
 disposal1               2976  non-null values
 drye1                   2976  non-null values
 dryg1                   2976  non-null values
 freezer1                2976  non-null values
 furnace1                2976  non-null values
 furnace2                2976  non-null values
 garage1                 2976  non-null values
 garage2                 2976  non-null values
 heater1                 2976  non-null values
 housefan1               2976  non-null values
 icemaker1               2976  non-null values
 jacuzzi1                2976  non-null values
 kitchen1                2976  non-null values
 kitchen2                2976  non-null values
 kitchenapp1             2976  non-null values
 kitchenapp2             2976  non-null values
 lights_plugs1           2976  non-null values
 lights_plugs2           2976  non-null values
 lights_plugs3           2976  non-null values
 lights_plugs4           2976  non-null values
 lights_plugs5           2976  non-null values
 lights_plugs6           2976  non-null values
 livingroom1             2976  non-null values
 livingroom2             2976  non-null values
 microwave1              2976  non-null values
 office1                 2976  non-null values
 outsidelights_plugs1    2976  non-null values
 outsidelights_plugs2    2976  non-null values
 oven1                   2976  non-null values
 oven2                   2976  non-null values
 pool1                   2976  non-null values
 pool2                   2976  non-null values
 poollight1              2976  non-null values
 poolpump1               2976  non-null values
 pump1                   2976  non-null values
 range1                  2976  non-null values
 refrigerator1           2976  non-null values
 refrigerator2           2976  non-null values
 security1               2976  non-null values
 shed1                   2976  non-null values
 sprinkler1              2976  non-null values
 subpanel1               2976  non-null values
 subpanel2               2976  non-null values
 unknown1                2976  non-null values
 unknown2                2976  non-null values
 unknown3                2976  non-null values
 unknown4                2976  non-null values
 use                     2976  non-null values
 utilityroom1            2976  non-null values
 venthood1               2976  non-null values
 waterheater1            2976  non-null values
 waterheater2            2976  non-null values
 winecooler1             2976  non-null values
 dtypes: int64(1), object(72), 9160: <class 'pandas.core.frame.DataFrame'>
 Int64Index: 2976 entries, 35948 to 41663
 Data columns (total 73 columns):
 id                      2976  non-null values
 utc_15min               2976  non-null values
 air1                    2976  non-null values
 air2                    2976  non-null values
 air3                    2976  non-null values
 airwindowunit1          2976  non-null values
 aquarium1               2976  non-null values
 bathroom1               2976  non-null values
 bathroom2               2976  non-null values
 bedroom1                2976  non-null values
 bedroom2                2976  non-null values
 bedroom3                2976  non-null values
 bedroom4                2976  non-null values
 bedroom5                2976  non-null values
 car1                    0  non-null values
 clotheswasher1          2976  non-null values
 clotheswasher_dryg1     2976  non-null values
 diningroom1             2976  non-null values
 diningroom2             2976  non-null values
 dishwasher1             2976  non-null values
 disposal1               2976  non-null values
 drye1                   2976  non-null values
 dryg1                   2976  non-null values
 freezer1                2976  non-null values
 furnace1                2976  non-null values
 furnace2                2976  non-null values
 garage1                 2976  non-null values
 garage2                 2976  non-null values
 heater1                 2976  non-null values
 housefan1               2976  non-null values
 icemaker1               2976  non-null values
 jacuzzi1                2976  non-null values
 kitchen1                2976  non-null values
 kitchen2                2976  non-null values
 kitchenapp1             2976  non-null values
 kitchenapp2             2976  non-null values
 lights_plugs1           2976  non-null values
 lights_plugs2           2976  non-null values
 lights_plugs3           2976  non-null values
 lights_plugs4           2976  non-null values
 lights_plugs5           2976  non-null values
 lights_plugs6           2976  non-null values
 livingroom1             2976  non-null values
 livingroom2             2976  non-null values
 microwave1              2976  non-null values
 office1                 2976  non-null values
 outsidelights_plugs1    2976  non-null values
 outsidelights_plugs2    2976  non-null values
 oven1                   2976  non-null values
 oven2                   2976  non-null values
 pool1                   2976  non-null values
 pool2                   2976  non-null values
 poollight1              2976  non-null values
 poolpump1               2976  non-null values
 pump1                   2976  non-null values
 range1                  2976  non-null values
 refrigerator1           2976  non-null values
 refrigerator2           2976  non-null values
 security1               2976  non-null values
 shed1                   2976  non-null values
 sprinkler1              2976  non-null values
 subpanel1               2976  non-null values
 subpanel2               2976  non-null values
 unknown1                2976  non-null values
 unknown2                2976  non-null values
 unknown3                2976  non-null values
 unknown4                2976  non-null values
 use                     2976  non-null values
 utilityroom1            2976  non-null values
 venthood1               2976  non-null values
 waterheater1            2976  non-null values
 waterheater2            2976  non-null values
 winecooler1             2976  non-null values
 dtypes: int64(1), object(72), 9654: <class 'pandas.core.frame.DataFrame'>
 Int64Index: 2976 entries, 0 to 2975
 Data columns (total 73 columns):
 id                      2976  non-null values
 utc_15min               2976  non-null values
 air1                    2976  non-null values
 air2                    2976  non-null values
 air3                    2976  non-null values
 airwindowunit1          2976  non-null values
 aquarium1               2976  non-null values
 bathroom1               2976  non-null values
 bathroom2               2976  non-null values
 bedroom1                2976  non-null values
 bedroom2                2976  non-null values
 bedroom3                2976  non-null values
 bedroom4                2976  non-null values
 bedroom5                2976  non-null values
 car1                    0  non-null values
 clotheswasher1          2976  non-null values
 clotheswasher_dryg1     2976  non-null values
 diningroom1             2976  non-null values
 diningroom2             2976  non-null values
 dishwasher1             2976  non-null values
 disposal1               2976  non-null values
 drye1                   2976  non-null values
 dryg1                   2976  non-null values
 freezer1                2976  non-null values
 furnace1                2976  non-null values
 furnace2                2976  non-null values
 garage1                 2976  non-null values
 garage2                 2976  non-null values
 heater1                 2976  non-null values
 housefan1               2976  non-null values
 icemaker1               2976  non-null values
 jacuzzi1                2976  non-null values
 kitchen1                2976  non-null values
 kitchen2                2976  non-null values
 kitchenapp1             2976  non-null values
 kitchenapp2             2976  non-null values
 lights_plugs1           2976  non-null values
 lights_plugs2           2976  non-null values
 lights_plugs3           2976  non-null values
 lights_plugs4           2976  non-null values
 lights_plugs5           2976  non-null values
 lights_plugs6           2976  non-null values
 livingroom1             2976  non-null values
 livingroom2             2976  non-null values
 microwave1              2976  non-null values
 office1                 2976  non-null values
 outsidelights_plugs1    2976  non-null values
 outsidelights_plugs2    2976  non-null values
 oven1                   2976  non-null values
 oven2                   2976  non-null values
 pool1                   2976  non-null values
 pool2                   2976  non-null values
 poollight1              2976  non-null values
 poolpump1               2976  non-null values
 pump1                   2976  non-null values
 range1                  2976  non-null values
 refrigerator1           2976  non-null values
 refrigerator2           2976  non-null values
 security1               2976  non-null values
 shed1                   2976  non-null values
 sprinkler1              2976  non-null values
 subpanel1               2976  non-null values
 subpanel2               2976  non-null values
 unknown1                2976  non-null values
 unknown2                2976  non-null values
 unknown3                2976  non-null values
 unknown4                2976  non-null values
 use                     2976  non-null values
 utilityroom1            2976  non-null values
 venthood1               2976  non-null values
 waterheater1            2976  non-null values
 waterheater2            2976  non-null values
 winecooler1             2976  non-null values
 dtypes: int64(1), object(72), 9737: <class 'pandas.core.frame.DataFrame'>
 Int64Index: 2976 entries, 23812 to 26794
 Data columns (total 73 columns):
 id                      2976  non-null values
 utc_15min               2976  non-null values
 air1                    2976  non-null values
 air2                    2976  non-null values
 air3                    2976  non-null values
 airwindowunit1          2976  non-null values
 aquarium1               2976  non-null values
 bathroom1               2976  non-null values
 bathroom2               2976  non-null values
 bedroom1                2976  non-null values
 bedroom2                2976  non-null values
 bedroom3                2976  non-null values
 bedroom4                2976  non-null values
 bedroom5                2976  non-null values
 car1                    0  non-null values
 clotheswasher1          2976  non-null values
 clotheswasher_dryg1     2976  non-null values
 diningroom1             2976  non-null values
 diningroom2             2976  non-null values
 dishwasher1             2976  non-null values
 disposal1               2976  non-null values
 drye1                   2976  non-null values
 dryg1                   2976  non-null values
 freezer1                2976  non-null values
 furnace1                2976  non-null values
 furnace2                2976  non-null values
 garage1                 2976  non-null values
 garage2                 2976  non-null values
 heater1                 2976  non-null values
 housefan1               2976  non-null values
 icemaker1               2976  non-null values
 jacuzzi1                2976  non-null values
 kitchen1                2976  non-null values
 kitchen2                2976  non-null values
 kitchenapp1             2976  non-null values
 kitchenapp2             2976  non-null values
 lights_plugs1           2976  non-null values
 lights_plugs2           2976  non-null values
 lights_plugs3           2976  non-null values
 lights_plugs4           2976  non-null values
 lights_plugs5           2976  non-null values
 lights_plugs6           2976  non-null values
 livingroom1             2976  non-null values
 livingroom2             2976  non-null values
 microwave1              2976  non-null values
 office1                 2976  non-null values
 outsidelights_plugs1    2976  non-null values
 outsidelights_plugs2    2976  non-null values
 oven1                   2976  non-null values
 oven2                   2976  non-null values
 pool1                   2976  non-null values
 pool2                   2976  non-null values
 poollight1              2976  non-null values
 poolpump1               2976  non-null values
 pump1                   2976  non-null values
 range1                  2976  non-null values
 refrigerator1           2976  non-null values
 refrigerator2           2976  non-null values
 security1               2976  non-null values
 shed1                   2976  non-null values
 sprinkler1              2976  non-null values
 subpanel1               2976  non-null values
 subpanel2               2976  non-null values
 unknown1                2976  non-null values
 unknown2                2976  non-null values
 unknown3                2976  non-null values
 unknown4                2976  non-null values
 use                     2976  non-null values
 utilityroom1            2976  non-null values
 venthood1               2976  non-null values
 waterheater1            2976  non-null values
 waterheater2            2976  non-null values
 winecooler1             2976  non-null values
 dtypes: int64(1), object(72), 9923: <class 'pandas.core.frame.DataFrame'>
 Int64Index: 2976 entries, 41664 to 44639
 Data columns (total 73 columns):
 id                      2976  non-null values
 utc_15min               2976  non-null values
 air1                    2976  non-null values
 air2                    2976  non-null values
 air3                    2976  non-null values
 airwindowunit1          2976  non-null values
 aquarium1               2976  non-null values
 bathroom1               2976  non-null values
 bathroom2               2976  non-null values
 bedroom1                2976  non-null values
 bedroom2                2976  non-null values
 bedroom3                2976  non-null values
 bedroom4                2976  non-null values
 bedroom5                2976  non-null values
 car1                    0  non-null values
 clotheswasher1          2976  non-null values
 clotheswasher_dryg1     2976  non-null values
 diningroom1             2976  non-null values
 diningroom2             2976  non-null values
 dishwasher1             2976  non-null values
 disposal1               2976  non-null values
 drye1                   2976  non-null values
 dryg1                   2976  non-null values
 freezer1                2976  non-null values
 furnace1                2976  non-null values
 furnace2                2976  non-null values
 garage1                 2976  non-null values
 garage2                 2976  non-null values
 heater1                 2976  non-null values
 housefan1               2976  non-null values
 icemaker1               2976  non-null values
 jacuzzi1                2976  non-null values
 kitchen1                2976  non-null values
 kitchen2                2976  non-null values
 kitchenapp1             2976  non-null values
 kitchenapp2             2976  non-null values
 lights_plugs1           2976  non-null values
 lights_plugs2           2976  non-null values
 lights_plugs3           2976  non-null values
 lights_plugs4           2976  non-null values
 lights_plugs5           2976  non-null values
 lights_plugs6           2976  non-null values
 livingroom1             2976  non-null values
 livingroom2             2976  non-null values
 microwave1              2976  non-null values
 office1                 2976  non-null values
 outsidelights_plugs1    2976  non-null values
 outsidelights_plugs2    2976  non-null values
 oven1                   2976  non-null values
 oven2                   2976  non-null values
 pool1                   2976  non-null values
 pool2                   2976  non-null values
 poollight1              2976  non-null values
 poolpump1               2976  non-null values
 pump1                   2976  non-null values
 range1                  2976  non-null values
 refrigerator1           2976  non-null values
 refrigerator2           2976  non-null values
 security1               2976  non-null values
 shed1                   2976  non-null values
 sprinkler1              2976  non-null values
 subpanel1               2976  non-null values
 subpanel2               2976  non-null values
 unknown1                2976  non-null values
 unknown2                2976  non-null values
 unknown3                2976  non-null values
 unknown4                2976  non-null values
 use                     2976  non-null values
 utilityroom1            2976  non-null values
 venthood1               2976  non-null values
 waterheater1            2976  non-null values
 waterheater2            2976  non-null values
 winecooler1             2976  non-null values
 dtypes: int64(1), object(72), 9932: <class 'pandas.core.frame.DataFrame'>
 Int64Index: 2976 entries, 62496 to 65471
 Data columns (total 73 columns):
 id                      2976  non-null values
 utc_15min               2976  non-null values
 air1                    2976  non-null values
 air2                    2976  non-null values
 air3                    2976  non-null values
 airwindowunit1          2976  non-null values
 aquarium1               2976  non-null values
 bathroom1               2976  non-null values
 bathroom2               2976  non-null values
 bedroom1                2976  non-null values
 bedroom2                2976  non-null values
 bedroom3                2976  non-null values
 bedroom4                2976  non-null values
 bedroom5                2976  non-null values
 car1                    0  non-null values
 clotheswasher1          2976  non-null values
 clotheswasher_dryg1     2976  non-null values
 diningroom1             2976  non-null values
 diningroom2             2976  non-null values
 dishwasher1             2976  non-null values
 disposal1               2976  non-null values
 drye1                   2976  non-null values
 dryg1                   2976  non-null values
 freezer1                2976  non-null values
 furnace1                2976  non-null values
 furnace2                2976  non-null values
 garage1                 2976  non-null values
 garage2                 2976  non-null values
 heater1                 2976  non-null values
 housefan1               2976  non-null values
 icemaker1               2976  non-null values
 jacuzzi1                2976  non-null values
 kitchen1                2976  non-null values
 kitchen2                2976  non-null values
 kitchenapp1             2976  non-null values
 kitchenapp2             2976  non-null values
 lights_plugs1           2976  non-null values
 lights_plugs2           2976  non-null values
 lights_plugs3           2976  non-null values
 lights_plugs4           2976  non-null values
 lights_plugs5           2976  non-null values
 lights_plugs6           2976  non-null values
 livingroom1             2976  non-null values
 livingroom2             2976  non-null values
 microwave1              2976  non-null values
 office1                 2976  non-null values
 outsidelights_plugs1    2976  non-null values
 outsidelights_plugs2    2976  non-null values
 oven1                   2976  non-null values
 oven2                   2976  non-null values
 pool1                   2976  non-null values
 pool2                   2976  non-null values
 poollight1              2976  non-null values
 poolpump1               2976  non-null values
 pump1                   2976  non-null values
 range1                  2976  non-null values
 refrigerator1           2976  non-null values
 refrigerator2           2976  non-null values
 security1               2976  non-null values
 shed1                   2976  non-null values
 sprinkler1              2976  non-null values
 subpanel1               2976  non-null values
 subpanel2               2976  non-null values
 unknown1                2976  non-null values
 unknown2                2976  non-null values
 unknown3                2976  non-null values
 unknown4                2976  non-null values
 use                     2976  non-null values
 utilityroom1            2976  non-null values
 venthood1               2976  non-null values
 waterheater1            2976  non-null values
 waterheater2            2976  non-null values
 winecooler1             2976  non-null values
 dtypes: int64(1), object(72), 9934: <class 'pandas.core.frame.DataFrame'>
 Int64Index: 2976 entries, 12409 to 17855
 Data columns (total 73 columns):
 id                      2976  non-null values
 utc_15min               2976  non-null values
 air1                    2976  non-null values
 air2                    2976  non-null values
 air3                    2976  non-null values
 airwindowunit1          2976  non-null values
 aquarium1               2976  non-null values
 bathroom1               2976  non-null values
 bathroom2               2976  non-null values
 bedroom1                2976  non-null values
 bedroom2                2976  non-null values
 bedroom3                2976  non-null values
 bedroom4                2976  non-null values
 bedroom5                2976  non-null values
 car1                    0  non-null values
 clotheswasher1          2976  non-null values
 clotheswasher_dryg1     2976  non-null values
 diningroom1             2976  non-null values
 diningroom2             2976  non-null values
 dishwasher1             2976  non-null values
 disposal1               2976  non-null values
 drye1                   2976  non-null values
 dryg1                   2976  non-null values
 freezer1                2976  non-null values
 furnace1                2976  non-null values
 furnace2                2976  non-null values
 garage1                 2976  non-null values
 garage2                 2976  non-null values
 heater1                 2976  non-null values
 housefan1               2976  non-null values
 icemaker1               2976  non-null values
 jacuzzi1                2976  non-null values
 kitchen1                2976  non-null values
 kitchen2                2976  non-null values
 kitchenapp1             2976  non-null values
 kitchenapp2             2976  non-null values
 lights_plugs1           2976  non-null values
 lights_plugs2           2976  non-null values
 lights_plugs3           2976  non-null values
 lights_plugs4           2976  non-null values
 lights_plugs5           2976  non-null values
 lights_plugs6           2976  non-null values
 livingroom1             2976  non-null values
 livingroom2             2976  non-null values
 microwave1              2976  non-null values
 office1                 2976  non-null values
 outsidelights_plugs1    2976  non-null values
 outsidelights_plugs2    2976  non-null values
 oven1                   2976  non-null values
 oven2                   2976  non-null values
 pool1                   2976  non-null values
 pool2                   2976  non-null values
 poollight1              2976  non-null values
 poolpump1               2976  non-null values
 pump1                   2976  non-null values
 range1                  2976  non-null values
 refrigerator1           2976  non-null values
 refrigerator2           2976  non-null values
 security1               2976  non-null values
 shed1                   2976  non-null values
 sprinkler1              2976  non-null values
 subpanel1               2976  non-null values
 subpanel2               2976  non-null values
 unknown1                2976  non-null values
 unknown2                2976  non-null values
 unknown3                2976  non-null values
 unknown4                2976  non-null values
 use                     2976  non-null values
 utilityroom1            2976  non-null values
 venthood1               2976  non-null values
 waterheater1            2976  non-null values
 waterheater2            2976  non-null values
 winecooler1             2976  non-null values
 dtypes: int64(1), object(72), 9937: <class 'pandas.core.frame.DataFrame'>
 Int64Index: 2976 entries, 70560 to 74399
 Data columns (total 73 columns):
 id                      2976  non-null values
 utc_15min               2976  non-null values
 air1                    2976  non-null values
 air2                    2976  non-null values
 air3                    2976  non-null values
 airwindowunit1          2976  non-null values
 aquarium1               2976  non-null values
 bathroom1               2976  non-null values
 bathroom2               2976  non-null values
 bedroom1                2976  non-null values
 bedroom2                2976  non-null values
 bedroom3                2976  non-null values
 bedroom4                2976  non-null values
 bedroom5                2976  non-null values
 car1                    0  non-null values
 clotheswasher1          2976  non-null values
 clotheswasher_dryg1     2976  non-null values
 diningroom1             2976  non-null values
 diningroom2             2976  non-null values
 dishwasher1             2976  non-null values
 disposal1               2976  non-null values
 drye1                   2976  non-null values
 dryg1                   2976  non-null values
 freezer1                2976  non-null values
 furnace1                2976  non-null values
 furnace2                2976  non-null values
 garage1                 2976  non-null values
 garage2                 2976  non-null values
 heater1                 2976  non-null values
 housefan1               2976  non-null values
 icemaker1               2976  non-null values
 jacuzzi1                2976  non-null values
 kitchen1                2976  non-null values
 kitchen2                2976  non-null values
 kitchenapp1             2976  non-null values
 kitchenapp2             2976  non-null values
 lights_plugs1           2976  non-null values
 lights_plugs2           2976  non-null values
 lights_plugs3           2976  non-null values
 lights_plugs4           2976  non-null values
 lights_plugs5           2976  non-null values
 lights_plugs6           2976  non-null values
 livingroom1             2976  non-null values
 livingroom2             2976  non-null values
 microwave1              2976  non-null values
 office1                 2976  non-null values
 outsidelights_plugs1    2976  non-null values
 outsidelights_plugs2    2976  non-null values
 oven1                   2976  non-null values
 oven2                   2976  non-null values
 pool1                   2976  non-null values
 pool2                   2976  non-null values
 poollight1              2976  non-null values
 poolpump1               2976  non-null values
 pump1                   2976  non-null values
 range1                  2976  non-null values
 refrigerator1           2976  non-null values
 refrigerator2           2976  non-null values
 security1               2976  non-null values
 shed1                   2976  non-null values
 sprinkler1              2976  non-null values
 subpanel1               2976  non-null values
 subpanel2               2976  non-null values
 unknown1                2976  non-null values
 unknown2                2976  non-null values
 unknown3                2976  non-null values
 unknown4                2976  non-null values
 use                     2976  non-null values
 utilityroom1            2976  non-null values
 venthood1               2976  non-null values
 waterheater1            2976  non-null values
 waterheater2            2976  non-null values
 winecooler1             2976  non-null values
 dtypes: int64(1), object(72)}

In [6]:
def plot_ac_over_one_month(d,month):
    pyplt.clf()
    day = days[month]
    x=[]
    for key,val in d.iteritems():
        a1 = [float(y) for y in list(val['air1'])]
        a2 = [float(y) + z for (y,z) in zip(list(val['air2']),a1)]
        a3 = [float(y) + z for (y,z) in zip(list(val['air3']),a2)]
        x = [float(y) + z for (y,z) in zip(list(val['airwindowunit1']),a3)]
    x = [y/25.0 for y in x]
    l = list(range(0,len(x),(len(x)/day)))
    ax = pyplt.figure(figsize=(20,10)).add_subplot(111)
    ax.set_xticks(l)
    ax.set_xticklabels(range(1,day+1))
    title = 'ac_'+month
    pyplt.title(title)
    ax.plot(x)
    pyplt.savefig(title+'.png')

    #pyplt.show()

In [7]:
def plot_heat_over_one_month(d,month):
    pyplt.clf()
    day = days[month]
    x=[]
    for key,val in d.iteritems():
        x = [float(y) for y in list(val['heater1'])]
    x = [y/25.0 for y in x]
    l = list(range(0,len(x),(len(x)/day)))
    ax = pyplt.figure(figsize=(20,10)).add_subplot(111)
    ax.set_xticks(l)
    ax.set_xticklabels(range(1,day+1))
    title = 'heat_'+month
    pyplt.title(title)
    ax.plot(x)
    pyplt.savefig(title+'.png')

    #pyplt.show()

In [8]:
##plot air conditioning for one month
##for a home add all the columns
'''
x=[]
for key,val in dfs_dict.iteritems():
    a1 = [float(y) for y in list(val['air1'])]
    a2 = [float(y) + z for (y,z) in zip(list(val['air2']),a1)]
    a3 = [float(y) + z for (y,z) in zip(list(val['air3']),a2)]
    x = [float(y) + z for (y,z) in zip(list(val['airwindowunit1']),a3)]
    x = [float(y) for y in list(val['heat1'])]
x = [y/25.0 for y in x]
#print x
'''


Out[8]:
"\nx=[]\nfor key,val in dfs_dict.iteritems():\n    a1 = [float(y) for y in list(val['air1'])]\n    a2 = [float(y) + z for (y,z) in zip(list(val['air2']),a1)]\n    a3 = [float(y) + z for (y,z) in zip(list(val['air3']),a2)]\n    x = [float(y) + z for (y,z) in zip(list(val['airwindowunit1']),a3)]\n    x = [float(y) for y in list(val['heat1'])]\nx = [y/25.0 for y in x]\n#print x\n"

In [9]:
##for plotting purposes
'''
l = list(range(0,len(x),(len(x)/31)))
len(l)
pyplt.clf()
'''


Out[9]:
'\nl = list(range(0,len(x),(len(x)/31)))\nlen(l)\npyplt.clf()\n'

In [10]:
#pyplt.clf()

In [11]:
##plotting
##should make this a method
'''
ax = pyplt.figure(figsize=(20,10)).add_subplot(111)
ax.set_xticks(l)
ax.set_xticklabels(range(1,32))
ax.plot(x)
pyplt.savefig('October_avg.pdf')

pyplt.show()
'''


Out[11]:
"\nax = pyplt.figure(figsize=(20,10)).add_subplot(111)\nax.set_xticks(l)\nax.set_xticklabels(range(1,32))\nax.plot(x)\npyplt.savefig('October_avg.pdf')\n\npyplt.show()\n"

In [12]:
#test_building=dfs_dict[8197]

In [12]:
def get_sums(house):
    house=house.drop('id',axis=1)
    #house=house.drop('dataid',axis=1)
    house=house.drop('utc_15min',axis=1)
    sums={}
   # print(house.columns)
    for col in house.columns:
       sums[str(col)]=float(house[col].sum())
    return sums

In [13]:
#get_sums(dfs_dict[8197])

In [14]:
def hvacs(house):
    other = 0 
    hvacs={'air1':0,'air2':0,'air3':0,'airwindowunit1':0,'furnace1':0,'furnace2':0,'heater1':0,'housefan1':0,'venthood1':0, 'other':0,'use':0}
    house2 = house.drop('id',axis=1)
   # house2=house2.drop('dataid',axis=1)
    house2=house2.drop('utc_15min',axis=1)
    
    #print(house2.columns)
    count = 0 
    for col in house2.columns:
        if str(col) in hvacs:
            hvacs[str(col)]=float(house2[col].sum())
        elif col!='use':
            hvacs['other']=float(hvacs['other']) + float(house2[col].sum())
            if not math.isnan(float(house2[col].sum())):
                count = count + float(house2[col].sum())
            hvacs['other']=count
        else:
            hvacs['use']=float(house2[col].sum())
    total_air = hvacs['air1']+hvacs['air2']+hvacs['air3']+hvacs['airwindowunit1']
    hvacs['air']=total_air
    hvacs.pop('air1',None)
    hvacs.pop('air2',None)
    hvacs.pop('air3',None)
    hvacs.pop('airwindowunit1',None)
    hvacs['furnaces']= hvacs['furnace1']+hvacs['furnace2']
    hvacs.pop('furnace1',None)
    hvacs.pop('furnace2',None)
    #print(count)
    
    return hvacs

In [16]:
'''
x=sum(val for (key,val) in hvacs.iteritems() if key!='other')
print(x)
print(1-(hvacs['other']/(x+hvacs['other'])))
'''


Out[16]:
"\nx=sum(val for (key,val) in hvacs.iteritems() if key!='other')\nprint(x)\nprint(1-(hvacs['other']/(x+hvacs['other'])))\n"

In [17]:
#funhouse = d[d.keys()[0]]

In [15]:
#check that use is the total use?
count=0
funhouset = funhouse
funhouset = funhouset.drop('id',axis=1)
   # house2=house2.drop('dataid',axis=1)
funhouset=funhouset.drop('utc_15min',axis=1)
for col in funhouset.columns:
   if col!='use':
       if not math.isnan(float(funhouset[col].sum())):
            count = count + float(funhouset[col].sum())
print count
print float(funhouset['use'].sum())


3843.059
7600.19

In [37]:
for month in months:
    d = query_month(month)
    plot_heat_over_one_month(d,month)


2014-06-16 15:58:59,395 INFO sqlalchemy.engine.base.Engine select version()
INFO:sqlalchemy.engine.base.Engine:select version()
2014-06-16 15:58:59,396 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 15:58:59,485 INFO sqlalchemy.engine.base.Engine select current_schema()
INFO:sqlalchemy.engine.base.Engine:select current_schema()
2014-06-16 15:58:59,486 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 15:58:59,577 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-06-16 15:58:59,578 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 15:58:59,623 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2014-06-16 15:58:59,625 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 15:58:59,748 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
INFO:sqlalchemy.engine.base.Engine:show standard_conforming_strings
2014-06-16 15:58:59,750 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 15:58:59,841 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_01
INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_01
2014-06-16 15:58:59,842 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:01:21,758 INFO sqlalchemy.engine.base.Engine select version()
INFO:sqlalchemy.engine.base.Engine:select version()
2014-06-16 16:01:21,759 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:01:21,847 INFO sqlalchemy.engine.base.Engine select current_schema()
INFO:sqlalchemy.engine.base.Engine:select current_schema()
2014-06-16 16:01:21,849 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:01:21,936 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-06-16 16:01:21,937 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:01:21,983 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2014-06-16 16:01:21,984 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:01:22,121 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
INFO:sqlalchemy.engine.base.Engine:show standard_conforming_strings
2014-06-16 16:01:22,123 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:01:22,212 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_02
INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_02
2014-06-16 16:01:22,213 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:03:25,807 INFO sqlalchemy.engine.base.Engine select version()
INFO:sqlalchemy.engine.base.Engine:select version()
2014-06-16 16:03:25,810 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:03:26,090 INFO sqlalchemy.engine.base.Engine select current_schema()
INFO:sqlalchemy.engine.base.Engine:select current_schema()
2014-06-16 16:03:26,091 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:03:26,327 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-06-16 16:03:26,328 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:03:26,448 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2014-06-16 16:03:26,449 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:03:26,846 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
INFO:sqlalchemy.engine.base.Engine:show standard_conforming_strings
2014-06-16 16:03:26,847 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:03:27,112 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_03
INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_03
2014-06-16 16:03:27,113 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:05:58,687 INFO sqlalchemy.engine.base.Engine select version()
INFO:sqlalchemy.engine.base.Engine:select version()
2014-06-16 16:05:58,688 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:05:58,957 INFO sqlalchemy.engine.base.Engine select current_schema()
INFO:sqlalchemy.engine.base.Engine:select current_schema()
2014-06-16 16:05:58,958 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:05:59,277 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-06-16 16:05:59,278 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:05:59,453 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2014-06-16 16:05:59,454 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:05:59,933 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
INFO:sqlalchemy.engine.base.Engine:show standard_conforming_strings
2014-06-16 16:05:59,933 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:06:00,237 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_04
INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_04
2014-06-16 16:06:00,238 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:08:39,568 INFO sqlalchemy.engine.base.Engine select version()
INFO:sqlalchemy.engine.base.Engine:select version()
2014-06-16 16:08:39,569 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:08:39,875 INFO sqlalchemy.engine.base.Engine select current_schema()
INFO:sqlalchemy.engine.base.Engine:select current_schema()
2014-06-16 16:08:39,876 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:08:40,174 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-06-16 16:08:40,174 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:08:40,319 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2014-06-16 16:08:40,319 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:08:40,767 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
INFO:sqlalchemy.engine.base.Engine:show standard_conforming_strings
2014-06-16 16:08:40,768 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:08:41,050 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_05
INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_05
2014-06-16 16:08:41,051 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:11:21,738 INFO sqlalchemy.engine.base.Engine select version()
INFO:sqlalchemy.engine.base.Engine:select version()
2014-06-16 16:11:21,740 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:11:21,834 INFO sqlalchemy.engine.base.Engine select current_schema()
INFO:sqlalchemy.engine.base.Engine:select current_schema()
2014-06-16 16:11:21,834 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:11:21,923 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-06-16 16:11:21,924 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:11:21,968 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2014-06-16 16:11:21,969 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:11:22,098 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
INFO:sqlalchemy.engine.base.Engine:show standard_conforming_strings
2014-06-16 16:11:22,099 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:11:22,193 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_06
INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_06
2014-06-16 16:11:22,194 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:13:50,629 INFO sqlalchemy.engine.base.Engine select version()
INFO:sqlalchemy.engine.base.Engine:select version()
2014-06-16 16:13:50,630 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:13:50,894 INFO sqlalchemy.engine.base.Engine select current_schema()
INFO:sqlalchemy.engine.base.Engine:select current_schema()
2014-06-16 16:13:50,895 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:13:51,151 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-06-16 16:13:51,152 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:13:51,278 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2014-06-16 16:13:51,280 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:13:51,675 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
INFO:sqlalchemy.engine.base.Engine:show standard_conforming_strings
2014-06-16 16:13:51,676 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:13:51,936 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_07
INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_07
2014-06-16 16:13:51,937 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:16:51,043 INFO sqlalchemy.engine.base.Engine select version()
INFO:sqlalchemy.engine.base.Engine:select version()
2014-06-16 16:16:51,045 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:16:51,334 INFO sqlalchemy.engine.base.Engine select current_schema()
INFO:sqlalchemy.engine.base.Engine:select current_schema()
2014-06-16 16:16:51,335 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:16:51,666 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-06-16 16:16:51,667 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:16:51,789 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2014-06-16 16:16:51,790 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:16:51,991 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
INFO:sqlalchemy.engine.base.Engine:show standard_conforming_strings
2014-06-16 16:16:51,992 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:16:52,124 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_08
INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_08
2014-06-16 16:16:52,125 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:19:48,914 INFO sqlalchemy.engine.base.Engine select version()
INFO:sqlalchemy.engine.base.Engine:select version()
2014-06-16 16:19:48,915 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:19:49,009 INFO sqlalchemy.engine.base.Engine select current_schema()
INFO:sqlalchemy.engine.base.Engine:select current_schema()
2014-06-16 16:19:49,009 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:19:49,099 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-06-16 16:19:49,100 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:19:49,145 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2014-06-16 16:19:49,147 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:19:49,279 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
INFO:sqlalchemy.engine.base.Engine:show standard_conforming_strings
2014-06-16 16:19:49,280 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:19:49,374 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_09
INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_09
2014-06-16 16:19:49,375 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:22:17,248 INFO sqlalchemy.engine.base.Engine select version()
INFO:sqlalchemy.engine.base.Engine:select version()
2014-06-16 16:22:17,249 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:22:17,407 INFO sqlalchemy.engine.base.Engine select current_schema()
INFO:sqlalchemy.engine.base.Engine:select current_schema()
2014-06-16 16:22:17,408 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:22:17,571 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-06-16 16:22:17,572 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:22:17,652 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2014-06-16 16:22:17,653 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:22:17,880 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
INFO:sqlalchemy.engine.base.Engine:show standard_conforming_strings
2014-06-16 16:22:17,882 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:22:18,096 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_10
INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_10
2014-06-16 16:22:18,097 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:24:08,918 INFO sqlalchemy.engine.base.Engine select version()
INFO:sqlalchemy.engine.base.Engine:select version()
2014-06-16 16:24:08,919 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:24:09,049 INFO sqlalchemy.engine.base.Engine select current_schema()
INFO:sqlalchemy.engine.base.Engine:select current_schema()
2014-06-16 16:24:09,050 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:24:09,168 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-06-16 16:24:09,169 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:24:09,247 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2014-06-16 16:24:09,248 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:24:09,641 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
INFO:sqlalchemy.engine.base.Engine:show standard_conforming_strings
2014-06-16 16:24:09,642 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 16:24:09,921 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_11
INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_11
2014-06-16 16:24:09,922 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
<matplotlib.figure.Figure at 0x15ec6e3d0>
<matplotlib.figure.Figure at 0x133f67890>
<matplotlib.figure.Figure at 0x16834ced0>
<matplotlib.figure.Figure at 0x115069810>
<matplotlib.figure.Figure at 0x135863710>
<matplotlib.figure.Figure at 0x10b3c6a50>
<matplotlib.figure.Figure at 0x112cc1b10>
<matplotlib.figure.Figure at 0x118bd2f90>
<matplotlib.figure.Figure at 0x14c372d50>
<matplotlib.figure.Figure at 0x148e4ea90>
<matplotlib.figure.Figure at 0x133963850>

In [8]:
d = query_month('01')


2014-06-17 09:57:17,037 INFO sqlalchemy.engine.base.Engine select version()
INFO:sqlalchemy.engine.base.Engine:select version()
2014-06-17 09:57:17,038 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-17 09:57:17,127 INFO sqlalchemy.engine.base.Engine select current_schema()
INFO:sqlalchemy.engine.base.Engine:select current_schema()
2014-06-17 09:57:17,128 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-17 09:57:17,212 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-06-17 09:57:17,213 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-17 09:57:17,261 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2014-06-17 09:57:17,262 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-17 09:57:17,392 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
INFO:sqlalchemy.engine.base.Engine:show standard_conforming_strings
2014-06-17 09:57:17,393 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-17 09:57:17,481 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_01
INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_01
2014-06-17 09:57:17,482 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}

In [12]:
hvacs_t = hvacs(d[d.keys()[0]])

In [63]:
plot_hvacs = {key:val for (key,val) in hvacs.iteritems() if val!=0}

In [64]:
fracs = [i for i in plot_hvacs.values()]
labels = [k for k in plot_hvacs.keys()]

In [67]:
ax = pyplt.figure(figsize=(6,6))
pyplt.pie(fracs,labels=labels)
pyplt.title('Percent Hvac')
savefig('test.png')



In [13]:
hvacs_test={'air':0,'furnaces':0,'heater1':0,'housefan1':0,'venthood1':0, 'other':0}
for val in d.values():
    h=hvacs(val)
    for (k,v) in h.iteritems():
        hvacs_test[k]=hvacs_test[k]+h[k]
hvacs_test


Out[13]:
{'air': 6135.7829999999985,
 'furnaces': 6210.662,
 'heater1': 478.561,
 'housefan1': 0.073,
 'other': 116378.40100000001,
 'venthood1': 0.0}

In [14]:
hvacs_test = {k:(val/25) for (k,val) in hvacs_test.iteritems()}

In [15]:
##should make this faster
def get_pie_month(month):
    pyplt.clf()
    d = query_month(month)
    hvacs_test={'air':0,'furnaces':0,'heater1':0,'housefan1':0,'venthood1':0, 'other':0}
    for val in d.values():
        h=hvacs(val)
        h.pop('use',None)
        for (k,v) in h.iteritems():
            hvacs_test[k]=hvacs_test[k]+h[k]
    hvacs_test = {k:(val/25) for (k,val) in hvacs_test.iteritems()}
    plot_hvacs = {key:val for (key,val) in hvacs_test.iteritems() if val!=0}
    fracs = [i for i in plot_hvacs.values()]
    labels = [k for k in plot_hvacs.keys()] 
    ax = pyplt.figure(figsize=(6,6))
    pyplt.pie(fracs,labels=labels)
    title = 'Percent_Hvac_'+month
    pyplt.title(title)
    savefig(title+'.png')

In [26]:
get_pie_month('11')


2014-06-18 08:51:35,159 INFO sqlalchemy.engine.base.Engine select version()
INFO:sqlalchemy.engine.base.Engine:select version()
2014-06-18 08:51:35,161 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-18 08:51:35,253 INFO sqlalchemy.engine.base.Engine select current_schema()
INFO:sqlalchemy.engine.base.Engine:select current_schema()
2014-06-18 08:51:35,254 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-18 08:51:35,343 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-06-18 08:51:35,344 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-18 08:51:35,391 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2014-06-18 08:51:35,392 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-18 08:51:35,524 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
INFO:sqlalchemy.engine.base.Engine:show standard_conforming_strings
2014-06-18 08:51:35,525 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-18 08:51:35,616 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_11
INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_11
2014-06-18 08:51:35,616 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
<matplotlib.figure.Figure at 0x10404ead0>

In [24]:
##for all the houses return the highest appliances
def get_highest(house):
    sums= get_sums(house)
    sums.pop('use',None)
    sums.pop('car1',None)
    sorted_sums = sorted(sums.iteritems(), key=operator.itemgetter(1), reverse = True)
    
    return sorted_sums

In [30]:
##seeing all of the appliances for 
def get_top_5_all(d):
    k = random.choice(d.keys())
    count_highest  = {str(k): 0 for k in d[k].columns}
    for house in d.values():
        apps=get_highest(house)
        for i in range(5):
            count_highest[apps[i][0]]=count_highest[apps[i][0]]+1
    sorted_sums = sorted(count_highest.iteritems(), key=operator.itemgetter(1), reverse = True)        
    return sorted_sums[:5]

In [116]:
h=get_top_5_all()

In [25]:
#month is a string
#d is the dictionary for that month, 
#where the keys are the house ids and the values are dataframes
def write_month(month,d):    
    dir_path = os.path.join(os.getcwd(), 'ranked_apps/' ) 
    #os.makedirs(dir_path)
    filename = month+'.json'
    text = []
    for (h_id,house) in d.iteritems():
        highs_dict = get_highest(house)
        #text_dict[h_id] = highs_dict[2:7]
      #  highs_dict.pop('use',None)
      #  highs_dict.pop('car1',None)
        try_this = {h_id:highs_dict[:5]}
        text.append(str(try_this)+'\n\n') 
    
    with open(os.path.join(dir_path, filename), 'w') as outfile:
        outfile.writelines(text)   
    outfile.close()

In [85]:
###check for cars - how many people have cars 
cars =  [] 
cars_sanity = [] 
for (house_id,house) in dfs_dict.iteritems():
    cars_sanity.append(house['car1'].sum())
    if not(math.isnan(house['car1'].sum())):
        
        cars.append(house_id)
cars
#cars_sanity


Out[85]:
[]

In [ ]:
##is any data missing?

In [182]:
percent_hvac(dfs_dict[9934])


Index([u'air1', u'air2', u'air3', u'airwindowunit1', u'aquarium1', u'bathroom1', u'bathroom2', u'bedroom1', u'bedroom2', u'bedroom3', u'bedroom4', u'bedroom5', u'car1', u'clotheswasher1', u'clotheswasher_dryg1', u'diningroom1', u'diningroom2', u'dishwasher1', u'disposal1', u'drye1', u'dryg1', u'freezer1', u'furnace1', u'furnace2', u'garage1', u'garage2', u'heater1', u'housefan1', u'icemaker1', u'jacuzzi1', u'kitchen1', u'kitchen2', u'kitchenapp1', u'kitchenapp2', u'lights_plugs1', u'lights_plugs2', u'lights_plugs3', u'lights_plugs4', u'lights_plugs5', u'lights_plugs6', u'livingroom1', u'livingroom2', u'microwave1', u'office1', u'outsidelights_plugs1', u'outsidelights_plugs2', u'oven1', u'oven2', u'pool1', u'pool2', u'poollight1', u'poolpump1', u'pump1', u'range1', u'refrigerator1', u'refrigerator2', u'security1', u'shed1', u'sprinkler1', u'subpanel1', u'subpanel2', u'unknown1', u'unknown2', u'unknown3', u'unknown4', u'use', u'utilityroom1', u'venthood1', u'waterheater1', u'waterheater2', u'winecooler1'], dtype='object')
Out[182]:
{'air1': Decimal('1414.214'),
 'air2': Decimal('0'),
 'air3': Decimal('0'),
 'airwindowunit1': Decimal('0'),
 'furnace1': Decimal('308.475'),
 'furnace2': Decimal('31.683'),
 'heater1': Decimal('0'),
 'housefan1': Decimal('0.073'),
 'other': 0,
 'venthood1': Decimal('0')}

In [26]:
##get all months and output the top five appliances for all houses in that month
for month in months:
    d = query_month(month)
    write_month(month,d)


2014-06-16 09:22:47,442 INFO sqlalchemy.engine.base.Engine select version()
INFO:sqlalchemy.engine.base.Engine:select version()
2014-06-16 09:22:47,443 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:22:47,536 INFO sqlalchemy.engine.base.Engine select current_schema()
INFO:sqlalchemy.engine.base.Engine:select current_schema()
2014-06-16 09:22:47,537 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:22:47,627 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-06-16 09:22:47,628 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:22:47,672 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2014-06-16 09:22:47,673 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:22:47,801 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
INFO:sqlalchemy.engine.base.Engine:show standard_conforming_strings
2014-06-16 09:22:47,802 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:22:47,887 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_01
INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_01
2014-06-16 09:22:47,888 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:25:28,448 INFO sqlalchemy.engine.base.Engine select version()
INFO:sqlalchemy.engine.base.Engine:select version()
2014-06-16 09:25:28,449 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:25:28,535 INFO sqlalchemy.engine.base.Engine select current_schema()
INFO:sqlalchemy.engine.base.Engine:select current_schema()
2014-06-16 09:25:28,536 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:25:28,629 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-06-16 09:25:28,630 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:25:28,680 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2014-06-16 09:25:28,682 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:25:28,815 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
INFO:sqlalchemy.engine.base.Engine:show standard_conforming_strings
2014-06-16 09:25:28,816 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:25:28,903 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_02
INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_02
2014-06-16 09:25:28,904 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:27:36,510 INFO sqlalchemy.engine.base.Engine select version()
INFO:sqlalchemy.engine.base.Engine:select version()
2014-06-16 09:27:36,511 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:27:36,595 INFO sqlalchemy.engine.base.Engine select current_schema()
INFO:sqlalchemy.engine.base.Engine:select current_schema()
2014-06-16 09:27:36,596 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:27:36,685 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-06-16 09:27:36,686 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:27:36,730 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2014-06-16 09:27:36,732 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:27:36,870 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
INFO:sqlalchemy.engine.base.Engine:show standard_conforming_strings
2014-06-16 09:27:36,871 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:27:36,960 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_03
INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_03
2014-06-16 09:27:36,961 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:30:05,863 INFO sqlalchemy.engine.base.Engine select version()
INFO:sqlalchemy.engine.base.Engine:select version()
2014-06-16 09:30:05,864 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:30:05,957 INFO sqlalchemy.engine.base.Engine select current_schema()
INFO:sqlalchemy.engine.base.Engine:select current_schema()
2014-06-16 09:30:05,958 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:30:06,047 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-06-16 09:30:06,048 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:30:06,092 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2014-06-16 09:30:06,094 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:30:06,231 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
INFO:sqlalchemy.engine.base.Engine:show standard_conforming_strings
2014-06-16 09:30:06,233 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:30:06,318 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_04
INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_04
2014-06-16 09:30:06,319 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:32:25,193 INFO sqlalchemy.engine.base.Engine select version()
INFO:sqlalchemy.engine.base.Engine:select version()
2014-06-16 09:32:25,194 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:32:25,281 INFO sqlalchemy.engine.base.Engine select current_schema()
INFO:sqlalchemy.engine.base.Engine:select current_schema()
2014-06-16 09:32:25,282 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:32:25,373 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-06-16 09:32:25,374 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:32:25,421 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2014-06-16 09:32:25,422 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:32:25,571 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
INFO:sqlalchemy.engine.base.Engine:show standard_conforming_strings
2014-06-16 09:32:25,572 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:32:25,658 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_05
INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_05
2014-06-16 09:32:25,659 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:34:56,285 INFO sqlalchemy.engine.base.Engine select version()
INFO:sqlalchemy.engine.base.Engine:select version()
2014-06-16 09:34:56,287 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:34:56,375 INFO sqlalchemy.engine.base.Engine select current_schema()
INFO:sqlalchemy.engine.base.Engine:select current_schema()
2014-06-16 09:34:56,376 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:34:56,465 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-06-16 09:34:56,466 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:34:56,512 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2014-06-16 09:34:56,513 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:34:56,655 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
INFO:sqlalchemy.engine.base.Engine:show standard_conforming_strings
2014-06-16 09:34:56,656 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:34:56,741 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_06
INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_06
2014-06-16 09:34:56,742 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:37:16,609 INFO sqlalchemy.engine.base.Engine select version()
INFO:sqlalchemy.engine.base.Engine:select version()
2014-06-16 09:37:16,610 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:37:16,702 INFO sqlalchemy.engine.base.Engine select current_schema()
INFO:sqlalchemy.engine.base.Engine:select current_schema()
2014-06-16 09:37:16,703 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:37:16,809 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-06-16 09:37:16,810 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:37:16,889 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2014-06-16 09:37:16,890 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:37:17,120 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
INFO:sqlalchemy.engine.base.Engine:show standard_conforming_strings
2014-06-16 09:37:17,120 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:37:17,349 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_07
INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_07
2014-06-16 09:37:17,350 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:39:44,602 INFO sqlalchemy.engine.base.Engine select version()
INFO:sqlalchemy.engine.base.Engine:select version()
2014-06-16 09:39:44,604 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:39:44,689 INFO sqlalchemy.engine.base.Engine select current_schema()
INFO:sqlalchemy.engine.base.Engine:select current_schema()
2014-06-16 09:39:44,690 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:39:44,780 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-06-16 09:39:44,781 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:39:44,829 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2014-06-16 09:39:44,829 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:39:44,964 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
INFO:sqlalchemy.engine.base.Engine:show standard_conforming_strings
2014-06-16 09:39:44,965 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:39:45,060 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_08
INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_08
2014-06-16 09:39:45,061 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:42:13,730 INFO sqlalchemy.engine.base.Engine select version()
INFO:sqlalchemy.engine.base.Engine:select version()
2014-06-16 09:42:13,731 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:42:13,821 INFO sqlalchemy.engine.base.Engine select current_schema()
INFO:sqlalchemy.engine.base.Engine:select current_schema()
2014-06-16 09:42:13,822 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:42:13,906 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-06-16 09:42:13,907 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:42:13,956 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2014-06-16 09:42:13,957 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:42:14,085 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
INFO:sqlalchemy.engine.base.Engine:show standard_conforming_strings
2014-06-16 09:42:14,086 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:42:14,175 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_09
INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_09
2014-06-16 09:42:14,176 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:44:34,372 INFO sqlalchemy.engine.base.Engine select version()
INFO:sqlalchemy.engine.base.Engine:select version()
2014-06-16 09:44:34,373 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:44:34,465 INFO sqlalchemy.engine.base.Engine select current_schema()
INFO:sqlalchemy.engine.base.Engine:select current_schema()
2014-06-16 09:44:34,466 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:44:34,557 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-06-16 09:44:34,559 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:44:34,605 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2014-06-16 09:44:34,606 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:44:34,734 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
INFO:sqlalchemy.engine.base.Engine:show standard_conforming_strings
2014-06-16 09:44:34,735 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:44:34,827 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_10
INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_10
2014-06-16 09:44:34,828 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:46:24,326 INFO sqlalchemy.engine.base.Engine select version()
INFO:sqlalchemy.engine.base.Engine:select version()
2014-06-16 09:46:24,327 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:46:24,415 INFO sqlalchemy.engine.base.Engine select current_schema()
INFO:sqlalchemy.engine.base.Engine:select current_schema()
2014-06-16 09:46:24,416 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:46:24,510 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-06-16 09:46:24,511 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:46:24,557 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2014-06-16 09:46:24,558 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:46:24,684 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
INFO:sqlalchemy.engine.base.Engine:show standard_conforming_strings
2014-06-16 09:46:24,685 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:46:24,772 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_11
INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_11
2014-06-16 09:46:24,773 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:48:42,297 INFO sqlalchemy.engine.base.Engine select version()
INFO:sqlalchemy.engine.base.Engine:select version()
2014-06-16 09:48:42,298 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:48:42,632 INFO sqlalchemy.engine.base.Engine select current_schema()
INFO:sqlalchemy.engine.base.Engine:select current_schema()
2014-06-16 09:48:42,633 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:48:42,722 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-06-16 09:48:42,723 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:48:42,771 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2014-06-16 09:48:42,773 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:48:42,902 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
INFO:sqlalchemy.engine.base.Engine:show standard_conforming_strings
2014-06-16 09:48:42,903 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:48:42,991 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_12
INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_12
2014-06-16 09:48:42,992 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 09:48:43,042 INFO sqlalchemy.engine.base.Engine ROLLBACK
INFO:sqlalchemy.engine.base.Engine:ROLLBACK
---------------------------------------------------------------------------
ProgrammingError                          Traceback (most recent call last)
<ipython-input-26-5c48eb3ffb70> in <module>()
      1 ##get all months and output the top five appliances for all houses in that month
      2 for month in months:
----> 3     d = query_month(month)
      4     write_month(month,d)

<ipython-input-7-33ba5dc70d56> in query_month(month)
      4     eng = create_engine(url)
      5     eng.echo=True
----> 6     eng_object=eng.execute(query)
      7     df = pd.DataFrame.from_records(eng_object.fetchall())
      8     df.columns = eng_object.keys()

/Users/sabina/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in execute(self, statement, *multiparams, **params)
   1680 
   1681         connection = self.contextual_connect(close_with_result=True)
-> 1682         return connection.execute(statement, *multiparams, **params)
   1683 
   1684     def scalar(self, statement, *multiparams, **params):

/Users/sabina/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in execute(self, object, *multiparams, **params)
    710         """
    711         if isinstance(object, util.string_types[0]):
--> 712             return self._execute_text(object, multiparams, params)
    713         try:
    714             meth = object._execute_on_connection

/Users/sabina/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _execute_text(self, statement, multiparams, params)
    859             statement,
    860             parameters,
--> 861             statement, parameters
    862         )
    863         if self._has_events or self.engine._has_events:

/Users/sabina/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _execute_context(self, dialect, constructor, statement, parameters, *args)
    945                                 parameters,
    946                                 cursor,
--> 947                                 context)
    948 
    949         if self._has_events or self.engine._has_events:

/Users/sabina/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _handle_dbapi_exception(self, e, statement, parameters, cursor, context)
   1106                                         self.dialect.dbapi.Error,
   1107                                         connection_invalidated=self._is_disconnect),
-> 1108                                     exc_info
   1109                                 )
   1110 

/Users/sabina/anaconda/lib/python2.7/site-packages/sqlalchemy/util/compat.pyc in raise_from_cause(exception, exc_info)
    183             exc_info = sys.exc_info()
    184         exc_type, exc_value, exc_tb = exc_info
--> 185         reraise(type(exception), exception, tb=exc_tb)
    186 
    187 if py3k:

/Users/sabina/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _execute_context(self, dialect, constructor, statement, parameters, *args)
    938                                      statement,
    939                                      parameters,
--> 940                                      context)
    941         except Exception as e:
    942             self._handle_dbapi_exception(

/Users/sabina/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/default.pyc in do_execute(self, cursor, statement, parameters, context)
    433 
    434     def do_execute(self, cursor, statement, parameters, context=None):
--> 435         cursor.execute(statement, parameters)
    436 
    437     def do_execute_no_params(self, cursor, statement, context=None):

ProgrammingError: (ProgrammingError) relation "PecanStreet_CuratedSets.group1_disaggregated_2013_12" does not exist
LINE 1: select * from "PecanStreet_CuratedSets".group1_disaggregated...
                      ^
 'select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_12' {}

In [31]:
##get most common 5 highs 
dir_path = os.path.join(os.getcwd(), 'ranked_apps/' ) 
for month in months:    
    filename = month+'_most_common_5.json'
    d = query_month(month)
    text = (str(get_top_5_all(d))+'\n\n')
    with open(os.path.join(dir_path, filename), 'w') as outfile:
        outfile.writelines(text)   
    outfile.close()


2014-06-16 10:37:22,835 INFO sqlalchemy.engine.base.Engine select version()
INFO:sqlalchemy.engine.base.Engine:select version()
2014-06-16 10:37:22,836 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 10:37:23,051 INFO sqlalchemy.engine.base.Engine select current_schema()
INFO:sqlalchemy.engine.base.Engine:select current_schema()
2014-06-16 10:37:23,052 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 10:37:23,255 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-06-16 10:37:23,257 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 10:37:23,372 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2014-06-16 10:37:23,374 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 10:37:23,840 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
INFO:sqlalchemy.engine.base.Engine:show standard_conforming_strings
2014-06-16 10:37:23,841 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 10:37:24,080 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_01
INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_01
2014-06-16 10:37:24,081 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 10:41:21,629 INFO sqlalchemy.engine.base.Engine select version()
INFO:sqlalchemy.engine.base.Engine:select version()
2014-06-16 10:41:21,630 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 10:41:21,926 INFO sqlalchemy.engine.base.Engine select current_schema()
INFO:sqlalchemy.engine.base.Engine:select current_schema()
2014-06-16 10:41:21,927 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 10:41:22,256 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-06-16 10:41:22,258 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 10:41:22,425 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2014-06-16 10:41:22,426 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 10:41:22,814 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
INFO:sqlalchemy.engine.base.Engine:show standard_conforming_strings
2014-06-16 10:41:22,816 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 10:41:23,069 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_02
INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_02
2014-06-16 10:41:23,070 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 10:45:02,446 INFO sqlalchemy.engine.base.Engine select version()
INFO:sqlalchemy.engine.base.Engine:select version()
2014-06-16 10:45:02,447 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 10:45:02,728 INFO sqlalchemy.engine.base.Engine select current_schema()
INFO:sqlalchemy.engine.base.Engine:select current_schema()
2014-06-16 10:45:02,729 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 10:45:03,010 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-06-16 10:45:03,011 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 10:45:03,058 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2014-06-16 10:45:03,059 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 10:45:03,191 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
INFO:sqlalchemy.engine.base.Engine:show standard_conforming_strings
2014-06-16 10:45:03,192 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 10:45:03,281 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_03
INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_03
2014-06-16 10:45:03,282 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 10:49:16,987 INFO sqlalchemy.engine.base.Engine select version()
INFO:sqlalchemy.engine.base.Engine:select version()
2014-06-16 10:49:16,988 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 10:49:17,240 INFO sqlalchemy.engine.base.Engine select current_schema()
INFO:sqlalchemy.engine.base.Engine:select current_schema()
2014-06-16 10:49:17,242 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 10:49:17,470 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-06-16 10:49:17,471 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 10:49:17,608 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2014-06-16 10:49:17,609 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 10:49:18,043 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
INFO:sqlalchemy.engine.base.Engine:show standard_conforming_strings
2014-06-16 10:49:18,044 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 10:49:18,320 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_04
INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_04
2014-06-16 10:49:18,321 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 10:52:48,206 INFO sqlalchemy.engine.base.Engine select version()
INFO:sqlalchemy.engine.base.Engine:select version()
2014-06-16 10:52:48,208 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 10:52:48,517 INFO sqlalchemy.engine.base.Engine select current_schema()
INFO:sqlalchemy.engine.base.Engine:select current_schema()
2014-06-16 10:52:48,518 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 10:52:48,839 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-06-16 10:52:48,840 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 10:52:48,954 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2014-06-16 10:52:48,956 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 10:52:49,090 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
INFO:sqlalchemy.engine.base.Engine:show standard_conforming_strings
2014-06-16 10:52:49,092 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 10:52:49,180 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_05
INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_05
2014-06-16 10:52:49,181 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 10:56:51,193 INFO sqlalchemy.engine.base.Engine select version()
INFO:sqlalchemy.engine.base.Engine:select version()
2014-06-16 10:56:51,194 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 10:56:51,469 INFO sqlalchemy.engine.base.Engine select current_schema()
INFO:sqlalchemy.engine.base.Engine:select current_schema()
2014-06-16 10:56:51,470 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 10:56:51,655 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-06-16 10:56:51,656 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 10:56:51,701 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2014-06-16 10:56:51,702 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 10:56:51,833 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
INFO:sqlalchemy.engine.base.Engine:show standard_conforming_strings
2014-06-16 10:56:51,834 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 10:56:51,928 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_06
INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_06
2014-06-16 10:56:51,929 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 11:00:31,898 INFO sqlalchemy.engine.base.Engine select version()
INFO:sqlalchemy.engine.base.Engine:select version()
2014-06-16 11:00:31,899 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 11:00:32,219 INFO sqlalchemy.engine.base.Engine select current_schema()
INFO:sqlalchemy.engine.base.Engine:select current_schema()
2014-06-16 11:00:32,220 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 11:00:32,530 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-06-16 11:00:32,531 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 11:00:32,601 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2014-06-16 11:00:32,602 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 11:00:32,832 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
INFO:sqlalchemy.engine.base.Engine:show standard_conforming_strings
2014-06-16 11:00:32,833 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 11:00:33,013 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_07
INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_07
2014-06-16 11:00:33,014 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 11:04:20,521 INFO sqlalchemy.engine.base.Engine select version()
INFO:sqlalchemy.engine.base.Engine:select version()
2014-06-16 11:04:20,522 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 11:04:20,777 INFO sqlalchemy.engine.base.Engine select current_schema()
INFO:sqlalchemy.engine.base.Engine:select current_schema()
2014-06-16 11:04:20,778 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 11:04:21,041 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-06-16 11:04:21,042 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 11:04:21,167 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2014-06-16 11:04:21,168 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 11:04:21,574 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
INFO:sqlalchemy.engine.base.Engine:show standard_conforming_strings
2014-06-16 11:04:21,575 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 11:04:21,855 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_08
INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_08
2014-06-16 11:04:21,856 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 11:08:11,448 INFO sqlalchemy.engine.base.Engine select version()
INFO:sqlalchemy.engine.base.Engine:select version()
2014-06-16 11:08:11,450 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 11:08:11,542 INFO sqlalchemy.engine.base.Engine select current_schema()
INFO:sqlalchemy.engine.base.Engine:select current_schema()
2014-06-16 11:08:11,543 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 11:08:11,632 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-06-16 11:08:11,633 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 11:08:11,682 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2014-06-16 11:08:11,683 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 11:08:11,816 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
INFO:sqlalchemy.engine.base.Engine:show standard_conforming_strings
2014-06-16 11:08:11,817 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 11:08:11,903 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_09
INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_09
2014-06-16 11:08:11,904 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 11:11:52,117 INFO sqlalchemy.engine.base.Engine select version()
INFO:sqlalchemy.engine.base.Engine:select version()
2014-06-16 11:11:52,118 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 11:11:52,426 INFO sqlalchemy.engine.base.Engine select current_schema()
INFO:sqlalchemy.engine.base.Engine:select current_schema()
2014-06-16 11:11:52,427 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 11:11:52,758 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-06-16 11:11:52,759 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 11:11:52,885 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2014-06-16 11:11:52,887 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 11:11:53,235 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
INFO:sqlalchemy.engine.base.Engine:show standard_conforming_strings
2014-06-16 11:11:53,236 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 11:11:53,494 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_10
INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_10
2014-06-16 11:11:53,495 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 11:14:32,839 INFO sqlalchemy.engine.base.Engine select version()
INFO:sqlalchemy.engine.base.Engine:select version()
2014-06-16 11:14:32,840 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 11:14:33,030 INFO sqlalchemy.engine.base.Engine select current_schema()
INFO:sqlalchemy.engine.base.Engine:select current_schema()
2014-06-16 11:14:33,031 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 11:14:33,300 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-06-16 11:14:33,301 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 11:14:33,438 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2014-06-16 11:14:33,439 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 11:14:33,840 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
INFO:sqlalchemy.engine.base.Engine:show standard_conforming_strings
2014-06-16 11:14:33,841 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2014-06-16 11:14:34,113 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_11
INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_11
2014-06-16 11:14:34,114 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}

In [ ]:
def pie(month):
    hvacs = percent_hvacs(hv)
    labels = []
    fracs = []
    total_air =