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

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

pimientito-handson-ml.hatenablog.com

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

【今回の目標到達点】

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

【目次】

参考資料のご紹介

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

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

前処理大全[データ分析のためのSQL/R/Python実践テクニック]

「分割」の概要

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

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

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

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

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

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

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

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

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

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

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

---

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

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

---

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

今回のテーマ

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

概要

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

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

モデル

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

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

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

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

機械学習モデルイメージ図

モデルの性能評価

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

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

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

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

ホールドアウト法

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

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

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

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

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

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

予測モデル構築時の分割データ使用場面

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

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

　以下、引用します。

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

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

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

決定境界による学習不足と過学習

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

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

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

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

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

交差検証の流れ

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

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

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

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

サンプルコード[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)]})

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

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関数の概要は、以下の通りです。

train_test_split関数の説明

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

トレーニングデータ(説明変数、80%)

トレーニングデータ(目的変数、80%)

テストデータ(説明変数、20%)

テストデータ(目的変数、20%)

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

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

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

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

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

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

行番号リストの表示

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

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

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

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

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

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回目の検証データ (説明変数)の内容

交差検証のためのデータ分割 1回目

交差検証のためのデータ分割 2回目

交差検証のためのデータ分割 3回目

交差検証のためのデータ分割 4回目

交差検証のためのデータ分割 5回目

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

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

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

今回の学習

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

テストデータの概要

条件

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

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

テストデータの選択

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

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

UCI Machine Learning Repository ロゴマーク

「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']

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

「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()

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

カテゴリデータを整数値にマッピング

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

#逆マッピング用(確認のために作成)
#逆マッピング用ディクショナリの作成
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()

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

文字列型カテゴリデータへ再マッピング

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

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

　ここからは「今回のテーマ」で取り上げた手順に則って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)

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

ホールドアウト法でデータを分割した結果

ホールドアウト法でデータを分割(その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)

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

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

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

分割データの型を確認

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

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

DataFrame型一次元配列の場合

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

Numpy型一次配列の場合

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

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

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

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

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

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

行番号リストの確認

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

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

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

#設定値の確認(k_fold)
k_fold

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

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ライブラリを使ってデータを可視化します。

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

相関行列説明図

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

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

　今回のテストデータ「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()

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

相関行列を使って関係を調べる

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

今回のまとめ

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

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

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

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

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

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

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

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

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

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

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

　今回は、以上です。

【参考資料】

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

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

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

Pythonで動かして学ぶ！ あたらしい機械学習の教科書

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実践テクニック」本橋智光氏著(技術評論社)

前処理大全[データ分析のための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(年月テーブル)】

tbl_month(年月テーブル)

【tbl_customer(顧客テーブル)】

tbl_customer(顧客テーブル)

【tbl_sales(売上テーブル)】

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)]
})

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

tbl_month(年月テーブル)が生成される処理

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

dateutil.readthedocs.io

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

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

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

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

tbl_month(年月テーブル)の結合キー作成

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')

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

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"))

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

結合キー「year_month」作成結果

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

結合キー「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']

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

テーブルの全結合・データの集約の結果

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

result.fillna(0, inplace=True)

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

今回の学習

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

テストデータの概要

条件

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

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

テストデータの選択

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

「東京都 オープンデータ カタログサイト」トップページ

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

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

　はじめにダウンロードした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以上の円が内接できる)	備考
便座に背もたれがある

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

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

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

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

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

• 山手線駅名テーブル

• トイレ種別名テーブル

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

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

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

「山手線駅名」テーブル

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

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

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

トイレ名の表示

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

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

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

「トイレ種別名」テーブル

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

必要なカラムの抽出

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

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

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

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

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

カラムの抽出結果

各トイレ名の件数を確認

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

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

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

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

トイレ名種別ごとの件数

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

言葉の揺らぎを修正

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

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

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

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

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

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

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

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

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

言葉の揺らぎ(トイレ名)修正後の結果

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

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

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

東日本旅客鉄道(JR東日本)に絞り込んだ結果

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

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

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

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

言葉の揺らぎ(鉄道駅名)修正後の結果

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

#鉄道駅名の確認
tbl_STWC_JR.values

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

values属性を指定してデータを確認

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

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

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

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

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

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

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

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

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

テーブルの全結合

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

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

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

テーブルを全結合した結果

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

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

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

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

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

オープンデータの情報を結合した結果

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

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

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

ピボットテーブルの表示結果

　今回使用したオープンデータからは、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()

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

山手線各駅に設置されているバリアフリー設備の件数

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

　また山手線各駅で「だれでも〜」「多機能〜」「多目的〜」が、最低でも一件ずつカウントされていることも、この結果の信憑性は低いと考えます。(各駅で、最低でも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実践テクニック」本橋智光氏著(技術評論社)

前処理大全[データ分析のための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」のサンプルコードを参考にコードを再作成

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

サンプルデータテーブルの内容

　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)

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

sort_values関数の結果1

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

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

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

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

sort_values関数の結果2

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

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

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

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

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

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)],
})

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

サンプルデータテーブル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関数を使用した結果は、以下の通りです。

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)],
})

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

受注一覧(サンプル)

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

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

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

受注ごとに合計金額を追加した受注一覧(サンプル)

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

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

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

UserIDでグループ化した結果

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

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

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

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

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))

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

受注3回分の平均受注金額

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

rolling関数のwindow概要図

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

reset_index(drop=True)の有無による結果を比較

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

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

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

今回の学習

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

テストデータの概要

条件

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

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

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

テストデータの選択

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

「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')

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

取得した株価情報(The Walt Disney Company(DIS))

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

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

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

カラム「Date」からインデックスを解除

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

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

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

必要項目のみ取得した結果

　使用する期間を絞り込みます。今回は、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)

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

データの期間の絞り込み

　次に、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日間の株価合計は、以下の通りです。

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月分のみに絞り込んだ結果は、以下の通りです。

株価情報(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)

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

