liveBook · Manning

Data cleaning

As explained in Learn AI Data Engineering

Data cleaning is the process of detecting and correcting, removing, or standardizing inaccurate, incomplete, duplicated, or unsuitable data.

Data cleaning makes data usable by detecting and correcting quality problems such as missing values, duplicate rows, irrelevant columns, inconsistent formatting, and unsuitable records. In practical pipelines, the work may be expressed as traditional Python transformations or delegated to an AI assistant that applies explicitly described rules. section 1.2.1 section 6.2

A basic cleanup workflow typically:

  • Identifies duplicate records and removes repeated rows. section 6.2
  • Finds columns dominated by null values and removes them using a defined threshold. section 6.2
  • Uses structured response models when AI determines what should be removed, returning duplicate indexes and columns to drop as parsed Python data. section 6.2.3
  • Validates cleaned content and fails loudly when upstream retrieval produces suspiciously empty data. section 12.3.3

Cleaning a customer DataFrame

A sample dataset contains duplicate customer 2 rows, a mostly null optional_note column, and a missing store location for customer 3. A pandas workflow calls drop_duplicates(), computes a 50-percent null threshold, and filters columns against it. The resulting table keeps customer_id, preferred_store_location, and purchase_amount, while removing optional_note and one duplicate row. An AI workflow can reach the same result by passing record dictionaries in a user message and receiving structured cleanup instructions through response_format.

Listing 6.4 AI - Fixing Data Quality Issues
import pandas as pd
import openai
import os
from dotenv import load_dotenv
from pydantic import BaseModel
from typing import List
 
load_dotenv()
openai.api_key = os.getenv("OPENAI_API_KEY")
 
# Define the response structure using a data class  #A
class CleanedData(BaseModel):  #A
    duplicates: List[int]              #A
    drop_columns: List[str]            #A
 
# Create a sample DataFrame with duplicate and null-heavy data  #B
df = pd.DataFrame({  #B
    'customer_id': [1, 2, 2, 3, 3],  #B
    'preferred_store_location': ['NY', 'CA', 'CA', 'TX', None],  #B
    'purchase_amount': [200, 300, 300, 400, 400],  #B
    'optional_note': [None, None, None, None, None]  #B
})
 
# Convert DataFrame to list of record dictionaries for input  #C
records = df.to_dict(orient="records")  #C
 
# Compose prompt asking for duplicates and null-heavy columns  #D
prompt = (
    "You are a data cleaning assistant. Review the dataset and return the following:\n"
    "- A list of row indexes that are exact duplicates based on values.\n"
    "- A list of column names that contain more than 50% null values and should be dropped.\n"
    "Respond with only the required data for cleaning."  #D
)
 
# Make the Chat Completions API call with structured response_format  #E
completion = openai.beta.chat.completions.parse(  #E
    model="gpt-4o",
    messages=[
        {"role": "system", "content": prompt},  #E
        {"role": "user", "content": str(records)}  #E
    ],
    response_format=CleanedData #E
)
 
# Parse response into the data class structure  #F
cleaning_info = completion.choices[0].message.parsed  #F
 
# Remove duplicate rows from the DataFrame  #G
df_cleaned = df.drop(index=cleaning_info.duplicates)  #G
 
# Drop irrelevant columns from the DataFrame  #G
df_cleaned = df_cleaned.drop(columns=cleaning_info.drop_columns, errors='ignore')  #G
 
# Output the cleaned DataFrame  #H
print(df_cleaned)  #H

Inspect the complete AI-assisted pattern: define a Pydantic response contract, convert a DataFrame to records, and request predictable duplicate and column-removal decisions. section 6.2.3

Listing 12.4 Agent 2: fetch and clean
import requests
from bs4 import BeautifulSoup
 
REMOVE_TAGS = [  #A
    "script", "style", "nav", "footer", "header",
    "iframe", "noscript", "svg", "form",
]
REMOVE_CLASSES = [
    "breadcrumb", "related-products", "recently-viewed",
    "newsletter", "cookie-banner", "site-footer", "site-header",
    "cart-drawer", "search-modal", "review", "reviews", "ratings",
]
 
SESSION = requests.Session()  #B
SESSION.headers.update({
    "User-Agent": (
        "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) "
        "AppleWebKit/537.36 (KHTML, like Gecko) "
        "Chrome/121.0.0.0 Safari/537.36"
    ),
    "Accept-Language": "en-US,en;q=0.9",
})
 
@with_retries(max_attempts=3, exceptions=(requests.RequestException,))  #C
def _fetch(url):
    resp = SESSION.get(url, timeout=20)
    resp.raise_for_status()
    return resp.text
 
def clean_html_aggressive(html: str) -> str:  #D
    """Strip non-product noise; returns extraction-ready text."""
    soup = BeautifulSoup(html, "html.parser")
    for tag_name in REMOVE_TAGS:
        for element in soup.find_all(tag_name):
            element.decompose()
    for pattern in REMOVE_CLASSES:
        for element in soup.find_all(
            class_=lambda c: c and pattern in " ".join(c).lower()
        ):
            element.decompose()
    return " ".join(soup.stripped_strings)
 
def fetch_and_clean(url: str) -> str:  #E
    """Agent: fetch a URL and return cleaned page text."""
    raw_html = _fetch(url)  #F
    cleaned = clean_html_aggressive(raw_html)  #G
    if len(cleaned) < 200:  #H
        raise ValueError("page too small after cleaning")
    return cleaned

Inspect a web-scraping variant that removes unwanted HTML elements, retries failed requests, and rejects pages that clean down to suspiciously little text. section 12.3.3

How Learn AI Data Engineering covers it

How deep it goes
Explains duplicate removal, null-column filtering, structured AI responses, retries, and empty-content validation without detailing broader governance policies.
Examples
Uses a customer DataFrame with duplicate rows and a null-heavy column, plus HTML pages requiring tag removal and retrieval checks.
What it assumes
Assumes basic Python and DataFrame familiarity while demonstrating the cleanup logic and response structure needed for AI assistance.
What you can do after
Apply pandas or structured Chat Completions workflows to remove duplicates, filter null-heavy columns, and validate cleaned content.
How it connects
Connects data quality with pandas, structured outputs, the Chat Completions API, data transformation, and web scraping.

Also explained in

Compare how 18 books explain data cleaning →

Related concepts

This explainer was generated by Manning's knowledge graph from the text of Learn AI Data Engineering. The section links point to the parts of the book it draws on.