absolute and percent change by rows

Question

I have a data frame of numeric and factor data and would like to calculate the absolute and percent change between rows

df <- data.frame(names=c("A","A","A","A","B","B","B","C","C","C"),
            x1=c(1:10),
            x2=c(11:20),
            x3=c(21:30))

names   x1  x2  x3
A   1   11  21
A   2   12  22
A   3   13  23
A   4   14  24
B   5   15  25
B   6   16  26
B   7   17  27
C   8   18  28
C   9   19  29
C   10  20  30

I am trying to get the output to look something like this:

names   x1  x2  x3  x1.absdif   x2.absdif   x3.absdif   x1.pcndif   x2.pcndif   x3.pcndif
A   1   11  21  NA  NA  NA  NA  NA  NA
A   2   12  22  1   1   1   1.00    0.09    0.05
A   3   13  23  1   1   1   0.50    0.08    0.05
A   4   14  24  1   1   1   0.33    0.08    0.04
B   5   15  25  NA  NA  NA  NA  NA  NA
B   6   16  26  1   1   1   0.20    0.07    0.04
B   7   17  27  1   1   1   0.17    0.06    0.04
C   8   18  28  NA  NA  NA  NA  NA  NA
C   9   19  29  1   1   1   0.13    0.06    0.04
C   10  20  30  1   1   1   0.11    0.05    0.03

Any help would be appreciated.

talat · Answer 1 · 2014-05-12T22:16:56.187

You can do this for example with the package dplyr:

require(dplyr)                   #install the package dplyr and load it into the library

df <- df %.%                           #your data.frame
  group_by(names) %.% 
  mutate(count = 1:n()) %.% 
  mutate(x1.absdif = ifelse(count==1,NA,diff(x1)),
     x2.absdif = ifelse(count==1, NA,diff(x2)),
     x3.absdif = ifelse(count==1, NA,diff(x3)),
     x1.pcndif = ifelse(count==1, NA, x1.absdif/lag(x1,1)),
     x2.pcndif = ifelse(count==1, NA, x2.absdif/lag(x2,1)),
     x3.pcndif = ifelse(count==1, NA, x3.absdif/lag(x3,1))) %.%
  select(-count)

output:

#>df
#   names x1 x2 x3 x1.absdif x2.absdif x3.absdif x1.pcndif  x2.pcndif  x3.pcndif
#1      A  1 11 21        NA        NA        NA        NA         NA         NA
#2      A  2 12 22         1         1         1 1.0000000 0.09090909 0.04761905
#3      A  3 13 23         1         1         1 0.5000000 0.08333333 0.04545455
#4      A  4 14 24         1         1         1 0.3333333 0.07692308 0.04347826
#5      B  5 15 25        NA        NA        NA        NA         NA         NA
#6      B  6 16 26         1         1         1 0.2000000 0.06666667 0.04000000
#7      B  7 17 27         1         1         1 0.1666667 0.06250000 0.03846154
#8      C  8 18 28        NA        NA        NA        NA         NA         NA
#9      C  9 19 29         1         1         1 0.1250000 0.05555556 0.03571429
#10     C 10 20 30         1         1         1 0.1111111 0.05263158 0.03448276

Thanks. I've been wanting to start using dplyr, so I guess this is a good opportunity. Is is possible to calculate the absdif and pcndif for any given number of columns instead of having to hardcode each one? — user3549195, May 12 '14 at 22:35
@user3549195 I can imagine that there is a way, but at this moment, I dont know how that would work, unfortunately. — talat, May 12 '14 at 22:41

absolute and percent change by rows

1 Answers1

Linked