Skip to main content
 首页 » 编程设计

python之Pandas DataFrame 计算

2025年05月04日13artech

我有一个相当复杂的数据框,如下所示:

df = pd.DataFrame({'0': {('Total Number of End Points', '0.01um', '0hr'): 12, 
  ('Total Number of End Points', '0.1um', '0hr'): 8, 
  ('Total Number of End Points', 'Control', '0hr'): 4, 
  ('Total Number of End Points', '0.01um', '24hr'): 18, 
  ('Total Number of End Points', '0.1um', '24hr'): 12, 
  ('Total Number of End Points', 'Control', '24hr'): 6, 
  ('Total Vessel Length', '0.01um', '0hr'): 12, 
  ('Total Vessel Length', '0.1um', '0hr'): 8, 
  ('Total Vessel Length', 'Control', '0hr'): 4, 
  ('Total Vessel Length', '0.01um', '24hr'): 18, 
  ('Total Vessel Length', '0.1um',  '24hr'): 12, 
  ('Total Vessel Length', 'Control',  '24hr'): 6}, 
  '1': {('Total Number of End Points', '0.01um', '0hr'): 12, 
  ('Total Number of End Points', '0.1um', '0hr'): 8, 
  ('Total Number of End Points', 'Control', '0hr'): 4, 
  ('Total Number of End Points', '0.01um', '24hr'): 18, 
  ('Total Number of End Points', '0.1um', '24hr'): 12, 
  ('Total Number of End Points', 'Control', '24hr'): 6, 
  ('Total Vessel Length', '0.01um', '0hr'): 12, 
  ('Total Vessel Length', '0.1um', '0hr'): 8, 
  ('Total Vessel Length', 'Control', '0hr'): 4, 
  ('Total Vessel Length', '0.01um', '24hr'): 18, 
  ('Total Vessel Length', '0.1um',  '24hr'): 12, 
  ('Total Vessel Length', 'Control',  '24hr'): 6}, 
  '2': {('Total Number of End Points', '0.01um', '0hr'): 12, 
  ('Total Number of End Points', '0.1um', '0hr'): 8, 
  ('Total Number of End Points', 'Control', '0hr'): 4, 
  ('Total Number of End Points', '0.01um', '24hr'): 18, 
  ('Total Number of End Points', '0.1um', '24hr'): 12, 
  ('Total Number of End Points', 'Control', '24hr'): 6, 
  ('Total Vessel Length', '0.01um', '0hr'): 12, 
  ('Total Vessel Length', '0.1um', '0hr'): 8, 
  ('Total Vessel Length', 'Control', '0hr'): 4, 
  ('Total Vessel Length', '0.01um', '24hr'): 18, 
  ('Total Vessel Length', '0.1um',  '24hr'): 12, 
  ('Total Vessel Length', 'Control',  '24hr'): 6}}) 
 
print(df) 
                                                 0   1   2 
        Total Number of End Points 0.01um  0hr   12  12  12 
                                           24hr  18  18  18 
                                   0.1um   0hr    8   8   8 
                                           24hr  12  12  12 
                                   Control 0hr    4   4   4 
                                           24hr   6   6   6 
        Total Vessel Length        0.01um  0hr   12  12  12 
                                           24hr  18  18  18 
                                   0.1um   0hr    8   8   8 
                                           24hr  12  12  12 
                                   Control 0hr    4   4   4 
                                           24hr   6   6   6 

我试图将每个值除以相应控制级别中列的平均值。我尝试了以下但没有用。

df2 = df.divide(df.xs('Control', level=1).mean(axis=1), axis='index') 

我对 python 和 pandas 还很陌生,所以我倾向于用 MS Excel 术语来思考这个问题。

如果在 Excel 中,A1 的公式(“终点总数”、“0.01um”、“0hr”、0)看起来是:

=A1/AVERAGE($A$5:$C$5)

B1 ('Total Number of End Points', '0.01um', '0hr', 1) 将是:

=B1/AVERAGE($A$5:$C$5)

