How to create columns iteratively?

April 15, 2022

Here is a sample of my data;

df <- structure(list(Product = c("A", "A", "A", "A", "A"), Month = 8:12, 
    Real = c(1550L, 2238L, 1534L, 726L, 255L), coef = c(0.9503, 
    0.9503, 0.9503, 0.9503, 0.9503)), row.names = c(NA, -5L), class = c("data.table", 
"data.frame"))

I need to calculate a constant (named Bias) for each row and create columns using Bias and Iter columns.

If it makes little sense, here is the way I do it;

df %>% 
mutate(Iter1 = ceiling(coef*Real)) %>% 
mutate(Bias1 = c(0,((Real[1] - Iter1[1])/Real[1]),rep(0,n() - 2))) %>% 
mutate(Iter2 = ceiling(Iter1*(1 + Bias1))) %>% 
mutate(Bias2 = c(0,0,((Real[2] - Iter2[2])/Real[2]),rep(0,n() - 3))) %>% 
mutate(Iter3 = ceiling(Iter2*(1 + Bias2))) %>% 
mutate(Bias3 = c(0,0,0,((Real[3] - Iter3[3])/Real[3]),rep(0,n() - 4))) %>% 
mutate(Iter4 = ceiling(Iter3*(1 + Bias3))) %>% 
mutate(Bias4 = c(0,0,0,0,((Real[4] - Iter4[4])/Real[4]),rep(0,n() - 5))) %>% 
mutate(Iter5 = ceiling(Iter4*(1 + Bias4))) %>% 
select(-contains('Bias'))

I can’t adapt this ugly and hard-coded thing everywhere because there are too many tables like this and the number of rows is different, which means the number of columns I have to create for each is different.

I’m trying to make it generic.

Here is my desired output ;

  Product Month  Real  coef Iter1 Iter2 Iter3 Iter4 Iter5
  <chr>   <int> <int> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 A           8  1550 0.950  1473  1473  1473  1473  1473
2 A           9  2238 0.950  2127  2233  2233  2233  2233
3 A          10  1534 0.950  1458  1458  1462  1462  1462
4 A          11   726 0.950   690   690   690   723   723
5 A          12   255 0.950   243   243   243   243   245

Thanks in advance.

>Solution :

A recursive use of for loop may be used

coef <- df$coef
 real <- df$Real
 for(i in seq_len(nrow(df))) {
 
     
      if(i == 1) {
        iter <- ceiling(coef* real)
        df[, paste0('Iter', i) := iter]
        bias <- c(rep(0, i),(( real[i]- iter[i])/real[i]),
              rep(0, nrow(df) - (i + 1)))
        df[, paste0("Bias", i) := bias]
      
      
      } else {
           
        iter <-  ceiling(df[[paste0('Iter', i-1)]]*(1 +
              df[[paste0('Bias', i-1)]]))
         df[, paste0('Iter', i) := iter]
         if(i < 5) {
           bias <- c(rep(0, i), ((real[i] - 
           df[[paste0('Iter', i)]][i])/real[i]),rep(0, nrow(df) - (i +1) ))
           df[, paste0("Bias", i) := bias]
           }
      
      }
 
 
}

df[, paste0("Bias", 1:4) := NULL]

-output

> df
   Product Month  Real   coef Iter1 Iter2 Iter3 Iter4 Iter5
    <char> <int> <int>  <num> <num> <num> <num> <num> <num>
1:       A     8  1550 0.9503  1473  1473  1473  1473  1473
2:       A     9  2238 0.9503  2127  2233  2233  2233  2233
3:       A    10  1534 0.9503  1458  1458  1462  1462  1462
4:       A    11   726 0.9503   690   690   690   723   723
5:       A    12   255 0.9503   243   243   243   243   245