
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.
