Label TrendUp, TrendDown, or NoTrend in a pandas DataFrame using vectorized run-length blocks

Learn a vectorized pandas approach to label consecutive increases and decreases. Use groupby.transform to tag TrendUp, TrendDown, or NoTrend by threshold.

Marking stretches of consecutive growth and decline in a pandas DataFrame sounds simple until you need clean, vectorized logic that scales. The task: label ranges as TrendUp or TrendDown once a run of increasing or decreasing values reaches a threshold (five or more), starting from the point where the run begins, and fallback to NoTrend when neither condition is met. Below is a compact way to do it without manual iteration over rows.

Dataset and baseline setup

We start with a single numeric column and derive two helper columns that count consecutive increases and decreases. This establishes the context for computing the final Trend label.

import pandas as pd
import numpy as np
payload = {'col1': [234,321,284,286,287,300,301,303,305,299,288,300,299,287,286,280,279,270,269,301]}
table = pd.DataFrame(data=payload)
ups = []
downs = []
arr = np.array(table['col1'])
for j in range(len(table)):
    ups.append(0)
    downs.append(0)
    if arr[j] > arr[j-1]:
        ups[j] = ups[j-1] + 1
    else:
        ups[j] = 0
    if arr[j] < arr[j-1]:
        downs[j] = downs[j-1] + 1
    else:
        downs[j] = 0
table['cnsIncr'] = ups
table['cnsDecr'] = downs

What the problem really is

The goal isn’t to count runs per se; it’s to tag the entire stretch leading up to and including the first point where the run length crosses the threshold. In other words, once a run of increases reaches five or more, the whole block from the reset (where the counter was 0) to that point should be labeled TrendUp. The same applies to decreases and TrendDown. If neither side reaches the threshold anywhere, everything is NoTrend.

The useful observation is that these run counters form blocks separated by resets at zeros. Within each block, the last value tells us whether the block reaches the threshold. If it does, we assign the same label to the entire block.

Solution with groupby.transform

With that in mind, the implementation becomes straightforward: detect blocks between zeros, compute the last run length per block, compare it to the threshold, and broadcast the decision back to all rows of the block. No explicit Python loops for the labeling step are needed.

MIN_RUN = 5
# group ids: each stretch starts where the counter is 0
grp_up = table['cnsIncr'].eq(0).cumsum()
grp_dn = table['cnsDecr'].eq(0).cumsum()
# does the last value in the block reach the threshold?
mask_up = table['cnsIncr'].groupby(grp_up).transform('last').ge(MIN_RUN)
mask_dn = table['cnsDecr'].groupby(grp_dn).transform('last').ge(MIN_RUN)
# final labeling: Up first, then Down, otherwise NoTrend
table['Trend'] = np.select([mask_up, mask_dn], ['TrendUp', 'TrendDown'], 'NoTrend')

This matches the stated rules: TrendUp when cnsIncr has a value greater or equal to 5 within a block, TrendDown under the same condition for cnsDecr, and NoTrend otherwise.

Why this works

Each run counter (cnsIncr and cnsDecr) increases monotonically inside a streak and resets to zero at the start of the next one. Calling eq(0).cumsum() converts those resets into stable group identifiers. Within each group, transform('last') gives the terminal run length for that block. Comparing it to the threshold tells you whether the entire block should be labeled, and np.select broadcasts that decision row-wise in a single pass.

Why it’s worth knowing

This pattern scales cleanly and keeps your logic declarative. It avoids row-wise loops for labeling, relies on stable pandas primitives like groupby and transform, and mirrors how many time series and event segmentation tasks can be structured. The same thinking—identify blocks, compute a summary per block, broadcast back—applies broadly to run-length classification, streak tagging, and threshold-based regime detection.

Takeaways

When you need to tag ranges based on consecutive behavior, define blocks by reset points, compute a block-level decision once, and push it back to all rows in the block. Here, that yields a concise Trend column with TrendUp, TrendDown, or NoTrend in a vectorized, readable way, staying true to the original rules: greater or equal to the threshold and starting from the reset that kicked off the streak.