Python Pandas là một thư viện phân tích dữ liệu Python. Nó có thể đọc, lọc và sắp xếp lại các tập dữ liệu lớn và nhỏ, đồng thời xuất chúng ở nhiều định dạng bao gồm cả Excel
Pandas ghi tệp Excel bằng mô-đun Xlwt cho tệp xls và mô-đun Openpyxl hoặc XlsxWriter cho tệp xlsx
Sử dụng XlsxWriter với Pandas
Để sử dụng XlsxWriter với Pandas, bạn chỉ định nó làm công cụ soạn thảo Excel
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') # Close the Pandas Excel writer and output the Excel file. writer.close()
Đầu ra từ này sẽ giống như sau
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 đồ
# Get the xlsxwriter objects from the dataframe writer object. workbook = writer.book worksheet = writer.sheets['Sheet1'] # Create a chart object. chart = workbook.add_chart({'type': 'column'}) # Get the dimensions of the dataframe. (max_row, max_col) = df.shape # Configure the series of the chart from the dataframe data. chart.add_series({'values': ['Sheet1', 1, 1, max_row, 1]}) # Insert the chart into the worksheet. worksheet.insert_chart(1, 3, chart)
Đầ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
# Add some cell formats. format1 = workbook.add_format({'num_format': '#,##0.00'}) format2 = workbook.add_format({'num_format': '0%'}) # Set the column width and format. worksheet.set_column(1, 1, 18, format1) # Set the format but not the column width. worksheet.set_column(2, 2, None, format2)
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
df.to_excel(writer, sheet_name='Sheet1', startrow=1, header=False, index=False)
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
column_settings = [{'header': column} for column in df.columns]
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 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
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à một 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 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
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