In Python, how to count a number of elements in multiple columns that are higher than element in one column?

Viewed 117

I like to count elements in columns 1 to 3 that are greater than elements in column 0. For example, I have a dataframe as below.

    a   b   c     d
0  50  60  40  20.0
1  40  10  30  40.0
2  30  40  20  35.0
3  20   0  30  25.0
4  10   5  40   NaN

And I want to count elements in columns b,c,d that are greater than element in column a. So the result should be as below.

    a   b   c     d  count
0  50  60  40  20.0      1
1  40  10  30  40.0      0
2  30  40  20  35.0      2
3  20   0  30  25.0      2
4  10   5  40   NaN      1

Thank you for the help in advance.

2 Answers

Use DataFrame.gt along axis=0 to create a boolean mask then use DataFrame.sum along axis=1 on this mask to get the count:

df['count'] = df[['b', 'c', 'd']].gt(df['a'], axis=0).sum(1)

Result:

    a   b   c     d  count
0  50  60  40  20.0      1
1  40  10  30  40.0      0
2  30  40  20  35.0      2
3  20   0  30  25.0      2
4  10   5  40   NaN      1

For each of the columns involved, we can do the comparison and convert the boolean results to int (1 for True, 0 for False):

def greater_value(df, reference, column):
    return (df[column] > df[reference]).astype(int)

And then add up the results:

df['count'] = greater_value(df, 'a', 'b') + greater_value(df, 'a', 'c') + greater_value(df, 'a', 'd')

Or generalize across a supplied set of column names:

def count_greater(df, reference, *columns):
    return sum(greater_value(df, reference, column) for column in columns)

df['count'] = count_greater(df, 'a', 'b', 'c', 'd')
Related