Lab Automation Has Three Floors: Hardware, Data, Decisions - Most Teams Get Stuck on the Second

My last post covered turning a formulation lab from a 5-10 samples/day manual shop into a 45-sample parallel automated line. The most common follow-up question I got: what happens after you buy the robots and dispensing systems?

The answer: buying hardware only pours the foundation. I split lab automation into three floors - hardware automation, data automation, decision automation - and most teams (including us, before we fixed it) get stuck on the second floor for a long time, often without realizing it, because the word "automation" already got used on the first floor. This post is about floors two and three: how I built the data pipeline, how AI-assisted reporting and anomaly flagging work, the pitfalls, and a question that won't go away - how much programming does a chemist actually need to do this?

This is a methodology-level writeup. No company data or formulation details.

What people are still doing after hardware automation

Once the equipment was in and samples could run in parallel, we found technicians' time didn't free up the way we expected. Tracking actual hours for a few weeks showed the bottleneck had just moved:

• Scheduling: 45 samples running in parallel is no problem, but deciding whose sample gets the machine next, or which project has priority, still ran on group chat messages and a shared spreadsheet - and collisions were routine.
• Copying numbers: every instrument (rheometer, particle sizer, imaging system) spits out its own raw format - CSV, proprietary binary, sometimes a screenshot PDF. Manually moving those numbers from five or six pieces of software into one summary sheet is new manual labor - it just swapped "pipetting" for "copy-paste."
• Reporting: what a client or internal review wants is a report with conclusions, not a pile of raw curves. Writing that report used to mean someone spending half a day digging through history, comparing trends, and organizing the narrative - and the more data automation produces, the slower this step gets, because there's more to compare against.
• Spotting anomalies: a rheology curve with the wrong shape, a batch separating faster than the historical baseline - catching that depended on a senior technician's "something's off" instinct. When that person wasn't around, it got missed.

All four have one thing in common: instrument vendors' automation offerings don't touch any of them. What vendors sell you is "produce data faster," not "act on data faster." That gap is what I mean by the second floor.

The three-floor model

Pull "lab automation" apart and it's actually three separate problems with completely different investment curves:

Hardware automation is "easy" because it's a one-time capital purchase: sign a contract, pay, install, train - clear boundaries, and sales reps chase you down. Data automation has none of that convenience - it's ongoing software engineering work, and there's no off-the-shelf "lab data platform" product that drops cleanly into your actual instrument mix (every vendor's software version, export format, and update cadence differs). You end up writing the glue code yourself. This is also why a lot of teams flying an "AI lab" banner go quiet when you ask where the data actually lives and whether the format is unified - they bought floor one and are pitching a floor-three story.

What I built: data pipeline, AI-assisted reporting, automatic anomaly flagging

Approach only, not proprietary formulations or data.

A unified data pipeline. The core decision here is as plain as the hardware layer: no matter how different the instruments, the landing format has to be unified. I wrote an adapter layer that parses each instrument's raw export (CSV, proprietary binary, or numbers extracted from a screenshot PDF) into the same timestamped wide table. The field design is roughly this skeleton (values and formulation content below are fabricated examples):

The design choice that matters isn't the table itself - it's that timestamp is acquisition time, not data-entry time. When a raw file was produced, when it was parsed, and when a human looked at it are three different moments; conflating them silently corrupts every trend analysis downstream. This table is the foundation everything else sits on - without it, AI assistance has nothing to work with.

AI-assisted report drafts. What a report needs is a narrative - "how does this batch compare to the historical baseline" - not raw numbers. The pipeline automatically compares the new batch's key metrics (onset time, viscosity curve features, particle size distribution) against historical formulations of the same type and generates a structured draft: trend description, flagged anomalies, deviations from past batches. A technician reviews and fills in the rest instead of starting from a blank page. This didn't make the report "auto-generate itself" - it removed the grunt work between raw data and a first draft, leaving the professional judgment call to the human.

