Advanced Data Wrangling

Tackling Missing Data

In a perfect world, every dataset would be complete. In reality, data is often full of holes. The first question when you find a missing value is always the same: delete it or fill it in? Deleting rows with missing data is the simplest path, but it's often a costly one. If a particular column has many missing values, deleting every incomplete row could mean throwing away a huge chunk of your dataset, and with it, valuable information.

The goal is to handle missingness without introducing bias or sacrificing too much data.

This is where imputation comes in. Instead of deleting, you make an educated guess to fill the gap. The most common strategies are straightforward. For numerical data, you can use the column's mean or median. For categorical data, the mode (the most frequent value) is a solid choice. These methods are fast and easy, but they can reduce the variance in your data and, in some cases, distort relationships between variables.

More sophisticated techniques exist, such as using regression to predict the missing value based on other columns. Another advanced method is K-Nearest Neighbors imputation, which finds similar data points and uses their values to fill in the blank. The right strategy depends on how much data is missing and the nature of your analysis.

Finding the Odd Ones Out

Outliers are data points that are significantly different from other observations. They can be legitimate, rare events or simple data entry errors. Either way, they can skew your analysis, pulling averages and inflating variance. Identifying them is a crucial step in cleaning your data.

One popular method for spotting outliers is the Z-score. It tells you how many standard deviations away a data point is from the mean. A common rule of thumb is to flag any data point with a Z-score greater than 3 or less than -3 as an outlier.

Z = \frac{x - \mu}{\sigma}

The Z-score works well for data that is roughly bell-shaped (normally distributed). When your data is skewed, the Interquartile Range (IQR) method is a more robust alternative. It focuses on the middle 50% of the data, making it less sensitive to extreme values.

To use it, you first find the range between the first quartile (Q1, the 25th percentile) and the third quartile (Q3, the 75th percentile). Any point that falls below $Q1 - 1.5 \times IQR$ or above $Q3 + 1.5 \times IQR$ is considered an outlier.

Reshaping Your Data

Sometimes your data isn't messy, just in the wrong shape. Datasets often come in a "wide" format, where each observational unit has its data spread across multiple columns. For many types of analysis and visualization, you need a "long" format, where each row is a single observation.

Name	Test 1	Test 2	Test 3
Alice	88	92	85
Bob	76	81	79

The table above is in a wide format. It's easy for a human to read, but what if you wanted to calculate the average score across all tests? You'd have to average three separate columns. The process of converting a wide table to a long one is called melting.

Name	Test	Score
Alice	Test 1	88
Alice	Test 2	92
Alice	Test 3	85
Bob	Test 1	76
Bob	Test 2	81
Bob	Test 3	79

This is the same data in a long, or "tidy," format. Now, all scores are in a single column, making aggregation much simpler. The reverse operation, going from long to wide, is called pivoting. Knowing how to reshape your data is fundamental for preparing it for different analysis tools and models. The concept of tidy data was popularized by statistician Hadley Wickham and has become a cornerstone of modern data analysis.

Cleaning Unruly Text

Text data is notoriously messy. A common issue is inconsistent labeling in categorical data, like a 'Country' column containing "USA", "U.S.", and "United States". The solution is standardization: choose one format ("USA") and convert all variations to match. This often involves simple find-and-replace operations.

For more complex text cleaning, you need a more powerful tool. Regular expressions (or regex) are sequences of characters that define a search pattern. They are incredibly useful for extracting specific information from strings, like pulling phone numbers from a block of text, or for cleaning messy data, like removing unwanted characters or HTML tags.

For example, you might have a column of product IDs that are supposed to be three letters followed by four numbers (e.g., 'ABC1234'), but some entries have extra characters like 'ABC-1234'. A simple regex could find all entries that don't match the correct pattern, or even automatically strip the unwanted characters.

# Example of using regex in Python to find a pattern
import re

text = "My product ID is FRT-4567. Theirs is HJK9012."

# This pattern looks for 3 letters, an optional hyphen, and 4 digits
pattern = r'[A-Z]{3}-?\d{4}'

product_ids = re.findall(pattern, text)
print(product_ids) # Output: ['FRT-4567', 'HJK9012']

While regex has a steep learning curve, mastering the basics can save you countless hours of manual text cleaning.

Quiz Questions 1/5

When handling missing numerical data, what is a potential drawback of imputing the missing values with the column's mean?

Quiz Questions 2/5

According to the Interquartile Range (IQR) method, a data point is considered an outlier if it is greater than which of the following thresholds?

Applied Data Analytics Mastery