title: Melting columns by pattern matching
body: <p>I have a very wide data frame that contains standard demographic characteristics (age, sex, race, education, income, and so on).  I also have respondent's answers to questions which can be prefaced in one of four ways (with "cb", "lb", "lw", or "cw").  </p>
  
  <p>The dataframe is currently in wide format with each row representing the answers of a single respondent.  I would like to convert this into long format, but I cannot find a straightforward solution using the reshape2 library.</p>
  
  <p>I would like to preserve all the demographic characteristics as their own columns, but reduce question, answer, confidence, and score columns into their own melted columns.  Here is an idea of what I'm looking for:</p>
  
  <pre><code>string &lt;- "
  response_id,age,sex,race_1,race_2,race_3,cb_1,cb_1_conf,cb_1_ans,cb_1_score,lb_1,lb_1_conf,lb_1_ans,lb_1_score
  11,25,M,white,NA,NA,Astrophysicist,9,Dog,0,Jackson,8,Jackson,1
  22,27,F,NA,black,asian,Monkey,8,Dog,0,Jackson,7,Jackson,1"
  
  x &lt;- read.csv(con &lt;- textConnection(string), header=TRUE)
  </code></pre>
  
  <p>Looks like this:</p>
  
  <pre><code>&gt; x
    response_id age sex race_1 race_2 race_3           cb_1 cb_1_conf cb_1_ans cb_1_score    lb_1 lb_1_conf lb_1_ans lb_1_score
  1          11  25   M  white   &lt;NA&gt;     NA Astrophysicist         9      Dog          0 Jackson         8  Jackson          1
  2          22  27   F   &lt;NA&gt;  black     NA         Monkey         8      Dog          0 Jackson         7  Jackson          1
  </code></pre>
  
  <p>Looking to convert it into this form:</p>
  
  <pre><code>string_2 &lt;- "
  response_id,age,sex,race,question,response,confidence,correct_answer,score
  11,25,M,white,cb_1,Astrophysicist,9,Dog,0
  11,25,M,white,lb_1,Jackson,8,Jackson,1
  22,27,F,black/asian,cb_1,Monkey,8,Dog,0
  22,27,F,black/asian,lb_1,Jackson,8,Jackson,1
  "
  
  x_2 &lt;- read.csv(con &lt;- textConnection(string_2), header=TRUE)
  
    response_id age sex        race question       response confidence correct_answer score
  1          11  25   M       white     cb_1 Astrophysicist          9            Dog     0
  2          11  25   M       white     lb_1        Jackson          8        Jackson     1
  3          22  27   F black/asian     cb_1         Monkey          8            Dog     0
  4          22  27   F black/asian     lb_1        Jackson          8        Jackson     1
  </code></pre>
  
  <p>I tried subsetting the df to contain only columns that are prefixed with cb, lb, cw, or lw, followed by this:</p>
  
  <pre><code>    melt(subset, id=c("ResponseID"), 
      +      measure.vars=grep("^(CB|LB|LW|CW)", colnames(subset)))
  
  But this doesn't allow me to flexibly melt the _conf columns the _ans columns and the _score columns.  
  
  I had to modify Maurits' answer a bit to work better for my case.  Here is my solution:
  
  df_test &lt;- df_ans %&gt;%
      unite(race, contains("race"), sep = "/") %&gt;%                # combine race_1,2,3
      mutate(race = str_replace_all(race, "(/NA|NA/)", "")) %&gt;%   # replace NA from race
      select_all( ~ gsub("(^[A-Z][A-Z]_\\d+$)", "\\1_response", .)) %&gt;%  # add "_response" to Q
      gather(key, val, -(1:24)) %&gt;%                                # wide to long
      separate(key, c("q1", "q2", "item")) %&gt;%                    # split into Q + item
      unite(question, q1, q2, sep = "_") %&gt;%                      # [continued]
      mutate(item = gsub("_", "", item)) %&gt;%                      # [continued]
      spread(item, val) %&gt;%                                       # long to wide
      rename(answer = ans, confidence = con)                      # rename columns
  </code></pre>