Cleaning PredictHealth's Customer Database

Introduction And Lesson Overview

Welcome back! In the last lesson, you learned how to build a complete insurance cost prediction model using both numerical and categorical features. You also practiced encoding categorical variables and building a modeling pipeline. Now, you are ready to take the next step: preparing your data for real-world challenges. In practice, data is rarely perfect. Customer databases often contain missing values, outliers, duplicates, and inconsistencies. If these issues are not addressed, your models may produce unreliable results or even fail to run. That is why data cleaning is a critical step in any data science project. In this lesson, you will learn how to clean PredictHealth's customer database so it is ready for modeling. You will inspect the data for problems, handle missing values, remove duplicate records, detect and treat outliers, and normalize numerical features. By the end, you will have a clean dataset that is ready for building robust predictive models. This lesson will build directly on your previous work, but with a focus on making your data as reliable as possible.

Inspecting The Messy Dataset

Before you can clean your data, you need to know what problems exist. In real-world datasets, it is common to find missing values and outliers. Missing values are simply empty cells in your data, while outliers are values that are unusually high or low compared to the rest of the data. Let's look at an example. Suppose you have a copy of the insurance data, but it has been made "messy" for demonstration purposes. This messy dataset has missing values added to all columns (about 6% missing rate) and some extreme outliers in the charges and bmi columns. Here is how you can check for missing values in the dataset: Python print("Checking for missing values:") print(messy_data.isnull().sum()) The output might look like this: plaintext Checking for missing values: age 78 sex 82 bmi 85 children 79 smoker 84 region 77 charges 86 dtype: int64 This tells you how many missing values are in each column. Even a few missing values can cause problems for your model, especially if they are in important columns. Notice that in this messy dataset, all columns have missing values, which is common in real-world scenarios. Outliers are another issue. The messy dataset contains some extreme outliers: insurance charges ranging from $100,000 to $200,000, and BMI values between 50 and 70. These values are far outside the normal range and can distort your model's understanding of the data, leading to poor predictions.

Join the 1M+ learners on CodeSignal

Be a part of our community of 1M+ users who develop and demonstrate their skills on CodeSignal

Detecting And Treating Outliers

Outliers are values that are much higher or lower than most of the data. They can have a big impact on your model, especially in regression tasks. One common way to detect outliers is the Interquartile Range (IQR) method. The IQR is the range between the 25th and 75th percentiles of the data. Any value outside 1.5 times the IQR from the lower or upper quartile is considered an outlier.

Here is a function that detects and caps outliers using the IQR method:

Python

print("\nData cleaning step 2: Handling outliers")
print("=" * 50)

import numpy as np

def clean_outliers(df, column):
    Q1 = df[column].quantile(0.25)
    Q3 = df[column].quantile(0.75)
    IQR = Q3 - Q1
    lower_bound = Q1 - 1.5 * IQR
    upper_bound = Q3 + 1.5 * IQR
    
    outliers = df[(df[column] < lower_bound) | (df[column] > upper_bound)]
    print(f"Outliers capped in {column}: {len(outliers)}")
    
    df[column] = np.where(df[column] < lower_bound, lower_bound, df[column])
    df[column] = np.where(df[column] > upper_bound, upper_bound, df[column])
    
    return df

print("\nData cleaning step 2: Handling outliers")
print("=" * 50)

import numpy as np

def clean_outliers(df, column):
    Q1 = df[column].quantile(0.25)
    Q3 = df[column].quantile(0.75)
    IQR = Q3 - Q1
    lower_bound = Q1 - 1.5 * IQR
    upper_bound = Q3 + 1.5 * IQR
    
    outliers = df[(df[column] < lower_bound) | (df[column] > upper_bound)]
    print(f"Outliers capped in {column}: {len(outliers)}")
    
    df[column] = np.where(df[column] < lower_bound, lower_bound, df[column])
    df[column] = np.where(df[column] > upper_bound, upper_bound, df[column])
    
    return df

You can apply this function to numerical columns like age, bmi, and charges:

Python

for col in ['age', 'bmi', 'charges']:
    messy_data = clean_outliers(messy_data, col)

print("Outliers handled successfully!")

for col in ['age', 'bmi', 'charges']:
    messy_data = clean_outliers(messy_data, col)

print("Outliers handled successfully!")

The output will show how many outliers were found and capped in each column:

plaintext

Data cleaning step 2: Handling outliers
==================================================
Outliers capped in age: 0
Outliers capped in bmi: 5
Outliers capped in charges: 8
Outliers handled successfully!

