Pandas: Data Reading and Writing

Q: Does read_excel require additional libraries?

Yes. .xlsx files require pip install openpyxl , and .xls files require pip install xlrd . Pandas 2.x uses the openpyxl engine by default. If you see a "Missing optional dependency" error, just install the corresponding library.

Q: How do I use chunksize?

pd.read_csv(path, chunksize=N) returns a TextFileReader object. Each iteration yields a DataFrame of N rows. This is ideal for files that exceed available memory — process chunk by chunk, aggregate chunk by chunk, save chunk by chunk. Note: chunksize mode does not support random access; it only reads sequentially.

Q: How do I read only specific columns?

Use the usecols parameter: pd.read_csv(path, usecols=['col1', 'col3']) selects by column name, or usecols=[0, 2, 4] selects by position. Loading only the columns you need can dramatically reduce memory usage, especially for wide tables (100+ columns but you only need 5).

Last updated: 2026-08-26

In real projects, data doesn't just appear in your code out of thin air — it comes from CSV files, Excel spreadsheets, databases, and API endpoints. Pandas provides 20+ IO functions that let you load and save data in various formats with a single line of code. This section covers the 4 most commonly used formats (CSV / Excel / JSON / SQL), along with practical tips for encoding and large file handling.

⚠️ Note: The code below must be run in a local Python environment. Some examples use StringIO to simulate file reading and writing.

1. What You Will Learn

❶ read_csv / to_csv in depth
❷ read_excel / to_excel multi-Sheet handling
❸ read_json / to_json format handling
❹ read_sql / to_sql database interaction
❺ Encoding issues and large file strategies (chunksize)

2. Bob's Encoding Nightmare

(1) The Pain: CSV Opens as Garbled Text

Bob receives a customer data CSV and loads it with default parameters — all the Chinese characters turn into gibberish:

PYTHON

import pandas as pd
# This fails or produces garbled text
# df = pd.read_csv('customers.csv')  # UnicodeDecodeError!

TEXT 📖 Display only

> **Output:** Run this in your local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

The reason: the file is GBK-encoded, but Pandas reads it as UTF-8 by default.

(2) The Fix: Specify encoding

▶ Example: Encoding Handling (Difficulty ⭐)

PYTHON

import pandas as pd
from io import StringIO

# Simulate GBK-encoded CSV
csv_gbk = "name,age,city\nAlice,28,Beijing\nBob,34,Shanghai"
# In real scenario: pd.read_csv('file.csv', encoding='gbk')

# UTF-8 (default) — works for most modern files
csv_utf8 = "name,age,city\nAlice,28,New York\nBob,34,London"
df = pd.read_csv(StringIO(csv_utf8))
print(df)
#    name  age      city
# 0  Alice   28  New York
# 1    Bob   34    London

TEXT 📖 Display only

> **Output:** Run this in your local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

(3) The Payoff: IO Functions Get It Done in One Line

Scenario	One-liner
Read CSV	`pd.read_csv('data.csv')`
Write CSV	`df.to_csv('out.csv', index=False)`
Read Excel	`pd.read_excel('data.xlsx')`
Read SQL	`pd.read_sql('SELECT * FROM t', conn)`

3. CSV Reading and Writing

(1) Common read_csv Parameters

▶ Example

TEXT 📖 Display only

> **Output:** Run this in your local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

: read_csv Parameter Details (Difficulty ⭐⭐)

PYTHON

import pandas as pd
from io import StringIO

csv_data = """product_id,product_name,price,stock,category
P001,Laptop,999.99,50,Electronics
P002,Phone,699.99,120,Electronics
P003,Tablet,349.99,80,Electronics
P004,Monitor,449.99,35,Electronics
P005,Keyboard,79.99,200,Accessories"""

# Basic read
df = pd.read_csv(StringIO(csv_data))
print(df.columns)  # auto-detected from first row

