<jupyter_start><jupyter_text>Activity 관련 feature engineering<jupyter_code>pd.set_option('display.max_rows', 5000)
pd.set_option('display.max_columns', 500)
pd.set_option('display.width', 1000)<jupyter_output><empty_output><jupyter_text>Train<jupyter_code>activity_df = pd.read_csv('../../data/train/train_activity.csv')
activity_df.tail()
activity_df.describe()
print(activity_df.shape)<jupyter_output>(440323, 38)<jupyter_text>1. 플레이한 주 횟수한 번 이상 접속한 적 있는 주를 카운트함.<jupyter_code>play_wk_cnt_df = activity_df.groupby("acc_id").size().reset_index(name="play_wk_cnt")
acc_id_df = play_wk_cnt_df[["acc_id"]]
play_wk_cnt_df.tail()<jupyter_output><empty_output><jupyter_text>2. 주별 플레이한 날짜 수 각 주마다 접속한 일수를 카운트함(예: play_cnt_1wk : 1주차에 한 번이라도 접속한 일수)<jupyter_code>for_cnt_dt_pivot = activity_df[["wk", "acc_id", "cnt_dt"]]
pivot_cnt_df = for_cnt_dt_pivot.pivot("acc_id", "wk", "cnt_dt")
pivot_cnt_df.rename(columns={1:"play_cnt_1wk", 2:"play_cnt_2wk", 3:"play_cnt_3wk", 4:"play_cnt_4wk",\
                            5:"play_cnt_5wk", 6:"play_cnt_6wk", 7:"play_cnt_7wk", 8:"play_cnt_8wk"},inplace = True)
pivot_cnt_df.fillna(0, inplace = True)
pivot_cnt_df.reset_index(inplace = True)
pivot_cnt_df.tail(5)<jupyter_output><empty_output><jupyter_text>3. 8주간 게임플레이한 총 일수 및 시간8주간의 총 접속일수와 총 접속시간을 합함.<jupyter_code>sum_cnt_dt_df = activity_df.loc[: , ["acc_id","cnt_dt", "play_time"]].groupby("acc_id").agg("sum").reset_index()
sum_cnt_dt_df.tail()<jupyter_output><empty_output><jupyter_text>4. 경험치의 총합NPC 사냥 일반 경험치,NPC 사냥 홍문 경험치, 퀘스트 일반 경험치, 퀘스트 홍문 경험치, 아이템 홍문 경험치 등 경험치 관련 변수의 활동주 별 집계를 총합함.<jupyter_code>sum_of_exp_df = activity_df.loc[:,["acc_id", "npc_exp", "npc_hongmun", "quest_exp", "quest_hongmun", "item_hongmun"]].groupby("acc_id").agg("sum").reset_index()
sum_of_exp_df.tail()<jupyter_output><empty_output><jupyter_text>5. 나머지 변수들 8주 총합기타 변수들의 주별 집계를 총합함.<jupyter_code>sum_of_activity_df = activity_df.loc[:, ["acc_id","game_combat_time", "get_money", "duel_cnt", "duel_win","partybattle_cnt", "partybattle_win",\
                                         "cnt_enter_inzone_solo", "cnt_enter_inzone_light", "cnt_enter_inzone_skilled","cnt_enter_inzone_normal",\
                                         "cnt_enter_raid", "cnt_enter_raid_light", "cnt_enter_bam", "cnt_clear_inzone_solo",\
                                         "cnt_clear_inzone_light", "cnt_clear_inzone_skilled", "cnt_clear_inzone_normal",\
                                         "cnt_clear_raid", "cnt_clear_raid_light", "cnt_clear_bam", "normal_chat", "whisper_chat",\
                                         "district_chat", "party_chat", "guild_chat", "faction_chat", "cnt_use_buffitem",\
                                         "gathering_cnt", "making_cnt"]
                                    ].groupby("acc_id").agg("sum").reset_index()