前週の平均株価の算出

　他の処理に入る前に、不要となったカラム「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のままにします。)

カラム「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']

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

前週との終値の比較結果

　以上で、前回【前処理の学習-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()

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

今回作成したプロット図

　前回【前処理の学習-28】で作成したプロット図は、以下の通りです。

【前処理の学習-28】で作成したプロット図

　前回も、今回も同じデータセットを使用しているため、全体的に同じようにプロットされていますが、今回は、前週平均株価(平均終値)のプロットも重ねてみました。

今回のまとめ

　前回、今回と二回に渡って、同じテーマをSQLとPythonで学習しました。

　作業工数の視点から見た場合、個人的な所感ですが、Pythonだけで進めるには、SQLより手間が掛かる印象が強いです。筆者のPythonの習熟度の低さを加味しても、Pythonによる作業は、ライブラリや関数を、もっと学習する必要があり、SQL以上にハードルが高いと感じました。(ただし、SQLの副問い合わせが、何重にも重なる場合においては、SQLの方が楽とも言い難いと思います)

　著者は、参考資料の中で「SQLでできるところはSQLで」と述べていらっしゃいますが、今の筆者のレベルでは、今後も引き続きSQLもPythonも学習しなければ、その違いが分からないと思っています。

　最後に「前処理大全」の著者 本橋智光氏が、執筆時のご苦労を語っていらっしゃる対談記事をご紹介して、今回の学習を終えたいと思います。

---

　 『仕事ではじめる機械学習』＆『前処理大全』著者対談

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実践テクニック」本橋智光氏著(技術評論社)

前処理大全[データ分析のための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句のコードに注釈を付けた図を作成しました。

WINDOW句の処理の流れ説明図

　また、この構文には、PARTITION BY句でグループ化された中に「フレーム」が作成されています。ここで「フレーム」についても、少し触れてみます。

ウィンドウ関数は、テーブルをウィンドウという部分集合にカットして、その中で順序づけを行なうものです。しかし、実はウィンドウの中でさらに集計範囲を細かく指定するオプション機能があります。そのオプションの集計範囲は「フレーム」と呼ばれています。

　参考・参照元：「SQL ゼロからはじめるデータベース操作」ミック氏著(翔泳社)「移動平均を算出する」(p.263)より抜粋

　WINDOW句の「パーティション」と「フレーム」の関係は、以下の通りです。

「パーティション」と「フレーム」の関係図

　「フレーム」では、集約関数の有効範囲を指定します。範囲の指定方法は「現在行から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」の株価情報を使用して、テストデータを作成します。

「Quandl」トップページ

　「Quandl」は、サブスクリプション型サービスを提供しているサイトのため、完全な「オープンデータ」ではありませんが、ユーザ登録をすることで、一部の情報が無料で利用できます。

　また、R言語や、Python、Excel用に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)のデータタイプを確認します。

株価データ(DIS)の型を確認

　PandasのDataFrame型であることが分かりました。続いてデータの内容を表示します。

株価データ(DIS)の内容

　カラム「Date」が、他のカラムより一段下に表示されています。これは「Date」のデータが、インデックスとして割り当てられているためです。その様子を、もう少し詳しく見てみます。

株価データ(DIS) インデックスの表示

　インデックスの内容が、日付になっていることが分かります。では、インデックスデータを、他の要素と同じように利用できるのか確認します。

株価データ(DIS) Shape属性の確認

　DataFrameのShape属性で確認すると、列数が12と表示されています。上述の【レコード構成】では、カラム数が13個あるため、このままでは日付データを利用できないことが分かりました。

　そこでDataFrameのreset_index関数を利用して、インデックスを他のカラムと同じレコードの要素に変換します。reset_index関数の実行結果は、以下の通りです。

reset_index関数実行後の株価データ(DIS)の内容

　0から始まるインデックスの列が新しく追加されて、「Date」カラムが、他のカラムと同じ高さで表示されていることが確認できました。先ほどと同じようにShape属性も確認してみます。

reset_index関数実行後の株価データ(DIS) Shape属性の確認

　カラム数が13個に変わっていることも確認できました。これで日付を含んだデータとして利用できます。

　次に、このデータをDBへ保存します。はじめにテストテーブルを作成するため、各カラムの型を確認します。実行結果は、以下の通りです。

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)は、以下の通りです。

抽出結果(DIS)の表示

「可視化」に挑戦

可視化の目的

　筆者は、株式投資の経験はありませんが、株式情報といえば、数々のグラフを駆使して分析されている投資家の方々の姿を思い浮かべます。

　今回は、数ある株式分析ツールの中から「ロウソク足チャート」に挑戦してみたいと思います。なお余談ですが、ロウソク足チャートは、日本発祥の分析ツールのようです。以下に、Wikiの情報の一部を抜粋します。

ローソク足は江戸時代に出羽国の本間宗久が発案し、大阪・堂島の米取引で使われたといわれている。現在は日本国内だけでなく世界中のヘッジファンドや個人投資家が、最も基本的なチャートの１つとしてローソク足チャートを利用している。

参考・参照元：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()

　プロットした結果は、以下の通りです。

プロット図(ロウソク足)

pimientitoの推測

　残念ながら、株式分析に必要な情報は、これだけでは不十分で、また今回の抽出データでも、正確な情報が取得できていません。(例えば、一週間の取引が通常5営業日ですが、祝祭日があった場合の取引日数の扱いなど)

　また、筆者に株式市場の基本知識が備わっていないため、ただ「上がった」「下がった」くらいしか判断できません。「機械学習」を続けていくことで、株式の基本知識も身に付いたら、データ分析が、より楽しいものになるかもしれません。

今回のまとめ

　今回は、過去データを集約・結合することの重要性を学びました。

　参考資料「前処理大全」の「結合」の章に入ってからは、いままで以上に、内容が複雑になり、単に構文や関数の理解だけでは追い付かない印象です。

