如何在Pandas中利用两个DataFrame实现类似SUMPRODUCT的功能

Step-by-Step Solution #

Got it, let's build this SUMPRODUCT equivalent in Pandas. The goal is to multiply each CD type's quantity from df_2_copy by its matching price from df, sum those products per month, and drop the results into the End Cash row. Here's how to do it:

import pandas as pd

# Original DataFrame setup (as provided)
data = [['1-mo CDs', 1.0, 1, 2000, '1, 2, 3, 4, 5, and 6'], 
        ['3-mo CDs', 4.0 ,3 ,3000,'1 and 4'], 
        ['6-mo CDs',9.0 ,6, 5000,'1']]
df = pd.DataFrame(data,columns=['Scenario','Yield', 'Term','Price', 'Purchase CDs in months'])

data_2 = [['Init Cash', 400000, 325000,335000,355000,275000,225000,240000], 
          ['Matur CDs',0,0,0,0,0,0,0], 
          ['Interest',0,0,0,0,0,0,0], 
          ['1-mo CDs',0,0,0,0,0,0,0], 
          ['3-mo CDs',0,0,0,0,0,0,0], 
          ['6-mo CDs',0,0,0,0,0,0,0], 
          ['Cash Uses',75000,-10000,-20000,80000,50000,-15000,60000], 
          ['End Cash', 0,0,0,0,0,0,0]]
df_2 = pd.DataFrame(data_2,columns=['Month', 'Month 1', 'Month 2', 'Month 3', 'Month 4', 'Month 5', 'Month 6', 'End'])
df_2_copy = df_2.copy()

# 1. Create a lookup map for CD prices from df
cd_price_map = df.set_index('Scenario')['Price'].to_dict()
# This gives us: {'1-mo CDs': 2000, '3-mo CDs': 3000, '6-mo CDs': 5000}

# 2. Pull just the CD quantity rows from df_2_copy (skip the 'Month' label column)
cd_quantity_rows = df_2_copy[df_2_copy['Month'].isin(cd_price_map.keys())].loc[:, 'Month 1':'End']

# 3. Calculate SUMPRODUCT: multiply each CD's quantities by its price, then sum across rows per month
sum_product_results = cd_quantity_rows.mul(list(cd_price_map.values())).sum(axis=0)

# 4. Assign the calculated sums to the 'End Cash' row (iloc[7])
df_2_copy.iloc[7, 1:] = sum_product_results

# Check the final result
print(df_2_copy)

Breakdown of the Logic #

• Price Lookup Map: We convert the CD type and price from df into a dictionary for quick, easy reference.
• Isolate CD Quantities: We filter df_2_copy to only keep the rows for our three CD types, and grab the numerical month columns (ignoring the row labels).
• SUMPRODUCT Calculation: Using mul() we multiply each CD's quantity column by its corresponding price, then sum(axis=0) adds up those products for every month column.
• Populate Results: Finally, we drop the summed values into the End Cash row, starting from the second column (since the first column is the row's label).

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