AddRowCountColumn — Analysis Tool Box

Introduction

Ranking the top 3 products per category, numbering a customer's orders chronologically, or identifying the first record in each group — these all require a SQL-style ROW_NUMBER() within groups, something pandas doesn't provide directly. AddRowCountColumn answers "where does this row fall in its group's order?" by assigning a sequential count per group according to whatever sort order you specify. Reach for it whenever the analytic question is about position or rank within a segment, not just the segment's aggregate.

Teaching Note

The function is particularly useful for:

Ranking records within groups (e.g., top 3 products per category)
Creating sequential identifiers within partitions
Implementing pagination or batch processing logic
Identifying first/last occurrences within groups
Generating ordered indices for time series analysis within entities
Implementing custom sorting and ranking logic

Parameters

Parameter	Default	Description
`dataframe`required	—	A pandas DataFrame to which the row count column will be added.
`list_of_grouping_variables`required	—	List of column names that define the groups. Rows with the same values across these columns will be grouped together, and row counting will restart at 0 for each unique group combination.
`list_of_order_columns`required	—	List of column names that determine the sorting order within each group. Multiple columns create a hierarchical sort (first column has priority, then second, etc.).
`list_of_ascending_order_args`	`None`	List of boolean values corresponding to each column in list_of_order_columns, specifying whether to sort in ascending (True) or descending (False) order. If None, all columns are sorted in ascending order.
`row_count_column_name`	`'Row Count'`	Name for the new row count column that will be added to the DataFrame.

Returns

The input DataFrame with an additional column containing the row count within each group. The DataFrame is sorted by the grouping variables and the row count column. Row counts start at 0 for the first row in each group.

Example

python

from analysistoolbox.data_processing import AddRowCountColumn
import pandas as pd

# Rank sales transactions by amount within each store
sales = pd.DataFrame({
    'store': ['A', 'A', 'A', 'B', 'B', 'B'],
    'amount': [100, 250, 150, 300, 200, 180]
})
sales = AddRowCountColumn(
    sales,
    list_of_grouping_variables=['store'],
    list_of_order_columns=['amount'],
    list_of_ascending_order_args=[False],  # Highest amounts first
    row_count_column_name='Sales Rank'
)