<jupyter_start><jupyter_text>ELECTRICITY for years 2018-2019, all BUILDING TYPES Foreign keys: One Hot encode categorical features BUILDINGTYPE, YEARBUILT and WARD, exclude DCREALPROPERTYID  Numeric features: sqft, awnd, cldd, htdd, snow Target feature: kbtu CONNECT TO DATABASE:<jupyter_code>user=creds.PGUSER
password=<PASSWORD>
host=creds.PGHOST
port=5432
database=creds.PGDATABASE

engine_str=f"postgresql+psycopg2://{user}:{password}@{host}:{port}/{database}"
engine = create_engine(engine_str)
conn = engine.raw_connection()
print('Connected')
cur = conn.cursor()
print('Cursor created')<jupyter_output>Connected
Cursor created<jupyter_text>EXTRACT DATASET:<jupyter_code>query='''
SELECT b.kbtu
      ,b.REPORTEDBUILDINGGROSSFLOORAREA
      ,b.dcrealpropertyid
      ,b.ward
      ,b.yearbuilt
      ,b.primarypropertytype_selfselect
      ,b.elegas
      ,n.awnd
      ,n.cldd
      ,n.htdd
      ,n.snow
      ,n.tavg
      ,n.wdf2
      ,n.wdf5
      ,n.wsf2
      ,n.wsf5
      ,n.date 
FROM buildings_data b
LEFT OUTER join noaa_data n
ON b.REPORTINGYEAR = n.WEATHERYEAR
WHERE b.MONTH = n.MONTH
AND b.ELEGAS = 'E'
--AND b.PRIMARYPROPERTYTYPE_SELFSELECT = '14'
AND b.REPORTINGYEAR BETWEEN 2018 AND 2019
AND b.YEARBUILT > 0
AND b.REPORTEDBUILDINGGROSSFLOORAREA > 50000;
'''

data=pd.read_sql(query,conn)
data.head()
data.isnull().values.any()<jupyter_output><empty_output><jupyter_text>FORMAT COLUMNS:<jupyter_code>#CONVERT 'Date' COLUMN TO datetime format

#data["reportingyear"] = data["reportingyear"].astype(str)
#data['month']=data['month'].apply(lambda x: '{0:0>2}'.format(x))
#data['date_time'] = data[['reportingyear', 'month']].agg('-'.join, axis=1)
#data['date_time'] = (data.date_time + "-01")
#data['date_time'] = datetime.strptime('date_time', "%Y-%m-%d")
data['datetime']=pd.to_datetime(data['date'])
data['primarypropertytype_selfselect'].dtype
data['primarypropertytype_selfselect']=data['primarypropertytype_selfselect'].astype('int32')
data.set_index('datetime', inplace=True)
data.head()
data.columns
data.dtypes
import matplotlib.pyplot as plt
import seaborn as sns
plt.figure(figsize=(25,15))
ftr = list(["kbtu", "primarypropertytype_selfselect", "reportedbuildinggrossfloorarea", "ward", "yearbuilt", "awnd", "cldd", "htdd", "snow"])
corrMatrix = data[ftr].corr()
sns.heatmap(corrMatrix, annot=True, fmt='.1f', linewidths=.5)<jupyter_output><empty_output><jupyter_text>DEFINE FEATURES:<jupyter_code>TARGET = "kbtu"
COLS = ['primarypropertytype_selfselect', 'reportedbuildinggrossfloorarea', 'ward', 'yearbuilt', 'awnd', 'cldd', 'htdd', 'snow', 'datetime']

def make_sklearn_data(df=data, target=TARGET, cols=COLS):
    df = df.reset_index()
    X, y = df[cols], df[target]
    return X, y
features = ['primarypropertytype_selfselect', 'reportedbuildinggrossfloorarea', 'ward', 'yearbuilt', 'awnd', 'cldd', 'htdd', 'snow']
X, y = make_sklearn_data(cols=features)
#Rank2D
from yellowbrick.features import Rank2D

# Instantiate the visualizer with the Pearson ranking algorithm
visualizer = Rank2D(algorithm='pearson', features=features, size=(1080, 720))

visualizer.fit(X, y)
visualizer.transform(X)
visualizer.show()
# Instantiate the visualizer with the Covariance algorithm
visualizer = Rank2D(algorithm='covariance', features=features, size=(1080, 720))

visualizer.fit(X, y)
visualizer.transform(X)
visualizer.show()
#Feature Importances
import yellowbrick as yb
from sklearn.ensemble import RandomForestRegressor
from yellowbrick.features import RadViz
from yellowbrick.features import FeatureImportances


model = RandomForestRegressor(n_estimators=10)
viz = FeatureImportances(model, labels=features, size=(1080, 720))

viz.fit(X, y)
# Note: the FeatureImportances visualizer is a model visualizer,
# not a feature visualizer, so it doesn't have a transform method!
viz.show()
#Feature Importances
import yellowbrick as yb
from sklearn.linear_model import Lasso
from yellowbrick.features import RadViz
from yellowbrick.features import FeatureImportances


model = Lasso()
viz = FeatureImportances(model, labels=features, size=(1080, 720))

viz.fit(X, y)
# Note: the FeatureImportances visualizer is a model visualizer,
# not a feature visualizer, so it doesn't have a transform method!
viz.show()
#CYCLIC ENCODER:  to capture temporal cycles (yearly).
from sklearn.base import BaseEstimator, TransformerMixin


