<jupyter_start><jupyter_text>读取数据<jupyter_code>import pandas as pd
import numpy as np
# 读取安装数据即首次访问
def read_install(app_name, target_day):
    base_dir = 'sample-data/section9/snapshot/install'
    f = 'data/{}/{}/{}/install.csv'.format(base_dir, app_name, target_day)
    df = pd.read_csv(f)
    return df
# read_install('game-01', '2013-09-30')
# 读取DAU数据
def read_dau(app_name, date_from, date_to):
    base_dir = 'sample-data/section9/daily/dau'
    dt_from = pd.to_datetime(date_from)
    dt_to = pd.to_datetime(date_to)
    dates = [i.strftime('%Y-%m-%d') for i in pd.date_range(date_from, date_to, freq='d').to_list()]
    dfs = []

    for day in dates:
        f = 'data/{}/{}/{}/dau.csv'.format(base_dir, app_name, day)
        df = pd.read_csv(f)
        dfs.append(df)

    frame = pd.concat(dfs, ignore_index=True)
    return frame
# read_dau('game-01', '2013-06-01', '2013-09-30')
# 读取行为数据
def read_action(app_name, action_name, date_from, date_to):
    base_dir = 'sample-data/section9/daily/action'
    dt_from = pd.to_datetime(date_from)
    dt_to = pd.to_datetime(date_to)
    dates = [i.strftime('%Y-%m-%d') for i in pd.date_range(date_from, date_to, freq='d').to_list()]
    dfs = []

    for day in dates:
        f = 'data/{}/{}/{}/{}/{}.csv'.format(base_dir, app_name, action_name, day, action_name)
        df = pd.read_csv(f)
        dfs.append(df)

    frame = pd.concat(dfs, ignore_index=True)
    return frame
