CSV Automation: Merge & De-Dupe

By the end of this lesson you can:

• Read CSV rows as dictionaries with csv.DictReader.
• Write them back out with csv.DictWriter (and correct newline handling).
• Merge many files, even with slightly different column orders.
• De-duplicate rows by a chosen key column, keeping the first or last seen.

A common chore: every month a system spits out sales-jan.csv, sales-feb.csv, … and someone opens each in Excel, copies the rows, and pastes them into a master file — re-typing the header, missing duplicates, making mistakes.

Reading rows as dictionaries

import csv

with open("sales.csv", newline="", encoding="utf-8") as f:
    reader = csv.DictReader(f)
    for row in reader:
        print(row["name"], row["amount"])   # access by column name

• DictReader uses the first line as headers; each row becomes a dict like {"name": "Aisha", "amount": "120"}.
• Always open with newline="" — the csv module handles line endings itself, and skipping this causes blank lines on Windows.
• Values are always strings — convert with int()/float() when you need numbers.

Writing rows back

rows = [
    {"name": "Aisha", "amount": "120"},
    {"name": "Ben",   "amount": "85"},
]

with open("out.csv", "w", newline="", encoding="utf-8") as f:
    writer = csv.DictWriter(f, fieldnames=["name", "amount"])
    writer.writeheader()           # write the column titles
    writer.writerows(rows)         # write all the rows at once

DictWriter needs fieldnames (the columns, in the order you want them). writeheader() writes the title row; writerow/writerows write data.

Merging files (order-independent)

from pathlib import Path
import csv

def merge(folder: str, out: str) -> int:
    all_rows = []
    fields = None
    for path in sorted(Path(folder).glob("*.csv")):
        with open(path, newline="", encoding="utf-8") as f:
            reader = csv.DictReader(f)
            fields = fields or reader.fieldnames    # take columns from first file
            all_rows.extend(reader)

    with open(out, "w", newline="", encoding="utf-8") as f:
        writer = csv.DictWriter(f, fieldnames=fields)
        writer.writeheader()
        writer.writerows(all_rows)
    return len(all_rows)

Because rows are dicts, DictWriter places each value under the right column even if an input file had a different order. (If files have genuinely different columns, pass extrasaction="ignore" or compute the union of fieldnames.)

De-duplicating by a key column

def dedupe(rows: list[dict], key: str, keep="first") -> list[dict]:
    seen = {}
    for row in rows:
        k = row[key]
        if keep == "last" or k not in seen:
            seen[k] = row          # last write wins if keep="last"
    return list(seen.values())

A dict keyed by the chosen column collapses duplicates automatically — keep="first" ignores later repeats, keep="last" lets newer rows overwrite older ones. Insertion order is preserved (dicts remember order since Python 3.7).

Goal: merge every sales-*.csv, drop duplicate order IDs (keeping the latest), and report totals — a complete monthly close.

import csv, logging
from pathlib import Path

logging.basicConfig(level=logging.INFO, format="%(levelname)s %(message)s")
log = logging.getLogger("sales")

def consolidate(folder: str, out: str, key: str = "order_id") -> None:
    rows, fields = [], None
    files = sorted(Path(folder).glob("sales-*.csv"))
    for path in files:
        with open(path, newline="", encoding="utf-8") as f:
            reader = csv.DictReader(f)
            fields = fields or reader.fieldnames
            count = 0
            for row in reader:
                rows.append(row)
                count += 1
            log.info("%s: %d rows", path.name, count)

    # de-dupe by order_id, keep latest (files are date-sorted)
    by_id = {row[key]: row for row in rows}
    unique = list(by_id.values())
    removed = len(rows) - len(unique)

    with open(out, "w", newline="", encoding="utf-8") as f:
        writer = csv.DictWriter(f, fieldnames=fields)
        writer.writeheader()
        writer.writerows(unique)

    total = sum(float(r["amount"]) for r in unique)
    log.info("merged %d files → %d unique rows (%d dupes removed)",
             len(files), len(unique), removed)
    log.info("total sales: %.2f", total)

consolidate("exports", "master_sales.csv")

INFO sales-jan.csv: 412 rows
INFO sales-feb.csv: 388 rows
INFO sales-mar.csv: 401 rows
INFO merged 3 files → 1188 unique rows (13 dupes removed)
INFO total sales: 84210.50

Read the code

Everything from this level comes together: glob finds the files, DictReader reads them order-independently, a dict comprehension de-dupes by order_id, DictWriter emits the master, and logging narrates the whole run. The hour-long monthly copy-paste chore is now a five-second script that's also correct — no missed duplicates. Lesson 22 will wrap output like this into a polished report.

csv.DictReader reads each row as a dict keyed by column name (open with newline=""; values are strings), and csv.DictWriter writes them back given fieldnames + writeheader(). Because access is by name, merging files with different column orders "just works." De-duplicate by collapsing rows into a dict keyed on your chosen column — keep="first" or "last". This turns the monthly copy-paste-merge chore into a correct, repeatable script. For huge data or complex joins, reach for pandas in Level 8.