　何度も繰り返して読む度に、その中で語られている内容を理解するためには、それ以上に参考資料外の知識が必要だと感じることも多くなりました。

　一節、一節と学習負荷が高まっていきますが、これからもコツコツと続けていきたいと思います。次回は、同じ節のPythonの処理について学習します。

　今回は、以上です。

【参考資料】

「SQL ゼロからはじめるデータベース操作」ミック氏著(翔泳社)

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実践テクニック」本橋智光氏著(技術評論社)

前処理大全[データ分析のための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文だけでは、概要を掴むことが難しいため、それぞれの処理に注釈を付けた概要図を作成しました。

条件別に結合するマスタテーブルを切り替える処理概要

　マスタテーブルを、既存のデータテーブルから作成し、あらためてデータテーブルと結合する処理は、参考資料のなかでも、複雑なコードになりやすいとも言及されています。そのため、可読性については、十分気を付ける必要はあるようです。

今回の学習

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

テストデータの概要

条件

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

• 条件分岐を行なえる要素を含んでいること。

テストデータの選択

　上記「条件」の内容をもとに、前回【前処理の学習-26】でご紹介したアメリカ マサチューセッツ州 ボストン市のオープンデータ「Welcome to ANALYZE BOSTON」より「311 SERVICE REQUESTS」の問い合わせ履歴データの一部を使用して、テストデータを作成します。

「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

　紐付けする処理は、今回の学習で作成するコードの中で行います。次に、区域番号の紐付け処理の流れを、以下の概要図を使用して、ご説明します。

　１．データセット「311 SERVICE REQUESTS」のカラム「pwd_district」の値をキーに、該当するコードインデクス番号を紐付けます。

コードインデクス番号の紐付け

　２．続いてデータ抽出時に、コードインデクス番号をキーにして、該当する区域名称を紐付けます。

区域名称の紐付け

　３．出力結果は「区域名称」と「問い合わせタイプ(内容)」でグループ化して、それぞれのレコード件数を算出します。

出力結果イメージ図

　以上の内容を、今回の学習を通して、実現します。

[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文のみでは、処理の流れが読みづらいため、コードに注釈を付けた図を作成しました。

今回の構文を参考に作成した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;"

　コードの注釈は、以下の通りです。

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('処理が完了しました.')

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

抽出結果(ランダム・サンプリング 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()

プロット図(横棒グラフ)

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実践テクニック」本橋智光氏著(技術評論社)

前処理大全[データ分析のためのSQL/R/Python実践テクニック]

「データ結合」の概要

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

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

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

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

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

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

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

今回のテーマ

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

概要

　参考資料「前処理大全」の「4-1 マスタテーブルの結合」で、著者は、テーブルの結合の中で、データテーブルとマスタテーブルの結合が、最も頻繁に行われると述べています。

　またテーブル結合の注意点として、結合するテーブルの大きさは、事前に、できる限り小さくし、メモリやCPUなど、リソースの消費を抑えることを勧めています。

　注意点をふまえて、著者が述べるテーブル結合の「好ましい例」と「好ましくない例」を、簡単なイメージ図にしました。

　【好ましい例】

　事前に、抽出する条件でレコードを絞り込み、必要なレコードのみを結合します。

テーブルの結合【好ましい例】

　理由：事前にフィルタリング(データの絞り込み)を行っておくことで、その後の処理で、メモリやCPUのリソース消費や、処理時間を抑えることができる。

　【好ましくない例】

　テーブルを結合したあとに、条件に合ったデータを検索(抽出)します。

テーブルの結合【好ましくない例】

　理由：すべてのデータを結合しているため、メモリや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」の問い合わせ履歴データを使用して、テストデータを作成します。

「Welcome to ANALYZE BOSTON」トップページ

「311 SERVICE REQUESTS」オープンデータセット ダウンロードページ

　「311 SERVICE REQUESTS」データセットの概要を、簡単にご説明します。

　このデータセットは、ボストン市の公共設備や公共サービスに関する問合せ受付センター「311」へ通報された依頼や苦情(以下、リクエストと表記)の応対記録です。

　リクエストの内容は、道路などインフラ設備の不備の指摘や、木々の伐採依頼、近隣の騒音や、ごみ処理の苦情、動物の死骸撤去依頼など「非緊急性」のリクエストが中心となります。

「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データの読み込み

　１．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')

　２．続いて読み込んだデータセットを格納する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)で格納し、データを使用する際、必要に応じて型変換を行っています。

　事前にデータ型を定義する方法と、使用時に型変換を行なう方法の、どちらが最善なのかは、今後の学習を進めていくなかで、学んでいきたいと思います。

　３．定義した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('処理が完了しました.')

　４．今回は、市民からのリクエストに対応する「部署」のマスタテーブル「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

部署ごとの問い合わせ件数ランキング

　ランキング結果を確認したところ、以下の部署の問い合わせ件数が多いことが分かりました。

部署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)

　それぞれ抽出した結果は、以下の通りです。

PWDx(Public Works)の抽出結果

BTDT(Transportation Dapartment)の抽出結果

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)の可視化の結果は、以下の通りです。

可視化の結果(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)

可視化の結果(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)

