Data, Files and Parsing

Choosing a file format

Format	Strengths	Limits	Use for
CSV / TSV	Plain text, opens in any spreadsheet, easy to append	No types or units; large; ambiguous quoting and decimal commas	Logs and tables you want people to open
JSON	Nested, self-describing, universal in web and APIs	Verbose; no comments; no binary or dates	Settings, messages, small records
YAML / TOML	Human-friendly configuration	Whitespace or subtle-syntax traps	Configuration files
Binary (struct, NumPy .npy)	Compact, fast, exact floats	Not human-readable; layout must be documented	Large or fast data, instrument dumps
HDF5, Parquet, Feather	Typed columns, compression, fast partial reads	Needs libraries	Big tables and arrays, long-term storage
SQLite	A whole database in one file; queries; safe concurrent writes	Not for huge multi-user systems	Logs you want to query; small applications
Excel (.xlsx)	Everyone has it; formulas and charts	Hidden type changes (dates, long numbers); slow; hard to version	Reports for people, not as a data store

CSV and text files

import csv

with open("log.csv", "a", newline="", encoding="utf-8") as f:       # append; newline="" is needed for csv
    w = csv.writer(f)
    if f.tell() == 0:
        w.writerow(["time_utc", "temp_c", "rh_percent"])              # a header with units in the names
    w.writerow([iso_time, round(temp_c, 2), round(rh, 1)])
    f.flush()                                                          # push to the OS, so a crash loses little

with open("log.csv", newline="", encoding="utf-8") as f:
    for row in csv.DictReader(f):
        t = float(row["temp_c"])

• Put units in the column names (temp_c, pressure_kpa) and a header row.
• Use a dot for decimals and a comma for fields in files you exchange; some locales write 23,41 and use semicolons, which breaks parsers.
• Round for storage, not in calculations: 23.41 to two decimals is 23.41. Store as many digits as the sensor supports, no more (see Numbers and Floating Point).
• Quote text fields that may contain commas or newlines (the csv module does it); never build CSV by string concatenation.
• Encoding: UTF-8; Excel may expect a byte-order mark or a different encoding for non-ASCII text.
• Append, do not rewrite: open with "a" so a crash cannot lose the earlier rows, and start a new file each day or size limit.

JSON and structured data

import json
record = {"t": "2026-10-31T14:05:09Z", "node": "n3", "temp_c": 23.41, "rh": 51.2, "ok": True}
text = json.dumps(record, separators=(",", ":"))          # compact; json.dumps(record, indent=2) for reading
back = json.loads(text)                                    # raises json.JSONDecodeError on bad input

with open("settings.json", encoding="utf-8") as f:
    cfg = json.load(f)
timeout = cfg.get("timeout_s", 0.5)                        # a default when the key is missing

• Types: strings, numbers, booleans, null, lists and objects. There is no date type (use ISO 8601 strings) and no NaN or infinity.
• Validate what you read: required keys, types and ranges (a schema library such as pydantic or jsonschema does it).
• JSON lines (one object per line) suit logs: append a record at a time and read it back line by line.
• Configuration: keep settings out of the code (JSON, TOML or YAML), with defaults, and never put passwords in a file you share.

Binary files

Binary is compact and exact: a float is stored in its 4 or 8 bytes, with no rounding to text. Read and write it with struct (for records) or NumPy (for arrays), following the documented layout of field sizes and byte order (see Bits, Bytes and Data Formats).

import numpy as np, struct

samples = np.array([...], dtype="<f4")                     # little-endian 32-bit floats
samples.tofile("run42.f32")                                # raw dump
back = np.fromfile("run42.f32", dtype="<f4")

np.save("run42.npy", samples)                               # .npy stores the dtype and shape too
np.load("run42.npy", mmap_mode="r")                         # memory-mapped: read parts of a huge file

REC = struct.Struct("<IHh")                                 # 4-byte time, 16-bit count, signed 16-bit value
for chunk in iter(lambda: f.read(REC.size), b""):
    t, count, raw = REC.unpack(chunk)

• Write a header with a signature, a version and the sample rate, so a file can describe itself and old files stay readable.
• Document the layout next to the data, including units and scale factors (a 16-bit count that means millivolts or 0.01 °C).
• Use HDF5 or Parquet when you need names, units, attributes and compression: h5py, pyarrow, pandas.read_parquet.

