Groupby + Lag

[oxinabox]

(For my reference this was feature number 11 on initial list)

julia> using DataFrames

julia> dayahead = DataFrame(time=[1,1,1,2,2,2,3,3,3], node = string.(repeat('a':'c', 3)), load=rand(9))
9×3 DataFrame
│ Row │ time  │ node   │ load     │
│     │ Int64 │ String │ Float64   │
├─────┼───────┼────────┼───────────┤
│ 1   │ 1     │ a      │ 0.644285  │
│ 2   │ 1     │ b      │ 0.0710845 │
│ 3   │ 1     │ c      │ 0.451687  │
│ 4   │ 2     │ a      │ 0.0336323 │
│ 5   │ 2     │ b      │ 0.835294  │
│ 6   │ 2     │ c      │ 0.535693  │
│ 7   │ 3     │ a      │ 0.614499  │
│ 8   │ 3     │ b      │ 0.472906  │
│ 9   │ 3     │ c      │ 0.597165  │

julia> groups = groupby(dayahead, :node)
GroupedDataFrame with 3 groups based on key: node
First Group (3 rows): node = "a"
│ Row │ time  │ node   │ load     │
│     │ Int64 │ String │ Float64   │
├─────┼───────┼────────┼───────────┤
│ 1   │ 1     │ a      │ 0.644285  │
│ 2   │ 2     │ a      │ 0.0336323 │
│ 3   │ 3     │ a      │ 0.614499  │
⋮
Last Group (3 rows): node = "c"
│ Row │ time  │ node   │ load    │
│     │ Int64 │ String │ Float64  │
├─────┼───────┼────────┼──────────┤
│ 1   │ 1     │ c      │ 0.451687 │
│ 2   │ 2     │ c      │ 0.535693 │
│ 3   │ 3     │ c      │ 0.597165 │

julia> mapreduce(vcat, groups) do grp
       lagged_grp = DataFrame(time = grp.time .+ 1, load_lag1 = grp.load)
       join(DataFrame(grp), lagged_grp; on=:time, kind=:left)
       end
9×4 DataFrame
│ Row │ time  │ node   │ load     │ load_lag1 │
│     │ Int64 │ String │ Float64   │ Float64⍰   │
├─────┼───────┼────────┼───────────┼────────────┤
│ 1   │ 1     │ a      │ 0.644285  │ missing    │
│ 2   │ 2     │ a      │ 0.0336323 │ 0.644285   │
│ 3   │ 3     │ a      │ 0.614499  │ 0.0336323  │
│ 4   │ 1     │ b      │ 0.0710845 │ missing    │
│ 5   │ 2     │ b      │ 0.835294  │ 0.0710845  │
│ 6   │ 3     │ b      │ 0.472906  │ 0.835294   │
│ 7   │ 1     │ c      │ 0.451687  │ missing    │
│ 8   │ 2     │ c      │ 0.535693  │ 0.451687   │
│ 9   │ 3     │ c      │ 0.597165  │ 0.535693   │

@cbdavis proposes alternative:

Ugly one-liner:

using ShiftedArrays
hcat(dayahead, select(by(dayahead, :node, load_lag1 = :load => lag), Not(:node)))

The key part is this: by(dayahead, :node, load_lag1 = :load => lag) but I haven't been able to figure out a way to insert additional columns into that statement. The Not(:node) part is included since it is assumed that the order of the results are the same as the original dataframe, and we can't just join the results on the node column since the time column is relevant too, but not outputted by the by statement.

generic function:

function lag_df(df::DataFrame, groupcol::Symbol, valuecol::Symbol)
  return hcat(df, select(by(df, groupcol, lag1 = valuecol => lag), Not(groupcol)))
end

lag_df(dayahead, :node, :load)