Python Pandas-查找具有最大聚合值的连续组 [英] Python Pandas - find consecutive group with max aggregate values

问题描述

我有一个包含日期时间和整数的数据框

I have a data frame with datetimes and integers

import numpy as np
import pandas as pd

df = pd.DataFrame()
df['dt'] = pd.date_range("2017-01-01 12:00", "2017-01-01 12:30", freq="1min")
df['val'] = np.random.choice(xrange(1, 100), df.shape[0])

给我

                    dt  val
0  2017-01-01 12:00:00   33
1  2017-01-01 12:01:00   42
2  2017-01-01 12:02:00   44
3  2017-01-01 12:03:00    6
4  2017-01-01 12:04:00   70
5  2017-01-01 12:05:00   94*
6  2017-01-01 12:06:00   42*
7  2017-01-01 12:07:00   97*
8  2017-01-01 12:08:00   12
9  2017-01-01 12:09:00   11
10 2017-01-01 12:10:00   66
11 2017-01-01 12:11:00   71
12 2017-01-01 12:12:00   25
13 2017-01-01 12:13:00   23
14 2017-01-01 12:14:00   39
15 2017-01-01 12:15:00   25

如何找到连续的N分钟组 dt给我最大的val总和?

How can I find which N-minute group of consecutive dt gives me the maximum sum of val?

在这种情况下，如果为N=3，则结果应为:

In this case, if N=3, then the result should be:

                    dt  val
5  2017-01-01 12:05:00   94
6  2017-01-01 12:06:00   42
7  2017-01-01 12:07:00   97

(上面标有星星)

推荐答案

您可以使用 np.convolve 以获得正确的起始索引并从那里开始.

You could use np.convolve to get the correct starting index and go from there.

def cons_max(df, N):
    max_loc = np.convolve(df.val, np.ones(N, dtype=int), mode='valid').argmax()
    return df.loc[max_loc:max_loc+N-1]

演示

>>> cons_max(df, 3)
                   dt  val
5 2017-01-01 12:05:00   94
6 2017-01-01 12:06:00   42
7 2017-01-01 12:07:00   97

>>> cons_max(df, 5)
                   dt  val
4 2017-01-01 12:04:00   70
5 2017-01-01 12:05:00   94
6 2017-01-01 12:06:00   42
7 2017-01-01 12:07:00   97
8 2017-01-01 12:08:00   12

这可以有效地使输入中的内核(一个1的数组)滑动"，并将大小为N的窗口中的元素相乘累加在一起.

This works be effectively "sliding" the kernel (array of ones) across our input and multiply-accumulating the elements in our window of size N together.

这篇关于Python Pandas-查找具有最大聚合值的连续组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！