On Loading and Preprocessing Very Messy Tabular Data

In 2016 a group of researchers did something petty and brilliant. They downloaded the supplementary Excel files from thousands of genomics papers and just checked them. About one in five had gene names silently converted into dates. SEPT2 became September 2nd. MARCH1 became March 1st. Nobody typed those dates. A spreadsheet guessed a type, guessed wrong, and no one noticed before publication.

The punchline is that the gene naming committee eventually gave up and renamed the genes. SEPT2 is now SEPTIN2. Humanity changed biology's vocabulary because a data loader was too clever. A follow-up study in 2021 found the error rate had gone up.

I think about that story a lot, because it is the perfect illustration of something we all pretend is not true: the loader is not the boring part before the real work. The loader is where your analysis silently becomes wrong. So this post is a tour of the programming ideas that make tabular data behave, roughly in the order I wish someone had taught them to me.

---

1. Type inference is a guess, so stop letting it happen

When you call pd.read_csv("data.csv"), something quietly dramatic happens. The parser peeks at your file, looks at a sample of each column, and guesses what type it is. That guess is why SEPT2 becomes a date. It is why your sample IDs like 007 come back as the integer 7. It is why a column of concentrations turns into a column of strings because someone wrote "ND" in row 4000 for "not detected."

This is called schema on read: the structure of the data is decided at load time by whatever is reading it. The alternative is schema on write, where the format itself carries the types and there is nothing to guess. Parquet does this. Databases do this. CSV, being a text file with commas in it, does not and cannot.

You cannot make CSV carry types. But you can refuse the guess:

stop guessing, start declaring

# the guess
df = pd.read_csv("samples.csv")           # sample_id -> int64. oops.

# the declaration
df = pd.read_csv(
    "samples.csv",
    dtype={"sample_id": "string", "gene": "string", "od600": "float64"},
    na_values=["ND", "NA", "n/a", ""],    # say what missing means
    keep_default_na=False,                 # nothing else counts as missing
)

It is four extra lines and it is the highest return on investment in this entire post. The general principle: make the implicit explicit at the boundary. Every ambiguity you leave unresolved at the door gets resolved by a heuristic somewhere, at a moment you are not watching.

---

2. Validate at the boundary, or debug forever

Declaring types is defence against the parser. The next idea is defence against reality: the file itself is often wrong. A pH of 74. A negative cell count. A duplicated sample ID. A well ID that does not match the plate layout.

The concept here is the data contract, borrowed from the "parse, don't validate" school of typed programming. Instead of scattering if checks through your analysis, you declare once, at the entry point, what valid data looks like. If the data does not match, you fail immediately and loudly, right where the problem is, instead of producing a beautiful wrong figure three hours later.

a contract with pandera

import pandera as pa
from pandera.typing import Series

class SampleSchema(pa.DataFrameModel):
    sample_id: Series[str]   = pa.Field(unique=True)
    od600:     Series[float] = pa.Field(ge=0, le=3.0)
    ph:        Series[float] = pa.Field(ge=0, le=14)
    treatment: Series[str]   = pa.Field(isin=["control", "low_do", "high_do"])

df = SampleSchema.validate(pd.read_csv("samples.csv"))
# if this line passes, every downstream function can stop being paranoid

The thing I love about this is not the validation. It is that the schema is documentation that cannot go stale. Six months later, that class tells you exactly what the file is supposed to contain, and unlike a comment or a README, it fails if it starts lying.

A bug found at load time costs you a minute. The same bug found in a figure costs you a week, and the same bug not found at all costs you a correction notice.

---

3. Tidy data: get the shape right and everything gets easier

Now the data is loaded and trustworthy. The next question is what shape it should be in, and this is where a genuinely great idea lives.

Hadley Wickham's tidy data is three rules: each variable is a column, each observation is a row, each type of observational unit is a table. That is it. It sounds like a triviality until you notice it is really a restatement of database normalization for people who do not want to hear the phrase "third normal form."

Here is why it matters. Wide data is how instruments and humans produce tables:

wide: readable, but a trap

sample   t0     t6     t12    t24
A1       0.05   0.31   0.88   1.42
A2       0.04   0.29   0.91   1.51

The problem is that t0, t6, t12 are not variables. They are values of a variable called time, hiding in the column headers. Every time you want to plot, group, or model, you have to write code that knows those names. Add a t48 and everything breaks.

long: ugly to read, wonderful to compute on

sample   time   od600
A1       0      0.05
A1       6      0.31
A1       12     0.88
...

