Python+SQL實(shí)戰(zhàn)：京東用戶行為數(shù)據(jù)分析案例解析（上）

# 導(dǎo)入python相關(guān)模塊
import?numpy as?np
import?pandas as?pd
import?seaborn as?sns
import?matplotlib.pyplot as?plt
from?datetime import?datetime
plt.style.use('ggplot')

%matplotlib inline

# 設(shè)置中文編碼和負(fù)號(hào)的正常顯示
plt.rcParams['font.sans-serif']=['SimHei']
plt.rcParams['axes.unicode_minus']=False

#?讀取數(shù)據(jù)，數(shù)據(jù)集較大，如果計(jì)算機(jī)讀取內(nèi)存不夠用，可以嘗試kaggle比賽
#?中的reduce_mem_usage函數(shù)，附在文末，主要原理是把int64/float64
#?類型的數(shù)值用更小的int(float)32/16/8來(lái)搞定
user_action = pd.read_csv('jdata_action.csv')

# 因數(shù)據(jù)集過(guò)大，本文截取'2018-03-30'至'2018-04-15'之間的數(shù)據(jù)完成本次分析
# 注：僅4月份的數(shù)據(jù)包含加購(gòu)物車行為，即type == 5
user_data?= user_action[(user_action['action_time'] > '2018-03-30') & (user_action['action_time'] < '2018-04-15')]

# 存至本地備用
user_data.to_csv('user_data.csv',sep=',')

# 查看原始數(shù)據(jù)各字段類型
behavior?= pd.read_csv('user_data.csv', index_col=0)
behavior[:10]

# OUTPUT
user_id???sku_id???action_time???module_id???type
17???1455298???208441???2018-04-11?15:21:43???6190659???1
18???1455298???334318???2018-04-11?15:14:54???6190659???1
19???1455298???237755???2018-04-11?15:14:13???6190659???1
20???1455298???6422???2018-04-11?15:22:25???6190659???1
21???1455298???268566???2018-04-11?15:14:26???6190659???1
22???1455298???115915???2018-04-11?15:13:35???6190659???1
23???1455298???208254???2018-04-11?15:22:16???6190659???1
24???1455298???177209???2018-04-14?14:09:59???6628254???1
25???1455298???71793???2018-04-14?14:10:29???6628254???1
26???1455298???141950???2018-04-12?15:37:53???10207258???1

behavior.info()

# OUTPUT
'pandas.core.frame.DataFrame'>
Int64Index: 7540394?entries, 17?to 37214234
Data columns (total 5?columns):
user_id int64
sku_id int64
action_time object
module_id int64
type???????????int64
dtypes: int64(4), object(1)
memory usage: 345.2+ MB

#?查看缺失值 
behavior.isnull().sum()

# OUTPUT
user_id 0
sku_id 0
action_time 0
module_id 0
type???????????0
dtype: int64

# 原始數(shù)據(jù)中時(shí)間列action_time，時(shí)間和日期是在一起的，不方便分析，對(duì)action_time列進(jìn)行處理，拆分出日期和時(shí)間列，并添加星期字段求出每天對(duì)應(yīng)
# 的星期，方便后續(xù)按時(shí)間緯度對(duì)數(shù)據(jù)進(jìn)行分析
behavior['date'] = pd.to_datetime(behavior['action_time']).dt.date # 日期
behavior['hour'] = pd.to_datetime(behavior['action_time']).dt.hour # 時(shí)間
behavior['weekday'] = pd.to_datetime(behavior['action_time']).dt.weekday_name # 周

# 去除與分析無(wú)關(guān)的列
behavior?= behavior.drop('module_id', axis=1)

# 將用戶行為標(biāo)簽由數(shù)字類型改為用字符表示
behavior_type = {1:'pv',2:'pay',3:'fav',4:'comm',5:'cart'}
behavior['type'] = behavior['type'].apply(lambda?x: behavior_type[x])
behavior.reset_index(drop=True,inplace=True)

#?查看處理好的數(shù)據(jù)
behavior[:10]

