识别非连续零的索引值

Question

我有一个由负数和零组成的数据帧，还有一个日期时间索引

我希望能够： （1） 确定非连续、非零值的开始和结束日期； （2） 这两个日期之间的天数； （3） 这两个日期之间的最小值

例如，如果我的数据框如下所示：

DATE        VAL  
2007-06-26  0.000000
2007-06-27  0.000000
2007-06-28  0.000000
2007-06-29 -0.006408
2007-07-02  0.000000
2007-07-03  0.000000
2007-07-04 -0.000003
2007-07-05  0.000000
2007-07-06  0.000000
2007-07-09  0.000000
2007-07-10 -0.018858
2007-07-11 -0.015624
2007-07-12  0.000000
2007-07-13  0.000000
2007-07-16 -0.008562
2007-07-17 -0.006587

我希望输出如下所示：

START        END          DAYS  MIN
2007-06-29   2007-06-29   1     -0.006408
2007-07-04   2007-07-04   1     -0.000003
2007-07-10   2007-07-11   2     -0.018858
2007-07-16   2007-07-17   2     -0.008562

如果将天数排除在周末之外（即7/13到7/16算作1天），这会更好，但我意识到这通常很复杂

numpy.argmax/min方法似乎实现了我想要的版本，但是根据文档设置axis=1并没有返回我期望的索引值集合

编辑：应已指定，以查找不需要循环的解决方案

Answer 1

这一个与最初的解决方案（Allen）有一些相似的逻辑，但较少“适用”。不确定性能比较

# a new group begins when previous value is 0, but current is negative
df['NEW_GROUP'] = df['VAL'].shift(1) == 0
df['NEW_GROUP'] &= df['VAL'] < 0

# Group by the number of times a new group has showed up, which determines the group number.
# Directly return a Series from `apply` to obviate further transformations
print(df.loc[df['VAL'] < 0]
        .groupby(df['NEW_GROUP'].cumsum())
        .apply(lambda x: pd.Series([x.DATE.iloc[0], x.DATE.iloc[-1], x.VAL.min(), len(x)],
                        index=['START','END','MIN','DAYS'])))

输出：

          START      END         MIN         DAYS
NEW_GROUP                                      
1         2007-06-29 2007-06-29 -0.006408     1
2         2007-07-04 2007-07-04 -0.000003     1
3         2007-07-10 2007-07-11 -0.018858     2
4         2007-07-16 2007-07-17 -0.008562     2

Answer 2

使用^{}在0.25+下工作的溶液：

#convert DatetimeIndex to column
df = df.reset_index()
#filter values equal 0
m = df['VAL'].eq(0)
#create groups only for non 0 rows filtering with inverting mask by ~
g = m.ne(m.shift()).cumsum()[~m]
#aggregation by groups
df1 = df.groupby(g).agg(START=('DATE','first'),
                        END=('DATE','last'),
                        DAYS= ('DATE', 'size'),
                        MIN=('VAL','min')).reset_index(drop=True)
print (df1)
       START        END  DAYS       MIN
0 2007-06-29 2007-06-29     1 -0.006408
1 2007-07-04 2007-07-04     1 -0.000003
2 2007-07-10 2007-07-11     2 -0.018858
3 2007-07-16 2007-07-17     2 -0.008562

熊猫解决方案<；0.25可以将dictionary传递到agg和最后设置的新列名：

df = df.reset_index()
m = df['VAL'].eq(0)
g = m.ne(m.shift()).cumsum()[~m]

df1 = df.groupby(g).agg({'DATE':['first','last','size'], 'VAL':'min'}).reset_index(drop=True)
df1.columns = ['START','END','DAYS','MIN']
print (df1)
       START        END  DAYS       MIN
0 2007-06-29 2007-06-29     1 -0.006408
1 2007-07-04 2007-07-04     1 -0.000003
2 2007-07-10 2007-07-11     2 -0.018858
3 2007-07-16 2007-07-17     2 -0.008562

Answer 3

首先创建一个标志来查找非零记录并将其分配到相同的组中，然后创建groupby并计算所需的属性

(
    df.assign(Flag = np.where(df.VAL.ge(0), 1, np.nan))
    .assign(Flag = lambda x: x.Flag.fillna(x.Flag.cumsum().ffill()))
    .loc[lambda x: x.Flag.ne(1)]
    .groupby('Flag')
    .apply(lambda x: [x.DATE.iloc[0], x.DATE.iloc[-1], len(x), x.VAL.min()])
    .apply(pd.Series)
    .set_axis(['START','END','DAYS','MIN'], axis=1, inplace=False)
)


        START       END         DAYS    MIN
Flag                
3.0     2007-06-29  2007-06-29  1   -0.006408
5.0     2007-07-04  2007-07-04  1   -0.000003
8.0     2007-07-10  2007-07-11  2   -0.018858
10.0    2007-07-16  2007-07-17  2   -0.008562