How to keep only the consecutive values in a Pandas dataframe using Python
shift
Get the year diffs as OP first did. Then check if equal to 1
or the previous value is 1
yd = df.Year.groupby(df.group).diff().eq(1)
df[yd | yd.shift(-1)]
group Year
0 A 2000
1 A 2001
2 A 2002
3 A 2003
5 A 2007
6 A 2008
7 A 2009
8 A 2010
9 A 2011
10 B 2005
11 B 2006
12 B 2007
15 B 2013
16 B 2014
17 B 2015
18 B 2016
19 B 2017
Setup
Thx jez
a = [('A',x) for x in range(2000, 2012) if x not in [2004,2006]]
b = [('B',x) for x in range(2005, 2018) if x not in [2008,2010,2012]]
df = pd.DataFrame(a + b, columns=['group','Year'])
If I understand correctly, using diff
and cumsum
create the additional group key, then groupby
it and your group columns, and drop the count
equal to 1.
df[df.g.groupby([df.g,df.Year.diff().ne(1).cumsum()]).transform('count').ne(1)]
Out[317]:
g Year
0 A 2000
1 A 2001
2 A 2002
3 A 2003
5 A 2007
6 A 2008
7 A 2009
8 A 2010
9 A 2011
10 B 2005
11 B 2006
12 B 2007
15 B 2013
16 B 2014
17 B 2015
18 B 2016
19 B 2017
Data
df=pd.DataFrame({'g':list('AAAAAAAAAABBBBBBBBBB',
'Year':[2000,2001,2002,2003,2005,2007,2008,2009,2010,2011,2005,2006,2007,2009,2011,2013,2014,2015,2016,2017])]})