# Key parameters (shown with simulated data)
# pd.read_csv('file.csv',
#     encoding='utf-8',        # file encoding
#     sep=',',                 # delimiter (default comma)
#     header=0,                # row number for column names
#     index_col='product_id',  # column to use as index
#     usecols=['product_name', 'price'],  # only load these columns
#     dtype={'price': float, 'stock': int},  # force types
#     na_values=['N/A', 'NULL'],  # treat these as NaN
#     parse_dates=['order_date'],  # parse as datetime
#     nrows=1000,             # only read first N rows
#     chunksize=5000          # iterate in chunks
# )

# Select specific columns (saves memory)
df_selected = pd.read_csv(StringIO(csv_data), usecols=['product_name', 'price'])
print(df_selected)

TEXT 📖 Display only

> **Output:** Run this in your local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

(2) Saving with to_csv

▶ Example

TEXT 📖 Display only

> **Output:** Run this in your local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

: to_csv Saving (Difficulty ⭐)

PYTHON

import pandas as pd
from io import StringIO

df = pd.DataFrame({
    'name': ['Alice', 'Bob', 'Charlie'],
    'age': [28, 34, 25],
    'city': ['New York', 'London', 'Tokyo']
})

# Save to CSV string (simulating file output)
output = StringIO()
df.to_csv(output, index=False)  # index=False — don't save row numbers
print(output.getvalue())
# name,age,city
# Alice,28,New York
# Bob,34,London
# Charlie,25,Tokyo

# Real file: df.to_csv('output.csv', index=False, encoding='utf-8')

# Append to existing file
# df.to_csv('output.csv', mode='a', header=False, index=False)

TEXT 📖 Display only

> **Output:** Run this in your local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

4. Excel Reading and Writing

(1) read_excel with Multiple Sheets

▶ Example

TEXT 📖 Display only

> **Output:** Run this in your local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

: Excel Multi-Sheet Reading (Difficulty ⭐⭐)

PYTHON

import pandas as pd
from io import BytesIO

# Create a multi-sheet Excel file in memory
buffer = BytesIO()
df1 = pd.DataFrame({'name': ['Alice', 'Bob'], 'score': [85, 92]})
df2 = pd.DataFrame({'product': ['Laptop', 'Phone'], 'price': [999, 699]})

with pd.ExcelWriter(buffer) as writer:
    df1.to_excel(writer, sheet_name='Students', index=False)
    df2.to_excel(writer, sheet_name='Products', index=False)

buffer.seek(0)

# Read specific sheet
students = pd.read_excel(buffer, sheet_name='Students')
print(students)

# Read all sheets as dict
buffer.seek(0)
all_sheets = pd.read_excel(buffer, sheet_name=None)  # dict of DataFrames
print(all_sheets.keys())  # dict_keys(['Students', 'Products'])

# Read by sheet position
buffer.seek(0)
second_sheet = pd.read_excel(buffer, sheet_name=1)  # 0-indexed
print(second_sheet)

TEXT 📖 Display only

> **Output:** Run this in your local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

💡 Tip: read_excel requires an additional engine: pip install openpyxl (for .xlsx) or pip install xlrd (for .xls). Pandas 2.x uses openpyxl by default.

(2) Saving with to_excel

▶ Example

TEXT 📖 Display only

> **Output:** Run this in your local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

: Excel Saving and Formatting (Difficulty ⭐)

PYTHON

import pandas as pd
from io import BytesIO

df = pd.DataFrame({
    'name': ['Alice', 'Bob', 'Charlie'],
    'salary': [75000, 92000, 68000]
})

# Save to single sheet
buffer = BytesIO()
df.to_excel(buffer, sheet_name='Employees', index=False)

# Save multiple DataFrames to different sheets
buffer2 = BytesIO()
with pd.ExcelWriter(buffer2) as writer:
    df.to_excel(writer, sheet_name='Employees', index=False)
    df.describe().to_excel(writer, sheet_name='Statistics')

TEXT 📖 Display only

> **Output:** Run this in your local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

5. JSON Reading and Writing

(1) JSON Format Types

▶ Example

TEXT 📖 Display only

> **Output:** Run this in your local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

: JSON Multi-Format Reading and Writing (Difficulty ⭐⭐)

PYTHON

import pandas as pd
from io import StringIO

