Python script to load Excel file and clean the empty rows and columns if the clean argument is set.

clean_excel_loader.py

#!/usr/bin/env python
# coding: utf-8

import pandas as pd

def load_excel(file_path, sheet_name=None, clean=True, ffill=True):
    """Load an Excel file into a pandas DataFrame.

    Args:
        file_path (str): Path to the Excel file.
        sheet_name (str, optional): Name of the sheet to load. Defaults to None.
        clean (bool, optional): Whether to clean the DataFrame by removing empty rows and columns and setting the first row as header. Defaults to True.
        ffill (bool, optional): Whether to forward-fill missing values. Defaults to True.

    Returns:
        pd.DataFrame: The loaded DataFrame.
    """
    if clean:
        # Clean the DataFrame by removing empty rows and columns, and set the first row as header
        df = pd.read_excel(file_path, sheet_name=sheet_name, header=None)
        df = df.dropna(how='all').dropna(how='all', axis=1)
        df = pd.DataFrame(df.values[1:], columns=df.iloc[0])
    else:
        df = pd.read_excel(file_path, sheet_name=sheet_name)
    if ffill:
        df = df.ffill()
    return df