<jupyter_start><jupyter_text>The aim of this notebook is to clean and prepare historical weather data for Dublin for 2018.   The data used was obtained from Open Weather Maps. ***  Import Packages<jupyter_code>import pandas as pd
import datetime
import matplotlib.pyplot as plt
import sqlite3
from sqlalchemy import create_engine<jupyter_output><empty_output><jupyter_text>***  Load Data<jupyter_code>df_weather = pd.read_csv('/home/faye/data/weather/historical_weather_2018.csv')<jupyter_output><empty_output><jupyter_text>***  Contents - [1. Overview of the Dataset](overview)- [2. The Features](features)- [3. Duplicates and Constants](duplicates_and_constants)- [4. Descriptive Statistics](desc_stats)- [5. Plot](plots)- [6. Data Quality Issues](data_quality)- [7. Save to database](db) ***  1. Overview of the Dataset[Back to contents](contents)<jupyter_code># print the number of rows and features
num_rows = df_weather.shape[0]
features = df_weather.shape[1]
print(f"The dataset has {num_rows} rows with {features} features.")
# print the first 5 rows of the dataset
print("The first 5 Rows are:")
df_weather.head(5)
# print the last 5 rows of the dataset
print("The last 5 Rows are:")
df_weather.tail(5)<jupyter_output>The last 5 Rows are:<jupyter_text>***  2. The Features[Back to contents](contents)<jupyter_code># print the data type for each feature
df_weather.dtypes<jupyter_output><empty_output><jupyter_text>- `dt` is a timestamp, I will leave this as a integer and use it to create the features `date` and `time`.- `dt_iso` is the datetime in iso format as a string, this will be obsolete when I create the `date` and `time` features.- We do not need the `timezone`, `city_name`, `lat` or `lon` features, I will leave these and drop them later.- Of the four temperature features we are only concerned with `temp` which will stay as a float.- I will keep the `pressure` as an int.- We are not concerned with either the `sea_level` or `grnd_level` features, these can be dropped.- I will keep the `humidity` feature as an integer.- I will keep `wind_speed` as float and `wind_deg` as integer.- I will keep the two rain features as floats.- I will also keep the two snow features as floats but I am not sure how useful they will be for us.- I will keep clouds_all as integer.- Of the remaining four features we will only keep `weather_id`, `weather_main` and `weather_description`.<jupyter_code># convert 'dt' from timestamp to datetime
df_weather['dt'] = pd.to_datetime(df_weather['dt'], unit='s')
# drop features
features_to_drop = [
    'dt_iso', 'timezone', 'city_name', 'lat', 'lon', 'feels_like',
    'temp_min', 'temp_max', 'sea_level', 'grnd_level', 'weather_icon'
]

df_weather = df_weather.drop(columns=features_to_drop)
# reorder the features
reorder_features = [
    'dt', 'temp', 'pressure', 'humidity', 'wind_speed', 'wind_deg', 
    'rain_1h','rain_3h', 'clouds_all', 'weather_id', 'weather_main', 
    'weather_description', 'snow_1h', 'snow_3h'
]

