# How to filter a DataFrame of DateTime data by the time of day?

**URL:** <https://discourse.julialang.org/t/how-to-filter-a-dataframe-of-datetime-data-by-the-time-of-day/79958>\
**Category:** General Usage\
**Tags:** dates, dataframes\
**Created:** [April 24, 2022, 9:16pm UTC](https://discourse.julialang.org/t/how-to-filter-a-dataframe-of-datetime-data-by-the-time-of-day/79958 "2022-04-24T21:16:37Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![phantom](https://avatars.discourse-cdn.com/v4/letter/p/e0b2c6/32.png) [@phantom](https://discourse.julialang.org/u/phantom)\
**Post date:** [April 24, 2022, 9:16pm UTC](https://discourse.julialang.org/t/how-to-filter-a-dataframe-of-datetime-data-by-the-time-of-day/79958/1 "2022-04-24T21:16:37Z")

</div>

Suppose you have an array of Dataframes. Each Dataframe contains the time and date of the datapoint in the DateTime format. Is there a way to filter/ remove rows from the DataFrame based on the time of day the data is recorded? So for example if I had the following DataFrame D.

```julia
         Data DateTime
   1 │ 1 2022-03-18T05:00:00
   2 │ 2 2022-03-18T10:54:00
   3 │ 3 2022-03-18T13:53:00
   4 │ 4 2022-03-18T20:52:00
   5 │ 5 2022-03-19T05:51:00
   6 │ 6 2022-03-19T11:50:00 
   7 │ 7 2022-03-19T15:49:00
   8 │ 8 2022-03-19T20:48:00

```

And on each day I only wanted to keep data from a given Start and Stop time, say 9:00 AM to 5:00 PM (17:00), i.e. rows 2,3,6,7 so that the resulting Dataframe looked like this.

```julia
         Data DateTime
  1 │ 2 2022-03-18T10:54:00
  2 │ 3 2022-03-18T13:53:00
  3 │ 6 2022-03-19T11:50:00 
  4 │ 7 2022-03-19T15:49:00

```

Is there an efficient way to achieve this without having to split the Dataframe up into different days and can it be done by indexing? Would something in this format work how would one enter the appropriate DateTime criteria?

```julia
D[(D.DateTime.>= Start) .& (D.DateTime.<=Stop) , :] 

```

Any insights would be greatly appreciated, Thanks!

---

<div class="post-metadata">

**Author:** ![phantom](https://avatars.discourse-cdn.com/v4/letter/p/e0b2c6/32.png) [@phantom](https://discourse.julialang.org/u/phantom)\
**Post date:** [April 24, 2022, 10:13pm UTC](https://discourse.julialang.org/t/how-to-filter-a-dataframe-of-datetime-data-by-the-time-of-day/79958/2 "2022-04-24T22:13:56Z")

</div>

I was able to get it to work with something like this but am leaving the post up in case this is not the best solution or if it is helpful to others.

```julia
D[(hour.(D.DateTime).>= 9) .& (hour.(D.DateTime).<=16) , :]

```

---

<div class="post-metadata">

**Author:** ![bkamins](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bkamins/32/208538_2.png) [@bkamins](https://discourse.julialang.org/u/bkamins)\
**Post date:** [April 24, 2022, 10:26pm UTC](https://discourse.julialang.org/t/how-to-filter-a-dataframe-of-datetime-data-by-the-time-of-day/79958/3 "2022-04-24T22:26:35Z")

</div>

> [@phantom](#):
>
> D[(hour.(D.DateTime).\>= 9) .& (hour.(D.DateTime).\<=17) , :]

This is mostly how you could write it. An alternative would be:

```julia
D[@. 9 <= hour(D.DateTime) <= 17, :]

```

or

```julia
filter(:DateTime => x-> 9 <= hour(x) <= 17, D)

```

---

<div class="post-metadata">

**Author:** ![phantom](https://avatars.discourse-cdn.com/v4/letter/p/e0b2c6/32.png) [@phantom](https://discourse.julialang.org/u/phantom)\
**Post date:** [April 25, 2022, 12:15am UTC](https://discourse.julialang.org/t/how-to-filter-a-dataframe-of-datetime-data-by-the-time-of-day/79958/4 "2022-04-25T00:15:33Z")

</div>

Thanks! this is amazingly helpful. Would you mind clarifying what the "@. " is doing in the code ? Is that another way of referencing the Dataframe? Also I have noticed my solution doesn’t work very well in the sense that if I wanted the cutoff time to be 5:00 pm I would actually have to place the cut off at `hour(D.DateTime)<= 16` because otherwise I would retain all the datapoints that occurred 5:01, 5:32 etc. However the problem then becomes I loose all datapoints that occur at exactly 5:00pm. Is there a better way so that I could include those datapoints?

---

<div class="post-metadata">

**Author:** ![bkamins](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bkamins/32/208538_2.png) [@bkamins](https://discourse.julialang.org/u/bkamins)\
**Post date:** [April 25, 2022, 7:12am UTC](https://discourse.julialang.org/t/how-to-filter-a-dataframe-of-datetime-data-by-the-time-of-day/79958/5 "2022-04-25T07:12:02Z")

</div>

> [@bkamins](#):
>
> `@. 9 <= hour(D.DateTime) <= 17`

This is just an equivalent of `9 .<= hour.(D.DateTime) .<= 17` to avoid typing `.` three times.

As for the second question use:

```julia
Time(9) .<= Time.(D.DateTime) .<= Time(17)

```

---

<div class="post-metadata">

**Author:** ![HerAdri](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/heradri/32/5816_2.png) [@HerAdri](https://discourse.julialang.org/u/HerAdri)\
**Post date:** [April 25, 2022, 7:40am UTC](https://discourse.julialang.org/t/how-to-filter-a-dataframe-of-datetime-data-by-the-time-of-day/79958/6 "2022-04-25T07:40:25Z")

</div>

> [@phantom](#):
>
> D[(hour.(D.DateTime).\>= 9) .& (hour.(D.DateTime).\<=17) , :]

how to proceed!!!

```julia

julia> D[@. 9 <= hour(D.DateTime) <= 17, :]
ERROR: MethodError: no method matching getindex(::DataFrame, ::Tuple{BitVector, Colon})
Closest candidates are:
  getindex(::AbstractDataFrame, ::CartesianIndex{2}) at C:\Users\Hermesr\.julia\packages\DataFrames\6xBiG\src\other\broa
dcasting.jl:3
  getindex(::AbstractDataFrame, ::Integer, ::Colon) at C:\Users\Hermesr\.julia\packages\DataFrames\6xBiG\src\dataframero
w\dataframerow.jl:210
  getindex(::AbstractDataFrame, ::Integer, ::Union{Colon, Regex, AbstractVector, All, Between, Cols, InvertedIndex}) at
C:\Users\Hermesr\.julia\packages\DataFrames\6xBiG\src\dataframerow\dataframerow.jl:208
  ...
Stacktrace:
 [1] top-level scope
   @ REPL[32]:100:
#########
julia> D[[@. 9 <= hour(D.datetime) <= 17], : ]
ERROR: ArgumentError: invalid index: BitVector[[0, 1, 1, 0, 0, 1, 1, 0]] of type Vector{BitVector}
Stacktrace:
 [1] to_index(I::Vector{BitVector})
   @ Base .\indices.jl:297
 [2] to_index(A::Vector{Int64}, i::Vector{BitVector})
   @ Base .\indices.jl:277
 [3] to_indices
   @ .\indices.jl:333 [inlined]
 [4] to_indices
   @ .\indices.jl:325 [inlined]
 [5] getindex(A::Vector{Int64}, I::Vector{BitVector})
   @ Base .\abstractarray.jl:1218
 [6] _threaded_getindex(selected_rows::Vector{BitVector}, selected_columns::UnitRange{Int64}, df_columns::Vector{Abstrac
tVector}, idx::DataFrames.Index)
   @ DataFrames C:\Users\Hermesr\.julia\packages\DataFrames\6xBiG\src\dataframe\dataframe.jl:543
 [7] getindex(df::DataFrame, row_inds::Vector{BitVector}, #unused#::Colon)
   @ DataFrames C:\Users\Hermesr\.julia\packages\DataFrames\6xBiG\src\dataframe\dataframe.jl:586
 [8] top-level scope
   @ REPL[34]:1

julia>

```

---

<div class="post-metadata">

**Author:** ![rafael.guerra](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rafael.guerra/32/216610_2.png) [@rafael.guerra](https://discourse.julialang.org/u/rafael.guerra)\
**Post date:** [April 25, 2022, 7:54am UTC](https://discourse.julialang.org/t/how-to-filter-a-dataframe-of-datetime-data-by-the-time-of-day/79958/7 "2022-04-25T07:54:29Z")

</div>

> [@HerAdri](#):
>
> `D[@. 9 <= hour(D.DateTime) <= 17, :]`

Try with brackets:

```julia
D[(@. 9 <= hour(D.DateTime) <= 17), :]

```

---

<div class="post-metadata">

**Author:** ![bkamins](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bkamins/32/208538_2.png) [@bkamins](https://discourse.julialang.org/u/bkamins)\
**Post date:** [April 25, 2022, 8:38am UTC](https://discourse.julialang.org/t/how-to-filter-a-dataframe-of-datetime-data-by-the-time-of-day/79958/8 "2022-04-25T08:38:41Z")

</div>

or

```julia
D[@.(9 <= hour(D.DateTime) <= 17), :]

```

I made an error with `@.` scope.

---

<div class="post-metadata">

**Author:** ![phantom](https://avatars.discourse-cdn.com/v4/letter/p/e0b2c6/32.png) [@phantom](https://discourse.julialang.org/u/phantom)\
**Post date:** [April 26, 2022, 7:52pm UTC](https://discourse.julialang.org/t/how-to-filter-a-dataframe-of-datetime-data-by-the-time-of-day/79958/9 "2022-04-26T19:52:35Z")

</div>

just for future reference the initial solution was not ideal because if you wanted the cutoff time to be 5:00 PM, using `hour.(D.DateTime).<= 16` would exclude all data at exactly 5:00 pm while using `hour.(D.DateTime). <=17` would include say 5:59, etc. The solution as stated in bkamins’ comments below is summarized here:

` D[@.(Time(9) <= Time(D.DateTime) <=Time(17), :]`

for anyone also new to Julia it might be helpful to read through bkamins comments to understand what the code is doing.

---

<div class="post-metadata">

**Author:** ![bkamins](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bkamins/32/208538_2.png) [@bkamins](https://discourse.julialang.org/u/bkamins)\
**Post date:** [April 26, 2022, 8:30pm UTC](https://discourse.julialang.org/t/how-to-filter-a-dataframe-of-datetime-data-by-the-time-of-day/79958/10 "2022-04-26T20:30:36Z")

</div>

> [@phantom](#):
>
> D[@.(Time(9) \<= Time(D.DateTime) \<=Time(17), :]

added missing parenthresis:

```julia
D[@.(Time(9) <= Time(D.DateTime) <= Time(17)), :]

```