# Summarizing Float64 values after grouping by different time scales from DateTime object

**URL:** <https://discourse.julialang.org/t/summarizing-float64-values-after-grouping-by-different-time-scales-from-datetime-object/90320>\
**Category:** General Usage\
**Tags:** dates, query, splitapplycombine\
**Created:** [November 16, 2022, 12:00am UTC](https://discourse.julialang.org/t/summarizing-float64-values-after-grouping-by-different-time-scales-from-datetime-object/90320 "2022-11-16T00:00:37Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Hugo](https://avatars.discourse-cdn.com/v4/letter/h/b38774/32.png) [@Hugo](https://discourse.julialang.org/u/Hugo)\
**Post date:** [November 16, 2022, 12:00am UTC](https://discourse.julialang.org/t/summarizing-float64-values-after-grouping-by-different-time-scales-from-datetime-object/90320/1 "2022-11-16T00:00:37Z")

</div>

I have a data frame with two columns. Column `time` has `datetime` elements while column `values` has `Float64` elements. I want to group the column `time` by year, month, day, and hour to calculate the average of the values.

I created a short example that helps drive the point home. I first create some data at step (2). I didn’t know how to create `DateTime` objects, so I created strings that I later transform. Then, I extract the parameters for the groups (step (4)) and add them as new columns in the data frame (step (5)). My final (failed) attempt is to use `@groupby`.

```julia
# (1) Pkgs
using DataFrames, Dates, Timezone, Query

# (2) Creating some data
time = ["2017-01-01 00:00:00", "2017-01-01 00:20:00", "2017-01-01 00:40:00", "2017-01-01 01:00:00", "2017-01-01 01:20:00", "2017-01-01 01:40:00"]
values = [100, 200, 300, 500, 600, 700]

df = DataFrame(time = time, values = values)

# (3) Transform column time to datetime. 
datetime = DateTime.(df.time, DateFormat("y-m-d HH:MM:SSzzzz"))
df.time = datetime 

# (4) Extract year, month, day, hour from datetime
year_ = year.(df.time_index)
month_ = month.(df.time_index)
day_ = day.(df.time_index)
hour_ = hour.(df.time_index)

# (5) Insert this vectors as columns in df
insertcols!(df, 1, :year_ => year_)
insertcols!(df 2, :month_ => month_)
insertcols!(df, 3, :day_ => day)
insertcols!(df, 4, :hour_ => hour_)

# (6) My attempt to use groupby
x |> @groupby(df,[:year_, :month_, :day_, :hour_])

```

I’d expect to have a simpler, faster, and easier-to-read way to do this. Creating individual vectors to later join them to the data frame as columns seems a bit of extra work. Yet, bare in mind, I’m still a Julia novice and I’m simply trying to achieve goals without focusing on early code optimization.

In `R`, I know how to group steps 4, 5, and 6 into one single command with the help of packages `dplyr` and `lubridate`.

```julia
  aux <-
    df %>%
    group_by(year(time_index),
             month(time_index),
             day(time_index),
             hour(time_index)) %>%
    summarize(avg_values = mean(values)) %>%
    ungroup()

```

where `aux` would be a vector of length 2 with elements `c(200, 600)`, in `R` notation.

**Does anyone know how I’d accomplish this? Thanks a lot.**

---

<div class="post-metadata">

**Author:** ![chiraganand](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/chiraganand/32/32787_2.png) [@chiraganand](https://discourse.julialang.org/u/chiraganand)\
**Post date:** [November 21, 2022, 2:33pm UTC](https://discourse.julialang.org/t/summarizing-float64-values-after-grouping-by-different-time-scales-from-datetime-object/90320/2 "2022-11-21T14:33:08Z")

</div>

I am assuming by looking at the R code that you want to compute summary of `values` every hour? It is a timeseries problem. You can use TSFrames.jl:

```julia
julia> using TSFrames

julia> time = [
    DateTime(2017, 01, 01, 0, 0),
    DateTime(2017, 01, 01, 0, 20),
    DateTime(2017, 01, 01, 0, 40),
    DateTime(2017, 01, 01, 01, 00),
    DateTime(2017, 01, 01, 01, 20),
    DateTime(2017, 01, 01, 01, 40)
];
julia> values = [100, 200, 300, 500, 600, 700];

julia> ts = TSFrame(values, time)
TSFrame(values, time)
6×1 TSFrame with DateTime Index
 Index x1
 DateTime Int64
────────────────────────────
 2017-01-01T00:00:00 100
 2017-01-01T00:20:00 200
 2017-01-01T00:40:00 300
 2017-01-01T01:00:00 500
 2017-01-01T01:20:00 600
 2017-01-01T01:40:00 700

## compute hourly mean
julia> apply(ts, Hour(1), mean)
2×1 TSFrame with DateTime Index
 Index x1_mean
 DateTime Float64
──────────────────────────────
 2017-01-01T00:00:00 200.0
 2017-01-01T01:00:00 600.0

```

Here, `Hour(1)` specifies the period to summarise on and `mean` is the summary function.

REF: [TSFrames API docs](https://xkdr.github.io/TSFrames.jl/dev/api/)

---

<div class="post-metadata">

**Author:** ![Hugo](https://avatars.discourse-cdn.com/v4/letter/h/b38774/32.png) [@Hugo](https://discourse.julialang.org/u/Hugo)\
**Post date:** [December 21, 2022, 3:56pm UTC](https://discourse.julialang.org/t/summarizing-float64-values-after-grouping-by-different-time-scales-from-datetime-object/90320/3 "2022-12-21T15:56:59Z")

</div>

Thanks a lot for your feedback, @chiraganand