pandas groupby and sorting

I continue to find this a little troubling...

Test Data

This is the test file I use for these example.

name,company,amount
tim,ibm,22
tim,ibm,22
tim,ibm,22
tim,gbm,35
juliet,indeff,10
juliet,indeff,20
juliet,indeff,30
juliet,indeff,40
andrew,ibm,4
andrew,gbm,6
andrew,ba,200

Read everything in ...

import pandas as pd
import numpy as np
df=pd.read_csv('test.csv')
df
name company amount
0 tim ibm 22
1 tim ibm 22
2 tim ibm 22
3 tim gbm 35
4 juliet indeff 10
5 juliet indeff 20
6 juliet indeff 30
7 juliet indeff 40
8 andrew ibm 4
9 andrew gbm 6
10 andrew ba 200

Sum the amount by person

Note: This creates an index field called name.

df.groupby(by=['name']).agg({'amount':'sum'})
amount
name
andrew 210
juliet 100
tim 101

Get the Number of occurances per person

df.groupby(['name']).agg({'amount':'count'})
amount
name
andrew 3
juliet 4
tim 4

Order the Number of occurances

a=df.groupby(['name']).agg({'amount':'count'})
a.sort_values('amount',ascending=False).reset_index()

And Now limit to values above 3

a=df.groupby(['name']).agg({'amount':'count'})
t=a.sort_values('amount',ascending=False).reset_index()
t[t.amount>3]
name amount
0 juliet 4
1 tim 4

Amount Per Person and Per Company

df.groupby(['name','company']).agg({'amount':'sum'})
amount
name company
andrew ba 200
gbm 6
ibm 4
juliet indeff 100
tim gbm 35
ibm 66

Amount per Person & Per Company ordered by Name, Company

t=(df.groupby(['name','company']).agg({'amount':'sum'}))
t=t.reset_index()
t.sort_values(['name','company'])
name company amount
0 andrew ba 200
1 andrew gbm 6
2 andrew ibm 4
3 juliet indeff 100
4 tim gbm 35
5 tim ibm 66

Amount per person per company ordered nicer output

(df.groupby(['name','company']).agg({'amount':'sum'})).reset_index().set_index(['name','company'])
amount
name company
andrew ba 200
gbm 6
ibm 4
juliet indeff 100
tim gbm 35
ibm 66

Who spend more the 50 with a company

t=(df.groupby(['name','company']).agg({'amount':'sum'}))
t=t.reset_index()
t.sort_values(['name','company'],inplace=True)
t[t.amount>50]
name company amount
0 andrew ba 200
3 juliet indeff 100
5 tim ibm 66

Which company got more than 100

t=(df.groupby(['company']).agg({'amount':'sum'})).reset_index()
t[t.amount>100]
company amount
0 ba 200