pimientitoの機械学習

「機械学習って何だろう。」から、はじまり、いまだ???の毎日。数学初心者、PG・DBアマチュアのサービス・エンジニアが、どこまで理解できるのか。

【前処理の学習-32】データを学ぶ ~分割~②

 前回は、交差検証を行なうためのデータの分割について学びました。

pimientito-handson-ml.hatenablog.com

 今回は、時系列データの分割について学びます。


【今回の目標到達点】

 時系列データの分割を学ぶ

【目次】


参考資料のご紹介

 はじめに、現在、主に参考とさせていただいている書籍をご紹介します。

 「前処理大全 データ分析のためのSQL/R/Python実践テクニック」本橋智光氏著(技術評論社)


「分割」の概要

 参考資料「前処理大全」第5章「分割」の冒頭で、著者はデータの分割について、以下のように述べています。

データの分割は予測モデルを評価する際に必要になる前処理です。主に学習データ(予測モデルを構築する際に利用するデータ)と検証データ(モデルの精度を測定するためのデータ)の分割に利用されます。

 学習データと検証データは、必要とする列データは同じです。「予測モデルに入力するための列データ」と「予測モデルの予測対象の列データ」です。そのため、学習データと検証データに対して適用する前処理はすべて同じです。

参考・参照元:第5章「分割」(p.130)より抜粋


 第5章「分割」では、データの分割について、以下の方法が紹介されています。


  • レコードデータにおけるモデル検証用のデータ分割

  • 時系列データにおけるモデル検証用のデータ分割



今回のテーマ

 時系列データにおけるモデル検証用のデータ分割


概要

 参考資料5-2「時系列データにおけるモデル検証用のデータ分割」では、時系列データの分割方法について紹介されています。

 なお著者曰く、前回学んだ交差検証では、時系列データを安易に使用できないと言及されています。以下、該当部分の抜粋です。

多くの人が間違えてしまいがちなのですが、実は時系列データにおいて単純な交差検証は有効ではありません。なぜなら、交差検証によって不当にモデル精度が高くなってしまうことが多いからです。これは、未来のデータを使って予測モデルを作成し、過去のデータを検証しているケースが混ざってしまっていることが大きな原因です。

参考・参照元:第5章「分割」5-2「時系列データにおけるモデル検証用のデータ分割」(p.138-139)より抜粋


 学習データに、予測対象のデータが混入することを「リーク(leak)」と呼びます。リークについては、前章「結合」で軽く触れました。

pimientito-handson-ml.hatenablog.com


 前回【前処理の学習-31】で学んだ交差検証(クロス・バリデーション)では、分割したデータを順序不同で学習・検証するため、未来データのリークを防ぐことができません。

f:id:Pimientito:20190601215316j:plain
交差検証の流れ


 上図の場合、オレンジ色の枠が検証データに該当します。学習・検証データの選択位置が交差検証ごとに前後するため、時系列データが適していないことが分かります。


 そこで著者は、時系列データに適した分割方法を紹介しています。


  • 時間軸に対してスライドしながら検証する方法

  • 時間軸に対して学習データを増やしながら検証する方法



 ひとつずつ処理の概要を学習します。


時間軸に対してスライドしながら検証する方法

 一つ目は、分割したデータを、過去から未来へ順次スライドする方法です。

f:id:Pimientito:20190701232018j:plain
学習期間を一定にするパターン


 この方法は、学習データ・検証データの抽出期間を固定して、検証ごとにデータの抽出場所をスライドします。例えば、検証データが二か月分右に移動すると、学習データも二か月分右に移動します。


 常に一定の期間でスライドしながら学習を進めるため、データのリークは発生しません。(事前に、日付型データの並び替えは必要です)


 上図「学習期間を一定にするパターン」の場合は、以下のようにデータをスライドしながら学習・検証しています。


  • 検証1回目:学習データ(1月~6月)、検証データ(7月~8月)

  • 検証2回目:学習データ(3月~8月)、検証データ(9月~10月)

  • 検証3回目:学習データ(5月~10月)、検証データ(11月~12月)



 この例題の問題点は、年間を通した学習・検証になっていないため、季節によって変動する傾向を正確に捉えることができません。そのため、このような学習・検証を行なう場合は、使用するデータが通年になるよう長い期間のデータが必要になると著者は述べています。


時間軸に対して学習データを増やしながら検証する方法

 二つ目は、時系列データの期間が短い場合、検証ごとにデータをスライドさせずに、使用済み検証データを、学習データに追加しデータ量を補う方法です。

f:id:Pimientito:20190701232125j:plain
学習期間を増やしていくパターン


 上図「学習期間を増やしていくパターン」の場合は、以下のように学習データを増やしながら、学習・検証しています。


  • 検証1回目:学習データ(1月~6月)、検証データ(7月~8月)

  • 検証2回目:学習データ(1月~8月)、検証データ(9月~10月)

  • 検証3回目:学習データ(1月~10月)、検証データ(11月~12月)



 この方法にも問題はあり、検証ごとに学習データ量が異なるため、モデルの精度を正確に把握することは難しく、そのため、データ量とモデル精度の関係も同時に把握する必要があるとのことです。


 データとモデルの精度の相関関係を知る方法については、まだ学習経験が無く、手順が不明なため、今回は割愛します。


サンプルコード[Python]

 参考資料によると、Pythonには、時系列データを分割処理するライブラリが、いまのところ無いため、自身でコーディングする必要があるそうです。ここでは、参考資料のサンプルコードを通して処理や動作を確認します。


サンプルデータセットを作成

 今回のサンプルデータセットの内容は、以下の通りです。

カラム名 内容 レコード件数
T_Data YYYY-mm-dd形式の日付型データ 731
Num1 np.random.randで生成した実数値データ 731
Num2 np.random.randで生成した実数値データ 731


 日付型データは、2019-01-01から2020-12-31の二年間731日分のデータです。2020年が閏年のため、レコード数が731件になります。


 実数型データのNum1とNum2は、筆者自身が、あまりNumPyライブラリに慣れていないため、練習として作成しました。カラム名や数値自体には、特に意味はありません。


 データセットを作成するコードは、以下の通りです。

#  【前処理の学習-32】データを学ぶ ~分割~②
# テストテーブル作成

import pandas as pd
import numpy as np

# 日付データの作成方法
tbl_lesson32 = pd.DataFrame({
    'T_Date' : pd.date_range('2019-01-01', '2020-12-31'), 
    'Num1' : np.random.rand(731), 
    'Num2' : np.random.rand(731)})


 作成されたデータセットの内容を、head関数と、info関数で確認します。

f:id:Pimientito:20190703214032p:plain
データの確認(head関数、info関数)


 日付型データと実数型データが作成されました。続いて参考資料のサンプルコードから時系列データを分割する方法を学びます。コーディングする前に、処理の流れを掴むためにイメージ図を作成しました。

f:id:Pimientito:20190704002034j:plain
サンプルコードの処理の流れ(時系列データの分割)


 前述した図「学習期間を一定にするパターン」「学習期間を増やしていくパターン」では、月単位のデータで学習・検証をしていましたが、サンプルコードでは、レコード単位でスライドしています。


 サンプルコードを参考に、再作成したコードは、以下の通りです。

# 前処理大全 5-2「時系列データにおけるモデル検証用のデータ分割」
# サンプルコード python_awesome.py(p.144-145)を参考に作成

# カウンター初期化
counter = 1

#  学習データの開始行番号
train_start = 0

# 学習データの終了行番号
train_end = 24

# 検証データ件数
test_range = 12

# スライドするデータ件数
slide_rows = 12

# 年月に基づいてデータを並び替え
tbl_lesson32.sort_values(by='T_Date')

while True:
    # 検証データの終了行番号
    test_end = train_end + test_range
    
    # 行番号を指定して、元データから学習データを抽出
    train = tbl_lesson32[train_start : train_end]
    print('train : ' + str(counter))
    print(train)
    print('')
    
    # 行番号を指定して、元データから検証データを抽出
    test = tbl_lesson32[train_end : test_end]
    print('test : ' + str(counter))
    print(test)
    print('')
    
    # 検証データの終了行番号が元データの行数以上になっているか判定
    if test_end > len(tbl_lesson32.index):
        
        # 全データを対象にした場合、終了
        break
        
    # データをスライドさせる
    train_start += slide_rows
    train_end += slide_rows
    
    # カウンター加算
    counter += 1
    
# --- これ以降は、交差検定の結果をまとめるコード ---


 実行結果は、以下の通りです。

f:id:Pimientito:20190703221604p:plain
時系列データ分割一周目(学習データ・検証データ)


 左端のインデックス番号を追うと、レコード単位で移動していることが分かります。次の結果は、二周目の学習データと検証データです。


f:id:Pimientito:20190703221708p:plain
時系列データ分割二周目(学習データ・検証データ)


 学習データと検証データの間に位置するレコード(赤枠部分)の抽出は、注意が必要です。Pythonのスライシング処理では、インデックスの指定を誤ると意図しないところでデータが欠落してしまいます。以下で、スライシングの動作について、少し詳しく学習します。


スライシング

 スライシングとは、[ : ]を使って配列データ(*)から部分抽出する方法です。抽出位置の指定方法は、以下の通りです。(*) 二文字以上の文字列も配列データです。


配列データA [ 抽出開始位置:抽出終了位置]


 ここで、スライシングのすべてをご紹介できませんが、連続したデータを抽出する際に、特に注意が必要な点について学習します。


 例えば、N個ある連続データを二つ以上の変数に分割する時には、以下のような手順でコーディングすると思います。



  • 変数A ⇨ Index[0] 〜 Index[X]

  • 変数B ⇨ Index[X + 1] 〜 Index[N]



 しかし、Pythonのスライシングでは、上記の指定では、変数A、B共に、それぞれ意図している最後尾のデータが欠落してしまいます。スライシングは、指定した開始位置から終了位置ー1までしか抽出されません。


 意図した範囲のデータを抽出する場合、末尾のインデックスに「+1」したインデックスを指定します。スライシングの範囲指定のイメージは、以下の通りです。

f:id:Pimientito:20190713221637j:plain
スライシングのインデックス指定のイメージ図


 イメージ図と同じ内容で、サンプルコードを作成しました。実際にコードを実行しながら、結果を確認します。はじめにデータを作成します。

#  スライシングの動作確認
import pandas as pd

# 日付型サンプルデータの作成
df = pd.DataFrame({
    'date' : pd.date_range('2019-01-01', '2019-01-16')})

# データの確認
df[:]


 作成したデータの内容は、以下の通りです。

f:id:Pimientito:20190713224109p:plain
スライシング動作確認(データ)


 続いてイメージ図と同じように2019-01-01〜2019-01-10のデータを抽出します。2019-01-01のインデックスは「0」、2019-01-10のインデックスは「9」を指しています。

# 学習データ部分抽出の確認 - 本来のインデックス番号の指定 -
df[0:9]


 指定したインデックスで抽出されたデータは、以下の通りです。

f:id:Pimientito:20190713224618p:plain
スライシング動作確認 -その1-


 いままで述べてきた通り、意図した範囲のデータではありませんでした。次に終了位置のインデックスに「+1」した値で指定します。

# 学習データ部分抽出の確認 - インデックス番号+1の指定 -
df[0:10]


 インデックス「10」のデータは「2019-01-11」ですが、抽出できたデータは、2019-01-01〜2019-01-10でした。確認した結果は、以下の通りです。

f:id:Pimientito:20190713225514p:plain
スライシング動作確認 -その2-


 スライシングを使った部分抽出の学習は、ここまでです。


 言語の仕様を正確に把握していないことで、コーディングミスと混同する恐れもあることを、あらためて学びました。これからも言語の細かい仕様について、少しずつ学習を続けたいと思います。


今回の学習

 「今回のテーマ」の内容を踏まえて、最初にテストデータの作成を行います。まず、どのようなデータが、今回の学習に適しているのか検討します。


テストデータの概要

条件

 以下の条件を満たしているデータが、今回の学習に適していると考えます。

  • 時系列データを含んでいること

  • データのボリュームが年単位であること


テストデータの選択

 上記「条件」の内容をもとに考えたところ、すぐに頭に浮かぶのは、本ブログのスタート時から、何度もお世話になっている「気象データ」です。今回も、この気象データを使用して学習します。

f:id:Pimientito:20190707233114p:plain
国土交通省 気象庁「過去の気象データ・ダウンロード」ホームページ


テストデータの加工

 今回は、時系列データの分割について学んでいるため、時系列データのカラム以外は、最小限のカラムに抑えました。データの内容は、以下の通りです。

項目 概要
カラム 年、月、日、日平均気温
範囲 2017/01/01〜2019/07/06
地域 東京
1レコード単位


 気象庁のサイトからダウンロードしたデータを読み込みます。

#【前処理の学習-32】データを学ぶ ~分割~②
# 前処理大全 5-2「時系列データにおけるモデル検証用のデータ分割」

import pandas as pd

# データの読み込み(読み込み開始行:5〜、読み込み対象列:0(年), 1(月), 2(日), 3(日平均気温))
df = pd.read_csv('./data/lesson32_sample_data.csv', encoding='shiftjis', usecols=[0, 1, 2, 3], skiprows=4)
df.columns = ['Year', 'Month', 'Day', 'Temp_mean']


 読み込んだデータの内容は、以下の通りです。

f:id:Pimientito:20190707235143p:plain
データの確認


 ダウンロードしたデータでは、時系列データの「年」「月」「日」が、個々のカラムに別れているため、ひとつのカラムにまとめます。

# データの整形
tbl_lesson32 = \
        pd.DataFrame({'Date' : pd.to_datetime(df[['Year', 'Month', 'Day']], format='%Y%m%d'), 
                      'Temp_mean' : df['Temp_mean'] })


 まとめた結果は、以下の通りです。

f:id:Pimientito:20190707235815p:plain
時系列データ「Date」の確認


 今回のテストデータの加工は、とても簡単ですが、これで準備が整いました。


テストデータを使って学習

 「今回のテーマ」では「月単位」で、データを分割していました。一方、参考資料のサンプルコードでは「レコード単位(日単位)」でスライドする方法でした。ここでは「月単位」で、学習データと検証データを分割する方法のコーディングに挑戦します。


 今回のコーディングでは、時系列データを学習データ・検証データに分割する部分を関数化しました。ただし、学習・検証する処理は、ダミー処理として集約関数を使用しています。


 また実用的なコードのように、一般的な入力チェックやエラーチェックは考慮していません。


自作関数「DateTypeData_Validation」

 関数の引数や戻り値は、以下の通りです。

パラメータ 概要
tbl データテーブル(データフレーム)
vl_start 検証開始年月日
vl_end 検証終了年月日
train_horizon 学習期間(月単位)
test_horizon 検証期間(月単位)
list_col カラムリスト
key_col ソート対象カラム
slide_flag スライド式(True)/学習期間増加式(False)
戻り値 無し


 今回の自作関数の処理の流れは、以下の通りです。

f:id:Pimientito:20190714153107j:plain
自作関数「DateTypeData_Validation」の処理の流れ


 実際のコードは、以下の通りです。なお、処理結果を確認するためのデバッグコードは残したままにしています。

def DateTypeData_Validation(tbl, vl_start, vl_end, train_horizon, test_horizon, list_col, key_col, slide_flag=True):
    
    # Pandasライブラリ
    import pandas as pd
    
    # 日付型データ操作用関数
    from pandas.tseries.offsets import Day, MonthEnd
    
    # 変数初期化
    # ループカウンター
    lp_cnt = 1
    
    # 学習データ開始位置
    train_start_point = pd.to_datetime(vl_start)
    
    # 学習データ終了位置
    train_end_point = train_start_point + MonthEnd(train_horizon)
    
    # 検証データ開始位置
    test_start_point = train_end_point + 1 * Day()
    
    # 検証データ終了位置
    test_end_point = test_start_point + MonthEnd(test_horizon)
    
    # 文字列型日付を、日付型に変換
    tbl[key_col] = pd.to_datetime(tbl[key_col])
        
    # 引数sort_keyに基づいてデータを並び替え
    tbl.sort_values(by=key_col)
    
    # 時系列型データの検証
    while True:
        
        # スライド式の場合
        if slide_flag :
            
            # 学習データ抽出
            train_data = tbl.loc[(pd.to_datetime(tbl[key_col].values) >= train_start_point) & \
                                 (pd.to_datetime(tbl[key_col].values) <= train_end_point), : ]
            #print(train_data.values)
                        
            # 検証データ抽出
            test_data = tbl.loc[(pd.to_datetime(tbl[key_col].values) >= test_start_point) & \
                                (pd.to_datetime(tbl[key_col].values) <= test_end_point), : ]
            
            # データ抽出位置更新
            # 学習データ開始位置
            train_start_point = train_start_point + MonthEnd(test_horizon) + 1 * Day()
            
            # 学習データ終了位置
            train_end_point = train_start_point + MonthEnd(train_horizon)
            
            # 検証データ開始位置
            test_start_point = train_end_point + 1 * Day()
            
            # 検証データ終了位置
            test_end_point = test_start_point + MonthEnd(test_horizon)
        
        # 学習期間増加式の場合
        else :
            
            # 学習データ抽出
            train_data = tbl.loc[(pd.to_datetime(tbl[key_col].values) >= train_start_point) & \
                                 (pd.to_datetime(tbl[key_col].values) <= train_end_point), : ]
            
            # 検証データを抽出
            test_data = tbl.loc[(pd.to_datetime(tbl[key_col].values) >= test_start_point) & \
                                (pd.to_datetime(tbl[key_col].values) <= test_end_point), : ]
                        
            # データ抽出位置更新 
            # 学習データ開始位置(固定のため、更新不要)
            
            # 学習データ終了位置
            train_end_point = train_end_point + MonthEnd(test_horizon)
            
            # 検証データ開始位置
            test_start_point = train_end_point + 1 * Day()
            
            # 検証データ終了位置
            test_end_point = test_start_point + MonthEnd(test_horizon)
        
        
        # 検証
        # 学習データ
        train_result = \
            train_data.groupby([train_data[key_col].dt.year, train_data[key_col].dt.month]).agg({list_col[1] : ['count', 'mean', 'std']})
        
        # 結果の表示(デバッグ用)
        print('')
        print('train_result : ' + str(lp_cnt))
        print('--------------info--------------------')
        print(train_result.info())
        print('--------------values-----------------')
        print(train_result.values)
        print('----------------------------------')


        # 検証データ
        test_result = \
            test_data.groupby([test_data[key_col].dt.year, test_data[key_col].dt.month]).agg({list_col[1] : ['count', 'mean', 'std']})

        # 結果の表示(デバッグ用)
        print('')
        print('test_result : ' + str(lp_cnt))
        print('--------------info--------------------')
        print(test_result.info())
        print('--------------values-----------------')
        print(test_result.values)
        print('----------------------------------')
        print('')
        
           
        # 終了判定(次回のテストデータ終了位置が、指定範囲を超えている場合)
        if test_end_point  > pd.to_datetime(vl_end) : 
            
            print('End of Cross_Validation.')
            break
        
        # ループカウンター
        lp_cnt += 1
        


 関数の呼び出し元は、以下の通りです。

# 関数の呼び出し
DateTypeData_Validation(tbl_lesson32, '20180101', '20181231', 4, 2, tbl_lesson32.columns, 'Date', True)


 パラメータ値は、以下の通りです。

パラメータ
tbl tbl_lesson32
vl_start 2018/01/01
vl_end 2018/12/31
train_horizon 4(ヶ月)
test_horizon 2(ヶ月)
list_col tbl_lesson32.columns
key_col Date
slide_flag True(スライド式)


 「slide_flag」で、Trueを指定すると「スライド式」、Falseを指定すると「学習期間増加式」の検証に切り替わります。


  ここからは、この関数を各パートに分けて見て行きます。

抽出範囲の指定(スライド式)

 スライド式でデータを抽出する部分です。今回のコーディングでは、loc関数で抽出範囲を指定しています。スライド式のイメージは、以下の通りです。

f:id:Pimientito:20190714002519j:plain
スライド式イメージ図


 データ抽出後、次のループのために、抽出開始位置と終了位置の値を更新します。

# 学習データ抽出
train_data = tbl.loc[(pd.to_datetime(tbl[key_col].values) >= train_start_point) & \
                    (pd.to_datetime(tbl[key_col].values) <= train_end_point), : ]
                        
# 検証データ抽出
test_data = tbl.loc[(pd.to_datetime(tbl[key_col].values) >= test_start_point) & \
                   (pd.to_datetime(tbl[key_col].values) <= test_end_point), : ]

# データ抽出位置更新
# 学習データ開始位置
train_start_point = train_start_point + MonthEnd(test_horizon) + 1 * Day()
            
# 学習データ終了位置
train_end_point = train_start_point + MonthEnd(train_horizon)
            
# 検証データ開始位置
test_start_point = train_end_point + 1 * Day()
            
# 検証データ終了位置
test_end_point = test_start_point + MonthEnd(test_horizon)


抽出範囲の指定(学習期間増加式)

 学習期間増加式でデータを抽出する部分です。こちらもスライド式同様、loc関数を使用して抽出範囲を決めています。


 学習期間増加式の場合、学習データの開始位置は固定にしています。検証が完了して、次のループ時には、前回ループ時の検証データ抽出範囲を学習データに追加します。


 学習期間増加式のイメージは、以下の通りです。

f:id:Pimientito:20190714003026j:plain
学習期間増加式イメージ図


 学習データの開始位置は固定のため、コーディングは不要ですが、コードの該当場所には、コメントを残しておきます。

# 学習データ抽出
train_data = tbl.loc[(pd.to_datetime(tbl[key_col].values) >= train_start_point) & \
                    (pd.to_datetime(tbl[key_col].values) <= train_end_point), : ]
            
# 検証データを抽出
test_data = tbl.loc[(pd.to_datetime(tbl[key_col].values) >= test_start_point) & \
                  (pd.to_datetime(tbl[key_col].values) <= test_end_point), : ]
                        
# データ抽出位置更新 
# 学習データ開始位置(固定のため、更新不要)
            
# 学習データ終了位置
train_end_point = train_end_point + MonthEnd(test_horizon)
            
# 検証データ開始位置
test_start_point = train_end_point + 1 * Day()
            
# 検証データ終了位置
test_end_point = test_start_point + MonthEnd(test_horizon)


学習と検証

 続いて、学習・検証のパートとなります。筆者は、まだ「予測モデル」を作成したことがないため、今回はダミー処理として、groupby関数と集約関数(count、mean、std)を実行しています。

# 検証
# 学習データ
train_result = \
    train_data.groupby([train_data[key_col].dt.year, train_data[key_col].dt.month]).agg({list_col[1] : ['count', 'mean', 'std']})
        
# 検証データ
test_result = \
    test_data.groupby([test_data[key_col].dt.year, test_data[key_col].dt.month]).agg({list_col[1] : ['count', 'mean', 'std']})
           


 groupby関数では「年」と「月」でグループ化しているため、複数年のデータで学習・検証を行なっても、月毎にデータが混ざることがありません。また「月」だけを指定して学習させれば、季節の傾向を学習・検証できるかもしれません。


 agg関数の引数では、具体的なカラム名ではなくカラムリストを使用しています。その理由は、関数内で特定のカラム名を指定することを避けたかったためです。


 しかし、同じ関数内で、カラムリストのインデックスは、1で固定してしまっているところが不完全な点です。まだまだ、改善の余地が多い関数です。


終了判定

 ループの終了条件は、以下の通りです。

# 終了判定(次回の検証データ終了位置が、指定範囲を超えている場合)
if test_end_point  > pd.to_datetime(vl_end) : 
            
    print('End of Cross_Validation.')
    break
        


 次回のループ時に、検証データの終了位置が、関数パラメータ「vl_end」を越えてしまう場合、学習・検証を終了させています。


 大まかですが、この関数の処理の流れについて説明しました。次に、この関数を実行した結果を確認します。


実行結果の確認

 はじめに「スライド式」の実行結果の一部を確認します。ループ1回目の結果は、以下の通りです。

f:id:Pimientito:20190714124215p:plain
検証1回目(スライド式)


 ループ2回目の結果を見ると、train_result、test_resultのそれぞれ「MultiIndex」の値が、ループ1回目の結果から2ヶ月分スライドしていることが確認できます。

f:id:Pimientito:20190714124305p:plain
検証2回目(スライド式)


 続いて「学習期間増加式」の実行結果の一部を確認します。

f:id:Pimientito:20190714124526p:plain
検証1回目(学習期間増加式)


 ループ2回目のtrain_resultの「MultiIndex」が、ループ1回目の4ヶ月分から、6ヶ月分に増えていることが確認できました。


 また「values」の結果もループ1回目より2回目の方が行数が増えていることも確認できます。

f:id:Pimientito:20190714124713p:plain
検証2回目(学習期間増加式)


 すべての結果を載せることはできませんが「スライド式」「学習期間増加式」共に、正常に動作していることが確認できました。


 関数の見直す点は、まだまだ多くありますが、今回の学習では、ここまでとします。


可視化に挑戦

 今回作成した自作関数「DateTypeData_Validation」の一部を変更して、可視化のためのデータを作成します。変更したコード部分のみ記載します。


 はじめに、結果の値を代入する変数train_resultとtest_resultをリスト型変数に変更します。

# リスト変数作成
train_result = []
test_result = []


 続いて学習・検証結果をリスト変数にappend関数で追加するようにコードを変更します。また複数の数値を同時に管理・操作することが、いまの筆者には、まだ難しいため、集約関数はstd(標準偏差)のみに絞りました。

# 検証
# 学習データ
train_result.append(train_data.groupby([train_data[key_col].dt.year, train_data[key_col].dt.month]).agg({list_col[1] : ['std']}).reset_index(drop=True))

# 検証データ
test_result.append(test_data.groupby([test_data[key_col].dt.year, test_data[key_col].dt.month]).agg({list_col[1] : ['std']}).reset_index(drop=True))        


 ループの終了判定の部分を、break文からreturn文に変更して、各結果が格納されているリスト変数train_resultとtest_resultを戻り値に設定します。

# 終了判定(次回の検証データ終了位置が、指定範囲を超えている場合)
if test_end_point  > pd.to_datetime(vl_end) : 
    print('End of Cross_Validation.')
    return train_result, test_result


 最後に、関数の戻り値を受ける変数を、関数呼び出し元に設定します。

