<jupyter_start><jupyter_text>Tanzanian Ministry of Water Dataset Analysis **Imported libraries**<jupyter_code>import pandas as pd
import numpy as np
import math

from sklearn.experimental import enable_iterative_imputer
from sklearn.preprocessing import LabelEncoder
from sklearn.impute import KNNImputer, IterativeImputer

from plotly.subplots import make_subplots
import plotly.graph_objects as go
import plotly.io as pio
pio.renderers.default = "notebook_connected"

import warnings
warnings.filterwarnings("ignore", category=RuntimeWarning)<jupyter_output><empty_output><jupyter_text>**Initial Data Wrangling**<jupyter_code># Loading in datasets
y_train = pd.read_csv('../assets/data/dependent_vars.csv')
X_train = pd.read_csv('../assets/data/independent_vars.csv')
X_test_final = pd.read_csv('../assets/data/independent_test.csv')
SF = pd.read_csv('../assets/data/SubmissionFormat.csv')

# Creating on single dataframe to work with
df = X_train.merge(y_train, on='id')

# Dropping unused features
df.drop(['extraction_type', 'extraction_type_group'], axis=1, inplace=True)
df.drop('waterpoint_type_group', axis=1, inplace=True)
df.drop(['source', 'source_type'], axis=1, inplace=True)
df.drop('quantity_group', axis=1, inplace=True)
df.drop('water_quality', axis=1, inplace=True)
df.drop('payment_type', axis=1, inplace=True)
df.drop('management', axis=1, inplace=True)
df.drop(['region', 'district_code'], axis=1, inplace=True)
df.drop('num_private', axis=1, inplace=True)
df.drop('wpt_name', axis=1, inplace=True)
df.drop('ward', axis=1, inplace=True)
df.drop('recorded_by', axis=1, inplace=True)
df.drop(['funder'], axis=1, inplace=True)
df.drop(['installer'], axis=1, inplace=True)
df.drop(['subvillage'], axis=1, inplace=True)
df.drop(['scheme_management'], axis=1, inplace=True)
df.drop(['scheme_name'], axis=1, inplace=True)

# Revealing nan values in remaining dataset
df.replace(0, np.nan, inplace=True)
df.replace(-2.000000e-08, np.nan, inplace=True)
df.replace('unknown', np.nan, inplace=True)<jupyter_output><empty_output><jupyter_text>**Encoding and imputing the variables**<jupyter_code># Encoding numeric features with KNNImputer
numeric_features = ['amount_tsh', 'gps_height', 'longitude', 'latitude', 
                    'region_code', 'population', 'public_meeting', 'permit', 'construction_year']

X_numeric = df[numeric_features]

imputer = KNNImputer(n_neighbors=2)
X_numeric = imputer.fit_transform(X_numeric)

X_numeric = pd.DataFrame(X_numeric, columns=numeric_features)

# Reconstructing the dataframe
df['amount_tsh'] = X_numeric['amount_tsh']
df['gps_height'] = X_numeric['gps_height']
df['longitude'] = X_numeric['longitude']
df['latitude'] = X_numeric['latitude']
df['region_code'] = X_numeric['region_code']
df['population'] = X_numeric['population']
df['public_meeting'] = X_numeric['public_meeting']
df['permit'] = X_numeric['permit']
df['construction_year'] = X_numeric['construction_year']

# Encoding and imputing categorical features with iterativeimputer
categorical_features = ['basin', 'lga', 'extraction_type_class', 'management_group', 
                        'payment', 'quality_group', 'quantity', 'source_class', 'waterpoint_type']

# Label encoding with a trick to keep nan values
df[categorical_features] = df[categorical_features].apply(lambda series: pd.Series(
        LabelEncoder().fit_transform(series[series.notnull()]),
        index=series[series.notnull()].index
        ))

X_categorical = df[categorical_features]

imputer = IterativeImputer()
X_categorical = imputer.fit_transform(X_categorical)
X_categorical = pd.DataFrame(X_categorical, columns=categorical_features)

