<jupyter_start><jupyter_text>Exploratory Data Analysis and Cleaning * Suspect values and NaN's have been imputed. * Visualization provided when appropriate.* Two features, date_recorded and construction_year, have been synthesized into one engineered feature, years elapsed.  Imports<jupyter_code>import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
from datetime import datetime
%run -i imputing_functions.py
TRAIN_FEATURES = '../data/external/pump-it-up-training-features.csv'
TRAIN_LABELS = '../data/external/pump-it-up-training-labels.csv'
#TRAIN_FEATURES = '../data/external/pump-it-up-test-features.csv'

train_features = pd.read_csv(TRAIN_FEATURES)
train_labels = pd.read_csv(TRAIN_LABELS)
train_full = pd.merge(train_features, train_labels, on='id')

REGIONS = train_features['region'].unique().tolist()
DISTRICTS = train_features['district_code'].unique().tolist()
SEED = 42
NUM_WELLS_TRAINING = train_features.shape[0]

sns.set_palette('colorblind')<jupyter_output><empty_output><jupyter_text>First Look<jupyter_code>train_features.info()
# Number of NaN values in features that have NaN's
feature_na_counts = pd.isna(train_features).sum()
feature_na_counts[feature_na_counts != 0]
# Number of zeros in features that have zeros
features = list(train_features.columns)

for feature in features:
    num_zeros = (train_features[feature] == 0).sum()
    if num_zeros > 0:
        print(feature, num_zeros)
# Majority of wells are 'functional' and only a few are 'functional needs repair'
label_dist = train_labels['status_group'].value_counts(normalize=True)
plt.pie(label_dist, 
        labels = label_dist.index,
        colors = ['yellowgreen', 'salmon', 'khaki'],
        autopct='%.0f%%')
plt.title('Majority of wells are functional', size=14)
plt.show()<jupyter_output><empty_output><jupyter_text>Examining Features  amount_tsh<jupyter_code>train_features['amount_tsh'].describe()
train_features.loc[train_features['amount_tsh'] == 0, 'amount_tsh'] = np.nan
fill_na_median_area(df=train_features, num_col='amount_tsh')<jupyter_output><empty_output><jupyter_text>date_recordeddate_recorded will become synthesized along with construction_year into synthetic feature, years_elapsed.  funder<jupyter_code>train_features['funder'] = train_features['funder'].str.lower()
train_features['funder'] = train_features['funder'].str.replace('  ', ' ')
train_features.loc[train_features['funder'] == '0', 'funder'] = np.nan
single_occurences_to_other(df=train_features, col='funder')
replace_na_region_district_mode(train_features, 'funder')
replace_na_region_mode(train_features, 'funder')
dataset_mode = train_features['funder'].mode()[0]
train_features['funder'].fillna(dataset_mode, inplace=True)
top_ten_funders = train_features['funder'].value_counts()[:10]
top_ten_funders = pd.DataFrame(top_ten_funders)
top_ten_funders.rename(columns = {'funder':'wells_funded'}, 
                       inplace = True)
top_ten_funders['percent_funded'] = top_ten_funders['wells_funded'] / NUM_WELLS_TRAINING * 100
sns.catplot(data=top_ten_funders,
            x=top_ten_funders.index,
            y='percent_funded',
            kind='bar',
            ci=None)
plt.title('Govt of Tanzania Built Almost 19% of All Wells', 
          size=16,
          y=1.08)
plt.xlabel('Top 10 Funders', size=14)
plt.xticks(rotation=45, ha='right')
plt.xlim(-0.7, top_ten_funders.shape[0])
plt.ylabel('Percent of All Wells Funded', size=14)
plt.ylim(0, 20)
plt.yticks([0, 5, 10, 15, 20])
plt.show()<jupyter_output><empty_output><jupyter_text>gps_height<jupyter_code>train_features['gps_height'].describe()
# Impute zeros with median since Tanzania is mountainous country
train_features.loc[train_features['gps_height'] == 0, 'gps_height'] = np.nan
fill_na_median_area(df=train_features, num_col='gps_height')<jupyter_output><empty_output><jupyter_text>installer<jupyter_code>train_features['installer'] = train_features['installer'].str.lower()
train_features['installer'] = train_features['installer'].str.replace('  ', ' ')
train_features.loc[train_features['installer'] == '0', 'installer'] = np.nan
single_occurences_to_other(df=train_features, col='installer')
replace_na_region_district_mode(train_features, 'installer')
replace_na_region_mode(train_features, 'installer')
dataset_mode = train_features['installer'].mode()[0]
train_features['installer'].fillna(dataset_mode, inplace=True)<jupyter_output><empty_output><jupyter_text>latitude and longitude<jupyter_code># 1812 observations from two regions have longitude and latitude of
# zero, which is outside Tanzania.
train_features.loc[(train_features['latitude'] > -0.1), 'latitude'] = 0
train_features.loc[train_features['latitude'] == 0, 'latitude'] = np.nan
train_features.loc[train_features['longitude'] == 0, 'longitude'] = np.nan
print(train_features['latitude'].isna().sum())
print(train_features['longitude'].isna().sum())
fill_na_mean_area(df=train_features, num_col='latitude')
fill_na_mean_area(df=train_features, num_col='longitude')
fig, ax = plt.subplots(figsize=(6, 6))

