In [1]:
# Data Manipulation
import psycopg2
import os
import pandas as pd
import numpy as np
import datetime, time

# Exploratory Data Visualization
import matplotlib.pyplot as plt
import seaborn as sns
import bokeh
import graphlab as gl
gl.canvas.set_target('ipynb')
sns.set_style('white')

# Interactive Graphics

# To communicate with Plotly's server, sign in with credentials file
import plotly.plotly as py

# Useful Python/Plotly tools
import plotly.tools as tls

# Graph objects to piece together plots
from plotly.graph_objs import *

%matplotlib inline


[INFO] GraphLab Create v1.8.3 started. Logging: /tmp/graphlab_server_1457473672.log

In [2]:
# Setup connection to Pecan Street Dataport
try:
    conn = psycopg2.connect("dbname='postgres' user='2c53Epq3kSyQ' host='dataport.pecanstreet.org' port='5434' password=''")
except:
    "Error: Check there aren't any open connections in notebook or pgAdmin"

In [209]:
conn.close()

In [ ]:
# psql -h dataport.pecanstreet.org -p 5434 -U 2c53Epq3kSyQ -W '' postgres

In [69]:
cursor = conn.cursor()

In [78]:
cursor.execute("SELECT dataid, localhour, SUM(use) FROM university.electricity_egauge_hours GROUP BY dataid, localhour")
for row in cursor:
    print row
    if row == None:
        break

In [38]:
electricity_df = pd.read_sql("SELECT localhour, SUM(use) AS usage, SUM(air1) AS cooling, SUM(furnace1) AS heating, \
                             SUM(car1) AS electric_vehicle \
                             FROM university.electricity_egauge_hours \
                             WHERE dataid = 114 AND use > 0  \
                             AND localhour BETWEEN '2013-10-16 00:00:00'::timestamp AND \
                             '2016-02-26 08:00:00'::timestamp \
                             GROUP BY dataid, localhour \
                             ORDER BY localhour", conn)

In [39]:
electricity_df['localhour'] = electricity_df.localhour.apply(pd.to_datetime)

In [56]:
electricity_df.set_index('localhour', inplace=True)

In [40]:
electricity_df.fillna(value=0.0, inplace=True)

In [12]:
# Min: 2013-10-16 00:00:00
# Max: 2016-02-26 08:00:00
# Count: 20,721
electricity_df.tail()


Out[12]:
usage cooling heating electric_vehicle
localhour
2016-02-26 10:00:00 3.634383 0.000000 0.087900 3.357417
2016-02-26 11:00:00 0.876583 0.000000 0.106583 0.581233
2016-02-26 12:00:00 0.349783 0.000000 0.094233 0.000000
2016-02-26 13:00:00 1.110150 0.000167 0.445933 0.000000
2016-02-26 14:00:00 1.182817 0.000867 0.510467 0.000000

In [13]:
electricity_df_nocar = electricity_df[['usage', 'cooling', 'heating']]

In [14]:
electricity_df_car = electricity_df[['usage','electric_vehicle']]

In [15]:
electricity_df_nocar.plot(figsize=(18,9), title="Pecan Street Household 114 Hourly Energy Consumption")
sns.despine();


Turns out house 114 has an electric car!


In [16]:
electricity_df_car.plot(figsize=(18,9), title="Pecan Street Household 114 Hourly Energy Consumption")
sns.despine();



In [ ]:
# Geohash: 30.292432 -97.699662 Austin, TX

In [17]:
from bokeh.io import output_file, output_notebook
from bokeh.plotting import show
from bokeh.models import GMapPlot, GMapOptions, DataRange1d, PanTool, WheelZoomTool, BoxSelectTool

In [18]:
map_options = GMapOptions(lat=30.292432, lng=-97.699662, map_type="roadmap", zoom=11)

In [19]:
plot = GMapPlot(x_range=DataRange1d(), y_range=DataRange1d(), map_options=map_options, title="Austin, TX")

In [20]:
plot.add_tools(PanTool(), WheelZoomTool(), BoxSelectTool())

In [21]:
output_notebook()
# output_file("gmap_plot.html")