Select Pandas dataframe rows based on round hours

Question

my dataframe's index is intervaled every 3min :

Timestamp                value
2019-06-30 07:00:00    0.065248
2019-06-30 07:03:00    0.067896
2019-06-30 07:06:00    0.070529
2019-06-30 07:09:00    0.073034
2019-06-30 07:12:00    0.085928
                         ...   
2019-06-30 16:48:00    0.246681
2019-06-30 16:51:00    0.246745
2019-06-30 16:54:00    0.247110
2019-06-30 16:57:00    0.247174
2019-06-30 17:00:00    0.246338

I need to select every round hour for a line plot.

My stupid solution is:

hour_ls = ['2019-06-30 07:00:00', '2019-06-30 08:00:00','2019-06-30 09:00:00','2019-06-30 10:00:00',
           '2019-06-30 11:00:00','2019-06-30 12:00:00','2019-06-30 13:00:00','2019-06-30 14:00:00',
           '2019-06-30 13:00:00','2019-06-30 16:00:00','2019-06-30 17:00:00']

hour_ls = [pd.to_datetime(i) for i in hour_ls]
df.loc[hour_ls]

Is there a pythonic\pandeic why of doing this cleanly?

Quang Hoang · Accepted Answer · 2020-04-16 18:51:13Z

3

You can use floor to find the hours and then check if the index is equal to the hour:

df[df.index == df.index.floor('H')]

answered Apr 16, 2020 at 18:51

Quang Hoang

151k11 gold badges64 silver badges86 bronze badges

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

3 Comments

tonish Over a year ago

How would you iterate the dates as well?

Ehsan Over a year ago

if you want round use: df[df.index == df.index.round('H')]. This will by default do what you need for entire data frame. No need for loops. You can now plot the resulting value column.

Quang Hoang Over a year ago

iterate for what purpose? Generally you should avoid iterating on dataframe, but if you do: for date in df.index():?

diegog · Accepted Answer · 2020-04-16 19:07:16Z

0

After converting the column into datetime elements you now have access to datetime.minute. Example: Given you have a dataframe df as given above:

df['Timestamp'] = pd.to_datetime(df['Timestamp'])
hourly_entries = pd.DataFrame([el for el in df['Timestamp'] if el.minute == 0])

This will create a new DataFrame named 'hourly_entries' that will contain all elements with datetime.minute == 0.

answered Apr 16, 2020 at 19:07

diegog

1116 bronze badges

Collectives™ on Stack Overflow

Select Pandas dataframe rows based on round hours

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related