rkuebler rkuebler - 13 days ago 3
R Question

Build Stock Variable in Data Frame from another column

I am looking for a handy and quick way to build a new column with a stock variable that depends on previous observations of the new column and on values in the old column.

So Column B should be ColumnBt = 0.01 * Bt-1 + ColumnAt

The data looks like and should lead to

ColumnA ColumnB
1 1
0 0.01
0 0.0001
4 4.000001
5 5.04000001
0 0.504


At some point it would even be handy to be able to determine the factor (0.01) as a variable to alternate the carry over.

Any suggestions how to achieve this easily?

Any help or suggestion is highly appreciated! Many thanks beforehand!

Answer

The key is to build a suitable unit response matrix for the difference equation ColumnB[k] = 0.01 * ColumnB[k-1] + ColumnA[k]:

> z
      [,1]  [,2]  [,3]  [,4] [,5] [,6]
[1,] 1e+00 0e+00 0e+00 0e+00 0.00    0
[2,] 1e-02 1e+00 0e+00 0e+00 0.00    0
[3,] 1e-04 1e-02 1e+00 0e+00 0.00    0
[4,] 1e-06 1e-04 1e-02 1e+00 0.00    0
[5,] 1e-08 1e-06 1e-04 1e-02 1.00    0
[6,] 1e-10 1e-08 1e-06 1e-04 0.01    1

Then, by superposition ColumnB <- z %*% ColumnA. To construct this matrix:

lag <- 0.01  ## This is your parameter
r <- lag^(seq_len(length(ColumnA))-1)
m <- matrix(rep(r,length(ColumnA)),nrow=length(ColumnA))

z <- matrix(0,nrow=length(ColumnA),ncol=length(ColumnA))
z[lower.tri(z,diag=TRUE)] <- m[row(m) <= (length(ColumnA)+1-col(m))]
##      [,1]  [,2]  [,3]  [,4] [,5] [,6]
##[1,] 1e+00 0e+00 0e+00 0e+00 0.00    0
##[2,] 1e-02 1e+00 0e+00 0e+00 0.00    0
##[3,] 1e-04 1e-02 1e+00 0e+00 0.00    0
##[4,] 1e-06 1e-04 1e-02 1e+00 0.00    0
##[5,] 1e-08 1e-06 1e-04 1e-02 1.00    0
##[6,] 1e-10 1e-08 1e-06 1e-04 0.01    1

We can put this into a function:

constructZ <- function(lag, N) {
  r <- lag^(seq_len(N)-1)
  m <- matrix(rep(r,N),nrow=N)
  z <- matrix(0,nrow=N,ncol=N)
  z[lower.tri(z,diag=TRUE)] <- m[row(m) <= (N+1-col(m))]
  z
}

Then,

df <- data.frame(ColumnA=c(1,0,0,4,5,0))
df$ColumnB <- constructZ(0.01,nrow(df)) %*% df$ColumnA
print(df)
##  ColumnA  ColumnB
##1       1 1.000000
##2       0 0.010000
##3       0 0.000100
##4       4 4.000001
##5       5 5.040000
##6       0 0.050400

Updated answer for applying function by each indicator value

Assume you have the data:

df <- structure(list(ColumnA = c(1L, 0L, 0L, 4L, 5L, 0L, 4L, 0L, 2L
), Indicator = c(1L, 1L, 1L, 2L, 2L, 2L, 3L, 3L, 3L), Time = c(1L, 
2L, 3L, 1L, 2L, 3L, 1L, 2L, 3L)), .Names = c("ColumnA", "Indicator", 
"Time"), class = "data.frame", row.names = c(NA, -9L))
##  ColumnA Indicator Time
##1       1         1    1
##2       0         1    2
##3       0         1    3
##4       4         2    1
##5       5         2    2
##6       0         2    3
##7       4         3    1
##8       0         3    2
##9       2         3    3

where you want to compute the response for all observations in ColumnA (over all Time) separately for each Indicator value. Then you can do the following using constructZ:

df$ColumnB <- unlist(by(df,df$Indicator,function(df) constructZ(0.5,nrow(df)) %*% df$ColumnA))

Here, we use by to compute the supplied function separately on the data frame df split by the Indicator values. The supplied function is simply constructZ(0.5,nrow(df)) %*% df$ColumnA) as before where the lag parameter is 0.5. The output of by is a list, which we unlist and then set to df$ColumnB. The result is as expected:

print(df)
##  ColumnA Indicator Time ColumnB
##1       1         1    1    1.00
##2       0         1    2    0.50
##3       0         1    3    0.25
##4       4         2    1    4.00
##5       5         2    2    7.00
##6       0         2    3    3.50
##7       4         3    1    4.00
##8       0         3    2    2.00
##9       2         3    3    3.00