sum_of_activity_df.tail(5)<jupyter_output><empty_output><jupyter_text>6. 계산된 컬럼 병합<jupyter_code>merge_activity_df = play_wk_cnt_df.merge(sum_cnt_dt_df)
merge_activity_df = merge_activity_df.merge(pivot_cnt_df)
merge_activity_df = merge_activity_df.merge(sum_of_exp_df)
merge_activity_df = merge_activity_df.merge(sum_of_activity_df)
merge_activity_df.tail(5)<jupyter_output><empty_output><jupyter_text>7. 각 컬럼을 1~8주로 전처리 7-1 Play_time 컬럼 변환<jupyter_code>play_time_ls = list(set(activity_df["play_time"]))
play_time_ls.sort()
dif_val = play_time_ls[1] - play_time_ls[0] 
play_time_min = play_time_ls[0] - dif_val
pivot_activity_play_time = activity_df.pivot('acc_id', 'wk', 'play_time')
pivot_activity_play_time.rename(columns = {
    1:'play_time_1wk', 2:'play_time_2wk',3:'play_time_3wk',4:'play_time_4wk',5:'play_time_5wk', 6:'play_time_6wk',
    7:'play_time_7wk', 8:'play_time_8wk'
}, inplace=True)
pivot_activity_play_time.reset_index(inplace = True)
pivot_activity_play_time.fillna(play_time_min, inplace = True)
merge_activity_df = merge_activity_df.merge(pivot_activity_play_time)
merge_activity_df.tail()
for_pivot_activity_ls = activity_df.columns[4:]
for_pivot_activity_ls<jupyter_output><empty_output><jupyter_text>7-2 나머지 컬럼 변환<jupyter_code>for column in for_pivot_activity_ls:
    pivot_tb = activity_df.pivot_table(values = column, index = 'acc_id', columns = 'wk', fill_value = activity_df[column].min())
    pivot_tb.rename(columns = {1:column +'_1wk', 2:column +'_2wk',3:column +'_3wk',4:column +'_4wk',5:column +'_5wk', 6:column +'_6wk',
                                7:column +'_7wk', 8:column +'_8wk'
                                }, inplace=True)
    pivot_tb.reset_index(inplace=True)
    acc_id_df = pd.merge(acc_id_df, pivot_tb)
merge_activity_df = merge_activity_df.merge(acc_id_df)
merge_activity_df.tail()<jupyter_output><empty_output><jupyter_text>8. 7,8주 activity 합의 비율각 변수의 최소값을 imputation한 뒤 각 변수의 8주간의 활동량 총합에서 7,8주의 활동량이 차지하는 비율을 구함<jupyter_code>for_ratio_ls = np.array(merge_activity_df.columns[46:])
for_ratio_ls = for_ratio_ls.reshape(-1, 8)
for_ratio_df = merge_activity_df[["acc_id"]]
for i, columns in enumerate(for_ratio_ls):
    if i == 0:
        for_ratio_df = pd.concat([for_ratio_df, merge_activity_df.loc[:, columns]], axis=1)
    else:
        min_value_ls = list(merge_activity_df.loc[:, columns].min())
        min_value_ls.sort()
        for_sum_value = -min_value_ls[0] + 0.05
        for_ratio_df = pd.concat([for_ratio_df, merge_activity_df.loc[:, columns] + for_sum_value], axis=1)
for_ratio_df.tail()
ratio_78_all_df = for_ratio_df[["acc_id"]]
for i, columns in enumerate(for_ratio_ls):
    sum_all = for_ratio_df.loc[:, columns].sum(axis=1)
    sum_78 = for_ratio_df.loc[:, columns[6:8]].sum(axis=1)
    ratio_78_all_df[activity_df.columns[i+2]+"_ratio_78_all"] =sum_78/sum_all
ratio_78_all_df.tail()<jupyter_output><empty_output><jupyter_text>9. 5, 6, 7, 8주 activity 합의 비율각 변수의 최소값을 imputation한 뒤 각 변수의 8주간의 활동량 총합에서 6,7,8주의 활동량이 차지하는 비율을 구함<jupyter_code>ratio_2half_all_df = for_ratio_df[["acc_id"]]
for i, columns in enumerate(for_ratio_ls):
    sum_all = for_ratio_df.loc[:, columns].sum(axis=1)
    sum_2half = for_ratio_df.loc[:, columns[4:8]].sum(axis=1)
    ratio_2half_all_df[activity_df.columns[i+2]+"_ratio_2half_all"] =sum_2half/sum_all
