python - 测量 Pandas 系列列中 NaN 间隙的持续时间

Question

我有一个 pandas DataFrame，其采样模式不规则，每列都有间隙。提供一个具体的例子:

tndx = pd.DatetimeIndex(['2019-01-04 00:00','2019-01-04 03:00',
                         '2019-01-04 10:17','2019-01-04 19:00',
                         '2019-01-05 09:00','2019-01-06 10:00',
                         '2019-01-07 16:00','2019-01-09 14:50'])

data = {"op":['a','a','b','b','b',np.nan,np.nan,np.nan], 
        "setting":[1.,2.,1.,np.nan,np.nan,2.,1.,np.nan]}
df = pd.DataFrame(data=data,index = tndx)


                      op  setting 
2019-01-04 00:00:00    a      1.0 
2019-01-04 03:00:00    a      2.0 
2019-01-04 10:17:00    b      1.0
2019-01-04 19:00:00    b      NaN 
2019-01-05 09:00:00    b      NaN
2019-01-06 10:00:00  NaN      2.0 
2019-01-07 16:00:00  NaN      1.0
2019-01-09 14:50:00  NaN      NaN

我想创建一个相同维度的 DataFrame，其中每个条目代表当前 NaN/非 NaN 状态的持续时间。因此使用分钟的输出将是:

                      op  setting
2019-01-04 00:00:00 3480     1140
2019-01-04 03:00:00 3480     1140
2019-01-04 10:17:00 3480     1140
2019-01-04 19:00:00 3480     2340
2019-01-05 09:00:00 3480     2340
2019-01-06 10:00:00 4610     4610
2019-01-07 16:00:00 4610     4610
2019-01-09 14:50:00 4610      0    

结果的数据类型并不重要——秒/分钟/timedelta。最后的边界情况也不太重要。最好的方法是什么？

Answer 1

我的解决方案获取每列的第一个和下一个连续缺失值和非缺失值的差异:

s = df.index.to_series()
cols = ['op','setting']
for c in cols:
    #test missing values
    miss = df[c].isna()
    #create consecutive groups
    g = miss.ne(miss.shift()).cumsum()
    #aggregate minimal 
    m1 = s.groupby(g).min()
    #get minimal of next groups, last valueis replaced last value of index
    m2 = m1.shift(-1).fillna(df.index[-1])
    #get difference, convert to minutes
    out = m2.sub(m1).dt.total_seconds().div(60).astype(int)
    #map to column
    df[c] = g.map(out)
print (df)

                       op  setting
2019-01-04 00:00:00  3480     1140
2019-01-04 03:00:00  3480     1140
2019-01-04 10:17:00  3480     1140
2019-01-04 19:00:00  3480     2340
2019-01-05 09:00:00  3480     2340
2019-01-06 10:00:00  4610     4610
2019-01-07 16:00:00  4610     4610
2019-01-09 14:50:00  4610        0