python - Python Pandas 中的 Excel IF 和 COUNTIF

Question

我有一个 Excel 表，其中单元格 G2 有以下论坛：

=IF(COUNTIF($C2:$F2,G$1)>0,$Z2/$B2,0)

我有这个公式水平直到单元格 Y2，所以 Y2 有

=IF(COUNTIF($C2:$F2,Y$1)>0,$Z2/$B2,0)

同样，我有这个公式垂直直到单元格 G223，所以 G223 有

=IF(COUNTIF($C223:$F223,G$1)>0,$Z223/$B223,0)

基本上，这将是一个相似矩阵。

我想在 Pandas 数据框中执行相同的操作。我怎样才能做到这一点？

标签： pythonexcelpandasdataframecountif

Answer 1

逻辑上完全一样。不知道将 Excel 类型逻辑复制到的基本原理pandas

import numpy as np
df = pd.DataFrame({"A":["","London","Singapore","London","KL","New York"],"B":["London","","","","",""],"C":["","","","","",""],"D":[25,10,"","","",""],"E":["","","","","",""],"F":["","","","","",""],"G":["","","","","",""]})

print(f"""{df.to_string()}
np.where((df.loc[1:5,'A']==df.loc[0,'B']).sum()>0,(df.loc[0,"D"]/df.loc[1,"D"]),0)
{np.where((df.loc[1:5,'A']==df.loc[0,'B']).sum()>0,(df.loc[0,"D"]/df.loc[1,"D"]),0)}
np.where((df.loc[1:5,'A']==df.loc[0,'C']).sum()>0,(df.loc[0,"D"]/df.loc[1,"D"]),0)
{np.where((df.loc[1:5,'A']==df.loc[0,'C']).sum()>0,(df.loc[0,"D"]/df.loc[1,"D"]),0)}
""")

输出

           A       B C   D E F G
0             London    25      
1     London            10      
2  Singapore                    
3     London                    
4         KL                    
5   New York                    
np.where((df.loc[1:5,'A']==df.loc[0,'B']).sum()>0,(df.loc[0,"D"]/df.loc[1,"D"]),0)
2.5
np.where((df.loc[1:5,'A']==df.loc[0,'C']).sum()>0,(df.loc[0,"D"]/df.loc[1,"D"]),0)
0.0