Home > database >  How to show a list in one row based on condition results of previous rows in python/pandas
How to show a list in one row based on condition results of previous rows in python/pandas

Time:02-01

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]
...
  • Related