# orient='records' (most common for APIs)
json_records = '[{"name":"Alice","age":28},{"name":"Bob","age":34}]'
df = pd.read_json(StringIO(json_records), orient='records')
print(df)
#     name  age
# 0  Alice   28
# 1    Bob   34

# orient='columns' (column-oriented)
json_cols = '{"name":{"0":"Alice","1":"Bob"},"age":{"0":28,"1":34}}'
df2 = pd.read_json(StringIO(json_cols), orient='columns')
print(df2)

# orient='index' (row-oriented)
json_idx = '{"0":{"name":"Alice","age":28},"1":{"name":"Bob","age":34}}'
df3 = pd.read_json(StringIO(json_idx), orient='index')
print(df3)

# Write to JSON
output = df.to_json(orient='records', indent=2)
print(output)

TEXT 📖 Display only

> **Output:** Run this in your local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

(2) JSON orient Comparison

orient	Structure	Use Case
records	`[{col:val}, ...]`	API responses (most common)
columns	`{col:{idx:val}}`	Column-oriented
index	`{idx:{col:val}}`	Row-oriented
split	`{index:[], columns:[], data:[[]]}`	Full decomposition
values	`[[v1,v2], ...]`	Raw data without labels

6. SQL Reading and Writing

(1) read_sql Database Queries

▶ Example

TEXT 📖 Display only

> **Output:** Run this in your local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

: SQL Database Interaction (Difficulty ⭐⭐)

PYTHON

import pandas as pd
import sqlite3
from io import StringIO

# Create in-memory SQLite database (for demo)
conn = sqlite3.connect(':memory:')

# Write data to SQL
df = pd.DataFrame({
    'name': ['Alice', 'Bob', 'Charlie'],
    'department': ['Sales', 'Engineering', 'Marketing'],
    'salary': [75000, 92000, 68000]
})
df.to_sql('employees', conn, if_exists='replace', index=False)

# Read entire table
df_read = pd.read_sql('SELECT * FROM employees', conn)
print(df_read)

# Read with SQL query
high_salary = pd.read_sql(
    'SELECT name, salary FROM employees WHERE salary > 70000',
    conn
)
print(high_salary)
#      name  salary
# 0   Alice   75000
# 1     Bob   92000

# Read with parameters (safe from SQL injection)
dept = 'Engineering'
result = pd.read_sql(
    'SELECT * FROM employees WHERE department = ?',
    conn,
    params=[dept]
)
print(result)

conn.close()

TEXT 📖 Display only

> **Output:** Run this in your local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

💡 Tip: read_sql supports all Python DB-API compatible databases including SQLite / PostgreSQL / MySQL / SQL Server. You need to install the corresponding driver: pip install psycopg2 (PostgreSQL), pip install pymysql (MySQL).

7. Large File Strategies

(1) Reading in Chunks with chunksize

▶ Example

TEXT 📖 Display only

> **Output:** Run this in your local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

: chunksize Large File Handling (Difficulty ⭐⭐)

PYTHON

import pandas as pd
from io import StringIO

# Simulate a large CSV
csv_large = "id,value\n" + "\n".join([f"{i},{i*10}" for i in range(100)])

# Read in chunks of 30 rows
chunks = pd.read_csv(StringIO(csv_large), chunksize=30)

total_sum = 0
total_count = 0

for chunk in chunks:
    total_sum += chunk['value'].sum()
    total_count += len(chunk)

print(f"Total rows: {total_count}")
print(f"Sum of values: {total_sum}")

# Alternative: process and save each chunk
# for i, chunk in enumerate(chunks):
#     chunk.to_csv(f'chunk_{i}.csv', index=False)

TEXT 📖 Display only

> **Output:** Run this in your local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

(2) Loading Only Selected Columns to Save Memory

▶ Example

TEXT 📖 Display only

> **Output:** Run this in your local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

: Selective Column Loading (Difficulty ⭐)

PYTHON

import pandas as pd
from io import StringIO

csv_data = """id,name,age,salary,department,address,phone
1,Alice,28,75000,Sales,123 Main St,555-0100
2,Bob,34,92000,Engineering,456 Oak Ave,555-0200
3,Charlie,25,68000,Marketing,789 Pine Rd,555-0300"""