和 A2 ('Total Number of End Points', '0.01um', '24hr', 0) 将是

=A1/AVERAGE($A$6:$C$6)

这个例子的预期结果是:

                                                 0  1  2 
        Total Number of End Points 0.01um  0hr   3  3  3 
                                           24hr  3  3  3 
                                   0.1um   0hr   2  2  2 
                                           24hr  2  2  2 
                                   Control 0hr   1  1  1 
                                           24hr  1  1  1 
        Total Vessel Length        0.01um  0hr   3  3  3 
                                           24hr  3  3  3 
                                   0.1um   0hr   2  2  2 
                                           24hr  2  2  2 
                                   Control 0hr   1  1  1 
                                           24hr  1  1  1 

注意:真实数据中有很多索引和列。

请您参考如下方法:

将 Control 值放在它们自己的列中会有所帮助。您可以使用 unstack 来做到这一点:

df.index.names = ['field', 'type', 'time'] 
df2 = df.unstack(['type']).swaplevel(0, 1, axis=1) 
 
# type                            0.01um 0.1um Control 0.01um 0.1um Control  \ 
#                                      0     0       0      1     1       1    
# field                      time                                              
# Total Number of End Points 0hr      12     8       4     12     8       4    
#                            24hr     18    12       6     18    12       6    
# Total Vessel Length        0hr      12     8       4     12     8       4    
#                            24hr     18    12       6     18    12       6    
 
# type                            0.01um 0.1um Control   
#                                      2     2       2   
# field                      time                        
# Total Number of End Points 0hr      12     8       4   
#                            24hr     18    12       6   
# Total Vessel Length        0hr      12     8       4   
#                            24hr     18    12       6   

现在求出每个对照的平均值:

ave = df2['Control'].mean(axis=1) 
# field                       time 
# Total Number of End Points  0hr     4 
#                             24hr    6 
# Total Vessel Length         0hr     4 
#                             24hr    6 
# dtype: float64 

如您所料,您可以使用 df2.divide 来计算所需的结果。请务必使用 axis=0 告诉 Pandas 根据行索引匹配值(在 df2 和 ave 中)。

result = df2.divide(ave, axis=0) 
# type                            0.01um 0.1um Control 0.01um 0.1um Control  \ 
#                                      0     0       0      1     1       1    
# field                      time                                              
# Total Number of End Points 0hr       3     2       1      3     2       1    
#                            24hr      3     2       1      3     2       1    
# Total Vessel Length        0hr       3     2       1      3     2       1    
#                            24hr      3     2       1      3     2       1    
 
# type                            0.01um 0.1um Control   
#                                      2     2       2   
# field                      time                        
# Total Number of End Points 0hr       3     2       1   
#                            24hr      3     2       1   
# Total Vessel Length        0hr       3     2       1   
#                            24hr      3     2       1   

本质上有您所追求的值(value)观。但是,如果您想重新排列 DataFrame 使其看起来与您发布的完全一样,那么:

result = result.stack(['type']) 
result = result.reorder_levels(['field','type','time'], axis=0) 
result = result.reindex(df.index) 

产量

                                         0  1  2 
field                      type    time          
Total Number of End Points 0.01um  0hr   3  3  3 
                                   24hr  3  3  3 
                           0.1um   0hr   2  2  2 
                                   24hr  2  2  2 
                           Control 0hr   1  1  1 
                                   24hr  1  1  1 
Total Vessel Length        0.01um  0hr   3  3  3 
                                   24hr  3  3  3 
                           0.1um   0hr   2  2  2 
                                   24hr  2  2  2 
                           Control 0hr   1  1  1 
                                   24hr  1  1  1 

综合起来:

df.index.names = ['field', 'type', 'time'] 
df2 = df.unstack(['type']).swaplevel(0, 1, axis=1) 
ave = df2['Control'].mean(axis=1) 
result = df2.divide(ave, axis=0) 
result = result.stack(['type']) 
result = result.reorder_levels(['field','type','time'], axis=0) 
result = result.reindex(df.index)