class CyclicEncoder(BaseEstimator, TransformerMixin):
    
    def __init__(self, date_extract="month"):
        if date_extract not in {"minute", "hour", "week", "month", "year"}:
            raise ValueError(f"specify correct date component to extract, not {date_extract}")
        self.date_extract = date_extract
    
    def get_date_component(self, x):
        if self.date_extract == "month":
            return x.dt.month
        elif self.date_extract == "year":
            return x.dt.year
        else:
            raise NotImplementedError(f"{self.date_extract} date component not implemented yet")
            
    def fit(self, X, y=None):
        self.cycle_max_ = self.get_date_component(X).max()
        return self

    def transform(self, X,  y=None):
        cols = []
        names = []
        x = self.get_date_component(X)
        xn = 2 * np.pi * x / self.cycle_max_
        cols.append(np.cos(xn))
        names.append(f"{X.name}_cos")
        cols.append(np.sin(xn))
        names.append(f"{X.name}_sin")
        return pd.DataFrame(np.asarray(cols).T, columns=names)

    
ce = CyclicEncoder().fit_transform(data.reset_index()["datetime"])
ce.plot(x="datetime_cos", y="datetime_sin", kind="scatter")
#FEATURE EXTRACTION
from sklearn.base import clone
from sklearn.compose import ColumnTransformer
from sklearn.pipeline import FeatureUnion, Pipeline
from sklearn.preprocessing import OneHotEncoder

extraction = Pipeline([
    ('column_selection', ColumnTransformer([
        ('time_components', FeatureUnion([
            ('month', CyclicEncoder(date_extract='month')), 
            ('year', CyclicEncoder(date_extract='year')), 
        ]), 'datetime'),
        ('ward_one_hot', OneHotEncoder(handle_unknown='ignore'), ['ward']),
        ('yearbuilt_one_hot', OneHotEncoder(handle_unknown='ignore'), ['yearbuilt']),
        ('type_one_hot', OneHotEncoder(handle_unknown='ignore'), ['primarypropertytype_selfselect']),
    ], remainder="passthrough")),
])

def make_energy_pipeline(model, append_transformers=None, fe=extraction):
    pipe = clone(fe)
    
    if append_transformers:
        for step in append_transformers:
            pipe.steps.append(step)
    
    pipe.steps.append(["model", clone(model)])
    return pipe
#Test the Feature Extraction Pipeline
from sklearn.linear_model import LinearRegression
from sklearn.model_selection import train_test_split as tts

X_train, X_test, y_train, y_test = tts(*make_sklearn_data(), test_size=0.2)

model = make_energy_pipeline(LinearRegression())
model.fit(X_train, y_train)
model.score(X_test, y_test)
#TIME SERIES CROSS VALIDATION
from functools import partial
from sklearn.metrics import make_scorer
from sklearn.model_selection import cross_val_score
from sklearn.model_selection import TimeSeriesSplit
from sklearn.metrics import r2_score, mean_squared_error, mean_absolute_error

rmse = partial(mean_squared_error, squared=False)


def time_series_evaluate(model, X, y):
    """
    Performs time series cross validation on the model, returning the
    cross validated r2, mse, and mae of the regressor, along with the 
    final fitted model, fitted on all of the data.
    """
    cv = TimeSeriesSplit(12)
    scores = {}
    
    scores["r2"] = cross_val_score(model, X, y, cv=cv, scoring=make_scorer(r2_score))
    scores["mse"] = cross_val_score(model, X, y, cv=cv, scoring=make_scorer(mean_squared_error))
#     scores["rmse"] = cross_val_score(model, X, y, cv=cv, scoring=make_scorer(rmse))
    scores["mae"] = cross_val_score(model, X, y, cv=cv, scoring=make_scorer(mean_absolute_error))
    
    model.fit(X, y)
    return model, scores
# LINEAR MODEL
X, y = make_sklearn_data()
lm = make_energy_pipeline(LinearRegression())
time_series_evaluate(lm, X, y)
#Second order polynomial regression
from sklearn.linear_model import SGDRegressor
from sklearn.preprocessing import PolynomialFeatures

qm = make_energy_pipeline(SGDRegressor(), [('quad', PolynomialFeatures(2))])
time_series_evaluate(qm, X, y)
#Third order polynomial regression
from sklearn.linear_model import SGDRegressor
from sklearn.preprocessing import PolynomialFeatures

qm = make_energy_pipeline(SGDRegressor(), [('cub', PolynomialFeatures(3))])
time_series_evaluate(qm, X, y)
#Fourth order polynomial regression
#from sklearn.linear_model import SGDRegressor
#from sklearn.preprocessing import PolynomialFeatures

#qm = make_energy_pipeline(SGDRegressor(), [('quar', PolynomialFeatures(4))])
#time_series_evaluate(qm, X, y)
from sklearn.ensemble import RandomForestRegressor

rfm = make_energy_pipeline(RandomForestRegressor(n_estimators=10, max_depth=3))
time_series_evaluate(rfm, X, y)
conn.close()
print('Closed')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12203464636433851, 'fraction_numerical': 0.010592519033432638, 'mean_word_length': 5.311977715877437, 'pattern_counts': {'":': 2, '<': 17, '<?xml version=': 0, '>': 20, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '227665', 'license': 'MIT', 'n_tokens_mistral': 3152, 'n_tokens_neox': 3027, 'n_words': 677, 'path': 'machine_learning/Roman_GLOBAL_ELEC_wardyearbuilttype_kbtu.ipynb', 'repo_name': 'georgetown-analytics/Power-Rangers', 'size': '9069'}