# and now every tool just works, forever
df.groupby("time")["od600"].mean()
sns.lineplot(df, x="time", y="od600", hue="sample")

The rule of thumb I use: store long, present wide. Reshape to wide at the very last moment, for a table a human will read. Everything upstream stays long. Once your data is tidy, the entire ecosystem of tools was designed for it, and you stop writing glue.

Tidy data also unlocks its natural companion, split-apply-combine: split the data into groups, apply a function to each, combine the results. It is the same idea as MapReduce, scaled down to a laptop. groupby in pandas, group_by in dplyr and Polars, all the same concept. Recognising that "for each sample, fit a curve" is a split-apply-combine problem rather than a for loop is one of those small shifts that changes how you write everything.

---

4. Think in columns, not rows

Here is a concept that sounds like an implementation detail and is actually a mental model.

A CSV is row-oriented: all of row 1, then all of row 2. To read one column you must physically read every byte of the file. Columnar formats like Parquet store all of column 1 together, then all of column 2. This changes what is cheap.

The other half of this idea is Apache Arrow, which is the same columnar layout but in memory, and standardised across languages. The point of Arrow is zero copy: pandas, Polars, DuckDB, and R can hand each other the same block of memory without serialising and deserialising it. Historically, moving a table between two tools meant converting it twice. Arrow's ambition is that the conversion disappears entirely.

The practical version of all this: if a CSV is read more than once, it should not still be a CSV. Convert it to Parquet on first read and never look back. One line, permanent payoff.

the conversion that pays for itself immediately

pd.read_csv("huge_counts.csv").to_parquet("huge_counts.parquet")

# later, and much faster
pd.read_parquet("huge_counts.parquet", columns=["gene", "sample", "tpm"])

---

5. Describe the work, then let something smarter run it

This is my favourite idea in the whole post, because it inverts something you probably do without thinking.

Normal pandas code is eager: each line executes the moment it is written. Filter, and it builds a filtered copy. Select columns, and it builds another copy. The machine does exactly what you said, in the order you said it, even when that order is dumb.

Lazy evaluation separates describing the work from doing it. You build up a plan, and nothing happens until you ask for the result. Which sounds like a minor scheduling trick until you realise what it enables: because the engine can see the whole plan before executing any of it, it can rewrite it. This is a query optimiser, and it is the single best idea databases ever had, now available in your data frame library.

eager vs lazy

# eager: reads all 40 GB, then throws most of it away
df = pd.read_csv("counts.csv")
df = df[df["tpm"] > 10]
df = df[["gene", "tpm"]]

# lazy: nothing has happened yet
q = (pl.scan_parquet("counts.parquet")
       .filter(pl.col("tpm") > 10)
       .select(["gene", "tpm"]))

df = q.collect()   # NOW it runs, having pushed the filter and the
                   # column selection all the way down into the file read

The lazy version never loads the rows it will discard. You did not tell it to do that. It figured it out, because you described a goal instead of dictating steps. That is the shift: declarative over imperative. Say what you want, not how to get it, and let an optimiser that knows more about the file format than you do handle the how.

This same idea shows up everywhere once you see it. SQL is declarative. dbplyr writes R that becomes SQL that runs in the database. Dask builds a task graph before executing. DuckDB will run a query straight against Parquet files on disk. Different tools, one concept.

---

6. Streaming: your data does not have to fit in RAM

Related, and worth naming separately because it removes a real fear. The instinct that data must fit in memory to be worked on is just false, and has been for a while.

Out-of-core processing streams data through in chunks, keeping only a window in memory at a time. Many operations do not need the whole table: a sum needs a running total, a filter needs one row, a group-by needs one accumulator per group. Only genuinely global operations like a full sort need everything at once.

the humble chunk loop, still undefeated

total = 0
for chunk in pd.read_csv("massive.csv", chunksize=100_000):
    total += chunk.loc[chunk["tpm"] > 10, "tpm"].sum()

# or let the engine do it
pl.scan_parquet("massive.parquet").filter(...).collect(streaming=True)

Reach for the chunk loop before you reach for a cluster. A surprising amount of "big data" is just data that someone tried to load all at once.

---

7. Stop mutating things

Two ideas that travel together, both borrowed from functional programming, both about making code you can trust.

Immutability. The classic disaster is a notebook where cell 12 modifies df in place. Run it twice, get different answers. Run cells out of order, get a third. The data frame becomes a mutable global variable with no history, which is precisely the thing every other part of programming learned to avoid decades ago. Treat transformations as producing new values, not editing old ones.

Method chaining is how you make that pleasant instead of tedious. Rather than reassigning through a dozen intermediate names, describe the transformation as a pipeline:

