29. Pandas: groupby

By Bernd Klein. Last modified: 21 Feb 2024.

This chapter of our Pandas tutorial deals with an extremely important functionality, i.e. groupby. It is not really complicated, but it is not obvious at first glance and is sometimes found to be difficult. Completely wrong, as we shall see. It is also very important to become familiar with 'groupby' because it can be used to solve important problems that would not be possible without it. The Pandas groupby operation involves some combination of splitting the object, applying a function, and combining the results. We can split a DataFrame object into groups based on various criteria and row and column-wise, i.e. using axis.

'Applying' means

• to filter the data,
• transform the data or
• aggregate the data.

groupby can be applied to Pandas Series objects and DataFrame objects! We will learn to understand how it works with many small practical examples in this tutorial.

goupby with Series

We create with the following Python program a Series object with an index of size nvalues. The index will not be unique, because the strings for the index are taken from the list fruits, which has less elements than nvalues:

import pandas as pd
import numpy as np
import random

nvalues = 30
# we create random values, which will be used as the Series values:
values = np.random.randint(1, 20, (nvalues,))
fruits = ["bananas", "oranges", "apples", "clementines", "cherries", "pears"]
fruits_index = np.random.choice(fruits, (nvalues,))

s = pd.Series(values, index=fruits_index)
print(s[:10])

pears           3
pears           1
oranges        14
clementines    15
bananas        18
oranges         3
bananas         9
cherries        3
cherries        4
apples         12
dtype: int64

grouped = s.groupby(s.index)
grouped

<pandas.core.groupby.generic.SeriesGroupBy object at 0x7f85100f1b90>

We can see that we get a SeriesGroupBy object, if we apply groupby on the index of our series object s. The result of this operation grouped is iterable. In every step we get a tuple object returned, which consists of an index label and a series object. The series object is s reduced to this label.

grouped = s.groupby(s.index)

for fruit, s_obj in grouped:
    print(f"===== {fruit} =====")
    print(s_obj)

===== apples =====
apples    12
apples     9
apples    11
dtype: int64
===== bananas =====
bananas    18
bananas     9
bananas    12
bananas    15
bananas     5
dtype: int64
===== cherries =====
cherries     3
cherries     4
cherries     3
cherries    13
cherries    18
cherries     2
cherries    11
dtype: int64
===== clementines =====
clementines    15
clementines     7
clementines    18
clementines     9
clementines    18
clementines     4
dtype: int64
===== oranges =====
oranges    14
oranges     3
oranges    16
oranges     9
oranges     3
dtype: int64
===== pears =====
pears     3
pears     1
pears     4
pears    19
dtype: int64

We could have got the same result - except for the order - without using `` groupby '' with the following Python code.

for fruit in set(s.index):
    print(f"===== {fruit} =====")
    print(s[fruit])

===== clementines =====
clementines    15
clementines     7
clementines    18
clementines     9
clementines    18
clementines     4
dtype: int64
===== bananas =====
bananas    18
bananas     9
bananas    12
bananas    15
bananas     5
dtype: int64
===== pears =====
pears     3
pears     1
pears     4
pears    19
dtype: int64
===== oranges =====
oranges    14
oranges     3
oranges    16
oranges     9
oranges     3
dtype: int64
===== cherries =====
cherries     3
cherries     4
cherries     3
cherries    13
cherries    18
cherries     2
cherries    11
dtype: int64
===== apples =====
apples    12
apples     9
apples    11
dtype: int64

Live Python training

Enjoying this page? We offer live Python training courses covering the content of this site.

See: Live Python courses overview

Enrol here

groupby with DataFrames

We will start with a very simple DataFrame. The DataFRame has two columns one containing names Name and the other one Coffee contains integers which are the number of cups of coffee the person drank.

import pandas as pd
beverages = pd.DataFrame({'Name': ['Robert', 'Melinda', 'Brenda',
                                   'Samantha', 'Melinda', 'Robert',
                                   'Melinda', 'Brenda', 'Samantha'],
                          'Coffee': [3, 0, 2, 2, 0, 2, 0, 1, 3],
                          'Tea':    [0, 4, 2, 0, 3, 0, 3, 2, 0]})
    
beverages

It's simple, and we've already seen in the previous chapters of our tutorial how to calculate the total number of coffee cups. The task is to sum a column of a DatFrame, i.e. the 'Coffee' column:

beverages['Coffee'].sum()

13

Let's compute now the total number of coffees and teas:

beverages[['Coffee', 'Tea']].sum()

Coffee    13
Tea       14
dtype: int64

'groupby' has not been necessary for the previous tasks. Let's have a look at our DataFrame again. We can see that some of the names appear multiple times. So it will be very interesting to see how many cups of coffee and tea each person drank in total. That means we are applying 'groupby' to the 'Name' column. Thereby we split the DatFrame. Then we apply 'sum' to the results of 'groupby':

res = beverages.groupby(['Name']).sum()
print(res)

          Coffee  Tea
Name                 
Brenda         3    4
Melinda        0   10
Robert         5    0
Samantha       5    0

