title: aggregate data by quarter
body: <p>I have a pivot pandas data frame (sales by region) that got created from another pandas data frame (sales by store) using the pivot_table method.  </p>
  
  <p>As an example:</p>
  
  <pre><code>df = pd.DataFrame(
      {'store':['A','B','C','D','E']*7, 
       'region':['NW','NW','SW','NE','NE']*7, 
       'date':['2017-03-30']*5+['2017-04-05']*5+['2017-04-07']*5+['2017-04-12']*5+['2017-04-13']*5+['2017-04-17']*5+['2017-04-20']*5,
       'sales':[30,1,133,9,1,30,3,135,9,11,30,1,140,15,15,25,10,137,9,3,29,10,137,9,11,30,19,145,20,10,30,8,141,25,25]
       })
  df['date'] = pd.to_datetime(df['date'])
  df_sales = df.pivot_table(index = ['region'], columns = ['date'], aggfunc = [np.sum], margins = True)
  df_sales = df_sales.ix[:,range(0, df_sales.shape[1]-1)]
  </code></pre>
  
  <p>My goal is to do the following to the sales data frame, df_sales.</p>
  
  <p>Create a new dataframe that summarizes sales by quarter.  I could use the original dataframe df, or the sales_df.</p>
  
  <p>As of quarter here we only have only two quarters (<a href="https://en.wikipedia.org/wiki/Fiscal_year" rel="nofollow noreferrer">USA fiscal calendar year</a>) so the quarterly aggregated data frame would look like:</p>
  
  <pre><code>2017Q1  2017Q2
  10      27
  31      37.5
  133     139.17
  </code></pre>
  
  <p>I take the average for all days in Q1, and same for Q2.  Thus, for example for the North east region, <code>'NE'</code>, the Q1 is the average of only one day 2017-03-30, i.e., 10, and for the Q2 is the average across 2017-04-05 to 2017-04-20, i.e., </p>
  
  <pre><code>(20+30+12+20+30+50)/6=27
  </code></pre>
  
  <p>Any suggestions?</p>
  
  <p>ADDITIONAL NOTE: I would ideally do the quarter aggregations on the df_sales pivoted table since it's a much smaller dataframe to keep in memory.  The current solution does it on the original df, but I am still seeking a way to do it in the df_sales dataframe.</p>