一場pandas與SQL的巔峰大戰（六）

本文目錄：

數據準備

日活計算

SQL計算日活

pandas計算日活

留存率計算

SQL計算

次日留存計算

多日留存計算

pandas方式

小結

在之前的五篇系列文章中，我們對比了pandas和SQL在數據方面的多項操作。

具體來講，第一篇文章 涉及到數據查看，去重計數，條件選擇，合併連接，分組排序等操作。

第二篇文章 涉及字符串處理，窗口函數

第三篇文章 圍繞日期操作展開，主要討論了日期獲取，日期轉換，日期計算等內容。

第四篇文章 學習了在MySQL，Hive SQL和pandas中用多種方式計算日環比，周同比的方法。

第五篇文章 我們用多種方案實現了分組和不分組情況下累計百分比的計算。

本篇文章主要來總結學習SQL和pandas中計算日活和多日留存的方法。

數據準備

先來看一下日活和留存的定義，對任何一款App而言，這兩個指標都是很重要的。

日活(Daily Active User，即DAU)

另一方面要約定計量的口徑，可以是計算用戶id的去重數，也可以是設備id的去重數。這兩種口徑統計結果會有差異，原因在於未登錄的用戶可能存在設備id，不存在用戶id；並且設備id與用戶id可能存在多對多的情況。因此對於運營來講，確定合理有效的口徑是很重要的。

留存是一個動態的概念，指的是某段時間使用了產品的用戶，在一段時間之後仍然在使用產品的用戶，二者相比可以求出留存率。常見的留存率有次日留存率，7日留存率，30日留存率等。次日留存是指今天活躍的用戶，在明天還剩下多少仍然活躍的用戶。留存率越高，說明產品的粘性越好。

我們的數據是一份用戶登錄數據，數據來源為：

https://www.kaggle.com/nikhil04/login-time-for-users 。

數據格式比較簡單：id：自增id，uid：用戶唯一id。ts：用戶登錄的時間(精確到秒)，數據樣例如下圖，在公眾號後臺回覆“

本次我們只用到MySQL和pandas。MySQL可以直接運行我提供的login.sql文件加載數據，具體過程可以參考前面的文章。pandas中直接使用read_csv的方式讀取即可，可以參考後面的代碼。

日活計算

這裡我們約定日活是指每天登錄的user_id去重數，從我們的數據來看，計算方式非常簡單。

SQL計算日活

早在系列第一篇中我們就學習過group by聚合操作。只需要按天分組，將uid去重計數，即可得到答案。代碼如下：

<code>selectsubstr(ts,1,10)asdt,count(distinctuid) as dau
fromt_logingroupbysubstr(ts,1,10)/<code>

pandas計算日活

pandas計算日活也不難，同樣是使用groupby ，對uid進行去重計數。代碼如下：

<code>importpandasaspd
login_data=pd.read_csv('login_data.txt',sep='\\t',parse_dates=['ts'])
login_data.head()

login_data['day']=login_data['ts'].map(lambdax:x.strftime('%Y-%m-%d'))
uid_count=login_data.groupby('day').aggregate({'uid':lambdax:x.nunique()})
uid_count.reset_index(inplace=True)
uid_count/<code>

我們增加了一列精確到天的日期數據，便於後續分組。在聚合時，使用了nunique進行去重。(在這裡也糾正一下系列第一篇文章中第6部分中的寫法，np.size 是不去重的，相當於count，但又不能直接寫np.nunique，所以我們採用了lambda函數的形式。感謝熱心讀者的指出~)最終uid_count的輸出結果如下圖所示，uid列就是我們要求的dau，結果和SQL算出來一樣。可以再用rename對列進行重命名，此處略：

留存計算

如前文所示，這裡我們定義，留存率是指一段時間後仍然登錄的用戶佔第一天登錄用戶的比例，由於2017-01-07登錄的用戶太少，我們選擇2017-01-12作為第一天。分別計算次日留存率，7日，14日留存率。

SQL方式

次日留存計算

同前面計算日環比周同比一樣，我們可以採用自連接的方式，但連接的條件除了日期外，還需要加上uid，這是一個更加嚴格的限制。左表計數求出初始活躍用戶，右表計數求出留存用戶，之後就可以求出留存率。代碼如下，注意連接條件：

