请求：用Python Pandas/Openpyxl批量读取账户Excel并命名变量

Hey there! Let's work through this problem step by step—since you're new to Python and Pandas, I'll make sure this is clear and actionable. Your core goal is to loop through account numbers from MasterSheet.xlsx, load the corresponding Excel files, and store each as a DataFrame tied to its account number. Here's how to fix your code and do it properly:

Key Issue with Your Original Code #

The iterrows() method returns tuples of (index, row_data), so when you did str(account), you weren't getting just the account number—you were getting a string representation of the entire tuple, which would create invalid filenames like (0, Account Number 123\nName: 0, dtype: object).xlsx. That's why your code wasn't working!

Recommended Solution: Use a Dictionary to Store DataFrames #

Storing DataFrames in a dictionary is way cleaner than dynamically creating variables (which can get messy fast). Here's the code:

import pandas as pd

# Load the master sheet containing all account numbers
master = pd.read_excel("MasterSheet.xlsx")

# Create a dictionary to hold each account's DataFrame (key = account number, value = DataFrame)
account_dataframes = {}

# Loop through each row in the master sheet
for index, row in master.iterrows():
    # Extract the account number from the "Account Number" column
    account_number = row["Account Number"]
    # Build the filename for the account's Excel file
    file_name = f"{account_number}.xlsx"
    
    try:
        # Load the Excel file and store it in the dictionary
        account_dataframes[account_number] = pd.read_excel(file_name)
        print(f"Successfully loaded data for account {account_number}")
    except FileNotFoundError:
        # Handle cases where the account's Excel file doesn't exist
        print(f"⚠️  Warning: File {file_name} not found. Skipping this account.")

How to Use the Stored DataFrames #

To access data for a specific account (say, account 123), just use:

account_dataframes[123]  # Replace 123 with your actual account number

Alternative: Dynamically Create Variables (Not Recommended) #

If you really want to create individual variables for each account (like df_123 for account 123), you can do this—but I don't recommend it because it's harder to track variables as your project grows. Here's how:

import pandas as pd

master = pd.read_excel("MasterSheet.xlsx")

for index, row in master.iterrows():
    account_number = row["Account Number"]
    file_name = f"{account_number}.xlsx"
    
    try:
        # Dynamically create a variable named df_{account_number}
        globals()[f"df_{account_number}"] = pd.read_excel(file_name)
        print(f"Created variable df_{account_number} for account {account_number}")
    except FileNotFoundError:
        print(f"⚠️  Warning: File {file_name} not found. Skipping.")

Important Notes #

• Make sure the "Account Number" column name in MasterSheet.xlsx matches exactly what you use in row["Account Number"] (capitalization and spaces matter!).
• All account-specific Excel files must be in the same folder as MasterSheet.xlsx.
• Adding the try-except block prevents your code from crashing if one file is missing.

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