CODEWITHSUNDEEP
r
thomas.diridondo
thomas.diridondo

Reputation: 89

Problem calculating nominal change by year for each company

I want to calculate the change in size and roa from year t to t+1 for each firm (isin).

I try the following code, but get 0 for all observations:

df3 <- totassetprice %>%
  group_by(isin) %>%
  arrange((year), .by_group = TRUE) %>%
  summarise(year,deltsize = (size - lag(size)), deltroa=(roa-lag(roa)))


          isin year     size        roa
1 JP3304200003 2009 9.617923 0.12711757
2 JP3304200003 2010 9.649482 0.08083183
3 JP3304200003 2011 9.811475 0.14535881
4 JP3304200003 2012 9.952205 0.14929742
5 JP3304200003 2013 9.944886 0.11951592
6 JP3304200003 2014 9.833800 0.12361346

Output

  isin          year deltsize deltroa
  <chr>        <int>    <dbl>   <dbl>
1 DE0005008007  2009        0       0
2 DE0005008007  2010        0       0
3 DE0005008007  2011        0       0
4 DE0005008007  2012        0       0
5 DE0005008007  2013        0       0
6 DE0005008007  2014        0       0

Upvotes: 0

Views: 33

Answers (1)

danlooo
danlooo

Reputation: 10637

You can use lag to get the difference compared to the previous row:

library(tidyverse)

data <- tribble(
  ~id, ~isin, ~year, ~size, ~roa,
  1, "JP3304200003", 2009, 9.617923, 0.12711757,
  2, "JP3304200003", 2010, 9.649482, 0.08083183,
  3, "JP3304200003", 2011, 9.811475, 0.14535881,
  4, "JP3304200003", 2012, 9.952205, 0.14929742,
  5, "JP3304200003", 2013, 9.944886, 0.11951592,
  6, "JP3304200003", 2014, 9.833800, 0.12361346,
)
data
#> # A tibble: 6 x 5
#>      id isin          year  size    roa
#>   <dbl> <chr>        <dbl> <dbl>  <dbl>
#> 1     1 JP3304200003  2009  9.62 0.127 
#> 2     2 JP3304200003  2010  9.65 0.0808
#> 3     3 JP3304200003  2011  9.81 0.145 
#> 4     4 JP3304200003  2012  9.95 0.149 
#> 5     5 JP3304200003  2013  9.94 0.120 
#> 6     6 JP3304200003  2014  9.83 0.124

data %>%
  group_by(isin) %>%
  arrange(year) %>%
  mutate(
    delta_size = size - lag(size),
    delta_roa = roa - lag(roa)
  )
#> # A tibble: 6 x 7
#> # Groups:   isin [1]
#>      id isin          year  size    roa delta_size delta_roa
#>   <dbl> <chr>        <dbl> <dbl>  <dbl>      <dbl>     <dbl>
#> 1     1 JP3304200003  2009  9.62 0.127    NA        NA      
#> 2     2 JP3304200003  2010  9.65 0.0808    0.0316   -0.0463 
#> 3     3 JP3304200003  2011  9.81 0.145     0.162     0.0645 
#> 4     4 JP3304200003  2012  9.95 0.149     0.141     0.00394
#> 5     5 JP3304200003  2013  9.94 0.120    -0.00732  -0.0298 
#> 6     6 JP3304200003  2014  9.83 0.124    -0.111     0.00410

Created on 2021-11-09 by the reprex package (v2.0.1)

Upvotes: 2

Related Questions