CSV, Excel and fixed-width, read without damage — Python for Programme Data — Data Analysis

What this lesson assumes

Data Analysis Foundations covers the principle: declare your types, declare your sentinels, and never let a reader infer either. This lesson takes that as given and goes into the formats themselves, because each one fails differently and the failures are not obvious from the data afterwards.

CSV is not one format

read_csv has around fifty parameters. Four of them account for most of the damage.

The separator

A file produced on a French or Spanish Windows machine is frequently semicolon-separated, because the comma is the decimal mark there.

import pandas as pd

# Wrong: everything lands in one column named after the whole header line.
wrong = pd.read_csv("export.csv")
print(wrong.shape)              # (2736, 1)

right = pd.read_csv("export.csv", sep=";", decimal=",")
print(right.shape)              # (2736, 7)

The symptom is unmistakable once you know it: a DataFrame with exactly one column. Check .shape immediately after every read.

The encoding

Accented characters in commune names — Gonaïves, L’Estère, Anse-Rouge — are where this bites.

# UnicodeDecodeError, or worse, silently mangled: "Gona\xefves"
muac = pd.read_csv(path)

muac = pd.read_csv(path, encoding="utf-8")       # what you want
muac = pd.read_csv(path, encoding="latin-1")     # what Excel often produces

latin-1 never raises, because every byte is a valid latin-1 character. That makes it a poor diagnostic and a decent fallback: if a file read as latin-1 shows GonaÃ¯ves, it was UTF-8 all along and you told the reader otherwise.

Thousands separators turn numbers into text

# target_population arrives as "1,480" and pandas reads it as a string.
epi = pd.read_csv(path, thousands=",")

Without thousands, that column is object dtype and every arithmetic operation on it either fails or concatenates. Check .dtypes after reading, every time.

Leading zeros

The single most expensive default in this sector.

# facility_id "007" becomes the integer 7; the join to the facility list fails
# for exactly the facilities whose code had a leading zero.
epi = pd.read_csv(path)

epi = pd.read_csv(path, dtype={"facility_id": "string"})

The rule generalises: if you will never do arithmetic on it, it is not a number. Facility codes, phone numbers, cluster identifiers, household numbers and administrative codes are text that happens to be written with digits.

A defensive habit for a file whose columns you do not yet know:

# Read everything as text first, look, then convert deliberately.
peek = pd.read_csv(path, dtype="string", nrows=200)
print(peek.head())
print(peek.columns.tolist())

Two hundred rows is enough to see the shape and cheap on a large file.

Excel

There is more than one sheet

book = pd.ExcelFile(path)
print(book.sheet_names)
# ['Cover', 'Data', 'Codebook', 'Sheet3']

data = pd.read_excel(path, sheet_name="Data")

read_excel without sheet_name returns the first sheet, which in a programme workbook is usually a cover page. Always list the sheets first.

sheet_name=None returns a dictionary of every sheet, which is how you handle a workbook with one sheet per month:

sheets = pd.read_excel(path, sheet_name=None)
monthly = pd.concat(sheets, names=["sheet"]).reset_index(level="sheet")

The header is not row 1

Programme workbooks routinely carry a title, a logo row and a blank line above the real header.

data = pd.read_excel(path, sheet_name="Data", skiprows=3)

The symptom is column names like Unnamed: 0, Unnamed: 1. If you see those, the header row is somewhere else.

Merged cells produce missing values

A merged cell holds its value in the top-left position and nothing in the rest. When a district name is merged across its facilities, only the first row of each district has one.

data["district"] = data["district"].ffill()

ffill is correct here and dangerous in general: it is only right because the blanks were created by merging, not by non-response. Never apply it to a column where a blank might mean “not answered”.

Excel dates

Excel stores dates as a number of days since 1899-12-30. read_excel usually converts them, but a column typed as text in the workbook comes through as the raw serial.

# 45306 is 2024-01-15
data["screening_date"] = pd.to_datetime(
    data["screening_date"], unit="D", origin="1899-12-30"
)

If a date column arrives as five-digit integers, this is why.

Fixed-width files

Still common from older health information systems and from ministry extracts. There is no separator; each field occupies a fixed range of characters.

CH00854Terre-Neuve    2024-01-15022m133false
CH01207Gonaives       2024-01-15034f118false

COLSPECS = [(0, 7), (7, 22), (22, 32), (32, 35), (35, 36), (36, 39), (39, 44)]
NAMES = ["child_id", "commune", "screening_date", "age_months", "sex",
         "muac_mm", "oedema"]

muac = pd.read_fwf(
    path,
    colspecs=COLSPECS,
    names=NAMES,
    dtype={"child_id": "string", "commune": "string"},
)

muac["commune"] = muac["commune"].str.strip()

Three notes:

Ranges are half-open, like Python slices: (0, 7) is characters 0 to 6. Off-by-one here shifts every subsequent field by one character and produces a file that looks almost right.
Strip the padding. Fixed-width fields are space-padded, so "Terre-Neuve " will not compare equal to "Terre-Neuve".
Get the spec from the documentation, not by counting on screen. read_fwf can infer colspecs, and it infers them from whitespace — which fails the moment a field is full-width or a value contains a space.

Verify the read before doing anything with it

Every read should be followed by the same four checks. Together they take one minute and catch nearly everything above.

def check(df, expected_rows=None):
    print("shape     ", df.shape)
    print("dtypes    ", df.dtypes.value_counts().to_dict())
    print("missing   ", df.isna().sum().to_dict())
    print("first row ", df.iloc[0].to_dict())
    if expected_rows is not None:
        assert len(df) == expected_rows, f"expected {expected_rows}, got {len(df)}"

muac = pd.read_csv(
    RAW / "muac-screening-artibonite-2024.v1.csv",
    dtype={"child_id": "string", "commune": "string", "sex": "string"},
    na_values={"muac_mm": ["-99"]},
)
check(muac, expected_rows=4218)

shape catches the wrong separator and a truncated download.
dtypes catches leading zeros lost, thousands separators, and a numeric column read as text.
missing catches a wrong encoding and a sentinel not declared.
first row catches a header offset — if the first row looks like a header, it was one.

The assertion on row count is the one people skip. A file that arrives with 4,190 rows instead of 4,218 has lost something between the server and you, and the script should say so rather than quietly report a smaller caseload.

Reading a large file

If a file will not fit comfortably in memory, read the columns you need rather than all of them:

COLUMNS = ["student_id", "attendance_date", "present"]
attendance = pd.read_csv(path, usecols=COLUMNS, dtype={"student_id": "string"})

usecols is a large saving on a wide export. Beyond that, chunksize returns an iterator of pieces you aggregate as you go:

totals = []
for chunk in pd.read_csv(path, chunksize=100_000, dtype={"student_id": "string"}):
    totals.append(chunk.groupby("student_id")["present"].sum())

per_student = pd.concat(totals).groupby(level=0).sum()

The 70,000-row attendance file does not need this. A multi-year DHIS2 extract does.

What comes next

The file is in memory with its shape and types intact. The next lesson deals with what is written inside the columns: sentinel codes, categorical vocabularies, boolean values recorded five different ways, and the value labels a Stata file carries that a CSV throws away.