Slice a dataframe with a hierarchy index
<p>I have two dataframes. One has stock transactions (things like buy date, buy price, sell date, sell price).  The other dataframe has all the prices in date order with an hierarchy index of <code>['symbol', 'date']</code> indexing <code>'close'</code> price called <code>dfPrice</code>.</p>

<p>Not knowing a better way of submitting a dataframe to this group, I have made a record of the first 10 lines by:</p>

<pre><code>ra = dfPrice.to_records()
</code></pre>

<p>yielding an <code>ra</code> of:</p>

<pre><code>rec.array([('A', Timestamp('2000-09-01 00:00:00'), 39.84),
 ('A', Timestamp('2000-09-05 00:00:00'), 39.8),
 ('A', Timestamp('2000-09-06 00:00:00'), 38.63),
 ('A', Timestamp('2000-09-07 00:00:00'), 39.84),
 ('A', Timestamp('2000-09-08 00:00:00'), 38.15),
 ('A', Timestamp('2000-09-11 00:00:00'), 36.54),
 ('A', Timestamp('2000-09-12 00:00:00'), 35.41),
 ('A', Timestamp('2000-09-13 00:00:00'), 35.41),
 ('A', Timestamp('2000-09-14 00:00:00'), 35.89),
 ('A', Timestamp('2000-09-15 00:00:00'), 36.7)], 
      dtype=[('symbol', 'S1'), ('date', 'O'), ('close', '&lt;f8')])
</code></pre>

<p>you can get the <code>dfPrice</code> by:</p>

<pre><code>dfPrice = DataFrame(ra)
dfPrice.set_index(['symbol', 'date'], inplace=True)
</code></pre>

<p>what I want is to use the buy date and sell date and look up the minimum price in the interval I held the stock. </p>

<p>If I bought stock 'A' on 2000-09-07 and sold on 2000-09-14 (keeping it over the weekend without any price entries) I thought I could get the minimum price over that interval by using something like:</p>

<pre><code>minPrice = dfPrice.min['A', '2000-09-07':'2000-09-14']
</code></pre>

<p>The answer is 35.41.</p>

<p>I have looked on Stack Overflow, but came up empty. What can I use to get what I want?</p>

<p>There might be a more straightforward way to do so, but I managed to get <a href="http://pandas.pydata.org/pandas-docs/stable/advanced.html#advanced-indexing-with-hierarchical-index" rel="nofollow">advanced indexing</a> to work using a tuple for your hierarchical indices:</p>

<pre><code>&gt;&gt;&gt; dfPrice[('A','2000-09-07'):('A','2000-09-14')]
                   close
symbol date             
 'A'   2000-09-07  39.84
       2000-09-08  38.15
       2000-09-11  36.54
       2000-09-12  35.41
       2000-09-13  35.41
       2000-09-14  35.89
&gt;&gt;&gt; dfPrice[('A','2000-09-07'):('A','2000-09-14')].min()
close    35.41
dtype: float64
</code></pre>

<p>So for one you have to use advanced indexing in order to slice into your second level of indices, and you have to apply the <code>.min()</code> method to a sliced dataframe (rather than trying to put indices inside the call to <code>.min()</code>).</p>

python indexing dataframe slice hierarchy