# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

# +
### #A1y1_HousingLoans_TestData-Preparation-Cleaning to make a classification model on Challenge 2

## Goal: Create, clean, & prepare data for accepted in USA dataset (Supervised ML)

# Import Python pkgs pandas, numpy, matplotlib.pyplot, & seaborn
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
import numpy.random as nr
import math

# %matplotlib inline  
# Start of magic command which configures execution environment, to display graphics w/in notebook

# +
# Load test feature dataset, display shape, & explore first 10 rows of Pandas data frame

LoanTestV = pd.read_csv('data/test_values.csv', header=0)
print(LoanTestV.shape)
LoanTestV.head(10)

# +
#Above- We have a total of 22 columns and 500,000 rows in the test feature dataset.

#Below- Review data types for each column
LoanTestV.dtypes

# +
# Find missing values w/in dataset
 # Can be coded as character string, numeric value like -999 or missing value NaN, or a NULL value.
# Then determine how to treat missing values.
 # Remove rows w/ missing values
 # Remove features w/ missing values
 # Impute by replacing values w/ mean or median using simple algorithms, or more complex ones like SMOTE or expectation maximization (EM).
 # Use nearest neighbor, averaging, forward filling or backward filling.

# Shows which features are missing values

#(LoanTestV.astype(np.object) == '?').any()  # This will only work w/ strings
pd.isna(LoanTestV).any()

# +
#Above- Test feature dataset does seem to contain missing values in 7 columns.

#Below- Find out how many values are missing w/in each object/categorical feature.

for col in LoanTestV.columns:
    if LoanTestV[col].dtype == bool:
        count = 0
        count = [count + 1 for x in LoanTestV[col] if pd.isna(x)]
        print(col + ' ' + str(sum(count)))

# +
#Above- Test feature dataset does not seem to contain missing values in boolean columns.

#Below- Find out how many values are missing w/in each float64 feature.

for col in LoanTestV.columns:
    if LoanTestV[col].dtype == float :
        count = 0
        count = [count + 1 for x in LoanTestV[col] if pd.isna(x)]
        print(col + ' ' + str(sum(count)))

# +
#Above-  Test feature dataset does seem to contain missing values in 7 float columns.


#Below- Find out how many values are missing w/in each int64 feature.

for col in LoanTestV.columns:
    if LoanTestV[col].dtype == int :
        count = 0
        count = [count + 1 for x in LoanTestV[col] if pd.isna(x)]
        print(col + ' ' + str(sum(count)))

# +
#Above- Test feature dataset does not seem to contain missing values in integer columns.

#Below- Find out how many values are missing as '-1' w/in this integer column feature.

print(any(LoanTestV.msa_md == -1))
print(LoanTestV.msa_md[LoanTestV.msa_md == -1].count())

# +
#Above- Test feature column msa_md does seem to contain 76,982 missing values.

#Below- Find out how many values are missing as '-1' w/in this integer column feature.

print(any(LoanTestV.state_code == -1))
print(LoanTestV.state_code[LoanTestV.state_code == -1].count())

# +
#Above- Test feature column state_code does seem to contain 19,132 missing values.

#Below- Find out how many values are missing as '-1' w/in this integer column feature.

print(any(LoanTestV.county_code == -1))
print(LoanTestV.county_code[LoanTestV.county_code == -1].count())

# +
#Above- Test feature column county_code does seem to contain 20,566 missing values.

#Below- Examine feature test data row_id for duplicates by checking for unique IDs.

print(LoanTestV.shape)
print(LoanTestV.row_id.unique().shape)

# +
#Above- Test feature dataset does not seem to contain duplicate values.

# Compute & display summary statistics for numeric columns

LoanTestV.describe()

# +
#Below- Examine test data for county_code duplicates by checking for unique IDs.

print(LoanTestV.shape)
print(LoanTestV.county_code.unique().shape)

# +
# Below - Handle missing data
 # Set limit that features which are missing 20% or more of their data are removed.
    
