自学教程 学编程从这里开始

Pandas 电商数据分析实战

Pandas 教程

承接前面全套Pandas教程，本篇是业务落地实战案例，基于电商订单明细数据，完整覆盖：数据读取清洗、核心指标计算、时间趋势、商品分析、用户RFM分层、复购留存、渠道分析、可视化输出、报表导出，所有代码可直接运行，贴合电商分析师日常工作流。
前置导入

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns

# 绘图中文设置
plt.rcParams["font.sans-serif"] = ["SimHei"]
plt.rcParams["axes.unicode_minus"] = False
sns.set_style("whitegrid")

一、模拟电商订单数据（和真实订单表结构对齐）

字段说明：订单ID、用户ID、下单时间、渠道、商品分类、商品名称、单价、数量、订单金额、支付金额、退款金额

df = pd.DataFrame({
    "order_id": ["O001","O002","O003","O004","O005","O006","O007","O008","O009","O010","O011","O012"],
    "user_id": ["U001","U002","U001","U003","U004","U002","U005","U003","U006","U001","U004","U007"],
    "order_time": pd.to_datetime([
        "2026-09-01 08:20","2026-09-01 10:30","2026-09-02 14:10","2026-09-02 16:50",
        "2026-09-03 09:10","2026-09-04 11:20","2026-09-04 15:40","2026-09-05 08:30",
        "2026-09-05 19:20","2026-09-06 10:10","2026-09-06 14:30","2026-09-07 11:00"
    ]),
    "channel": ["抖音","淘宝","抖音","小红书","淘宝","抖音","淘宝","小红书","抖音","抖音","淘宝","小红书"],
    "category": ["手机","电脑","配件","手机","配件","电脑","手机","配件","电脑","配件","手机","配件"],
    "product_name": ["iPhone15","联想小新","钢化膜","小米14","数据线","华硕","华为Mate","充电头","戴尔","手机壳","vivo","耳机"],
    "price": [4999,4299,29,3999,39,4599,4699,59,4199,19,3599,129],
    "qty": [1,1,2,1,3,1,1,1,1,1,1,1],
    "order_amount": [4999,4299,58,3999,117,4599,4699,59,4199,19,3599,129],
    "pay_amount": [4999,4299,58,3999,117,4599,0,59,4199,19,3599,129],
    "refund_amount": [0,0,0,0,0,0,4699,0,0,0,0,0]
})
print("原始订单数据：")
print(df.head())

业务口径约定：
GMV = order_amount 订单金额（含未支付、退款）
实际营收 = pay_amount 支付金额（剔除未支付）
有效营收 = pay_amount – refund_amount

二、第一步：数据清洗 & 特征工程（电商必做）

# 1. 新增业务字段
df["real_income"] = df["pay_amount"] - df["refund_amount"]
df["order_date"] = df["order_time"].dt.date
df["order_hour"] = df["order_time"].dt.hour
df["weekday"] = df["order_time"].dt.dayofweek

# 2. 去重、空值检查
df = df.drop_duplicates(subset=["order_id"])
print("缺失值统计：")
print(df.isna().sum())

# 3. 过滤脏数据：金额负数、数量<=0
df = df[(df["qty"]>0) & (df["order_amount"]>=0)]

# 4. 标记订单状态
df["order_status"] = np.where(df["refund_amount"]>0, "已退款", np.where(df["pay_amount"]>0, "已支付", "未支付"))
print("清洗后数据：")
print(df[["order_id","user_id","order_status","real_income"]].head())

三、核心大盘指标计算（电商看板基础指标）

公式：

  • 订单数：唯一order_id数量
  • 支付订单数：支付>0的订单
  • 成交用户数：去重user_id
  • 客单价AOV：实际营收 / 支付订单数
  • 人均消费：实际营收 / 成交用户数
  • 退款率：退款订单数 / 总订单数
