title: How to aggregate and average various rows based on multiple groups while keeping other columns intact
body: <p>I have spent hours trying to figure out how to do this but I am not sure what the best approach would be. I have species and environmental data with 2 replicates per site (for each particular day and month) and would like to combine data collected at the 'S' and 'E' tows for each particular site, month, and day. I am doing some analyses and want to combine the 'S' and 'E' tows so there is only one row of data for both tows in each site (by day and month). I am not sure how to verbally explain this very well so I will try to show an example to better explain myself.</p>
  <p>Here is a shortened version of my data:</p>
  <pre><code>structure(list(month = c(&quot;11&quot;, &quot;11&quot;, &quot;11&quot;, &quot;11&quot;, &quot;11&quot;, &quot;11&quot;, 
  &quot;7&quot;, &quot;7&quot;, &quot;7&quot;, &quot;7&quot;, &quot;7&quot;, &quot;7&quot;, &quot;8&quot;, &quot;8&quot;, &quot;8&quot;, &quot;8&quot;), day = c(&quot;4&quot;, 
  &quot;4&quot;, &quot;4&quot;, &quot;4&quot;, &quot;5&quot;, &quot;5&quot;, &quot;20&quot;, &quot;20&quot;, &quot;27&quot;, &quot;27&quot;, &quot;27&quot;, &quot;27&quot;, 
  &quot;16&quot;, &quot;16&quot;, &quot;16&quot;, &quot;16&quot;), Site = c(11L, 11L, 6L, 6L, 9L, 9L, 10L, 
  10L, 13L, 13L, 2L, 3L, 4L, 5L, 5L, 6L), Tow = c(&quot;E&quot;, &quot;S&quot;, &quot;E&quot;, 
  &quot;S&quot;, &quot;E&quot;, &quot;S&quot;, &quot;E&quot;, &quot;S&quot;, &quot;E&quot;, &quot;S&quot;, &quot;S&quot;, &quot;S&quot;, &quot;S&quot;, &quot;E&quot;, &quot;S&quot;, &quot;E&quot;
  ), Depth = c(10L, 11L, 22L, 22L, 12L, 13L, 13L, 13L, 19L, 19L, 
  14L, 21L, 22L, 22L, 22L, 22L), Temp = c(12.75, 12.9, 14.25, 14.239, 
  12.975, 12.955, 23.804, 23.804, 23.89, 23.9, 24.41, 24.04, 23.915, 
  23.988, 24.021, 23.957), DO_mgL = c(10.54, 10.45, 10.16, 10.12, 
  10.4, 10.39, 7.24, 7.11, 8.07, 8.1, 9.14, 1.29, 2.44, 2.45, 2.48, 
  2.54), secchi = c(1.25, 1.25, 2.25, 2.25, 1.5, 1.5, 2.7, 2.7, 
  2.1, 2.1, 2.75, 1.25, 2.8, 3, 3, 3.25), d.lept = c(0, 0, 0, 0, 
  0, 0, 0.008037479, 0.155240934, 0.128494423, 0.025249815, 0.053921767, 
  0.012391113, 0.069338871, 0.022259485, 0.013767903, 0.046661095
  ), d.byths = c(0, 0, 0, 0, 0, 0, 0, 0.007392425, 0, 0, 0, 0, 
  0, 0.044518969, 0.013767903, 0.015553698), d.daph = c(0.140036552, 
  1.010093452, 1.629907953, 2.762608821, 1.130338642, 1.311853781, 
  0.031419235, 0.029569702, 0.0525659, 0.084166051, 0.024509894, 
  0.049564452, 0.104008307, 0.133556908, 0.082607421, 0.062214794
  )), row.names = c(1L, 2L, 3L, 4L, 21L, 22L, 23L, 24L, 33L, 34L, 
  35L, 36L, 58L, 59L, 60L, 61L), class = &quot;data.frame&quot;)
  </code></pre>
  <p>For example, my first two rows (month == 11, day == 4, Site == 11) should end up as 1 row with the 'Temp' and 'DO_mgL' of the two rows averaged, the 'secchi' readings are always the same for the 'S' and 'E' tows so that should stay the same and the species densities should be summed(added). The tow column can be removed after this is done. I want to end up with something like this (just showing example of what the first two rows should end up like).</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>month</th>
  <th>day</th>
  <th>Site</th>
  <th>Depth</th>
  <th>Temp</th>
  <th>DO_mgL</th>
  <th>secchi</th>
  <th>d.lept</th>
  <th>d.byths</th>
  <th>d.daph</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>11</td>
  <td>4</td>
  <td>11</td>
  <td>10.5</td>
  <td>12.83</td>
  <td>10.50</td>
  <td>1.25</td>
  <td>0</td>
  <td>0</td>
  <td>1.15013</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>To be honest, I am not even sure where to begin to achieve this. Doing the following achieves somewhat what I want for my species but this only does it for one species at a time (I have a total of 8 species, shortened in this example) and deletes other columns:</p>
  <pre><code>aggregate(d.lept ~ month + day + Site, data=zp1, FUN = sum)
  </code></pre>
  <p>Again, I need the 'S' and 'E' tows to be treated as one collection by:</p>
  <ul>
  <li>having the 'Temp' and 'DO_mgL' averaged between the 'S' and 'E' tows for each site, day and month</li>
  <li>keeping 'secchi' as is since the value is the same for each 'S' and 'E' combo</li>
  <li>adding the species densities between the 'S' and 'E' tows for each site, day, and month</li>
  </ul>
  <p>To further complicate things, due to time/weather constraints in the field sometimes we were not able to collect replicates so some sites only have data with 'S' tows, those should be left intact since there is only one row for those particular sites/day/months.</p>
  <p>My entire dataset has 97 rows and 16 columns. I have a total of 24 sites that were sampled in July, August, and September. I have 8 species with their associated densities (derived from counts).</p>
  <p>I looked at the following posts concerning the aggregate portion of my question, but they did not quite help me: <a href="https://stackoverflow.com/questions/45665168/aggregate-values-by-levels-of-another-factor-but-keep-all-rows-in-r">here</a>, <a href="https://stackoverflow.com/questions/47418127/r-how-to-aggregate-some-columns-while-keeping-other-columns">here</a>, and <a href="https://stackoverflow.com/questions/69577853/how-to-aggregate-data-based-on-consecutive-row-values">here</a>.</p>
  <p>I hope this is clear and makes sense, but I am happy to provide further clarification. Thank you for your time.</p>