M6-012Mediumpandas 管道 · pandas pipelines

如何用 pandas 计算漏斗转化(含时间顺序)?

Compute funnel conversion in pandas including the ordering constraint.
💡 考场高分一句话答案:

按用户聚合各步最早时间,用布尔条件判断顺序,再算各步人数与转化率。

Pivot to one row per user with the earliest timestamp per step, then apply boolean ordering conditions and divide each step by the previous one.

📌 核心考察要点 (Key Points)

  • 聚合:groupby('user_id')['ts'].min() 分事件类型
  • 顺序:比较时间戳,注意 NaN 比较返回 False(正好符合需求)
  • 转化率:每步除以上一步,用 np.divide 防除零
  • 分层:按渠道/平台 groupby 后再算
  • Pivot: pivot_table(index='user_id', columns='event', values='ts', aggfunc='min')
  • Ordering: timestamp comparisons; NaN comparisons return False, which is exactly what you want
  • Rates: divide by the previous step and use np.divide to avoid division by zero
  • Segmentation: group by channel or platform before computing

🔍 大厂面试官连环追问 (Follow-ups)

  • 如何加“7 天内完成”的约束?
  • pivot_table 遇到重复键会怎样?

嫌字多看太累?进入苏格拉底式对话推演实验室

零打字负担,以大厂考官真实困境切入,通过点选直觉选项暴露思维盲区,手把手推导 Why。

💬 开启对话式破冰 ➔
⚡
Test muscle memory on this topic via 3-Layer Flashcards
Active Recall: Mentally formulate Prompt ➔ Math Derivation ➔ Benchmark Implementation in 3 minutes.
🎯 Drill This Card Now➔