Reading and Writing Data

Reading CSV Files

CSV (Comma-Separated Values) is the most common format for tabular data exchange. pandas reads CSV files with pd.read_csv() and writes them with df.to_csv().

Basic CSV Reading

import pandas as pd

# Read a CSV file
df = pd.read_csv("sales_data.csv")

# Inspect what loaded
print(df.head())
print(df.shape)
print(df.dtypes)

read_csv() automatically detects the header row, infers column types, and parses the file into a DataFrame. For well-formed CSVs, no additional parameters are needed.

Common read_csv Parameters

filepath_or_buffer: Path to the file. Can be a local path, URL, or file-like object.

sep: Delimiter character. Default is comma. For tab-separated files, use sep="\t".

# Tab-separated values
df = pd.read_csv("data.tsv", sep="\t")

# Semicolon-separated (common in European locales)
df = pd.read_csv("data.csv", sep=";")

header: Row to use as column names. Default is 0 (first row). Use header=None if the file has no header.

# No header row — pandas assigns numeric column names
df = pd.read_csv("data_no_header.csv", header=None)

# Use the second row as header (skip the first)
df = pd.read_csv("data.csv", header=1)

names: Provide explicit column names. Overrides the header.

df = pd.read_csv("data.csv", names=["date", "product", "revenue"])

index_col: Use a column as the row index.

df = pd.read_csv("data.csv", index_col="date")

usecols: Read only specific columns. Reduces memory for large files.

df = pd.read_csv("data.csv", usecols=["product", "revenue"])

dtype: Specify column types explicitly. Essential when type inference fails.

df = pd.read_csv("data.csv", dtype={"product": "category", "revenue": "float64"})

na_values: Additional strings to recognize as missing values.

df = pd.read_csv("data.csv", na_values=["N/A", "missing", "-", ""])

nrows: Read only the first N rows. Useful for previewing large files.

df = pd.read_csv("huge_file.csv", nrows=1000)

encoding: File encoding. Default is UTF-8. For Windows-generated files, try encoding="latin-1" or encoding="cp1252".

df = pd.read_csv("data.csv", encoding="latin-1")

Handling Missing Values

CSV files represent missing values inconsistently: empty cells, "NA", "null", "-", "?". By default, pandas recognizes a set of common missing value markers. You can extend this list:

df = pd.read_csv("data.csv", na_values=["N/A", "missing", "-", "?"])

After loading, check for missing values:

df.isnull().sum()          # count per column
df.isnull().sum().sum()    # total missing values
df.dropna()               # drop rows with any missing values
df.fillna(0)              # fill missing values with 0

Writing CSV Files

# Write to CSV
df.to_csv("output.csv", index=False)

# Write without header
df.to_csv("output.csv", header=False, index=False)

# Write specific columns only
df.to_csv("output.csv", columns=["product", "revenue"], index=False)

# Write with custom separator
df.to_csv("output.tsv", sep="\t", index=False)

Always set index=False unless you need the index in the output. The default writes the index as an unnamed first column, which causes problems when re-reading the file.

CSV Limitations

CSV files have no type information — everything is text. Dates, numbers, and strings all load as object dtype until you convert them. CSV files also cannot store multiple sheets, formatting, or metadata. For these reasons, CSV is best for simple data exchange, not for complex or typed data storage.

Reading CSV from URLs

read_csv() accepts URLs, which is useful for downloading data directly from public sources:

# Read CSV directly from a URL
df = pd.read_csv("https://example.com/data.csv")

# Read from a GitHub raw file
df = pd.read_csv("https://raw.githubusercontent.com/user/repo/main/data.csv")

This eliminates the manual download step. However, be cautious with large files over slow connections — the entire file downloads before parsing begins. For production pipelines, download first, then parse, so you can cache the file and retry parsing without re-downloading.

Reading Excel and JSON

Excel files are ubiquitous in business environments. JSON is the standard format for web APIs and nested data. pandas handles both, with different considerations for each.

Reading Excel Files

Excel files require an additional engine library. For .xlsx files, install openpyxl:

pip install openpyxl

import pandas as pd

# Read the first sheet
df = pd.read_excel("sales_data.xlsx")

# Read a specific sheet by name
df = pd.read_excel("sales_data.xlsx", sheet_name="Q1 Sales")

# Read a specific sheet by position (0-based)
df = pd.read_excel("sales_data.xlsx", sheet_name=0)

# Read multiple sheets (returns a dict of DataFrames)
sheets = pd.read_excel("sales_data.xlsx", sheet_name=["Q1", "Q2", "Q3"])
q1_df = sheets["Q1"]

