How to find the annual evolution rate for each firm in my data table?-CodePudding

So I have a data table of 5000 firms, each firm is assigned a numerical value ("id") which is 1 for the first firm, 2 for the second ...

Here is my table with only the profit variable : |id | year | profit |:----| :----| :----| |1 |2001 |-0.4 |1 |2002 |-0.89 |2 |2001 |1.89 |2 |2002 |2.79

Each firm is expressed twice, one line specifies the data in 2001 and the second in 2002 (the "id" value being the same on both lines because it is the same firm one year apart).

How to calculate the annual rate of change of each firm ("id") between 2001 and 2002 ?

I'm really new to R and I don't see where to start? Separate the 2001 and 2002 data? I did this : years <- sort(unique(group$year))years

And I also found this on the internet but with no success :

library(dplyr)
res <-
  group %>%
  arrange(id,year) %>%
  group_by(id) %>%
  mutate(evol_rate = ("group$year$2002" / lag("group$year$2001") - 1) * 100)  %>%
  ungroup()

Thank you very much

CodePudding user response：

From what you've written, I take it that you want to calculate the formula for ROC for the profit values of 2001 and 2002:

ROC=(current_value/previous_value − 1) ∗ 100

To accomplish this, I suggest tidyr::pivot_wider() which reshapes your dataframe from long to wide format (see: https://r4ds.had.co.nz/tidy-data.html#pivoting).

Code:

require(tidyr)
require(dplyr)

id <- sort(rep(seq(1,250, 1), 2))
year <- rep(seq(2001, 2002, 1), 500)
value <- sample(500:2000, 500)

df <- data.frame(id, year, value)

head(df, 10)
#>    id year value
#> 1   1 2001   856
#> 2   1 2002  1850
#> 3   2 2001  1687
#> 4   2 2002  1902
#> 5   3 2001  1728
#> 6   3 2002  1773
#> 7   4 2001   691
#> 8   4 2002  1691
#> 9   5 2001  1368
#> 10  5 2002   893

df_wide <- df %>%
  pivot_wider(names_from = year,
              names_prefix = "profit_",
              values_from = value,
              values_fn = mean)

res <- df_wide %>%
  mutate(evol_rate = (profit_2002/profit_2001-1)*100) %>%
  round(2)

head(res, 10)
#> # A tibble: 10 x 4
#>       id profit_2001 profit_2002 evol_rate
#>    <dbl>       <dbl>       <dbl>     <dbl>
#>  1     1         856        1850    116.  
#>  2     2        1687        1902     12.7 
#>  3     3        1728        1773      2.6 
#>  4     4         691        1691    145.  
#>  5     5        1368         893    -34.7 
#>  6     6         883         516    -41.6 
#>  7     7        1280        1649     28.8 
#>  8     8        1579        1383    -12.4 
#>  9     9        1907        1626    -14.7 
#> 10    10        1227        1134     -7.58