Data Preprocessing

Goals of this lecture¶

A huge $\%$ of science involves data wrangling.

This could be an entire course on its own, but today we’ll focus on:

• What is data wrangling?
• What to do about missing values?
• How to combine datasets?
• Tidy data!
  • What is it?
  • How do we make our data tidy?

Importing relevant libraries¶

What is data wrangling?¶

Data wrangling refers to manipulating, reshaping, or transforming a dataset as needed for your goals (e.g., visualization and/or analysis).

• A huge $\%$ of working with data involves “wrangling”.
• Includes:
  • “Cleaning” data (missing values, recasting variables, etc.).
  • Merging/combining different datasets.
  • Reshaping data as needed.

Data Wrangling with Pandas¶

CHEAT-SHEET is available here

Types of missing data¶

Before we start analyzing missing values, it is important to understand the different reasons for missing data. Generally speaking, there are three possible reasons:

1. Missing completely at random (MCAR).

The missing values of a given variable (Y) are not related to other variables in the data set or to the variable (Y) itself. In other words, there is no specific reason for the missing values.

2. Missing at random (MAR).

MAR occurs when the missing value is not random, but when the missing value can be fully explained by variables for which there is complete information.

3. Missing not at random (MNAR).

The missing value depends on unobserved data or on the values of the missing data itself.

MCAR¶

What does it mean? When missing data points do not follow any particular reasoning or pattern.

• Example 1. You have demographic data for your community. But the “Middle Name” variable is missing 50% of the values. This 50% of the data is a perfect example of MCAR data. There is no pattern or reason why the middle name is blank in most of the entries.

• Example 2. In a paper survey, one page accidentally fell out of several forms during shipping. So the answers to questions on page 3 are missing, but regardless of who the respondent was or how they answered earlier.

How to deal with this?

For MCAR data, the following methods can be used:

1. Row-wise deletion: Delete a record if there is missing data in any of the variables/columns in the dataset. This works best only when the amount of missing data is small, such as when only 2% of the data in the dataset is missing completely at random.
2. Pairwise deletion: Pairwise deletion removes only the cases where one of the variables used in the statistical method under consideration is missing. It works on the same principle as the correlation matrix. In case of missing values between two variables (in the sense of pairwise), finding the correlation matrix takes into account all the complete cases for those two variables. Suppose the number of cases in this scenario is N. After taking another set of variables and calculating the correlation matrix, the number of complete cases will be different from N. This serves as the main difference between rowwise and pairwise deletion. Pairwise deletion has the advantage of causing minimal data loss. In case of a data set which has common missing values in almost all variables, pairwise deletion would be a wiser choice to deal with the missing values. 3. Mean, Median and Mode Imputation: Missing values can also be replaced by the mean, median and mode of the respective variables.

MAR¶

What does it mean? When missing data points follow a pattern.

• Example 1. Take the same example of demographic data for your community. But this time, the salaries of several men over the age of 45 are missing.

• Example 2. In a health survey, women are more likely than men to not report their weight. Missing data is determined by gender (which is known), but not by the weight value itself.

In this case, missing data is imputed to data from another variable. As such, it is a “missing at random” mechanism. MAR is probably the most difficult to understand because of its name.

How to deal with it?

Because there is a relationship in this mechanism, the best option would be to use an imputation technique—mean, median, mode, or multiple imputation.

MNAR¶

What does this mean?

When the missing data points follow a pattern, it means that they follow the MNAR mechanism.

• Example 1. In the same demographic data of residents of your community, let’s say that the salary of several men is missing when the salary exceeds a certain amount. (say, one million).

• Example 2. People with very high incomes do not want to disclose their earnings in the survey. The missing data on income depends directly on its value (i.e. the one that is missing).

In this case, we are dealing with the “Missing Not at Random” mechanism. Usually, when the missing data is not MCAR or MAR, it tends to follow MNAR.

How to deal with it?

Since MNAR is a self-induced dependence, the best way to avoid it is to pool the data or model the missing data.

The reason we need to analyze the mechanisms of missing values is that we need to understand the best way to impute a missing value so that the accuracy of our ML models remains unaffected.

Dealing with missing values¶

