<jupyter_start><jupyter_text>03.03 - Weather Data - Compile & Impute Missing Values  + Imports & Setup + Compile Raw Weather Data + Review Data + Verify and Clean Up Data + Imputation + Review Imputed Data  Imports and Setup<jupyter_code>import pathlib
import datetime
from os import PathLike
from typing import Union

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
from pandas.plotting import register_matplotlib_converters
register_matplotlib_converters()

%matplotlib inline

PROJECT_DIR = pathlib.Path.cwd().parent.resolve()
RAW_DATA_DIR = PROJECT_DIR / 'data' / '01-raw' /'weather-toronto'
COMPILED_DATA_DIR = PROJECT_DIR / 'data' / '02-compiled' / 'weather-toronto'
IMPUTED_DATA_DIR = PROJECT_DIR / 'data' / '03-imputed' / 'weather-toronto'<jupyter_output><empty_output><jupyter_text>Compile Raw Weather Data<jupyter_code>def format_weather_file(filepath: Union[str, PathLike]) -> pd.DataFrame:
    """
    Read an hourly weather data report formatted as per an Ministry of environment Weather Station file
    Rename the columns to shorter form
    Remove empty rows by looking for blanks in the Temp column
    Returns ascending date indexed DataFrame
    """
    col_renames = {'Date/Time': 'date', 'Temp (°C)': 'temp', 'Dew Point Temp (°C)':'dew_point_temp',
                   'Rel Hum (%)': 'rel_hum', 'Wind Spd (km/h)': 'wind_speed', 'Visibility (km)': 'visibility',
                   'Stn Press (kPa)': 'press', 'Hmdx': 'hmdx', 'Wind Chill': 'wind_chill', 'Weather': 'weather'}
    keeper_cols = list(col_renames.values())
    
    df = pd.read_csv(filepath, sep=',', parse_dates=['Date/Time'], skiprows=15)
    df.rename(columns=col_renames, inplace=True)
    df = df[keeper_cols]
    df.set_index('date', inplace=True, drop=True)
    df.dropna(subset =['temp'], inplace=True)
    df.sort_index(inplace=True)

    return df
# Get the raw directory filepaths
csv_fps = [fp for fp in RAW_DATA_DIR.iterdir() if fp.is_file()]
# create multiple dfs from each filepath
dfs = [format_weather_file(fp) for fp in csv_fps]
# combine into a single DataFrame and save in the interim dir
df = pd.concat(dfs, axis=0)
# We need to drop duplicate index values due to the woverlap in the weather data
df = df.loc[~df.index.duplicated()]
df.sort_index(inplace=True)
df.to_csv(COMPILED_DATA_DIR / "weather_toronto.csv")<jupyter_output><empty_output><jupyter_text>Review Data<jupyter_code>dtypes = {'temp': np.float64, 'dew_point_temp':np.float64, 'rel_hum':np.float64,
          'wind_speed': np.float64, 'visibility': np.float64, 'press': np.float64,
          'hmdx': np.float64, 'wind_chill': np.float64, 'weather': np.object}

df = pd.read_csv(COMPILED_DATA_DIR / "weather_toronto.csv", parse_dates=['date'],
                 dtype=dtypes, index_col=0, infer_datetime_format=True)

