Python中的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)

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

我想在数据帧中执行相同的操作。我该怎么做

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