• In practice, real-world data is often messy.
• This includes missing values, which take on the value/label NaN.
  • NaN = “Not a Number”.
• Dealing with NaN values is one of the main challenges in EDA!

Loading a dataset with missing values¶

The titanic dataset contains information about different Titanic passengers and whether they Survived (1 vs. 0).

• Commonly used as a tutorial for machine learning, regression, and data wrangling.

Why is missing data a problem?¶

If you’re unaware of missing data:

• You might be overestimating the size of your dataset.
• You might be biasing the results of a visualization or analysis (if missing data are non-randomly distributed).
• You might be complicating an analysis.
  • By default, many analysis packages will “drop” missing data––so you need to be aware of whether this is happening.

How to deal with missing data¶

1. Identify whether and where your data has missing values.
2. Analyze how these missing values are distributed.
3. Decide how to handle them.

Not an easy problem––especially step 3!

Step 1: Identifying missing values¶

The first step is identifying whether and where your data has missing values.

There are several approaches to this:

• Using .isna
• Using .info
• Using .isnull

isna()¶

• The isna() function tells us whether a given cell of a DataFrame has a missing value or not (True vs. False).
• If we call isna().any(), it tells us which columns have missing values.

Inspecting columns with nan¶

Now we can inspect specific columns that have nan values.

How many nan?¶

If we call sum on the nan values, we can calculate exactly how many nan values are in each column.

info¶

The info() function gives us various information about the DataFrame, including the number of not-null (i.e., non-missing) values in each column.

Check-in¶

How many rows of the DataFrame have missing values for Cabin?

Solution¶

Visualizing missing values¶

• Finally, we can visualize the rate of missing values across columns using seaborn.heatmap.
  • The dark cells are those with not-null values.
  • The light cells have nan values.

Step 2: Analyze how the data are distributed¶

Having identified missing data, the next step is determining how those missing data are distributed.

Is variable $Y$ different depending on whether $X$ is nan?¶

One approach is to ask whether some variable of interest (e.g., Survived) is different depending on whether some other variable is nan.

Check-in¶

What is the mean Survived rate for values with a nan value for Age vs. those with not-null values? How does this compare to the overall Survived rate?

Solution¶

Package missingno¶

If you want to dig deeper, you can check out the missingno Python library (which needs to be installed separately).

• Tutorial for the missingno package.

• Homepage for the missingno package.

Finding the reason for missing data using a matrix chart¶

Finding the cause of missing data using Heatmap¶

The heat map feature shows that there are no strong correlations between missing values of different features. This is good; low correlations also indicate that the data is MAR.

Finding the Cause of Missing Data Using a Dendrogram¶

A dendrogram is a tree diagram of missing data. It groups together highly correlated variables.

Step 3: Determine what to do!¶

Having identified missing data, you need to determine how to handle it.

There are several approaches you can take.

• Removing all rows with any missing data.
• Removing rows with missing data only when that variable is relevant to the analysis or visualization.
• Imputing (i.e., guessing) what values missing data should have.

Removing all rows with any missing data¶

• We can filter our DataFrame using dropna, which will automatically “drop” any rows containing null values.
• Caution: if you have lots of missing data, this can substantially impact the size of your dataset.

Removing all rows with missing data in specific columns¶

• Here, we specify that we only want to dropna for rows that have nan in the Age column specifically.
• We still have missing nan for Cabin, but perhaps that’s fine in our case.

Imputing missing data¶

One of the most complex (and controversial) approaches is to impute the values of missing data.

There are (again) multiple ways to do this:

1. Decide on a constant value and assign it to all nan values.
   • E.g., assign the mean Age to all people with nan in that column.
2. Try to guess the value based on specific characteristics of the data.
   • E.g., based on other characteristics of this person, what is their likely Age?

Imputing a constant value¶

We can use fillna to assign all values with nan for Age some other value.

Guessing based on other characteristics¶

• You can try to guess what their Age would be, based on other features.
• The more sophisticated version of this is to use statistical modeling or using SimpleImputer from the sklearn library.
• For now, simply note that Age correlates with other features (like Pclass).

Check-in¶

What would happen if you used fillna with the median Age instead of the mean? Why would this matter?

