如何根据连续天数对DataFrame进行索引

Question

我有一只熊猫的数据，有一个不规则的日期索引。现在，我想根据连续的连续观察来索引数据。换句话说，如果存在x或更多的连续观测，我只希望保留这些值。

以下列例子为例：

idx = pd.DatetimeIndex(['2003-04-11', '2003-04-12', '2003-04-13','2003-04-17','2003-05-02', '2003-05-03', '2003-05-04','2003-07-23', '2003-07-24'])
df = pd.DataFrame(np.random.random((9,2)),index=idx)
df
              0        1
2003-04-11    0.954287 0.331016    
2003-04-12    0.553477 0.858590    
2003-04-13    0.179510 0.103970     
2003-04-17    0.608664 0.746860     
2003-05-02    0.691829 0.081192     
2003-05-03    0.790748 0.319989     
2003-05-04    0.955903 0.668918     
2003-07-23    0.630201 0.297902     
2003-07-24    0.692403 0.847222 

2003-04-11 ~ 13连续观测3次，2003-04-17观测1次，2003-05-02 ~ 04连续观测3次，以2003-07-23 ~ 24连续观测结束。

我如何索引这些连续3天或更长时间的观测？在本例中，它应保留以下意见：

              0        1
2003-04-11    0.954287 0.331016    
2003-04-12    0.553477 0.858590    
2003-04-13    0.179510 0.103970   
2003-05-02    0.691829 0.081192     
2003-05-03    0.790748 0.319989     
2003-05-04    0.955903 0.668918   

Answer 1

尽管接受了答案，但您可以尝试不同的方法：

df1 = df.loc[df.groupby((~(df.index.to_series().diff() ==  pd.Timedelta(1, unit='d'))).astype(int).cumsum() ).transform(len).iloc[:, 0] == 3]
print df1
                   0         1
2003-04-11  0.350339  0.904514
2003-04-12  0.903141  0.423335
2003-04-13  0.394534  0.803299
2003-05-02  0.158032  0.565684
2003-05-03  0.715311  0.772509
2003-05-04  0.136462  0.533705

一步步地：

print ~(df.index.to_series().diff() ==  pd.Timedelta(1, unit='d'))
#2003-04-11     True
#2003-04-12    False
#2003-04-13    False
#2003-04-17     True
#2003-05-02     True
#2003-05-03    False
#2003-05-04    False
#2003-07-23     True
#2003-07-24    False
#dtype: bool

print (~(df.index.to_series().diff() ==  pd.Timedelta(1, unit='d'))).astype(int)
#2003-04-11    1
#2003-04-12    0
#2003-04-13    0
#2003-04-17    1
#2003-05-02    1
#2003-05-03    0
#2003-05-04    0
#2003-07-23    1
#2003-07-24    0
#dtype: int32
print (~(df.index.to_series().diff() ==  pd.Timedelta(1, unit='d'))).astype(int).cumsum()
#2003-04-11    1
#2003-04-12    1
#2003-04-13    1
#2003-04-17    2
#2003-05-02    3
#2003-05-03    3
#2003-05-04    3
#2003-07-23    4
#2003-07-24    4
#dtype: int32

print df.groupby((~(df.index.to_series().diff() ==  pd.Timedelta(1, unit='d'))).astype(int).cumsum()).transform(len)
#            0  1
#2003-04-11  3  3
#2003-04-12  3  3
#2003-04-13  3  3
#2003-04-17  1  1
#2003-05-02  3  3
#2003-05-03  3  3
#2003-05-04  3  3
#2003-07-23  2  2
#2003-07-24  2  2
print df.groupby((~(df.index.to_series().diff() ==  pd.Timedelta(1, unit='d'))).astype(int).cumsum()).transform(len).iloc[:, 0]
#2003-04-11    3
#2003-04-12    3
#2003-04-13    3
#2003-04-17    1
#2003-05-02    3
#2003-05-03    3
#2003-05-04    3
#2003-07-23    2
#2003-07-24    2
#Name: 0, dtype: float64

print df.groupby((~(df.index.to_series().diff() ==  pd.Timedelta(1, unit='d'))).astype(int).cumsum()).transform(len).iloc[:, 0] == 3
#2003-04-11     True
#2003-04-12     True
#2003-04-13     True
#2003-04-17    False
#2003-05-02     True
#2003-05-03     True
#2003-05-04     True
#2003-07-23    False
#2003-07-24    False
#Name: 0, dtype: bool
print df.loc[df.groupby((~(df.index.to_series().diff() ==  pd.Timedelta(1, unit='d'))).astype(int).cumsum()).transform(len).iloc[:, 0] == 3]
#                   0         1
#2003-04-11  0.120301  0.635707
#2003-04-12  0.747283  0.681601
#2003-04-13  0.118192  0.777899
#2003-05-02  0.481396  0.294547
#2003-05-03  0.619790  0.058048
#2003-05-04  0.179386  0.348843

Answer 2

假设索引是排序的，并且所有的值都是上升的，基本上，当从2行(使用shift)减去行标签时，我们确定了2天内不同的行(使用loc )，然后执行列表理解来生成范围，对它们进行排序，并使用它们使用loc进行索引。

In [133]:
row_labels = df.index[(df.index.to_series() - df.index.to_series().shift(2)) == pd.Timedelta(2, unit='d')]
rows = [x - pd.Timedelta(n, unit='d') for n in range(0,3) for x in row_labels]
rows = sorted(rows)
df.loc[rows]

Out[133]:
                   0         1
2003-04-11  0.352054  0.228887
2003-04-12  0.776784  0.594784
2003-04-13  0.137554  0.852900
2003-05-02  0.589869  0.574012
2003-05-03  0.061270  0.590426
2003-05-04  0.245350  0.340445

您可以看到初始计算的结果：

In [134]:
df.index[(df.index.to_series() - df.index.to_series().shift(2)) == pd.Timedelta(2, unit='d')]

Out[134]:
DatetimeIndex(['2003-04-13', '2003-05-04'], dtype='datetime64[ns]', freq=None)