Reading instrument data

Why this matters

Instruments rarely give you a tidy CSV. A thermocouple logger, a UV-Vis spectrometer, a tensile tester or a plate reader exports a file with a block of metadata at the top, column names that include units, a semicolon delimiter because the software was configured in Europe, decimal commas, and markers such as ---, OVER or NaN where a reading failed. Opening it in a spreadsheet and fixing it by hand works once, but it is slow, error-prone and leaves no record. A short, documented Python function turns every future export into clean data the same way.

A typical export

Instrument: TC-08 Thermocouple Logger
Serial: A1234
Operator: J. Rivera
Exported: 2026-03-14 09:30
[Data]
Time (s);Temperature (°C);Signal (mV)
0;21,4;0,812
10;21,9;0,830
20;---;0,851
30;23,1;0,866
40;23,8;OVER
...

Before writing code, open the raw file in a text editor (not a spreadsheet, which silently reformats it) and note:

1. How many lines of metadata come before the header, or what marker ends them ([Data] here).
2. The delimiter (;), the decimal separator (,) and the encoding (usually UTF-8; older Windows software may use latin-1).
3. What missing or out-of-range values look like (---, OVER).
4. The units in each column header.

Reading it with pandas

pd.read_csv handles all of this with arguments:

import io
import pandas as pd

def read_export(text):
    lines = text.splitlines()
    start = lines.index("[Data]") + 1           # data header is the next line
    df = pd.read_csv(
        io.StringIO("\n".join(lines[start:])),
        sep=";",
        decimal=",",
        na_values=["---", "OVER"],
    )
    df.columns = ["time_s", "temp_C", "signal_mV"]
    return df

With a real file you pass the path instead of io.StringIO, plus encoding="utf-8" and skiprows= or comment="#" as needed. Two useful arguments for messy files: skipinitialspace=True (strips spaces after delimiters) and usecols= (keeps only the columns you need).

Rename columns to short, code-friendly names with the unit in the name (temp_C, not Temperature (°C)). It makes later code readable and keeps the unit attached.

Keep the metadata

The header lines are data too: serial number, operator, calibration date, sample ID. Parse them into a dictionary and keep them with the results:

def read_metadata(text):
    meta = {}
    for line in text.splitlines():
        if line == "[Data]":
            break
        key, _, value = line.partition(":")
        meta[key.strip()] = value.strip()
    return meta

str.partition(":") splits on the first colon only, so the time 09:30 in the value survives.

Check before you clean

df = read_export(text)
print(df.dtypes)            # every column should be float64 or int64
print(df.isna().sum())      # missing values per column
print(df.describe())        # ranges: is anything physically impossible?

If a column you expect to be numeric shows object, a marker you did not list in na_values (or a stray unit, or a thousands separator) is hiding in it. Find it with df[pd.to_numeric(df["temp_C"], errors="coerce").isna()].

Cleaning, transparently

Decide what to do with bad rows and record the decision:

n_before = len(df)
clean = df.dropna().reset_index(drop=True)
print(f"dropped {n_before - len(clean)} of {n_before} rows with missing values")

Options, from most to least conservative:

• Drop rows with any missing value (dropna()), as here.
• Drop only where a key column is missing (dropna(subset=["temp_C"])).
• Interpolate short gaps in smooth time series (df["temp_C"].interpolate()), and say so in the methods.
• Flag suspect values in a new Boolean column rather than deleting them.

Never overwrite the raw export. Keep the raw file read-only, and let your script produce a separate cleaned file (clean.to_csv("run42_clean.csv", index=False)), so anyone can rerun the cleaning from scratch.

Large and binary formats

Bigger instruments use other formats: HDF5 (h5py, pd.read_hdf), NetCDF (xarray), Excel (pd.read_excel), vendor binaries with Python readers, or fixed-width text (pd.read_fwf). The habits are the same: inspect the raw file, parse with explicit options, check dtypes and ranges, clean transparently.

Common mistakes

• Opening and re-saving in Excel, which can change decimal separators, dates and leading zeros.
• Letting pandas guess the delimiter or decimal separator: 21,4 silently becomes text or splits into two columns.
• Unlisted missing markers that turn a numeric column into object dtype.
• Dropping rows without counting them, so nobody knows how much data was lost.
• Hard-coding a row number for the header when a marker line ([Data]) is more robust to metadata changes.

Quick recap

• Inspect the raw file first: metadata, delimiter, decimal mark, missing markers, units.
• pd.read_csv(..., sep=";", decimal=",", na_values=[...]); rename columns to name_unit.
• Parse and keep the metadata; check dtypes, isna().sum() and describe().
• Clean with recorded, reversible decisions; never edit the raw file.

Try it

In the Do tab you will parse the logger export above into a DataFrame, extract its metadata and clean out the failed readings.