學習記錄——共享汽車分析

前言

1. 本文僅記錄個人學習程序寫的代碼供自己復盤使用，如果對你有幫助和啟發那就更好了，
2. 新人作品，歡迎討論和斧正，大神輕噴，
3. 純代碼實作，無結論，
4. 一些相似的維度舉一反三就行，
5. 需要資料集練習的可以留言

目標

1. python, mysql, matplotlib 代碼練習
2. 常用資料指標的實作

資料匯入

python 連接資料庫 Mysql

#獲取資料方法
import pymysql 
import pandas as pd 
from matplotlib import pyplot as plt
from matplotlib import font_manager

def get_mysql_data(DB, sql):
    conn = pymysql.connect(host=DB['host'], port=DB['port'], user=DB['user'], password=DB['password'], database=DB['dbname'])
 
    # 創建游標
    cursor = conn.cursor()
    # 執行sql陳述句
    cursor.execute(sql)
    # 調出資料
    data = cursor.fetchall()
    # cols為欄位資訊 
    cols = cursor.description
    # 將資料轉換為DataFrame
    col = []
    for i in cols:
        col.append(i[0])
    # data轉成list形式 
    data = list(map(list, data))
    data = pd.DataFrame(data,columns=col)
    # 關閉游標以及連接
    cursor.close()
    conn.close()
    return data

日期時間處理——獲取年月日時維度

def time_data(data):
        
    data['下單年'] = data["下單時間"].astype("str").apply(lambda x: x.split('-')[0]) 
    data['下單年月'] = data["下單時間"].astype("str").apply(lambda x: x.split('-')[0]+"/"+x.split('-')[1])
    data['下單月'] = data["下單時間"].astype("str").apply(lambda x: x.split('-')[1])
    data['下單日'] = data["下單時間"].astype("str").apply(lambda x: x.split('-')[2]) 
    data['下單時'] = data["下單時間"].astype("str").apply(lambda x: x.split('-')[0]) 
    data['付款年'] = data["付款日期"].astype("str").apply(lambda x: x.split('-')[0]) 
    data['付款年月'] = data["付款日期"].astype("str").apply(lambda x: x.split('-')[0]+"/"+x.split('-')[1])
    data['付款月'] =data["付款日期"].astype("str").apply(lambda x: x.split('-')[1])
    data['付款日'] = data["付款日期"].astype("str").apply(lambda x: x.split('-')[2]) 
    data['付款時'] = data["付款日期"].astype("str").apply(lambda x: x.split('-')[0]) 
    return data



DB = { 'host':"127.0.0.1",'port':3306, 'user':'root','password':'password','dbname':'datebase'}

留存分析

# 留存分析
SQL='''select *,
concat(ROUND(100*次日留存用戶數/榷訓躍用戶數),"%") 次日留存率,
concat(ROUND(100*三日留存用戶數/榷訓躍用戶數),"%") 三日留存率,
concat(ROUND(100*七日留存用戶數/榷訓躍用戶數),"%") 七日留存率
from(
select date(a.`付款時間`) 日期 ,DATE_FORMAT(a.`付款時間`,"%H") 時間,
COUNT(DISTINCT a.`用戶ID`) 榷訓躍用戶數,
COUNT(DISTINCT b.`用戶ID`) 次日留存用戶數,
COUNT(DISTINCT c.`用戶ID`) 三日留存用戶數,
COUNT(DISTINCT d.`用戶ID`) 七日留存用戶數
from paper_data a 
left join paper_data b 
on  a.`用戶ID`=b.`用戶ID`
and date(b.`付款時間`)=date(a.`付款時間`)+1
left join paper_data c
on  a.`用戶ID`=c.`用戶ID`
and date(c.`付款時間`)=date(a.`付款時間`)+3
left join paper_data d
on  a.`用戶ID`=d.`用戶ID`
and date(d.`付款時間`)=date(a.`付款時間`)+7
where a.`付款時間` between "2020-01-01" and "2020-01-31"
GROUP BY date(a.`付款時間`)
)f;'''
liuchun = get_mysql_data(DB, SQL)
liuchun
x=liuchun['日期']
y1=liuchun['次日留存率']
y2=liuchun['三日留存率']
y3=liuchun['七日留存率']
plt.figure(figsize=(25,10),dpi=80)
my_font = font_manager.FontProperties(fname="C:/Users/wty_pc/Anaconda3/Lib/site-packages/matplotlib/mpl-data/fonts/ttf/STSONG.TTF",size = 18)
rects1=plt.plot(x,y1,color="red",alpha=0.5,linestyle="-",linewidth=3,label="次日留存率")
rects2=plt.plot(x,y2,color="g",alpha=0.5,linestyle="-",linewidth=3,label="三日留存率")
rects3=plt.plot(x,y3,color="b",alpha=0.5,linestyle="-",linewidth=3,label="七日留存率")
rects4=plt.legend(prop=my_font,loc="best")
for i in range(len(x)):
        plt.text(x[i],y1[i],y1[i],fontsize=15,ha="center")

