【前処理の学習-17】データを学ぶ ～抽出～④

　前回は、ランダム関数を使用してデータを抽出するサンプリング手法について学びました。

pimientito-handson-ml.hatenablog.com

　今回も引き続きサンプリング手法について学んでいきます。

【今回の目標到達点】

• 分析対象とサンプリングデータの単位を揃えた抽出手法について学ぶ

• SQLのWINDOW句（関数）と、GROUP BY～ ORDER BY句の処理速度の違いについて学ぶ

【目次】

参考資料のご紹介

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

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

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

「データ抽出」の概要

　現在「前処理大全」第２章「抽出」で、著者の本橋智光氏（以後、「著者」と表記。また本ブログ執筆者については「筆者」と表記）が、以下のように述べている部分について学習を進めています。

適切な抽出作業は、無駄な処理を減らせたり、扱うデータサイズを小さくしたりすることができるため、重要です。

参考・参照元：第2章「抽出」より抜粋

　その上で、データの抽出方法について、以下、4種類の考え方をご紹介されています。

• データ列を指定して抽出

• 条件指定によるデータ行の抽出

• データ値に基づかないサンプリング

• 集約IDに基づくサンプリング

今回のテストテーブル

　今回使用するテストテーブルの内容は、以下の通りです。

項目	詳細
テーブル名	tbl_temperature
レコード数	572,058

カラム名	概要	型
point_number	地点番号	int
province_code	管区コード	int
number_of_observations	観測回数	int
observation_item	観測項目	int
location_name	地点名	varchar
observation_date	観測年月日	date
average_temperature	平均気温	int
highest_temperature	最高気温	int
lowest_temperature	最低気温	int

集約IDに基づくサンプリング

　参考資料　第２章　2-4「集約IDに基づくサンプリング」のなかで、著者は、偏りのあるデータ抽出は、その後の分析で誤った判断に陥る危険性があることを指摘しています。

　そのため、抽出では、分析対象の単位と、サンプリングデータの単位を、揃えることに、注視するよう述べています。なお「集約ID」とは、ある特定のものを指す言葉ではなく、先に述べた「単位」の対象となる項目（カラム）を指す代名詞のことです。

以下、該当箇所の引用です。

サンプリングにおいて、公平なサンプリングをすることは最も重要なことです。偏りのあるサンプリングをしてしまうと、そのあとの分析でこの偏りをデータによる傾向とミスリードしてしまうからです。公平なサンプリングを行うには、分析対象の単位とサンプリングする単位を揃える必要があります。

参考・参照元：第2章「抽出」2-4「集約IDに基づくサンプリング」（P.046）より抜粋

　ここに「発注ID」「会員ID」「発注日」「個数」から構成される「発注テーブル」の図を作成しました。

　この図を使用して、上述した内容を詳しく確認します。（この説明に使用するテーブルは、本説明のみに使用するもので、参考資料や、サンプルプログラム上のテーブルとは異なります）

　このテーブルでは、一レコード（行）が、一件の発注を意味します。白抜きの部分は、サンプリングとして抽出されたレコードとします。

　このテーブルから、抽出されたサンプリングデータで、どのような分析ができるのか/できないのか。その一例を、以下に記載します。

---

• 「分析できること」

　「個数」ごとに区分けして、それぞれの発注件数を見ることができる。

　この場合、個数(50)の「発注数」は、2件。個数(100)の「発注数」は、3件。

• 「分析できないこと」

　「会員ID」で区分けして、それぞれの発注件数を見ることができない。

　この場合、抽出されたレコードは、発注単位のため「会員ID」をもとに、分析することはできません。例えば、今回のサンプリングデータでは「K00002」「K00005」は抽出されていません。そのため「K00002」「K00005」からの発注は無いと、ミスリードしてしまいます。

---

　著者は、このような問題の解決方法として、以下の２点の手法を、例に挙げています。（以下、著者の記述した内容を、上記「発注テーブル」の内容に、置換えて記載しています）

1. 「会員ID」単位で、集約してからサンプリングする。（集約→サンプリング）

2. 「会員ID」単位で、ランダムサンプリングを行う。（サンプリング→集約）

　1.の手法について、サンプリングされないデータの分まで、集約処理するため、処理速度が遅くなると指摘しています。

　さっそく、提案されているサンプリング手法をもとに、コーディングし、動作確認を行います。

[SQL] WITH句とWINDOW句（関数）を利用したサンプリング手法

　ここからは、いつもの「地上気象観測時日別編集データ」を使って、サンプルコードを作成します。

　なお、前述した「発注テーブル」の項目と、内容を混同しないために、双方の項目を紐付けします。

発注テーブル	気象観測データ	主キー
発注ID	地点番号、観測年月日	○
会員ID	地点番号
発注日	観測年月日
個数	平均気温

　今回のSQLのサンプルコードでは、処理速度の比較を行うため、前回【前処理の学習-16】で学んだ「好ましい例」に、GROUP BY ～ ORDER BY句を追加したSQL文も使用します。

　今回の検証SQL文は、以下の通りです。

• サンプルコード「002_selection/04」（p.049）で紹介されているSQL文（以後、"SQL1"と記載）

WITH temporary_table_name AS ( SELECT *, FIRST_VALUE( RANDOM( ) ) OVER ( PARTITION BY intensive_id ) AS temporary_column_name FROM main_table_name ) SELECT column_name_list FROM temporary_table_name WHERE RANDOM() <= extraction_rate;

• 前回【前処理の学習-16】の「好ましい例」で作成したSQL文にGROUP BY～ ORDER BY 句を、追加したSQL文（以後、"SQL2"と記載）

SELECT column_name_list FROM main_table_name WHERE RANDOM() <= extraction_rate GROUP BY column_name_list ORDER BY sort_column_name;

[Windows7][SQL][Jupyter Notebook]

#【前処理の学習-17】データを学ぶ ～抽出～④ [SQL版]-2018.11.11 修正版
#WINDOW句（関数）を利用した場合と、GROUP BY～ ORDER BY句を利用した場合の処理速度を比較検証

#標準ライブラリモジュール
import sys

#Pandasライブラリモジュール
import pandas as pd

#postgreSQL用Python DB API「psycopg」
import psycopg2

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

#日付、時間の取得
from datetime import datetime as dt

#変数の初期化
con = None

#テーブル名
tbl_name = 'tbl_temperature'
tbl_name_tmp = 'tbl_temperature_random'

#カラム名
clmn_list = 'point_number, observation_date, average_temperature'
clmn_id = 'point_number'
clmn_tmp = 'random_num'

#サーバ・サイド・カーソル名
cur_parameter = 'cur_test'

#サンプリング比率(0.0～1.0を指定)
frac = 0.5

#SELECT文(主問合せ部)
sql_select_random = 'SELECT ' + clmn_list + ' FROM ' + tbl_name_tmp + ' WHERE RANDOM() <= '

#WITH句（副問い合わせ部）
sql_with_tmptbl = 'WITH ' + tbl_name_tmp + ' AS '

#SUBQUERY部（副問い合わせ部）
sql_with_subqry ='(SELECT *, FIRST_VALUE(RANDOM()) OVER (PARTITION BY ' + clmn_id + ') AS ' + clmn_tmp + ' FROM ' + tbl_name + ') '

#SELECT文（【前処理の学習-17】で学ぶSQL文）
sql_select = sql_with_tmptbl + sql_with_subqry + sql_select_random + str(frac) + ';'

#SELECT文(前回【前処理の学習-16】で学んだ「好ましい例」に、GROUP BY句を追加したSQL文)
sql_select_intensive = 'SELECT ' + clmn_list + ' FROM ' + tbl_name + ' WHERE RANDOM() <= ' + str(frac) + ' GROUP BY ' + clmn_list + ' ORDER BY ' + clmn_id + ';'


#データベース接続パラメータ
db_connection_parameter = "host='localhost' dbname='weather_data' user='pimientito' password='passwd'"