# 関数の呼び出し
train_result, testDate = DateTypeData_Validation(tbl_lesson32, '20180101', '20181231', 4, 2, tbl_lesson32.columns, 'Date', True)


 変更したコードを実行した結果は、以下の通りです。(train_resultの場合)

f:id:Pimientito:20190714222700p:plain
学習結果(std関数)の確認(4回分)


 学習結果の数値をプロット図にするため、リスト型からNumPy型配列へ変換します。

# list型をNumPy型配列に変換
import numpy as np

np_train_result = \
    np.array([train_result[x].values for x in range(len(train_result))])

# 変換結果の確認
np_train_result


 変換した結果は、以下の通りです。

f:id:Pimientito:20190714223106p:plain
list型からNumPy型配列に変換した結果


 NumPy型二次元配列変数をインデックス指定で、値を表示させて確認します。(インデックス[0]と[1]のみ)

f:id:Pimientito:20190714223525p:plain
インデックス指定でデータを確認


 最後にプロット図を作成します。グラフは、折れ線グラフで表示させました。表示内容は、ダミー処理の標準偏差の値を使用しています。(表示内容に意味はありません。)

# 可視化ライブラリ
import matplotlib.pyplot as plt

# figureオブジェクトの初期化
fig = plt.figure()

# サブプロット図の関連付け
ax1=fig.add_subplot(2, 2, 1)
ax2=fig.add_subplot(2, 2, 2)
ax3=fig.add_subplot(2, 2, 3)
ax4=fig.add_subplot(2, 2, 4)

# train_resultの結果の関連付け
ax1.plot(np_train_result[0], 'go--')    # 緑色表示
ax2.plot(np_train_result[1], 'ro--')    # 赤色表示
ax3.plot(np_train_result[2], 'yo--')    # 黄色表示
ax4.plot(np_train_result[3], 'bo--')    # 青色表示

# サブプロット図ごとにタイトル表示
ax1.set_title('train_result[0]')
ax2.set_title('train_result[1]')
ax3.set_title('train_result[2]')
ax4.set_title('train_result[3]')

# サブプロット図周辺の空白調整
plt.subplots_adjust(wspace=0.3, hspace=0.5)

# プロット図表示
plt.show()


 表示したプロット図は、以下の通りです。

f:id:Pimientito:20190714224140p:plain
train_resultの結果をプロット図化


 今回のプロット図からは、何の推論も立たないですが、自身で作成した予測モデルを検証して行く過程を、ほんの少し体験できたような気分になれました。


今回のまとめ

 前回と今回の二回で、データの分割を学びました。学習に入る前に考えていたデータ分割とは意味が異なり、非常に戸惑うことが多かったように思います。


 第一に、予測モデルのための学習データ・検証データの分割については、教科書レベルでは知っていましたが、実際に予測モデルを作成したことがない筆者にとっては、データ分割後の処理の方が気になってしまい、いろいろと参考資料を漁ってみるものの、もう少し先で学ぶ内容が多く、結果的に、いままで学んだgroupby関数や集約関数を使ってダミー処理に落ち着くといった手戻りが多いテーマでした。


 また、データを分割するライブラリや関数は、Pythonには、まだ少ないようで、R言語で実現できる機能をコーディングする必要があり、そのためには、Pythonの仕様をより深く理解しなければならないことを痛感しました。


 本ブログを始めて一年が経過しましたが、「機械学習」を実務に活かすには、まだまだ何も理解しておらず、また何かを開発することもできない己の現実を知り、ため息つく日もありますが、まずは自分が選んだこの一冊「前処理大全」を読み切るまでは「機械学習」の学習を続けていきたいと思います。


 一年間、本当にありがとうございました。また、次の一年も、よろしくお願いいたします。



 今回は、以上です。



【参考資料】

国土交通省 気象庁「過去の気象データ・ダウンロード」

www.data.jma.go.jp



本ブログに関するお問い合わせ

 筆者自身の「機械学習」の学習のため、参考にさせていただいている資料や、Web情報に対して、情報元の権利を侵害しないよう、できる限り、細心の注意を払って、ブログの更新に努めておりますが、万が一、関係各所の方々に、不利益が生じる恐れがある場合、大変お手数ですが、下記の「お問い合わせ先」まで、ご一報いただけますようお願いいたします。



お問い合わせ先:otoiawase@handson-t-arte.com


【前処理の学習-31】データを学ぶ ~分割~①

 前回まで五回に渡り、データの「結合」について学びました。

pimientito-handson-ml.hatenablog.com

 今回からデータの「分割」について学びます。


【今回の目標到達点】

 交差検証(クロスバリデーション)のためのデータ分割を学ぶ


【目次】


参考資料のご紹介

 はじめに、現在、主に参考とさせていただいている書籍をご紹介します。

「前処理大全 データ分析のためのSQL/R/Python実践テクニック」本橋智光氏著(技術評論社)


「分割」の概要

 参考資料「前処理大全」第5章「分割」の冒頭で、著者はデータの分割について、以下のように述べています。

 データの分割は予測モデルを評価する際に必要になる前処理です。主に学習データ(予測モデルを構築する際に利用するデータ)と検証データ(モデルの精度を測定するためのデータ)の分割に利用されます。


 学習データと検証データは、必要とする列データは同じです。「予測モデルに入力するための列データ」と「予測モデルの予測対象の列データ」です。そのため、学習データと検証データに対して適用する前処理はすべて同じです。

参考・参照元:第5章「分割」(p.130)より抜粋


 資料によって、データの名称が異なるため、以下の表に代表的な名称をまとめました。

名称 使用場面
学習データ
訓練データ
レーニングデータ
訓練セット
Train Data
予測モデル構築時
検証データ
評価データ
テストデータ
テストセット
Test Data
予測モデル精度測定(評価)時


 このほかにも、本番の予測に使う「本番データ」や「適用データ」と呼ばれるものや、モデル構築後の性能評価のためだけに使用するデータなどもあります。


 筆者のような機械学習初学者にとっては、単なる名称だけでも迷ってしまいますが、以下のような例をイメージすると、理解しやすいかもしれません。

使用場面 高校野球のボールに例えると
モデル構築時
(特徴量設計)
放課後の部活用ボール
(使い込んでボロボロ)
モデル構築時
(モデルテスト)
練習試合用ボール
(たまに使うのできれい)
モデル検証
または本番
公式戦用ボール
(新品)


 少し変な例えですが、データの名称については、あまり神経質に捉える必要はなく、予測モデルが完成するまでに、自分自身が、どの位の「トライ&エラー」を行なうのか計算した上で、使用するデータを分割し、保存すれば良いのではと思います。


 今回、第5章「分割」では、データの分割について、以下、二つの方法が紹介されています。


  • レコードデータにおけるモデル検証用のデータ分割

  • 時系列データにおけるモデル検証用のデータ分割



 次の章から、具体的にデータの分割を学びます。


今回のテーマ

 レコードデータにおけるモデル検証用のデータ分割


概要

 参考資料 5-1「レコードデータにおけるモデル検証用のデータ分割」では、交差検証(クロスバリデーション)に使用するデータの分割方法について紹介されています。


 筆者は、まだ自身で「予測モデル」を作成した経験がありません。今回の学習をはじめる前に、機械学習の基本的なことについて、少し触れたいと思います。


モデル

 はじめに予測モデル・機械学習モデルの「モデル」とは、一体何を指しているのでしょうか。以下、引用します。

機械学習では、学習の結果得られた法則性を表すものを、一般的に「モデル」と呼びます。機械学習モデルは適用するアルゴリズムごとに異なり、それぞれ何らかの数式やデータ構造と、その中に含まれる変更可能なパラメータの値の集合で表現されます。

参考・参照元:データサイエンティスト養成読本 機械学習入門編(技術評論社) 第1部 特集1 「機械学習を使いたい人のための入門講座」(比戸将平氏著)第1章 機械学習の概要 「モデル」より抜粋


 モデルとは、値を入力することで、ある結果を導き出す(予測する/分類する)コードや、数式のアルゴリズムのことを指す言葉のようです。以下は、イメージ図です。


f:id:Pimientito:20190615182702j:plain
機械学習モデルイメージ図


モデルの性能評価

 モデルの性能評価の概要について調べてみました。以下、引用します。

学習に用いるデータ集合Dは観測データxとその正解ラベルyからなる.機械学習では、Dを使って未知の観測データのラベルを予測するシステム、アルゴリズムを学習するので、学習結果のシステム性能を評価する必要がある.これは、(1)種々のアルゴリズムを比較してどのアルゴリズムが優位であるかを示すため、(2)実データに適用したときどの程度の性能を示すか目処をつけるためである.

参考・参照元東京大学工学教程 情報工学 機械学習(中川裕志氏著 丸善出版) 1.5 「評価方法」(p.16)より抜粋


 「正解ラベル」による性能評価は「教師あり学習」「教師なし学習」「強化学習」の、すべてで使用できる性能評価なのでしょうか。残念ながら、もう少し先のお話しになってしまうため、今回は割愛します。


ホールドアウト法

 予測モデルの検証方法のひとつ。以下、引用します。  

ホールドアウト法は、機械学習のモデルの汎化性能を評価するために従来より使用されている一般的なアプローチである。

参考・参照元:達人データサイエンティストによる理論と実践 Python 機械学習プログラミング[第2版](インプレス) 6.2.1 ホールドアウト法(p.185)より抜粋


 従来のホールドアウト法は、元のデータセットをモデルの「トレーニング用」と「性能評価用」に分割しますが、性能評価用のデータでも繰り返し使用すれば、トレーニング用データと同様であるという考えから、同著「達人データサイエンティストによる理論と実践 Python 機械学習プログラミング[第2版]」では、以下、三つのデータに分ける方法を紹介しています。


データセット 使用場面
レーニングデータセット 様々なモデルの学習に使用
検証データセット モデル選択時に使用
テストデータセット 最終的な性能評価


 三つに分割されたデータを使用した場合のホールドアウト法の流れは、以下の通りです。

f:id:Pimientito:20190601122024j:plain
予測モデル構築時の分割データ使用場面


 予測モデル構築時に繰り返し使用するデータは「トレーニングデータ」と「検証データ」です。「テストデータ」を繰り返し使用し、入力値(ハイパーパラメータ)の調整を行なうことは、モデルの「過学習」に繋がります。


過学習(Over-learning)/過適合(Over-fitting)

 以下、引用します。

正解ラベル付きの観測データ集合Dから学習した回帰や分類の予測式がDに精度高くあてはまるが、一方で新規データの予測精度は必ずしもよくないという現象を過学習あるいは過適合と呼ぶ.

参考・参照元東京大学工学教程 情報工学 機械学習(中川裕志氏著 丸善出版) 4.1 「過学習」(p.69)より抜粋


 過学習と学習不足、または妥当な性能のモデルの評価例を、簡単な図にすると、以下のようになります。

f:id:Pimientito:20190607000133j:plain
決定境界による学習不足と過学習


交差検証(k分割交差検証)

 最も有名な予測モデルの検証方法。以下、引用します。

最もメジャーなモデルの検証方法は、交差検証(クロスバリデーション)です。交差検証では、データをいくつかに分割し、その分割した1つのデータ群をモデルの評価用のデータとして利用し、その他のデータ群でモデルの学習を行ないます。すべてのデータ群が一度だけ評価用のデータとして採用されるように、データ群の個数分繰り返して精度測定を行い、モデルを評価します。

参考・参照元:「前処理大全」5-1「レコードデータにおけるモデル検証用のデータ分割」(p.131)より抜粋


  交差検証(クロスバリデーション)の流れは、以下の通りです。

f:id:Pimientito:20190601215316j:plain
交差検証の流れ


 上図では、各交差検証の精度測定を「平均二乗誤差」で算出し、最後に、すべての交差検証の結果を合算して平均値を算出しています。


 しかし「交差検証」という方法では、すべて平均二乗誤差の平均を使ってモデルの精度測定を行なうのでしょうか。残念ながら、いまは、その答えが分かりません。今後、学習を続けるなかで学ぶ必要があります。


 なお、この図からは、ホールドアウト法の説明図にあった「テストデータ(最終性能評価用)」のようなものが存在しません。学習データと検証データを繰り返し使用することを考えると、最終性能評価用データを、訓練前に別途分割しておくと良いかもしれません。


 まだまだ機械学習の基礎知識のお話しは尽きませんが、学習を進めます。


サンプルコード[Python]

 今回のサンプルコード(python_awesome.py(p.136-138))では、ホールドアウト法と交差検証(クロスバリデーション)法を組み合わせています。処理の流れは、以下の通りです。


  1. ホールドアウト検証用関数で、トレーニングデータと、テストデータを分割

  2. レーニングデータを、交差検証(クロスバリデーション)用関数で、トレーニングデータと、検証データに分割



 サンプルコードを使用して、動作確認を行ないます。


サンプルデータセットを作成

 動作確認を行なう前に、サンプルデータセットを作成します。

#前処理大全 5-1「レコードデータにおけるモデル検証用のデータ分割」
#サンプルコード python_awesome.py(p.136-138)を参考に作成
#テストテーブル作成
import pandas as pd

tbl_lesson31 = \
pd.DataFrame({'name' : ['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z'], 
              'number' : range(1, 27), 
              'double' : [x * 2 for x in range(1, 27)], 
              'square' : [x**2 for x in range(1, 27)], 
              'cube' : [x**3 for x in range(1, 27)]})


 今回は、データ分割の動作確認を行ないやすいようにレコード数の少ないテーブルを作成しました。データの内容は、以下の通りです。

f:id:Pimientito:20190602224043p:plain
tbl_lesson31の内容


 各データの内容は、以下の通りです。

カラム名 内容 レコード件数
name 小文字アルファベット(a-z) 26
number 1-26までの数字 26
double 1-26の数字に2を乗算 26
square 1-26の数字の二乗 26
cube 1-26の数字の三乗 26


ホールドアウト法によるデータ分割

 train_test_split関数を使用してトレーニングデータ、テストデータに分割します。

 またトレーニングデータ、テストデータを、それぞれ説明変数と目的変数(応答変数)に分割します。

データ名 変数種別 カラム名 割合(%)
レーニングデータ 説明変数 name,
number,
double,
square
80
レーニングデータ 目的変数 cube 80
テストデータ 説明変数 name,
number,
double,
square
20
テストデータ 目的変数 cube 20


 説明変数・目的変数とは・・・


  • 説明変数とは、予測に使用する値

  • 目的変数とは、予測対象の値



 今回は、トレーニングデータを全体の80%、テストデータは全体の20%の割合で分割します。分割の割合は、test_sizeパラメータに小数点形式で指定します。

#sklearnライブラリ(ホールドアウト法)
from sklearn.model_selection import train_test_split

#ホールドアウト法でデータを分割
dt_training, dt_test, target_training, target_test = \
    train_test_split(tbl_lesson31.drop('cube', axis=1), tbl_lesson31[['cube']], test_size=0.2)

#分割後のデータのインデックスを振り直し
dt_training.reset_index(inplace=True, drop=True)
dt_test.reset_index(inplace=True, drop=True)
target_training.reset_index(inplace=True, drop=True)
target_test.reset_index(inplace=True, drop=True)


 train_test_split関数の概要は、以下の通りです。

f:id:Pimientito:20190602180643p:plain
train_test_split関数の説明


 分割したデータは、以下の通りです。(分割される行や順番は、分割する度に異なります)

f:id:Pimientito:20190602225847p:plain
レーニングデータ(説明変数、80%)


f:id:Pimientito:20190602225936p:plain
レーニングデータ(目的変数、80%)


f:id:Pimientito:20190602230024p:plain
テストデータ(説明変数、20%)


f:id:Pimientito:20190602230059p:plain
テストデータ(目的変数、20%)


交差検証(クロスバリデーション)法によるデータ分割

 次にホールドアウト法で分割したトレーニングデータ(元データの80%)を、交差検証(クロスバリデーション)で使用します。


 はじめにレコード件数をrange関数の範囲として利用し、行番号リストを作成します。

#sklearnライブラリ(クロスバリデーション)
from sklearn.model_selection import KFold

#80%に分割したトレーニングデータで交差検証を実施
#トレーニングデータのレコード件数を行番号リストとして使用
row_number = list(range(len(target_training)))


 実行結果は、以下の通りです。

f:id:Pimientito:20190602231811p:plain
行番号リストの表示


 次に、KFold関数を使用して、交差検証の初期設定を行ないます。

#交差検証用データの分割(=交差検証回数の指定)
# レコード20件を5分割(4レコード × 5回分(トレーニング:4回分(16レコード)、検証:1回分(4レコード)))
k_fold = KFold(n_splits=5, shuffle=True) 


 交差検証で分割するデータの内訳は、以下の通りです。

項目 数量
元データレコード件数 20
1データ分割内のレコード件数 4
データ分割数 5
学習回数 4
検証回数 1


 k_foldオブジェクトの設定内容は、以下の通りです。

f:id:Pimientito:20190602233941p:plain
k_foldオブジェクトの設定内容


 KFold関数のパラメータは、以下の通りです。

パラメータ名 初期値 概要
n_splits 3 データ分割数(学習データ+検証データ)
random_state None データシャッフル時の乱数値
shuffle False 分割前データのシャッフル


 KFold.split関数を使用して、データを分割します。

 (以下のサンプルコードでは、処理内容の見える化を行なうため、print()文を多く含んでいます)

#ループカウンター
counter = 1

#交差検証繰り返し処理(並列処理も可能な部分)
for train_crossval_no, test_crossval_no in k_fold.split(row_number):
    
    print('loop count : ' , counter)
    print('train_crossval_no : ', train_crossval_no)
    print('test_crossval_no : ', test_crossval_no)
    print('')
    
    #交差検証における学習データを抽出
    train_crossval = dt_training.iloc[train_crossval_no, : ]
    
    #データの表示(train_crossval)
    print('train_crossval : ', counter)
    print(train_crossval)
    print('')
    
    #交差検証における検証データを抽出 ※トレーニングデータから取得
    test_crossval = dt_training.iloc[test_crossval_no, : ]
    
    #データの表示(test_crossval)
    print('test_crossval : ', counter)
    print(test_crossval)
    print('')
    print('-------')
    
    #ループカウンター インクリメント
    counter +=1


 分割したデータを、以下の内容で表示しました。

 (5回分のfor文実行結果)

表示名 表示内容
loop count for文の回数
train_crossval_no レーニングデータとして
抽出された行番号
test_crossval_no 検証データとして
抽出された行番号
train_crossval:n n回目のトレーニングデータ
(説明変数)の内容
test_crossval:n n回目の検証データ
(説明変数)の内容


f:id:Pimientito:20190602235832p:plain
交差検証のためのデータ分割 1回目


f:id:Pimientito:20190602235925p:plain
交差検証のためのデータ分割 2回目


f:id:Pimientito:20190602235955p:plain
交差検証のためのデータ分割 3回目


f:id:Pimientito:20190603000023p:plain
交差検証のためのデータ分割 4回目


f:id:Pimientito:20190603000054p:plain
交差検証のためのデータ分割 5回目


 分割されるトレーニングデータ、または検証データの内容が、毎回異なることを確認しました。


 なお、このサンプルコードでは、トレーニングデータの「目的変数」と、テストデータの「説明変数」「目的変数」のデータを分割するコードは、割愛されています。


 よってFor文の中のコーディングは、本来は予測モデルの訓練および検証を行なうため、もう少し複雑な内容になります。


今回の学習

 「今回のテーマ」の内容を踏まえて、最初にテストデータの作成を行います。まず、どのようなデータが、今回の学習に適しているのか検討します。


テストデータの概要

条件

 以下の条件を満たしているデータが、今回の学習に適していると考えます。

  • すでに機械学習用データとして整備されているもの


テストデータの選択

 上記「条件」で挙げたように、今回の学習では、どのようなデータが機械学習に使用されているのかを学ぶため、事前に機械学習用データとして整備されているオープンデータを探しました。


 今回のデータは「the UC Irvine Machine Learning Repository」から「Car Evaluation Data Set」をダウンロードして使用しました。

f:id:Pimientito:20190530003550p:plain
UCI Machine Learning Repository ロゴマーク


f:id:Pimientito:20190530003917p:plain
「Car Evaluation Data Set」紹介ページ


テストデータの加工

 今回使用するオープンデータは、既に整備されているためデータをダウンロードし、Pythonで読み込めば、すぐに使用できます。(カラム名のレコードが含まれていないため、別途カラム名の定義は必要です)

# UCI (University of California, Irvine カリフォルニア大学アーバイン校)
# Machine Learning Repository
#「Car Evaluation Data Set」
# http://archive.ics.uci.edu/ml/datasets/Car+Evaluation

import pandas as pd

df = pd.read_csv('./data/lesson31/car.data', encoding='utf-8')
df.columns=['buying', 'maint', 'doors', 'persons', 'lug_boot', 'safety', 'class']


 読み込んだ結果は、以下の通りです。

f:id:Pimientito:20190530004952p:plain
「Car Evaluation Data Set」の内容


 データセットの概要は、以下の通りです。

カラム名 概要 カテゴリ 予測対象
buying buying price low,
med,
high,
vhigh(*1)
-
maint price of the maintenance low,
med,
high,
vhigh
-
doors number of doors 2,
3,
4,
5more
-
persons capacity in terms of
persons to carry
2,
4,
more
-
lug_boot the size of
luggage boot
small,
med,
big
-
safety estimated safety of
the car
low,
med,
high
-
class Class Values unacc(*2),
acc(*3)
good
vgood

参考・参照元UCI Machine Learning Repository 「Car Evaluation Data Set」- Data Set Information -より抜粋


(*1) very high(非常に高い)の略と推測。

(*2) unacceptable(許容できない、受入不可)の略と推測。

(*3) acceptable(許容できる、受入可)の略と推測。


カテゴリデータを数値化

 データセットの内容を確認したところ、ほとんどの値が文字列型でした。統計学でいうところの「質的データ」に該当するカテゴリデータには、以下の種類があります。

名称 概要
名義特徴量(*1) 色や物の名称など、それだけでは順序付けが不可能
順序特徴量 データの並び替えや順序・順位付けが可能

(*1) 特徴量とは、予測モデルへ入力する値。説明変数、パラメータなどとも呼ばれています。


 文字列型のカテゴリデータは、そのままでは予測モデルへ投入できないため、整数型の値に変換する必要があります。なお文字列型データと整数型データを関連付ける(変換する)処理を「マッピング」と呼びます。


 カテゴリデータのマッピングには、名義特徴量と順序特徴量と、それぞれに異なる手段があり、取り扱う特徴量ごとに細かく設定・処理を行ないますが、今回は、すべてのデータを順序特徴量としてマッピング処理を行ないます。

#参考・参照元:達人データサイエンティストによる理論と実践 Python 機械学習プログラミング[第2版](インプレス)
#第4章 データ前処理 - よりよいトレーニングセットの構築 - (p.105)を元に作成

import numpy as np
import pandas as pd

#ラベルと整数を関連付けたディクショナリを作成
buying_mapping = {'low':1, 'med':2, 'high':3, 'vhigh':4}
maint_mapping = {'low' : 1, 'med' : 2, 'high' : 3, 'vhigh' : 4}
doors_mapping = {'2' : 1, '3' : 2, '4' : 3, '5more' : 4}
persons_mapping = {'2' : 1, '4' : 2, 'more' : 3}
lug_boot_mapping = {'small' : 1, 'med' : 2, 'big' : 3}
safety_mapping = {'low' : 1, 'med' : 2, 'high' : 3}
class_mapping = {'unacc' : 1, 'acc' : 2, 'good' : 3, 'vgood' : 4}

#ラベルを整数に変換
df['buying'] = df['buying'].map(buying_mapping)
df['maint'] = df['maint'].map(maint_mapping)
df['doors'] = df['doors'].map(doors_mapping)
df['persons'] = df['persons'].map(persons_mapping)
df['lug_boot'] = df['lug_boot'].map(lug_boot_mapping)
df['safety'] = df['safety'].map(safety_mapping)
df['class'] = df['class'].map(class_mapping)

#データの確認
df.head()


 すべての特徴量を整数値に変換した結果は、以下の通りです。

f:id:Pimientito:20190609184531p:plain
カテゴリデータを整数値にマッピング


 整数化した特徴量を、文字列型のカテゴリデータへ再マッピングする場合は、以下の処理を行ないます。

#逆マッピング用(確認のために作成)
#逆マッピング用ディクショナリの作成
inv_buying_mapping = {v: k for k, v in buying_mapping.items()}
inv_maint_mapping = {v: k for k, v in maint_mapping.items()}
inv_doors_mapping = {v: k for k, v in doors_mapping.items()}
inv_persons_mapping = {v: k for k, v in persons_mapping.items()}
inv_lug_boot_mapping = {v: k for k, v in lug_boot_mapping.items()}
inv_safety_mapping = {v: k for k, v in safety_mapping.items()}
inv_class_mapping = {v: k for k, v in class_mapping.items()}

#ラベルをラベル名に再変換
df['buying'] = df['buying'].map(inv_buying_mapping)
df['maint'] = df['maint'].map(inv_maint_mapping)
df['doors'] = df['doors'].map(inv_doors_mapping)
df['persons'] = df['persons'].map(inv_persons_mapping)
df['lug_boot'] = df['lug_boot'].map(inv_lug_boot_mapping)
df['safety'] = df['safety'].map(inv_safety_mapping)
df['class'] = df['class'].map(inv_class_mapping)

#データの確認
df.head()


 再マッピングした結果は、以下の通りです。

f:id:Pimientito:20190609185238p:plain
文字列型カテゴリデータへ再マッピング


 ここでは、整数型の値にマッピングしたデータを使用します。


テストデータを使って学習

 ここからは「今回のテーマ」で取り上げた手順に則ってPythonで学習を進めます。

ホールドアウト法でデータを分割(その1)

 サンプルコードと同様にtrain_test_split関数を使ってデータを分割します。

#ホールドアウト法でのデータ分割(テストデータ20%確保)
train_data, test_data, train_target, test_target = \
    train_test_split(df.drop('class', axis=1), df[['class']], test_size=0.2)

#インデクスの採番
#データセット(説明変数)
train_data.reset_index(inplace=True, drop=True)
test_data.reset_index(inplace=True, drop=True)

