ELOコンペにトライ part2_EDA

## これは何の記事？
ELO MERCHANT CATEGORY RECOMMENDATIONコンペの学習ログです
探索的データ分析（EDA）の続きを行います
[▶前回の記事はこちら](https://qiita.com/kame_house_0828/items/5633ce546a9fbb7a1825)

## 環境
Google Colab
VSCode

## 記事の流れ
<ul>
<li> データの概要把握
    <ul>
    <li> 2. testデータ
    <li> 3. historicalデータ
    </ul>
<li> データの前処理
</ul>


## データの概要把握
#### 2. testデータ
各card_idのtargetを推測する
|Columns|Description|
|---|---|
|card_id|Unique card identifier|
|first_active_month|初回購入月|
|feature_1|特徴量1|
|feature_2|特徴量2|
|feature_3|特徴量3|

欠損値、および学習データとの重複がないか確認
```python
# 学習データとテストデータを結合させ、欠損値および重複を確認
all_df = pd.concat([train_df, test_df], sort=False).reset_index(drop=True)

all_df
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3179561/ccb48248-a561-c2af-c0c5-49ab3d659335.png)

first_active_monthに欠損値あり、処理処理
```python
all_df.isnull().sum()
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3179561/f6407ce0-2186-f449-58cd-89e47d4c4699.png)
card_idに重複はなし
```python
all_df.duplicated(subset="card_id").sum()
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3179561/4f19b1ea-2099-ae1e-ed7c-218aec54dfd3.png)

#### 3. historicalデータ

|Columns|Description|
|---|---|
|card_id|Card identifier|
|month_lag|month lag to reference date|
|purchase_date|Purchase date|
|authorized_flag|Y' if approved, 'N' if denied|
|category_3|anonymized category|
|installments|number of installments of purchase|
|category_1|anonymized category|
|merchant_category_id|Merchant category identifier (anonymized)|
|subsector_id|Merchant category group identifier (anonymized)|
|merchant_id|Merchant identifier (anonymized)|
|purchase_amount|Normalized purchase amount|
|city_id|City identifier (anonymized)|
|state_id|State identifier (anonymized)|
|category_2|anonymized category|

気になるカラムは以下
・authorized_flag（承認フラグ）：クレカの承認？取引成立ってこと？
・category_1~3：何かしら有効そう

データを軽く見てみると、恐らくmerchant_id × card_idでユニークな購買履歴
```python
# 先頭5行
historical_df.head()
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3179561/d8ff0bd8-fcb6-22f9-1244-8f3c5659faa5.png)
29,112,361行 × 14列あるので、当然重い
```python
# 型と行数確認
historical_df.info()
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3179561/7afb3701-38c1-efd8-01e4-7e52fea35d71.png)

欠損値確認
```python
# 欠損値確認
historical_df.isnull().sum()
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3179561/5a76c7a1-56a6-3bc5-e307-e801f9d69a7d.png)

学習およびテストデータのcard_idがすべて含まれているか確認したい
重複なしと確認済のall_dfのレコード数と同じ値が出力されたので、満遍なくcard_idが含まれている模様
```python
# 各dfのcard_idを格納した辞書を作成
card_id = {"all_card_id":all_df["card_id"].unique(),
           "historical_card_id":historical_df["card_id"].unique()}

card_id
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3179561/7db15c9e-af62-56a3-88f1-e1353672369c.png)
```python
# 空集合のsetにそれぞれの値を格納し、両方に存在する値の数を数える
len(set(card_id["all_card_id"])&set(card_id["historical_card_id"]))
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3179561/e938a8e8-9f7e-2dbe-c59f-1a82a47a944a.png)

Authorized FlagのYとNの数を比較
```python
# authorized_flagごとにカウント
authorized_counts = historical_df["authorized_flag"].value_counts()
authorized_counts

# 棒グラフを作成、authorized_counts.plot(kind='bar')も可
authorized_counts.plot.bar()
plt.xlabel("Authorized Flag")
plt.ylabel('Count')
plt.title('Count of each Authorized Flag')
plt.xticks(rotation=0)
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3179561/c0c86217-4270-aef8-d848-b3c7b2ce4d0a.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3179561/2927d8db-1af1-fb98-9378-33132ea3efb0.png)

約8.6%が承認されなかった模様
```python
# 比較しにくいので円グラフにする
plt.pie(authorized_counts, labels=authorized_counts.index, autopct='%1.1f%%', startangle=90)
plt.title('Authorized Flag Ratio')
plt.show()
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3179561/1fac683f-bd67-9c9d-5d6d-07c7b8ac9c92.png)