# 大盘指标
total_order_cnt = df["order_id"].nunique()
pay_order_cnt = df[df["pay_amount"]>0]["order_id"].nunique()
refund_order_cnt = df[df["refund_amount"]>0]["order_id"].nunique()
total_user_cnt = df["user_id"].nunique()
gmv = df["order_amount"].sum()
real_total_income = df["real_income"].sum()

aov = real_total_income / pay_order_cnt if pay_order_cnt>0 else 0
user_avg_spend = real_total_income / total_user_cnt if total_user_cnt>0 else 0
refund_rate = refund_order_cnt / total_order_cnt if total_order_cnt>0 else 0

print("===== 大盘指标 =====")
print(f"总订单数：{total_order_cnt}")
print(f"支付订单数：{pay_order_cnt}")
print(f"成交用户数：{total_user_cnt}")
print(f"GMV：{gmv:.2f}")
print(f"实际营收：{real_total_income:.2f}")
print(f"客单价AOV：{aov:.2f}")
print(f"用户人均消费：{user_avg_spend:.2f}")
print(f"订单退款率：{refund_rate:.2%}")

四、维度拆解分析

4.1 时间趋势：按日营收、下单时段分布

# 按日汇总
daily_report = df.groupby("order_date").agg(
    order_cnt=("order_id","nunique"),
    income=("real_income","sum")
).reset_index()
print("每日营收报表：")
print(daily_report)

# 时段分布（看流量高峰）
hour_report = df.groupby("order_hour").agg(
    order_cnt=("order_id","nunique")
).reset_index()
print("时段下单分布：")
print(hour_report)

4.2 渠道分析（抖音/淘宝/小红书）

channel_report = df.groupby("channel").agg(
    order_cnt=("order_id","nunique"),
    user_cnt=("user_id","nunique"),
    income=("real_income","sum")
).reset_index()
channel_report["aov"] = channel_report["income"] / channel_report["order_cnt"]
channel_report = channel_report.sort_values("income", ascending=False)
print("渠道分析：")
print(channel_report)

4.3 商品分类、爆款商品分析

cate_report = df.groupby("category").agg(
    income=("real_income","sum"),
    order_cnt=("order_id","nunique"),
    sale_qty=("qty","sum")
).reset_index().sort_values("income",ascending=False)
print("品类销售：")
print(cate_report)

# TOP爆款商品
top_product = df.groupby("product_name").agg(
    income=("real_income","sum"),
    sale_qty=("qty","sum")
).reset_index().sort_values("income",ascending=False).head(5)
print("TOP5爆款：")
print(top_product)

五、用户分析：RFM用户分层（电商经典模型）

R：Recency 最近一次购买距离当前天数
F：Frequency 购买频次（订单数）
M：Monetary 累计消费金额

业务含义：高价值用户、潜力用户、流失预警、沉睡用户

# 取参考截止时间（示例最大日期）
max_date = df["order_time"].max()
user_rfm = df.groupby("user_id").agg(
    last_order_time=("order_time","max"),
    freq=("order_id","nunique"),
    money=("real_income","sum")
).reset_index()

# 计算R，距离最后下单天数
user_rfm["R_days"] = (max_date - user_rfm["last_order_time"]).dt.days

# 分箱打分（简单二分，业务可改成四分位数）
user_rfm["R_score"] = np.where(user_rfm["R_days"] <= 3, 2, 1)
user_rfm["F_score"] = np.where(user_rfm["freq"] >=2, 2, 1)
user_rfm["M_score"] = np.where(user_rfm["money"] >= 1000, 2, 1)

# RFM总分
user_rfm["rfm_total"] = user_rfm["R_score"] + user_rfm["F_score"] + user_rfm["M_score"]
# 用户分层
def user_label(score):
    if score >=6:
        return "高价值用户"
    elif score >=4:
        return "潜力用户"
    elif score >=3:
        return "沉睡用户"
    else:
        return "流失用户"
user_rfm["user_level"] = user_rfm["rfm_total"].apply(user_label)
print("RFM用户分层结果：")
print(user_rfm[["user_id","R_days","freq","money","user_level"]])
print(user_rfm["user_level"].value_counts())

