Exercise 4. - Data Cleansing

Handling Missing Values in Python¶

Real world data is messy and often contains a lot of missing values.

There could be multiple reasons for the missing values but primarily the reason for missingness can be attributed to:

A guide to handling missing values¶

Please read this tutorial on handling missing values first, before working on dirty data this week: TUTORIAL.

Dirty data¶

Load the dataset from the provided URL using pandas.

Introduce Missing Values¶

Randomly introduce missing values into the dataset to mimic the Python code behavior.

Introduction¶

Analysis of data is a process of inspecting, cleaning, transforming, and modeling data with the goal of highlighting useful information, suggesting conclusions and supporting decision making.

Many times in the beginning we spend hours on handling problems with missing values, logical inconsistencies or outliers in our datasets. In this tutorial we will go through the most popular techniques in data cleansing.

We will be working with the messy dataset iris. Originally published at UCI Machine Learning Repository: Iris Data Set, this small dataset from 1936 is often used for testing out machine learning algorithms and visualizations. Each row of the table represents an iris flower, including its species and dimensions of its botanical parts, sepal and petal, in centimeters.

Take a look at this dataset here:

Detecting NA¶

A missing value, represented by NaN in Python, is a placeholder for a datum of which the type is known but its value isn’t. Therefore, it is impossible to perform statistical analysis on data where one or more values in the data are missing. One may choose to either omit elements from a dataset that contain missing values or to impute a value, but missingness is something to be dealt with prior to any analysis.

Can you see that many values in our dataset have status NaN = Not Available? Count (or plot), how many (%) of all 150 rows is complete.

Does the data contain other special values? If it does, replace them with NA.

Checking consistency¶

Consistent data are technically correct data that are fit for statistical analysis. They are data in which missing values, special values, (obvious) errors and outliers are either removed, corrected or imputed. The data are consistent with constraints based on real-world knowledge about the subject that the data describe.

We have the following background knowledge:

• Species should be one of the following values: setosa, versicolor or virginica.

• All measured numerical properties of an iris should be positive.

• The petal length of an iris is at least 2 times its petal width.

• The sepal length of an iris cannot exceed 30 cm.

• The sepals of an iris are longer than its petals.

Define these rules in a separate object ‘RULES’ and read them into Python. Print the resulting constraint object.

Now we are ready to determine how often each rule is broken (violations). Also we can summarize and plot the result.

What percentage of the data has no errors?

Find out which observations have too long sepals using the result of violations.

Find outliers in sepal length using boxplot approach. Retrieve the corresponding observations and look at the other values. Any ideas what might have happened? Set the outliers to NA (or a value that you find more appropiate)

They all seem to be too big... may they were measured in mm i.o cm?

Note that simple boxplot shows an extra outlier!

Correcting¶

Replace non positive values from Sepal.Width with NA:

Replace all erroneous values with NA using (the result of) localizeErrors:

NA’s pattern detection¶

Here we are going to use missingno library to diagnose the missingness pattern for the ‘dirty_iris’ dataset.

Matrix Plot (msno.matrix):¶

This visualization shows which values are missing in each column. Each bar represents a column, and white spaces in the bars indicate missing values.

If you see many white spaces in one column, it means that column has a lot of missing data. If the white spaces are randomly scattered, the missing data might be random. If they are clustered in specific areas, it might indicate a pattern.

Heatmap Plot (msno.heatmap):¶

This visualization shows the correlations between missing values in different columns. If two columns have a high correlation (dark colors), it means that if one column has missing values, the other column is also likely to have missing values.

Low correlations (light colors) indicate that missing values in one column are not related to missing values in another column.

Dendrogram Plot (msno.dendrogram):¶

This visualization groups columns based on the similarity of their missing data patterns. Columns that are close to each other in the dendrogram have similar patterns of missing data.

This can help identify groups of columns that have similar issues with missing data.

Based on these visualizations, we can identify which columns have the most missing data, whether the missing data is random or patterned, and which columns have similar patterns of missing data.

Based on the dendrogram plot, we can interpret the pattern of missing data in the “dirty iris” dataset as follows:

Grouping of Columns:

The dendrogram shows that the columns “Species” and “Petal.Width” are grouped together, indicating that they have similar patterns of missing data.

Similarly, “Sepal.Width” and “Petal.Length” are grouped together, suggesting they also share a similar pattern of missing data.

“Sepal.Length” is somewhat separate from the other groups, indicating it has a different pattern of missing data compared to the other columns.

Pattern of Missing Data:

The grouping suggests that missing data in “Species” is likely to be associated with missing data in “Petal.Width”.

Similarly, missing data in “Sepal.Width” is likely to be associated with missing data in “Petal.Length”.

“Sepal.Length” appears to have a distinct pattern of missing data that is not strongly associated with the other columns.

From this dendrogram, we can infer that the missing data is not completely random. Instead, there are specific patterns where certain columns tend to have missing data together. This indicates a systematic pattern of missing data rather than a purely random one.

Imputing NA’s¶

Imputation is the process of estimating or deriving values for fields where data is missing. There is a vast body of literature on imputation methods and it goes beyond the scope of this tutorial to discuss all of them.