# OUTPUT
user_id???sku_id???action_time???type???date???hour???weekday
0???1455298???208441???2018-04-11?15:21:43???pv???2018-04-11???15???Wednesday
1???1455298???334318???2018-04-11?15:14:54???pv???2018-04-11???15???Wednesday
2???1455298???237755???2018-04-11?15:14:13???pv???2018-04-11???15???Wednesday
3???1455298???6422???2018-04-11?15:22:25???pv???2018-04-11???15???Wednesday
4???1455298???268566???2018-04-11?15:14:26???pv???2018-04-11???15???Wednesday
5???1455298???115915???2018-04-11?15:13:35???pv???2018-04-11???15???Wednesday
6???1455298???208254???2018-04-11?15:22:16???pv???2018-04-11???15???Wednesday
7???1455298???177209???2018-04-14?14:09:59???pv???2018-04-14???14???Saturday
8???1455298???71793???2018-04-14?14:10:29???pv???2018-04-14???14???Saturday
9???1455298???141950???2018-04-12?15:37:53???pv???2018-04-12???15???Thursday

# 總訪問(wèn)量
pv?= behavior[behavior['type'] == 'pv']['user_id'].count()
# 總訪客數(shù)
uv?= behavior['user_id'].nunique()
# 消費(fèi)用戶數(shù) 
user_pay?= behavior[behavior['type'] == 'pay']['user_id'].unique()
# 日均訪問(wèn)量
pv_per_day?= pv / behavior['date'].nunique()
# 人均訪問(wèn)量
pv_per_user?= pv / uv
# 消費(fèi)用戶訪問(wèn)量
pv_pay?= behavior[behavior['user_id'].isin(user_pay)]['type'].value_counts().pv
# 消費(fèi)用戶數(shù)占比
user_pay_rate?= len(user_pay) / uv
# 消費(fèi)用戶訪問(wèn)量占比
pv_pay_rate?= pv_pay / pv
# 消費(fèi)用戶人均訪問(wèn)量
pv_per_buy_user?= pv_pay / len(user_pay)

# SQL
SELECT count(DISTINCT user_id) UV, 
(SELECT count(*) PV from behavior_sql WHERE type?= 'pv') PV
FROM behavior_sql;

SELECT count(DISTINCT user_id)
FROM behavior_sql
WHERE?WHERE type?= 'pay';

SELECT type, COUNT(*) FROM behavior_sql
WHERE 
user_id IN
(SELECT DISTINCT user_id
FROM behavior_sql
WHERE type?= 'pay')
AND type?= 'pv'
GROUP BY type;

print('總訪問(wèn)量為 %i'?%pv)
print('總訪客數(shù)為 %i'?%uv)
print('消費(fèi)用戶數(shù)為 %i'?%len(user_pay))
print('消費(fèi)用戶訪問(wèn)量為 %i'?%pv_pay)
print('日均訪問(wèn)量為 %.3f'?%pv_per_day)
print('人均訪問(wèn)量為 %.3f'?%pv_per_user)
print('消費(fèi)用戶人均訪問(wèn)量為 %.3f'?%pv_per_buy_user)
print('消費(fèi)用戶數(shù)占比為 %.3f%%'?%(user_pay_rate * 100))
print('消費(fèi)用戶訪問(wèn)量占比為 %.3f%%'?%(pv_pay_rate * 100))

# OUTPUT
總訪問(wèn)量為 6229177
總訪客數(shù)為 728959
消費(fèi)用戶數(shù)為 395874
消費(fèi)用戶訪問(wèn)量為 3918000
日均訪問(wèn)量為 389323.562
人均訪問(wèn)量為 8.545
消費(fèi)用戶人均訪問(wèn)量為 9.897
消費(fèi)用戶數(shù)占比為 54.307%
消費(fèi)用戶訪問(wèn)量占比為 62.898%

# 跳失率：只進(jìn)行了一次操作就離開的用戶數(shù)/總用戶數(shù)
attrition_rates?= sum(behavior.groupby('user_id')['type'].count() == 1) / (behavior['user_id'].nunique())

# SQL
SELECT 
(SELECT COUNT(*)
FROM?(SELECT user_id 
FROM behavior_sql GROUP BY user_id 
HAVING COUNT(type)=1) A) /
(SELECT COUNT(DISTINCT user_id) UV FROM behavior_sql) attrition_rates;

print('跳失率為 %.3f%%'??%(attrition_rates * 100) )

#?OUTPUT
跳失率為 22.585%

# 單個(gè)用戶消費(fèi)總次數(shù)
total_buy_count = (behavior[behavior['type']=='pay'].groupby(['user_id'])['type'].count()
???????????????????.to_frame().rename(columns={'type':'total'}))
# 消費(fèi)次數(shù)前10客戶
topbuyer10 = total_buy_count.sort_values(by='total',ascending=False)[:10]
# 復(fù)購(gòu)率
re_buy_rate = total_buy_count[total_buy_count>=2].count()/total_buy_count.count()

