Remove all rows between two timestamps from pandas dataframe

Question

How to delete all rows from dataframe between two timestamps inclusive?

my Dataframe looks like :

                   b      a      
0 2016-12-02 22:00:00  19.218519 
1 2016-12-02 23:00:00  19.171197  
2 2016-12-03 00:00:00  19.257836  
3 2016-12-03 01:00:00  19.195610  
4 2016-12-03 02:00:00  19.176413

For eg : I want to delete all rows from above dataframe whose timestamp falls is in between : "2016-12-02 22:00:00" to "2016-12-03 00:00:00". So, the result will contain only rows 3 and 4.

the type of b column is datetime64 and the type of a is float.

Please suggest.

I'm getting error : ValueError: labels ['b' ,'a'] not contained in axis — Nikita Gupta, Jun 01 '17 at 20:04

score 9 · Answer 1 · answered Jun 01 '17 at 19:43

9

You can filter those out:

from_ts = '2016-12-02 22:00:00'
to_ts = '2016-12-03 00:00:00'
df = df[(df['b'] < from_ts) | (df['b'] > to_ts)]

answered Jun 01 '17 at 19:43

Tristan Reid

5,844
2
26
31

score 2 · Answer 2 · answered Jun 01 '17 at 19:54

Convert the column b to datetime and then apply mask

df.b = pd.to_datetime(df.b, format = '%Y-%m-%d %H:%M:%S')
df[(df.b < '2016-12-02 22:00:00') | (df.b > '2016-12-03 00:00:00')]

    b                   a
3   2016-12-03 01:00:00 19.195610
4   2016-12-03 02:00:00 19.176413

score 1 · Answer 3 · answered Jun 01 '17 at 20:24

1

index_list= df.b[(df.b >= "2016-12-02 22:00:00") & (df.b <= "2016-12-03 00:00:00")].index.tolist()
df.drop(df.index[index_list] , inplace = True)

answered Jun 01 '17 at 20:24

Nikita Gupta

495
9
24

This worked! With no error of labels not contained in axis. Also, it will remove those rows permanently from dataframe. – Nikita Gupta Jun 01 '17 at 20:25

Remove all rows between two timestamps from pandas dataframe

3 Answers3