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
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]:
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();
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")