[R] running balance down a dataframe referring back to previous row

From: seanpor <seanpor_at_acm.org>
Date: Wed, 19 Mar 2008 02:58:42 -0700 (PDT)

Good morning, I've searched high and low and I've tried many different ways of doing this, but I can't seem to get it to work.

I'm looking for a way of vectorising a "running balance"; i.e. the value in the first row of the dataframe is zero, and following rows add to this running balance. This is easy to write in a loop, but I can't seem to get it working in vectorised code. Hopefully the example below will explain what I'm trying to do...

#####
# create a dummy dataframe

txns <- data.frame(LETTERS)
set.seed(123)

# randomly specify debit / credit columns

txns$drcr <- sample(c('d','c'), nrow(txns), replace=T)
txns$dr <- 0
txns$cr <- 0

# give values to the debits / credits...
txns[txns$drcr == 'd', 'dr'] <- runif(nrow(txns[txns$drcr == 'd',]), min=0, max=10000)
txns[txns$drcr == 'c', 'cr'] <- runif(nrow(txns[txns$drcr == 'c',]), min=0, max=10000)

# reset the initial dr/cr value to zero...
txns[1,'dr'] <- 0
txns[1,'cr'] <- 0

# intialize the entire running balance column to zero
txns$rbal <- 0

# setup a row index starting at row 2 so that we only operate on these
rows...
r0 <- c(2:nrow(txns))

# setup a row index offset by 1 so that we can access the running balance
# from the previous line...

r1 <- c(2:nrow(txns)) - 1

# calculate the running balance using vectorized code unfortunately this
doesn't work...
txns[r0,'rbal'] <- txns[r1,'rbal'] + txns[r0,'dr'] - txns[r0,'cr']

# calculate the running balance using a loop
txns$running.bal <- 0
for (i in (2:nrow(txns))) {

        txns[i,'running.bal'] <- txns[(i-1), 'running.bal'] + txns[i, 'dr'] - txns[i, 'cr']
}
txns
#####

I was hoping that rbal and running.bal would be the same... evidently not... I've even tried --vanilla...

Is there a specified order in which vectorized dataframe calculations are carried out? Top to bottom or unspecified? Does it work off a copy and then replace the old column? Do I just have to use a loop for this?

                           
platform       i386-pc-mingw32             
arch           i386                        
os             mingw32                     
system         i386, mingw32               
status                                     
major          2                           
minor          6.2                         
year           2008                        
month          02                          
day            08                          
svn rev        44383                       
language       R                           
version.string R version 2.6.2 (2008-02-08)

Many thanks in advance,

Best regards,
Sean O'Riordain

-- 
View this message in context: http://www.nabble.com/running-balance-down-a-dataframe-referring-back-to-previous-row-tp16142263p16142263.html
Sent from the R help mailing list archive at Nabble.com.

______________________________________________
R-help_at_r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Received on Wed 19 Mar 2008 - 10:01:14 GMT

Archive maintained by Robert King, hosted by the discipline of statistics at the University of Newcastle, Australia.
Archive generated by hypermail 2.2.0, at Wed 19 Mar 2008 - 16:30:22 GMT.

Mailing list information is available at https://stat.ethz.ch/mailman/listinfo/r-help. Please read the posting guide before posting to the list.

list of date sections of archive