Parsing serial and instrument messages

Instruments send text or binary lines. Parse them defensively: split, check the number of fields, convert, check ranges and the checksum, and reject or flag anything odd instead of letting a bad value reach your data.

import re

LINE = re.compile(r"^\$(?P<body>[A-Z]+,[-0-9.]+,[A-Z])\*(?P<cs>[0-9A-F]{2})$")

def parse(line):
    m = LINE.match(line.strip())
    if not m:
        return None                                        # not a valid frame
    body, cs = m["body"], int(m["cs"], 16)
    calc = 0
    for ch in body.encode("ascii"):
        calc ^= ch
    if calc != cs:
        return None                                        # corrupt
    kind, value, unit = body.split(",")
    return kind, float(value), unit

• Split with care: line.split(",") fails on quoted commas; use csv or a regular expression for anything non-trivial.
• Regular expressions are powerful and cryptic: compile them once, comment them, and test them on good and bad lines.
• Convert with error handling: float("N/A") raises ValueError; catch it and decide what to do (skip, flag, NaN).
• Stream parsers must cope with partial lines (see Embedded Systems: serial buses).
• Keep the raw line alongside the parsed values in the log: parsing bugs can then be fixed later.

Time stamps and time zones

Time is the most error-prone field in a log. The rules are few: store UTC, in a standard format, with the source of the clock recorded; convert to local time only when showing it to a person.

Representation	Example	Notes
ISO 8601 (UTC)	2026-10-31T13:45:09Z	Sortable as text, unambiguous with the Z (or an offset like +02:00); the best for logs
Unix time (seconds)	1793454309	Seconds since 1970-01-01 UTC; a signed 32-bit integer overflows on 2038-01-19 03:14:07 UTC (the year 2038 problem), so use 64 bits
Unix time (milliseconds)	1 793 454 309 000	Fits in a double with a resolution of 2.44e-4 ms, fine; do not use 32-bit floats (see Numbers and Floating Point)
Local time without a zone	2026-03-08 02:30	Ambiguous or non-existent around daylight-saving changes: avoid
Monotonic clock	time.monotonic()	Counts up steadily, unaffected by clock changes: use it to measure durations, not as a date

• Daylight-saving time makes local days 23 or 25 hours long, and some local times do not exist or occur twice. A log in local time has duplicate and missing hours. (Phoenix, Arizona never changes its clocks while most of its neighbors do, so the offset between two cities changes with the season.)
• Know where the clock comes from: a microcontroller’s crystal drifts (tens of seconds a month; see Embedded Systems); a PC synchronizes with NTP; GPS gives accurate time. Record the source and correct a drifting clock.
• Time stamp at the source, as early as possible (when the sample was taken, not when it was logged), and note the sampling interval.
• In Python: datetime.now(timezone.utc), datetime.fromisoformat, zoneinfo.ZoneInfo("America/Phoenix"); avoid naive datetimes with no zone.

Working with tables: pandas

pandas holds a table (a DataFrame) with named columns and a time index, and does the filtering, grouping, resampling and joining that a spreadsheet does by hand, repeatably.

import pandas as pd

df = pd.read_csv("log.csv", parse_dates=["time_utc"], index_col="time_utc")      # time as the index
df = df.sort_index()

df["temp_c"] = df["temp_c"].where(df["temp_c"].between(-40, 85))        # out-of-range values become NaN
hourly = df.resample("1h").agg(["mean", "min", "max", "count"])         # hourly statistics
smooth = df["temp_c"].rolling("10min").mean()                           # a time-based moving average
daily = df.groupby(df.index.date)["temp_c"].describe()                  # summary per day
df.to_parquet("log.parquet")                                            # compact, typed, fast

Databases and SQL

When data outgrow a pile of files, or several programs need them, use a database. SQLite is a database in a single file with no server: Python includes it (sqlite3), and it handles millions of rows.

import sqlite3
db = sqlite3.connect("sensors.db")
db.execute("""CREATE TABLE IF NOT EXISTS readings (
    t_utc   TEXT    NOT NULL,           -- ISO 8601
    node    TEXT    NOT NULL,
    temp_c  REAL,
    PRIMARY KEY (node, t_utc))""")
