How to use Groupby and Aggregate for exploratory data analysis in pandas ?


Benjamin H.G. Marchant


Testing Groupby and Aggregate operations for exploratory data analysis with pandas. Please feel free to download and edit it as you like.

Summary:

Introduction

Import pandas

In [91]:
import pandas as pd

Jupyter notebook center output images:

In [92]:
from IPython.core.display import HTML
HTML("""
<style>
.output_png {
    display: table-cell;
    text-align: center;
    vertical-align: middle;
}
</style>
""")
Out[92]:

GroupBy

In [93]:
data = {'Age':[21,26,82,15,28],
        'weight':[120,148,139,156,129],
       'Gender':['male','male','female','male','female'],
       'Country':['France','USA','USA','Germany','USA']}

df = pd.DataFrame(data=data)
In [94]:
df.groupby(by="Gender").mean()
Out[94]:
Age weight
Gender
female 55.000000 134.000000
male 20.666667 141.333333
In [95]:
df.groupby(["Gender",'Country']).mean()
Out[95]:
Age weight
Gender Country
female USA 55 134
male France 21 120
Germany 15 156
USA 26 148
In [96]:
df.groupby(["Gender",'Country']).Age.mean()
Out[96]:
Gender  Country
female  USA        55
male    France     21
        Germany    15
        USA        26
Name: Age, dtype: int64
In [97]:
df.groupby(["Gender",'Country'])['Age'].mean()
Out[97]:
Gender  Country
female  USA        55
male    France     21
        Germany    15
        USA        26
Name: Age, dtype: int64
In [98]:
df.groupby(["Country",'Gender']).mean()
Out[98]:
Age weight
Country Gender
France male 21 120
Germany male 15 156
USA female 55 134
male 26 148
In [99]:
df.groupby(["Country",'Gender']).mean().sort_values(by='Age')
Out[99]:
Age weight
Country Gender
Germany male 15 156
France male 21 120
USA male 26 148
female 55 134
In [100]:
df.groupby(by="Gender").describe()
Out[100]:
Age weight
count mean std min 25% 50% 75% max count mean std min 25% 50% 75% max
Gender
female 2.0 55.000000 38.183766 28.0 41.5 55.0 68.5 82.0 2.0 134.000000 7.071068 129.0 131.5 134.0 136.5 139.0
male 3.0 20.666667 5.507571 15.0 18.0 21.0 23.5 26.0 3.0 141.333333 18.903263 120.0 134.0 148.0 152.0 156.0

Agg

In [101]:
df.groupby(by="Gender").agg('mean')
Out[101]:
Age weight
Gender
female 55.000000 134.000000
male 20.666667 141.333333
In [102]:
df.groupby(by="Gender").agg(['mean','count','sum','min','max'])
Out[102]:
Age weight
mean count sum min max mean count sum min max
Gender
female 55.000000 2 110 28 82 134.000000 2 268 129 139
male 20.666667 3 62 15 26 141.333333 3 424 120 156

Testing on the Titanic database

Let's test groupby and agg operations on the database titanic.csv

In [103]:
df = pd.read_csv('https://gist.githubusercontent.com/michhar/2dfd2de0d4f8727f873422c5d959fff5/raw/fa71405126017e6a37bea592440b4bee94bf7b9e/titanic.csv')
In [104]:
df.head()
Out[104]:
PassengerId Survived Pclass Name Sex Age SibSp Parch Ticket Fare Cabin Embarked
0 1 0 3 Braund, Mr. Owen Harris male 22.0 1 0 A/5 21171 7.2500 NaN S
1 2 1 1 Cumings, Mrs. John Bradley (Florence Briggs Th... female 38.0 1 0 PC 17599 71.2833 C85 C
2 3 1 3 Heikkinen, Miss. Laina female 26.0 0 0 STON/O2. 3101282 7.9250 NaN S
3 4 1 1 Futrelle, Mrs. Jacques Heath (Lily May Peel) female 35.0 1 0 113803 53.1000 C123 S
4 5 0 3 Allen, Mr. William Henry male 35.0 0 0 373450 8.0500 NaN S
In [105]:
df.columns
Out[105]:
Index(['PassengerId', 'Survived', 'Pclass', 'Name', 'Sex', 'Age', 'SibSp',
       'Parch', 'Ticket', 'Fare', 'Cabin', 'Embarked'],
      dtype='object')
In [106]:
df['Age'].plot(kind="hist")
Out[106]:
<matplotlib.axes._subplots.AxesSubplot at 0x7febd0fa1898>
In [107]:
df['Age'].mean()
Out[107]:
29.69911764705882
In [108]:
df['Sex'].value_counts(dropna=False)
Out[108]:
male      577
female    314
Name: Sex, dtype: int64
In [109]:
df['Age'][ df['Sex'] == "male" ].hist()

df['Age'][ df['Sex'] == "male" ].mean()
Out[109]:
30.72664459161148
In [110]:
df['Age'][ df['Sex'] == "female" ].hist()

df['Age'][ df['Sex'] == "female" ].mean()
Out[110]:
27.915708812260537
In [111]:
df.groupby(['Survived','Sex'])[['Age','Fare']].mean()
Out[111]:
Age Fare
Survived Sex
0 female 25.046875 23.024385
male 31.618056 21.960993
1 female 28.847716 51.938573
male 27.276022 40.821484
In [112]:
df.groupby(['Survived','Sex']).Age.mean()
Out[112]:
Survived  Sex   
0         female    25.046875
          male      31.618056
1         female    28.847716
          male      27.276022
Name: Age, dtype: float64
In [113]:
%%time

df[['Pclass','Survived']].groupby(['Pclass']).mean()

CPU times: user 5.27 ms, sys: 1.83 ms, total: 7.1 ms
Wall time: 5.92 ms
Out[113]:
Survived
Pclass
1 0.629630
2 0.472826
3 0.242363

Same but slower since the mean is calculated for every numeric columns !!

In [114]:
%%time

df.groupby(['Pclass'])[['Survived']].mean()

CPU times: user 7.42 ms, sys: 744 µs, total: 8.17 ms
Wall time: 7.96 ms
Out[114]:
Survived
Pclass
1 0.629630
2 0.472826
3 0.242363
In [115]:
df.groupby(['Survived','Sex']).Age.describe()
Out[115]:
count mean std min 25% 50% 75% max
Survived Sex
0 female 64.0 25.046875 13.618591 2.00 16.75 24.5 33.25 57.0
male 360.0 31.618056 14.056019 1.00 21.75 29.0 39.25 74.0
1 female 197.0 28.847716 14.175073 0.75 19.00 28.0 38.00 63.0
male 93.0 27.276022 16.504803 0.42 18.00 28.0 36.00 80.0
In [116]:
df.groupby(['Survived','Sex'])[['Age','Fare']].agg(['mean','max','min'])
Out[116]:
Age Fare
mean max min mean max min
Survived Sex
0 female 25.046875 57.0 2.00 23.024385 151.5500 6.750
male 31.618056 74.0 1.00 21.960993 263.0000 0.000
1 female 28.847716 63.0 0.75 51.938573 512.3292 7.225
male 27.276022 80.0 0.42 40.821484 512.3292 0.000
In [117]:
df.groupby(['Survived']).Age.mean()
Out[117]:
Survived
0    30.626179
1    28.343690
Name: Age, dtype: float64
In [118]:
df.groupby(['Survived']).Fare.mean()
Out[118]:
Survived
0    22.117887
1    48.395408
Name: Fare, dtype: float64

Links