Python in ExcelとPowerQueryを使って機械学習をし、タイタニックの生存者を予測する

# はじめに
Python in Excel はExcelでPythonのコードを実行することができます。
VBAと違い、セル内にコードを記述します。
せっかくExcelを使うので、セル内にゴリゴリコードを書くのではなく、PowerQueryを活用した機械学習をやってみました。

# やること
Python in Excel を用いて機械学習をする。
Kaggleの「Titanic - Machine Learning from Disaster」を用いて、回答を提出する。
（https://www.kaggle.com/competitions/titanic/）
データの前処理に**PowerQuery**を活用し、Pythonのコードをあまり書かずに実現する。
### ※注
投稿者はPython in Excelで初めて機械学習に触れたレベルですので、機械学習の解説はありません。とりあえず提出できることを目標にしています。

# 回答提出までの流れ
　・訓練データをPowerQueryで整形して取り込む
　・機械学習モデルを作成する
　・テストデータをPowerQueryで取り込み、提出用のファイルを作成する

# データ取り込み
まずはPowerQueryでデータを取り込み、seabornで可視化してデータを分析します。
### 取り込み
1. 訓練データ(train.csv)をダウンロードする
2. データの取得の「テキストまたはCSVから」で訓練データを取り込む
　　「読み込み先」を選択し、「接続の作成のみ」にする
![1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3562157/17c2973e-74f6-1b0e-d95c-c58e13563ad7.png)
![2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3562157/895120a3-91ef-0c40-03e2-166a9e5190e6.png)


### 欠損値を確認
PowerQuery上で、フィルタから欠損値を確認します。Age、Embarkedに欠損値(null)があるようです。もしAgeやEmbarkedを機械学習に使用する場合は、何らかの方法で補完する必要があります。
![11.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3562157/da1efa78-fea4-a6e9-4bdf-ab5feb55ce48.png)

### Excelのシートに取り込む
セル内で
~~~:A1セル
=PY( 
~~~
と入力すると、数式バーが緑色の"PY"に代わり、Pythonの入力モードになります。
ここでPythonのコードを入力し、「Ctrl＋Enter」でコミットします。
![3.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3562157/e5f1ff60-2568-969e-d80b-3c18bb6d33da.png)
~~~python:A1セル
in_data = xl("train")
~~~
と入力して、PowerQueryのデータを取得します。
セルの表示は下記のようになります。
![4.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3562157/68092afa-df0a-4daa-7ee4-52b26a0f5b3b.png)
データの中身を表示することもできます。
数式バーからPython出力を「Excelの値」に変更することでデータの中身が表示されます。
![5.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3562157/cdd59742-4b1f-ec52-a54e-e6f683ebe195.png)
テーブルの中身が表示されます。
![6.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3562157/7e86e75a-1c95-d350-df64-cde448106a36.png)

### seabornで可視化
では取り込んだ訓練データをグラフなどで表示します。
seabornを使います。seabornは初めからsnsとしてimportされていますので、先ほどのコードに１行追加することでグラフを表示することができます。
~~~python:A1セル
in_data = xl("train")
sns.barplot(x='Age', y='Survived' , data=in_data)
~~~
![7.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3562157/64b8a741-552a-ee17-28b5-fa47cd7df47e.png)
グラフが小さいので、セルの大きさを調整します。
![8.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3562157/ad858264-2ccc-7e94-3d1f-d47d5d219d4d.png)
計算順序はA1→B1→C1…→A2→B2→C2…→A3→…と進みます。
次は隣のB1セルに違うグラフを書いてみます。A1セルで「in_data」は定義済みのため、改めて定義する必要はありません。
~~~python:B1セル
sns.barplot(x='Pclass', y='Survived' , data=in_data)
~~~
![9.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3562157/8d3dd8c5-77a8-b084-477b-586a70fa1291.png)

ヒートマップも描いてみます。
~~~python:C1セル
cor = in_data[[ 'Survived','Age','Pclass','Fare']].corr()
sns.heatmap(cor, cmap= sns.color_palette('coolwarm', 10), annot=True,fmt='.2f', vmin = -1, vmax = 1)
~~~
![10.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3562157/f36c68a3-1af8-6367-7a77-be317a177335.png)

データを可視化して特徴を掴み、データを整形していきます。

# 訓練データをPowerQueryで整形する
### 文字を数値に
Sexはmale、femaleと文字列になっています。
機械学習のために男性=0、女性=1と数値に変更します。
PowerQueryの値の置換で変換します。
ちなみに「female」を1に変換するより先に、「male」を0に変換すると「female」が「fe0」になるので注意してください。
![12.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3562157/faec171c-fa6b-4f60-7e8c-3f80a4b42b57.png)

同じようにEmbarkedのC=0､S=1､Q=2に置換します。

### 2つの項目を足す
SibSpは兄弟、配偶者の数、Parchは親、子供の数です。
この２つを足して、新しい項目「Family」を作成します。
カスタム列から新しい列名を「Family」、カスタム列の式を[SibSp]+[Parch]に設定します。
![13.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3562157/6a9acc91-203a-4682-93d0-0c96c2353a0c.png)

### 条件分岐
Family=0の時は単独での乗船となり、生存率に影響がありそうです。
Family=0の時に1となる新しい列「IsAlone」を作成します。
条件列の追加から作成します。
![14.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3562157/ecd99eb8-30e6-d2bf-a866-4d5cc384a3d3.png)

### 欠損値の処理
Ageに欠損値があるままだと正しく機械学習できません。
カスタム列Age2を作成し、AgeがnullならAgeの平均値を返すようにします。
~~~csharp:Age2
 if [Age] = null then List.Average(削除された列[Age]) else [Age]
~~~
![16.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3562157/a0d1e226-6345-7f90-c795-419cc149c749.png)

### 名前に特定の文字が含まれているか
Nameに特定の文字が含まれているかどうかで、人物の属性を分けます。
~~~:属性の分け方一覧
Officer:"Rev""Dr""Major""Capt""Col"のどれかが含まれている
Royalty:"Jonkheer""Don""Sir""the Countess""Dona""Lady"のどれかが含まれている
Mr:"Mr."が含まれている
Master:"Master"が含まれている
Miss:"Mlle""Miss"のどれかが含まれている
Mrs:"Mme""Ms""Mrs"のどれかが含まれている
Master:"Master"が含まれている
~~~
条件列の追加から下記のように作成します。
図はOfficerの項目です。
![15.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3562157/2604cfdb-9e13-c877-a271-a880b615f23d.png)

### 年齢を４つのカテゴリに分ける
年齢をそのまま使うのではなく、4つのカテゴリに分けて学習に使用します。
年齢順に4分割して階層を作成し、下位25%、25%~50%、50%~75%、75%以上の4つのカテゴリに分類します。

1.行数の変数を作成する
詳細エディターに、行数を入れる変数「row_count」を定義します。
~~~csharp:詳細エディターに追加
row_count = Table.RowCount(変更された型),
~~~
こんな感じで途中（4行目)に入れときます。
![17.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3562157/b84ba978-430d-d486-2f91-6c4567c337d4.png)

