Excel Automation with Python: Merge, Clean, Analyze 1000+ Files

#python #excel #automation #tutorial

Excel Automation with Python: Merge, Clean, Analyze 1000+ Files

Stop opening Excel files manually. Here is how to automate everything with Python.

The Complete Excel Workflow

import pandas as pd
import glob

class ExcelAutomator:
    def merge_all(self, folder):
        files = glob.glob(folder + "/*.xlsx")
        dfs = []
        for f in files:
            df = pd.read_excel(f)
            df["source"] = f.split("/")[-1]
            dfs.append(df)
        return pd.concat(dfs, ignore_index=True)

    def clean(self, df):
        df = df.drop_duplicates()
        for col in df.columns:
            if df[col].dtype in ["float64", "int64"]:
                df[col] = df[col].fillna(df[col].median())
        return df

    def analyze(self, df):
        return {
            "rows": len(df),
            "columns": list(df.columns),
            "total": df.select_dtypes("number").sum().to_dict()
        }

auto = ExcelAutomator()
df = auto.merge_all("reports/")
df = auto.clean(df)
print(auto.analyze(df))
df.to_excel("master_report.xlsx", index=False)