Enforcing GTFS dtypes in pandas

Declare a dtype for every column as data, read every identifier as text, pass keep_default_na=False with an explicit na_values=[""], and coerce numerics after reading rather than during it. Those four rules eliminate the whole family of silent corruption that pandas’ type inference introduces into GTFS: dropped leading zeros, identifiers promoted to floats, and a genuinely-named stop called NA turning into a null. The readers this policy applies to are compared in parsing GTFS with pandas and partridge.

Root Cause Analysis

pandas.read_csv guesses a type per column by sampling values. That is a reasonable default for analytical data and a poor one for an interchange format built almost entirely out of opaque identifiers.

Leading zeros. A stop_id of 00123 looks numeric, so inference makes it int64 and the value becomes 123. Every subsequent join against the source file, against a realtime feed, or against another table read differently, silently fails for that stop. Nothing raises, and the row count drops.

Integer columns promoted to float. A route_id column of digits with a single missing value becomes float64, so 1234 becomes 1234.0. Cast back to string for a join and it does not match. This one is particularly nasty because it depends on whether the sampled chunk happened to contain the null.

The default NA literals. pandas treats roughly twenty strings as missing unless told otherwise, including NA, N/A, NULL, None, nan and -. NA is a real route short name — it is also a real stop_id in more than one feed — and - appears as a placeholder headsign. Every one of those becomes a null, with no warning.

Mixed types within a column. Reading a large file in chunks, pandas can infer different types for different chunks and emit a DtypeWarning that most pipelines log and ignore. The resulting column holds a mixture of int and str, and comparisons behave differently depending on which row you are on.

The fix in all four cases is the same: do not let pandas guess. Declare the schema, read text, convert deliberately.

Production-Ready Python Implementation

"""A declarative dtype policy for reading GTFS with pandas."""
from __future__ import annotations

import logging
from dataclasses import dataclass, field

import pandas as pd

log = logging.getLogger("gtfs.dtypes")

# Only an EMPTY field is missing. pandas' defaults would swallow a stop called
# "NA" and a headsign of "-", both of which occur in production feeds.
NA_VALUES: list[str] = [""]

ID = "string"          # every identifier is an opaque label, never a number


@dataclass(frozen=True)
class ColumnSpec:
    dtype: str                  # the pandas dtype to COERCE to, after reading
    required: bool = False
    note: str = ""


@dataclass(frozen=True)
class TableSpec:
    columns: dict[str, ColumnSpec]
    key: tuple[str, ...] = ()

    @property
    def read_dtypes(self) -> dict[str, str]:
        """Everything is read as text; conversion happens afterwards, visibly."""
        return {name: "string" for name in self.columns}


NUMERIC = {"int8", "int16", "int32", "int64", "Int8", "Int16", "Int32", "Int64",
           "float32", "float64", "Float32", "Float64"}

SCHEMA: dict[str, TableSpec] = {
    "agency.txt": TableSpec({
        "agency_id": ColumnSpec(ID, note="conditionally required"),
        "agency_name": ColumnSpec("string", required=True),
        "agency_url": ColumnSpec("string", required=True),
        "agency_timezone": ColumnSpec("string", required=True),
        "agency_lang": ColumnSpec("string"),
    }, key=("agency_id",)),

    "routes.txt": TableSpec({
        "route_id": ColumnSpec(ID, required=True),
        "agency_id": ColumnSpec(ID),
        "route_short_name": ColumnSpec("string", note="'NA' is a real value here"),
        "route_long_name": ColumnSpec("string"),
        "route_type": ColumnSpec("Int16", required=True),
        "route_color": ColumnSpec("string", note="hex, may have leading zeros"),
    }, key=("route_id",)),

    "trips.txt": TableSpec({
        "route_id": ColumnSpec(ID, required=True),
        "service_id": ColumnSpec(ID, required=True),
        "trip_id": ColumnSpec(ID, required=True),
        "trip_headsign": ColumnSpec("string"),
        "direction_id": ColumnSpec("Int8"),
        "block_id": ColumnSpec(ID),
        "shape_id": ColumnSpec(ID),
    }, key=("trip_id",)),

    "stops.txt": TableSpec({
        "stop_id": ColumnSpec(ID, required=True, note="leading zeros are common"),
        "stop_code": ColumnSpec(ID),
        "stop_name": ColumnSpec("string"),
        "stop_lat": ColumnSpec("Float64", required=True),
        "stop_lon": ColumnSpec("Float64", required=True),
        "zone_id": ColumnSpec(ID),
        "location_type": ColumnSpec("Int8"),
        "parent_station": ColumnSpec(ID),
    }, key=("stop_id",)),

    "stop_times.txt": TableSpec({
        "trip_id": ColumnSpec(ID, required=True),
        "arrival_time": ColumnSpec("string", note="may exceed 24:00; never a time type"),
        "departure_time": ColumnSpec("string", note="as above"),
        "stop_id": ColumnSpec(ID, required=True),
        "stop_sequence": ColumnSpec("Int32", required=True),
        "pickup_type": ColumnSpec("Int8"),
        "drop_off_type": ColumnSpec("Int8"),
        "shape_dist_traveled": ColumnSpec("Float64"),
        "timepoint": ColumnSpec("Int8"),
    }, key=("trip_id", "stop_sequence")),
}