for i in range(len(x)):
        plt.text(x[i],y2[i],y2[i],fontsize=15,ha="center")
for i in range(len(x)):
        plt.text(x[i],y3[i],y3[i],fontsize=15,ha="center")
        
plt.show()

訂單分析

訂單分析(可以把訂單換成其他的，比如用戶數，銷售量等資料)

# 1.根據下單的時間來統計訂單量
# (1)年維度
SQL='''select date_format(付款時間,"%Y")下單年 ,count(訂單ID) as 訂單量 from paper_data group by date_format(付款時間,"%Y")'''
order_num_year=get_mysql_data(DB, SQL)

my_font = font_manager.FontProperties(fname="C:/Users/wty_pc/Anaconda3/Lib/site-packages/matplotlib/mpl-data/fonts/ttf/STSONG.TTF",size = 18)
x=order_num_year["下單年"]
y=order_num_year["訂單量"]
plt.figure(figsize=(20,8),dpi=80)
rects = plt.bar(x,y,width=0.3,color="b",label="2019")
# 標注
for rect in rects:
    height=rect.get_height()
    plt.text(rect.get_x()+rect.get_width()/2,height+0.3,str(height),ha="center")
plt.title("年度訂單量",FontProperties=my_font)
plt.xlabel("年份",FontProperties=my_font)
plt.ylabel("訂單量",FontProperties=my_font)
plt.legend(prop=my_font,loc="best")
plt.show()

# 訂單分析
# 1.根據下單的時間來統計訂單量
# (2)年月維度
SQL='''select date_format(付款時間,"%Y/%m")下單年月 ,count(訂單ID) as 訂單量 from paper_data group by date_format(付款時間,"%Y/%m")'''
order_num_month=get_mysql_data(DB, SQL)

my_font = font_manager.FontProperties(fname="C:/Users/wty_pc/Anaconda3/Lib/site-packages/matplotlib/mpl-data/fonts/ttf/STSONG.TTF",size = 18)
x=order_num_month["下單年月"]
y=order_num_month["訂單量"]
plt.figure(figsize=(20,8),dpi=80)
rects = plt.bar(x,y,width=0.3,color=["r","g","b"])
# 標注
for rect in rects:
    height=rect.get_height()
    plt.text(rect.get_x()+rect.get_width()/2,height+0.3,str(height),ha="center")
plt.show()

# (3)年月、城市維度（折線圖）
SQL='''select date_format(下單時間,"%Y/%m")下單年月 ,城市,count(訂單ID) as 訂單量 
from paper_data
group by date_format(下單時間,"%Y/%m"),城市
order by date_format(下單時間,"%Y/%m");
'''
order_num_month1=get_mysql_data(DB, SQL)



#把每個城市的資料取出來
order_month_city=order_num_month1.groupby(["城市"])
order_shanghai=order_month_city.get_group("上海").reset_index(drop="true")
order_beijing=order_month_city.get_group("北京").reset_index(drop="true")
order_hangzhou=order_month_city.get_group("杭州").reset_index(drop="true")
order_xian=order_month_city.get_group("西安").reset_index(drop="true")


# matplotlib畫圖部分
my_font = font_manager.FontProperties(fname="C:/Users/wty_pc/Anaconda3/Lib/site-packages/matplotlib/mpl-data/fonts/ttf/STSONG.TTF",size = 18)
plt.figure(figsize=(25,10),dpi=80)

