How can I get the last 10 records of each day?

Question

I have a DataFrame with 96 records each day, for 5 consecutive days.

Data: {'value': {Timestamp ('2018-05-03 00:07:30'): 13.02657778, Timestamp ('2018-05-03 00:22:30'): 10.89890556, Timestamp ('2018-05-03 00:37:30'): 11.04877222,... (more days and times)

Datatypes: DatetimeIndex (index column) and float64 ('flow' column).

I want to save 10 records before an indicated hour (H), of each day.

I only managed to do that for one day:

df.loc[df['time'] < '09:07:30'].tail(10)

Don't work. Returns all of the rows of the initial dataset. Thk (Note that my code line is wrong because it assumes that the time is a column) — catsandc, Apr 30 '21 at 14:00
I think you need to put some filter before groupby, so added it as an answer now. — simpleApp, Apr 30 '21 at 15:31

Gerd · Accepted Answer · 2021-05-01T10:35:08.920

0

You can group your data by day (or by month or by other ranges) using pandas.Grouper (see also this discussion).

In your case, use something like:

df.groupby(pd.Grouper(freq='D')).tail(10)

EDIT:

For getting all rows before a given hour, use df.loc[df.index.hour < H] (as already proposed in simpleApp's answer) where H is the hour as an integer value.

So in one line:

df.loc[df.index.hour < H].groupby(pd.Grouper(freq='D')).tail(10)

edited May 01 '21 at 10:35

answered Apr 30 '21 at 12:10

Gerd

2,568
1
7
20

It works, but only just save the last 10 records of each day. I want to save 10 records before an indicated hour (H). – catsandc Apr 30 '21 at 13:56
@catsandc: You seemed to already have a solution to to this (in your question). Edited my answer. – Gerd May 01 '21 at 10:40

score 0 · Answer 2 · answered Apr 30 '21 at 15:30

I would suggest filter the record by an hour and then group by date.

Data setup:

import pandas as pd
start, end = '2020-10-01 01:00:00', '2021-04-30 23:30:00'
rng = pd.date_range(start, end, freq='5min')
df=pd.DataFrame(rng,columns=['DateTS'])

set the hour

noon_hour=12 # fill the hour , for filteration

Result, if head or tail does not work on your data, you would need to sort it.

df_before_noon=df.loc[df['DateTS'].dt.hour < noon_hour] # records before noon
df_result=df_before_noon.groupby([df_before_noon['DateTS'].dt.date]).tail(10) # group by date

How can I get the last 10 records of each day?

2 Answers2