application_vnd.jupyter.stderr_output_2.png text_plain_output_3.png text_plain_output_1.png
import numpy as np

import pandas as pd

def one_hot_encoder(df, nan_as_category=True):
    original_columns = list(df.columns)
    categorical_columns = [col for col in df.columns if df[col].dtype == 'object']
    df = pd.get_dummies(df, columns=categorical_columns, dummy_na=nan_as_category)
    new_columns = [c for c in df.columns if c not in original_columns]
    return (df, new_columns)

def train_test():
    train = pd.read_csv('../input/application_train.csv')
    test = pd.read_csv('../input/application_test.csv')
    (print(train.shape), print(test.shape))
    ind_train = train['SK_ID_CURR']
    ind_test = test['SK_ID_CURR']
    new_df = train.append(test).reset_index()
    avg = [col for col in new_df.columns if 'AVG' in col]
    new_df['AVG_MEAN'] = new_df[avg].sum(axis=1)
    new_df['AVG_SUM'] = new_df[avg].sum(axis=1)
    new_df['AVG_STD'] = new_df[avg].std(axis=1)
    new_df['AVG_MEDIAN'] = new_df[avg].median(axis=1)
    mode = [col for col in new_df.columns if 'MODE' in col]
    new_df['MODE_MEAN'] = new_df[mode].sum(axis=1)
    new_df['MODE_SUM'] = new_df[mode].sum(axis=1)
    new_df['MODE_STD'] = new_df[mode].std(axis=1)
    new_df['MODE_MEDIAN'] = new_df[mode].median(axis=1)
    medi = [col for col in new_df.columns if 'MEDI' in col]
    new_df['MEDI_MEAN'] = new_df[medi].sum(axis=1)
    new_df['MEDI_SUM'] = new_df[medi].sum(axis=1)
    new_df['MEDI_STD'] = new_df[medi].std(axis=1)
    new_df['MEDI_MEDIAN'] = new_df[medi].median(axis=1)
    new_df['DAYS_EMPLOYED'].replace(365243, np.nan, inplace=True)
    new_df['AGE_CLIENT'] = new_df['DAYS_BIRTH'] / -365
    new_df['EMPLOYED_YEAR'] = new_df['DAYS_EMPLOYED'] / -365
    new_df['_REGISTRATION_YEAR'] = new_df['DAYS_REGISTRATION'] / -365
    new_df['ID_PUBLISH_YEAR'] = new_df['DAYS_ID_PUBLISH'] / -365
    new_df['RATIO_CHILD_MEMBERS_FAM'] = new_df['CNT_CHILDREN'] / new_df['CNT_FAM_MEMBERS']
    new_df['RATIO_INCOME_MEMBERS_FAM'] = new_df['AMT_INCOME_TOTAL'] / new_df['CNT_FAM_MEMBERS']
    new_df['RATIO_INCOME_CREDIT'] = new_df['AMT_INCOME_TOTAL'] / new_df['AMT_CREDIT']
    new_df['RATIO_INCOME_ANNUITY'] = new_df['AMT_INCOME_TOTAL'] / new_df['AMT_ANNUITY']
    new_df['RATIO_PRICE_INCOME'] = new_df['AMT_GOODS_PRICE'] / new_df['AMT_INCOME_TOTAL']
    new_df['RATIO_PRICE_CREDIT'] = new_df['AMT_GOODS_PRICE'] / new_df['AMT_CREDIT']
    new_df['EXT_SCORE_SUM'] = new_df['EXT_SOURCE_1'] + new_df['EXT_SOURCE_2'] + new_df['EXT_SOURCE_3']
    new_df['EXT_SCORE_MEAN'] = (new_df['EXT_SOURCE_1'] + new_df['EXT_SOURCE_2'] + new_df['EXT_SOURCE_3']) / 3
    new_df['OBS_90_CNT_SOCIAL_CIRCLE_SUM'] = new_df['OBS_30_CNT_SOCIAL_CIRCLE'] + new_df['OBS_60_CNT_SOCIAL_CIRCLE']
    new_df['OBS_90_CNT_SOCIAL_CIRCLE_MEAN'] = (new_df['OBS_30_CNT_SOCIAL_CIRCLE'] + new_df['OBS_60_CNT_SOCIAL_CIRCLE']) / 2
    new_df['DEF_90_CNT_SOCIAL_CIRCLE_MEAN'] = (new_df['DEF_60_CNT_SOCIAL_CIRCLE'] + new_df['DEF_30_CNT_SOCIAL_CIRCLE']) / 2
    new_df['DEF_90_CNT_SOCIAL_CIRCLE_SUM'] = new_df['DEF_60_CNT_SOCIAL_CIRCLE'] + new_df['DEF_30_CNT_SOCIAL_CIRCLE']
    flag_doc_col = [col for col in new_df.columns if 'FLAG_DOCUMENT' in col]
    new_df['FLAG_DOC_MEAN'] = train[flag_doc_col].mean(axis=1)
    new_df['FLAG_DOC_SUM'] = train[flag_doc_col].sum(axis=1)
    new_df, col = one_hot_encoder(new_df)
    train = new_df.loc[new_df['SK_ID_CURR'].isin(ind_train)]
    test = new_df.loc[new_df['SK_ID_CURR'].isin(ind_test)]
    (print(train.shape), print(test.shape))
    return (train, test)

