# DataFrame, aggregate by month of date

**URL:** <https://discourse.julialang.org/t/dataframe-aggregate-by-month-of-date/34428>\
**Category:** General Usage\
**Tags:** dataframes\
**Created:** [February 10, 2020, 6:19pm UTC](https://discourse.julialang.org/t/dataframe-aggregate-by-month-of-date/34428 "2020-02-10T18:19:53Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![BMval](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bmval/32/7647_2.png) [@BMval](https://discourse.julialang.org/u/BMval)\
**Post date:** [February 10, 2020, 6:19pm UTC](https://discourse.julialang.org/t/dataframe-aggregate-by-month-of-date/34428/1 "2020-02-10T18:19:54Z")

</div>

I am sorry for the easy question.  
I have DataFrame, one the column is Date and Count:: Int.  
What is the easiest way to calculate the sum(Count) per month?

thank you.

P.S. I have done by loop, but I fill there has to be a better way.

thank you in advance.

---

<div class="post-metadata">

**Author:** ![lungben](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/lungben/32/12314_2.png) [@lungben](https://discourse.julialang.org/u/lungben)\
**Post date:** [February 10, 2020, 8:35pm UTC](https://discourse.julialang.org/t/dataframe-aggregate-by-month-of-date/34428/2 "2020-02-10T20:35:54Z")

</div>

```julia

using DataFrames
using Dates
dates = Date("2015-01-01") .+ Day.(1:1000)
items = (1:1000) .% 7
df = DataFrame(:dt => dates, :count => items)
df[!, :month] = month.(df[!, :dt])
by(df, :month, :count=>sum)

```

Hope this helps!

---

<div class="post-metadata">

**Author:** ![Thuener](https://avatars.discourse-cdn.com/v4/letter/t/7ba0ec/32.png) [@Thuener](https://discourse.julialang.org/u/Thuener)\
**Post date:** [July 14, 2020, 12:37pm UTC](https://discourse.julialang.org/t/dataframe-aggregate-by-month-of-date/34428/3 "2020-07-14T12:37:12Z")

</div>

I think the best way is to use Query.jl

```julia
using DataFrames, Query

df = DataFrame(name=["John", "Sally", "Kirk", "Sanders"], age=[23., 42., 59., 31.], children=[3,2,2,0], date=[Date("2015-01-01"),Date("2015-01-10"),Date("2015-02-20"), Date("2015-02-05")])

x = df |>
    @groupby(Dates.format(_.date, "yyyy-mm")) |>
    @map({Key=key(_), Count=length(_)}) |>
    DataFrame

println(x)

```

```julia
2×2 DataFrame
│ Row │ Key │ Count │
│ │ String │ Int64 │
├─────┼─────────┼───────┤
│ 1 │ 2015-01 │ 2 │
│ 2 │ 2015-02 │ 2 │

```

---

<div class="post-metadata">

**Author:** ![kcdysart](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/kcdysart/32/11619_2.png) [@kcdysart](https://discourse.julialang.org/u/kcdysart)\
**Post date:** [February 2, 2022, 1:34pm UTC](https://discourse.julialang.org/t/dataframe-aggregate-by-month-of-date/34428/4 "2022-02-02T13:34:38Z")

</div>

I am brand new to Julia so I apologize if the format of the post is not exactly as it should be. I had the same question and found this post in my search for a solution. I took the above and modified it slightly with a LINQ approach. I wanted the outputted DataFrame to have the month still as a date to use in a time series. Other languages I use have a function for such a return. It is likely not elegant but worked. It returns y-m-d format with the first of each month. Could not get it to y-m. But it does aggregate on month. I needed the mean of a column grouped by month. Any aggregation function should work I would think. I did modify the example data frame above by adding a few more rows.

df\_2 = DataFrame(name=[“John”, “Sally”, “Kirk”, “Sanders”, “Hank”], age=[23., 42., 59., 31., 65], children=[3,2,2,0, 5], date=[Date(“2015-01-01”),Date(“2015-01-10”),Date(“2015-02-20”), Date(“2015-02-05”), Date(“2015-02-28”)])

df\_3 = @from i in df\_2 begin

```
   @group i by Date.(Dates.format(i.date, "yyyy-mm")) into g

   @select{Month=key(g), Mean_age = mean(g.age) }

   @collect DataFrame

```

end

---

<div class="post-metadata">

**Author:** ![sijo](https://avatars.discourse-cdn.com/v4/letter/s/da6949/32.png) [@sijo](https://discourse.julialang.org/u/sijo)\
**Post date:** [February 2, 2022, 1:51pm UTC](https://discourse.julialang.org/t/dataframe-aggregate-by-month-of-date/34428/5 "2022-02-02T13:51:35Z")

</div>

Here’s a solution using just DataFrames:

```julia
using DataFrames, Dates, Statistics

df_2 = DataFrame(name=["John", "Sally", "Kirk", "Sanders", "Hank"],
                 age=[23., 42., 59., 31., 65],
                 children=[3,2,2,0,5],
                 date=[Date("2015-01-01")
                       Date("2015-01-10")
                       Date("2015-02-20")
                       Date("2015-02-05")
                       Date("2015-02-28")])

df_3 = transform(df_2, :date => ByRow(yearmonth) => :Month)
df_4 = combine(groupby(df_3, :Month), :age => mean => :Mean_age)

# Result:
2×2 DataFrame
 Row │ Month Mean_age 
     │ Tuple… Float64  
─────┼─────────────────────
   1 │ (2015, 1) 32.5
   2 │ (2015, 2) 51.6667

```

The same with syntax sugar from DataFramesMeta:

```julia
using DataFramesMeta

@chain df_2 begin
    @rtransform :Month = yearmonth(:date)
    groupby(:Month)
    @combine :Mean_age = mean(:age)
end

```

and if you want the month as a “year-month” string:

```julia
@chain df_2 begin
    @rtransform :Month = join(yearmonth(:date), '-')
    groupby(:Month)
    @combine :Mean_age = mean(:age)
end

# Result:
2×2 DataFrame
 Row │ Month Mean_age 
     │ String Float64  
─────┼──────────────────
   1 │ 2015-1 32.5
   2 │ 2015-2 51.6667

```