Is there a way to autoadjust Excel column widths with pandas ExcelWriter

As a data scientist or software engineer, you might have come across the challenge of exporting data from Python to Excel. While pandas.ExcelWriter makes it easy to write data frames to Excel, adjusting column widths can be a tedious task. In this article, we will explore a solution to this problem that will save you time and effort.

Table of Contents

1. The Problem
2. The Solution
3. Conclusion

The Problem

When exporting a pandas data frame to Excel using pandas.ExcelWriter, the default column widths may not be optimal for displaying the data. In fact, the columns may be too narrow, causing the data to be truncated, or too wide, wasting valuable screen space. Adjusting the column widths manually can be a time-consuming process, especially when dealing with large data sets.

The Solution

Fortunately, pandas.ExcelWriter provides a way to adjust the column widths automatically based on the content of the cells. This can be achieved by using the set_column method of the XlsxWriter engine, which is used by pandas.ExcelWriter under the hood.

Here’s how to use this method to adjust the column widths:

import pandas as pd

# create a data frame
df = pd.DataFrame({
    'Name': ['John', 'Jane', 'Bob', 'Alice a Long Name'], # this is a very long name
    'Age': [25, 30, 35, 40],
    'Salary': [50000, 60000, 70000, 80000]
})

# create a pandas.ExcelWriter object
writer = pd.ExcelWriter('data.xlsx', engine='xlsxwriter')

# write the data frame to Excel
df.to_excel(writer, index=False, sheet_name='Sheet1')

# get the XlsxWriter workbook and worksheet objects
workbook = writer.book
worksheet = writer.sheets['Sheet1']

# adjust the column widths based on the content
for i, col in enumerate(df.columns):
    width = max(df[col].apply(lambda x: len(str(x))).max(), len(col))
    worksheet.set_column(i, i, width)

# save the Excel file
writer.save()

Let’s go over this code step by step.

• First, we create a data frame with some sample data. Next, we create a pandas.ExcelWriter object and write the data frame to Excel using the to_excel method. This creates an Excel file with a single sheet named ‘Sheet1’.

• To adjust the column widths, we need to access the xlsxwriter workbook and worksheet objects associated with the ExcelWriter object. We do this using the book and sheets attributes of the ExcelWriter object.

• Next, we loop over the columns of the data frame and calculate the appropriate width for each column. The width is calculated as the maximum length of the column name and the contents of all cells in the column. This ensures that the column width is wide enough to display all the data without truncating it.

• Finally, we use the set_column method of the worksheet object to set the width of each column. The set_column method takes three arguments: the index of the first column to set the width for, the index of the last column to set the width for (in this case, we only want to set the width for one column at a time), and the width to set.

Conclusion

In this article, we have shown how to automatically adjust the column widths of an Excel file created using pandas.ExcelWriter. By using the set_column method of the xlsxwriter engine, we can calculate the appropriate width for each column based on the content of the cells, and set the width accordingly. This saves us time and effort compared to adjusting the column widths manually.

SHARE:

About Saturn Cloud

Saturn Cloud is your all-in-one solution for data science & ML development, deployment, and data pipelines in the cloud. Spin up a notebook with 4TB of RAM, add a GPU, connect to a distributed cluster of workers, and more. Request a demo today to learn more.