# SQL
#消費(fèi)次數(shù)前10客戶

SELECT user_id, COUNT(type) total_buy_count 
FROM behavior_sql 
WHERE type = 'pay'
GROUP BY user_id
ORDER BY COUNT(type) DESC
LIMIT 10


#復(fù)購(gòu)率
CREAT VIEW v_buy_count 
AS?SELECT user_id, COUNT(type) total_buy_count
FROM behavior_sql
WHERE type = 'pay'
GROUP BY user_id;

SELECT CONCAT(ROUND((SUM(CASE?WHEN total_buy_count>=2?THEN 1?ELSE?0?END)/
SUM(CASE?WHEN total_buy_count>0?THEN 1?ELSE?0?END))*100,2),'%') AS?re_buy_rate 
FROM v_buy_count;

topbuyer10.reset_index().style.bar(color='skyblue',subset=['total'])

# 單個(gè)用戶消費(fèi)總次數(shù)可視化
tbc_box = total_buy_count.reset_index()
fig, ax = plt.subplots(figsize=[16,6])
ax.set_yscale("log")
sns.countplot(x=tbc_box['total'],data=tbc_box,palette='Set1')
for?p?in ax.patches:
????????ax.annotate('{:.2f}%'.format(100*p.get_height()/len(tbc_box['total'])), (p.get_x() - 0.1, p.get_height()))
plt.title('用戶消費(fèi)總次數(shù)')

print('復(fù)購(gòu)率為 %.3f%%'??%(re_buy_rate * 100) )

#?OUTPUT
復(fù)購(gòu)率為 13.419%

# 日活躍人數(shù)（有一次操作即視為活躍） 
daily_active_user?= behavior.groupby('date')['user_id'].nunique()
# 日消費(fèi)人數(shù)
daily_buy_user?= behavior[behavior['type'] == 'pay'].groupby('date')['user_id'].nunique()
# 日消費(fèi)人數(shù)占比
proportion_of_buyer?= daily_buy_user / daily_active_user
# 日消費(fèi)總次數(shù)
daily_buy_count?= behavior[behavior['type'] == 'pay'].groupby('date')['type'].count()
# 消費(fèi)用戶日人均消費(fèi)次數(shù)
consumption_per_buyer?= daily_buy_count / daily_buy_user

# SQL
# 日消費(fèi)總次數(shù)
SELECT date, COUNT(type) pay_daily FROM behavior_sql 
WHERE type?= 'pay'
GROUP BY date;
# 日活躍人數(shù)
SELECT date, COUNT(DISTINCT user_id) uv_daily FROM behavior_sql 
GROUP BY date;
# 日消費(fèi)人數(shù)
SELECT date, COUNT(DISTINCT user_id) user_pay_daily FROM behavior_sql 
WHERE type?= 'pay'
GROUP BY date;

# 日消費(fèi)人數(shù)占比
SELECT
(SELECT date, COUNT(DISTINCT user_id) user_pay_daily FROM behavior_sql 
WHERE type?= 'pay'
GROUP BY date) /
(SELECT date, COUNT(DISTINCT user_id) uv_daily FROM behavior_sql 
GROUP BY date) 
# 日人均消費(fèi)次數(shù)
SELECT
(SELECT date, COUNT(type) pay_daily FROM behavior_sql 
WHERE type?= 'pay'
GROUP BY date) /
(SELECT date, COUNT(DISTINCT user_id) uv_daily FROM behavior_sql 
GROUP BY date)

# 日消費(fèi)人數(shù)占比可視化

# 柱狀圖數(shù)據(jù)
pob_bar = (pd.merge(daily_active_user,daily_buy_user,on='date').reset_index()
???????????.rename(columns={'user_id_x':'日活躍人數(shù)','user_id_y':'日消費(fèi)人數(shù)'})
???????????.set_index('date').stack().reset_index().rename(columns={'level_1':'Variable',0: 'Value'}))
# 線圖數(shù)據(jù)
pob_line = proportion_of_buyer.reset_index().rename(columns={'user_id':'Rate'})

fig1 = plt.figure(figsize=[16,6])
ax1 = fig1.add_subplot(111)
ax2 = ax1.twinx()

sns.barplot(x='date', y='Value', hue='Variable', data=pob_bar, ax=ax1, alpha=0.8, palette='husl')