try:
    with psycopg2.connect(db_connection_parameter) as con:

        #オートコミット機能制御
        con.autocommit = False
        
        #サーバ・サイド・コンソール生成
        with con.cursor(cur_parameter) as cur:
                                    
            #処理時間　計測開始
            s_time = dt.now()
            
            #サンプリング(検証によってコメントアウトを外す)
            #cur.execute(sql_select)
            #cur.execute(sql_select_intensive)

            i = 0
            while True:
                row = cur.fetchone()
                
                if row == None:
                    break
                
                i += 1
                print(row)
            
            #処理時間　計測終了
            e_time = dt.now()
            
    #空行表示
    print('')
    print('')
            
    #抽出データ件数/計測時間の表示
    print('Data Row\'s number: ' + str(i))
    print('start   time: ' + str(s_time))
    print('end     time: ' + str(e_time))
    print('process time: ' + str(e_time - s_time))
    
    #空行表示
    print('')
    print('')
    
except psycopg2.DatabaseError as e:
    print ('Error: %s' % e)
    traceback.print_exc()
    sys.exit(1)
    
except Exception as e:
    print('Exception Error: %s' % e)
    traceback.print_exc()
    sys.exit(1)
    
finally:   
    if con:
        cur.close()
        con.close()

    print('処理が完了しました.')

　また、できる限り同じ環境で比較できるよう、以下の条件を追加しました。

---

1. キャッシュをクリアするため、1回検証する度に、パソコンを再起動する。

2. SQL文の実行から、結果が画面に表示されるまでの時間を、処理時間として計測する。

---

処理速度比較結果

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

検証回	SQL文	処理時間	抽出比率(%)	抽出件数
1	SQL1	0:00:39	50	286,768
2	SQL1	0:00:40	50	286,422
3	SQL1	0:00:40	50	286,671
1	SQL2	0:01:42	50	286,076
2	SQL2	0:01:40	50	286,381
3	SQL2	0:01:41	50	285,652

　副問合せでWINDOW句（関数）を使用したSQL文のほうがGROUP BY～ ORDER BY句を使用したものより、明らかに処理が速い結果が出ました。その理由は、なぜでしょう。

---

（2018.11.10 記事訂正・追記）

　SQL1の構文に誤りがあり、修正し、再々度、検証を行ったところ、SQL1とSQL2の処理速度に、残念ながら大差はありませんでした。（この結果は、あくまで、筆者の検証環境によるもので、すべての環境で、同じ結果になるという確証はありません）

　構文を見直し、あらためて検証した結果は、表にまとめました。また修正内容は、以下の通りです。（上述した記事の一部と、コードは（修正後）の内容で、差し替えています。）

[記事]

（修正前）

WITH temporary_table_name AS ( SELECT *, FIRST_VALUE( RANDOM( ) ) OVER ( PARTITION BY intensive_id ) AS temporary_column_name FROM main_table_name ) SELECT column_name_list FROM main_table_name WHERE RANDOM() <= extraction_rate;

（修正後）

WITH temporary_table_name AS ( SELECT *, FIRST_VALUE( RANDOM( ) ) OVER ( PARTITION BY intensive_id ) AS temporary_column_name FROM main_table_name ) SELECT column_name_list FROM temporary_table_name WHERE RANDOM() <= extraction_rate;

[サンプルコード]

（修正前）

sql_select_random = 'SELECT ' + clmn_list + ' FROM ' + tbl_name + ' WHERE RANDOM() <= '

（修正後）

sql_select_random = 'SELECT ' + clmn_list + ' FROM ' + tbl_name_tmp + ' WHERE RANDOM() <= '

再々度の検証結果

検証回	SQL文	処理時間	抽出比率(%)	抽出件数
1	SQL1	0:01:39	50	286,098
2	SQL1	0:01:42	50	286,574
3	SQL1	0:01:41	50	286,515
1	SQL2	0:01:38	50	285,788
2	SQL2	0:01:43	50	286,030
3	SQL2	0:01:37	50	286,083

　結果として、筆者の検証環境では「抽出処理速度」に関しては、優位性は見られませんでした。しかし、ランダムサンプリングの手法として、いろいろなパターンを知ることは、決して無駄なことではないと考えています。

　今後は、その時々に合った手法を使い分けて、データ分析の前処理を行っていきたいと思います。

---

　今回の記事ではWINDOW句（関数）の詳細や、抽出結果の確認まで、到達できませんでした。

　次回の記事では、今回、追い切れなかった部分について、もう少し掘り下げて学習します。

　今回は、以上です。

次回の課題

1. WINDOW句/OVER句/PARTITION BY句って何？

2. 副問合せのなかでは、どんなことが行われているのか？

3. FIRST_VALUE( )関数とは？

4. Pythonを使った、集約IDに基づくサンプリング手法

【参考資料】

SQL 第2版 ゼロからはじめるデータベース操作 (プログラミング学習シリーズ)

PostgreSQL全機能バイブル

【更新履歴】

---

2018.11.10

[記事]

1. ORDER BYの記載を、GROUP BY～ ORDER BYに修正しました（本文、コード内コメント）

2. 検証SQL文（SQL1、SQL2）を明記

3. サンプルコード差し替えに伴う「処理速度比較」を、再検証し、検証結果を修正

[サンプルコード]

1. 【前処理の学習-16】で作成したSQL文のORDER BYを、GROUP BY ～ ORDER BYに修正

2. サンプルコード差し替え

---

2018.11.11

[記事][サンプルコード]

1. SQL1を修正（詳細は、記事内（2018.11.10 記事訂正・追記）を参照）

2. 「処理速度比較」を、再々検証し、検証結果表を、あらためて作成（詳細は、記事内（2018.11.10 記事訂正・追記）を参照）

---

【前処理の学習-16】データを学ぶ ～抽出～③

　前回、DBテーブルのカラム（項目、列）に、インデックスを設定することで、検索処理コストが軽減されるのか検証しましたが、残念ながら、筆者の検証環境の規模が小さいため、目に見えるほどの効果は確認できませんでした。

pimientito-handson-ml.hatenablog.com

　今回も引き続きデータの抽出について学んでいきます。

【今回の目標到達点】

　ランダムなサンプリングについて学ぶ

【目次】

参考資料のご紹介

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

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

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

「データ抽出」の概要

　現在「前処理大全」第２章「抽出」で、著者の本橋智光氏（以後、著者と表記）が、以下のように述べている部分について学習を進めています。

適切な抽出作業は、無駄な処理を減らせたり、扱うデータサイズを小さくしたりすることができるため、重要です。

参考・参照元：第2章「抽出」より抜粋

　その上で、データの抽出方法について、以下、4種類の考え方をご紹介されています。

• データ列を指定して抽出

• 条件指定によるデータ行の抽出

• データ値に基づかないサンプリング

• 集約IDに基づくサンプリング

今回のテストテーブル

　今回使用するテストテーブルの内容は、以下の通りです。

テーブル名：tbl_temperature

カラム名	概要	型
point_number	地点番号	int
province_code	管区コード	int
number_of_observations	観測回数	int
observation_item	観測項目	int
location_name	地点名	varchar
observation_date	観測年月日	date
average_temperature	平均気温	int
highest_temperature	最高気温	int
lowest_temperature	最低気温	int

データ値に基づかないサンプリング

　はじめに「サンプリング」とは、どういったものなのでしょうか。少し調べてみました。

サンプリングとは、対象全体の中から何らかの基準や規則に基いて一部を取り出すこと。

参考・参照元：IT用語辞典 e-Words「サンプリング【sampling】標本化」より抜粋

また同Webページで、統計におけるサンプリングについて、以下のように説明されています。

統計や調査などの分野では、母集団全体を対象とすることが困難な場合に、集団を代表する少数の標本を抽出して対象とし、その結果から統計的に母集団の性質を推計する手法をサンプリングという。製品の検査や社会調査などで広く用いられ、標本から母集団の推定値を算出する方法や偏りのない標本の抽出方法などについて様々な手法が提唱されている。

参考・参照元：IT用語辞典 e-Words「サンプリング【sampling】標本化　■統計・調査におけるサンプリング」より抜粋

