DataFrame の集約処理 (数値データ)

import numpy as np
import pandas as pd

train = pd.read_csv('data/application_train.csv')

print('Training data shape:', train.shape)
print('Unique number of SK_ID_CURR in training data:', train.SK_ID_CURR.nunique())
train.head()

bureau = pd.read_csv('data/bureau.csv')

print('Bureau data shape:', bureau.shape)
print('Unique number of SK_ID_CURR in bureau data:', bureau.SK_ID_CURR.nunique())
print('Unique number of SK_ID_BUREAU in bureau data:', bureau.SK_ID_BUREAU.nunique())
bureau.head()

# SK_ID_BUREAU も数値データとして扱われるので、除外する
bureau_agg = bureau.drop(columns=['SK_ID_BUREAU'])

bureau_agg = bureau_agg.groupby('SK_ID_CURR', as_index=False).agg(['count', 'mean', 'sum']).reset_index()
bureau_agg.head()

# column 名のリストを準備
columns = ['SK_ID_CURR']

# カラム名を一つ一つ読み出す
for col in bureau_agg.columns.levels[0]:
    
    # SK_ID_CURR は飛ばす
    if col != 'SK_ID_CURR':
        
        for stat in bureau_agg.columns.levels[1][:-1]:
            # 変数と統計量の新しい名前を生成し、リストへ追加
            columns.append('bureau_%s_%s' % (col, stat))
            
# 作成したカラム名を dataframe へ適用
bureau_agg.columns = columns

print('bureau_agg shape:', bureau_agg.shape)
bureau_agg.head()

print('Traning data shape before merged:', train.shape)

train = train.merge(bureau_agg, on='SK_ID_CURR', how='left')

print('Training data shape after merged:', train.shape)
train.head()

corrs = []

# TARGET と各特徴量の相関係数を計算
for col in columns:
    corr = train['TARGET'].corr(train[col])
    corrs.append((col, corr))

# 相関係数の絶対値の降順で上位 10 個を表示
corrs = sorted(corrs, key=lambda x: abs(x[1]), reverse=True)
corrs[:10]

# df          : 処理する dataframe
# group_var   : 集約する起点となる特徴量名 (ID名)
# df_name     : dataframe名 (string型)
# agg_list    : 処理する統計量のリスト
# remove_cols : 処理しない特徴量リスト

def agg_numeric(df, group_var, df_name, agg_list, remove_cols):
    
    # ID と不要な特徴量を削除する前に、ID列を抜き出し
    group_ids = df[group_var]
    df = df.drop(columns=remove_cols)
    df = df.drop(columns=group_var)
    numeric_df = df.select_dtypes('number')
    numeric_df.loc[:, group_var] = group_ids
    
    # aggregation 処理
    agg = numeric_df.groupby(group_var).agg(agg_list).reset_index()
    
    # カラム名を修正
    columns = [group_var]
    for col in agg.columns.levels[0]:
        if col != group_var:
            for stat in agg.columns.levels[1][:-1]:
                columns.append('%s_%s_%s' % (df_name, col, stat))
                
    agg.columns = columns
    
    return agg

agg_list = ['count', 'mean', 'sum']

bureau_agg_by_func = agg_numeric(bureau, group_var='SK_ID_CURR', df_name='bureau',
                                 agg_list=agg_list, remove_cols=['SK_ID_BUREAU'])
print('bureau_agg_by_func shape:', bureau_agg.shape)
bureau_agg_by_func.head()