palette ={'functional': 'mediumseagreen',
          'functional needs repair': 'yellow',
          'non functional': 'red'} 

sns.scatterplot(data=train_full, 
                    x='longitude', 
                    y='latitude', 
                    hue='status_group',
                    palette=palette,
                    style='status_group',
                    alpha=0.2)

ax.set_title('Pockets of Functional and Non Functional Wells',
             size=14,
             y=1.04)
ax.legend(bbox_to_anchor= (0.5, 0.19))
ax.set_xlim(28, 41)
ax.set_xticks(range(28, 41))
ax.set_ylim(-13, 0)
ax.set_yticks(range(-13, 0))
ax.set_aspect(aspect=1)
plt.show()<jupyter_output>No handles with labels found to put in legend.<jupyter_text>wpt_name<jupyter_code>train_features['wpt_name'].value_counts()<jupyter_output><empty_output><jupyter_text>num_private<jupyter_code>percent_num_private_zero = (train_features['num_private'] == 0).sum() / NUM_WELLS_TRAINING * 100
print('{}% of num_private values are zero'.format(round(percent_num_private_zero, 2)))<jupyter_output>98.69% of num_private values are zero<jupyter_text>basin<jupyter_code>print(train_features['basin'].describe(), '\n')
print('Basins:', train_features['basin'].unique().tolist())
wells_by_basin = pd.DataFrame(train_features.groupby('basin')['id'].count())

sns.catplot(data=wells_by_basin,
            x=wells_by_basin.index,
            y='id',
            kind='bar',
            ci=None)
plt.title('Uneven Distribution of Wells by Basin', 
          size=16,
          y=1.08)
plt.xlabel('Basin', size=14)
plt.xticks(rotation=45, ha='right')
plt.xlim(-0.6, wells_by_basin.shape[0])
plt.ylabel('Number of wells', size=14)
plt.show()<jupyter_output><empty_output><jupyter_text>subvillage<jupyter_code># Wards are next smallest geographic area in Tanzania. Sometimes 
# (like in cities) the ward and the village are the same.
train_features['subvillage'].fillna(train_features['ward'], inplace=True)<jupyter_output><empty_output><jupyter_text>region and region_code<jupyter_code>wells_by_region = pd.DataFrame(train_features.groupby('region')['id'].count())

sns.catplot(data=wells_by_region,
            x=wells_by_region.index,
            y='id',
            kind='bar',
            ci=None)
plt.title('Uneven Distribution of Wells by Region', 
          size=16,
          y=1.08)
plt.xlabel('Region', size=14)
plt.xlim(-0.7, wells_by_region.shape[0])
plt.xticks(rotation=45, ha='right')
plt.ylabel('Number of wells', size=14)
plt.show()<jupyter_output><empty_output><jupyter_text>district_code, lga, and ward<jupyter_code># clean data<jupyter_output><empty_output><jupyter_text>population<jupyter_code># Populations of 0 or 1 seem suspicious
for i in range(2):
    print('{} waterpoints have population of {}'.format(
        (train_features['population'] == i).sum(), i))
pre_impute_pop_df = train_features.loc[:, ['id', 'population']]
pre_impute_pop_df.rename(columns = {'population':'pre_impute_pop'}, 
                       inplace = True)
pop_0 = train_features['population'] == 0
pop_1 = train_features['population'] == 1
train_features.loc[pop_0 | pop_1, 'population'] = np.nan
fill_na_median_area(df=train_features, num_col='population')
train_features['population'] = train_features['population'].astype(int)
pop_df = pd.merge(pre_impute_pop_df, 
                  train_features[['id', 'population']],
                  on='id')
pop_df.rename(columns = {'population':'post_impute_pop'}, 
                       inplace = True)

pop_500_pre_impute = pop_df['pre_impute_pop'] <= 500
pop_500_post_impute = pop_df['post_impute_pop'] <= 500
pop_500_df = pop_df[pop_500_pre_impute & pop_500_post_impute]
fig, (ax1, ax2) = plt.subplots(2, sharex=True, figsize=(6, 6))
sns.set_style(style="whitegrid",
              rc= {'patch.edgecolor': 'lightblue',
                   'grid.linestyle': ':'})

plt.subplot(2, 1, 1)
sns.histplot(data=pop_500_df, x='pre_impute_pop')
plt.title('Imputing Population Yields More Plausible Distribution',
          size=14,
          y=1.15)
plt.xlabel('Population Before Imputation')
plt.yticks(range(0, 40000, 10000))

