Pandas read write files

Reading a file successfully does not prove that pandas preserved its meaning. Leading-zero IDs can become numbers, the text NA can become a missing value, and a misplaced Excel header can turn the first record into column names. Start with an explicit schema and verify a small round trip before processing a real dataset.

These examples run in memory with Python 3.12.14, pandas 2.2.3 with Copy-on-Write enabled and pandas 3.0.6, openpyxl 3.1.5, and PyArrow 23.0.1. Run the blocks in order. Excel and Parquet require their respective optional engines; the fixture does not open production files or databases.

Choose a format for the actual handoff

from io import BytesIO, StringIO
import pandas as pd

frame = pd.DataFrame({
    'id': pd.Series(['001', '002', '003'], dtype='string'),
    'amount': pd.Series([12, None, 7], dtype='Int64'),
    'region': pd.Series(['NA', 'East', 'West'], dtype='string'),
})
dtypes = {'id': 'string', 'amount': 'Int64', 'region': 'string'}
print(frame['id'].tolist())
print(frame['amount'].fillna(-1).tolist())
print(frame['region'].tolist())

Expected output:

['001', '002', '003']
[12, -1, 7]
['NA', 'East', 'West']

Here NA is a literal region code, and an empty amount is missing. The -1 above is only a display marker; it is not written into the DataFrame. Nullable Int64 keeps the numeric column integer-valued while allowing a missing cell.

Round-trip CSV with explicit types and missing markers

csv_text = frame.to_csv(index=False, lineterminator='\n', na_rep='')
csv_bytes = csv_text.encode('utf-8')
restored_csv = pd.read_csv(
    BytesIO(csv_bytes), encoding='utf-8', dtype=dtypes,
    keep_default_na=False, na_values={'amount': ['']},
)
pd.testing.assert_frame_equal(restored_csv, frame)
print(restored_csv['id'].tolist())
print(restored_csv['amount'].isna().tolist())
print(restored_csv.loc[0, 'region'])

Expected output:

['001', '002', '003']
[False, True, False]
NA

Use a known encoding from the source contract. Trying arbitrary encodings until one decodes can produce plausible but corrupted text. A CSV contains text, so its reader must reconstruct the schema; the original pandas dtypes are not stored in the file. index=False avoids exporting an unintended index column.

Excel header positions are relative to skipped rows

This workbook places a title on worksheet row 1, leaves row 2 blank, and writes the header on row 3. After skipping two rows, that header is position zero. Using header=1 would consume worksheet row 4, the first data record, as column names.

workbook = BytesIO()
with pd.ExcelWriter(workbook, engine='openpyxl') as writer:
    frame.to_excel(writer, sheet_name='Data', startrow=2, index=False)
    writer.sheets['Data'].cell(row=1, column=1, value='Score report')

workbook.seek(0)
restored_excel = pd.read_excel(
    workbook, sheet_name='Data', engine='openpyxl', skiprows=2, header=0,
    dtype=dtypes, keep_default_na=False, na_values={'amount': ['']},
)
pd.testing.assert_frame_equal(restored_excel, frame)
print(restored_excel.columns.tolist())

workbook.seek(0)
wrong_header = pd.read_excel(workbook, engine='openpyxl', skiprows=2, header=1)
print([str(value) for value in wrong_header.columns])

Expected output:

['id', 'amount', 'region']
['001', '12', 'NA']

For an ordinary workbook with a first-row header, omit skiprows and use the default header=0. A dtype declaration cannot recover a leading zero that was already lost because the source workbook stored an ID as a number. Formatting and formulas also need a separate workbook-specific review; this fixture contains literal values.

Preserve analytical types with Parquet

parquet = BytesIO()
frame.to_parquet(parquet, engine='pyarrow', index=False)
parquet.seek(0)
restored_parquet = pd.read_parquet(parquet, engine='pyarrow')
pd.testing.assert_frame_equal(restored_parquet, frame)
print(restored_parquet['id'].tolist())
print(str(restored_parquet['amount'].dtype))

parquet.seek(0)
selected = pd.read_parquet(parquet, engine='pyarrow', columns=['id', 'amount'])
print(selected.columns.tolist())

Expected output:

['001', '002', '003']
Int64
['id', 'amount']

The tested round trip preserves this fixture's nullable integer and string columns. Verify other types in your own schema, particularly nested values, timezone-aware dates, and extension types. Selecting needed columns can avoid loading unrelated columns; Parquet is not a CSV chunk iterator with different spelling.

Use an explicit JSON record orientation

json_lines = frame.to_json(orient='records', lines=True)
restored_json = pd.read_json(StringIO(json_lines), orient='records', lines=True, dtype=dtypes)
pd.testing.assert_frame_equal(restored_json, frame)
print(len(restored_json))
print(restored_json['amount'].isna().tolist())

Expected output:

3
[False, True, False]

Each line contains one record, with JSON null for the missing amount. Passing a StringIO makes it clear that the input is literal content rather than a filesystem path. The dtype mapping prevents a numeric-looking ID from changing meaning.

Aggregate chunks without accumulating every row

total, observed = 0, 0
with pd.read_csv(
    BytesIO(csv_bytes), dtype=dtypes, keep_default_na=False,
    na_values={'amount': ['']}, chunksize=2,
) as chunks:
    for chunk in chunks:
        total += int(chunk['amount'].sum())
        observed += int(chunk['amount'].count())
mean = total / observed if observed else None
print(total, observed, mean)

Expected output:

19 2 9.5

The mean uses the total observed amount divided by the number of observed amounts. Averaging per-chunk averages can give the wrong answer when chunk sizes or missing counts differ. Concatenating every chunk into one growing DataFrame also gives up the main memory advantage. See correct CSV totals and averages for the complete chunking lesson.

Fail visibly when the format contract changes

Check expected columns, row counts, null counts, and dtypes after reading. Handle an empty file separately from a valid header-only table. Do not use on_bad_lines='skip' as a silent default; define how rejected records are counted and reviewed. When writing a real output that replaces an existing file, write to a temporary file in the same directory and replace the destination only after validation succeeds.

For cleaning policies after ingestion, see the pandas cleaning workflow. Primary references: pandas I/O tools and read_excel header and skiprows behavior.