<jupyter_start><jupyter_text>Dataport Database Extraction Notebook for the Efficiency-BTUs Notebook This notebook will connect to the database and extract the data live and put it into compressed zip files in this directory. You'll need to modify the read_csv calls in that notebook to point at these instead of the ones we've extracted and prepared for you in the /shared directory on the JupyterHub server if you would like to use the ones exported by this notebook.<jupyter_code>#import packages
import pandas as pd
import psycopg2
import sqlalchemy as sqla
import os
import numpy as np
import sys
sys.path.insert(0,'..')
from config.read_config import get_database_config
%matplotlib inline
sys.executable  # shows you your path to the python you're using

# read in db credentials from config/config.txt
# * make sure you add those to the config/config.txt file! *

database_config = get_database_config("../config/config.txt")
# get our DB connection
engine = sqla.create_engine('postgresql://{}:{}@{}:{}/{}'.format(database_config['username'],
                                                                     database_config['password'],
                                                                     database_config['hostname'],
                                                                     database_config['port'],
                                                                     database_config['database']
                                                                     ))
#Select a list of Texas homes from dataport metadata having good gas data availabilty
query = """select distinct dataid, egauge_1min_data_availability, gas_data_availability, grid, solar from other_datasets.metadata 
                                         
                                          where grid = 'yes'
                                          and egauge_1min_min_time <= '2018-03-01' 
                                          and egauge_1min_max_time > '2018-09-01'
                                          and city='Austin'
                                          and (egauge_1min_data_availability like '100%' 
                                               or 
                                               egauge_1min_data_availability like '9%')
                                          and gas_ert_min_time <= '2018-03-01'
                                          and gas_ert_max_time > '2018-09-01'
                                          and
                                              (
                                              gas_data_availability like '100%'
                                              or
                                              gas_data_availability like '9%'
                                              or
                                              gas_data_availability like '8%'
                                              or
                                              gas_data_availability like '7%'
                                              )
                                           LIMIT 50
                                          ;
         """

df = pd.read_sql_query(sqla.text(query), engine)
df
# export to a zipped csv
compression_opts = dict(method='zip',
                        archive_name='efficiency_btus_metadata.csv')
df.to_csv('efficiency_btus_metadata.zip', index=False,
          compression=compression_opts)
# grab dataids and convert them to a string to put into the SQL query
dataids_list = df['dataid'].tolist()
print("{} dataids selected listed here:".format(len(dataids_list)))
dataids_str = ','.join(list(map(str, dataids_list)))
dataids_str
#Pull electricity data for selected homes.
data = """select dataid,localminute::timestamp,solar,grid 
               from electricity.eg_realpower_1min 
               where localminute >= '2018-03-01' and localminute <  '2018-09-01' """
data = data + """AND dataid in ({})""".format(dataids_str)

# create a dataframe with the data from the sql query
data_df = pd.read_sql_query(sqla.text(data), engine)

data_df
# export to a zipped csv
compression_opts = dict(method='zip',
                        archive_name='efficiency_btus_electricity_data.csv')
data_df.to_csv('efficiency_btus_electricity_data.zip', index=False,
          compression=compression_opts)
# Pull gas data for same homes and time period
gas_sql = """select * 
               from water_and_gas.gas_ert 
               where readtime >= '2018-03-01' and readtime <  '2018-09-01' """
gas_sql = gas_sql + """AND dataid in ({})""".format(dataids_str)

# create a dataframe with the data from the sql query
gas_df = pd.read_sql_query(sqla.text(gas_sql), engine)

gas_df
# export to a zipped csv
compression_opts = dict(method='zip',
                        archive_name='efficiency_btus_gas_data.csv')
gas_df.to_csv('efficiency_btus_gas_data.zip', index=False,
          compression=compression_opts)
# lets go get some blucube water data now
#Pull data for selected homes.
water_sql = """SELECT * FROM water_and_gas.blucube_water_data
               where epoch_timestamp >= '2018-03-01' and epoch_timestamp <  '2018-09-01' """
water_sql = water_sql + """AND dataid in ({})""".format(dataids_str)

# create a dataframe with the data from the sql query
water_df = pd.read_sql_query(sqla.text(water_sql), engine)

water_df
# export to a zipped csv
compression_opts = dict(method='zip',
                        archive_name='efficiency_btus_water_data.csv')
water_df.to_csv('efficiency_btus_water_data.zip', index=False,
          compression=compression_opts)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.07745603126665482, 'fraction_numerical': 0.017765144785930005, 'mean_word_length': 1.696360153256705, 'pattern_counts': {'":': 0, '<': 10, '<?xml version=': 0, '>': 10, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 56, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '214981', 'license': 'MIT', 'n_tokens_mistral': 1435, 'n_tokens_neox': 1335, 'n_words': 396, 'path': 'Efficiency/Efficiency-BTUs-data.ipynb', 'repo_name': 'Pecan-Street/JupyterHub-Examples', 'size': '5629'}