Sum Values of Consecutive Duplicates in Python Pandas DataFrame using groupby()

To sum the values of consecutive duplicates in a Pandas DataFrame, you must first identify blocks of identical adjacent rows using shift() to create a grouping key. Then, apply groupby() on this key combined with an index reset, and aggregate the target column using sum(). This approach efficiently collapses sequential identical entries into single rows with summed metrics.

Understanding Consecutive Duplicates

In data analysis, it is common to encounter datasets where multiple rows contain identical information for a specific period or category. For example, a sales log might list the same product ID and name for every transaction within a day. Standard groupby() operations group all instances of a value globally, regardless of their position. However, "consecutive duplicates" refers specifically to rows that are identical and appear next to each other in the sorted or original order. Summing these requires a different strategy than standard aggregation.

The core challenge is that Pandas does not have a built-in function called groupby_consecutive. We must manually construct a unique identifier for each block of consecutive identical rows. This involves comparing each row with its predecessor to detect changes in value. When a change is detected, we start a new group. This method ensures that only truly adjacent duplicates are merged, preserving the temporal or sequential integrity of the data.

Step-by-Step Implementation

The most robust way to solve this problem involves creating a helper column that marks the boundaries between different consecutive groups. Here is the logical flow:

1. Create a Comparison Column: Use shift(1) on the columns you want to check for duplication. This moves the values down by one row.
2. Detect Changes: Compare the current row's values with the shifted values. If they differ, it indicates the start of a new consecutive block.
3. Generate Group Keys: Create a cumulative sum of these boolean change indicators. Each time a change occurs, the counter increments, creating a unique ID for each block.
4. Aggregate: Group by this new key and the original static columns, then sum the numeric values.

This technique is efficient because it relies on vectorized operations rather than Python loops, which can be slow on large DataFrames. It is particularly useful when dealing with time-series data where events happen in bursts.

Code Example

import pandas as pd

# Sample Data
data = {
    'category': ['A', 'A', 'A', 'B', 'B', 'C'],
    'value': [10, 20, 30, 5, 15, 100]
}
df = pd.DataFrame(data)

# Identify consecutive duplicates
# Create a mask where True means the row is different from the previous one
mask = df['category'] != df['category'].shift(1)

# Create a group ID by taking the cumulative sum of the mask
# The first row is always a new group, so we add 1 or handle index carefully
group_id = mask.cumsum()

# Group by the new ID and the category, then sum the value
result = df.groupby([group_id, 'category'])['value'].sum().reset_index(drop=True)

print(result)

In this code, shift(1) aligns the previous row's category with the current row. The inequality check creates a boolean Series. cumsum() turns this into a running total that increments every time the category changes. This effectively labels the first three 'A's as group 1, the two 'B's as group 2, and the 'C' as group 3.

Handling Multiple Columns

Often, you need to check for duplicates across multiple columns, not just one. In such cases, you can use diff() or compare tuples. A more flexible approach is to select all relevant columns and check if any value has changed.

# Check for changes in both 'category' and 'type'
cols_to_check = ['category', 'type']
mask = (df[cols_to_check] != df[cols_to_check].shift(1)).any(axis=1)
group_id = mask.cumsum()

result = df.groupby([group_id] + cols_to_check).sum()

This pattern scales well. By using any(axis=1), we ensure that if *any* of the monitored columns change, a new group starts. This is crucial for complex logs where metadata might change alongside the primary value.

Common Pitfalls and Solutions

One common error is forgetting to sort the DataFrame before applying this logic. Consecutive duplicates are defined by their adjacency. If your data is unordered, shift() will compare arbitrary rows, leading to incorrect grouping. Always ensure your data is sorted by the relevant keys before identifying consecutive blocks.

Another issue arises with missing values (NaN). NaN != NaN` evaluates to True, which can cause spurious group breaks. You may need to fill NaN values with a placeholder or use fillna() before comparison to ensure consistent behavior.