0

I have an Excel sheet where cell G2 has the following forumula:

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

I have this formula horizontally till cell Y2, so that Y2 has

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

Same way, I have this formula vertically till cell G223, so that G223 has

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

Basically, this will be a like matrix.

I want to perform the same operation in Pandas dataframe. How can I do this?

f722axzo5d
  • 21
  • 1
  • 8
  • 3
    Can you provide some sample data and expected output so, we can verify the calculation? – Scott Boston Aug 27 '20 at 18:38
  • 1
    If you are asking question in `pandas`, don't expect other users to be proficient in `excel`. Please go through [`How to make good pandas reproducible`](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) – Ch3steR Aug 27 '20 at 18:43

1 Answers1

0

It's logically exactly the same. Don't know rationale for replicating Excel type logic into 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)}
""")

output

           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
Rob Raymond
  • 29,118
  • 3
  • 14
  • 30