python - 在python中自动调整数据框
问题描述
我尝试使用有人在此处回答的此功能。当我将数据框输出到 xlsx 电子表格时对其进行自动调整。这是功能:
def get_col_widths(dataframe):
# First we find the maximum length of the index column
idx_max = max([len(str(s)) for s in dataframe.index.values] + [len(str(dataframe.index.name))])
# Then, we concatenate this to the max of the lengths of column name and its values for each column, left to right
return [idx_max] + [max([len(str(s)) for s in dataframe[col].values] + [len(col)]) for col in dataframe.columns]
for i, width in enumerate(get_col_widths(df)):
worksheet.set_column(i, i, width)
这适用于一些但不是所有的列。我想知道是否有人知道自动调整熊猫数据框的方法。
解决方案
将数据框保存到 Excel 后,您可以使用 xlwings 自动调整列。
import pandas as pd
import xlwings as xw
report_file = "test.xlsx"
df1 = pd.DataFrame([
('this is a long term1', 1, 1, 3),
('this is a long term2', 1, 2, 5),
('this is a long term3', 1, 1, 6),
('this is a long term2', 1, 1, 9),
], columns=['term', 'aaaa', 'bbbbbbb', "cccccccccccccccccccccccccccccccccccccccccccccc"])
writer = pd.ExcelWriter(report_file, engine="xlsxwriter")
df1.to_excel(writer, sheet_name="Sheet1", index=False)
workbook = writer.book
worksheet1 = writer.sheets["Sheet1"]
num_format = workbook.add_format({"num_format": '#,##0.00'})
worksheet1.set_column("B:D", cell_format=num_format)
writer.save()
# Autofit all columns with xlwings.
app = xw.App(visible=False)
wb = xw.Book(report_file)
for ws in wb.sheets:
ws.autofit(axis="columns")
wb.save(report_file)
app.quit()