CODEWITHSUNDEEP
rdataframe
thequerist
thequerist

Reputation: 1824

Is there a faster way to get percent change?

I have a data frame with around 25000 records and 10 columns. I am using code to determine the change to the previous value in the same column (NewVal) based on another column (y) with a percent change already in it.

x=c(1:25000)
y=rpois(25000,2)
z=data.frame(x,y)
z[1,'NewVal']=z[1,'x']

So I ran this:

for(i in 2:nrow(z)){z$NewVal[i]=z$NewVal[i-1]+(z$NewVal[i-1]*(z$y[i]/100))}

This takes considerably longer than I expected it to. Granted I may be an impatient person - as a scathing letter drafted to me once said - but I am trying to escape the world of Excel (after I read http://www.burns-stat.com/pages/Tutor/spreadsheet_addiction.html, which is causing me more problems as I have begun to mistrust data - that letter also mentioned my trust issues).

I would like to do this without using any of the functions from packages as I would like to know what the formula for creating the values is - or if you will, I am a demanding control freak according to that friendly missive.

I would also like to know how to get a moving average just like rollmean in caTools. Either that or how do I figure out what their formula is? I tried entering rollmean and I think it refers to another function (I am new to R). This should probably be another question - but as that letter said, I don't ever make the right decisions in my life.

Upvotes: 1

Views: 932

Answers (2)

IRTFM
IRTFM

Reputation: 263362

I got about a 800-fold improvement with Reduce:

    system.time(z[, "NewVal"] <-Reduce("*",  c(1, 1+z$y[-1]/100), accumulate=T) )
   user  system elapsed 
  0.139   0.008   0.148 

> head(z)
    x y NewVal
1   1 1  1.000
2   2 1  1.010
3   3 1  1.020
4   4 5  1.071
5   5 1  1.082
6   6 2  1.103
7   7 2  1.126
8   8 3  1.159
9   9 0  1.159
10 10 1  1.171
> system.time(for(i in 2:nrow(z)){z$NewVal[i]=z$NewVal[i-1]+
                                              (z$NewVal[i-1]*(z$y[i]/100))})
   user  system elapsed 
  37.29  106.38  143.16

Upvotes: 6

Andrie
Andrie

Reputation: 179428

The secret in R is to vectorise. In your example you can use cumprod to do the heavy lifting:

z$NewVal2 <- x[1] * cumprod(with(z, 1 +(c(0, y[-1]/100))))

all.equal(z$NewVal, z$NewVal2)
[1] TRUE

head(z, 10)
    x y   NewVal  NewVal2
1  25 4 25.00000 25.00000
2  24 3 25.75000 25.75000
3  23 0 25.75000 25.75000
4  22 1 26.00750 26.00750
5  21 3 26.78773 26.78773
6  20 2 27.32348 27.32348
7  19 2 27.86995 27.86995
8  18 3 28.70605 28.70605
9  17 4 29.85429 29.85429
10 16 2 30.45138 30.45138

On my machine, the loop takes just less than 3 minutes to run, while the cumprod statement is virtually instantaneous.

Upvotes: 7

Related Questions