　参考資料　第２章　2-3「データ値に基づかないサンプリング」では、サンプリングの抽出手法として「恣意的なサンプリング」と「ランダム（無作為）なサンプリング」を挙げています。

具体的には

• 恣意的なサンプリングとは、具体的な抽出条件を決めて、条件に合致したデータを取得する手法

• ランダムなサンプリングとは、具体的な抽出条件を決めず、ランダム関数などを利用して、無作為にデータを取得する手法

となります。

　同節では、主に「ランダムなサンプリング」について説明されています。はじめは、SQLを使ってサンプリングする手法を学びます。

[SQL] RANDOM( )関数を使ったサンプリング

　参考資料では、SQL/R言語/Pythonを使って、それぞれのサンプリング手法について言及されていますが、本ブログでは、Pythonを中心にコーディングしているため、本節のSQLでサンプリングする手法も、Pythonコードのなかで実行しています。そのため、若干、参考資料のサンプルコードとは異なります。

　本節では、SQLのRANDOM( )関数を使ったサンプリング手法を「好ましくない例」と「好ましい例」として、それぞれ一例ずつ取り上げ比較しています。（読者によって、異なる視点や、ご意見があることを考え、あえて「良い例」「悪い例」という表現を避けました）

• 好ましくない例

SELECT * FROM table_name ORDER BY RANDOM() LIMIT ROUND(data_total_number * extraction_rate)

　好ましくない理由は、データ量が多い場合ORDER BY句を使用すると、データの並び替えのため、処理コストが大きくなることを、著者は指摘しています。

　またLIMIT句では、サブクエリをネストすることができないことも言及されています。

• 好ましい例

SELECT * FROM table_name WHERE RANDOM( ) <= rate(※)

(※) RANDOM( )関数の出力結果は、0.0～1.0の間になるため、0.0～1.0の数値を入力します。数値が1.0に近づけば、抽出されるデータが増え、反対に0.0に近づくほど、抽出されるデータは少なくなります。（実際には、0.0～0.1の数字を指定しても数行ほど抽出されます（例：0.0001など））

　好ましい理由としてORDER BY句を使用していないことと、可読性が高いことを挙げています。

　またデータの並び替えORDER BY句を使用していないことで、分散処理での対応が可能になるという記載がありますが、残念ながら、いまの筆者にDBの分散処理について知識がないため、この部分については、今後の課題にします。

　「好ましくない例」と「好ましい例」を、それぞれコーディングし、その動きを見てみます。

「好ましくない例」のコーディング

[Windows7][Python][Jupyter Notebook]

#【前処理の学習-16】データを学ぶ ～抽出～③ 
#好ましくない例のコーディング
#ORDER BY RANDOM( ),LIMIT( )を使用したサンプリング抽出

#標準ライブラリモジュール
import sys

#Pandasライブラリモジュール
import pandas as pd

#postgreSQL用Python DB API「psycopg」
import psycopg2

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

#日付、時間の取得
from datetime import datetime as dt

#変数の初期化
con = None

#テーブル名
tbl_name = 'tbl_temperature'

#サーバ・サイド・カーソル名
cur_parameter = 'cur_test'

#サンプリング比率(0.0～1.0を指定)
frac = 0.9

#SELECT文
#データ件数の確認
sql_select_cnt = 'SELECT count(*) FROM ' + tbl_name + ';'

#ODER BY句を使ったケース
sql_select_orderbylimit = 'SELECT * FROM ' + tbl_name + ' ORDER BY RANDOM() LIMIT'

#データベース接続パラメータ
db_connection_parameter = "host='localhost' dbname='weather_data' user='pimientito' password='passwd'"

try:
    with psycopg2.connect(db_connection_parameter) as con:

        #オートコミット機能制御
        con.autocommit = False
        
        #サーバ・サイド・コンソール生成
        with con.cursor(cur_parameter) as cur:
                        
            #データ件数確認
            cur.execute(sql_select_cnt)

            #データの取り出し
            cnt = cur.fetchone()
            
            #tuple型配列[0]番目のみ抜出し、サンプリング比率を乗算してサンプリング数を割り出す
            sampling_num = round(int(cnt[0]) * frac)

    with psycopg2.connect(db_connection_parameter) as con:

        #オートコミット機能制御
        con.autocommit = False
        
        #サーバ・サイド・コンソール生成
        with con.cursor(cur_parameter) as cur:
            
            #処理時間　計測開始
            s_time = str(dt.now())
            
            #サンプリング
            cur.execute(sql_select_orderbylimit + '(' + str(sampling_num) + ');')
            
            #処理時間　計測終了
            e_time = str(dt.now())
            
            #抽出データの表示
            i = 0
            while True:
                row = cur.fetchone()
                
                if row == None:
                    break
                
                i += 1
                print(row)
            
    #空行表示
    print('')
    print('')
            
    #抽出データ件数/計測時間の表示
    print('Data Row\'s number: ' + str(i))
    print('start time: ' + s_time)
    print('end   time: ' + e_time)
    
    #空行表示
    print('')
    print('')
    
except psycopg2.DatabaseError as e:
    print ('Error: %s' % e)
    traceback.print_exc()
    sys.exit(1)
    
except Exception as e:
    print('Exception Error: %s' % e)
    traceback.print_exc()
    sys.exit(1)
    
finally:   
    if con:
        cur.close()
        con.close()

    print('処理が完了しました.')

検証１回目の結果

検証2回目の結果

検証3回目の結果

　前回の記事同様、検証環境のコンピュータの性能と、使用しているデータ量の少なさからORDER BY句を使用した場合の処理時間については、検証の価値はありませんでしたが、サンプリング比率（frac = 0.9）の指定による抽出行数が、すべて同数であることは確認できました。

　続いて「好ましい例」を見ていきます。

「好ましい例」のコーディング

[Windows7][Python][Jupyter Notebook]

#【前処理の学習-16】データを学ぶ ～抽出～③ 
#好ましい例のコーディング
#ORDER BY RANDOM( ),LIMIT( )を使用しないサンプリング抽出

#標準ライブラリモジュール
import sys

#Pandasライブラリモジュール
import pandas as pd

#postgreSQL用Python DB API「psycopg」
import psycopg2

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

#日付、時間の取得
from datetime import datetime as dt

#変数の初期化
con = None

#テーブル名
tbl_name = 'tbl_temperature'

#サーバ・サイド・カーソル名
cur_parameter = 'cur_test'

#サンプリング比率(0.0～1.0を指定)
frac = 0.9

#SELECT文
#WHERE句でRANDOM()関数を使ったケース
sql_select_random = 'SELECT * FROM ' + tbl_name + ' WHERE RANDOM() <= '

#データベース接続パラメータ
db_connection_parameter = "host='localhost' dbname='weather_data' user='pimientito' password='passwd'"

try:
    with psycopg2.connect(db_connection_parameter) as con:

        #オートコミット機能制御
        con.autocommit = False
        
        #サーバ・サイド・コンソール生成
        with con.cursor(cur_parameter) as cur:
                                    
            #処理時間　計測開始
            s_time = str(dt.now())
            
            #サンプリング
            cur.execute(sql_select_random + str(frac) + ';')
            
            #処理時間　計測終了
            e_time = str(dt.now())

            #抽出データの表示
            i = 0
            while True:
                row = cur.fetchone()
                
                if row == None:
                    break
                
                i += 1
                print(row)
            
    #空行表示
    print('')
    print('')
            
    #抽出データ件数/計測時間の表示
    print('Data Row\'s number: ' + str(i))
    print('start time: ' + s_time)
    print('end   time: ' + e_time)
    
    #空行表示
    print('')
    print('')
    
except psycopg2.DatabaseError as e:
    print ('Error: %s' % e)
    traceback.print_exc()
    sys.exit(1)
    
except Exception as e:
    print('Exception Error: %s' % e)
    traceback.print_exc()
    sys.exit(1)
    