There is no one single best imputation method that works in all cases. The imputation model of choice depends on what auxiliary information is available and whether there are (multivariate) edit restrictions on the data to be imputed.

The availability of Python software for imputation under edit restrictions is, to our best knowledge, limited. However, a viable strategy for imputing numerical data is to first impute missing values without restrictions, and then minimally adjust the imputed values so that the restrictions are obeyed. Separately, these methods are available in Python.

We can mention several approaches to imputation:

1. For the quantitative variables:

• imputing by mean/median/mode

• hotdeck imputation

• KNN -- K-nearest-neighbors approach

• RPART -- random forests multivariate approach

• mice - Multivariate Imputation by Chained Equations approach

2. For the qualitative variables:

• imputing by mode

• RPART -- random forests multivariate approach

• mice - Multivariate Imputation by Chained Equations approach

  ... and many others. Please read the theoretical background if you are interested in those techniques.

Exercise 1. Use kNN imputation (‘sklearn’ package) to impute all missing values. The KNNImputer from sklearn requires all data to be numeric. Since our dataset contains categorical data (e.g., the Species column), you need to handle these columns separately. One approach is to use one-hot encoding for categorical variables before applying the imputer.

Transformations¶

Finally, we sometimes encounter the situation where we have problems with skewed distributions or we just want to transform, recode or perform discretization. Let’s review some of the most popular transformation methods.

First, standardization (also known as normalization):

• Z-score approach - standardization procedure, using the formula: $z=\frac{x-\mu}{\sigma}$ where μ = mean and σ = standard deviation. Z-scores are also known as standardized scores; they are scores (or data values) that have been given a common standard. This standard is a mean of zero and a standard deviation of 1.

• minmax approach - An alternative approach to Z-score normalization (or standardization) is the so-called MinMax scaling (often also simply called “normalization” - a common cause for ambiguities). In this approach, the data is scaled to a fixed range - usually 0 to 1. The cost of having this bounded range - in contrast to standardization - is that we will end up with smaller standard deviations, which can suppress the effect of outliers. If you would like to perform MinMax scaling - simply substract minimum value and divide it by range:$(x-min)/(max-min)$

In order to solve problems with very skewed distributions we can also use several types of simple transformations:

• log
• log+1
• sqrt
• x^2
• x^3

Exercise 2. Standardize incomes and present the transformed distribution of incomes on boxplot.

Binning¶

Sometimes we just would like to perform so called ‘binning’ procedure to be able to analyze our categorical data, to compare several categorical variables, to construct statistical models etc. Thanks to the ‘binning’ function we can transform quantitative variables into categorical using several methods:

• quantile - automatic binning by quantile of its distribution

• equal - binning to achieve fixed length of intervals

• pretty - a compromise between the 2 mentioned above

• kmeans - categorization using the K-Means algorithm

• bclust - categorization using the bagged clustering algorithm

Exercise 3. Using quantile approach perform binning of the variable ‘Income’.

Exercise 4. Recode the original distribution of incomes using fixed length of intervals and assign them labels.

In case of statistical modeling (i.e. credit scoring purposes) - we need to be aware of the fact, that the optimal discretization of the original distribution must be achieved. The ‘binning_by’ function comes with some help here.

Optimal binning with binary target¶

Exercise 5. Perform discretization of the variable ‘Advertising’ using optimal binning.

We choose a variable to discretize and the binary target.

Import and instantiate an OptimalBinning object class. We pass the variable name, its data type, and a solver, in this case, we choose the constraint programming solver.

We fit the optimal binning object with arrays x and y.

You can check if an optimal solution has been found via the status attribute:

You can also retrieve the optimal split points via the splits attribute:

The binning table

The optimal binning algorithms return a binning table; a binning table displays the binned data and several metrics for each bin. Class OptimalBinning returns an object BinningTable via the binning_table attribute.

The binning_table is instantiated, but not built. Therefore, the first step is to call the method build, which returns a pandas.DataFrame.

Let’s describe the columns of this binning table:

Bin: the intervals delimited by the optimal split points.
Count: the number of records for each bin.
Count (%): the percentage of records for each bin.
Non-event: the number of non-event records (𝑦=0) for each bin.
Event: the number of event records (𝑦=1) for each bin.
Event rate: the percentage of event records for each bin.
WoE: the Weight-of-Evidence for each bin.
IV: the Information Value (also known as Jeffrey’s divergence) for each bin.
JS: the Jensen-Shannon divergence for each bin.
The last row shows the total number of records, non-event records, event records, and IV and JS.

You can use the method plot to visualize the histogram and WoE or event rate curve. Note that the Bin ID corresponds to the binning table index.

Note that WoE is inversely related to the event rate, i.e., a monotonically ascending event rate ensures a monotonically descending WoE and vice-versa. We will see more monotonic trend options in the advanced tutorial.

Read more here: https://gnpalencia.org/optbinning/tutorials/tutorial_binary.html

Working with ‘missingno’ library¶

Exercise 6. Your turn!

Work with the ‘carseats’ dataset, find the best way to perform full diagnostic (dirty data, outliers, missing values). Fix problems.