Reading and Writing Data
Get data in and results out without surprises
Every analysis starts and ends at a boundary: read Excel, CSV, JSON and SQL into frames, then write summary tables back. Knowing the gotchas — encodings, separators, types — saves hours.
What you will learn
- Read CSV, Excel, JSON and SQL into DataFrames
- Handle delimiters, encodings and type inference
- Write clean output with controlled formats
What is this?
pandas.read_csv/read_excel/read_json/read_sql import nearly any tabular source, while to_csv/to_excel/to_json and to_sql persist results. Each reader accepts parameters for separators, encodings, type coercion, date parsing and skipped rows — the boundary work that keeps logging out of the analysis.
Why do you need it?
- Imports are where 80% of data-quality bugs are born — encodings, delimiters, blank rows.
- Reading once with the right parameters beats fixing a mis-parsed frame forever.
- Type coercion at read time (parse_dates, dtype=) prevents silent string bugs.
- Consistent write formats (quoting, encoding) keep pipelines interoperable.
Mental model
Customs inspection at a port
Reading data into pandas
The inspector checks what each crate claims on the manifest — date columns are actual dates, numbers are numbers, blank rows aren't cargo. read_csv's parameters are the inspection rules; nail them at the border and nothing contraband reaches the analysis floor.
How it works
- read_csv(path) infers delimiters, headers and types; sep, encoding, dtype, parse_dates tune it.
- read_excel(path, sheet_name=) imports each sheet; engine handles .xlsx/.xls.
- read_json reads structured JSON (records/columns) into frames.
- read_sql(query, conn) executes SQL against any DB connector and returns a DataFrame.
- to_csv(to_csv(...), index=False, quoting=csv.QUOTE_NONNUMERIC) writes clean, readable tables.
- Every reader accepts low_memory, na_values, skiprows for messy real-world exports.
The boundary: import → transform → export.
Examples
One import, production parameters
import pandas as pd df = pd.read_csv( "exports/revenue_2026.csv", encoding="utf-8-sig", # handles Excel BOM parse_dates=["order_date"], dtype={"plan": "category"}, na_values=[".", "n/a"],)print(df.dtypes)df.to_csv("reports/revenue_summary.csv", index=False)Encoding, dates, categorical types and missing-value markers are all fixed at import; the export writes a clean table without row numbers.
In production
- A finance reconciliation reads a vendor's .xls with 3 header rows by setting skiprows and header=1.
- An ingestion job bulk-loads CSVs with dtype maps so columns stay typed across months.
- A reporting module writes to_sql into the warehouse, replacing the day's summary grain.
Best practices
- Fix encodings, separators and type maps at read_csv, not downstream.
- Use parse_dates explicitly; inference is unreliable on mixed formats.
- Write index=False unless the index is actual data.
- For SQL, SELECT only needed columns and cast at the query level when possible.
Common mistakes
- Ignoring the BOM, which renames the first header to 'city'.
- Trusting delimiter auto-detection on tabs or semicolon CSVs.
- Reading millions of rows with low_memory default and seeing inconsistent dtypes per chunk.
- to_csv writing a stray index column that breaks the next load.
Debugging tips
- A NameError on a column is usually a header/BOM problem: print(df.columns) first.
- Mixed date formats → set parse_dates and check the dtype you expected.
- Inconsistent row counts across runs → look for trailing blank lines or partial writes.
- When a column of ints reads as object, open the raw file and inspect for spaces or quotes.
Interview checkpoint
Hands-on lab
- Parse a semicolon-separated file with a BOM
- Set dates, dtypes and missing values at read time
- Write a clean summary CSV that re-imports identically
Import Messy Exports Cleanly
30 min · beginner
Quick quiz
Which parameter handles a text column that should be numeric?
to_csv writes the index column by default.