title: How to subtract one row value from another and assign the value to a different row all in the same column, by group, for multiple columns in R
body: <p>I have a strangely structured data frame with a grouping variable as a factor, followed by a character variable identifying expected vs. actual values for a series of ongoing days (where the actual values are recorded each day in a new column), as well as cumulatively, and I'm trying to calculate the difference between the expected and the actual values.</p>
  <p>However, given the structure of the data, I'm struggling to figure out how to do this without getting into a whole mess of reshaping the data only to have to put it back together like this.</p>
  <p>Is there a way I can calculate the differences, for each group, across all 'daily' columns, and then store the output in each 'original' column?</p>
  <p><strong>Sample Data</strong></p>
  <pre><code>df1 &lt;- structure(list(Group = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 3L, 3L, 3L, 3L, 3L, 3L, 3L), .Label = c(&quot;A&quot;, &quot;B&quot;, &quot;C&quot;), class = &quot;factor&quot;), Variable = c(&quot;Day&quot;, &quot;Actual.Cumulative&quot;, &quot;Actual.Daily&quot;, &quot;Expected.Cumulative&quot;, &quot;Expected.Daily&quot;, &quot;Diff.Daily&quot;, &quot;Diff.Cumulative&quot;, &quot;Day&quot;, &quot;Actual.Cumulative&quot;, &quot;Actual.Daily&quot;, &quot;Expected.Cumulative&quot;, &quot;Expected.Daily&quot;, &quot;Diff.Daily&quot;, &quot;Diff.Cumulative&quot;, &quot;Day&quot;, &quot;Actual.Cumulative&quot;, &quot;Actual.Daily&quot;, &quot;Expected.Cumulative&quot;, &quot;Expected.Daily&quot;, &quot;Diff.Daily&quot;, &quot;Diff.Cumulative&quot;), Day1 = c(&quot;Sunday&quot;, &quot;1&quot;, &quot;1&quot;, &quot;3&quot;, &quot;3&quot;, NA, NA, &quot;Sunday&quot;, &quot;1&quot;, &quot;1&quot;, &quot;1&quot;, &quot;1&quot;, NA, NA, &quot;Sunday&quot;, &quot;8&quot;, &quot;8&quot;, &quot;5&quot;, &quot;5&quot;, NA, NA), Day2 = c(&quot;Monday&quot;, &quot;4&quot;, &quot;3&quot;, &quot;7&quot;, &quot;4&quot;, NA, NA, &quot;Monday&quot;, &quot;2&quot;, &quot;1&quot;, &quot;3&quot;, &quot;2&quot;, NA, NA, &quot;Monday&quot;, &quot;17&quot;, &quot;9&quot;, &quot;10&quot;, &quot;5&quot;, NA, NA), Day3 = c(&quot;Tuesday&quot;, &quot;6&quot;, &quot;2&quot;, &quot;10&quot;, &quot;3&quot;, NA, NA, &quot;Tuesday&quot;, &quot;3&quot;, &quot;1&quot;, &quot;4&quot;, &quot;1&quot;, NA, NA, &quot;Tuesday&quot;, &quot;29&quot;, &quot;12&quot;, &quot;14&quot;, &quot;4&quot;, NA, NA), Day4 = c(&quot;Wednesday&quot;, &quot;11&quot;, &quot;5&quot;, &quot;12&quot;, &quot;2&quot;, NA, NA, &quot;Wednesday&quot;, &quot;4&quot;, &quot;1&quot;, &quot;5&quot;, &quot;1&quot;, NA, NA, &quot;Wednesday&quot;, &quot;32&quot;, &quot;3&quot;, &quot;17&quot;, &quot;3&quot;, NA, NA)), row.names = c(NA, -21L), class = &quot;data.frame&quot;)
  df1
  </code></pre>
  <p>What I would like to do is fill  in the <code>Diff.Daily</code> cells in each &quot;Day&quot; column (i.e. <code>Day1</code>, <code>Day2</code>, etc.) by taking the difference between the <code>Actual.Daily</code> and <code>Expected.Daily</code> values for each <code>Group</code> (A, B, C), and then do similarly for <code>Diff.Cumulative</code> (i.e. <code>Actual.Cumulative - Expected.Cumulative</code>).</p>
  <p><strong>Desired Output</strong></p>
  <pre><code>df2 &lt;- structure(list(Group = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 3L, 3L, 3L, 3L, 3L, 3L, 3L), .Label = c(&quot;A&quot;, &quot;B&quot;, &quot;C&quot;), class = &quot;factor&quot;), Variable = c(&quot;Day&quot;, &quot;Actual.Cumulative&quot;, &quot;Actual.Daily&quot;, &quot;Expected.Cumulative&quot;, &quot;Expected.Daily&quot;, &quot;Diff.Daily&quot;, &quot;Diff.Cumulative&quot;, &quot;Day&quot;, &quot;Actual.Cumulative&quot;, &quot;Actual.Daily&quot;, &quot;Expected.Cumulative&quot;, &quot;Expected.Daily&quot;, &quot;Diff.Daily&quot;, &quot;Diff.Cumulative&quot;, &quot;Day&quot;, &quot;Actual.Cumulative&quot;, &quot;Actual.Daily&quot;, &quot;Expected.Cumulative&quot;, &quot;Expected.Daily&quot;, &quot;Diff.Daily&quot;, &quot;Diff.Cumulative&quot;), Day1 = c(&quot;Sunday&quot;, &quot;1&quot;, &quot;1&quot;, &quot;3&quot;, &quot;3&quot;, &quot;-2&quot;, &quot;-2&quot;, &quot;Sunday&quot;, &quot;1&quot;, &quot;1&quot;, &quot;1&quot;, &quot;1&quot;, &quot;0&quot;, &quot;0&quot;, &quot;Sunday&quot;, &quot;8&quot;, &quot;8&quot;, &quot;5&quot;, &quot;5&quot;, &quot;3&quot;, &quot;3&quot;), Day2 = c(&quot;Monday&quot;, &quot;4&quot;, &quot;3&quot;, &quot;7&quot;, &quot;4&quot;, &quot;-1&quot;, &quot;-3&quot;, &quot;Monday&quot;, &quot;2&quot;, &quot;1&quot;, &quot;3&quot;, &quot;2&quot;, &quot;-1&quot;, &quot;-1&quot;, &quot;Monday&quot;, &quot;17&quot;, &quot;9&quot;, &quot;10&quot;, &quot;5&quot;, &quot;4&quot;, &quot;7&quot;), Day3 = c(&quot;Tuesday&quot;, &quot;6&quot;, &quot;2&quot;, &quot;10&quot;, &quot;3&quot;, &quot;-1&quot;, &quot;-4&quot;, &quot;Tuesday&quot;, &quot;3&quot;, &quot;1&quot;, &quot;4&quot;, &quot;1&quot;, &quot;0&quot;, &quot;-1&quot;, &quot;Tuesday&quot;, &quot;29&quot;, &quot;12&quot;, &quot;14&quot;, &quot;4&quot;, &quot;8&quot;, &quot;15&quot;), Day4 = c(&quot;Wednesday&quot;, &quot;11&quot;, &quot;5&quot;, &quot;12&quot;, &quot;2&quot;, &quot;3&quot;, &quot;-1&quot;, &quot;Wednesday&quot;, &quot;4&quot;, &quot;1&quot;, &quot;5&quot;, &quot;1&quot;, &quot;0&quot;, &quot;-1&quot;, &quot;Wednesday&quot;, &quot;32&quot;, &quot;3&quot;, &quot;17&quot;, &quot;3&quot;, &quot;0&quot;, &quot;15&quot;)), row.names = c(NA, -21L), class = &quot;data.frame&quot;)
  df2    
  </code></pre>
  <p>Here you can see for Group A on Day 1, <code>Diff.Daily</code> = -2 as expected from <code>Actual.Daily</code> of 1 minus an <code>Expected.Daily</code> of 3.</p>
  <p><strong>My Attempts</strong></p>
  <p>As the &quot;Day&quot; columns are of character class because of their mixed nature, I know I need to wrap the calculations with <code>as.numeric()</code>, and I can calculate the values individually for each column:</p>
  <pre><code>Diff.Cumulative &lt;- as.numeric(df1[df1$Variable == &quot;Actual.Cumulative&quot;, &quot;Day1&quot;]) - as.numeric(df1[df1$Variable == &quot;Expected.Cumulative&quot;, &quot;Day1&quot;])
  </code></pre>
  <p>But I'm struggling with how to expand this to all &quot;Day&quot; columns and to fit it into the current data frame structure. I came across a <a href="https://stackoverflow.com/questions/43899377/how-to-subtract-one-row-from-multiple-rows-by-group-for-data-set-with-multiple">similar but ultimately different</a> question.</p>