Cleaning CSV Data with Pandas for AI: A Step-by-Step Script

This guide shows you how to turn a messy CSV file into a clean, model-ready dataset with Pandas in under fifteen minutes. You will load a raw file, fix missing values, drop duplicates, set the right column types, normalize text, and export a file you can hand straight to an AI step. Replace the example column names with your own headers, run the script, and you get a predictable dataset every time.

Pandas is the standard Python library for working with table-shaped data (rows and columns, like a spreadsheet). A DataFrame is the in-memory table Pandas builds from your file. If those two terms are new, that is fine, the steps below explain each line as you go.

Raw exports cause more AI problems than most people expect. Inconsistent casing turns Acme and acme into two different things. Trailing spaces hide in text fields. Empty cells trigger API validation errors. A clean file fixes all of that before the data ever reaches a model, which is why data hygiene is a foundation skill covered across Data Cleaning for AI.

Prerequisites

This guide assumes you already have Python 3.10 or newer and a working virtual environment. If you do not, set one up first with Create a Python Virtual Environment for AI, then return here.

Install the one dependency you need:

pip install pandas

You also need a CSV file to clean. The examples below use a file named input.csv with columns called company, prompt_text, category, and price. Swap these for your real headers as you follow along. To see your actual column names, run a quick check first:

import pandas as pd

df = pd.read_csv("input.csv")
print(df.columns.tolist())
print(df.shape)  # (rows, columns)

df.shape prints a pair like (1200, 4), meaning 1,200 rows and 4 columns. Note that starting number, you will compare it against the cleaned count at the end to confirm nothing went missing by accident.

Step 1: Load the CSV and inspect it

Always look at the data before you change it. Loading and inspecting takes seconds and saves you from cleaning the wrong column.

import pandas as pd

df = pd.read_csv("input.csv", encoding="utf-8-sig")  # utf-8-sig strips Excel's hidden BOM

print(df.info())   # column names, non-null counts, and types
print(df.head())   # the first five rows