def bureau_bb():
    bureau_balance = pd.read_csv('../input/bureau_balance.csv')
    bureau = pd.read_csv('../input/bureau.csv')
    bureau_balance, cat_bb = one_hot_encoder(bureau_balance)
    bb_agg = {'MONTHS_BALANCE': ['median', 'min', 'max'], 'STATUS_0': ['sum', 'mean'], 'STATUS_1': ['sum', 'mean'], 'STATUS_2': ['sum', 'mean'], 'STATUS_3': ['sum', 'mean'], 'STATUS_4': ['sum', 'mean'], 'STATUS_5': ['sum', 'mean'], 'STATUS_C': ['sum', 'mean'], 'STATUS_X': ['sum', 'mean'], 'STATUS_nan': ['sum', 'mean']}
    bureau_balance_agg = bureau_balance.groupby('SK_ID_BUREAU').agg(bb_agg)
    bureau_balance_agg.columns = pd.Index([e[0] + '_' + e[1].upper() for e in bureau_balance_agg.columns.tolist()])
    bureau_balance_agg = bureau_balance_agg.reset_index()
    bureau = bureau.merge(bureau_balance_agg, how='left', on='SK_ID_BUREAU').drop(['SK_ID_BUREAU'], axis=1)
    bureau, cat_b = one_hot_encoder(bureau)
    b_agg = {'DAYS_CREDIT': ['median'], 'CREDIT_DAY_OVERDUE': ['median', 'min', 'max'], 'DAYS_CREDIT_ENDDATE': ['median'], 'DAYS_ENDDATE_FACT': ['median'], 'DAYS_CREDIT_UPDATE': ['median'], 'AMT_CREDIT_MAX_OVERDUE': ['min', 'max'], 'CNT_CREDIT_PROLONG': ['sum', 'mean', 'min', 'max'], 'AMT_CREDIT_SUM': ['min', 'mean', 'max'], 'AMT_CREDIT_SUM_DEBT': ['min', 'mean', 'max'], 'AMT_CREDIT_SUM_LIMIT': ['min', 'mean', 'max'], 'AMT_CREDIT_SUM_OVERDUE': ['min', 'mean', 'max'], 'MONTHS_BALANCE_MEDIAN': ['median'], 'MONTHS_BALANCE_MIN': ['min', 'median', 'max'], 'MONTHS_BALANCE_MIN': ['min', 'median', 'max'], 'AMT_ANNUITY': ['min', 'mean', 'max']}
    cat_b_agg = {}
    for col in cat_b:
        cat_b_agg[col] = ['mean']
    for col in cat_bb:
        cat_b_agg[col + '_SUM'] = ['mean']
        cat_b_agg[col + '_MEAN'] = ['mean']
    bureau_agg = bureau.groupby('SK_ID_CURR').agg({**b_agg, **cat_b_agg})
    bureau_agg.columns = pd.Index([e[0] + '_' + e[1].upper() for e in bureau_agg.columns.tolist()])
    bureau_agg = bureau_agg.reset_index()
    return bureau_agg

bureau = bureau_bb()

