How to keep only the consecutive numbers that contain certain value

Question

I have a dataframe that looks like this:

There are several groups in the dataframe, and each group contains more than two consecutive years. For example, in this case, group A contains 2000-2005 and 2010-2015. What I want to do is to keep only the consecutive years that contains the target year. For group A these values will be 2010-2015. Group B will be deleted since none of the consecutive year groups contain the target year 2006. The result should look like follows

I have been thinking about the problem for a few days but didn't have any clue. Any help would be greatly appreciated and thanks a lot for your guidance.

Sincerely

Wilson

[please paste the data as text](https://meta.stackoverflow.com/questions/285551/why-not-upload-images-of-code-on-so-when-asking-a-question). — anky, May 21 '19 at 14:28

score 1 · Accepted Answer · answered May 21 '19 at 14:30

1

So just like how we solved your previous problem , here just need adding another conditions created by any and transform

s1=df.g.groupby([df.g,df.Year.diff().ne(1).cumsum()]).transform('count').ne(1)
s2=df.Year.eq(df['Target Year']).groupby([df.g,df.Year.diff().ne(1).cumsum()]).transform('any')
df[s1&s2]

answered May 21 '19 at 14:30

BENY

317,841
20
164
234

Dear @WeNYoBen thanks a lot for your great help. So I'm guessing "transform('any')df[s1&s2]" should be in the same line right? – yihao ren May 21 '19 at 15:02
@yihaoren no not the same line ,first two line is to get the conditions , last line is to using them to filter – BENY May 21 '19 at 15:07
I think I see. df[s1&s2] is a dataframe that contains s1 and s2 right? – yihao ren May 21 '19 at 15:10
@yihaoren s1 and s2 is True or False bool serise , – BENY May 21 '19 at 15:15

How to keep only the consecutive numbers that contain certain value

1 Answers1