# Only load columns you need
df = pd.read_csv(StringIO(csv_data), usecols=['name', 'salary', 'department'])
print(df)
# Also works: usecols=[1, 3, 4] (by position)

TEXT 📖 Display only

> **Output:** Run this in your local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

(3) IO Format Comparison

Format	Read	Write	Speed	Size	Best For
CSV	read_csv	to_csv	Medium	Medium	General exchange
Excel	read_excel	to_excel	Slow	Large	Business reports
JSON	read_json	to_json	Slow	Large	API / Web
SQL	read_sql	to_sql	Slow	—	Databases
Parquet	read_parquet	to_parquet	Fast	Small	Big data storage
HDF5	read_hdf	to_hdf	Fast	Small	Scientific data
Feather	read_feather	to_feather	Fastest	Small	Temporary storage

8. Complete Example: Load → Clean → Export in Multiple Formats

(5) ▶ IO Decision Tree

graph TB
    A[Data Source] --> B{File size?}
    B -->|Small| C[CSV / Excel / JSON]
    B -->|Medium| D[CSV + chunksize]
    B -->|Large| E[Parquet / Feather]
    A --> F{Need a database?}
    F -->|Yes| G[read_sql / to_sql]
    F -->|No| H{Need an API?}
    H -->|Yes| I[read_json]
    H -->|No| J[read_csv]

TEXT 📖 Display only

> **Output:** Run this in your local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

▶ Example

TEXT 📖 Display only

> **Output:** Run this in your local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

: Full IO Pipeline (Difficulty ⭐⭐⭐)

PYTHON

import pandas as pd
import sqlite3
from io import StringIO, BytesIO

# ============================================
# Comprehensive example: Full IO pipeline
# Load CSV → Clean → Export CSV/Excel/JSON/SQL
# ============================================

# 1. Load from CSV
csv_data = """name,age,salary,department,hire_date
Alice,28,75000,Sales,2022-03-15
Bob,34,92000,Engineering,2019-08-01
Charlie,25,NaN,Marketing,2023-01-10
Carol,30,88000,Sales,2020-06-20
David,45,105000,Management,2015-11-01"""
df = pd.read_csv(StringIO(csv_data), na_values=['NaN'])

# 2. Clean data
df['salary'] = df['salary'].fillna(df['salary'].median())
df['hire_date'] = pd.to_datetime(df['hire_date'])
print("=== Cleaned Data ===")
print(df)

# 3. Export to CSV
csv_out = StringIO()
df.to_csv(csv_out, index=False)
print(f"\n✅ CSV export: {len(csv_out.getvalue())} characters")

# 4. Export to Excel
excel_out = BytesIO()
with pd.ExcelWriter(excel_out) as writer:
    df.to_excel(writer, sheet_name='Employees', index=False)
    df.describe().to_excel(writer, sheet_name='Stats')
print(f"✅ Excel export: {len(excel_out.getvalue())} bytes, 2 sheets")

# 5. Export to JSON
json_out = df.to_json(orient='records', indent=2, date_format='iso')
print(f"✅ JSON export: {len(json_out)} characters")

# 6. Export to SQL
conn = sqlite3.connect(':memory:')
df.to_sql('employees', conn, if_exists='replace', index=False)
df_from_sql = pd.read_sql('SELECT * FROM employees', conn)
print(f"✅ SQL round-trip: {len(df_from_sql)} rows")
conn.close()

TEXT 📖 Display only

> **Output:** Run this in your local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

❓ FAQ

Q What do I do about CSV encoding errors?

