除法类型复合指标异动贡献度计算

经常遇到除法类型复合指标异动贡献度的计算，公式可以轻易搜到，比如xhs，简单来说如下

#结构变化贡献值
##（子群体指标值after-总体指标值before）*(子群体结构比例after-子群体结构比例before)
#指标变化贡献值
##（子群体指标值after-子群体指标值before）*(子群体结构比例before)
1
2
3
4

每次对着标准公式手拉excel,很烦，干脆写了个模版，留给自己以及有缘人~

• 数据样式
  demo数据
  

• 完整代码

import pandas as pd
#读取数据
data=pd.read_excel('demo.xlsx')
data.columns=['label', 'dim', 'value_up', 'value_down']
data['cal_value']=data['value_up']/data['value_down']
data['value_up_all']=data.groupby('label')['value_up'].transform(sum)
data['value_down_all']=data.groupby('label')['value_down'].transform(sum)
data['cal_value_all']=data['value_up_all']/data['value_down_all']
data['proportion']=data['value_down']/data['value_down_all']
##计算贡献值
t1=pd.pivot_table(data=data,index=['dim'],columns=['label'],values=['proportion','cal_value','cal_value_all']).reset_index()
t1.columns = t1.columns.droplevel(level=1)
t1.columns=['dim','after_cal_value','before_cal_value','after_cal_value_all','before_cal_value_all','after_proportion','before_proportion']
#结构变化贡献值
##（子群体指标值after-总体指标值before）*(子群体结构比例after-子群体结构比例before)
t1['propotion_change']=t1.apply(lambda x: (x.after_cal_value-x.before_cal_value_all)*(x.after_proportion-x.before_proportion),axis=1)
#指标变化贡献值
##（子群体指标值after-子群体指标值before）*(子群体结构比例before)
t1['cal_value_change']=t1.apply(lambda x:(x.after_cal_value-x.before_cal_value)*x.before_proportion,axis=1)
t1['all_change']=t1['cal_value_change']+t1['propotion_change']
t1=t1[['dim','before_proportion','after_proportion','before_cal_value','after_cal_value','propotion_change','cal_value_change','all_change','before_cal_value_all','after_cal_value_all']]
#关联原始数据
t3=pd.pivot_table(data=data,index=['dim'],columns=['label'],values=['value_up','value_down']).reset_index()
t3.columns = t3.columns.droplevel(level=1)
t3.columns=['dim','after_value_down','before_value_down','after_value_up','before_value_up']
t3=t3[['dim','before_value_up','before_value_down','after_value_up','after_value_down']]
t2=pd.merge(t1,t3,on=['dim'],how='left')
#导出数据
t2.to_clipboard(excel=True)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

• 计算结果
  
  进一步简单处理，仅仅30S即可解决之前需要10min的任务~