db.execute("INSERT OR IGNORE INTO readings VALUES (?, ?, ?)", (iso, "n3", 23.41))   # ? placeholders: never build SQL from text
db.commit()

for node, avg, n in db.execute("""SELECT node, AVG(temp_c), COUNT(*) FROM readings
                                  WHERE t_utc >= ? GROUP BY node ORDER BY node""", ("2026-10-31",)):
    print(node, round(avg, 2), n)

• Always use placeholders (?) rather than formatting values into the SQL string: it prevents SQL injection and quoting bugs.
• Give every table a key and an index on what you search by (time, node); an index turns a scan of millions of rows into a lookup.
• Time-series databases (InfluxDB, TimescaleDB) and MQTT brokers suit continuous sensor streams at scale (see Data Logging).
• Back up the file while it is not being written, or use the SQLite backup API.

Data quality, gaps and units

Problem	Sign	What to do
Missing samples	Gaps in the time stamps; counts below expected	Log the gap; mark it NaN; never fill with zero
Duplicates	The same time stamp twice (a retry, a clock step)	Drop exact repeats; investigate clock changes
Spikes and stuck values	A reading out of range, or the same value for hours	Range and rate-of-change checks; flag, do not silently delete
Wrong units or scale	A step change when a device was replaced; values off by 10, 25.4 or 1 000	Record units and calibration with each stream; plot before trusting
Clock jumps	Time goes backwards or leaps	Use a monotonic clock for order, UTC for time; log clock corrections
Sensor drift	Slow change against a reference	Recalibrate and record the calibration date
Selection bias	Only “good” data were kept	Keep everything; filter in analysis, with the rule written down

• Plot everything first: most data problems are visible in a plot long before they show in a statistic.
• Validate on the way in: check types, ranges and time order when the data arrive, and count rejects.
• Carry the uncertainty: a sensor accurate to ±0.5 °C does not support two decimals.

Keeping data safe and reproducible

• Never overwrite the raw data. Keep the original files read-only, do all processing in scripts that write new files, and you can always redo the analysis.
• Script every step, from raw file to final plot, so that a result can be regenerated; keep the script, the data version and the library versions together (see Software Practice, Testing and Debugging).
• Name files so they sort: 2026-10-31_run07_nodeA.csv, with the date first; keep a README describing the columns, units, instrument and settings.
• Back up (the 3-2-1 rule: three copies, two media, one off-site) and test a restore.
• Checksums: store a SHA-256 of important files to detect silent corruption (hashlib.sha256).
• Privacy and licenses: data about people or from a customer may be protected; record where data came from and how you may use them.
• Long-term formats: prefer open formats (CSV, JSON, HDF5, Parquet) that will still open in twenty years.

Related

• Programming Languages: the overview of this guide.
• Python for Engineers: NumPy, pandas and pyserial.
• Bits, Bytes and Data Formats: byte order, structs and text encodings.
• Data Logging: sampling, storage and timestamps on a real logger.
• Machine Learning: preparing and splitting data for models.

FAQ

CSV or a database?

CSV for a log that people open in a spreadsheet and for exchange. A database (SQLite is enough) when you need to query, join, update or write from several programs safely, or when the data are large.

Should I store time stamps in local time?

No. Store UTC (ISO 8601 with a Z), and convert for display. Local time has duplicate and missing hours at daylight-saving changes and is ambiguous without the zone.

How do I handle missing data?

Mark it as missing (NaN or null), never as zero, record how many readings each result rests on, and decide explicitly whether to ignore, interpolate or flag it.

Why does Excel change my data?

It guesses types: it turns long numbers into scientific notation, text like “1-2” into dates and drops leading zeros. Import text columns as text, or avoid Excel as a store and use it only to view results.

References

• Python documentation: csv, json, sqlite3, struct, datetime and zoneinfo; pandas documentation (time series).
• ISO 8601, Date and time format; RFC 4180 (CSV); RFC 8259 (JSON); the SQLite documentation.
• Wilson, G. et al., “Good enough practices in scientific computing”, PLOS Computational Biology, 2017.

Last reviewed: October 2026. File sizes use the example records shown: yours will differ.