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

1. Introduction

In the previous article we raised a question about handling Excel files in subfolders; this article provides a solution.

2. Implementation

We asked ChatGPT for a method. The task: each subfolder contains Excel files with two columns (longitude, latitude) without headers. We need to add headers "经度" and "纬度".

We can use Python's pandas library to read and write Excel files. The steps are:

import os
import pandas as pd

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

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

# Add headers to each Excel file and save
for subfolder in subfolders:
    # Get all Excel file paths in the subfolder
    excel_paths = [f.path for f in os.scandir(subfolder) if f.is_file() and f.name.endswith(".xlsx")]
    for excel_path in excel_paths:
        # Read Excel file
        df = pd.read_excel(excel_path, header=None)
        # Add headers
        df.columns = ['经度', '纬度']
        # Save Excel file
        df.to_excel(excel_path, index=False)

The script first defines the folder path, then uses os.scandir() to list subfolders, iterates each subfolder, finds all ".xlsx" files, reads them with pd.read_excel(), assigns column names, and writes them back with to_excel(). After running the script, every Excel file will have the proper headers.

If some files are in ".xls" format, the code can be adjusted accordingly in a future article.

3. Conclusion

This article demonstrates a Python automation solution for adding headers to multiple Excel files, providing clear code and explanation to help readers solve the problem efficiently.