如何用Python在现有DataFrame新增列实现VLOOKUP及报错解决？

First, let's break down why your code threw that error:

AttributeError: 'Series' object has no attribute 'merge'

The merge() method is a DataFrame operation, not a Series one. When you called df1['mittlere Leistung'].merge(...), you were trying to merge two Series objects—which isn't supported. Instead, you need to work with full DataFrames (or use Series-based methods designed for lookups).

Here are three reliable ways to add the 'soll' column directly to your existing df1:

Method 1: Use merge() and Reassign to df1 #

This is the most straightforward extension of your working code. Instead of saving to a new results DataFrame, merge only the necessary columns from df2 into df1:

# Keep only the matching key and target column from df2 to avoid duplicates
df1 = df1.merge(df2[['mittlere Wind', 'soll']], on='mittlere Wind', how='left')

This adds the 'soll' column to df1 while preserving all existing data. The how='left' ensures every row in df1 is retained—rows without a matching 'mittlere Wind' in df2 will get NaN in the 'soll' column, just like Excel's VLOOKUP.

Method 2: Use map() (Best for Unique Keys) #

If the 'mittlere Wind' values in df2 are unique (no duplicates), map() is a faster, more concise option. It works by creating a lookup Series from df2:

# Create a lookup Series where the index is your matching key
lookup_soll = df2.set_index('mittlere Wind')['soll']
# Map the values directly to df1's 'mittlere Wind' column
df1['soll'] = df1['mittlere Wind'].map(lookup_soll)

This modifies df1 directly without reassigning the entire DataFrame, making it ideal for large datasets with unique keys.

Method 3: Use join() (Index-Based Matching) #

Similar to merge(), but uses indexes for matching. First set df2's index to your key, then join it to df1:

# Set df2's index to the matching key and isolate the 'soll' column
df2_soll = df2.set_index('mittlere Wind')[['soll']]
# Join to df1 using 'mittlere Wind' as the matching column
df1 = df1.join(df2_soll, on='mittlere Wind')

This is useful if you're already working with indexed DataFrames, but it functions identically to the merge() approach for your use case.

Key Notes: #

• If df2 has duplicate 'mittlere Wind' entries: merge() will add multiple rows to df1 (one per match), while map() will only use the last occurrence of each key. Choose the method that fits your data's structure.
• All methods will leave NaN values in the 'soll' column where no matching key exists in df2.

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