plt.plot(order_shanghai["下單年月"],order_shanghai["訂單量"],marker='o',color="r",label="上海訂單量")
plt.plot(order_beijing["下單年月"],order_beijing["訂單量"],marker='v',color="g",label="北京訂單量")
plt.plot(order_hangzhou["下單年月"],order_hangzhou["訂單量"],marker='^',color="b",label="杭州訂單量")
plt.plot(order_xian["下單年月"],order_xian["訂單量"],marker='.',color="y",label="西安訂單量")
plt.xlabel("時間",FontProperties=my_font)
plt.ylabel("訂單量",FontProperties=my_font)
plt.title("各城市每月訂單量",FontProperties=my_font)
plt.legend(prop=my_font,loc="best")



#  df.idxmax()默認值是0，求列最大值的行索引，1就是反過來
def city_max(df):
    return df.idxmax()



# 最大值標記
plt.text(order_shanghai["下單年月"].iloc[city_max(order_shanghai["訂單量"])],order_shanghai.max()["訂單量"]+5,"上海最大訂單：{}".format(order_shanghai.max()["訂單量"]),fontsize=12,ha="center",color="r",FontProperties=my_font)
plt.text(order_beijing["下單年月"].iloc[city_max(order_beijing["訂單量"])],order_beijing.max()["訂單量"]+5,"北京最大訂單：{}".format(order_beijing.max()["訂單量"]),fontsize=12,ha="center",color="g",FontProperties=my_font)
plt.text(order_hangzhou["下單年月"].iloc[city_max(order_hangzhou["訂單量"])],order_hangzhou.max()["訂單量"]+5,"杭州最大訂單：{}".format(order_hangzhou.max()["訂單量"]),fontsize=12,ha="center",color="b",FontProperties=my_font)
plt.text(order_xian["下單年月"].iloc[city_max(order_xian["訂單量"])],order_xian.max()["訂單量"]+5,"西安最大訂單：{}".format(order_xian.max()["訂單量"]),fontsize=12,ha="center",color="y",FontProperties=my_font)
plt.show()


# -----------------------------

# 每年、每月的一樣的做法，改下日期欄位就好

# (3)年城市維度(柱狀圖)
SQL='''select date_format(付款時間,"%Y")下單年,城市,count(訂單ID) as 訂單量 
from paper_data
group by date_format(付款時間,"%Y"),城市
order by date_format(付款時間,"%Y");
'''
order_num_year=get_mysql_data(DB, SQL)
# order_num_month.describe()

# 按城市拆分資料
order_year_city=order_num_year.groupby(["城市"])
order_shanghai=order_year_city.get_group("上海").reset_index(drop="true")
order_beijing=order_year_city.get_group("北京").reset_index(drop="true")
order_hangzhou=order_year_city.get_group("杭州").reset_index(drop="true")
order_xian=order_year_city.get_group("西安").reset_index(drop="true")

# -----matplotlib畫圖部分------
my_font = font_manager.FontProperties(fname="C:/Users/wty_pc/Anaconda3/Lib/site-packages/matplotlib/mpl-data/fonts/ttf/STSONG.TTF",size = 18)
plt.figure(figsize=(25,10),dpi=80)
# 設定柱子位置，通過偏移量a來實作，
# 由于時間是datetime型別或者object(str)型別的不能直接加偏移量所以這里用長度長度實作柱子的位置，然后把刻度值重新設定 
a=0.2 
x_01=list(range(len(order_shanghai["下單年"])))
x_02=[i+a for i in x_01]
x_03=[i-2*a for i in x_01]
x_04=[i-a for i in x_01]


# 畫圖
rects1=plt.bar(x_01,order_shanghai["訂單量"],a,color="r",align='edge',label="上海訂單量")
rects2=plt.bar(x_02,order_beijing["訂單量"],a,color="g",align='edge',label="北京訂單量")
rects3=plt.bar(x_03,order_hangzhou["訂單量"],a,color="b",align='edge',label="杭州訂單量")
rects4=plt.bar(x_04,order_xian["訂單量"],a,color="y",align='edge',label="西安訂單量")

# x軸刻度
plt.xticks(x_01,labels=order_shanghai["下單年"])

