需求：为Dataframe实现ID下子ID新增与丢失的标识功能

Hey there! Let's work through how to track daily child ID additions and deletions for each parent ID in your DataFrame. This approach is scalable even with thousands of parent IDs, using pandas' grouping and set operations to efficiently compare daily changes.

Step 1: Set Up Sample Data (to mimic your use case) #

First, let's create a sample DataFrame that reflects your structure—parent IDs, dates, and child IDs that change day-to-day:

import pandas as pd

# Sample data matching your scenario
data = {
    'parent_id': ['A', 'A', 'A', 'A', 'B', 'B', 'B'],
    'date': ['2024-05-12', '2024-05-12', '2024-05-13', '2024-05-13', '2024-05-12', '2024-05-13', '2024-05-13'],
    'child_id': ['B', 'C', 'B', 'D', 'X', 'X', 'Y']
}
df = pd.DataFrame(data)

# Convert date to datetime for proper sorting (critical for chronological comparison)
df['date'] = pd.to_datetime(df['date'])

Step 2: Group and Prepare Child ID Sets #

We'll first group the DataFrame by parent_id and date, then aggregate child IDs into sets—this makes it easy to compare which IDs are new or missing between days:

# Create grouped DataFrame with sets of child IDs per parent + date
grouped = df.groupby(['parent_id', 'date'])['child_id'].agg(set).reset_index(name='child_set')

# Sort to ensure we're comparing days in order for each parent
grouped = grouped.sort_values(['parent_id', 'date'])

# Shift to get the previous day's child ID set for each parent
grouped['prev_child_set'] = grouped.groupby('parent_id')['child_set'].shift(1)

Step 3: Calculate Added and Lost Child IDs #

Now we'll compute which child IDs are new (present today, not yesterday) or lost (present yesterday, not today) for each parent-date combination:

# Calculate added children (current set minus previous set)
grouped['added_children'] = grouped.apply(
    lambda row: row['child_set'] - row['prev_child_set'] if pd.notna(row['prev_child_set']) else row['child_set'],
    axis=1
)

# Calculate lost children (previous set minus current set)
grouped['lost_children'] = grouped.apply(
    lambda row: row['prev_child_set'] - row['child_set'] if pd.notna(row['prev_child_set']) else set(),
    axis=1
)

Step 4: Merge Back to Original DataFrame and Add Flags #

Finally, we'll merge these results back to the original DataFrame and create boolean columns (is_added, is_lost) to mark each child ID's status:

# Merge the grouped results back to the original DataFrame
df = df.merge(grouped[['parent_id', 'date', 'added_children', 'lost_children']], on=['parent_id', 'date'])

# Create the final flag columns (1 = yes, 0 = no)
df['is_added'] = df.apply(lambda row: row['child_id'] in row['added_children'], axis=1).astype(int)
df['is_lost'] = df.apply(lambda row: row['child_id'] in row['lost_children'], axis=1).astype(int)

Optional: Show Rows for Lost Child IDs #

By default, lost child IDs won't appear in the current day's rows. If you want to explicitly show rows for lost IDs (to make tracking easier), add this step:

# Generate rows for lost children
lost_rows = []
for _, row in grouped.iterrows():
    if row['lost_children'] and pd.notna(row['prev_child_set']):
        for child in row['lost_children']:
            lost_rows.append({
                'parent_id': row['parent_id'],
                'date': row['date'],
                'child_id': child,
                'is_added': 0,
                'is_lost': 1,
                'added_children': row['added_children'],
                'lost_children': row['lost_children']
            })

# Combine with original DataFrame and sort
df_with_lost = pd.concat([df, pd.DataFrame(lost_rows)]).sort_values(['parent_id', 'date', 'child_id']).reset_index(drop=True)

Final Output #

Running the code above will give you a DataFrame like this (with the optional lost rows included):

parent_id       date child_id added_children lost_children  is_added  is_lost
0         A 2024-05-12        B        {B, C}             {}         1        0
1         A 2024-05-12        C        {B, C}             {}         1        0
2         A 2024-05-13        B             {D}          {C}         0        0
3         A 2024-05-13        C             {D}          {C}         0        1
4         A 2024-05-13        D             {D}          {C}         1        0
5         B 2024-05-12        X           {X}             {}         1        0
6         B 2024-05-13        X             {Y}             {}         0        0
7         B 2024-05-13        Y             {Y}             {}         1        0

This method is efficient even with thousands of parent IDs, as pandas' grouping and set operations are optimized for large datasets. Just make sure your date column is properly formatted as datetime to avoid sorting issues!

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