如何用Pandas实现等效SQL的CSV过滤？含时间差与消息数统计问题

前提说明 #

假设你的目标SQL逻辑是关联orders与customer_courier_chat_messages表，计算消息时间差，并按订单ID统计客户、Courier各自的消息数量——如果你的SQL有特殊过滤/关联规则，可补充后调整代码。

1. 修复时间差计算错误 #

时间差报错90%源于时间列未转换为datetime类型，或分组/关联逻辑与SQL不符，直接落地代码：

import pandas as pd

# 读取文件时直接解析时间列，避免字符串操作报错
chat_df = pd.read_csv('customer_courier_chat_messages.csv', parse_dates=['message_sent_at'])
orders_df = pd.read_csv('orders.csv', parse_dates=['order_created_at', 'order_completed_at'])

# 场景1：计算同订单内相邻消息的时间差（对应SQL的LAG函数）
chat_df = chat_df.sort_values(['order_id', 'message_sent_at'])  # 与SQL排序逻辑对齐
chat_df['time_diff'] = chat_df.groupby('order_id')['message_sent_at'].diff()
# 按需转换为秒/分钟（匹配SQL的TIMESTAMPDIFF逻辑）
chat_df['time_diff_seconds'] = chat_df['time_diff'].dt.total_seconds()

# 场景2：计算消息与订单关键时间点的间隔（比如消息发送时间与订单创建时间的差）
merged_df = pd.merge(chat_df, orders_df, on='order_id', how='inner')  # 关联方式与SQL一致（INNER/LEFT）
merged_df['time_since_order_created'] = merged_df['message_sent_at'] - merged_df['order_created_at']

常见错误排查：

• 时间列格式问题：若parse_dates失效，用pd.to_datetime(chat_df['message_sent_at'], errors='coerce')强制转换，再用chat_df['message_sent_at'].isna().sum()查看无效时间值
• 关联键类型不匹配：确认order_id在两个表中类型一致（均为字符串/整数），避免关联失败

2. 按订单统计客户、Courier消息数量 #

假设聊天表有sender_type字段（值为customer/courier），两种实现方式完全对齐SQL的COUNT(CASE WHEN ...)逻辑：

方法1：分组聚合（最贴近SQL写法）

message_stats = chat_df.groupby('order_id').agg(
    customer_message_count=('sender_type', lambda x: (x == 'customer').sum()),
    courier_message_count=('sender_type', lambda x: (x == 'courier').sum())
).reset_index()

方法2：透视表（更简洁）

message_stats = chat_df.pivot_table(
    index='order_id',
    columns='sender_type',
    values='message_id',  # 任意非空唯一列均可
    aggfunc='count',
    fill_value=0
).rename(columns={'customer': 'customer_message_count', 'courier': 'courier_message_count'}).reset_index()

若需关联订单表的其他字段（如订单状态、完成时间），直接合并即可：

final_result = pd.merge(message_stats, orders_df, on='order_id', how='inner')

针对错误截图的补充建议 #

• 若报错TypeError: unsupported operand type(s) for -: 'str' and 'str'：优先检查时间列是否完成datetime转换
• 若报错KeyError：确认分组/关联的列名与CSV文件完全一致（比如是order_id还是orderId）
• 若出现NaN值问题：用fillna()填充或dropna()清理缺失数据

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