在 pandas 中按组获得最长连续周的连续记录 [英] Get longest streak of consecutive weeks by group in pandas
问题描述
目前我正在处理不同主题的每周数据,但它可能有一些没有数据的长时间连续记录,所以,我想做的是为每个 id<保持连续几周的最长连续记录代码>.我的数据如下所示:
Currently I'm working with weekly data for different subjects, but it might have some long streaks without data, so, what I want to do, is to just keep the longest streak of consecutive weeks for every id
. My data looks like this:
id week
1 8
1 15
1 60
1 61
1 62
2 10
2 11
2 12
2 13
2 25
2 26
我的预期输出是:
id week
1 60
1 61
1 62
2 10
2 11
2 12
2 13
我有点接近,试图在 week
==week.shift()+1
时用 1 标记.问题是这种方法不会标记连续出现的第一次,而且我也无法过滤最长的一次:
I got a bit close, trying to mark with a 1 when week
==week.shift()+1
. The problem is this approach doesn't mark the first occurrence in a streak, and also I can't filter the longest one:
df.loc[ (df['id'] == df['id'].shift())&(df['week'] == df['week'].shift()+1),'streak']=1
根据我的例子,这会带来这个:
This, according to my example, would bring this:
id week streak
1 8 nan
1 15 nan
1 60 nan
1 61 1
1 62 1
2 10 nan
2 11 1
2 12 1
2 13 1
2 25 nan
2 26 1
关于如何实现我想要的任何想法?
Any ideas on how to achieve what I want?
推荐答案
试试这个:
df['consec'] = df.groupby(['id',df['week'].diff(-1).ne(-1).shift().bfill().cumsum()]).transform('count')
df[df.groupby('id')['consec'].transform('max') == df.consec]
输出:
id week consec
2 1 60 3
3 1 61 3
4 1 62 3
5 2 10 4
6 2 11 4
7 2 12 4
8 2 13 4
这篇关于在 pandas 中按组获得最长连续周的连续记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!