#予測対象データセット(目的変数)
train_target.reset_index(inplace=True, drop=True)
test_target.reset_index(inplace=True, drop=True)


 データを分割した結果は、以下の通りです。

f:id:Pimientito:20190609231944p:plain
ホールドアウト法でデータを分割した結果


ホールドアウト法でデータを分割(その2)

 ここでは、その1の手順とは異なる指定方法で、データを分割する方法をご紹介します。

 その1の方法では、DataFrame型の分割データが出力されますが、その2の方法では、Numpy配列型で出力されます。

#分割方法 - その2 -
#①指定列データを抽出(X:説明変数、y:目的変数)
X, y = df.iloc[:, 0:6].values, df.iloc[:, 6].values

#②X, yを、それぞれ分割比率(20%)に沿ってデータを分割
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, stratify=y, random_state=0)


 出力結果は、以下の通りです。

f:id:Pimientito:20190609234622p:plain
ホールドアウト法でデータを分割した結果(その2)


 type関数を使って、それぞれの型を確認します。

f:id:Pimientito:20190609234833p:plain
分割データの型を確認


 なおNumpy型配列の場合、shape属性ではカラム数が表示されていません。データの内容を見比べてみます。

 DataFrame型の場合は、行/列がマトリックス(格子形状)で管理されていることが分かりました。

f:id:Pimientito:20190616182224p:plain
DataFrame型一次元配列の場合


 一方、Numpyの場合は、リスト型変数のように要素が連続して管理されています。

f:id:Pimientito:20190616183044p:plain
Numpy型一次配列の場合


 同じshape属性ですが、PandasライブラリとNumpyライブラリで仕様が異なるようです。仕様については、また別の機会で調べてみます。


 その1では、DataFrame型データの分割、その2では、Numpy型データの分割をご紹介しました。データの種類や状況によって、どのように使い分けるのか、今後学習を進めるなかで、理解を深めます。


k分割交差検証でデータを分割

 最後は、k分割交差検証を行ないます。はじめに行番号リストを作成します。

#対象行の行番号リストを生成
Row_Number = list(range(len(train_target)))


 作成したリストは、以下の通りです。

f:id:Pimientito:20190616220235p:plain
行番号リストの確認


 次にKFold関数のパラメータ値を設定します。

# 交差検証用データの分割
from sklearn.model_selection import KFold

#パラメータ値(分割:5, データシャッフル:有り)
k_fold = KFold(n_splits=5, shuffle=True)

#設定値の確認(k_fold)
k_fold


 KFold関数のパラメータ設定の結果は、以下の通りです。

f:id:Pimientito:20190616221417p:plain
KFold関数のパラメータ設定


 最後に、ループ文の中にデータの分割と予測モデルを検証するコードを記載します。(今回は、データの分割まで)

# k分割交差検証を実施
for train_cssvl_no, test_cssvl_no in k_fold.split(Row_Number):
    
    # 学習データを分割(説明変数、目的変数)
    train_cssvl_data = train_data.iloc[train_cssvl_no, : ]
    train_cssvl_target=train_target.iloc[train_cssvl_no, :]
    
    # 検証データを分割(説明変数、目的変数)
    test_cssvl_data = train_data.iloc[test_cssvl_no, : ]
    test_cssvl_target=train_target.iloc[test_cssvl_no, : ]


    #--------------

    # 性能評価対象のモデルを記載
    
    #--------------


 モデルの性能評価を行なう部分に、いろいろな書籍のサンプルコード(例:cross_val_score関数や、パイプラインを使用して変換器と推定器を結合など)を写経してみましたが、たくさんのエラーや、何の結果か分からない数値が表示されるなど、解説できない結果ばかりだったため、今回はデータを分割するところまでとします。


可視化に挑戦

 今回の学習では、データを分割したものの、実際に検証できる「予測モデル」が無いため、可視化するデータを取得することができませんでした。

 その代わりに、今回の可視化では、使用するデータの事前調査を行なう方法のひとつ「相関行列図」を、seabornライブラリを使ってデータを可視化します。


 相関行列図では、行と列が交差する特徴量同士の相関関係を見ることができます。

f:id:Pimientito:20190616232751j:plain
相関行列説明図


 相関行列図の対角線上を赤線で引かれているグレー部分は、行列共に同じ特徴量(項目)を掛け合わせているので、相関関係の確認対象外です。


 また赤線に対して垂直に交わる紺色の矢印線で結ばれているマス目(同系色のペア)は、行列の並びが反対の同一項目です。


 今回のテストデータ「Car Evaluation Data Set」を使用して相関行列図を作成します。このテストデータの目的変数「class」が、その他の説明変数と、どのような相関関係(一方に変化があれば、他方も影響を受ける関係)があるのか確認します。

# 達人データサイエンティストによる理論と実践
# Python 機械学習プログラミング(インプレス)
# 10.2.3 相関行列を使って関係を調べる(p.303-) サンプルコードを元に作成

import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns

cols = ['buying', 'maint', 'doors', 'persons', 'lug_boot', 'safety', 'class']
cm = np.corrcoef(df[cols].values.T)

hm = sns.heatmap(cm, 
                 cbar=True, 
                annot=True, 
                square=True, 
                fmt='.2f', 
                annot_kws={'size' : 10}, 
                yticklabels=cols,
                xticklabels=cols)

plt.tight_layout()
plt.show()


 データの相関関係を可視化した結果は、以下の通りです。

f:id:Pimientito:20190609185649p:plain
相関行列を使って関係を調べる


 この図で見る限りでは、目的変数「class」は、説明変数「safety」と「persons」からは、何らかの影響を受ける関係であることが分かりました。


今回のまとめ

 今回から、データの「分割」の章に入りました。

 いままでは、どちらかと言えば、データテーブルの操作などデータベース寄りのお話しが多く、何とか課題についていけていたのですが、この章では、一気に機械学習の要素が増え、なかなか前へ進むことができませんでした。


 「機械学習モデル」「予測モデル」という名前は知っていても、どのようにコーディングしていくものなのか、いまだに理解できておらず、今回の執筆中でも、何度かk分割交差検証のループ文に、参考資料等のサンプルコードを挿入してみました。

 実行結果に「accuracy:0.0xx, 0.0xxx......」と表示されるものの、筆者自身が、その結果を解釈できなかったため、今回の記事に盛り込むことは控えました。


  • 機械学習モデルって、どうやって作るの?

  • 開発者が意図した結果を出すモデルと、過学習モデルの境界は、どこにあるの?

  • 予測・推測するモデルと、分類するモデルは、入力値が、連続データか離散データかの違いだけなの?

  • データを入れただけなのに「accuracy」とは、何に対しての正確性なの?


 などなど。今回の執筆では、いままで以上に、機械学習に対しての疑問が浮かんできました。


 本ブログを開始して、間もなく一年になります。この一年で、筆者自身、機械学習を理解できるようになったのでしょうか。。。。次の一年では、もっともっと機械学習の中心に向かってアプローチしなければなりません。


 まだまだ、機械学習の学習は続きます。



 今回は、以上です。



【参考資料】

データサイエンティスト養成読本 機械学習入門編 (Software Design plus)

データサイエンティスト養成読本 機械学習入門編 (Software Design plus)


東京大学工学教程 情報工学 機械学習

東京大学工学教程 情報工学 機械学習


[第2版]Python 機械学習プログラミング 達人データサイエンティストによる理論と実践 (impress top gear)

[第2版]Python 機械学習プログラミング 達人データサイエンティストによる理論と実践 (impress top gear)



UCI(University of California, Irvine(カリフォルニア大学アーバイン校)) Machine Learning Repository

archive.ics.uci.edu


Machine Learning Repository「Car Evaluation Data Set」

archive.ics.uci.edu


本ブログに関するお問い合わせ

 筆者自身の「機械学習」の学習のため、参考にさせていただいている資料や、Web情報に対して、情報元の権利を侵害しないよう、できる限り、細心の注意を払って、ブログの更新に努めておりますが、万が一、関係各所の方々に、不利益が生じる恐れがある場合、大変お手数ですが、下記の「お問い合わせ先」まで、ご一報いただけますようお願いいたします。


お問い合わせ先:otoiawase@handson-t-arte.com


【前処理の学習-30】データを学ぶ ~結合~⑤

 前回は、Pythonによる過去データの結合について学びました。

pimientito-handson-ml.hatenablog.com

 今回は、すべてのデータを結び付ける「全結合」について学びます。


【今回の目標到達点】

 データテーブルを全結合する

【目次】


参考資料のご紹介

 はじめに、現在、主に参考とさせていただいている書籍をご紹介します。

 「前処理大全 データ分析のためのSQL/R/Python実践テクニック」本橋智光氏著(技術評論社)


「データ結合」の概要

 参考資料「前処理大全」の「第4章 結合」冒頭で、データの結合について、著者は以下のように述べています。

必要なデータが1つのテーブルにすべて入っていることはまれです。業務システムのデータベースは、データの種類ごとにテーブルが分かれているからです。一方、データ分析用のデータは1つのテーブルにまとまった横に長いデータが望ましく、そのようなデータを得るためにはテーブル同士を結合する処理が必要になります。

参考・参照元:第4章「結合」(p.084)より抜粋


 その上で、データの結合について、以下、3つの考え方をご紹介されています。

  • マスタテーブルから情報を取得

  • 条件に応じて結合するマスタテーブルを切り替え

  • 過去データから情報を取得


今回のテーマ

 テーブルの全結合

概要

 参考資料「前処理大全」の「4-4 全結合」の冒頭で、著者は全結合の必要性について述べています。


 例えば、結合するテーブルの一方に、データが存在しない場合、テーブル結合時に「存在しないレコード」が「存在する」ことに気付く事ができません。


 そのような状況を回避するためには、テーブルを全結合し、すべての組み合わせを生成することで「存在しないレコード」の存在も認識できるようになります。


 次の項よりサンプルコードを使って、実際に動作確認を行ないます。


サンプルコード[Python]

 Pythonでは、テーブルの全結合を可能とする関数が提供されていないため、参考資料のサンプルコードでは、テーブルの全結合を実現するためのコーディングが紹介されています。


 しかしサンプルコードから汎用的な「構文」だけを抜き出すことが難しいため、サンプルコードの動作確認を行ないながら、学習を進めます。本ブログのコードは、参考資料の「python_awesome.py」(抜粋)(p.127)を参考に再作成しています。


テーブル作成

 使用するサンプルテーブルは、ひとつひとつの処理を理解しやすいように、規模の小さいものを作成しました。

#サンプルコード[Python]
#前処理大全 4-4 全結合 python_awesome.py(抜粋)(p.127)を参考に作成 

import pandas as pd

# 日付型用ライブラリ
from datetime import date as dt

#日付の計算用ライブラリ
from dateutil.relativedelta import relativedelta

#年月テーブルの作成
tbl_month = pd.DataFrame({
    'year_month' : [(dt(2019, 1, 1) + relativedelta(months=x)).strftime("%Y%m")
                   for x in range(0, 3)]
})

#顧客テーブルの作成
tbl_customer = pd.DataFrame({
   'customer_id' : ['c_01', 'c_02', 'c_03'],
    'name' : ['ABC corp.', 'DEF, Inc.', 'PQR Ltd.'],
    'location' : ['東京', '大阪', '福岡' ]
})

#売上テーブル
tbl_sales = pd.DataFrame({
    'customer_id' : ['c_01', 'c_01', 'c_01', 'c_01', 'c_01', 'c_01', 
                                'c_02', 'c_02', 'c_02', 'c_02', 'c_03', 'c_03', 'c_03'],
    'sales_date' : [dt(2019,1,10), dt(2019, 1, 25), dt(2019, 2, 8), dt(2019,2,22), 
                            dt(2019, 3, 8), dt(2019, 3, 20), dt(2019, 2, 5), dt(2019,2,15), 
                            dt(2019, 3, 5), dt(2019, 3, 20), dt(2019,1,15), dt(2019, 1, 25), dt(2019, 2, 12)],
    'proceeds' : [2000, 2500, 1800, 5000, 3800, 10000, 
                          2800, 1500, 3300, 5700, 4800, 12000, 1200]
    
})


 各テーブルの概要は、以下の通りです。

【tbl_month(年月テーブル)】顧客テーブルと売上テーブルを結合するための中間テーブル

カラム名 型名 概要
year_month 日付 YYYYMM形式


【tbl_customer(顧客テーブル)】

カラム名 型名 概要
customer_id 文字列 c_01〜c_03
name 文字列 顧客名
location 文字列 都道府県名


【tbl_sales(売上テーブル)】

カラム名 型名 概要
customer_id 文字列 c_01〜c_03
sales_date 日付 YYYYMMDD形式
proceeds 整数 売上金


 各テーブルのデータは、以下の通りです。

【tbl_month(年月テーブル)】

f:id:Pimientito:20190503231137p:plain
tbl_month(年月テーブル)


【tbl_customer(顧客テーブル)】

f:id:Pimientito:20190503231328p:plain
tbl_customer(顧客テーブル)


【tbl_sales(売上テーブル)】

f:id:Pimientito:20190503231356p:plain
tbl_sales(売上テーブル)


 ここで注目したいのは、以下の部分です。連続した日付型の配列要素を、relativedelta関数を使用して作成しています。

#日付の計算用ライブラリ
from dateutil.relativedelta import relativedelta

#年月テーブルの作成
tbl_month = pd.DataFrame({
    'year_month' : [(dt(2019, 1, 1) + relativedelta(months=x)).strftime("%Y%m")
                   for x in range(0, 3)]
})


 この部分で行なわれている処理を、以下にまとめました。

f:id:Pimientito:20190504010643p:plain
tbl_month(年月テーブル)が生成される処理


 relativedelta関数の詳細は、dateutilドキュメント「relativedelta」をご覧ください。

dateutil.readthedocs.io


結合キー(カラム)の作成

 次に、tbl_month(年月テーブル)とtbl_customer(顧客テーブル)の結合キー(カラム)を作成します。

#テーブルに結合キーを持つカラムを作成
tbl_month['j_key'] = 0
tbl_customer['j_key'] = 0


 新規カラム「j_key」を作成した結果は、以下の通りです。

f:id:Pimientito:20190504141549p:plain
tbl_month(年月テーブル)の結合キー作成


f:id:Pimientito:20190504141734p:plain
tbl_customer(顧客テーブル)の結合キー作成


テーブルの全結合

 pandasのmerge関数を使用して、tbl_month(年月テーブル)とtbl_customer(顧客テーブル)を結合します。結合キーは、上記で作成した「j_key」です。結合した結果は、顧客テーブルに集約します。

#tbl_monthとtbl_customerを全結合
tbl_customer = pd.merge(tbl_customer[['customer_id', 'j_key']], tbl_month, on='j_key')


 結合した結果は、以下の通りです。

f:id:Pimientito:20190504143227p:plain
tbl_month(年月テーブル)とtbl_customer(顧客テーブル)を結合した結果


 顧客ID 1つに対して、年月(2019.01〜2019.03)の3ヶ月を結合して、合計9レコードが作成されました。


売上テーブルに結合キーを作成

 売上テーブルの売上日付「sales_date」を元に、結合キーを作成します。結合キーの作成とテーブル結合までの流れは、以下の通りです。



  1. 売上テーブルのカラム「sales_date」(%Y-%m-%d形式)を元に、結合キーのカラム「year_month」(%Y%d形式)を作成

  2. 売上テーブルのカラム「customer_id, year_month」と顧客テーブルの「customer_id, year_month」を結合キーとして、テーブルを結合



 売上テーブルに結合キーを作成するコードは、以下の通りです。

#tbl_salesのカラム「sales_date」を"年"、"月"の形式に変換して新規カラムへ格納
tbl_sales['year_month'] = tbl_sales['sales_date'].apply(lambda x : pd.to_datetime(x, format='%Y-%m-%d').strftime("%Y%m"))


 結合キーを作成した結果は、以下の通りです。

f:id:Pimientito:20190504163035p:plain
結合キー「year_month」作成結果


 この部分で行なわれている処理を、以下にまとめました。

f:id:Pimientito:20190504165851p:plain
結合キー「year_month」作成処理


顧客テーブルと売上テーブルの結合

 最後に、顧客テーブルと売上テーブルを、カラム「customer_id」と「year_month」を、複合キーにして結合します。


 参考資料では、テーブル結合後、sum関数で集計していますが、ここではagg関数を使用して、各顧客IDの「売上金額の合計」と「売上レコード数」を、月ごとに集計しています。

#tbl_customerとtbl_salesを結合,
result = pd.merge( tbl_customer, tbl_sales[['customer_id', 'year_month', 'proceeds']], \
                  on=['customer_id', 'year_month'], how='left').groupby(['customer_id', 'year_month']).agg({'proceeds' : ['sum', 'count']}).reset_index()

#カラム名の再設定
result.columns=['customer_id', 'year_month', 'total_preceeds', 'sales_count']


 結果は、以下の通りです。

f:id:Pimientito:20190504172543p:plain
テーブルの全結合・データの集約の結果


 なお、結果の値が'NaN'の場合、以下のコードを実行します。

result.fillna(0, inplace=True)


 以上が、Pythonで行なうテーブルの全結合でした。


今回の学習

 「今回のテーマ」の内容を踏まえて、最初にテストデータの作成を行います。まず、どのようなデータが、今回の学習に適しているのか検討します。


テストデータの概要

条件

 以下の条件を満たしているデータが、今回の学習に適していると考えます。

  • 複数のテーブルに分かれているデータであること。


テストデータの選択

 上記「条件」の内容をもとに、今回は「東京都 オープンデータ カタログサイト」より「都内鉄道駅におけるだれでもトイレバリアフリー設備情報」を使用して、テストデータを作成します。

f:id:Pimientito:20190506011147p:plain
「東京都 オープンデータ カタログサイト」トップページ


テストデータの加工(前処理の前処理)

 今回使用するオープンデータは、都内鉄道駅に設置されているバリアフリー設備(主に“だれでもトイレ”を指します)の設置状況を記録したものです。


 はじめにダウンロードしたCSVファイルを読み込みます。

#データの読み込み
#東京都 オープンデータ カタログサイト
#http://opendata-portal.metro.tokyo.jp/www/index.html

#だれでもトイレのバリアフリー情報(平成31年1月時点)
#都内鉄道駅におけるだれでもトイレのバリアフリー設備情報
#http://opendata-catalogue.metro.tokyo.jp/dataset/t000010d0000000062/resource/a03fbc68-76c6-4563-ac5e-c1cbe06b4a8e

import pandas as pd

df = pd.read_csv('./data/lesson30/tonaitetsudoueki_barrier-free-wc.csv', encoding='shift_jis')


 データの内容は、以下の通りです。

カラム名 カラム名
管理者種別番号 便座に手すりがある
鉄道駅通し番号 オストメイト用設備がある
鉄道駅内トイレ
通し番号
オストメイト用設備が
温水対応している
鉄道会社名 大型ベッドを備えている
路線名 乳幼児用おむつ交換台等を
備えている
鉄道駅名 乳幼児用椅子を備えている
都道府県 非常用呼び出しボタンを
設置している
市区町村・番地 月曜日
ビル建物名 火曜日
トイレ名 水曜日
設置フロア 木曜日
経度 金曜日
緯度 土曜日
座標系 日曜日
性別の分け 祝日
トイレへの誘導路として点字ブロック
敷設している
その他
トイレの位置等を音声で
案内している
写真データ(トイレの入り口)
戸の形式 写真データ(トイレ内)
車椅子が出入りできる
(出入口の有効幅員80cm以上)
写真データ(トイレ内(別角度))
車椅子が転回できる
(直径150cm以上の円が内接できる)
備考
便座に背もたれがある


 とてもユニークな名称のカラム名が多い印象です。カラム名が、ほぼ「文章」となっているものの大半は「○」か「×」の値が入っています。


 カラム「月曜日」〜「日曜日」と「祝日」には、“始発_終車”の文字列が入っており、これは駅の営業中(終日)は、対象のバリアフリー設備が利用可能であることを意味するのでしょう。


 今回は、このデータセットを使用して「山手線各駅のバリアフリー設備(だれでもトイレ)の設置状況」を確認します。


全結合用補助テーブルの作成

 「今回のテーマ」である“テーブルの全結合”を実現するため、オープンデータの他に2つのデータテーブルを作成します。


  • 山手線駅名テーブル

  • トイレ種別名テーブル


 山手線駅名テーブルは、単に山手線駅名のみを並べたテーブルです。テーブル作成のコードは、以下の通りです。

#「山手線駅名」テーブルの作成
tbl_YMTST = pd.DataFrame({
    '鉄道駅名' : ['大崎', '五反田', '目黒', '恵比寿', '渋谷', 
        '原宿', '代々木', '新宿', '新大久保', '高田馬場', 
        '目白', '池袋', '大塚', '巣鴨', '駒込', '田端', 
        '西日暮里', '日暮里', '鶯谷', '上野', '御徒町', 
        '秋葉原', '神田', '東京', '有楽町', '新橋', '浜松町', 
        '田町', '品川']
}) 


 作成したテーブルを確認した結果は、以下の通りです。

f:id:Pimientito:20190511223710p:plain
「山手線駅名」テーブル


 次に、トイレ種別名テーブルを作成しますが、その前に、今回のオープンデータには、どのようなトイレ名が存在するのか確認します。drop_duplicates関数を使用して、一意のトイレ名を表示します。

df['トイレ名'].drop_duplicates()


 表示結果は、以下の通りです。

f:id:Pimientito:20190511224429p:plain
トイレ名の表示


 トイレ名を確認すると、似通った名称が多いことが分かりました。そこで、トイレ種別名テーブルでは「だれでもトイレ」「多機能トイレ」「多目的トイレ」の三種類のみ使用することにしました。

#「トイレ種別名」テーブルの作成
tbl_TW_type = pd.DataFrame({
    'トイレ名' : ['だれでもトイレ', '多機能トイレ', '多目的トイレ']
    
})


 作成したテーブルを確認した結果は、以下の通りです。

f:id:Pimientito:20190511225322p:plain
「トイレ種別名」テーブル


 最後に、オープンデータ「都内鉄道駅におけるだれでもトイレバリアフリー設備情報」の前処理を行ないます。


必要なカラムの抽出

 データセットのカラム数は41個あるため、その中から必要なカラムだけ抽出します。今回の学習で使用するカラムは、以下の通りです。

カラム名
鉄道会社名
路線名
鉄道駅名
トイレ名


 カラムの抽出には、loc関数を使用します。

#カラムの抽出
tbl_STWC = df.loc[ : , ['鉄道会社名', '路線名', '鉄道駅名', 'トイレ名']].reset_index(drop=True)


 抽出したカラムの結果は、以下の通りです。

f:id:Pimientito:20190511231527p:plain
カラムの抽出結果


各トイレ名の件数を確認

 作成した「トイレ種別名テーブル」では、「だれでもトイレ」「多機能トイレ」「多目的トイレ」の三種類のトイレ名しかありませんが、オープンデータ側の「トイレ名」は類似名が複数あるため「言葉の揺らぎ」をトイレ種別名テーブルに合わせる必要があります。


 まずオープンデータのトイレ名の種類ごとに件数を確認します。

#トイレ名の種類を確認
tbl_STWC.groupby('トイレ名').count().reset_index()


 確認した結果は、以下の通りです。

f:id:Pimientito:20190511233729p:plain
トイレ名種別ごとの件数


 大まかに四種類(緑、黄、赤、青枠)に分けることができます。それぞれ類似する名称を統一します。


言葉の揺らぎを修正

 言葉の揺らぎは、replace関数を使用して修正します。最初にトイレ名の中に、キーワード「多機能」を含んだトイレ名を、すべて「多機能トイレ」に修正しました。

#言葉の揺らぎを修正(多機能〜)
tbl_STWC['トイレ名'].replace(['多機能お手洗', '多機能お手洗い', '多機能お手洗い(共用)(ヒカリエトイレ)', \
                        '多機能お手洗い(共用)(道玄坂トイレ)', '多機能お手洗い(女性用)(ヒカリエトイレ)', \
                        '多機能お手洗い(女性用)(宮益坂トイレ)', '多機能お手洗い(男性用)(宮益坂トイレ)', \
                        '多機能トイレ女子トイレ', '多機能トイレ男子トイレ', '多機能化粧室', '男女共用多機能トイレ'], '多機能トイレ', inplace=True)


 次にトイレ名に、キーワード「多目的」を含んだトイレ名を、すべて「多目的トイレ」に修正します。

#言葉の揺らぎを修正(多目的〜)
tbl_STWC['トイレ名'].replace(['多目的トイレ(共用)(ラウンジトイレ)', '多目的(だれでも)トイレ'], '多目的トイレ', inplace=True)


 残りは、トイレ名の種類を確認したときに、その名称の中に「だれでも〜」「多目的〜」「多機能〜」というキーワードを含んでいなかったトイレ名(図「トイレ名種別ごとの件数」の黄枠部分)について対応します。


 少々、乱暴ですが、今回は黄枠のトイレ名は「多機能トイレ」と「だれでもトイレ」に分類しました。対象行を削除するという考えもありますが、その分、トイレの設置件数が減ってしまうため、対象行の削除は行ないませんでした。


#言葉の揺らぎを修正(化粧室女子用、化粧室男子用)
tbl_STWC['トイレ名'].replace(['化粧室女子用', '化粧室男子用'], '多機能トイレ', inplace=True)


#言葉の揺らぎを修正(ファミリートイレ)
tbl_STWC['トイレ名'].replace(['ファミリートイレ'], 'だれでもトイレ', inplace=True)


 トイレ名の揺らぎを修正した結果は、以下の通りです。

f:id:Pimientito:20190512000510p:plain
言葉の揺らぎ(トイレ名)修正後の結果


 次は駅名の揺らぎを修正します。はじめに鉄道会社を、東日本旅客鉄道(JR東日本)に絞り込みます。

#鉄道会社名が「東日本旅客鉄道(JR東日本)」を絞り込み    
tbl_STWC_JR = tbl_STWC.loc[tbl_STWC['鉄道会社名']=='東日本旅客鉄道', ['鉄道会社名', '路線名', '鉄道駅名', 'トイレ名']]


 JR東日本の情報のみに絞り込んだ結果は、以下の通りです。

