title: Pivot data from wide to long using column suffix to get table with multiple columns with values (using pivot_longer)
body: <p>I have a tibble/dataframe that looks like this:</p>
  
  <pre><code>hc_inpatient_sum hc_ambulant_sum hc_inpatient_mean hc_ambulant_mean
  5                2               5.5              2.2
  </code></pre>
  
  <p>My desired output is:</p>
  
  <pre><code>my_names       sum  mean
  hc_inpatient     5   5.5
  hc_ambulant      2   2.2
  </code></pre>
  
  <p>I get what I want using the following code. However, it seems pretty complicated. I guess that the same result could be obtained using less complicated code.</p>
  
  <pre><code>library(dplyr)
  library(tidyr)
  
  my_data &lt;- tibble(hc_inpatient_sum = 5, hc_ambulant_sum = 2, hc_inpatient_mean = 5.5, 
  hc_ambulant_mean = 2.2)
  
  res &lt;- my_data %&gt;% 
    pivot_longer(cols = everything(), names_to = "my_names", values_to = "my_values") %&gt;% 
    separate(my_names, into = c("my_names", "stats"), sep = "_(?=[^_]+$)") %&gt;% 
    pivot_wider(names_from = "stats", values_from = "my_values")
  </code></pre>
  
  <p>Is there a more direct way to get the same result using tidyr::pivot_longer?</p>
  
  <p>Alternatively I could do something like this...</p>
  
  <pre><code>res2 &lt;- pivot_longer(my_data, cols = everything(),
               names_to = c(".value", "stats"), 
               names_pattern = "(.*)_(.*)") %&gt;% 
    t()
  
  colnames(res2) &lt;- res2["stats",]
  
  res2 &lt;- as_tibble(res2[-1,], rownames = "my_names") %&gt;% 
    mutate_at(vars(-my_names), as.double)
  </code></pre>
  
  <p>... but that is even more awkward.</p>