Solution¶

The median Age is slightly lower.

Random imputation/hot deck¶

Hot deck imputation is a method in which each missing value is replaced with a value from a similar record in the dataset, often referred to as the “donor.” The donor record is selected based on matching criteria—such as demographic characteristics or proximity in a multidimensional feature space—to ensure that the imputation preserves the inherent distribution and relationships in the data.

For example, if income data for a survey respondent are missing, the income value of a similar respondent (based on factors such as occupation, geographic location, and education level) is used as a surrogate.

Hot deck imputation is crucial to maintaining the integrity of the dataset. It ensures that:

• Statistical analyses are not biased by the arbitrary or discarded incomplete cases.

• Natural variability and distributional patterns present in complete cases are preserved.

• Comparisons between different groups within the data remain feasible and valid.

Hot Deck Random Imputation: In this method, missing values are imputed by randomly selecting a donor from a pool of potential candidates. This preserves the distribution of the data, especially when the missing values are random. The downside is that the randomness can overlook nuances in the data.

Hot Deck Nearest Neighbor Imputation: In this case, a donor is selected based on the similarity of observed features. A commonly used algorithm is the k-nearest neighbors (k-NN) approach, which calculates a distance metric between observations. For a record with a missing value, its k nearest neighbors (in terms of Euclidean or Mahalanobis distance) are determined, and one of their values is used for imputation.

The k-NN algorithm identifies the closest records that minimize the distance:

$d(x, y) = \sqrt{\sum_{i=1}^n (x_i - y_i)^2}$

where:

• $(x)$ and $(y)$ are two points in n-dimensional space,
• $(x_i)$ and $(y_i)$ are the coordinates of points $(x)$ and $(y)$ in dimension $(i)$,
• $d(x, y)$ is the Euclidean distance between points $(x)$ and $(y)$.

Hot Deck imputation offers several advantages over other methods:

1. Mean/Median Imputation: Unlike simply replacing missing values with the grand mean or median, Hot Deck imputation uses information from similar cases. This preserves local variability and takes into account relationships between variables.

2. Regression Imputation: While regression imputation relies on predictive models to estimate missing values, it can lead to biased estimates if model assumptions are violated. Hot Deck imputation, on the other hand, borrows directly from observed values, often making it more robust under different conditions.

3. Multiple Imputation: Multiple imputation creates multiple imputed data sets and combines the results for a final analysis, accounting for imputation uncertainty. Although it is often more statistically sophisticated, it can be computationally expensive. Hot Deck imputation remains a practical choice when maintaining ease of interpretation and computational efficiency are priorities.

The main conclusion is that Hot Deck imputation offers an intuitive, practically feasible approach that still respects the statistical properties of the data.

Hot-deck Random Imputation¶

Hot Deck Nearest Neighbor (k-NN) Imputation¶

Summary¶

1. Hot Deck Random Imputation:

• NaN values in a column are replaced with a random value from existing data in the same column.
• It is fast but may introduce randomness that does not take into account the relationships between variables.

2. Hot Deck Nearest Neighbor Imputation (k-NN):

• The k-NN algorithm uses other features (e.g. Pclass, Fare) to find similar records and impute missing values.
• It is more advanced and takes into account the relationships between variables.

Dirty Data¶

Many times we spend hours troubleshooting missing values, logical inconsistencies, or outliers in our datasets. In this tutorial, we will discuss the most popular data cleaning techniques.

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

Check out this dataset here:

Coherent data are technically correct data that are suitable for statistical analysis. They are data in which missing values, special values, (obvious) errors, and outliers have been removed, corrected, or imputed. The data conform to constraints based on actual knowledge about the subject that the data describes.

We have the following basic knowledge:

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

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

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

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

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

We will now define these rules in a separate “RULES” object and read them into Python. We will print the resulting constraint object:

Now we can determine how often each rule is broken (violations). We can also summarize and plot the result.

What percentage of % of data has errors?

Find which flowers have sepals that are too long using the violation results.

Let’s find outliers in the plot length using the boxplot method.

We’ll take the relevant observations and check the remaining values.

Any ideas what might have happened?

