cassionData Analysis

Back to the lessonLesson 3 of 8Getting the export in

CSV, Excel and fixed-width, read without damage

The same deck as the downloads, rendered as a page. Start the slideshow to present it full screen — arrow keys or a click advance one slide, Escape leaves.

Slides · PDFSlides · PowerPoint

  1. Slide 1 / 29

    What this lesson covers

    • What this lesson assumes
    • CSV is not one format
    • Excel
    • Fixed-width files
    • Verify the read before doing anything with it
    • Reading a large file
    • What comes next
    Speaker notes
    Encodings, separators, multi-sheet workbooks, merged header rows and column specifications — the format-specific traps that corrupt a file before you have looked at a single value.

  2. Slide 2 / 29

    What this lesson assumes

    • Data Analysis Foundations covers the principle: declare your types, declare your sentinels, and never let a reader infer either.
    Speaker notes
    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.

  3. Slide 3 / 29

    CSV is not one format

    • The separator
    Speaker notes
    read_csv has around fifty parameters. Four of them account for most of the damage. A file produced on a French or Spanish Windows machine is frequently semicolon-separated, because the comma is the decimal mark there.

  4. Slide 4 / 29

    CSV is not one format — In Python

    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)

  5. Slide 5 / 29

    CSV is not one format

    • The encoding
    Speaker notes
    The symptom is unmistakable once you know it: a DataFrame with exactly one column. Check .shape immediately after every read. Accented characters in commune names — Gonaïves, L'Estère, Anse-Rouge — are where this bites.

  6. Slide 6 / 29

    CSV is not one format — In Python

    # 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

  7. Slide 7 / 29

    CSV is not one format

    • Thousands separators turn numbers into text
    Speaker notes
    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.

  8. Slide 8 / 29

    CSV is not one format — In Python

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

  9. Slide 9 / 29

    CSV is not one format

    • Leading zeros
    Speaker notes
    Without thousands, that column is object dtype and every arithmetic operation on it either fails or concatenates. Check .dtypes after reading, every time. The single most expensive default in this sector.

  10. Slide 10 / 29

    CSV is not one format — In Python

    # 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"})

  11. Slide 11 / 29

    CSV is not one format — In Python

    # 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())
    Speaker notes
    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: Two hundred rows is enough to see the shape and cheap on a large file.

  12. Slide 12 / 29

    Excel

    • There is more than one sheet

  13. Slide 13 / 29

    Excel — In Python

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

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

  14. Slide 14 / 29

    Excel — In Python

    sheets = pd.read_excel(path, sheet_name=None)
monthly = pd.concat(sheets, names=["sheet"]).reset_index(level="sheet")
    Speaker notes
    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:

  15. Slide 15 / 29

    Excel

    • The header is not row 1
    Speaker notes
    Programme workbooks routinely carry a title, a logo row and a blank line above the real header.

  16. Slide 16 / 29

    Excel — In Python

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

  17. Slide 17 / 29

    Excel

    • Merged cells produce missing values
    Speaker notes
    The symptom is column names like Unnamed: 0, Unnamed: 1. If you see those, the header row is somewhere else. 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.

  18. Slide 18 / 29

    Excel — In Python

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

  19. Slide 19 / 29

    Excel

    • Excel dates
    Speaker notes
    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 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.

  20. Slide 20 / 29

    Excel — In Python

    # 45306 is 2024-01-15
data["screening_date"] = pd.to_datetime(
    data["screening_date"], unit="D", origin="1899-12-30"
)
    Speaker notes
    If a date column arrives as five-digit integers, this is why.

  21. Slide 21 / 29

    Fixed-width files — Example

    CH00854Terre-Neuve    2024-01-15022m133false
CH01207Gonaives       2024-01-15034f118false
    Speaker notes
    Still common from older health information systems and from ministry extracts. There is no separator; each field occupies a fixed range of characters.

  22. Slide 22 / 29

    Fixed-width files — In Python

    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()

  23. Slide 23 / 29

    Fixed-width files

    • Ranges are half-open, like Python slices: (0, 7) is characters 0 to 6. Off-by-one here shifts every subsequent…
    • Strip the padding. Fixed-width fields are space-padded, so "Terre-Neuve " will not compare equal to…
    • Get the spec from the documentation, not by counting on screen. read_fwf can infer colspecs, and it infers them…
    Speaker notes
    Three notes:

  24. Slide 24 / 29

    Verify the read before doing anything with it — In Python

    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)
    Speaker notes
    Every read should be followed by the same four checks. Together they take one minute and catch nearly everything above.

  25. Slide 25 / 29

    Verify the read before doing anything with it

    • 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.
    Speaker notes
    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.

  26. Slide 26 / 29

    Reading a large file — In Python

    COLUMNS = ["student_id", "attendance_date", "present"]
attendance = pd.read_csv(path, usecols=COLUMNS, dtype={"student_id": "string"})
    Speaker notes
    If a file will not fit comfortably in memory, read the columns you need rather than all of them:

  27. Slide 27 / 29

    Reading a large file — In Python

    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()
    Speaker notes
    usecols is a large saving on a wide export. Beyond that, chunksize returns an iterator of pieces you aggregate as you go: The 70,000-row attendance file does not need this. A multi-year DHIS2 extract does.

  28. Slide 28 / 29

    What comes next

    • The file is in memory with its shape and types intact.
    Speaker notes
    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.

  29. Slide 29 / 29

    Where this goes next

    Read the full lesson, with runnable code Back to the lesson