如何在for循环中通过DataFrame数值计算更新Results DataFrame？

原代码的核心问题 #

1. 列名不匹配：Results的Q1/Q2列实际名为Stock Q1/Stock Q2，但代码里用了Q1/Q2，导致更新了不存在的列，原数据自然没变化。
2. Quarter值类型不匹配：Forecast的Quarter列存储的是数字1/2，但代码判断条件是row2['Quarter']=='Q1'（字符串），条件永远不成立，更新逻辑根本没执行。
3. Series取值问题：从df3筛选出的Days是Series类型，直接相乘会返回Series，需要提取单个数值才能正确计算。

修复后的循环代码 #

import pandas as pd

results = pd.DataFrame([['Ra',0,0],['Co',0,0],['Si',0,0],['Lem',0,0],['Fa',0,0]], columns=['Shops','Stock Q1','Stock Q2'])
forecast = pd.DataFrame([['Ra',1,199,'A'],['Co',2,145,'A'],['Si',2,119,'A'],['Ra',2,133,'B'],['Co',1,123,'B'],['Si',2,111,'A'],['Fa',1,121,'A']], columns=['Shops','Quarter','ForeC','Type'])
df3 = pd.DataFrame([['Ra',25,'A'],['Fa',33,'A'],['Co',30,'A'],['Lem',29,'A'],['Si',22,'A'],['Ra',25,'B'],['Fa',33,'B'],['Co',30,'B'],['Lem',29,'B'],['Si',22,'B']], columns=['Shop','Days','Type'])

for index, row1 in results.iterrows():
    shop = row1['Shops']
    # 处理Q1
    q1_forecasts = forecast[(forecast['Shops'] == shop) & (forecast['Quarter'] == 1)]
    for _, row2 in q1_forecasts.iterrows():
        # 提取对应Days的数值
        days = df3.loc[(df3['Shop'] == shop) & (df3['Type'] == row2['Type']), 'Days'].values[0]
        results.at[index, 'Stock Q1'] += days * row2['ForeC']
    # 处理Q2
    q2_forecasts = forecast[(forecast['Shops'] == shop) & (forecast['Quarter'] == 2)]
    for _, row2 in q2_forecasts.iterrows():
        days = df3.loc[(df3['Shop'] == shop) & (df3['Type'] == row2['Type']), 'Days'].values[0]
        results.at[index, 'Stock Q2'] += days * row2['ForeC']

print(results)

更高效的Pandas向量化实现（推荐） #

嵌套循环在数据量大时效率极低，用Pandas的合并和聚合操作更高效：

import pandas as pd

results = pd.DataFrame([['Ra',0,0],['Co',0,0],['Si',0,0],['Lem',0,0],['Fa',0,0]], columns=['Shops','Stock Q1','Stock Q2'])
forecast = pd.DataFrame([['Ra',1,199,'A'],['Co',2,145,'A'],['Si',2,119,'A'],['Ra',2,133,'B'],['Co',1,123,'B'],['Si',2,111,'A'],['Fa',1,121,'A']], columns=['Shops','Quarter','ForeC','Type'])
df3 = pd.DataFrame([['Ra',25,'A'],['Fa',33,'A'],['Co',30,'A'],['Lem',29,'A'],['Si',22,'A'],['Ra',25,'B'],['Fa',33,'B'],['Co',30,'B'],['Lem',29,'B'],['Si',22,'B']], columns=['Shop','Days','Type'])

# 合并forecast和df3，计算单条记录的乘积
merged = pd.merge(forecast, df3, left_on=['Shops', 'Type'], right_on=['Shop', 'Type'], how='left')
merged['calc'] = merged['ForeC'] * merged['Days']

# 按店铺和季度分组求和
summary = merged.groupby(['Shops', 'Quarter'])['calc'].sum().unstack(fill_value=0)
summary.columns = ['Stock Q1', 'Stock Q2']

# 合并到原始results，保留所有店铺
final_results = pd.merge(results[['Shops']], summary, on='Shops', how='left').fillna(0)
print(final_results)

运行后会得到正确的计算结果：

Shops  Stock Q1  Stock Q2
0    Ra    4975.0    3325.0
1    Co    3690.0    4350.0
2    Si       0.0    5060.0
3   Lem       0.0       0.0
4    Fa    3993.0       0.0

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