如何用Pandas筛选user_id行并计算用户下单前的首次/末次访问日期

1. 如何在Pandas中筛选包含指定user_id的行 #

两种高效实现方法：

• 布尔索引法：直接通过列值匹配筛选目标行

import pandas as pd

# 假设数据框名为df
target_user_id = 1
filtered_df = df[df['USER ID'] == target_user_id]

• query方法：适合复杂筛选逻辑的场景

filtered_df = df.query('`USER ID` == @target_user_id')

2. 计算每个用户每次下单前后的访问日期统计 #

原始数据 #

USER ID TYPE    DATE
1   Visited September 14, 2020
1   Visited October 4, 2020
1   Visited October 24, 2020
1   Ordered November 1, 2020
2   Visited September 14, 2020
2   Visited October 1, 2020
3   Visited September 1, 2020
3   Visited October 4, 2020
3   Visited October 4, 2020
3   Visited October 19, 2020
3   Ordered January 1, 2021
3   Visited February 11, 2021
3   Visited February 24, 2021
3   Visited March 1, 2021
3   Ordered April 21, 2021

实现代码 #

import pandas as pd

# 构造数据（实际场景可从文件读取）
data = [
    [1, 'Visited', 'September 14, 2020'],
    [1, 'Visited', 'October 4, 2020'],
    [1, 'Visited', 'October 24, 2020'],
    [1, 'Ordered', 'November 1, 2020'],
    [2, 'Visited', 'September 14, 2020'],
    [2, 'Visited', 'October 1, 2020'],
    [3, 'Visited', 'September 1, 2020'],
    [3, 'Visited', 'October 4, 2020'],
    [3, 'Visited', 'October 4, 2020'],
    [3, 'Visited', 'October 19, 2020'],
    [3, 'Ordered', 'January 1, 2021'],
    [3, 'Visited', 'February 11, 2021'],
    [3, 'Visited', 'February 24, 2021'],
    [3, 'Visited', 'March 1, 2021'],
    [3, 'Ordered', 'April 21, 2021']
]
df = pd.DataFrame(data, columns=['USER ID', 'TYPE', 'DATE'])

# 转换日期列格式
df['DATE'] = pd.to_datetime(df['DATE'])

# 为每个用户标记订单分组：同一订单前的访问归为一组
df['order_group'] = df.groupby('USER ID')['TYPE'].apply(lambda x: x.eq('Ordered').cumsum())

# 拆分订单与访问数据
orders = df[df['TYPE'] == 'Ordered'].copy()
visits = df[df['TYPE'] == 'Visited'].copy()

# 计算每组访问的首次、末次日期
visit_stats = visits.groupby(['USER ID', 'order_group']).agg(
    MIN_DATE=('DATE', 'min'),
    MAX_DATE=('DATE', 'max')
).reset_index()

# 标记用户的订单序号
orders['Ordered'] = orders.groupby('USER ID').cumcount() + 1
orders = orders[['USER ID', 'Ordered', 'order_group']]

# 合并订单与访问统计结果
result = pd.merge(orders, visit_stats, on=['USER ID', 'order_group'], how='right')

# 处理无订单用户
no_order_users = df['USER ID'].unique()[~df['USER ID'].isin(orders['USER ID'])]
no_order_df = pd.DataFrame({
    'USER ID': no_order_users,
    'Ordered': 0,
    'MIN_DATE': df[df['USER ID'].isin(no_order_users)].groupby('USER ID')['DATE'].min(),
    'MAX_DATE': pd.NaT
}).reset_index(drop=True)

# 合并所有结果并调整格式
final_result = pd.concat([result, no_order_df], ignore_index=True)
final_result = final_result[['USER ID', 'Ordered', 'MIN_DATE', 'MAX_DATE']]
final_result['MIN_DATE'] = final_result['MIN_DATE'].dt.strftime('%B %d, %Y')
final_result['MAX_DATE'] = final_result['MAX_DATE'].dt.strftime('%B %d, %Y').where(final_result['MAX_DATE'].notna(), 'NAT')

# 打印最终结果
print(final_result.to_string(index=False))

预期输出 #

USER ID Ordered       MIN DATE       MAX DATE
       1        1 September 14, 2020 October 24, 2020
       2        0 September 14, 2020              NAT
       3        1  September 1, 2020 October 19, 2020
       3        2 February 11, 2021    March 1, 2021

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