# 軸名稱
plt.xlabel("年份",FontProperties=my_font)
plt.ylabel("訂單量",FontProperties=my_font)
# 標題
plt.title("各城市每月訂單量",FontProperties=my_font)
# 圖例
plt.legend(prop=my_font,loc="best")
# 標記
def mark_bar(rects):
    for rect in rects:
        height=rect.get_height()
        plt.text(rect.get_x()+rect.get_width()/2,height+0.3,str(height),fontsize=15,ha="center")
mark_bar(rects1)
mark_bar(rects2)
mark_bar(rects3)
mark_bar(rects4)
plt.show()

# 3、小時訂單量分布情況
SQL= '''SELECT date_format(下單時間,"%H")  下單時, count(`訂單ID`) 訂單量
from paper_data
where  date_format(下單時間,"%Y-%m")="2020-01"
GROUP BY date_format(下單時間,"%H") 
ORDER BY date_format(下單時間,"%H") 
;'''

h_dingdan=get_mysql_data(DB, SQL)


my_font = font_manager.FontProperties(fname="C:/Users/wty_pc/Anaconda3/Lib/site-packages/matplotlib/mpl-data/fonts/ttf/STSONG.TTF",size = 18)
plt.figure(figsize=(25,10),dpi=80)
x=h_dingdan["下單時"]
y=h_dingdan["訂單量"]
plt.plot(x,y,color="r",label="每小時訂單量")
# 設定標記
for a,b  in zip(x,y):
    #a x坐標, b坐標, (a,b) 一個點 
    # ha 水平方向  va 垂直方向  fontsize 大小 
    plt.text(a,b,b ,ha='center',va='bottom', fontsize=12)
#設定刻度
xtick_labels = ['{}:00'.format(i) for i in x]
plt.xticks(x,xtick_labels)
#設定坐標名稱
plt.xlabel("時間",FontProperties=my_font)
plt.ylabel("訂單量",FontProperties=my_font)
#設定表名
plt.title("1月每小時訂單量",FontProperties=my_font)
#設定圖例
plt.legend(prop=my_font,loc="best")
plt.show()

RFM模型

#RMF分層
SQL='''SELECT 用戶ID,COUNT(訂單ID) AS F,round(sum(實收金額),2) AS M,date_format(max(付款時間),"%Y/%m/%d") as R 
            FROM paper_data GROUP BY 用戶ID ORDER BY 用戶ID
            ;'''
User_layer=get_mysql_data(DB, SQL)
# RMF評分公式

# User_layer["R1"]=((pd.to_datetime("2020/03-01")-pd.to_datetime(User_layer["R"])) 后面的處理方式是為了把timedelta型別轉換成Int型別
User_layer["R1"]=((pd.to_datetime("2020/03-01")-pd.to_datetime(User_layer["R"]))/pd.Timedelta(1,"D")).fillna(0).astype(int)
User_layer["RR"]=(pd.to_datetime(User_layer["R"])-pd.to_datetime(min(User_layer["R"])))/((pd.to_datetime(max(User_layer["R"]))-pd.to_datetime(min(User_layer["R"]))))
User_layer["FF"]=(User_layer["F"]-min(User_layer["F"]))/(max(User_layer["F"])-min(User_layer["F"]))
User_layer["MM"]=(User_layer["M"]-min(User_layer["M"]))/(max(User_layer["M"])-min(User_layer["M"]))
User_layer["SCORE"]=100*(0.25*User_layer["FF"]+0.6*User_layer["MM"]+0.15*User_layer["RR"])
User_layer

5929 rows × 9 columns

# 根據權重SCORE四分位分組的依據
User_describe=User_layer.describe()
User_describe

# df遍歷后的結果最好用list/dict存放起來，df本身一個一個添加資料遍歷效率不高，而且難取數，最好用List/dict整體取賦值
list_level=[]
for index ,rows in User_layer.iterrows():
#     print("索引是：",index,"資料是：",rows)
    if  rows["SCORE"]<=User_describe.loc["25%"]["SCORE"]:
        rows["level"]="四等"
        
    elif  rows["SCORE"]<=User_describe.loc["50%"]["SCORE"]:
        rows["level"]="三等"
        
    elif  rows["SCORE"]<=User_describe.loc["75%"]["SCORE"]:
        rows["level"]="二等"
        
    else:
        rows["level"]="一等"  
    list_level.append(rows["level"])
    