@dataclass
class ReadReport:
    table: str
    rows: int
    coerced: dict[str, int] = field(default_factory=dict)   # column -> values lost
    unknown_columns: list[str] = field(default_factory=list)


def read_table(handle, table: str) -> tuple[pd.DataFrame, ReadReport]:
    spec = SCHEMA.get(table)
    if spec is None:
        raise KeyError(f"no dtype schema declared for {table}")

    frame = pd.read_csv(handle, dtype="string", encoding="utf-8-sig",
                        keep_default_na=False, na_values=NA_VALUES)
    report = ReadReport(table=table, rows=len(frame))

    report.unknown_columns = [c for c in frame.columns if c not in spec.columns]
    if report.unknown_columns:
        log.debug("%s: %d extra column(s) kept as text: %s", table,
                  len(report.unknown_columns), report.unknown_columns)

    for name, column_spec in spec.columns.items():
        if name not in frame.columns:
            if column_spec.required:
                raise ValueError(f"{table} is missing required column {name}")
            continue
        # Identifiers and free text stay exactly as published.
        if column_spec.dtype not in NUMERIC:
            frame[name] = frame[name].str.strip()
            continue
        before = frame[name].notna().sum()
        converted = pd.to_numeric(frame[name], errors="coerce")
        lost = int(before - converted.notna().sum())
        if lost:
            bad = frame.loc[converted.isna() & frame[name].notna(), name]
            log.error("%s.%s: %d value(s) are not numeric, e.g. %r",
                      table, name, lost, bad.iloc[0])
            report.coerced[name] = lost
        frame[name] = converted.astype(column_spec.dtype)

    log.info("%s: %d row(s), %d column(s), %d coercion loss(es)",
             table, len(frame), len(frame.columns), sum(report.coerced.values()))
    return frame, report

Step-by-Step Walkthrough

Everything is read with dtype="string". One argument removes inference entirely. Nothing is guessed, nothing is promoted, and every value arrives exactly as the file holds it. The cost is memory, and it is recovered later by casting identifiers to category once they have been validated.

keep_default_na=False with na_values=[""]. This is the pair that matters. Together they say: a field is missing if and only if it is empty. A route short-named NA, a headsign of -, a stop_code of NULL all survive as the strings they are.

Numeric coercion is a separate, visible step. pd.to_numeric(..., errors="coerce") turns unconvertible values into NA and the code immediately counts how many were lost and shows one. Doing the conversion at read time would have produced the same nulls with no way to know they happened.

Nullable integer types throughout. Int8 rather than int8, Float64 rather than float64. A GTFS direction_id is legitimately absent on many rows, and a non-nullable integer column cannot represent that — pandas would silently promote the whole column to float, putting 0.0 and 1.0 where 0 and 1 belong.

arrival_time is typed as string, deliberately, with a note. It looks like a time and is not one: values past 24:00 exist and no time type accepts them. Converting it here would break every overnight trip, so the schema records why it must not be converted.

Unknown columns are kept, not dropped. Feeds carry extensions, and a producer-specific column is often the only place some useful attribute lives. They stay as text and are reported at debug level, so nothing is lost and nothing is guessed about them.

