Pandas: Group by two parameters and sort by third parameter

Question

I want to group my dataframe by two columns (Name and Budget) and then sort the aggregated results by a third parameter (Prio).

Name     Budget    Prio    Quantity
peter    A         2       12
         B         1       123
joe      A         3       34
         B         1       51
         C         2       43

I already checked this post, which was very helpful and leads to the following output. However, I cannot manage sorting by the third parameter (Prio).

df_agg = df.groupby(['Name','Budget','Prio']).agg({'Quantity':sum})

g = df_agg['Quantity'].groupby(level=0, group_keys=False)

res = g.apply(lambda x: x.sort_values(ascending=True))

I would now like to sort the prio in ascending order within each of the groups. To get something like:

Name     Budget    Prio    Quantity
peter    B         1       123
         A         2       12
joe      B         1       51
         C         2       34
         A         3       43

score 1 · Accepted Answer · answered Oct 21 '19 at 16:12

1

IIUC,

df.groupby(['Name','Budget','Prio']).agg({'Quantity':sum}).sort_values(['Name','Prio'])

Output:

                   Quantity
Name  Budget Prio          
joe   B      1           51
      C      2            4
      A      3           34
peter B      1          123
      A      2           12

answered Oct 21 '19 at 16:12

Scott Boston

147,308
15
139
187

Thank you, that really helped! After this is done, is there any way to loop through the rows and access the specific values? – maxiw46 Oct 22 '19 at 11:21
@maxiw46 Yes, you can use tuples to access a specific value. `df_out.loc[('joe','A',3), 'Quantity']` returns 34. – Scott Boston Oct 22 '19 at 13:25

score 1 · Answer 2 · answered Oct 21 '19 at 16:13

If you want only sort by Prio, you can use sort_index:

(df.groupby(['Name','Budget','Prio'])
   .agg({'Quantity':'sum'})
   .sort_index(level=['Name', 'Prio'],
                ascending=[False, True])
)

Output:

                   Quantity
Name  Budget Prio          
peter B      1          123
      A      2           12
joe   B      1           51
      C      2           43
      A      3           34

Pandas: Group by two parameters and sort by third parameter

2 Answers2