Data cleaning step 2: Handling outliers
==================================================
Outliers capped in age: 0
Outliers capped in bmi: 5
Outliers capped in charges: 8
Outliers handled successfully!

This step helps keep your data realistic and prevents extreme values from skewing your model.

Normalizing Numerical Features

After handling missing values, duplicates, and outliers, it is a good idea to normalize your numerical features. Normalization scales all values to a similar range, usually between 0 and 1. This is especially important when your features have very different scales, as it helps the model treat all features fairly.

You can use the MinMaxScaler from scikit-learn to normalize your data:

Python

print("\nData cleaning step 4: Normalizing numerical features")
print("=" * 50)

from sklearn.preprocessing import MinMaxScaler

# Create list of numerical columns to normalize
numerical_columns = ['age', 'bmi', 'children']

# Store original min/max values before scaling
original_ranges = {}
for col in numerical_columns:
    original_ranges[col] = {
        'min': messy_data[col].min(),
        'max': messy_data[col].max()
    }

# Initialize and apply MinMaxScaler
scaler = MinMaxScaler()
messy_data[numerical_columns] = scaler.fit_transform(messy_data[numerical_columns])

print("\nData cleaning step 4: Normalizing numerical features")
print("=" * 50)

from sklearn.preprocessing import MinMaxScaler

# Create list of numerical columns to normalize
numerical_columns = ['age', 'bmi', 'children']

# Store original min/max values before scaling
original_ranges = {}
for col in numerical_columns:
    original_ranges[col] = {
        'min': messy_data[col].min(),
        'max': messy_data[col].max()
    }

# Initialize and apply MinMaxScaler
scaler = MinMaxScaler()
messy_data[numerical_columns] = scaler.fit_transform(messy_data[numerical_columns])

After normalization, you can check the results:

Python

# Print min and max values for each normalized column
for col in numerical_columns:
    print(f"Normalized {col} - Min: {messy_data[col].min():.6f}, Max: {messy_data[col].max():.6f}")

# Calculate and show range transformation for age column
original_age_range = original_ranges['age']['max'] - original_ranges['age']['min']
normalized_age_range = messy_data['age'].max() - messy_data['age'].min()

print(f"\nAge column transformation:")
print(f"Original range: {original_ranges['age']['min']:.1f} to {original_ranges['age']['max']:.1f} (range: {original_age_range:.1f})")
print(f"Normalized range: {messy_data['age'].min():.6f} to {messy_data['age'].max():.6f} (range: {normalized_age_range:.6f})")

print(f"\nNormalization completed successfully!")

# Print min and max values for each normalized column
for col in numerical_columns:
    print(f"Normalized {col} - Min: {messy_data[col].min():.6f}, Max: {messy_data[col].max():.6f}")

# Calculate and show range transformation for age column
original_age_range = original_ranges['age']['max'] - original_ranges['age']['min']
normalized_age_range = messy_data['age'].max() - messy_data['age'].min()

print(f"\nAge column transformation:")
print(f"Original range: {original_ranges['age']['min']:.1f} to {original_ranges['age']['max']:.1f} (range: {original_age_range:.1f})")
print(f"Normalized range: {messy_data['age'].min():.6f} to {messy_data['age'].max():.6f} (range: {normalized_age_range:.6f})")

print(f"\nNormalization completed successfully!")

The output will show that all normalized values are between 0 and 1:

plaintext

Data cleaning step 4: Normalizing numerical features
==================================================
Normalized age - Min: 0.000000, Max: 1.000000
Normalized bmi - Min: 0.000000, Max: 1.000000
Normalized children - Min: 0.000000, Max: 1.000000

Age column transformation:
Original range: 18.0 to 64.0 (range: 46.0)
Normalized range: 0.000000 to 1.000000 (range: 1.000000)

Normalization completed successfully!

Data cleaning step 4: Normalizing numerical features
==================================================
Normalized age - Min: 0.000000, Max: 1.000000
Normalized bmi - Min: 0.000000, Max: 1.000000
Normalized children - Min: 0.000000, Max: 1.000000

Age column transformation:
Original range: 18.0 to 64.0 (range: 46.0)
Normalized range: 0.000000 to 1.000000 (range: 1.000000)

Normalization completed successfully!

Notice that we don't normalize the charges column since it's our target variable, and we want to keep it in its original scale for interpretability.

Introduction And Lesson Overview

Inspecting The Messy Dataset

Join the 1M+ learners on CodeSignal

Handling Missing Values

Detecting And Treating Outliers

Standardizing Categorical Values

Normalizing Numerical Features

Final Data Preparation Overview

Summary And Preparation For Practice