<code>SELECTsubstr(a.ts,1,10)asdt,count(distincta.uid),count(distinctb.uid),
  concat(round((count(distinctb.uid)/count(distincta.uid))*100,2),'%')as1_day_remain
fromt_loginaleftjoint_loginbona.uid=b.uid
anddate_add(substr(a.ts,1,10),INTERVAL1day)=substr(b.ts,1,10)
groupbysubstr(a.ts,1,10)/<code>

得到的結果如下：

多日留存計算

上面自連接的方法固然可行，但是如果要同時計算次日，7日，14日留存，還需要在此基礎上進行關聯兩次，關聯條件分別為日期差為6和13。讀者可以試試寫一下代碼。

當數據量比較大時，多次關聯在執行效率上會有瓶頸。因此我們可以考慮新的思路。在確定要求固定日留存時，我們使用了日期關聯，那麼如果不確定求第幾日留存的情況下，是不是可以不寫日期關聯的條件呢，答案是肯定的。來看代碼：

<code>selectsubstr(a.ts,1,10)asdt,count(distincta.uid),count(distinctif(datediff(substr(b.ts,1,10),substr(a.ts,1,10))=1,b.uid,null))as1_day_remain_uid,count(distinctif(datediff(substr(b.ts,1,10),substr(a.ts,1,10))=6,b.uid,null))as7_day_remain_uid,count(distinctif(datediff(substr(b.ts,1,10),substr(a.ts,1,10))=13,b.uid,null))as14_day_remain_uidfromt_loginaleftjoint_loginbona.uid=b.uidgroupbysubstr(a.ts,1,10)/<code>

如代碼所示，在關聯時先不限制日期，最外層查詢時根據自己的目標限定日期差，可以算出相應的留存用戶數，第一天的活躍用戶也可以看作是日期差為0時的情況。這樣就可以一次性計算多日留存了。結果如下，如果要計算留存率，只需轉換為對應的百分比即可，參考前面的代碼，此處略。

pandas方式

次日留存計算

pandas計算留存也是緊緊圍繞我們的目標進行：同時求出第一日和次日的活躍用戶數，然後求比值。同樣也可以採用自連接的方式。代碼如下(這裡的步驟比較多)：

1.導入數據並添加兩列日期，分別是字符串格式和datetime64格式，便於後續日期計算

<code>importpandasaspd
fromdatetimeimporttimedelta
login_data=pd.read_csv('login_data.txt',sep='\\t',parse_dates=['ts'])
login_data['day']=login_data['ts'].map(lambdax:x.strftime('%Y-%m-%d')
)login_data['dt_ts']=pd.to_datetime(login_data['day'],format='%Y-%m-%d')login_data.head()/<code>

2.構造新的dataframe，計算日期，之後與原數據進行連接。

<code>data_1=login_data.copy()
data_1['dt_ts_1']=data_1['dt_ts']+timedelta(-1)
data_1.head()/<code>

3.合併前面的兩個數據，使用uid和dt_ts 關聯，dt_ts_1是當前日期減一天，左邊是第一天活躍的用戶，右邊是第二天活躍的用戶

<code>merge_1=pd.merge(login_data,data_1,left_on=['uid','dt_ts'],right_on=['uid','dt_ts_1'],how='left')
merge_1.head(10)/<code>

4.計算第一天活躍的用戶數

<code>init_user=merge_1.groupby('day_x').aggregate({'uid':lambdax:x.nunique()})init_user.reset_index(inplace=True)
init_user.head()/<code>

5.計算次日活躍的用戶數

<code>one_day_remain_user=merge_1[merge_1['day_y'].notnull()].groupby('day_x').aggregate({'uid':lambdax:x.nunique()})one_day_remain_user.reset_index(inplace=True)
one_day_remain_user.head()/<code>

6.合併前面兩步的結果，計算最終留存

<code>merge_one_day=pd.merge(init_user,one_day_remain_user,on=['day_x'])merge_one_day['one_remain_rate']=merge_one_day['uid_y']/merge_one_day['uid_x']merge_one_day['one_remain_rate']=merge_one_day['one_remain_rate'].apply(lambdax:format(x,'.2%'))
merge_one_day.head(20)/<code>

多日留存計算

