大型DataFrame用户行为指标计算的高效脚本优化需求

Hey there! Let's fix that slow performance issue with your user behavior metrics script. The original approach has unnecessary overhead from repeated groupby operations, intermediate data conversions, and slow in membership checks. Here's a streamlined, vectorized solution that leverages pandas' optimized functions to handle your 117MB DataFrame much faster:

Optimized Code #

import pandas as pd

# Convert Timestamp to datetime (cleaner concise syntax)
df['Timestamp'] = pd.to_datetime(df['Timestamp'])
# Extract date using pandas' dt accessor (far faster than list comprehension)
df['date'] = df['Timestamp'].dt.date

# Calculate all metrics with vectorized groupby operations
# 1. Active: User has any day with >2 clicks
active = df.groupby(['USER', 'date']).size().reset_index(name='count')
active = active.groupby('USER')['count'].any(lambda x: x > 2).rename('Active')

# 2. Multiple_days: User has >1 unique visit date
multiple_days = df.groupby('USER')['date'].nunique() > 1
multiple_days = multiple_days.rename('Multiple_days')

# 3. Busi_weekday: User has at least one visit during business hours on a business day
busi_weekday = df.groupby('USER').apply(lambda x: (x['Busi_days'] & x['Busi_hours']).any())
busi_weekday = busi_weekday.rename('Busi_weekday')

# Combine all metrics into the final target DataFrame
target_df = pd.concat([active, multiple_days, busi_weekday], axis=1).reset_index()

Key Performance Improvements #

• Vectorized Operations: Ditches slow Python loops (like [True if x in UID else False ...]) for pandas' C-optimized groupby aggregations, which are orders of magnitude faster for large datasets.
• Reduced Data Traversal: Cuts down on full DataFrame passes—your original script ran multiple separate groupbys, each requiring a full scan of the data. This version minimizes redundant iterations.
• Efficient Checks: Replaces O(n) in membership checks with pandas' index-based merging, which operates in O(log n) time per entry.

Sample Output #

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