How to group pandas DataFrame by varying dates?
<p>I am trying to roll up daily data into fiscal quarter data.  For example, I have a table with fiscal quarter end dates:</p>

<pre><code>Company Period Quarter_End
M       2016Q1 05/02/2015
M       2016Q2 08/01/2015
M       2016Q3 10/31/2015
M       2016Q4 01/30/2016
WFM     2015Q2 04/12/2015
WFM     2015Q3 07/05/2015 
WFM     2015Q4 09/27/2015
WFM     2016Q1 01/17/2016
</code></pre>

<p>and a table of daily data:</p>

<pre><code>Company Date       Price
M       06/20/2015 1.05
M       06/22/2015 4.05
M       07/10/2015 3.45
M       07/29/2015 1.86
M       08/24/2015 1.58
M       09/02/2015 8.64
M       09/22/2015 2.56
M       10/20/2015 5.42
M       11/02/2015 1.58
M       11/24/2015 4.58
M       12/03/2015 6.48
M       12/05/2015 4.56
M       01/03/2016 7.14
M       01/30/2016 6.34
WFM     06/20/2015 1.05
WFM     06/22/2015 4.05
WFM     07/10/2015 3.45
WFM     07/29/2015 1.86
WFM     08/24/2015 1.58
WFM     09/02/2015 8.64
WFM     09/22/2015 2.56
WFM     10/20/2015 5.42
WFM     11/02/2015 1.58
WFM     11/24/2015 4.58
WFM     12/03/2015 6.48
WFM     12/05/2015 4.56
WFM     01/03/2016 7.14
WFM     01/17/2016 6.34
</code></pre>

<p>And I would like to create the table below.</p>

<pre><code>Company Period  Quarter_end Sum(Price)
M       2016Q2  8/1/2015    10.41
M       2016Q3  10/31/2015  18.2
M       2016Q4  1/30/2016   30.68
WFM     2015Q3  7/5/2015    5.1
WFM     2015Q4  9/27/2015   18.09
WFM     2016Q1  1/17/2016   36.1
</code></pre>

<p>However, I don't know how to group by varying dates without looping through each record. Any help is greatly appreciated.</p>

<p>Thanks!</p>

<ul>
<li><code>set_index</code></li>
<li><code>pd.concat</code> to align indices</li>
<li><code>groupby</code> with <code>agg</code></li>
</ul>

<hr>

<pre><code>prd_df = period_df.set_index(['Company', 'Quarter_End'])

prc_df = price_df.set_index(['Company', 'Date'], drop=False)

df = pd.concat([prd_df, prc_df], axis=1)

df.groupby([df.index.get_level_values(0), df.Period.bfill()])  \
  .agg(dict(Date='last', Price='sum')).dropna()
</code></pre>

<p><a href="https://i.stack.imgur.com/EuJ86.png" rel="nofollow"><img src="https://i.stack.imgur.com/EuJ86.png" alt="enter image description here"></a></p>

python pandas numpy