Count values in previous rows that are greater than current row value

user_13902120 0
Viewed 1015

I want to find the count for the number of previous rows that have the a greater value than the current row in a column and store it in a new column. It would be like a rolling countif that goes back to the beginning of the column. The desired example output below shows the value column given and the count column I want to create.

Desired Output:
Value  Count
5      0 
7      0
4      2
12     0
3      4
4      3
1      6

I plan on using this code with a large dataframe so the fastest way possible is appreciated.

countif pandas python loops
4 Answers

We can do subtract.outer from numpy , then get lower tri and find the value is less than 0, and sum the value per row

a = np.sum(np.tril(np.subtract.outer(df.Value.values,df.Value.values), k=0)<0, axis=1)
# results in array([0, 0, 2, 0, 4, 3, 6])
df['Count'] = a
user_79645270

IMPORTANT: this only works with pandas < 1.0.0 and the error seems to be a pandas bug. An issue is already created at https://github.com/pandas-dev/pandas/issues/35203

We can do this with expanding and applying a function which checks for values that are higher than the last element in the expanding array.

import pandas as pd
import numpy as np
# setup
df = pd.DataFrame([5,7,4,12,3,4,1], columns=['Value'])
# calculate countif
df['Count'] = df.Value.expanding(1).apply(lambda x: np.sum(np.where(x > x[-1], 1, 0))).astype('int')

Input

    Value
0   5
1   7
2   4
3   12
4   3
5   4
6   1

Output

    Value   Count
0   5        0
1   7        0
2   4        2
3   12       0
4   3        4
5   4        3
6   1        6
user_95343900 
count = []   
for i in range(len(values)):
       count = 0
       for j in values[:i]:
           if values[i] < j: 
              count += 1
       count.append(count)
user_139004200

The below generator will do what you need. You may be able to further optimize this if needed.


def generator (data) :
    i=0
    count_dict ={} 
    while i<len(data) :
        m=max(data)
        v=data[i] 
        count_dict[v] =count_dict[v] +1 if v in count_dict else 1
        
        t=sum([(count_dict[j] if j in count_dict else 0) for j in range(v+1,m)])
        i +=1
        yield t

d=[1, 5,7,3,5,8]
foo=generator (d)
result =[b for b in foo] 
print(result)
user_102598130
Related