How to filter rows based on datetime index

Question

I have a dataframe that has an index of date type and contains hourly data per day.

I need to take only the rows which have the last record of the day 
and filter out REST OF THE RECORDS FOR THAT DAY ONLY... 

and do the same for other days.

                   COL-A
DATE    
2014-01-01 00:56:00     NaN
2014-01-01 01:56:00     NaN
2014-01-01 02:56:00     NaN
2014-01-01 03:56:00     NaN
2014-01-01 04:00:00     NaN
2014-01-01 04:56:00     42.0
2014-01-01 05:56:00     NaN
2014-01-01 06:56:00     NaN
2014-01-01 07:56:00     NaN
2014-01-01 08:56:00     NaN
2014-01-01 09:56:00     NaN
2014-01-01 10:00:00     19.0
2014-01-01 10:56:00     NaN
2014-01-01 11:56:00     NaN
2014-01-01 12:56:00     NaN
2014-01-01 13:56:00     NaN
2014-01-01 14:56:00     NaN
2014-01-01 15:56:00     NaN
2014-01-01 16:00:00     NaN
2014-01-01 16:56:00     36.0
2014-01-01 17:56:00     NaN
2014-01-01 18:56:00     NaN
2014-01-01 19:56:00     NaN
2014-01-01 20:56:00     NaN
2014-01-01 21:56:00     NaN
2014-01-01 22:00:00     NaN
2014-01-01 22:56:00     NaN
2014-01-01 23:56:00     NaN
2014-01-01 23:59:00     41.0
2014-01-02 00:56:00     NaN
...
...
...

I need to keep only the row 2014-01-01 23:59:00 41.0

or just df.resample('D').last()

anky
– anky

2019-08-25 05:00:09 +00:00
Commented Aug 25, 2019 at 5:00 — anky
– anky, Commented Aug 25, 2019 at 5:00

moys · Accepted Answer · 2019-08-25 04:21:46Z

1

Try this.

df.groupby([pd.Grouper(key = 'DATE', freq = 'd')]).last()

answered Aug 25, 2019 at 4:21

moys

8,1173 gold badges19 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rahul Kumar Over a year ago

it threw up 'Grouper named DATE is not found' so i removed key='DATE' and kept only df.groupby([pd.Grouper(freq = 'd')]).last() ... looks like it works.. will it give unexpected result?

moys Over a year ago

Since the DATE is already index. it should not give any unexpected results.

Collectives™ on Stack Overflow

How to filter rows based on datetime index

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related