drop row if there are more than 10 similar rows (pandas)

user_18111036 0
Viewed 73

I have encountered an interesting problem regarding sorting and filtering dataframes. What I am trying to do is to merge two data frames with matching time data. However, while trying to do this I stumbled upon a problem. When taking a look at the second data frame I see that the data is recorded with an irregular frequency. Visualized below:

11-Nov-2020 03:24:13,65.2483683333333,21.6083716666667,53,2,0.725775179245232
11-Nov-2020 03:24:13,65.2483683333333,21.6083716666667,53,2,0.726649020300092
11-Nov-2020 03:24:13,65.2483683333333,21.6083716666667,53,2,0.72729159079124
11-Nov-2020 03:24:13,65.2483683333333,21.6083716666667,53,2,0.727677715607114
11-Nov-2020 03:24:13,65.2483683333333,21.6083716666667,53,2,0.728880087146763
11-Nov-2020 03:24:13,65.2483683333333,21.6083716666667,53,2,0.729360473905706
11-Nov-2020 03:24:13,65.2483683333333,21.6083716666667,53,2,0.730057032586954
11-Nov-2020 03:24:13,65.2483683333333,21.6083716666667,53,2,0.73087151228958
11-Nov-2020 03:24:13,65.2483683333333,21.6083716666667,53,2,0.731687795062698
11-Nov-2020 03:24:13,65.2483683333333,21.6083716666667,53,2,0.732025075281149
11-Nov-2020 03:24:13,65.2483683333333,21.6083716666667,53,2,0.732264359809922
11-Nov-2020 03:24:14,65.2483683333333,21.6083716666667,53,2,0.735130987737598
11-Nov-2020 03:24:14,65.2483683333333,21.6083716666667,53,2,0.736691273093919
11-Nov-2020 03:24:14,65.2483683333333,21.6083716666667,53,2,0.740142318585631
11-Nov-2020 03:24:14,65.2483683333333,21.6083716666667,53,2,0.738218394663463
11-Nov-2020 03:24:14,65.248355,21.6086866666667,53.2,2,0.735181734805166
11-Nov-2020 03:24:14,65.248355,21.6086866666667,53.2,2,0.734764836369492
11-Nov-2020 03:24:14,65.248355,21.6086866666667,53.2,2,0.735047479962169
11-Nov-2020 03:24:14,65.248355,21.6086866666667,53.2,2,0.7354234562092
11-Nov-2020 03:24:14,65.248355,21.6086866666667,53.2,2,0.73582921509618
11-Nov-2020 03:24:14,65.248355,21.6086866666667,53.2,2,0.734441003913533
-> 11-Nov-2020 03:24:14,65.24834,21.6090016666667,53.4,2,0.734780511599276

On the last row, you can see it has the same time data as the one above it. However, there are 11 rows with the time 03:24:14 (as you can see above).

Because of this, I am not able to merge with my other dataframe that has a constant frequency of 10hz. That's why I would like to remove the 11th row (in a time period that recorded 11 points with the same time data).

Is this possible? Thank you in advance!

pandas python
3 Answers

You can use counter for each time and store in dict.

I'll write the pseudo code.

counter = dict()

for row in df:
    time = row['time']
    if time not in counter:
        counter[time] = 1
    if counter[time] > 10:
        delete row
    else:
        counter[time] += 1
user_86636430

You could group the dataframe by the time date column and keep only the first 10 rows. To do this, use df.groupby('time_date') to group by the same time data follow by head to return only the first n rows of each group.

df = df.groupby('time_date').head(10)

Keep in mind that Groupby preserves the order of rows within each group., therefore preserving the orderings as passed in.

user_131441430

Data used for testing:

                       t         a          b          c     d  e         f
