ELOコンペにトライ part3_特徴量エンジニアリング

## これは何の記事？
ELO MERCHANT CATEGORY RECOMMENDATIONコンペの学習ログです
特徴量エンジニアリングを行います（[▶前回の記事はこちら](https://qiita.com/kame_house_0828/items/405d1d6d090e5a20083c)）
※途中からvscodeに環境を切り替え（[▶参考記事](https://qiita.com/kame_house_0828/items/1b8beccac82b9f8f7cfc)）

## 環境
VSCode

## 記事の流れ
<ul>
<li> 特徴量群の作成
<li> 統計的な特徴量群の作成
<li> null処理
<li> train_dfに結合
</ul>

## 特徴量群の作成
#### ①card_idの取引回数
```python
# card_idの取引回数カラムを追加
transaction_counts = historical_df[["card_id", "purchase_date"]].groupby("card_id").count().reset_index()
transaction_counts.columns = ["card_id", "transaction_counts"]

# train_dfにtransaction_countsを結合
train_df = pd.merge(train_df, transaction_counts, on="card_id", how="left")
```

#### ②card_idの平均取引額
```python
# card_idの平均取引額カラムを追加
avg_purchase_amount = historical_df[["card_id", "purchase_amount"]].groupby("card_id").mean().reset_index()
avg_purchase_amount.columns = ["card_id", "avg_purchase_amount"]

# train_dfにavg_purchase_amountを結合
train_df = pd.merge(train_df, avg_purchase_amount, on="card_id", how="left")
```

#### ③各card_idの取引merchantの多様性（取引を行ったユニークな店舗数）
```python
# card_idの取引merchantのユニーク数
merchant_variety = historical_df[["card_id", "merchant_id"]].groupby("card_id").nunique().reset_index()
merchant_variety.columns = ["card_id", "merchant_variety"]

# train_dfにmerchant_varietyを結合
train_df = pd.merge(train_df, merchant_variety, on="card_id", how="left")
```

#### ④各card_id最頻のmerchantカテゴリ（最も利用した店舗カテゴリ）
```python
# card_idの最頻のmerchantカテゴリ
most_common_merchant = historical_df[["card_id", "merchant_category_id"]].groupby("card_id").agg(lambda x: x.value_counts().index[0]).reset_index()
most_common_merchant.columns = ["card_id", "most_common_merchant"]

# train_dfにmost_common_merchantを結合
train_df = pd.merge(train_df, most_common_merchant, on="card_id", how="left")
```
#### ⑤各card_idのcityごとの取引回数
```python
# card_idのcityごとの取引回数
most_common_city = historical_df[["card_id", "city_id"]].groupby("card_id").agg(lambda x: x.value_counts().index[0]).reset_index()
most_common_city.columns = ["card_id", "most_common_city"]

# train_dfにmost_common_cityを結合
train_df = pd.merge(train_df, most_common_city, on="card_id", how="left")
```

## 追加で特徴量群の作成
#### ⑥card_idごとの統計量
```python
# historical_dfをcard_idでグループ化し、指定の統計量を計算
historical_stats = historical_df.groupby("card_id").agg({
    "purchase_amount": ["max", "min", "var"],
    "installments": ["sum", "max", "min", "mean"]
})

historical_stats.head()
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3179561/02b79b2d-602d-db6a-3a93-a59df432b9e9.png)
```python
# カラム名を分かりやすく
historical_stats.columns = ["_".join(col).strip() for col in historical_stats.columns.values]
historical_stats.head()
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3179561/d14e6178-8b45-d546-5315-b49b7857add5.png)

```python
# Indexの再設定
historical_stats.reset_index(inplace=True)
historical_stats.head()
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3179561/0df22f9f-2d6a-8ec1-4cc2-55288a83a711.png)
```python
# 欠損値確認
historical_stats.isnull().sum()
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3179561/e8ca5fb7-282b-492f-b573-148572f34d25.png)
```python
# purchase_amount_varの欠損値を0で補完
historical_stats["purchase_amount_var"].fillna(0, inplace=True)

# train_dfに特徴量を結合
train_df = pd.merge(train_df, historical_stats, on="card_id", how="left")
```

**ここでfeatureという特徴量をまとめるための箱を作成する。特徴量の確認を行い、大丈夫そうならあとでまとめてtrain_dfに結合**
```python
# featureというdfを用意、card_idで一意に
feature = pd.DataFrame(historical_df["card_id"].unique(), columns=["card_id"])
feature.head()
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3179561/d535b6b2-f258-068d-c197-a39d47b55acc.png)


#### ⑦各card_idの利用期間
```python
# purchase_dateを日付型に変換
historical_df["purchase_date"] = pd.to_datetime(historical_df["purchase_date"])

