如何使用Pandas实现仅返回首个匹配项的VLOOKUP效果（替代低效嵌套循环）

The issue with your current merge is that your table_to_search has multiple entries for the same hotel name—so when you merge, it brings in every matching row. To replicate your nested loop behavior (take the first match and break), you just need to deduplicate the lookup table first, keeping only the first occurrence of each hotel name.

Step-by-Step Fix: #

1. Clean the lookup table: Use drop_duplicates() on table_to_search to retain only the first entry for each hotel Name.
2. Merge with the cleaned table: Now when you merge, each hotel in main_table will only match one row from the lookup table, just like your loop does.

Modified Code: #

import pandas as pd
from datetime import datetime

start_time = datetime.now()
file_path = r'/Users/myuser/Desktop/VLOOKUP_TEST/hotel_data.xlsx'

main_table = pd.read_excel(file_path, 'market_segment', header=0)
table_to_search = pd.read_excel(file_path, '2018', header=0)

print('\nVLOOKUP with loop')
loop_vlookup = []
for k in range(0, len(main_table['hotel name'])):
    status = False
    for item in range(0, len(table_to_search['hotel Name'])) :
        if str(main_table['hotel name'][k]) == table_to_search['hotel Name'][item]:
            print('coincidence')
            loop_vlookup.append(table_to_search['market_segment'][item])
            status = True
            break
    if not status:
        loop_vlookup.append("")
print("\n", loop_vlookup)

# Pandas VLOOKUP (Fixed Version)
print("\nPandas VLOOKUP (First Match Only)")
mapping = {'hotel Name': 'hotel name', 'market_segment': 'vlookup_column'}

# Step 1: Deduplicate the lookup table - keep only the first entry per hotel name
cleaned_lookup = table_to_search.rename(columns=mapping).drop_duplicates(subset='hotel name', keep='first')

# Step 2: Merge with main table (use how='left' to keep all main_table rows, even if no match)
out = main_table.merge(cleaned_lookup[['hotel name', 'vlookup_column']], on='hotel name', how='left')

# Replace NaN with empty string to match your loop's behavior for no matches
out['vlookup_column'] = out['vlookup_column'].fillna('')

print(out)

Why This Works: #

• drop_duplicates(subset='hotel name', keep='first') ensures that for each hotel in the lookup table, only the first occurrence of its market_segment is kept—exactly what your nested loop does when it hits break after finding the first match.
• Using how='left' in the merge guarantees that all rows from main_table are preserved, just like your loop (which adds an empty string if no match is found; we use fillna('') to replicate that).

Performance Note: #

This approach will be far faster than your nested loops, especially as your dataset grows. Pandas operations are vectorized and optimized, unlike Python-level loops which are slow for large data.

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