title: Pandas Multiindex: what am I doing wrong?
body: <p>I have a program where I have a large pandas dataframe of pairwise interactions (in rows) that I do random walks through. The list of options for each successive step is narrowed down from the entire dataframe by specific values in two columns, so basically,  </p>
  
  <pre><code>df_options = df[(df.A == x) &amp; (df.B == y)]
  </code></pre>
  
  <p>I had the thing working using syntax like above, but it seemed like it would be a great idea in terms of speed (which was limiting) to index df by A, B like so:  </p>
  
  <pre><code>df.sort(['A', 'B'], inplace=True)
  df.index = df.index.rename('idx')
  df = df.set_index(['A', 'B'], drop=False, append=True, verify_integrity=True)
  </code></pre>
  
  <p>(note I'm keeping the original index as 'idx' because that was how I was recording the random walks and accessing specific rows)<br>
  So then I replaced the original df_options code with, firstly,<br>
  <code>df.xs((x, y), level=('A', 'B'))</code><br>
  and after having problems with that,<br>
  <code>df.loc(axis=0)[:,A,B]</code>  </p>
  
  <p>Also, where I needed specific values, the original syntax changed from  </p>
  
  <pre><code>df_options.loc[new, 'sim']
  </code></pre>
  
  <p>to  </p>
  
  <pre><code>df_options.xs(new, level='idx')['sim'].values[0]
  </code></pre>
  
  <p>or  </p>
  
  <pre><code>df_options.loc(axis=0)[new,:,:]['sim'].values[0]
  </code></pre>
  
  <p>("new" is the randomly chosen next index of df, and 'sim' is a column of pairwise similarity scores.)</p>
  
  <p>As I hacked away trying to get this to work, I kept getting errors like <code>'...not hashable'</code> and <code>AttributeError: 'Int64Index' object has no attribute 'get_loc_level</code></p>
  
  <p>Which brings me to the question in the title: what am I doing wrong? More specifically:<br>
  1) does multiindex really have the potential to speed this process up like I think?,<br>
  2) if so, what are the correct idioms to use here (feels like I'm up a creek with .xs and .loc),<br>
  3) or should I use something else like raw numpy?</p>
  
  <p><strong>EDIT</strong> In the process of creating an example with code, I managed to get it working. I would say that I had to jump through some awkward hoops though, like <code>.values[0]</code> in <code>row.p2.values[0]</code> and <code>df.index[rand_pair][0][0]</code>. </p>
  
  <p>In response to Jeff:
  pandas 0.14.1</p>
  
  <pre><code>df.info()
  &lt;class 'pandas.core.frame.DataFrame'&gt;
  MultiIndex: 561567 entries, (0, 0, 003) to (561566, 26127, 011)
  Data columns (total 14 columns):
  p1              561567 non-null int64
  smp1            561567 non-null object
  rt1             561567 non-null float64
  cas1            561567 non-null object
  sim1            561567 non-null float64
  p2              561567 non-null int64
  smp2            561567 non-null object
  rt2             561567 non-null float64
  cas2            561567 non-null object
  sim2            561567 non-null float64
  nlsim1          561567 non-null float64
  sum_spec_sq1    561567 non-null float64
  sum_spec_sq2    561567 non-null float64
  sum_s1s2        561567 non-null float64
  dtypes: float64(8), int64(2), object(4)
  </code></pre>
  
  <p>Note: "p1", "smp2", and "nlsim1" correspond to "A" "B", and "sim" in my question above.
  Enough data to walk a couple steps:</p>
  
  <pre><code>df = pd.DataFrame({u'nlsim1': {174513: 0.8782, 270870: 0.9461, 478503: 0.8809},
   u'p1': {174513: 8655, 270870: 13307, 478503: 22276},
   u'p2': {174513: 13307, 270870: 22276, 478503: 2391},
   u'smp1': {174513: u'007', 270870: u'010', 478503: u'016'},
   u'smp2': {174513: u'010', 270870: u'016', 478503: u'002'}})
  df.index = df.index.rename('idx')
  df = df.set_index(['p1', 'smp2'], drop=False, append=True, verify_integrity=True)
  
  def weighted_random_choice():
      options = df_options.index.tolist()
      tot = df_options.nlsim1.sum()
      options_weight = df_options.nlsim1 / tot
      return np.random.choice(options, p=list(options_weight))
  </code></pre>
  
  <p>Initiates the walk:</p>
  
  <pre><code>samples = set([c for a, b, c in df.index.values])
  df_numbered = range(df.shape[0])
  #rand_pair = random.sample(df_numbered, 1)
  rand_pair = [0]
  path = [df.index[rand_pair][0][0]]
  </code></pre>
  
  <p>The walk (iterate it):</p>
  
  <pre><code>row = df.loc[path[-1],:,:]
  p = row.p2.values[0]
  smp = row.smp2.values[0]
  print p, smp
  samples.discard(smp)
  print sorted(list(samples))
  pick_sample = random.sample(samples, 1)[0]
  print pick_sample
  df_options = df.xs((p, pick_sample), level=('p1', 'smp2'))
  if df_options.shape[0] &lt; 1:
      print "out of options, stop iterating"
      print "path=", path
  else:
      print "# options: ", df_options.shape[0]
      new = weighted_random_choice()
      path.append(new)
      print path
      print "you should keep going"
  </code></pre>
  
  <p>output, 1st step:</p>
  
  <pre><code>13307 010
  [u'002', u'016']
  016
  # options:  1
  [174513, 270870]
  you should keep going
  </code></pre>
  
  <p>2nd step:</p>
  
  <pre><code>22276 016
  [u'002']
  002
  # options:  1
  [174513, 270870, 478503]
  you should keep going
  </code></pre>
  
  <p>3rd step errors as expected b/c it runs out of samples.</p>