Working with CSV Files in Python

CSV is the most common data interchange format you'll encounter — spreadsheet exports, database dumps, API responses, IoT sensor logs. This article covers Python's built-in python csv module in full: reader/writer, DictReader/DictWriter, the newline='' gotcha that trips up nearly everyone on Windows, custom delimiters, and an honest comparison to when pandas is actually the better tool.

Introduction: Why CSV Needs Its Own Module

CSV is everywhere

CSV (comma-separated values) is the default export format for spreadsheets, a common database dump format, a frequent shape for API responses, and a typical log format for IoT devices — genuinely one of the most common data interchange formats you'll encounter in real work.

Why naive comma-splitting breaks

It's tempting to think you could just read a CSV file and split each line on commas yourself. This breaks the moment a field's actual content contains a comma, a quote character, or a newline — all of which are legitimate inside a properly quoted CSV field, but would completely confuse a naive line.split(",") approach:

"Smith, John","Software Engineer, Senior","Loves ""quotes"" in his bio"

That single row has three fields, but naive comma-splitting would see six pieces, none of them correct. Python's csv module handles this quoting and escaping correctly and automatically, which is precisely why it exists rather than everyone just splitting on commas manually.

What this article covers

The core csv.reader/csv.writer tools, the more convenient DictReader/DictWriter variants, real-world quirks like custom delimiters, and — honestly — when you should reach for pandas instead of the built-in module entirely.

Reading CSV Files: csv.reader vs. csv.DictReader

csv.reader: raw but fast
import csv

with open("data.csv", newline="", encoding="utf-8") as f:
    reader = csv.reader(f)
    for row in reader:
        print(row)
# ['name', 'age', 'city']
# ['Alex', '30', 'Nagpur']
# ['Sam', '25', 'Austin']

csv.reader gives you each row as a plain list of strings, accessed by position — row[0], row[1], and so on. This is the simpler, lower-overhead option, but it means you need to remember which column index corresponds to which piece of data, and that mapping breaks silently if the column order ever changes.

csv.DictReader: more convenient
import csv

with open("data.csv", newline="", encoding="utf-8") as f:
    reader = csv.DictReader(f)
    for row in reader:
        print(row)
# {'name': 'Alex', 'age': '30', 'city': 'Nagpur'}
# {'name': 'Sam', 'age': '25', 'city': 'Austin'}

DictReader treats the first row of the file as column headers automatically, and gives you each subsequent row as a dictionary, keyed by those column names — row["name"], row["age"], rather than a positional index. This is genuinely more convenient and more robust for most real work: your code reads clearly by field name, and it doesn't silently break if columns get reordered in a future version of the file (though it would still break if a column were renamed or removed entirely).

The critical gotcha: newline=''

This is worth committing to memory: always open CSV files with newline="", exactly as shown in both examples above.

with open("data.csv", newline="", encoding="utf-8") as f:
    reader = csv.reader(f)

Without newline="", Python's own universal newline translation can interfere with the csv module's own internal handling of newlines embedded inside quoted fields — particularly on Windows, where this can cause genuinely broken row parsing, with extra blank rows appearing or rows being split incorrectly. The csv module's documentation explicitly recommends this, and it's one of those details that's easy to skip and only surfaces as a confusing bug much later, often only on a different operating system than the one you originally tested on.

A reminder: everything comes back as strings

Just like input(), covered in the earlier input/print article, every value read from a CSV file — through either reader or DictReader — comes back as a plain string, even values that look numeric:

row = {"name": "Alex", "age": "30"}
print(type(row["age"]))   # <class 'str'>, not int

age = int(row["age"])   # explicit conversion required, exactly as covered in the type conversion article

You need to explicitly convert any numeric or otherwise non-string fields yourself — the csv module makes no assumptions about your data's actual types.

Writing CSV Files: csv.writer and csv.DictWriter

csv.writer: plain lists, row by row
import csv

with open("output.csv", "w", newline="", encoding="utf-8") as f:
    writer = csv.writer(f)
    writer.writerow(["name", "age", "city"])
    writer.writerow(["Alex", 30, "Nagpur"])
    writer.writerow(["Sam", 25, "Austin"])

.writerow() writes a single row at a time, from a plain list. .writerows() writes several rows at once, from a list of lists:

with open("output.csv", "w", newline="", encoding="utf-8") as f:
    writer = csv.writer(f)
    writer.writerow(["name", "age", "city"])
    writer.writerows([
        ["Alex", 30, "Nagpur"],
        ["Sam", 25, "Austin"],
    ])
csv.DictWriter: writing dictionaries
import csv

with open("output.csv", "w", newline="", encoding="utf-8") as f:
    fieldnames = ["name", "age", "city"]
    writer = csv.DictWriter(f, fieldnames=fieldnames)
    writer.writeheader()   # writes the header row, using fieldnames
    writer.writerow({"name": "Alex", "age": 30, "city": "Nagpur"})
    writer.writerow({"name": "Sam", "age": 25, "city": "Austin"})