User_layer['level']=list_level
User_layer

5929 rows × 10 columns

用戶標簽

# 用戶打標簽
SQL='''select 用戶ID,count(*) as order_num,count(date_format(付款時間,"%M")) 月數,
                  sum(case when 取車型別='自取' then 1 else 0 end) as 自取次數,
                  sum(case when 優惠金額>1 then 1 else 0 end) as 優惠券使用次數,
                  sum(case when 非網點還車費>0 then 1 else 0 end) as  還車次數
                  from paper_data 
                  group by 用戶ID
                  ;
'''

bq=get_mysql_data(DB, SQL)

data_total=pd.merge(User_layer,bq,on="用戶ID")

data_total
# print(data_total["FF"].dtypes,data_total["自取次數"].dtypes)
for index,row in data_total.iterrows():
# 取車
    if row["FF"]==0:
        row["FF"]= row["FF"]+0.0001
# Int/浮點數會報錯，注意轉換型別
        if float(row["自取次數"])/row["FF"]>0.5:
            data_total["取車偏好"]="時租居多"
        else:
            data_total["取車偏好"]="日租居多"
    #       網點還車  
        if float(row["還車次數"])/row["FF"]>0.5:
            data_total["還車偏好"]="非網點還車"
        else:
            data_total["還車偏好"]="網點還車"
    #     月均訂單
        if row["FF"]/row["月數"]>6:
            data_total["月均單量"]="月均單量大于6"
        else:
            data_total["月均單量"]="月均單量小于6"

data_total

   

5929 rows × 18 columns

用戶生命周期

def user_life(data,a,b,c):
    list_1=[]
    for index,row in data.iterrows():
#         print(row[a])
#         print(index)
        if row[a]==1:
            row[c]="引入期"
        elif row[a]>1 and row[a]<3 and row[b]<=200:
            row[c]="成長期"
        elif row[a]>=3 and row[b]<=300:
            row[c]="休眠期"
        elif row[b]>300:
            row[c]="流失期"
        else:
            row[c]="其他"
#         print(row[c])
        list_1.append(row)
    data_1=pd.DataFrame(list_1)
#     print(data_1)
    return data_1


Userlife=pd.DataFrame()
Userlife=user_life(data_total,"F","R1","生命周期")
Userlife.head(10)

"休眠期" in Userlife["生命周期"].values

True

zq=Userlife.groupby("生命周期")
yr=zq.get_group("引入期")["生命周期"].count()
cz=zq.get_group("成長期")["生命周期"].count()
xm=zq.get_group("休眠期")["生命周期"].count()
ls=zq.get_group("流失期")["生命周期"].count()

x=["引入期","成長期","休眠期","流失期"]
y=[yr,cz,xm,ls]
for i in range(len(x)):
    print(x[i],y[i])

my_font = font_manager.FontProperties(fname="C:/Users/wty_pc/Anaconda3/Lib/site-packages/matplotlib/mpl-data/fonts/ttf/STSONG.TTF",size = 18)
plt.figure(figsize=(25,10),dpi=80)
plt.rcParams['font.sans-serif']=['STSONG'] #中文標簽設定
# plt.rcParams['axes.unicode_minus']=False  #用來正常顯示坐標負號
rects=plt.bar(x,y,0.3,color=["r","g","b","y"])
plt.xticks(x,x,fontproperties=my_font)

for rect in rects:
    height=rect.get_height()
    plt.text(rect.get_x()+rect.get_width()/2,height+1,str(height),fontsize=18,ha="center")


plt.show()

引入期 2528
成長期 895
休眠期 2414
流失期 32

用戶評分統計

# 評分 
#0分代表未參與評分，可以統計用戶的參與度

SQL='''select date_format(付款時間,"%Y/%m") 付款年月,評分,count(distinct 用戶ID) as user_num,count(*) as order_num 
                   from paper_data group by date_format(付款時間,"%Y/%m"),評分;'''


