Calculate new variable based on time categories

I would like to calculate new indexes such as (PPE in year(t)/TA in year(t-1)) for different ID groups (means differenc companies) and then make regression analysis for those different variables; could anyone help me ? :sweat_smile:Thanks so much!!!

Hi!

To help us help you, could you please prepare a proper reproducible example (reprex) illustrating your issue? Please have a look at this guide, to see how to create one:

Hi, thanks so much for your kind information.

suppose there are data frame such that:

c1_Year=c(1,2,3,4,1,2,1,2,3)
c2_ID=c(1,1,1,1,2,2,3,3,3)
c3_a=c(1,2,3,4,5,6,7,8,9)
c4_b=c(1,2,1,2,1,2,1,2,1)
mydata=cbind(c1_Year,c2_ID,c3_a,c4_b)

I want to create a new variable in a new column (e.g. x=a_{t-1}/b_t) in which it should be calculated for different IDs separately, such that x equals a of the last year t-1 divided by b of the current year t. Could you help me? Thanks!!

Is this what you mean?

library(dplyr)

mydata <- data.frame(
    Year = c(1, 2, 3, 4, 1, 2, 1, 2, 3),
    ID = c(1, 1, 1, 1, 2, 2, 3, 3, 3),
    a = c(1, 2, 3, 4, 5, 6, 7, 8, 9),
    b = c(1, 2, 1, 2, 1, 2, 1, 2, 1)
)

mydata %>%
    group_by(ID) %>% 
    mutate(x = lag(a)/b)
#> # A tibble: 9 x 5
#> # Groups:   ID [3]
#>    Year    ID     a     b     x
#>   <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1     1     1     1     1  NA  
#> 2     2     1     2     2   0.5
#> 3     3     1     3     1   2  
#> 4     4     1     4     2   1.5
#> 5     1     2     5     1  NA  
#> 6     2     2     6     2   2.5
#> 7     1     3     7     1  NA  
#> 8     2     3     8     2   3.5
#> 9     3     3     9     1   8

Created on 2020-03-24 by the reprex package (v0.3.0.9001)

Oh dear! Thanks so much for your help ( this is exactly what I mean:) )Wish you a wonderful day ahead!! :grin:

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.