【SQL Server 2017 による In-Database Python 分析 チュートリアル】Step 3: データの探索と可視化

#### チュートリアルのはじめから
[SQL開発者のための In-Database Python 分析](http://qiita.com/qio9o9/items/2a521f5ac8afe39e1721)

#### 前のステップ
[Step 2: PowerShellを使用したSQL Serverへのデータインポート](http://qiita.com/qio9o9/items/98df36982f1fbecdf5e7)

#### 次のステップ
[Step 4: T-SQLを使用したデータの特徴抽出](http://qiita.com/qio9o9/items/904b4045cb60d3dbe63f)

# Step 3: データの探索と可視化

データサイエンスソリューション開発には、通常、集中的なデータの探索とデータの可視化が含まれます。このステップでは、サンプルデータを探索しいくつかのプロットを生成します。さらに、Pythonでグラフィックスオブジェクトをシリアライズする方法、およびデシリアライズしてプロットを作成する方法を学習します。

> [!NOTE]
> このチュートリアルでは二項分類モデルのみを示します。回帰分類や多項分類などほかのモデルを構築することも可能です。

## データの確認

元のデータセットにはタクシー識別子と運転記録が別々のファイルで提供されていますが、サンプルデータを使いやすくするために_medallion_、_hack_license_、_pickup_datetime_をキーにジョインしています。また使用するレコードは、元のレコード数の1％でサンプリングしています。サンプリングされたデータセットは1,703,957の行と23の列を持っています。

**タクシー識別子**

- medallion列はタクシーの一意なID番号を示します。
- hack_license列は運転手の匿名化された運転免許証番号を示します。

**運転記録および運賃記録**

- 各運転記録には、乗車と降車の場所と時間、および運転距離が含まれます。
- 各運賃記録には、支払タイプ、合計支払い額、チップ金額などの支払情報が含まれます。
- 最後の3つの列は、さまざまな機械学習タスクに使用できます。
    - tip_amount列は連続した数値が含まれ、回帰分析のためのラベル列（目的変数）として使用できます。
    - tipped列は yes / no 値のみがあり、二項分類のためのラベル列（目的変数）として使用できます。
    - tip_class列は複数の**クラスラベル**があり、多項分類のためのラベル列（目的変数）として使用できます。
- ラベル列として使用される値はtip_amount列に基づいています。

    |列|ルール|
    |:--|:--|
    |tipped|If tip_amount > 0, tipped = 1, otherwise tipped = 0|
    |tip_class|Class 0: tip_amount = 0ドル <br/> Class 1: tip_amount > 0ドル and tip_amount <= 5ドル <br/> Class 2: tip_amount > 5ドル and tip_amount <= 10ドル <br/> Class 3: tip_amount > 10ドル and tip_amount <= 20ドル <br/> Class 4: tip_amount > 20ドル|

## T-SQL内のPythonでプロットを作成する

可視化はデータと異常値の分布を理解するために重要で、Pythonにはデータ可視化のための多くのパッケージが提供されています。matplotlibモジュールは、ヒストグラム、散布図、箱ひげ図、およびその他のデータ探索グラフを作成するための多くの機能を含みます。

このセクションでは、ストアドプロシージャを使用してプロットを操作する方法を学習します。ここではプロットをvarbinary型のデータとして扱っています。

### プロットをvarbinaryデータ型として格納する

SQL Server 2017 Machine Learning Servicesに含まれるPythonライブラリの**RevoScalePy**パッケージは、RライブラリのRevoScaleRパッケージに相当します。この例ではrxHistogramを使用し、Transact-SQLクエリの結果データに基づいたヒストグラムをプロットします。利用を簡単にするためにPlotHistogramストアドプロシージャでラップします。

このストアドプロシージャはシリアライズされたPython描画オブジェクトをvarbinaryデータのストリームとして返します。バイナリデータは直接表示することはできませんが、クライアント上でPythonコードを使用してバイナリデータをデシリアライズし、その画像ファイルをクライアントコンピュータに保存します。

### SerializePlotsストアドプロシージャを定義する

ストアドプロシージャ`SerializePlots`は[Step 2: PowerShellを使用したSQL Serverへのデータインポート](http://qiita.com/qio9o9/items/98df36982f1fbecdf5e7)を通じてSQL Serverに定義されています。

1. Management Studioのオブジェクトエクスプローラで、[プログラミング]、[ストアドプロシージャ]の順に展開します。

2. `SerializePlots`を右クリックし、[変更] を選択して新しいクエリウィンドウでTransact-SQLスクリプトを開きます。
    
    ```SQL:SerializePlots
    
    CREATE PROCEDURE [dbo].[SerializePlots]
    AS
    BEGIN
      SET NOCOUNT ON;
      DECLARE @query nvarchar(max) =
      N'SELECT cast(tipped as int) as tipped, tip_amount, fare_amount FROM [dbo].[nyctaxi_sample]'
      EXECUTE sp_execute_external_script
      @language = N'Python',
      @script = N'
    import matplotlib
    matplotlib.use("Agg")
    import matplotlib.pyplot as plt
    import pandas as pd
    import pickle

    fig_handle = plt.figure()
    plt.hist(InputDataSet.tipped)
    plt.xlabel("Tipped")
    plt.ylabel("Counts")
    plt.title("Histogram, Tipped")
    plot0 = pd.DataFrame(data =[pickle.dumps(fig_handle)], columns =["plot"])
    plt.clf()

    plt.hist(InputDataSet.tip_amount)
    plt.xlabel("Tip amount ($)")
    plt.ylabel("Counts")
    plt.title("Histogram, Tip amount")
    plot1 = pd.DataFrame(data =[pickle.dumps(fig_handle)], columns =["plot"])
    plt.clf()

    plt.hist(InputDataSet.fare_amount)
    plt.xlabel("Fare amount ($)")
    plt.ylabel("Counts")
    plt.title("Histogram, Fare amount")
    plot2 = pd.DataFrame(data =[pickle.dumps(fig_handle)], columns =["plot"])
    plt.clf()

    plt.scatter( InputDataSet.fare_amount, InputDataSet.tip_amount)
    plt.xlabel("Fare Amount ($)")
    plt.ylabel("Tip Amount ($)")
    plt.title("Tip amount by Fare amount")
    plot3 = pd.DataFrame(data =[pickle.dumps(fig_handle)], columns =["plot"])
    plt.clf()

    OutputDataSet = plot0.append(plot1, ignore_index=True).append(plot2, ignore_index=True).append(plot3, ignore_index=True)
    ',
                                     @input_data_1 = @query
      WITH RESULT SETS ((plot varbinary(max)))
    END

    GO
  
    ```
    - 変数`@query`は、Pythonコードブロックへのインプット`@input_data_1`として渡されるクエリテキストを定義しています。
    - **matplotlibライブラリ**の`figure`によってヒストグラムと散布図を作成し、これらのオブジェクトを**pickleライブラリ**を使用してシリアライズしています。
    - Python描画オブジェクトはアウトプットのために**pandas**データフレームへシリアライズされます。

### varbinaryデータを画像ファイルとして出力する

1.  Management Studioで以下のクエリを実行します。
  
    ```
    EXEC [dbo].[SerializePlots]
    ```
  
    ![sqldev-python-step3-1-gho9o9.png](https://qiita-image-store.s3.amazonaws.com/0/195839/2c6ef140-e6fd-df9f-12a3-46b25e33ccb3.png)

  
2.  ダウンロードしたファイル群の中のPythonスクリプト`DeserializeSavePlots.py`内の接続文字列を環境に合わせて変更した後、実行します。
  
    **SQL Server 認証の場合**
    
    ```python:DeserializeSavePlots
    import pyodbc
    import pickle
    import os
    cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER={SERVER_NAME};DATABASE={DB_NAME};UID={USER_NAME};PWD={PASSOWRD}')
    cursor = cnxn.cursor()
    cursor.execute("EXECUTE [dbo].[SerializePlots]")
    tables = cursor.fetchall()
    for i in range(0, len(tables)):
        fig = pickle.loads(tables[i][0])
        fig.savefig(str(i)+'.png')
    print("The plots are saved in directory: ",os.getcwd())
    ```

    **Windows認証の場合**

    ```Python:DeserializeSavePlots.py
    import pyodbc
    import pickle
    import os
    cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER={SERVER_NAME};DATABASE={DB_NAME};Trusted_Connection=yes;')
    cursor = cnxn.cursor()
    cursor.execute("EXECUTE [dbo].[SerializePlots]")
    tables = cursor.fetchall()
    for i in range(0, len(tables)):
        fig = pickle.loads(tables[i][0])
        fig.savefig(str(i)+'.png')
    print("The plots are saved in directory: ",os.getcwd())
    ```

    > [!NOTE]
    > Pythonのランタイムのバージョンをサーバとクライアントで合わせてください。またクライアントで使用するmatplotlibなどPythonライブラリのバージョンはサーバと合わせるもしくは上位のバージョンとしてください。

3.  接続が成功すると、以下の結果が表示されます。

    ![sqldev-python-step3-2-gho9o9.png](https://qiita-image-store.s3.amazonaws.com/0/195839/6e4f1b8e-4194-3ad0-2a81-78da82130299.png)

4.  4つのファイルがPythonの作業ディレクトリに作成されます。

    チップが得られた数と得られなかった数を示します。
    ![sqldev-python-step3-3-1-gho9o9.png](https://qiita-image-store.s3.amazonaws.com/0/195839/66586b25-4b5f-44a4-126a-84c0ac9f8da2.png)

    チップ金額の分布を示します。
    ![sqldev-python-step3-3-2-gho9o9.png](https://qiita-image-store.s3.amazonaws.com/0/195839/76cd7465-aa84-2f52-2441-e3064d5887a1.png)

    運賃の分布を示します。
    ![sqldev-python-step3-3-3-gho9o9.png](https://qiita-image-store.s3.amazonaws.com/0/195839/f03cc7ab-67cf-040d-5567-6ad2b7061ed6.png)

    x軸上に運賃、y軸上にチップ金額とした散布図です。
    ![sqldev-python-step3-3-4-gho9o9.png](https://qiita-image-store.s3.amazonaws.com/0/195839/a0252b2e-5d9b-9414-2040-ddbcd4e2625a.png)
    
## リンク

#### 次のステップ
[Step 4: T-SQLを使用したデータの特徴抽出](http://qiita.com/qio9o9/items/904b4045cb60d3dbe63f)

#### 前のステップ
[Step 2: PowerShellを使用したSQL Serverへのデータインポート](http://qiita.com/qio9o9/items/98df36982f1fbecdf5e7)

#### チュートリアルのはじめから
[SQL開発者のための In-Database Python 分析](http://qiita.com/qio9o9/items/2a521f5ac8afe39e1721)

#### 出典
[Step 3: Explore and Visualize the Data](https://docs.microsoft.com/en-us/sql/advanced-analytics/tutorials/sqldev-py3-explore-and-visualize-the-data)

#### 関連項目
[Machine Learning Services with Python](https://docs.microsoft.com/en-us/sql/advanced-analytics/python/sql-server-python-services)