title: R: How do I replace NAs in a dataframe column with values from conditions leveraging other multiple columns?
body: <p>Using R, I am trying to fill NAs in a column with values leveraging conditions of other columns. The data frame has 4columns. The 4 columns are described below.</p>
  
  <p>"Water_Level": Has some values which also include NAs. This is the column I want to replace the NAs. Take this column as the amount of water in liters in a tank.</p>
  
  <p>"Tank": Unique identifier for tanks. In this sample, I have tank 1 and tank 2.</p>
  
  <p>"Flag": This has a series of 0's and 1's. When value is 0 the tap is opened and the Water_level value decreases by a constant of 0.05. When flag is 1, the tank is being pumped, so the water level increases in the respective tank gradually to the peak value at the end of the series of 1's. The rate of increase is varies and is determined by the length of 1's in the Flag column or the Counter number corresponding to the end of the series of 1's.</p>
  
  <p>"Counter": A column counting the number of 0's and 1's in the flag column in order.</p>
  
  <p>I need to fill the NAs in the "Water_level" column with the conditions of the other columns.</p>
  
  <p>Honestly, I haven't been able to try anything despite clearly understanding the outcome required.</p>
  
  <pre><code>df &lt;- data.frame(
    Water_level = c(67.92, rep(NA,9),67.96,10.5,rep(NA,8),20),
    Flag = c(rep(0,5),rep(1,6),rep(0,5),rep(1,5)),
    Tank= c(rep(1, 11), rep(2, 10)),
    Counter = c(seq(1:5),seq(1:6), seq(1:5),seq(1:5))
  )
  
  df
  
     Water_level Flag Tank Counter
  1        67.92    0    1       1
  2           NA    0    1       2
  3           NA    0    1       3
  4           NA    0    1       4
  5           NA    0    1       5
  6           NA    1    1       1
  7           NA    1    1       2
  8           NA    1    1       3
  9           NA    1    1       4
  10          NA    1    1       5
  11       67.96    1    1       6
  12       10.50    0    2       1
  13          NA    0    2       2
  14          NA    0    2       3
  15          NA    0    2       4
  16          NA    0    2       5
  17          NA    1    2       1
  18          NA    1    2       2
  19          NA    1    2       3
  20          NA    1    2       4
  21       20.00    1    2       5
  </code></pre>
  
  <p>The result expected is to fill the NAs in the Water_level as described by the conditions in my introduction.</p>
  
  <p>For example, line 2 in the "Water_level" should be 67.92 - 0.05 = 67.87. This is because the tap is open i.e Flag is at 0. line 3 will be 67.87 - 0.05 = 67.82 and so on.</p>
  
  <p>The tricky part is in line 6 were the Flag changes to 1 i.e the tank is being pumped. We can see the series of 1's for Tank 1 ends at line 11. The peak value recorded for water_level is 67.96. So the rate of increase from line 6 to 10 will now be as seen in the formular below.</p>
  
  <p>(67.96- value at line5 following the decrease pattern) / number of Counter steps i.e 6 for this case</p>
  
  <p>This calculation continues for Tank 2.</p>
  
  <p>Thanks is anticipation for a solution.</p>
  
  <p>Update.</p>
  
  <p>@manotheshark. This is a good beginning. But it doesnt generalise well. When I include row 12 to 16, it produces a wrong output. i.e. it doesnt decline by 0.05 from line 11.</p>
  
  <pre><code>df &lt;- data.frame(
    Water_level = c(67.92, rep(NA,9),67.96, rep(NA,5),10.5,rep(NA,8),20),
    Flag = c(rep(0,5),rep(1,6),rep(0,5),rep(0,5),rep(1,5)),
    Tank= c(rep(1, 16), rep(2, 10)),
    Counter = c(seq(1:5),seq(1:6),seq(1:5), seq(1:5),seq(1:5))
  )
  df
  
     Water_level Flag Tank Counter
  1        67.92    0    1       1
  2           NA    0    1       2
  3           NA    0    1       3
  4           NA    0    1       4
  5           NA    0    1       5
  6           NA    1    1       1
  7           NA    1    1       2
  8           NA    1    1       3
  9           NA    1    1       4
  10          NA    1    1       5
  11       67.96    1    1       6
  12          NA    0    1       1
  13          NA    0    1       2
  14          NA    0    1       3
  15          NA    0    1       4
  16          NA    0    1       5
  17       10.50    0    2       1
  18          NA    0    2       2
  19          NA    0    2       3
  20          NA    0    2       4
  21          NA    0    2       5
  22          NA    1    2       1
  23          NA    1    2       2
  24          NA    1    2       3
  25          NA    1    2       4
  26       20.00    1    2       5
  </code></pre>
  
  <p>The output running your solution is presented below. Line 12 should be 67.96 - 0.05 = 67.91.</p>
  
  <pre><code>   Water_level Flag Tank Counter
  1     67.92000    0    1       1
  2     67.87000    0    1       2
  3     67.82000    0    1       3
  4     67.77000    0    1       4
  5     67.72000    0    1       5
  6     67.30167    1    1       1
  7     67.43333    1    1       2
  8     67.56500    1    1       3
  9     67.69667    1    1       4
  10    67.82833    1    1       5
  11    67.96000    1    1       6
  12    67.37000    0    1       1
  13    67.32000    0    1       2
  14    67.27000    0    1       3
  15    67.22000    0    1       4
  16    67.17000    0    1       5
  17    10.50000    0    2       1
  18    10.45000    0    2       2
  19    10.40000    0    2       3
  20    10.35000    0    2       4
  21    10.30000    0    2       5
  22    12.24000    1    2       1
  23    14.18000    1    2       2
  24    16.12000    1    2       3
  25    18.06000    1    2       4
  26    20.00000    1    2       5
  </code></pre>