title: python pandas groupby after sorted multindex not correct
body: <p>Dear python/pandas experts</p>
  
  <p>I am having problems with a sort on a index of a multiindex pandas dataframe. More specifically, it seems that the sort works, but that a groupby operation afterwards is 'forgetting' the sort again. For the reference: I am running python 3.4.3 (by anaconda) and pandas 0.16.2 (np19py34_0).</p>
  
  <p>Now in detail what I am trying to do.</p>
  
  <p>I have a multindex dataframe created as </p>
  
  <pre><code>import pandas as pd
  label="sdjks"
  sidechar="B"
  mi_level_fields = (label, sidechar)
  mi_level_names = ["Label", "Side"]
  pipeinfo_index = pd.MultiIndex.from_tuples([mi_level_fields], names=mi_level_names)
  
  pipeinfoDF = pd.DataFrame(index=pipeinfo_index, columns=[])
  pipeinfoDF.ix[(label, sidechar), "Nc"] = 10
  pipeinfoDF.ix[(label, "C"), "Nc"] = 10
  pipeinfoDF.ix[("ztest", "C"), "Nc"] = 400
  pipeinfoDF.ix[("ztest", "B"), "Nc"] = 400
  pipeinfoDF.ix[("yaki", "B"), "Nc"] = 1
  pipeinfoDF.ix[("yaki", "C"), "Nc"] = 1
  </code></pre>
  
  <p>This pipeinfoDF data frame looks now like</p>
  
  <pre><code>             Nc
  Label Side     
  sdjks B      10
        C      10
  ztest C     400
        B     400
  yaki  B       1
        C       1
  </code></pre>
  
  <p>Now I want to sort the indices of the dataframe such that the NC columns is in ascending order. This can be done with </p>
  
  <pre><code>pipeinfoDF.sort_index(by=["Nc"], inplace=True, ascending=True)
  </code></pre>
  
  <p>Which indeed correctly yields using </p>
  
  <pre><code>print(pipeinfoDF.head())
  
               Nc
  Label Side     
  yaki  B       1
        C       1
  sdjks B      10
        C      10
  ztest C     400
        B     400
  </code></pre>
  
  <p>The problem, however, appears when I want to loop over the rows of this multindex dataframe, which I normally do with </p>
  
  <pre><code>for (label, df) in pipeinfoDF.groupby(level=0, sort=False):
      side_list = df.index.get_level_values('Side')
      for side in side_list:
          data = pipeinfoDF.ix[(label, side)]
          print(label, side, data.Nc)
  </code></pre>
  
  <p>Which gives now as an output</p>
  
  <pre><code>sdjks B 10.0
  sdjks C 10.0
  ztest C 400.0
  ztest B 400.0
  yaki B 1.0
  yaki C 1.0
  </code></pre>
  
  <p>as you an see, although the head() statement shows that the dataframe has been sorted correctly, while looping over the indices (which I normally do to copy the data to another table) does not seem to use the correct sorted indices. </p>
  
  <p>This to me seems to be a bug: the sort option in the groupby statement has no effect on the result, and something similar has been reported <a href="https://github.com/pydata/pandas/issues/8868" rel="nofollow">here</a> as well.</p>
  
  <p>Now my question is: is there an easy way to fix this ? The head statement seems to be correctly giving my my sorted multindex dataframe, so I have been trying to make a copy of the output of this head like </p>
  
  <pre><code>result = pipeinfoDF.head()
  </code></pre>
  
  <p>but this not seems to work. </p>
  
  <p>My only last attempt tries to create a new data frame based on the resetted indices:</p>
  
  <pre><code>tmp = pipeinfoDF.copy()
  tmp.reset_index(inplace=True)
  
  lbls = tmp.Label.values
  sds  = tmp.Side.values
  
  pipeinfo_index2 = pd.MultiIndex.from_tuples(list(zip(lbls,sds)), names=mi_level_names)
  pipeinfoDF2 = pd.DataFrame(index=pipeinfo_index2, columns=[])
  
  for index, row in tmp.iterrows():
      for col in tmp.columns[2:]:
          pipeinfoDF2.ix[(row["Label"], row["Side"]), col] = row[col]
  </code></pre>
  
  <p>Again with head() I get the correct results</p>
  
  <pre><code>             Nc
  Label Side     
  yaki  B       1
        C       1
  sdjks B      10
        C      10
  ztest C     400
  </code></pre>
  
  <p>But with the loop over the multindex frame as earlier it is again sorting on the first group, which I explicitly prevented with sort=False</p>
  
  <pre><code>for (label, df) in pipeinfoDF2.groupby(level=0, sort=False):
      side_list = df.index.get_level_values('Side')
      for side in side_list:
          data = pipeinfoDF2.ix[(label, side)]
          print(label, side, data.Nc)
  </code></pre>
  
  <p>This give</p>
  
  <pre><code>sdjks B 10.0
  sdjks C 10.0
  yaki B 1.0
  yaki C 1.0
  ztest C 400.0
  ztest B 400.0
  </code></pre>
  
  <p>So the groupby option again seems to sort on the first index. </p>
  
  <p>Edit: I found the following to fix this. If you print the index of the dataframe it has labels not in numerical order:</p>
  
  <pre><code>print(pipeinfoDF2.index)
  
  MultiIndex(levels=[['sdjks', 'yaki', 'ztest'], ['B', 'C']],
             labels=[[1, 1, 0, 0, 2, 2], [0, 1, 0, 1, 1, 0]],
             names=['Label', 'Side'])
  </code></pre>
  
  <p>Here the levels are 'sdjks', 'yaki' 'ztest' and the labels correspond with the order 1,1,0,0,2,2
  The same can be seen when plotting the index of the first sorted pipeinfoDF, where the sort_index keeps the order of the levels in MultiIndex, but only changes the order of the labels.</p>
  
  <p>I can therefore fix my problem by forcing the labels to run as 0,0,1,1,2,2, because apparently the groupby ignores the order of the labels and always picks the order of the levels. My fix is therefore</p>
  
  <pre><code>pipeinfo_index2 = pd.MultiIndex.from_tuples([tuples[0]], names=mi_level_names)
  pipeinfoDF2 = pd.DataFrame(index=pipeinfo_index2, columns=[])
  </code></pre>
  
  <p>and then to fill the rest of the fields as I did before. In this way, the multindex looks as </p>
  
  <pre><code>MultiIndex(levels=[['yaki', 'sdjks', 'ztest'], ['B', 'C']],
             labels=[[0, 0, 1, 1, 2, 2], [0, 1, 0, 1, 1, 0]],
             names=['Label', 'Side'])
  </code></pre>
  
  <p>And now looping over the rows with groupby give the following output</p>
  
  <pre><code>yaki B 1.0
  yaki C 1.0
  sdjks B 10.0
  sdjks C 10.0
  ztest C 400.0
  ztest B 400.0
  </code></pre>
  
  <p>Which is correct!</p>
  
  <p>So  I have found a very ugly work around: after the sort, copy the whole dataframe to a new one, reset the index, and copy everything back to enforce the MultiIndex labels into numerical order. But I think this is very inefficient and generates lots of code and I am sure it is possible to do this more efficient. </p>
  
  <p>My question therefore is: is there a way to loop over the rows of a multiindex data frame which takes into account the labels order ? It is clearly ignored. Am I missing something? Hopefully there is an easier way to do this.</p>
  
  <p>Any hints appreciated!</p>
  
  <p>Edit: </p>
  
  <p>The suggestion of Firelynx works. If I do </p>
  
  <pre><code>for (label,side) in pipeinfoDF.index:
      data = pipeinfoDF.ix[(label, side)]
      print(label, side, data.Nc)
  </code></pre>
  
  <p>after the first sort I correctly get the data in the sorted order</p>
  
  <pre><code>yaki B 1.0
  yaki C 1.0
  sdjks B 10.0
  sdjks C 10.0
  ztest C 400.0
  ztest B 400.0
  </code></pre>
  
  <p>Saves my a lot of coding. The question remaining nevertheless is: should groupby with the sort=False option on not yield the same result? Is this a bug, or am I miss using the groupby way to loop over my data frame. It is based on examples I googled around, but it should be used with care. 
  Anyway, for now I solver my problem and I am going to abandon my groupby way of accessing data. </p>
  
  <p>Edit:</p>
  
  <p>The solution of Firelynx works, however, it is not taking into account the multilevel structure into account anymore, but just puts all the Label and side levels into one list. </p>
  
  <p>In order to get something very similar as I want to acchieve with the groupby method, I now do the following hack</p>
  
  <pre><code>label_list = []
  for (label,side) in pipeinfoDF.index:
      if not label in label_list:
          label_list.append(label)
  
  for label in label_list:
      df = pipeinfoDF.loc[label]
      side_list = df.index.get_level_values('Side')
      for side in side_list:
          data = pipeinfoDF.ix[(label, side)]
          print(label, side, data.Nc)
  </code></pre>
  
  <p>Which correctly yields </p>
  
  <pre><code>yaki B 1.0
  yaki C 1.0
  sdjks B 10.0
  sdjks C 10.0
  ztest C 400.0
  ztest B 400.0
  </code></pre>
  
  <p>So I extract the sorted label list first using the suggestion of Firelynx, and then loop over this list to get the side per label and do something I want to do with it. Although this is much cleaner than my first approach, still I have the feeling it could be done more directly somehow. I can not imaging that you can not use the groupby method on sorted multiindex dataframes without messing  up the sort order. Perhaps someone has a suggestion? Anyway, for now I am happy with the solution</p>
  
  <p>Based on the latest suggestion of Firelynx I have a small update which makes it a bit cleaner. Still, you need to keep a list in order to prevent double counts of the label, because unique apply only to a unique (label,side) combination. So I now have </p>
  
  <pre><code>label_list = []
  for (label, side) in pipeinfoDF.index.unique():
      if not label in label_list:
          label_list.append(label)
      else:
          continue
      df = pipeinfoDF.loc[label]
      side_list = df.index.get_level_values('Side')
      for side in side_list:
          data = pipeinfoDF.ix[(label, side)]
          print(label, side, data.Nc)
  </code></pre>
  
  <p>Is it possible to have unique() apply on the label alone? Then I could remove the label_list to keep track of which label has been processed already</p>