如何基于另一DataFrame的价格为DataFrame添加总价列？

Scenario Recap #

Let's start by laying out your data clearly:

import pandas as pd

# df1: Product price reference
df1 = pd.DataFrame({
    'product': ['apples', 'bananas', 'oranges', 'lemons', 'Olive Oil'],
    'price': [1.99, 1.20, 1.49, 0.50, 8.99]
})

# df2: Rows of product combinations
df2 = pd.DataFrame({
    'product': ['apples', 'bananas', 'Olive Oil', 'lemons'],
    'product.1': ['bananas', 'lemons', 'bananas', 'apples'],
    'product.2': ['Olive Oil', 'oranges', 'oranges', 'bananas']
})

Your Goal #

Add a total_price column to df2, where each value is the sum of prices for all products in that row (pulled from df1). Your expected output looks like this:

product product.1 product.2  total_price
0     apples    bananas  Olive Oil        12.18
1    bananas     lemons    oranges         3.19
2  Olive Oil    bananas    oranges        11.68
3     lemons     apples    bananas         3.69

The Problem with Your Current Approach #

Your repeated pd.merge method works for small datasets, but it scales poorly:

• Each merge creates intermediate DataFrames, wasting memory
• You’re duplicating logic for every product column, which adds unnecessary computation time as df1 grows or df2 gains more product columns

Optimal Solution: Vectorized Mapping & Summation #

The fastest, most scalable approach leverages Pandas' vectorized operations (optimized under the hood with NumPy) to avoid redundant work:

Step 1: Create a Price Lookup Dictionary #

First, turn df1 into a simple lookup map where product names map to their prices:

price_map = df1.set_index('product')['price'].to_dict()

Step 2: Map Prices & Calculate Total #

Use df.replace() to swap product names with their prices across all columns, then sum each row:

# Replace product names with prices, then sum along rows (axis=1)
df2['total_price'] = df2.replace(price_map).sum(axis=1)

Full Working Code #

import pandas as pd

# Initialize DataFrames
df1 = pd.DataFrame({
    'product': ['apples', 'bananas', 'oranges', 'lemons', 'Olive Oil'],
    'price': [1.99, 1.20, 1.49, 0.50, 8.99]
})

df2 = pd.DataFrame({
    'product': ['apples', 'bananas', 'Olive Oil', 'lemons'],
    'product.1': ['bananas', 'lemons', 'bananas', 'apples'],
    'product.2': ['Olive Oil', 'oranges', 'oranges', 'bananas']
})

# Create price lookup map
price_map = df1.set_index('product')['price'].to_dict()

# Calculate total price
df2['total_price'] = df2.replace(price_map).sum(axis=1)

print(df2)

Why This Works Better #

• Blazing Fast: Vectorized operations like replace() and sum() run far quicker than loops or repeated merges, even for large datasets
• Low Memory Overhead: No intermediate DataFrames are created—you modify df2 directly (or make a copy if you want to preserve the original)
• Scalable: This method works seamlessly no matter how many product columns df2 has—no need to write extra merge code for each new column

Alternative: Explicit Row-Wise Approach (Less Efficient) #

If you prefer a more readable (but slower) row-wise method, you can use apply:

df2['total_price'] = df2.apply(lambda row: row.map(price_map).sum(), axis=1)

Note: apply is not vectorized, so it won’t perform as well as the replace() method for big datasets.

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