CODEWITHSUNDEEP
rdataframe
kostr
kostr

Reputation: 856

Function for Appending Rows to Data Frame Based on Previous Variable Values in R

I'm trying to create a function that allows me to append rows to an existing data frame based on variables from the most previous row (immediately above), multiple times over. 

#Here's what I'm starting with 

Balance <- c(25000)
Pmt <- c(1500)
Interest <- c(.05)
DF <- data.frame(Balance,Pmt,Interest)
DF  
   Balance Pmt Interest
1   25000 100     0.05

For example, I'd like to see the next 4 rows added where the "Balance" of the new row is equal to ((Balance-Pmt)*(1+Inerest)) of the previous row, and the "Pmt" and "Interest" rows stay constant. 

# Manually
DF[2,1] <- (DF[1,1] - DF[1,2])*(1+DF[1,3])
DF[2,2] <- DF[1,2]
DF[2,3] <- DF[1,3]
D
  Balance  Pmt Interest
1   25000 1500     0.05
2   24675 1500     0.05

Obviously, I'd like to replicate this as many times, n, as I'd like without having to manually reference the previous row. I'd like a function that allows me to add rows to the data frame as many times as I'd like, following that same logic. Any help is appreciated!

Upvotes: 1

Views: 446

Answers (4)

zx8754
zx8754

Reputation: 56249

Using tvm: Time Value of Money Functions package:

library(tvm)

n <- 5
rem(cf = rep(1500, n), amt = 25000, r = 0.05)
# [1] 24750.00 24487.50 24211.88 23922.47 23618.59

Upvotes: 1

Pierre L
Pierre L

Reputation: 28461

Instead of building the data frame line by line which will be very inefficient, you can setup the data frame to have the end size in mind. In this example you know you will run this process three times to end with four rows total. Make that data frame first to make the rest easier and quicker:

DF2 <- 'row.names<-'(DF[rep(1,4),], NULL)
DF2$Balance <- Reduce(function(x,y) (x - y)*(1+DF2[1,3]), DF2[-1,2], init=DF[1,1], acc=TRUE)
DF2
#    Balance  Pmt Interest
# 1 25000.00 1500     0.05
# 2 24675.00 1500     0.05
# 3 24333.75 1500     0.05
# 4 23975.44 1500     0.05

Self-Study: How does Reduce work?

Reduce is a higher level function that works in a unique way that takes practice to master. It takes in two main pieces, 1) a function with two arguments and 2) a single vector*. That's the weird part. It wants a function with two args and one vector. That doesn't make sense at first. How does it go through one vector with a function that needs two args? Example:

#one vector
x <- c(2,3,4,5)

#function with two arguments
multiply <- function(a, b)  {a * b}

This is a vector from 2 to 5, and a simple function that takes two numbers and multiplies them. Reduce will accept these two objects and do this:

Reduce(multiply, x) #<- this
#is the same as
ans1 <- multiply(2, 3) 
ans2 <- multiply(ans1, 4)
ans3 <- multiply(ans2, 5)

It went through the vector c(2,3,4,5) and took the first two parts (2 and 3) and called the function on them. Then it took that answer and brought in the third element of x (4) to run the function, then it took that answer and ran it with the fourth element (5).

In your example, we used it to go through the first balance and the payments:

#one vector
x <- c(25000, 1500, 1500, 1500)

#function with two arguments
f <- function(a,b) (a - b)*(1 + 0.05)

Let's see what it did internally:

Reduce(f, x) #what we did
#internally Reduce did this
ans1 <- (25000 - 1500) * (1 + 0.05)
ans2 <- (ans1 - 1500) * (1 + 0.05)
ans3 <- (ans2 - 1500) * (1 + 0.05)

This gave us the desired process. That is the idea of what we did. To totally complete our discussion, we added two extra arguments that helped us to get the desired result. accumulate=TRUE and init=DF[1,1]. The first simply tells Reduce that we want each successive answer instead of just the last one. The second tells Reduce what value we want to start with. To show what init did:

#Supply vector for function
x <- DF[-1,2]
x
[1] 1500 1500 1500

#We need the initial balance
#init=DF[1,1] does: 
[1] 25000 1500 1500 1500

Reduce can also take a list but we should achieve vector mastery first then introduce list methods.

Upvotes: 5

user2100721
user2100721

Reputation: 3597

You can use this function

add_function <- function(n) invisible(replicate(n,{DF <<- rbind(DF,DF[nrow(DF),],make.row.names = F);DF[nrow(DF),1] <<- (DF[nrow(DF)-1,1]-DF[nrow(DF)-1,2])*(1+DF[nrow(DF)-1,3])}))

Just supply the value of n

Upvotes: 0

rar
rar

Reputation: 924

Creating the dataset:

    Balance <- c(25000)
    Pmt <- c(1500)
    Interest <- c(.05)
    DF <- data.frame(Balance,Pmt,Interest)
    DF
1   25000 100     0.05

Then use the function for say n=10 times:

> for(i in 2:10){
+   DF[i,1] <- (DF[i-1,1] - DF[i-1,2])*(1+DF[i-1,3])
+   DF[i,2] <- DF[i-1,2]
+   DF[i,3] <- DF[i-1,3]
+ }
> DF
    Balance  Pmt Interest
# 1  25000.00 1500     0.05
# 2  24675.00 1500     0.05
# 3  24333.75 1500     0.05
# 4  23975.44 1500     0.05
# 5  23599.21 1500     0.05
# 6  23204.17 1500     0.05
# 7  22789.38 1500     0.05
# 8  22353.85 1500     0.05
# 9  21896.54 1500     0.05
# 10 21416.37 1500     0.05

You can replace 10 with 100 or 1000 depending on how many times you want to run this function.

Upvotes: 0

Related Questions