df.head()
df.tail()
df.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
DatetimeIndex: 582248 entries, 1953-01-01 00:00:00 to 2019-06-11 23:00:00
Data columns (total 9 columns):
temp              582248 non-null float64
dew_point_temp    582213 non-null float64
rel_hum           582210 non-null float64
wind_speed        582246 non-null float64
visibility        582248 non-null float64
press             582239 non-null float64
hmdx              77410 non-null float64
wind_chill        143782 non-null float64
weather           552716 non-null object
dtypes: float64(8), object(1)
memory usage: 44.4+ MB<jupyter_text>Verify and Clean Up DataThe data should cover every hour from midnight on 1 January 1953, to 23:00 on 11 June 2019There should be no gaps in the temperature data as this is a major driver of pwer demand<jupyter_code># Firstly, make sure the datetime index covers every hour from start to finish  
# create an empty DataFrame that starts at the data start date and ends at the data end date
start = df.index.values[0] ; end = df.index.values[-1] ; freq = '1h'
full_index = pd.date_range(freq=freq, start=start, end=end)
mt_df = pd.DataFrame([np.NaN]* len(full_index), index=full_index)
# Concat this fully indexed mt dataFrame to the data dataFrame with outer join
concat_df = pd.concat([mt_df, df], axis=1,join='outer', sort=True)
# find any places where the temp data is a NaN
gaps = concat_df[concat_df['temp'].isnull()].index.values
len(gaps)
gaps<jupyter_output><empty_output><jupyter_text>This looks pretty good - Only 185 missing values out of 582432 posiible instances ~ 0.03%Furthermore, by inspection of the miossing points above, there are no major consecutive gapsBased on the above two points, I will impute thropugh linear interpolation.  Imputation<jupyter_code>df = concat_df.copy()
df.drop(columns=[0], inplace=True)
interp_cols = ['temp', 'dew_point_temp', 'rel_hum', 'wind_speed', 'visibility', 'press']
df[interp_cols] = df[interp_cols].interpolate(method='linear', limit_direction='forward', axis=0)
df.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
DatetimeIndex: 582433 entries, 1953-01-01 00:00:00 to 2019-06-11 23:00:00
Data columns (total 9 columns):
temp              582433 non-null float64
dew_point_temp    582433 non-null float64
rel_hum           582433 non-null float64
wind_speed        582433 non-null float64
visibility        582433 non-null float64
press             582433 non-null float64
hmdx              77410 non-null float64
wind_chill        143782 non-null float64
weather           552716 non-null object
dtypes: float64(8), object(1)
memory usage: 44.4+ MB<jupyter_text>Review Imputed Data<jupyter_code>plt.style.use('fivethirtyeight')
fig, ax = plt.subplots(figsize=(15,7))
ax.plot(df['temp'], linewidth=1)
ax.set_ylabel('Temperature (C)')
ax.set_title('Temperature at Toronto Airport Hourly: 1 January 1953 - 11 June 2019')
plt.show();
plt.style.use('fivethirtyeight')
fig, ax = plt.subplots(figsize=(15,7))
ax.plot(df.loc['1953-01-01':'1953-12-31', 'temp'], linewidth=1)
ax.set_ylabel('Temperature (C)')
ax.set_title('Temperature at Toronto Airport Hourly: 1 January 1953 - 31 December 1953')
plt.show();
plt.style.use('fivethirtyeight')
fig, ax = plt.subplots(figsize=(15,7))
ax.plot(df.loc['1953-01-01':'1953-12-31', 'rel_hum'], linewidth=1)
ax.set_ylabel('Relative Humidity (%)')
ax.set_ylim((0,105))
ax.set_title('Relative Humidity at Toronto Airport Hourly: 1 January 1953 - 31 December 1953')
plt.show();
df.to_csv(IMPUTED_DATA_DIR / "weather_toronto.csv")
dtypes = {'temp': np.float64, 'dew_point_temp':np.float64, 'rel_hum':np.float64,
          'wind_speed': np.float64, 'visibility': np.float64, 'press': np.float64,
          'hmdx': np.float64, 'wind_chill': np.float64, 'weather': np.object}
df = pd.read_csv(IMPUTED_DATA_DIR / "weather_toronto.csv", parse_dates=True,
                 dtype=dtypes, index_col=0, infer_datetime_format=True)

df.head()
df.tail()
df.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
DatetimeIndex: 582433 entries, 1953-01-01 00:00:00 to 2019-06-11 23:00:00
Data columns (total 9 columns):
temp              582433 non-null float64
dew_point_temp    582433 non-null float64
rel_hum           582433 non-null float64
wind_speed        582433 non-null float64
visibility        582433 non-null float64
press             582433 non-null float64
hmdx              77410 non-null float64
wind_chill        143782 non-null float64
weather           552716 non-null object
dtypes: float64(8), object(1)
memory usage: 44.4+ MB
{'fraction_non_alphanumeric': 0.11621914835909954, 'fraction_numerical': 0.06699213452671549, 'mean_word_length': 4.355846042120552, 'pattern_counts': {'":': 0, '<': 25, '<?xml version=': 0, '>': 26, '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': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '287542', 'license': 'MIT', 'n_tokens_mistral': 2861, 'n_tokens_neox': 2506, 'n_words': 689, 'path': 'notebooks/03.03 - Data - Weather Data - Compile & Impute Missing Values.ipynb', 'repo_name': 'DMacGillivray/ontario-peak-power-forecasting', 'size': '7374'}