求助：用Pandas实现Excel中SUMPRODUCT及上下方向计算的Python代码

First, let's break down your requirements into two clear parts: generating the sample C/D columns you described, and implementing the Excel formulas you provided for additional columns. I'll cover both with straightforward Pandas code below.

1. Generate Sample C and D Columns #

Based on your sample table, these columns use simple cumulative sum logic:

• Column C: Multiply the running total of column A by the current value in column B.
• Column D: Running total of the product of columns A and B.

Here's the code to implement this:

import pandas as pd

# Create your sample DataFrame (replace with your actual dataset)
data = {'A': [1, 3, 5, 7, 9], 'B': [2, 4, 6, 8, 10]}
df = pd.DataFrame(data)

# Compute sample C and D columns
df['C_sample'] = df['A'].cumsum() * df['B']
df['D_sample'] = (df['A'] * df['B']).cumsum()

# Print the result
print("Sample C and D Columns:")
print(df[['A', 'B', 'C_sample', 'D_sample']])

Output: #

A   B  C_sample  D_sample
0  1   2         2         2
1  3   4        16        14
2  5   6        54        44
3  7   8       128       100
4  9  10       250       190

2. Implement Excel Formulas (Parts a and b) #

Next, let's translate your Excel formulas into efficient Pandas code. We'll create two new columns: C_excel_a for the top-down calculation and E_excel_b for the bottom-up calculation.

Part (a): Top-Down Excel Formula #

Your Excel formula for each row is:
Cn = IFERROR($Bn * SUM(A$2:An) - SUMPRODUCT(B$2:Bn, A$2:An), 0)

We can use Pandas' expanding windows to replicate the cumulative sum behavior of Excel's absolute references:

# Compute C_excel_a (matches your part a formula)
df['C_excel_a'] = df['B'] * df['A'].expanding().sum() - (df['A'] * df['B']).expanding().sum()
# Replace any NaNs with 0 (equivalent to Excel's IFERROR)
df['C_excel_a'] = df['C_excel_a'].fillna(0)

Part (b): Bottom-Up Excel Formula #

Your Excel formula for each row is:
En = IFERROR(SUMPRODUCT($Bn:B$14, $Cn:C$14) - $Bn * SUM($Cn:C$14), 0)

To compute this efficiently, we reverse the DataFrame, use expanding windows to calculate sums from each row to the end, then reverse back to get the original order:

# Reverse the DataFrame to compute bottom-up cumulative sums
rev_B = df['B'][::-1].reset_index(drop=True)
rev_C = df['C_excel_a'][::-1].reset_index(drop=True)

# Calculate expanding sums for the reversed data
exp_sum_BC = (rev_B * rev_C).expanding().sum()
exp_sum_C = rev_C.expanding().sum()

# Compute reversed E values and reverse back to original order
rev_E = exp_sum_BC - rev_B * exp_sum_C
df['E_excel_b'] = rev_E[::-1].reset_index(drop=True)
# Replace NaNs with 0
df['E_excel_b'] = df['E_excel_b'].fillna(0)

Full Output #

Print the complete DataFrame to see all computed columns:

print("\nFull DataFrame with All Computed Columns:")
print(df)

Output: #

A   B  C_sample  D_sample  C_excel_a  E_excel_b
0  1   2         2         2        0.0      692.0
1  3   4        16        14        2.0      492.0
2  5   6        54        44       10.0      296.0
3  7   8       128       100       28.0      120.0
4  9  10       250       190       60.0        0.0

Key Explanations #

• Expanding Windows: Used for part (a) to calculate cumulative sums from the start of the DataFrame up to each row, mimicking Excel's fixed-start references like $A$2:An.
• Reverse Expanding: For part (b), reversing the DataFrame lets us use expanding windows to compute sums from each row to the end, then reversing back gives the bottom-up calculation needed.

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