How to group and aggregate transactional data using Pandas

Transactional item data can be used to create a number of other useful datasets to help you analyse ecommerce products and customers. From the core list of items purchased you can create additional datasets containing order data, customer data, and product data, which can be used for analysis, reporting, or modeling.

Pandas makes it really straightforward to perform this process, thanks to its helpful groupby() and agg() functions which allow you to group by a given column and then calculate aggregate statistics on the selected data. As well as standard metrics, such as sum, count, min, max, nunique, and std, you can also pass in Numpy functions or create your own custom metrics. Here’s how it’s done.

Load the order items

The data set I’m using here is the Online Retail dataset from the UCI Machine Learning Repository. It’s typical of the transaction item data set that you’ll deal with every day if you work in ecommerce and contains one line per SKU, with details on the order ID, the customer ID, order date and the price paid. To tidy it up, I’ve renamed some of the columns and skipped the first row of headers.

import pandas as pd

df = pd.read_csv('online-retail.csv', 
                 names=['invoice', 'sku', 'description', 'qty', 'order_date', 
                        'unit_price', 'customer_id', 'country'],
                low_memory=False, 
                skiprows=1)
df.head()

Feature engineering

This data set doesn’t include a line price, so to save hassle in later steps, we will first calculate this by multiplying the qty field by the unit_price field. The order_date field is also an object instead of a datetime, so we can reset this to the correct type using the Pandas to_datetime() function.

df['line_price'] = df['qty'] * df['unit_price']
df['order_date'] = pd.to_datetime(df['order_date'])

df.head()

Create an orders dataframe

From our single dataframe of transactional items, we can now using grouping and aggregation to create other dataframes containing data on orders, customers, and products. To create the orders dataframe, we first groupby() the invoice column and then use the agg() function to calculate new metrics for each of the columns of interest.

To calculate the revenue on each order we use the revenue=('line_price', 'sum') argument, which calculates the sum of the line_price column. We’ll calculate the number of unique SKUs in the order with nunique and the total number of items by calculating the sum of the qty field.

df_orders = df.groupby('invoice').agg(
    revenue=('line_price', 'sum'),
    skus=('sku', 'nunique'),
    items=('qty', 'sum'),
)

df_orders.head()

Create a customers dataframe

This should be pretty self explanatory, as we’re just repeating the above approach to create a customers dataframe. This groups on the customer_id and then uses agg() to calculate the total revenue, the number of unique orders, the number of unique SKUs, the total number of units, and the first and last date of the customers orders.

df_customers = df.groupby('customer_id').agg(
    revenue=('line_price', 'sum'),
    orders=('invoice', 'nunique'),
    skus=('sku', 'nunique'),
    units=('qty', 'sum'),
    first_order_date=('order_date', 'min'),
    last_order_date=('order_date', 'max')    
).reset_index().sort_values(by='revenue', ascending=False)

df_customers.head()

Create a products dataframe

Finally, to create a products dataframe, we can groupby() the sku column and use the agg() function to calculate the total number of units sold, the average number of units purchased by each customer, the average line price paid, the total number of unique invoices and the total number of customers who’ve purchased the product.

df_products = df.groupby('sku').agg(
    units=('qty', 'sum'),
    avg_units=('qty', 'mean'),    
    revenue=('line_price', 'sum'),
    orders=('invoice', 'nunique'),
    customers=('customer_id', 'nunique'),
)

df_products.head()

Matt Clarke, Saturday, March 06, 2021