finally:   
    if con:
        cur.close()
        con.close()

    print('処理が完了しました.')

検証１回目の結果（抽出データ数/データ総数：50,936/56,730、抽出率：89.78%）

検証2回目の結果（抽出データ数/データ総数：51,166/56,730、抽出率：90.19%）

検証3回目の結果（抽出データ数/データ総数：50,984/56,730、抽出率：89.87%）

　WHERE RANDOM( ) <= rate句（今回は、rate=0.9を指定）を使用した場合、データ行ごとにRANDOM( )関数を実行し、その結果を「rate」の値と比較しているため、抽出されるデータ数は、実行ごとに異なり、バラつきがありますが、上記、検証結果の抽出率を見ても、大きな差異はないようです。

　ただし、実際の分析に利用するサンプルデータの場合は、複数回、抽出を行い、あまりバラつきがあるような場合は、別途、サンプリング手法を検討する必要があると思います。

　本節の「好ましい例」と「好ましくない例」から、ORDER BY句の利用時の注意点や、コード保守の視点から、可読性の重要性を、あらためて学びました。

　最後に、Pythonを使ったサンプリングを学びます。

[Python] sample( )関数を使ったサンプリング

　PandasのDataFrameが持つsample( )関数を使用すると、指定したデータ群から、指定した比率でデータを抽出します。

　実際にコーディングして、その動作を確認します。

[Windows7][Python][Jupyter Notebook]

#【前処理の学習-16】データを学ぶ ～抽出～③ 
#Python sample()関数を使用したサンプリング抽出

#標準ライブラリモジュール
import sys

#Pandasライブラリモジュール
import pandas as pd

#Pandas DataFrame
from pandas import DataFrame as df

#postgreSQL用Python DB API「psycopg」
import psycopg2

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

#変数の初期化
con = None

#テーブル名
tbl_name = 'tbl_temperature'

#サーバ・サイド・カーソル名
cur_parameter = 'cur_test'

#サンプリング比率(0.0～1.0を指定)
frac_point = 0.9

#SELECT文
sql_select = 'SELECT * FROM ' + tbl_name + ';'

#データベース接続パラメータ
db_connection_parameter = "host='localhost' dbname='weather_data' user='pimientito' password='passwd'"

try:
    with psycopg2.connect(db_connection_parameter) as con:

        #サンプリング
        #DBテーブル「tbl_temperature」の全データを選択
        df = pd.read_sql_query(sql_select, con)
            
        #選択したデータから、サンプリング比率に合わせてデータを抽出
        df = df.sample(frac=frac_point)
        
        #DataFrame属性shapeで、サンプリング情報を確認
        #shapeの表示形式： (行数, 項目数)
        print('df.shape: ' + str(df.shape))
        print('')
        
        #DataFrame.head()関数で、サンプリングデータ先頭数行を表示
        print('df.head(): ')
        print(df.head())  
        print('')
    
except psycopg2.DatabaseError as e:
    print ('Error: %s' % e)
    traceback.print_exc()
    sys.exit(1)
    
except Exception as e:
    print('Exception Error: %s' % e)
    traceback.print_exc()
    sys.exit(1)
    
finally:   
    if con:
        cur.close()
        con.close()

    print('処理が完了しました.')

検証１回目の結果

検証2回目の結果

検証3回目の結果

　抽出されたデータ行数は、すべて同数ですが、df.head( )関数で、抽出データの先頭の数行を表示してみると、データの内容が、すべて異なっていることから、sample( )関数が、毎回、ランダム（無作為）に、データを抽出していることが分かります。

　またPython標準ライブラリをはじめとする、PandasやNumpyなどのライブラリを使用すると、コードが簡素化されて、Python上でSQLをコーディングするよりも、可読性が高まることも再認識しました。

　引続き、Python & SQLによる「機械学習」を学習していきますが、コードの可読性や、ライブラリの利便性も意識していきたいと思います。

　今回は「機械学習」の学習を進めていく上で、必ず必要となる「データ・サンプリング」の手法のうち、ランダムにデータを抽出する手法を学びました。

　分析するデータの内容（画像や文字/数字など）によって、いろいろな手法が、たくさんあると思いますので、それらを取り入れて、より良い「機械学習」が続けられるよう、少しずつ習得していきたいと思います。

　今回は、以上です。

【参考資料】

初めてのPython 第３版（O'REILLY）

初めてのPython 第3版

現場で役立つA to Z PostgreSQL 全機能バイブル

PostgreSQL全機能バイブル

pandas 0.23.4 documentation

https://pandas.pydata.org/pandas-docs/stable/#

IT用語辞典 e-Words「サンプリング【sampling】標本化」

e-words.jp

【前処理の学習-番外編-4】Python/Psycopg2を利用してPostgreSQLへ大量データをINSERTしてみた

　前回「【前処理の学習-14】データを学ぶ～抽出～①」で作成したテスト用DBテーブル（以後、テストテーブルと表記）に不足な点があったため、テストテーブルを再作成しようとしたところ、思わぬ問題にぶつかり、試行錯誤を繰り返したため、その足跡を番外編としてお送りします。

pimientito-handson-ml.hatenablog.com

　はじめに前回作成したテストテーブルの構成（左側）と、本来、検証で利用したいと考えていた構成（右側）を比較します。

カラム	概要	カラム	概要
point_number	地点番号	point_number
province_code	管区コード	province_code
number_of_ observations	観測回数	number_of_ observations
observation_item	観測項目	observation_item
location_name	地点名	location_name
observation_year	観測年	observation_date	観測年月日
observation_month	観測月
observation_day	観測日
average_local_ atmospheric_pressure	平均現地気圧	average_local_ atmospheric_pressure
mean_sea_ level_pressure	平均海面気圧	mean_sea_ level_pressure

　元データでは、観測年月日が「年」「月」「日」として、ひとつずつカラムに分かれていました。

　そこで、今回作成するテストテーブルでは、それらを、ひとつのカラムに集約して「観測年月日」という日付型カラムに格納しようと考えました。

　最初に考えたコーディングの概要は、以下の通りです。

1. 元データからSELECT文で、データを抽出する。

2. 抽出したデータから、一行ずつfetchone()メソッドで抜き出す。

3. 対象のrow[]（年、月、日）の部分を文字列連結したあと、日付型に型変換を行い、別途用意した変数に代入する。

4. 他のrow[]と組合せて、SQLのINSERT文の引数に渡す。

5. あとは、2～4の繰返し

　しかし結果は、思惑通りにならず、次から次へと異なるエラーに阻まれて、一向に新しいテストテーブルが作成できませんでした。

　主な問題は、以下の通りです。（順不同）

　（※）今回出会ったエラーが複数あり、ひとつずつキャプチャーや説明を含めると、かなりの量になってしまうため、ざっくりとした箇条書きにしました。

• テーブル作成後、INSERT文が一回だけ実行されるがループしない。

• cursor()を、サーバ・サイド・カーソルで生成してもINSERT文では使用できない。（SELECT文では、問題なく使用できる）※筆者の体験によるもの

• PostgreSQLの自動コミット機能をOFFにしても、INSERT文がループしない。

• fetchone()ではなくfetchall()に切り替えてみたが、そもそもデータ編集が、簡単にピンポイントで行えない。

• INSERT文がループするようになっても、Jupyter Notebookのバッファオーバーフローが起きる。（Jupyter Notebookのコンフィグファイルの編集はおこないませんでした）

などなど。

　データの送り元と送り先で、同じテーブル構成の場合ならばINSERT INTO table_name SELECT * FROM original_table_name;やCOPYコマンドで問題なく行えるのですが、今回のような元データを編集して、カラム数の異なるテーブルへ挿入する事例が、Google検索でトップ表示されている記事でも見当たらず、またstack overflowでも、似たような事例が見つかっても、明確な回答に出会えませんでした。

　それでも、インターネットの情報を頼りに、少しずつ組み立てて、以下のような仕様であれば、今回の目的に合ったテストテーブルを作成することができました。

1. 元データからSELECT文で、データを抽出する。

