Cleaning PredictHealth's Customer Database

Cleaning PredictHealth's Customer Database | CodeSignal Learn

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

100,000 to

Join the 1M+ learners on CodeSignal

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

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())

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

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.

Handling Missing Values

Once you have identified missing values, you need to decide how to handle them. For numerical features like age, bmi, and children, a common approach is to fill in missing values with the median of that column. The median is less affected by outliers than the mean, so it is a robust choice. Here is how you can fill missing values for numerical features: Python print("Data cleaning step 1: Handling missing values") print("=" * 50) for col in ['age', 'bmi', 'children']: messy_data[col] = messy_data[col].fillna(messy_data[col].median()) print("Data cleaning step 1: Handling missing values") print("=" * 50) for col in ['age', 'bmi', 'children']: messy_data[col] = messy_data[col].fillna(messy_data[col].median()) For categorical features such as sex, smoker, and region, you can fill missing values with the most common value, also known as the mode. This ensures that the filled value is a valid category. Python for col in ['sex', 'smoker', 'region']: messy_data[col] = messy_data[col].fillna(messy_data[col].mode()[0]) for col in ['sex', 'smoker', 'region']: messy_data[col] = messy_data[col].fillna(messy_data[col].mode()[0]) If the target variable (charges) is missing, it is best to remove those rows entirely, since you cannot train or evaluate a model without a target value. Python messy_data = messy_data.dropna(subset=['charges']) print("Missing values handled successfully!") messy_data = messy_data.dropna(subset=['charges']) print("Missing values handled successfully!") The output will be: plaintext Data cleaning step 1: Handling missing values ================================================== Missing values handled successfully! Data cleaning step 1: Handling missing values ================================================== Missing values handled successfully! After completing these steps, your dataset will be free of missing values and ready for the next cleaning step.

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: Pythonprint("\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 dfprint("\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: Pythonfor 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: plaintextData 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.

Standardizing Categorical Values

Real-world categorical data often contains inconsistencies like different casing ("Male" vs "male") and extra whitespace. These can create artificial categories that hurt model performance. Here's how to standardize categorical values: Pythonprint("\nData cleaning step 3: Standardizing categorical values") print("=" * 50) categorical_columns = ['sex', 'smoker', 'region'] # Standardize categorical columns for col in categorical_columns: messy_data[col] = messy_data[col].astype(str).str.strip().str.lower() print("Categorical values standardized successfully!")print("\nData cleaning step 3: Standardizing categorical values") print("=" * 50) categorical_columns = ['sex', 'smoker', 'region'] # Standardize categorical columns for col in categorical_columns: messy_data[col] = messy_data[col].astype(str).str.strip().str.lower() print("Categorical values standardized successfully!") The output will be: plaintextData cleaning step 3: Standardizing categorical values ================================================== Categorical values standardized successfully!Data cleaning step 3: Standardizing categorical values ================================================== Categorical values standardized successfully! This ensures all categorical values are lowercase and have no extra spaces, making them consistent for modeling.

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: Pythonprint("\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: plaintextData 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.

Final Data Preparation Overview

You have now completed all the key steps in cleaning your dataset. You started by inspecting the data for missing values and outliers, then filled in or removed problematic values. You normalized the numerical features so they are on the same scale, ensuring your data is clean and consistent. The cleaned dataset is now ready for preprocessing steps like categorical encoding (which you learned in the previous lesson) before modeling. You can check its final shape: Pythonprint(f"Final dataset shape: {messy_data.shape}")print(f"Final dataset shape: {messy_data.shape}") The output will show your cleaned dataset dimensions: plaintextFinal dataset shape: (1252, 7)Final dataset shape: (1252, 7) All data quality issues have been resolved and the data is ready for modeling.