train = pd.merge(train, bureau, how='left', on='SK_ID_CURR')
test = pd.merge(test, bureau, how='left', on='SK_ID_CURR')

def previous_application():
    previous_application = pd.read_csv('../input/previous_application.csv')
    previous_application, cat_pr = one_hot_encoder(previous_application)
    previous_application['RATE_DOWN_PAYMENT'][previous_application['RATE_DOWN_PAYMENT'] < 0] = np.nan
    previous_application['AMT_DOWN_PAYMENT'][previous_application['AMT_DOWN_PAYMENT'] < 0] = np.nan
    previous_application['DAYS_TERMINATION'][previous_application['DAYS_TERMINATION'] == 365243] = np.nan
    previous_application['DAYS_LAST_DUE'][previous_application['DAYS_LAST_DUE'] == 365243] = np.nan
    previous_application['DAYS_FIRST_DUE'][previous_application['DAYS_FIRST_DUE'] == 365243] = np.nan
    previous_application['DAYS_FIRST_DRAWING'][previous_application['DAYS_FIRST_DRAWING'] == 365243] = np.nan
    pa_agg = {'AMT_ANNUITY': ['median', 'min', 'max'], 'AMT_APPLICATION': ['median', 'min', 'max'], 'AMT_CREDIT': ['median', 'min', 'max'], 'AMT_DOWN_PAYMENT': ['median', 'min', 'max'], 'AMT_GOODS_PRICE': ['median', 'min', 'max'], 'HOUR_APPR_PROCESS_START': ['mean', 'min', 'max'], 'NFLAG_LAST_APPL_IN_DAY': ['sum'], 'RATE_DOWN_PAYMENT': ['mean', 'min', 'max', 'sum'], 'RATE_INTEREST_PRIMARY': ['mean', 'min', 'max', 'sum'], 'RATE_INTEREST_PRIVILEGED': ['mean', 'min', 'max', 'sum'], 'DAYS_DECISION': ['median', 'min', 'max'], 'CNT_PAYMENT': ['median', 'min', 'max'], 'DAYS_FIRST_DRAWING': ['median', 'min', 'max'], 'DAYS_FIRST_DUE': ['median', 'min', 'max'], 'DAYS_LAST_DUE': ['median', 'min', 'max'], 'DAYS_TERMINATION': ['median', 'min', 'max'], 'NFLAG_INSURED_ON_APPROVAL': ['sum']}
    cat_agg = {}
    for cat in cat_pr:
        cat_agg[cat] = ['mean']
    previous_application_agg = previous_application.groupby('SK_ID_CURR').agg({**pa_agg, **cat_agg})
    previous_application_agg.columns = pd.Index([e[0] + '_' + e[1].upper() for e in previous_application_agg.columns.tolist()])
    previous_application_agg = previous_application_agg.reset_index()
    return previous_application_agg

previous_application = previous_application()

train = pd.merge(train, previous_application, how='left', on='SK_ID_CURR')
test = pd.merge(test, previous_application, how='left', on='SK_ID_CURR')

def POS_CASH_balance():
    POS_CASH_balance = pd.read_csv('../input/POS_CASH_balance.csv')
    POS_CASH_balance, cat_pc_b = one_hot_encoder(POS_CASH_balance)
    pc_b_agg = {'MONTHS_BALANCE': ['median', 'min', 'max'], 'CNT_INSTALMENT': ['median', 'min', 'max'], 'CNT_INSTALMENT_FUTURE': ['median', 'min', 'max'], 'SK_DPD': ['median', 'min', 'max'], 'SK_DPD_DEF': ['median', 'min', 'max']}
    cat_agg = {}
    for cat in cat_pc_b:
        cat_agg[cat] = ['mean']
    POS_CASH_balance_agg = POS_CASH_balance.groupby(['SK_ID_CURR']).agg({**pc_b_agg, **cat_agg})
    POS_CASH_balance_agg.columns = pd.Index([e[0] + '_' + e[1].upper() for e in POS_CASH_balance_agg.columns.tolist()])
    POS_CASH_balance_agg = POS_CASH_balance_agg.reset_index()
    return POS_CASH_balance_agg

POS_CASH_balance = POS_CASH_balance()