Pandas Data Reading and Writing

Pandas provides a rich set of functions to read and write various data formats. In addition to the commonly used CSV and Excel, it also supports SQL databases, HTML tables, Parquet, and other formats. This section introduces these supplementary I/O functions to help you flexibly handle data import and export in different scenarios.

Core I/O Functions Overview

Pandas' I/O functionality is very powerful and supports reading and writing of multiple data formats. The following are commonly used read and write functions:

Read Functions Write Functions Supported Formats Typical Scenarios
pd.read_csv() to_csv() CSV、TSV Log files, tabular data
pd.read_excel() to_excel() Excel（.xlsx, .xls） Business reports, financial data
pd.read_sql() to_sql() SQL databases Enterprise database interaction
pd.read_html() - HTML tables Web data scraping
pd.read_parquet() to_parquet() Apache Parquet Big data analysis, storage
pd.read_feather() to_feather() Feather format Fast read/write, in-memory data
pd.read_json() to_json() JSON API data, Web services
pd.read_pickle() to_pickle() Python pickle Serialize Python objects

Different formats have different applicable scenarios. CSV is a universal format but files are large; Parquet is columnar storage, suitable for big data analysis; Excel is suitable for manual viewing but not for large data volumes.

CSV and Text Files

CSV (Comma-Separated Values) is the most universal data exchange format, and Pandas provides the most complete support for it.

Common Reading Parameters

Example

import pandas as pd

# Basic reading
df = pd.read_csv("data.csv")

# Specify the delimiter (CSV defaults to comma, TSV to tab)
df_tsv = pd.read_csv("data.tsv", sep="\t")

# Specify encoding (commonly used for Chinese files)
df_utf8 = pd.read_csv("data.csv", encoding="utf-8")
df_gbk = pd.read_csv("data.csv", encoding="gbk")

# Skip rows (skip rows after the header or comment lines)
df = pd.read_csv("data.csv", skiprows=3)  # Skip the first 3 rows
df = pd.read_csv("data.csv", skiprows=[2, 4])  # Skip rows 2 and 4

# Use the header row (first row by default)
df = pd.read_csv("data.csv", header=0)  # Use row 0 as the header
df = pd.read_csv("data.csv", header=None)  # Do not use a header, auto-generate 0,1,2...

# Specify column names
df = pd.read_csv("data.csv", names=["ID", "Name", "Age", "City"])

# Specify the index column
df = pd.read_csv("data.csv", index_col=0)  # Use column 0 as the index
df = pd.read_csv("data.csv", index_col=["Name"])  # Multiple columns as a compound index

Handling Missing Values

Example

import pandas as pd
import numpy as np

# Specify which values are treated as missing values
df = pd.read_csv(
    "data.csv",
    na_values=["NA", "null", "NULL", "N/A", "", " "]  # These values will all be recognized as NaN
)

# Different columns can use different missing value markers
df = pd.read_csv(
    "data.csv",
    na_values={
        "Age": ["Unknown", "0"],  # Missing value for the "Age" column
        "City": ["Unknown"]        # Missing value for the "City" column
    }
)

# Keep certain values as normal values (not recognized as missing values)
df = pd.read_csv("data.csv", keep_default_na=False)

Writing CSV Files

Example

import pandas as pd

# Prepare test data
df = pd.DataFrame({
    "Name": ["Zhang San", "Li Si", "Wang Wu"],
    "Age": [25, 30, 28],
    "City": ["Beijing", "Shanghai", "Guangzhou"]
})

# Write CSV (with index by default)
df.to_csv("output.csv")

# Do not write the index
df.to_csv("output.csv", index=False)

# Specify the delimiter
df.to_csv("output.csv", sep="\t")

# Do not write the header
df.to_csv("output.csv", header=False)

# Specify the encoding
df.to_csv("output.csv", encoding="utf-8-sig")  # With BOM, suitable for opening in Excel

JSON Files

JSON (JavaScript Object Notation) is the most common data format in Web applications. Pandas provides flexible read and write functions.

JSON Structure and Reading Methods

JSON data structures come in many forms. When reading, you need to choose appropriate parameters based on the actual structure:

Example

import pandas as pd
import json

# Prepare test JSON data
data_records = '''
[
{"name": "Zhang San", "age": 25, "city": "Beijing"},
{"name": "Li Si", "age": 30, "city": "Shanghai"},
{"name": "Wang Wu", "age": 28, "city": "Guangzhou"}
]
'''

# Method 1: JSON array (one object per row) -> DataFrame
df = pd.read_json(data_records, orient="records")
print("orient='records':")
print(df)
print()

# Method 2: JSON object (key-value pairs) -> Series
data_dict = '{"name": "Zhang San", "age": 25, "city": "Beijing"}'
s = pd.read_json(data_dict, typ="series")
print("Read as Series:")
print(s)

JSON Lines Format

JSON Lines (.jsonl) is a format where each line is a complete JSON object, commonly used in log and big data scenarios:

Example

import pandas as pd

# JSON Lines format data
jsonl_data = '''{"name": "Zhang San", "age": 25}
{"name": "Li Si", "age": 30}
{"name": "Wang Wu", "age": 28}
{"name": "Zhao Liu", "age": 35}
'''

# Write JSON Lines file
with open("data.jsonl", "w", encoding="utf-8") as f:
    f.write(jsonl_data)

# Read JSON Lines (each line is a JSON object)
df = pd.read_json("data.jsonl", lines=True)
print(df)

Writing JSON

Example

import pandas as pd

df = pd.DataFrame({
    "Name": ["Zhang San", "Li Si", "Wang Wu"],
    "Age": [25, 30, 28],
    "City": ["Beijing", "Shanghai", "Guangzhou"]
})

# Write JSON with different orient
df.to_json("output_records.json", orient="records", force_ascii=False, indent=2)
df.to_json("output_index.json", orient="index", force_ascii=False, indent=2)
df.to_json("output_columns.json", orient="columns", force_ascii=False, indent=2)

# JSON Lines format
df.to_json("output.jsonl", orient="records", lines=True, force_ascii=False)

Pickle Serialization

Pickle is Python's native object serialization format, which can save any Python object, including DataFrames and complex nested structures.

Example

import pandas as pd
import pickle

# Create a DataFrame with a complex structure
df = pd.DataFrame({
    "A": range(10),
    "B": range(10, 20),
    "C": ["foo", "bar"] * 5
})

# Write a Pickle file
df.to_pickle("data.pkl")

# Read a Pickle file
df_loaded = pd.read_pickle("data.pkl")
print(df_loaded)

# Compressed write (gzip compression, smaller file)
df.to_pickle("data.pkl.gz", compression="gzip")
df_loaded = pd.read_pickle("data.pkl.gz", compression="gzip")

The Pickle format can only be read by Python and is not suitable for cross-language data exchange. In addition, loading Pickle files from untrusted sources poses a security risk; do not load .pkl files of unknown origin.

Performance and Scenario Selection

Different data formats have different performance characteristics. Choosing the appropriate format can greatly improve efficiency:

Format Read Speed Write Speed File Size Applicable Scenario
CSV Slow Medium big Universal exchange format, human-readable
Parquet Very fast Fast Very small Big data analysis, columnar query
Feather Extremely fast Extremely fast big Fast in-memory data transfer
Pickle Fast Fast Medium Python object persistence
JSON Slow Medium Very large Web API, cross-language

Common Issues and Precautions

1. Insufficient memory when reading large files

For very large CSV or JSON files, you can use thechunksizeparameter to read in chunks, avoiding loading everything into memory at once.

2. Chinese encoding issues

When processing Chinese files, make sure to specify the correct encoding. Common encodings include utf-8, gbk, gb2312, utf-8-sig (BOM), etc.

3. File corrupted after writing

Before writing important data, it is recommended to read and verify first. When writing large files, using a compressed format can reduce disk I/O and lower the risk of data corruption.

4. Excel Format Limitations

Excel 2003 (.xls) supports a maximum of 65,536 rows per sheet, while Excel 2007 (.xlsx) supports up to 1,048,576 rows. If the limit is exceeded, please consider other formats.

Other Extensions