# Reconstructing the dataframe
df['basin'] = X_categorical['basin']
df['lga'] = X_categorical['lga']
df['extraction_type_class'] = X_categorical['extraction_type_class']
df['management_group'] = X_categorical['management_group']
df['payment'] = X_categorical['payment']
df['quality_group'] = X_categorical['quality_group']
df['quantity'] = X_categorical['quantity']
df['source_class'] = X_categorical['source_class']
df['waterpoint_type'] = X_categorical['waterpoint_type']<jupyter_output><empty_output><jupyter_text>**Date recorded feature engineering**<jupyter_code>df['date_recorded'] = pd.to_datetime(df['date_recorded'], infer_datetime_format=True)
df['year_recorded'] = df['date_recorded'].dt.year
df['month_rec'] = df['date_recorded'].dt.month
df['day_rec'] = df['date_recorded'].dt.day<jupyter_output><empty_output><jupyter_text>**Normalization and cos/sin feature of month and day**<jupyter_code>days_in_a_month = 31 # can potentially be done better (28, 30, 31)
months_in_a_year = 12

# Normalize
df['norm_day'] = 2 * math.pi * df["day_rec"] / df["day_rec"].max()
df['norm_month'] = 2 * math.pi * df['month_rec'] / df['month_rec'].max()

# Sine
df['sin_day'] = np.sin((df.day_rec-1)*(2*np.pi/days_in_a_month))
df['sin_month'] = np.sin((df.month_rec-1)*(2*np.pi/months_in_a_year))

# Cosine
df['cos_day'] = np.cos((df.day_rec-1)*(2*np.pi/days_in_a_month))
df['cos_month'] = np.cos((df.month_rec-1)*(2*np.pi/months_in_a_year))<jupyter_output><empty_output><jupyter_text>**Plotting the new cyclical relationships of day and month**<jupyter_code>df_sample = df.sample(100)


fig = make_subplots(rows=2, cols=2, subplot_titles=("Day", "Month", "Day", "Month"))

# graphing day features (row1, col1)
df_sample = df_sample.sort_values('norm_day').reset_index(drop=True)
fig.add_trace(go.Scatter(x=df_sample.norm_day, y=df_sample.sin_day, name='Sin'), row=1, col=1)
fig.add_trace(go.Scatter(x=df_sample.norm_day, y=df_sample.cos_day, name='Cosine'), row=1, col=1)

# graphing months features (row1, col2)
df_sample = df_sample.sort_values('norm_month').reset_index(drop=True)
fig.add_trace(go.Scatter(x=df_sample.norm_month, y=df_sample.sin_month, name='Sin'), row=1, col=2)
fig.add_trace(go.Scatter(x=df_sample.norm_month, y=df_sample.cos_month, name='Cosine'), row=1, col=2)

# graphing sin and cosine feature relationship
fig.add_trace(go.Scatter(x=df_sample.cos_day, y=df_sample.sin_day, mode="markers"), row=2, col=1)
fig.add_trace(go.Scatter(x=df_sample.cos_month, y=df_sample.sin_month, mode="markers"), row=2, col=2)

fig.update_layout(height=600, width=800, title_text="DateTime Cyclical Sin and Cosine Features")

fig.show()<jupyter_output><empty_output><jupyter_text>**Years in service feature engineering**<jupyter_code>df['years_in_service'] = df['year_recorded'] - df['construction_year']<jupyter_output><empty_output><jupyter_text>**Dropping features not used after engineering of additional features**<jupyter_code>df.drop(['id'], axis=1, inplace=True)
df.drop('date_recorded', axis=1, inplace=True)
df.drop('construction_year', axis=1, inplace=True)
df.drop('year_recorded', axis=1, inplace=True)
df.drop('month_rec', axis=1, inplace=True)
df.drop('day_rec', axis=1, inplace=True)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15875327701718614, 'fraction_numerical': 0.011797261870084475, 'mean_word_length': 8.471724137931034, 'pattern_counts': {'":': 0, '<': 33, '<?xml version=': 0, '>': 33, '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': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '648724', 'license': 'MIT', 'n_tokens_mistral': 2561, 'n_tokens_neox': 2483, 'n_words': 410, 'path': 'notebooks/TMW Dataset Analysis.ipynb', 'repo_name': 'tylerrussin/Tanzanian-Ministry-of-Water-Dataset', 'size': '6866'}