title: reshape to wide format, while varying only a single column at a time (for sensitivity analysis)
body: <p>There are already many Q&amp;A's relating to reshaping data, but I did not find one that deals with this specific case.</p>
  <p>I want to take a long format table that provides low, average and high values for an arbitrary number of parameters, with values varying over an arbitrary number of groups.  Here is a simple example table with just two parameters (f1, f2) and two groups (A, B).</p>
  <pre><code>library(data.table)
  input = fread(&quot;
  grp variable value level
  A   f1       0.1   low
  A   f1       0.2   average
  A   f1       0.3   high
  A   f2       0.5   low
  A   f2       0.6   average
  A   f2       0.7   high
  B   f1       1.0   low
  B   f1       2.0   average
  B   f1       3.0   high
  B   f2       -10   low
  B   f2       -5    average
  B   f2       0     high
  &quot;)
  </code></pre>
  <p>To conduct sensitivity analyses, I would like to convert this to a table in which each parameter is varied from low to high, while fixing all other parameters at their average value (for each group).</p>
  <p>The expected output for the simple example would look like this:</p>
  <pre><code>expected = fread(&quot;
  grp  variable    level  f1   f2
  A    f1          low   0.1  0.6
  A    f1          high  0.3  0.6
  A    f2          low   0.2  0.5
  A    f2          high  0.2  0.7
  B    f1          low   1.0  -5
  B    f1          high  3.0  -5
  B    f2          low   2.0  -10
  B    f2          high  2.0  0
  &quot;)
  </code></pre>
  <p>I can find the varying factors for each sensitivity, using <code>dcast</code>:</p>
  <pre><code>dcast(input, grp + variable + level ~ variable)[level != 'average']
  #    grp sensitivity level  f1    f2
  # 1:   A          f1  high 0.3    NA
  # 2:   A          f1   low 0.1    NA
  # 3:   A          f2  high  NA   0.7
  # 4:   A          f2   low  NA   0.5
  # 5:   B          f1  high 3.0    NA
  # 6:   B          f1   low 1.0    NA
  # 7:   B          f2  high  NA   0.0
  # 8:   B          f2   low  NA -10.0
  </code></pre>
  <p>But, I did not find a neat way to also fill in the average values for the other factors that works for any number of groups and factors.  I'm really interested in base or <code>data.table</code> solutions, but other packages also welcome as they could help others.</p>
  <hr />
  <p>Here's a slightly larger input table with 3 parameters and 3 groups, to test solutions on:</p>
  <pre><code>structure(list(grp = c(&quot;A&quot;, &quot;A&quot;, &quot;A&quot;, &quot;A&quot;, &quot;A&quot;, &quot;A&quot;, &quot;A&quot;, &quot;A&quot;, 
  &quot;A&quot;, &quot;B&quot;, &quot;B&quot;, &quot;B&quot;, &quot;B&quot;, &quot;B&quot;, &quot;B&quot;, &quot;B&quot;, &quot;B&quot;, &quot;B&quot;, &quot;C&quot;, &quot;C&quot;, &quot;C&quot;, 
  &quot;C&quot;, &quot;C&quot;, &quot;C&quot;, &quot;C&quot;, &quot;C&quot;, &quot;C&quot;), variable = c(&quot;f1&quot;, &quot;f1&quot;, &quot;f1&quot;, 
  &quot;f2&quot;, &quot;f2&quot;, &quot;f2&quot;, &quot;f3&quot;, &quot;f3&quot;, &quot;f3&quot;, &quot;f1&quot;, &quot;f1&quot;, &quot;f1&quot;, &quot;f2&quot;, &quot;f2&quot;, 
  &quot;f2&quot;, &quot;f3&quot;, &quot;f3&quot;, &quot;f3&quot;, &quot;f1&quot;, &quot;f1&quot;, &quot;f1&quot;, &quot;f2&quot;, &quot;f2&quot;, &quot;f2&quot;, &quot;f3&quot;, 
  &quot;f3&quot;, &quot;f3&quot;), value = c(0.1, 0.2, 0.3, 0.5, 0.6, 0.7, 4, 5, 6, 
  1, 2, 3, -10, -5, 0, 11, 12, 13, 100, 200, 300, 3, 6, 9, 21, 
  22, 23), level = c(&quot;low&quot;, &quot;average&quot;, &quot;high&quot;, &quot;low&quot;, &quot;average&quot;, 
  &quot;high&quot;, &quot;low&quot;, &quot;average&quot;, &quot;high&quot;, &quot;low&quot;, &quot;average&quot;, &quot;high&quot;, &quot;low&quot;, 
  &quot;average&quot;, &quot;high&quot;, &quot;low&quot;, &quot;average&quot;, &quot;high&quot;, &quot;low&quot;, &quot;average&quot;, 
  &quot;high&quot;, &quot;low&quot;, &quot;average&quot;, &quot;high&quot;, &quot;low&quot;, &quot;average&quot;, &quot;high&quot;)), row.names = c(NA, 
  -27L), class = c(&quot;data.table&quot;, &quot;data.frame&quot;))
  </code></pre>
  <hr />
  <p>In case it helps, we can also generate larger input tables to test using the following.  My current use case has nearly 10,000 groups and ~25 parameters.</p>
  <pre><code>Ngrp = 5
  Nf = 7
  grp = 1:Ngrp
  f = paste0(&quot;f&quot;, 1:Nf)
  input = expand.grid(grp = grp, variable=f, level = c(&quot;low&quot;, &quot;average&quot;, &quot;high&quot;))
  setDT(input)
  setkey(input, grp, variable, level)
  input[level == 'low',     value := rnorm(.N, grp,    grp/10), by=.(grp,variable)]
  input[level == 'average', value := rnorm(.N, grp*5,  grp/2), by=.(grp,variable)]
  input[level == 'high',    value := rnorm(.N, grp*10, grp), by=.(grp,variable)]
  </code></pre>