df.info() is the most useful single command here. It lists every column, how many non-null (non-empty) values it holds, and the type Pandas guessed for it. If a column you expect to be numbers shows up as object (Pandas's label for text), that is an early warning that the column contains stray text you will fix in Step 3.

A BOM (byte order mark) is an invisible character some programs write at the start of a file. Reading with encoding="utf-8-sig" removes it so it does not contaminate your first column header.

Those few lines involve three separate things worth keeping apart in your head: a file sitting on disk, a parser that decides how bytes become text, and a table in memory that can then describe itself.

Step 2: Fix missing values

Missing values are the most common cause of AI API errors, because most endpoints reject empty or null text. Handle required fields and optional fields differently.

df = df.dropna(subset=["prompt_text", "category"])   # no text, no row
df["company"] = df["company"].fillna("unknown")      # keep the row, mark the gap

remaining_gaps = df[["prompt_text", "category"]].isnull().sum().sum()
print(f"Remaining gaps in required columns: {remaining_gaps}")

The difference matters. dropna deletes rows, so you only want it where a missing value makes the row worthless. fillna keeps the row and replaces the gap, which preserves data you can still use. Print the gap count to confirm the required columns are now fully populated.

The choice is easier to apply consistently if you reduce it to a single question you ask once per column, before you write either line.

Step 3: Remove duplicates and fix types

Duplicate rows inflate costs (you pay to process the same text twice) and skew any counts or analysis. Wrong types cause crashes the moment you call a text method on a number.

before = len(df)
df = df.drop_duplicates()                            # exact matches across every column
print(f"Removed {before - len(df)} duplicate rows")

df["prompt_text"] = df["prompt_text"].astype(str)    # .str methods need real strings
df["category"] = df["category"].astype(str)

df["price"] = pd.to_numeric(df["price"], errors="coerce")  # "N/A" becomes NaN, not a crash

drop_duplicates() with no arguments removes rows that match across all columns. If two rows should count as duplicates based on one key column only, pass subset=["prompt_text"] to compare just that field.

Note the limit of this approach: it only catches rows that are identical character for character. Two support tickets that say the same thing in different words survive it untouched. When that kind of near-duplicate is the problem you actually have, Remove Duplicate Records with Embeddings in Python shows the meaning-based approach that catches them.

pd.to_numeric(..., errors="coerce") is the safe way to convert text to numbers. The errors="coerce" setting turns anything it cannot parse into NaN (Pandas's missing-value marker) instead of crashing. After this line you can fill or drop those new gaps the same way you did in Step 2.

Step 4: Normalize text fields

Normalizing means forcing text into one consistent shape so identical values actually match. This is the step that most improves prompt accuracy and embedding quality.

df["prompt_text"] = (
    df["prompt_text"]
    .str.strip()                               # remove leading/trailing spaces
    .str.replace(r"\s+", " ", regex=True)      # collapse runs of whitespace
    .str.replace(r"[\r\n]+", " ", regex=True)  # flatten line breaks
)

df["category"] = df["category"].str.strip().str.lower()  # "Sales" and "SALES" become one value

Each method in the chain does one job. .str.strip() trims the outer spaces. .str.replace(r"\s+", " ", regex=True) collapses double and triple spaces into single ones. The line-break replacement flattens hidden \r and \n characters that break CSV and JSON payloads. Lowercasing labels is what lets later grouping treat Sales and sales as the same category.

Be deliberate about which columns you lowercase. Lowercasing a category label is helpful; lowercasing a sentence you plan to show a user later may not be. Apply it only where consistent matching matters more than the original casing.

Seeing one value travel through the chain makes the division of labour obvious: each call fixes exactly one defect and leaves the others alone.

Step 5: Validate and export a clean CSV

Before you trust the file, run three quick checks, then write it to a new name so your raw data stays intact.

assert df[["prompt_text", "category"]].isnull().sum().sum() == 0   # 1. no gaps left
print(f"Final rows: {len(df)}")                                    # 2. expected row count
print(df.sample(min(5, len(df))))                                  # 3. eyeball a few rows

df.to_csv("clean_output.csv", index=False, encoding="utf-8")  # index=False drops the row numbers
print("Saved clean_output.csv, ready for your AI step.")

Writing to clean_output.csv instead of overwriting input.csv means you can re-run with different settings if you spot a problem. The cleaned file is now ready to feed into an AI workflow, whether you are sending each row to a model or building embeddings from it. For where that data goes next, see Understanding LLM APIs.

Two things are worth checking before you start sending rows. If any single cleaned cell is longer than a model will accept in one request, break it up first using Split Long Text into Chunks for AI APIs. And if the file is large enough that the bill matters, price the run up front with Count Tokens in Python Before You Send rather than discovering the cost afterwards.

Key-parameter quick reference

Troubleshooting

1. UnicodeDecodeError: 'utf-8' codec can't decode byte — Cause: the file is not UTF-8, often it is Latin-1 from an older system. Fix: pass the matching encoding, for example pd.read_csv("input.csv", encoding="latin-1").
2. AttributeError: Can only use .str accessor with string values — Cause: you called a .str method on a column that holds numbers or mixed types. Fix: cast it first with df["col"] = df["col"].astype(str) before the normalization chain.
3. KeyError: 'prompt_text' — Cause: the column name in your code does not match the file, often due to a trailing space or different casing in the header. Fix: run print(df.columns.tolist()) and copy the exact name, or normalize headers with df.columns = df.columns.str.strip().
4. Cleaned file shows garbled symbols in Excel — Cause: Excel expects a BOM to read UTF-8 correctly. Fix: write with df.to_csv("clean_output.csv", index=False, encoding="utf-8-sig") so Excel renders accents and emoji properly.

If you hit an error that is not on this list, the fastest route is to read the failure report Python prints rather than guessing; Read a Python Traceback in Five Minutes walks through how to find the offending line.

When to use this vs. alternatives

• Use this Pandas script when your data fits in memory (up to a few hundred thousand rows on a normal laptop) and you want full control over each cleaning rule. This is the right default for almost every creator, marketer, or founder project.
• Reach for a database query instead when the file is too large to load at once or already lives in a SQL system. Filtering and deduplicating in SQL before exporting a smaller, cleaner CSV avoids loading the whole thing into Python.
• Skip dedicated cleaning code when the work is genuinely one-off and tiny, say a dozen rows you can fix by hand in a spreadsheet. A script pays off when you will repeat the job or need the result to be exactly reproducible. If this cleaning is part of a recurring pipeline, wrap it in a scheduled run as shown in Automating Repetitive Tasks with Python.

Back to Data Cleaning for AI.

Related guides

• Data Cleaning for AI — the main guide for preparing data for AI, with the full set of preprocessing techniques.
• Split Long Text into Chunks for AI APIs — the next step when a single cleaned cell is too long to send in one request.
• Automating Repetitive Tasks with Python — turn this one-off clean-up into a script that runs on a schedule.
• Understanding LLM APIs — where your cleaned data goes next when you send it to a model.
• Create a Python Virtual Environment for AI — set up the isolated Python environment these scripts assume.