Identify duplicates from a rolling time window in Pandas Dataframe

user_15502660 0
Viewed 368

I have a dataframe where I'd like to identify (and eventually delete) duplicate rows within a sliding time window.

dict={
    'type': ['apple','apple','apple','berry','grape','apple'],
    'attr': ['red','green','red','blue','green','red'],
    'timestamp': [ '2021-03-01 12:00:00',
                  '2021-03-01 12:00:30',
                  '2021-03-01 12:01:13',
                  '2021-03-01 12:01:30',
                  '2021-03-01 12:10:00',
                  '2021-03-01 12:11:00',
                 ]
}
df = pd.DataFrame(dict)
df['is_dup'] = False
print(df)

    type   attr            timestamp  is_dup
0  apple    red  2021-03-01 12:00:00   False
1  apple  green  2021-03-01 12:00:30   False
2  apple    red  2021-03-01 12:01:13   False
3  berry   blue  2021-03-01 12:01:30   False
4  grape  green  2021-03-01 12:10:00   False
5  apple    red  2021-03-01 12:11:00   False

In the example, my goal is to mark a row as a duplicate when 'type' and 'attr' equal another row that occurred within 2 minutes. So I'd like to mark index 2 is_dup=True because it matches index 0 and is within the 2 minute timeframe, but not row 5 because its timestamp doesn't fall within the window.

So the resulting dataframe would look like:

    type   attr            timestamp  is_dup
0  apple    red  2021-03-01 12:00:00   False
1  apple  green  2021-03-01 12:00:30   False
2  apple    red  2021-03-01 12:01:13   True
3  berry   blue  2021-03-01 12:01:30   False
4  grape  green  2021-03-01 12:10:00   False
5  apple    red  2021-03-01 12:11:00   False

Thanks in advance.

pandas python
1 Answers

I am creating a temporary column diff which groups and stores the time difference. Then I separately check if the time difference is less than 2 minutes and then modify is_dup as True.

df['diff'] = df.groupby(['type', 'attr'])['timestamp'].diff().fillna(pd.Timedelta(seconds=0))
df.loc[(df['diff']>pd.Timedelta(0,'m')) & (df['diff']<=pd.Timedelta(2,'m')), 'is_dup'] = True
df=df.drop(['diff'], axis=1)
print(df)

The resulting output is

    type   attr           timestamp  is_dup
0  apple    red 2021-03-01 12:00:00   False
1  apple  green 2021-03-01 12:00:30   False
2  apple    red 2021-03-01 12:01:13    True
3  berry   blue 2021-03-01 12:01:30   False
4  grape  green 2021-03-01 12:10:00   False
5  apple    red 2021-03-01 12:11:00   False
user_19776140
Related