f:id:Pimientito:20190512163010p:plain
東日本旅客鉄道(JR東日本)に絞り込んだ結果


 鉄道会社で絞り込んだ結果を見ると、鉄道駅名「東京駅」が、複数の名称で存在することが分かりました。


 今回のデータセットでは、路線名にキーワード「山手線」を含んでいない山手線の駅が、複数散見することにより、路線名を結合キーとして利用できません。その代わりに鉄道駅名を結合キーとして使用します。そのために鉄道駅名から、言葉の揺らぎを修正する必要があります。


#言葉の揺らぎを修正(東京〜)
tbl_STWC_JR['鉄道駅名'].replace(['東京(地下)', '東京(京葉地下)'], '東京', inplace=True)


 東京駅から言葉の揺らぎを修正した結果は、以下の通りです。

f:id:Pimientito:20190512164420p:plain
言葉の揺らぎ(鉄道駅名)修正後の結果


 余談ですが、Jupyter Notebookでデータを表示する時、レコード数が多いと途中のデータの表示が省略されてしまいます。そのような場合は、values属性を指定すると、array型配列として文字列が画面に表示されます。この方法を使って、他の駅名に揺らぎが無いか確認します。

#鉄道駅名の確認
tbl_STWC_JR.values


 確認した結果は、以下の通りです。

f:id:Pimientito:20190512164927p:plain
values属性を指定してデータを確認


 東京駅以外の駅名では、言葉の揺らぎが無いことを確認しました。これで、すべての前処理が完了しました。作成したテーブルを使用して、今回の学習を行ないます。


テストデータを使って学習

 ここからは「今回のテーマ」で取り上げた手順に則って、Pythonで学習を進めます。

結合キーのカラムを新規作成

 「山手線駅名」テーブルと「トイレ種別名」テーブルを全結合するための結合キー(カラム)を新規作成します。

#各テーブルに結合用キーのカラムを作成
tbl_YMTST['j_key'] = 1
tbl_TW_type['j_key'] = 1


 結合キーのカラムを作成した結果は、以下の通りです。

f:id:Pimientito:20190512171050p:plain
結合キー(カラム)の作成結果


 前項「サンプルコード[Python]」では「j_key」の値を"0"に指定していましたが、今回は"1"を指定しています。二つのテーブルの値が同じであれば良いだけで、値の内容は何でも構いません。


テーブルの全結合

 作成した結合キーを元に「鉄道駅名」テーブルと「トイレ種別名」テーブルを全結合します。

#鉄道駅名テーブルとトイレ種別名テーブルを全結合
tbl_YMTST = pd.merge(tbl_YMTST[['鉄道駅名', 'j_key']], tbl_TW_type, on='j_key')


 テーブルを全結合した結果は、以下の通りです。

f:id:Pimientito:20190512172401p:plain
テーブルを全結合した結果


 山手線駅(29駅) × トイレ名(3種) 合計87パターンのレコードが作成されました。


 ここにオープンデータの情報を「鉄道駅名」と「トイレ名」を複合キーにして結合します。なおトイレ設置件数は、レコード件数(j_key)をカウントすることで算出しました。

#バリアフリー設備設置状況を全結合
result = pd.merge(tbl_YMTST, tbl_STWC_JR[['鉄道駅名', 'トイレ名']], on=['鉄道駅名', 'トイレ名'], how='left').groupby(['鉄道駅名', 'トイレ名'])['j_key'].count().reset_index()

#カラム名再設定
result.columns=['鉄道駅名', 'トイレ名', '設置件数']


 オープンデータの情報を関連付けした結果は、以下の通りです。

f:id:Pimientito:20190512173805p:plain
オープンデータの情報を結合した結果


 結合した結果が、少々見づらいため、pivot_table関数を使用して結果を表示します。

#pivot_table関数を使用して結合結果を表示
tips = result.pivot_table('設置件数', index=['鉄道駅名', 'トイレ名'])


 ピボットテーブルで表示した結果は、以下の通りです。

f:id:Pimientito:20190512174325p:plain
ピボットテーブルの表示結果


 今回使用したオープンデータからは、0件データ(駅構内にバリアフリー化されたトイレが存在しない)の確認はできませんでした。(山手線でバリアフリー化されていない方があり得ないかもしれません)


 テーブルの全結合によって、データの全組み合わせを確認できることは、データの傾向を把握するためには、とても有効ですが、反面、処理速度や、リソースの使用率にも大きく影響するため、結合する範囲の絞り込みには、十分気を付ける必要があります。


可視化に挑戦

可視化の目的

 すべてのデータを結合した情報をピボットテーブルに置き換えて表示しましたが、全体(山手線全29駅)を相対的に比較するには、少し難しいため可視化を行ないます。


 今回は、横軸に鉄道駅名、縦軸に設置件数(正確にはレコード件数)を設定して、トイレ種別ごとの棒グラフで可視化しました。


 残念ながら、筆者のスキル不足で、ピボットテーブルを、そのまま可視化することができなかったため、今回は、Seabornライブラリを使用して可視化を行ないました。


[seaborn]

 今回作成したコードは、以下の通りです。

#可視化ライブラリ
import seaborn as sns
import matplotlib.pyplot as plt

#日本語化ライブラリ
import japanize_matplotlib

#グラフ範囲の設定
fig = plt. subplots(figsize = (25, 10)) 

sns.barplot(x='鉄道駅名', y='設置件数', hue='トイレ名', data=result)

#グリッド線の表示
plt.grid()


 プロット図は、以下の通りです。

f:id:Pimientito:20190512180227p:plain
山手線各駅に設置されているバリアフリー設備の件数


 プロット図を確認すると、東京駅の設置件数が圧倒的に多いですが、データの前処理の項で述べましたが、今回のデータでは、路線名で「山手線」を特定することができなかったため、「鉄道駅名」でデータを抽出した結果、複数路線(中央線や京葉線など多数)の情報が入ってしまったために起きた結果です。


 また山手線各駅で「だれでも〜」「多機能〜」「多目的〜」が、最低でも一件ずつカウントされていることも、この結果の信憑性は低いと考えます。(各駅で、最低でも3箇所の設備があり、それぞれ別名で登録されているとは考えづらい)


 使用するデータの整合性確認や、厳密な前処理など、もっと学ぶ必要があります。


今回のまとめ

 全5回に渡って「結合」を学びました。テーブルの結合により、分析対象のデータの内容が多彩になる反面、データ量が爆発的に増加してしまう危険性についても同時に学びました。


 本ブログで学習している範囲では、処理速度が著しく遅くなったり、メモリなどのリソースが枯渇してエラーになっても、特段問題にもなりませんが、もし実務の場で、そのようなことが起きれば、システムインシデントに繋がる恐れもあるでしょう。


 ついつい分析に夢中になる余りに、思わぬ「落とし穴」に落ちないよう、常に「スモール・スタート」を心掛けなければいけないと肝に命じるばかりです。


 今回で「結合」編は、一旦完了となります。次回から「第5章 分割」を学習します。



 今回は、以上です。



【参考資料】

東京都 オープンデータ カタログサイト「都内鉄道駅におけるだれでもトイレバリアフリー設備情報」

opendata-catalogue.metro.tokyo.jp


UnicodeDecodeError: 'utf-8' codec can't decode byte

stackoverflow.com


Qiita「pip install して import するだけで matplotlib を日本語表示対応させる」@uehara1414氏

qiita.com



本ブログに関するお問い合わせ

 筆者自身の「機械学習」の学習のため、参考にさせていただいている資料や、Web情報に対して、情報元の権利を侵害しないよう、できる限り、細心の注意を払って、ブログの更新に努めておりますが、万が一、関係各所の方々に、不利益が生じる恐れがある場合、大変お手数ですが、下記の「お問い合わせ先」まで、ご一報いただけますようお願いいたします。


お問い合わせ先:otoiawase@handson-t-arte.com

【前処理の学習-29】データを学ぶ ~結合~④

 前回は、過去データの結合について学びました。

pimientito-handson-ml.hatenablog.com

 今回は、前回のテーマをPythonコードで学習します。そのため学習内容が前回【前処理の学習-28】と、一部重複しているところもあります。


【今回の目標到達点】

 時系列データを利用して、過去データを結合する(Pythonで学習)

【目次】

参考資料のご紹介

 はじめに、現在、主に参考とさせていただいている書籍をご紹介します。

「前処理大全 データ分析のためのSQL/R/Python実践テクニック」本橋智光氏著(技術評論社)


「データ結合」の概要

 参考資料「前処理大全」の「第4章 結合」冒頭で、データの結合について、著者は、以下のように述べています。

必要なデータが1つのテーブルにすべて入っていることはまれです。業務システムのデータベースは、データの種類ごとにテーブルが分かれているからです。一方、データ分析用のデータは1つのテーブルにまとまった横に長いデータが望ましく、そのようなデータを得るためにはテーブル同士を結合する処理が必要になります。

参考・参照元:第4章「結合」(p.084)より抜粋


 その上で、データの結合について、以下、3つの考え方をご紹介されています。

  • マスタテーブルから情報を取得

  • 条件に応じて結合するマスタテーブルを切り替え

  • 過去データから情報を取得


今回のテーマ

過去データから情報を取得

概要

 参考資料「前処理大全」の「4-3 過去データの結合」の冒頭で、著者は「過去データ」を活用することは、基礎分析や、予測モデルを構築する上で、有用であると述べています。以下、該当の文章を引用します。

基礎分析をするにしても、予測モデルを構築するにしても、過去データを活用することは有用です。筆者は「過去データの前処理を制するものはデータの前処理を制す」ぐらい重要だと考えています。

参考・参照元:第4章「4-3 過去データの結合」(p.103)より抜粋


 反面、時系列データの取り扱いは難しく、処理によっては、意図しない変換や、リーク(予測モデルに使用するデータに、未来のデータが混入すること)が伴うことがあるため、十分な注意が必要とも述べています。


 なお、時系列データを無作為に結合することにより、データ数が大量に増えてしまう危険性についても触れており、以下、二点の対策を利用することを、推奨されています。

1.結合対象とする過去の期間を絞る

2.結合した過去データに集約関数を利用して、データ数を増やさないようにする

参考・参照元:第4章「4-3 過去データの結合」(p.104)より抜粋


 具体的には「過去データ」を扱う際には、不必要に長い期間を指定するのではなく、分析に必要な期間に限定することや、過去データの結合時に、集約関数を使用して、データをまとめることで、結合によるデータ増加(レコード増加)を、抑止することができるようです。


サンプルコード[Python]

 参考資料「4-3 過去データの結合」で紹介されているサンプルコードの内、行または行数を指定して、データを取得する処理を学習します。


  • 行を指定してデータを取得

  • 行数を指定してデータを取得



n件前のデータ取得

 ある行を指定して、過去データを取得する構文は、以下の通りです。なおPythonには、LAG関数が提供されていないため、shift関数を利用して行移動を行う方法を、参考資料では紹介されています。

#① レコードセットをグループ化し、各グループごとに時系列でソートします。
result = data_table.groupby(grouping_column).apply(lambda group:group.sort_values(by=datetime_column, axis=0, inplace=False))


#② shift関数で指定した行位置のデータを取得して、新しいカラムへ値を代入
result[new_column] = pd.Series(result[target_column].shift(periods=n))

参考・参照元:「python_awesome.py」(抜粋)(p.107)を参考に作成


【補足】

項目 概要
result データセットから取得した結果
data_table データセット(データの源泉)
grouping_column グループ化対象カラム
datetime_column 並び替え対象日付型カラム
new_column 新規作成カラム
target_column 取得対象カラム
n shift関数の引数


 参考資料では、sort_values関数について、以下の補足説明がされています。

sort_values関数は引数のbyに指定された行/列名によって、データ行/列の並び替えを行います。axisが0の場合は、指定された列名の値によって行を並び替えます。axisが1の場合は、指定された行名の値によって列を並び替えます。

参考・参照元:第4章「4-3 過去データの結合」(p.108)より抜粋


 もう少し詳細に動作の違いを確認します。なおDataFrame型のsort_values関数には「by」パラメータが存在しますが、Series型のsort_values関数には存在しないようです。各々のパラメータの詳細については、pandasのドキュメントをご覧ください。


【パラメータ「axis」と「by」の組み合わせ】

axis by 並び替え方向
0 列名 行方向
1 行名 列方向


 pandasドキュメントのサンプルコードを参考に、動作確認を行ないます。

import pandas as pd

df = pd.DataFrame({
    'col1' : ['A', 'B', 'C', 'D', 'E', 'F'],
    'col2' : ['F', 'E', 'D', 'C', 'B', 'A'],
    'col3' : [0, 1, 2, 3, 4, 5],
    'col4' : [5, 4, 3, 2, 1, 0],
    'col5' :['あ', 'い', 'う', 'え', 'お', 'か'],
    'col6' :['か', 'お', 'え', 'う', 'い', 'あ'],
})

参考・参照元:pandas 0.24.2 documentation「pandas.DataFrame.sort_values」のサンプルコードを参考にコードを再作成


 サンプルデータテーブルの内容は、以下の通りです。

f:id:Pimientito:20190429130537p:plain
サンプルデータテーブルの内容


 sort_values関数の動きが分かりやすいように、アルファベット、数字、ひらがなを、それぞれ昇順、降順に並べたDataFrameを用意しました。


【サンプルデータテーブル】

index col1 col2 col3 col4 col5 col6
0 A F 0 5
1 B E 1 4
2 C D 2 3
3 D C 3 2
4 E B 4 1
5 F A 5 0


 続いて、sort_values関数のパラメータ「by」と「axis」の組み合わせによって、どのような結果になるのか確認します。


 はじめは「by」パラメータに列名、「axis」には、0を指定して行方向へ並び替えを行います。

df.sort_values(by='col6', axis=0)


 並び替えの結果は、以下の通りです。

f:id:Pimientito:20190429132709p:plain
sort_values関数の結果1


 カラム「col6」を軸として、行を縦断(赤矢印方向)するように昇順で並んでいます。


 続いて「by」パラメータに行名、「axis」には、1を指定して列方向へ並び替えを行います。

df.sort_values(by=[0, 1, 2, 3, 4, 5], axis=1)


 並び替えの結果は、以下の通りです。

f:id:Pimientito:20190429134753p:plain
sort_values関数の結果2


 少々、結果が分かりづらいですが、行名「0」を軸に列を横断(赤矢印方向)するように、左から昇順で並び替えられています。列名のcol1〜col6を見ると、順番が入れ替わっていることが分かります。


 はじめサンプルデータテーブルの列は左から「アルファベット列」「数値列」「ひらがな列」と並んでいましたが、並び替え後は「数値列」「アルファベット列」「ひらがな列」に変わっています。


 今度は「by」パラメータに、上記の逆順で行名を指定してみます。

df.sort_values(by=[5, 4, 3, 2, 1, 0], axis=1)


 並び替えの結果は、以下の通りです。


f:id:Pimientito:20190429140902p:plain
sort_values関数の結果3


 今度は、行名「5」を軸に、列を横断(赤矢印方向)するように、左から昇順で並び替えられています。


 今回、sort_values関数の動作確認の中で気付いたことですが、「by」パラメータの指定では、列名を指定する場合は、1列以上の列名を指定すれば動作しましたが、行名の場合は、2行以上の行名を指定しないと動作しませんでした。ドキュメントで、何か見落としているのかもしれませんが、今回は、このまま進めます。


 続いてshift関数の動作確認をします。先ほど作成したサンプルデータテーブルの値を少し増やし、時系列データのカラムを新規に追加しました。

import pandas as pd
from datetime import datetime as dt

df = pd.DataFrame({
    'col1' : ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J'],
    'col2' : ['J', 'I', 'H', 'G', 'F', 'E', 'D', 'C', 'B', 'A'],
    'col3' : [0, 1, 2, 3, 4, 5, 6, 7, 8, 9],
    'col4' : [9, 8, 7, 6, 5, 4, 3, 2, 1, 0],
    'col5' :['あ', 'い', 'う', 'え', 'お', 'か', 'き', 'く', 'け', 'こ'],
    'col6' :['こ', 'け', 'く', 'き', 'か', 'お', 'え', 'う', 'い', 'あ'],
    'col7' :[dt(2019, 1, 1), dt(2019, 2, 1), dt(2019, 3, 1), dt(2019, 4, 1), dt(2019, 5, 1), 
                dt(2019, 6, 1), dt(2019, 7, 1), dt(2019, 8, 1), dt(2019, 9, 1), dt(2019, 10, 1)],
})



 データの内容は、以下の通りです。

f:id:Pimientito:20190429181538p:plain
サンプルデータテーブル2の内容


 このテーブルに対しshift関数を用いて、指定した行数前または後のデータを取得して新しいカラムに代入します。shift関数を使用したコードは以下の通りです。

#パラメータ「periods」に正数を指定(古い日付データの取得)
df['periods=2'] = pd.Series(df['col7'].shift(periods=2))

#パラメータ「periods」に負数を指定(新しい日付データの取得)
df['periods=-2'] = pd.Series(df['col7'].shift(periods=-2))

#結果の表示
df

参考・参照元:「python_awesome.py」(抜粋)(p.107)を参考に作成


 shift関数を使用した結果は、以下の通りです。

f:id:Pimientito:20190429182148p:plain
shift関数の結果


 パラメータ「periods」に正数を指定すると、基準位置から上位の行(時系列データ昇順並びの場合、古い日付データ)へ移動し、負数を指定すると基準位置から下位の行(時系列データ昇順並びの場合、新しい日付データ)へ移動します。(図中の'NaT'は、日付型の欠損値(欠測値)を表す'Not a Time'です)


 筆者は、正数値は下位行へ、負数値は上位行へ移動するものと勝手にイメージしていました。pandas 0.24.2 documentation「pandas.DataFrame.shift」を確認したところ、正数を指定すると新しい日付(下位行)へ進むような記載になっていましたが、今回の動作確認では、逆の動きをしていました。


 どちらが正しいのか、現状分かりませんが、思い込みや、記憶違いを避けるためにも、使用する関数は、都度、ドキュメントや資料の確認を怠らないほうが良いでしょう。


過去n件の合計値の取得

 続いて、ある区間の行を指定して、過去データを取得する構文です。


 著者によると、PythonにはWindow関数が提供されていないため、SQLでは容易にできる処理を、Pythonで実現するためには、少々、長いコードになってしまうそうです。


 サンプルコードは、以下の通りです。

result[new_column] = 
    pd.Series(data_table.groupby(grouping_column)
    .apply(lambda x : x.sort_values(by=datetime_column, ascending=True))
    .loc[ : , target_column].rolling(center=False, window=m, min_periods=n)
    .some_function().reset_index(drop=True))

参考・参照元:「python_awesome.py」(抜粋)(p.112)を参考に作成


【補足】

項目 概要
result データセットから取得した結果
new_column 新規作成カラム
data_table データセット(データの源泉)
grouping_column グループ化対象カラム
datetime_column ソート対象日付型カラム
target_column 取得対象カラム
m パラメータ「window」の値
集約関数の処理範囲
n パラメータ「min_periods」の値
最小データ個数
some_function 集約関数


 このサンプルコードでは、以下のように動作します。


  1. カラム「grouping_column」でグループ化

  2. カラム「datetime_column」を時系列に昇順で並び替え

  3. カラム「target_column」を処理対象として、rolling関数でwindow化

  4. windowで分割された範囲に対して、集約関数「some_function」を実行



 サンプルコードだけでは、処理の内容が具体的にイメージできないため、サンプルデータを作成して、実際に動かしてみます。


 はじめに、架空の受注一覧を作成します。

import pandas as pd
from datetime import datetime as dt

df = pd.DataFrame({
    'UserID' : ['A001', 'A002', 'A003', 'A001', 'A002', 'A003', 'A001', 'A002', 'A003', 'A001', 'A002', 'A003'],
    'ProductID' : ['B001', 'D002', 'B001', 'B001', 'B001', 'D002', 'B001', 'D002', 'D002', 'D002', 'D002', 'B001'],
    'Type' : ['Blue-ray', 'DVD', 'Blue-ray', 'Blue-ray', 'Blue-ray', 'DVD', 'Blue-ray', 'DVD', 'DVD', 'DVD', 'DVD', 'Blue-ray'],
    'Price' : [50, 20, 50, 50, 50, 20, 50, 20, 20, 20, 20, 50],
    'Count' :[100, 50, 100, 150, 200, 50, 250, 100, 50, 100, 150, 50],
    'Date' :[dt(2019, 5, 1), dt(2019, 5, 3), dt(2019, 5, 5), dt(2019, 5, 5), dt(2019, 5, 8), dt(2019, 5, 10), 
                dt(2019, 5, 4), dt(2019, 5, 8), dt(2019, 5, 4), dt(2019, 5, 2), dt(2019, 5, 7), dt(2019, 5, 3)],
})


 作成した受注一覧は、以下の通りです。

f:id:Pimientito:20190430172403p:plain
受注一覧(サンプル)


 作成した受注一覧には、商品単価(Price)と受注個数(Count)があり、これらを掛け合わせて、1データレコード単位で合計金額を算出し、新規カラム「Total」へ格納します。

df['Total'] = df['Price'] * df['Count']


 合計金額のカラムを追加した受注一覧は、以下の通りです。

f:id:Pimientito:20190430173308p:plain
受注ごとに合計金額を追加した受注一覧(サンプル)


 次にサンプルコードの前半部分、グループ化と時系列データをソートする部分の結果を表示します。ここでは「UserID(顧客ID)」でグループ化したパターンと、「Type(商品種別)」でグループ化したパターンで確認します。

#UserIDでグループ化し、Dateを昇順でソート
df.groupby('UserID').apply(lambda x : x.sort_values(by='Date', ascending=True))


 「UserID」でグループ化した結果は、以下の通りです。

f:id:Pimientito:20190430183358p:plain
UserIDでグループ化した結果


 赤枠の部分が、UserID単位でグループ化された結果で、青枠の部分が、グループごとに古い日付から新しい日付に並び替えた結果です。


 同じように、今度は「Type」でグループ化します。

df.groupby('Type').apply(lambda x : x.sort_values(by='Date', ascending=True))


 「Type」でグループ化した結果は、以下の通りです。

f:id:Pimientito:20190430185514p:plain
Typeでグループ化した結果


 こちらの結果も、赤枠がグループ化された結果、青枠はグルーブごとに日付順で並び替えた結果となります。以上の結果を踏まえて、次はrolling関数の動きを確認します。


 サンプルコードでは、sum関数が使われていましたが、ここではmean関数(平均)を使用して動作を確認します。主な処理は、以下の通りです。


  • カラム「Type(商品種別)」でグループ化して、日付の古い順に並び替える。

  • 集約対象カラムは「Total(合計)」

  • ウィンドウサイズは3とし、ウィンドウ内に1個でも数値データがあれば平均金額を算出する。

  • rolling関数のパラメータ「center」を設定し、自身と前後1回分の受注、計3回分の受注平均金額を算出する。



 実際のコードは、以下の通りです。

df['mean'] = pd.Series(df.groupby('Type')
                       .apply(lambda x : x.sort_values(by='Date', ascending=True))
                       .loc[ : , 'Total'].rolling(window=3, min_periods=1, center=True).mean()
                       .reset_index(drop=True))


 結果は、以下の通りです。

f:id:Pimientito:20190430201910p:plain
受注3回分の平均受注金額


 rolling関数のwindow概要図は、以下の通りです。

f:id:Pimientito:20190501010345p:plain
rolling関数のwindow概要図


 最後に、サンプルコード末尾にあるreset_index(drop=True)関数ですが、この関数を省略すると集約関数の結果を正しく新しいカラムに格納することができません。実際に、どのような値が返されるのか確認します。


f:id:Pimientito:20190430205317p:plain
reset_index(drop=True)の有無による結果を比較


 今回の学習したサンプルコードの中で使用されている関数について、大まかに動作確認を行ないました。


 それぞれの関数には、今回ご紹介したパラメータ以外にも複数あり、中には公式ドキュメントを何度も読み返し、コーディングしても、なかなか理解できないものもありました。


 すべてを頭に記憶することは難しいですが、都度、ドキュメントを読み、理解するしかありません。


今回の学習

 「今回のテーマ」の内容を踏まえて、最初にテストデータの作成を行います。まず、どのようなデータが、今回の学習に適しているのか検討します。


テストデータの概要

条件

 以下の条件が含まれているデータが、今回の学習に適していると考えます。

  • 連続した日付型データを含んでいること。

  • 時系列で変化する要素を含んでいること。


テストデータの選択

 今回のテストデータは、前回【前処理の学習-28】で使用したカナダ トロントにある「Quandl」の株価情報を引き続き使用します。

f:id:Pimientito:20190317113820j:plain
「Quandl」トップページ


 データの詳細については、前回【前処理の学習-28】をご覧ください。

pimientito-handson-ml.hatenablog.com


テストデータの加工(前処理の前処理)

 今回の学習では、Pythonでコーディングするため、データの加工もPython上で行ないます。


テストデータを使って学習

 前回【前処理の学習-28】では「Quandl」のAPIを使用して、「The Walt Disney Campany」と「Microsoft Corporation」の株価データを取得し、2018年5月1日〜5月31日の1ヶ月間の株価変動を確認しました。今回も、同じデータセットや条件で、学習を進めます。


[Python]

 はじめに「Quandl」APIで、「The Walt Disney Campany」と「Microsoft Corporation」の株価データを取得します。

#【前処理の学習-29】データを学ぶ ~結合~④(Python編)
#過去データから情報を取得

#Quandl API を使用して、株価情報のデータセットを取得

#Quandl API
import quandl

#Quandl API 証明キーのセット(証明キーセットは伏字(*)に置き換えています)
quandl.ApiConfig.api_key = "****"

#データセット読み込み(The Walt Disney Company Stock Prices)
data_DIS = quandl.get('EOD/DIS')

#データセット読み込み(Microsoft Corporation Stock Prices)
data_MSFT = quandl.get('EOD/MSFT')


 取得したデータセットの内容は、以下の通りです。

f:id:Pimientito:20190501211809p:plain
取得した株価情報(The Walt Disney Company(DIS))


 続いてカラム「Date」が、インデックスとして設定されているため、reset_index関数を使って、インデックスを解除(新規インデックスを追加)します。