ratio_2half_all_df.tail()
ratio_df = pd.merge(ratio_78_all_df, ratio_2half_all_df)
merge_activity_df = merge_activity_df.merge(ratio_df)<jupyter_output><empty_output><jupyter_text>10. 7,8주와 3,4주 비교3,4주의 활동량의 합량에서 7,8주의 활동량의 합량의 증감을 구함.<jupyter_code>def zero(activity_df):
    
    """
    최소값과 Q3의 값이 동일한 경우 최소값을 0으로, 
    그렇지 않은 경우에는 최소값에서 최소값 다음으로 큰 값을 뺀 값을 0으로 정의하여 
    각 변수의 0값이 저장된 데이터프레임 act_meta를 생성합니다
    act_meta.shape = (activity_df 칼럼 수 x 2) 
    """
    
    act_meta = activity_df.describe().T[['min']]
    zero = [0,0]
    for c in activity_df.columns[3:]:
        zero.append( activity_df[c].min() - ( activity_df[c][activity_df[c] != activity_df[c].min()].min() - activity_df[c].min() ) )
    act_meta['zero'] = zero
    
    f0 = activity_df.describe().T[activity_df.describe().T['75%'] - activity_df.describe().T['min'] == 0]
    act_meta['zero'].loc[list(f0.index)] = act_meta['min'].loc[list(f0.index)]
    return act_meta

act_meta = zero(activity_df)
# 3,4주 7,8주를 합한 df 생성
act_34 = activity_df[activity_df.wk.isin([3,4])].groupby('acc_id').sum().reset_index().drop('wk', axis=1)
act_78 = activity_df[6<activity_df.wk].groupby('acc_id').sum().reset_index().drop('wk', axis=1)

# 3,4주 데이터포인트가 없는 acc_id들 때문에 7,8주의 acc_id를 기준으로 merge
act_34 = pd.merge(act_78[['acc_id']], act_34, how = 'left')

#0값 imputation
for column in act_34.columns[1:]:
    act_34[column].fillna(value=(act_meta.loc[column].zero)*2, inplace=True)

act_7834 = act_78.iloc[:,1:] - act_34.iloc[:,1:]
act_7834 = pd.concat([act_78[['acc_id']], act_7834], axis=1)


# RadomForest로 feature importance의 상위 25개의 변수 선택
importantft = ['making_cnt', 'game_combat_time', 'cnt_use_buffitem', 'play_time',
               'item_hongmun', 'quest_exp', 'cnt_dt', 'npc_exp',
               'cnt_enter_inzone_normal', 'cnt_clear_inzone_normal', 'cnt_enter_inzone_solo',
               'npc_hongmun', 'quest_hongmun', 'cnt_clear_inzone_solo', 'cnt_clear_inzone_light',
               'whisper_chat', 'cnt_enter_inzone_light', 'guild_chat', 'get_money',
               'normal_chat', 'party_chat', 'cnt_enter_raid', 'cnt_clear_raid',
               'gathering_cnt', 'faction_chat']

act_7834_final = act_7834[['acc_id'] + importantft]

def columnchng(df,features):
    
    dic = {}
    for feature in features:
        dic[feature] = feature + '_7834'
    df.rename(columns=dic, inplace=True)
    return df

act_7834_final = columnchng(act_7834_final,importantft)
merge_activity_df = pd.merge(merge_activity_df, act_7834_final,how='left')<jupyter_output><empty_output><jupyter_text>11. activity정리 최종<jupyter_code>merge_activity_df.tail()
merge_activity_df.shape<jupyter_output><empty_output><jupyter_text>----  Test<jupyter_code>activity_df = pd.read_csv("../../data/test/test_activity.csv")
activity_df.tail()
activity_df.describe()
print(activity_df.shape)<jupyter_output>(175631, 38)<jupyter_text>1. 플레이한 주 횟수<jupyter_code>play_wk_cnt_df = activity_df.groupby("acc_id").size().reset_index(name="play_wk_cnt")
acc_id_df = play_wk_cnt_df[["acc_id"]]
play_wk_cnt_df.tail()<jupyter_output><empty_output><jupyter_text>2. 주별 플레이한 날짜 수<jupyter_code>for_cnt_dt_pivot = activity_df[["wk", "acc_id", "cnt_dt"]]
pivot_cnt_df = for_cnt_dt_pivot.pivot("acc_id", "wk", "cnt_dt")
pivot_cnt_df.rename(columns={1:"play_cnt_1wk", 2:"play_cnt_2wk", 3:"play_cnt_3wk", 4:"play_cnt_4wk",\
                            5:"play_cnt_5wk", 6:"play_cnt_6wk", 7:"play_cnt_7wk", 8:"play_cnt_8wk"},inplace = True)
