First Value for Each Group – Pandas Groupby

You can use Pandas groupby to group the underlying data on one or more columns and estimate useful statistics like count, mean, median, std, min, max etc.  Sometimes knowing the first, last, or the nth value in the group might also be useful. In this tutorial we will look at how to get the first value for each group with the help of some examples.

How to get the first value in each group?

You can use the pandas.groupby.first() function or the pandas.groupby.nth(0) function to get the first value in each group. There is a slight difference between the two methods which we have covered at the end of this tutorial. The following is the syntax assuming you want to group the dataframe on column “Col1” and get the first value in the “Col2” for each group.

# using pandas.groupby().first()
df.groupby('Col1')['Col2'].first()
# using pandas.grouby().nth(0)
df.groupby('Col1')['Col2'].nth(0)

Examples

Let’s look at some examples of using the above syntax. First, let’s create a dataframe that we will be using throughout this tutorial to demonstrate the examples.

import pandas as pd

# create a dataframe of GRE scores of two students
df = pd.DataFrame({
    'Name': ['Jim', 'Jim', 'Jim', 'Pam', 'Pam'],
    'Attempt': ['First', 'Second', 'Third', 'First', 'Second'],
    'GRE Score': [298, 321, 314, 318, 330]
})
# display the dataframe
df

Output:

We now have a dataframe containing the GRE scores of two students across their multiple attempts.

1. Using Pandas Groupby First

Let’s get the first “GRE Score” for each student in the above dataframe. For this, we will group the dataframe df on the column “Name”, then apply the first() function on the “GRE Score” column.

# the first GRE score for each student
df.groupby('Name')['GRE Score'].first()

Output:

Name
Jim    298
Pam    318
Name: GRE Score, dtype: int64

We get first “GRE Score” for both Jim and Pam. You can see that Pam scored more than Jim on the first attempt.

2. Using Pandas Groupby nth(0)

The pandas.groupby.nth() function is used to get the value corresponding the nth row for each group. To get the first value in a group, pass 0 as an argument to the nth() function. For example, let’s again get the first “GRE Score” for each student but using the nth() function this time.

# the first GRE score for each student
df.groupby('Name')['GRE Score'].nth(0)

Output:

Name
Jim    298
Pam    318
Name: GRE Score, dtype: int64

We get the same result as above.

Pandas groupby.first() vs groupby.nth(0)

We will use an example to illustrate the difference between the two methods.

Let’s replace the first “GRE Score” for “Jim” with NaN.

import numpy as np

# modfiy the dataframe
df = df.replace(298, np.nan)
# display the dataframe
df

Let’s see what we get with the two methods. First, using pandas.groupby.first()

# the first GRE score for each student
df.groupby('Name')['GRE Score'].first()

Output:

Name
Jim    321.0
Pam    318.0
Name: GRE Score, dtype: float64

You can see that we do not strictly get the first value rather we get the first non-Nan value in each group with the pandas.groupby.first() function.

Now, let’s use the pandas.groupby.nth(0) function.

# the first GRE score for each student
df.groupby('Name')['GRE Score'].nth(0)

Output:

Name
Jim      NaN
Pam    318.0
Name: GRE Score, dtype: float64

You can see that we get the first value in each group irrespective of whether it’s NaN or not.

With this, we come to the end of this tutorial. The code examples and results presented in this tutorial have been implemented in a Jupyter Notebook with a python (version 3.8.3) kernel having numpy version 1.18.5 and pandas version 1.0.5