df_weather = df_weather.reindex(columns=reorder_features)
df_weather.dtypes<jupyter_output><empty_output><jupyter_text>***  3. Duplicates and Constants[Back to contents](contents)<jupyter_code># Find number of duplicate rows
num_duplicate_rows = df_weather.duplicated().sum()
print(f"There are {num_duplicate_rows} duplicated rows in this dataset (excluding the first row).")
num_duplicate_rows_inclusive = df_weather[df_weather.duplicated(keep=False)].shape[0]
print(f"There are {num_duplicate_rows_inclusive} duplicated rows in this dataset (including row that is duplicated).")
# Find number of duplicate columns
df_transposed = df_weather.T
num_duplicate_cols = df_transposed.duplicated().sum()
print(f"There are {num_duplicate_cols} duplicated columns in this dataset (excluding the first column).")
num_duplicate_cols_inclusive = df_transposed[df_transposed.duplicated(keep=False)].shape[0]
print(f"There are {num_duplicate_cols_inclusive} duplicated columns in this dataset (including column that is duplicated).")
# Display duplicate columns
df_duplicate_cols = df_transposed[df_transposed.duplicated(keep=False)]
df_duplicate_cols.T<jupyter_output><empty_output><jupyter_text>> It appears that these three features may be constant columns of null values.<jupyter_code># print the unique values of the three duplicate columns
print(df_weather['rain_3h'].unique())
print(df_weather['snow_1h'].unique())
print(df_weather['snow_3h'].unique())<jupyter_output>[nan]
[nan]
[nan]<jupyter_text>> As these features are duplicates and constant columns of null values they will be dropped.<jupyter_code># drop duplicate constant columns
duplicate_constants = ['rain_3h', 'snow_1h', 'snow_3h']

df_weather = df_weather.drop(columns=duplicate_constants)
# select all continuous features
continuous_features = [
    'temp', 'pressure', 'humidity', 'wind_speed', 
    'wind_deg', 'rain_1h', 'clouds_all', 
]
# select all categorical features
categorical_features = [
    'weather_id', 'weather_main', 'weather_description'
]
# Check for constant columns in categorical features
df_weather[categorical_features].describe().T
# Check for constant columns in continuous features
df_weather[continuous_features].std()<jupyter_output><empty_output><jupyter_text>> There are no constant columns in this dataset as all of the categorical features have more than 1 unique feature and all of the continuous features have a standard deviation greater than 0. ***  4. Descriptive Statistics[Back to contents](contents)  Continuous<jupyter_code># Print table with descriptive statistics for all the continuos features
continuous_feat_desc = df_weather[continuous_features].describe().T
continuous_feat_desc
# check for null values for each continuous feature
print("The sum of null values for Continuous Features")
print("-"*50)
print(df_weather[continuous_features].isnull().sum())<jupyter_output>The sum of null values for Continuous Features
--------------------------------------------------
temp             0
pressure         0
humidity         0
wind_speed       0
wind_deg         0
rain_1h       8734
clouds_all       0
dtype: int64<jupyter_text>> We can see that we have a very high number of missing values for 'rain_1h' as there are a total of 8800 rows in the dataset<jupyter_code># print the % of null values for 'rain_1h'
print("The percentage of null values for rain_1h")
print("-"*50)
print(df_weather['rain_1h'].isnull().sum() / df_weather.shape[0] * 100)
# print the cardinality of each continuous feature
features_cardinality = list(df_weather[continuous_features].columns.values)

print("{0:35} {1}" .format("Feature","Cardinality") )
print("{0:35} {1}" .format("-------","-----------") )

for c in features_cardinality:
    print("{0:35} {1}" .format( c, (len(df_weather[c].unique()))) )<jupyter_output>Feature                             Cardinality
-------                             -----------
temp                                2179
pressure                            63
humidity                            64
wind_speed                          105
wind_deg                            57
rain_1h                             21
clouds_all                          90<jupyter_text>Categorical<jupyter_code># print the descriptive statistics for all the categorical features
categorical_feat_desc = df_weather[categorical_features].describe().T
categorical_feat_desc
# check for null values for each categorical feature
print("The sum of null values for Continuous Features")
print("-"*50)
print(df_weather[categorical_features].isnull().sum())
# frequency table for categorical features
for feat in categorical_features:
    title = "Frequency Table for " + feat + ":"
    print(title)
    print("-"*50)
    print(df_weather[feat].value_counts(normalize=True) * 100)
    print()<jupyter_output>Frequency Table for weather_id:
