python - 如何统计一个时间段的连续出现次数？ - Pandas 蟒

Question

我有一个这样的数据框:

datetime              levels       shear
2016-05-01 00:10:00    100           5
2016-05-01 00:10:00    200           6 
2016-05-01 00:10:00    300           10
2016-05-01 00:10:00    400           11
2016-05-01 00:20:00    100           7  
2016-05-01 00:20:00    300           10
2016-05-01 00:30:00    100           5
2016-05-01 00:30:00    200           9
2016-05-01 00:30:00    300           12
2016-05-01 00:30:00    400           15
2016-05-01 00:40:00    100           19
2016-05-01 00:40:00    200           2
2016-05-01 00:40:00    300           18

这是每10分钟间隔在不同高度的风切变。

我想做的是按级别对其进行分组，然后计算每个级别的连续发生的剪切。因此，首先我创建另一列，它是每个连续数据组按级别的时间差:

data3_5['Delta'] = data3_5.sort_values(['levels','datetimes']).groupby('levels')['datetimes'].diff()

然后定义一个函数，对连续出现的相同时间差进行滚动计数:

  def rolling_count(val):
    if val == rolling_count.previous:
       rolling_count.count +=1
    else:
       rolling_count.previous = val
       rolling_count.count = 1
  return rolling_count.count
 rolling_count.count = 0 #static variable
 rolling_count.previous = None #static variable

应用该函数并添加一个新列:

data3_5['count'] = data3_5.sort_values(['levels','datetimes']).groupby('levels')['index'].diff().apply(rolling_count)

然后最后打印出结果:

group = data3_5.groupby('levels', as_index=False)
group_keys = sorted(list(group.groups.keys()))

for each in range(0,len(group_keys)):
    group_result = group.get_group(group_keys[each])
    print(group_result)

部分结果如下:

datetime              levels  shear  Delta     count
2016-05-01 00:10:00   100     5      NaT       1
2016-05-01 00:20:00   100     7      00:10:00  1
2016-05-01 00:30:00   100     5      00:10:00  2
2016-05-01 00:40:00   100     19     00:10:00  3
datetime              levels  shear  Delta     count
2016-05-01 00:10:00   200     6      NaT       1
2016-05-01 00:30:00   200     9      00:20:00  1
2016-05-01 00:40:00   200     2      00:10:00  1

这不是我们想要的结果，因为会有两个问题:1. 每个级别的第一个计数将被视为 NaT，因此下一个连续出现的值将被视为第一次出现。2. 与问题1类似，对于200级，第三个结果应计为2。

我该如何解决这个问题？谢谢。

Answer 1

让我们试试:

首先让我们确保 df['datetime'] 确实是 datetime 对象:

df['datetime'] = pd.to_datetime(df['datetime'])

df['Delta'] = df.groupby('levels')['datetime'].transform(lambda x: x-x.shift(1))
df['count'] = df.groupby('levels')['Delta'].transform(lambda x : x.le(pd.Timedelta('10 minutes')).cumsum())
df.sort_values(by='levels')

输出:

              datetime  levels  shear    Delta  count
0  2016-05-01 00:10:00     100      5      NaT      0
4  2016-05-01 00:20:00     100      7 00:10:00      1
6  2016-05-01 00:30:00     100      5 00:10:00      2
10 2016-05-01 00:40:00     100     19 00:10:00      3
1  2016-05-01 00:10:00     200      6      NaT      0
7  2016-05-01 00:30:00     200      9 00:20:00      0
11 2016-05-01 00:40:00     200      2 00:10:00      1
2  2016-05-01 00:10:00     300     10      NaT      0
5  2016-05-01 00:20:00     300     10 00:10:00      1
8  2016-05-01 00:30:00     300     12 00:10:00      2
12 2016-05-01 00:40:00     300     18 00:10:00      3
3  2016-05-01 00:10:00     400     11      NaT      0
9  2016-05-01 00:30:00     400     15 00:20:00      0