ax1.legend().set_title('')
ax1.legend().remove() 

sns.pointplot(pob_line['date'], pob_line['Rate'], ax=ax2,markers='D', linestyles='--',color='teal')
x=list(range(0,16))
for?a,b?in zip(x,pob_line['Rate']):
????plt.text(a+0.1, b?+ 0.001, '%.2f%%'?% (b*100), ha='center', va= 'bottom',fontsize=12)

fig1.legend(loc='upper center',ncol=2)
plt.title('日消費(fèi)人數(shù)占比')

# 消費(fèi)用戶日人均消費(fèi)次數(shù)可視化

# 柱狀圖數(shù)據(jù)
cpb_bar = (daily_buy_count.reset_index().rename(columns={'type':'Num'}))
# 線圖數(shù)據(jù)
cpb_line = (consumption_per_buyer.reset_index().rename(columns={0:'Frequency'}))

fig2 = plt.figure(figsize=[16,6])
ax3 = fig2.add_subplot(111)
ax4 = ax3.twinx()

sns.barplot(x='date', y='Num', data=cpb_bar, ax=ax3, alpha=0.8, palette='pastel')
sns.pointplot(cpb_line['date'], cpb_line['Frequency'], ax=ax4, markers='D', linestyles='--',color='teal')

x=list(range(0,16))
for?a,b?in zip(x,cpb_line['Frequency']):
????plt.text(a+0.1, b?+ 0.001, '%.2f'?% b, ha='center', va= 'bottom',fontsize=12)
plt.title('消費(fèi)用戶日人均消費(fèi)次數(shù)')

dau3_df?= behavior.groupby(['date','user_id'])['type'].count().reset_index()
dau3_df?= dau3_df[dau3_df['type'] >= 3]

# 每日高活躍用戶數(shù)（每日操作數(shù)大于3次）
dau3_num?= dau3_df.groupby('date')['user_id'].nunique()

# SQL
SELECT date, COUNT(DISTINCT user_id) 
FROM
(SELECT date, user_id, COUNT(type)
FROM behavior_sql
GROUP BY date, user_id
HAVING COUNT(type) >= 3) dau3
GROUP BY date;

fig, ax = plt.subplots(figsize=[16,6])
sns.pointplot(dau3_num.index, dau3_num.values, markers='D', linestyles='--',color='teal')
x=list(range(0,16))
for?a,b?in zip(x,dau3_num.values):
????plt.text(a+0.1, b?+ 300?, '%i'?% b, ha='center', va= 'bottom',fontsize=14)
plt.title('每日高活躍用戶數(shù)')

# 高活躍用戶累計(jì)活躍天數(shù)分布
dau3_cumsum?= dau3_df.groupby('user_id')['date'].count()

# SQL
SELECT user_id, COUNT(date) 
FROM
(SELECT date, user_id, COUNT(type)
FROM behavior_sql
GROUP BY date, user_id
HAVING COUNT(type) >= 3) dau3
GROUP BY user_id;

fig, ax = plt.subplots(figsize=[16,6])
ax.set_yscale("log")
sns.countplot(dau3_cumsum.values,palette='Set1')
for?p?in ax.patches:
????????ax.annotate('{:.2f}%'.format(100*p.get_height()/len(dau3_cumsum.values)), (p.get_x() + 0.2, p.get_height() + 100))
plt.title('高活躍用戶累計(jì)活躍天數(shù)分布')

#每日瀏覽量
pv_daily?= behavior[behavior['type'] == 'pv'].groupby('date')['user_id'].count()
#每日訪客數(shù)
uv_daily?= behavior.groupby('date')['user_id'].nunique()

# SQL
#每日瀏覽量
SELECT date, COUNT(type) pv_daily FROM behavior_sql 
WHERE type?= 'pv'
GROUP BY date;
#每日訪客數(shù)
SELECT date, COUNT(DISTINCT user_id) uv_daily FROM behavior_sql 
GROUP BY date;

# 每日瀏覽量可視化

fig, ax = plt.subplots(figsize=[16,6])
sns.pointplot(pv_daily.index, pv_daily.values,markers='D', linestyles='--',color='dodgerblue')
x=list(range(0,16))
for?a,b?in zip(x,pv_daily.values):
????plt.text(a+0.1, b?+ 2000?, '%i'?% b, ha='center', va= 'bottom',fontsize=14)
plt.title('每日瀏覽量')

# 每日訪客數(shù)可視化