#reset_indexで、カラム'Date'をインデックス解除
data_DIS = data_DIS.reset_index()
data_MSFT = data_MSFT.reset_index()


 インデックスを解除した結果は、以下の通りです。

f:id:Pimientito:20190501212521p:plain
カラム「Date」からインデックスを解除


 次に、必要な情報のみ抜き出します。

#必要な情報のみ取得
df_DIS = data_DIS[['Date', 'Open', 'High', 'Low', 'Close']]
df_MSFT = data_MSFT[['Date', 'Open', 'High', 'Low', 'Close']]


 抜き出した結果は、以下の通りです。

f:id:Pimientito:20190501213122p:plain
必要項目のみ取得した結果


 使用する期間を絞り込みます。今回は、2018年5月1〜2018年5月31日の株価情報を使用して学習しますが、前週の平均株価との比較を行うため、5月1日〜5月7日までの比較対象として、4月20日の株価情報から取得します。(期間の絞り込み:2018年4月20日〜5月31日)

#2018.04.20〜2018.05.31の株価情報を取得
df_mrkt_2018_May_DIS = \
        df_DIS.loc[(df_DIS['Date'] >= '2018-04-20') & (df_DIS['Date'] < '2018-06-01'), \
                   ['Date', 'Open', 'High', 'Low', 'Close']].reset_index(drop=True)

df_mrkt_2018_May_MSFT = \
        df_MSFT.loc[(df_MSFT['Date'] >= '2018-04-20') & (df_MSFT['Date'] < '2018-06-01'), \
                   ['Date', 'Open', 'High', 'Low', 'Close']].reset_index(drop=True)


 絞り込んだ結果は、以下の通りです。

f:id:Pimientito:20190501214400p:plain
データの期間の絞り込み


 次に、rolling関数を使って集約しますが、rolling関数のwindowは、比較対象行(現在行)まで含んでしまうため、前週一週間のデータと比較するためには、少々手間が必要となります。


 一例ですが、今回は前週の平均株価を算出するために、以下の手順で「前週平均株価」を算出しました。


  1. 「前週一週間+比較対象行(現在行)=8日間」でwindowを生成し、全ての値を合計します。(仮に「Sum_8D」と呼びます)

  2. 続いて「Sum_8D」から、比較対象行(現在行)の株価を減算します。

  3. 残りの「Sum_8D」の値を、7(一週間)で除算します。


 実際に作成したコードは、以下の通りです。はじめに8日間分の株価を、rolling関数を使って集約します。(今回の学習では、groupby関数やsort_values関数を使用する必要が無かったため、割愛しています。)

#過去7日間+1日(計8日間)のClose価格を合算
df_mrkt_2018_May_DIS['Sum_8D'] = \
        round(df_mrkt_2018_May_DIS['Close'].rolling(window=8, min_periods=8).sum(), 2)

df_mrkt_2018_May_MSFT['Sum_8D'] = \
        round(df_mrkt_2018_May_MSFT['Close'].rolling(window=8, min_periods=8).sum(), 2)


 8日間の株価合計は、以下の通りです。

f:id:Pimientito:20190501221925p:plain
8日間の株価合計を算出


 週間平均株価を算出する前に、データの範囲を5月1日〜5月31日に絞り込みました。

#株価情報を2018年5月分のみ再取得
df_mrkt_2018_May_DIS = \
        df_mrkt_2018_May_DIS.loc[(df_mrkt_2018_May_DIS['Date'] >= '2018-05-01') & (df_mrkt_2018_May_DIS['Date'] < '2018-06-01'), \
                   ['Date', 'Open', 'High', 'Low', 'Close', 'Sum_8D']].reset_index(drop=True)

df_mrkt_2018_May_MSFT = \
        df_mrkt_2018_May_MSFT.loc[(df_mrkt_2018_May_MSFT['Date'] >= '2018-05-01') & (df_mrkt_2018_May_MSFT['Date'] < '2018-06-01'), \
                   ['Date', 'Open', 'High', 'Low', 'Close', 'Sum_8D']].reset_index(drop=True)


 株価情報を5月分のみに絞り込んだ結果は、以下の通りです。

f:id:Pimientito:20190501222743p:plain
株価情報(2018年5月分)


 8日間分の「Close(終値)」から、比較対象行(現在行)の「Close」を減算し、7(一週間)で除算した結果を、新規カラム「Avg_lastweek」へ格納します。

#8日間のClose合計値から、比較対象行(現在行)のClose値を減算(過去一週間の株価合計値のみ)
#残った値を、7(一週間)で除算して平均株価を算出

df_mrkt_2018_May_DIS['Avg_lastweek'] = \
       round( (df_mrkt_2018_May_DIS['Sum_8D'] - df_mrkt_2018_May_DIS['Close']) / 7, 2)

df_mrkt_2018_May_MSFT['Avg_lastweek'] = \
       round( (df_mrkt_2018_May_MSFT['Sum_8D'] - df_mrkt_2018_May_MSFT['Close']) / 7, 2)


 結果は、以下の通りです。

f:id:Pimientito:20190501224251p:plain
前週の平均株価の算出


 他の処理に入る前に、不要となったカラム「Sum_8D」を削除します。

#不要なカラム「Sum_8D」を削除
df_mrkt_2018_May_DIS.drop('Sum_8D', axis=1, inplace=True)
df_mrkt_2018_May_MSFT.drop('Sum_8D', axis=1, inplace=True)


 カラム「Sum_8D」を削除した結果は、以下の通りです。パラメータ「inplace」をTrueに設定することで、恒久的に処理結果が、変数に反映されます。(デフォルトは「False」です。変数内の値を更新したくない場合は、Falseのままにします。)


f:id:Pimientito:20190501231137p:plain
カラム「Sum_8D」を削除した結果


 最後に、比較対象行(現在行)のカラム「Close(終値)」から、前週の平均株価を引くことで損益を確認します。

#前週のClose値平均額と比較
df_mrkt_2018_May_DIS['Comp_lastweek'] = \
        df_mrkt_2018_May_DIS['Close'] - df_mrkt_2018_May_DIS['Avg_lastweek']

df_mrkt_2018_May_MSFT['Comp_lastweek'] = \
        df_mrkt_2018_May_MSFT['Close'] - df_mrkt_2018_May_MSFT['Avg_lastweek']


 比較した結果は、以下の通りです。

f:id:Pimientito:20190501231927p:plain
前週との終値の比較結果


 以上で、前回【前処理の学習-28】と同じ学習結果を取得できました。最後に、前回の学習同様に、ロウソク足チャートで、プロットします。


「可視化」に挑戦

可視化の目的

 前回【前処理の学習-28】のプロット図との比較


[matplotlib]ロウソク足チャート

 【前処理の学習-28】のコードに少し手を加えて、今回のデータをプロットしています。

#可視化(【前処理の学習-28】のコードを、一部改変)
#Matplotlib mpl_financeライブラリを利用して可視化

import pandas as pd
import matplotlib.pyplot as plt
from mpl_finance import candlestick2_ohlc
from datetime import datetime as dt


# プロットオブジェクトの初期設定
fig = plt.figure(figsize=(16, 13))
ax1 = plt.subplot(2, 1, 1)
ax2 = plt.subplot(2, 1, 2)


#The Walt Disney Company Stock Prices(May, 2018)
# candlestick2でローソク足を描画
candlestick2_ohlc(ax1, df_mrkt_2018_May_DIS["Open"], df_mrkt_2018_May_DIS["High"], \
                  df_mrkt_2018_May_DIS["Low"], df_mrkt_2018_May_DIS["Close"], \
                  width=0.2, colorup="b", colordown="r", alpha=1)

#前週平均株価
ax1.plot(df_mrkt_2018_May_DIS["Avg_lastweek"])

#タイトル設定
ax1.set_title("The Walt Disney Company")

# X軸の設定
#ラベルの設定(rotation: ラベル表示角度(例:45→45°)
ax1.set_xticklabels(df_mrkt_2018_May_DIS["Date"],rotation=45,fontsize='small')
# 目盛のデータ数が可変の場合、range関数にshape属性の値を渡すと流動的に範囲を変更できます。
ax1.set_xticks(range(df_mrkt_2018_May_DIS.shape[0]))
ax1.set_xlabel("Day")

# 横軸の範囲をデータ個数とする(0〜shape属性の配列[0]番目を指定)
ax1.set_xlim([0, df_mrkt_2018_May_DIS.shape[0]])
ax1.set_ylabel("Price")



#Microsoft Corporation Stock Prices(May, 2018)
candlestick2_ohlc(ax2, df_mrkt_2018_May_MSFT["Open"], df_mrkt_2018_May_MSFT["High"], \
                  df_mrkt_2018_May_MSFT["Low"], df_mrkt_2018_May_MSFT["Close"], \
                  width=0.2, colorup="b", colordown="r", alpha=1)

#前週平均株価
ax2.plot(df_mrkt_2018_May_MSFT["Avg_lastweek"])

ax2.set_title("Microsoft Corporation")

ax2.set_xticklabels(df_mrkt_2018_May_MSFT["Date"], rotation=45,fontsize='small')
ax2.set_xticks(range(df_mrkt_2018_May_MSFT.shape[0]))
ax2.set_xlabel("Day")

ax2.set_xlim([0, df_mrkt_2018_May_MSFT.shape[0]])
ax2.set_ylabel("Price")

#サブプロット間の空白調整
plt.subplots_adjust(wspace=0, hspace=0.5)

#グリッド表示
ax1.grid(True)
ax2.grid(True)

#凡例の表示
ax1.legend()
ax2.legend()

#プロット表示
plt.show()


 出力したプロット図は、以下の通りです。

f:id:Pimientito:20190501234948p:plain
今回作成したプロット図


 前回【前処理の学習-28】で作成したプロット図は、以下の通りです。

f:id:Pimientito:20190323175230p:plain
【前処理の学習-28】で作成したプロット図


 前回も、今回も同じデータセットを使用しているため、全体的に同じようにプロットされていますが、今回は、前週平均株価(平均終値)のプロットも重ねてみました。


今回のまとめ

 前回、今回と二回に渡って、同じテーマをSQLPythonで学習しました。


 作業工数の視点から見た場合、個人的な所感ですが、Pythonだけで進めるには、SQLより手間が掛かる印象が強いです。筆者のPythonの習熟度の低さを加味しても、Pythonによる作業は、ライブラリや関数を、もっと学習する必要があり、SQL以上にハードルが高いと感じました。(ただし、SQLの副問い合わせが、何重にも重なる場合においては、SQLの方が楽とも言い難いと思います)


 著者は、参考資料の中で「SQLでできるところはSQLで」と述べていらっしゃいますが、今の筆者のレベルでは、今後も引き続きSQLPythonも学習しなければ、その違いが分からないと思っています。


 最後に「前処理大全」の著者 本橋智光氏が、執筆時のご苦労を語っていらっしゃる対談記事をご紹介して、今回の学習を終えたいと思います。



  『仕事ではじめる機械学習』&『前処理大全』著者対談

www.oreilly.co.jp




 今回は、以上です。



【参考資料】

pandas 0.24.2 documentation「pandas.Series.sort_values」

pandas.pydata.org


pandas 0.24.2 documentation「pandas.DataFrame.sort_values」

pandas.pydata.org


SciPy.org「numpy.sort」

docs.scipy.org


Youtube「When should I use the "inplace" parameter in pandas ?」

www.youtube.com


note.nkmk.me「pandasで窓関数を適用するrollingを使って移動平均などを算出」

note.nkmk.me


本ブログに関するお問い合わせ先

 筆者自身の「機械学習」の学習のため、参考にさせていただいている資料や、Web情報に対して、情報元の権利を侵害しないよう、できる限り、細心の注意を払って、ブログの更新に努めておりますが、万が一、関係各所の方々に、不利益が生じる恐れがある場合、大変お手数ですが、下記の「お問い合わせ先」まで、ご一報いただけますようお願いいたします。



 お問い合わせ先:otoiawase@handson-t-arte.com

【前処理の学習-28】データを学ぶ ~結合~③

 前回は、既存データから生成した、新しいマスタテーブルの結合について、学びました。

pimientito-handson-ml.hatenablog.com

 今回も、引続きデータの「結合」について学んでいきます。

【今回の目標到達点】

 時系列データを利用して、過去データを結合する

【目次】


参考資料のご紹介

 はじめに、現在、主に参考とさせていただいている書籍をご紹介します。

 「前処理大全 データ分析のためのSQL/R/Python実践テクニック」本橋智光氏著(技術評論社)


「データ結合」の概要

 参考資料「前処理大全」の「第4章 結合」冒頭で、データの結合について、著者は、以下のように述べています。

必要なデータが1つのテーブルにすべて入っていることはまれです。業務システムのデータベースは、データの種類ごとにテーブルが分かれているからです。一方、データ分析用のデータは1つのテーブルにまとまった横に長いデータが望ましく、そのようなデータを得るためにはテーブル同士を結合する処理が必要になります。

参考・参照元:第4章「結合」(p.084)より抜粋


 その上で、データの結合について、以下、3つの考え方をご紹介されています。

  • マスタテーブルから情報を取得

  • 条件に応じて結合するマスタテーブルを切り替え

  • 過去データから情報を取得


今回のテーマ

 過去データから情報を取得


概要

 参考資料「前処理大全」の「4-3 過去データの結合」の冒頭で、著者は「過去データ」を活用することは、基礎分析や、予測モデルを構築する上で、有用であると述べています。以下、該当の文章を引用します。


基礎分析をするにしても、予測モデルを構築するにしても、過去データを活用することは有用です。筆者は「過去データの前処理を制するものはデータの前処理を制す」ぐらい重要だと考えています。

参考・参照元:第4章「4-3 過去データの結合」(p.103)より抜粋


 反面、時系列データの取り扱いは難しく、処理によっては、意図しない変換や、リーク(予測モデルに使用するデータに、未来のデータが混入すること)が伴うことがあるため、十分な注意が必要とも述べています。


 なお、時系列データを無作為に結合することにより、データ数が大量に増えてしまう危険性についても触れており、以下、二点の対策を利用することを、推奨されています。


1.結合対象とする過去の期間を絞る


2.結合した過去データに集約関数を利用して、データ数を増やさないようにする

参考・参照元:第4章「4-3 過去データの結合」(p.104)より抜粋


 具体的には「過去データ」を扱う際には、不必要に長い期間を指定するのではなく、分析に必要な期間に限定することや、過去データの結合時に、集約関数を使用して、データをまとめることで、結合によるデータ増加(レコード増加)を、抑止することができるようです。


 なおSQLに限定したことですが、JOIN句/関数より、WINDOW句/関数を用いた方が、簡潔にまとめることができ、かつ、計算処理のパフォーマンスも効率的であると、著者は述べています。


 前回【前処理の学習-27】では、結合処理により、爆発的にデータが増加することを経験したため、上記の対策を意識して、学習を続けます。


[SQL]

 参考資料「4-3 過去データの結合」で紹介されているサンプルコードの内、行または行数を指定して、データを取得する処理を学習します。



  1. 行を指定してデータを取得

  2. 行数を指定してデータを取得



n件前のデータ取得

 ある行を指定して、過去データを取得する構文は、以下の通りです。

SELECT *,
    LAG(target_column, n) 
        OVER (PARTITION BY grouping_column ORDER BY datatime_column) 
            AS new_column

FROM data_table ;

 参考・参照元:「sql_awesome.sql」(p.105-106)を参考に作成


【補足】

項目 概要
LAG WINDOW関数(*1)
target_column 取得対象カラム
n LAG関数の引数
n行前を意味します
grouping_column グループ化対象カラム
datatime_column ソート対象日付型カラム
new_column 新規作成カラム
data_table データテーブル

(*1) SQLのWINDOW関数のひとつ。パーティション内の現在行よりoffset(*2)行数分前のレコードの値を返す。

(*2) offsetとは、基準となる位置からの差を表す値のこと。相対位置(参考・参照元:IT用語辞典 e-Word「オフセット【offset】」より抜粋) なお、現在行より後のレコードの値を返す場合は、LEAD関数を使用します。


過去n件の合計値の取得

 続いて、ある区間の行を指定して、過去データを取得する構文です。ここでは条件式(CASE WHEN~THEN句)でレコード件数を確認し、条件を満たすデータのみ集計する流れになっています。

SELECT *, 
    CASE WHEN
        COUNT(target_column)
            OVER (PARTITION BY grouping_column ORDER BY datatime_column 
                ROWS BETWEEN n PRECEDING AND CURRENT ROW) = some_number
    THEN

        SUM(target_column) 
            OVER (PARTITION BY grouping_column ORDER BY datatime_column 
                ROWS BETWEEN n PRECEDING AND CURRENT ROW)

    ELSE NULL END AS new_column

FROM data_table ;

 参考・参照元:「sql_awesome.sql」(p.109-110)を参考に作成


【補足】

項目 概要
target_column 集約対象カラム
grouping_column グループ化対象カラム
datatime_column ソート対象日付型カラム
n 行数
new_column 新規作成カラム
data_table データテーブル


 SQL文のみでは、処理の流れが分かりづらいため、WINDOW句のコードに注釈を付けた図を作成しました。

f:id:Pimientito:20190303165821j:plain
WINDOW句の処理の流れ説明図


 また、この構文には、PARTITION BY句でグループ化された中に「フレーム」が作成されています。ここで「フレーム」についても、少し触れてみます。


ウィンドウ関数は、テーブルをウィンドウという部分集合にカットして、その中で順序づけを行なうものです。しかし、実はウィンドウの中でさらに集計範囲を細かく指定するオプション機能があります。そのオプションの集計範囲は「フレーム」と呼ばれています。

 参考・参照元:「SQL ゼロからはじめるデータベース操作」ミック氏著(翔泳社)「移動平均を算出する」(p.263)より抜粋


 WINDOW句の「パーティション」と「フレーム」の関係は、以下の通りです。

f:id:Pimientito:20190309000019j:plain
パーティション」と「フレーム」の関係図


 「フレーム」では、集約関数の有効範囲を指定します。範囲の指定方法は「現在行からn行前まで」または「現在行からn行後まで」と記載します。



 「現在行からn行前まで」の場合

 ROWS BETWEEN n PRECEDING AND CURRENT ROW


 「現在行からn行後まで」の場合

 ROWS BETWEEN CURRENT ROW AND n FOLLOWING



 CURRENT ROWの代わりに、PRECEDING n や、FOLLOWING nを指定することで「現在行ーn行から何行前まで」「現在行+n行から何行後まで」と指定することも可能です。


 参考資料「前処理大全」では、この他にPythonを使用した場合の処理も紹介されていますが、少々長く、複雑なサンプルコードのため、次回、あらためて学習します。


今回の学習

 「今回のテーマ」の内容を踏まえて、最初にテストデータの作成を行います。まず、どのようなデータが、今回の学習に適しているのか検討します。


テストデータの概要

条件

 以下の条件が含まれているデータが、今回の学習に適していると考えます。

  • 連続した日付型データを含んでいること。

  • 時系列で変化する要素を含んでいること。


テストデータの選択

 上記「条件」の内容をもとに、今回は、カナダ トロントにある「Quandl」の株価情報を使用して、テストデータを作成します。

f:id:Pimientito:20190317113820j:plain
「Quandl」トップページ


 「Quandl」は、サブスクリプション型サービスを提供しているサイトのため、完全な「オープンデータ」ではありませんが、ユーザ登録をすることで、一部の情報が無料で利用できます。


 また、R言語や、PythonExcel用にAPIが提供されており、ユーザ登録することで「API認証キー」が付与されます。


 以下のフィルタを利用して、データを検索することもできます。

【FILTER】

項目名 和訳
Premium 有料
Free 無料


【ASSET CLASS(資産クラス)】

英名 和訳
Equities 資産
Currencies 通過
Interest Rates & Fixed Income(*) 金利と固定収入
Options オプション取引
Indexs インデックス運用
Mutual Funds & ETFs(*) 証券投資信託と上場投資信託
Real Estate 不動産
Economy & Society 経済社会
Energy エネルギー
Agriculture 農業
Metals 鉄鋼業
Futures 先物
Other その他

(*) 有料データ(一部、無料サンプル有り)


【DATA TYPE】

英名 和訳
Prices & Volumes 価格と数量
Estimates(*) 概算/見積もり
Fundamentals 経済基礎条件
Corporate Actions(*) 企業活動
Sentiment(*) 相場状況
Derived Metrics(*) 指標
National Statistics 統計

(*) 有料データ(一部、無料サンプル有り)


 今回「Quandl」の「End of Day US Stock Prices」から、無料枠内で取得できるデータを、サンプルデータとして学習に使用します。レコードの構成は、以下の通りです。

【レコード構成】

カラム名 概要
Date 市場取引年月日
Open 始値
High 高値
Low 安値
Close 終値
Volume 出来高
Dividend 配当
Split 株式分割
Adj_Open 始値修正
Adj_High 高値修正
Adj_Low 安値修正
Adj_Close 終値修正
Adj_Volume 出来高修正


 銘柄は「ウォルト・ディズニー・カンパニー(DIS)」と「マイクロソフト(MSFT)」を選択しました。


テストデータの加工(前処理の前処理)

 今回使用するデータの取得では、Quandlが提供するPython API「quandl」を使用します。APIは事前に、Python環境へインストールしておく必要があります。


 インストール方法と、APIの使用方法の概要は、以下の通りです。

#Quandl API Install
pip install quandl

#Quandl API Usage
import quandl

#Quandl API 証明キーのセット
#****部分に、ユーザ登録後に付与される証明キーを記載します
quandl.ApiConfig.api_key = "****"

#データの読み込み
mydata = quandl.get(“{Directory/Stock_Name}”)

#{ }で囲った部分にデータのパスを入力します


 今回の前処理では、以下の対応が必要です。


  1. レコードに、カラム「銘柄(DIS/MSFT)」を新規追加

  2. データをDBに取り込む



 はじめに、APIでデータを取得します。

[MacOS][Python][Jupyter Notebook]

#【前処理の学習-28】データを学ぶ ~結合~③
#過去データから情報を取得

#0. Quandl API を使用して、株価データを取得

#Quandl API
import quandl

#Quandl API 証明キーのセット
#****部分に、ユーザ登録後に付与される証明キーを記載します
quandl.ApiConfig.api_key = "****"

#データ読み込み(The Walt Disney Company Stock Prices)
data_DIS = quandl.get('EOD/DIS')

#データ読み込み(Microsoft Corporation Stock Prices)
data_MSFT = quandl.get('EOD/MSFT')


 続いて取得した株価データ(The Walt Disney Company Stock Prices)のデータタイプを確認します。

f:id:Pimientito:20190321144422p:plain
株価データ(DIS)の型を確認


 PandasのDataFrame型であることが分かりました。続いてデータの内容を表示します。

f:id:Pimientito:20190321145020p:plain
株価データ(DIS)の内容


 カラム「Date」が、他のカラムより一段下に表示されています。これは「Date」のデータが、インデックスとして割り当てられているためです。その様子を、もう少し詳しく見てみます。

f:id:Pimientito:20190321153439p:plain
株価データ(DIS) インデックスの表示


 インデックスの内容が、日付になっていることが分かります。では、インデックスデータを、他の要素と同じように利用できるのか確認します。

f:id:Pimientito:20190321155039p:plain
株価データ(DIS) Shape属性の確認


 DataFrameのShape属性で確認すると、列数が12と表示されています。上述の【レコード構成】では、カラム数が13個あるため、このままでは日付データを利用できないことが分かりました。


 そこでDataFrameのreset_index関数を利用して、インデックスを他のカラムと同じレコードの要素に変換します。reset_index関数の実行結果は、以下の通りです。

f:id:Pimientito:20190321165441p:plain
reset_index関数実行後の株価データ(DIS)の内容


 0から始まるインデックスの列が新しく追加されて、「Date」カラムが、他のカラムと同じ高さで表示されていることが確認できました。先ほどと同じようにShape属性も確認してみます。

f:id:Pimientito:20190321170401p:plain
reset_index関数実行後の株価データ(DIS) Shape属性の確認


 カラム数が13個に変わっていることも確認できました。これで日付を含んだデータとして利用できます。


 次に、このデータをDBへ保存します。はじめにテストテーブルを作成するため、各カラムの型を確認します。実行結果は、以下の通りです。

f:id:Pimientito:20190321200840p:plain
DataFrame info()関数の実行結果


 株価情報のため、先頭の日付型を除いて、すべて浮動小数点型で構成されています。データの型が確認できましたので、テストテーブルを作成し、データを格納します。

[MacOS][Python][Jupyter Notebook]

#1. テストテーブル「tbl_markets」の作成

#標準モジュール
import sys

#コード実行時の履歴確認
import traceback

#postgreSQL用Python DB API「psycopg」
import psycopg2

#変数の初期化
con = None

#----- パラメータ -----
#DBテーブル名
tbl_name = 'tbl_markets'

#CREATE文(カラムリスト 14カラム)
clmn_list_create = 'mrkt_date date, brand_id varchar, open numeric, high numeric,\
                    low numeric, close numeric, volume numeric, dividend numeric,\
                    split numeric, adj_open numeric, adj_high numeric, adj_low numeric,\
                    adj_close numeric, adj_volume numeric,'

#CREATE文(主キー)
clmn_P_Key = 'mrkt_date, brand_id'

#----- SQL -----
#CREATE TABLE
sql_create = 'CREATE TABLE ' + tbl_name + ' (' + clmn_list_create + ' PRIMARY KEY(' + clmn_P_Key + '));'


#データベース接続パラメータ
#passwordは、伏字(*)に変換しています。
db_connection_parameter = "host='localhost' dbname='pimientito_ml' user='pimientito' password='****'"

try:
    #データベースへ接続
    with psycopg2.connect(db_connection_parameter) as con:
        con.autocommit = False
        
        #コンソールオブジェクト生成
        with con.cursor() as cur:
            
            #CREATE文実行
            cur.execute(sql_create)
            con.commit()
                                
except psycopg2.DatabaseError as e:
    if con:
        con.rollback()
 
    print('Error: %s' % e)
    traceback.print_exc()
    sys.exit(1)
    
except Exception as e:
    if con:
        con.rollback()
        
    print('Exception Error: %s' % e)
    traceback.print_exc()
    sys.exit(1)
    
finally:   
    con.commit()
    if con:
        cur.close()
        con.close()

    print('処理が完了しました.')


 作成したテストテーブル「tbl_markets」のカラムは、以下の通りです。

