Python - Filtering Pandas Timestamp Index

Question

Given Timestamp indices with many per day, how can I get a list containing only the last Timestamp of a day? So in case I have such:

import pandas as pd

all   = [pd.Timestamp('2016-05-01 10:23:45'), 
         pd.Timestamp('2016-05-01 18:56:34'), 
         pd.Timestamp('2016-05-01 23:56:37'),
         pd.Timestamp('2016-05-02 03:54:24'), 
         pd.Timestamp('2016-05-02 14:32:45'), 
         pd.Timestamp('2016-05-02 15:38:55')]

I would like to get:

# End of Day:
EoD   = [pd.Timestamp('2016-05-01 23:56:37'), 
         pd.Timestamp('2016-05-02 15:38:55')]

Thx in advance!

are you working with Vanilla Python lists or with Pandas structures? — MaxU - stand with Ukraine
– MaxU - stand with Ukraine, Commented Apr 26, 2017 at 21:14
Sry, I should have been more clear. I extract those from a Pandas Dataframe (with DatetimeIndex index) — Tim
– Tim, Commented Apr 26, 2017 at 22:48

Vaishali · Accepted Answer · 2017-04-26 21:17:59Z

3

Try pandas groupby

all   = pd.Series(all)
all.groupby([all.dt.year, all.dt.month, all.dt.day]).max()

You get

2016  5  1   2016-05-01 23:56:37
         2   2016-05-02 15:38:55

answered Apr 26, 2017 at 21:17

Vaishali

38.5k5 gold badges62 silver badges88 bronze badges

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

1 Comment

Tim Over a year ago

That is nice, but I wasn't precise enough. Actually, I use a Dataframe with a DatetimeIndex. Your way works when I retrieve the index first and proceed as proposed. However, is there a more direct way from the Dataframe itself?

dylan_fan · Accepted Answer · 2017-04-27 08:19:52Z

0

I've created an example dataframe.

import pandas as pd
all   = [pd.Timestamp('2016-05-01 10:23:45'), 
         pd.Timestamp('2016-05-01 18:56:34'), 
         pd.Timestamp('2016-05-01 23:56:37'),
         pd.Timestamp('2016-05-02 03:54:24'), 
         pd.Timestamp('2016-05-02 14:32:45'), 
         pd.Timestamp('2016-05-02 15:38:55')]
df = pd.DataFrame({'values':0}, index = all)

Assuming your data frame is structured as example, most importantly is sorted by index, code below is supposed to help you.

for date in set(df.index.date):
    print(df[df.index.date == date].iloc[-1,:])

This code will for each unique date in your dataframe return last row of the slice so while sorted it'll return your last record for the day. And hey, it's pythonic. (I believe so at least)

edited Apr 27, 2017 at 8:19

answered Apr 27, 2017 at 8:14

dylan_fan

7201 gold badge5 silver badges18 bronze badges

1 Comment

Tim Over a year ago

Thx, interesting approach. However, I think for this, you'd still need to sort the output and it doesn't scale as nicely as the other answer.

Collectives™ on Stack Overflow

Python - Filtering Pandas Timestamp Index

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related