fig, ax = plt.subplots(figsize=[16,6])
sns.pointplot(uv_daily.index, uv_daily.values, markers='H', linestyles='--',color='m')
x=list(range(0,16))
for?a,b?in zip(x,uv_daily.values):
????plt.text(a+0.1, b?+ 500?, '%i'?% b, ha='center', va= 'bottom',fontsize=14)
plt.title('每日訪客數(shù)')

#每時(shí)瀏覽量
pv_hourly?= behavior[behavior['type'] == 'pv'].groupby('hour')['user_id'].count()
#每時(shí)訪客數(shù)
uv_hourly?= behavior.groupby('hour')['user_id'].nunique()

# SQL 
# 每時(shí)瀏覽量
SELECT date, COUNT(type) pv_daily FROM behavior_sql 
WHERE type?= 'pv'
GROUP BY hour;
# 每時(shí)訪客數(shù)
SELECT date, COUNT(DISTINCT user_id) uv_daily FROM behavior_sql 
GROUP BY hour;

# 瀏覽量隨小時(shí)變化可視化

fig, ax = plt.subplots(figsize=[16,6])
sns.pointplot(pv_hourly.index, pv_hourly.values, markers='H', linestyles='--',color='dodgerblue')

for?a,b?in zip(pv_hourly.index,pv_hourly.values):
????plt.text(a, b?+ 10000?, '%i'?% b, ha='center', va= 'bottom',fontsize=12)
plt.title('瀏覽量隨小時(shí)變化')

# 訪客數(shù)隨小時(shí)變化可視化
fig, ax = plt.subplots(figsize=[16,6])
sns.pointplot(uv_hourly.index, uv_hourly.values, markers='H', linestyles='--',color='m')

for?a,b?in zip(uv_hourly.index,uv_hourly.values):
????plt.text(a, b?+ 1000?, '%i'?% b, ha='center', va= 'bottom',fontsize=12)
plt.title('訪客數(shù)隨小時(shí)變化')

# 用戶各操作隨小時(shí)變化
type_detail_hour?= pd.pivot_table(columns = 'type',index = 'hour', data = behavior,aggfunc=np.size,values = 'user_id')
# 用戶各操作隨星期變化
type_detail_weekday?= pd.pivot_table(columns = 'type',index = 'weekday', data = behavior,aggfunc=np.size,values = 'user_id')
type_detail_weekday?= type_detail_weekday.reindex(['Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday'])

# SQL
# 用戶各操作隨小時(shí)變化 
SELECT hour,
SUM(CASE?WHEN behavior='pv'?THEN 1?ELSE?0?END)AS?'pv',
SUM(CASE?WHEN behavior='fav'?THEN 1?ELSE?0?END)AS?'fav',
SUM(CASE?WHEN behavior='cart'?THEN 1?ELSE?0?END)AS?'cart',
SUM(CASE?WHEN behavior='pay'?THEN 1?ELSE?0?END)AS?'pay'
FROM behavior_sql
GROUP BY hour
ORDER BY hour


# 用戶各操作隨星期變化 
SELECT weekday,
SUM(CASE?WHEN behavior='pv'?THEN 1?ELSE?0?END)AS?'pv',
SUM(CASE?WHEN behavior='fav'?THEN 1?ELSE?0?END)AS?'fav',
SUM(CASE?WHEN behavior='cart'?THEN 1?ELSE?0?END)AS?'cart',
SUM(CASE?WHEN behavior='pay'?THEN 1?ELSE?0?END)AS?'pay'
FROM behavior_sql
GROUP BY weekday
ORDER BY weekday

tdh_line?= type_detail_hour.stack().reset_index().rename(columns={0: 'Value'})
tdw_line?= type_detail_weekday.stack().reset_index().rename(columns={0: 'Value'})
tdh_line= tdh_line[~(tdh_line['type'] == 'pv')]
tdw_line= tdw_line[~(tdw_line['type'] == 'pv')]

# 用戶操作隨小時(shí)變化可視化
fig, ax = plt.subplots(figsize=[16,6])
sns.pointplot(x='hour', y='Value', hue='type', data=tdh_line, linestyles='--')
plt.title('用戶操作隨小時(shí)變化')

# 用戶操作隨星期變化可視化
fig, ax = plt.subplots(figsize=[16,6])
sns.pointplot(x='weekday', y='Value', hue='type', data=tdw_line[~(tdw_line['type'] == 'pv')], linestyles='--')
plt.title('用戶操作隨星期變化')