分析DataFrame中各唯一ID的event=5前后事件状态转换

Let's break down how to solve this problem using pandas. The goal is to identify, for each uid, what events happened immediately before and after every occurrence of event=5.

Step 1: Prepare the Data #

First, we'll load your data into a pandas DataFrame and ensure the timestamp is properly formatted as datetime (critical for sorting events in chronological order):

import pandas as pd

# Your raw event data
data = [
    [34900, "2015-01-01 00:00:10", 5],
    [90100, "2015-01-07 00:00:00", 4],
    [90100, "2015-03-02 00:00:00", 9],
    [34900, "2015-01-03 00:00:00", 5],
    [34900, "2015-01-01 00:40:00", 6],
    [34900, "2015-01-01 00:00:01", 2],
    [90100, "2015-03-01 00:07:00", 5],
    [34900, "2015-01-04 00:00:09", 8],
    [34900, "2015-01-07 00:00:10", 2],
    [90100, "2015-01-01 20:00:00", 1]
]

# Create DataFrame and clean timestamp column
df = pd.DataFrame(data, columns=["uid", "timestamp", "event"])
df["timestamp"] = pd.to_datetime(df["timestamp"])

Step 2: Sort Events Chronologically #

We need to sort the data by uid and timestamp so each user's events are ordered as they happened:

df_sorted = df.sort_values(by=["uid", "timestamp"]).reset_index(drop=True)

Step 3: Detect Transitions Around event=5 #

We'll write a helper function to process each user's event group, locate every event=5 entry, and capture the immediately preceding and following events (handling edge cases where event=5 is the first or last event for a user):

def get_event_transitions(group):
    # Find all positions in the group where event equals 5
    event5_positions = group[group["event"] == 5].index
    
    transition_list = []
    for pos in event5_positions:
        # Get previous event (if it exists)
        prev_event = group.iloc[pos - 1]["event"] if pos > 0 else None
        # Get next event (if it exists)
        next_event = group.iloc[pos + 1]["event"] if pos < len(group) - 1 else None
        
        # Format a human-readable transition summary
        if prev_event and next_event:
            transition_str = f"{prev_event} → 5 → {next_event}"
        elif next_event:
            transition_str = f"→ 5 → {next_event}"
        elif prev_event:
            transition_str = f"{prev_event} → 5 →"
        else:
            transition_str = "5 (no surrounding events)"
        
        transition_list.append({
            "uid": group["uid"].iloc[0],
            "event5_timestamp": group.iloc[pos]["timestamp"],
            "previous_event": prev_event,
            "next_event": next_event,
            "transition_summary": transition_str
        })
    
    return pd.DataFrame(transition_list)

# Apply the function to each user's event group
transition_results = df_sorted.groupby("uid", group_keys=False).apply(get_event_transitions)

Step 4: View the Final Results #

Printing transition_results gives you a clear breakdown of each event=5 occurrence and its surrounding events:

print(transition_results)

Sample Output:

uid      event5_timestamp  previous_event  next_event transition_summary
0  34900 2015-01-01 00:00:10               2           6        2 → 5 → 6
1  34900 2015-01-03 00:00:00               6           8        6 → 5 → 8
0  90100 2015-03-01 00:07:00               4           9        4 → 5 → 9

This output clearly shows:

• Which user (uid) the transition belongs to
• The exact timestamp of the event=5 occurrence
• The event that happened right before (if any)
• The event that happened right after (if any)
• A concise summary of the state transition

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