How to Normalize Entities in a DataFrame with Featuretools Using normalize_entity

How to Normalize Entities in a DataFrame with Featuretools Using normalize_entity
Image by Editor | ChatGPT

Introduction

In data analysis and machine learning, we often work with complex datasets in a single, denormalized table. This can cause inefficiencies like redundancy, slow processing, and analysis challenges. Entity normalization splits denormalized data into smaller, related tables. This process reduces redundancy, improves organization, and makes data easier to analyze. Featuretools is a Python library that automates entity normalization, helping transform raw data into structured formats for better analysis and feature engineering.

In this article, we’ll explore how to normalize entities in a Pandas DataFrame using Featuretools.

Prerequisites

First, you’ll need to install and import Featuretools. You can install Featuretools using pip:

pip install featuretools

Once installed, import the necessary libraries:

import featuretools as ft
import pandas as pd

What is normalize_dataframe?

The normalize_dataframe method in Featuretools splits a single table within an EntitySet into two separate, related tables. For example, if you have one table containing both customer and order details, this method can create a new, distinct table for customers while leaving the order information in the original table. It automatically creates and preserves the relationship between these two tables, which helps organize the data and makes it easier to work with.

The basic syntax of normalize_dataframe is:

entityset.normalize_dataframe(
    base_dataframe_name, 
    new_dataframe_name, 
    index, 
    make_time_index=None, 
    additional_columns=None
)

Here’s a breakdown of the parameters:

  • base_dataframe_name: The name of the existing dataframe in the EntitySet from which you want to create a new one
  • new_dataframe_name: The name for the new DataFrame that will be created
  • index: The column in the base DataFrame that will become the unique index of the new DataFrame
  • make_time_index: If provided, specifies a column to be used as the time index for the new DataFrame
  • additional_columns: A list of other columns to move from the base dataframe to the new DataFrame

To illustrate how to use normalize_dataframe, let’s walk through a simple example.

Preparing Data

Consider a scenario where we have sales data in a single DataFrame that includes customer information and their orders. 

# Sample data
data = {
    'customer_id': [1, 2, 1, 3],
    'customer_name': ['Ron', 'Katie', 'Ron', 'Lily'],
    'order_id': [101, 102, 103, 104],
    'order_date': ['2025-01-01', '2025-01-05', '2025-02-01', '2025-02-05'],
    'order_amount': [250, 400, 150, 300]
}

df = pd.DataFrame(data)

Creating an EntitySet

An EntitySet in Featuretools holds all DataFrames in a dataset and their relationships. In this case, we only have one DataFrame, so we’ll start by creating an EntitySet with a single entity representing our orders.

# Create an EntitySet
es = ft.EntitySet(id="sales_data")

# Add the original DataFrame as an entity in the EntitySet, using a unique index
es.add_dataframe(
    dataframe_name="orders",
    dataframe=df,
    index="order_id",
    time_index="order_date"
)

Normalizing Entities

Now, we will use normalize_dataframe to extract customer information from the orders entity into a new, separate customers entity.

# Normalize the "orders" DataFrame to create a "customers" entity
es.normalize_dataframe(
    base_dataframe_name="orders",
    new_dataframe_name="customers",
    index="customer_id",
    additional_columns=["customer_name"]
)

The original orders DataFrame is now split. We created a new customers entity that holds customer-related information (customer_id and customer_name). The orders entity now contains only order-specific details (order_id, order_date, and order_amount) and a customer_id column that links each order back to the customers entity. This separation makes the data more organized and easier to work with.

Viewing the Normalized Entities

Now that we’ve normalized the entities, we can inspect the DataFrames within our EntitySet:

# View the new customers dataframe
print("Customers DataFrame:")
print(es["customers"].df)

# View the modified orders dataframe
print("\nOrders DataFrame:")
print(es["orders"].df)
Customers DataFrame:
   customer_id customer_name
0            1           Ron
1            2         Katie
2            3          Lily
Orders DataFrame:
          order_date  order_amount  customer_id
order_id                                     
101       2025-01-01           250            1
102       2025-01-05           400            2
103       2025-02-01           150            1
104       2025-02-05           300            3

Conclusion

Featuretools simplifies the process of breaking down complex, denormalized data into separate, related entities. By organizing data into distinct tables for concepts like customers and orders, it ensures that relationships between the data are preserved. Normalizing entities makes data easier to analyze and saves time by automating a traditionally manual process. This structured approach is fundamental for effective machine learning and data analysis.

Leave a Reply

Your email address will not be published. Required fields are marked *