We can see that the names are now the index of the resulting DataFrame:

print(res.index)

Index(['Brenda', 'Melinda', 'Robert', 'Samantha'], dtype='object', name='Name')

There is only one column left, i.e. the Coffee column:

print(res.columns)

Index(['Coffee', 'Tea'], dtype='object')

We can also calculate the average number of coffee and tea cups the persons had:

beverages.groupby(['Name']).mean()

Another Example

The following Python code is used to create the data, we will use in our next groupby example. It is not necessary to understand the following Python code for the content following afterwards. The module faker has to be installed. In cae of an Anaconda installation this can be done by executing one of the following commands in a shell:

conda install -c conda-forge faker
conda install -c conda-forge/label/gcc7 faker
conda install -c conda-forge/label/cf201901 faker
conda install -c conda-forge/label/cf202003 faker 

from faker import Faker
import numpy as np
from itertools import chain

fake = Faker('de_DE')

number_of_names = 10
names = []
for _ in range(number_of_names):
    names.append(fake.first_name())


data = {}
workweek = ("Monday", "Tuesday", "Wednesday", "Thursday", "Friday")
weekend = ("Saturday", "Sunday")

for day in chain(workweek, weekend):
    data[day] = np.random.randint(0, 10, (number_of_names,))
    
data_df = pd.DataFrame(data, index=names)
data_df

print(names)

['Kristina', 'Zofia', 'Sahin', 'Giuseppe', 'Ulrich', 'Hans-Michael', 'Karl-Werner', 'Ludmila', 'Peter', 'Margret']

names = ('Ortwin', 'Mara', 'Siegrun', 'Sylvester', 'Metin', 'Adeline', 'Utz', 'Susan', 'Gisbert', 'Senol')
data = {'Monday': np.array([0, 9, 2, 3, 7, 3, 9, 2, 4, 9]),
        'Tuesday': np.array([2, 6, 3, 3, 5, 5, 7, 7, 1, 0]),
        'Wednesday': np.array([6, 1, 1, 9, 4, 0, 8, 6, 8, 8]),
        'Thursday': np.array([1, 8, 6, 9, 9, 4, 1, 7, 3, 2]),
        'Friday': np.array([3, 5, 6, 6, 5, 2, 2, 4, 6, 5]),
        'Saturday': np.array([8, 4, 8, 2, 3, 9, 3, 4, 9, 7]),
        'Sunday': np.array([0, 8, 7, 8, 9, 7, 2, 0, 5, 2])}

data_df = pd.DataFrame(data, index=names)
data_df

We will demonstrate with this DataFrame how to combine columns by a function.

def is_weekend(day):
    if day in {'Saturday', 'Sunday'}:
        return "Weekend"
    else:
        return "Workday"
        

for res_func, df in data_df.T.groupby(by=is_weekend):
    print(df)

          Ortwin  Mara  Siegrun  Sylvester  Metin  Adeline  Utz  Susan  \
Saturday       8     4        8          2      3        9    3      4   
Sunday         0     8        7          8      9        7    2      0   

          Gisbert  Senol  
Saturday        9      7  
Sunday          5      2  
           Ortwin  Mara  Siegrun  Sylvester  Metin  Adeline  Utz  Susan  \
Monday          0     9        2          3      7        3    9      2   
Tuesday         2     6        3          3      5        5    7      7   
Wednesday       6     1        1          9      4        0    8      6   
Thursday        1     8        6          9      9        4    1      7   
Friday          3     5        6          6      5        2    2      4   

           Gisbert  Senol  
Monday           4      9  
Tuesday          1      0  
Wednesday        8      8  
Thursday         3      2  
Friday           6      5  

data_df.T.groupby(by=is_weekend).sum()

Exercises

Exercise 1

Calculate the average prices of the products of the following DataFrame:

import pandas as pd

d = {"products": ["Oppilume", "Dreaker", "Lotadilo", 
                  "Crosteron", "Wazzasoft", "Oppilume", 
                  "Dreaker", "Lotadilo", "Wazzasoft"],
     "colours": ["blue", "blue", "blue", 
                 "green", "blue", "green", 
                 "green", "green", "red"],
     "customer_price": [2345.89, 2390.50, 1820.00, 
                        3100.00, 1784.50, 2545.89,
                        2590.50, 2220.00, 2084.50],
     "non_customer_price": [2445.89, 2495.50, 1980.00, 
                            3400.00, 1921.00, 2645.89, 
                            2655.50, 2140.00, 2190.00]}

product_prices = pd.DataFrame(d)
product_prices

Exercise 2

Calculate the sum of the price according to the colours.

Exercise 3

Read in the project_times.txt file from the data1 directory. This rows of this file contain comma separated the date, the name of the programmer, the name of the project, the time the programmer spent on the project.

Calculate the time spend on all the projects per day

Exercise 4

Create a DateFrame containing the total times spent on a project per day by all the programmers

Exercise 5

Calculate the total times spent on the projects over the whole month.

Exercise 6

Calculate the monthly times of each programmer regardless of the projects

