Aggregation And Grouping

Aggregation: We will study aggregations like sum(), mean(), median(), min(), and max(), in which a single number gives insight into the nature of a potentially large dataset **Grouping: **When we are dealing with large datasets, it is useful to group data by common categories (value of particular column). To accomplish this, we use the GroupBy function pandas DataFrame

🛳 Titanic Ship Data for Demonstration

• We will use Titanic survivors data from our analysis

• You can download your own copy here

import numpy as np
import pandas as pd 

titanic = pd.read_csv('data/titanic.csv')

# fetching column names using .keys method
print(titanic.keys()) 

Index(['survived', 'pclass', 'sex', 'age', 'fare', 'embarked', 'who',
       'embark_town', 'alive', 'alone'],
      dtype='object')

print(titanic.head()) # show the header, includes first five rows

   survived  pclass     sex   age     fare embarked    who  embark_town alive  \
0         0       3    male  22.0   7.2500        S    man  Southampton    no   
1         1       1  female  38.0  71.2833        C  woman    Cherbourg   yes   
2         1       3  female  26.0   7.9250        S  woman  Southampton   yes   
3         1       1  female  35.0  53.1000        S  woman  Southampton   yes   
4         0       3    male  35.0   8.0500        S    man  Southampton    no   
   alone  
0  False  
1  False  
2   True  
3  False  
4   True  

1. SIMPLE AGGREGATION

**Features of Dataset: ** Columns of a DataFrame is referred to as features of a dataset, these features can be:

• Quantitative feature: Any value represented by numbers. We apply numerical metrics (sum, mean, std) on them

• Categorical feature: Values are categories that can be used to group the dataset. GroupBy is applied on categorical features

1.1. describe()

Computes several common aggregates for each column (containing integers and floats) i.e, .describe applies to categorical features

print(titanic.describe())

         survived      pclass         age        fare
count  891.000000  891.000000  714.000000  891.000000
mean     0.383838    2.308642   29.699118   32.204208
std      0.486592    0.836071   14.526497   49.693429
min      0.000000    1.000000    0.420000    0.000000
25%      0.000000    2.000000   20.125000    7.910400
50%      0.000000    3.000000   28.000000   14.454200
75%      1.000000    3.000000   38.000000   31.000000
max      1.000000    3.000000   80.000000  512.329200

1.2. Fetching Unique Values

For categorical features, we can use .unique() method to obtain unique instances of each categorical feature:

# fetching unique values under column 'embark_station'
titanic['embark_town'].unique()

array(['Southampton', 'Cherbourg', 'Queenstown', nan], dtype=object)

1.3. Frequency of Unique Values

For categorical features, we can use .value_counts() method to obtain frequency counts of each category in column feature

# fetching all unique instances of column 'embark_town'
# along with the frequency count
titanic['embark_town'].value_counts()

Southampton    644
Cherbourg      168
Queenstown      77
Name: embark_town, dtype: int64

We can use normalize=True argument to get proportion of frequency count

# sum of all values is equal to 1
titanic['embark_town'].value_counts(normalize=True)

Southampton    0.724409
Cherbourg      0.188976
Queenstown     0.086614
Name: embark_town, dtype: float64

2. GROUPBY: SPLIT, APPLY, COMBINE

GroupBy, split-apply-combine; is one of the most common and useful strategy of data analysis:

• The split step involves breaking up and grouping a DataFrame depending on the values of the specified column

• The apply step involves computing some function - usually an aggregate, transformation, or filtering; within the individual groups. Apply can take following forms:

  • Aggregation

  • Tranformation

  • Filteration

• The combine step merges the results of these operations into an output array.

2.1. Grouping by Single Column

a. Split

Split (Creating a DataFrame GroupBy Object): Let suppose we would like to GroupBy column name sex. Doing this will create a GroupBy object which stores the data of the individual groups in the form of key value pairs, which we can fetch using .groups method on this DataFrame object

#grouping by column 'sex'
group_by_sex = titanic.groupby('sex') 

# the output is DataFrameGroupBy object
group_by_sex

<pandas.core.groupby.generic.DataFrameGroupBy object at 0x7f9778b203d0>

# getting all groups as key-value pair
group_by_sex.groups

{'female': Int64Index([  1,   2,   3,   8,   9,  10,  11,  14,  15,  18,
             ...
             866, 871, 874, 875, 879, 880, 882, 885, 887, 888],
            dtype='int64', length=314),
 'male': Int64Index([  0,   4,   5,   6,   7,  12,  13,  16,  17,  20,
             ...
             873, 876, 877, 878, 881, 883, 884, 886, 889, 890],
            dtype='int64', length=577)}

b. Apply-Combine

Suppose we are interested to know .mean() of all columns values, once the data is grouped by sex This step will apply the mean to each instance of group and then combine the result to give us a Series (for single column selection) or DataFrame (for multiple columns selection)

titanic.groupby('sex').mean()

        survived    pclass        age       fare     alone
sex                                                       
female  0.742038  2.159236  27.915709  44.479818  0.401274
male    0.188908  2.389948  30.726645  25.523893  0.712305

