Batch Add Longitude and Latitude Headers to Excel Files with Python

Introduction

In the previous article we raised a question about processing Excel files of different names located in nested folders, with both .xls and .xlsx formats. This article provides a step‑by‑step solution.

Problem Description

The task is to write a Python script that traverses a top‑level folder and all its sub‑folders, finds every Excel file that contains only two columns (longitude and latitude), and adds a header row naming the columns “经度” (longitude) and “纬度” (latitude).

Solution

The following code uses the os module to walk the directory tree and pandas (with xlrd support) to read, modify, and save each workbook.

import os
import pandas as pd

# Path to the folder containing the Excel files
folder_path = r'C:\Users\YourFolder\YourExcelFiles'
excel_files = []
for root, dirs, files in os.walk(folder_path):
    for file in files:
        if file.endswith('.xlsx'):
            excel_files.append(os.path.join(root, file))

# Add headers to each Excel file
for file_path in excel_files:
    df = pd.read_excel(file_path)  # read the Excel file
    df.columns = ['经度', '纬度']   # set column names
    df.to_excel(file_path, index=False)  # write back to the same file

The script first collects all .xlsx files, then reads each file into a DataFrame, assigns the desired column names, and writes the updated DataFrame back to the original file. Adjust folder_path to point to your own directory.

Conclusion

This example demonstrates how Python can automate repetitive Excel‑processing tasks, making it easy to add consistent headers across many files in a nested folder structure.