Z-按时间和分组得分

Question

我有以下数据样式的dataframe

2014年8月31日，我试着为style列中的每一家公司(比如F1、F2、F3)计算z-分数(标准化)，我想在该风格内的每个公司(比如建筑材料)中分别计算那个月的z-分数(F1，F2，F3 )。再次在2014年8月31日，我想计算z-评分在风格(例如电子设备，仪器和组件)每一家公司“电子设备，仪器和部件”为该月份。每个月都重复这个过程。回顾一下，首先从日期开始，然后在每种风格中计算z-得分，然后每个月重复。

我首先尝试定义z得分zscr=lambda x：(X-x.均())/x.std()，然后按日期、样式分组，但没有得到想要的结果。

提前谢谢你

         Date  Name                                        Style   ID  \
0   8/31/2014   XYZ                          Construction Materials  ABC   
1   9/30/2014   XYZ                          Construction Materials  ABC   
2  10/31/2014   XYZ                          Construction Materials  ABC   
3  11/30/2014   XYZ                          Construction Materials  ABC   
4   8/31/2014  Acme  Electronic Equipment, Instruments & Components  KYZ   
5   9/30/2014  Acme  Electronic Equipment, Instruments & Components  KYZ   
6  10/31/2014  Acme  Electronic Equipment, Instruments & Components  KYZ   

         F1        F2        F3  
0  0.032111  0.063330  0.027733  
1  0.068824  0.158614  0.032489  
2  0.076838  0.034735  0.020062  
3  0.020903  0.154653  0.056860  
4  0.032807  1.099790  0.233216  
5 -0.014995  0.814866  0.498432  
6 -0.002233  1.954578  0.727823  

2014年8月31日3个名字的风格建筑材料的详细示例

Date    Name    Style   F1  F2  F3  Avg F1  Avg F2  Avg F3  Std F1  Std F2  Std F3  Zscore F1   Zscore F2   Zscore F3
8/31/2014   XYZ Construction Materials  ABC 0.0321  0.0633  0.0277  0.0292  0.5066  0.3623  0.0219  0.5091  0.3078  0.131514468 -0.870730766    -1.087062133
8/31/2014   ABC Construction Materials  XKSD    0.0495  0.3939  0.4258  0.0292  0.5066  0.3623  0.0219  0.5091  0.3078  0.927735574 -0.221422977    0.206304231
8/31/2014   HCAG Construction Materials TETR    0.0061  1.0626  0.6334  0.0292  0.5066  0.3623  0.0219  0.5091  0.3078  -1.059250041    1.092153743 0.880757903

Answer 1

我相信你在找groupby + transform。

names = ['F1', 'F2', 'F3']
zscore = lambda x: (x - x.mean()) / x.std()
df[names] = df.groupby([df.Date, df.Style])[names].transform(zscore)

Answer 2

我把组改为年和公司，并在zscores上过滤。

F1=[0.032111,0.068824,0.076838,0.020903, 0.032807, -0.014995, -0.002233]
F2=[0.063330,0.158614,0.034735,0.154653,1.099790,0.814866,1.954578]
F3=[0.027733,0.032489,0.020062,0.056860,0.233216,0.498432,0.727823]
Date=['8/31/2014','9/30/2014','10/31/2014','11/30/2014','8/31/2014','9/30/2014','10/31/2014']
Name=['XYZ','XYZ','XYZ','XYZ','Acme','Acme','Acme']

df=pd.DataFrame({'f1':F1,'f2':F2,'f3':F3,'date':Date,'name':Name})
df['date']=pd.to_datetime(df['date'],errors='coerce')
df['year']=df['date'].dt.strftime('%Y')
df['f1']=df['f1'].astype(np.float)
df['f2']=df['f2'].astype(np.float)
df['f3']=df['f3'].astype(np.float)
print(df)
splitting=df.groupby(['year','name'])

standardized=splitting['f1','f2','f3'].transform(zscore)
print("\n zscores for f1,f2,f3", standardized)

outliers=(standardized['f1']>1)
print(df.loc[outliers])

   f1        f2        f3       date  name  year
0  0.032111  0.063330  0.027733 2014-08-31   XYZ  2014
1  0.068824  0.158614  0.032489 2014-09-30   XYZ  2014
2  0.076838  0.034735  0.020062 2014-10-31   XYZ  2014
3  0.020903  0.154653  0.056860 2014-11-30   XYZ  2014
4  0.032807  1.099790  0.233216 2014-08-31  Acme  2014
5 -0.014995  0.814866  0.498432 2014-09-30  Acme  2014
6 -0.002233  1.954578  0.727823 2014-10-31  Acme  2014

 zscores for f1,f2,f3
   f1        f2        f3
0 -0.741823 -0.721383 -0.476007
1  0.809296  1.018644 -0.130533
2  1.147886 -1.243571 -1.033224
3 -1.215359  0.946310  1.639764
4  1.366408 -0.392237 -1.253219
5 -0.998952 -0.980577  0.059088
6 -0.367457  1.372814  1.194131

outliers (zscore for f1 >1)
         f1        f2        f3       date  name  year
2  0.076838  0.034735  0.020062 2014-10-31   XYZ  2014
4  0.032807  1.099790  0.233216 2014-08-31  Acme  2014