CSV Files with Python & pandas — A Beginner's Guide

Every bank, online shop, and web form exports data as a CSV file — a plain-text table where commas separate the columns. Your scraped prices, your bank statements, your form responses: all CSV. And almost none of it arrives clean.

That's where pandas comes in. It's Python's spreadsheet brain: you load a CSV, and instead of clicking around a screen, you write commands that clean, filter, and summarize the data — repeatably. This guide takes you from "never opened a CSV in Python" to a working data-cleaning script in about ten minutes.

Setup: One Install

pip install pandas

That's the whole setup. pandas is the only third-party package we need — everything else uses Python's standard library.

What a CSV Actually Looks Like

Open a CSV file in any text editor and you'll see something like:

Date , Item ,  Cost,Category
2026-08-01,USB cable, $5.99,Electronics
2026-08-02,Coffee beans, 12.50,Food

First line = column names. Every other line = one row. Commas = the gaps between columns. Messy real-world exports add their own chaos: stray spaces, dollar signs inside numbers, missing values, duplicated rows. All of it shows up in the file above — we'll fix every bit.

Step 1: Load the File and Look Around

import pandas as pd

df = pd.read_csv("messy_expenses.csv")
print(df.shape)      # (rows, columns)
print(df.columns)    # column names
print(df.head())     # first 5 rows

Run that against our messy file and you get:

(6, 4)
Index(['Date ', ' Item ', '  Cost', 'Category'], dtype='object')
     Date   Item    Cost     Category
0  2026-08-01  USB cable   $5.99  Electronics
1  2026-08-02  Coffee beans  12.50  Food
...

df is a DataFrame — pandas' name for a table that lives in memory. It already noticed something useful: the column names have stray spaces ('Date ', ' Item '). That's our first fix.

Step 2: Fix the Column Names

df.columns = [c.strip() for c in df.columns]
print(df.columns)
# Index(['Date', 'Item', 'Cost', 'Category'], dtype='object')

strip() removes whitespace from both ends of a string. One line, and every column name is clean — much easier than renaming them one by one.

Step 3: Fix the Values

Same trick, applied to the data instead of the headers:

df["Item"] = df["Item"].str.strip()

df["Item"] selects one column, and .str gives every string in it the string methods you already know (strip, lower, replace...). This is how pandas handles text: one method call per column, applied to every row at once — no loops.

Step 4: Remove Duplicate Rows

Exports often contain the same row twice (double exports, retried uploads). One call removes them:

before = len(df)
df = df.drop_duplicates()
print(f"{before} -> {len(df)} rows")   # 6 -> 5 rows

By default this compares entire rows. If your duplicate rows differ in one column (say, two purchases of the same item on different dates), tell pandas which columns make a row "the same":

df = df.drop_duplicates(subset=["Item"])   # one row per item

Step 5: Fill the Gaps

Rows where the Category is empty arrived as NaN — pandas' marker for "missing". Decide on a default and fill it:

df["Category"] = df["Category"].fillna("Other")

Now every row has a category. The alternative — df.dropna() — throws away rows with any missing value. Use it when you'd rather lose a few rows than guess.

Step 6: Turn Text into Numbers

The Cost column is useless as long as it says $5.99 — that's text, and you can't sum text. Two small steps fix it:

df["Cost"] = df["Cost"].str.replace("$", "", regex=False).astype(float)
print(df["Cost"].dtype)   # float64

Line by line: .str.replace("$", "") deletes the dollar sign, and .astype(float) converts the rest to real numbers. The regex=False matters more than it looks: str.replace may treat the first argument as a regular expression, and the default has changed across pandas versions — so the same code can behave differently depending on what's installed. The classic trap: in regex, . matches any character, so str.replace("a.c", "X") in regex mode rewrites "abc" and "axc" too. Writing regex=False explicitly means "treat this as plain text" — on every version.

Step 7: Ask Questions

Now the fun part. The data is clean, so answers are one-liners:

# What did I spend on food?
food = df[df["Category"] == "Food"]
print(food)

# What were the three most expensive things?
print(df.sort_values("Cost", ascending=False).head(3))

# Total spend per category?
print(df.groupby("Category")["Cost"].sum())

Category
Electronics     5.99
Food           17.25
Other           8.00
Name: Cost, dtype: float64

Three patterns to memorize: df[condition] filters rows, sort_values orders them, and groupby is the pivot table — group rows by a column, then sum/average/count another. These three cover 80% of everyday data questions.

Step 8: Save the Clean File

df.to_csv("clean_expenses.csv", index=False)

Two things to know: index=False stops pandas from adding an extra number column to your file (people forget this constantly and end up with a mystery first column), and the result is another plain CSV — openable in Excel, Google Sheets, or the next Python script.

The Complete Script

All eight steps together, ready to run:

"""clean-expenses.py — tidy up a messy CSV export."""
import pandas as pd

df = pd.read_csv("messy_expenses.csv")
df.columns = [c.strip() for c in df.columns]          # fix headers
df["Item"] = df["Item"].str.strip()                   # fix values
df = df.drop_duplicates()                             # remove doubles
df["Category"] = df["Category"].fillna("Other")       # fill gaps
df["Cost"] = df["Cost"].str.replace("$", "", regex=False).astype(float)

print(df.sort_values("Cost", ascending=False).head(3))
print(df.groupby("Category")["Cost"].sum())
df.to_csv("clean_expenses.csv", index=False)
print("Saved clean_expenses.csv")

Swap the filename and the fixes to match whatever mess your own export contains — the skeleton stays identical.

Where to Go From Here

CSV is the glue format of automation. The price tracker saves its price history as a CSV, so this guide's skills let you chart your own data. Want Excel instead of CSV at the end? The Excel automation guide shows to_excel(). And once you're comfortable filtering and grouping, the natural next step is combining two files (a price list + a stock list) with pd.merge() — same idea, one more column.

Gotchas (And Their Fixes)

One last tip: whenever a CSV misbehaves, open it in a text editor first — not Excel, which politely hides the commas, the encoding, and everything else that's actually wrong.

All code in this article was tested and runs successfully on Python 3.8.10 (Ubuntu 20.04) with pandas 2.0.3 — verified August 2026.