カラム名 型 (*1) 概要 主キー
mrkt_date date 日付
brand_id(*2) varchar 銘柄略称
open numeric 始値
high numeric 高値
low numeric 安値
close numeric 終値
volume numeric 出来高
dividend numeric 配当
split numeric 株式分割
adj_open numeric 始値修正
adj_high numeric 高値修正
adj_low numeric 安値修正
adj_close numeric 終値修正
adj_volume numeric 出来高修正

(*1) PostgreSQLの型名

(*2) オリジナルのレコードには、銘柄表記(DIS/MSFT)が不足しているため新規追加


 次に取得したデータを、テストテーブルへ挿入します。(例外処理のコードは割愛します)

[MacOS][Python][Jupyter Notebook]

#SQL INSERT文のフォーマットに合わせるために使用
from pandas import DataFrame as df_cast_data

#postgreSQL用Python DB API「psycopg」
import psycopg2

#変数の初期化
con = None

#----- パラメータ -----
#DBテーブル名
tbl_name = 'tbl_markets'

#----- SQL -----
#INSERT
sql_insert = 'INSERT INTO ' + tbl_name + ' VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) '


#データベース接続パラメータ
#passwordは、伏字(*)に変換しています。
db_connection_parameter = "host='localhost' dbname='pimientito_ml' user='pimientito' password='****'"

try:
    with psycopg2.connect(db_connection_parameter) as con:
        con.autocommit = False
        
        #コンソールオブジェクト生成
        with con.cursor() as cur:
            
            #リスト型配列
            arry_row = []
            
            #テストテーブルへデータ挿入(The Walt Disney Company Stock Prices)
            for row in data_DIS_rstidx.values:
                                    
                #抽出したデータを、DataFrame型(一行単位)に変換
                df_cast_data = (row[0], "DIS", row[1], row[2], row[3], row[4],row[5], \
                                row[6], row[7], row[8], row[9], row[10], row[11],row[12])

                #リスト型配列arry_rowへ追加
                arry_row.append(df_cast_data)
                    
            #DBテーブルへデータを挿入
            cur.executemany(sql_insert,arry_row)
            con.commit()

            
    #テストテーブルへデータ挿入(Microsoft Corporation Stock Prices)
    with psycopg2.connect(db_connection_parameter) as con:
        con.autocommit = False
        
        #コンソールオブジェクト生成
        with con.cursor() as cur:
            
            #リスト型配列
            arry_row = []
            
            #DataFrameのデータ抽出(MSFT)
            for row in data_MSFT_rstidx.values:
                                
                #抽出したデータを、DataFrame型(一行単位)に変換
                df_cast_data = (row[0], "MSFT", row[1], row[2], row[3], row[4],row[5], \
                                row[6], row[7], row[8], row[9], row[10], row[11],row[12])
                    
                #リスト型配列arry_rowへ追加
                arry_row.append(df_cast_data)
                                        
            #DBテーブルへデータを挿入
            cur.executemany(sql_insert,arry_row)
            con.commit()


 サンプルデータは、2,2723レコードありました。今回は、このデータを使用して学習を進めます。


テストデータを使って学習

 「今回のテーマ」で取り上げた"過去n件の合計値の取得"のSQL構文を参考に、以下のSQL文を作成しました。

WITH tbl_temporary AS ( 
    SELECT *, 
        CASE WHEN 
            COUNT(*) OVER (PARTITION BY brand_id ORDER BY mrkt_date ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) = 5 
        THEN 
            AVG(close) OVER (PARTITION BY brand_id ORDER BY mrkt_date ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) 
        ELSE NULL END AS avg_lastweek_close 
    FROM tbl_markets 
    WHERE brand_id = 'DIS' AND (mrkt_date BETWEEN to_date('2018-04-20', 'YYYY MM DD') AND to_date('2018-05-31', 'YYYY MM DD')))

SELECT mrkt_date, round(open,2) AS open, round(high,2) AS high, round(low,2) AS low, round(close,2) AS close, 
                round(avg_lastweek_close,2) AS avg_lastweek, round(close,2) - round(avg_lastweek_close,2) AS comp_lastweek 
FROM tbl_temporary AS tmp 
WHERE tmp.mrkt_date BETWEEN to_date('2018-05-01', 'YYYY MM DD') AND to_date('2018-05-31', 'YYYY MM DD');


 このSQL文で抽出できるデータの内容は、以下の通りです。


  • 抽出期間は、2018/05/01〜2018/05/31

  • 始値、高値、安値、終値

  • 各レコードの直前5行(おおよそ前週分データに相当)の平均終値

  • 当日終値から前5日間の平均終値を引いた差分



[Python]

 上記のSQL文を、Pythonに組み込んだサンプルコードは、以下の通りです。(例外処理の部分は割愛しています)

[MacOS][Python][Jupyter Notebook]

#3. 2018年5月期の株価情報を取得
# 取得データ: 始値、高値、安値、終値
# 加工データ: 前5日間の平均終値、当日終値ー前5日間の終値の差額

#標準モジュール
import sys

#コード実行時の履歴確認
import traceback

#日付、時間の取得
from datetime import datetime as dt

#Pandasモジュール
import pandas as pd

#SQL SELECT文の結果
from pandas import DataFrame as df_mrkt_2018_May_DIS
from pandas import DataFrame as df_mrkt_2018_May_MSFT

#postgreSQL用Python DB API「psycopg」
import psycopg2

#変数の初期化
con = None

#----- SQL -----
#SELECT(The Walt Disney Company Stock Prices)
sql_mrkt_2018_May_DIS = \
'WITH tbl_temporary AS ( \
SELECT *, \
CASE WHEN \
COUNT(*) OVER (PARTITION BY brand_id ORDER BY mrkt_date ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) = 5 \
THEN \
AVG(close) OVER (PARTITION BY brand_id ORDER BY mrkt_date ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) \
ELSE NULL END AS avg_lastweek_close \
FROM tbl_markets \
WHERE brand_id = \'DIS\' AND (mrkt_date BETWEEN to_date(\'2018-04-20\', \'YYYY MM DD\') AND to_date(\'2018-05-31\', \'YYYY MM DD\'))) \
SELECT mrkt_date, round(open,2) AS open, round(high,2) AS high, round(low,2) AS low, round(close,2) AS close, \
round(avg_lastweek_close,2) AS avg_lastweek, round(close,2) - round(avg_lastweek_close,2) AS comp_lastweek \
FROM tbl_temporary AS tmp \
WHERE tmp.mrkt_date BETWEEN to_date(\'2018-05-01\', \'YYYY MM DD\') AND to_date(\'2018-05-31\', \'YYYY MM DD\');'


#SELECT(Microsoft Corporation Stock Prices)
sql_mrkt_2018_May_MSFT = \
'WITH tbl_temporary AS ( \
SELECT *, \
CASE WHEN \
COUNT(*) \
OVER (PARTITION BY brand_id ORDER BY mrkt_date ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) = 5 \
THEN \
AVG(close) OVER (PARTITION BY brand_id ORDER BY mrkt_date ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) \
ELSE NULL END AS avg_lastweek_close \
FROM tbl_markets \
WHERE brand_id = \'MSFT\' AND (mrkt_date BETWEEN to_date(\'2018-04-20\', \'YYYY MM DD\') AND to_date(\'2018-05-31\', \'YYYY MM DD\'))) \
SELECT mrkt_date, round(open,2) AS open, round(high,2) AS high, round(low,2) AS low, round(close,2) AS close, \
round(avg_lastweek_close,2) AS avg_lastweek, round(close,2) - round(avg_lastweek_close,2) AS comp_lastweek \
FROM tbl_temporary AS tmp \
WHERE tmp.mrkt_date BETWEEN to_date(\'2018-05-01\', \'YYYY MM DD\') AND to_date(\'2018-05-31\', \'YYYY MM DD\');'


#データベース接続パラメータ
#passwordは、伏字(*)に変換しています。
db_connection_parameter = "host='localhost' dbname='pimientito_ml' user='pimientito' password='****'"

try:
    #データベースへ接続
    with psycopg2.connect(db_connection_parameter) as con:
        con.autocommit = False
        
        #コンソールオブジェクト生成
        with con.cursor() as cur:
            
            #処理時間 計測開始
            s_time = dt.now()
            
            #SELECT文実行
            df_mrkt_2018_May_DIS = pd.read_sql_query(sql_mrkt_2018_May_DIS, con)
            df_mrkt_2018_May_MSFT = pd.read_sql_query(sql_mrkt_2018_May_MSFT, con)
            
            #処理時間 計測終了
            e_time = dt.now()
            
            #計測時間の表示
            print('start   time: ' + str(s_time))
            print('end     time: ' + str(e_time))
            print('process time: ' + str(e_time - s_time))
            


 抽出したデータ(DIS)は、以下の通りです。

f:id:Pimientito:20190323014454p:plain
抽出結果(DIS)の表示


「可視化」に挑戦


可視化の目的

 筆者は、株式投資の経験はありませんが、株式情報といえば、数々のグラフを駆使して分析されている投資家の方々の姿を思い浮かべます。


 今回は、数ある株式分析ツールの中から「ロウソク足チャート」に挑戦してみたいと思います。なお余談ですが、ロウソク足チャートは、日本発祥の分析ツールのようです。以下に、Wikiの情報の一部を抜粋します。


ローソク足は江戸時代に出羽国本間宗久が発案し、大阪・堂島の米取引で使われたといわれている。現在は日本国内だけでなく世界中のヘッジファンド個人投資家が、最も基本的なチャートの1つとしてローソク足チャートを利用している。

参考・参照元wikipediaローソク足チャート」より抜粋


[matplotlib]ロウソク足チャート

 今回の株価情報のプロットには、matplotlibの「mpl_finance」ライブラリを利用しますが、2019年3月23日の時点では、Anacondaからのインストールはできませんでした。そのため、pipを使ってインストールする必要があります。インストール方法は、以下の通りです。

pip install git+https://github.com/matplotlib/mpl_finance


[MacOS][Python][Jupyter Notebook]

#4. matplotlib mpl_financeライブラリを利用して可視化

import matplotlib.pyplot as plt
from mpl_finance import candlestick2_ohlc


# プロットオブジェクトの初期設定
fig = plt.figure(figsize=(16, 13))
ax1 = plt.subplot(2, 1, 1)
ax2 = plt.subplot(2, 1, 2)


#The Walt Disney Company Stock Prices(May, 2018)
# candlestick2でローソク足を描画
candlestick2_ohlc(ax1, df_mrkt_2018_May_DIS["open"], df_mrkt_2018_May_DIS["high"], \
                  df_mrkt_2018_May_DIS["low"], df_mrkt_2018_May_DIS["close"], \
                  width=0.2, colorup="b", colordown="r", alpha=1)

#タイトル設定
ax1.set_title("The Walt Disney Company")

# X軸の設定
#ラベルの設定(rotation: ラベル表示角度(例:45→45°)
ax1.set_xticklabels(df_mrkt_2018_May_DIS["mrkt_date"].values,rotation=45,fontsize='small')
# 目盛のデータ数が可変の場合、range関数にshape属性の値を渡すと流動的に範囲を変更できます。
ax1.set_xticks(range(df_mrkt_2018_May_DIS.shape[0]))
ax1.set_xlabel("Day")

# 横軸の範囲をデータ個数とする(0〜shape属性の配列[0]番目を指定)
ax1.set_xlim([0, df_mrkt_2018_May_DIS.shape[0]])
ax1.set_ylabel("Price")


#Microsoft Corporation Stock Prices(May, 2018)
candlestick2_ohlc(ax2, df_mrkt_2018_May_MSFT["open"], df_mrkt_2018_May_MSFT["high"], \
                  df_mrkt_2018_May_MSFT["low"], df_mrkt_2018_May_MSFT["close"], \
                  width=0.2, colorup="b", colordown="r", alpha=1)

ax2.set_title("Microsoft Corporation")

ax2.set_xticklabels(df_mrkt_2018_May_MSFT["mrkt_date"].values,rotation=45,fontsize='small')
ax2.set_xticks(range(df_mrkt_2018_May_MSFT.shape[0]))
ax2.set_xlabel("Day")

ax2.set_xlim([0, df_mrkt_2018_May_MSFT.shape[0]])
ax2.set_ylabel("Price")

#サブプロット間の空白調整
plt.subplots_adjust(wspace=0, hspace=0.3)
plt.show()


 プロットした結果は、以下の通りです。

f:id:Pimientito:20190323175230p:plain
プロット図(ロウソク足)


pimientitoの推測

 残念ながら、株式分析に必要な情報は、これだけでは不十分で、また今回の抽出データでも、正確な情報が取得できていません。(例えば、一週間の取引が通常5営業日ですが、祝祭日があった場合の取引日数の扱いなど)


 また、筆者に株式市場の基本知識が備わっていないため、ただ「上がった」「下がった」くらいしか判断できません。「機械学習」を続けていくことで、株式の基本知識も身に付いたら、データ分析が、より楽しいものになるかもしれません。


今回のまとめ

 今回は、過去データを集約・結合することの重要性を学びました。


 参考資料「前処理大全」の「結合」の章に入ってからは、いままで以上に、内容が複雑になり、単に構文や関数の理解だけでは追い付かない印象です。


 何度も繰り返して読む度に、その中で語られている内容を理解するためには、それ以上に参考資料外の知識が必要だと感じることも多くなりました。


 一節、一節と学習負荷が高まっていきますが、これからもコツコツと続けていきたいと思います。次回は、同じ節のPythonの処理について学習します。



 今回は、以上です。



【参考資料】

SQL ゼロからはじめるデータベース操作」ミック氏著(翔泳社)

SQL 第2版 ゼロからはじめるデータベース操作 (プログラミング学習シリーズ)

SQL 第2版 ゼロからはじめるデータベース操作 (プログラミング学習シリーズ)


Quandl

www.quandl.com


Quandl API Documentation

docs.quandl.com


CODELAB技術ブログ「PythonでQuandlから金融データを取得する」

www.codelab.jp


PostgreSQL 10.5文書「9.21. ウィンドウ関数」

www.postgresql.jp


IT用語辞典 e-Words「オフセット 【 offset 】」

e-words.jp


株式会社 証券会計翻訳総研「会計・金融・証券用語辞典」

www.shokenkaikei.com


英語雑貨屋「ビジネス英語用語集 - 株式英語用語集(英語から)

www.rondely.com


Infobank マネー百科 by ARTIS「金融用語辞典」

money.infobank.co.jp


Quite @katafuchix氏「ModuleNotFoundError: No module named 'matplotlib.finance' 対応メモ」

qiita.com


Quite @eggman氏「Jupyter Notebookでビットコインローソク足チャートを描画」

qiita.com


質問をすることでしか得られない、回答やアドバイスがある。「株価の日付表示の仕方(グラフのx軸)」

teratail.com


wikipediaローソク足チャート」

ja.wikipedia.org



本ブログに関するお問い合わせ先

 筆者自身の「機械学習」の学習のため、参考にさせていただいている資料や、Web情報に対して、情報元の権利を侵害しないよう、できる限り、細心の注意を払って、ブログの更新に努めておりますが、万が一、関係各所の方々に、不利益が生じる恐れがある場合、大変お手数ですが、下記の「お問い合わせ先」まで、ご一報いただけますようお願いいたします。



 お問い合わせ先:otoiawase@handson-t-arte.com


【前処理の学習-27】データを学ぶ ~結合~②

 前回は、データテーブルとマスタテーブルの効率的な結合について学びました。

pimientito-handson-ml.hatenablog.com

 今回も、引続きデータの「結合」について学んでいきます。

 【今回の目標到達点】

 既存データから新しいマスタテーブルを生成し結合する

 【目次】


参考資料のご紹介

 はじめに、現在、主に参考とさせていただいている書籍をご紹介します。

 「前処理大全 データ分析のためのSQL/R/Python実践テクニック」本橋智光氏著(技術評論社)


「データ結合」の概要

 参考資料「前処理大全」の「第4章 結合」冒頭で、データの結合について、著者は、以下のように述べています。

必要なデータが1つのテーブルにすべて入っていることはまれです。業務システムのデータベースは、データの種類ごとにテーブルが分かれているからです。一方、データ分析用のデータは1つのテーブルにまとまった横に長いデータが望ましく、そのようなデータを得るためにはテーブル同士を結合する処理が必要になります。

参考・参照元:第4章「結合」(p.084)より抜粋


 その上で、データの結合について、以下、3つの考え方をご紹介されています。

  • マスタテーブルから情報を取得

  • 条件に応じて結合するマスタテーブルを切り替え

  • 過去データから情報を取得


今回のテーマ

 条件に応じて結合するマスタテーブルを切り替え


概要

 参考資料「前処理大全」の「4-2 条件に応じた結合テーブルの切り替え」では、著者曰く、あまり「一般的ではない結合処理」について述べられています。


 既存データから、条件に応じて得た結果を、マスタテーブルとして生成し、データテーブルと結合するという、いままで学んできた基本処理と比べ、より実践的な印象を受ける内容です。


[SQL]

 参考資料「4-2 条件に応じた結合テーブルの切り替え」で、紹介されているSQLサンプルコードは、以下の通りです。

WITH master_table_1 AS (
    SELECT columns_list, 
    CASE WHEN some_conditions THEN true_value
    ELSE false_value END AS new_column 
    FROM data_table),

master_table_2 AS (
    SELECT columns_list_A
    FROM data_table
    UNION
    SELECT columns_list_B
    FROM data_table)

SELECT columns_list FROM data_table

INNER JOIN master_table_1 ON data_table.id = master_table_1.id
INNER JOIN master_table_2 ON master_table_1.id = master_table_2.id
AND some_conditions ;

 参考・参照元:「sql_awesome.sql」(p.097-098)を参考に作成


【補足】

項目 概要
master_table_1
master_table_2
新規作成されたマスタテーブル
columns_list SELECT文で指定するカラム
some_conditions 条件式
true_value 条件式結果がTrueの場合の値
false_value 条件式結果がFalseの場合の値
new_column 新規作成カラム
data_table データテーブル
data_table.id
master_table_1.id
master_table_2.id
結合キー


 今回のコードは、実践的な処理に近く、SQL文だけでは、概要を掴むことが難しいため、それぞれの処理に注釈を付けた概要図を作成しました。

f:id:Pimientito:20190216230325j:plain
条件別に結合するマスタテーブルを切り替える処理概要


 マスタテーブルを、既存のデータテーブルから作成し、あらためてデータテーブルと結合する処理は、参考資料のなかでも、複雑なコードになりやすいとも言及されています。そのため、可読性については、十分気を付ける必要はあるようです。


今回の学習

 「今回のテーマ」の内容を踏まえて、最初にテストデータの作成を行います。まず、どのようなデータが、今回の学習に適しているのか検討します。


テストデータの概要

条件

 以下の条件が含まれているデータが、今回の学習に適していると考えます。

  • 条件分岐を行なえる要素を含んでいること。


テストデータの選択

 上記「条件」の内容をもとに、前回【前処理の学習-26】でご紹介したアメリマサチューセッツ州 ボストン市のオープンデータ「Welcome to ANALYZE BOSTON」より「311 SERVICE REQUESTS」の問い合わせ履歴データの一部を使用して、テストデータを作成します。

f:id:Pimientito:20190128004600j:plain
「311 SERVICE REQUESTS」オープンデータセット ダウンロードページ


 なおオープンデータ「311 SERVICE REQUESTS」の詳細については、過去記事【前処理の学習-26】をご覧ください。

pimientito-handson-ml.hatenablog.com


テストデータの加工(前処理の前処理)

 前回【前処理の学習-26】で使用した「311 SERVICE REQUESTS」のデータセットは、ボストン市の公共設備や公共サービスに関する問合せ受付センター「311」へ通報された依頼や苦情(以下、リクエストと表記)の応対記録です。


 市民からの問合せに対応する部署や管理局の一覧は、下記の通りです。なお日本語訳の部分については、ボストン市のホームページ「BOSTON.GOV」の情報や、日本の行政機関を参考に、筆者にて名称を付けているため、実際の役割とは異なる場合もあります。

【部署名と問合せ件数】

id department name 部署名 件数
ANML Animal Control 動物管理局 2,618
BHA_ Boston Housing Authority 住宅管理局 183
BPD_ Boston Police Department 警察署 734
BPS_ Boston Public Schools 公立学校 358
BTDT Transportation Department 交通管理局 231,759
BWSC Water and Sewer Commission 上下水管理局 13,959
CAFF unknown(※1) 不明 1
CHT_ City Hall Truck 市役所 37
DISB Disability Commission 障がい者
生活支援局
2,289
DND_ Department of Neighborhood Development 地域生活向上局 112
GEN_ unknown(※1) 不明 11,035
HS_D Disabilities/ADA 障がい者
苦情受付局
37
HS_O Housing Office of Civil Rights 公民権 6,979
HS_V Veterans Call log 退役軍人支援局 98
HS_W Women’s Commission 女性支援局 11
HS_Y Youthline 青少年支援局 73
INFO Information Channel インフォメーション
チャンネル
76,640
ISD Inspectional Services 検査・調査局 151,818
No Q No queue assigned 未割当 104
ONS_ Office for National Statistics 統計局 304
PARK Parks 公園管理局 81,795
PROP Property Management 資産管理局 23,226
PWDx Public Works 公共事業管理局 829,727
Temp Temporary 一時受付 2

※1 該当する部署情報が、Web上から見つけることができなかったため「unknown」としています。


 問合せ件数を見ると「Public Works(以後、"公共事業管理局"と表記)」への問合せが、829,727件(問合せ全体 1,433,899件)で、全体の60%近くに達しています。今回は、公共事業管理局への問合せのみを、テストデータとして使用します。


 学習をはじめる前に、公共事業管理局の主な事業を、Webサイト「CITY of BOSTON」の「HOW PUBLIC WORKS HELPS THE CITY」から調べてみました。主に、以下の事業を担当している部署のようです。

CITY of BOSTON「HOW PUBLIC WORKS HELPS THE CITY」

www.boston.gov


【公共事業管理局の主な事業】

  • 高速道路事業

  • 建築物管理事業

  • 廃棄物削減事業

  • 街路照明事業

  • 技術事業

  • 許可申請管理事業


 道路や建築物など、ボストン市全体のインフラストラクチャ(インフラ)を中心に、担当していることが分かりました。


 続いて、公共事業管理局への問い合わせ内容の内訳を「311 SERVICE REQUESTS」のデータセットのカラム「reason(理由)」から確認します。

rank reason 理由 件数 比率
1 Sanitation 衛生管理 220,092 0.2652583
2 Street Cleaning 道路清掃 206,472 0.2488433
3 Highway Maintenance 高速道路維持 177,785 0.2142693
4 Code Enforcement 自治体警察 78,039 0.0940538
5 Street Lights 街灯 72,027 0.0868081
6 Recycling リサイクル 52,760 0.0635872
7 Administrative &
General Requests
行政に関する
要望
8,000 0.0096417
8 Employee &
General Comments
雇用に関する
コメント
6,716 0.0080942
9 Graffiti 落書き 3,006 0.0036229
10 Park Maintenance & Safety 公園維持 986 0.0011883
11 Enforcement &
Abandoned Vehicles
放置車両の取締り 933 0.0011245
12 Trees 街路樹 928 0.0011184
13 Signs & Signals 標識と信号機 688 0.0008292
14 General Request 一般的な要望 445 0.0005363
15 Abandoned Bicycle 放置自転車 423 0.0005098
16 Bridge Maintenance 橋梁維持 85 0.0001024
17 Catchbasin 下水溝 60 0.0000723
18 Sidewalk Cover / Manhole ガードレール/
マンホール
56 0.0000675
19 Traffic Management & Engineering 交通管理と設計 52 0.0000627
20 Needle Program 注射針交換
プログラム
47 0.0000566
21 Operations 運行 43 0.0000518
22 Building 建築物維持 13 0.0000157
23 Housing 住宅 12 0.0000145
24 Pothole 舗装道路整備 12 0.0000145
25 Environmental Services 環境サービス 10 0.0000121
26 Programs プログラム 8 0.0000096
27 Parking Complaints 駐車に関する
苦情
7 0.0000084
28 Boston Bikes 自転車生活向上 5 0.000006
29 Fire Hydrant 消火栓 5 0.000006
30 Animal Issues 動物の問題 3 0.0000036
31 Cemetery 墓地 2 0.0000024
32 Notification お知らせ 2 0.0000024
33 Water Issues 上下水問題 2 0.0000024
34 Alert Boston 警報 1 0.0000012
35 Health 健康 1 0.0000012
36 Test Cases テストケース 1 0.0000012


 問合せ件数上位5位までのうち、2位「道路清掃」、3位「高速道路維持」、5位「街灯」に関しては「公共事業管理局」が、Web上で掲げている主な事業に該当するものでした。


 一方、1位「衛生管理」と、4位「自治体警察」への問い合わせとは、どのような内容なのでしょうか。残念ながら、カラム「reason」のみでは、読み解くことができません。他のカラムから、もう少し詳しく調べてみます。


 次に、カラム「type」から問い合わせ内容の内訳を見てみます。

【衛生管理に関する問合せ内訳】

type タイプ 件数 比率
Schedule a Bulk Item Pickup 粗大ごみ収集予定 10,7554 0.48868
Missed Trash/Recycling/Yard Waste/Bulk Item 未収集ゴミ/リサイクル/庭ゴミ/粗大ごみ 76,179 0.34612
Schedule a Bulk Item Pickup SS 粗大ごみ収集予定 31,900 0.14494
Space Savers 収納家具 4,450 0.02022
Yardwaste Asian Longhorned Beetle Affected Area カミキリムシ被害 9 0.00004


自治体警察に関する問合せ内訳】

type タイプ count 比率
Improper Storage of Trash (Barrels) ゴミの不適切な保管 36,209 0.46399
Unshoveled Sidewalk 未整備の歩道 18,650 0.23898
Poor Conditions of Property 土地(建物)の老朽化 15,727 0.20153
Illegal Dumping 不法投棄 5,443 0.06975
Parking on Front/Back Yards (Illegal Parking) 違法駐車 756 0.00969
Construction Debris 建築ゴミ 571 0.00732
Illegal Posting of Signs 看板の違法掲示 403 0.00516
Illegal Vending 違法な販売 243 0.00311
Snow Removal 除雪 37 0.00047