2. 抽出したデータから、一行ずつfetchone()メソッドで抜き出す。

3. 対象のrow[]（年、月、日）の部分を文字列連結したあと、日付型に型変換を行い、別途用意した変数に代入する。

4. 他のrow[]と組合せて、pandasのDataFrameに挿入して、データのフォーマットを整える。

5. リスト型二次元配列変数へ挿入する。

6. 2～5を繰り替えす。

7. カーソルメソッドcursor.executemany()の引数に、INSERT文とリスト型二次元配列変数を指定して実行する。

　ただし、この方法でも、データ数が多いと、Jupyter Notebookのバッファオーバーフローで、処理が止まってしまいますので、今回の場合、1月～12月までのデータを、一ヶ月ごとに分けて、複数回、手動で実行しました。

　実際に作成したコードは、以下の通りです。

[Windows7][Python, PostgreSQL][Jupyter Notebook]

#【前処理の学習-番外編-4】Python/Psycopg2を利用してPostgreSQLへ大量データをINSERTしてみた
#テスト用テーブル作成と、データ挿入（再チャレンジ）

import sys
import pandas as pd

#postgreSQL用Python DB API「psycopg」
import psycopg2

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

#日付、時間の取得
import datetime

#変数の初期化
con = None

#テーブル名
tbl_weather ='dly_edit_data_grnd_wthr_obsrv'
tbl_new = 'tbl_test'

#カラムリスト
clmn_list_select = 'point_number,province_code,number_of_observations,observation_item,location_name,observation_year,observation_month,observation_day,average_local_atmospheric_pressure,mean_sea_level_pressure'

clmn_list_create = 'point_number integer,province_code integer,number_of_observations integer,observation_item integer,location_name varchar,observation_date date,average_local_atmospheric_pressure integer,mean_sea_level_pressure integer, '

clmn_list_PKey = 'point_number,observation_date'

clmn_list_insert = '(row[0],row[1],row[2],row[3],row[4],observation_date,row[8],row[9])'

#データ抽出月（抽出対象月は手動で変更）
obsrv_month = 1

#SELECT文
#テーブル「dly_edit_data_grnd_wthr_obsrv」のデータを抽出
sql_select1 = 'SELECT ' + clmn_list_select + ' FROM ' + tbl_weather + ' where observation_month=\'' + str(obsrv_month) + '\';'

#新しいテーブルデータを確認
sql_select2 = 'SELECT * FROM ' + tbl_new + ';'

#CREATE TABLE文
sql_create = 'CREATE TABLE ' + tbl_new + ' (' + clmn_list_create + ' PRIMARY KEY(' + clmn_list_PKey + '));'

#INSERT文 テストテーブルへデータを挿入
sql_insert = 'INSERT INTO ' + tbl_new + ' VALUES (%s,%s,%s,%s,%s,%s,%s,%s) '

#データベース接続パラメータ
db_connection_parameter = "host='localhost' dbname='weather_data' user='pimientito' password='passwd'"


try:
    #テーブル作成のコードは、初回実行後、コメントアウトする。
    #with psycopg2.connect(db_connection_parameter) as con:
    #    con.autocommit = False
        
    #    with con.cursor() as cur:

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

    with psycopg2.connect(db_connection_parameter) as con:
        con.autocommit = False

        with con.cursor() as cur:
            
            #テストテーブル用データを抽出
            cur.execute(sql_select1)
            
            #リスト生成
            arry_row = []

            #データ挿入
            while True:
                row = cur.fetchone()
                
                if row == None:
                    break
        
                #観測日付をyyyy-mm-ddで成形
                observation_date = datetime.datetime(int(row[5]),int(row[6]),int(row[7]))        
                observation_date = format(observation_date,'%Y-%m-%d')
                
                #加工した行を、Pandas.DataFrameを利用してフォーマット整正
                pd.dataframe = (row[0],row[1],row[2],row[3],row[4],observation_date,row[8],row[9])
                
                #リスト型配列に格納
                arry_row.append(pd.dataframe)

            #リスト型変数を利用して複数データを挿入
            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('処理が完了しました.')

　もっと効率の良い方法があるかと思うのですが、筆者の現在のスキルでは、これが精一杯でした。今後、ライブラリの機能なども並行して学習しなければ「効率の良い」「スマートな」『前処理』を習得できないなと痛感した一週間でした。

今回は、以上です。

【参考資料】

qiita.com

qiita.com

qiita.com

qiita.com

note.nkmk.me

note.nkmk.me

pynative.com

www.lifewithpython.com

sonickun.hatenablog.com

www.sejuku.net

www.javadrive.jp

www.javadrive.jp

【前処理の学習-15】データを学ぶ ～抽出～②

　前回から、データ前処理「抽出」について、学びはじめました。

pimientito-handson-ml.hatenablog.com

【今回の目標到達点】

• DBのインデックス機能を利用した場合の優位性を確認する。

【目次】

参考資料のご紹介

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

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

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

「データ抽出」の概要

　現在「前処理大全」第２章「抽出」で、著者の本橋智光氏（以後、著者と表記）が、以下のように述べている部分について学習を進めています。

適切な抽出作業は、無駄な処理を減らせたり、扱うデータサイズを小さくしたりすることができるため、重要です。

参考・参照元：第2章「抽出」より抜粋

　その上で、データの抽出方法について、以下、4種類の考え方をご紹介されています。

• データ列を指定して抽出

• 条件指定によるデータ行の抽出

• データ値に基づかないサンプリング

• 集約IDに基づくサンプリング

現在「条件指定によるデータ行の抽出」について学んでいます。

条件指定によるデータ行の抽出

　前回の記事では、データベース上のデータ（正しくはカラム（列））に、インデックスを設定することで、検索処理コストを軽減できることを学び、実際に検証するために、テスト用DBテーブル（tbl_test）（以後、テストテーブルと表記）を作成したところで終りました。

今回は、このテストテーブルを使って、インデックスの設定の効果について検証します。（※）

（※）前回作成したテストテーブルでは、不足な部分があったため、テストテーブルを再作成しました。再作成にいたる経緯や、再作成の際に出会ったトラブルなどの詳細は、別記事「【前処理の学習-番外編-4】Python/Psycopg2を利用してPostgreSQLへ大量データをINSERTしてみた」をご覧ください。

pimientito-handson-ml.hatenablog.com

では、さっそくインデックスの効果について学んでいきます。

検証環境

検証に使用した機器は、以下の通りです。

項目	内容
CPU	Intel(R) Core i7 3.40GHz
物理メモリ	16.0GB
OS	Windows 7 Pro Service Pack1
システム種別	64bit

今回作成したテストテーブルの内容は、以下の通りです。

カラム	概要	型
point_number	地点番号	int
province_code	管区コード	int
number_of_observations	観測回数	int
observation_item	観測項目	int
location_name	地点名	varchar
observation_date	観測年月日	date
average_local_atmospheric_pressure	平均現地気圧	int
mean_sea_level_pressure	平均海面気圧	int

インデックスを設定する「観測年月日」の日付型形式は、以下の通りです。

カラム	形式
observation_date	yyyy-mm-dd

レコード数（行数）と内訳は、以下の通りです。

項目	数量
観測期間	2000.01.01-12.31
観測日数	366(閏年)
観測地点数	155
レコード総数	56,730

　今回作成したPythonプログラムでは、PostgreSQLのDB API「psycopg2」のカーソルオブジェクトをサーバ・サイド・カーソルで作成しました。サーバ・サイド・カーソルの詳細については、以下の資料をご参照ください。

psycopg2「Server side cursors」（英文）

Basic module usage — Psycopg 2.8.dev0 documentation

検証

　インデックスの効果を測定するため、以下の条件で検証します。

【検証方法】

• 日付型カラム（observation_date）に、インデックスを設定後、任意の日付を指定した検索を実施。インデックスを設定しない場合との検索時間の差異を確認します。

• SQL文の「CREATE INDEX」文と「DROP INDEX」文の実行は、別途、psql上で実施し、Pythonコードには含めません。

