Cách chỉnh sửa file excel trong python pandas

Trong phần này, tôi sẽ trình bày cách sử dụng thư viện Pandas với Excel. Chúng tôi sẽ sử dụng các thao tác bảng excel cơ bản như tạo trang tính mới, thêm dữ liệu hàng loạt, nối thêm dữ liệu, đọc dữ liệu, định dạng dữ liệu và thêm biểu đồ

Cài đặt

Thực hiện các lệnh sau để cài đặt các thư viện Pandas, xlsxwriter, xlrd và openpyxl

pip install pandaspip install xlsxwriterpip install xlrdpip install openpyxl

Tạo một bảng tính Excel

import pandas as pdwriter = pd.ExcelWriter['demo.xlsx', engine='xlsxwriter']writer.close[]

Mã này sẽ tạo một tệp

import pandas as pd

# dataframe Name and Age columns
df = pd.DataFrame[{'Name': ['A', 'B', 'C', 'D'],
                   'Age': [10, 0, 30, 50]}]

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter['demo.xlsx', engine='xlsxwriter']

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel[writer, sheet_name='Sheet1', index=False]

# Close the Pandas Excel writer and output the Excel file.
writer.close[]

6 mới với trang tính mặc định có tên là

import pandas as pd

# dataframe Name and Age columns
df = pd.DataFrame[{'Name': ['A', 'B', 'C', 'D'],
                   'Age': [10, 0, 30, 50]}]

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter['demo.xlsx', engine='xlsxwriter']

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel[writer, sheet_name='Sheet1', index=False]

# Close the Pandas Excel writer and output the Excel file.
writer.close[]

7

Xem ví dụ đầy đủ tại Ví dụ. Ví dụ Pandas Excel

Truy cập XlsxWriter từ Pandas

Để áp dụng các tính năng của XlsxWriter như Biểu đồ, Định dạng có điều kiện và Định dạng cột cho đầu ra của Pandas, chúng ta cần truy cập các đối tượng bảng tính và sổ làm việc bên dưới. Sau đó, chúng ta có thể coi chúng như các đối tượng XlsxWriter bình thường

Tiếp tục từ ví dụ trên, chúng tôi làm điều đó như sau

import pandas as pd

# Create a Pandas dataframe from the data.
df = pd.DataFrame[{'Data': [10, 20, 30, 20, 15, 30, 45]}]

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter['pandas_simple.xlsx', engine='xlsxwriter']

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel[writer, sheet_name='Sheet1']

# Get the xlsxwriter objects from the dataframe writer object.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

Điều này tương đương với đoạn mã sau khi sử dụng riêng XlsxWriter

workbook  = xlsxwriter.Workbook['filename.xlsx']
worksheet = workbook.add_worksheet[]

Các đối tượng Workbook và Worksheet sau đó có thể được sử dụng để truy cập các tính năng khác của XlsxWriter, xem bên dưới

Thêm biểu đồ vào đầu ra Dataframe

Khi chúng ta có các đối tượng Workbook và Worksheet, như đã trình bày trong phần trước, chúng ta có thể sử dụng chúng để áp dụng các tính năng khác, chẳng hạn như thêm biểu đồ

________số 8

Đầu ra sẽ trông như thế này

Xem ví dụ đầy đủ tại Ví dụ. Đầu ra Pandas Excel với một biểu đồ

Định dạng đầu ra Dataframe

XlsxWriter và Pandas cung cấp rất ít hỗ trợ để định dạng dữ liệu đầu ra từ khung dữ liệu ngoài định dạng mặc định, chẳng hạn như ô tiêu đề và ô chỉ mục và bất kỳ ô nào chứa ngày tháng hoặc thời gian. Ngoài ra, không thể định dạng bất kỳ ô nào đã áp dụng định dạng mặc định

Nếu bạn yêu cầu định dạng được kiểm soát chặt chẽ của đầu ra khung dữ liệu thì có lẽ tốt hơn hết bạn nên sử dụng trực tiếp Xlsxwriter với dữ liệu thô được lấy từ Pandas. Tuy nhiên, một số tùy chọn định dạng có sẵn

Ví dụ: có thể đặt định dạng ngày và giờ mặc định thông qua giao diện Pandas

writer = pd.ExcelWriter["pandas_datetime.xlsx",
                        engine='xlsxwriter',
                        datetime_format='mmm d yyyy hh:mm:ss',
                        date_format='mmmm dd yyyy']

Cái nào sẽ cho

Xem ví dụ đầy đủ tại Ví dụ. Đầu ra Pandas Excel với datetimes

Có thể định dạng bất kỳ dữ liệu cột nào khác, không phải ngày/giờ bằng cách sử dụng

import pandas as pd

# Create a Pandas dataframe from the data.
df = pd.DataFrame[{'Data': [10, 20, 30, 20, 15, 30, 45]}]

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter['pandas_simple.xlsx', engine='xlsxwriter']

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel[writer, sheet_name='Sheet1']

# Get the xlsxwriter objects from the dataframe writer object.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

8

import pandas as pdwriter = pd.ExcelWriter['demo.xlsx', engine='xlsxwriter']writer.close[]

1

Xem ví dụ đầy đủ tại Ví dụ. Đầu ra Pandas Excel với định dạng cột

Thêm một khung dữ liệu vào bảng trang tính

Như đã giải thích trong Làm việc với Bảng Trang tính, các bảng trong Excel là một cách để nhóm một dải ô thành một thực thể duy nhất, như thế này

Cách để thực hiện việc này với khung dữ liệu Pandas trước tiên là ghi dữ liệu mà không có chỉ mục hoặc tiêu đề và bằng cách bắt đầu chuyển tiếp 1 hàng để có khoảng trống cho tiêu đề bảng

