使用For Loop匹配DataFrame值并更新对应列（类Excel VLOOKUP）

Hey there! Let's walk through how to replicate Excel's VLOOKUP behavior using a for loop in pandas, exactly as you requested.

Step 1: Set Up Your DataFrames #

First, let's start with your original data (included here for clarity):

import pandas as pd

df1 = pd.DataFrame(data={'col1': ['a', 'b', 'd'], 'col2': [1, 2, 4], 'col3': [2, 3, 4]})
df2 = pd.DataFrame(data={'col1': ['a', 'f', 'c', 'd']})

Step 2: Initialize the Result DataFrame df3 #

We'll start by copying df2 as the base for df3, then add empty col2 and col3 columns to hold our matched values:

# Create df3 from df2 and add empty columns for col2 and col3
df3 = df2.copy()
df3['col2'] = ''
df3['col3'] = ''

Step 3: Use a For Loop to Match and Populate Values #

Now we'll loop through each row in df3, check if the col1 value exists in df1, and if so, fill in col2 and col3 with the corresponding values from df1:

# Iterate over each row in df3
for idx, row in df3.iterrows():
    # Get the current value we're looking up in col1
    lookup_val = row['col1']
    
    # Find the matching row(s) in df1
    match = df1[df1['col1'] == lookup_val]
    
    # If we found a match (i.e., the filtered DataFrame isn't empty)
    if not match.empty:
        # Assign the matched col2 and col3 values to df3
        df3.at[idx, 'col2'] = match['col2'].iloc[0]
        df3.at[idx, 'col3'] = match['col3'].iloc[0]

Step 4: Check the Result #

When you print df3, you'll get exactly the output you're expecting:

print(df3)

Output:

col1 col2 col3
0    a    1    2
1    f        
2    c        
3    d    4    4

A Quick Note #

While pandas has built-in functions like merge() that can do this more efficiently (and I'd recommend it for larger datasets), this for loop approach directly aligns with your requirement to manually implement the lookup logic step-by-step.

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