2.Age列を昇順に並び替える
PowerQueryのホームタブの並び替えで並び替える

3.インデックス列を追加する

4.インデックス列 ÷ row_count　からどのカテゴリに属するか判定する
項目Age3を作成し、下位25%なら0、25%~50%なら1、50%~75%なら2、75%以上なら3にします。
~~~csharp:Age3
if [インデックス]/row_count < 0.25 then 0 else if [インデックス]/row_count < 0.5 then 1 else if [インデックス]/row_count < 0.75 then 2 else 3
~~~

### 不要な列を削除する
Survived、Pclass、Sex、Family、IsAlone、Officer、Royalty、Master、Mrs、Miss、Mr、Age3以外の列は不要なので削除します。

# 機械学習モデルを作成する
セルA2にPythonコードを書いていきます。
X_allはSurvived列を除いたテーブル、y_allはSurvied列のみのテーブルです。
訓練データの1/3を予測精度テストに用います。
~~~python:A2セル
from sklearn.model_selection import train_test_split
X_all = xl("train").drop(columns='Survived')
y_all = xl("train")['Survived']
X_train, X_test, y_train, y_test = train_test_split(X_all, y_all, test_size=0.33, random_state=0)
~~~
これで訓練データを学習用のデータと精度確認用のデータに分割できました。
さらにA3セルに予測精度を確認するコードを記述します。
~~~python:A3セル
from sklearn.ensemble import RandomForestClassifier
from sklearn.metrics import classification_report
RF=RandomForestClassifier()
RF.fit(X_train, y_train)
y_true, y_pred = y_test, RF.predict(X_test)
classification_report(y_true, y_pred)
~~~
セルにレポートが表示されます。
accuracyが82%なので、まあまあの予測精度がありそうです。
![24.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3562157/13f1c545-a189-d332-76ed-daae3a0deba2.png)

# テストデータをPowerQueryで取り込み、提出用のファイルを作成する
テストデータをPowerQueryで取り込み、訓練データの取り込みと同じ加工をする必要があります。訓練データと同様に接続のみで作成し、詳細エディターでCSV取り込みの行（1行目)より後を訓練データからコピペすればOKです。
コピペしたら2点修正します。

1 .テストデータにはSurvived列が無いので、Survived列のヘッダを作る部分を削除します
~~~csharp:詳細エディター
変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"PassengerId", Int64.Type}, {"Survived", Int64.Type}, {"Pclass", Int64.Type}...
~~~
「{"Survived", Int64.Type},」を削除します。

2 .Kaggleに提出するデータを作成するのでPassengerId列を削除せずに残しておきます
Kaggleに提出するデータはPassengerIdとSurvived列が必要なので、PassengerIdを残します。

閉じてExcelのシートに戻ります。
新しいシートを作成し、A1セルに入力します。
PassengerId列は予測には不要であるため、初めにPassengerIdを除外して予測し、予測した結果に改めてPassengerId列を結合します。
~~~python:シート2：A1セル
X_test = xl("test").drop(columns='PassengerId')
y_pred = RF.predict(X_test)
my_solution = pd.DataFrame({'PassengerId':xl("test")['PassengerId'],'Survived':y_pred})
~~~

タイタニックの生存者予想ができました。
![25.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3562157/0ffb6c1a-8078-7d53-c8ad-3f8c25790c1c.png)

あとはこれをCSVに保存して、Kaggleに投稿すればOKです。
![21.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3562157/1754310a-c861-9d3b-afe4-32409959bca4.png)

スコアは**0.77511**でした。accuracyが82%あったのでもう少し良いスコアが出るのかと思いましたが、簡単にはいかないようです。

# 感想
データの前処理をPowerQueryでやりましたので、予測に必要なPythonのコードは10行ほどで済みました。
Python in Excelについて、「セルの中にコードを書くのがやりづらい」「Pythonで処理してCSVでExcelにデータ渡した方が効率が良い」といった意見を見ました。
Pythonでコードが書くことができる人向けというよりは、私のように普段はExcelしか使っていないような人がセルに数行のコードをチマチマ書いて、よりリッチな可視化や分析をしていくと便利かと思います。