notebook.community

Edit and run 





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")