テストデータを使って学習

 とても大まかですが、前項で、公共事業管理局への問合せのうち「衛生管理」と「自治体警察」に関する問い合わせ内容を見てきました。ここからは、今回学習する構文を使用して、問合せの多い地域を割り出してみます。


 はじめに、使用するデータセットの状態と、そのデータセットから、今回、どのようにデータを抽出したいのかをご説明します。


 データセット「311 SERVICE REQUESTS」のカラム「pwd_district」には、管轄の区域番号が入っています。しかし、理由は不明ですが、データセットの区域番号と、コードインデクス(定義書)で定義されている区域番号の表記形式が異なっていることが分かりました。


 そのため、人間の目では、異なる表記も「同一」と判断できる内容でも、分析を行う際、機械では「同一」と判断することができないため、事前にデータを加工する必要があります。


 実際のデータと、コードインデクスで定義されている各区域番号の対応表は、以下の通りです。

区域番号
(pwd_district)
区域番号
(コードインデクス)
区域名称
1A
1B
1C
District 1 Boston Proper & Charlestown
2
02
District 2 Jamaica Plain
3
03
District 3 North Dorchester
4
04
District 4 Brighton
5
05
District 5 South Boston
6
06
District 6 West Roxbury
7
07
District 7 South Dorcherster
8
08
District 8 Hyde Park
9
09
District 9 East Boston
10A
10B
District 10 Roxbury


 データセットのカラム「pwd_district」のデータと、コードインデクスの紐付けを行うため、コードインデクスを参考に、外部マスタテーブル「tbl_mst_pwd_location」を、事前に作成しました。マスタテーブルの内容は、以下の通りです。


【外部マスタテーブル(tbl_mst_pwd_location)】

district_id location_name
District1 Boston Proper and Charlestown
District2 Jamaica Plain
District3 North Dorchester
District4 Brighton
District5 South Boston
District6 West Roxbury
District7 South Dorchester
District8 Hyde Park
District9 East Boston
District10 Roxbury


 紐付けする処理は、今回の学習で作成するコードの中で行います。次に、区域番号の紐付け処理の流れを、以下の概要図を使用して、ご説明します。


 1.データセット「311 SERVICE REQUESTS」のカラム「pwd_district」の値をキーに、該当するコードインデクス番号を紐付けます。

f:id:Pimientito:20190224182653j:plain
コードインデクス番号の紐付け


 2.続いてデータ抽出時に、コードインデクス番号をキーにして、該当する区域名称を紐付けます。

f:id:Pimientito:20190224182852j:plain
区域名称の紐付け


 3.出力結果は「区域名称」と「問い合わせタイプ(内容)」でグループ化して、それぞれのレコード件数を算出します。

f:id:Pimientito:20190224183025j:plain
出力結果イメージ図


 以上の内容を、今回の学習を通して、実現します。


[SQL]

 はじめに参考資料の構文を参考に、以下のSQL文を作成しました。

WITH tbl_temporary AS (
        SELECT *, FIRST_VALUE(RANDOM()) OVER(PARTITION BY department) AS random_id
        FROM tbl_311_requests),

        tbl_mst_pwd_district AS (

            SELECT pwd_district,

                CASE WHEN pwd_district='1A' OR pwd_district='1B' OR pwd_district='1C' THEN 'District1'
                         WHEN pwd_district='02' OR pwd_district='2' THEN 'District2'
                         WHEN pwd_district='03' OR pwd_district='3' THEN 'District3'
                         WHEN pwd_district='04' OR pwd_district='4' THEN 'District4'
                         WHEN pwd_district='05' OR pwd_district='5' THEN 'District5'
                         WHEN pwd_district='06' OR pwd_district='6' THEN 'District6'
                         WHEN pwd_district='07' OR pwd_district='7' THEN 'District7'
                         WHEN pwd_district='08' OR pwd_district='8' THEN 'District8'
                         WHEN pwd_district='09' OR pwd_district='9' THEN 'District9'
                         WHEN pwd_district='10A' OR pwd_district='10B' THEN 'District10'
                END AS district_id

            FROM tbl_311_requests
)

SELECT location.location_name, tmp.type, count(tmp.type) FROM tbl_temporary AS tmp

INNER JOIN tbl_mst_pwd_district AS district ON tmp.pwd_district=district.pwd_district
INNER JOIN tbl_mst_pwd_location AS location ON district.district_id=location.district_id

WHERE tmp.department='PWDx' AND tmp.reason='Sanitation' AND tmp.random_id<=0.2

GROUP BY location.location_name, tmp.type ORDER BY location.location_name, tmp.type;


 SQL文のみでは、処理の流れが読みづらいため、コードに注釈を付けた図を作成しました。

f:id:Pimientito:20190224021514j:plain
今回の構文を参考に作成したSQL文の説明図


 このSQL文を実行したところ、JOIN句の影響で、中間テーブルのレコード数が、膨大に増え続け(※)、正確なデータ抽出が行なえませんでした。

 (※) データレコードが、一行しか存在しないデータでも、上記のSQL文を実行したところ、カウントされたレコード数は、100,000行を超えていました。(一意であるはずの「問合せ番号」が重複していました)


 JOIN句の特性を、よく理解できていないため、その法則性も特定できずに、データが倍々に増え続けていきました。また処理時間が長く(数時間)、かつレコード数が多い(1,433,899行)ため、なかなか、その原因を特定できず、不要な試行錯誤が続きました。


 上記の失敗から、新しいSQL文では、ほとんどの処理(JOIN句も含めて)を、WITH句の中で行い、データを抽出するSELECT文は、最小限の処理をするように作り直しました。


 またWITH句の中では、DISTINCTを使用して「問合せ番号」の重複を削除してから、主のSELECT文へデータを渡しています。


 試行錯誤の末、時間は掛かるものの(数時間)、以下のコーディングで、期待するデータを抽出することができました。

WITH tbl_temporary AS ( 
    SELECT *, FIRST_VALUE(RANDOM()) OVER(PARTITION BY department) AS random_id FROM tbl_311_requests WHERE department<>'NaN'),
    tbl_mst_pwd_district AS ( 
        SELECT pwd_district,
            CASE WHEN pwd_district='1A' OR pwd_district='1B' OR pwd_district='1C' THEN 'District1' 
                     WHEN pwd_district='02' OR pwd_district='2' THEN 'District2' 
                     WHEN pwd_district='03' OR pwd_district='3' THEN 'District3' 
                     WHEN pwd_district='04' OR pwd_district='4' THEN 'District4' 
                     WHEN pwd_district='05' OR pwd_district='5' THEN 'District5' 
                     WHEN pwd_district='06' OR pwd_district='6' THEN 'District6' 
                     WHEN pwd_district='07' OR pwd_district='7' THEN 'District7' 
                     WHEN pwd_district='08' OR pwd_district='8' THEN 'District8' 
                     WHEN pwd_district='09' OR pwd_district='9' THEN 'District9' 
                     WHEN pwd_district='10A' OR pwd_district='10B' THEN 'District10' 
            END AS district_id FROM tbl_311_requests),
    tbl_test_data AS (
        SELECT DISTINCT tmp.case_enquiry_id, tmp.department, tmp.reason, district_info.location_name, tmp.type, tmp.random_id FROM tbl_temporary AS tmp 
        INNER JOIN (tbl_mst_pwd_district AS district INNER JOIN tbl_mst_pwd_location AS location ON district.district_id=location.district_id) AS district_info ON tmp.pwd_district=district_info.pwd_district 
        WHERE tmp.department='PWDx' AND tmp.reason='Code Enforcement' 
        GROUP BY tmp.case_enquiry_id, tmp.department, tmp.reason, district_info.location_name, tmp.type, tmp.random_id ORDER BY tmp.case_enquiry_id 
    ) 
SELECT t_dt.department, t_dt.location_name, t_dt.type, count(t_dt.type) FROM tbl_test_data AS t_dt 
WHERE t_dt.random_id<=0.2 
GROUP BY t_dt.department, t_dt.location_name, t_dt.type ORDER BY t_dt.department, t_dt.location_name, t_dt.type;"


 コードの注釈は、以下の通りです。

f:id:Pimientito:20190224173052j:plain
WITH句のなかにJOIN句とWHERE句を挿入して作成したSQL文の説明図


 Pythonのコードは、以下の通りです。

[Windows7][Python][Jupyter Notebook]

#【前処理の学習-27】データを学ぶ ~結合~②
#1. 公共事業管理局への問い合わせを地域ごとに内訳

#標準モジュール
import sys

#コード実行時の履歴確認
import traceback

#日付、時間の取得
from datetime import datetime as dt

#Pandasモジュール
import pandas as pd
from pandas import DataFrame as df_result

#postgreSQL用Python DB API「psycopg」
import psycopg2

#変数の初期化
con = None

#----- SQL -----
#SELECT(ランダム・サンプリング)
sql_select_311call = \
"WITH tbl_temporary AS ( \
SELECT *, FIRST_VALUE(RANDOM()) OVER(PARTITION BY department) AS random_id FROM tbl_311_requests WHERE department<>'NaN'),\
tbl_mst_pwd_district AS ( \
SELECT pwd_district,\
CASE WHEN pwd_district='1A' OR pwd_district='1B' OR pwd_district='1C' THEN 'District1' \
WHEN pwd_district='02' OR pwd_district='2' THEN 'District2' \
WHEN pwd_district='03' OR pwd_district='3' THEN 'District3' \
WHEN pwd_district='04' OR pwd_district='4' THEN 'District4' \
WHEN pwd_district='05' OR pwd_district='5' THEN 'District5' \
WHEN pwd_district='06' OR pwd_district='6' THEN 'District6' \
WHEN pwd_district='07' OR pwd_district='7' THEN 'District7' \
WHEN pwd_district='08' OR pwd_district='8' THEN 'District8' \
WHEN pwd_district='09' OR pwd_district='9' THEN 'District9' \
WHEN pwd_district='10A' OR pwd_district='10B' THEN 'District10' \
END AS district_id FROM tbl_311_requests),\
tbl_test_data AS (\
SELECT DISTINCT tmp.case_enquiry_id, tmp.department, tmp.reason, district_info.location_name, tmp.type, tmp.random_id FROM tbl_temporary AS tmp \
INNER JOIN (tbl_mst_pwd_district AS district INNER JOIN tbl_mst_pwd_location AS location ON district.district_id=location.district_id) AS district_info \
ON tmp.pwd_district=district_info.pwd_district \
WHERE tmp.department='PWDx' AND tmp.reason='Code Enforcement' \
GROUP BY tmp.case_enquiry_id, tmp.department, tmp.reason, district_info.location_name, tmp.type, tmp.random_id ORDER BY tmp.case_enquiry_id \
) \
SELECT t_dt.department, t_dt.location_name, t_dt.type, count(t_dt.type) FROM tbl_test_data AS t_dt \
WHERE t_dt.random_id<=0.2 \
GROUP BY t_dt.department, t_dt.location_name, t_dt.type ORDER BY t_dt.department, t_dt.location_name, t_dt.type;"


#データベース接続パラメータ
#passwordは、伏字(*)に変換しています。
db_connection_parameter = "host='localhost' dbname='pimientito_ml' \
                            user='pimientito' password='*****'"

try:
    #PostgreSQLへ接続
    with psycopg2.connect(db_connection_parameter) as con:
        con.autocommit = False
        
        #コンソールオブジェクト生成
        with con.cursor() as cur:
            
            #処理時間 計測開始
            s_time = dt.now()
            
            #SELECT文実行
            df_result = pd.read_sql_query(sql_select_311call, con)
            
            #処理時間 計測終了
            e_time = dt.now()
            
            #計測時間の表示
            print('start   time: ' + str(s_time))
            print('end     time: ' + str(e_time))
            print('process time: ' + str(e_time - s_time))

except psycopg2.DatabaseError as e:
    if con:
        con.rollback()
 
    print('Error: %s' % e)
    traceback.print_exc()
    sys.exit(1)
    
except Exception as e:
    if con:
        con.rollback()
        
    print('Exception Error: %s' % e)
    traceback.print_exc()
    sys.exit(1)
    
finally:   
    con.commit()
    if con:
        cur.close()
        con.close()
    print('処理が完了しました.')


 抽出結果は、以下の通りです。

f:id:Pimientito:20190225022830j:plain
抽出結果(ランダム・サンプリング 20%)


 比較的データ量の少ない「自治体警察」(78,039行)で、しかも、ランダム・サンプリングの抽出率は、20%に抑えても、処理時間は、約2時間半掛かりました。


 余りにも、試行錯誤の時間が長く掛かってしまったため、残念ながら「衛生管理」のデータ抽出まで、到達することができませんでした。


 今回の記事での、データ抽出は、ここまでとし、続いて「可視化」を行います。


「可視化」に挑戦


可視化の目的

 抽出したデータから、各区域の問い合わせ内容や件数を見ることで、その区域の特色(ビジネス街なのか、住宅街なのかなど)を、推測したいと思います。


[matplotlib]横棒グラフ

 今回は、matplotlibのサブプロット機能を使って、横棒グラフで、各区域の問合せ件数を並べて比較します。

[Windows7][Python][Jupyter Notebook]

#2. 区域ごとにデータを抽出

#Pandasモジュール
import pandas as pd

from pandas import DataFrame as df_BPaChr  # Boston Proper and Charlestown
from pandas import DataFrame as df_JmcPln  # Jamaica Plain
from pandas import DataFrame as df_NrtDch  # North Dorchester
from pandas import DataFrame as df_Brghtn  # Brighton
from pandas import DataFrame as df_SthBst  # South Boston
from pandas import DataFrame as df_WstRxb  # West Roxbury
from pandas import DataFrame as df_SthDch  # South Dorchester
from pandas import DataFrame as df_HydPrk  # Hyde Park
from pandas import DataFrame as df_EstBst  # East Boston
from pandas import DataFrame as df_Roxbry  # Roxbury

#区域ごとに、変数へデータを格納
df_BPaChr = df_result[(df_result['location_name']=='Boston Proper and Charlestown')]
df_JmcPln = df_result[(df_result['location_name']=='Jamaica Plain')]
df_NrtDch = df_result[(df_result['location_name']=='North Dorchester')]
df_Brghtn = df_result[(df_result['location_name']=='Brighton')]
df_SthBst = df_result[(df_result['location_name']=='South Boston')]
df_WstRxb = df_result[(df_result['location_name']=='West Roxbury')]
df_SthDch = df_result[(df_result['location_name']=='South Dorchester')]
df_HydPrk = df_result[(df_result['location_name']=='Hyde Park')]
df_EstBst = df_result[(df_result['location_name']=='East Boston')]
df_Roxbry = df_result[(df_result['location_name']=='Roxbury')]

#不要な項目(カラムを削除)
df_BPaChr.drop('department', axis='columns')
df_JmcPln.drop('department', axis='columns')
df_NrtDch.drop('department', axis='columns')
df_Brghtn.drop('department', axis='columns')
df_SthBst.drop('department', axis='columns')
df_WstRxb.drop('department', axis='columns')
df_SthDch.drop('department', axis='columns')
df_HydPrk.drop('department', axis='columns')
df_EstBst.drop('department', axis='columns')
df_Roxbry.drop('department', axis='columns')

#インデクス解除
df_BPaChr.reset_index(inplace=True)
df_JmcPln.reset_index(inplace=True)
df_NrtDch.reset_index(inplace=True)
df_Brghtn.reset_index(inplace=True)
df_SthBst.reset_index(inplace=True)
df_WstRxb.reset_index(inplace=True)
df_SthDch.reset_index(inplace=True)
df_HydPrk.reset_index(inplace=True)
df_EstBst.reset_index(inplace=True)
df_Roxbry.reset_index(inplace=True)


[Windows7][Python][Jupyter Notebook]

#3. 可視化

#Matplotlib API
import matplotlib.pyplot as plt

#Figureオブジェクト生成
fig = plt.figure()

#サブプロット設定
ax1  = fig.add_subplot(2,5,1)  # Boston Proper and Charlestown
ax2  = fig.add_subplot(2,5,2)  # Jamaica Plain
ax3  = fig.add_subplot(2,5,3)  # North Dorchester
ax4  = fig.add_subplot(2,5,4)  # Brighton
ax5  = fig.add_subplot(2,5,5)  # South Boston
ax6  = fig.add_subplot(2,5,6)  # West Roxbury
ax7  = fig.add_subplot(2,5,7)  # South Dorchester
ax8  = fig.add_subplot(2,5,8)  # Hyde Park
ax9  = fig.add_subplot(2,5,9)  # East Boston
ax10 = fig.add_subplot(2,5,10) # Roxbury

# Boston Proper and Charlestown
ax1.barh(df_BPaChr['type'], df_BPaChr['count'])
ax1.set_title('Bstn&Chrls') # タイトル(区域名称)

# Jamaica Plain
ax2.barh(df_JmcPln['type'], df_JmcPln['count'])
ax2.set_yticklabels('') # Y軸(問合せタイプ)のラベルを明示的に非表示
ax2.set_title('Jmc')    # タイトル(区域名称)

# North Dorchester
ax3.barh(df_NrtDch['type'], df_NrtDch['count'])
ax3.set_yticklabels('') # Y軸(問合せタイプ)のラベルを明示的に非表示
ax3.set_title('N_Drch') # タイトル(区域名称)

# Brighton
ax4.barh(df_Brghtn['type'], df_Brghtn['count'])
ax4.set_yticklabels('') # Y軸(問合せタイプ)のラベルを明示的に非表示
ax4.set_title('Brghtn') # タイトル(区域名称)

# South Boston
ax5.barh(df_SthBst['type'], df_SthBst['count'])
ax5.set_yticklabels('') # Y軸(問合せタイプ)のラベルを明示的に非表示
ax5.set_title('S_Bstn') # タイトル(区域名称)

# West Roxbury
ax6.barh(df_WstRxb['type'], df_WstRxb['count'])
ax6.set_title('W_Rxbry') # タイトル(区域名称)

# South Dorchester
ax7.barh(df_SthDch['type'], df_SthDch['count'])
ax7.set_yticklabels('') # Y軸(問合せタイプ)のラベルを明示的に非表示
ax7.set_title('S_Drch') # タイトル(区域名称)

# Hyde Park
ax8.barh(df_HydPrk['type'], df_HydPrk['count'])
ax8.set_yticklabels('')# Y軸(問合せタイプ)のラベルを明示的に非表示
ax8.set_title('H_Prk') # タイトル(区域名称)

# East Boston
ax9.barh(df_EstBst['type'], df_EstBst['count'])
ax9.set_yticklabels('') # Y軸(問合せタイプ)のラベルを明示的に非表示
ax9.set_title('E_Bstn') # タイトル(区域名称)

# Roxbury
ax10.barh(df_Roxbry['type'], df_Roxbry['count'])
ax10.set_yticklabels('')# Y軸(問合せタイプ)のラベルを明示的に非表示
ax10.set_title('Rxbry') # タイトル(区域名称)

#サブプロット間の距離設定
plt.subplots_adjust(wspace=0.2, hspace=0.4)
#プロット図表示
plt.show()


f:id:Pimientito:20190224233548j:plain
プロット図(横棒グラフ)


Pimientitoの推測

 「テストデータを使って学習」でも述べたとおり、今回の学習で作成したコードでは、抽出したデータの信頼性の担保を取れていないため、この可視化だけでは、断言はできませんが、以下のようなことが推測されるのではないでしょうか。


  1. ボストン中心地以外は、全般的に歩道が未整備であり、住民は不満がある。(ボストン中心地は、整備されている)

  2. ボストン市全体を通して「ゴミの不適切な保管」に問題がある。(市街清掃や、ゴミ処理場の許容量などに問題があるのか)

  3. ボストン市全体を通して、不法な売買に関する問い合わせが少ないことから、ボストン市は、比較的、他の大都市より安全なところかもしれない。


 以上が、今回の学習で得たデータをもとに、ボストン市の環境を推測してみました。


 ※ 本推測は、あくまで筆者の私見の域を超えるものではありません。


今回のまとめ

 今回の学習では、データの加工や操作といった「初歩的な問題」に、大変、悩まされました。


 参考資料の著者のように、実践に熟知している方には、それほど悩ましいことではないことかもしれませんが、今回は、参考資料のサンプルを理解するところから、非常に苦しみました。


 頭の中で、紙と鉛筆を使ってと、幾度となく読み直してみても、記載内容が頭に入らず、また、理解したつもりで、自身が扱っているデータに対して、コーディングを行うと、その理解が異なっていることに気づいたりなど、何度も、試行錯誤が続きました。


 また、実際にコーディングが終わって、データ抽出を行う際にも、上述しましたJOIN句の特性に悩まされ、一進一退が続いた学習でした。


 「機械学習」を学ぶためには、統計学が必要と思い、個人的に学習を進めていますが「データを取り扱う技術」(いまのところ、DBやSQL)に関する、いわゆる従来の「ITスキル」と呼ばれる技術も、見直さなければならないと、あらためて思わされた回でした。


 まだまだ学習が足りません。



 今回は、以上です。



【参考資料】

Pythonで学ぶデータ分析・AI・機械学習「AI-interのPython入門」~Pandas DataFrameから条件指定でのデータ抽出~

ai-inter1.com


Pythonで学ぶデータ分析・AI・機械学習「AI-interのPython入門」~Pandas DataFrameへのインデックス追加と削除~

ai-inter1.com




本ブログに関するお問い合わせ先

 筆者自身の「機械学習」の学習のため、参考にさせていただいている資料や、Web情報に対して、情報元の権利を侵害しないよう、できる限り、細心の注意を払って、ブログの更新に努めておりますが、万が一、関係各所の方々に、不利益が生じる恐れがある場合、大変お手数ですが、下記の「お問い合わせ先」まで、ご一報いただけますようお願いいたします。


 お問い合わせ先:otoiawase@handson-t-arte.com



【前処理の学習-26】データを学ぶ ~結合~①

 前回までは、六回に渡りデータの「集約」について学びました。

pimientito-handson-ml.hatenablog.com

 今回からデータの「結合」について学んでいきます。


 【今回の目標到達点】

 データテーブルとマスタテーブルの結合を学ぶ


 【目次】


参考資料のご紹介

 はじめに、現在、主に参考とさせていただいている書籍をご紹介します。

 「前処理大全 データ分析のためのSQL/R/Python実践テクニック」本橋智光氏著(技術評論社)


「データ結合」の概要

 参考資料「前処理大全」の「第4章 結合」冒頭で、データの結合について、著者は、以下のように述べています。

必要なデータが1つのテーブルにすべて入っていることはまれです。業務システムのデータベースは、データの種類ごとにテーブルが分かれているからです。一方、データ分析用のデータは1つのテーブルにまとまった横に長いデータが望ましく、そのようなデータを得るためにはテーブル同士を結合する処理が必要になります。

参考・参照元:第4章「結合」(p.084)より抜粋


 その上で、データの結合について、以下、3つの考え方をご紹介されています。

  • マスタテーブルから情報を取得

  • 条件に応じて結合するマスタテーブルを切り替え

  • 過去データから情報を取得


今回のテーマ

 マスタテーブルから情報を取得する

概要

 参考資料「前処理大全」の「4-1 マスタテーブルの結合」で、著者は、テーブルの結合の中で、データテーブルとマスタテーブルの結合が、最も頻繁に行われると述べています。


 またテーブル結合の注意点として、結合するテーブルの大きさは、事前に、できる限り小さくし、メモリやCPUなど、リソースの消費を抑えることを勧めています。


 注意点をふまえて、著者が述べるテーブル結合の「好ましい例」と「好ましくない例」を、簡単なイメージ図にしました。


 【好ましい例】

 事前に、抽出する条件でレコードを絞り込み、必要なレコードのみを結合します。

f:id:Pimientito:20190127124441j:plain
テーブルの結合【好ましい例】


 理由:事前にフィルタリング(データの絞り込み)を行っておくことで、その後の処理で、メモリやCPUのリソース消費や、処理時間を抑えることができる。


 【好ましくない例】

 テーブルを結合したあとに、条件に合ったデータを検索(抽出)します。

f:id:Pimientito:20190127124516j:plain
テーブルの結合【好ましくない例】


 理由:すべてのデータを結合しているため、メモリやCPUなどのリソース消費が大きく、またデータ検索(抽出)においても、不要なデータが多いため、処理時間に影響する。


 なお参考資料では「ホテルの予約テーブル」と「ホテルマスタテーブル」を例に挙げており、予約テーブルとマスターテーブルを、それぞれ事前にフィルタリングしてから、必要なレコードのみ結合する手順をご紹介されています。


[SQL]マスタテーブルの結合

 参考資料「4-1 マスタテーブルの結合」で、紹介されているSQLコードは、以下の通りです。


 SELECT columns_list FROM data_table JOIN master_table ON data_table.columns = master_table.columns WHERE some_conditions

 参考・参照元:「sql_2_awesome.sql」(p.090)を参考に作成


【補足】

項目 概要
columns_list SELECT文で指定するカラム
data_table データテーブル
master_table マスタテーブル
data_table.columns
master_table.columns
テーブル結合共通カラム
some_conditions データ検索(抽出)条件


[Python]マスタテーブルの結合

 Pythonコードでは、Pandasライブラリのmerge関数を使用したサンプルが、ご紹介されています。構文は、以下の通りです。


pd.merge(data_table.query(some_conditions), master_table.query(some_conditions), on=join_key_columns, how=join_type)

 参考・参照元:「python_2_awesome.sql」(p.093)を参考に作成


 【補足】

項目 概要
data_table DataFrame型変数
master_table DataFrame型変数
some_conditions データ検索(抽出)条件
join_key_columns テーブル結合共通カラム
join_type 結合タイプ(※1)

(※1) 'inner', 'left', 'right', 'outer'を指定(CROSS JOINは、サポート外)


 なお参考資料によると、Pythonのデータ処理は、イン・メモリ(オン・メモリ)であり、かつmerge関数は、中間データが膨れ上がりやすいため、データサイズが大きい場合は、SQLの使用を推奨されています。


今回の学習

 「今回のテーマ」の内容を踏まえて、最初にテストデータの作成を行います。まず、どのようなデータが、今回の学習に適しているのか検討します。


テストデータの概要

