Compute Category-wise Daily Differences in Pandas with groupby and diff

Rescue pandas series (10) – Computing row-wise differences for numeric columns by category

Recently I found many colleagues reluctant to use pandas, turning to other data manipulation libraries. As a data professional, I constantly advocate pandas, so I wrote this series to help more people fall in love with pandas.

/ Data Requirement

We have a dataset with a category column, a date column, and numeric columns for which we need to compute differences, e.g., how many more apples were sold on 2022-01-02 than on 2022-01-01.

/ Requirement Breakdown

We need to group by each type and compute values, so first we categorize the types, then evaluate each group to meet the requirement.

/ Solution

Instead of using a for‑loop to index each type and compute differences, pandas provides the groupby function which efficiently separates each type and aggregates results, making it ideal for this requirement.

Method One

# groupby.apply(lambda x: x.diff())
# After aggregation the result is sorted by type and date, so sort the data before groupby
# This prevents misalignment when adding the result back to the original data

data.sort_values(['类型', '日期'], inplace=True)
data[['进货diff', '卖出diff']] = data.groupby(['类型']).apply(lambda x: x[['进货总量', '卖出总量']].diff()).values

The result shows that the first day and subsequent days for each type are calculated correctly.

Method Two

# groupby.diff
# Directly call diff on the selected columns after groupby

data.sort_values(['类型', '日期'], inplace=True)
data[['进货diff', '卖出diff']] = data.groupby(['类型'])[['进货总量', '卖出总量']].diff()

The result is identical to Method One.

Method Three

# groupby.transform  # Returns a DataFrame with the same index as the original
# This method avoids the need to sort the original DataFrame before aggregation

data[['进货diff', '卖出diff']] = data.groupby(['类型']).transform(lambda x: x.diff())[['进货总量', '卖出总量']]

Method Four (custom function)

import numpy as np
import pandas as pd

def get_data_diff(data: pd.DataFrame, group_cols: list, val_cols: list, diff_cols: list, ascending: bool = True):
    """
    Custom function to compute diff for a DataFrame.
    :param data: pd.DataFrame
    :param group_cols: list of columns to group by
    :param val_cols: list of columns to sort by
    :param diff_cols: list of columns to compute diff on
    :param ascending: sorting order, default True
    :return: DataFrame with diff columns
    """
    # Preserve original index
    datac = data.reset_index().copy()
    index_colname = datac.columns[0]
    # Sort the DataFrame
    datac.sort_values(group_cols + val_cols, ascending=ascending, inplace=True)
    # Compute diff
    datac[[f'{i}_diff' for i in diff_cols]] = datac[diff_cols].diff()
    # Rank within each group
    rank0 = np.hstack(datac.value_counts(group_cols, sort=False).map(lambda x: range(x)).values)
    # Replace the first value of each group with NaN
    datac.loc[rank0 == 0, [f'{i}_diff' for i in diff_cols]] = np.nan
    # Restore original index
    datac.index = datac[index_colname].values
    del datac[index_colname]
    return datac

get_data_diff(df, ['类型'], ['日期'], ['进货总量', '卖出总量'])

This custom function also achieves the desired result.

/ Summary

Using pandas' diff for row‑wise difference calculations is very convenient; when category‑wise computation is needed, simply call groupby before diff. Although applying a custom lambda via apply works, it can be slower, so prefer built‑in methods for faster results.

久旱逢甘露，滴水当涌泉。

Written on March 30, 2022