title: How to reshape data with summary statistics?
body: <p>I'm conducting a survey and would like to reshape my data to provide specific summary statistics on responses to a given question by a given group <em>without</em> manually creating fields. (The number of questions I'll be handling would make this prohibitive.)</p>
  
  <p>Currently, this is what I have:</p>
  
  <pre><code>library(reshape2)
  
  test.0 &lt;- read.csv("test.csv")
  
  test.1 &lt;- dcast(
      test.0,
      school+year~questionnaire+question+answer+answer.code
      )
  
  test.2 &lt;- melt.data.frame(
      test.1,
      id.vars = c(school, year),
      variable.names = c("questionnaire", "question", "answer", "answer.code"),
      factorsAsStrings = TRUE
      )
  </code></pre>
  
  <p>The <code>dcast</code> function will reshape the data into columns, but I can't figure out what to add to get it to do calculations and add columns automatically. (I was thinking I could <code>melt</code> the data frame, run calculations, and recast it, but I'm having trouble melting.</p>
  
  <p>For each question, I would like to calculate (short hand, not code):</p>
  
  <ol>
  <li>*The number of responses with each answer option (count if school = a, year = b, question = c, answer = d)</li>
  <li>The total number of responses (sum answer != "DNA")</li>
  <li>*The percent of responses with each answer option (count if answer = d / sum answer != "DNA)</li>
  <li>The percent response rate (sum answer != c("NULL", "DNA") / sum answer != c("DNA"))</li>
  <li>The average response as a score (sum question.code / count answer != c("NULL", "DNA"))</li>
  </ol>
  
  <p>*May require multiple columns, one for each response option</p>
  
  <p>Here is a sample data set (NULL = Student did not answer, DNA = I did not ask):</p>
  
  <pre><code>studentid    year    school      questionnaire   question    answer      answer-code 
  517202  2018     High School    1    Do you like ice cream?      Yes    5
  553908  2017     High School    1    Do you like ice cream?      No     1
  424835  2019     High School    1    Do you like ice cream?      Yes    5
  471321  2024     Middle School  1    Do you like ice cream?      No     1
  458237  2021     Middle School  1    Do you like ice cream?      No     1
  300763  2024     Middle School  1    Do you like ice cream?      NULL    NULL 
  173314  2018     High School    1    Do you like ice cream?      NULL    NULL 
  924930  2023     Middle School  1    Do you like ice cream?      Yes    5
  902908  2019     High School    1    Do you like ice cream?      No     1
  227533  2018     High School    1    Do you like ice cream?      No     1
  517202  2018     High School    1    Do you like ice cream sundaes?      Yes    5
  553908  2017     High School    1    Do you like ice cream sundaes?      No     1
  424835  2019     High School    1    Do you like ice cream sundaes?      Yes    5
  471321  2024     Middle School  1    Do you like ice cream sundaes?      No     1
  458237  2021     Middle School  1    Do you like ice cream sundaes?      No     1
  300763  2024     Middle School  1    Do you like ice cream sundaes?      DNA     DNA 
  173314  2018     High School    1    Do you like ice cream sundaes?      DNA     DNA 
  924930  2023     Middle School  1    Do you like ice cream sundaes?      Yes    5
  902908  2019     High School    1    Do you like ice cream sundaes?      NULL    NULL 
  227533  2018     High School    1    Do you like ice cream sundaes?      No     1
  517202  2018     High School    2    The ice cream party made me like ice cream more.    Neither Agree nor Disagree     3
  553908  2017     High School    2    The ice cream party made me like ice cream more.    Agree  4
  424835  2019     High School    2    The ice cream party made me like ice cream more.    Disagree   2
  471321  2024     Middle School  2    The ice cream party made me like ice cream more.    Strongly Agree     5
  458237  2021     Middle School  2    The ice cream party made me like ice cream more.    Disagree   2
  300763  2024     Middle School  2    The ice cream party made me like ice cream more.    Agree  3
  173314  2018     High School    2    The ice cream party made me like ice cream more.    Strongly Disagree  1
  924930  2023     Middle School  2    The ice cream party made me like ice cream more.    Neither Agree nor Disagree     3
  902908  2019     High School    2    The ice cream party made me like ice cream more.    Agree  4
  227533  2018     High School    2    The ice cream party made me like ice cream more.    NULL    NULL 
  </code></pre>
  
  <p>Here is the data frame I would like to end up with (except repeating columns 2-8 for the other questions in columns 9+):</p>
  
  <pre><code>school | year | 1_Do you like ice cream?_#Yes | 1_Do you like ice cream?_#No | 1_Do you like ice cream?_#NULL | 1_Do you like ice cream?_#Total | 1_Do you like ice cream?_%Yes | 1_Do you like ice cream?_%No | 1_Do you like ice cream?_Response Rate | 1_Do you like ice cream?_Ice Cream Score**
  High School | 2017 | 0 | 1 | 0 | 1 | 0 | 1 | 1 | 1
  High School | 2018 | 1 | 1 | 1 | 3 | 0.333333333 | 0.333333333 | 0.666666667 | 2
  High School | 2019 | 1 | 1 | 0 | 2 | 0.5 | 0.5 | 1 | 3
  High School | 2020 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
  Middle School | 2021 | 0 | 1 | 0 | 1 | 0 | 1 | 1 | 1 
  Middle School | 2022 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 
  Middle School | 2023 | 1 | 0 | 0 | 1 | 1 | 0 | 1 | 5 
  Middle School | 2024 | 0 | 1 | 1 | 2 | 0 | 0.5 | 0.5 | 1 
  </code></pre>
  
  <p>Is there a simpler solution? Am I at least on the right track? Any guidance would be super appreciated!</p>