pivot_cnt_df.fillna(0, inplace = True)
pivot_cnt_df.reset_index(inplace = True)
pivot_cnt_df.tail(5)<jupyter_output><empty_output><jupyter_text>3. 8주간 게임플레이한 총 일수 및 시간<jupyter_code>sum_cnt_dt_df = activity_df.loc[: , ["acc_id","cnt_dt", "play_time"]].groupby("acc_id").agg("sum").reset_index()
sum_cnt_dt_df.tail()<jupyter_output><empty_output><jupyter_text>4. 경험치의 총합<jupyter_code>sum_of_exp_df = activity_df.loc[:,["acc_id", "npc_exp", "npc_hongmun", "quest_exp", "quest_hongmun", "item_hongmun"]].groupby("acc_id").agg("sum").reset_index()
sum_of_exp_df.tail()<jupyter_output><empty_output><jupyter_text>5. 나머지 변수들 8주 총합<jupyter_code>sum_of_activity_df = activity_df.loc[:, ["acc_id","game_combat_time", "get_money", "duel_cnt", "duel_win","partybattle_cnt", "partybattle_win",\
                                         "cnt_enter_inzone_solo", "cnt_enter_inzone_light", "cnt_enter_inzone_skilled","cnt_enter_inzone_normal",\
                                         "cnt_enter_raid", "cnt_enter_raid_light", "cnt_enter_bam", "cnt_clear_inzone_solo",\
                                         "cnt_clear_inzone_light", "cnt_clear_inzone_skilled", "cnt_clear_inzone_normal",\
                                         "cnt_clear_raid", "cnt_clear_raid_light", "cnt_clear_bam", "normal_chat", "whisper_chat",\
                                         "district_chat", "party_chat", "guild_chat", "faction_chat", "cnt_use_buffitem",\
                                         "gathering_cnt", "making_cnt"]
                                    ].groupby("acc_id").agg("sum").reset_index()
sum_of_activity_df.tail(5)<jupyter_output><empty_output><jupyter_text>6. 계산된 컬럼 병합<jupyter_code>merge_activity_df = play_wk_cnt_df.merge(sum_cnt_dt_df)
merge_activity_df = merge_activity_df.merge(pivot_cnt_df)
merge_activity_df = merge_activity_df.merge(sum_of_exp_df)
merge_activity_df = merge_activity_df.merge(sum_of_activity_df)
merge_activity_df.tail(5)<jupyter_output><empty_output><jupyter_text>7. 각 컬럼을 1~8주로 전처리 7-1 Play_time 컬럼 변환<jupyter_code>play_time_ls = list(set(activity_df["play_time"]))
play_time_ls.sort()
dif_val = play_time_ls[1] - play_time_ls[0] 
play_time_min = play_time_ls[0] - dif_val
pivot_activity_play_time = activity_df.pivot('acc_id', 'wk', 'play_time')
pivot_activity_play_time.rename(columns = {
    1:'play_time_1wk', 2:'play_time_2wk',3:'play_time_3wk',4:'play_time_4wk',5:'play_time_5wk', 6:'play_time_6wk',
    7:'play_time_7wk', 8:'play_time_8wk'
}, inplace=True)
pivot_activity_play_time.reset_index(inplace = True)
pivot_activity_play_time.fillna(play_time_min, inplace = True)
merge_activity_df = merge_activity_df.merge(pivot_activity_play_time)
merge_activity_df.tail()
for_pivot_activity_ls = activity_df.columns[4:]
for_pivot_activity_ls<jupyter_output><empty_output><jupyter_text>7-2 나머지 컬럼 변환<jupyter_code>for column in for_pivot_activity_ls:
    pivot_tb = activity_df.pivot_table(values = column, index = 'acc_id', columns = 'wk', fill_value = activity_df[column].min())
    pivot_tb.rename(columns = {1:column +'_1wk', 2:column +'_2wk',3:column +'_3wk',4:column +'_4wk',5:column +'_5wk', 6:column +'_6wk',
                                7:column +'_7wk', 8:column +'_8wk'
                                }, inplace=True)
    pivot_tb.reset_index(inplace=True)
    acc_id_df = pd.merge(acc_id_df, pivot_tb)
merge_activity_df = merge_activity_df.merge(acc_id_df)
merge_activity_df.tail()<jupyter_output><empty_output><jupyter_text>8. 7,8주 activity 합의 비율<jupyter_code>for_ratio_ls = np.array(merge_activity_df.columns[46:])
for_ratio_ls = for_ratio_ls.reshape(-1, 8)
for_ratio_df = merge_activity_df[["acc_id"]]
for i, columns in enumerate(for_ratio_ls):
    if i == 0:
        for_ratio_df = pd.concat([for_ratio_df, merge_activity_df.loc[:, columns]], axis=1)
    else:
        min_value_ls = list(merge_activity_df.loc[:, columns].min())
        min_value_ls.sort()
        for_sum_value = -min_value_ls[0] + 0.05
        for_ratio_df = pd.concat([for_ratio_df, merge_activity_df.loc[:, columns] + for_sum_value], axis=1)
