Lag vector by group using another vector as the grouping variable

General Usage
1

Hello everyone,

I was wondering if it were possible to lag a vector by group, when the grouping variable is a separate vector.

Here’s a minimum working example:

firm_id = ["A", "A", "A", "A", "B", "B", "B", "B"] 
revenue = [100, 200, 300, 400, 50, 67, 75, 90]
year = [2001,2002,2003,2004,2001,2002,2003,2004]

where you can think of the revenue as a panel data that corresponds to each firm.

I’d like to lag the revenue variable by firm_id, to get the following result:

revenue_lag = [missing, 100, 200, 300, missing, 50, 67, 75]

In my project I have to perform this operation repeatedly for different values of the parameter I’m optimizing over.

I realize that this can be done by putting firm_id and revenue into a DataFrame and then lagging it, but I was wondering if it were possible to do so without creating a DataFrame every time I want to do this. My (uninformed) guess is that creating a DataFrame everytime to lag is more time-consuming, please correct me if I’m wrong about this.

I saw that the ShiftedArrays package has a lag() function but couldn’t figure out how it could be applied by a grouping vector.

Thanks!

2

No, I would definitely use DataFrames and ShiftedArrays. Probably with Chain or another similar piping package.

1 Like
3

Agree, just make sure you don’t copy everything when you create the DataFrame:

julia> using DataFrames, BenchmarkTools

julia> firm_id = rand(["A", "B"], 100_000); revenue = rand(Int, 100_000); year = rand(2001:2004, 100_000);

julia> @btime DataFrame(firm_id = $firm_id, revenue = $revenue, year = $year);
  122.900 μs (34 allocations: 2.29 MiB)

julia> @btime DataFrame(firm_id = $firm_id, revenue = $revenue, year = $year; copycols = false);
  2.140 μs (27 allocations: 1.80 KiB)
3 Likes
4

Ah that’s a nice trick, thanks!

5 
using DataFrames, ShiftedArrays
using Chain

firm_id = ["A", "A", "A", "A", "B", "B", "B", "B"]
revenue = [100, 200, 300, 400, 50, 67, 75, 90]
year = [2001,2002,2003,2004,2001,2002,2003,2004]

df = DataFrame(;firm_id, revenue, year, copycols = false)

@chain df begin
    groupby(:firm_id)
    transform(:revenue => lag => :revenue_lag, ungroup = false)
    transform([:revenue, :revenue_lag] => ByRow((x, y) -> x - y) => :diff_prior_year, ungroup = false) 
end

## avoid anonymous function for better performance

diff(x, y) = x - y

@chain df begin
    groupby(:firm_id)
    transform(:revenue => lag => :revenue_lag, ungroup = false)
    transform([:revenue, :revenue_lag] => ByRow(diff) => :diff_prior_year, ungroup = false) 
end
1 Like