python - 进行 pandas 计算的更好方法是什么？

Question

我有一个按日期时间索引的特征数据框，其间隔是可变的，但大约在[百分之一秒，2秒]左右。一些示例输入:

2018-05-30 01:00:00.177335    0.008845    0.078787    0.075259    0.062903    0.080644      0.070641      0.123609      0.123609      0.379142      0.334873
2018-05-30 01:00:00.197618    0.008165    0.072722    0.069466    0.058061    0.084252      0.065204      0.114090      0.114090      0.349875      0.309034
2018-05-30 01:00:00.198604    0.007582    0.077386    0.084229    0.123003    0.078229     -0.028003      0.046915      0.105932     -0.038534      0.277084
2018-05-30 01:00:00.209151    0.125945    0.072224    0.088524    0.144552    0.172234      0.062866      0.103104      0.098863      0.329249      0.268425
2018-05-30 01:00:00.209000   -0.001327    0.067707    0.082986    0.135505    0.151487      0.058935      0.096654      0.092678      0.100460      0.241702
2018-05-30 01:00:00.209954    0.008740    0.063721    0.078101    0.127524    0.152567      0.055466      0.090963      0.087222      0.094545      0.227452
2018-05-30 01:00:00.211234    0.008255    0.060179    0.073759    0.120431    0.144079      0.052383      0.075903      0.082372     -0.070497      0.005282
2018-05-30 01:00:00.212916    0.078199    0.067070    0.069874    0.114085    0.136485      0.049624      0.081938      0.078034      0.093496      0.215160
2018-05-30 01:00:00.213127    0.074286    0.073802    0.076467    0.108375    0.129652      0.047142      0.057717      0.074129     -0.071906     -0.006338
2018-05-30 01:00:00.246716    0.101065    0.151183    0.143619    0.123444    0.133590      0.044896      0.054967      0.070597     -0.068484     -0.006036
2018-05-30 01:00:00.254647    0.096466    0.144302    0.137082    0.117827    0.127510      0.042855      0.052468      0.067385     -0.065373     -0.005762

我想生成一个“窗口”列表，其中每个窗口都是最后 120 秒的数据，并为不超过 10 秒的最后一个条目创建一个标签。目前，我正在这样做:

for date in df.index:                                                                                                                                                                                                                                         
        window = df.loc[(df.index <= date) & (df.index >= date-datetime.timedelta(seconds=120))]                                                                                                                                                                                        
        if len(window) > 0:                                                                                                                                                                                                                                                                       
            range_ = df.loc[(df.index > date) & (df.index < date+datetime.timedelta(seconds=10))].values                                                                                                                                                                          
            if len(range_) > 0:                                                                                                                                                                                                                                                        
                X.append(window.values)                                                                                                                                                                                                                                                
                y = range_[-1][0]                                                                                                                                                                                                                             
                Y.append(y)  

但这在 215k 行数据帧上需要近 14 分钟。我怎样才能以另一种方式最好地矢量化/加速这个计算？

Answer 1

通过您提供的小样本数据，我已经能够将行按间隔分组。在本示例中，我使用了 20 毫秒 的范围，而不是 120 秒，因此很清楚这些组是否实际出现

# Note the index is formatted to a datetime using pd.to_datetime()
# The index is also sorted using df.sort_index()
df
Out[]: 
                                   1         2         3         4         5         6         7         8         9        10
date                                                                                                                          
2018-05-30 01:00:00.177335  0.008845  0.078787  0.075259  0.062903  0.080644  0.070641  0.123609  0.123609  0.379142  0.334873
2018-05-30 01:00:00.197618  0.008165  0.072722  0.069466  0.058061  0.084252  0.065204  0.114090  0.114090  0.349875  0.309034
2018-05-30 01:00:00.198604  0.007582  0.077386  0.084229  0.123003  0.078229 -0.028003  0.046915  0.105932 -0.038534  0.277084
2018-05-30 01:00:00.209000 -0.001327  0.067707  0.082986  0.135505  0.151487  0.058935  0.096654  0.092678  0.100460  0.241702
2018-05-30 01:00:00.209151  0.125945  0.072224  0.088524  0.144552  0.172234  0.062866  0.103104  0.098863  0.329249  0.268425
2018-05-30 01:00:00.209954  0.008740  0.063721  0.078101  0.127524  0.152567  0.055466  0.090963  0.087222  0.094545  0.227452
2018-05-30 01:00:00.211234  0.008255  0.060179  0.073759  0.120431  0.144079  0.052383  0.075903  0.082372 -0.070497  0.005282
2018-05-30 01:00:00.212916  0.078199  0.067070  0.069874  0.114085  0.136485  0.049624  0.081938  0.078034  0.093496  0.215160
2018-05-30 01:00:00.213127  0.074286  0.073802  0.076467  0.108375  0.129652  0.047142  0.057717  0.074129 -0.071906 -0.006338
2018-05-30 01:00:00.246716  0.101065  0.151183  0.143619  0.123444  0.133590  0.044896  0.054967  0.070597 -0.068484 -0.006036
2018-05-30 01:00:00.254647  0.096466  0.144302  0.137082  0.117827  0.127510  0.042855  0.052468  0.067385 -0.065373 -0.005762

您的解决方案(我只关注问题的这一部分):

%%timeit
X = []
Y = []
for date in df.index:                                                                                                                                                                                                                                         
        window = df.loc[(df.index <= date) & (df.index >= date-datetime.timedelta(seconds=0.02))]                                                                                                                                                                                        
        if len(window) > 0:                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
            X.append(window.values)  

4.06 ms ± 55 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

让我们检查每个间隔中的行数，以确保稍后获得相同的数字:

[len(i) for i in X]
Out[110]: [1, 1, 2, 3, 4, 5, 6, 7, 8, 1, 2]

更快的解决方案

# It's now not necessary for the df to be sorted by datetime
intervals = np.logical_and(
    np.greater_equal.outer(df.index.values, df.index.values - np.timedelta64(20, 'ms')),
    np.less_equal.outer(df.index.values, df.index.values)
)
11.6 µs ± 54.1 ns per loop (mean ± std. dev. of 7 runs, 100000 loops each)

# When you want to use these intervals:
for i in np.arange(intervals.shape[0]):
    # do whatever you want to do with df.iloc[intervals[:, i]]
    print(df.iloc[intervals[:, i]])

再次检查每个间隔的长度:

[len(df.iloc[intervals[:, i]]) for i in np.arange(intervals.shape[0])]
Out[]: [1, 1, 2, 3, 4, 5, 6, 7, 8, 1, 2]

太好了，它们是一样的

我希望这是一个起点!我对 numpy 很陌生，所以这对我来说也很困难!