条件

 以下の条件が含まれているデータが、今回の学習に適していると考えます。

  • 複数のテーブルに分割することができること。

  • マスタテーブルが作成できる要素を含んでいること。


テストデータの選択

 上記「条件」の内容をもとに、今回はアメリマサチューセッツ州 ボストン市のオープンデータ「Welcome to ANALYZE BOSTON」より「311 SERVICE REQUESTS」の問い合わせ履歴データを使用して、テストデータを作成します。


f:id:Pimientito:20190128004251j:plain
「Welcome to ANALYZE BOSTON」トップページ


f:id:Pimientito:20190128004600j:plain
「311 SERVICE REQUESTS」オープンデータセット ダウンロードページ


 「311 SERVICE REQUESTS」データセットの概要を、簡単にご説明します。


 このデータセットは、ボストン市の公共設備や公共サービスに関する問合せ受付センター「311」へ通報された依頼や苦情(以下、リクエストと表記)の応対記録です。


 リクエストの内容は、道路などインフラ設備の不備の指摘や、木々の伐採依頼、近隣の騒音や、ごみ処理の苦情、動物の死骸撤去依頼など「非緊急性」のリクエストが中心となります。


 

f:id:Pimientito:20190128012056j:plain
「BOS:311」トップページ


 データセットの詳細は、以下の通りです。

 なおオリジナルデータでは、英語略称でカラム名が作成されており、その内容を理解するため、データセットに付属された定義資料「datadictionary-crmvaluecodeindex.pdf」を読み解きながら、できる限り正確な日本語訳に努めましたが、一部の情報については、資料に掲載されておらず、また日本語に直訳すると意味を成さないため、データの内容を見ながら、適宜、日本語に訳しています。


項目 内容
取得期間 2011/07/01 01:32:33 ~
2019/01/25 23:53:00
レコード数 1,433,899


No. カラム名 日本語訳 欠測数 欠測率(%)
1 case_enquiry_id リクエストID 0 0
2 open_dt 受付日 0 0
3 target_dt 対応予定期限 264,120 18
4 closed_dt 完了日 113,381 8
5 ontime 対応実績 673 1未満
6 case_status 状態 0 0
7 closure_reason 完了理由 113,102 8
8 case_title 主題 532 1未満
9 subject 副題 0 0
10 reason リクエスト概要 0 0
11 type リクエスト詳細 0 0
12 queue 対応内容 0 0
13 department 部署 0 0
14 submittedphoto 画像 1,093,761 76
15 closedphoto 完了画像 1,234,712 86
16 location 住所 47,998 3
17 fire_district 消防地区 52,564 4
18 pwd_district 公共事業地区 49,827 3
19 city_council_district 市議会地区 48,230 3
20 police_district 警察地区 49,593 3
21 neighborhood 近郊サービス
地区名
49,491 3
22 neighborhood_services_district 近郊サービス
地区番号
48,229 3
23 ward リクエスト元
地区番号
48,179 3
24 precinct 番地 53,170 4
25 location_street_name 路地名 48,004 3
26 location_zipcode 郵便番号 340,766 24
27 latitude 緯度 0 0
28 longitude 経度 0 0
29 source リクエストツール 0 0


 過去の記事で扱った数々のデータには「欠測値(欠損値)'NaN'」は存在していませんでしたが、今回のデータセットには、欠測率は低いものの、複数の項目に欠測値が含まれていました。


 資料などを片手に、欠測値データの性質を確認し、今回は、難しい数式(代入法)を用いずに、学習が進められそうです。判断理由は、以下の通りです。


 判断理由

  1. 欠測しているデータが「ID」や「画像へのリンク」の類のため。

  2. 一部を除き、欠測している比率が、各要素で5%未満程度のため。


 「欠測値」の処理は、いったん保留になり、ホッとしたのも束の間、今回のデータセットでは、いくつかの項目で「揺らぎ」も存在しました。


 「揺らぎ」を持つデータとは、同じ値にも関わらず、表記(フォーマット)が異なることで、別の値と評価されてしまうデータのことを指します。今回のデータセットに存在する「揺らぎ」の一例を、以下に記載します。


公共事業地区 公共事業地区 警察地区 警察地区
1A 6 A1 D4
1B 06 A7 D-4
1C 7 A-7 D14
2 07 A15 D-14
02 8 A-15 E5
3 08 B2 E-5
03 9 B-2 E13
4 09 B3 E-13
04 10A B-3 E18
5 10B C6 E-18
05 C-6
C11
C-11


 人間の目では「同じデータ(意味)だろう。」と思えるものでも、コンピュータには「別のデータ」に見えてしまいます。そのため、コンピュータに理解できるようにデータを整備する必要があります。


 分析に適したデータへ整備することを「データクレンジング」と呼び、今回のデータセットでも、分析する内容によって、適宜、データクレンジングを行なう必要があります。


 なお今回のデータセットには「欠測値」「揺らぎ」のほかに、付属資料「datadictionary-crmvaluecodeindex.pdf」には定義されていない項目や値が含まれていることもあり、データ分析初学者には、なかなかハードルの高いデータセットを引き当ててしまいました。


 しかし、ボストン市の公共サービスに関する情報は、wikipediaなどでも、丁寧に説明されていることもあり、比較的、データの補完はしやすい印象です。


 次の項目から、実際に今回の学習用テストデータを作成します。


テストデータの加工(前処理の前処理)

CSVデータの読み込み

 1.WebからダウンロードしたCSV「311 SERVICE REQUESTS」を読み込みます。

[Windows7][Python][Jupyter Notebook]

#【前処理の学習-26】データを学ぶ ~結合~①

#1.サンプルデータの読み込み

#Pandasモジュール
import pandas as pd
from pandas import DataFrame as df_sample_data

#CSVファイルの読み込み(Boston 311 Service Requests)
df_sample_data = pd.read_csv("./data/lesson26_sample_data.csv", encoding='utf-8')


 2.続いて読み込んだデータセットを格納するDBテーブル「tbl_311_requests」を定義します。

[Windows7][Python/SQL][Jupyter Notebook]

#2.DBテーブル作成

#標準モジュール
import sys

#コード実行時の履歴確認
import traceback

#Pandasモジュール
import pandas as pd

#postgreSQL用Python DB API「psycopg」
import psycopg2

#変数の初期化
con = None

#----- パラメータ -----
#DBテーブル名
tbl_name = 'tbl_311_requests'

#CREATE文(カラムリスト 29カラム)
clmn_list_create = 'case_enquiry_id varchar, open_dt varchar, target_dt varchar, \
                    closed_dt varchar, ontime varchar, case_status varchar, closure_reason varchar, \
                    case_title varchar, subject varchar, reason varchar, type varchar, queue varchar, \
                    department varchar, submittedphoto varchar, closedphoto varchar, location varchar, \
                    fire_district varchar, pwd_district varchar, city_council_district varchar, \
                    police_district varchar, neighborhood varchar, neighborhood_services_district varchar, \
                    ward varchar, precinct varchar, location_street_name varchar, location_zipcode varchar, \
                    latitude varchar, longitude varchar, source varchar, '

#CREATE文(主キー)
clmn_P_Key = 'case_enquiry_id'

#----- SQL -----
#CREATE TABLE
sql_create = 'CREATE TABLE ' + tbl_name + ' (' + clmn_list_create + ' PRIMARY KEY(' + clmn_P_Key + '));'

#データベース接続パラメータ
#passwordは、伏字(*)に変換しています。
db_connection_parameter = "host='localhost' dbname='pimientito_ml' user='pimientito' password='*****'"

try:
    #DBテーブル作成(テーブル作成後、コメントアウト)
    with psycopg2.connect(db_connection_parameter) as con:
        con.autocommit = False
        
        #コンソールオブジェクト生成
        with con.cursor() as cur:

            #テストテーブル作成
            cur.execute(sql_create)
            con.commit()

except psycopg2.DatabaseError as e:
    if con:
        con.rollback()
 
    print('Error: %s' % e)
    traceback.print_exc()
    sys.exit(1)
    
except Exception as e:
    if con:
        con.rollback()
        
    print('Exception Error: %s' % e)
    traceback.print_exc()
    sys.exit(1)
    
finally:   
    con.commit()
    if con:
        cur.close()
        con.close()

    print('処理が完了しました.')


 CSVファイルから読み込んだデータセットには、日付や座標などの数値データが存在しますが、筆者は、DBへ取込む際には、すべて文字列型(varchar)で格納し、データを使用する際、必要に応じて型変換を行っています。


 事前にデータ型を定義する方法と、使用時に型変換を行なう方法の、どちらが最善なのかは、今後の学習を進めていくなかで、学んでいきたいと思います。


 3.定義したDBテーブルへ、CSVファイルから読み込んだデータセットを挿入します。

[Windows7][Python/SQL][Jupyter Notebook]

#3.サンプルデータ挿入

#標準モジュール
import sys

#コード実行時の履歴確認
import traceback

#Pandasモジュール
import pandas as pd

#SQL INSERT文のフォーマットに合わせるために使用
from pandas import DataFrame as df_cast_data

#postgreSQL用Python DB API「psycopg」
import psycopg2

#変数の初期化
con = None

#----- パラメータ -----
#DBテーブル名
tbl_name = 'tbl_311_requests'

#----- SQL -----
#INSERT
sql_insert = 'INSERT INTO ' + tbl_name + ' VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,\
                                                    %s,%s,%s,%s,%s,%s,%s,%s,%s,%s,\
                                                    %s,%s,%s,%s,%s,%s,%s,%s,%s) '

#データベース接続パラメータ
#passwordは、伏字(*)に変換しています。
db_connection_parameter = "host='localhost' dbname='pimientito_ml' user='pimientito' password='*****'"

try:

    #DBテーブルへデータ挿入
    with psycopg2.connect(db_connection_parameter) as con:
        con.autocommit = False

        #コンソールオブジェクト生成
        with con.cursor() as cur:
            
            #リスト型配列
            arry_row = []
            
            #DataFrameのデータを抽出
            for row in df_sample_data.values:
                
                #抽出したデータを、DataFrame型(一行単位)に変換
                df_cast_data = (row[0], row[1], row[2], row[3], row[4], \
                                row[5], row[6], row[7], row[8], row[9], \
                                row[10], row[11], row[12], row[13], row[14], \
                                row[15], row[16], row[17], row[18], row[19], \
                                row[20], row[21], row[22], row[23], row[24], \
                                row[25], row[26], row[27], row[28])
                
                #リスト型配列arry_rowへ追加
                arry_row.append(df_cast_data)

            #DBテーブルへデータを挿入
            cur.executemany(sql_insert,arry_row)
            con.commit()

except psycopg2.DatabaseError as e:
    if con:
        con.rollback()
 
    print('Error: %s' % e)
    traceback.print_exc()
    sys.exit(1)
    
except Exception as e:
    if con:
        con.rollback()
        
    print('Exception Error: %s' % e)
    traceback.print_exc()
    sys.exit(1)
    
finally:   
    con.commit()
    if con:
        cur.close()
        con.close()

    print('処理が完了しました.')


 4.今回は、市民からのリクエストに対応する「部署」のマスタテーブル「tbl_departments」を作成します。テーブル構成は、以下の通りです。

id name
ANML Animal Control
BHA_ Boston Housing Authority
BPD_ Boston Police Department
BPS_ Boston Public Schools
BTDT Transportation Department
BWSC Water and Sewer Commission
CAFF unknown
CHT_ City Hall Truck
DISB Disability Commission
DND_ Department of Neighborhood Development
GEN_ unknown
HS_D Disabilities/ADA
HS_O Housing Office of Civil Rights
HS_V Veterans Call log
HS_W Women’s Commission
HS_Y Youthline
INFO Information Channel(Not a department)
ISD Inspectional Services
No Q No queue assigned(Not a department)
ONS_ Office for National Statistics
PARK Parks
PROP Property Management
PWDx Public Works
Temp Temporary


 コードインデクスに記載されていない部署があり、Webなどで調べた結果、不明な部署名については「unkown」としました。


 「departments」テーブルに挿入するデータは、事前にCSVファイルを作成し読み込ませます。具体的なコーディングは、以下の通りです。

[Windows7][Python/SQL][Jupyter Notebook]

#4.マスタテーブル「Department」の作成

#標準モジュール
import sys

#コード実行時の履歴確認
import traceback

#Pandasモジュール
import pandas as pd
from pandas import DataFrame as df_csv_data

#SQL INSERT文のフォーマットに合わせるために使用
from pandas import DataFrame as df_cast_data

#postgreSQL用Python DB API「psycopg」
import psycopg2

#変数の初期化
con = None

#----- パラメータ -----
#DBテーブル名
tbl_name = 'tbl_departments'

#CREATE文(カラムリスト 29カラム)
clmn_list_create = 'id varchar, name varchar, '

#CREATE文(主キー)
clmn_P_Key = 'id'

#----- SQL -----
#CREATE TABLE
sql_create = 'CREATE TABLE ' + tbl_name + ' (' + clmn_list_create + ' PRIMARY KEY(' + clmn_P_Key + '));'

#INSERT
sql_insert = 'INSERT INTO ' + tbl_name + ' VALUES (%s,%s) '

#データベース接続パラメータ
#passwordは、伏字(*)に変換しています。
db_connection_parameter = "host='localhost' dbname='pimientito_ml' user='pimientito' password='*****'"


#CSVファイルの読み込み(CSVの1行目(0番目)は、カラム名のため、スキップします)
df_csv_data = pd.read_csv("./data/lesson26_departments_data.csv", encoding='utf-8', skiprows=0)

try:
    #DBテーブル作成(テーブル作成後、コメントアウト)
    with psycopg2.connect(db_connection_parameter) as con:
        con.autocommit = False
        
        #コンソールオブジェクト生成
        with con.cursor() as cur:

            #テストテーブル作成
            cur.execute(sql_create)
            con.commit()
            

            #リスト型配列
            arry_row = []
            
            #DataFrameのデータを抽出
            for row in df_csv_data.values:
                
                #抽出したデータを、DataFrame型(一行単位)に変換
                df_cast_data = (row[0], row[1])
                
                #リスト型配列arry_rowへ追加
                arry_row.append(df_cast_data)

            #DBテーブルへデータを挿入
            cur.executemany(sql_insert,arry_row)
            con.commit()

            

except psycopg2.DatabaseError as e:
    if con:
        con.rollback()
 
    print('Error: %s' % e)
    traceback.print_exc()
    sys.exit(1)
    
except Exception as e:
    if con:
        con.rollback()
        
    print('Exception Error: %s' % e)
    traceback.print_exc()
    sys.exit(1)
    
finally:   
    con.commit()
    if con:
        cur.close()
        con.close()

    print('処理が完了しました.')


 以上で、今回のテストデータ作成は完了しました。続いて、テストデータを使って学習を進めます。


テストデータを使って学習

 今回のテストデータでは、前述した「欠測値」「揺らぎ」を含まない要素を使い、SQLで学習します。最終的に抽出するデータの構成は、以下の通りです。


カラム名 テーブル
department tbl_311_requests.department
tbl_departments.id
name tbl_department.name
source tbl_311_requests
count count(request) group by department


[SQL]マスタテーブルの結合

 マスタテーブルの結合を行う前に、抽出する対象を、以下の条件で絞り込みます。

  • 311への問い合わせのうち、部署ごとの問い合わせ件数の上位3位までを抽出する。


 データ件数のランキング付けについては、前回学習しました。その時に学んだRANK( )関数を使って、上位3位以内の部署を確認します。

pimientito-handson-ml.hatenablog.com


 SQL文は、以下の通りです。(例外処理の部分は割愛しています)

[Windows7][Python/SQL][Jupyter Notebook]

#5. リクエスト(問合せ)件数の上位部署を確認

#標準モジュール
import sys

#コード実行時の履歴確認
import traceback

#Pandasモジュール
import pandas as pd
from pandas import DataFrame as df_result

#postgreSQL用Python DB API「psycopg」
import psycopg2

#変数の初期化
con = None

#----- パラメータ -----
#DBテーブル名
tbl_name = 'tbl_311_requests'

#----- SQL -----
#SELECT
sql_select_ranking = 'SELECT department, COUNT(*) AS requests_count, \
                        RANK() OVER (ORDER BY COUNT(*) DESC) AS ranking \
                        FROM tbl_311_requests GROUP BY department;'

#データベース接続パラメータ
#passwordは、伏字(*)に変換しています。
db_connection_parameter = "host='localhost' dbname='pimientito_ml' \
                            user='pimientito' password='*****'"

try:
    #PostgreSQLへ接続
    with psycopg2.connect(db_connection_parameter) as con:
        con.autocommit = False
        
        #コンソールオブジェクト生成
        with con.cursor() as cur:

            #SELECT文実行
            df_result = pd.read_sql_query(sql_select_ranking, con)


 ランキング結果を確認します。

#結果の確認
df_result


f:id:Pimientito:20190203221834j:plain
部署ごとの問い合わせ件数ランキング


 ランキング結果を確認したところ、以下の部署の問い合わせ件数が多いことが分かりました。

部署ID 部署名 リクエスト件数 全体比率(%)
PWDx Public Works 829,727 58
BTDT Transportation Dapartment 231,759 16
ISD Inspectional Services 151,818 11


 上位3部署のみで、リクエスト全体(1,433,899件)の85%を占めることが分かりました。次に、この3部署のリクエストを、それぞれ抽出します。


 なお、それぞれデータの件数が多いため、今回学習するSQL文に、以前学習した「ランダム・サンプリング」の手法を組み込み、各リクエストの20%のデータを抽出することにします。


 ランダム・サンプリングの学習については、下記の記事をご覧ください。

pimientito-handson-ml.hatenablog.com


 今回の学習「マスタテーブルの結合」を行いつつ、各部署へのリクエストのうち、20%のランダム・サンプリングを行うコードは、以下の通りです。(例外処理の部分は割愛しています)

[Windows7][Python/SQL][Jupyter Notebook]

#6. マスタテーブルとの結合 & ランダム・サンプリング

#標準モジュール
import sys

#コード実行時の履歴確認
import traceback

#Pandasモジュール
import pandas as pd
from pandas import DataFrame as df_result_PWDx
from pandas import DataFrame as df_result_BTDT
from pandas import DataFrame as df_result_ISD

#postgreSQL用Python DB API「psycopg」
import psycopg2

#変数の初期化
con = None

#----- パラメータ -----
#DBテーブル名
tbl_name = 'tbl_311_requests'

#----- SQL -----
#SELECT
sql_select_PWDx = 'WITH tbl_temporary as (SELECT * , FIRST_VALUE(RANDOM()) \
                    OVER (PARTITION BY department ORDER BY department) AS clmn_random_id \
                    FROM tbl_311_requests) SELECT tmp.department, dep.name, tmp.source, \
                    count(*) FROM tbl_temporary AS tmp JOIN tbl_departments AS dep \
                    ON tmp.department = dep.id WHERE tmp.department=\'PWDx\' \
                    AND tmp.clmn_random_id <= 0.2 GROUP BY tmp.department, dep.name, tmp.source \
                    ORDER BY tmp.department, tmp.source;'

sql_select_BTDT = 'WITH tbl_temporary as (SELECT * , FIRST_VALUE(RANDOM()) \
                    OVER (PARTITION BY department ORDER BY department) AS clmn_random_id \
                    FROM tbl_311_requests) SELECT tmp.department, dep.name, tmp.source, \
                    count(*) FROM tbl_temporary AS tmp JOIN tbl_departments AS dep \
                    ON tmp.department = dep.id WHERE tmp.department=\'BTDT\' \
                    AND tmp.clmn_random_id <= 0.2 GROUP BY tmp.department, dep.name, tmp.source \
                    ORDER BY tmp.department, tmp.source;'

sql_select_ISD = 'WITH tbl_temporary as (SELECT * , FIRST_VALUE(RANDOM()) \
                    OVER (PARTITION BY department ORDER BY department) AS clmn_random_id \
                    FROM tbl_311_requests) SELECT tmp.department, dep.name, tmp.source, \
                    count(*) FROM tbl_temporary AS tmp JOIN tbl_departments AS dep \
                    ON tmp.department = dep.id WHERE tmp.department=\'ISD\' \
                    AND tmp.clmn_random_id <= 0.2 GROUP BY tmp.department, dep.name, tmp.source \
                    ORDER BY tmp.department, tmp.source;'


#データベース接続パラメータ
#passwordは、伏字(*)に変換しています。
db_connection_parameter = "host='localhost' dbname='pimientito_ml' \
                            user='pimientito' password='*****'"

try:
    #PostgreSQLへ接続
    with psycopg2.connect(db_connection_parameter) as con:
        con.autocommit = False
        
        #コンソールオブジェクト生成
        with con.cursor() as cur:

            #SELECT文実行
            df_result_PWDx = pd.read_sql_query(sql_select_PWDx, con)
            df_result_BTDT = pd.read_sql_query(sql_select_BTDT, con)
            df_result_ISD = pd.read_sql_query(sql_select_ISD, con)


 それぞれ抽出した結果は、以下の通りです。

f:id:Pimientito:20190203233102j:plain
PWDx(Public Works)の抽出結果


f:id:Pimientito:20190203233215j:plain
BTDT(Transportation Dapartment)の抽出結果


f:id:Pimientito:20190203233255j:plain
ISD(Inspectional Services)の抽出結果


 続いて、抽出したデータの可視化に挑戦します。


「可視化」に挑戦


可視化の目的

 今回抽出したデータでは、311へのリクエストが、どのようなツールを経由して、各担当部署へ通知されたのかを、可視化して理解を深めたいと思います。リクエストツールは、以下の一覧の通りです。


source
Citizens Connect App
City Worker App
Constituent Call
Employee Generated
Maximo Integration
Self Service
Twitter


[matplotlib]円グラフで可視化

 今回は、matplotlibの「Basic Pie Chart」ツールを使用します。はじめに、PWDx(Public Works)のリクエストツールごとのリクエスト件数を見てみます。

[Windows7][Python][Jupyter Notebook]

#7. 可視化 PWDx(Public Works)リクエストツールごとのリクエスト数内訳
#参考・参照元:matplotlib example 「Basic pie chart」をもとに作成

#matplotlibモジュール
import matplotlib.pyplot as plt

#ラベルの設定
labels = df_result_PWDx['source']

#リクエスト件数の設定
sizes = df_result_PWDx['count']

#円グラフ要素の距離設定
explode = (0, 0, 0, 0.2, 0.2, 0.2, 0.4)

fig1, ax1 = plt.subplots()

ax1.pie(sizes, explode=explode, labels=labels, \
        autopct='%1.1f%%', shadow=True, startangle=90)

ax1.axis('equal')


 PWDx(Public Works)の可視化の結果は、以下の通りです。

f:id:Pimientito:20190204003701j:plain
可視化の結果(PWDx(Public Works))


 以下、BTDT(Transportation Dapartment)と、ISD(Inspectional Services)のコードは、設定値が異なる部分のみ抜粋して記載します。


 BTDT(Transportation Dapartment)の場合

[Windows7][Python][Jupyter Notebook]

#ラベルの設定
labels = df_result_BTDT['source']

#リクエスト件数の設定
sizes = df_result_BTDT['count']

#円グラフ要素の距離設定
explode = (0, 0, 0, 0.1, 0.3, 0.5, 0.7)


f:id:Pimientito:20190204004136j:plain
可視化の結果(BTDT(Transportation Dapartment))


 ISD(Inspectional Services)の場合

[Windows7][Python][Jupyter Notebook]

#ラベルの設定
labels = df_result_ISD['source']

#リクエスト件数の設定
sizes = df_result_ISD['count']

#円グラフ要素の距離設定
explode = (0.6, 0.4, 0, 0, 0.4, 0.6, 0.8)


f:id:Pimientito:20190204004328j:plain
可視化の結果(ISD(Inspectional Services))


 隣接する要素が重なって、図として見づらい場合、変数explodeで、互いの距離を微調整します。


 可視化することで、今回のデータから、以下のような推測が導かれるのではないでしょうか。

  • ボストン市提供の専用アプリが浸透しており、十分な成果を上げている。

  • コールセンターの役割も、非常に重要で、問い合わせ内容によっては、まだまだ電話による応対が必要である。(人的リソースの確保は重要で、雇用機会が一定数保たれているかもしれない)

  • 問合せや苦情に関する分野において、Twitterは、あまり有効的な手段ではない。


 ※ 本推測は、あくまで筆者の私見の域を超えるものではありません。より正確な分析をするためには、使用しているデータの正規分布を取ったり、母集団(1,433,899レコード)から、妥当なサンプリングを行うなど、広範囲な統計学の知識や、それに伴う確率論などの学習が必須です。また検定と呼ばれるスキルも身に付けなければ、推測の妥当性を立証することができません。


今回のまとめ

 今回の学習では、データテーブルと、マスタテーブルの結合について学びました。


 データベースを業務で利用する場合、多くのデータテーブルや、マスタテーブルを、複雑に結合して、必要なデータを生成するということは、頻繁に行われると思います。


 参考資料のなかで、著者も言及していましたが、アウトプットのデータに合わせて、コーディングを行なってしまうと、無為に不要な処理を多く含んでしまう恐れがあるため、できる限りデータ生成の工程を整理し、最短で、効率的な手順を検討しなければならないことを、あらためて考えさせられた学習でした。


 効率的な処理やコーディングを行うには、まだまだ広範囲に学習が必要です。



 今回は、以上です。



【参考資料】

Welcome to ANALYZE BOSTON

data.boston.gov


311 Service Requests

data.boston.gov


CRM Value Codex

data.boston.gov


matplotlib example 「Basic pie chart」

matplotlib.org




本ブログに関するお問い合わせ先

 筆者自身の「機械学習」の学習のため、参考にさせていただいている資料や、Web情報に対して、情報元の権利を侵害しないよう、できる限り、細心の注意を払って、ブログの更新に努めておりますが、万が一、関係各所の方々に、不利益が生じる恐れがある場合、大変お手数ですが、下記の「お問い合わせ先」まで、ご一報いただけますようお願いいたします。


 なお、本ブログへの誹謗・中傷や、記事内容についてのご質問やご指摘につきましては、お答えできないこともございますので、あらかじめご理解・ご了承いただけますよう、何卒よろしくお願いいたします。


 お問い合わせ先:otoiawase@handson-t-arte.com