Pandas Data Export

Pandas provides rich export functionality, allowing DataFrame to be exported to various common formats, including CSV, Excel, SQL databases, JSON, and more. This section details the usage and considerations for each export method.

Export to CSV

CSV is the most common data exchange format. Exports are affected by the environment's default encoding, so you need to pay attention to Chinese encoding issues.

Basic Export

Example

import pandas as pd

# Prepare test data
df = pd.DataFrame({
    "Name": ["Zhang San", "Li Si", "Wang Wu", "Zhao Liu"],
    "Age": [25, 30, 28, 35],
    City: ["Beijing", "Shanghai", Guangzhou, Shenzhen],
    Salary: [12000, 15000, 11000, 18000]
})

# The most basic export (including index)
df.to_csv("output.csv")

# Without index
df.to_csv("output.csv", index=False)

Chinese Encoding Handling

Example

import pandas as pd

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

# UTF-8 encoding (recommended)
df.to_csv("output_utf8.csv", encoding="utf-8")

# UTF-8 with BOM (Excel opens without garbled characters)
df.to_csv("output_utf8_bom.csv", encoding="utf-8-sig")

# GBK encoding (suitable for legacy systems)
df.to_csv("output_gbk.csv", encoding="gbk")

# Verify encoding
import os
print("File Size Comparison:")
for f in ["output_utf8.csv", "output_utf8_bom.csv", "output_gbk.csv"]:
    print(f"{f}: {os.path.getsize(f)} bytes")

Export Options Explained

Example

import pandas as pd

df = pd.DataFrame({
    "Name": ["Zhang San", "Li Si"],
    "Age": [25, 30]
})

# Specify the delimiter (default is comma)
df.to_csv("output.tsv", sep="\t")  # TSV format

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

# Custom column names (when header=False)
df.to_csv("output.csv", header=False, columns=["Name", "Age"])

# Export only specific columns
df.to_csv("output.csv", columns=["Name"])  # Export only the "Name" column

# Missing value handling
import numpy as np
df_with_na = pd.DataFrame({
    "A": [1, 2, np.nan, 4],
    "B": ["a", None, "c", "d"]
})
df_with_na.to_csv("output.csv", na_rep="NULL")  # Specify missing value representation

# Float precision
df = pd.DataFrame({"value": [1.23456789, 2.3456789]})
df.to_csv("output.csv", float_format="%.2f")  # Keep 2 decimal places

Export to Excel

Excel format is suitable for manual viewing and editing, but exporting large files is slower.

Install Dependencies

pip install openpyxl xlwt

Basic Export

Example

import pandas as pd

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

# Export to Excel (.xlsx format, requires openpyxl)
df.to_excel("output.xlsx", index=False)

# Export to legacy .xls format (requires xlwt)
df.to_excel("output.xls", index=False)

Multi-Sheet Export

Example

import pandas as pd

# Create multiple DataFrames
df1 = pd.DataFrame({"A": [1, 2, 3], "B": [4, 5, 6]})
df2 = pd.DataFrame({"C": [7, 8, 9], "D": [10, 11, 12]})

# Export to different Sheets in the same Excel
with pd.ExcelWriter("output.xlsx", engine="openpyxl") as writer:
    df1.to_excel(writer, sheet_name="Sheet1", index=False)
    df2.to_excel(writer, sheet_name="Sheet2", index=False)

print(Multiple Sheets Exported Successfully)

Formatted Export

Example

import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment

# Create DataFrame
df = pd.DataFrame({
    "Name": ["Zhang San", "Li Si", "Wang Wu"],
    "Age": [25, 30, 28],
    Salary: [12000, 15000, 11000]
})