We’ll set outliers to NA (or whatever value you think is more appropriate).

They all seem too big... maybe they were measured in mm instead of cm?

Notice that the simple boxplot shows an additional outlier!

Error Correction¶

Let’s replace non-positive values from Sepal.Width with NA:

Replacing all error values with NA:

Better to have NA before errors in data. One could possibly attempt to impute newly created gaps now...

Merging datasets¶

Merging refers to combining different datasets to leverage the power of additional information.

• In Week 1, we discussed this in the context of data linkage.
• Can link datasets as a function of:
  • Shared time window.
  • Shared identity.

Why merge?¶

• Each dataset contains limited information.
  • E.g., GDP by Year.
• But merging datasets allows us to see how more variables relate and interact.
• Much of social science research involves locating datasets and figuring out how to combine them.

How to merge?¶

In Python, pandas.merge allows us to merge two DataFrames on a common column(s).

pd.merge(df1, df2, on = "shared_column")

merge in practice¶

For demonstration, we’ll merge two Linguistics datasets:

• One dataset contains information about the Age of Acquisition of different English words (Kuperman et al., 2014).
• The other dataset contains information about the Frequency and Concreteness of English words (Brysbaert et al., 2014).

Loading datasets¶

Different kinds of merging¶

As we see, the datasets are not the same size. This leaves us with a decision to make when merging.

• inner join: Do we preserve only the words in both datasets?
• left join: Do we preserve all the words in one dataset (the “left” one), regardless of whether they occur in the other?
• right join: Do we preserve all the words in one dataset (the “right” one), regardless of whether they occur in the other?
• outer join: Do we preserve all words in both, leaving empty (nan) values where a word only appears in one dataset?

inner join¶

• For our purposes, it makes the most sense to use an inner join.
• This leaves us with fewer words than occur in either dataset.

Check-in¶

What happens if you use a different kind of join, e.g., outer or left? What do you notice about the shape of the resulting DataFrame? Do some rows have nan values?

Solution¶

Why merge is so useful¶

Now that we’ve merged our datasets, we can look at how variables across them relate to each other.

Reshaping data¶

Reshaping data involves transforming it from one format (e.g., “wide”) to another format (e.g., “long”), to make it more amenable to visualization and analysis.

Often, we need to make our data tidy.

What is tidy data?¶

Tidy data is a particular way of formatting data, in which:

1. Each variable forms a column (e.g., GDP).
2. Each observation forms a row (e.g., a country).
3. Each type of observational unit forms a table (tabular data!).

Originally developed by Hadley Wickham, creator of the tidyverse in R.

Tidy vs. “untidy” data¶

Now let’s see some examples of tidy vs. untidy data.

Keep in mind:

• These datasets all contain the same information, just in different formats.
• “Untidy” data can be useful for other things, e.g., presenting in a paper.
• The key goal of tidy data is that each row represents an observation.

Tidy data¶

Check-in: Why is this data considered tidy?

Untidy data 1¶

Check-in: Why is this data not considered tidy?

Untidy data 2¶

Check-in: Why is this data not considered tidy?

Making data tidy¶

Fortunately, pandas makes it possible to turn an “untidy” DataFrame into a tidy one.

The key function here is pandas.melt.

