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)