pinfen_num=get_mysql_data(DB,SQL)
pinfen_num["評分"]=pinfen_num["評分"].astype(int)
pinfen_num["參與度"]=pinfen_num["user_num"]/pinfen_num["order_num"]
pinfen_num.dtypes

付款年月          object
評分             int32
user_num       int64
order_num      int64
參與度          float64
dtype: object

# 用戶評分參與度=有評分的用戶/下單用戶量
import numpy as np
# 按評分分組
pf_group=pinfen_num.groupby(["評分"])

# id_name
# 取出分組的值
# 方法一
list_pf=[]
for key,value in pf_group:
    list_pf.append(value.reset_index(drop="true"))
# 方法二格式化成串列或者字典，詳見后面一個代碼塊

x=list_pf[0]["付款年月"]
y=[]
for i in range(len(list_pf)):
    y.append(list_pf[i]["參與度"])
# print("x:",x.values)
# print("y:",y[1])


my_font = font_manager.FontProperties(fname="C:/Users/wty_pc/Anaconda3/Lib/site-packages/matplotlib/mpl-data/fonts/ttf/STSONG.TTF",size = 15)
fig=plt.figure(figsize=(20,40),dpi=80)

colors=["r","g","b"]
for i in range(1,12):
    ax=fig.add_subplot(11,1,i)
    ax.plot(x,y[i-1],color=colors[np.random.randint(3)],label="{}分".format(i-1))
    x_label="評分為{}的趨勢".format(i-1)
    ax.set_xlabel(x_label,fontproperties=my_font)
    ax.set_ylabel("參與度",fontproperties=my_font)
    ax.legend()
 #調整子圖間距    
plt.tight_layout()
plt.show()

groupby取數的技巧

# groupby 格式化成串列 為轉換成字典做鋪墊

a=list(pinfen_num.groupby(["評分"]))
# 串列的每個元素是個元組，每個元組的值為分組鍵和值（datafarme）
a[0][1].reset_index(drop = True)
# print(type(a[1]))

# groupby 格式化成字典
a=dict(list(pinfen_num.groupby(["評分"])))
# type(a[0])
a[0].reset_index(drop=True)

pinfen_num.groupby(["評分"]).size()

評分
0     26
1     26
2     26
3     26
4     26
5     26
6     26
7     26
8     26
9     26
10    26
dtype: int64

pinfen_num.groupby(["評分"]).count()

# 每個評分最小參與度

pinfen_num["參與度"].groupby(pinfen_num["評分"]).min()
pinfen_num.groupby(pinfen_num["評分"]).min()["參與度"]
pinfen_num.groupby(pinfen_num["評分"])["參與度"].min()

評分
0     0.926357
1     0.923695
2     0.917749
3     0.925620
4     0.912000
5     0.934694
6     0.916318
7     0.929961
8     0.929577
9     0.940092
10    0.928571
Name: 參與度, dtype: float64

# 按評分選取多列
# pinfen_num.groupby(pinfen_num["評分"])[["參與度","order_num"]].min()
pinfen_num[["參與度","order_num"]].groupby(pinfen_num["評分"]).min()

# 多維分組用unstack可以解成二維表（不堆疊）
a=pinfen_num["參與度"].groupby([pinfen_num["付款年月"],pinfen_num["評分"]]).min().unstack()
a
# a.shape

#多維分組堆疊成多索引 
b=pinfen_num["參與度"].groupby([pinfen_num["付款年月"],pinfen_num["評分"]]).min()
b
# b.shape

付款年月     評分
2019/01  0     0.954357
         1     0.950000
         2     0.971074
         3     0.966805
         4     0.967871
                 ...   
2021/02  6     1.000000
         7     1.000000
         8     0.969231
         9     1.000000
         10    0.984848
Name: 參與度, Length: 286, dtype: float64

pinfen_num["參與度"].groupby(pinfen_num["評分"]).describe().unstack()

       評分
count  0     26.000000
       1     26.000000
       2     26.000000
       3     26.000000
       4     26.000000
               ...    
max    6      1.000000
       7      1.000000
       8      0.977679
       9      1.000000
       10     0.984848
Length: 88, dtype: float64

pinfen_num["參與度"].groupby(pinfen_num["評分"]).describe()