Compute Category‑Based Row Differences in Pandas Using groupby and diff

Rescue pandas Plan (10) – Computing Row Differences for Numeric Columns by Category

Many data practitioners still hesitate to use pandas, preferring other libraries; this series aims to demonstrate pandas' power and help users fall in love with it.

Data Requirements

The dataset contains a category column, a date column, and numeric columns whose day‑to‑day differences need to be computed, 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, then compute the differences within each group.

Solution

Instead of looping, pandas provides the groupby function, which efficiently separates each type and aggregates the results.

Method 1

# groupby.apply(lambda x: x.diff())
# In the aggregated result the type and date are sorted ascending, 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 first day and subsequent days produce correct differences for each type.

Method 2

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

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

The result matches Method 1.

Method 3

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

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

Method 4 (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 diff calculation for a DataFrame.
    :param data: pd.DataFrame
    :param group_cols: columns to group by
    :param val_cols: columns to sort by
    :param diff_cols: columns to compute diff on
    :param ascending: sort order, default True
    :return: DataFrame with diff columns
    """
    # Preserve original index
    datac = data.reset_index().copy()
    index_colname = datac.columns[0]
    # Sort the data
    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()
    # Assign NaN to the first value of each group
    rank0 = np.hstack(datac.value_counts(group_cols, sort=False).map(lambda x: range(x)).values)
    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.

Conclusion

Using pandas' diff together with groupby makes calculating row‑wise differences across categories straightforward. While apply with a lambda works, it can be slower; built‑in methods like diff, transform, or a well‑defined helper function are preferred for performance.

May the rain finally come after a long drought.

Written on 2022‑03‑30