Removing Blank Rows and Stray Footer Lines From a CSV

Exports that come from reports, not databases, carry decoration: a title, a timestamp, a blank line, a header repeated at every page break, a total at the end, a line that says how many rows there were. A human reads past it. A parser reads it as data, and the damage is quiet: a row of Total counted as a customer, a blank row that becomes a null record, a title that turns the header into garbage. This article cleans one such file line by line, shows why the obvious dropna removes nothing, and uses the footer for something useful, as a checksum on the result. It is part of the cleaning series. The skiprows and skip_blank_lines options are in the pandas read_csv reference, and skip is in the DuckDB CSV overview.

What the file looks like

import sys, io, re
sys.path.insert(0, "/batches/csv-0001-0200/lab/clean")
import pandas as pd
from decimal import Decimal
from clean import parse_money

path = "/batches/csv-0001-0200/lab/data/clean/report-export.csv"
print(open(path, encoding="utf-8").read())
try:
    print(pd.read_csv(path))
except Exception as e:
    print("pandas default:", type(e).__name__, str(e)[:90])

# Find the header row instead of guessing how many lines to skip
lines = open(path, encoding="utf-8").read().splitlines()
want = ["id", "name", "amount"]
hdr = next(i for i, l in enumerate(lines) if [c.strip().lower() for c in l.split(",")] == want)
print("header found on line", hdr + 1)
df = pd.read_csv(path, skiprows=hdr, dtype=str, keep_default_na=False)
print(f"read {len(df)} rows; dropna(how='all') would drop {len(df.dropna(how='all')) - len(df)} of them")

blank = df.apply(lambda c: c.str.strip()).eq("").all(axis=1)
repeat = df["id"].str.strip().str.lower().eq("id")
footer = df["id"].str.strip().str.match(r"(?i)^(total\b|\(\d+ rows?\))")
print(f"blank rows {int(blank.sum())}, repeated headers {int(repeat.sum())}, footer rows {int(footer.sum())}")
clean = df[~(blank | repeat | footer)].reset_index(drop=True)
print(clean.to_string(index=False))

# The footer is the provider's own claim: check it
total_claim = Decimal(df.loc[df["id"].str.strip().str.lower().eq("total"), "amount"].iloc[0])
actual = sum(Decimal(v) for v in clean["amount"])
rows_claim = int(re.search(r"\((\d+) rows?\)", " ".join(df["id"])).group(1))
print(f"footer says total {total_claim}, rows below it add up to {actual}; footer says {rows_claim} rows, found {len(clean)}")

python /batches/csv-0001-0200/lab/clean/t0119.py

Customer export
Generated 2025-03-21 08:00 UTC

id,name,amount
1,Ana,10.00
2,Bo,20.00
,,
3,Cy,30.00
id,name,amount
4,Di,40.00
   ,   ,
5,Ed,50.00
Total,,150.00
(5 rows)

pandas default: ParserError Error tokenizing data. C error: Expected 1 fields in line 4, saw 3

header found on line 4
read 10 rows; dropna(how='all') would drop 0 of them
blank rows 2, repeated headers 1, footer rows 2
id name amount
 1  Ana  10.00
 2   Bo  20.00
 3   Cy  30.00
 4   Di  40.00
 5   Ed  50.00
footer says total 150.00, rows below it add up to 150.00; footer says 5 rows, found 5

The top of the output is the raw file. Lines 1 to 3 are a title, a timestamp and a blank line. Line 4 is the real header. Data follows, with a row of commas (,,) after Bo, a repeated header (id,name,amount) in the middle where a page break was, a row of padded spaces ( , ,), then Total,,150.00 and (5 rows). Five records are data; nine other lines are not.

What the default read does

pd.read_csv(path) took the first line, Customer export, as the header, which has one field, and failed on line 4: ParserError: Error tokenizing data. C error: Expected 1 fields in line 4, saw 3. That at least is loud. Had the title line contained two commas, the read would have succeeded with garbage column names and every real header and row below it as data. The cure is to find the header yourself instead of guessing how many lines to skip:

hdr = next(i for i, l in enumerate(lines) if [c.strip().lower() for c in l.split(",")] == want)

Scan for the first line whose cells equal the column names you expect. Here that is line 4, and skiprows=hdr does the rest. If the header is not found, stop with an error. A file that no longer contains the columns you need is a file you should not process, and it is more useful as an exception than as an empty table.

Why dropna removes nothing

After the read (dtype=str, keep_default_na=False, so nothing is converted and blank cells stay as the empty string) the frame had 10 rows, and df.dropna(how="all") dropped 0 of them. That is the trap. dropna looks for missing values, NaN or None, and an empty cell read as text is "", which is a value. The blank rows were not missing; they were rows of empty strings. A mask that says what you mean works:

