In [2]:
import psycopg2
import pandas as pd
import sqlalchemy as sa
from sqlalchemy import *
import xlwt
In [3]:
user_name = 'USERNAME'
pw='PASSWORD'
host = "db.wiki-energy.org"
port = "5432"
db = "postgres"
In [4]:
#trying sqlalchemy engine
#url format:
#dialect+driver://username:password@host:port/database
url = "postgresql"+"://"+user_name+":"+pw+"@"+host+":"+port+"/"+db
In [5]:
engine = create_engine(url)
engine.echo=True
In [6]:
#df = pd.DataFrame.from_records(engine.execute(query).fetchall(),index_col=1)
In [7]:
l = engine.execute("""select distinct("PecanStreet_CuratedSets".group1_disaggregated_2013_01.dataid) from "PecanStreet_CuratedSets".group1_disaggregated_2013_01""").fetchall()
l=[e[0] for e in l]
In [8]:
query = """select * from "PecanStreet_CuratedSets".group1_disaggregated_2013_01"""
eng = engine.execute(query)
In [9]:
fall = eng.fetchall()
keys = eng.keys()
In [10]:
df = pd.DataFrame.from_records(fall)
df.columns = keys
In [11]:
df2=df.groupby('dataid')
In [12]:
df.head()
Out[12]:
In [13]:
##should actually hook this up so it is only taking the houses with audit data or some other subset
dict_df={}
for i in df2:
if i[0]!=9923:
dict_df[i[0]]=i
In [14]:
df_temp = (dict_df[9654][1]).drop('dataid',1)
df_temp = df_temp.drop('id',1)
In [20]:
##could write to excel may be easier than hooking this up to Pecan as is
cv=[str(name) for name in list(df_temp.columns.values)]
column_names
column_names
In [25]:
(cv)
Out[25]:
In [23]:
appliance_name_mapping = {
'ksac': 'kitchen outlets',
'tvroom': 'misc',
'garage/refrigerator': 'fridge',
'office': 'desktop computer',
'dryg': 'washer dryer',
'bath': 'bathroom misc',
'genlight': 'lighting',
'oven': 'oven',
'bedroom': 'bedroom misc',
'subl': 'subpanel',
'masterbed': 'bedroom misc',
'bathroom': 'bathroom misc',
'livingroom': 'misc',
'sprinkler': 'sprinkler',
'disposal': 'disposal',
'masterbath': 'bathroom misc',
'microwave': 'microwave',
'drye': 'washer dryer',
'smallappliance': 'misc',
'washer': 'washing machine',
'furnace': 'central heater boiler',
'gri': 'grid',
'lighting&plugs': 'plugs',
'famroom': 'misc',
'dryer': 'washer dryer',
'diningroom': 'misc',
'ove': 'oven',
'backyard': 'misc',
'cooktop': 'cooker',
'refrigerator': 'fridge',
'kitchen': 'kitchen misc',
'dishwasher': 'dishwasher',
'theater': 'entertainment',
'washingmachine': 'washing machine',
'car': 'electric vehicle',
'air': 'air conditioner',
'garage': 'misc',
'range': 'cooker',
'waterheater': 'water heater',
'security': 'security',
'ai': 'air conditioner'
}
In [28]:
##are any of the names the same?
##no
for a in appliance_name_mapping.keys():
if a in cv:
print a
In [ ]:
In [ ]:
In [ ]:
In [ ]:
In [31]:
(sorted(appliance_name_mapping))
Out[31]:
In [32]:
cv
Out[32]:
In [ ]: