rkuebler - 1 year ago 54
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!

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
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download