# Read all sheets
all_sheets = pd.read_excel("sales_data.xlsx", sheet_name=None)

Excel-Specific Parameters

header: Row to use as column names (default 0).

usecols: Columns to read. Accepts column letters ("A:C") or indices.

df = pd.read_excel("data.xlsx", usecols="A:D")
df = pd.read_excel("data.xlsx", usecols=[0, 1, 3])

skiprows: Rows to skip at the start. Useful for files with title rows or metadata.

df = pd.read_excel("data.xlsx", skiprows=3)  # skip first 3 rows

nrows: Read only N rows.

Writing Excel Files

# Write to a single sheet
df.to_excel("output.xlsx", sheet_name="Results", index=False)

# Write multiple sheets to one file
with pd.ExcelWriter("output.xlsx") as writer:
    q1_df.to_excel(writer, sheet_name="Q1", index=False)
    q2_df.to_excel(writer, sheet_name="Q2", index=False)
    summary.to_excel(writer, sheet_name="Summary", index=False)

The ExcelWriter context manager is required for multi-sheet output. Without it, each to_excel call overwrites the previous sheet.

Reading JSON Files

JSON (JavaScript Object Notation) is a text format for structured data. It is the standard format for web APIs and configuration files.

import pandas as pd

# Read a JSON file
df = pd.read_json("data.json")

# Read from a URL (common for APIs)
df = pd.read_json("https://api.example.com/data")

# Read JSON lines format (one JSON object per line)
df = pd.read_json("data.jsonl", lines=True)

JSON Structure Matters

JSON data can be structured in different ways. pandas handles common patterns:

// Array of records (most common — works directly)
[
  {"product": "A", "sales": 150},
  {"product": "B", "sales": 230}
]

df = pd.read_json("records.json")
#   product  sales
# 0       A    150
# 1       B    230

For nested JSON, you may need to normalize:

import json

# Nested JSON requires normalization
with open("nested.json") as f:
    data = json.load(f)

df = pd.json_normalize(data, record_path=["orders"])

Writing JSON Files

# Write as array of records (most common)
df.to_json("output.json", orient="records")

# Write with indentation for readability
df.to_json("output.json", orient="records", indent=2)

# Write as JSON lines (one record per line)
df.to_json("output.jsonl", orient="records", lines=True)

The orient parameter controls the output structure. "records" (array of objects) is the most common and the format most APIs expect.

When to Use Each Format

Excel is the right choice when your audience uses spreadsheets — business stakeholders, financial reports, multi-sheet workbooks. JSON is the right choice when your data comes from or goes to web APIs, or when you need to represent nested/hierarchical structures that flat tables cannot capture.

Working with Excel Formulas

read_excel() reads calculated values, not formulas. If a cell contains =SUM(A1:A10), pandas returns the computed result, not the formula string. This is usually what you want for analysis. However, if you need to preserve formulas, you would need openpyxl directly. For data analysis, computed values are the correct input — formulas are a spreadsheet concern, not a data concern.

Reading from SQL Databases

pandas reads SQL query results directly into DataFrames using pd.read_sql(). This bridges the gap between database storage and Python analysis.

Setting Up the Connection

pd.read_sql() requires a database connection object. The standard approach uses SQLAlchemy:

pip install sqlalchemy

import pandas as pd
from sqlalchemy import create_engine

# Create a connection engine
engine = create_engine("postgresql://user:password@localhost:5432/mydb")

# Read a query into a DataFrame
df = pd.read_sql("SELECT * FROM sales WHERE year = 2024", engine)

# Read an entire table
df = pd.read_sql("sales", engine)

Database URL Formats

SQLAlchemy uses URL-style connection strings:

SQLite is the simplest for local work — it requires no server, just a file path.

read_sql Parameters

sql: SQL query string or table name.

con: SQLAlchemy engine or DBAPI connection.

index_col: Column to use as the DataFrame index.

df = pd.read_sql("SELECT * FROM sales", engine, index_col="sale_id")

columns: Columns to select (when reading a table name).

df = pd.read_sql("sales", engine, columns=["product", "revenue"])

chunksize: Read in chunks for large result sets. Returns an iterator.

# Process 10,000 rows at a time
for chunk in pd.read_sql("SELECT * FROM large_table", engine, chunksize=10000):
    process(chunk)  # handle each chunk

Chunked reading prevents memory overflow when querying large tables. Each chunk is a DataFrame you can process independently.

Writing to SQL

# Write a DataFrame to a SQL table
df.to_sql("sales_backup", engine, if_exists="replace", index=False)

# Append to an existing table
df.to_sql("sales_backup", engine, if_exists="append", index=False)

# Fail if table exists (default)
df.to_sql("sales_backup", engine, if_exists="fail", index=False)

