请求协助：用Pandas实现多条件分组统计与筛选（附SQL参考）

To replicate your SQL CTE logic in Pandas, we'll break the process into three core steps: creating conditional flags, grouping to calculate targeted counts, and filtering results to meet your criteria. Here's a straightforward implementation:

Step-by-Step Implementation #

First, let's start with your provided test dataset:

import pandas as pd

# Test dataset
a = pd.DataFrame()
a['client'] = range(35)
a['high'] = ['02','47','47','47','79','01','43','56','46','47','17','58','42','90','47','86','41','56', '55','49','47','49','95','23','46','47','80','80','41','49','46','49','56','46','31']
a['qr'] = ['1','1','1','1','2','1','1','2','2','1','1','2','2', '2','1','1','1','2','1','2','1','2','2','1','1','1','2','2','1','1', '1','1','1','1','2']
a['now'] = ['0','0','0','0','0','0','0','0','0','0','0','0','1','0','0','0','0', '0','0','0','0','0','0','0','0','0','0','0','0','0','0','1','0','0','0']

1. Create Conditional Flags

We'll add boolean columns to mark rows that match your qr and now criteria. This makes it easy to sum valid records later:

# Flag rows where qr=1 AND now=1
a['q1_bad_flag'] = (a['qr'] == '1') & (a['now'] == '1')
# Flag rows where qr=2 AND now=1
a['q2_bad_flag'] = (a['qr'] == '2') & (a['now'] == '1')

2. Group by high and Calculate Counts

Use groupby and agg to sum the flags (since True equals 1 in arithmetic operations) for each high group:

grouped_stats = a.groupby('high').agg(
    q1_bad=('q1_bad_flag', 'sum'),  # Total qr=1 & now=1 per high group
    q2_bad=('q2_bad_flag', 'sum')   # Total qr=2 & now=1 per high group
).reset_index()

3. Filter Groups to Meet Your Criteria

Finally, filter the grouped results to keep only groups where both counts meet the threshold and high is not null:

filtered_highs = grouped_stats[
    (grouped_stats['q1_bad'] >= 2) & 
    (grouped_stats['q2_bad'] >= 2) & 
    (grouped_stats['high'].notna())
]

print(filtered_highs)

Notes on the Test Data #

With your provided test dataset, this will return an empty DataFrame. Looking at the data, there's only one row where now=1 for qr=1 (high='49') and one row where now=1 for qr=2 (high='42'). No high group meets the threshold of 2 or more for both counts.

Alternative: No Intermediate Columns #

If you prefer not to modify the original DataFrame, you can compute the counts directly in the agg function:

grouped_stats = a.groupby('high').agg(
    q1_bad=pd.NamedAgg(
        column='now',
        aggfunc=lambda x: ((a.loc[x.index, 'qr'] == '1') & (x == '1')).sum()
    ),
    q2_bad=pd.NamedAgg(
        column='now',
        aggfunc=lambda x: ((a.loc[x.index, 'qr'] == '2') & (x == '1')).sum()
    )
).reset_index()

# Apply the same filter as before
filtered_highs = grouped_stats[
    (grouped_stats['q1_bad'] >= 2) & 
    (grouped_stats['q2_bad'] >= 2) & 
    (grouped_stats['high'].notna())
]

This achieves the same result without adding columns to your original dataset.

内容的提问来源于stack exchange，提问作者Semyon-coder