Reading and Writing Data

Analogy categoryAnalogies

Pandas provides a family of read_* and to_* functions that make moving data between external files and DataFrames largely a one-line operation: pd.read_csv for comma-separated files, pd.read_excel for spreadsheets, pd.read_json for JSON, pd.read_sql for database queries, and pd.read_parquet for the increasingly popular columnar Parquet format, each mirrored by a corresponding df.to_csv, df.to_excel, and so on for writing. While the defaults work for tidy, well-formed files, real-world data is rarely that clean, so these functions expose dozens of parameters to control delimiters, header rows, column selection, dtype coercion, missing-value markers, and date parsing at load time — getting these right up front avoids painful cleanup later.

🏏

Cricket analogy: read_csv, read_excel, read_json, and read_sql are like different ways a scorer receives match data - a paper scoresheet, a spreadsheet, a JSON feed from a scoring app, or a database query - and to_csv writes the final scorecard back out.

Reading CSV Files with Precision

pd.read_csv accepts parameters like sep (delimiter, useful for tab- or semicolon-separated files), usecols (load only specific columns, saving memory), dtype (force specific columns to a given type instead of relying on inference), parse_dates (convert specified columns to datetime64 at load time rather than as a separate step), and na_values (extra strings, beyond the pandas defaults, that should be treated as missing). Specifying dtype and parse_dates explicitly is considered good practice for large files: it avoids pandas' sometimes slow or ambiguous automatic type inference and prevents surprises, such as a ZIP code or ID column being inferred as int64 and losing leading zeros.

🏏

Cricket analogy: Setting parse_dates on a match-date column at load time avoids treating '2024-06-01' as text later, and forcing dtype on a jersey-number column prevents pandas from silently inferring it as float and adding a stray decimal point.

python

import pandas as pd

df = pd.read_csv(
    'orders.csv',
    sep=',',
    usecols=['order_id', 'customer', 'order_date', 'total'],
    dtype={'order_id': 'string', 'total': 'float64'},
    parse_dates=['order_date'],
    na_values=['N/A', 'unknown'],
)
print(df.dtypes)
# order_id             string
# customer             object
# order_date    datetime64[ns]
# total               float64
# dtype: object

Writing Data Back Out

The mirror-image to_* methods write a DataFrame back to disk: df.to_csv('out.csv', index=False) is the most common pattern, where index=False prevents pandas from writing the DataFrame's row index as an extra unnamed column (a very common oversight that pollutes downstream files). For repeated read/write cycles in a data pipeline, binary formats like Parquet (df.to_parquet) are strongly preferred over CSV: they preserve dtypes exactly (no re-inference needed on read-back), compress data far more efficiently, and read/write substantially faster, especially for large files with many columns.

🏏

Cricket analogy: df.to_csv('scorecard.csv', index=False) avoids writing a stray unnamed row-index column into the final scorecard; for repeated season-long logging, Parquet preserves dtypes and compresses far better than CSV for a full archive.

python

import pandas as pd

df = pd.DataFrame({'id': [1, 2, 3], 'value': [10.5, 22.1, 7.8]})

df.to_csv('output.csv', index=False)     # no extra unnamed index column
df.to_parquet('output.parquet')          # preserves dtypes, compressed, fast

round_trip = pd.read_parquet('output.parquet')
print(round_trip.dtypes)   # exactly matches df.dtypes, no inference needed

Parquet stores a schema alongside the data, so read_parquet never has to guess whether a column is an int, float, or string the way read_csv does — this is why round-tripping through Parquet is both faster and safer for dtype fidelity than CSV.

Forgetting index=False when writing to CSV is one of the most common pandas mistakes: the file gains an unnamed extra column holding the old row index, which then gets re-imported as a spurious 'Unnamed: 0' column the next time the file is read.

pd.read_csv, pd.read_excel, pd.read_json, pd.read_sql, and pd.read_parquet cover the most common input formats.
Use dtype, parse_dates, and na_values at read time to avoid unreliable automatic type inference.
usecols loads only needed columns, reducing memory usage for wide files.
Always pass index=False to to_csv unless you intentionally want the row index written out.
Parquet preserves exact dtypes and is faster and more compact than CSV for pipeline-to-pipeline data exchange.
Reading a column as a string that looks numeric (e.g. ZIP codes) requires explicit dtype to avoid losing leading zeros.

Practice what you learned

1. What is the effect of forgetting `index=False` when calling `df.to_csv('file.csv')`?

2. Why is specifying `dtype` explicitly recommended when reading a large CSV?

3. What is a key advantage of Parquet over CSV for storing intermediate pipeline data?

4. Which read_csv parameter converts specified columns to datetime64 automatically at load time?

5. What does the `usecols` parameter in `pd.read_csv` control?

Was this page helpful?

DataFrame Basics

Topics covered

#Python#PandasNumPyStudyNotes#DataScience#ReadingAndWritingData#Reading#Writing#Data#CSV#StudyNotes#SkillVeris

Reading and Writing Data