pd.melt(df, ### Dataframe
        id_vars = [...], ### what are the identifying columns?
        var_name = ..., ### name for variable grouping over columns
        value_name = ..., ### name for the value this variable takes on

If this seems abstract, don’t worry––it’ll become clearer with examples!

Using pd.melt¶

• Let’s start with our first messy DataFrame.
• Has columns for each ppt, which contain info about rt.

Check-in¶

Try to use pd.melt to turn df_messy2 into a tidy DataFrame.

Hint: Think about the existing structure of the DataFrame––how is data grouped––and what the id_vars would be.

Solution¶

Hands-on: a real dataset¶

Now, we’ll turn to a real dataset, which Timothy Lee, creator of Full Stack Economics, compiled and shared with me.

Check-in¶

Is this dataset tidy? How could we make it tidy, if not––i.e., if we wanted each row to be a single observation corresponding to one of the reasons for missing work?

Solution¶

Why tidy data is useful¶

Finally, let’s use this dataset to recreate a graph from FullStackEconomics.

Original graph¶

Check-in¶

As a first-pass approach, what tools from seaborn could you use to recreate this plot?

Solution¶

This is okay, but not really what we want. This is grouping it by Year. But we want to group by both Year and Month.

Using datetime¶

• Let’s make a new column called date, which combines the Month and Year.
• Then we can use pd.to_datetime to turn that into a custom pandas representation.

Plotting again¶

Much better!

Data Transformations¶

Sometimes we come across a situation where we have problems with skewed distributions or we simply want to transform, recode or perform discretization. Let’s look at some of the most popular transformation methods.

First, standardization (also known as normalization):

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

• minmax approach - An alternative approach to normalizing (or standardizing) the $Z$ score is the so-called MinMax scaling (often also called simply “normalization” - which is a common cause of ambiguity). In this approach, the data is scaled to a fixed range - usually from 0 to 1. The trade-off of having this limited range - unlike normalization - is that we get smaller standard deviations, which can suppress the effect of outliers. If you want to do MinMax scaling - simply subtract the minimum value and divide it by the range: $(x-min)/(max-min)$.

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: Normalize income and plot the transformed income distribution in a box plot.

Box - Cox Transformations¶

The Box-Cox transformation is a technique used in data wrangling that helps stabilize the variance and transform the data so that it is closer to a normal distribution—which is desirable in many statistical analysis and modeling methods. This transformation works particularly well for data with a right-skewed distribution. It requires positive values and involves finding the optimal parameter λ (lambda), which is chosen using the maximum likelihood method. In Python, the Box-Cox transformation can be performed using the boxcox function from the scipy.stats module.

Let $y=(y_1,y_2,…,y_n)$ be the data to which the Box-Cox transformation is to be applied. Box and Cox defined their transformation as:

for $\lambda \neq 0$:

for $\lambda = 0$

such as the unknown λ,

where $y^{(\lambda)}$ is the λ-transformed data, X is the design matrix (possible covariates of interest), β is a set of parameters associated with the λ-transformed data, and $ϵ = (ϵ_1, ϵ_2, ..., ϵ_n)$ is an error term. Since the goal of equation (1) is that

then $\epsilon ~ N(0, \sigma^2)$. Note that the transformation in equation (1) is only valid for $y_i$ > 0, i = 1, 2, ..., n, and modifications must be made when negative observations are present.

Data categorization¶

Sometimes we would simply like to perform the so-called “binning” procedure to be able to analyze our categorical data, compare several categorical variables, construct statistical models, etc. With the “binning” function, we can transform quantitative variables into categorical ones using several methods:

1. Quantile Binning

• Divides data into equal-sized groups based on percentiles. Formula:
• Example: pd.qcut(data, q=4) creates 4 quantile-based bins.

2. Equal-Width Binning

• binning to obtain a fixed length of intervals (e.g. every 100 USD)
• Formula:
  $$\text{Bin width} = \frac{\text{max(data)} - \text{min(data)}}{\text{number of bins}}$$

  (5)
• Example: pd.cut(data, bins=5) creates 5 equal-width bins.

3. Pretty Binning

• A compromise between quantile and equal-width binning, creating aesthetically pleasing intervals.
• Rounded values based on data range and bin count.

4. K-Means Binning

• Uses the K-Means clustering algorithm to group data into bins.
• Formula:
  $$\text{Centroids minimize } \sum_{i=1}^{n} (x_i - \mu_k)^2$$

  (6)
• where $( \mu_k )$ is the mean of cluster $( k )$.

5. Bag Clustering (BClust)

• Groups data using bagging techniques to improve clustering stability.
• Clusters are formed by aggregating results from multiple clustering models.

Each method is suited for different use cases, depending on the data distribution and analysis goals.

Exercise Using the quantile approach, binning the “Income” variable. Hint: Pandas has a ready-to-use function pd.qcut

Summary¶

That was an introduction to data wrangling.

• As mentioned, data organization is an incredibly important topic - and could be an entire subject in this postgraduate program.

• But today we focused on:

• Identifying and removing missing data.

• Merging data sets.

• Organizing data.