The if_exists parameter controls behavior when the table already exists:

• "fail" (default): Raise an error.
• "replace" : Drop the table and recreate it.
• "append" : Insert rows into the existing table.

When to Use SQL vs. Files

Use SQL when data lives in a database and you need to filter or join before loading. Use files when data is shared across systems, version-controlled, or used outside a database context.

For analysis workflows, a common pattern is: query the database to extract relevant data, save to CSV for sharing, then analyze the CSV. This decouples analysis from database access.

Security Considerations

Never hardcode database credentials in your code. Use environment variables or a configuration file:

import os

engine = create_engine(
    f"postgresql://{os.environ['DB_USER']}:{os.environ['DB_PASS']}@localhost/mydb"
)
```n
This keeps credentials out of version control and allows different credentials for development and production environments.

### Parameterized Queries

When reading SQL with user-supplied parameters, never use string formatting. Use parameterized queries to prevent SQL injection:

```python
# WRONG — vulnerable to SQL injection
query = f"SELECT * FROM users WHERE name = '{user_input}'"
df = pd.read_sql(query, engine)

# CORRECT — parameterized
df = pd.read_sql("SELECT * FROM users WHERE name = %s", engine, params=(user_input,))

SQL injection is a critical security vulnerability. Even in analysis scripts, parameterized queries are essential when handling external input.

Type Handling and Format Selection

Data files do not always load with correct types. Dates become strings, numbers become objects, and categorical columns consume excessive memory. Proper type handling is essential for correct analysis.

Diagnosing Type Problems

import pandas as pd

df = pd.read_csv("sales_data.csv")

# Check types
df.dtypes
# date          object    <- should be datetime
# product       object    <- correct (string)
# revenue       object    <- should be numeric
# quantity       int64    <- correct

Two common problems:

1. Dates as strings: object dtype for date columns prevents date arithmetic and time-based filtering.
2. Numbers as strings: object dtype for numeric columns prevents math operations.

Fixing Types After Loading

# Convert string to datetime
df["date"] = pd.to_datetime(df["date"])

# Convert string to numeric (coerce errors to NaN)
df["revenue"] = pd.to_numeric(df["revenue"], errors="coerce")

# Convert to category (saves memory for repeated strings)
df["product"] = df["product"].astype("category")

The errors="coerce" parameter in pd.to_numeric converts unparseable values to NaN instead of raising an error. This is essential when data contains mixed formats like "$1,299.99" or "N/A".

Specifying Types During Loading

It is more efficient to specify types during loading rather than converting after:

df = pd.read_csv(
    "sales_data.csv",
    dtype={"product": "category", "quantity": "int32"},
    parse_dates=["date"]
)

parse_dates converts date columns during loading. dtype specifies types upfront. Both avoid the memory overhead of loading as object and converting later.

Handling Currency and Thousands Separators

CSV files often store numbers with formatting: "$1,299.99" or "1.299,99" (European). pandas cannot parse these directly.

# Remove $ and commas, then convert
df["revenue"] = df["revenue"].str.replace("$", "", regex=False)
df["revenue"] = df["revenue"].str.replace(",", "", regex=False)
df["revenue"] = pd.to_numeric(df["revenue"])

For European formats (comma as decimal separator), use the decimal parameter:

df = pd.read_csv("european_data.csv", decimal=",", thousands=".")

Choosing the Right Format

Parquet: The Analyst's Format

Parquet is a columnar binary format that preserves types, compresses efficiently, and reads/writes faster than CSV. It is the preferred format for data exchange between analysts.

# Write to Parquet
df.to_parquet("data.parquet", index=False)

# Read from Parquet
df = pd.read_parquet("data.parquet")

Parquet preserves dtypes (including datetime and category), so no type conversion is needed after loading. Install with pip install pyarrow.

Encoding Issues

Text files may use different encodings. UTF-8 is standard, but Windows-generated files often use cp1252 or latin-1.

# Try different encodings if UTF-8 fails
try:
    df = pd.read_csv("data.csv", encoding="utf-8")
except UnicodeDecodeError:
    df = pd.read_csv("data.csv", encoding="latin-1")

Encoding errors manifest as UnicodeDecodeError or garbled characters (e.g., "cafe" with a mangled accent instead of the correct character). Always verify text data after loading.

A Systematic I/O Checklist

When loading any dataset, follow this checklist: (1) Check df.shape — row and column counts match expectations. (2) Check df.dtypes — types are correct or convertible. (3) Check df.isnull().sum() — missing values are identified. (4) Check df.head() — values look reasonable. This 30-second routine prevents hours of debugging corrupted analysis.