时间戳

首页 > 解决方案 > 有条件地计算列的最大值和最小值

python - 有条件地计算列的最大值和最小值

问题描述

我有一个数据框如下：

row_no  stock_name  last_price  Var1
1       SAIL        501.00      0
2       SAIL        501.60      23
3       SAIL        500.00      0
4       SAIL        499.10      0
5       SAIL        499.40      0
6       SAIL        499.40      0
7       SAIL        502.00      0
8       SAIL        497.95      0
9       SAIL        495.55      20
10      SAIL        496.75      0
11      SAIL        496.75      0
12      SAIL        513.00      0
13      SAIL        497.00      0
14      SAIL        497.20      0
15      SAIL        497.00      0
16      SAIL        494.00      0
17      SAIL        497.00      0
18      SAIL        497.00      0
19      SAIL        497.00      0
20      SAIL        496.60      -9
21      SAIL        497.25      0

Var1 不为零时需要计算 Max 和 Min last_price。

当当前 Var1 不等于 0 时，在 Var1 之前的非零值之间计算 Max 和 Min last_price。

示例：对于第 9 行：最大 last_price 计算为 Max(last_price from row 9 to row 2) = 502 和 Min last_price as Min(last_price from row 9 to row 2) = 495.55

我想要最终的数据框如下：

row_no  stock_name  last_price  Var1    Max_LTP Min_LTP
1       SAIL        501 0       0       0       0
2       SAIL        501.6       23      501.6   501
3       SAIL        500 0       0       0       0
4       SAIL        499.1       0       0       0
5       SAIL        499.4       0       0       0
6       SAIL        499.4       0       0       0
7       SAIL        502 0       0       0       0
8       SAIL        497.95      0       0       0
9       SAIL        495.55      20      502     495.55
10      SAIL        496.75      0       0       0
11      SAIL        496.75      0       0       0
12      SAIL        513 0       0       0       0
13      SAIL        497 0       0       0       0
14      SAIL        497.2       0       0       0
15      SAIL        497 0       0       0       0
16      SAIL        494 0       0       0       0
17      SAIL        497 0       0       0       0
18      SAIL        497 0       0       0       0
19      SAIL        497 0       0       0       0
20      SAIL        496.6       -9      513     494
21      SAIL        497.25      0   0   0       0

标签： pythonpython-3.xpandasdataframelist-comprehension

解决方案

使用 Var1shift并cumsum 创建组键，然后groupby将min和max值分配回原始数据帧

mask=df.Var1.ne(0).shift().fillna(0).cumsum()
s=df.groupby(mask).last_price.agg(['min','max'])
s=s[df['Var1'].ne(0).groupby(mask).agg('any')]
s.index=df.index[df.Var1.ne(0)]



df[['Min_LTP','Max_LTP']]=s
df
Out[274]: 
    row_no stock_name  last_price  Var1  Max_LTP  Min_LTP
0        1       SAIL      501.00     0      NaN      NaN
1        2       SAIL      501.60    23    501.6   501.00
2        3       SAIL      500.00     0      NaN      NaN
3        4       SAIL      499.10     0      NaN      NaN
4        5       SAIL      499.40     0      NaN      NaN
5        6       SAIL      499.40     0      NaN      NaN
6        7       SAIL      502.00     0      NaN      NaN
7        8       SAIL      497.95     0      NaN      NaN
8        9       SAIL      495.55    20    502.0   495.55
9       10       SAIL      496.75     0      NaN      NaN
10      11       SAIL      496.75     0      NaN      NaN
11      12       SAIL      513.00     0      NaN      NaN
12      13       SAIL      497.00     0      NaN      NaN
13      14       SAIL      497.20     0      NaN      NaN
14      15       SAIL      497.00     0      NaN      NaN
15      16       SAIL      494.00     0      NaN      NaN
16      17       SAIL      497.00     0      NaN      NaN
17      18       SAIL      497.00     0      NaN      NaN
18      19       SAIL      497.00     0      NaN      NaN
19      20       SAIL      496.60    -9    513.0   494.00
20      21       SAIL      497.25     0      NaN      NaN

推荐阅读