CODEWITHSUNDEEP
aggregatejulia
emonigma
emonigma

Reputation: 4436

Aggregate in Julia like R or pandas

I want to aggregate a monthly series at the quarterly frequency, for which R has ts and aggregate() (see the first answer on this thread) and pandas has df.resample("Q").sum() (see this question). Does Julia offer something similar?

Appendix: my current solution uses a function to convert a data to the first quarter and split-apply-combine:

"""
    month_to_quarter(date)

Returns the date corresponding to the first day of the quarter enclosing date

# Examples
```jldoctest
julia> Date(1990, 1, 1) == RED.month_to_quarter(Date(1990, 2, 1))
true
julia> Date(1990, 1, 1) == RED.month_to_quarter(Date(1990, 1, 1))
true
julia> Date(1990, 1, 1) == RED.month_to_quarter(Date(1990, 2, 25))
true
```
"""
function month_to_quarter(date::Date)
    new_month = 1 + 3 * floor((Dates.month(date) - 1) / 3)
    return Date(Dates.year(date), new_month, 1)
end


""" 
    monthly_to_quarterly(monthly_df)

Aggregates a monthly data frame to the quarterly frequency. The data frame should have a :DATE column.

# Examples
```jldoctest
julia> monthly = convert(DataFrame, hcat(collect([Dates.Date(1990, m, 1) for m in 1:3]), [1; 2; 3]));

julia> rename!(monthly, :x1 => :DATE);

julia> rename!(monthly, :x2 => :value);

julia> quarterly = RED.monthly_to_quarterly(monthly);

julia> quarterly[:value][1]
2.0

julia> length(quarterly[:value])
1
```
"""
function monthly_to_quarterly(monthly::DataFrame)

    # quarter months: 1, 4, 7, 10
    quarter_months = collect(1:3:10)

    # Deep copy the data frame
    monthly_copy = deepcopy(monthly)

    # Drop initial rows until it starts on a quarter
    while !in(Dates.month(monthly_copy[:DATE][1]), quarter_months)

        # Verify that something is left to pop
        @assert 1 <= length(monthly_copy[:DATE])

        monthly_copy = monthly_copy[2:end, :]
    end

    # Drop end rows until it finishes before a quarter
    while !in(Dates.month(monthly_copy[:DATE][end]), 2 + quarter_months)
    monthly_copy = monthly_copy[1:end-1, :]
    end

    # Change month of each date to the nearest quarter
    monthly_copy[:DATE] = month_to_quarter.(monthly_copy[:DATE])

    # Split-apply-combine
    quarterly = by(monthly_copy, :DATE, df -> mean(df[:value]))

    # Rename
    rename!(quarterly, :x1 => :value)

    return quarterly

end

Upvotes: 1

Views: 1010

Answers (1)

Felipe Lema
Felipe Lema

Reputation: 2718

I couldn't find such a function in the docs. Here's a more DataFrames.jl-ish and more succint version of your own answer 

using DataFrames
# copy-pasted your own function
function month_to_quarter(date::Date)
    new_month = 1 + 3 * floor((Dates.month(date) - 1) / 3)
    return Date(Dates.year(date), new_month, 1)
end

# the data
r=collect(1:6)
monthly = DataFrame(date=[Dates.Date(1990, m, 1) for m in r], 
            val=r);

# the functionality
monthly[:quarters] = month_to_quarter.(monthly[:date])
_aggregated = by(monthly, :quarters, df -> DataFrame(S = sum(df[:val])))

@show monthly
@show _aggregated

Upvotes: 1

Related Questions