Checking for consecutive value in dataframe on python

user_14411584 0
Viewed 42

Edited: How do i check if there is consecutive value in a dataframe and dropping the data which does not have consecutive value.

The dataframe is as follows:
Animal Speed Year
Falcon 380 2010
Falcon NaN 2011
Falcon 380 2012
Parrot 26 2010
Parrot 90 2012
Bird 20 2010
Bird 10 2012

The outcome that i want to get is to drop the animal which does not have a consecutive year between 2010 to 2012, example, in the above dataframe, the outcome is to drop parrot as it does not have 2011 data.

I have attempt to use df.groupby() to drop the data, however, i could not find a good solution online to work on this.

My dataset comes with 30000 + rows, hence i am thinking of creating a loop to resolve this.


Edit: I am trying to resolve this with groupby and matching the animal that does not fulfil 3 years data

df = pd.DataFrame({'Animal': ['Falcon', 'Falcon', 'Falcon', 'Parrot', 'Parrot', 'Bird', 'Bird'],'Max Speed': [380., np.nan, 380, 26, 90, 20, 10], 'Year': [2010,2011,2012,2010,2012,2010,2012]}

grouped_test_new = df.groupby(['Animal'])

missing = grouped_test_new.filter(lambda x: len(x) < 3)

catmissing = missing['Animal'].unique().tolist()

for i in catmissing:
    index = df[df['Animal'] == i].index

df_drop = df.copy()
df_drop = df_drop.drop(index, inplace = True)

I realise the above doesn't work as the index result only shows the index 5 and 6 for 'Bird' but did not show 'Parrot'.

pandas-groupby python
1 Answers


manage to resolve this by converting index to list and to 1D list

df = pd.DataFrame({'Animal': ['Falcon', 'Falcon', 'Falcon', 'Parrot', 'Parrot', 'Bird', 'Bird'],'Max Speed': [380., np.nan, 380, 26, 90, 20, 10], 'Year': [2010,2011,2012,2010,2012,2010,2012]}

grouped_test_new = df.groupby(['Animal'])

missing = grouped_test_new.filter(lambda x: len(x) < 3)

catmissing = missing['Animal'].unique().tolist()

list = []
for i in catmissing:
    list.append(df[df['Animal'] == i].index.tolist())

one_list = sum(list,[])

df_drop = df.copy()
df_drop.drop(list, inplace = True)
user_144115840
Related