title: Query HDF5 in Pandas
body: <p>I have following data (18,619,211 rows) stored as a pandas dataframe object in hdf5 file:</p>
  
  <pre><code>             date    id2         w
  id                              
  100010 1980-03-31   10401  0.000839
  100010 1980-03-31   10604  0.020140
  100010 1980-03-31   12490  0.026149
  100010 1980-03-31   13047  0.033560
  100010 1980-03-31   13303  0.001657
  </code></pre>
  
  <p>where <code>id</code> is index and others are columns. <code>date</code> is <code>np.datetime64</code>. I need to perform query like this (the code doesn't work of course):</p>
  
  <pre><code>db=pd.HDFStore('database.h5')
  data=db.select('df', where='id==id_i &amp; date&gt;bgdt &amp; date&lt;endt')
  </code></pre>
  
  <p>Note <code>id_i, bgdt, endt</code> are all variables, not actual values and need to be passed within a loop. for example: </p>
  
  <p><code>dates</code> is a Pandas Period index or timestamps index, either way, I can convert to each other.</p>
  
  <pre><code>dates=['1990-01', 1990-04','1990-09',......]  
  </code></pre>
  
  <p><code>id_list</code> is a list of IDs</p>
  
  <pre><code>id_list=[100010, 100011,1000012,.......]
  </code></pre>
  
  <p>The loop looks like this (the reason I am doing loop is because the data is huge, there are other datasets I have query in the same time and then perform some operations)</p>
  
  <pre><code>db=pd.HDFStore('database.h5')
  for id_i in id_list:
      for date in dates:
          bgdt=date-1 (move to previous month)
          endt=date-60 (previous 60 month)
          data=db.select('df', where='index==id_i &amp; date&gt;bgdt &amp; date&lt;endt')
          ......
  </code></pre>
  
  <p>This problem have 2 parts: </p>
  
  <ul>
  <li>I don't know how to query index and columns in the same time. The doc in pandas showed how to query based on index conditions OR columns conditions, but no examples on how to query based on them in the SAME TIME. 
  <ul>
  <li>(BTW, This is very common in Pandas Documentation. The doc usually shows very simple thing like how to do 'A', OR how to do 'B', but not how to do BOTH 'A' and 'B'. A good example is use <code>query</code> on a MultiIndex pandas dataframe. The doc shows based on either <code>level=0</code> OR <code>level=1</code>, but no example on how to do BOTH in the SAME TIME.)</li>
  </ul></li>
  <li>I don't know how to pass three variables <code>id_i, bgdt, endt</code> to the query. I know how to pass only on by using <code>%s</code>, but not all of them.
  <ul>
  <li>I am also a little confused with the datetime datatype. There seems to be quite a few of datetimes:<code>datetime.datetime</code>, <code>numpy.datetime64</code>, <code>pandas.Period</code>. I am mostly working on monthly data, so <code>pandas.Period</code> is the most useful one. But I can't easily convert a column (not index) of timestamps (the default date type of Pandas when parsed from raw data). Is there any datatype that is simply a 'date', not timestamps, not period, but just a simple DATE with only year,month and day? </li>
  </ul></li>
  </ul>
  
  <p>A lot troubles, but I really LOVE python and pandas (I am trying to move my workflow from SAS to Python). Any help will be appreciated!</p>