How to Import and Export Data in Pandas

In Pandas, importing data from different file formats is a common task. Pandas offers several functions to read data from CSV, Excel, JSON, SQL databases, HTML, HDF5, Pickle, TSV, fixed‑width files, and the clipboard.

1. Import from CSV

Use read_csv() to load a CSV file into a DataFrame.

import pandas as pd
# 从CSV文件读取数据
df = pd.read_csv('data.csv')
print("从CSV文件读取的数据:")
print(df)

2. Import from Excel

Use read_excel() to load an Excel file.

# 从Excel文件读取数据
df = pd.read_excel('data.xlsx')
print("从Excel文件读取的数据:")
print(df)

3. Import from JSON

Use read_json() to load a JSON file.

# 从JSON文件读取数据
df = pd.read_json('data.json')
print("从JSON文件读取的数据:")
print(df)

4. Import from SQL

Use read_sql() with SQLAlchemy to query a database.

import pandas as pd
from sqlalchemy import create_engine
# 创建数据库连接
engine = create_engine('sqlite:///example.db')
# 从SQL数据库读取数据
df = pd.read_sql('SELECT * FROM people', con=engine)
print("从SQL数据库读取的数据:")
print(df)

5. Import from HTML

Use read_html() to parse tables in an HTML file.

# 从HTML文件读取数据
dfs = pd.read_html('data.html')
# 打印第一个表格
print("从HTML文件读取的第一个表格:")
print(dfs[0])

6. Import from HDF5

Use read_hdf() to load HDF5 files.

# 从HDF5文件读取数据
df = pd.read_hdf('data.h5', key='df')
print("从HDF5文件读取的数据:")
print(df)

7. Import from Pickle

Use read_pickle() to load a Pickle file.

# 从Pickle文件读取数据
df = pd.read_pickle('data.pkl')
print("从Pickle文件读取的数据:")
print(df)

8. Import from TSV (text) files

Use read_table() with a tab separator.

# 从TSV文件读取数据
df = pd.read_table('data.tsv', sep='\t')
print("从TSV文件读取的数据:")
print(df)

9. Import from Fixed‑Width files

Use read_fwf() to read fixed‑width formatted files.

# 从固定宽度文件读取数据
df = pd.read_fwf('data.txt', widths=[10, 10, 10])
print("从固定宽度文件读取的数据:")
print(df)

10. Import from Clipboard

Use read_clipboard() to paste tabular data.

# 从剪贴板读取数据
df = pd.read_clipboard()
print("从剪贴板读取的数据:")
print(df)

Summary of Import

Pandas provides simple functions to import data from a wide range of sources, allowing you to choose the method that best fits your data format.

---

Exporting data from Pandas follows a similar pattern, with functions to write DataFrames to CSV, Excel, JSON, SQL databases, HTML, HDF5, and Pickle files.

1. Export to CSV

import pandas as pd
# 创建一个示例 DataFrame
df = pd.DataFrame({
'Name': ['Alice', 'Bob', 'Charlie'],
'Age': [25, 30, 35],
'City': ['New York', 'Los Angeles', 'Chicago']
})
# 将 DataFrame 导出为 CSV 文件
df.to_csv('output.csv', index=False)
print("已导出为 CSV 文件: output.csv")

2. Export to Excel

# 将 DataFrame 导出为 Excel 文件
df.to_excel('output.xlsx', index=False)
print("已导出为 Excel 文件: output.xlsx")

3. Export to JSON

# 将 DataFrame 导出为 JSON 文件
df.to_json('output.json', orient='records')
print("已导出为 JSON 文件: output.json")

4. Export to SQL

import pandas as pd
from sqlalchemy import create_engine
# 创建一个示例 DataFrame
df = pd.DataFrame({
'Name': ['Alice', 'Bob', 'Charlie'],
'Age': [25, 30, 35],
'City': ['New York', 'Los Angeles', 'Chicago']
})
# 创建数据库连接
engine = create_engine('sqlite:///example.db')
# 将 DataFrame 导出到 SQL 数据库
df.to_sql('people', con=engine, if_exists='replace', index=False)
print("已导出到 SQL 数据库: example.db")

5. Export to HTML

# 将 DataFrame 导出为 HTML 文件
df.to_html('output.html', index=False)
print("已导出为 HTML 文件: output.html")

6. Export to HDF5

# 将 DataFrame 导出为 HDF5 文件
df.to_hdf('output.h5', key='df', mode='w')
print("已导出为 HDF5 文件: output.h5")

7. Export to Pickle

# 将 DataFrame 导出为 Pickle 文件
df.to_pickle('output.pkl')
print("已导出为 Pickle 文件: output.pkl")

Summary of Export

Pandas makes exporting data straightforward, supporting a variety of formats so you can store results in the most suitable file type for downstream use.