Enhance Your Pandas Excel Output: Freezing Panes, Filtering, and Adjusting Column Widths

Introduction:

When working with Pandas and exporting data to Excel using to_excel, you might have encountered issues with narrow column widths, making it difficult to review and analyze the data efficiently. In this blog post, we'll explore a comprehensive approach to enhance your Excel output by freezing panes, applying filters, and adjusting column widths. By combining Pandas and the xlsxwriter library, we can easily achieve these tasks and focus on the code rather than manual formatting.

Section 1: Matrix Freeze

To freeze specific rows and columns, we need to utilize the xlsxwriter engine and the freeze_panes method.

import pandas as pd

# Assuming 'df' contains your DataFrame
with pd.ExcelWriter(file_path, engine='xlsxwriter') as writer:
    df.to_excel(writer, index=False)
    ws = writer.sheets['Sheet1']

    # Freeze the first row
    ws.freeze_panes(1, 0)

Section 2: Adjusting Column Widths

You can set the width for each column by using the set_column method.

import pandas as pd

# Assuming 'df' contains your DataFrame
with pd.ExcelWriter(file_path, engine='xlsxwriter') as writer:
    df.to_excel(writer, index=False)
    ws = writer.sheets['Sheet1']

    # Adjust column width for each column
    for i, col in enumerate(df.columns):
        width = get_width(col)  # Custom function to calculate width based on column content
        ws.set_column(i, i, width + 4)  # Add a buffer to prevent text blockage by filter

If you want a more general approach to automatically adjust column widths based on the content, you can use the get_width function:

import string
import re

def get_width(test_str):
    letter_to_width = {
        'lower': 0.95,     # Column width per English lowercase letter
        'upper': 1.18,     # Column width per English capital letter
        'digit': 1,        # Column width per digit
        'korea': 1.85,     # Column width per Korean character
        'other': 0.95,     # Column width per remaining character
    }
    width = 0
    for c in test_str:
        if c in string.ascii_lowercase:
            width += letter_to_width['lower']
        elif c in string.ascii_uppercase:
            width += letter_to_width['upper']
        elif c in string.digits:
            width += letter_to_width['digit']
        elif re.match(r'[a-heh]', c):
            width += letter_to_width['korea']
        else:
            width += letter_to_width['other']

    return width

Section 3: Adding Filters

You can add filters to your DataFrame by using the autofilter method.

import pandas as pd

# Assuming 'df' contains your DataFrame
with pd.ExcelWriter(file_path, engine='xlsxwriter') as writer:
    df.to_excel(writer, index=False)
    ws = writer.sheets['Sheet1']

    # Add a filter to the first row
    ws.autofilter(0, 0, df.shape[0] - 1, df.shape[1] - 1)

Section 4: Combining All Features

Here's an example of combining all the mentioned features into a single function:

import pandas as pd
import string
import re

def get_width(test_str):
    # Your implementation of the get_width function

def apply_column_format(df, file_path):
    with pd.ExcelWriter(file_path, engine='xlsxwriter') as writer:
        df.to_excel(writer, index=False)
        ws = writer.sheets['Sheet1']

        # Adjust column width for each column
        for i, col in enumerate(df.columns):
            width = get_width(col)
            ws.set_column(i, i, width + 4)  # Add a buffer to prevent text blockage by filter

        # Add a filter to the first row
        ws.autofilter(0, 0, df.shape[0] - 1, df.shape[1] - 1)

        # Freeze the first row
        ws.freeze_panes(1, 0)

# Example usage
apply_column_format(new_stock, f'./{current_date}_stock_match.xlsx')

Conclusion:

By utilizing the xlsxwriter engine and Pandas, we can easily enhance our Excel output with features like freezing panes, applying filters, and adjusting column widths. This will streamline the data analysis process and save you time on manual formatting, allowing you to focus more on your code and analysis. Happy coding!