【想定される結果】

　データ量が少ないため、インデックスを設定した場合と、設定していない場合の差異が出たとしても数ミリ秒単位ではないかと思われ、明らかな差異が出ない限り、初学者である筆者では、その差異がインデックスによる効果であるかないかの判断ができないのではと考えています。

【検証コード】

[Windows7][Python][Jupyter Notebook]

#【前処理の学習-15】データを学ぶ ～抽出～②
#インデックス機能を設定している場合と、設定していない場合の検索処理速度を検証

import sys

#postgreSQL用Python DB API「psycopg」
import psycopg2

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

#日付、時間の取得
from datetime import datetime as dt

#変数の初期化
con = None

#テーブル名
tbl_name = 'tbl_test'

#サーバ・サイド・カーソル名
cur_parameter = 'test_cur'

#検索対象日付(yyyy-mm-dd)を代入
obsrv_date1 = '2000-05-14'
obsrv_date2 = '2000-10-23'

#WHERE句(検証毎にコメントアウトで切り替える)
where_sentence = ' WHERE observation_date =  '
#where_sentence = ' WHERE observation_date BETWEEN  '

#SELECT文(検証毎にコメントアウトで切り替える)
#データ検索(単体)
sql_select = 'SELECT * FROM ' + tbl_name + where_sentence + '\'' + obsrv_date1 +'\';'
#データ検索(BETWEEN AND条件)
#sql_select = 'SELECT * FROM ' + tbl_name + where_sentence + '\'' + obsrv_date1 + '\' AND \'' + obsrv_date2 + '\';'

#データベース接続パラメータ
db_connection_parameter = "host='localhost' dbname='weather_data' user='****' password='passwd'"

try:
    with psycopg2.connect(db_connection_parameter) as con:

        #オートコミット機能制御
        con.autocommit = False

        #サーバ・サイド・コンソール生成
        with con.cursor(cur_parameter) as cur:
            
            #計測開始
            print('start time: ' + str(dt.now()))
            
            #テストテーブル用データ検索
            cur.execute(sql_select)
            
            #計測終了
            print('end   time: ' + str(dt.now()))
            print('')
            print('')
            
            #検索結果の確認
            while True:
                row = cur.fetchone()
                
                if row == None:
                    break
                
                print(row)

except psycopg2.DatabaseError as e:
    print ('Error: %s' % e)
    traceback.print_exc()
    sys.exit(1)
    
except Exception as e:
    print('Exception Error: %s' % e)
    traceback.print_exc()
    sys.exit(1)
    
finally:   
    if con:
        cur.close()
        con.close()

    print('処理が完了しました.')

【検証結果】

　やはりデータ量が少ないため、インデックス機能を有効／無効にしても、画面キャプチャを貼るほどの大きな変化は見られませんでした。そろそろ新しいデータの補充を考える必要があるかもしれません。

【考察】

　今回は、残念ながら、目に見えてインデックスの有効性を確認することができませんでしたが、それはインデックス機能の問題ではなく、データ分析を学ぶには、検証環境が、あまりにも稚拙であるためでした。

　インデックス機能については「データ処理コストの軽減」に役立つ、ひとつの手段として、今後、機会があれば、積極的に取り入れていきたいと思います。

　なかなか、お話が先に進みませんが「いそぐ旅でなし」をモットーに、じっくりと「機械学習」を進めて行きたいと思います。

今回は、以上です。

【参考資料】

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

SQL 第2版 ゼロからはじめるデータベース操作 (プログラミング学習シリーズ)

【前処理の学習-14】データを学ぶ ～抽出～①

　前回は、いままでの流れを再確認するため、参考資料のご紹介と、検証環境、サンプルデータについて振り返りました。

pimientito-handson-ml.hatenablog.com

　今回も、引続き「機械学習」の前処理「抽出」について学んでいきます。

【今回の目標到達点】

• インデックスの効果について学ぶ（SQL）

• テスト用の小規模DBテーブルを作成する。

【目次】

参考資料のご紹介

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

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

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

データ抽出

　前々回「前処理大全」第２章「抽出」で、著者の本橋智光氏が、以下のように述べている部分について触れました。

適切な抽出作業は、無駄な処理を減らせたり、扱うデータサイズを小さくしたりすることができるため、重要です。

参考・参照元：第2章「抽出」より抜粋

　その上で、データの抽出方法について、以下、4種類の考え方をご紹介されています。

• データ列を指定して抽出

• 条件指定によるデータ行の抽出

• データ値に基づかないサンプリング

• 集約IDに基づくサンプリング

　前々回の最後に「データ列を指定して抽出」は、学びました。

pimientito-handson-ml.hatenablog.com

　今回は、第2章2-2「条件指定による抽出」から学習します。

条件指定によるデータ行の抽出

　本節のはじめで、本橋智光氏（以後、著者と表記）は、列の条件指定によるデータの絞り込みの重要性について述べています。著者によると、適切な抽出対象の列を選択することによって、そのあとに続く分析処理では、計算コストを減らすことができると述べています。

　ただし、抽出対象とする列の絞り込みの判断は難しく、例えば、機械学習モデルの種類や、ほかの分析結果によって、列を選択する条件が変わることが多いため、絞り込みの条件は、柔軟に対応できるようにした方が良いとも述べています。

　列同士の関係性の検討や、選択条件の分析・検証など、いわゆる「データサイエンス」に類する学問については、もう少し先で学ぶとして、いまは列の抽出について学びます。

インデックスの利用について（SQL）

　まずデータベースを利用して条件抽出をする場合、著者は「インデックス」の使用を推奨されています。

　私は、本節を読むまで「インデックス」とは、DBカラムの「項番」または「ID」と称される列のことと混同していました。

　あらためてPostgreSQLドキュメント「PostgreSQL 10.4文書 第11章インデックス」を確認したところ、検索には「インデックススキャン」と「シーケンシャル（順序、連鎖）スキャン」とがあり、インデックスを設定しない場合は「シーケンシャル」となります。なおPostgreSQLでは、インデックス検索のデフォルトは「B-Tree（B木）」に設定されています。

　インデックスを設定している場合と、設定していない場合の違いを、簡単な図で表します。

　実際には、このように単純ではないと思いますが、検索コストが大きく違うことは、図を見ても明らかです。

　本ブログで使用しているデータ量では「インデックス検索」と「シーケンシャル検索」の違いを明確に確認できないかもしれませんが、試してみたいと思います。

　本章で紹介されている手法をもとに、以下、二通りの確認を行います。

1. ある列にインデックスが設定されている場合と、設定されていない場合の違いを確認

2. インデックスを設定した列を含めた複数列を、検索条件に含めて検索（インデックスを設定していない場合も検証）

　なお検証用として構築したDB「weather_data」の「dly_edit_data_grnd_wthr_obsrv」テーブルでは、項目数が多く扱いづらいため、もう少し小さいサイズのテーブルを作成します。

#【前処理の学習-14】データを学ぶ ～抽出～①
#テスト用テーブル作成と、データ挿入

import sys

#postgreSQL用Python DB API「psycopg」
import psycopg2

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

#変数の初期化
con = None

#テーブル名
tbl_weather ='dly_edit_data_grnd_wthr_obsrv'
tbl_new = 'tbl_test'

#カラムリスト
clmn_list_select = 'point_number,province_code,number_of_observations,observation_item,location_name,observation_year,observation_month,observation_day,average_local_atmospheric_pressure,mean_sea_level_pressure'

clmn_list_create = 'point_number integer,province_code integer,number_of_observations integer,observation_item integer,location_name varchar,observation_year varchar,observation_month varchar,observation_day text,average_local_atmospheric_pressure integer,mean_sea_level_pressure integer, '

clmn_list_PKey = 'point_number,observation_year,observation_month,observation_day'

#SELECT文
#テーブル「dly_edit_data_grnd_wthr_obsrv」のデータを抽出
sql_select1 = 'SELECT ' + clmn_list_select + ' FROM ' + tbl_weather + ';'

