Two-Variable Grouped Aggregation:

  • To perform aggregate calculations on your data, we must first split the data into relevent groups.
  • The pandas groupby() function splits a DataFrame’s data into subsets that have matching values for one or more columns.
  • For example, we can use groupby() to group a DataFrame by continent and year to calculate the yearly average life expectancy for each continent. Then, we can apply the pandas mean aggregation function to each group.
    • 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 columns 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['continent'].unique())
8print(df['year'].unique())

Output:

1['Asia' 'Europe' 'Africa' 'Americas' 'Oceania']
2[1952 1957 1962 1967 1972 1977 1982 1987 1992 1997 2002 2007]
  • Then, we can group the data by the continent and year columns, calculate the mean lifeExp and reset the index of the resulting DataFrame:
python
9df_grouped = df.groupby(['continent', 'year'])['lifeExp'].mean().reset_index()
10print(df_grouped.head(20))
  • On line 9 above, to group the data by the continent and year columns, the inner square brackets, [‘continent’, ‘year’] are passed as an argument to groupby(). The outer square brackets, such as, [‘lifeExp’] are used to create a list of column names.
    • This is a common pandas operation for subsetting DataFrames to include only specific columns, similar to the lifeExp column in this example.

Output:

1   continent  year    lifeExp
20     Africa  1952  39.135500
31     Africa  1957  41.266346
42     Africa  1962  43.319442
53     Africa  1967  45.334538
64     Africa  1972  47.450942
75     Africa  1977  49.580423
86     Africa  1982  51.592865
97     Africa  1987  53.344788
108     Africa  1992  53.629577
119     Africa  1997  53.598269
1210    Africa  2002  53.325231
1311    Africa  2007  54.806038
1412  Americas  1952  53.279840
1513  Americas  1957  55.960280
1614  Americas  1962  58.398760
1715  Americas  1967  60.410920
1816  Americas  1972  62.394920
1917  Americas  1977  64.391560
2018  Americas  1982  66.228840
2119  Americas  1987  68.090720
  • To add the mean gdpPercap for each continent and year to the DataFrame, the double square brackets [[ ]] below on line 11 are used to select multiple columns from the DataFrame. This specifies a list of column names (in this case, ‘lifeExp’ and ‘gdpPercap’) that we want to include in the resulting DataFrame after performing the groupby operation:
python
11df_grouped = df.groupby(['continent', 'year'])[['lifeExp', 'gdpPercap']].mean().reset_index()