# 利用期間の最大と最小
tmp = historical_df[["card_id", "purchase_date"]].groupby("card_id").agg(["min", "max"]).reset_index()
tmp.columns = ["card_id", "min_purchase_date", "max_purchase_date"]
tmp.head()
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3179561/3d931052-0942-3268-25aa-7acb0934a999.png)
```python
# 差分をdt.daysで日単位で出力
tmp["hist_purchase_date_term"] = (tmp["max_purchase_date"] - tmp["min_purchase_date"]).dt.days
tmp.head()
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3179561/8f266380-44b2-cacc-7a54-d95442583e5d.png)
```python
# 利用期間のみfeatureに結合
feature = pd.merge(feature, tmp[["card_id", "hist_purchase_date_term"]], on="card_id", how="left")
feature.head()
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3179561/f3814667-fffc-0bf2-1a1f-771236bf6da8.png)

#### ⑧各card_idの月当たりの平均利用回数
```python
# sizeメソッドで、cadr_idの各purchase_dateの月単位で何回利用されたか集計
tmp = historical_df.groupby(['card_id', historical_df['purchase_date'].dt.to_period("M")]).size().reset_index()
tmp.columns = ["card_id", "purchase_month", "purchase_times"]
tmp.head()
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3179561/bda53a8a-0592-c3f5-4693-848efa7da153.png)
```python
tmp = tmp[["card_id", "purchase_times"]].groupby("card_id").mean().reset_index()
tmp.columns = ["card_id", "hist_purchase_date_freq"]
tmp.head()
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3179561/537e94bf-c351-d22d-fc3c-f421d6c52b0b.png)
```python
feature = pd.merge(feature, tmp[["card_id", "hist_purchase_date_freq"]], on="card_id", how="left")
feature.head()
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3179561/ce981125-d779-4c4f-3c0b-af3e2fc0301b.png)

#### ⑨各card_idごとのmonth_lagの統計量
```python
tmp = historical_df[["card_id", "month_lag"]].groupby("card_id").agg(["mean", "min", "max", "var"]).reset_index()
tmp.head()
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3179561/6bd43fa8-5b9d-8fa2-79dd-665d8ab28bf5.png)
```python
tmp.columns = ["card_id", "hist_lag_mean", "hist_lag_min", "hist_lag_max", "hist_lag_var"]
feature = pd.merge(feature, tmp, on="card_id", how="left")
feature.head()
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3179561/a1246f96-ee8e-372f-0a1a-167f652049f4.png)

#### ⑩各card_idごとのauthorized_flagのカウント
```python
# authorized_flagのカウント
tmp = historical_df.groupby(["card_id", "authorized_flag"]).size().reset_index()
tmp.head()
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3179561/001a44e0-0c9b-2452-6946-d417a33d0964.png)
```python
# 出力方法を変える
tmp = historical_df.groupby(["card_id", "authorized_flag"]).size().unstack().reset_index()
tmp.columns = ["card_id", "authorized_flag_N", "authorized_flag_Y"]
tmp.head()
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3179561/4ecfb256-15c0-6872-8ab5-72bfa1e25147.png)
```python
# featureに結合
feature = pd.merge(feature, tmp, on="card_id", how="left")
feature.head()
```

#### ⑪各card_idごとのcategory_1のカウント
```python
# category_1のカウント
tmp = historical_df.groupby(["card_id", "category_1"]).size().unstack().reset_index()
tmp.columns = ["card_id", "category_1_N", "category_1_Y"]
tmp.head()
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3179561/9028e35f-85f4-0dec-4a0e-db89ef67b129.png)
```python
# 比率に変換
tmp["hist_category_1_ratio_Y"] = tmp["category_1_Y"] / (tmp["category_1_N"] + tmp["category_1_Y"])
tmp["hist_category_1_ratio_N"] = tmp["category_1_N"] / (tmp["category_1_N"] + tmp["category_1_Y"])

tmp.head()
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3179561/435c7d74-b974-fdbd-7aed-dd7ffe47aba5.png)

#### ⑫category_2,3の最頻値
```python
# category_2, category_3の最頻値
for col in ["category_2", "category_3"]:
    tmp = historical_df.groupby("card_id")[col].apply(lambda x: x.mode()[0] if len(x.mode()) > 0 else np.nan).reset_index()
    tmp.columns = ["card_id", f"hist_{col}_freq"]
    feature = pd.merge(feature, tmp, on="card_id", how="left")

feature.head()
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3179561/ff9f3b9f-d06f-5669-4810-1ea94e5716b4.png)

## null処理
NULLが発生していることを確認
```python
feature.isnull().sum()
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3179561/0e86b6fa-0fd4-1dba-5196-fb3a67a5d8fa.png)

hist_lag_var にNULLが発生するのはcard_idが一つしかなく、差分が取れなかったため
```python
# hist_lag_varは0で埋める
feature["hist_lag_var"].fillna(0, inplace=True)
```

authorized_flag_N にNULLが発生するのは、card_idに対して"Y"しか存在しなかったため。他も同様。
```python
# 他も0で埋める
feature["authorized_flag_N"].fillna(0, inplace=True)
feature["hist_category_1_ratio_Y"].fillna(0, inplace=True)
feature["hist_category_1_ratio_N"].fillna(0, inplace=True)
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3179561/57190668-7649-55d2-e76e-851e221eec57.png)

## train_dfに結合
featureをtrain_dfに結合
```python
train_df = pd.merge(train_df, feature, on="card_id", how="left")
train_df.head()
```

～part4に続く～