Reading & Writing Data

Understanding Reading & Writing Data

Reading data is a translation step, not simply opening a table. A CSV contains characters and separators; a pandas DataFrame contains labeled columns with data types. The reader must decide where records begin, which fields are missing, and whether values represent numbers or text. Writing reverses that translation, but some formats cannot retain everything the DataFrame knows. Reliable input/output therefore requires checking meaning as well as successful execution.

Consider a product identifier such as 0042. Its digits do not make it a quantity: adding identifiers is meaningless, and removing leading zeros changes its representation. Automatic type inference may interpret it as an integer. Supplying a schema, meaning expected column names and types, prevents that mistake. Dates likewise need an agreed interpretation, especially when a value such as 03/04/2026 could describe two different days.

Missing values also require a contract. An empty field might mean an unknown quantity, whereas zero means a known absence. pandas supports nullable types, including Int64, which stores integers alongside missing values. Capitalization matters: this is not the ordinary NumPy int64 type. Text tokens such as NA may also be interpreted as missing by default, so review missing-value rules when those tokens are legitimate labels.

Choose an output format for its consumer. CSV is readable and widely supported, but does not embed a reliable column-type schema. Excel supports spreadsheet workflows and requires an appropriate engine. JSON is useful for structured records, although nested objects may need reshaping. Parquet stores data columnwise with type information and supports compression, making it useful for many analytical tables. It requires a compatible engine, such as PyArrow; actual size and speed depend on the data and workload, not a universal multiplier.

The pandas user guide groups these operations under IO tools and treats missing data and nullable integers separately. That separation is useful: parsing, validating, and choosing storage are related but distinct tasks. A successful read does not prove the source is correct. Check columns, row counts, missingness, and relevant constraints before analysis. After export, a round-trip check can reveal unwanted index columns or changed types, although it cannot establish that the original observations were accurate.

Practical walkthrough

Use Python 3.11 or newer with pandas 2.2 or 3.x already installed. The example uses StringIO from Python's standard library to provide an in-memory text stream. No files, downloads, or optional storage engines are needed. This keeps attention on parsing and serialization rather than paths or permissions.

First, inspect the tiny source table. It has two columns: product_id and units. Identifiers contain leading zeros. One units field is blank, and another contains zero. Assume each row describes one product and blank units means unknown inventory. We deliberately preserve that distinction rather than filling the blank with zero.

Next, define the schema once and reuse it for both reads. The string type protects identifiers; Int64 permits a missing integer. Passing this mapping to read_csv makes the intended interpretation explicit. Unexpected nonnumeric text in units will fail rather than quietly becoming valid inventory.

Then validate the imported table. The assertions check column order, identifier uniqueness, and nonnegative known quantities. These are small teaching checks, not a complete validation framework. In production, use explicit error handling because Python can disable assertions when running with optimization.

Serialize with to_csv without supplying a destination. pandas returns the CSV as a string. Set index=False because the default row labels are not product data. Set na_rep to MISSING and fix the line terminator for predictable text.

Finally, read that string back using the same schema and an explicit missing token. Compare both DataFrames. This tests this particular conversion, including types, but does not guarantee that other applications will apply identical rules.

Worked example

from io import StringIO
import pandas as pd

source = "product_id,units\n0042,7\n0091,\n0105,0\n"
schema = {"product_id": "string", "units": "Int64"}

df = pd.read_csv(StringIO(source), dtype=schema)
assert df.columns.tolist() == ["product_id", "units"]
assert df["product_id"].is_unique
assert df["units"].dropna().ge(0).all()

csv_text = df.to_csv(index=False, na_rep="MISSING", lineterminator="\n")
restored = pd.read_csv(
    StringIO(csv_text),
    dtype=schema,
    keep_default_na=False,
    na_values=["MISSING"],
)

print(csv_text, end="")
print("Rows:", len(restored))
print("Missing units:", int(restored["units"].isna().sum()))
print("Types:", ", ".join(str(dtype) for dtype in restored.dtypes))
print("Round trip equal:", df.equals(restored))

Expected output

product_id,units
0042,7
0091,MISSING
0105,0
Rows: 3
Missing units: 1
Types: string, Int64
Round trip equal: True

The output begins with the serialized CSV, not a formatted DataFrame. Each identifier retains its leading zeros because both reads explicitly request strings. The missing integer becomes the text MISSING during export and becomes a missing value again during import. The recorded zero remains zero throughout. There is no extra row-label column because index=False excludes it. The final comparison is True because column labels, values, corresponding missing entries, and dtypes agree. Notice that CSV itself did not remember the schema: the second read received it again. A consumer omitting those options could interpret the same text differently.

Common mistakes and how to avoid them

• Trusting inferred types for identifiers.

  Declare identifier columns as strings during import. Converting an already inferred integer column to strings cannot recover leading zeros unless an external formatting rule specifies their width.

• Treating missing quantities as zero automatically.

  Confirm the source convention first. Preserve unknown counts with a nullable type and inspect missingness before deciding whether any replacement is justified.

• Exporting the index without deciding what it means.

  Use index=False for incidental row labels. If the index contains meaningful identifiers, deliberately preserve it, preferably as a clearly named data column.

• Assuming a custom missing token cannot conflict with real data.

  Reserve and document the token. If MISSING is a legitimate text value, choose another agreed convention or use typed storage that represents nulls separately from strings.

Review questions

Further reading

• pandas user guide
• scikit-learn user guide