Python中如何基于LOWVAL和HIGHVAL列范围对DataFrame分组聚合？

Problem Statement #

I've been struggling to get the desired output after multiple attempts, so I'm reaching out for help from Python experts. I have a DataFrame df structured like this:

VARIABLES  Estimate  LOWVAL  HIGHVAL
0   ppseg12   -0.1753      20       51
1   ppseg12   -0.1753      61       70
2   ppseg12   -0.1753      71       80
3   ppseg12   -0.1753      81       90
4   ppseg12    0.1153      91      100

I need to group the data based on the ranges in LOWVAL and HIGHVAL with the following aggregation logic:

• Rule 1: For rows with the same VARIABLES and Estimate, if the ranges are continuous (e.g., 61-70, 71-80, 81-90), merge them into a single row using the minimum LOWVAL and maximum HIGHVAL of the continuous group.
• Rule 2: For rows with the same VARIABLES and Estimate but non-continuous ranges, keep them as separate groups (using min LOWVAL and max HIGHVAL for each non-continuous block).
• Rule 3: Rows with different Estimate values should be treated as separate groups, even if ranges are adjacent.

My desired output df1 is:

VARIABLES  Estimate  LOWVAL  HIGHVAL
0   ppseg12   -0.1753      61       90
1   ppseg12   -0.1753      20       51
2   ppseg12    0.1153      91      100

Solution #

Here's a step-by-step approach to achieve this using pandas:

Step 1: Sort the DataFrame #

First, we need to sort the data by VARIABLES, Estimate, and LOWVAL to ensure we can correctly identify continuous ranges.

import pandas as pd

# Sample data
data = {
    'VARIABLES': ['ppseg12']*5,
    'Estimate': [-0.1753, -0.1753, -0.1753, -0.1753, 0.1153],
    'LOWVAL': [20, 61, 71, 81, 91],
    'HIGHVAL': [51, 70, 80, 90, 100]
}
df = pd.DataFrame(data)

# Sort the DataFrame
df_sorted = df.sort_values(by=['VARIABLES', 'Estimate', 'LOWVAL']).reset_index(drop=True)

Step 2: Identify Continuous Ranges #

We'll create a helper column to mark when a new non-continuous group starts. A range is continuous if the current row's LOWVAL is exactly one more than the previous row's HIGHVAL.

# Create a flag for non-continuous groups
df_sorted['is_continuous'] = df_sorted.groupby(['VARIABLES', 'Estimate'])['LOWVAL'].diff() == df_sorted['HIGHVAL'].shift() + 1

# Fill the first row of each group with False (since there's no previous row)
df_sorted['is_continuous'] = df_sorted['is_continuous'].fillna(False)

# Create a group ID by cummulating the inverse of the continuous flag
df_sorted['group_id'] = (~df_sorted['is_continuous']).cumsum()

Step 3: Aggregate the Groups #

Now we can group by VARIABLES, Estimate, and group_id, then aggregate to get the min LOWVAL and max HIGHVAL for each group.

# Aggregate to get the desired output
df1 = df_sorted.groupby(['VARIABLES', 'Estimate', 'group_id'], as_index=False).agg(
    LOWVAL=('LOWVAL', 'min'),
    HIGHVAL=('HIGHVAL', 'max')
).drop(columns='group_id')

# Reorder if needed to match the desired output (optional)
df1 = df1.sort_values(by=['Estimate', 'LOWVAL'], ascending=[True, False]).reset_index(drop=True)

print(df1)

Output #

VARIABLES  Estimate  LOWVAL  HIGHVAL
0   ppseg12   -0.1753      61       90
1   ppseg12   -0.1753      20       51
2   ppseg12    0.1153      91      100

Explanation #

• Sorting: Ensures we process ranges in order, which is critical for detecting continuity.
• Continuous Flag: The is_continuous column checks if the current range starts right after the previous one ends.
• Group ID: By cumulatively summing the inverse of the continuous flag, we assign a unique ID to each non-continuous block within the same VARIABLES and Estimate group.
• Aggregation: Finally, we group by these IDs to merge continuous ranges into single rows, while keeping non-continuous ranges separate.

内容的提问来源于stack exchange，提问作者Deepak Kumar