title: Calculating monthly averages in R with a large dataset spanning several years
body: <p>I've looked around but haven't found a solution that quite matches my problem.</p>
  
  <p>I have a dataset (df1) with daily river flow readings, from 1945 to 1981 - so it's a very big dataset.</p>
  
  <p>I want to calculate the average flow for every month, each year. I can do this in Excel using Pivot Tables, but I'm wondering if it would be a lot less laborious in R.</p>
  
  <p>My problem is twofold. Firstly, I'm having trouble conversing the dates to an actual date format (despite trying recommended code), and secondly, once I have the date sorted, I'm not sure how to aggregate monthly averages for every year in the dataset. I'm fairly new to R - I've been looking up tutorials where possible but this particular problem does not seem to have an easily found solution.</p>
  
  <p>So, my data looks like this:</p>
  
  <pre><code>         X1    X2
  1 01/10/1945 0.835
  2 02/10/1945 0.835
  3 03/10/1945 0.835
  4 04/10/1945 0.835
  5 05/10/1945 0.835
  6 06/10/1945 0.433
  </code></pre>
  
  <p>The date is initially character format, so I'm using the code </p>
  
  <pre><code>df1$X1&lt;-as.Date(df1$X1,"%m/%d/%Y")
  </code></pre>
  
  <p>To try to convert it to date format, but for some reason it keeps mucking up, with several gaps in the dates:</p>
  
  <pre><code>8132  1968-05-01  4.163
  8133  1968-06-01  4.134
  8134  1968-07-01  1.464
  8135  1968-08-01  1.682
  8136  1968-09-01  1.036
  8137  1968-10-01  0.564
  8138  1968-11-01  0.575
  8139  1968-12-01  0.547
  8140        &lt;NA&gt; 10.590
  8141        &lt;NA&gt; 16.760
  8142        &lt;NA&gt;  3.879
  8143        &lt;NA&gt; 11.410
  </code></pre>
  
  <p>The dates display in sets of 12, so I'm assuming the problem is relating to some confusion with "months", but I've tried a few different combinations of d/m/Y and none of them are working. </p>
  
  <p>So to sum up,</p>
  
  <ul>
  <li>How can I convert data spanning multiple years into a date format without R becoming confused?</li>
  <li>Once the dates are sorted, how can I produce monthly averages when my data spans multiple years?</li>
  </ul>
  
  <p>Many thanks,</p>
  
  <p>I</p>