<jupyter_start><jupyter_text>INNOTECH Austin 2019: Data Cleansing Strategies 94% of businesses suspect that their customer and prospect data is inaccurate!**Data cleansing** is the process of detecting and correcting (or removing) corrupt or inaccurate records from a data set and refers to identifying incomplete, incorrect, inaccurate or irrelevant parts of the data and then replacing, modifying, or deleting the dirty or coarse data.**Resources:** * [Data Cleansing on Wikipedia](https://en.wikipedia.org/wiki/Data_cleansing) * [35+ Statistics About Dirty Data](https://blog.zoominfo.com/the-effect-of-dirty-data-on-roi/) * [Car Features and MSRP](https://www.kaggle.com/CooperUnion/cardataset/data)  Connect Google Drive on Colab **Google Colaboratory** (or Colab) is a research tool for machine learning education and research. It’s a Jupyter notebook environment that requires no setup to use: * [Overview of Colaboratory Features](https://colab.research.google.com/notebooks/basic_features_overview.ipynb) * [Colab FAQ](https://research.google.com/colaboratory/faq.html)<jupyter_code>from google.colab import drive
drive.mount('/content/drive/')
%cd /content/drive/'My Drive'/Data-Cleansing-Innotech<jupyter_output><empty_output><jupyter_text>Import Required Packages **NumPy** is a library for Python which adds support for large, multi-dimensional arrays and matrices, along with a large collection of high-level mathematical functions to operate on these arrays: * [NumPy on Wikipedia](https://en.wikipedia.org/wiki/NumPy) * [NumPy website](https://numpy.org/)<jupyter_code>import numpy as np
print("NumPy version:", np.__version__)<jupyter_output>NumPy version: 1.17.3<jupyter_text>**Pandas** is a library for Python which adds high-performance, easy-to-use data structures, along with data manipulation and analysis tools: * [Pandas on Wikipedia](https://en.wikipedia.org/wiki/Pandas_(software)) * [Pandas website](https://pandas.pydata.org/)<jupyter_code>import pandas as pd
print("Pandas version:", pd.__version__)<jupyter_output>Pandas version: 0.25.2<jupyter_text>**Matplotlib** is a plotting library for Python and its numerical mathematics extension NumPy: * [Matplotlib on Wikipedia](https://en.wikipedia.org/wiki/Matplotlib) * [Matplotlib website](https://matplotlib.org/)<jupyter_code>%matplotlib inline
import matplotlib
import matplotlib.pyplot as plt
print("Matplotlib version:", matplotlib.__version__)<jupyter_output>Matplotlib version: 3.1.1<jupyter_text>**Scikit-learn** is a machine learning library for Python. It features various classification, regression and clustering algorithms and is designed to interoperate with the Python numerical and scientific libraries NumPy and SciPy: * [Scikit-learn on Wikipedia](https://en.wikipedia.org/wiki/Scikit-learn) * [Scikit-learn website](https://scikit-learn.org/stable/)<jupyter_code>import sklearn
print("Scikit-learn version:", sklearn.__version__)
from sklearn.utils import shuffle
from sklearn.ensemble import RandomForestRegressor
from sklearn.ensemble import GradientBoostingClassifier
from sklearn.model_selection import GridSearchCV, cross_val_score, train_test_split
from sklearn.metrics import classification_report, make_scorer, mean_squared_error<jupyter_output>Scikit-learn version: 0.21.3<jupyter_text>Read in Data In this analysis we used data set with car features including make, model, year, engine, and other properties of the car used to predict its price. This data set was scraped from [edmunds.com](https://www.edmunds.com/) - an American online resource for automotive information. All details on the dataset can be found at its [kaggle page](https://www.kaggle.com/CooperUnion/cardataset/data).First, we read the data set into pandas data frame:<jupyter_code>df_cars_pop_msrp = pd.read_csv("../data/raw/Cars_Popularity_MSRP.csv")<jupyter_output><empty_output><jupyter_text>Shape (number of rows, number of columns) of the data frame is the following:<jupyter_code>df_cars_pop_msrp.shape<jupyter_output><empty_output><jupyter_text>Print out information on the columns of the data set:<jupyter_code>df_cars_pop_msrp.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 11914 entries, 0 to 11913
Data columns (total 16 columns):
Make                 11914 non-null object
Model                11914 non-null object
Year                 11914 non-null int64
Engine Fuel Type     11911 non-null object
Engine HP            11845 non-null float64
Engine Cylinders     11884 non-null float64
Transmission Type    11914 non-null object
Driven Wheels        11914 non-null object
Number of Doors      11908 non-null float64
Market Category      8172 non-null object
Vehicle Size         11914 non-null object
Vehicle Style        11914 non-null object
Highway MPG          11914 non-null int64
City MPG             11914 non-null int64
Popularity           11914 non-null int64
MSRP                 11914 non-null int64
dtypes: float64(3), int64(5), object(8)
memory usage: 1.5+ MB<jupyter_text>From the information above we conclude that the data set is pretty much clean, for most columns they have 11914 non-null object. `Engine HP` is the column that has 11845 non-null float64, i.e. 69 records are **missing**. `Engine Cylinders` has 30 missing records and `Market Category` has largest number of 3742 missing records. Let's look into statistics for numerical columns:<jupyter_code>df_cars_pop_msrp.describe()<jupyter_output><empty_output><jupyter_text>Let's also look into random 10 records:<jupyter_code>df_cars_pop_msrp.sample(10, random_state = 1).head(10)<jupyter_output><empty_output><jupyter_text>Data Matrix for ML EstimatorCreate a function that accepts data frame and returns matrix `X` and vector `Y` for machine learning estimator:<jupyter_code>def PrepDataMatrix(df):
    X = df.copy()
    X.fillna(0, inplace=True)
    Z = X.pop('Engine Cylinders')
    Xmake = X['Make']
    Xmodel = X['Model']
    # Turns categorical data into binary values across columns
    #X = pd.get_dummies(X, dummy_na = False, columns=['Make', 'Model', 'Engine Fuel Type', 'Transmission Type', 'Driven Wheels', 'Market Category', 'Vehicle Size', 'Vehicle Style'] )
    #X = pd.get_dummies(X, dummy_na = False, columns=['Make', 'Engine Fuel Type', 'Transmission Type', 'Driven Wheels', 'Market Category', 'Vehicle Size', 'Vehicle Style'] )
    X = pd.get_dummies(X, dummy_na = False, columns=['Engine Fuel Type', 'Transmission Type', 'Driven Wheels', 'Market Category', 'Vehicle Size', 'Vehicle Style'] )
    #X.insert(0, 'Model', Xmodel);
    #X.insert(0, 'Make', Xmake);
    Y = X.pop('MSRP')
    return shuffle(X,Y, random_state = 1)
X,Y = PrepDataMatrix(df_cars_pop_msrp)
print(type(X))
X
df_new1 = df_cars_pop_msrp.copy()
df_new2 = df_cars_pop_msrp.copy()
df_new2['Engine HP Missing'] = df_new2['Engine HP']
print(type(Y))
Y<jupyter_output><class 'pandas.core.series.Series'><jupyter_text>Training and testing data sets Split data set into 2 parts: training (90%) and testing (10%)<jupyter_code>X_trn, X_tst, Y_trn, Y_tst = train_test_split(X, Y, test_size=0.10, random_state=2)
MSE_Scorer = make_scorer(mean_squared_error)<jupyter_output><empty_output><jupyter_text>Drop model/make columns so they are not used in the model, but we can re-use them later to estimate how they correlate with model's prediction and see overpriced model/makes (see orignal work on [kaggle page](https://www.kaggle.com/CooperUnion/cardataset/data)).<jupyter_code>X_trn2 = X_trn.drop('Make', axis = 1).drop('Model', axis = 1)
X_tst2 = X_tst.drop('Make', axis = 1).drop('Model', axis = 1)<jupyter_output><empty_output><jupyter_text>Let's check that `Make` and `Model` columns disappeared from the data matrix:<jupyter_code>X_trn2<jupyter_output><empty_output><jupyter_text>Machine Learning Model Build **Random Forest** model which is a machine learning method for classification, regression and other tasks that operates by constructing a multitude of **[decision trees](https://en.wikipedia.org/wiki/Decision_tree_learning)** at training time and outputting the class that is the mode of the classes or mean prediction of the individual trees. * [Random Forest on Wikipedia](https://en.wikipedia.org/wiki/Random_forest) * [`sklearn.ensemble.RandomForestRegressor`](https://scikit-learn.org/stable/modules/generated/sklearn.ensemble.RandomForestRegressor.html) * [`scikit-learn` User Guide](https://scikit-learn.org/stable/modules/ensemble.htmlforest)<jupyter_code>rfr = RandomForestRegressor(n_estimators=100, max_features="auto", random_state=11)<jupyter_output><empty_output><jupyter_text>Train model using data matrix `X_trn2` and vector `Y`:<jupyter_code>%%time
rfr.fit(X_trn2, Y_trn)<jupyter_output>Wall time: 11.5 s<jupyter_text>Cross-Validation and Errors Perform **cross-validation** to evaluate estimator performance * [`scikit-learn` User Guide](https://scikit-learn.org/stable/modules/cross_validation.html) * [`sklearn.model_selection.cross_val_score`](https://scikit-learn.org/stable/modules/generated/sklearn.model_selection.cross_val_score.html)The mean score and the 95% confidence interval of the score estimate:<jupyter_code>%%time
scores = cross_val_score(rfr, X_tst2, Y_tst, cv = 5)
accuracy = scores.mean()
print("Accuracy: %0.2f" % accuracy)<jupyter_output>Accuracy: 0.90<jupyter_text>Calculate predicted values using test sample:<jupyter_code>Y_prd = rfr.predict(X_tst2)
mean_error = sum(abs(Y_tst-Y_prd))/len(Y_tst)
mean_error_p = 100*mean_error/np.mean(Y_tst)
print("Mean absolute error:", mean_error)
print("Mean percent error: ", mean_error_p)<jupyter_output>Mean absolute error: 3049.003861290586
Mean percent error:  8.028335797760814<jupyter_text>What features most predict price? Get the importances for each features and calculate standard deviations for them:<jupyter_code>importances = rfr.feature_importances_
std = np.std([tree.feature_importances_ for tree in rfr.estimators_], axis=0)
indices = np.argsort(importances)[::-1]

# Get the feature names
features = X_tst2.columns.values

# Want the top 5 features, so limit the indices and labels
topLimit = 5 # limit to show up to, ex. top 10
indices = indices[0: topLimit] # indices for features
topLabels = features[indices[0: topLimit]] # actual feature labels, we want to print these

# Plot the feature importances of the forest (top 20)
figsize = (8,6)
plt.figure(figsize=figsize)
plt.title("Top Important Features")
ax = plt.bar(range(topLimit), importances[indices], color="r", yerr=std[indices], align="center")
plt.xticks(rotation=90)
plt.xticks(range(topLimit), topLabels)
plt.xlim([-1, topLimit])
plt.xlabel('Features')
plt.ylabel('Importance')
plt.show()
importances[indices]<jupyter_output><empty_output><jupyter_text>Introduce Missing Values Now we randomly sample 5000 records (approximately 50%) from data set and replace values in `Engine HP` with `None` - effectively introduce missing values into the most importnat feature:<jupyter_code>df_idx1 = df_cars_pop_msrp.sample(5000, random_state = 1).index
df_new1.loc[df_idx1, 'Engine HP'] = None<jupyter_output><empty_output><jupyter_text>Prepare data matrix for regression:<jupyter_code>X,Y = PrepDataMatrix(df_new1)<jupyter_output><empty_output><jupyter_text>Split data in test and train sets:<jupyter_code>X_trn, X_tst, Y_trn, Y_tst = train_test_split(X, Y, test_size=0.10, random_state=2)
MSE_Scorer = make_scorer(mean_squared_error)<jupyter_output><empty_output><jupyter_text>Drop make and model:<jupyter_code>X_trn2 = X_trn.drop('Make', axis = 1).drop('Model', axis = 1)
X_tst2 = X_tst.drop('Make', axis = 1).drop('Model', axis = 1)<jupyter_output><empty_output><jupyter_text>Run fit for Random Forest regression:<jupyter_code>%%time
rfr.fit(X_trn2, Y_trn)<jupyter_output>Wall time: 10.6 s<jupyter_text>Calculate cross-validation scores:<jupyter_code>%%time
scores = cross_val_score(rfr, X_tst2, Y_tst, cv = 5)<jupyter_output>Wall time: 4.93 s<jupyter_text>Accuracy of the new regression with missing values:<jupyter_code>accuracy_new1 = scores.mean()
print("Accuracy: %0.2f" % accuracy_new1)<jupyter_output>Accuracy: 0.83<jupyter_text>Calculate predicted values:<jupyter_code>Y_prd = rfr.predict(X_tst2)<jupyter_output><empty_output><jupyter_text>Calculate absolute and relative error on the prediction:<jupyter_code>mean_error1 = sum(abs(Y_tst-Y_prd))/len(Y_tst)
mean_error_p1 = 100*mean_error1/np.mean(Y_tst)
print("Mean absolute error:", mean_error1)
print("Mean percent error: ", mean_error_p1)<jupyter_output>Mean absolute error: 4209.0945395249
Mean percent error:  11.082971982043931<jupyter_text>Calculate importance factors for all features and plot top 5:<jupyter_code>importances = rfr.feature_importances_
std = np.std([tree.feature_importances_ for tree in rfr.estimators_], axis=0)
indices = np.argsort(importances)[::-1]

# Get the feature names
features = X_tst2.columns.values

# Want the top 5 features, so limit the indices and labels
topLimit = 5 # limit to show up to, ex. top 10
indices = indices[0: topLimit] # indices for features
topLabels = features[indices[0: topLimit]] # actual feature labels, we want to print these

# Plot the feature importances of the forest (top 20)
figsize = (8,6)
plt.figure(figsize=figsize)
plt.title("Top Important Features")
ax = plt.bar(range(topLimit), importances[indices], color="r", yerr=std[indices], align="center")
plt.xticks(rotation=90)
plt.xticks(range(topLimit), topLabels)
plt.xlim([-1, topLimit])
plt.xlabel('Features')
plt.ylabel('Importance')
plt.show()<jupyter_output><empty_output><jupyter_text>Print top 5 important features:<jupyter_code>importances[indices]<jupyter_output><empty_output><jupyter_text>Introduce Missing Values and Missing Label Similarly to above we randomly sample 5000 records (approximately 50%) from data set and replace values in `Engine HP` with `None` - effectively introduce missing values into the most importnat feature.Also we introduce new feature `Engine HP Missing` with `1` for missing values in `Engine HP` and `0` for present values in `Engine HP`.<jupyter_code>df_new2.loc[df_idx1, 'Engine HP'] = None
df_new2.loc[df_new2.index.difference(df_idx1), 'Engine HP Missing'] = 0<jupyter_output><empty_output><jupyter_text>Prepare data matrix for regression:<jupyter_code>X,Y = PrepDataMatrix(df_new2)<jupyter_output><empty_output><jupyter_text>Split data in test and train sets:<jupyter_code>X_trn, X_tst, Y_trn, Y_tst = train_test_split(X, Y, test_size=0.10, random_state=2)
MSE_Scorer = make_scorer(mean_squared_error)<jupyter_output><empty_output><jupyter_text>Drop make and model:<jupyter_code>X_trn2 = X_trn.drop('Make', axis = 1).drop('Model', axis = 1)
X_tst2 = X_tst.drop('Make', axis = 1).drop('Model', axis = 1)<jupyter_output><empty_output><jupyter_text>Run fit for Random Forest regression:<jupyter_code>%%time
rfr.fit(X_trn2, Y_trn)<jupyter_output>Wall time: 11.9 s<jupyter_text>Calculate cross-validation scores:<jupyter_code>%%time
scores = cross_val_score(rfr, X_tst2, Y_tst, cv = 5)<jupyter_output>Wall time: 5.55 s<jupyter_text>Accuracy of the new regression with missing values:<jupyter_code>accuracy_new2 = scores.mean()
print("Accuracy: %0.2f" % accuracy_new2)<jupyter_output>Accuracy: 0.86<jupyter_text>Calculate predicted values:<jupyter_code>Y_prd = rfr.predict(X_tst2)<jupyter_output><empty_output><jupyter_text>Calculate absolute and relative error on the prediction:<jupyter_code>mean_error2 = sum(abs(Y_tst-Y_prd))/len(Y_tst)
mean_error_p2 = 100*mean_error2/np.mean(Y_tst)
print("Mean absolute error:", mean_error2)
print("Mean percent error: ", mean_error_p2)<jupyter_output>Mean absolute error: 3671.989780322733
Mean percent error:  9.668720783416308<jupyter_text>Calculate importance factors for all features and plot top 5:<jupyter_code>importances = rfr.feature_importances_
std = np.std([tree.feature_importances_ for tree in rfr.estimators_], axis=0)
indices = np.argsort(importances)[::-1]

# Get the feature names
features = X_tst2.columns.values

# Want the top 5 features, so limit the indices and labels
topLimit = 5 # limit to show up to, ex. top 10
indices = indices[0: topLimit] # indices for features
topLabels = features[indices[0: topLimit]] # actual feature labels, we want to print these

# Plot the feature importances of the forest (top 20)
figsize = (8,6)
plt.figure(figsize=figsize)
plt.title("Top Important Features")
ax = plt.bar(range(topLimit), importances[indices], color="r", yerr=std[indices], align="center")
plt.xticks(rotation=90)
plt.xticks(range(topLimit), topLabels)
plt.xlim([-1, topLimit])
plt.xlabel('Features')
plt.ylabel('Importance')
plt.show()<jupyter_output><empty_output><jupyter_text>Print top 5 important features:<jupyter_code>importances[indices]<jupyter_output><empty_output><jupyter_text>Results Finally, we compare results for accuracies and mean errors (in percents) for three cases: * original data with no missing values * data with ~50% values in `Engine HP` missing * data with ~50% values in `Engine HP` missing but with new feature that labels missing data<jupyter_code>print("*** Original data ***")
print("Accuracy: %0.2f" % accuracy)
print("Mean percent error: %0.2f" % mean_error_p)
print()
print("*** Introduce missing values for Engine HP ***")
print("Accuracy: %0.2f" % accuracy_new1)
print("Mean percent error: %0.2f" % mean_error_p1)
print()
print("*** Add column with missing labels for Engine HP ***")
print("Accuracy: %0.2f" % accuracy_new2)
print("Mean percent error: %0.2f" % mean_error_p2)<jupyter_output>*** Original data ***
Accuracy: 0.90
Mean percent error: 8.03

*** Introduce missing values for Engine HP ***
Accuracy: 0.83
Mean percent error: 11.08

*** Add column with missing labels for Engine HP ***
Accuracy: 0.86
Mean percent error: 9.67
{'fraction_non_alphanumeric': 0.11186402394262804, 'fraction_numerical': 0.02823423118188492, 'mean_word_length': 6.5233644859813085, 'pattern_counts': {'":': 0, '<': 153, '<?xml version=': 0, '>': 153, 'https://': 22, 'lorem ipsum': 0, 'www.': 4, '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': '42', 'dir': 'jupyter-structured-clean-dedup', 'id': '521892', 'license': 'MIT', 'n_tokens_mistral': 6321, 'n_tokens_neox': 5851, 'n_words': 1577, 'path': 'notebooks/Demo.ipynb', 'repo_name': 'pakhotin/Data-Cleansing-Innotech', 'size': '17709'}