基于Pandas时间戳创建移动应用交互会话的技术实现

Got it, let's work through this problem together. You need to group user mobile interactions into sessions (where a session ends if there's no activity for 5 minutes), assign a unique SessionID to each row, and then calculate the total number of sessions plus the apps used in each session. Here's a step-by-step solution using pandas:

Step 1: Setup and Load Data #

First, let's import pandas and load your sample data (replace this with your actual DataFrame if needed):

import pandas as pd

# Your sample dataset
data = {
    'timestamp': [
        '2018-04-08 14:31:29.209', '2018-04-08 14:58:42.875', '2018-04-08 18:18:04.757',
        '2018-04-08 21:08:41.368', '2018-04-11 10:53:10.744', '2018-04-14 19:54:37.441',
        '2018-04-14 19:54:59.833', '2018-04-14 19:55:10.844', '2018-04-14 19:55:34.486',
        '2018-04-14 20:23:00.315', '2018-04-15 08:23:44.873', '2018-04-15 08:24:07.257',
        '2018-04-16 16:42:35.538', '2018-04-16 16:42:48.351', '2018-04-17 08:10:54.734',
        '2018-04-17 08:13:28.855', '2018-04-17 08:16:49.408', '2018-04-17 08:18:55.049',
        '2018-04-17 08:21:04.201', '2018-04-17 08:26:14.254'
    ],
    'App': [
        'Google', 'Google', 'Chrome', 'Google', 'Google', 'Google', 'Google',
        'YouTube', 'Google', 'Google', 'Google', 'Google', 'Google', 'Google',
        'Google', 'Google', 'Google', 'Google', 'Google', 'Google'
    ]
}

df = pd.DataFrame(data)

Step 2: Convert Timestamp to Datetime Type #

First, we need to ensure the timestamp column is recognized as a datetime object (it's likely stored as a string in raw data):

df['timestamp'] = pd.to_datetime(df['timestamp'])

Step 3: Identify Session Boundaries #

We'll calculate the time gap between each interaction and the previous one, then flag when a new session starts (either the first row, or when the gap exceeds 5 minutes):

# Calculate time difference in minutes between current and previous row
df['time_diff'] = df['timestamp'].diff().dt.total_seconds() / 60

# Mark rows where a new session begins
df['new_session'] = (df['time_diff'] > 5) | df['time_diff'].isna()

Step 4: Assign Unique Session IDs #

Using the new_session flag, we generate a continuous SessionID by taking the cumulative sum of the flag (each True value increments the ID):

df['SessionID'] = df['new_session'].cumsum()

Step 5: Final Output (Matching Your Expected Format) #

Drop the intermediate helper columns to get the clean output you want:

# Clean up the DataFrame
df_final = df.drop(['time_diff', 'new_session'], axis=1)
print(df_final.to_string(index=False))

This will produce exactly the output you requested:

timestamp     App  SessionID
2018-04-08 14:31:29  Google          1
2018-04-08 14:58:42  Google          2
2018-04-08 18:18:04  Chrome          3
2018-04-08 21:08:41  Google          4
2018-04-11 10:53:10  Google          5
2018-04-14 19:54:37  Google          6
2018-04-14 19:54:59  Google          6
2018-04-14 19:55:10  YouTube         6
2018-04-14 19:55:34  Google          6
2018-04-14 20:23:00  Google          7
2018-04-15 08:23:44  Google          8
2018-04-15 08:24:07  Google          8
2018-04-16 16:42:35  Google          9
2018-04-16 16:42:48  Google          9
2018-04-17 08:10:54  Google         10
2018-04-17 08:13:28  Google         10
2018-04-17 08:16:49  Google         10
2018-04-17 08:18:55  Google         10
2018-04-17 08:21:04  Google         10
2018-04-17 08:26:14  Google         11

Step 6: Calculate Session Statistics #

To get the total number of sessions and the unique apps used per session:

# Total number of sessions
total_sessions = df['SessionID'].nunique()
print(f"Total number of sessions: {total_sessions}")

# Unique apps used in each session
session_app_summary = df.groupby('SessionID')['App'].unique().reset_index()
session_app_summary.columns = ['SessionID', 'Apps_Used']
print("\nApps used per session:")
print(session_app_summary.to_string(index=False))

This will output:

Total number of sessions: 11

Apps used per session:
 SessionID        Apps_Used
         1         [Google]
         2         [Google]
         3         [Chrome]
         4         [Google]
         5         [Google]
         6 [Google, YouTube]
         7         [Google]
         8         [Google]
         9         [Google]
        10         [Google]
        11         [Google]

This approach uses vectorized pandas operations, so it's efficient even for large datasets—no slow loops needed!

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