0   11-Nov-2020 03:24:13  03:24:13  65.248368  21.608372  53.0  2  0.725775
1   11-Nov-2020 03:24:13  03:24:13  65.248368  21.608372  53.0  2  0.726649
2   11-Nov-2020 03:24:13  03:24:13  65.248368  21.608372  53.0  2  0.727292
3   11-Nov-2020 03:24:13  03:24:13  65.248368  21.608372  53.0  2  0.727678
4   11-Nov-2020 03:24:13  03:24:13  65.248368  21.608372  53.0  2  0.728880
5   11-Nov-2020 03:24:13  03:24:13  65.248368  21.608372  53.0  2  0.729360
6   11-Nov-2020 03:24:13  03:24:13  65.248368  21.608372  53.0  2  0.730057
7   11-Nov-2020 03:24:13  03:24:13  65.248368  21.608372  53.0  2  0.730872
8   11-Nov-2020 03:24:13  03:24:13  65.248368  21.608372  53.0  2  0.731688
9   11-Nov-2020 03:24:13  03:24:13  65.248368  21.608372  53.0  2  0.732025
10  11-Nov-2020 03:24:13  03:24:13  65.248368  21.608372  53.0  2  0.732264
11  11-Nov-2020 03:24:14  03:24:14  65.248368  21.608372  53.0  2  0.735131
12  11-Nov-2020 03:24:14  03:24:14  65.248368  21.608372  53.0  2  0.736691
13  11-Nov-2020 03:24:14  03:24:14  65.248368  21.608372  53.0  2  0.740142
14  11-Nov-2020 03:24:14  03:24:14  65.248368  21.608372  53.0  2  0.738218
15  11-Nov-2020 03:24:14  03:24:14  65.248355  21.608687  53.2  2  0.735182
16  11-Nov-2020 03:24:14  03:24:14  65.248355  21.608687  53.2  2  0.734765
17  11-Nov-2020 03:24:14  03:24:14  65.248355  21.608687  53.2  2  0.735047
18  11-Nov-2020 03:24:14  03:24:14  65.248355  21.608687  53.2  2  0.735423
19  11-Nov-2020 03:24:14  03:24:14  65.248355  21.608687  53.2  2  0.735829
20  11-Nov-2020 03:24:14  03:24:14  65.248355  21.608687  53.2  2  0.734441
21  11-Nov-2020 03:24:14  03:24:14  65.248340  21.609002  53.4  2  0.734780

One possible answer:

df = df.drop(index=range(10, len(df), 11)).reset_index(drop=True)

Output:

                       t         a          b          c     d    e         f
0   11-Nov-2020 03:24:13  03:24:13  65.248368  21.608372  53.0  2.0  0.725775
1   11-Nov-2020 03:24:13  03:24:13  65.248368  21.608372  53.0  2.0  0.726649
2   11-Nov-2020 03:24:13  03:24:13  65.248368  21.608372  53.0  2.0  0.727292
3   11-Nov-2020 03:24:13  03:24:13  65.248368  21.608372  53.0  2.0  0.727678
4   11-Nov-2020 03:24:13  03:24:13  65.248368  21.608372  53.0  2.0  0.728880
5   11-Nov-2020 03:24:13  03:24:13  65.248368  21.608372  53.0  2.0  0.729360
6   11-Nov-2020 03:24:13  03:24:13  65.248368  21.608372  53.0  2.0  0.730057
7   11-Nov-2020 03:24:13  03:24:13  65.248368  21.608372  53.0  2.0  0.730872
8   11-Nov-2020 03:24:13  03:24:13  65.248368  21.608372  53.0  2.0  0.731688
9   11-Nov-2020 03:24:13  03:24:13  65.248368  21.608372  53.0  2.0  0.732025
10  11-Nov-2020 03:24:14  03:24:14  65.248368  21.608372  53.0  2.0  0.735131
11  11-Nov-2020 03:24:14  03:24:14  65.248368  21.608372  53.0  2.0  0.736691
12  11-Nov-2020 03:24:14  03:24:14  65.248368  21.608372  53.0  2.0  0.740142
13  11-Nov-2020 03:24:14  03:24:14  65.248368  21.608372  53.0  2.0  0.738218
14  11-Nov-2020 03:24:14  03:24:14  65.248355  21.608687  53.2  2.0  0.735182
15  11-Nov-2020 03:24:14  03:24:14  65.248355  21.608687  53.2  2.0  0.734765
16  11-Nov-2020 03:24:14  03:24:14  65.248355  21.608687  53.2  2.0  0.735047
17  11-Nov-2020 03:24:14  03:24:14  65.248355  21.608687  53.2  2.0  0.735423
18  11-Nov-2020 03:24:14  03:24:14  65.248355  21.608687  53.2  2.0  0.735829
19  11-Nov-2020 03:24:14  03:24:14  65.248355  21.608687  53.2  2.0  0.734441
user_118659560
Related