a pipeline you can read top to bottom

clean = (
    pd.read_csv("raw.csv", dtype=SCHEMA, na_values=["ND"])
      .rename(columns=str.lower)
      .dropna(subset=["sample_id"])
      .assign(log_tpm=lambda d: np.log1p(d["tpm"]))
      .query("od600 > 0.1")
      .pipe(SampleSchema.validate)
)

No intermediate variables to leak, no df2, df3, df_final, df_final_v2. Every step is visible and the whole thing reads as one declaration of what clean data means. This is the same "describe, don't dictate" instinct from the lazy section, applied to code you have to read.

---

8. The loader is part of the experiment

One last idea, more cultural than technical. Idempotency means running something twice produces the same result as running it once. Your loading pipeline should be idempotent, cached on the content of the input rather than its filename, and versioned in the repository next to the analysis.

Because here is the thing the gene name story really teaches. That was not an Excel bug. Excel did what it was designed to do. The failure was that nobody treated the import step as part of the science, so nobody checked it, so nobody caught it, so it got published a few thousand times.

Your loader encodes decisions: what counts as missing, what type a column is, what values are impossible. Those are scientific decisions wearing engineering clothes. They deserve the same scrutiny as your statistics, and right now they almost never get it.

---

The through-line

If there is one thread running through all of these, it is this: push the work to the boundary and make it explicit. Types, contracts, shape, and plans all belong at the edge of the system, declared up front, where they are visible and checkable, instead of being resolved by accident somewhere in the middle of your analysis.

The reason this is worth caring about is not elegance. It is that the alternative already happened, at scale, in our field, and the fix was to rename the genes.

---

Putting it together with today's AI agents

Here is the honest reason most people skip everything above. It is not that they disagree. It is that writing schemas, declaring types, and building conversion steps is tedious, and tedious things get dropped under deadline. That friction is the whole reason the disciplines lapse. It is also exactly what a coding agent removes. So the interesting question is not whether an AI agent can write your loader. It obviously can. The question is how to use one without recreating, one level up, the precise failure this post is about.

Because here is the trap. An agent that reads your file and produces a loader is doing type inference again. It guesses your schema, guesses what missing means, guesses the valid ranges, and it does all of it fluently enough that you stop checking. That is the failure from section one wearing a much better suit. A confident, plausible guess that nobody reviews is the thing that got gene names renamed, and an agent is the most confident, most plausible guesser you have ever pointed at your data.

The resolution is the same one the whole post has been building toward: keep the human at the boundary. Do not ask the agent for a loader. Ask it for the boundary artifacts, review those, and then let it write the code that satisfies them. The schema stops being tedious boilerplate and becomes the one thing you actually read, the contract you hand the agent as a specification, and the guardrail on everything it produces.

In practice this is a short prompt sequence with whatever coding agent you already use, and the schema is the object passed between every step. The pattern in miniature:

the schema is the spec and the guardrail

# Step 2 output: the ONE artifact a human reviews and signs off.
# The agent proposed it; you approved it; it is now the contract.
class SampleSchema(pa.DataFrameModel):
    sample_id: Series[str]   = pa.Field(unique=True)
    od600:     Series[float] = pa.Field(ge=0, le=3.0, nullable=True)
    ph:        Series[float] = pa.Field(ge=0, le=14)
    treatment: Series[str]   = pa.Field(isin=["control", "low_do", "high_do"])

# Step 4: the same schema is now the agent's feedback signal.
try:
    df = SampleSchema.validate(load_it(path), lazy=True)   # agent-written loader
except pa.errors.SchemaErrors as e:
    # Hand these rows straight back to the agent as the next instruction.
    # "Your loader produced these failures. Fix the loader, not the data."
    feedback = e.failure_cases[["column", "check", "failure_case", "index"]]
    print(feedback)

Notice what the human did and did not do. You did not write the loader, the profiler, or the retry logic. The agent did all of that, quickly. You wrote and approved the schema, because that is where the scientific decisions live, and you kept the validation as the thing that checks the agent's work. The boring, delegatable work went to the agent. The one judgment that matters stayed with you.

An AI agent is a faster, more fluent version of the guess this whole post warns about. The schema is how you keep it honest.

This is why the disciplines matter more in the agent era, not less. An agent will generate a plausible pipeline in seconds, and plausible-but-unchecked is precisely the thing that gets published a few thousand times. The contract is what turns a fast, confident generator into a fast, confident, and correct one. Hand over the typing. Keep the type. That is the one job not to delegate.

References and further reading

← All writing