方法一：

多日留存的計算可以沿用SQL中的思路，關聯時先不用帶日期條件

1.計算日期差，為後續做準備

<code>merge_all=pd.merge(login_data,login_data,on=['uid'],how='left'merge_all['diff']=(merge_all['dt_ts_y']-merge_all['dt_ts_x']).map(lambdax:x.days)#使用map取得具體數字merge_all.head()/<code>

2.計算第n天的留存人數，n=0,1,6,13。需要先進行篩選再進行計數，仍然使用nunique

<code>diff_0=merge_all[merge_all['diff']==0].groupby('day_x')['uid'].nunique()
diff_1=merge_all[merge_all['diff']==1].groupby('day_x')['uid'].nunique()
diff_6=merge_all[merge_all['diff']==6].groupby('day_x')['uid'].nunique()
diff_13=merge_all[merge_all['diff']==13].groupby('day_x')['uid'].nunique()
diff_0=diff_0.reset_index()#groupby計數後得到的是series格式，reset得到dataframe
diff_1=diff_1.reset_index()
diff_6=diff_6.reset_index()
diff_13=diff_13.reset_index()/<code>

3.對多個dataframe進行一次合併

<code>liucun=pd.merge(pd.merge(pd.merge(diff_0,diff_1,on=['day_x'],how='left'),diff_6,on=['day_x'],how='left'),diff_13,on=['day_x'],how='left')
liucun.head()/<code>

4.對結果重命名，並用0填充na值

<code>liucun.columns=['day','init','one_day_remain','seven_day_remain','fifteen_day_remain']#後來發現英文寫錯了，將就看，懶得改了
liucun.fillna(0,inplace=True)
liucun.head(20)/<code>

得到的結果和SQL計算的一致，同樣省略了百分比轉換的代碼。

方法二：

這種方法是從網上看到的，也放在這裡供大家學習，文末有鏈接。它沒有用自關聯，而是對日期進行循環，計算當日的活躍用戶數和n天后的活躍用戶數。把n作為參數傳入封裝好的函數中。參考下面代碼：

<code>defcal_n_day_remain(df,n):
    dates=pd.Series(login_data.dt_ts.unique()).sort_values()[:-n]#取截止到n天的日期，保證有n日留存
    users=[]#定義列表存放初始用戶數remains=[]#定義列表存放留存用戶數
    fordindates:
        user=login_data[login_data['dt_ts']==d]['uid'].unique()#當日活躍用戶
        user_n_day=login_data[login_data['dt_ts']==d+timedelta(n)]['uid'].unique()#n日後活躍用戶
        remain=[xforxinuser_n_dayifxinuser]#取交集
        users.append(len(user))
        remains.append(len(remain))#一次循環計算一天的n日留存#循環結束後構造dataframe並返回
    remain_df=pd.DataFrame({'days':dates,'user':users,'remain':remains})
    returnremain_df/<code>

代碼的邏輯整體比較簡單，必要的部分我做了註釋。但需要一次一次調用，最後再merge起來。最後結果如下所示，從左到右依次是次日，7日，14日留存，和前面結果一樣(可以再重命名一下)。

<code>one_day_remain=cal_n_day_remain(login_data,1)
seven_day_remain=cal_n_day_remain(login_data,6)
fifteen_day_remain=cal_n_day_remain(login_data,13)
liucun2=pd.merge(pd.merge(one_day_remain,seven_day_remain[['days','remain']],on=['days'],how='left'),fifteen_day_remain[['days','remain']],on=['days'],how='left')
liucun2.head(20)/<code>

至此，我們完成了SQL和pandas對日活和留存率的計算。

小結

本篇文章我們研究了非常重要的兩個概念，日活和留存。探討了如何用SQL和pandas進行計算。日活計算比較簡單。留存計算可以有多種思路。pandas計算這兩個指標沒有特別之處，但是用到了前面文章中的分組聚合(第一篇)，日期處理(第三篇)的部分，正好幫我們複習一下。後臺回覆“對比六”可以獲取本文pdf版本、數據和代碼~希望對你有所幫助！

reference

https://blog.csdn.net/msspark/article/details/86727058

閱讀更多 超哥的雜貨鋪 的文章

關鍵字: 日活 Hive 文章