I want to compare the current row value vs the previous 10 rows and check if they are in 80% - 120% of current row. Now I have the count of the result, but I want to show them in a list to tell me the exact result in current row. How can I get it? Please help
This is how I get the count value
s = pd.DataFrame(3 np.random.randn(50,2),columns=list('AB'))
s['vol'] = pd.Series(np.random.randint(low=5, high=10, size = 50))
s['ub'] = s['B']*1.2
s['lb'] = s['B']*0.8
s['inrange_count'] = s['B'].rolling(10).apply(lambda x: np.sum((x<=s.loc[x.index.max(),'B']*1.2) & (x>=s.loc[x.index.max(),'B']*0.8)))
I want to get the value like this in red I want to get a result like I highlighted in red
CodePudding user response:
You can use a custom function with side-effects.
With a list:
def count_list(x, lst=[]):
mask = (x<=s.loc[x.index.max(),'B']*1.2) & (x>=s.loc[x.index.max(),'B']*0.8)
lst.append(mask.tolist())
return mask.sum()
N = 10
lst = []
s['inrange_count'] = s['B'].rolling(N).apply(count_list, kwargs={'lst': lst})
s['lst'] = pd.Series(lst, index=s.index[N-1:])
With a dictionary:
def count_list(x, dic):
idx = x.index.max()
mask = (x<=s.loc[idx,'B']*1.2) & (x>=s.loc[idx,'B']*0.8)
dic[idx] = mask.tolist()
return mask.sum()
N = 10
dic = {}
s['inrange_count'] = s['B'].rolling(N).apply(count_list, kwargs={'dic': dic})
s['lst'] = pd.Series(dic)
Output:
A B vol ub lb inrange_count lst
0 4.764052 3.400157 5 4.080189 2.720126 NaN NaN
1 3.978738 5.240893 9 6.289072 4.192715 NaN NaN
2 4.867558 2.022722 8 2.427267 1.618178 NaN NaN
3 3.950088 2.848643 8 3.418371 2.278914 NaN NaN
4 2.896781 3.410599 6 4.092718 2.728479 NaN NaN
5 3.144044 4.454274 5 5.345128 3.563419 NaN NaN
6 3.761038 3.121675 5 3.746010 2.497340 NaN NaN
7 3.443863 3.333674 6 4.000409 2.666939 NaN NaN
8 4.494079 2.794842 7 3.353810 2.235873 NaN NaN
9 3.313068 2.145904 5 2.575085 1.716723 2.0 [False, False, True, False, False, False, False, False, False, True]
10 0.447010 3.653619 8 4.384342 2.922895 4.0 [False, False, False, True, False, True, True, False, False, True]
11 3.864436 2.257835 6 2.709402 1.806268 3.0 [True, False, False, False, False, False, False, True, False, True]
12 5.269755 1.545634 6 1.854761 1.236507 1.0 [False, False, False, False, False, False, False, False, False, True]
13 3.045759 2.812816 9 3.375379 2.250253 5.0 [False, False, True, True, True, False, False, True, False, True]
14 4.532779 4.469359 5 5.363231 3.575487 3.0 [True, False, False, False, False, True, False, False, False, True]
...