基于Pandas的Excel自动匹配系统开发求助：匹配功能后如何推进

Hey there! Let's work through this bottleneck you're hitting with your Excel matching tool. I totally get what you're aiming for—replicating that intuitive Excel lookup functionality, and you've already got a great start. Let's break down the gaps in your current code and get this working smoothly.

First, Fix the Immediate Errors #

Right now, your code will throw errors because:

• The df variable you try to write to Excel isn't initialized anywhere
• Your loadingTime() function is defined but never called, so you won't see any progress updates
• Using global variables for i can lead to messy state issues (we'll clean that up)

Next, Optimize the Matching Logic (The Big Bottleneck Fix) #

Your nested loops will work for small files, but they'll get really slow as your Excel files grow. Pandas is built for vectorized operations—let's leverage that instead of looping through every row manually. We'll replicate your "case-insensitive match on the first column, then capture full rows from both files" logic way more efficiently.

Here's the Refined, Working Code #

import pandas as pd

# Load full files (we'll handle column selection directly here)
full_file1 = pd.read_excel("Excel_test.xlsx", header=None)
full_file2 = pd.read_excel("Excel_test02.xlsx", header=None)

# Step 1: Prepare case-insensitive match keys
# We'll use the first column (index 0) for matching, converted to lowercase
full_file1['match_key'] = full_file1.iloc[:, 0].str.lower()
full_file2['match_key'] = full_file2.iloc[:, 0].str.lower()

# Step 2: Find all matching rows with a merge (way faster than loops!)
# 'inner' merge keeps only rows where the match_key exists in both files
merged_results = pd.merge(
    full_file1, 
    full_file2, 
    on='match_key', 
    how='inner',
    suffixes=('_file1', '_file2')  # Add suffixes to distinguish columns from each file
)

# Step 3: Clean up the temporary match key column
merged_results = merged_results.drop(columns=['match_key'])

# Step 4: Save the results to Excel
with pd.ExcelWriter("output.xlsx") as writer:
    merged_results.to_excel(writer, sheet_name="Matched Rows", index=False)

print("Matching complete! Results saved to output.xlsx")

If You Still Want Progress Updates (For Very Large Files) #

If you're working with massive datasets and want to track progress, we can use a progress bar library like tqdm (you'll need to install it first with pip install tqdm). Here's how to adapt the loop approach with proper progress tracking (though the merge method is still preferred for speed):

import pandas as pd
from tqdm import tqdm

full_file1 = pd.read_excel("Excel_test.xlsx", header=None)
full_file2 = pd.read_excel("Excel_test02.xlsx", header=None)

# Initialize results DataFrame
results = pd.DataFrame()

# Get lowercase versions of the match columns
file1_match_col = full_file1.iloc[:, 0].str.lower()
file2_match_col = full_file2.iloc[:, 0].str.lower()

# Loop with progress bar
for idx1, val1 in tqdm(enumerate(file1_match_col), total=len(file1_match_col)):
    # Find all matching indices in file2
    match_indices = file2_match_col[file2_match_col == val1].index
    for idx2 in match_indices:
        # Combine rows from both files and add to results
        combined_row = pd.concat([full_file1.iloc[idx1], full_file2.iloc[idx2]], axis=0)
        results = pd.concat([results, combined_row.to_frame().T], ignore_index=True)

# Save results
with pd.ExcelWriter("output.xlsx") as writer:
    results.to_excel(writer, sheet_name="Matched Rows", index=False)

Key Improvements Explained #

1. Vectorized Operations: The merge method uses Pandas' optimized backend, which is orders of magnitude faster than nested Python loops for large datasets.
2. Cleaner State: No more global variables—we use Pandas' built-in methods to handle data flow.
3. Clear Output: The suffixes (_file1, _file2) make it easy to tell which columns came from which original Excel file.
4. Proper Resource Handling: Using with pd.ExcelWriter(...) ensures the file is closed properly after saving.

You should now have a working tool that replicates Excel's lookup functionality, runs efficiently, and produces clean, readable results. Let me know if you need to tweak the logic further (like keeping non-matching rows, or adjusting how matches are counted)!

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