Pandas groupby example

Pandas groupby function is really useful and powerful in many ways. This week, I am going to show some examples of using this groupby functions that I usually use in my analysis. 

import pandas as pd
import matplotlib.pyplot as plt
import numpy as np
plt.style.use('seaborn-poster')

%matplotlib inline

Let's first create a DataFrame, that contains the name, score and round for some games. 

a = ['Qingkai', 'Ironman', 'Batman', 'Qingkai', 'Ironman', 'Qingkai', 'Batman']
b = [3., 4, 2., 4, 5, 1, 2]
c = range(len(a))

d = [[x,y,z] for x,y,z in zip(a,b,c)]

df = pd.DataFrame(d, columns=['name', 'score', 'round'])
df

Now I want to calculate the mean scores for different users across all the games and the standard deviations. It could be quite simple with Pandas. As I am showing below:

df.groupby('name').mean()

	score	round
name
Batman	2.000000	4.000000
Ironman	4.500000	2.500000
Qingkai	2.666667	2.666667

df.groupby('name').std()

	score	round
name
Batman	0.000000	2.828427
Ironman	0.707107	2.121320
Qingkai	1.527525	2.516611

Or I can loop through the groupby object once to calculate them all. 

for ix, grp in df.groupby('name'):
    print('Name: %s, mean: %.1f, std: %.1f'%(ix, grp['score'].mean(), grp['score'].std()))

Name: Batman, mean: 2.0, std: 0.0
Name: Ironman, mean: 4.5, std: 0.7
Name: Qingkai, mean: 2.7, std: 1.5

But also, we could do it with one liner using the agg function:

df.groupby('name').agg({'score':['mean','std'],'round':'count'})

	score		round
	mean	std	count
name
Batman	2.000000	0.000000	2
Ironman	4.500000	0.707107	2
Qingkai	2.666667	1.527525	3

Besides, you can also use some customized functions in the agg as well. For example, if we want to calculate the RMS value of the score, we could do the following:

def cal_RMS(x):
    return np.sqrt(sum(x**2/len(x)))

df.groupby('name').agg({'score':['mean',cal_RMS],'round':'count'})

	score		round
	mean	cal_RMS	count
name
Batman	2.000000	2.000000	2
Ironman	4.500000	4.527693	2
Qingkai	2.666667	2.943920	3