CODEWITHSUNDEEP
r
isuckatcoding
isuckatcoding

Reputation: 31

Difference between two rows divided by the previous row

I have been able to find the difference between two consecutive rows for every column but now I need to divide this by the previous row. 

Date    FXDL.AED.USD    FXDL.ALL.USD
03/01/2005  3.6726  92.1
04/01/2005  3.6726  92.1
05/01/2005  3.6725  94.5
06/01/2005  3.6726  95.1
07/01/2005  3.6725  95.3

For example, the difference between (row 2 - row 1)/row 1 then (row 3 - row 2)/row 2 etc. for each column.

I have used diff_fxdl_weekdays <- sapply(fxdl_weekdays[,sapply(fxdl_weekdays, is.numeric)], diff)to find the difference and then I attempted to divide this by -nrow but this didn't work.

Upvotes: 2

Views: 1632

Answers (2)

Sotos
Sotos

Reputation: 51592

One idea is by using dplyr package and lag function,

library(dplyr)

#define the function
fun1 <- function(x){ (x - lag(x))/lag(x)}

mutate_if(df, is.numeric, fun1)
#        Date  FXDL.AED.USD FXDL.ALL.USD
#1 03/01/2005            NA           NA
#2 04/01/2005  0.000000e+00  0.000000000
#3 05/01/2005 -2.722867e-05  0.026058632
#4 06/01/2005  2.722941e-05  0.006349206
#5 07/01/2005 -2.722867e-05  0.002103049

If you also want to keep the original columns, then simply,

mutate_if(df, is.numeric, funs('diff' = fun1))
#        Date FXDL.AED.USD FXDL.ALL.USD FXDL.AED.USD_diff FXDL.ALL.USD_diff
#1 03/01/2005       3.6726         92.1                NA                NA
#2 04/01/2005       3.6726         92.1      0.000000e+00       0.000000000
#3 05/01/2005       3.6725         94.5     -2.722867e-05       0.026058632
#4 06/01/2005       3.6726         95.1      2.722941e-05       0.006349206
#5 07/01/2005       3.6725         95.3     -2.722867e-05       0.002103049

Upvotes: 4

MPhD
MPhD

Reputation: 456

Create a function:

f <- function(x) c(NA,(tail(x,-1) - head(x,-1))/head(x,-1))

Apply it to the numeric columns:

sapply(d[,sapply(d, is.numeric)], f)

And if you want to add it to the dataframe:

# replace the numeric columns with the new ones:
d[, sapply(d, is.numeric)] <- sapply(d[,sapply(d, is.numeric)], f)
# add new colums to the dataframe:
d[, paste0(names(d)[sapply(d, is.numeric)],'_diff')] <- sapply(d[,sapply(d, is.numeric)], f)

The result of the latter:

> d
        Date FXDL.AED.USD FXDL.ALL.USD FXDL.AED.USD_diff FXDL.ALL.USD_diff
1 2005-01-03       3.6726         92.1                NA                NA
2 2005-01-04       3.6726         92.1      0.000000e+00       0.000000000
3 2005-01-05       3.6725         94.5     -2.722867e-05       0.026058632
4 2005-01-06       3.6726         95.1      2.722941e-05       0.006349206
5 2005-01-07       3.6725         95.3     -2.722867e-05       0.002103049

Upvotes: 2

Related Questions