python - Count values greater than in another dataframe based on values in existing dataframe

Question

I have two python pandas dataframes, in simplified form they look like this:

DF1

+-----+-----+-----+-----+-----+-----+
| a_1 | a_2 | a_3 | a_4 | a_5 | a_6 |
+-----+-----+-----+-----+-----+-----+
|   0 |   2 |   5 |   0 |   2 |   5 |
|   1 |   3 |   4 |   1 |   3 |   4 |
|   1 |   0 |   3 |   0 |   0 |   1 |
+-----+-----+-----+-----+-----+-----+

DF2

+-----+
|   A | 
+-----+
| a_1 | 
| a_2 | 
| a_3 |
| a_4 | 
| a_5 | 
| a_6 | 
+-----+

I want to create a count column of values (per row) in DF2, if each of the value in column A (DF2) equals to column names in DF1 then count number greater than zero in selected column

Desire output:

+-----+-------+
|   A | Count | 
+-----+-------+
| a_1 |   2   |  
| a_2 |   2   | 
| a_3 |   3   | 
| a_4 |   1   | 
| a_5 |   2   | 
| a_6 |   3   | 
+-----+-------+

Appreciate any comment and feedback, thanks in advance

question from:https://stackoverflow.com/questions/65883507/count-values-greater-than-in-another-dataframe-based-on-values-in-existing-dataf

Answer 1

Use Series.map with compared values in DF1 by DataFrame.gt and count Trues by sum:

DF2['Count'] = DF2['A'].map(DF1.gt(0).sum())
print (DF2)
     A  Count
0  a_1      2
1  a_2      2
2  a_3      3
3  a_4      1
4  a_5      2
5  a_6      3