如何在Pandas中计算月度复购客户消费占比并新增对应列

实现代码 #

第一步：先做数据类型预处理 #

你提供的原始样例数据中多个字段为字符串格式，需要先转换为对应可计算的类型：

import pandas as pd
import numpy as np

# 原始数据（你已生成df的话可以跳过这部分）
df = pd.DataFrame(
    {
        "date_created": ["2019-11-16", "2019-11-16", "2019-11-16", "2019-11-16", "2019-11-16", "2019-11-16"],
        "customer_id": ["1733", "6356", "6457", "6599", "6637", "6638"],
        "total": ["746.02", "1236.60", "1002.32", "1187.21", "1745.03", "2313.14"],
        "recurring_customer": ["False", "False", "False", "False", "False", "False"],
    }
)

# 类型转换
df['date_created'] = pd.to_datetime(df['date_created'])
df['total'] = pd.to_numeric(df['total'])
df['recurring_customer'] = df['recurring_customer'].replace({'True': True, 'False': False}).astype(bool)
# 设置日期为索引，支持resample重采样能力
df = df.set_index('date_created')

第二步：按预设思路完成指标计算 #

# 1. 新建复购客户订单金额列，复购订单取对应消费额，非复购订单取0
df['recurring_customer_total'] = np.where(df['recurring_customer'], df['total'], 0)

# 2. 按月重采样汇总对应指标
df_monthly = df.resample('1M').agg(
    # 月度总营业额
    monthly_total = ('total', 'sum'),
    # 月度复购客户总消费额
    recurring_customer_total = ('recurring_customer_total', 'sum'),
    # 可按需保留原逻辑的其他指标，比如复购客户占比、平均客单价等
    recurring_customer_rate = ('recurring_customer', 'mean'),
    avg_order_amount = ('total', 'mean')
).reset_index()

# 3. 计算复购消费额占月度总营业额的比例
df_monthly['recurring_customer_total_percentage'] = df_monthly['recurring_customer_total'] / df_monthly['monthly_total']

补充说明 #

如果需要保留你之前月度汇总里的customer_id均值这类自定义指标，直接在agg方法里新增对应的聚合规则即可，比如添加avg_customer_id = ('customer_id', 'mean')。

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