# read_action('game-01', 'battle', '2013-06-01', '2013-08-31')
# Install
install = read_install('game-01', '2013-09-30')
# DAU
dau = read_dau('game-01', '2013-06-01', '2013-09-30')
# 战斗
battle = read_action('game-01', 'battle', '2013-06-01', '2013-08-31')
# 发送消息
msg = read_action('game-01', 'message', '2013-06-01', '2013-08-31')
# 协作
hlp = read_action('game-01', 'help', '2013-06-01', '2013-08-31')<jupyter_output><empty_output><jupyter_text>计算登录密度<jupyter_code># 合并DAU和首次访问数据 
dau_inst = pd.merge(dau, install, on='user_id', suffixes=('', '_inst'))
dau_inst.head()
# 筛选用户为开始游戏后第7~13天的数据
dau_inst['log_date'] = pd.to_datetime(dau_inst['log_date'])
dau_inst['log_date_inst'] = pd.to_datetime(dau_inst['log_date_inst'])
dau_inst['elapsed_days'] = (dau_inst['log_date'] - dau_inst['log_date_inst']).dt.days
dau_inst_7_13 = dau_inst[(dau_inst['elapsed_days'] >= 7) & (dau_inst['elapsed_days'] <= 13)]
# 计算登录密度
dau_inst_7_13_login_ds = dau_inst_7_13.groupby('user_id').agg({'log_date': lambda x: len(x) / 7})
dau_inst_7_13_login_ds = dau_inst_7_13_login_ds.reset_index().rename(columns={'log_date': 'density'})
dau_inst_7_13_login_ds.head()
# 筛选目标用户
install['log_date'] = pd.to_datetime(install['log_date'])
target_install = install[(install['log_date'] >= '2013-06-01') & (install['log_date'] <= '2013-08-25')]
# 合并目标用户和登录密度数据
target_install_login_ds = pd.merge(target_install, dau_inst_7_13_login_ds, how='left', on='user_id')
target_install_login_ds['density'] = np.where(target_install_login_ds['density'].isnull(), 0, target_install_login_ds['density'])
target_install_login_ds.head()<jupyter_output><empty_output><jupyter_text>生成关于战斗的数据<jupyter_code># 合并战斗行为数据和用户首次访问时间的数据
battle_inst = pd.merge(battle, install, on='user_id', suffixes=('', '_inst'))
battle_inst.head()
# 计算用户进行战斗的时间和首次访问时间的时间差
battle_inst['log_date'] = pd.to_datetime(battle_inst['log_date'])
battle_inst['log_date_inst'] = pd.to_datetime(battle_inst['log_date_inst'])
battle_inst['elapsed_days'] = (battle_inst['log_date'] - battle_inst['log_date_inst']).dt.days
# 筛选用户进行战斗的时间和首次访问时间的时间差在1周以内的数据
battle_inst2 = battle_inst[(battle_inst['elapsed_days'] >= 0) & (battle_inst['elapsed_days'] <= 6)].copy()
battle_inst2.head()
# battle_inst2['elapsed_days'] = battle_inst2['elapsed_days'].map(lambda x: 'd' + str(x))
# 将首次访问之后经过的天数按列交叉
battle_inst2_cast = battle_inst2.pivot_table(index='user_id', columns='elapsed_days', values='count', aggfunc='sum').fillna(0)
battle_inst2_cast.columns = battle_inst2_cast.columns.map(lambda x: 'd'+str(x))
battle_inst2_cast.head(6)
# 生成比率数据
battle_inst2_cast_prop = battle_inst2_cast.copy()
battle_inst2_cast_prop = battle_inst2_cast_prop.apply(lambda x: x / battle_inst2_cast_prop.sum(axis=1))
battle_inst2_cast_prop.head(6)
from sklearn.decomposition import PCA
from sklearn.preprocessing import scale
# PCA
X = battle_inst2_cast_prop.values
# X = scale(X)
b_pca = PCA(whiten=True) 
b_pca.fit(X)
b_pca_x = b_pca.transform(X)
pca_names = ['PC1', 'PC2', 'PC3', 'PC4', 'PC5', 'PC6', 'PC7']
battle_inst2_cast_pca = pd.DataFrame(b_pca_x, index=battle_inst2_cast_prop.index, columns=pca_names)
battle_inst2_cast_pca.head()<jupyter_output><empty_output><jupyter_text>生成发送消息的数据<jupyter_code># 合并发送消息数据和用户首次访问时间的数据
msg_inst = pd.merge(msg, install, on='user_id', suffixes=('', '_inst'))
msg_inst.head()
# 计算发送消息的时间和首次访问时间的时间差
msg_inst['log_date'] = pd.to_datetime(msg_inst['log_date'])
msg_inst['log_date_inst'] = pd.to_datetime(msg_inst['log_date_inst'])
msg_inst['elapsed_days'] = (msg_inst['log_date'] - msg_inst['log_date_inst']).dt.days
# 筛选发送消息的时间和首次访问时间的时间差在1周以内的数据
msg_inst2 = msg_inst[(msg_inst['elapsed_days'] >= 0) & (msg_inst['elapsed_days'] <= 6)].copy()
# 将首次访问之后经过的天数按列交叉
msg_inst2_cast = msg_inst2.pivot_table(index='user_id', columns='elapsed_days', values='count', aggfunc='sum').fillna(0)
msg_inst2_cast.columns = msg_inst2_cast.columns.map(lambda x: 'd'+str(x))
msg_inst2_cast.head()
# 生成比率数据
msg_inst2_cast_prop = msg_inst2_cast.copy()
msg_inst2_cast_prop = msg_inst2_cast_prop.apply(lambda x: x / msg_inst2_cast_prop.sum(axis=1))
msg_inst2_cast_prop.head()
# PCA
X = msg_inst2_cast_prop.values
# X = scale(X)
m_pca = PCA(whiten=True)
m_pca.fit(X)
m_pca_x = m_pca.transform(X)
msg_inst2_cast_pca = pd.DataFrame(m_pca_x, index=msg_inst2_cast_prop.index, columns=pca_names)
msg_inst2_cast_pca.head()<jupyter_output><empty_output><jupyter_text>生成协作数据<jupyter_code># 合并用户协作数据和首次访问时间的数据
hlp_inst = pd.merge(hlp, install, on='user_id', suffixes=('', '_inst'))
hlp_inst.head()
# 计算发生协作的时间和首次访问时间的时间差
hlp_inst['log_date'] = pd.to_datetime(hlp_inst['log_date'])
hlp_inst['log_date_inst'] = pd.to_datetime(hlp_inst['log_date_inst'])
hlp_inst['elapsed_days'] = (hlp_inst['log_date'] - hlp_inst['log_date_inst']).dt.days
# 筛选发生协作的时间和首次访问时间的时间差在1周以内的数据
hlp_inst2 = hlp_inst[(hlp_inst['elapsed_days'] >= 0) & (hlp_inst['elapsed_days'] <= 6)].copy()
# 将首次访问之后经过的天数按列排列
hlp_inst2_cast = hlp_inst2.pivot_table(index='user_id', columns='elapsed_days', values='count', aggfunc='sum').fillna(0)
hlp_inst2_cast.columns = hlp_inst2_cast.columns.map(lambda x: 'd'+str(x))
hlp_inst2_cast.head()
# 生成比率数据
hlp_inst2_cast_prop = hlp_inst2_cast.copy()
hlp_inst2_cast_prop = hlp_inst2_cast_prop.apply(lambda x: x / hlp_inst2_cast_prop.sum(axis=1))
# PCA
X = hlp_inst2_cast_prop.values
# X = scale(X)
h_pca = PCA(whiten=True)
h_pca.fit(X)
h_pca_x = h_pca.transform(X)
hlp_inst2_cast_pca = pd.DataFrame(h_pca_x, index=hlp_inst2_cast_prop.index, columns=pca_names)
hlp_inst2_cast_pca.head()<jupyter_output><empty_output><jupyter_text>对行为日志进行聚类<jupyter_code>from sklearn.cluster import KMeans
# 生成聚类数据函数
def create_cluster_data(aname, x, x_prop, x_pca):
    dfs = []
    for i in range(3, 7):
        km = KMeans(n_clusters=i, random_state=10)
        km.fit(x)
        km_prop = KMeans(n_clusters=i, random_state=10)
        km_prop.fit(x_prop)
        km_pca = KMeans(n_clusters=i, random_state=10)
        km_pca.fit(x_pca)
        sub = pd.DataFrame({'user_id': x.index, 'cluster_type': '{}{:0>2d}'.format(aname, i), 'freq_cluster_id': km.labels_,
                           'prop_cluster_id': km_prop.labels_, 'pca_cluster_id': km_pca.labels_})
        dfs.append(sub)
        
    df = pd.concat(dfs)
    cluster_melt = pd.melt(df, id_vars=['user_id', 'cluster_type'])
    result = cluster_melt.pivot_table(index='user_id', columns=['cluster_type', 'variable'], values='value', aggfunc='sum')
    result.columns = ['{}_{}'.format(i, j) for i,j in result.columns]
    return result
