Back to Literature

The Taming of the CSV: A Guide to Robust Data Ingestion

2025-11-24
by Andri Ólafsson
Data EngineeringPythonPandasBest PracticesETL

The Taming of the CSV: A Guide to Robust Data Ingestion

The Unglamorous Reality of Data Science

In the world of data science, we love to talk about the sexy stuff: deep learning, Large Language Models, and dazzling interactive dashboards. But if you shadow any working professional—from a junior analyst to a senior engineer—you’ll quickly learn the industry's open secret: 80% of our time is spent cleaning data.

It’s not glamorous. It doesn't look cool in a movie montage. But it is the absolute foundation of trustworthy insights.

At Blóðskimun til Bjargar, where I work with large-scale medical datasets, precision isn’t optional. A misplaced decimal, a mangled character, or a shifted column isn’t just a "bug"—it’s a threat to research integrity. Over the years, moving from mechatronics to data engineering, I’ve built a "Survival Guide" for ingesting data from the wild.

Here is how to tame the beast.

1. The Ingestion: Know Your Enemy (The CSV)

The CSV (Comma Separated Value) file is the lingua franca of data exchange. It is simple, human-readable, and universal. It is also deceptively dangerous.

A CSV has no schema, no type enforcement, and no standard encoding. Before you even think about running pd.read_csv(), run through this checklist.

The Encoding Trap

Living in Iceland, I have spent more hours than I care to admit debugging text files. Standard ASCII parsers choke on our alphabet. If you blindly ingest a file containing Þ, Æ, or Ö using standard settings, you will often end up with mojibake (garbled text) or a hard crash.

The Fix: Never assume the encoding.

  1. Start with UTF-8: encoding='utf-8' is the modern standard.
  2. The Windows Fallback: If that fails (common with older Excel exports), try encoding='latin1' or encoding='cp1252'.
  3. The Excel Export: If you are creating the CSV for Excel users, use encoding='utf-8-sig'. This adds a Byte Order Mark (BOM) to the file, which tells Excel, "Hey, this is UTF-8, please respect the special characters."

The Separator Confusion

The name "Comma Separated Values" is a lie. In many European countries (Iceland included), the comma , is used as the decimal separator (e.g., 1.500,00). Therefore, computers cannot use the comma to separate columns.

The Fix: Always explicitly define your separator. Never trust the default.

import pandas as pd

# ❌ The Roulette Method: Hoping for the best
# df = pd.read_csv('data.csv') 

# ✅ The Robust Method: Explicit definitions
try:
    df = pd.read_csv(
        'data.csv', 
        sep=';',              # Explicitly defined for European formats
        encoding='utf-8',     # Try standard first
        dtype={'id': str}     # Prevent leading zero loss (see section 4)
    )
except UnicodeDecodeError:
    # Fallback for legacy Windows files
    df = pd.read_csv('data.csv', sep=';', encoding='cp1252')
  1. The Wrangling: Defensive Coding Once the data is inside a Pandas DataFrame, your job is to distrust it. Pandas tries to be helpful by guessing data types, but its guesses are often wrong.

Schema Enforcement Dates are notoriously inconsistent. Is 02/01/2023 February 1st (US) or January 2nd (Europe)? If you let Pandas guess, it might guess differently for row 1 than for row 100.

The Strategy: Force the format and flag the failures.

# Coerce errors turns unparseable dates into NaT (Not a Time)
df['measurement_date'] = pd.to_datetime(
    df['measurement_date'], 
    format='%d/%m/%Y', 
    errors='coerce'
)

# Check what failed
failed_rows = df[df['measurement_date'].isna()]
if not failed_rows.empty:
    print(f"Warning: {len(failed_rows)} dates could not be parsed.")

Strings vs Objects By default, Pandas loads text columns as the generic object type (which can hold anything: strings, integers, lists). This leads to mixed-type chaos where a column contains both the number 1 and the string "1".

The Strategy: Convert text columns to the dedicated string type immediately.

df['patient_notes'] = df['patient_notes'].astype('string')
  1. Cleaning Strategies Whitespace is the Enemy To a human, "Reykjavik" and "Reykjavik " (with a trailing space) look identical. To a computer trying to join two tables, they are completely different keys. This is the number one cause of failed SQL joins.

The Fix: Don't spot-check. Systematically strip whitespace from all string columns.

# Apply strip() to all columns formatted as strings
df[df.select_dtypes(['object', 'string']).columns] = \
    df.select_dtypes(['object', 'string']).apply(lambda x: x.str.strip())

Null Handling: To Drop or To Impute? In general data science tutorials, you often see people filling missing values with the mean (df.fillna(df.mean())).

In medical contexts, this is dangerous. If a patient's blood pressure is missing, making up a generic "average" number can falsify a diagnosis.

The Strategy:

Flag it: Create a boolean mask column. The fact that data is missing is often a data point in itself (e.g., maybe the patient was too sick to be measured).

Keep it Null: Let the analysis stage decide how to handle the gaps.

# Create a flag before handling the null
df['is_bp_missing'] = df['blood_pressure'].isna()
  1. The Golden Rule: Do Not Mutate the Source There is one rule I insist on above all others: Never open the raw CSV in Excel, save it, and then run your script.

Excel is an aggressive auto-formatter. It is famous for:

Converting gene names (like MARCH1) into dates.

Stripping leading zeros from ID numbers.

Example: An Icelandic Kennitala is 10 digits. If a child is born in the year 2000+, their ID might start with a zero. Excel sees this as a number and strips the "useless" zero, invalidating the ID.

The Workflow:

Raw Data: (Read Only)

Python Script: (Transforms data in memory)

Cleaned Data: (Saved as Parquet or SQL)

Why Parquet? Once I have cleaned the CSV, I save it as a .parquet file.

Schema Preservation: It remembers that 'ID' is a string and 'Date' is a timestamp.

Speed: It loads ~10x faster than CSV.

Size: It is highly compressed.

  1. Validation: Trust but Verify You have ingested, wrangled, and cleaned. But is the data right? Before pushing data downstream to a database or dashboard, run assertions.

These checks act as "unit tests" for your data.

# 1. Check for uniqueness
assert df['transaction_id'].is_unique, "Critical Error: Duplicate IDs found!"

# 2. logical bounds checks
assert df['age'].between(0, 120).all(), "Error: Age values outside realistic bounds."

# 3. Temporal consistency
import datetime
assert df['created_at'].max() <= datetime.datetime.now(), "Error: Data from the future detected."

If any of these trigger, the pipeline stops immediately. It is better to have a broken dashboard than a dashboard showing lies.

Conclusion: Pipelines that Scream We cannot fix every messy dataset by hand—that’s a losing battle. But we can build pipelines that scream loudly when something looks wrong.

Defensive coding, schema enforcement, and strict validation aren’t the "fun" parts of data science. But they are the difference between a prototype that "works on my machine" and a production system that saves lives.

Have you ever had a dataset break your pipeline in a spectacular way? A rogue semicolon or a corrupted encoding? Share your horror stories below.