title: r - Pivot Longer distributing values into multiple columns
body: <p>There are quite a few questions already regarding the use of pivot longer and pivot wider to reshape data in the way that I need, but none quite get the answer right. I've successfully used a combination of pivot longer and pivot wider in the past to get the output that I need with differently shaped data, but the approach isn't quite working here.</p>
  <pre><code>library(tidyverse)
  sampleData &lt;- tribble(
    ~ID, ~ExamCode_01, ~ExamGrade_01, ~AdminYear_01, ~ExamCode_02, ~ExamGrade_02, ~AdminYear_02,
    123, 4, 4, 22, 26, 5, 22,
    456, 26, 3, 22, 83, 3, 21,
    789, 26, 5, 22, NA, NA, NA
  ) # In the actual data, these go up to 30 potential Exam Code/Grade/Year combinations
  
  ## Attempt to distribute column names/values using names_sep as informed by other StackOverflow posts. I've also wrestled with names_pattern regex but can't get anything more useful than this.
  sampleData_long_try1 &lt;- sampleData %&gt;%
    pivot_longer(cols = -1,
                 names_to = c(&quot;ExamCode&quot;, &quot;ExamGrade&quot;, &quot;AdminYear&quot;),
                 names_sep = &quot;_&quot;)
  
  ## This approach uses pivot longer/wider in combination to get closer, but the output won't preserve duplicate IDs, instead returning list-values in columns, which is not helpful
  sampleData_long_try2 &lt;- sampleData %&gt;%
    pivot_longer(cols = -ID,
                 names_to = &quot;type&quot;,
                 values_to = &quot;value&quot;,
                 values_drop_na = T)
  
  sampleData_final &lt;- sampleData_long_try2 %&gt;%
    mutate(type = str_replace_all(type, &quot;[:digit:]&quot;, &quot;&quot;)) %&gt;% # name repair so that it doesn't just  pivot wider back into the original format
    pivot_wider(names_from = &quot;type&quot;,
                values_from = &quot;value&quot;) # can't figure out how to preserve unique ID/Exam Code combinations rather than consolidating the data into one row per ID with list-cols
  
  ## The desired final output needs to have each subject's exam code, score, and administration year on one row, as shown below.
  desired_output &lt;- tribble(
    ~ID, ~ExamCode, ~ExamGrade, ~AdminYear,
    123, 4, 4, 22,
    123, 26, 5, 22,
    456, 26, 3, 22,
    456, 83, 3, 21,
    789, 26, 5, 22,
  )
  
  </code></pre>
  <p>If this question is indeed a duplicate, please point me in the right direction. The closest answers I've found:</p>
  <ul>
  <li><a href="https://stackoverflow.com/questions/68114090/pivot-longer-for-multiple-columns-of-repeated-measurements-data">Pivot_longer for multiple columns of repeated measurements data</a></li>
  <li><a href="https://stackoverflow.com/questions/65704911/pivot-wider-in-r-with-multiple-columns-but-keep-the-name-of-former-variables-in">pivot_wider in R with multiple columns, but keep the name of former variables in separate columns</a></li>
  </ul>
  <p>Thank you for any help you can provide!</p>