Pandas DataFrame中按Subject保留与首次AD记录最近的CN记录的处理需求问询

Hey there! Let's work through this problem step by step to get exactly the result you need. Here's a practical, pandas-based solution tailored to your requirements:

Step 1: Set up the initial DataFrame #

First, let's start with your original dataset:

import pandas as pd

df = pd.DataFrame({'Subject': [1,1,1,2,2,2,3,3,3,4,4,4,5,5,5,5], 
                   'dx1': ['CN','AD','AD','CN','UNC','AD','AD','CN','CN','CN','AD','CN','CN','CN','AD','AD']})

Step 2: Locate the first 'AD' occurrence per Subject #

We need a reference point: the index of the first time 'AD' appears for each Subject. This is what we'll use to find the closest 'CN' rows:

# Get the index of the first 'AD' entry for each Subject
first_ad_indices = df[df['dx1'] == 'AD'].groupby('Subject').idxmin()['dx1']

Step 3: Mark rows to retain #

Now we'll iterate over each Subject, calculate how far each 'CN' row is from the first 'AD', and mark the closest ones. We'll also mark all 'AD' rows since they're our core reference points:

# Initialize a column to track which rows to keep
df['retain'] = False

# First, mark all 'AD' rows as keepers
df.loc[df['dx1'] == 'AD', 'retain'] = True

# Process each Subject to find the closest 'CN' rows
for subject, ad_idx in first_ad_indices.items():
    # Filter to get only 'CN' rows for the current Subject
    cn_rows = df[(df['Subject'] == subject) & (df['dx1'] == 'CN')]
    
    if not cn_rows.empty:
        # Calculate absolute distance between each 'CN' row and the first 'AD'
        cn_rows['distance'] = abs(cn_rows.index - ad_idx)
        # Find the smallest distance value
        min_distance = cn_rows['distance'].min()
        # Get indices of all 'CN' rows that have this minimum distance
        closest_cn_indices = cn_rows[cn_rows['distance'] == min_distance].index
        # Mark these rows to retain
        df.loc[closest_cn_indices, 'retain'] = True

Step 4: Check the final result #

If you print the DataFrame, you'll see the retain column correctly flags the rows we need:

print(df)

Output:

Subject dx1  retain
0        1  CN    True
1        1  AD    True
2        1  AD    True
3        2  CN    True
4        2  UNC   False
5        2  AD    True
6        3  AD    True
7        3  CN    True
8        3  CN   False
9        4  CN    True
10       4  AD    True
11       4  CN    True
12       5  CN   False
13       5  CN    True
14       5  AD    True
15       5  AD    True

Verification against your requirements: #

• Subject 1: The 'CN' row immediately precedes the first 'AD'—marked as retain.
• Subject 2: The only 'CN' row is two positions before the first 'AD' (with 'UNC' in between)—marked as retain.
• Subject 3: The first 'CN' row right after the first 'AD' is the closest—marked as retain, the second 'CN' is farther so it's excluded.
• Subject 4: Both 'CN' rows are one position away from the first 'AD' (one before, one after)—both marked as retain.
• Subject 5: The second 'CN' row is immediately before the first 'AD'—marked as retain, the first 'CN' is farther so it's excluded.

This solution handles all edge cases you noted, including multiple 'CN' entries per Subject!

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