for col in LoanTestV.columns:
    if LoanTestV[col].dtype == float :
        count = 0
        count = [count + 1 for x in LoanTestV[col] if pd.isna(x)]
        prct = ((sum(count))/500000)*100
        print(col + ' ' + str(prct))

# +
#Below- Find out how many values are missing as '-1' w/in this integer column feature.
 # Set limit that features which are missing 20% or more of their data are removed.
    
missing_msa_md = LoanTestV.msa_md[LoanTestV.msa_md == -1].count()
missing_state_code = LoanTestV.state_code[LoanTestV.state_code == -1].count()
missing_county_code = LoanTestV.county_code[LoanTestV.county_code == -1].count()

pctmiss_msa_ma = ((missing_msa_md)/500000)*100
pctmiss_state_code = ((missing_state_code)/500000)*100
pctmiss_county_code = ((missing_county_code)/500000)*100

print(pctmiss_msa_ma)
print(pctmiss_state_code)
print(pctmiss_county_code)

# +
#Above- None of the columns are above the 20% mark in the dataset.

#Below- Save this data to a csv (backup)

LoanTestV.to_csv('data/LoanTest_2019-09-12.csv', index = False, header = True)

# +
# Sorted the backup csv by smallest to largest for state_code then county_code. No data deleted.
# Load the grouped feature dataset, display shape, & explore first 10 rows of Pandas data frame.

LoanTestG = LoanTestV
print(LoanTestG.shape)
LoanTestG.head(10)

# +
#Below- RECHECK-Find out how many values are missing as '-1' w/in this integer column feature.

print(any(LoanTestG.msa_md == -1))
print(LoanTestG.msa_md[LoanTestG.msa_md == -1].count())

# +
#Above- Still have 76,993 missing data for msa_md.

#Below- RECHECK-Find out how many values are missing as '-1' w/in this integer column feature.

print(any(LoanTestG.state_code == -1))
print(LoanTestG.state_code[LoanTestG.state_code == -1].count())

# +
#Above- Still have 19,120 missing data for state_code.

#Below- RECHECK-Find out how many values are missing as '-1' w/in this integer column feature.

print(any(LoanTestG.county_code == -1))
print(LoanTestG.county_code[LoanTestG.county_code == -1].count())

# +
#Above- Still have 20,566 missing data for county_code.

#Below- Since the mean and medians were so different from each other, indicating skewness, we will replace missing values in rows
 # with medians instead of means. 
    
# Calculate median of each column in dataset.

median= LoanTestG.median()
print(median)

# +
#Below- RECHECK missing values for county_code & msa_md in dataset.

LoanTestG

# +
#Fill the remaining numerical missing values with median values for each column.

LoanTestG = LoanTestG.fillna(median)
LoanTestG.shape

# +
# RECHECK- Now double check and look for missing values again in dataset.

for col in LoanTestG.columns:
    if LoanTestG[col].dtype == float:
        count = 0
        count = [count + 1 for x in LoanTestG[col] if pd.isna(x)]
        print(col + ' ' + str(sum(count)))

# +
#Below- RECHECK - Find out how many values are missing as '-1' w/in this integer column feature.
    
missing_msa_md = LoanTestG.msa_md[LoanTestG.msa_md == -1].count()
missing_state_code = LoanTestG.state_code[LoanTestG.state_code == -1].count()
missing_county_code = LoanTestG.county_code[LoanTestG.county_code == -1].count()

pctmiss_msa_ma = ((missing_msa_md)/500000)*100
pctmiss_state_code = ((missing_state_code)/500000)*100
pctmiss_county_code = ((missing_county_code)/500000)*100

print(pctmiss_msa_ma)
print(pctmiss_state_code)
print(pctmiss_county_code)

# +
#Above - Will keep the missing interger values as '-1' within the test dataset.

#Below - Recalculate the summary statistcs for numeric values with missing values filled with median.

LoanTestG.describe()

# +
#Save this cleaned test data to a csv

LoanTestG.to_csv('progressData/LoanTest_Clean_2019-08-12y1.csv', index = False, header = True)
# -



StarcoderdataJupyterScriptsDedupFiltered