title: How to reshape complicated elections data using tidyverse?
body: <p>I need to reshape a complicated table from rows of stacked election data to cleanly formatted columns containing all the information. I'm having trouble automating this.</p>
  <p>Here's a simple version of the input data. Note that there are just 2 elections in this example; in the real data there are many, so the code needs to generalize:</p>
  <pre><code>input &lt;- 
    structure(list(a = c(&quot;2020 ge&quot;, &quot;winner&quot;, NA, &quot;2016 ge&quot;, &quot;winner&quot;
  ), b = c(NA, &quot;orange (cat)&quot;, NA, NA, &quot;peach (kitten)&quot;), c = c(NA, 
  &quot;runner up&quot;, NA, NA, &quot;runner up&quot;), d = c(NA, &quot;peach (kitten)&quot;, NA, 
  NA, &quot;orange (cat)&quot;), e = c(NA, &quot;margin&quot;, NA, NA, &quot;margin&quot;), f = c(NA, 
  100, NA, NA, 150)), row.names = c(NA, 5L), class = &quot;data.frame&quot;)
  
  </code></pre>
  <p>And this is the output I would like:</p>
  <pre><code>output &lt;- 
    structure(list(`2019_winner_name` = &quot;orange&quot;, `2020_winner_party` = &quot;cat&quot;, 
      `2020_runner_up_name` = &quot;peach&quot;, `2020_runner_up_party` = &quot;kitten&quot;, 
      `2020_margin` = 100, `2016_winner_name` = &quot;peach&quot;, `2016_winner_party` = &quot;kitten&quot;, 
      `2016_runner_up_name` = &quot;orange&quot;, `2016_runner_up_party` = &quot;cat&quot;, 
      `2016_margin` = 150), row.names = 1L, class = &quot;data.frame&quot;)
  </code></pre>
  <p>Here is what I've tried so far, which works for one year:</p>
  <pre><code># test data
  test &lt;-
    input %&gt;%
    slice(1:2) %&gt;%
    fill(c(b, c, d, e, f), .direction = c(&quot;up&quot;))
  
  # select first row
  row_one &lt;-
    test %&gt;%
    select(a) %&gt;%
    slice(1)
  
  # select year
  year  &lt;- 
    str_extract(row_one$a, &quot;^([0-9]*)&quot;)
  
  # select second row as name
  row_two &lt;-
    test %&gt;%
    select(a) %&gt;%
    slice(2) %&gt;%
    as.character()
  
  # bring back to test data
  test &lt;- 
    test %&gt;%
    mutate(a  = row_two) %&gt;%
    slice(1) %&gt;%
    add_row() %&gt;%
    fill(c(b, d, f)) %&gt;%
    mutate(a = ifelse(is.na(a), b, a),
           c = ifelse(is.na(c), d, c),
           e = ifelse(is.na(e), f, e)) %&gt;%
    select(a, c, e) %&gt;%
    row_to_names(1) %&gt;%
    rename_all(funs(paste0(year, &quot;_&quot;, .)))
  
  # extract party variable
  test &lt;- 
    test %&gt;%
    mutate_at(vars(contains(&quot;winner&quot;), contains(&quot;runner&quot;)), 
              funs(party = str_extract(., &quot;(?&lt;=\\().+?(?=\\))&quot;))) %&gt;%
    mutate_at(vars(ends_with(&quot;winner&quot;), ends_with(&quot;up&quot;)), 
              funs(name = str_extract(., &quot;([^()]*)&quot;)))
  </code></pre>
  <p>What would be an easier and more concise way to do this, given the unusual data format? How could I automate this so that I can run it over multiple election years?</p>
  <p>Thank you.</p>