Search code examples
rlagrolling-computationdelta

Running delta - Lag difference over row of data frame


I have a data frame such as

df1 <- data.frame(Company = c('A','B','C','D','E'),
                  `X1980` = c(1, 5, 3, 8, 13),
                  `X1981` = c(20, 13, 23, 11, 29),
                  `X1982` = c(33, 32, 31, 41, 42),
                  `X1983` = c(45, 47, 53, 58, 55))

I would like to create a new data frame (df2) keeping the company column as is. The values for the years 1980 and 1983 should be calculated by using the current value minus the previous value. So basically I would like a data frame resulting in the rolling deltas.

        Company  1980  1981  1982  1983
           A      NA    19    13    12
           B      NA    8     19    15
           C      NA    20    8     22
           D      NA    3     30    17
           E      NA    16    13    13

Thanks for the help! If there's any way for me to improve the question, then just let me know.


Solution

  • You can find difference in each row adding NA to first value.

    df1[-1] <- t(apply(df1[-1], 1, function(x) c(NA, diff(x))))
    df1
    
    #  Company X1980 X1981 X1982 X1983
    #1       A    NA    19    13    12
    #2       B    NA     8    19    15
    #3       C    NA    20     8    22
    #4       D    NA     3    30    17
    #5       E    NA    16    13    13
    

    You can also use tidyverse functions.

    library(dplyr)
    library(tidyr)
    
    df1 %>%
      pivot_longer(cols = -Company) %>%
      group_by(Company) %>%
      mutate(value = value - lag(value)) %>%
      pivot_wider()