使用Pandas重命名列报错：'ExcelFile'对象无'rename'属性

Hey there! Let's break down why you're hitting that AttributeError: 'ExcelFile' object has no attribute 'rename' error and get your code working properly.

The Root Cause #

The issue is that pd.ExcelFile() creates a file-handling object for reading Excel files, not a tabular DataFrame that you can modify with methods like rename(). You can't rename columns directly on an ExcelFile instance—you need to load the file's data into a pandas DataFrame first.

Better Approach: Rename Before Saving (More Efficient) #

Since you already have all your combined data in the all_data DataFrame before saving it, you don't need to re-read the saved Excel file to rename columns. You can modify the headers right in the existing DataFrame, then save it once. Here's how to adjust your code:

import pandas as pd
import glob
import os

all_data = pd.DataFrame()
for f in glob.glob(r'C:\\Test\\*.xls'):
    df = pd.read_excel(f)
    # Convert all string values to uppercase
    df = df.applymap(lambda s: s.upper() if isinstance(s, str) else s)
    all_data = pd.concat([all_data, df], ignore_index=True)  # Using concat instead of append (append is deprecated)

# Rename columns directly in the combined DataFrame
all_data.rename(columns={'A 1': 'A1', 'A 2': 'B1', 'A 3': 'C1'}, inplace=True)

# Save the modified DataFrame to Excel
writer = pd.ExcelWriter(r'C:\\Test\\alldata.xlsx', engine='xlsxwriter')
all_data.to_excel(writer, index=False)  # Add index=False to avoid saving the default index column
writer.close()  # Use close() instead of save() (save() is deprecated in newer pandas versions)

print("All data in upload folder combined into one file")

# Delete original .xls files
files = glob.glob(r'C:\\Test\\*.xls')
for f in files:
    os.remove(f)

If You Do Need to Rename After Saving #

If for some reason you have to modify the already-saved Excel file later, you need to read it into a DataFrame first, make changes, then save again:

# Read the saved Excel file into a DataFrame
data_df = pd.read_excel(r'C:\\Test\\alldata.xlsx')

# Rename columns
data_df.rename(columns={'A 1': 'A1', 'A 2': 'B1', 'A 3': 'C1'}, inplace=True)

# Save the modified DataFrame back to Excel
data_df.to_excel(r'C:\\Test\\alldata_renamed.xlsx', index=False)

Key Fixes & Improvements #

• Replaced all_data.append() with pd.concat() because append() is deprecated in pandas 2.0+.
• Used isinstance(s, str) instead of type(s) == str—it's more flexible for subclassed string types.
• Added index=False to to_excel() to prevent saving the auto-generated index column (unless you need it).
• Switched from writer.save() to writer.close()—the former is deprecated in newer pandas versions.

内容的提问来源于stack exchange，提问作者user8763797