Two details worth calling out explicitly: fieldnames is required, not optional — unlike DictReader, which infers column names automatically from the file's first row, DictWriter has no file to infer them from yet, since it's the one creating the file. You must explicitly tell it the column names and their order. And .writeheader() must be called explicitly — DictWriter won't automatically write a header row for you; you need to call it yourself, typically as the very first write operation.

A practical example: filter and rewrite

Combining DictReader and DictWriter is a genuinely common real-world pattern — reading an existing CSV, filtering or transforming its rows, and writing a new, filtered CSV:

import csv

with open("employees.csv", newline="", encoding="utf-8") as infile:
    reader = csv.DictReader(infile)
    engineering_rows = [row for row in reader if row["department"] == "Engineering"]

with open("engineering_only.csv", "w", newline="", encoding="utf-8") as outfile:
    writer = csv.DictWriter(outfile, fieldnames=["name", "department", "salary"])
    writer.writeheader()
    writer.writerows(engineering_rows)

Handling Real-World CSV Quirks

Custom delimiters and quote characters

Not every "CSV" file actually uses commas — semicolon-separated files are common in some locales (particularly where commas are used as decimal separators), and pipe-separated files show up in various data exports too. The delimiter and quotechar parameters handle this:

with open("data.csv", newline="", encoding="utf-8") as f:
    reader = csv.reader(f, delimiter=";", quotechar='"')
    for row in reader:
        print(row)
with open("data.psv", newline="", encoding="utf-8") as f:
    reader = csv.reader(f, delimiter="|")
    for row in reader:
        print(row)
Modifying data mid-pipeline

Combining DictReader and DictWriter also makes it straightforward to transform data as it flows through — adding a computed column, dropping a column entirely, or converting values along the way:

import csv

with open("employees.csv", newline="", encoding="utf-8") as infile:
    reader = csv.DictReader(infile)
    rows = []
    for row in reader:
        row["annual_salary"] = int(row["monthly_salary"]) * 12   # adding a computed column
        del row["monthly_salary"]                                  # dropping the original
        rows.append(row)

with open("employees_annual.csv", "w", newline="", encoding="utf-8") as outfile:
    writer = csv.DictWriter(outfile, fieldnames=["name", "department", "annual_salary"])
    writer.writeheader()
    writer.writerows(rows)
Memory-conscious processing for large files

Exactly the same lazy, memory-efficient principle covered in the earlier text files article applies to CSVs: for a genuinely large file, avoid building a full list of every row in memory at once (as the examples above do for clarity) — instead, process and write each row as you go, using a generator function with yield:

import csv

def filter_rows(input_path, condition):
    with open(input_path, newline="", encoding="utf-8") as f:
        reader = csv.DictReader(f)
        for row in reader:
            if condition(row):
                yield row

with open("output.csv", "w", newline="", encoding="utf-8") as outfile:
    writer = csv.DictWriter(outfile, fieldnames=["name", "department", "salary"])
    writer.writeheader()
    for row in filter_rows("employees.csv", lambda r: r["department"] == "Engineering"):
        writer.writerow(row)

This processes one row at a time throughout the entire pipeline — reading, filtering, and writing — without ever holding the full dataset in memory simultaneously, exactly the same lazy-evaluation benefit generators provide anywhere else in Python.

When to Reach for pandas Instead

The built-in csv module's strengths

No external dependencies, fast, and genuinely memory-efficient when you process row by row rather than loading everything at once, as shown above. It's the right tool for straightforward scripts, automation tasks, and everyday CSV processing where you don't need heavier data-analysis capabilities.

Where pandas fits better

pandas loads an entire CSV into memory as a DataFrame and provides a considerably richer data-analysis API on top:

import pandas as pd

df = pd.read_csv("employees.csv")
engineering_df = df[df["department"] == "Engineering"]
engineering_df.to_csv("engineering_only.csv", index=False)

print(df["salary"].mean())     # aggregation
print(df.groupby("department")["salary"].sum())   # grouping

For genuinely heavier data work — aggregation, grouping, statistical summaries, joining multiple datasets together, complex filtering across many columns — pandas' API is considerably more expressive and convenient than hand-rolling the equivalent logic with the plain csv module.

Quick guidance: match the tool to the task

The practical rule of thumb: don't reach for pandas reflexively for every single CSV touch, especially for simple scripts — it's a genuinely heavier dependency, and loading an entire large file into memory as a DataFrame is exactly the kind of thing the memory-conscious, row-by-row csv module patterns from Section 4 are specifically designed to avoid. Use the built-in csv module for straightforward reading, writing, and filtering, particularly in lightweight scripts or when processing files too large to comfortably fit in memory. Reach for pandas once you're doing genuine data analysis — aggregation, statistics, joining multiple sources — where its richer API meaningfully outweighs the cost of the extra dependency and the memory it uses to hold everything at once.

PREVIOUSNEXT LESSON