Python Pandas Equivalent to Excel Countif

Question

I have tried different solutions where similar questions where made, but didn't get the expected result, so far.

I have two dataframes, df1 and df2, where both of them contain a column named 'fb rq id'.

There are far more lines in df2 than in df1, and I want to check which values from df2['fb rq id'] are in df1['fb rq id'] and how many times it appears there, creating a column on df2 with the number of times the value on that line is in df1.

If you know how to create a column with just binary information wether df2.['fb rq id'] is in df1['fb rq id'] , that also helps.

Thank you!!

"*If you know how to create a column with just binary information wether df2.['fb rq id'] is in df1['fb rq id'] , that also helps.*" This is `df2['id'].isin(df1['id'])` and if you put `.sum()` at the end, you'll get the total number of occurrences. — ayhan, Jul 25 '17 at 18:16
Or `df1['rb rq id'].value_counts()[df2['rb rq id']]` may be more efficienct in some cases. — hilberts_drinking_problem, Jul 25 '17 at 18:27
@YakymPirozhenko wouldn't that raise KeyError's for values in df2 but not in df1? — ayhan, Jul 25 '17 at 18:34
@YakymPirozhenko Maybe something like `df2['rb rq id'].map(df1['rb rq id'].value_counts())`? — ayhan, Jul 25 '17 at 18:37

score 1 · Answer 1 · answered Aug 04 '17 at 06:47

1

how about this?

df2['count_from_df1'] = [list(df1['fb rq id']).count(id) 
                         if id in df1['fb rq id'] else 0 
                         for id in df2['fb rq id']]

answered Aug 04 '17 at 06:47

BGHV

92
4

Python Pandas Equivalent to Excel Countif

1 Answers1