title: Group data in csv by season and year using python and pandas
body: <p>This is an extension of an <a href="https://stackoverflow.com/questions/22615288/group-data-by-seasons-using-python-and-pandas/22623876?noredirect=1#22623876">earlier question</a>.</p>
  
  <p>I want to use Pandas and Python to iterate through my .csv file and group the data by season (and year) calculating the mean for each season in the year. Currently the quarterly script does Jan-Mar, Apr-Jun etc. I want the seasons correlate to months by </p>
  
  <p><code>- 11: 'Winter', 12: 'Winter', 1: 'Winter', 2: 'Spring', 3: 'Spring', 4: 'Spring', 5: 'Summer', 6: 'Summer', 7: 'Summer', \
  8: 'Autumn', 9: 'Autumn', 10: 'Autumn'</code></p>
  
  <p>I have the following data:</p>
  
  <pre><code>Date,HAD
  01/01/1951,1
  02/01/1951,-0.13161201
  03/01/1951,-0.271796132
  04/01/1951,-0.258977158
  05/01/1951,-0.198823057
  06/01/1951,0.167794502
  07/01/1951,0.046093808
  08/01/1951,-0.122396694
  09/01/1951,-0.121824587
  10/01/1951,-0.013002463
  </code></pre>
  
  <p>...</p>
  
  <p>all the way up to </p>
  
  <pre><code>20/12/2098,62.817
  21/12/2098,59.998
  22/12/2098,50.871
  23/12/2098,88.405
  24/12/2098,81.154
  25/12/2098,83.617
  26/12/2098,120.675
  27/12/2098,273.795
  28/12/2098,316.324
  29/12/2098,260.951
  30/12/2098,198.505
  31/12/2098,150.755
  </code></pre>
  
  <p>This is the code from the earlier question which works</p>
  
  <pre><code>import pandas as pd
  import os
  import re
  
  lookup = {
      11: 'Winter',
      12: 'Winter',
      1: 'Winter',
      2: 'Spring',
      3: 'Spring',
      4: 'Spring',
      5: 'Summer',
      6: 'Summer',
      7: 'Summer',
      8: 'Autumn',
      9: 'Autumn',
      10: 'Autumn'
  }
  
  os.chdir('C:/Users/n-jones/testdir/output/')
  
  for fname in os.listdir('.'):
      if re.match(".*csv$", fname):
          data = pd.read_csv(fname, parse_dates=[0], dayfirst=True)
          data['Season'] = data['Date'].apply(lambda x: lookup[x.month])
          data['count'] = 1
          data = data.groupby(['Season'])['HAD', 'count'].sum()
          data['mean'] = data['HAD'] / data['count']
          data.to_csv('C:/Users/n-jones/testdir/season/' + fname)
  </code></pre>
  
  <p>I want my output csv file to be:</p>
  
  <pre><code>Autumn 1951, Mean, Winter 1951/52, Mean, Spring 1952, Mean, Summer 1952, Mean,
  Autumn 1952, Mean, Winter 1952/53, Mean, Spring 1953, Mean, Summer 1953, Mean,
  </code></pre>
  
  <p>and so on...</p>
  
  <p>I hope this makes some sense.</p>
  
  <p>Thank you in advance!</p>