4.4 Importing and Exporting Tabular Data

A file is not a DataFrame waiting to be opened. It is bytes plus a format convention, and parsing turns those bytes into columns, values, missing markers, and dtypes. Every parser default is therefore a decision. Reliable analysis makes important decisions explicit and validates the result after reading.

Inspect the boundary before loading everything

Before importing an unfamiliar file, establish:

• Its format and delimiter.

• Character encoding, often UTF-8 but not guaranteed.

• Whether a header exists and whether names are unique.

• Which strings represent missing values.

• Decimal, thousands, date, and time-zone conventions.

• Whether identifiers contain leading zeros or exceed safe numeric ranges.

• Expected columns, row count scale, and malformed-row policy.

Preview raw text with a text-aware tool. Do not open an untrusted file as executable code, and do not load an entire multi-gigabyte file merely to discover its header.

For a CSV with this content:

order_id,ordered_at,zone,minutes,debug_note
00127,2026-08-01,North,31,ok
00128,2026-08-02,South,UNKNOWN,late scan
00129,2026-08-03,West,44,ok

define an import contract:

import pandas as pd

required = ["order_id", "ordered_at", "zone", "minutes"]

deliveries = pd.read_csv(
    "orders.csv",
    usecols=required,
    dtype={
        "order_id": "string",
        "zone": "string",
    },
    parse_dates=["ordered_at"],
    date_format="%Y-%m-%d",
    na_values={"minutes": ["UNKNOWN"]},
    encoding="utf-8",
    on_bad_lines="error",
)

deliveries["minutes"] = deliveries["minutes"].astype("Int64")

usecols limits the ingestion boundary and documents which fields matter. Declaring order_id as a string preserves 00127; if it were first parsed as integer 127, the original identifier format could not be recovered. parse_dates identifies intended date columns, while date_format removes ambiguity and can improve parsing performance. A per-column na_values mapping avoids interpreting the word UNKNOWN as missing in unrelated text columns.

on_bad_lines="error" makes malformed rows visible. Skipping malformed data can be a deliberate quarantine policy, but silently dropping it is not data cleaning. Preserve rejected rows and counts for audit.

The parser lab is a boundary autopsy rather than a set of presets. Edit the raw CSV and its parser manifest, then run the delimiter and quote scanner. Leading-zero loss, malformed records, text-contaminated numeric columns, accepted rows, and inferred dtypes are all derived from the exact file and contract you authored.

Successful parsing is only the first checkpoint

Immediately inspect and validate the result:

print(deliveries.shape)
print(deliveries.head())
print(deliveries.dtypes)
print(deliveries.isna().sum())

assert list(deliveries.columns) == required
assert deliveries["order_id"].notna().all()
assert deliveries["order_id"].is_unique
assert deliveries["ordered_at"].notna().all()
assert deliveries["minutes"].dropna().between(0, 24 * 60).all()

These checks answer different questions:

• Structure: are the required columns present, with the expected names and order?

• Identity: are order keys present and unique?

• Types: did dates and nullable integers parse as intended?

• Validity: do values satisfy plausible ranges and category rules?

• Completeness: where did parsing introduce or preserve missing values?

A parser can successfully produce a DataFrame in which every duration is text, every date is missing, or two orders share an identifier. “No exception” is not a quality criterion.

For large files, nrows can sample the structure, and chunksize can iterate through manageable blocks:

for chunk in pd.read_csv("orders.csv", chunksize=100_000, **read_options):
    validate_chunk(chunk)
    process_chunk(chunk)

Chunk processing changes the algorithm. A global duplicate check, quantile, or sort cannot be validated independently within each chunk. Maintain cross-chunk state or use a storage engine designed for the operation. Chapter 11 will revisit streaming ideas for time-ordered data.

Export is an interface contract

CSV is useful for broad interoperability. When the default positional index has no business meaning, omit it explicitly:

deliveries.to_csv(
    "orders_clean.csv",
    index=False,
    encoding="utf-8",
    na_rep="UNKNOWN",
    date_format="%Y-%m-%d",
)

Without index=False, a later read often reveals an extra column such as Unnamed: 0. If the index is a real business key, give it a name and decide whether the receiving system expects it as a field. Do not let a serialization default make that decision.

CSV stores characters, not a complete pandas schema. On re-import, a string identifier may be inferred as integer, a nullable integer may become floating-point, categories lose their category definitions, and time-zone details require explicit handling. The corresponding read options are part of the exchange contract.

For analytical storage, Parquet is a columnar format that usually preserves more type information and supports selective column reads and compression:

deliveries.to_parquet(
    "orders_clean.parquet",
    index=False,
)

restored = pd.read_parquet(
    "orders_clean.parquet",
    columns=required,
)

Parquet requires a compatible engine such as PyArrow. Engine versions and cross-system type mappings still matter, so validate the restored data rather than assuming perfect portability. Format choice depends on consumers: spreadsheets and simple integrations often prefer CSV, while analytical pipelines benefit from schema-aware columnar storage.

Avoid pickle for exchanging untrusted data. Loading a pickle can execute code, and pickle is tied closely to Python object versions. Use an interoperable data format and validate it.

Test the round trip, not only the write call

A round trip writes data, reads it back through the documented contract, and compares the restored result with the source:

from io import StringIO
from pandas.testing import assert_frame_equal

buffer = StringIO()
deliveries.to_csv(
    buffer,
    index=False,
    na_rep="UNKNOWN",
    date_format="%Y-%m-%d",
)

buffer.seek(0)
restored = pd.read_csv(
    buffer,
    dtype={"order_id": "string", "zone": "string"},
    parse_dates=["ordered_at"],
    date_format="%Y-%m-%d",
    na_values={"minutes": ["UNKNOWN"]},
)
restored["minutes"] = restored["minutes"].astype("Int64")

assert_frame_equal(deliveries[required], restored[required])

assert_frame_equal checks values, labels, order, and dtypes by default. If the exchange contract permits a difference, such as harmless floating-point rounding, relax only the corresponding comparison and document the tolerance.

The round-trip lab uses adversarial property testing. Edit the source records and exchange contract, then generate edge rows containing leading-zero identifiers, huge numbers, spreadsheet formulas, and delimiter-bearing text. Separate tests for values, dtypes, columns, and spreadsheet safety prevent one successful write from masquerading as equivalence.

Write production outputs safely

A production export also needs operational safeguards:

• Write to a temporary file in the destination filesystem, validate it, then atomically replace the final path when supported.

• Never overwrite the only copy of source data with a transformed result.

• Record source version, transformation version, row count, schema, and checksum when auditability matters.

• Escape or neutralize spreadsheet formula prefixes when exporting untrusted text for spreadsheet users.

• Protect credentials and sensitive fields; a convenient export is still a data disclosure.

• Test the exact consumer path, including encoding, line endings, time zones, and missing markers.

Chapter 4 field checklist

Before handing a DataFrame to the next stage, confirm:

• Row identity, index uniqueness, column meanings, and dtypes are explicit.

• Label alignment is intentional and unmatched labels remain visible until resolved.

• .loc and .iloc match the intended coordinate system and result shape.

• Filters have named policies for missing values and deterministic sorting rules.

• Assignments target one object in one operation under Copy-on-Write.

• File parsing, validation, export, and round-trip behavior form one documented contract.

Chapter 5 will work with the problems these checks uncover: missing observations, duplicate entities, inconsistent strings and dates, mixed units, and outliers. The goal will not be to make warnings disappear, but to preserve evidence while making defensible cleaning decisions.