Exercise 7

Rearrange the DataFrame with a MultiIndex consisting of the date and the project names, the columns should be the programmer names and the data of the columns the time of the programmers spent on the projects.

                   time
programmer         Antonie  Elise  Fatima  Hella  Mariola
date     project
2020-01-01 BIRDY   NaN      NaN    NaN     1.50   1.75
           NSTAT   NaN      NaN    0.25    NaN    1.25
           XTOR    NaN      NaN    NaN     1.00   3.50
2020-01-02 BIRDY   NaN      NaN    NaN     1.75   2.00
           NSTAT   0.5      NaN    NaN     NaN    1.75

Replace the NaN values by 0.

Exercise 8:

The folder data contains a file donation.txt with the following data:

firstname,surname,city,job,income,donations
Janett,Schwital,Karlsruhe,Politician,244400,2512
Daniele,Segebahn,Freiburg,Student,16800,336
Kirstin,Klapp,Hamburg,Engineer,116900,1479
Oswald,Segebahn,Köln,Musician,57700,1142

group the data by the job of the persons.

Live Python training

Enjoying this page? We offer live Python training courses covering the content of this site.

See: Live Python courses overview

Upcoming online Courses

Python for Engineers and Scientists

Data Analysis With Python

Enrol here

Solutions

Solution to Exercise 1

average_prices = product_prices.groupby(["products", "colours"]).mean()

print(average_prices)

                   customer_price  non_customer_price
products  colours                                    
Crosteron green           3100.00             3400.00
Dreaker   blue            2390.50             2495.50
          green           2590.50             2655.50
Lotadilo  blue            1820.00             1980.00
          green           2220.00             2140.00
Oppilume  blue            2345.89             2445.89
          green           2545.89             2645.89
Wazzasoft blue            1784.50             1921.00
          red             2084.50             2190.00

Solution to Exercise 2

x = product_prices[['colours', 'customer_price', 'non_customer_price']].groupby('colours').sum()
x

# better
sum_of_prices = product_prices.drop(columns=["products"]).groupby("colours").sum()

print(sum_of_prices)

         customer_price  non_customer_price
colours                                    
blue            8340.89             8842.39
green          10456.39            10841.39
red             2084.50             2190.00

# or
product_prices[['colours', 'customer_price', 'non_customer_price']].groupby("colours").sum()

Solution to Exercise 3

import pandas as pd

df = pd.read_csv("../data1/project_times.txt", index_col=0)
df

times_per_day = df['time'].groupby(df.index).sum()
print(times_per_day[:10])

date
2020-01-01     9.25
2020-01-02     6.00
2020-01-03     2.50
2020-01-06     5.75
2020-01-07    15.00
2020-01-08    13.25
2020-01-09    10.25
2020-01-10    17.00
2020-01-13     4.75
2020-01-14    10.00
Name: time, dtype: float64

Solution to Exercise 4

times_per_day_project = df[['project', 'time']].groupby([df.index, 'project']).sum()
print(times_per_day_project[:10])

                    time
date       project      
2020-01-01 BIRDY    3.25
           NSTAT    1.50
           XTOR     4.50
2020-01-02 BIRDY    3.75
           NSTAT    2.25
2020-01-03 BIRDY    1.00
           NSTAT    0.25
           XTOR     1.25
2020-01-06 BIRDY    2.75
           NSTAT    0.75

Solution to Exercise 5

df[['project', 'time']].groupby(['project']).sum()

Solution to Exercise 6

df[['programmer', 'time']].groupby(['programmer']).sum()

Solution to Exercise 7

x = df.groupby([df.index, 'project', 'programmer']).sum()

x = x.unstack()
x

x = x.fillna(0)
print(x[:10])

                      time                           
programmer         Antonie Elise Fatima Hella Mariola
date       project                                   
2020-01-01 BIRDY      0.00   0.0   0.00  1.50    1.75
           NSTAT      0.00   0.0   0.25  0.00    1.25
           XTOR       0.00   0.0   0.00  1.00    3.50
2020-01-02 BIRDY      0.00   0.0   0.00  1.75    2.00
           NSTAT      0.50   0.0   0.00  0.00    1.75
2020-01-03 BIRDY      0.00   0.0   1.00  0.00    0.00
           NSTAT      0.25   0.0   0.00  0.00    0.00
           XTOR       0.00   0.0   0.00  0.50    0.75
2020-01-06 BIRDY      0.00   0.0   0.00  2.50    0.25
           NSTAT      0.00   0.0   0.00  0.00    0.75

Solution to Exercise 8:

import pandas as pd

data = pd.read_csv('../data/donations.txt')
data

data_sum = data[['job', 'income', 'donations']].groupby(['job']).sum()
data_sum.sort_values(by='donations')

data_sum['relative'] = data_sum.donations * 100 / data_sum.income

data_sum.sort_values(by='relative')

Live Python training

Enjoying this page? We offer live Python training courses covering the content of this site.

See: Live Python courses overview

Upcoming online Courses

Python for Engineers and Scientists

Data Analysis With Python

Enrol here