#テストテーブルのデータを確認
sql_select2 = 'SELECT * FROM ' + tbl_new + ';'

#CREATE TABLE文
sql_create = 'CREATE TABLE ' + tbl_new + ' (' + clmn_list_create + ' PRIMARY KEY(' + clmn_list_PKey + '));'

#INSERT文 テストテーブルへデータを挿入
sql_insert = 'INSERT INTO ' + tbl_new + ' ' + sql_select1

try:
    con = psycopg2.connect("host='localhost' dbname='weather_data' user='pimientito' password='****'")   
    cur = con.cursor()

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

    #テストテーブルにデータ挿入
    cur.execute(sql_insert)
    con.commit()
        
    #テストテーブルのデータを確認
    cur.execute(sql_select2)
    
    while True:
        row = cur.fetchone()

        if row == None:
            break
            
        #テストテーブルのデータを表示
        print(row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8],row[9])
          
except psycopg2.DatabaseError as e:
    if con:
        con.rollback()
 
    print ('Error: %s' % e)
    traceback.print_exc()
    
    sys.exit(1)
    
except Exception as e:
    print('Exception Error: %s' % e)
    traceback.print_exc()
    
    sys.exit(1)
    
finally:   
    if con:
        cur.close()
        con.close()

    print('処理が完了しました.')

　次回は、このテーブルのデータを使って、インデックスの効果について学習していきます。

　今回は、以上です。

【参考資料】

前処理大全[データ分析のためのSQL/R/Python実践テクニック]　本橋智光著（技術評論社）

PostgreSQL 10.4文書「第11章 インデックス」

https://www.postgresql.jp/document/10/html/indexes.html

Python 3.7.1rc2 documentation

https://docs.python.org/3/

Stack overflow「type object 'datetime.datetime' has no attribute 'datetime'」

stackoverflow.com

Stack overflow「Executing an insert query on each row in results: psycopg2.ProgrammingError: no results to fetch」

stackoverflow.com

Stack overflow「Insert into … values ( SELECT … FROM … )」

stackoverflow.com

技術書典5の御礼

　去る10/08（月 祝）池袋にて行われた「技術書典5」で、初の試みとして同人誌『非理系ですが「機械学習」をはじめました。』を出品いたしました。

　110部と、自分では大冒険ではありましたが、みなさまのお蔭で、無事、完売することができました。本当にありがとうございました。

　またご購入に至らなくても、数多くの方々が、お手に取ってくださり、真剣に読んでくださっているお姿を拝見し、反省と共に、今後の励みにもなりました。

　ここで、頂戴した貴重なご意見やご感想を、ひとつひとつご紹介したいところですが、自分にとって、とても大切な経験のため、掲載は控えさせて頂きます。

　これからも、誠心誠意「機械学習」を学んでいきますので、今後とも『pimientitoの機械学習』と『非理系ですが「機械学習」はじめました。』を、ご支援いただけますよう、よろしくお願いいたします。

【前処理の学習-13】データを学ぶ・・・その前に。

前回から、本格的に機械学習の前処理について学びはじめました。

pimientito-handson-ml.hatenablog.com

【今回の目標到達点】

いままでの足跡を振り返る

【目次】

参考資料のご紹介

はじめに現在、参考にさせていただいている書籍についてご説明します。

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

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

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

本ブログは、ご紹介した書籍の書評ではございません。またご紹介した書籍の販売促進やアフェリエイトを目的としたものでもございません。あくまで機械学習初心者である筆者が、機械学習を学ぶ上で必要とする資料として活用させていただいています。

なお参考書籍のなかで、さまざまなサンプルデータやサンプルコードがご紹介されていますが、本ブログでは、データは筆者が選択した別のデータを利用しており、サンプルコードにおきましても、書籍内容を筆者なりに理解・咀嚼してから、あらためてサンプルコードを作成しておりますので、書籍本文と完全に一致していないことがございます。これらご了承いただけますよう何卒よろしくお願いいたします。

いままでの振り返り（検証環境とサンプルデータについて）

多くの読者のみなさまに見守られ、本ブログ「pimientitoの機械学習」が、先日500PVを達成いたしました。本当にありがとうございました。

そこで今回は、本ブログで使用している検証環境や、サンプルデータについて、あらためて読者のみなさまへご説明したいと思います。

本ブログでは、以下の検証環境で機械学習を学んでいます。

項目	内容
CPU	Intel(R) Core i7-2600 3.40GHz
ﾒﾓﾘ	16GB
OS	Windows 7 Professional 64bit版
DB	PostgreSQL
開発ﾌﾟﾗｯﾄﾌｫｰﾑ	Jupyter Notebook（Anaconda Navigator使用）
言語	Python, SQL

サンプルデータについては、以下のデータを使用しています。

項目	内容
CDﾀｲﾄﾙ	地上気象観測時日別編集データ（CSV版）2000年
発行元	（財）気象業務支援センター
ﾃﾞｰﾀﾌｧｲﾙ	地上気象観測日別編集データ
ﾌｧｲﾙ数	155
ﾚｺｰﾄﾞ数/ﾌｧｲﾙ	28～31
ｶﾗﾑ数/ﾚｺｰﾄﾞ	73

サンプルデータの詳細については、下記の過去記事をご覧ください。

pimientito-handson-ml.hatenablog.com

いままで、以下のような流れでデータ操作を学んできました。

1. 複数のフォルダに分けられた、複数のCSVファイルを、繰返し処理でPostgreSQLのテーブルへ取込む（複数のCSVファイルをDBへ取込むPythonコードについては、下記の過去記事をご覧ください。）

2. PostgreSQL DB API「Psycopg」を利用して、PythonからDBへ接続

3. API「Psycopg」を利用してSQL文を発行

4. 戻り値は、Pythonライブラリ「Pandas」のDataFrameに格納

5. 以下、その都度のテーマに沿ってデータを使用

複数CSVファイルから、データを抽出してDBテーブルへ取込む方法について

<ご注意ください>

下記の記事でご紹介しているDBは、PostgreSQLです。ご利用になっているDBが異なる場合、使用するAPIも異なる可能性があります。ご自身の環境で試す場合は、ご利用のDBまたはPythonのドキュメントから該当する情報を確認してください。

pimientito-handson-ml.hatenablog.com

Pythonと主なDBとの接続等については、以下のwiki（英文）の情報もご参考ください。

MySQL

https://wiki.python.org/moin/MySQL

OracleDB

https://wiki.python.org/moin/Oracle

DB2

https://wiki.python.org/moin/DB2

以上が、検証環境とサンプルデータについてです。

今回は「pimientitoの機械学習」500PVを節目として、新しい読者の方々に、いままでの流れをご理解いただけるよう、過去の作業を振り返りました。

次回から、あらためて「前処理大全」第２章「抽出」を学んでいきます。

今回は、以上です。

【告知】 10/8 技術書典5へ出品いたしますっ！

まだはじめて間もない「機械学習」ですが、はてなブログ「Pimientitoの機械学習」で綴ってきた内容をブラッシュアップして一冊の同人誌にいたします。

内容は「pimientitoの機械学習」だけに留まらず、機械学習やデータ分析には欠かせないデータの収集に「オープンデータ」を活用する方法や、「機械学習って、どうやってはじめれば良いの？」というお悩みをお持ちの方々に、最低限必要なツールの入手方法などをご説明しています。

また総務省発行の「情報通信白書」からは「人工知能（AI）研究の歴史」を参考に、AI研究の歴史について触れています。

難しい数式やアルゴリズムだけではない「機械学習」を一緒に学びませんか？

技術書典5

techbookfest.org

サークルカット（出品場所：き01　サークル名：でーたちっぷす）

同人誌『非理系ですが「機械学習」はじめました。』の表紙

当日、お近くをお通りの際は、是非お立ち寄りください。みなさまのお越しをお待ちしておりますっ！

【前処理の学習-12】データに触れる（Windows環境編）②

前回から機械学習に使うデータに触れはじめました。

pimientito-handson-ml.hatenablog.com