for_ratio_df.tail()
ratio_78_all_df = for_ratio_df[["acc_id"]]
for i, columns in enumerate(for_ratio_ls):
    sum_all = for_ratio_df.loc[:, columns].sum(axis=1)
    sum_78 = for_ratio_df.loc[:, columns[6:8]].sum(axis=1)
    ratio_78_all_df[activity_df.columns[i+2]+"_ratio_78_all"] =sum_78/sum_all
ratio_78_all_df.tail()<jupyter_output><empty_output><jupyter_text>9. 5, 6, 7, 8주 activity 합의 비율<jupyter_code>ratio_2half_all_df = for_ratio_df[["acc_id"]]
for i, columns in enumerate(for_ratio_ls):
    sum_all = for_ratio_df.loc[:, columns].sum(axis=1)
    sum_2half = for_ratio_df.loc[:, columns[4:8]].sum(axis=1)
    ratio_2half_all_df[activity_df.columns[i+2]+"_ratio_2half_all"] =sum_2half/sum_all
ratio_2half_all_df.tail()
ratio_df = pd.merge(ratio_78_all_df, ratio_2half_all_df)
merge_activity_df = merge_activity_df.merge(ratio_df)<jupyter_output><empty_output><jupyter_text>10. 7,8주와 3,4주 비교<jupyter_code>act_meta = zero(activity_df)
# 3,4주 7,8주를 합한 df 생성
act_34 = activity_df[activity_df.wk.isin([3,4])].groupby('acc_id').sum().reset_index().drop('wk', axis=1)
act_78 = activity_df[6<activity_df.wk].groupby('acc_id').sum().reset_index().drop('wk', axis=1)

# 3,4주 데이터포인트가 없는 acc_id들 때문에 7,8주의 acc_id를 기준으로 merge
act_34 = pd.merge(act_78[['acc_id']], act_34, how = 'left')

#0값 imputation
for column in act_34.columns[1:]:
    act_34[column].fillna(value=(act_meta.loc[column].zero)*2, inplace=True)

act_7834 = act_78.iloc[:,1:] - act_34.iloc[:,1:]
act_7834 = pd.concat([act_78[['acc_id']], act_7834], axis=1)

importantft = ['making_cnt', 'game_combat_time', 'cnt_use_buffitem', 'play_time',
               'item_hongmun', 'quest_exp', 'cnt_dt', 'npc_exp',
               'cnt_enter_inzone_normal', 'cnt_clear_inzone_normal', 'cnt_enter_inzone_solo',
               'npc_hongmun', 'quest_hongmun', 'cnt_clear_inzone_solo', 'cnt_clear_inzone_light',
               'whisper_chat', 'cnt_enter_inzone_light', 'guild_chat', 'get_money',
               'normal_chat', 'party_chat', 'cnt_enter_raid', 'cnt_clear_raid',
               'gathering_cnt', 'faction_chat']

act_7834_final = act_7834[['acc_id'] + importantft]

def columnchng(df,features):
    
    dic = {}
    for feature in features:
        dic[feature] = feature + '_7834'
    df.rename(columns=dic, inplace=True)
    return df

act_7834_final = columnchng(act_7834_final,importantft)
merge_activity_df = pd.merge(merge_activity_df, act_7834_final,how='left')<jupyter_output><empty_output><jupyter_text>11. activity정리 최종<jupyter_code>merge_activity_df.tail()
merge_activity_df.shape<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14298921417565486, 'fraction_numerical': 0.028659476117103237, 'mean_word_length': 5.1954944635357005, 'pattern_counts': {'":': 0, '<': 109, '<?xml version=': 0, '>': 107, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 28, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '27', 'dir': 'jupyter-structured-clean-dedup', 'id': '55802', 'license': 'MIT', 'n_tokens_mistral': 7246, 'n_tokens_neox': 7385, 'n_words': 865, 'path': '1_1_FE_activity.ipynb', 'repo_name': 'hyeshinoh/Project_Classification_GameUser_Exit_Prediction', 'size': '16225'}