title: Using melt or pivot_longer over 2 rows with variables
body: <p>I have this output that contains the values within a family of genes (e.g. family01, family21) in distinct 'places' (bel..tej), but it also contains some metadata associated with these sites (type of Environment). I know the data is not as tidy as I wish, so I wonder if there is an elegant way to use <code>pivot_longer</code> to tidy this in a long format to make a heatmap later.</p>
  <p>Here is the dataset.</p>
  <pre><code>Sample  bel buc mal man pen poc tej
  Environment C_3 C_1 C_3 C_3 C_2 C_1 C_3
  family01    1.962   20.790  0.000   0.000   0.000   0.000   1.962
  family03    0.000   3.150   0.000   0.000   152.614 0.089   0.000
  family08    4.482   12.603  0.168   0.460   0.000   2.917   4.482
  family13    0.000   1.697   0.000   0.000   169.841 0.000   0.000
  family17    0.462   10.689  0.000   0.000   0.000   1.387   0.462
  family21    0.000   0.410   0.000   0.000   122.959 0.000   0.000
  </code></pre>
  <p>I searched for help with the function <code>pivot_longer</code> and several questions referred to multiple <em>columns</em> not <em>rows</em> and not about 'simultaneously' applying the function to two set of variables. First I tried this <code>long &lt;- pivot_longer(data = output.txt, cols = -c(Family), names_to = &quot;site&quot;, values_to = &quot;rpkm&quot;)</code> but I obtained this result</p>
  <pre><code># A tibble: 49 x 3
     Sample      site  rpkm  
     &lt;chr&gt;       &lt;chr&gt; &lt;chr&gt; 
   1 Environment bel   C_3    
   2 Environment buc   C_1    
   3 Environment mal   C_3    
   4 Environment man   C_3    
   5 Environment pen   C_2    
   6 Environment poc   C_1    
   7 Environment tej   C_3    
   8 family1     bel   1.962 
   9 family1     buc   20.790
  10 family1     mal   0.000 
  </code></pre>
  <p>This is the expected format</p>
  <pre><code>  Family  site  Env  rpkm
    family1   bel  3   1.962      
    family1   buc  1   20.790 
    family1   mal  3   0.000  
  </code></pre>
  <p>I suppose that if the first row wasn't there I could do</p>
  <pre><code>output.txt %&gt;%
    pivot_longer(
      -Environment,
      names_to = c(&quot;.value&quot;, &quot;Env&quot;),
      names_sep = &quot;_&quot;)
  </code></pre>
  <p>In order to get a column 'Env' with the type of environment stored as a variable and then I would need 'another' conversion to long format.</p>
  <p>I imagine two successive pivot_longer rounds could fix this or using other strategy with <code>melt</code> (e.g. on site and type of site as first 2 rows). This is a long table so I wanted to avoid sorting or manually editing it.</p>
  <p><strong>This is the output of dput:</strong></p>
  <pre><code>structure(list(Sample = c(&quot;Environment&quot;, &quot;family01&quot;, &quot;family03&quot;, 
  &quot;family08&quot;, &quot;family13&quot;, &quot;family17&quot;, &quot;family21&quot;), bel = c(&quot;C__3&quot;, 
  &quot;1.962&quot;, &quot;0&quot;, &quot;4.482&quot;, &quot;0&quot;, &quot;0.462&quot;, &quot;0&quot;), buc = c(&quot;C_1&quot;, &quot;20.79&quot;, 
  &quot;3.15&quot;, &quot;12.603&quot;, &quot;1.697&quot;, &quot;10.689&quot;, &quot;0.41&quot;), mal = c(&quot;C_3&quot;, 
  &quot;0&quot;, &quot;0&quot;, &quot;0.168&quot;, &quot;0&quot;, &quot;0&quot;, &quot;0&quot;), man = c(&quot;C_3&quot;, &quot;0&quot;, &quot;0&quot;, &quot;0.46&quot;, 
  &quot;0&quot;, &quot;0&quot;, &quot;0&quot;), pen = c(&quot;C_2&quot;, &quot;0&quot;, &quot;152.614&quot;, &quot;0&quot;, &quot;169.841&quot;, 
  &quot;0&quot;, &quot;122.959&quot;), poc = c(&quot;C_1&quot;, &quot;0&quot;, &quot;0.089&quot;, &quot;2.917&quot;, &quot;0&quot;, &quot;1.387&quot;, 
  &quot;0&quot;), tej = c(&quot;C_3&quot;, &quot;1.962&quot;, &quot;0&quot;, &quot;4.482&quot;, &quot;0&quot;, &quot;0.462&quot;, &quot;0&quot;
  )), row.names = c(NA, -7L), class = c(&quot;tbl_df&quot;, &quot;tbl&quot;, &quot;data.frame&quot;
  ))
  </code></pre>
  <p><strong>addendum</strong> in case anyone wants to use the solution I had to use a workaround saving (xlsx or csv) and loading again the tibble and specifying that the values (rpkm) are numeric.</p>