可視化の結果(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

---

【前処理の学習-25】データを学ぶ ～集約～⑥

　前回は、最頻値について学習しました。

pimientito-handson-ml.hatenablog.com

　今回も、引続きデータの「集約」について学んでいきます。

【今回の目標到達点】

　順位の算出を学ぶ

【目次】

参考資料のご紹介

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

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

前処理大全[データ分析のためのSQL/R/Python実践テクニック]

「データ集約」の概要

　参考資料「前処理大全」の「第３章 集約」では、集約について、著者は、以下のように述べています。

データ分析において、データの集約は重要な前処理の1つです。なぜなら、集約処理によってデータの価値を大きく損失せずに、分析の単位を変更できるからです。

　参考・参照元：第３章「集約」(p.052)より抜粋

今回のテーマ

概要

　参考資料「前処理大全」の「3-6 順位の算出」の冒頭で、著者は、以下のように述べています。

前処理において、まれに順位付けを利用することがあります。たとえば、対象のデータを絞る際に順位を利用したり、複雑な時系列の結合をする際に時間順に順位付けし、結合条件に利用することもできます。

　「複雑な時系列の結合をする際に時間順に順位付けし」とは、時系列に「ソート(並び替え)」することと同意なのでしょうか。筆者のDBの経験値では、残念ながら、まだまだ実感できないところです。

　著者は、データの「順位付け」(=データの並び替え)の注意点として「計算コスト(処理速度)」についても言及していますが、この「計算コスト」については、過去記事で、ある程度の検証を行ってきましたので、本記事では、割愛します。

　SQLのORDER BYの並び替えによる「計算コスト」について、以前、検証しました。よろしければ、以下の過去記事を、ご一読ください。

pimientito-handson-ml.hatenablog.com

　著者は、グループごとの並べ替えには、SQLのWINDOW句(関数)の利用が適していると述べています。WINDOW句(関数)については、以前、本ブログでも取り上げたことがありました。機能などの詳細については、以下の過去記事を、ご一読ください。

pimientito-handson-ml.hatenablog.com

　また参考資料では、SQL/R/Pythonの「順位付け関数」が紹介されています。本ブログでは、R言語については、学習していませんが、併せてご紹介します。

【順位付け関数】

言語	関数名
SQL	RANK, ROW_NUMBER
R	min_rank, row_number, dense_rank
Python	min, max, first, last, random, average

参考・参照元：第3章「集約」表3.1「順位付け関数と予約回数による順位表示」を参考に、一覧を作成

　なお、参考資料の「3-6 順位の算出」では、「時系列に番号を付与」(p.076)と「ランキング」(p.080)の二通りの手法が、ご紹介されていますが、今回の記事では「ランキング」に焦点を絞り、SQLのRANK( )関数を使用して学習を進めていきます。

[SQL]順位の算出構文例

　参考資料「3-6 順位の算出」で、紹介されているSQLコードは、以下の通りです。

　SELECT column, RANK( ) OVER (ORDER BY COUNT(*) DESC) AS new_column FROM data_table GROUP BY column

　参考・参照元：「sql_awesome.sql」（p.081）を参考に作成

　【補足】

項目	概要
column	集約対象カラム
new_column	WINDOW関数(RANK)で 作成されるカラム
data_table	データテーブル

今回の学習

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

テストデータの概要

条件

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

• グループ化したとき、各グループに複数行のデータレコードが存在すること。

• 順位付け可能な要素(カラム)が存在すること。

• 順位付け可能な要素の値が、同値または類似する値が複数存在すること。

テストデータの選択

　上記「条件」の内容をもとに、今回は、株式会社KSP-SPのオープンデータ「KSP-POS 食品スーパー新商品売れ筋ランキング」を使用して、テストデータを作成します。

株式会社KSP-SP トップページ

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

　今回の学習では、SQLも含まれているため、取得したデータは、PostgreSQLのデータテーブルに挿入します。

　はじめに、テストデータができるまでの流れを図にしました。

テストデータ作成の流れ

　また、今回「クローリング技術」と「スクレイピング技術」を使用しましたので、クローリングとスクレイピングの定義について、少し触れてみたいと思います。今回、筆者が参考にした資料「Python クローリング&スクレイピング」加藤耕太氏著(技術評論社)」によると、以下の機能を持つものと述べています。

・クローリング

　Webページのハイパーリンクをたどって次々にWebページをダウンロードする作業。

・スクレイピング

　ダウンロードしたWebページから必要な情報を抜き出す作業。

参考・参照元：Python クローリング&スクレイピング」加藤耕太氏著(技術評論社)「1.1.1 クローリングとスクレイピング」(p.3)より抜粋

　それぞれの機能を、筆者なりに理解して、イメージ図を作成しました。はじめに「クローリング」のイメージ図です。

クローリングのイメージ図

　「リンク」から「リンク」に渡って、Webページの情報をダウンロードするところまでが「クローリング」のようです。

　続いて「スクレイピング」のイメージ図です。

スクレイピングのイメージ図

　Webページ内の情報を取得するのが「スクレイピング」です。筆者が作成したサンプルコードでも「クローリング」「スクレイピング」を取り入れていますが、残念ながら「ここまでが、クローリング」「ここからは、スクレイピング」と、明確に線引きできていません。これからの学習で、より深く理解していきたいと思います。

　サンプルコードをご紹介する前に、今回のテストデータの概要をご説明します。

【テストデータの概要】

項目名	カラム名	型	概要	主キー
URL識別子	weeks	varchar	URL末尾の識別子 (データ集計週(※1))	○
商品種別	type	varchar	菓子, 酒類, 飲料, デザート, その他食品
JANCODE	jancode	varchar	JANコード	○
メーカー名	maker	varchar	販売メーカー
商品名称	product	varchar	商品名称
平均売価	mean_price	int	円/個の販売平均価格	○(※2)

　※1. 2018年1月1日～2019年1月6日までの期間を、1週間単位(第1週～第53週)で集計したデータ

　※2. 今回使用したデータのうち、同じ「JANCODE」で、異なる値のデータが、複数存在したため「URL識別子」「JANCODE」「平均売価」の組み合わせで、主キーとしました。

　続いて、以下に、筆者が作成したサンプルコードを記載します。

　【ブログ読者のみなさまへのお願い】

　クローリングやスクレイピングは、使い方によって、他の方へご迷惑をお掛けする可能性が高い技術です。誤った利用によって、情報提供元関係者のご負担や、情報開示の規制に繋がるような結果にならないよう、十分注意して、ご利用ください。

　なお、本記事のコード内では、情報提供元URLや、データ構成が特定できる部分につきましては、あらかじめ伏字で置き換えています。恐れ入りますが、ご理解のほど、何卒よろしくお願いいたします。

対象のWebページがクローリングを許可しているか確認

　はじめに、アクセス対象のWebページが、クローリングを許可しているかを確認します。

　確認方法は、対象WebページのトップページのURL後に「robots.txt」を追加すると、テキストが表示されます。(robots.txtが存在しない場合もあります)

　URLの指定例：www.example.com/robots.txt

　具体的なサンプルとして、下記に、wikipedia.orgの「robots.txt」へのリンクを貼っています。

https://www.mediawiki.org/robots.txt

　「robots.txt」とは、クローラやボットが、Webサイト全体を縦横無尽に走査しないよう、Webサイト運営側が、事前に、フォーマットに則って、特定の階層へのアクセス許可・拒否を明記しているテキストです。ロボット排除規約、robots.txtプロトコルと呼ばれています。

　なお「robots.txt」には、法的な拘束性は無いようですが、Webサイト運営者が、事前に「見られたくない」「見せたくない」と明記している情報に、故意にアクセスすることは「マナー違反」と考えますので、今回の記事から、クローリングや、スクレイピングのコードには、事前に「robots.txt」を見て、許可されている場合のみ、その後の処理を続けるように作成しています。

　「robots.txt」の詳細については、下記のページをご覧ください。

ja.wikipedia.org

　では実際に、情報提供元の「robots.txt」を確認します。

[Windows7][Python][Jupyter Notebook]

#【前処理の学習-25】データを学ぶ ～集約～⑥
#クローリング・スクレイピングで取得したデータを、PostgreSQLのテーブルへ挿入


#0. 事前確認
#robots.txtをパース(parse 解析)することで、アクセス先のWebページが、クローリングが禁止されているのかを確認する

#robots.txt分析モジュール
import urllib.robotparser

#robots.txtの所在
#情報提供元の権利保護のため、具体的な情報は伏字(*)に変換しています。
robots_txt_url='****'

#robots.txtの読み込み
rp = urllib.robotparser.RobotFileParser(robots_txt_url)
rp.read()

#リクエスト・レート(リクエスト数, アクセス間隔)の確認
rrate = rp.request_rate("*")

#戻り値がNoneの場合(設定されていない)
if rrate is None:
    print('Request_rate is None.')

else:
    print('You are allowed ' + rrate.requests + ' times requests.')
    print('You have to wait for ' + rrate.seconds + ' secands for 1 requests.')

#すべてのユーザに、クローリングが認められているかを確認
access_permission = rp.can_fetch("*", robots_txt_url)

#許可されている場合
if access_permission == True:
    print('This Web page is allowed that robots access.')

#禁止されている場合
elif access_permission == False:
    print('This Web page is prohibited that any robots access.')


#参考・参照元：Python 3.7.2 Documentation「urllib.robotparser — Parser for robots.txt」を元に作成

WebページのURLを取得

　続いて、スクレイピング対象のWebページのURLを、すべて抜き出しリスト化します。こちらも、上述したサンプルコードと同様に、情報提供元の情報は、すべて伏字(*)に変換しています。

[Windows7][Python][Jupyter Notebook]

#参考資料
#「Python クローリング&スクレイピング - データ収集・解析のための実践開発ガイド」 加藤 耕太氏著（技術評論社）

#1. 子ページURLの取得

#標準モジュール
import sys

#コード実行時の履歴確認
import traceback

#lxmlモジュール(XML and HTML with Python)
import lxml.html

#正規表現(rawリテラル)モジュール
import re

#HTTPライブラリ requestsモジュール(HTTP for Humans™)
import requests

#URL結合モジュール
from urllib.parse import urljoin

#WebトップページURL
#情報提供元の権利保護のため、具体的な情報は伏字(*)に変換しています。
root_url='*****'

#クローリング対象ページURL
#情報提供元の権利保護のため、具体的な情報は伏字(*)に変換しています。
target_url='*****'

#HTTPリクエストのヘッダーに、クローラー側情報を付与(ここでは、筆者のメールアドレス(伏字)を入力しています)
#サーバ側のアクセス・ログに記録されるため、サーバ管理者から確認ができます。
my_info = {'user-agent': '*****@*****.com'}

#list変数宣言(商品情報)
leaves_url_paths = []

#ステータスコード(処理を継続しても問題ないと判断)
GOOD_RESPONSE_STATUS_CODES =(100, 200, 301, 302, 304)

#ステータスコード(処理を中断したほうがよいと判断)
BAD_RESPONSE_STATUS_CODES = (400, 401, 403, 404, 408, 500, 502, 503, 504)


try:
    #robots.txtで、プログラムによるアクセスが許可されている場合
    if access_permission == True:
        
        #Webサイトからレスポンス(応答)情報を取得
        https_response = requests.get(target_url, headers=my_info)
    
        #HTTPステータスコードが、正常範囲内と判断できる場合のみ、以後の処理を行う
        if https_response.status_code in GOOD_RESPONSE_STATUS_CODES:
    
            #取得したコンテンツ情報を変数へ代入
            root_html_tree = lxml.html.fromstring(https_response.content)

            #コンテンツ情報から、<a>タグの情報を取得
            for item in root_html_tree.cssselect('a'):
    
                #相対パスURL取得(子ページ)
                leaves_url = item.get('href')
    
                #正規表現で、必要な相対パスURL情報のみ取得
                if re.search(r'yw', str(leaves_url)) is not None:
        
                    #相対パスから絶対パスに変換し、リスト型変数へ追加
                    leaves_url_paths.append(urljoin(root_url, leaves_url))
                
            #処理完了メッセージ
            print('proccess has success.')
    
        #HTTPステータスコードが、正常範囲外と判断できる場合は、処理を中断する
        elif https_response.status_code in BAD_RESPONSE_STATUS_CODES:
        
            #処理中断メッセージ
            print('HTTP STATUS CODE: {0}'.format(https_response.status_code))
        
            #強制終了
            print('your proccess has been interrupted.')
            sys.exit(1)
            
    #robots.txtで、プログラムによるアクセスが禁止されている場合
    elif access_permission == False:
        
        print('This Web page is prohibited that any robots access.')
        sys.exit(1)

#例外処理
except Exception as e:
    
    #エラーメッセージ
    print('Exception Error: %s' % e)
    print('Target URL: ' + url_path)
    
    #履歴のトレース
    traceback.print_exc()
    
    #強制終了
    print('your proccess has been interrupted.')
    sys.exit(1)
    
finally:
    
    #プロセス完了メッセージ
    print('your proccess has done.')  

スクレイピングでWebページのデータを取得

　取得したURLリストをもとに、各Webページから、必要なデータを取得します。

[Windows7][Python][Jupyter Notebook]

#2. 子ページから情報を取得

#標準モジュール(sleep用)
import time

#list変数宣言(商品情報)
list_weeks = []      #集計週
list_type = []       #商品種別
list_jancode = []    #JANCODE
list_maker = []      #メーカー名
list_product = []    #商品名称
list_mean_price = [] #平均売価

#定数
#--<div>タグの値--
#情報提供元の権利保護のため、具体的な情報は伏字(*)に変換しています。
tag_div_id = '*****'

#--<table class>タグの値--
#情報提供元の権利保護のため、具体的な情報は伏字(*)に変換しています。
tag_tblclass_sweets = '*****'     #「菓子」情報
tag_tblclass_alcohol = '*****'    #「酒類」情報
tag_tblclass_softdrink = '*****' #「飲料」情報
tag_tblclass_dessert = '*****'    #「デザート」情報
tag_tblclass_other = '*****'       #「その他食品」情報

#--<td>タグの値--
#情報提供元の権利保護のため、具体的な情報は伏字(*)に変換しています。
tag_td_jancode = '*****'      #JANCODE
tag_td_maker = '*****'         #メーカー名
tag_td_product = '*****'      #商品名称
tag_td_mean_price = '*****' #平均売価

#進捗カウンタ
counter = 0

try:
    
    for url_path in leaves_url_paths:
        
        #テスト用リミッタ―(テスト時以外は、コメントアウト)
        #※外部にアクセスするようなループ文を作成する場合に使用
        #if counter > 2:
        #    print('end of test')
        #    sys.exit(0)
        
        #子ページの情報を取得
        https_response = requests.get(url_path, headers=my_info)
        
        #URL末尾の識別子(集計週)を検索
        url_string = url_path
        weeks_position = url_string.rfind('=')
        
        #URL末尾の識別子(集計週)が見つかった場合
        if weeks_position != -1:
            
            #URL末尾の識別子(集計週)を切り取り
            weeks = url_string[weeks_position+1:]
        
                
        #HTTPステータスコードが、正常範囲内と判断できる場合のみ、以後の処理を行う
        if https_response.status_code in GOOD_RESPONSE_STATUS_CODES:

            #HTML要素の取得
            leaves_html_tree = lxml.html.fromstring(https_response.content)
            
            #<div>タグ情報(ランキング情報を含むブロック)
            for div_info in leaves_html_tree.cssselect('div'):
                
                if div_info.get('id') == tag_div_id:
                    
                    #<table>タグ情報(ランキング情報を含むテーブル)                    
                    for tbl_info in div_info.cssselect('table'):
                        
                        #テーブル種別
                        tbl_name = tbl_info.get('class')
                        
                        #「菓子」の場合
                        if tbl_name == tag_tblclass_sweets:
                            
                            product_type = 'sweets'
                        
                        #「酒類」の場合
                        elif tbl_name == tag_tblclass_alcohol:
                            
                            product_type = 'alchol'
                            
                        #飲料
                        elif tbl_name == tag_tblclass_softdrink:
                            
                            product_type = 'softdrink'
                            
                        #デザート
                        elif tbl_name == tag_tblclass_dessert:
                            
                            product_type = 'dessert'
                            
                        #その他食品
                        else:
                            
                            product_type = 'other'

                        #<td>タグ情報(ランキング情報を含むセル)
                        for td_info in tbl_info.cssselect('td'):
                            
                            #class属性の値を取得(データテーブルのカラム番号)
                            column_number = td_info.get('class')
                            
                            #商品情報を、各list変数へ代入
                            if column_number == tag_td_jancode:      #JANCODE
                                
                                #list変数へ追加
                                list_jancode.append(td_info.text)
                                
                                #URL識別子追加
                                list_weeks.append(weeks)
                                                                
                                #商品種別
                                list_type.append(product_type)
                                
                            elif column_number == tag_td_maker:      #メーカー名
                                
                                #list変数へ追加
                                list_maker.append(td_info.text)
                                
                            elif column_number == tag_td_product:    #商品名称
                                
                                #文字変換(全角スペースを、半角スペースに変換)
                                temporary = td_info.text
                                temporary = temporary.replace('　', ' ')
                                
                                #list変数へ追加
                                list_product.append(temporary)
                                
                            elif column_number == tag_td_mean_price: #平均売価(int型変換)
                                
                                #小数点表記の文字列があった場合、一旦、float型に変換
                                float_temporary = float(td_info.text)
                                
                                #float型を、int型に変換
                                int_temporary = int(float_temporary)
                                
                                #list変数へ追加
                                list_mean_price.append(int_temporary)
                                
                            else:  # 上記以外の情報の場合
                                
                                continue
                    
                #<div>タグ情報(ランキング情報以外のブロック)   
                else:
                    continue

        #HTTPステータスコードが、正常範囲外と判断できる場合は、処理を中断する
        elif https_response.status_code in BAD_RESPONSE_STATUS_CODES:
        
            #問題が発生したURLと、ステータスコードを表示
            print(url_path)
            print('HTTP STATUS CODE: {0}'.format(https_response.status_code))
        
            #処理を継続
            continue
    
        #進捗メッセージを表示
        counter += 1
        print('read complete file: ' + str(counter))        
            
        #時間を置いて、次のWebページへのアクセス(10秒待つ)
        time.sleep(10)


#例外処理
except Exception as e:
    
    #エラーメッセージ
    print('Exception Error: %s' % e)
    print('Target URL: ' + url_path)
    
    #履歴のトレース
    traceback.print_exc()
    
    #強制終了
    print('your proccess has been interrupted.')
    sys.exit(1)
    
    
finally:
    
    #プロセス完了メッセージ
    print('your proccess has done.')  

　以上が、Webページからデータを取得するために作成したクローリング・スクレイピングのコードです。

スクレイピングしたデータを集約

　DBテーブルへ挿入する前に、スクレイピングで取得したデータ(リスト型変数)を、DataFrame型変数に集約します。

[Windows7][Python][Jupyter Notebook]

#2.5. 取得データの集約

#Pandasモジュール
import pandas as pd
from pandas import DataFrame as df_test_data

#DataFrame作成
df_test_data = pd.DataFrame({'weeks':list_weeks, 'jancode':list_jancode, 'type':list_type, 'maker':list_maker, 'product':list_product, 'mean_price':list_mean_price})

#結果の確認
df_test_data.head(10)

　各要素のlist型変数を、DataFrame型変数に集約した結果は、以下の通りです。

list型変数をDataFrame型変数に集約した結果

テストデータをDBテーブルへ挿入

　DataFrame型変数を使って、PostgreSQLのDBテーブルへINSERTしています。いまだ、DataFrame型やlist型変数の取り扱いに不慣れでいるため、同じデータを、何度も「変換」している感が否めませんが、どうにかDBへデータを格納できました。

[Windows7][Python][Jupyter Notebook]

#3. DBテーブル作成と、データ挿入

#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_lesson25'

#CREATE文(カラムリスト)
clmn_list_create = 'weeks varchar, jancode varchar, type varchar, maker varchar, product varchar, mean_price integer,'

#CREATE文(主キー)
clmn_P_Key = 'weeks, jancode, mean_price'

#----- 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,%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:

            #テストテーブル作成
            cur.execute(sql_create)
            con.commit()

    
    #DBテーブルへデータ挿入
    with psycopg2.connect(db_connection_parameter) as con:
        con.autocommit = False

        #コンソールオブジェクト生成
        with con.cursor() as cur:
            
            #リスト型配列
            arry_row = []
            
            #DataFrameのデータを抽出
            for row in df_test_data.values:
                
                #抽出したデータを、DataFrame型(一行単位)に変換
                df_cast_data = (row[0], row[1], row[2], row[3], row[4], row[5])
                
                #リスト型配列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('処理が完了しました.')

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

select * from tbl_lesson25;の出力結果(一部)

　以上で、テストデータの作成が完了しました。続いて、今回の学習に進みます。

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

　データベースに入力したデータレコード数を確認したところ、合計で18,130行ありました。内訳は、以下の通りです。

商品種別	行数
酒類	4,891
飲料	2,650
菓子	2,650
デザート	2,639
その他食品	5,300

　これらの中には、同一の商品が入っていますが「新商品売れ筋ランキング」と銘打たれているデータのため、繰返し同一商品が、リストアップされているのでしょう。

[SQL]データの順位を算出

　今回、参考資料の構文をもとに、テストデータに沿って、若干、組み直しました。作成したSQL文は、以下の3つです。

• 商品種別ごとに、年間最頻出人気商品ランキングを算出

SELECT type, jancode, product, COUNT(*) AS appear_count, RANK() OVER(PARTITION BY type ORDER BY COUNT(*) DESC) AS ranking FROM tbl_lesson25 GROUP BY type, jancode, product;

• 商品種別ごとに、年間最頻出人気商品のメーカーランキングを算出

SELECT maker, type, COUNT(*) AS listup_count, RANK() OVER(PARTITION BY type ORDER BY count(*) DESC) AS ranking FROM tbl_lesson25 GROUP BY maker, type;

• メーカー/商品種別ごとに、平均売価総額の平均額ランキングを算出

WITH tbl_temporary AS (SELECT DISTINCT ON (jancode, mean_price) maker, type, mean_price FROM tbl_lesson25) SELECT maker, type, ROUND(AVG(mean_price)) AS mean_price, RANK() OVER (PARTITION BY type ORDER BY ROUND(AVG(mean_price)) DESC) AS ranking FROM tbl_temporary GROUP BY maker, type ORDER BY type, ranking;

　Python上で、PostgreSQLにアクセスし、上述した3つのSQL文を実行しました。サンプルコードは、以下の通りです。

[Windows7][Python/SQL][Jupyter Notebook]

#4. [SQL]DBテーブルからデータを抽出・順位の算出

#Pandasモジュール
import pandas as pd

#DataFrame
from pandas import DataFrame as df_popular_product_ranking  #人気商品ランキング(商品区分別)
from pandas import DataFrame as df_popular_maker_ranking    #人気商品メーカーランキング(商品区分別)
from pandas import DataFrame as df_maker_mean_price_ranking #売価総額の平均額ランキング(商品区分/メーカー別)

#postgreSQL用Python DB API「psycopg」
import psycopg2


#変数の初期化
con = None

#----- SQL -----
#SELECT文(商品種別ごとに、年間最頻出人気商品ランキングを算出)
sql_popular_product_ranking = 'SELECT type, jancode, product, COUNT(*) AS appear_count, \
                                RANK() OVER(PARTITION BY type ORDER BY COUNT(*) DESC) AS ranking \
                                FROM tbl_lesson25 GROUP BY type, jancode, product;'

#SELECT文(商品種別ごとに、年間最頻出人気商品のメーカーランキングを算出)
sql_popular_maker_ranking = 'SELECT maker, type, COUNT(*) AS listup_count, \
                            RANK() OVER(PARTITION BY type ORDER BY count(*) DESC) AS ranking \
                            FROM tbl_lesson25 GROUP BY maker, type;'

#SELECT文(メーカー/商品種別ごとに、平均売価総額の平均額ランキングを算出)
sql_maker_mean_price_ranking = 'WITH tbl_temporary AS \
                                (SELECT DISTINCT ON (jancode, mean_price) maker, type, mean_price \
                                FROM tbl_lesson25) SELECT maker, type, ROUND(AVG(mean_price)) AS mean_price, \
                                RANK() OVER (PARTITION BY type ORDER BY ROUND(AVG(mean_price)) DESC) AS ranking \
                                FROM tbl_temporary GROUP BY maker, type ORDER BY type, ranking;'


#データベース接続パラメータ
#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:
            
            df_popular_product_ranking = pd.read_sql_query(sql_popular_product_ranking, con)
            df_popular_maker_ranking = pd.read_sql_query(sql_popular_maker_ranking, con)            
            df_maker_mean_price_ranking = pd.read_sql_query(sql_maker_mean_price_ranking, con)
            

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('処理が完了しました.')

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

#抽出内容を確認
#商品種別ごとに、年間最頻出人気商品ランキングを算出(リストアップされた回数)
df_popular_product_ranking

商品種別ごとに年間最頻出人気商品ランキングの結果

#抽出内容の確認
#商品種別ごとに、年間最頻出人気商品のメーカーランキングを算出
df_popular_maker_ranking

商品種別ごとに年間最頻出人気商品メーカーランキングの結果

#抽出内容の確認
#メーカー/商品種別ごとに、平均売価総額の平均額ランキングを算出
df_maker_mean_price_ranking

メーカー／商品種別ごとに平均売価総額の平均額ランキングの結果

　総レコード数が多いため、本ブログ上で、画像による確認には、限界がありますが、まずまずの結果は得られていました。しかし、商品数や、メーカー数など非常に多いため「データを整理する能力」を鍛えないと、有効な資料としては、まだまだ利用することができません。ただ「データを取ってきただけ」が現状です。

　最後に、いつものように、取得したデータを可視化してみます。

「可視化」に挑戦

可視化の目的

　筆者としては、本来、ここでは各メーカーの平均売価から標準偏差や、正規分布などを算出・可視化を試みたいところですが、いまだ、書籍を漁ってみるものの「チンプンカンプン」の毎日です。。。

　ただ「知らないから、やらない」というのではなく、その時、その時で、最善を尽くしていきたいと考えているため、今回も、いつものように簡単に利用できるライブラリを使って、可視化します。

[Seaborn]棒グラフで可視化

　今回は、Seabornライブラリのbarplotを使用して可視化しました。設定内容は、以下の通りです。

パラメータ	カラム	概要
x	maker	メーカー名
y	mean_price	メーカー/商品種別でグループ化した 平均売価総額の平均値
hue(※)	type	商品種別(酒類,飲料,菓子, デザート, その他食品)

(※) hue: カテゴリ型データ

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

#6. 可視化
# メーカー/商品種別ごとに、平均売価総額の平均額を可視化

#Seabornモジュール
import seaborn as sns

#x:メーカー名, y:平均売価
sns.barplot(x='maker', y='mean_price', hue='type', data=df_maker_mean_price_ranking)

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

Seabornライブラリ barplotの表示結果

　上述しましたが、データの整理が、きちんと行えていないため、18,130行分のデータが、この小さい画面にギッシリと盛り込まれています。

　また、近くに寄って見てみると、日本語表示のメーカー名が文字化けしているように思えます。「機械学習」全般に言えることですが、可視化ツールの学習も、まだまだ先が長いです。

もう少し踏み込んで･･･

　今回は、記事のなかで、いろいろと踏み込んで調べてきましたので、本項目は、割愛しますが、一点、筆者自身の備忘録として記録します。

　リスト型変数や、DataFrame型の相互変換について、自身でも学習が足りないことは、日々感じていますが、今回の学習で、あらためて痛感したことは、Pythonコード内だけでなく、異なるアプリケーションや、ツールに、データを橋渡しするとき、どのような型の変数や、配列を使用した方が良いのか、非常に悩みました。

　pandasのDataFrame型は、とても使い勝手が良いため、ついつい頼りがちになりますが、いざDBのINSERT文のパラメータに指定すると、上手く利用できないといったことがありました。(DataFrame型配列の場合は駄目だが、一行単位でDataFrame型変数に代入した後、list型配列に代入した場合は、INSERT文のパラメータに利用できたなど)

　今後も、Pythonを介して、さまざまなシステムや、アプリケーション、データなどを利用することを考えた場合、numpyなども含めて、Pythonの仕様について、もっともっと深く理解する必要があると感じた学習でした。

今回のまとめ

　今回の学習で、参考資料の「集約」が完了しました。学習したとはいえ、空で暗記しているわけではありませんので、事あるごとに読み返し、その都度、あたらしい発見や理解があると思います。

　参考資料で、ご紹介されている内容を、すべて網羅できていませんが、いったん、次に進みたいと思います。次回から「結合」の学習をはじめます。まだまだ「機械学習」の前処理編は続きますが、今後とも、よろしくお願いいたします。

　今回は、以上です。

【参考資料】

株式会社KSP-SP「食品POSデータならKSP-POS」

www.ksp-sp.com

「Python クローリング&スクレイピング」加藤耕太氏著(技術評論社)

Pythonクローリング&スクレイピング -データ収集・解析のための実践開発ガイド-

SEO Pack「robots.txtとは？ クローラーの最適化に必要な設定方法」

seopack.jp

Python 3.7.2 Documentation「urllib.robotparser - Parser for robots.txt」

docs.python.org

一般財団法人 流通システム開発センター「GS1(ジーエスワン)事業者コード・JAN(ジャン)コード(GTIN(ジーティン))とは」

www.dsri.jp

---

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

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

　なお、本ブログへの誹謗・中傷や、記事内容についてのご質問やご指摘につきましては、お答えできないこともございますので、あらかじめご理解・ご了承いただけますよう、何卒よろしくお願いいたします。

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

---