残念ながら、いまの状態では分析に利用できそうもなく、もう少しデータの整理が必要になりそうです。

【今回の目標到達点】

前処理の学習の方向性を探る

【目次】

データ項目の見直し

あらためてデータ項目（カラム）一覧を見てみます。

現在使用しているデータは「地上気象観測時日別編集データ（CSV版）2000年」（（財）気象業務支援センター　発行）です。

項目名	項目名	項目名
地点番号	蒸発量	昼間.概況3接続詞.RMK
管区コード	日降水量	昼間.概況3天気
観測回数	最大1時間降水量	昼間.概況3天気.RMK
観測項目	最大10分間降水量	昼間.概況4接続詞
地点名	降雪の深さの日合計	昼間.概況4接続詞.RMK
年	日最深積雪	昼間.概況4天気
月	未使用	昼間.概況4天気.RMK
日	未使用	夜間.概況1接続詞
平均現地気圧	大気現象1	夜間.概況1接続詞.RMK
平均海面気圧	大気現象2	夜間.概況1天気
最低海面気圧	大気現象3	夜間.概況1天気.RMK
平均気温	大気現象4	夜間.概況2接続詞
最高気温	大気現象5	夜間.概況2接続詞.RMK
最低気温	降水強風時間	夜間.概況2天気
平均蒸気圧	欠測処理1	夜間.概況2天気.RMK
平均相対湿度	欠測処理2	夜間.概況3接続詞
最小相対湿度	昼間.概況1接続詞	夜間.概況3接続詞.RMK
平均風速	昼間.概況1接続詞.RMK	夜間.概況3天気
最大風速	昼間.概況1天気	夜間.概況3天気.RMK
最大風速の風向	昼間.概況1天気.RMK	夜間.概況4接続詞
最大瞬間風速	昼間.概況2接続詞	夜間.概況4接続詞.RMK
最大瞬間の風向	昼間.概況2接続詞.RMK	夜間.概況4天気
平均雲量	昼間.概況2天気	夜間.概況4天気.RMK
日照時間	昼間.概況2天気.RMK
全天日射量	昼間.概況3接続詞

いままで、何度もご紹介してきた項目一覧。しかし、ここからどのように進めて良いのか、正直分かりません。

データ分析を行うために必要な基礎知識（視点）が、まるっきり無いため、ただ数字の羅列を眺めているばかりです。。。

書籍『前処理大全 』から学ぶ

自身にスキルが無いのであれば、各分野の諸先輩方のお知恵をお借りして学習するしか方法がありません。

本ブログ開始時にご紹介させていただきました本橋智光氏著「前処理大全　データ分析のためのSQL/R/Python実践テクニック」（技術評論社）（以後「前処理大全」と表記）を参考に前処理について学んでいきます。

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

本書「前処理大全」のなかでもサンプルデータが使われていますが、本ブログでは、引続き「地上気象観測時日別編集データ」を使用いたします。そのため「前処理大全」の内容と、本ブログでご紹介する内容が、必ずしも一致するものではございません。

また本ブログは、ご紹介する書籍の書評するものではございません。あくまで「機械学習初心者」の学習の軌跡を綴っているものです。本ブログ読者のみなさまには、ご理解いただけますようお願いいたします。

なお今後、本「前処理大全」より引用した部分については、文章末尾にカッコ（）で括った章番号や章のタイトルのみ表記することといたします。書籍名や著者名については、毎回ブログ記事先頭でご紹介することとして、記事内で繰返し表記することを割愛いたします。

では最初に学ぶのは「抽出」からです。書籍のなかでは、抽出について、このように述べています。

単純な作業に思えますが、適切な抽出作業は、無駄な処理を減らせたり、扱うデータサイズを小さくしたりすることができるため、重要です。

（参考・参照元：第2章「抽出」より抜粋）

また同章では、以下の4種類の抽出について説明されています。

1. データ列を指定して抽出

2. 条件指定によるデータ行の抽出

3. データ値に基づかないサンプリング

4. 集約IDに基づくサンプリング

（参考・参照元：第2章「抽出」より抜粋）

ひとつずつ、手を動かしながら抽出について学んでみましょう。

1. データ列を指定して抽出

書籍のなかでは、列（カラム）を指定して抽出することの利点について、このように説明されています。

必要な列のみに絞り込むことによって、1行あたりのデータサイズを減らし、後続のデータ分析をやりやすくすることが、データ列抽出の役割です。

（参考・参照元：第2章「抽出」2-1 「データ列指定による抽出」より抜粋）

著者は、データ列の選定をする際、氏名などの固有名詞やマスキングされて内容が特定できない列は、データの傾向を把握する分析には利用しづらい要素としてお話しされています。

また数字型に比べて、文字列型はデータサイズが大きくなる傾向にあるため、必要最小限の文字列型項目の抽出を勧めています。

「前処理大全」では、DB上から列を抽出するサンプルコードが紹介されています。本ブログでも、データや環境に合わせて再編したサンプルコードを記載いたします。

『学習の概要』

Pythonで列を抽出する場合、PandasのDataFrameの機能を利用する方法が簡単です。ただし、列の指定方法もさまざまなパターンが提供されており、可読性が高く、データの変更にも強いAwesomeなコードを実現するには、適切な指定方法を学ぶことが大事です。

（参考・参照元：第2章「抽出」2-1 「データ列指定による抽出」より抜粋）

『学習のポイント』

• loc/iloc/ix関数の仕様の特性について学ぶ。

• 抽出する列を指定する場合、列番号指定（iloc関数）より、列名指定（loc関数）の方がコードの可読性が良く、また列の追加／削除があった場合でも、列名で指定しているためコード修正の必要性が無い。

上記『学習のポイント』をもとにコーディングしてみました。

[Windows7][Python, PostgreSQL][jupyter notebook]

#【前処理の学習-12】データに触れる（Windows環境編）②

#!/usr/bin/python
# -*- coding: utf-8 -*-

import pandas as pd
import psycopg2
import sys

con = None
Tbl_Nm ='dly_edit_data_grnd_wthr_obsrv'
sql = 'SELECT * FROM ' + Tbl_Nm + ';'

try:
    con = psycopg2.connect("host='localhost' dbname='weather_data' user='pimientito' password='****'")
    df = pd.read_sql(sql,con)
        
except psycopg2.DatabaseError as e:
    print ('Error %s' %e)    
    sys.exit(1)

else:
    print('iloc関数による抽出')
    print(df.iloc[0:5, 0:4])
    print('')
    print('')
    print('loc関数による抽出')
    print(df.loc[0:5, ['point_number', 'province_code', 'number_of_observations', 'observation_item']])

finally:
    if con:
        con.close()
    print('')
    print('Processing has ended.')

iloc関数とloc関数のコーディング部分のみ切り出しました。

確かにメンテナンスするときには、loc関数で記載されている方が親切ですね。とても見やすいコードです。

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

表示内容は、iloc関数、loc関数とも同じですが、コーディングの視点からは、それぞれ特色がありました。

今回から、はじまりました新しい試み。書籍からの写経だけで終らず、少しずつでも学習内容を実用的に使えるよう進めて行きたいと思います。

今回は、以上です。

【参考資料】

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

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

loc関数

pandas.DataFrame.loc — pandas 0.23.4 documentation

iloc関数

pandas.DataFrame.iloc — pandas 0.23.4 documentation

ix関数

pandas.DataFrame.ix — pandas 0.23.4 documentation

【告知】 10/8 技術書典5へ出品いたしますっ！

まだはじめて間もない「機械学習」ですが、はてなブログ「Pimientitoの機械学習」で綴ってきた内容をブラッシュアップして一冊の同人誌にいたします。

技術書典5

techbookfest.org

サークルカット（出品場所：き01　サークル名：でーたちっぷす

同人誌『非理系ですが「機械学習」はじめました。』の表紙

同人誌『非理系ですが「機械学習」はじめました。』の目次

目次1

目次2

目次3

当日、お近くをお通りの際は、是非お立ち寄りください。よろしくお願いします。