基于Pandas 3.5实现ID下SubID新增/删除的标记列开发需求

Hey there! Let's work through this problem together. You've got a DataFrame with thousands of parent IDs, each linked to multiple SubIDs that change day-to-day—some get added, others removed. You want to add two columns to flag these changes, and you already have a SQL solution as a reference (like your example where SubID 'D' was added on the 12th). Here's a straightforward, efficient way to do this with Pandas:

First, let's start with a sample DataFrame that mirrors your scenario (you can swap this out with your actual data):

import pandas as pd

# Sample data: Parent ID, SubID, Date of observation
data = [
    ("A1", "A", "2024-01-11"),
    ("A1", "B", "2024-01-11"),
    ("A1", "C", "2024-01-11"),
    ("A1", "A", "2024-01-12"),
    ("A1", "B", "2024-01-12"),
    ("A1", "C", "2024-01-12"),
    ("A1", "D", "2024-01-12"),  # New SubID added on 12th
    ("A2", "X", "2024-01-11"),
    ("A2", "Y", "2024-01-11"),
    ("A2", "X", "2024-01-12"),  # SubID Y removed on 12th
]

df = pd.DataFrame(data, columns=["ID", "SubID", "Date"])
df["Date"] = pd.to_datetime(df["Date"])

Step 1: Group SubIDs by ID and Date #

First, we'll group the data to get a set of SubIDs for each parent ID on each date. Then we'll shift this data to compare each day to the previous one:

# Get the unique set of SubIDs per ID and Date
daily_subids = df.groupby(["ID", "Date"])["SubID"].apply(set).reset_index(name="Current_SubIDs")

# Shift to get the previous day's SubID set for each ID
daily_subids["Previous_SubIDs"] = daily_subids.groupby("ID")["Current_SubIDs"].shift(1)

Step 2: Calculate New and Removed SubIDs #

Next, we'll define helper functions to identify which SubIDs are new (present today, not yesterday) and which are removed (present yesterday, not today):

def find_new_subids(current, previous):
    # For the first day of an ID, all SubIDs are new
    if pd.isna(previous):
        return current
    return current - previous

def find_removed_subids(current, previous):
    # For the first day of an ID, no SubIDs are removed
    if pd.isna(previous):
        return set()
    return previous - current

# Apply the functions to get daily changes
daily_subids["New_SubIDs"] = daily_subids.apply(
    lambda row: find_new_subids(row["Current_SubIDs"], row["Previous_SubIDs"]),
    axis=1
)
daily_subids["Removed_SubIDs"] = daily_subids.apply(
    lambda row: find_removed_subids(row["Current_SubIDs"], row["Previous_SubIDs"]),
    axis=1
)

Step 3: Merge Back to Original DataFrame #

Now we'll join this change data back to the original DataFrame to create our flag columns:

# Merge daily change info with the original data
merged_df = df.merge(
    daily_subids[["ID", "Date", "New_SubIDs", "Removed_SubIDs"]],
    on=["ID", "Date"],
    how="left"
)

# Create boolean flag columns
merged_df["is_new"] = merged_df.apply(
    lambda row: 1 if row["SubID"] in row["New_SubIDs"] else 0,
    axis=1
)
merged_df["is_removed"] = merged_df.apply(
    lambda row: 1 if row["SubID"] in row["Removed_SubIDs"] else 0,
    axis=1
)

# Clean up intermediate columns if needed
merged_df = merged_df.drop(["New_SubIDs", "Removed_SubIDs"], axis=1)

Result #

When you print merged_df, you'll see the flags working as expected:

ID SubID       Date  is_new  is_removed
0  A1     A 2024-01-11       1           0
1  A1     B 2024-01-11       1           0
2  A1     C 2024-01-11       1           0
3  A1     A 2024-01-12       0           0
4  A1     B 2024-01-12       0           0
5  A1     C 2024-01-12       0           0
6  A1     D 2024-01-12       1           0
7  A2     X 2024-01-11       1           0
8  A2     Y 2024-01-11       1           0
9  A2     X 2024-01-12       0           0

Optional: Include Removed SubIDs as Rows #

If you want to explicitly see removed SubIDs as rows (like SubID Y on 2024-01-12), you can expand the dataset to include all possible ID-SubID-Date combinations:

# Get all unique ID-SubID pairs and all unique dates
all_id_subid = df[["ID", "SubID"]].drop_duplicates()
all_dates = df["Date"].unique()

# Create a full cross-join to cover every possible combination
full_dataset = all_id_subid.merge(pd.DataFrame(all_dates, columns=["Date"]), how="cross")

# Add a flag for whether the SubID was present that day
full_dataset = full_dataset.merge(
    df.assign(is_present=1),
    on=["ID", "SubID", "Date"],
    how="left"
).fillna({"is_present": 0})

# Calculate previous day's presence and set flags
full_dataset["prev_present"] = full_dataset.groupby(["ID", "SubID"])["is_present"].shift(1)
full_dataset["is_new"] = full_dataset.apply(
    lambda row: 1 if row["is_present"] == 1 and row["prev_present"] == 0 else 0,
    axis=1
)
full_dataset["is_removed"] = full_dataset.apply(
    lambda row: 1 if row["is_present"] == 0 and row["prev_present"] == 1 else 0,
    axis=1
)

This will give you a row for SubID Y on 2024-01-12 with is_removed=1.

Key Tips for Large Datasets #

• Since we're grouping by parent ID first, this approach stays efficient even with thousands of IDs—we avoid comparing every SubID across the entire dataset.
• If your dates aren't consecutive, use resample within each ID group to fill in missing dates before shifting, so comparisons are accurate.
• This logic mirrors the self-join/window function approach you'd use in SQL, but adapted to Pandas' strengths in grouping and shifting.

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