Automatic anomaly flagging. Historical curves and image sequences establish a baseline (a simple statistical bound, not a fancy model). When a new sample's curve deviates past a threshold, the system flags it red with a short note on what's off, and it goes into a review queue - instead of waiting for a quarterly rollup to surface it. The threshold is deliberately conservative: better to flag a few false positives for a human to clear than to miss a real anomaly. The goal was never to replace judgment - it was to stop judgment from arriving late.

Pitfalls

The reality of lab IT environments. A lot of instrument computers are still running whatever OS shipped with them, network access is restricted or physically air-gapped, and data export often means a USB stick. Before designing the data pipeline, design how you're going to get data off a computer that isn't networked, safely. That sounds unglamorous, but it determines whether the whole plan is even feasible.

Data silos are an organizational problem, not a technical one. Every instrument having its own data format exists because nobody ever demanded otherwise - different groups and projects stored data their own way for years before anyone tried to roll it up, and by the time you want to unify it, the fragmentation is years deep. Fixing it after the fact always costs more than standardizing from day one.

"Excel is the center of the universe." However clean the database gets, technicians still want an Excel file at the end of the day. Fighting that head-on just gets routed around; the more workable move is making the database export to something Excel can open with one click, treating Excel as a consumption layer rather than a data source - there's exactly one authoritative copy of the raw data, and everything else is a view.

The pipeline itself rots. A minor instrument software update can silently rename a field or change a date format in the export, and the parser doesn't error out - it just parses the wrong numbers. That's more dangerous than an outright crash, because nobody notices right away. I added basic sanity checks to every parsed record (value ranges, required fields not null); errors need to fail loudly, because silent bad data is far more expensive than downtime.

How much programming does a chemist actually need

This is the question I get asked most, and the answer is more optimistic than people expect.

What actually gets used is a plain layer: pandas for reading and cleaning tabular data, a few dozen lines of script to parse different export formats into one shape, basic SQL to pull what you want out of a database. No formal CS algorithms background, no need to design elaborate software architecture - this work is closer to "data tidying" than "software engineering."

What's actually moved the bar is AI coding assistants over the last couple of years. Writing a working parser from a blank file used to be a real wall for someone without formal training. Now you hand a messy instrument export to Claude Code or Codex, say "write me a script that turns this into a table shaped like this," and what comes back usually runs, or runs after a few tweaks. That's how I personally went from "can read Python" to "can build a data pipeline independently" - that path deserves its own post, so I'll leave it here for now.

One concrete piece of advice: don't try to learn "data science" end to end first. Start from the specific task of parsing one instrument's export file into something queryable, and learn whatever you need as you hit it. Working backwards, the path runs roughly: learn enough Python and pandas to get by; practice on a real export file from your own lab, not a tutorial dataset; learn to describe to an AI coding assistant exactly what table shape you want, instead of hand-writing every line of syntax; finally pick up enough SQL that data can be queried, not just opened. Four stops in, you already have enough to build a working pipeline - you don't need to go further.

Decision automation, and an honest boundary

Of the three floors, decision automation is where progress is shallowest. Anomaly flagging and historical comparison are running day to day, but "suggest a formulation directly from historical data" or "tie real-time equipment status monitoring to experiment design" are both still in validation - not yet something you'd trust to make decisions on its own.

Here's the cold water: whether decision automation gets anywhere at all depends entirely on data quality from floor two. How clean, uniformly formatted, and fully labeled your historical data is directly sets the ceiling on how trustworthy any AI-assisted judgment can be - build the data pipeline sloppily, and no amount of fancy modeling downstream fixes garbage in, garbage out. That's also why this post's weight sits on the data layer instead of rushing to talk about "what we're doing with AI": without the foundation poured properly, everything above it is a story that only survives in a slide deck.

If you're hiring to build a lab data team, or you're a lab director evaluating whether to greenlight an "AI lab" project, this three-floor framework is a usable self-check: ask which floor your lab is actually stuck on before asking whether you can "do AI." The answer is very likely floor two - data formats aren't unified yet, and historical data is still scattered across different groups' individual computers. Getting that floor solid gets you closer to real automation than any product with an "AI" label slapped on it.

If you're working on lab data automation too - especially dealing with instrument data silos and lab IT constraints - I'd like to hear from you. I'll keep writing about the follow-up work in the 化学与实验室 category.