Groupby Operations:

  • Grouped operations are a powerful way to aggregate, transform, and filter data. They rely on the mantra of “split-apply-combine”.

    1. Data is split into parts based on key(s).
    2. A function is applied to each part of the data.
    3. The results from each part are combined to create a new data set.

  • Your Pipeline:

python
1df
2  .groupby('year')            # split
3  .mean(numeric_only=True)    # apply
4  .reset_index()              # combine
  • This is a powerful concept because parts of your original data can be split up into independent parts to perform a calculation.

Basic One-Variable Grouped Aggregation:

  • Aggregation is the process of taking multiple values and returning a single value.
  • The groupby() method in the following example will essentially repeat the process of splitting the data (i.e., the year column), apply and calculate the mean lifeExp for each year, and then combine the results into a new DataFrame.
    • To start, read the data file into a pandas DataFrame and display the first couple rows and shape:
python
1import pandas as pd
2
3df = pd.read_csv('data/gapminder.tsv', sep='\t')
4print(df.head(2))
5print(df.shape)

Output:

1       country continent  year  lifeExp      pop   gdpPercap
20  Afghanistan      Asia  1952   28.801  8425333  779.445314
31  Afghanistan      Asia  1957   30.332  9240934  820.853030
4(1704, 6)
  • Next, we can check the Data Types of the columns in the DataFrame:
python
6print(df.dtypes)

Output:

1country       object
2continent     object
3year           int64
4lifeExp      float64
5pop            int64
6gdpPercap    float64
7dtype: object
  • Next, we can optionally display the unique values of the column we want to group by.
    • This is sometimes helpful since groupby statements can be thought of as creating a subset of each unique value of a column (or unique pairs from columns)
python
7print(df['year'].unique())

Output:

1[1952 1957 1962 1967 1972 1977 1982 1987 1992 1997 2002 2007]
  • Then, we can group the data by the year column, calculate the mean lifeExp for each year and reset the index of the resulting DataFrame:
    • Inevitable, when using groupby(), you will create a multilevel index or MultiIndex, which can happen in both index and columns. DataFrames with MultiIndexes can be difficult to work with, so it is often a good idea to reset the index.
    • Resetting the index will make the year column a regular column instead of an index column.
    • The as_index=False argument in the groupby() method can be used to ensure that the grouped column (in this case, ‘year’) remains a regular column in the resulting DataFrame rather than becoming the index.
python
8df_grouped = df.groupby('year').mean(numeric_only=True).reset_index()
9print(df_grouped)

Output:

1    year    lifeExp           pop     gdpPercap
20   1952  49.057620  1.695040e+07   3725.276046
31   1957  51.507401  1.876341e+07   4299.408345
42   1962  53.609249  2.042101e+07   4725.812342
53   1967  55.678290  2.265830e+07   5483.653047
64   1972  57.647386  2.518998e+07   6770.082815
75   1977  59.570157  2.767638e+07   7313.166421
86   1982  61.533197  3.020730e+07   7518.901673
97   1987  63.212613  3.303857e+07   7900.920218
108   1992  64.160338  3.599092e+07   8158.608521
119   1997  65.014676  3.883947e+07   9090.175363
1210  2002  65.694923  4.145759e+07   9917.848365
1311  2007  67.007423  4.402122e+07  11680.071820
  • Finally, to remove the unnecessary columns, we can use the drop() method to remove all columns except year and lifeExp:
python
10df_grouped_and_dropped = df_grouped.drop(columns=['pop', 'gdpPercap'])
11print(df_grouped_and_dropped)

Output:

1    year    lifeExp
20   1952  49.057620
31   1957  51.507401
42   1962  53.609249
53   1967  55.678290
64   1972  57.647386
75   1977  59.570157
86   1982  61.533197
97   1987  63.212613
108   1992  64.160338
119   1997  65.014676
1210  2002  65.694923
1311  2007  67.007423

The Top 5 Highest Mean lifeExp Values:

  • To determine the top 5 highest mean lifeExp values, we can sort the DataFrame by lifeExp in descending order and then select the first 5 rows using iloc[:5]:
python
1import pandas as pd
2
3df = pd.read_csv('data/gapminder.tsv', sep='\t')
4
5df_grouped = df.groupby('year').mean(numeric_only=True).reset_index()
6df_grouped_and_dropped = df_grouped.drop(columns=['pop', 'gdpPercap'])
7df_sorted = df_grouped_and_dropped.sort_values(by='lifeExp', ascending=False)
8df_sorted_TopN = df_sorted.iloc[:5]
9print(df_sorted_TopN)

Output:

1    year    lifeExp
211  2007  67.007423
310  2002  65.694923
49   1997  65.014676
58   1992  64.160338
67   1987  63.212613