import pandas as pdwriter = pd.ExcelWriter['demo.xlsx', engine='xlsxwriter']writer.close[]

2

Sau đó, chúng tôi tạo một danh sách các tiêu đề để sử dụng trong

import pandas as pd

# Create a Pandas dataframe from the data.
df = pd.DataFrame[{'Data': [10, 20, 30, 20, 15, 30, 45]}]

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter['pandas_simple.xlsx', engine='xlsxwriter']

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel[writer, sheet_name='Sheet1']

# Get the xlsxwriter objects from the dataframe writer object.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

9

import pandas as pdwriter = pd.ExcelWriter['demo.xlsx', engine='xlsxwriter']writer.close[]

4

Cuối cùng, chúng tôi thêm cấu trúc bảng Excel, dựa trên hình dạng khung dữ liệu và với các tiêu đề cột mà chúng tôi đã tạo từ các cột khung dữ liệu

import pandas as pdwriter = pd.ExcelWriter['demo.xlsx', engine='xlsxwriter']writer.close[]

5

Xem ví dụ đầy đủ tại Ví dụ. Đầu ra Pandas Excel với bảng trang tính

Thêm bộ lọc tự động vào đầu ra Dataframe

Như đã giải thích trong Làm việc với bộ lọc tự động, bộ lọc tự động trong Excel là cách lọc phạm vi dữ liệu 2d để chỉ hiển thị các hàng phù hợp với tiêu chí do người dùng xác định

Cách để thực hiện việc này với khung dữ liệu Pandas trước tiên là ghi dữ liệu mà không có chỉ mục [trừ khi bạn muốn đưa nó vào dữ liệu đã lọc]

import pandas as pdwriter = pd.ExcelWriter['demo.xlsx', engine='xlsxwriter']writer.close[]

6

Sau đó, chúng tôi lấy hình dạng khung dữ liệu và thêm bộ lọc tự động

import pandas as pd

# Create a Pandas dataframe from the data.
df = pd.DataFrame[{'Data': [10, 20, 30, 20, 15, 30, 45]}]

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter['pandas_simple.xlsx', engine='xlsxwriter']

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel[writer, sheet_name='Sheet1']

# Get the xlsxwriter objects from the dataframe writer object.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

0

Chúng tôi cũng có thể thêm một tiêu chí lọc tùy chọn. Phần giữ chỗ “Vùng” trong bộ lọc bị bỏ qua và có thể là bất kỳ chuỗi nào làm tăng thêm sự rõ ràng cho biểu thức

import pandas as pd

# Create a Pandas dataframe from the data.
df = pd.DataFrame[{'Data': [10, 20, 30, 20, 15, 30, 45]}]

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter['pandas_simple.xlsx', engine='xlsxwriter']

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel[writer, sheet_name='Sheet1']

# Get the xlsxwriter objects from the dataframe writer object.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

1

Tuy nhiên, nếu chỉ áp dụng các tiêu chí là chưa đủ. Các hàng không khớp cũng phải được ẩn. Chúng tôi sử dụng Pandas để tìm hàng nào cần ẩn

import pandas as pd

# Create a Pandas dataframe from the data.
df = pd.DataFrame[{'Data': [10, 20, 30, 20, 15, 30, 45]}]

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter['pandas_simple.xlsx', engine='xlsxwriter']

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel[writer, sheet_name='Sheet1']

# Get the xlsxwriter objects from the dataframe writer object.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

2

Điều này mang lại cho chúng tôi một bảng tính được lọc như thế này

Xem ví dụ đầy đủ tại Ví dụ. Đầu ra Pandas Excel với bộ lọc tự động

Xử lý nhiều Pandas Dataframes

Có thể ghi nhiều hơn một khung dữ liệu vào một trang tính hoặc nhiều trang tính. Ví dụ: để ghi nhiều khung dữ liệu vào nhiều trang tính

import pandas as pd

# Create a Pandas dataframe from the data.
df = pd.DataFrame[{'Data': [10, 20, 30, 20, 15, 30, 45]}]

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter['pandas_simple.xlsx', engine='xlsxwriter']

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel[writer, sheet_name='Sheet1']

# Get the xlsxwriter objects from the dataframe writer object.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

3

Xem ví dụ đầy đủ tại Ví dụ. Pandas Excel với nhiều khung dữ liệu

Cũng có thể định vị nhiều khung dữ liệu trong cùng một trang tính

import pandas as pd

# Create a Pandas dataframe from the data.
df = pd.DataFrame[{'Data': [10, 20, 30, 20, 15, 30, 45]}]

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter['pandas_simple.xlsx', engine='xlsxwriter']

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel[writer, sheet_name='Sheet1']

# Get the xlsxwriter objects from the dataframe writer object.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

4

Xem ví dụ đầy đủ tại Ví dụ. Định vị khung dữ liệu Pandas Excel

Chuyển các tùy chọn hàm tạo XlsxWriter cho Pandas

XlsxWriter hỗ trợ một số tùy chọn hàm tạo

workbook  = xlsxwriter.Workbook['filename.xlsx']
worksheet = workbook.add_worksheet[]

0, chẳng hạn như

workbook  = xlsxwriter.Workbook['filename.xlsx']
worksheet = workbook.add_worksheet[]

1. Những điều này cũng có thể được áp dụng cho đối tượng

workbook  = xlsxwriter.Workbook['filename.xlsx']
worksheet = workbook.add_worksheet[]

2 được tạo bởi Pandas bằng cách sử dụng từ khóa

workbook  = xlsxwriter.Workbook['filename.xlsx']
worksheet = workbook.add_worksheet[]

3

Chúng tôi có thể chỉnh sửa tệp Excel bằng Python không?

Chúng ta có thể đọc tệp Excel trong gấu trúc không?