手机中的相机是深受大家喜爱的应用之一，现在该手机厂商想要分析手机中的应用（相机）的活跃情况，需统计如下数据：
某日活跃用户（用户id）在后续的一周内的留存情况（计算次日留存用户数，3日留存用户数，7日留存用户数）
指标定义：
•某日活跃用户数，某日活跃的去重用户数。
•N日活跃用户数，某日活跃的用户数在之后的第N日活跃用户数。
•N日活跃留存率，N日留存用户数/某日活跃用户数
•例：登陆时间（20180501日）去重用户数10000，这批用户在20180503日仍有7000人活跃，则3日活跃留存率为7000/10000=70%
image.png
1、分别用Python、sql导入附件（相机.xlsx）的数据进入自己的Mysql当中，要求字段符合、表名符合（可加后缀区分）。
import pandas as pd
import numpy as np
import pymysql
pymysql.install_as_MySQLdb()
from sqlalchemy import create_engine
excel_tab = pd.read_excel('camera_app.xlsx', sheetname='Sheet1')
excel_tab.head()
engine=create_engine("mysql://root@localhost/camera_app_ana?charset=gbk")
excel_tab.to_sql("camear_app_ana_table", con = engine)
2、分析pd.to_sql中的if_exist参数可用哪些，分别是什么，有什么细节区别？认为用哪种更好。
- fail 直接插入失败
- append 在原来数据上面追加
- replace 删除原来的数据，插入新数据。
一般情况下append好一些
3、总结MYSQL的date_format、concat的用法，并以此数据举例说明。
concat('a', 1, 'b')---->a1b
4、利用MYSQL语句解题，分步骤达到目的再一次性运行，达到题目要求。
•（1）求活跃度计算
•（2）次日留存
•（3）延伸 次日、三日、七日留存
with tab_temp
as( select uid, dayno from camear_app_ana_table
where cast(app_name as char(64))='相机'
GROUP BY uid, dayno) ,-- 去重,只保留相机
temp1
as( select uid as uid1 from tab_temp where day(dayno)=1 ),
temp2
as( select uid as uid2 from tab_temp where day(dayno)=1+1 ),
temp3
as( select uid as uid3 from tab_temp where day(dayno)=1+2 ),
temp7
as( select uid as uid7 from tab_temp where day(dayno)=1+6 )
select count(uid1) as 活跃用户数,
count(uid2) as 次日留存数 ,
count(uid3) as 三日留存数,
count(uid7) as 七日留存数,
count(uid2)/count(uid1) as 次日留存率,
count(uid3)/count(uid1) as 三日留存率,
count(uid7)/count(uid1) as 七日留存率
from ((temp1 left join temp2 on uid1=uid2) --注意连环join的写法
left join temp3 on uid1=uid3)
left join temp7 on uid1=uid7;
image.png
5、用Python解题，要求函数封装，利用调用函数计算。
方法一
目标是转为这种样式
image.png
# 先是一堆预处理
cam_data = excel_tab[ excel_tab.app_name=='相机'].copy() # 筛选
cam_data = cam_data.groupby(['uid', 'dayno']).count().reset_index() # 去重
cam_data['day']=cam_data.dayno.apply(lambda x:x.day) # 抽取天 更直观
cam_pure_data = cam_data[ ['uid', 'day']] # 只保留最核心的两列
# groupby
grouped_uid = cam_pure_data.groupby('uid')
grouped_uid
# 把groupby 行转列，搞成新的df
ret_df = pd.DataFrame(columns=range(1,11))
for uid, day_df in grouped_uid:
day_df_indexed = day_df.set_index('day')
# 转化为天--》uid的映射df
#day
#3 3
#4 3
#10 3
day_df_indexed['uid'] = 1 # 只需要区分0 1 即可
day_series_indexed = day_df_indexed.uid # 转为series
org_zero = pd.Series(range(1, 11), index=range(1, 11)) # 一共就10天 1日到10日
#print(day_series_indexed)
maped = org_zero.map(day_series_indexed) # 最神奇的一步，用了摄像机的天将会变为1， 没用的不会变还是0
#print(maped)
ret_df.loc[uid] = maped;
ret_df.fillna(0, inplace=True)
print((ret_df))
结果如下：
image.png
有了这个表再计算 活跃留存都是轻轻松松。
def cal_from_df(day):
sub_df = ret_df[ [day, day+1, day+2, day+6] ].copy()
sub_df[day+1]=sub_df[day]*sub_df[day+1]
sub_df[day+2]=sub_df[day]*sub_df[day+2]
sub_df[day+6]=sub_df[day]*sub_df[day+6]
c1 = sub_df[day].sum()
c2 = sub_df[day+1].sum()
c3 = sub_df[day+2].sum()
c7 = sub_df[day+6].sum()
return c1, c2, c3, c7, c2/c1, c3/c1, c7/c1
print(cal_from_df(1))
image.png
方法二
def cal(day):
cam_data = excel_tab[ excel_tab.app_name=='相机'].copy() # 筛选
cam_data = cam_data.groupby(['uid', 'dayno']).count().reset_index() # 去重
cam_data['day']=cam_data.dayno.apply(lambda x:x.day) # 抽取天 更直观
cam_pure_data = cam_data[ ['uid', 'day']] # 只保留最核心的两列
#print(cam_pure_data)
set1 = cam_pure_data.loc[cam_pure_data['day']==day]
set2 = cam_pure_data.loc[cam_pure_data['day']==day+1]
set3 = cam_pure_data.loc[cam_pure_data['day']==day+2]
set7 = cam_pure_data.loc[cam_pure_data['day']==day+6]
c1 = set1.uid.count()
merged = pd.merge(set1, set2, how='left', on='uid')
c2 = merged.day_y.count()
merged = pd.merge(set1, set3, how='left', on='uid')
c3 = merged.day_y.count()
# print(merged)
# print(set1, set3)
merged = pd.merge(set1, set7, how='left', on='uid')
# print(merged)
# print(set1, set7)
c7 = merged.day_y.count()
return c1, c2, c3, c7, c2/c1, c3/c1, c7/c1
print(cal(1))
image.png
