Python: selecting rows by hour in a dataframe

Question

I have the below dataframe in csv file, I would like to select all rows corresponding to current hour.

time,values

2018-10-28 08:16:49.469508,48

2018-10-28 08:16:54.471987,48

2018-10-28 08:16:59.475236,48

2018-10-28 08:17:04.478681,48

Below is the funtion I am trying current = datetime.datetime.now()

start = datetime.datetime(current.year,current.month,current.day,current.hour,0)

end = datetime.datetime(current.year,current.month,current.day,current.hour,59)

df = pd.io.parsers.read_csv('water_data1.csv', parse_dates=[0], index_col=0)

print(df.query('start < time < end'))

I get the following error

pandas.core.computation.ops.UndefinedVariableError: name 'start' is not defined

Could someone suggest what is the right syntax to achieve this. Thanks Hemanth

print(df.query('@start < time < @end')) is the correct syntax for query — Hemanth V
– Hemanth V, Commented Nov 2, 2018 at 6:34

jpp · Accepted Answer · 2018-11-01 17:53:29Z

1

pd.DataFrame.query requires external variables to be preceded by @:

df = pd.DataFrame({'A': list(range(10))})

start, end = 3, 6

print(df.query('@start < A < @end'))

   A
4  4
5  5

You can also use pd.Series.between:

res = df[df['A'].between(start, end, inclusive=False)]

Finally, when working with datetime values, you should prefer pd.Timestamp over regular Python types:

now = pd.Timestamp('now')
start = now.replace(second=0, microsecond=0)
end = now.replace(second=59, microsecond=0)

print((start, end))

(Timestamp('2018-11-01 17:36:00'), Timestamp('2018-11-01 17:36:59'))

edited Nov 1, 2018 at 17:53

answered Nov 1, 2018 at 17:34

jpp

166k37 gold badges301 silver badges363 bronze badges

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

Comments

D M · Accepted Answer · 2018-11-01 17:25:29Z

1

You can try

df[(df['time'] > start) & (df['time'] < end])]

answered Nov 1, 2018 at 17:25

D M

1,1951 gold badge10 silver badges8 bronze badges

Collectives™ on Stack Overflow

Python: selecting rows by hour in a dataframe

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related