How can I resample a DataFrame so that it is properly aligned with another DataFrame?
<p>I've got several Pandas DataFrames at different time intervals. One is at the daily level:</p>

<pre><code>DatetimeIndex(['2007-12-01', '2007-12-02', '2007-12-03', '2007-12-04',
               '2007-12-05', '2007-12-06', '2007-12-07', '2007-12-08',
               '2007-12-09', '2007-12-10',
               ...
               '2016-08-22', '2016-08-23', '2016-08-24', '2016-08-25',
               '2016-08-26', '2016-08-27', '2016-08-28', '2016-08-29',
               '2016-08-30', '2016-08-31'],
              dtype='datetime64[ns]', length=3197, freq=None)
</code></pre>

<p>The others are at some non-daily level (they will <em>always</em> be less resolute than daily). For example, this one is weekly:</p>

<pre><code>DatetimeIndex(['2007-01-01', '2007-01-08', '2007-01-15', '2007-01-22',
               '2007-01-29', '2007-02-05', '2007-02-12', '2007-02-19',
               '2007-02-26', '2007-03-05',
               ...
               '2010-03-08', '2010-03-15', '2010-03-22', '2010-03-29',
               '2010-04-05', '2010-04-12', '2010-04-19', '2010-04-26',
               '2010-05-03',        'NaT'],
              dtype='datetime64[ns]', name='week', length=176, freq=None)
</code></pre>

<p>This one is monthly:</p>

<pre><code>DatetimeIndex(['2013-04-01', '2013-05-01', '2013-06-01', '2013-07-01',
               '2013-08-01', '2013-09-01', '2013-10-01', '2013-11-01',
               '2013-12-01', '2014-01-01', '2014-02-01', '2014-03-01',
               '2014-04-01', '2014-05-01', '2014-06-01', '2014-07-01',
               '2014-08-01', '2014-09-01', '2014-10-01', '2014-11-01',
               '2014-12-01', '2015-01-01', '2015-02-01', '2015-03-01',
               '2015-04-01', '2015-05-01', '2015-06-01', '2015-07-01',
               '2015-08-01', '2015-09-01', '2015-10-01', '2015-11-01',
               '2015-12-01', '2016-01-01', '2016-02-01', '2016-03-01',
               '2016-04-01', '2016-05-01', '2016-06-01', '2016-07-01',
               '2016-08-01'],
              dtype='datetime64[ns]', name='month', freq=None)
</code></pre>

<p>This is just an oddball with an irregular interval:</p>

<pre><code>DatetimeIndex(['2014-02-14', '2014-05-08', '2014-09-19', '2014-09-24',
               '2015-01-21', '2016-05-26', '2016-06-02', '2016-06-04'],
              dtype='datetime64[ns]', name='date', freq=None)
</code></pre>

<p>What I need to do is resample (sum) the daily data to the intervals specified by the others. So if a DatetimeIndex is monthly, I need to resample the daily data to monthly. If it's weekly, it should be resampled weekly. If it's irregular, it needs to match. I need this because I'm building statistical models on these data, and I need the ground truth to line up with the observed values.</p>

<p>How can I get Pandas to resample a DataFrame, <code>df1</code>, to match the DatetimeIndex of another arbitrary DataFrame, <code>df2</code>? I've search around, but I can't figure this out. It seems like it'd be a really common Pandas task, so I must just be missing something. Thanks!</p>

<p>Consider using pandas <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.resample.html" rel="nofollow">DataFrame.resample()</a>:</p>

<pre><code># EXAMPLE DATA OF SEQUENTIAL DATES AND RANDOM NUMBERS
index = pd.date_range('12/01/2007', periods=3197, freq='D', dtype='datetime64[ns]')
series = pd.Series(np.random.randint(0,100, 3197), index=index)
df = pd.DataFrame({'num':series})
#             num
# 2007-12-01   73
# 2007-12-02   17
# 2007-12-03   63
# 2007-12-04   72
# 2007-12-05    4
# 2007-12-06   91
# 2007-12-07   20
# 2007-12-08   99
# 2007-12-09   97
# 2007-12-10   33

wdf = df.resample('W-SAT').sum()        # SATURDAY WEEK START
#             num
# 2007-12-01   73
# 2007-12-08  366
# 2007-12-15  354
# 2007-12-22  302
# 2007-12-29  310
# 2008-01-05  323
# 2008-01-12  424

mdf = df.resample('MS').sum()           # MONTH START
#              num
# 2007-12-01  1568
# 2008-01-01  1465
# 2008-02-01  1317
# 2008-03-01  1473
# 2008-04-01  1762
# 2008-05-01  1698
# 2008-06-01  1345
</code></pre>

<p>For the irregular interval, use a custom function in <code>DataFrame.apply()</code> to create a <em>enddate</em> column which would be the end cut-off date of the interval the current row's date falls in series (i.e., <em>2015-01-01</em>'s end date being <em>2015-01-21</em> in Datetimeindex series), calculated by using a series filter. Then, run a <code>groupby()</code> on new <em>enddate</em> column for sum aggregation:</p>

<pre><code>irrdt = pd.DatetimeIndex(['2014-02-14', '2014-05-08', '2014-09-19', '2014-09-24',
                          '2015-01-21', '2016-05-26', '2016-06-02', '2016-06-04'],
                           dtype='datetime64[ns]', name='date', freq=None)    
def findrng(row):                      
    ed = str(irrdt[irrdt &gt; row['Date']].min())[0:10]
    row['enddt'] = ed if ed !='NaT' else str(irrdt.max())[0:10]
    return(row)

df['Date'] = df.index
df = df.apply(findrng, axis=1).groupby(['enddt']).sum()    
#                num
# enddt             
# 2014-02-14  112143
# 2014-05-08    3704
# 2014-09-19    5958
# 2014-09-24     365
# 2015-01-21    5730
# 2016-05-26   24126
# 2016-06-02     305
# 2016-06-04    4142
</code></pre>

python python-3.x pandas