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
001/01/080.123440NaNNaN102/01/08-0.123440NaNNaN203/01/08-0.123443NaNNaN304/01/08-0.123440NaNNaN405/01/08-0.1234434.01.0506/01/08-0.1234405.00.0607/01/08-0.1234435.00.0708/01/08-0.1234405.00.0809/01/08-0.1234435.00.0910/01/080.1234404.01.01011/01/08-0.1234404.01.01112/01/08-0.1234434.01.01213/01/08-0.1234404.01.01314/01/08-0.1234434.01.01415/01/08-0.1234405.00.01516/01/08-0.1234435.00.01617/01/08-0.1234405.00.01718/01/08-0.1234435.00.01819/01/080.1234404.01.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