# 战斗
battle_cluster = create_cluster_data('battle', battle_inst2_cast, battle_inst2_cast_prop, battle_inst2_cast_pca)
battle_cluster.head()
# 发送消息
msg_cluster = create_cluster_data('msg', msg_inst2_cast, msg_inst2_cast_prop, msg_inst2_cast_pca)
msg_cluster.head()
# 协作
hlp_cluster = create_cluster_data('hlp', hlp_inst2_cast, hlp_inst2_cast_prop, hlp_inst2_cast_pca)
hlp_cluster.head()<jupyter_output><empty_output><jupyter_text>类合并<jupyter_code>cluster_data = pd.merge(target_install_login_ds, battle_cluster.reset_index(), on='user_id', how='left')
cluster_data = pd.merge(cluster_data, msg_cluster.reset_index(), on='user_id', how='left')
cluster_data = pd.merge(cluster_data, hlp_cluster.reset_index(), on='user_id', how='left')
cluster_data = cluster_data.fillna(0)
cluster_data.head()
# cluster_data.sum()<jupyter_output><empty_output><jupyter_text>按照登录密度重新排列<jupyter_code># 将列转换为行
cluster_data_melt = pd.melt(cluster_data.set_index('user_id').loc[:, 'density':].reset_index(), id_vars=['user_id', 'density'])
cluster_data_melt.head()
# 按照类的种类或编号计算各个类的平均登录密度
cluster_data_avg = cluster_data_melt.groupby(['variable', 'value']).agg({'density': 'mean'}).reset_index()
cluster_data_avg = cluster_data_avg.rename(columns={'density': 'average_density'})
# cluster_data_
cluster_data_avg.head()
cluster_data_avg = cluster_data_avg.sort_values(['variable', 'average_density'])
cluster_data_avg['value2'] = cluster_data_avg.groupby(['variable'])['average_density'].apply(lambda x: x.rank())
cluster_data_avg.head(10)
cluster_data_melt2 = pd.merge(cluster_data_melt, cluster_data_avg, on=['variable', 'value'])
# cluster_data_melt2[cluster_data_melt2['user_id'] == 1693]
cluster_data_melt2.head()
cluster_data_melt2.value2.value_counts()
# cluster_data_melt2[cluster_data_melt2['user_id'] == 1693]
cluster_data2 = cluster_data_melt2.pivot_table(index=['user_id', 'density'], columns='variable', values='value2', aggfunc='sum')
cluster_data2.head(10)
# for i in cluster_data2.columns:
#     print(cluster_data2[i].value_counts())
cluster_data3 = cluster_data_melt2[cluster_data_melt2['variable'] == 'hlp04_pca_cluster_id'][['user_id', 'average_density', 'value2']]
cluster_data3 = cluster_data3.rename(columns={'value2': 'cluster'})
cluster_data3.head()
hlp_inst2_cast_prop2 = pd.merge(hlp_inst2_cast_prop, cluster_data3, on='user_id')
hlp_inst2_cast_prop2['cluster'].value_counts()
hlp_inst2_cast_prop2.head()
hlp_inst2_cast_prop2.isnull().sum()
def dx_value(x):
    return sum(x) / len(x)
hlp_inst2_cast_summary = hlp_inst2_cast_prop2.groupby('cluster').agg({'average_density': 'mean', 'user_id': 'count', 'd0': dx_value,
                                                                     'd1': dx_value, 'd2': dx_value, 'd3': dx_value,
                                                                     'd4': dx_value, 'd5': dx_value, 'd6': dx_value})
hlp_inst2_cast_summary = hlp_inst2_cast_summary.reset_index().rename(columns={'average_density': 'login_density'})
hlp_inst2_cast_summary.head()
import matplotlib.pyplot as plt
%matplotlib inline
plt.plot(hlp_inst2_cast_summary['cluster'], hlp_inst2_cast_summary['login_density'])<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.158540989526905, 'fraction_numerical': 0.029974720115565186, 'mean_word_length': 6.789732770745429, 'pattern_counts': {'":': 0, '<': 38, '<?xml version=': 0, '>': 39, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 6, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '405679', 'license': 'MIT', 'n_tokens_mistral': 4908, 'n_tokens_neox': 4881, 'n_words': 576, 'path': 'ch09.ipynb', 'repo_name': 'yangjiada/data_science_from_business_applications', 'size': '11076'}