I have a dataframe like this,
df = pd.DataFrame({'numb': [2,4,6,2,4,9]})
print(df)
numb
0 2
1 4
2 6
3 2
4 4
5 9
I would like to count the number of rows where numb is lesser than the numb of the previous, like
numb lesser_count
0 2 0
1 4 0
2 6 0
3 2 2
4 4 1
5 9 0
I came across a similar example but not sure how to take the count.
Let's try numpy broadcasting:
a = df['numb'].to_numpy()
df['lesser_count'] = np.triu(a<a[:,None]).sum(0)
Output:
numb lesser_count
0 2 0
1 4 0
2 6 0
3 2 2
4 4 1
5 9 0
We can use numba for a fast for loop implementation:
from numba import njit
@njit
def count_lesser(arr):
counts = np.empty(arr.shape[0])
for i, val in enumerate(arr):
counts[i] = np.sum(val < arr[:i])
return counts
df['lesser_count'] = count_lesser(df['numb'].to_numpy())
print(df)
numb lesser_count
0 2 0
1 4 0
2 6 0
3 2 2
4 4 1
5 9 0
Since we'll need an iterative solution here or to rely on potentially memory inefficient numpy based approaches, numba is a good option:
from numba import njit, int64
@njit('int64[:](int64[:])')
def lesser_prev(a):
out = np.empty(len(a), dtype=int64)
count = 0
for i in range(len(a)):
curr = a[i]
for j in range(i):
if curr<a[j]:
count+=1
out[i] = count
count = 0
return out
df['lesser_count'] = lesser_prev(df.numb.to_numpy())
print(df)
numb lesser_count
0 2 0
1 4 0
2 6 0
3 2 2
4 4 1
5 9 0
Check on performace:
df_ = pd.concat([df]*1000).reset_index()
%timeit lesser_prev(df_.numb.to_numpy())
# 15.2 ms ± 1.05 ms per loop (mean ± std. dev. of 7 runs, 100 loops each)
%timeit count_lesser(df_['numb'].to_numpy())
# 15.3 ms ± 132 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)
%%timeit
a = df_['numb'].to_numpy()
np.triu(a<a[:,None]).sum(0)
# 280 ms ± 11.4 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)