基于上一行计算列值

Question

我想在每一行中添加一个新的Y列，它将告诉我在过去10个记录中X列val大于>；1的百分比

stock price history

   ticker       date    adj_open   ad_close       X(%) 
0    ABC     2017-10-06   12.10      13.11        8.0
1    ABC     2017-12-05   11.11      11.87        5.0
2    ABC     2017-12-04   12.08      11.40       -7.0
3    ABC     2017-12-03   12.01      13.03       10.1
4    ABC     2017-07-04   9.01        9.59        8.0
5    ABC     2017-07-03   7.89        8.19        4.0

Resultant transformed data set

    ticker       date    adj_open ad_close    X(%)     Y(%)    
0    ABC     2017-10-06   12.10    13.11      8.0        80
1    ABC     2017-12-05   11.11    11.87      5.0        75
2    ABC     2017-12-04   12.08    11.40     -7.0       100
3    ABC     2017-12-03   12.01    13.03     10.1       100
4    ABC     2017-07-04   9.01     9.59       8.0       100
5    ABC     2017-07-03   7.89     8.19       4.0        0

Answer 1

您有：

df
    ticker  date        adj_open    ad_close    X(%)
0   ABC     2017-10-06  12.10       13.11       8.0
1   ABC     2017-12-05  11.11       11.87       5.0
2   ABC     2017-12-04  12.08       11.40      -7.0
3   ABC     2017-12-03  12.01       13.03       10.1
4   ABC     2017-07-04  9.01        9.59        8.0
5   ABC     2017-07-03  7.89        8.19        4.0

让我们定义window和一个计算所需数量的函数：

w = 2
def count_pcnt(x, window = w):
    return (np.sum(x>1)/window)*100.0

最后，让我们应用函数：

df["Y(%)"] = df["X(%)"].rolling(window=w).apply(count_pcnt)
df

    ticker  date        adj_open    ad_close    X(%)    Y(%)
0   ABC     2017-10-06  12.10       13.11       8.0     NaN
1   ABC     2017-12-05  11.11       11.87       5.0     100.0
2   ABC     2017-12-04  12.08       11.40      -7.0     50.0
3   ABC     2017-12-03  12.01       13.03      10.1     50.0
4   ABC     2017-07-04  9.01         9.59       8.0     100.0
5   ABC     2017-07-03  7.89         8.19       4.0     100.0

您可以将w更改为10，因为您有更多的数据。你知道吗

编辑

如果您愿意：

w=4
df["Y(%)"] = df["X(%)"].rolling(window=w).apply(lambda x: count_pcnt(x, window = w))

df
    ticker  date        adj_open    ad_close    X(%)    Y(%)
0   ABC     2017-10-06  12.10       13.11       8.0     NaN
1   ABC     2017-12-05  11.11       11.87       5.0     NaN
2   ABC     2017-12-04  12.08       11.40      -7.0     NaN
3   ABC     2017-12-03  12.01       13.03      10.1     75.0
4   ABC     2017-07-04  9.01        9.59        8.0     75.0
5   ABC     2017-07-03  7.89        8.19        4.0     75.0

编辑2

w=4 # specify the desired window
df["Y(%)"] = df["X(%)"].rolling(window=w).apply(lambda x: (np.sum(x>1)/x.shape[0])* 100.0)

编辑3

w=4
df["Y(%)"] = df["X(%)"].rolling(window=w
                                ,min_periods = 0).apply(lambda x: (np.sum(x>1)/x.shape[0])* 100.0)
df

ticker  date    adj_open    ad_close    X(%)    Y(%)
0   ABC 2017-10-06  12.10   13.11   8.0     100.000000
1   ABC 2017-12-05  11.11   11.87   5.0     100.000000
2   ABC 2017-12-04  12.08   11.40   -7.0    66.666667
3   ABC 2017-12-03  12.01   13.03   10.1    75.000000
4   ABC 2017-07-04  9.01    9.59    8.0     75.000000
5   ABC 2017-07-03  7.89    8.19    4.0     75.000000

Answer 2

试着用tryexcept做一个simple循环，这是基于你的示例输出，试着根据你的data修改它

n=5 #your example
df['boolean']=df['X(%)']>1
A=[]
for i in range(len(df)):
     try :
         A.append(sum(df.boolean[i+1:i+n+1])/len(df.boolean[i+1:i+n+1]))
     except:
         A.append(0)

df['Y(%)']=A


df

     ticker       date  adj_open  ad_close   X(%) boolean Y(%)
   0    ABC  10/6/2017     12.10     13.11   8.0   True  0.80
   1    ABC  12/5/2017     11.11     11.87   5.0   True  0.75
   2    ABC  12/4/2017     12.08     11.40  -7.0  False  1.00
   3    ABC  12/3/2017     12.01     13.03  10.1   True  1.00
   4    ABC   7/4/2017      9.01      9.59   8.0   True  1.00
   5    ABC   7/3/2017      7.89      8.19   4.0   True  0.00