一场pandas与SQL的巅峰大战（六）

预计阅读需要20分钟

本文目录：

数据准备

日活计算

SQL计算日活

pandas计算日活

留存率计算

SQL计算

次日留存计算

多日留存计算

pandas方式

小结

在之前的五篇系列文章中，我们对比了2 O j xpandas和SQL在数据方面的多项操作。

具体来讲，第一篇文章一场pandas与SQL的巅峰大战涉及到数据查看，去重计数，/ D F i c / y条件选择，合并连接，分组排序等操作。

第二篇文章一场pandas与SQL的b C Y巅峰大战（二）涉及字符串处理，窗口函数，行列转换，类型转换等操作。

第三篇文: E u e z I |章一场pandas与SQL的巅峰大战（三）围绕日期操作展开，主要讨论了日期获取，日期转换，日期计y ) O & e . 2算等内容。

第四篇文章一场pandas与SQL的巅峰大战（四）学习了在MySQL，Hive SQL和pandas中用多种方式计算日环比，周同比的方法。

第五篇文章一场pandas与SQL的巅峰大战（五）我们用多种方案实现了分组和不分组情况下累计百分比的计算。

本篇文章主要来总结学习SQL和pandas中计算日活和多日留存的方法。7 ] M X / + ! G

数据准备K 5 O # L : {

先来看一下日活和留存的定义，对任何一款Ap/ H C o T k U up而言，这两个指标都是很重要的。

