# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

# +
import sys
print(f'Interpreter dir: {sys.executable}')
import os

if os.path.basename(os.getcwd()) == 'notebooks':
    os.chdir('../')
    
print(f'Working dir: {os.getcwd()}')
# %load_ext autoreload
# %autoreload 2
# -

import pandas as pd
from pandas_profiling import ProfileReport
pd.set_option('display.max_rows', 200)
import numpy as np


# # Explore AW dataset

# +
df_DMAleaks = pd.read_csv('data/raw/DMALeaks.csv')

# To timestamp
df_DMAleaks['DateRaised'] = pd.to_datetime(df_DMAleaks.DateRaised)

# There are extra commas :/ lets merge extra column
df_DMAleaks['DMAName'] = df_DMAleaks['DMAName'].combine_first(df_DMAleaks['AUX'])
df_DMAleaks = df_DMAleaks.drop('AUX', axis=1)
df_DMAleaks
# -

df_DMAleaks.groupby('DMAName').count()

# - Something is funny with a couple of entries, due to an extra comma in the LeakType - fixed manually

# # Prepare a dataset for classification problem
#
# The target is the leakage at DMA level, which is a binary variable. We can organize the work as follows:
# 1. Find the times when a leakage was raised. Mark with 1
# 2. Add a new column, with a few hours ebfore and after that indicates is_leakage
# 3. We can predict that, or we can predict a (probabbly( easier target one that says "There is a leakage today"
#
# On the features side, we need to align:
# - Inflow
# - Pressure
# - ...
# - Feature engineered columns (past values, moving average, ...)
#

# # Prepare features dataset

# +
df_p = pd.read_csv('data/raw/DMA15MinPressure.csv')
df_p['Timestamp'] = pd.to_datetime(df_p.Timestamp)

df_p = df_p.rename(columns={'Value':'PressureBar'})
print(df_p.info())
df_p

# +
df_inf = pd.read_csv('data/raw/DMA15MinNetFLow.csv')
df_inf['ReferenceTimestamp'] = pd.to_datetime(df_inf.ReferenceTimestamp)

print(df_inf.info())
df_inf
# -

# Lets merge both
df_m = pd.merge(df_p, df_inf, left_on=['DMA','Timestamp'], right_on=['DMA','ReferenceTimestamp'])
df_m = df_m.drop('ReferenceTimestamp', axis=1)

ProfileReport(df_m, title="Pandas Profiling Report",progress_bar=False).to_widgets()

# # Prepare target dataset
#
# Convert the dataset df_DMAleaks into a time-series

# MErged dataset with leaks
# IMPORTANT: tolerance is set to 15 minutes, on the nearest. We select the 2 closes timestamps.
df_ml = pd.merge_asof(df_m.sort_values('Timestamp'),
                      df_DMAleaks.sort_values('DateRaised'),
                      left_by='DMA', right_by='DMAName',
                      left_on ="Timestamp", right_on="DateRaised",
                     tolerance = pd.Timedelta('15min'),
                     direction='nearest')
df_ml

df_ml['is_leakage'] = np.where(~df_ml.DateRaised.isna(),1,0)
df_ml[df_ml['is_leakage'] == 1]

# # Merge with weather data

df_weather = pd.read_csv('data/raw/Norwich_weather_Dec2016Jun2020.csv',parse_dates = ['date_time'])
print(df_weather.columns)
df_weather

# +
df_weather_sel = df_weather[['date_time', 'maxtempC', 'mintempC', 'totalSnow_cm',
                                 'sunHour','uvIndex', 'DewPointC', 'humidity',
                                 'precipMM', 'pressure','tempC']]

# Let's roudn the timestamps to the closets hour and then to the classical merge?
df_m2 = pd.merge_asof(df_ml,
                      df_weather_sel,
                      left_on='Timestamp',
                      right_on='date_time',
                      direction='nearest',
                      tolerance=pd.Timedelta('59min')
                     )
print(f"Shape df_ml:{df_ml.shape} Shape df_weather_sel: {df_weather_sel.shape} Shape result df_m2: {df_m2.shape}")
assert df_ml.shape[0] == df_m2.shape[0], "Something went wrong with the shapes, not 1 to 1 merge"

df_m2 = df_m2.drop('date_time', axis=1)
df_m2      
# -

# Save to csv
df_m2.to_csv('data/processed/DMAVolumePressureWeather.csv', sep=",", index=False)

df_m2.info()

# ## Graphics

from waterhack.visualize import time_vs_y

# +

fig = time_vs_y(df = df_m2,
          time_col = 'Timestamp',
         id_col_name = 'DMA',
         id_list =['NEWSEVMA', 'NORW21MA', 'NEWSTUMA', 'BURYRDMA', 'NORW37MA',
       'NEWCENMA', 'NORFIFMA', 'SWALSHMA', 'NORW18MA', 'NORCRDMA',
       'BLOFLDMA'],
         cols_descr = ['PressureBar','m3Volume','tempC','DewPointC'],
          y_col = 'is_leakage',
         title='Time series of leakages at DMA level')
fig.write_html("./data/figures/time_series.html")

# -



df_m2[df_m2.is_leakage == 1]



StarcoderdataJupyterScriptsDedupFiltered