Using .asof and MultiIndex in Pandas
<p>I've seen this question asked a few times but with no answer.  The short version:</p>

<p>I have a pandas <code>DataFrame</code> with a two-level <code>MultiIndex</code> index; both levels are integers.  How can I use <code>.asof()</code> on this <code>DataFrame</code>?</p>

<p>Long version:</p>

<p>I have a <code>DataFrame</code> with some time series data:</p>

<pre><code>&gt;&gt;&gt; df
                            A
2016-01-01 00:00:00  1.560878
2016-01-01 01:00:00 -1.029380
...                       ...
2016-01-30 20:00:00  0.429422
2016-01-30 21:00:00 -0.182349
2016-01-30 22:00:00 -0.939461
2016-01-30 23:00:00  0.009930
2016-01-31 00:00:00 -0.854283

[721 rows x 1 columns]
</code></pre>

<p>I'm then constructing a weekly model of that data:</p>

<pre><code>&gt;&gt;&gt; df['weekday'] = df.index.weekday
&gt;&gt;&gt; df['hour_of_day'] = df.index.hour
&gt;&gt;&gt; weekly_model = df.groupby(['weekday', 'hour_of_day']).mean()
&gt;&gt;&gt; weekly_model
                            A
weekday hour_of_day          
0       0            0.260597
        1            0.333094
...                       ...
        20           0.388932
        21          -0.082020
        22          -0.346888
        23           1.525928
[168 rows x 1 columns]
</code></pre>

<p>That's what gives me a <code>DataFrame</code> with the index described above.</p>

<p>I'm now trying to extrapolate that model into an annual time series:</p>

<pre><code>&gt;&gt;&gt; dates = pd.date_range('2015/1/1', '2015/12/31 23:59', freq='H')
&gt;&gt;&gt; annual_series = weekly
weekly        weekly_model  
&gt;&gt;&gt; annual_series = weekly_model.A.asof((dates.weekday, dates.hour))
Traceback (most recent call last):
  File "&lt;stdin&gt;", line 1, in &lt;module&gt;
  File "/home/tkcook/azimuth-web/lib/python3.5/site-packages/pandas/core/series.py", line 2657, in asof
    locs = self.index.asof_locs(where, notnull(values))
  File "/home/tkcook/azimuth-web/lib/python3.5/site-packages/pandas/indexes/base.py", line 1553, in asof_locs
    locs = self.values[mask].searchsorted(where.values, side='right')
ValueError: operands could not be broadcast together with shapes (8760,) (2,) 
&gt;&gt;&gt; dates = pd.date_range('2015/1/1', '2015/12/31 23:59', freq='H')
&gt;&gt;&gt; annual_series = weekly_model.A.asof((dates.weekday, dates.hour))
Traceback (most recent call last):
  File "&lt;stdin&gt;", line 1, in &lt;module&gt;
  File "/home/tkcook/azimuth-web/lib/python3.5/site-packages/pandas/core/series.py", line 2657, in asof
    locs = self.index.asof_locs(where, notnull(values))
  File "/home/tkcook/azimuth-web/lib/python3.5/site-packages/pandas/indexes/base.py", line 1553, in asof_locs
    locs = self.values[mask].searchsorted(where.values, side='right')
ValueError: operands could not be broadcast together with shapes (8760,) (2,) 
</code></pre>

<p>What does this error mean, and what's the best way of doing this?</p>

<p>The best I've come up with so far is this:</p>

<pre><code>&gt;&gt;&gt; annual_series = weekly_model.A.loc[list(zip(dates.weekday, dates.hour))]
</code></pre>

<p>It works, but it means turning the <code>zip</code> iterator into a list first, which is not exactly memory-friendly.  Is there a way of avoiding this?</p>

<p>I read your post multiple times and I think I finally get what you are trying to achieve.</p>

<p>try this:</p>

<pre><code>df['weekday'] = df.index.weekday
df['hour_of_day'] = df.index.hour
weekly_model = df.groupby(['weekday', 'hour_of_day']).mean()
dates = pd.date_range('2015/1/1', '2015/12/31 23:59', freq='H')
</code></pre>

<p>then use merge like this:</p>

<pre><code>annual_series = pd.merge(df.reset_index(), weekly_model.reset_index(), on=['weekday', 'hour_of_day']).set_index('date')
</code></pre>

<p>now you can use asof since you have dates as index</p>

<pre><code>annual_series.asof(dates)
</code></pre>

<p>is that what you were looking for?</p>

python pandas