基于外层DataFrame行过滤内层DataFrame及动态计算实现方案

目标 #

基于外层DataFrame（门店列表）的行及其值，过滤内层DataFrame（销售记录），展示各门店的预算销售额、实际销售额及两者差值。

数据定义 #

三个初始DataFrame：

• 门店列表：

import pandas as pd

Shop = pd.DataFrame({
    'Shop_number': ['SH10','SH20', 'SH30','SH40' ,'SH50','SH60','SH70'],
    'Shop': ['Chisinau','Milan', 'Paris','Madrid', 'Lyon','London', 'foo']
})

• 实际销售记录：

Sales = pd.DataFrame({
    'Shop_number': ['SH20', 'SH30', 'SH50','SH70'],
    'Amount':[200,400,50,1000],
    'Type' : 'Actual'
})

• 预算销售记录：

Budget = pd.DataFrame({
    'Shop_number': ['SH10','SH20', 'SH30','SH40' ,'SH50','SH60','SH70'],
    'Amount':[100,150,200,300,40,500,150],
    'Type' : 'Budget'
})

合并为基础数据：

base = pd.concat([Sales, Budget])

尝试的错误代码及报错 #

尝试通过assign动态关联门店编号时触发报错：

(Shop
    .assign(
        Budget = lambda x : 
            base.loc[(base['Shop_number']==x.Shop_number) & (base['Type']=='Budget')].loc[:,'Amount'].sum(),
        Actual = lambda x : 
            base.loc[(base['Shop_number']==x.Shop_number) & (base['Type']=='Actual')].loc[:,'Amount'].sum(),
        var = lambda x : 
            x.Budget-x.Actual
    )
)

报错信息：

ValueError: Can only compare identically-labeled Series objects

解答 #

1. 修改现有代码实现动态关联 #

报错原因是x.Shop_number是整个Series，与base['Shop_number']比较时会按索引对齐，两者索引不匹配导致错误。改用apply逐行处理每个门店编号即可解决：

(Shop
    .assign(
        Budget = lambda df: df['Shop_number'].apply(lambda sn: base.loc[(base['Shop_number'] == sn) & (base['Type'] == 'Budget'), 'Amount'].sum()),
        Actual = lambda df: df['Shop_number'].apply(lambda sn: base.loc[(base['Shop_number'] == sn) & (base['Type'] == 'Actual'), 'Amount'].sum()),
        var = lambda df: df['Budget'] - df['Actual']
    )
)

通过df['Shop_number'].apply()逐个提取门店编号sn，再去base中过滤对应记录并求和，避免了Series间的索引对齐问题。

2. 更优的实现方式 #

推荐使用分组聚合+宽表转换+合并的矢量化操作，效率远高于逐行apply，尤其适合大数据量场景：

# 1. 按门店编号和销售类型分组求和，转换为宽表
agg_base = base.groupby(['Shop_number', 'Type'])['Amount'].sum().unstack(fill_value=0)

# 2. 与门店列表左连接，确保所有门店都被保留
result = Shop.merge(agg_base, on='Shop_number', how='left')

# 3. 计算差值
result['var'] = result['Budget'] - result['Actual']

这种方式利用pandas的矢量化操作替代循环，代码更简洁，执行效率更高，同时逻辑更清晰。

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