blank = df.apply(lambda c: c.str.strip()).eq("").all(axis=1)

It strips each cell and tests whether the whole row is empty. That also catches , , (spaces), which a test against "" alone would miss. The output line reads blank rows 2, repeated headers 1, footer rows 2.

The csv module shows that "blank" is really two different things:

bash /batches/csv-0001-0200/lab/clean/t0119b.sh

--- csv module: a row of only commas/whitespace is a record, not an empty line
line 3: [] -> empty list
line 7: ['', '', ''] -> all fields blank
line 11: ['   ', '   ', ''] -> all fields blank
--- DuckDB
┌───────┬──────┬────────┐
│  id   │ name │ amount │
├───────┼──────┼────────┤
│ 1     │ Ana  │ 10.00  │
│ 2     │ Bo   │ 20.00  │
│ NULL  │ NULL │ NULL   │
│ 3     │ Cy   │ 30.00  │
│ id    │ name │ amount │
│ 4     │ Di   │ 40.00  │
│       │      │ NULL   │
│ 5     │ Ed   │ 50.00  │
│ Total │ NULL │ 150.00 │
└───────┴──────┴────────┘

Line 3, a truly empty line, comes back as [] (an empty list, no fields). Line 7, ,,, comes back as ['', '', ''] (three empty fields), and line 11 as [' ', ' ', '']. A loop that skips if not row removes the first kind only. The rule that covers both is "every field is empty after stripping".

Repeated headers and footers

A repeated header is a row whose first cell equals the first column name: df["id"].str.strip().str.lower().eq("id"). They appear when a report is paginated or when several exports were concatenated, which is also how a footer ends up in the middle of the file. The footers are matched with a pattern: ^(total\b|\(\d+ rows?\)) caught Total and (5 rows). Report writers vary, so list the footers you have actually seen and make the pattern strict. A loose pattern such as "first cell is not a number" would delete rows whose id is legitimately text.

DuckDB can read past the preamble with skip, and treats rows it cannot parse as errors you can ignore:

SELECT * FROM read_csv('report-export.csv', skip=3, header=true, all_varchar=true, ignore_errors=true)

With skip=3 the header was line 4 and the result was the nine data-ish rows, including the NULL row (,,), the repeated header and Total; the (5 rows) line was silently dropped by ignore_errors. My first try, skip=4, skipped the real header and made 1,Ana,10.00 the column names. Counting the skipped lines is as fragile in SQL as anywhere, which is why I prefer to find the header by name. For a large file the pattern is to find the header line with a few lines of Python and then hand skip to DuckDB for the bulk read.

The footer is a checksum

After removing the decoration, 5 rows remained: Ana, Bo, Cy, Di and Ed. The footer made two claims, and both can be checked:

footer says total 150.00, rows below it add up to 150.00; footer says 5 rows, found 5

The total in the file is 150.00 and the amounts of the five rows add up to 150.00. The row count in the file is 5 and five rows were kept. Those are the provider's own statements about what they exported, and agreeing with them is strong evidence that nothing was lost, duplicated or mis-parsed on the way. A mismatch is the most useful result a cleaning script can produce: it means a row went missing in transit, a page was cut off or an amount was misparsed. On the customer file used in the rest of the series, the same check tells a different story. Its Total row says $2,500.25, and the balances that parse above it add up to 3,799.25. I would not guess why; the point is that the file contradicts itself, and that is a finding for the provider, not something to clean up silently.

Do the reconciliation with exact decimals, as the currency article does, and report the difference, not just pass or fail.

Keep what you remove, when it matters

Titles and timestamps are metadata. Generated 2025-03-21 08:00 UTC tells you when the data was true, which you will want in the audit trail and nowhere in the table. I save the dropped lines to a sidecar file with their line numbers (report-export.dropped.txt) and record the as-of timestamp in the run log. When somebody asks which version of the export this was, the answer exists.

How to tell it worked

Count: total lines minus dropped lines equals the data rows. Confirm that the footer's total and count match, and fail loudly if they do not. Check that no row has total or id in its first cell. Look at the first and last data rows against the raw file. And run the cleaner on an export that has no decoration at all; it should change nothing, which tests that your masks are not too greedy.

When a footer is information

Sometimes the footer is the only place a fact lives: the as-of date, the grand total that you reconcile to, a count of suppressed rows, a note about a methodology change. Treat it as data about the file and store it separately. When the decoration varies by provider or by month, do not write one clever pattern; write one explicit rule per source and fail when a line matches none of them. The unknown line is the one that tells you the export format changed. The next step in the series, fixing numbers with thousands separators, deals with a problem that is hidden in the data rows themselves.