Is it possible to populate R dataframe with two interdependent columns

Question

I am try to use R to calculate sales as a function of inventory as a function of sales. See below data snapshot. Is there anyway to calculate this?

• Group, Day and Sell_Thru are independent variables
• Inv = lag(Inv,1) – lag(Sales,1)
• Sales = (Inv * Sell Thru)

I am given this data frame:

Group <- c("A","A","A","A","A","B","B","B","B","B")
Day <- c(1,2,3,4,5,1,2,3,4,5)
Inventory <- c(50000,NA,NA,NA,NA,20000,NA,NA,NA,NA)
Sell_Thru <- c(.05,.06,.07,.08,.09,.05,.06,.04,.11,.01)
Sales <- c(NA,NA,NA,NA,NA,NA,NA,NA,NA,NA)


inv_proj <- data.frame(Group, Day,Inventory,Sell_Thru,Sales)

Trying to populate this data frame

Group <- c("A","A","A","A","A","B","B","B","B","B")
Day <- c(1,2,3,4,5,1,2,3,4,5)
Inventory <-c(50000,47500,44650,41525,38203,20000,19000,17860,17146,15260)
Sell_Thru <- c(.05,.06,.07,.08,.09,.05,.06,.04,.11,.01)
Sales <- c(2500,2850,3126,3322,3438,1000,1140,714,1886,153)

inv_proj <- data.frame(Group, Day,Inventory,Sell_Thru,Sales)

Answer 1

for(i in 1:nrow(inv_proj)) {
  inv_proj$Sales[i] = round(inv_proj$Inventory[i] * inv_proj$Sell_Thru[i])
  if (i == nrow(inv_proj)) break
  if (is.na(inv_proj$Inventory[i + 1])) {
    inv_proj$Inventory[i + 1] = inv_proj$Inventory[i] - inv_proj$Sales[i]
  }
}

inv_proj
#    Group Day Inventory Sell_Thru Sales
# 1      A   1     50000      0.05  2500
# 2      A   2     47500      0.06  2850
# 3      A   3     44650      0.07  3126
# 4      A   4     41524      0.08  3322
# 5      A   5     38202      0.09  3438
# 6      B   1     20000      0.05  1000
# 7      B   2     19000      0.06  1140
# 8      B   3     17860      0.04   714
# 9      B   4     17146      0.11  1886
# 10     B   5     15260      0.01   153