Calculating Rate of change by year for each sector

Specific Domains Finance and Economics
1

Hi all,

I have dataframes as follow:

 Row │ year   sector                    roa 
     │ Int64  String                    Float64
─────┼─────────────────────────────────────────
   1 │  2017  Construction              1.15
   2 │  2017  Services                  1.78
   3 │  2017  Agriculture               1.82
   4 │  2018  Construction              1.05
   5 │  2018  Services                  1.56
   6 │  2018  Agriculture               1.55
   7 │  2019  Construction              1.32
   8 │  2019  Services                  1.53
   9 │  2019  Agriculture               1.38
  10 │  2020  Construction              1.11
  11 │  2020  Services                  1.93
  12 │  2020  Agriculture               1.21
  13 │  2021  Construction              0.03
  14 │  2021  Services                  0.06
  15 │  2021  Agriculture               0.36

I would like to calculate the rate of change starting from 2017 to 2021 for each sector. I appreciate any suggestions. Thanks all!

2

You can use the package ShiftedArrays.

using DataFrames
import ShiftedArrays: lag

# mock data
using Random; Random.seed!(1234)
dff = DataFrame(year = repeat(2017:2021, inner=5), sector = repeat(["A", "B", "C", "D", "E"],5), roa = rand(25))

# computation
sort!(dff, [:sector, :year])                                    # sort data by the group and year
transform!(groupby(dff,[:sector]), :roa => lag => :prev_roa)    # create lag value
dff.rate_change = (dff.roa ./ dff.prev_roa .- 1 ) * 100         # create rate_change

with output

julia> dff
25×5 DataFrame
 Row │ year   sector  roa       prev_roa        rate_change   
     │ Int64  String  Float64   Float64?        Float64?
─────┼────────────────────────────────────────────────────────
   1 │  2017  A       0.579862  missing         missing
   2 │  2018  A       0.639562        0.579862       10.2955
   3 │  2019  A       0.566704        0.639562      -11.3918
  ⋮  │   ⋮      ⋮        ⋮            ⋮               ⋮
  23 │  2019  E       0.806704        0.696041       15.899
  24 │  2020  E       0.939548        0.806704       16.4674
  25 │  2021  E       0.131026        0.939548      -86.0544
                                               19 rows omitted
2 Likes