ratio with a reference value by ID and year using R

December 12, 2023

I have a panel data with fields – ID, Value, Year, Qtr. I need to compute a ratio field for each ID by dividing the current period value with the starting period value. So, for ID = 1, the ratio would be 10/10, 12/10, and 13/10. Same for ID = 2.

ID  Value   Year    Qtr Ratio
1   10  2010    1   1
1   12  2010    2   1.2
1   13  2010    3   1.3
2   15  2012    1   1
2   22  2012    2   1.466666667
2   10  2012    3   0.666666667
2   16  2012    4   1.066666667

Panel Data

I checked few suggestions such as How to look up previous values in an R data frame by ID and year? and Calculating ratios by group with dplyr – but am unable to code this. Kindly guide. Thanks.

>Solution :

At the risk of overlooking something obvious, I would use ave:

df <- data.frame(ID = c(1,1,1,2,2,2,2),
                 Value = c(10,12,13,15,22,10,16),
                 Year = c(rep(c(2010, 2012), each = 3), 2012),
                 Qtr = c(rep(1:3, 2), 4))

# df$Result = 
with(df, ave(x= Value, ID, FUN = \(x) x / x[[1L]]))
#> [1] 1.0000000 1.2000000 1.3000000 1.0000000 1.4666667 0.6666667 1.0666667

^{Created on 2023-12-12 with reprex v2.0.2}