--------------------------------------------------
803    46.625000
802    14.454545
801    13.625000
500     7.022727
300     3.045455
800     2.863636
520     2.556818
310     2.488636
804     2.113636
521     1.363636
741     1.011364
701     0.875000
620     0.750000
501     0.545455
600     0.340909
612     0.125000
601     0.079545
301     0.045455
311     0.022727
615     0.011364
621     0.011364
502     0.011364
711     0.011364
Name: weather_id, dtype: float64

Frequency Table for weather_main:
--------------------------------------------------
Clouds     76.818182
Rain       11.500000
Drizzle     5.602273
Clear       2.863636
Snow        1.318182
Fog         1.011364
Mist        0.875000
Smoke       0.011364
Name: weather_main, dtype: float64

Frequency Table for weather_description:
--------------------------------------------------
broken clouds                   46.625000
scattered clouds                14.454545
few clouds                 [...]<jupyter_text>> We can see that the most common weather feature is clouds. ***  5. Plots[Back to contents](contents)  Continuous<jupyter_code># Plot histograms for continuous features
df_weather[continuous_features].hist(layout=(4, 2), figsize=(30,30), bins=12)
#plt.savefig('continuous_histograms_1-1.pdf')
#plt.xlabel()
plt.show()<jupyter_output><empty_output><jupyter_text>Categorical<jupyter_code>for col in categorical_features:
        f = df_weather[col].value_counts().plot(kind='bar', figsize=(12,10))
        plt.title(col)
        #plt.ylabel()
        plt.show()<jupyter_output><empty_output><jupyter_text>***  6. Data Quality Issues[Back to contents](contents)  Missing values for 'rain_1h' I will replace the missing values with the value zero.<jupyter_code>df_weather['rain_1h'].fillna(0, inplace=True)<jupyter_output><empty_output><jupyter_text>***  7. Save to db[Back to contents](contents)<jupyter_code># create connection using 'create_engine' and then invoke 'connect'
db_engine = create_engine('sqlite:///dublinbus.db', echo=True)
#sqlite_connection = engine.connect()
# save weather to db
df_weather.to_sql('weather', db_engine)<jupyter_output>2021-07-09 22:04:45,190 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("weather")
2021-07-09 22:04:45,192 INFO sqlalchemy.engine.Engine [raw sql] ()
2021-07-09 22:04:45,193 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info("weather")
2021-07-09 22:04:45,194 INFO sqlalchemy.engine.Engine [raw sql] ()
2021-07-09 22:04:45,198 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2021-07-09 22:04:45,200 INFO sqlalchemy.engine.Engine 
CREATE TABLE weather (
	"index" BIGINT, 
	dt DATETIME, 
	"temp" FLOAT, 
	pressure BIGINT, 
	humidity BIGINT, 
	wind_speed FLOAT, 
	wind_deg BIGINT, 
	rain_1h FLOAT, 
	clouds_all BIGINT, 
	weather_id BIGINT, 
	weather_main TEXT, 
	weather_description TEXT
)


2021-07-09 22:04:45,201 INFO sqlalchemy.engine.Engine [no key 0.00127s] ()
2021-07-09 22:04:45,222 INFO sqlalchemy.engine.Engine CREATE INDEX ix_weather_index ON weather ("index")
2021-07-09 22:04:45,223 INFO sqlalchemy.engine.Engine [no key 0.00137s] ()
2021-07-09 22:04:45,225 INFO sqlalchemy.engine.Eng[...]
{'fraction_non_alphanumeric': 0.12246760357729512, 'fraction_numerical': 0.05694469793757985, 'mean_word_length': 4.052558782849239, 'pattern_counts': {'":': 1, '<': 55, '<?xml version=': 0, '>': 60, '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': '15', 'dir': 'jupyter-structured-clean-dedup', 'id': '259732', 'license': 'FTL', 'n_tokens_mistral': 3917, 'n_tokens_neox': 3395, 'n_words': 1016, 'path': 'Preparation/Historical-weather.ipynb', 'repo_name': 'CityRoute/Data-Analytics-CityRoute', 'size': '10958'}