title: Casting the difference between two vectors into a specified format
body: <p>I am trying to create code that automatically creates data in the format below (for more background on the <code>colspan</code> see <a href="https://stackoverflow.com/questions/71966045/feeding-r-data-to-html-in-r-markdown-where-the-output-is-a-word-document">this link</a>).</p>
  <p>I need to do two things:</p>
  <ol>
  <li>Calculate the &quot;difference&quot; or &quot;distance&quot; for lack of a better word between two vectors.</li>
  <li>Somehow cast or transpose the data.</li>
  </ol>
  <p>I have no idea where to start, so I was hoping someone could help me get started.</p>
  <h1>Desired format</h1>
  <pre><code>lower_threshold &lt;- data.frame(row=c(&quot;Lower threshold&quot;), freq = c(0, 25, 50, 100, 250, 500, 1000, 1500, 3000, &quot;Infinity&quot;, &quot;SUM&quot;), width=50, colspan=1)
  
                 row  freq width colspan
  1  Lower threshold     0    50      1
  2  Lower threshold    25    50      1
  3  Lower threshold    50    50      1
  4  Lower threshold   100    50      1
  5  Lower threshold   250    50      1
  6  Lower threshold   500    50      1
  7  Lower threshold  1000    50      1
  8  Lower threshold  1500    50      1
  9  Lower threshold  3000    50      1
  10 Lower threshold   SUM    50      1
  </code></pre>
  <h1>Data to be formatted</h1>
  <p>The data I have is as follows:</p>
  <pre><code>dat &lt;- structure(list(Name = c(&quot;Name_a&quot;, 
  &quot;Name_b&quot;), freq = list(bb = c(5, 4, 3, 2, 14), aa = c(1, 6, 0, 7)), columns_used = list(bb = c(0, 25, 100, 500, &quot;Infinity&quot;, &quot;SUM&quot;), aa = c(0, 25, 3000, &quot;Infinity&quot;, &quot;SUM&quot;))), row.names = c(NA, 
  -2L), class = &quot;data.frame&quot;)
  </code></pre>
  <p><a href="https://i.stack.imgur.com/BNVew.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/BNVew.png" alt="enter image description here" /></a></p>
  <h1>Calculating colspan/width</h1>
  <p>I want to calculate <code>colspan</code> and <code>width</code> (which is simply <code>50*colspan</code>) based on the difference between the <code>columns_used</code> column and the following vector:</p>
  <pre><code>total_colspan = c(0, 25, 50, 100, 250, 500, 1000, 1500, 3000, &quot;Infinity&quot;, &quot;SUM&quot;)
  </code></pre>
  <p>As an example, for <code>Name_a</code>, the first <code>colspan</code> would be <code>1</code> (and <code>width=50</code>), because the first threshold is <code>0-25</code> for both vectors. However <code>Name_a</code> skips the number <code>50</code>, so the next <code>colspan</code> should be <code>2</code> (and <code>width=100</code>).</p>
  <h1>Desired output:</h1>
  <pre><code>   row         row freq  width colspan
  1  Name_a              5    50      1
  2  Name_a              4   100      2
  3  Name_a              3   100      2
  4  Name_a              2   200      4
  5  Name_a             14    50      1
  6  Name_b              1    50      1
  ...
  11 Name_b              7    50      1
  </code></pre>
  <p>Where the sum of <code>colspan</code> for each <code>Name_x</code> should be 10 in total and <code>row_freq</code> comes from the <code>freq</code> column in <code>dat</code>.</p>
  <p>The idea is to use the desired output to create a <code>hmtl</code> table:</p>
  <p><a href="https://i.stack.imgur.com/8GTWR.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/8GTWR.png" alt="enter image description here" /></a></p>