各categoryの比率も確認
```python
# category_1の比率
historical_cat1_counts = historical_df["category_1"].value_counts()

plt.pie(historical_cat1_counts, labels=historical_cat1_counts.index, autopct='%1.1f%%', startangle=90)
plt.title('category_1 Ratio')
plt.show()

# category_2,3も同様に行う
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3179561/23c8f002-4c21-92ad-7bf7-ddb6c015188a.png)

次にinstallmentsを調べてみる
```python
# installmentsのヒストグラム
plt.figure(figsize=(10,4))
plt.hist(historical_df["installments"], bins=50)
plt.title('Histogram of Installments')
plt.xlabel('Installments')
plt.ylabel('Frequency')
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3179561/bc4dba84-d300-de4b-627d-de72a9fccb9b.png)
```python
# 何もわからないので各値を見る
historical_df["installments"].value_counts()

# どうやら999が悪さしている模様
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3179561/8d1020d5-062f-af0b-786d-93b742479f81.png)

※ここで、Google colabだとRAM上限に達するので環境をVSCodeに変更。やり方は[▶この記事](https://qiita.com/kame_house_0828/items/1b8beccac82b9f8f7cfc)を参考に

```python
# 999を削除あと、-1も意味が分からないので削除
historical_df = historical_df[~historical_df["installments"].isin([-1, 999])]

# 改めてヒストグラム出すと以下
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3179561/8692f04f-c287-70da-9ad0-98f7e3357d5d.png)

次にpurchase_amountを調べる
```python
# purchase_amountの最小値と最大値の確認
print(historical_df["purchase_amount"].min(),
      historical_df["purchase_amount"].max())
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3179561/432900cc-cc99-f978-f0e7-78c76066dab3.png)
```python
# ヒストグラムにすると分布が確認できなさそうなので、箱ひげ図に
plt.figure(figsize=(12,4))
plt.boxplot(historical_df["purchase_amount"], vert=False, whis=1.5)
plt.title("Box plot of target")
plt.xlabel("purchase amount")
plt.show()
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3179561/8807ef2a-ae6f-6c03-7cd5-c12eda198197.png)
```python
# 外れ値を削除
historical_df = historical_df[historical_df["purchase_amount"]<=6010603]

print(historical_df["purchase_amount"].max())
# いい感じ
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3179561/5777553e-eee9-e140-e55a-8b2f094146e6.png)
再度、箱ひげ図作成
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3179561/2cc068e0-ff0b-707d-a185-63eb1fef81ff.png)

次にmonth_lagを調べる
```python
# month_lagのヒストグラムを求める
plt.figure(figsize=(10,4))
plt.hist(historical_df["month_lag"], bins=50)
plt.xlabel("month_log")
plt.ylabel("Frequency")
plt.show()
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3179561/88f3bbae-d49a-4c8d-90ed-af8e97688e57.png)





## データの前処理
#### train_dfの前処理
targetが-33未満のものを削除
```python
train_df = train_df[train_df["target"]>-33]
print(train_df.shape)
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3179561/79a8b9f1-5309-6945-6d90-1bb43dd34b7f.png)

#### test_dfの前処理
first_active_monthを最頻値で補完
```python
# test_dfのfirst_active_monthを最頻値を算出
mode_test_df = test_df["first_active_month"].mode()[0]
mode_test_df
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3179561/eb49034d-148d-d2d9-463b-e860fddbfcea.png)
```python
# 最頻値で埋める
test_df["first_active_month"].fillna(mode_test_df, inplace=True)
```

#### historical_dfの前処理
category_2の欠損値を補完する
```python
# いったん最頻値で埋める
mode = historical_df["category_2"].mode()[0]

historical_df["category_2"].fillna(historical_df["category_2"].mode()[0], inplace=True)
```

merchant_idの欠損値を"missing_id"で埋める
```python
historical_df["merchant_id"].fillna("missing_id", inplace=True)

historical_df.isnull().sum()
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3179561/76e03227-f934-132b-1d87-5ba2dcce2691.png)

～part3に続く～