六、复购分析（新客/老客）

逻辑：判断用户是否多次下单

# 首次下单时间
user_first = df.groupby("user_id")["order_time"].min().reset_index().rename(columns={"order_time":"first_time"})
df = df.merge(user_first, on="user_id")
# 标记是否为复购订单
df["is_rebuy"] = df["order_time"] > df["first_time"]
rebuy_report = df.groupby("user_id")["is_rebuy"].any().reset_index()
rebuy_report.columns = ["user_id","is_rebuy_user"]
rebuy_cnt = rebuy_report["is_rebuy_user"].sum()
total_buy_user = rebuy_report["user_id"].nunique()
rebuy_rate = rebuy_cnt / total_buy_user
print(f"复购用户数：{rebuy_cnt}，复购率：{rebuy_rate:.2%}")

七、可视化报表（对接matplotlib+seaborn）

fig, axes = plt.subplots(2,2,figsize=(14,10))
# 1.渠道营收柱状图
sns.barplot(data=channel_report, x="channel", y="income", ax=axes[0,0], errorbar=None)
axes[0,0].set_title("各渠道营收")
# 2.品类营收
sns.barplot(data=cate_report, x="category", y="income", ax=axes[0,1], errorbar=None)
axes[0,1].set_title("品类营收")
# 3.每日营收趋势
sns.lineplot(data=daily_report, x="order_date", y="income", marker="o", ax=axes[1,0])
axes[1,0].set_title("每日营收趋势")
axes[1,0].tick_params(axis="x", rotation=30)
# 4.RFM用户分层分布
level_cnt = user_rfm["user_level"].value_counts().reset_index()
sns.barplot(data=level_cnt, x="user_level", y="count", ax=axes[1,1], errorbar=None)
axes[1,1].set_title("用户分层数量")

plt.tight_layout()
plt.savefig("电商分析报表.png",dpi=300,bbox_inches="tight")
plt.close()
print("图表已导出：电商分析报表.png")

八、结果导出Excel（多sheet报表）

with pd.ExcelWriter("电商分析结果.xlsx", engine="openpyxl") as writer:
    daily_report.to_excel(writer, sheet_name="每日报表", index=False)
    channel_report.to_excel(writer, sheet_name="渠道报表", index=False)
    cate_report.to_excel(writer, sheet_name="品类报表", index=False)
    user_rfm.to_excel(writer, sheet_name="RFM用户分层", index=False)
print("Excel报表导出完成")

九、业务解读与拓展方向

  1. 渠道：看哪个渠道营收高、客单价高，判断投放ROI，调整广告预算
  2. 品类：找到高营收品类、低毛利走量品类，做捆绑销售
  3. RFM分层：高价值用户做会员、专属券；沉睡用户发召回优惠券
  4. 复购率低：考虑售后、会员、定期活动提升复购
  5. 进阶扩展：同期群Cohort留存、毛利分析、广告投放归因、SKU库存周转

十、高频坑清单

  1. GMV和实际营收混淆：GMV包含未支付、退款，对外汇报优先用支付后营收
  2. 订单表是明细行，一个订单多商品会拆成多行，计算订单数必须 nunique(order_id)，不能直接count
  3. RFM计算时间口径：必须固定截止日期，不要每次跑脚本动态变化
  4. 复购统计口径：区分【订单复购】和【用户复购】，业务常用用户复购率
  5. 大订单明细数据：百万行以上用chunksize分块读取，避免内存溢出
  6. 退款、取消订单过滤不彻底，指标虚高

十一、速查表

分析场景核心Pandas方法
大盘指标groupby + agg + nunique / sum
时间趋势resample / groupby date
渠道/品类拆解groupby + sort_values
用户RFM分层groupby用户 + 时间差dt.days + np.where分箱
复购率取用户首单时间，对比后续订单
批量导出多sheetpd.ExcelWriter
画像可视化seaborn barplot / lineplot
标签：

0 条笔记