Python Pandas中如何实现Excel COUNTIFS多条件计数功能？

你的DataFrame示例 #

实现步骤 #

我来帮你把Excel里的COUNTIFS统计逻辑搬到Pandas里，思路其实和Excel一致：先筛选符合条件的行，再计数。不过Pandas处理大数据量的速度会比Excel快很多，下面一步步来：

第一步：确保时间差列类型正确

首先要确认Difference列是**timedelta（时间差）**类型，如果你的DataFrame里这列还是字符串格式，先做转换：

import pandas as pd

# 转换为timedelta类型，让Pandas能识别时长大小
df['Difference'] = pd.to_timedelta(df['Difference'])

第二步：单个统计项的写法

直接写出和Excel COUNTIFS对应的筛选条件，然后对布尔值求和（True会被计为1，False为0，求和结果就是符合条件的会话数）：

# Buyers_0-to-1 min
buyers_0_1 = ((df['Buyer'] == 1) & 
              (df['Difference'] >= pd.Timedelta(0)) & 
              (df['Difference'] <= pd.Timedelta('00:01:00'))).sum()

# Buyers_1.1-to-5 min
buyers_1_5 = ((df['Buyer'] == 1) & 
              (df['Difference'] >= pd.Timedelta('00:01:01')) & 
              (df['Difference'] <= pd.Timedelta('00:05:00'))).sum()

# Sellers_0-to-1 min
sellers_0_1 = ((df['Seller'] == 1) & 
               (df['Difference'] >= pd.Timedelta(0)) & 
               (df['Difference'] <= pd.Timedelta('00:01:00'))).sum()

# Non_buyer_sellers_0-to-1 min
non_buyer_sellers_0_1 = ((df['Non_buyer_seller'] == 1) & 
                         (df['Difference'] >= pd.Timedelta(0)) & 
                         (df['Difference'] <= pd.Timedelta('00:01:00'))).sum()

第三步：批量统计的优化写法

如果需要统计很多组和时长区间，写个复用函数会更高效，避免重复代码：

def count_session_by_group(df, group_column, min_duration, max_duration):
    """
    统计指定用户组、指定时长区间内的会话数
    :param df: 目标DataFrame
    :param group_column: 用户组列名（如'Buyer'、'Seller'）
    :param min_duration: 最小时长（pd.Timedelta类型）
    :param max_duration: 最大时长（pd.Timedelta类型）
    :return: 符合条件的会话数
    """
    filter_mask = (df[group_column] == 1) & \
                  (df['Difference'] >= min_duration) & \
                  (df['Difference'] <= max_duration)
    return filter_mask.sum()

# 调用函数完成所有统计
buyers_0_1 = count_session_by_group(df, 'Buyer', pd.Timedelta(0), pd.Timedelta('00:01:00'))
buyers_1_5 = count_session_by_group(df, 'Buyer', pd.Timedelta('00:01:01'), pd.Timedelta('00:05:00'))
sellers_0_1 = count_session_by_group(df, 'Seller', pd.Timedelta(0), pd.Timedelta('00:01:00'))
non_buyer_sellers_0_1 = count_session_by_group(df, 'Non_buyer_seller', pd.Timedelta(0), pd.Timedelta('00:01:00'))

这样得到的结果和你Excel里的COUNTIFS完全一致，而且处理几十万行数据的速度会快不少哦！

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