How to Automatically Add Headers to Multiple Excel Files with Python and pandas

Introduction

Hello, I'm PiPi. Recently a follower asked a Python automation question about adding headers to Excel files, which I share here.

Solution Overview

The task is to add column headers "经度" (longitude) and "纬度" (latitude) to each Excel file located in subfolders of a given folder. The solution uses the pandas library.

Installation

pip install pandas

Implementation

import os
import pandas as pd

# Define folder path
folder_path = r"文件夹路径"

# Get all subfolders
subfolders = [f.path for f in os.scandir(folder_path) if f.is_dir()]

# Process each subfolder
for subfolder in subfolders:
    # Excel file path
    excel_file = os.path.join(subfolder, "Excel表名.xlsx")

    # Read Excel without header
    df = pd.read_excel(excel_file, header=None)

    # Add column headers
    df.columns = ["经度", "纬度"]

    # Save back without index
    df.to_excel(excel_file, index=False)

The script scans the target directory, reads each Excel file with header=None, assigns the new column names, and writes the file back while preserving the original data.

Conclusion

This code provides a quick way to automate adding headers to multiple Excel files using Python and pandas.