日活(Daily Active U( k 7 k [ 2 F Xser，即DAU)顾名思义即每天的活跃用户，至于如何定义就有多种口径了。一方面要约定何为“活跃”，可以是启动一次App，可以是U a j I H &到达某一个页面，可以是进入: [ [App后产生某一个行为等等。

另一: / z ) 9 _方面要约定计量的口径，可以是计算用户id的去重数u 8 w z，也e ? B V c * C ] ~可以是设备id的去重数。这两种口径统计结果会有差异，原因在于未登录的用户可能存在设备id，不存在用户id；并且设备id与用户id可能存在多对多的情况。因此对于运营来讲，确定合理有效的口径是很重要的。

留存是一个动态的概念，指的是某段时间使用了产品的用户，在一段时间之后仍然在使用产品的用户，二者相比可以求出留存率。常见的留存率有次日留存率，7日留存率，30日留存率等。次日留存是指今天活跃的用户，在明天还剩下多少仍然活跃的用户。留存率越高，说明产品的粘性I C q o越好。

我们的数据是一份用户登录数据，数据来源为/ v r V：

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

数据格式比较简单：id：自增id，uid：用户唯一id。ts：用户登录的时间(精确到秒)，数据样例如下图，在公众号后台回复“对比六”可以获得本文全部的数据和代码，方便进行实操。

一场pandas与SQL的巅峰大战（六）

本次我们只用到MySQL和pandas。MySr w jQL可以直接运行我提u G C x } o供的login.sql文件加载数据，具体L S i r u 5过程可以参考前面的文章。pandas中直接使用read_csv的方式读取即可，可以参考后面的代码。

日活计算

这里我们约定日活是指每天登录的use 2 & / M * ( [ Gr_id去重数，从我们的数据来看，计h v # - 7 U算方式非常简单。

SQL计算日活

早在系列第一篇中我们就学习过q l } J ngroup by聚合操作。只需要按天分组，将uid去重计数，即可得到答案。代码如下：

selectsubstr(ts,1,10)9 s : ) g Jasdt,count(dV J pistinctuid) as dau
fromt_logingroupbysubstr(ts,1,10)
一场pandas与SQL的巅峰大战（六）

pandas计算日活

pandas计算日活也不难，同样是使用groupby= ` t ; S k 0 v ，对v F I Huid进行去, ~ h重计数。代码如下：

importpandasaspd
login_data=pd.read_csv(\'login_data.txt\'- I K p y X !,sep=\'\\t\',parse_dates=[\'ts\'])
lF S = M 7 5ogin_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_countM r [ ,.reset_index(inplace=True)
uid_count

我们增加了一列精确到天的日期数据，便于后续分组。= = e k C i在聚合时，使用了nunc # v a 8 u Cique进行去重。(在这里也纠正一下系列第一篇文章中第6部分6 ? # 1 P m u Z中的写法，np.size 是不去重的，相当于count，但又, } ^ J不能直接写np.nu, | t 6 ) dnique，所以我们采用了lambda函数的形式。感谢热心读者的指出J ? = o~)最终uid_count的输出结果如下图所示，uid列就A } D R ? ) #是我们要求的dau，结果和SQL算出来一样。可以再用rename对列进行重命名，此处略：

一场pandas与SQL的巅峰大战（六）

留存计算

如前文所示，这里我们定义，留存率是指一段时间后仍然登录的用户占第一天登录用户的比例，由于2017-01-07登录的用户太少，我们选择2017-01-12作为第一天。分别计算p j G ; + % C U *次日留存率，7日，14日留存率。

SQL方式

次日留存计算

同前面计算日环比周同比一G 2 5 ) - k k B Q样，我们可以采用自连接的方式，但连接的条件除了日l E K E b E期外，还需要加上uid，这是一个更加严格的限制。左表计数求出初始活跃用户，右表计数求出留存用户，之后就可以求出留存率。代码如下，注意连接条件：

SELECTG I r 7 ] 1 fsubstr(a.ts,1,10)asdt,count(H g ! d E J ;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_ah : k : Add(substr(a.ts,1,10),INTERVAL1day)=substr(b.ts,1,10)
groupbysubstr(a.ts,1,10)

得到的结果如下：

一场pandas与SQL的巅峰大战（六）

多日留存计算

上K { B ? h 0 M S面自连接的方法固然可行，但是如果要同时计算次日，7日，14日留存，还需要在此基础上. 2 E ) { G进行关联两次，关联条件分别为日期差为6c A ^ N T e u ! 2和13。读者可以试试写一下代码。

当数据量比较大时，多次关联在执行效率上会有瓶颈。因此我们可以考虑新的思路。在确定要求固定日留存时，我们使用了日期关联，那么~ B 3 2 K如果不确定求第几日留存的情况下，是不是可以不写日期关联的条件呢，答案是肯定的。来看代码：

selectsubstr(a.ts,1,10)asdt,count(distincta.uid),Z B L S zcount(distinctif(datediff(substr(b.ts,1,10),substr(a.ts,1,10))=1,b.uid,n[ L 0 6ull)9 S I v S h %)as1_day_remain_uid,count(distinctif(datediff(substr(b.ts,1,10),substr(a.ts,1R ; A Q,10))=6,b.uid,null))as7_day_remain_uid,count(distinctif(datediff(substr(b.ts,1,10),s- g O & u ~ i % aubstr(a.ts,1,10))=13,b.} , I . & ! Ouid,null))as14_day_remain_uidfromt_loginaleftjoint_loginbona.uid=b.uidgroupbysubstp U O g $ n A ) Tr(a.ts,1,10)

如代码所示，在关x A 9 @联时先不限制b 7 w o R { y日期，最外层查询时根据自己的目标限定日期差V _ e V S Z | N V，可以算出相应的留H o * : j 4 H存用户数，第一天的活跃用户也可以看作是日期差为0时的情况。这样就可以一次性计算多日留存了。结果如下，如果要计算留存率，只需转换为对应的百分比即可，参考前面的代码，此处略。

一场pandas与SQL的巅峰大战（六）

pandas方式

次日留存计算

pandas计算留存也是紧紧围绕我们的目标进行：同时求出第一日和次日的活跃用户数，然后求比值。同样也可以采用自连接的方式。代i : u - ?码如下(这里的步骤比较多)：

1.导~ i Q t u R T x A入数据并添加两列日期，分别是字符串格式和datetime64格式，便于后续日期计算

importpandasas. . f 8 upd
fromdatetimeime d : y Y 1 d Yporttimedelta
login_data=pd.read_csv(\'login_data.txt\',sep=\'\\t\',parse_dates=[\'ts\'])
login_data[\'day\']=login_data[\'ts\'].map(lambdV W ] ; ^ P :ax:x.strftime(\'%Y-%m-%d\')
)login_data[\'dt_tso $ t b / U\']=pd.to_datetime(login_data[\'day\'],format=\J U U'%Y-%m-%d\')login_dc $ o 5 b &ata.head()
一场pandas与SQL的巅峰大战（六）

2.构造新的dataframe，计算日期，之后与原数据进行连接。

data_1=login_data.copy()
data_1[\'dt_ts_1\']=data_1[\'dt_ts\']+timedelta(-1)
data_15 } a h 9 ; C 4 b.head()
一场pandas与SQL的巅峰大战（六）

3.合并前面的F L r S ? : l s U两个数据，使用uid和dt_ts 关联，w * / Bdt_ts_1S ; 6 Y l b ^ F q是当前日期减一天，左边是第一天活跃的用户，右边是第二天活跃的用户

merge2 G  J_1=pd.merge$ U r T h p(login_data,data_1,left_H + n Q l _ @ oon=[\'uid\',\'dt_ts\'],right_on=[\'uid\',\'dt_ts_1\'],how=\'left\')
merge_1.head(10)
一场pandas与SQL的巅峰大战（六）

4.计算第一天活跃的用户数S . v { s ^ n 2 b

init_uN u g M & R * wser=merge_1.groupby(\C ^ % *'day_xs T 8 { m  X Z B\').q J { naggregate({\'uid\':lambdax:x.nunique()})init_user.reset_index(inplace=True)
init_user.head()
一场pandas与SQL的巅峰大战（六）

5.计算次日活跃的用户数

one_day_remain_user=merge_1[merge_1[\'day_y\'].notnull()].groupby(\'day_x\').aggregate({\'uid\':S 2 : & o + / M Alambdy M X P f : ? J rax:x.nunique()})one_day_remain_user.reset_index(inplace=True)
one_day_remain_user.head()
一场pandas与SQL的巅峰大战（六）

6.合并前面两步的结果，计算最终留存

merge_one_day=pd.merge(init_user,one_C 3 R - ;dayw O a [_remain_user,on=[\'day_x\'])merge_one_day[\i : x M  [ s ,'one_remain_rate\']=merge_one_day[\'uid_y\']/merge_one_day[\'uid_x\']merge_one_day[\'one_rem; G m t uain_rate\']=merge_one_day{ o a c X u t { r[\'one_remain_rate\']*  s ; T y.apply(ls Z vambdax:format(x,\'.2%\'))
merge_one_day.hp } {ead(20)

一场pandas与SQL的巅峰大战（六）

多日留存计算

方法一：

多日留存的计算可以沿用SQL中的思路，关联时先不用带日期条件

1.计算日期差，为后续做准备

mp { / d 4 # C Nerge_all=pd.me) u n ^ 0 w @ K Hrge(login_data,login_data,on=[\'uid\'],how=\'left\'merge_all[\'diff\']=(merge_all[\'dt_ts_y\']-merge_all[\'dt_ts_x\']).maq & ` Hp(lam Y N b t R h r @bdax:x.days)#使用map取得具体数字merge_all.head()

一场pandas与SQL的巅峰大战（六）

2.计算第n天的留存人数，n=0,1,6,13。需要先进行筛选再进行计数，仍然使用nunique

diff_0=merge_all[merge_all[\'diff\']==0].groupby(\'day_x\')[\'uid\'].nuniP I O 2que()
diff_1=merge_all[merge_all[\'diff\']==1].groupby(\'day_x\')[\'uid\'].nunm j a Xique()
diff_6=merge_all[merge_all[\'diff\u f I h']==6].groupr X a  K {by(\'day_x\')[\'uN t 9 V C [ K O Aid\'].R r % ] c 2 / Rnunique()
diff_13=meF 0 . @ o %rge_all[merge_all[\'diff\']==13].groupby(\'day_x\')[\'uid\'].nunique()
difff [ X_0=diff_0.reset_index()#groupbyS _ 8 )  b R f计数后得到的是series格式，reset得到dataframe
diff_1=dif i u B Z ) j [ +f_1.reset_index()
diff_6=diff_6.reset_index()
diff_13=diff_13.res~  e 7 R J ` s ;et_index()

3.对多个dataframe进行一次合F - O r A并

liucun=pd.meu . l = ( ^ Mrge(pd.merge(pd.merge(diff_0,diff_2 j H 6 g W Y ;1,on=[\n f f 7'day_x\'],how=\'left\'),diff_6,on=[\'day_x\'],howb 0 M % { j=\'left\'),diff_13,on=[\, y  /'day_x\'],how=\B u M j { H U 1 B'left\')
liucun.head()

一场pandas与SQL的巅峰大战（六）

4.对结果重命名，并用0填充na值

liucun.columns=[\'day\',\'init\',\'one_day_remain\',\'sevw A fen_day_rw 8 `emain\',\'fifteen_day_remain\']#后来发现英文写错了，将就看，懒得改了
liucun.fillna(0,inplace=f q D  H I [ xTrue)
liucun.head(20)
一场pandas与SQL的巅峰大战（六）

得到的结果和SQL计算的一致m 2 g P & F ! d，同样省略了百分比转换的代码。

方法二：

这种方法是从网上看到的，* p y也放在这里供大家学习，文末有a O d C链接。它没有用自关联，而是对日期进行循环，计算当日的活跃用户数和n天后的活跃用户数。把n作为 j r参数传入封装好的函数中。参考下面代码：

defcal_n_day_remain(df,n):
    dates=pd.Series(logt h iin_data.dt_ts.unique()).sort_va{ m T ] p & 0 olues()[:-n]#取截止到nJ 7 )天的日期，保证有n日留存
    users=[]#定义列表存放初始用户数remains=[]#定义列表存放留存用户数
    fordindateq n - $ Ms:
        user=login_data[login_data[\'dt_ts\']==d][\'uid\'].unique()#当日活 Q s E $ l X K跃用户
        user_n_day=login_d/ 6 ? g l 3 0 R cata[login_data[\'dt_ts\']==d+timedelta(n)][\'uid\'].unique()#0 e |n日后活跃用户
        remain=[xforxinuser_n_dayifxinuser]#取交集
        users.append(len(user))
        remains.append(len(remain))#一次循环计算一天的n日留存#循环结束后构造dataframe并返G ` q f d [ 3 [回
    remain_df=pd.DataFrame(= q 3 .{\'days\':dates,$ ? ) D x = T\'user\':users,\'remain\':remaG w Lins})
    returnremain_df

代码的逻辑整体比较简单，必要的部分我做了注释。但需要一次一次调用，最4 K s F后再merge起来。最后结果如下所示，从左到右依K W = y次是次日，7日，14日留存，和前面结果一样(可以 U N再重命名一下)。

one_day_remaiF , z zn=cal_n_day_remain(loz x T Z u l S Lgin_data,1)
seven_dayF ` U ) ._remain=cal_n_day_remain(login_data,6)
fifteen_day_remain=cal_n_day_; K Nremain(login_data,13)
liucun2=pd.merge(pd.merge5 Q f H K(one_dd a F h vay_remain,seven_day_remain[[\'days\',\'remain\']],on=[\'days\'],how=\'le% s / | V ) ; ; Rft\'),fifteen_day_remain[[\'days\',\'remain\']],on=[\'dv M h 4 ! M [ r Qays\'],how=\'left\')
liucun2.head(20)
一场pandas与SQL的巅峰大战（六）

至此，我们完成了SQL和pandas对日活和留存率的计算。

小结

本篇文章我们研究了非常重要的两个概念，日活和留存。探讨了如何用SQL和paL N I 8 ! v Yndas进行计算。日活计算比较简单。留存计算可以有多种思f 7 F ! 4 P z $ l路。pandas计算这两个指标没有特别之处，但是用到了前面文章中的分组聚合(第一篇)，日期处理(第三篇)的部分，正好帮我们复习一下。后台回复“对比六”可以获取本文pdf版本、U q O ] r D z 6数据和代码~希望对你有所帮助！

refere9 A ^ = o k 5nce

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

一场pandas与SQL的巅峰大战（六）

本文系本站编辑转载，文章版权归原作者所有，内容为作者个人观点，转载目的在于传递更多信息，并不代表本站赞同其观点和对其真实性负责。如涉及作品内容、版权和其它问题，请与本站联系，本站将在第一时间删除内容！
上一篇

中本聪之后，是他撑起了比特币的一片蓝天（上）

下一篇

Sentinel -流量控制、熔断降级、系统负载保护解析

评论已经被关闭。

插入图片
返回顶部