# Use ExcelWriter for finer control
with pd.ExcelWriter("output_formatted.xlsx", engine="openpyxl") as writer:
    df.to_excel(writer, sheet_name="Employee Information", index=False)

    # Get the worksheet
    worksheet = writer.sheets["Employee Information"]

    # Set column width
    worksheet.column_dimensions["A"].width = 15
    worksheet.column_dimensions["B"].width = 10
    worksheet.column_dimensions["C"].width = 12

    # Freeze the first row
    worksheet.freeze_panes = "A2"

print("Formatted Export Completed")

Export to Database

You can directly export a DataFrame to a SQL database. See the chapter "Pandas Reading SQL Database" for details.

Example

import pandas as pd
from sqlalchemy import create_engine

# Create an in-memory database
engine = create_engine("sqlite:///output.db")

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

# Export to the database
# if_exists: 'fail' (default) / 'replace' / 'append'
df.to_sql("users", con=engine, if_exists="replace", index=False)

print("Export to database succeeded")

Export to JSON

Example

import pandas as pd

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

# Different orient formats
df.to_json("records.json", orient="records", force_ascii=False, indent=2)
df.to_json("index.json", orient="index", force_ascii=False)
df.to_json("columns.json", orient="columns", force_ascii=False)

# JSON Lines format (one JSON object per line, suitable for logs)
df.to_json("lines.json", orient="records", lines=True, force_ascii=False)

Export to Other Formats

Export to HTML

Example

import pandas as pd

df = pd.DataFrame({
    "Name": ["Zhang San", "Li Si"],
    "Age": [25, 30]
})

# Export to HTML table
df.to_html("output.html", index=False)

# Add styles
df.to_html(
    "output_styled.html",
    index=False,
    border=2,
    classes=["table", "table-striped"]  # Add CSS classes
)

Export to Markdown

Example

import pandas as pd

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

# Export to Markdown table
print(df.to_markdown(index=False))
# Output:
# | Name | Age |
# | ---- | ---- |
# | Zhang San | 25 |
# | Li Si | 30 |
# | Wang Wu | 28 |

# Save to file
with open("output.md", "w") as f:
    f.write(df.to_markdown(index=False))

Export to LaTeX

Example

import pandas as pd

df = pd.DataFrame({
    "Name": ["Zhang San", "Li Si"],
    "Age": [25, 30]
})

# Export to LaTeX table
print(df.to_latex(index=False))
# Output LaTeX table code

Export to Pickle

Example

import pandas as pd

df = pd.DataFrame({
    "A": [1, 2, 3],
    "B": ["a", "b", "c"]
})

# Export to Pickle (preserve data types)
df.to_pickle("data.pkl")

# Compressed format
df.to_pickle("data.pkl.gz", compression="gzip")

print("Pickle export completed")

Large Data Export

When exporting large amounts of data, you need to pay attention to memory and performance issues.

Example

import pandas as pd

# Recommendations for large data export:
# 1. Use chunksize to write in batches
df = pd.DataFrame({"a": range(1000000), "b": range(1000000)})

# Write CSV in chunks
csv_path = "large_output.csv"
df.to_csv(csv_path, index=False, chunksize=100000)

# 2. Use a more efficient format (Parquet)
df.to_parquet("large_output.parquet", index=False)

# 3. Use compressed format to reduce IO
df.to_csv("large_output.csv.gz", index=False, compression="gzip")

print("Large data volume export complete")

Common Issues and Considerations

1. Excel opens CSV files with garbled text

Useutf-8-sigExport with encoding, Excel can correctly recognize Chinese.

2. Insufficient memory when exporting large data

UsechunksizeWrite parameters in batches, or use Parquet format instead of CSV.

3. Date and time format lost

When exporting CSV, it will be converted to a string. You can specifydate_formatparameters to control the format.

4. Type mismatch when exporting to database

You can usedtypeparameters to explicitly specify column types.

For daily data analysis, the Parquet format is recommended. It has advantages such as high compression ratio, fast read/write, and preserving data types. Only use Excel or CSV formats when manual viewing or sharing is needed.

Other extensions