Get Count Of All Positive Values From A The Last 150 Rows At Each Row - Pandas

I have the following dataset where I have the column Date and Values for each row. It has both +ve and -ve values. I have to get a count of all positive values for the last 150 row

Solution 1:

You want to use pd.rolling() to perform a rolling count of the positives and negatives given the previous 'period' count.

period = 5
df['less_than_zero'] = (df['values']
                        .rolling(window=period, min_periods=period)
                        .agg(lambda x: (x < 0).sum()))

df['greater_than_zero'] = (df['values']
                          .rolling(window=period,min_periods=period)
                          .agg(lambda x: (x > 0).sum()))

This should give you what you want

Out[30]: 
         date    values  less_than_zero  greater_than_zero
0   01/01/08  0.123440             NaN                NaN
1   02/01/08 -0.123440             NaN                NaN
2   03/01/08 -0.123443             NaN                NaN
3   04/01/08 -0.123440             NaN                NaN
4   05/01/08 -0.123443             4.0                1.0
5   06/01/08 -0.123440             5.0                0.0
6   07/01/08 -0.123443             5.0                0.0
7   08/01/08 -0.123440             5.0                0.0
8   09/01/08 -0.123443             5.0                0.0
9   10/01/08  0.123440             4.0                1.0
10  11/01/08 -0.123440             4.0                1.0
11  12/01/08 -0.123443             4.0                1.0
12  13/01/08 -0.123440             4.0                1.0
13  14/01/08 -0.123443             4.0                1.0
14  15/01/08 -0.123440             5.0                0.0
15  16/01/08 -0.123443             5.0                0.0
16  17/01/08 -0.123440             5.0                0.0
17  18/01/08 -0.123443             5.0                0.0
18  19/01/08  0.123440             4.0                1.0

Note: It's worth throwing a few 0s into the sample data set to ensure that you are not miss-attributing them in this case. (We're not, but still)

Solution 2:

This might be what you are looking for:

import numpy as np

tail = df.tail(5)
pos = len(tail[df['values']>0])
neg = len(tail[df['values']<0])

df['pos_values'], df['neg_values'] = np.nan, np.nan
df.loc[df.index.values[-5:], 'pos_values'] = pos
df.loc[df.index.values[-5:], 'neg_values'] = neg

#         Date    values  pos_values  neg_values
# 0   01/01/08  0.123440         NaN         NaN
# 1   02/01/08 -0.123440         NaN         NaN
# 2   03/01/08 -0.123443         NaN         NaN
# 3   04/01/08 -0.123440         NaN         NaN
# 4   05/01/08 -0.123443         NaN         NaN
# 5   06/01/08 -0.123440         NaN         NaN
# 6   07/01/08 -0.123443         NaN         NaN
# 7   08/01/08 -0.123440         NaN         NaN
# 8   09/01/08 -0.123443         NaN         NaN
# 9   10/01/08  0.123440         NaN         NaN
# 10  11/01/08 -0.123440         NaN         NaN
# 11  12/01/08 -0.123443         NaN         NaN
# 12  13/01/08 -0.123440         NaN         NaN
# 13  14/01/08 -0.123443         NaN         NaN
# 14  15/01/08 -0.123440         1.0         4.0
# 15  16/01/08 -0.123443         1.0         4.0
# 16  17/01/08 -0.123440         1.0         4.0
# 17  18/01/08 -0.123443         1.0         4.0
# 18  19/01/08  0.123440         1.0         4.0