Search code examples
pythonpandasgroup-by

MultiIndex Dataframe: Sort values for each group

Assume I have a pandas MultiIndex Dataframe similar to the one below. How do I sort the values for each group by preserving the assignment and order of the indexes? The code below works for the sorting, but I fail to resave the sorted values and inplace does not seem to work.

df = pd.DataFrame([4,8,6,11,13,15], columns=['val'],
    index=[['A','A','A','B','B','B'],['a1','a2','a3','b1','b2','b3']])

#         val
#  A  a1    4
#     a2    8
#     a3    6
#  B  b1   11
#     b2   13
#     b3   15


for idx in df.index.unique(level=0):
    tmp = df.loc[idx].sort_values(by='val')

#      val
#  a1    4
#  a3    6
#  a2    8
Solution

  • A variant of @Andrej's answer using groupby.transform rather that apply and sorted rather than unaligning the indices by converting to numpy:

    df['val'] = df.groupby(level=0)['val'].transform(sorted)

    Output:

          val
A a1    4
  a2    6
  a3    8
B b1   11
  b2   13
  b3   15