File and Object I/O

Read delimited text with the schema declared

Pandas exposes top-level readers such as read_csv() and corresponding object writers such as DataFrame.to_csv(). The pattern is consistent across formats: a top-level read_* function brings data in, and a DataFrame.to_* method sends it back out.

For delimited text, schema preservation begins by declaring how fields and labels are encoded. A CSV file carries no type information and no guarantee about its own layout, so anything you do not declare is guessed.

sep selects the delimiter, header identifies the row or rows used as column names, names supplies explicit names, and index_col selects the column or columns used as row labels.

Two of these work together in a way worth remembering. A file with no header row needs both header=None, so that its first line is not mistaken for column names, and names, so that the columns have names at all. Supplying names without header=None costs you the first row of data; supplying header=None without names leaves you with numbered columns.

For a pipe-delimited file, sep="|" is the declaration that makes every other setting meaningful — parse the fields wrongly and there is nothing left to type or label correctly.

Limit the columns and request their types

Wide files carry columns nobody needs, and inferred types are a frequent source of silent damage — an account number with leading zeros becomes an integer and the zeros are gone.

usecols limits which columns are parsed, while dtype maps the whole object or individual columns to requested types. These are two separate controls with two separate jobs: one decides what is read, the other decides how it is represented.

If converters are supplied, they run instead of dtype conversion for those columns. So a column named in both places is handled by the converter, and its dtype entry does not also apply.

Declare missing markers and dates, then write back

Missing markers and dates also require deliberate choices, because both are conventions of the file rather than facts pandas can derive.

na_values adds strings to recognize as missing, and keep_default_na=False limits recognition to explicitly supplied markers when na_values is present. That second setting is what turns a permissive default into a strict policy: with it, only the markers you named count as missing, and a literal NA left in the data stays a string instead of quietly disappearing.

parse_dates identifies columns to parse as dates, while date_format supplies their format. Declaring the format removes the guesswork from ambiguous dates, where the same text can be read two different ways.

Export mirrors the same decisions. On export, to_csv() can control the delimiter, selected columns, header, index, encoding, quoting, missing-value representation, and date format.

Put together, an ingestion for a pipe-delimited file with no header, NA? as its only missing marker, an identifier that must stay text, and only three columns of interest is a single declared call:

• sep="|" and header=None with explicit names for the layout,
• usecols for the three columns,
• a text dtype for the identifier column,
• na_values=["NA?"] with keep_default_na=False for the missing-marker policy.

Nothing in that list is optional if the result has to match the file. Reading with defaults and repairing afterwards cannot recover leading zeros that were already parsed away, nor undo a real NA string that was already read as missing.

Match the JSON orientation to the record shape

to_json() and read_json() support multiple orientations for representing a labeled object. The orientation is the single decision that determines what survives the trip, so it should follow from what the consumer needs.

records represents a DataFrame as a list of column-to-value objects and does not include index labels. split stores columns, index, and data in separate entries. values stores only nested value arrays and omits both column and index labels. table follows JSON Table Schema and can preserve metadata including dtypes and index names.

The choice has a consequence at read time as well: a non-default orientation used to write data should also be supplied when reading it. The file does not announce its own shape, so a reader that assumes the default cannot interpret a split or table document correctly.

That pairing decides the hardest case. If a table must survive a JSON round trip with its dtypes and its index name intact, table is the orientation that carries that metadata — and it has to be given on both sides:

• records drops the index, so an index name cannot be recovered later.
• values drops both labeled axes, leaving nothing to reconstruct from.
• A document written with one orientation and read with another is not the same document.

Read and write JSON line by line

Not every JSON file is a single document. For newline-delimited JSON, use lines=True.

With records orientation, writing with lines=True emits one record per line; reading with lines=True can also use chunksize to return an iterator over groups of lines. That combination is what makes newline-delimited JSON workable at scale: the writer appends independent lines, and the reader walks groups of them without holding the whole file at once.

One representation detail is worth knowing before anyone compares files. Missing values represented by NaN, NaT, or None are written as JSON null. Three distinct in-memory markers therefore arrive as one value on disk, which is correct JSON but not a distinction the file can carry.

Select spreadsheet content deliberately

A workbook is several tables in one file, so reading one means choosing which. read_excel() accepts sheet_name as a sheet name, a zero-based sheet position, a list, or None. Its default value is zero, so the first sheet is read.

A list returns a dictionary of the specified sheets, and None returns a dictionary of all sheets. So sheet_name=["North", 3] returns a dictionary holding the sheet called North and the sheet at position three — two DataFrames keyed by what you asked for, not one appended table.

Within a sheet, the selection continues. usecols can select Excel ranges such as A,C:E, positions, inferred names, or a callable — the spreadsheet range notation people already use for the same job. Lists passed to header and index_col can reconstruct MultiIndex columns and rows, which is how a report with stacked header rows or several label columns comes back with its structure intact rather than flattened.

Writing follows the same logic. to_excel() writes a DataFrame to a named sheet, while ExcelWriter allows separate DataFrames to be written to separate sheets in one workbook.

One practical caveat: engine support depends on the spreadsheet format and installed optional dependency. Which formats a given environment can read or write is therefore a property of that environment, not something the code alone settles.

Use columnar Parquet with explicit boundaries

Parquet is a partitioned binary columnar format designed for efficient DataFrame reading, writing, and cross-language sharing. Because it stores data by column, it can serve a few fields of a wide table without touching the rest, and other tools can read the same file.

to_parquet() and read_parquet() use either the pyarrow or fastparquet engine. read_parquet(columns=[...]) reads only the selected columns. That projection happens on the read side, which is what keeps unneeded fields out of memory in the first place — a different job from index, compression or partitioned output.

The index deserves a deliberate decision. Index serialization differs by engine unless the index argument is explicit; index=False omits the DataFrame index, while index=True writes it.

For a consumer outside pandas that expects exactly the named data columns and rejects anything extra, index=False is the setting that guarantees the schema — with the honest tradeoff that a custom index will not come back on a later read.

Two limits complete the picture: duplicate column names and non-string column names are not supported by the documented Parquet path. Both have to be resolved before the write, not diagnosed after it.

Key takeaways

• Declare delimiter, labels, missing markers, dates, and dtypes when reading text — sep, header, names, index_col, usecols, dtype, na_values with keep_default_na=False, parse_dates and date_format, with converters taking precedence over dtype on the columns they name.
• Choose a JSON orientation that retains the structure the consumer needs: records drops the index, split separates columns, index and data, values drops both axes, and table carries dtypes and index names — and supply a non-default orientation when reading too.
• Work line by line when the file is newline-delimited: lines=True emits one record per line and supports chunksize on the read, and NaN, NaT and None all appear as JSON null.
• Select workbook sheets, ranges, headers, and indexes explicitly — sheet_name defaults to the first sheet, a list or None returns a dictionary, usecols accepts ranges such as A,C:E, and ExcelWriter puts several DataFrames in one workbook.
• Use Parquet column selection and an explicit index policy for a controlled round trip, remembering that duplicate and non-string column names are outside the supported path.