本文可以學(xué)習(xí)到以下內(nèi)容：

1. 使用 pandas 中的 read_sql 讀取 sqlite 中的數(shù)據(jù)
2. 獲取指定的日期的周一和周日
3. 使用 groupby+agg 方法統(tǒng)計(jì)每周的商品總銷量和總退單量
4. 使用 value_counts 方法統(tǒng)計(jì)商品的退單數(shù)據(jù)
5. 使用 merge 方法合并數(shù)據(jù)

數(shù)據(jù)及源碼地址：https://gitee.com/myrensheng/data_analysis

項(xiàng)目背景

早上剛到公司，小凡就被叫去開晨會。

這么早，肯定有大事要發(fā)生。偌大的會議室，擠滿了各個部門的人......

"最近公司的商品退單率很高，給公司造成了損失，各個部門匯報一下最近工作"，總經(jīng)理端坐著，一邊聽各部門負(fù)責(zé)人的報告，一邊記錄著。

接下來就是長達(dá)2個小時的報告......

總經(jīng)理給各個部門分配好任務(wù)，這次的任務(wù)直接影響年終獎的金額，要求各部門認(rèn)真負(fù)責(zé)，積極配合，高效的解決這次危機(jī)。

散會后，經(jīng)理給小凡安排工作。

"小凡，你把最近2個月的商品退單情況整理一下，下午我和業(yè)務(wù)部門開會要用"，經(jīng)理安排好，就急匆匆的離開啦。

小凡了解到，退單率是由部分商品引起的，所以小凡需要根據(jù)最近2個月的數(shù)據(jù)，找出退單率高的異常商品。

接杯水后，小凡有了思路：

將2個月分為8周，在這8周內(nèi)，統(tǒng)計(jì)每周的商品退單率排名

如果商品退單率排名超過5次排名靠前，那么就定義為異常商品

新的一天，從敲代碼開始！

獲取數(shù)據(jù)

import os
import pandas as pd
import numpy as np
from sqlalchemy import create_engine
from datetime import datetime,timedelta

# 數(shù)據(jù)庫地址：數(shù)據(jù)庫放在上一級目錄下
db_path = os.path.join(os.path.dirname(os.getcwd()), "data.db")
engine_path = "sqlite:///" + db_path
# 創(chuàng)建數(shù)據(jù)庫引擎
engine = create_engine(engine_path)

sql = """
select 
*
from
shopRefuse
"""

df = pd.read_sql(sql, engine)

df2.sample(5)

1.png

shopid：商品id

create_time：商品訂單創(chuàng)建時間

total_num：當(dāng)天總銷售商品數(shù)

td_num：當(dāng)天銷售商品退單數(shù)

數(shù)據(jù)計(jì)算

根據(jù)商品的創(chuàng)建時間獲取當(dāng)天對應(yīng)的周一和周日，函數(shù)如下：

def get_monday_to_sunday(today, weekly=0):
    """
    :function: 獲取指定日期的周一和周日的日期
    :param today: '2021-11-16'; 當(dāng)前日期：today = datetime.now().strftime('%Y-%m-%d')
    :param weekly: 獲取指定日期的上幾周或者下幾周，weekly=0當(dāng)前周，weekly=-1上一周，weekly=1下一周
    :return: 返回指定日期的周一和周日日期
    :return_type: tuple
    """
    last = weekly * 7
    today = datetime.strptime(str(today), "%Y-%m-%d")
    monday = datetime.strftime(today - timedelta(today.weekday() - last), "%Y-%m-%d")
    monday_ = datetime.strptime(monday, "%Y-%m-%d")
    sunday = datetime.strftime(monday_ + timedelta(monday_.weekday() + 6), "%Y-%m-%d")
    return "{0}|{1}".format(monday, sunday)

將數(shù)據(jù)重新拷貝一份，避免操作失誤導(dǎo)致數(shù)據(jù)重跑

df2 = df.copy()
# 為新數(shù)據(jù)添加周維度數(shù)據(jù)
df2["week_range"] = df2["create_time"].map(lambda x:get_monday_to_sunday(str(x)[:10]))
df2.sample(5)

2.png

根據(jù)新增的周維度數(shù)據(jù)，統(tǒng)計(jì)每周商品的退單率：

td_rate_df = df2.groupby(by=["shopid","week_range"],as_index=False).agg({"total_num":"sum","td_num":"sum"})
td_rate_df["td_rate"] = td_rate_df["td_num"]/td_rate_df["total_num"]
td_rate_df.sample(1)

3.png

統(tǒng)計(jì)每周總體的退單率：

week_td_rate_df = df2.groupby(by="week_range",as_index=False).agg({"total_num":"sum","td_num":"sum"})

week_td_rate_df["week_td_rate"] = week_td_rate_df["td_num"]/week_td_rate_df["total_num"]

week_td_rate_df

4.png

統(tǒng)計(jì)次數(shù)

統(tǒng)計(jì)每周高于均值的商品的次數(shù)：

merge_df = pd.merge(
    td_rate_df,week_td_rate_df[["week_range","week_td_rate"]],
    on="week_range",
    how="left"
)

merge_df["td_count"] = merge_df[["td_rate","week_td_rate"]].apply(lambda x:0 if x[0]<=x[1] else 1,axis=1)

result_df = merge_df.pivot_table(index="shopid",columns="week_range",values="td_count",margins=True,aggfunc=lambda x:x.sum())

result_df

5.png

異常商品

將超過5次退單率的商品定義為異常商品：

unnormal_shop_df = result_df[result_df["All"]>5]
unnormal_shop_df

6.png

小凡將數(shù)據(jù)保存為excel文件，方便后續(xù)的數(shù)據(jù)展示。

下午5點(diǎn)左右，小凡和經(jīng)理一起參加業(yè)務(wù)部門的會議，小凡的分析思路得到了一致的認(rèn)可。