A Common encodings include UTF-8 (modern default), GBK/GB2312 (Chinese Windows), and ISO-8859-1 (Western European). Try encoding='utf-8' first; if that fails, use encoding='gbk'. A universal fallback is encoding='latin1' (it won't throw an error but may produce garbled text). You can also use the chardet library to auto-detect the encoding.

Q Does read_excel require additional libraries?

A Yes. .xlsx files require pip install openpyxl, and .xls files require pip install xlrd. Pandas 2.x uses the openpyxl engine by default. If you see a "Missing optional dependency" error, just install the corresponding library.

Q How many JSON formats are there?

A There are 5 orient modes — records (most common for APIs), columns (column-oriented), index (row-oriented), split (full decomposition), and values (raw data). Use records for API interaction, and columns or split for internal Pandas storage.

Q How do I use chunksize?

A pd.read_csv(path, chunksize=N) returns a TextFileReader object. Each iteration yields a DataFrame of N rows. This is ideal for files that exceed available memory — process chunk by chunk, aggregate chunk by chunk, save chunk by chunk. Note: chunksize mode does not support random access; it only reads sequentially.

Q How do I read only specific columns?

A Use the usecols parameter: pd.read_csv(path, usecols=['col1', 'col3']) selects by column name, or usecols=[0, 2, 4] selects by position. Loading only the columns you need can dramatically reduce memory usage, especially for wide tables (100+ columns but you only need 5).

Q What's the difference between Parquet and CSV?

A Parquet is a columnar storage format that reads 5-50x faster than CSV and produces files 50-80% smaller (thanks to built-in compression). However, Parquet is not plain text (requires pip install pyarrow) and isn't suitable for direct human inspection. Recommendation: use CSV for exchanging raw data, and Parquet for storing processed data.

Q What does index=False mean in to_csv?

A By default, to_csv writes the DataFrame's Index as the first column. In most cases you don't need to save the default RangeIndex(0,1,2,...), so use index=False to omit it. If your Index carries meaningful labels (such as dates), you can keep it with index=True.

📖 Summary

read_csv is the most commonly used IO function. Key parameters: encoding / sep / usecols / dtype / parse_dates
When saving with to_csv, use index=False to avoid writing meaningless row numbers
Excel reading and writing requires openpyxl and supports multi-Sheet operations (via the sheet_name parameter)
JSON has 5 orient modes; use records for API interaction
SQL reading and writing works through DB-API connections and supports parameterized queries to prevent injection
For large files, use chunksize for chunked reading + usecols for selective column loading
Parquet/HDF5/Feather are 5-50x faster than CSV and ideal for big data storage

📝 Exercises

Basic (Difficulty ⭐): Use StringIO to simulate a CSV file, load it with read_csv (specifying dtype and usecols), then save it with to_csv (index=False).
Intermediate (Difficulty ⭐⭐): Create 2 DataFrames, write them to different Sheets in the same Excel file using ExcelWriter, then read all Sheets back using read_excel with sheet_name=None.
Challenge (Difficulty ⭐⭐⭐): Simulate a 1000-row CSV (containing NaN values and a date column), then complete the following: read_csv (with na_values/parse_dates/dtype) → fill missing values → export in three formats: to_csv/to_json/to_sql → read back with read_json to verify consistency.

← Previous: Data Selection · Next: Missing Value Handling →

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us

Pandas: Data Reading and Writing

1. What You Will Learn

2. Bob's Encoding Nightmare

(1) The Pain: CSV Opens as Garbled Text

(2) The Fix: Specify encoding

▶ Example: Encoding Handling (Difficulty ⭐)

(3) The Payoff: IO Functions Get It Done in One Line

3. CSV Reading and Writing

(1) Common read_csv Parameters

▶ Example

(2) Saving with to_csv

▶ Example

4. Excel Reading and Writing

(1) read_excel with Multiple Sheets

▶ Example

(2) Saving with to_excel

▶ Example

5. JSON Reading and Writing

(1) JSON Format Types

▶ Example

(2) JSON orient Comparison

6. SQL Reading and Writing

(1) read_sql Database Queries

▶ Example

7. Large File Strategies

(1) Reading in Chunks with chunksize

▶ Example

(2) Loading Only Selected Columns to Save Memory

▶ Example

(3) IO Format Comparison

8. Complete Example: Load → Clean → Export in Multiple Formats

(5) ▶ IO Decision Tree

▶ Example

❓ FAQ

📖 Summary

📝 Exercises

🙏 帮我们做得更好