Multiple Ways to Get the First Row for Each Group in Pandas

Use case

Sometimes you just want to capture the first (or last) event of something. Let’s say, you have a list of clients and want to capture their first purchase. This is useful if you want a list of new paying customers.

Dataset

We’re thinking about customers here, so let’s get the Online Retail Dataset from the UCI Machine Learning Repository. We can download this dataset directly using Pandas.

>>> import pandas as pd
>>> customers = pd.read_excel('https://archive.ics.uci.edu/ml/machine-learning-databases/00352/Online%20Retail.xlsx')

Methodology

If you have a good eye you’ll notice that it’s one invoice for multiple products that were purchased by a customer, this is why the values in InvoiceNo are duplicated. There’s a couple of things we can accomplish here:

• Get the first row for every CustomerID
• Get the first invoice for every CustomerID
• Get the total sum for a customer’s first time purchase
• Get the first purchase of the day

Get the first row for every CustomerID

Here’s a neat trick using the methods groupby and head to get the first row of a group:

customers.groupby('CustomerID').head(1)

You’ll notice that the values in CustomerID are unique and only the first row is presented.

Get the first invoice for every CustomerID

It’s great to have the first row, however, you’ll notice that in our dataset the values in InvoiceNo are repeated, this is because many customers buy multiple things in one transaction. So, it doesn’t make sense in a real scenario to filter only the first row.

Instead, we want to keep every item from the first invoice only. We can do that by making a list of the first transaction by customer and then applying a mask to our dataset:

# Create a list of the first invoice by client
first_invoices = customers.groupby(['CustomerID']).InvoiceNo.first().to_list()

# Filter first invoices by client
customers[customers.InvoiceNo.isin(first_invoices)]

Get the total sum for a customer’s first time purchase

What we want to do in this context is to summarize the customer’s first purchase by multiplying the item Quantity times the UnitPrice and storing the operation result in Total.

Because we’re only interested in the first purchase, we’ll continue to use the dataframe first_purchase that we just created.

# Filter first invoices by client
first_purchase = customers[customers.InvoiceNo.isin(first_invoices)].copy()

first_purchase['Total'] = first_purchase.Quantity * first_purchase.UnitPrice
first_purchase[['CustomerID', 'Description', 'Quantity', 'UnitPrice', 'Total']]

Now we can see how much users spent on their first purchase.

first_purchase.groupby(['CustomerID']).Total.sum()

Get the first purchase of the day

To achieve this we will do the following:

• Sort values by date: Just in case our dataset is not sorted by time.
• Extract the date from InvoiceNo: We will want to remove the hour from the timestamp.
• Keep only one row per day: We will remove duplicates from the date column and keep the first one only.
• Drop the date column: We won’t need it anymore, so we’ll drop it.

Here’s how it works:

customers.sort_values('InvoiceDate', inplace=True)
customers['date'] = customers.InvoiceDate.dt.date
customers.drop_duplicates('date', keep='first', inplace=True)
customers.drop('date', axis=1, inplace=True)
customers

Notice how there’s only one row per day.

Conclusion

That’s it, you know now some quick methods to get the first event of something. Make sure to follow me in Twitter if you haven’t and want to be updated of my future posts: Follow @__franccesco