Fix a pandas KeyError caused by inconsistent CSV column names

A sales export opens successfully, but selecting the revenue column raises KeyError. The visible spreadsheet heading looks correct, so changing the calculation does not help.

Why this happens

Column lookup uses the actual label. A trailing space, different capitalisation or a byte-order mark can make two apparently identical headings different. A missing field can also mean the wrong export template was downloaded.

Step-by-step solution

1. Print repr(df.columns.tolist()) immediately after loading the CSV. Compare it with the exporter’s documented schema before renaming anything.

2. Read a UTF-8 export containing a byte-order mark with encoding='utf-8-sig'. Normalise surrounding whitespace only if that is part of your ingestion contract.

3. After normalisation, reject duplicate headings and explicitly validate required columns. Report the missing names instead of allowing a later calculation to fail.

Worked example

import pandas as pd
from io import StringIO
df = pd.read_csv(StringIO(' revenue ,channel\n1200,email\n'))
df.columns = df.columns.str.strip()
assert df.columns.is_unique
assert 'revenue' in df.columns
assert df['revenue'].sum() == 1200

Verify the fix

The example returns a revenue total of 1200. Also test an export without revenue: validation should stop with a clear schema error, rather than create an empty column.

Prevent a repeat

Keep a small, anonymised sample for each supported export version. Do not lowercase every field blindly if your source treats case-sensitive identifiers as distinct.

Reference

Official documentation or standards guidance

An original Gistmax troubleshooting guide. Scenarios and sample values are illustrative; configuration depends on your software version and environment. Cover diagram: Gistmax Tutorials.