1

Havent been able to find anything similar, and new to Julia.

Trying to see if this can be done in a single process, or should be split up, or something else I am not thinking of. Basically as the df below shows - trying to see how i can add a conditional logic column to this df, anchored on Year column (apologies for the int64 - real data is actually a Date df format).

Specifically what would be best way to add a trailing 2 year column for the sample (next to the column that shows overall growth ongoing - in the example df, its the ProValue column), something akin to:
"ProValue2YrTrailing = cumprod(:Growth .+1) when Count of years is 2 per group specified"

Cant quite figure out how to use @linq and Dataframes here to create a conditional column via transform.

using DataFramesMeta
df = DataFrame(region  = ["US","US","US","US","US","EU","EU","EU","EU","EU"],
             product =          
   ["apple","apple","apple","banana","banana","apple","apple","banana","banana","banana"],
             year    = [2009,2010,2011,2010,2011,2010,2011,2009,2010,2011],
             Growth   = [0.13,0.23,0.05,0.22,0.28,0.24,0.23,0.03,0.17,0.18])
df = @linq df |>
groupby([:region,:product]) |>
transform(ProValue = cumprod(:Growth .+1))

thanks!

edit: One way i can think of doing is by below, but doesnt seem very elegant, especially as periods frames grow from 2 to say 30:

df = @linq df |>
groupby([:region,:product]) |>
transform(ProValueTrailing2 = ["missing"; rolling(prod, :Growth .+1, 2)])
SubT
  • 11
  • 3

1 Answers1

0

Is this what you want (I was not fully sure - my :ProValue variable shows the values relative to the start of the first year per group, so that is why for the first year it is 1.0)?

julia> transform(groupby(df, [:region, :product]),
                 :Growth => (x -> [1.0; cumprod(x .+ 1)[1:end-1]]) => :ProValue)       
10×5 DataFrame
 Row │ region  product  year   Growth   ProValue 
     │ String  String   Int64  Float64  Float64  
─────┼───────────────────────────────────────────
   1 │ US      apple     2009     0.13    1.0
   2 │ US      apple     2010     0.23    1.13
   3 │ US      apple     2011     0.05    1.3899
   4 │ US      banana    2010     0.22    1.0
   5 │ US      banana    2011     0.28    1.22
   6 │ EU      apple     2010     0.24    1.0
   7 │ EU      apple     2011     0.23    1.24
   8 │ EU      banana    2009     0.03    1.0
   9 │ EU      banana    2010     0.17    1.03
  10 │ EU      banana    2011     0.18    1.2051
Bogumił Kamiński
  • 66,844
  • 3
  • 80
  • 107
  • yep that can work too, needs some changes though - the +1 is there to make the growth compound on a trailing basis of 2 periods.
    Found last night RollingFunctions.jl package, which solves the problem as well. It has a Running function, which you can use same way as Rolling but without the issues of missing items. So something like this: `transform(Growth = running(prod, :Provalue .+1, 2))`     – SubT Sep 03 '21 at 12:39