➞ Let suppose further that instead of getting mean of all column values, we are only interested in finding mean of values under column survived and age

print(titanic.groupby('sex')['survived','age'].mean())

        survived        age
sex                        
female  0.742038  27.915709
male    0.188908  30.726645

➞ In addition, we can also fetch specific group using get_group('group_name_here') and apply aggregator on that object:

group_by_sex.get_group('male').mean()

survived     0.188908
pclass       2.389948
age         30.726645
fare        25.523893
alone        0.712305
dtype: float64

2.2. Grouping by two Columns

We are not limited to GroupBy single column. In this example, we group the DataFrame by columns sex and alive and then apply the mean aggregator. Further, we are only interested to get the output on age and fare columns

print(titanic.groupby(['sex','alive'])['age','fare'].mean())

                    age       fare
sex    alive                      
female no     25.046875  23.024385
       yes    28.847716  51.938573
male   no     31.618056  21.960993
       yes    27.276022  40.821484

2.3. Iteration over groups

We have discussed earlier that GroupBy function creates a GroupBy object, which stores the values in dictionary style key-value pair. To demonstrate this concept, we will apply a for loop on GroupBy object

# let get the shape of each group, when we GroupBy 'pclass'
for (name, group) in titanic.groupby('pclass'):
    print(f"{name}: shape={group.shape}")

1: shape=(216, 10)
2: shape=(184, 10)
3: shape=(491, 10)

3. AGGREGATE, FILTER, TRANSFORM, APPLY

GroupBy objects have aggregate(), filter(), transform(), and apply() methods that efficiently implement a variety of useful operations before combining the grouped data

First step is to construct a DataFrame for this discussion

rand = np.random.RandomState(42)
df = pd.DataFrame({'key': ['A','B','C','A','B','C'],
                  'data1': rand.randint(1,10, size=6),
                  'data2': rand.randint(1,20, size=6)})

print(df)

  key  data1  data2
0   A      7     11
1   B      4     11
2   C      8      4
3   A      5      8
4   B      7      3
5   C      3      2

3.1. Aggregation

In the above example, we just calculated a single aggregator(mean), however using the aggregate() method, we can compute multiple aggregators in a single command. The aggregate() method takes a string, a function or a list of all the required aggregates to compute.

# passing list of aggregators we need for the data1 and data 2
df.groupby('key').aggregate([min,max,np.median,np.mean,np.std])

    data1                           data2                          
      min max median mean       std   min max median mean       std
key                                                                
A       5   7    6.0  6.0  1.414214     8  11    9.5  9.5  2.121320
B       4   7    5.5  5.5  2.121320     3  11    7.0  7.0  5.656854
C       3   8    5.5  5.5  3.535534     2   4    3.0  3.0  1.414214

Let suppose, we don’t want to compute one type of aggregator for all columns, rather, we want to compute median for data1 and mean for data2

# we will pass the dictionary of key(column-name) and value(aggregator)
df.groupby('key').aggregate({'data1':np.median,
                            'data2':np.mean})

     data1  data2
key              
A      6.0    9.5
B      5.5    7.0
C      5.5    3.0

➞ Now, as we grasp the concept of aggregate() method, let apply this on our titanic data

titanic.groupby('sex')['age','fare'].aggregate([min,max,np.mean])

         age                   fare                     
         min   max       mean   min       max       mean
sex                                                     
female  0.75  63.0  27.915709  6.75  512.3292  44.479818
male    0.42  80.0  30.726645  0.00  512.3292  25.523893

3.2 Filtering

A filtering operation allows us to drop data based on some group properties Filter is applied in form of function

Let’s apply filter to drop rows where standard deviation of data1 is greater than 2

df.groupby('key').filter(lambda x: x['data1'].std() > 2)

  key  data1  data2
1   B      4     11
2   C      8      4
4   B      7      3
5   C      3      2

Let’s find out the standard deviation of each key, so we can say for sure that key A is dropped because standard deviation of data1 is less than 2

print(df.groupby('key').std())

        data1     data2
key                    
A    1.414214  2.121320
B    2.121320  5.656854
C    3.535534  1.414214

3.3. Transformation

In the above example, when we applied the aggregate function, we end up with reduced version of the data (For example, in aggregate example above, we end up with 3 rows from 6 rows). However, transformation function/method can return some transformed version of the full data but the output remains the same shape as the input.

Suppose we would like to create a new column that list sum of each key for ‘data1’ and ‘data2’

df_new_col = df.groupby('key').transform('sum')
print(df_new_col)

   data1  data2
0     12     19
1     11     14
2     11      6
3     12     19
4     11     14
5     11      6

3.4. Apply

The apply() method applies function to the group results. In the example below, let me add another column data3 which is sum of corresponding row value of columns data1 and data2

def apply_func(x):
    x['data3'] = x['data1'] + x['data2']
    return x

print(df.groupby('key').apply(apply_func))

  key  data1  data2  data3
0   A      7     11     18
1   B      4     11     15
2   C      8      4     12
3   A      5      8     13
4   B      7      3     10
5   C      3      2      5