route_color is text. A hex colour of 00FF00 read as a number becomes 0, which is how a feed’s green line turns black.

Verification and Output

def verify(frame: pd.DataFrame, table: str, raw_sample: pd.DataFrame) -> None:
    """raw_sample: the same file re-read with dtype=str, as ground truth."""
    spec = SCHEMA[table]

    for name, column_spec in spec.columns.items():
        if name not in frame.columns or column_spec.dtype in NUMERIC:
            continue
        # An identifier must be byte-for-byte what the file holds.
        original = raw_sample[name].fillna("").str.strip()
        got = frame[name].fillna("").astype(str)
        mismatch = int((original != got).sum())
        assert not mismatch, (
            f"{table}.{name}: {mismatch} identifier(s) changed shape on read — "
            "check for inference or a stripped leading zero")

    if spec.key and all(k in frame.columns for k in spec.key):
        duplicated = int(frame.duplicated(list(spec.key)).sum())
        assert not duplicated, f"{table}: {duplicated} duplicate key row(s)"

    for name, column_spec in spec.columns.items():
        if column_spec.required and name in frame.columns:
            missing = int(frame[name].isna().sum())
            assert not missing, f"{table}.{name} is required but empty on {missing} row(s)"

The identifier comparison is the check worth having. Reading the same file twice — once through the schema, once as raw text — and asserting that every identifier is unchanged catches leading-zero loss, float promotion and whitespace differences in one assertion, and it does so against ground truth rather than against an expectation.

A clean read:

INFO gtfs.dtypes: stops.txt: 8904 row(s), 12 column(s), 0 coercion loss(es)
INFO gtfs.dtypes: stop_times.txt: 1840112 row(s), 9 column(s), 0 coercion loss(es)

A feed with real problems:

ERROR gtfs.dtypes: stops.txt.stop_lat: 4 value(s) are not numeric, e.g. '42,3601'
INFO  gtfs.dtypes: stops.txt: 8904 row(s), 12 column(s), 4 coercion loss(es)

A comma decimal separator, four stops with no usable coordinate, named precisely — rather than four silently-null latitudes discovered later by a spatial join returning nothing.

Gotchas and Edge Cases

Memory during the read. Every column as string is the most expensive representation. On stop_times.txt that is the peak, and it is why chunked reading applies the same schema per chunk rather than reading the whole table first.
Categorical casts after validation. Once identifiers are verified, casting trip_id and stop_id to category typically recovers more memory than inference would have used in the first place. Do it after, never before — a categorical read in chunks produces incompatible category sets.
shape_dist_traveled in unknown units. Typed as a float, which is all the schema can say. Whether it is metres, kilometres or feet is not recorded anywhere in the feed, and it must be checked against measured geometry before use.
Extension columns that collide with future specification columns. Keeping unknown columns as text means a producer-specific stop_id_alt survives, and it also means a column the specification later defines will arrive untyped. Reviewing the unknown-column list per feed occasionally is worthwhile.
Whitespace inside identifiers. .str.strip() handles the ends. Internal whitespace is left alone, because it may be genuine — and stripping it would break the byte-for-byte identifier check for exactly the wrong reason.

Frequently Asked Questions

Why must identifiers be read as text?

Because a stop_id of 00123 is not the number 123. Inference reads it as an integer, drops the leading zeros, and every join against the original file then fails for that stop. The value is an opaque label, and labels are text.

What does keep_default_na break?

pandas treats about twenty literal strings as missing by default, including NA, N/A, NULL, None and nan. NA is a real stop_id and a real route short name in production feeds, so the default turns valid data into nulls with no warning at all.

Should numeric columns be coerced at read time or after?

After. Reading everything as text and coercing explicitly lets you see exactly which values failed rather than getting a column silently promoted to float because one row held a stray space.

Does this cost memory?

Reading as text costs more than reading as int, yes. Recover it by casting identifiers to category after validation, which usually ends up smaller than the inferred types would have been.

Optimizing pandas Memory Usage for Transit Feeds — recovering the memory this policy spends
Checking GTFS Referential Integrity in Python — the joins that only work if identifiers survived intact
Up: Parsing GTFS with pandas and partridge — the readers this schema feeds
Section: Python Parsing & Data Normalization · Home