<jupyter_start><jupyter_text>PurposeThis notebook explores the connection between well age and status. We will examine both construction year and date recorded with the goal of finding features that provide predictive power. Table of Contents1. Initial EDA2. Date Recorded3. Construction Year4. Age5. Results  1. Initial EDA<jupyter_code>%load_ext autoreload
%autoreload 2

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
X_train = pd.read_csv('../data/train_data.csv')
X_train.head()
y_train = pd.read_csv('../data/train_targets.csv')
y_train
df = X_train.merge(y_train, on='id')
df.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 59400 entries, 0 to 59399
Data columns (total 41 columns):
 #   Column                 Non-Null Count  Dtype  
---  ------                 --------------  -----  
 0   id                     59400 non-null  int64  
 1   amount_tsh             59400 non-null  float64
 2   date_recorded          59400 non-null  object 
 3   funder                 55765 non-null  object 
 4   gps_height             59400 non-null  int64  
 5   installer              55745 non-null  object 
 6   longitude              59400 non-null  float64
 7   latitude               59400 non-null  float64
 8   wpt_name               59400 non-null  object 
 9   num_private            59400 non-null  int64  
 10  basin                  59400 non-null  object 
 11  subvillage             59029 non-null  object 
 12  region                 59400 non-null  object 
 13  region_code            59400 non-null  int64  
 14  district_code          59400 non-null  int64  
 15  lg[...]<jupyter_text>2. Date Recorded:date_recorded - According the the DrivenData [website](https://www.drivendata.org/competitions/7/pump-it-up-data-mining-the-water-table/page/25/), date_recorded refers to the day on which the row was entered into the database.  Convert to datetime object to perform some descriptive statistics<jupyter_code>df['date_recorded'] = pd.to_datetime(df.date_recorded, yearfirst=True)
df.date_recorded.describe()<jupyter_output><empty_output><jupyter_text>Convert to a float so that the describe method will provide quartiles and averages<jupyter_code>def month_year_float(date_time):
    """
    converts a datetime object to a float where whole numbers represent years
    and the month is converted to a fraction of a year and added as a decimal.
    """
    return date_time.year + (date_time.month-1)/12
recorded_year_month = df.date_recorded.map(lambda x: month_year_float(x))
recorded_year_month.describe()<jupyter_output><empty_output><jupyter_text>Most records were taken between 2011 and 2013 but some were taken in 2002. Here's a distribution to see how the dates compare.<jupyter_code>sns.distplot(recorded_year_month)<jupyter_output><empty_output><jupyter_text>It looks like many records were taken in early 2011 and 2012. These could indicate seasonal data collection schedules by the government, or they could represent seasonal waterpoint damage reports by taarifa.  Do we have multiple records for a single well?This is important because it would allow us to zero in on the date at which the well became nonfunctional. We would need to find a unique identifier for each well to determine if the same well shows up more than once. There is no information on whether the id column indicates the id of the record or of the well, but if there are multiple records for each id then it is likely indicating the well. We can test this by checking how many unique values are in the id column. If it does not match the length of the dataframe, then it might be safe to assume that each id represents a well. If it does not match, then we still do not know either way.<jupyter_code>len(df.id) == len(df)<jupyter_output><empty_output><jupyter_text>If the id does not indicate an individual well, we'll look at the waterpoint name column 'wpt_name'. We'll look at the top 45 values to get an idea of how these waterpoints are named.<jupyter_code>df.wpt_name.value_counts()[:45]<jupyter_output><empty_output><jupyter_text>It looks like these names do not indicate unique waterpoints due to the fact that some are named Church, Hospital, or School with no specificity as to which location. As of right now, we do not know of any unique well identifiers.  3. Construction YearIf wells are not properly maintained then we can expect that they will be less likely to be functional they older they get.First, let's take a look at the values in our construction_year column.<jupyter_code>df.construction_year.value_counts()[:10]<jupyter_output><empty_output><jupyter_text>We have many zero values which probably means that the value was not recorded. Here is the percentage of entries that lack a construction year.<jupyter_code>(len(df[df.construction_year == 0])/(len(df)))*100<jupyter_output><empty_output><jupyter_text>35% of our data is missing construction year, which is a significant portion. For the purposes of this notebook we will exclude construction year. We will explore options for imputing these values later on.<jupyter_code>has_year = df[df['construction_year'] !=0]
df.status_group.value_counts()
has_year = df[df['construction_year'] !=0].copy()
has_year.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 38691 entries, 0 to 59399
Data columns (total 41 columns):
 #   Column                 Non-Null Count  Dtype         
---  ------                 --------------  -----         
 0   id                     38691 non-null  int64         
 1   amount_tsh             38691 non-null  float64       
 2   date_recorded          38691 non-null  datetime64[ns]
 3   funder                 37350 non-null  object        
 4   gps_height             38691 non-null  int64         
 5   installer              37345 non-null  object        
 6   longitude              38691 non-null  float64       
 7   latitude               38691 non-null  float64       
 8   wpt_name               38691 non-null  object        
 9   num_private            38691 non-null  int64         
 10  basin                  38691 non-null  object        
 11  subvillage             38681 non-null  object        
 12  region                 38691 non-null  object        
 13  r[...]<jupyter_text>4. AgeBecause the records were taken at different times, it would be more accurate to calculate the age of each well. To get the age of the well we will subtract the construction year from the date record.<jupyter_code>has_year['age'] = has_year.date_recorded.map(lambda x: x.year) - has_year.construction_year
has_year.age.value_counts()<jupyter_output><empty_output><jupyter_text>Because of the negative values at the bottom of the list above, one of our columns is not accurate. Because these negative values represent such a small proportion of our dataset, we will drop them for the purposes of this analysis.<jupyter_code>has_year = has_year[has_year['age']>0]<jupyter_output><empty_output><jupyter_text>Let's subset our data by the well status and see the age distributions for each one. If well age has an impact, we will likely see different distributions in each one. For simplicity, we will look only at functional and nonfucntional groups.<jupyter_code>functional = has_year[has_year['status_group']=='functional']
non_functional =  has_year[has_year['status_group']=='non functional']
plt.style.use('ggplot')

fig, ax = plt.subplots(1,1,figsize=(12,12))

sns.distplot(non_functional.age, ax=ax, label='Non-functional', hist=False, kde_kws = {'shade': True, 'linewidth': 3})
sns.distplot(functional.age, ax=ax, label='Functional', hist=False, kde_kws = {'shade': True, 'linewidth': 3})
ax.tick_params(axis='both', labelsize=35)
ax.set_xlabel('Age in Years', fontsize=35)
ax.set_ylabel('Kernel Density Estimate', fontsize=35)
plt.legend(loc='upper right', fontsize=35)

plt.tight_layout()
# plt.savefig('../reports/figures/age_dist.png', transparent=True)<jupyter_output><empty_output><jupyter_text>The graph above shows a huge spike towards the left of te histogram, indicating a large portion of this subset is made up of relatively new wells between the ages of 0 and 10. The non-functional wells look to have a near uniform distribution with more non-functional than functional wells being between the ages of 25 and 45. Let's look at some descriptive statistics for each subset.  Functional Wells<jupyter_code>functional.age.describe()<jupyter_output><empty_output><jupyter_text>Non Fucntional Wells<jupyter_code>non_functional.age.describe()<jupyter_output><empty_output><jupyter_text>Difference between stats, positive number indicates a higher value for non-functional wells.  5. Results CorrelationLet's see the correlation between age, construction year to see what kind of predicitve power they may have. First we'll need to convert the target to a numeric type. Because we still don't know the relationship between 'functional needs repair' and 'functional', we'll drop it from this part of the analysis.<jupyter_code>has_year = has_year[has_year['status_group'] != 'functional needs repair']
status_code_numeric = {'functional': 0,
                      'non functional': 1}

has_year['status_code'] = has_year.status_group.replace(status_code_numeric)
has_year.status_code.value_counts()
has_year.loc[:,['age', 'construction_year', 'status_code']].corr()<jupyter_output><empty_output><jupyter_text>There seems to be a stronger correlation between age and status, compared to construction year, but still quite low. Because the dependant variable is categorical and the independant variable is continuous we will use a default decision tree to further measure its predicitive power.<jupyter_code>from sklearn.tree import DecisionTreeClassifier

dt = DecisionTreeClassifier()<jupyter_output><empty_output><jupyter_text>Split<jupyter_code>x = has_year.loc[:,['age']]
y = has_year['status_code']<jupyter_output><empty_output><jupyter_text>Fit and score<jupyter_code>dt.fit(x, y)
result = dt.score(x, y)
print(f'Accuracy: {round(result*100, 2)}%')<jupyter_output>Accuracy: 66.59%
{'fraction_non_alphanumeric': 0.07836338418862691, 'fraction_numerical': 0.03219734495740043, 'mean_word_length': 3.7707939508506616, 'pattern_counts': {'":': 0, '<': 72, '<?xml version=': 0, '>': 73, 'https://': 1, 'lorem ipsum': 0, 'www.': 1, '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': '18', 'dir': 'jupyter-structured-clean-dedup', 'id': '391146', 'license': 'MIT', 'n_tokens_mistral': 3186, 'n_tokens_neox': 2860, 'n_words': 1073, 'path': 'notebooks/04a_mef_EDA_year_built.ipynb', 'repo_name': 'sydroth/tanzaniawellclassification', 'size': '10094'}