plt.subplot(2, 1, 2)
sns.histplot(data=pop_500_df, x='post_impute_pop')
plt.yticks(range(0, 40000, 10000))
plt.xlabel('Population After Imputation')

plt.tight_layout()
plt.show()<jupyter_output><empty_output><jupyter_text>public_meeting<jupyter_code>print(train_features['public_meeting'].value_counts())
train_features['public_meeting'].fillna(True, inplace=True)<jupyter_output>True     12738
False     1291
Name: public_meeting, dtype: int64<jupyter_text>recorded_by<jupyter_code>train_features['recorded_by'].describe()<jupyter_output><empty_output><jupyter_text>scheme_management and scheme_name<jupyter_code>replace_na_region_district_mode(train_features, 'scheme_management')<jupyter_output><empty_output><jupyter_text>permit<jupyter_code>permit_mode = (train_features['permit'].mode())[0]
print(train_features['permit'].value_counts())
print('Number of NaNs:', train_features['permit'].isna().sum())
replace_na_region_district_mode(train_features, 'permit')
train_features['permit'].isna().sum()
train_features['permit'].fillna(permit_mode, inplace=True)<jupyter_output><empty_output><jupyter_text>construction_year<jupyter_code># Missing values listed as 0
train_features.loc[train_features['construction_year'] == 0, 'construction_year'] = np.nan
print('There are {} waterpoints with construction_year of NaN'.format(
      train_features['construction_year'].isna().sum()))
# Use median to impute due to distribution with long left tail
fill_na_median_area(df=train_features, num_col='construction_year')
train_features['construction_year'] = train_features['construction_year'].astype('int')
train_features['date_recorded'] = pd.to_datetime(train_features['date_recorded'])
train_features['construction_year'] = pd.to_datetime(train_features['construction_year'], format='%Y')
train_features['years_elapsed'] = train_features['date_recorded'].dt.year - train_features['construction_year'].dt.year
train_features[train_features['years_elapsed'] < 0][['date_recorded', 
                                                           'construction_year', 
                                                           'years_elapsed']]
print('Wells with negative years_elapsed:', train_features[train_features['years_elapsed'] < 0].shape[0])
train_features.loc[train_features['years_elapsed'] < 0, 'years_elapsed'] = 0
print('Wells with negative years_elapsed:', train_features[train_features['years_elapsed'] < 0].shape[0])
sns.histplot(data=train_features, x='years_elapsed')
plt.title('Years Between Date of Construction and Date of Data Collection', 
          size=14,
          y=1.08)
plt.xlabel('Number of Years')
plt.show()<jupyter_output><empty_output><jupyter_text>extraction_type, extraction_type_group, extraction_type_class<jupyter_code>train_features.groupby(['extraction_type', 'extraction_type_group', 'extraction_type_class']).size()<jupyter_output><empty_output><jupyter_text>management and management_group<jupyter_code>train_features.groupby(['management', 'management_group']).size()<jupyter_output><empty_output><jupyter_text>payment and payment_type<jupyter_code>train_features.groupby(['payment', 'payment_type']).size()<jupyter_output><empty_output><jupyter_text>water_quality and quality_group<jupyter_code>train_features.groupby(['water_quality', 'quality_group']).size()<jupyter_output><empty_output><jupyter_text>quantity and quantity_group<jupyter_code>train_features.groupby(['quantity', 'quantity_group']).size()<jupyter_output><empty_output><jupyter_text>source, source_type, source_class<jupyter_code>train_features.groupby(['source', 'source_type', 'source_class']).size()<jupyter_output><empty_output><jupyter_text>waterpoint_type and waterpoint_type_group<jupyter_code>train_features.groupby(['waterpoint_type', 'waterpoint_type_group']).size()<jupyter_output><empty_output><jupyter_text>Convert any remaining 'object' features that contain upper case to lower case<jupyter_code>object_features = train_features.select_dtypes(include=["object"]).columns.tolist()
print("Names of object columns : ", object_features)

for feature in object_features:
    train_features[feature] = train_features[feature].str.lower()
    
train_features.iloc[0]
train_features.describe()
now = datetime.now().strftime("%d-%m-%Y %H:%M:%S")
train_features.to_csv('../data/processed/train_features_clean_{}'.format(now, index=False))
#train_features.to_csv('../data/processed/test_features_clean_{}'.format(now, index=False))<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1388949871935602, 'fraction_numerical': 0.01646542261251372, 'mean_word_length': 6.375067458175931, 'pattern_counts': {'":': 0, '<': 112, '<?xml version=': 0, '>': 108, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '27', 'dir': 'jupyter-structured-clean-dedup', 'id': '399176', 'license': 'MIT', 'n_tokens_mistral': 4902, 'n_tokens_neox': 4594, 'n_words': 705, 'path': 'notebooks/.ipynb_checkpoints/pump_it_up_EDA_cleaning-checkpoint.ipynb', 'repo_name': 'kurteulau/pump-it-up', 'size': '13665'}