【前処理の学習-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

---

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

　前回は、分散値・標準偏差値について学習しました。

pimientito-handson-ml.hatenablog.com

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

【今回の目標到達点】

　最頻値の算出を学ぶ

【目次】

参考資料のご紹介

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

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

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

「データ集約」の概要

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

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

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

今回のテストデータ

　今回のテストデータは、前回に引き続き「Sportsnavi」の競馬ページより、昨年、2018年12月23日(日) 中山競馬場で行われた、第1レースから第12レースの払戻金一覧データを使用して学習します。

Sportsnavi「競馬」2018年12月23日(日) 競走成績・払戻金一覧

競馬データ「競走成績・払戻金一覧」の概要

　今回のテストデータについて、もう少し詳しくご紹介します。データ構成は、以下の通りです。

項目	カラム名	概要
レース番号	race	1～12
勝馬投票券種類	ticket	単勝,複勝,枠連,馬連, ワイド,馬単,3連複,3連単
払戻金	refund	単位：円

　データ数は、144レコード。上記のテストデータを作成するにあたり、今回、いろいろとデータの加工を行いました。続いて、データ加工の手順について記載します。

データの加工(前処理の前処理)

　今回行ったデータ加工は、以下の条件のデータを、別々に取得し、統合しました。

【要素の相違点】

項目	要素数	概要	HTMLタグ
レース番号	12	1R～12R	p
勝馬投票券種類	8(12)※1	単勝,複勝,枠連,馬連, ワイド,馬単,3連複,3連単	th
払戻金	144※	-	td

　※1「複勝」「ワイド」は、それぞれ3行ずつデータがあるため、勝馬投票券は8種類、12データとなります。

　※2 払戻金の要素数は、レース数(12)ｘ勝馬投票券データ数(12) ＝144レコードとなります。

スクレイピングによるデータ取得の概要

　次に、スクレイピング技術を使ってデータを取得する、一般的な流れを、下記の概要図でご説明します。

スクレイピングによるデータ取得の概要図

　HTMLタグの詳細については、本記事末尾の【参考資料】株式会社シーマン「CMAN - WEBページ作成リファレンス -」のHTMLタグ一覧をご覧ください。

今回のテストデータ取得の概要

　続いてWebから取得した情報のうち、今回必要となるデータを取得するまでの流れを、簡単な概要図でご説明します。

今回使用するデータを取得するまでのスクレイピング概要図

データ取得対象Webページのデータ構成を確認

　上記の概要図だけでは、今回、どのような情報を、どのように取得したか、少々、分かりづらい部分があるため、実際の画面をもとに、もう少し詳細にご説明します。

１．今回のテストデータでは「レース番号」「勝馬投票券種(馬券)」「払戻金」を使用します。下記のWebページ上では、赤枠で囲った部分が、取得対象のデータとなります。

2018年12月23日(日) 中山競馬場 競走成績・払戻金一覧のWebページ

２．Webページ上で確認すると、払戻金のうち「複勝」「ワイド」の欄が、1枠3行で払戻金の情報があることが、分かりました。

払戻金一覧の表構成

３．次にWebページのHTMLコード上から確認を行いますが、その前にWebページのHTMLコードを見るための手順を、簡単にご説明します。

　WebページのHTMLコードをブラウザで表示する方法は、ブラウザによって多少異なりますが、概ね以下のような方法で表示することができます。

　Webページの画面上で、右クリックするとメニューが表示されます。メニューの中から「ソースの表示」(Internet Explorerの場合)または「ページのソースを表示」(Google Chromeの場合)を選択します。

HTML形式のコードを表示(Internet Explorerの場合)

HTML形式のコードを表示(Google Chromeの場合)

　表示されるソースコードの外観は、どのブラウザも、あまり違いはありませんので、ここでは、Internet Explorerの画面のみ、ご紹介します。

Webページのソースコード表示例(Internet Explorerの場合)

４．本題に戻り、ブラウザ上でHTML形式のコードを確認したところ、取得するデータのタグ属性が、それぞれ異なることが分かりました。残念ながら、単一なコーディングでは、すべての情報を一括に取得できないため、タグごとに適したコーディングが必要となります。

　データの属性は、以下の図の通りです。

レース番号が格納されているタグ情報

勝馬投票券種と払戻金が格納されているタグ情報

　スクレイピング技術を学習する前は「Web上から、ほしい情報を、即座に取得できる技術」と思っていましたが、実際には、取得するデータの構成や構造を、事前に、よく観察し、コーディングする必要があることを学びました。

　スクレイピングに特化したライブラリや、APIが、いろいろとあるようですので、今後もオープンデータの取得で、積極的に使用していきたいと思います。

テストデータ加工用コーディング

　以下が、今回作成したスクレイピングのコードです。取得するタグ属性に適したコーディングを行いましたので、処理ごとに区切って表示しています。

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

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

　なお、今回のコードの中でも、URLを指定する部分はありますが、敢えて伏字に変更しています。ご理解のほど、何卒よろしくお願いいたします。

　はじめに、Webページにアクセスし、Webページの情報をファイル単位で取得します。

　なお、コード内にはurlopen( )関数のみで、HTTPコネクションのClose処理がありませんが、参考資料「Python クローリング&スクレイピング - データ収集・解析のための実践開発ガイド」 加藤 耕太氏著(技術評論社)(p.43)によると、HTTPコネクションは、自動的に閉じられると記載されています。

[Windows7][Python][Jupyter Notebook]

#【前処理の学習-24】データを学ぶ ～集約～⑤ 前処理の前処理
#スクレイピングを使用したデータ収集

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

#Pandasモジュール
import pandas as pd
from pandas import DataFrame as df_race_result     #レース払戻金一覧

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

#スクレイピング用モジュール
from urllib.request import urlopen
import lxml.html

#list変数宣言
list_race_numbers = []
list_ticket_types = []
list_refunds = []

#URLを指定して、ファイルオブジェクトを取得
#"****"に、WebページのURLが入ります。
html_tree = lxml.html.parse(urlopen('****'))

#HTML要素の取得
html_element = html_tree.getroot()

　続いて「レース番号」に関するデータを取得します。レース番号は、1R～12Rの計12レコードのデータですが、勝馬投票券の要素数が、1レースにつき、12レコードあるため、データの統合を考え、事前に1レースごとに、12回のループを行い、リスト変数に代入しています。

　また1R、2R、3R･･･と、レース番号を表す「R」を、事前に削除して、文字型から数値型に変換しています。

#レース番号情報の取得
#<p>タグを検索
for element_item_race_number in html_element.cssselect('p'):
    
    #<p>タグのtext属性の値が、None以外
    if element_item_race_number.text is not None:
        
        #レース番号(1R, 2R...)の場合
        if re.search(r'.*R', element_item_race_number.text) is not None:

            #文字列末尾の'R'(Race)の文字を削除
            item = element_item_race_number.text
            item = item.replace('R', '')
            
            #int型にキャストして、リスト型変数へ格納(払戻金要素数(12データ)繰り返す)
            for counter in range(12):
                list_race_numbers.append(int(item))

#結果の確認
print(list_race_numbers)

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

レース番号のリスト化

　勝馬投票券の種類は、全部で8種ですが「複勝」「ワイド」は、3レコード必要なため、条件式で判別して、リスト変数に3行追加するようにしています。

　また<th>タグでは、不要な情報(タイム（差）, 着順, 馬番, 馬名, 枠番)も含まれているため、reモジュールを使用して、正規表現で、不要なデータを取得しないようにしています。

#勝馬投票券情報の取得
#<th>タグを検索
for element_item_ticket_type in html_element.cssselect('th'):
    
    #<th>タグのtext属性の値が、None以外
    if element_item_ticket_type.text is not None:
        
        #勝馬投票券以外の情報は、除外(タイム（差）, 着順, 馬番, 馬名, 枠番)
        #除外対象の正規表現(".順", ".番", ".名", "タイム.*")
        if re.search(r'.順', element_item_ticket_type.text) is not None:
            continue
            
        elif re.search(r'.番', element_item_ticket_type.text) is not None:
            continue
            
        elif re.search(r'.名', element_item_ticket_type.text) is not None:
            continue
            
        elif re.search(r'タイム.*', element_item_ticket_type.text) is not None:
            continue
            
        else:
            #勝馬投票券が「複勝」の場合、同一内容を３行作成
            if element_item_ticket_type.text == '複勝':
                list_ticket_types.append(element_item_ticket_type.text)
                list_ticket_types.append(element_item_ticket_type.text)
                list_ticket_types.append(element_item_ticket_type.text) 
                
            #勝馬投票券が「ワイド」の場合、同一内容を３行作成
            elif element_item_ticket_type.text == 'ワイド':
                list_ticket_types.append(element_item_ticket_type.text)
                list_ticket_types.append(element_item_ticket_type.text)
                list_ticket_types.append(element_item_ticket_type.text) 
            
            #上記以外は、すべて一行作成
            else:
                list_ticket_types.append(element_item_ticket_type.text)

#結果の確認
print(list_ticket_types)

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

勝馬投票券のリスト化

　払戻金は、144レコード分のデータを読み込むだけですが、文末に「円」が含まれているため、事前に削除して、文字型から数値型に変換しています。

#払戻金情報の取得
#<td>タグを検索
for element_item_refund in html_element.cssselect('td'):
    
    #<td>タグのtext属性の値が、None以外
    if element_item_refund.text is not None:
        
        #払戻金(～円)の場合
        if re.search(r'.*円', element_item_refund.text) is not None:
            
            #文字列末尾の'円'の文字を削除
            item = element_item_refund.text
            item = item.replace('円', '')
            
            #int型にキャストして、リスト型変数へ格納
            list_refunds.append(int(item))

#結果の確認
print(list_refunds)

払戻金のリスト化

　最後に、リスト変数「レース番号」「勝馬投票券」「払戻金」を、PandasのDataFrame変数へ代入します。

#情報の結合
#レース番号（1R～12R)
#勝馬投票券種(8種類)単勝, 複勝, 枠連, 馬連, ワイド, 馬単, 3連複, 3連単
#払戻金(12種類)単勝x1, 複勝x3, 枠連x1, 馬連x1, ワイドx3, 馬単x1, 3連複x1, 3連単x1

#DataFrame作成
df_race_result = pd.DataFrame({'race':list_race_numbers, 'ticket':list_ticket_types, 'refund':list_refunds})

#結果の確認
print(df_race_result)

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

DataFrame「df_race_result」のprint( )実行結果

DataFrame「df_race_result」のhead( )実行結果

DataFrame「df_race_result」のdescribe( )実行結果

　以上で、今回のテストデータの加工が完了しました。

　前回【前処理の学習-23】でご紹介したデータ加工では、Webページの画面コピーを取り、表計算ソフトを使って、編集・CSVファイル化しましたが、即席な対応としては、前回の方が、明らかにテストデータの作成が速いです。

　しかし定型的な処理や、大量のWebページから、同様なデータを取得する場合は、やはりスクレイピング技術は、とても有効な手段だと、あらためて思いました。

最頻値の算出

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

代表値は数値データだけではなくカテゴリ値にも存在します。それは最頻値です。最頻値とは、最も多く出現している値のことです。数値でも、カテゴリ値に変換することによって最頻値を利用できます。

参考・参照元：第3章「集約」3-5「最頻値の算出」(P.070)より抜粋

　また数値をカテゴリ値に変換する例として、著者は、以下のように述べています。

数値を四捨五入で整数化したり、100ごとのレンジで値をカテゴリ化(十の桁を切り捨てして、143→100、1233→1200などの変換)して、最頻値を利用します。

参考・参照元：第3章「集約」3-5「最頻値の算出」(P.070)より抜粋

　さっそく今回のテストデータを使用して、学習を進めます。

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

　参考資料「3-5 最頻値の算出」で紹介されているPythonコードを元に、今回のテストデータを分析します。

　今回使用する構文は、以下の通りです。

　data_source['column'].round(numbers).mode( )

　参考・参照元：「python_awesome.py(抜粋)」（p.074）を参考に作成

　【補足】

項目名	概要
data_source	データ源泉
column	集約対象カラム
round	指定した位置より下の値を四捨五入 正数：小数点以下の桁数を指定 負数：小数点以上の桁数を指定
numbers	round( )関数の引数
mode	最頻値を算出

[Python]データの最頻値を算出

　参考資料の構文をもとに、今回作成したテストデータから、最頻値を算出します。

[Windows7][Python][Jupyter Notebook]

#【前処理の学習-24】データを学ぶ ～集約～⑤
#最頻値を算出

#2018年12月23日(日) 中山競馬場で行われた第1レース～第12レースの払戻金のうち
#最も回数が多かった払戻金額を算出(100円単位未満は四捨五入)
#(平均金額とは異なる)
df_race_result['refund'].round(-2).mode()

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

1R～12Rすべての勝馬投票券で払い戻された金額の最頻値

　第1レースから第12レースまでの間で、すべての勝馬投票券の払戻金が「200円」と出ました。筆者は、競馬を嗜まないため、通常一日平均で、いくら競馬に投資するのか分かりませんが、ちょっと、さびしい金額です。

　最後に、もう少し最頻値を算出してみます。今度は、各勝馬投票券ごとにグループ化し、それぞれの勝馬投票券の払戻金の最頻値を算出してみます。

　groupby( )関数で使用できる集約関数には限りがありますのでlambdaキーワードを使用して無名関数を作成し、apply( )関数で実行します。

#apply関数を使用して集約関数を利用

#最頻値を算出する関数を、lambdaキーワードで作成
mode_func = lambda df: df['refund'].round(-2).mode()

#勝馬投票券でグループ化し、最頻値を算出
result = df_race_result.groupby('ticket').apply(mode_func)

#結果の確認
print(result)

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

各勝馬投票券に払い戻された金額の最頻値

　勝馬投票券ごとにグループ化すると、払戻金額に開きが出てきました。なお、複数行表示されている場所は、最頻値が複数あったことを意味します。

【今回のまとめ】

　今回は、スクレイピング技術を使用して、テストデータの取得・加工するところで、時間を取られ過ぎた感がします。しかし機械学習を学習する上で「データ収集」は、非常に重要な要素のため、引続きスクレイピングについても、学習を進めていきたいと思います。

　最頻値の算出については、筆者のPython習熟度が、まだまだ低いため、せっかく取得したデータを、いろいろな角度から検証することができませんでした。

　例えば、PandasのDataFrameの扱いを、より高度に行えれば、勝馬投票券でグループ化した払戻金額を、相互に比較したり、また可視化することで、どの枠で投票券を購入すれば、より確実な収益に繋がる可能性が高いか、なども分析できるかもしれません。

　ブログの回数を追うごとに、いろいろな課題に突き当たりますが、少しずつでも前に進めたらと思いながら、これからも学習を続けます。

　今回は、以上です。

【参考資料】

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

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

test.py「listやarrayからPandas DataFrameを作成 」Shoto氏著

testpy.hatenablog.com

株式会社シーマン「CMAN - WEBページ作成リファレンス -」HTMLタグ一覧

HTMLタグ 機能別一覧 【HTMLリファレンス】

note.nkmk.me「pandasで行・列ごとの最頻値を取得するmode」nkmk.me氏著

note.nkmk.me

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

　前回は、データの極値・代表値について学習しました。

pimientito-handson-ml.hatenablog.com

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

【今回の目標到達点】

　データのばらつき具合の算出手法を学ぶ

【目次】

参考資料のご紹介

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

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

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

「データ集約」の概要

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

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

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

今回のテストデータ

　今回のテストデータは「Sportsnavi」の海外サッカーページより、スペインリーグ総合順位表のデータを使用して学習します。

「Sportsnavi」トップページ

「スペインリーグ 総合順位表データ」の概要

　今回のテストデータについて、もう少し詳しくご紹介します。はじめに、データ構成は、以下の通りです。

項目	概要
順位	1位～20位まで表記
チーム名	日本語表記
勝点	90分での勝利：3点 延長戦での勝利：2点 引き分け：1点
試合数	16試合～17試合
勝数	勝った試合数
引分数	引き分けた試合数
負数	負けた試合数
得点	味方がゴールした点数
失点	敵にゴールされた点数
損失点差	得点-失点=正数または負数

　今回のデータは、Webページに記載されている情報を直接使用しました。本来ですと「クローリング」や「スクレイピング」技術を駆使して「カッコイイ」データ収集をご紹介したいところですが、まだまだ未習熟なことが多いため、今回は、非常に"泥臭い"手法で、学習用データを加工しました。

データの加工(前処理の前処理)

　今回は、Webページ上に表示されているデータを、そのままコピーしてテキストファイルを作成しています。詳細は、以下の手順の通りです。

　【データ加工概要】

---

１．Webページ上の該当データを選択し、コピーします。

Sportsnavi「海外サッカー(スペイン)」総合順位表からデータをコピー

２．コピーしたデータを、テキスト(メモ帳など)(※)に貼り付けます。

　(※) メモ帳などを使用する理由は、Webから直接取得した文字列には「目に見えない」不要な情報(改行コードや、リンク情報など)が入っている場合があるため、テキストアプリを使用して、必要な文字列のみを取得します。

コピーしたデータをテキストに貼り付け

３．テキストに貼り付けた文字列を全選択して、再度コピーします。

４．コピーした文字列を、Excelなどの表計算アプリに貼り付けます。

コピーした文字列をExcelに貼り付け

５．ここからは、Excel上でのデータ加工になります。貼り付けた文字列の順序などが①と②で異なっています。それぞれ適切に整理して、ひとつのテーブルにまとめます。

加工前のデータ

６．図「加工前のデータ」上の①の場合は、データのカラムが縦に表示されています。不要な空行を削除してから、すべてを選択コピーし、右クリックから「行列を入れ替える」を指定して貼り付けます。

コピー&貼り付け「行列を入れ替える」

７．図「加工前のデータ」上の②の場合は、レコード単位のデータが、スペース区切りになっていますので、データの列を全選択してから「データ」＞「区切り位置」を選択して「スペース」で区切り、繋がっている文字列を、セル単位に分割します。(より詳細なExcel操作については、割愛します)

Excel機能「区切り位置」

８．Excelシート上に、データテーブルができました。

Excelシート状のデータテーブル

９．作成したテーブルを保存する際、ファイルの種類を「CSV UTF-8(コンマ区切り)」(Excel for Office 365の場合)に指定して保存します。

１０．仕上がったデータファイルは、以下のようになります。筆者の場合「カラム名」と「サッカーチーム名」を日本語表記から、英語表記に修正しています。

データ加工後のCSVファイル

---

　今回のデータ加工は、少々、手間は掛かりましたが、特殊な技術が無くても、なんとかデータ収集はできるものということを学びました。しかし「クローリング」や「スクレイピング」技術を習得すれば、より効率的にデータ収集が行えることは間違いありません。

分散値、標準偏差値の算出

　参考資料「前処理大全」の「3-4 ばらつき具合の算出」の冒頭で、著者は、以下のように述べています。

分散値／標準偏差値は、数値データのばらつき具合を表します。前節の代表値(※)とともに算出することで、数値データの全体の傾向をより表現できるため有用です。

参考・参照元：第3章「集約」3-4「ばらつき具合の算出」(P.066)より抜粋

　(※) 「最小値」「最大値」「中央値」「パーセンタイル」を指します。

　また少ないデータ数で、分散値、標準偏差値を算出する場合の注意点についても、以下のように言及されています。

データ数1のときは、データのバラツキがまったくないということを意味するので、通常は分散値／標準偏差値のどちらの場合も0にしてください。

参考・参照元：第3章「集約」3-4「ばらつき具合の算出」(P.066)より抜粋

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

　ここでは、学習を進めるなかで、出会ったキーワードのうち「もう少し調べてみたい」と思ったものを、ピックアップしています。

分散

　「分散」とは、あるデータが、データ全体の平均からどれくらい離れているかを表す指標のひとつです。分散の英名は「Variance」です。

　分散の大小で、以下のような解釈ができます。

---

• 分散が大きい ー＞ 平均 から遠く離れたデータが多い ー＞ 散らばり大

• 分散が小さい ー＞ 平均 に近いデータが多い ー＞ 散らばり小

---

参考・参照元：高校数学の美しい物語～定期試験から数学オリンピックまで800記事～「分散の意味と二通りの計算方法」より抜粋

　データ全体の平均を (ミュー)で表します。なお (バー エックス)でも、平均を意味するそうですが、本記事では、データ全体の平均は (ミュー)で統一します。

　数式で表現すると、以下のようになります。

　 の部分を二乗することによって、例え平均との差(平均との距離)が負数であっても、正数として扱うことができ、単純に「平均との距離」を算出できます。(例：平均との差が負数の場合、(負数)の二乗 = (負数) x (負数) = (正数)となります)

標準偏差

　「標準偏差」とは、データのばらつき具合を表す指標のひとつです。標準偏差の英名は「Standard Deviation」です。

　標準偏差の大小で、以下のような解釈ができます。

---

• 標準偏差が大きい ー＞ 平均 から遠く離れたデータが多い ー＞ 散らばり大

• 標準偏差が小さい ー＞ 平均 に近いデータが多い ー＞ 散らばり小

---

参考・参照元：高校数学の美しい物語～定期試験から数学オリンピックまで800記事～「標準偏差の意味と分散との違い」より抜粋

　数式で表現すると、以下のようになります。

　" " は「シグマ(小文字)」と読みます。前回【前処理の学習-22】でご紹介した"" (総和)とは、異なる意味となりますので、ご注意ください。

　「分散」も「標準偏差」も、同じ「データのばらつき」を表す指標であり「標準偏差の二乗 = 分散」の関係となります。

　それぞれの詳細について、ご興味のある方は、本記事末尾の【参考資料】でご紹介させていただいている資料をご覧ください。

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

　最後に、参考資料「3-4 ばらつき具合の算出」で紹介されているPythonコードを元に、今回のテストデータを分析します。

　今回使用する構文は、以下の通りです。

　result = data_source.groupby('column1').agg({'column2' : ['var', 'std']}).reset_index( )

　参考・参照元：「python_awesome.py(抜粋)」（p.069）を参考に作成

【補足】

項目名	概要
result	戻り値
data_source	データ源泉
column1	グループ化対象カラム
column2	集約対象カラム
var	集約関数(分散)
std	集約関数(標準偏差)

　またデータ数が1件の場合、戻り値が「na」となるため、0に置換する構文も紹介されています。

　result.fillna(values = {'column1' : 0, 'column2' : 0, ･･･}, inplace=True)

　参考・参照元：「Point」（p.070）を参考に作成

【補足】

項目名	概要
result	DataFrame型変数
fillna	欠損値を、指定した値で穴埋めする関数
values	DataFrameメンバ(属性)
column1, 2	置換対象カラム
inplace	データの直接置換

[Python]データの分散値・標準偏差値を算出

　参考資料のサンプルコードの一部と、テストデータを使って、データの分散・標準偏差を算出しました。

　なお今回使用するデータは、2018年のスペインリーグの成績表で、各チームの成績が、一レコードでまとめられているため、サンプルコードにあるgroupby( )関数やfillna( )関数は割愛しました。

　はじめに、テストデータを読込みます。

[Windows7][Python][Jupyter Notebook]

#【前処理の学習-23】データを学ぶ ～集約～④

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

#CSVファイルの読み込み
df = pd.read_csv("./data/lesson23_sample_data.csv")

　読み込んだテストデータを確認します。

#読み込んだデータを確認
df.head()

df.head( )の結果

　続いて、今回のテーマ「分散値、標準偏差値の算出」を行います。分析対象は「ゴール得点(get_score)」と「ゴール失点(lose_score)」とします。

　また前回【前処理の学習-22】で学んだ「平均値」も、一緒に算出します。

#「前処理大全」第３章集約(p.069)
#python_awesome.py(抜粋)を参考にコーディング

#得点/失点の平均値(mean)、分散値(var)、標準偏差値(std)を求める
#得点
result_get_score = df.agg({'get_score': ['mean', 'var', 'std']})

#失点
result_lose_score = df.agg({'lose_score': ['mean', 'var', 'std']})

　print( )関数で、算出結果を確認します。

#算出結果を確認
print(result_get_score)
print('')
print(result_lose_score)

算出結果の確認

　平均値が「得点」「失点」共に、同値であることは、すぐに理解できるのですが、「得点」「失点」の分散値と標準偏差値の誤差については、前述した「もう少し踏み込んで･･･」の各計算式で、手計算してみないと正誤が判断できませんでした。

　徐々にですが、数学記号を含んだ数式が読めるようになったとはいえ、数式への理解が、まだまだ未熟としか言いようがありません。不足な部分が、たくさん課題としてありますが、今回は、このまま学習を進めます。

「可視化」に挑戦

　今回は、以下の考えで可視化を試みました。

---

• X軸が「チーム名」、Y軸が「得点」または「失点」の棒グラフを作成

• 算出した得点、または失点の「平均値」「分散値」「標準偏差値」を、X軸に平行して表示

• 「平均値」「分散値」「標準偏差値」の凡例は、以下の通りです。

【凡例】

名称	マーク	色
平均値	ー・－	オレンジ
分散値	ーー	スカイブルー
標準偏差値	実線	イエローグリーン

---

　はじめに各チームの得点と、得点の「平均値」「分散値」「標準偏差値」を可視化します。

[Windows7][Python][Jupyter Notebook]

#可視化
import matplotlib.pyplot as plt

#各チームの得点を棒グラフで表示
df.plot.bar(x='team', y='get_score')

#平均値ライン(オレンジ色)
plt.axhline(result_get_score['get_score']['mean'], linewidth=3, linestyle='-.', color='Orange')

#分散値ライン(スカイブルー色)
plt.axhline(result_get_score['get_score']['var'], linewidth=3, linestyle='--', color='SkyBlue')

#標準偏差値ライン(イエローグリーン色)
plt.axhline(result_get_score['get_score']['std'], linewidth=3, color='YellowGreen')

　可視化した結果は、以下の通りです。

2018年スペインリーグ チーム別得点と平均・分散・標準偏差の算出結果

　続いて、各チームの失点と、失点の「平均値」「分散値」「標準偏差値」を可視化します。

#各チームの失点を棒グラフで表示
df.plot.bar(x='team', y='lose_score')

#平均値ライン(オレンジ色)
plt.axhline(result_lose_score['lose_score']['mean'], linewidth=3, linestyle='-.', color='Orange')

#分散値ライン(スカイブルー色)
plt.axhline(result_lose_score['lose_score']['var'], linewidth=3, linestyle='--', color='SkyBlue')

#標準偏差値ライン(イエローグリーン色)
plt.axhline(result_lose_score['lose_score']['std'], linewidth=3, color='YellowGreen')

　可視化した結果は、以下の通りです。

2018年スペインリーグ チーム別失点と平均・分散・標準偏差の算出結果

　

　可視化するにあたって、取得した「分散値」と「標準偏差値」を、どのようにグラフへ反映するものなのか、残念ながら、今回の学習では、理解まで至りませんでした。

【今回のまとめ】

　今回学んだ「分散」や「標準偏差」は、日常でも、よく耳にする言葉で「なんとなく」分かっているような、いないような世界でした。

　記事末尾の【参考資料】に記載した各種資料を、すべて読み、理解できた訳ではありませんが、データを理解するためには、必要な知識であることだけは分かりました。

　多種多様な関数や、ライブラリが、簡単に利用できるとしても、根本的に理解できていないと、誤った判断に陥る危険性を、あらためて学ぶことができました。まだまだ先は長いですが、引続き、幅広い分野に渡って学習することが必要です。

　今回は、以上です。

【参考資料】

データサイエンティスト養成読本 登竜門編「第９章 コーディング前に知りたい統計知識」牧 允皓氏著

データサイエンティスト養成読本 登竜門編 (Software Design plus)

Qiita【統計学】初めての「標準偏差」(統計学に挫折しないために) @kenmatsu4 氏著

qiita.com

Qiita 統計学基礎の基礎 @kenmatsu4 氏著

qiita.com

高校数学の美しい物語～定期試験から数学オリンピックまで800記事～「標準偏差の意味と分散との違い」@mathelegant 氏著

mathtrain.jp

高校数学の美しい物語～定期試験から数学オリンピックまで800記事～「分散の意味と二通りの計算方法」@mathelegant 氏著

mathtrain.jp

SHOYAN BLOG「LATEXチートシート - 数式記号の読み方・表し方 -6.確率・統計-」山崎勝平氏著

48n.jp

Pythonによるデータ分析入門

Pythonによるデータ分析入門 第2版 ―NumPy、pandasを使ったデータ処理

スポーツ辞典 S-Words「勝ち点【勝点制】」

勝ち点とは（勝点制） - 意味/定義 ： スポーツ用語辞典

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

　前回は、Pythonの集約関数sum( )について学習しました。

pimientito-handson-ml.hatenablog.com

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

　【今回の目標到達点】

　データの極値・代表値の算出手法を学ぶ

　【目次】

参考資料のご紹介

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

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

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

「データ集約」の概要

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

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

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

今回のテストデータ

　今回のテストデータは「DATA GO.JP」から、厚生労働省 人口動態・保険社会統計室作成「Vital Statistics_Vital statistics of Japan_Final data_Population_Yearly_2015」より「性別・年代別の人口動態データ」を使用して学習します。

「DATA GO.JP」ホームページ

「性別・世代別の人口動態データ」の概要

　今回のテストデータについて、もう少し詳しくご紹介します。

　「Vital Statistics_Vital statistics of Japan_Final data_Population_Yearly_2015」のデータは、複数のファイルから構成されており、その中で「3_Trends in population by sex and age:Japan」を選択しました。データ構成は、以下の通りです。

項目	概要
調査期間	1935年～2015年
調査周期	約5年
調査世代	0歳～100歳以上
調査世代区間	5歳刻み
対象性別	男性・女性

　ファイルは、CSV形式で、男女混合・男性・女性と3ファイルに分かれており、今回は、男性・女性の統計ファイルのみを使用します。続いてオリジナルデータを、学習用データに加工するための手順概要をご説明します。

データの加工(前処理の前処理)

　インターネットからダウンロードしたオリジナルデータ(以後、"元データ"と記載)のままでは、学習用に適していないため、以下の内容で、データ加工を行いました。

　【データ加工概要】

---

1. 元データでは、行が「調査対象世代」、列が「調査実施年」で構成されており、学習（分析）に適していないため、データ構成を変更しました。(下表、【テストデータ概要】を参照)

2. 「調査対象世代」の項目が、0～4歳、5～9歳、10～14歳、15～19歳･･･と5年刻みで表記されているため、0歳(0歳以上、5歳未満)、5歳(5歳以上、10歳未満)、10歳(10歳以上、15歳未満)、15歳(15歳以上、20歳未満)･･･と、先頭の世代のみ表記することにしました。

3. 「調査対象世代」で、95～99歳と、100歳以上の人口が、男性・女性共に、1935年～2000年までの計13回(全体18回)の統計で、欠測(※)しているため、今回の加工では、95歳以上(男性・女性共に)のデータは、含めないこととしました。

　(※) データが無い状態。欠損、欠落とも呼ばれます。

---

　加工したデータレコードの概要は、以下の通りです。

　【テストデータレコード概要】

項目	カラム名	特記
統計調査年	year	西暦表記
世代	generations	5歳刻み
性別	sex	M(男性)、F(女性)
人口	populations	対象世代の人口

　今回のデータ加工では、Excelを使用して、男性用・女性用データのCSVファイルの内容を、コピー＆ペーストを繰り返しながら、手作業で行いました。ファイルを保存する際は、「CSV UTF-8(コンマ区切り)」を指定して保存してください。

極値、代表値の算出

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

異なるデータ群（集合）を比較する際に、同じ列の数値データの平均値同士を比較することは分析の基本です。

（途中、省略）

ただし、データの分析（ばらつき具合）を把握しないで、平均値の結果を鵜呑みすると間違った認識をしてしまいます。

（途中、省略）

平均値が同じでも、データの分布が異なれば、データの特性は大きく異なります。

参考・参照元：第3章「集約」3-3「極値、代表値の算出」(P.061)より抜粋

　著者は、データの特徴を、より正確に把握するためには、平均値のみに頼らず、最小値、最大値、中央値、パーセンタイル、分散、標準偏差なども、併せて確認することが必要だと述べています。

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

　学習を進めるなかで出会ったキーワードで「もう少し調べてみたい」と思ったものだけを、ピックアップして記載しています。

実は「平均値」を求める定義は複数あった！

　いままで、平均値を算出する際は、すべての値を加算して、その合計値を、値の個数で割るだけの単純なものだと思っていましたが、実は平均値を求める定義が、複数あることを、今回の学習を通して、はじめて知りました。

　以下、定義名や数式を、簡単ですがまとめました。参考・参照元の詳細については、記事末尾の【参考資料】をご覧ください。

• 算術平均(一般的な平均)

　私たちが、日常のあらゆる場面で利用する「平均」は、数学・統計学では「算術平均」または「相加平均」と呼ばれます。数式で表現すると、以下のようになります。

　参考・参照元：Wikipedia「算術平均」より抜粋

　この数式では「aの1番目からaのn番目までをすべて加算し、n個で除算しなさい」という意味になります。

　なお""は「シグマ」と読み、数学用語では「総和」と呼ばれます。

• 幾何平均

　「幾何平均」または「相乗平均」は、「算術平均」のように、すべての数を加算する代わりに、乗算し、その積の冪根(べきこん)(※)をとることで得ることができます。数式で表現すると、以下のようになります。

　(※) 乗算する数値がN個の場合、N乗根となります。

　参考・参照元：Wikipedia「幾何平均」より抜粋

　この数式では「aの1番目からaのn番目までをすべて乗算し、n乗根をとりなさい」という意味になります。

　なお""は「パイ」と読み、数学用語では「総乗」と呼ばれます。

• 調和平均

　「調和平均」は、率の平均を求められる場面で利用されるそうです。

正の実数について、調和平均は逆数の算術平均の逆数として定義される。

　参考・参照元：Wikipedia「調和平均」より抜粋

　完全に筆者の理解の及ばない世界になりつつあります。例として「速度の平均」を正確に算出する場合に利用されるようです。数式で表現すると、以下のようになります。

　参考・参照元：Statistics How To「What is the Harmonic Mean?」より抜粋

　平均値を求める定義だけでも、いろいろとあり、あらためて数学の奥深さを痛感しました。「機械学習」の学習を進めていくためには、避けて通れない「数学」の世界を少しずつですが、着実に歩んでいきたいと思います。

　今回学ぶ「中央値」と「パーセンタイル」についても、ざっと調べてみました。

「中央値」とは

代表値の一つで、有限個のデータを小さい順に並べたとき、中央に位置する値。

(途中、省略)

ただし、データが偶数個の場合は、中央に近い2つの値の算術平均をとる。

　参考・参照元：Wikipedia「中央値」より抜粋

　中央値と平均値は、類似した目的に使用されることが多いですが、場合によって、平均値より中央値の方が優れていることがあるとも記載されています。

　「データが偶数個の場合、中央に近い2つの値の算術平均をとる。」という件では、思わず「なるほどっ!」と感心してしまいました。

「パーセンタイル」とは

計測値の分布(ばらつき)を小さい数字から大きい数字に並べ替え、パーセント表示することによって、小さい数字から大きな数字に並べ替えた計測値においてどこに位置するのかを測定する単位。

　参考・参照元：企業年金連合会 用語集「パーセンタイル(percentile)」より抜粋

　以上が、今回の「もう少し踏み込んで･･･」で調べた内容です。「分散」と「標準偏差」については、次回のテーマで、少し掘り下げてみます。

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

　最後に、参考資料「前処理大全」3-3「極値、代表値の算出」で紹介されているPythonコードを元に、今回のテストデータを分析します。

　今回使用する構文は、以下の通りです。

　result = data_source.groupby('column1').agg({'column2' : ['max', 'min', 'mean', 'median', lambda x: np.percentile(x, q=20) ]}).reset_index( )

　参考・参照元：「python_awesome.py(抜粋)」（p.065）を参考に作成

　【補足】

項目名	概要
result	戻り値
data_source	データ源泉
column1	グループ化対象カラム
column2	集約対象カラム
lambda x:	lambda式(※)

　(※) lambda式(ラムダ式)は、別名「無名関数」と呼ばれ、構文はlambda 引数: 式と記載します。プログラム中に、簡易な関数を作成する場合に有効です。今回のサンプルでは、agg関数内で、パーセンタイル値の集計のため、lamda式が組み込まれています。 　

[Python]データの極値・代表値を算出

　参考資料のサンプルコードと、テストデータを使って、データの極値・代表値を算出しました。はじめにテストデータを読み込みます。

　[Windows7][Python][Jupyter Notebook]

#【前処理の学習-22】データを学ぶ ～集約～③

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

#CSVファイルの読み込み
df = pd.read_csv("./data/lesson22_sample_data.csv")

　読み込んだテストデータを確認します。

#読み込んだデータを確認
df.head()

df.head( )の結果

　続いて、今回のテーマ「極値、代表値の算出」を行います。なお、今回は「性別」でグループ化し、男性・女性それぞれ人口の最大値、最小値、平均値、中央値、パーセンタイル(20%)を取得します。

#「前処理大全」第３章集約(p.065)
#python_awesome.py(抜粋)を参考にコーディング

#統計情報を取得
result_vital_statistics = df.groupby('sex').agg({'populations':['max', 'min', 'mean', 'median', lambda x: np.percentile(x, q=20)]}).reset_index()

#カラム名の付与
result_vital_statistics.columns = ['sex', 'populations_max', 'populations_min', 'populations_mean', 'populations_median', 'populations_per20']

#集約したデータを確認
result_vital_statistics.head()

極値、代表値の算出結果

　本来は、「世代別」「性別」でグループ化して、極地、代表値を算出したかったのですが、このあとに続く可視化で、表示結果が、あまりにも細かくなり過ぎるため、可視化の表示を単純化するため、敢えてグループ化する項目をひとつにしました。

#可視化(棒グラフ)
#性別ごとに「最大値」「最小値」「平均値」「中央値」「20パーセンタイル」を表示
result_vital_statistics.plot.bar(x='sex')

人口動態データから極値、代表値を算出(男女比)

　複数の項目を、どのように組み合わせて可視化してよいのか、整理ができていないため、せっかく取得したテストデータも、上手く生かすことができませんでした。残念ですが、現在の実力を真摯に受け止め、学習を続けるほかありません。

【今回のまとめ】

　今回から、参考資料に出てくる「数学的」「統計学的」な要素にも、率先して取り組むようにしました。

　いままでは、単に関数に値を渡して、それらしい戻り値を、そのまま、その後の処理に利用していましたが、今後は、少しずつですが、できる限り、インプット・アウトプットの値の妥当性について検証できるよう、数学的・統計学的な知識も、この「機械学習」の学習と共に吸収していきたいと思います。

　今回は、以上です。

【参考資料】

DATA GO.JP (データカタログサイト)

www.data.go.jp

データサイエンティスト養成読本 登竜門編

データサイエンティスト養成読本 登竜門編 (Software Design plus)

高校数学の美しい物語「平均値，中央値，最頻値の求め方といくつかの例」

mathtrain.jp

【統計学】初めての「標準偏差」（統計学に挫折しないために）

qiita.com

Wikipedia「算術平均」

算術平均 - Wikipedia

Wikipedia「幾何平均」

幾何平均 - Wikipedia

Statistics How To「What is the Harmonic Mean?」

www.statisticshowto.datasciencecentral.com

Wikipedia「中央値」

中央値 - Wikipedia

企業年金連合会 用語集「パーセンタイル(percentile)」

www.pfa.or.jp

LaTexコマンド集

www.latex-cmd.com

はてなブログのTex記法チートシート（自分用メモ）

k-mawa.hateblo.jp

【更新履歴】

---

2018.12.24

[記事]

LaTex表記内の日本語が、スマートホン上で文字化けしてしまうため、以下のように名称を英名に変更しました。

算術平均 -> Arithmetic Mean

幾何平均 -> Geometric Mean

調和平均 -> Harmonic Mean

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

　前回は、Pythonのgroupby( )関数を利用して「集約」について学びました。

pimientito-handson-ml.hatenablog.com

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

【今回の目標到達点】

　集約関数を使って、データを指定単位にまとめる

【目次】

参考資料のご紹介

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

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

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

「データ集約」の概要

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

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

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

　さっそく参考資料「3-2 合計値の算出」（p.058～）から学習します。

今回のテストデータ

　前回【前処理の学習-20】と同じように、今回もオープンデータを利用します。今回は「気象データ」と、東京電力の「電力使用実績データ」を使って、学習を進めます。

「気象データ」の概要

　気象データは、気象庁のホームページからダウンロードすることができます。リンクは【参考資料】に記載していますので、利用方法については、気象庁のホームページをご覧ください。

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

　気象データの概要は、以下の通りです。

項目	カラム名	概要	特記
年月日	DATE	2017/01/01～2017/12/31	-
時刻 (単位：1時間)	TIME	00:00～23:00	-
気温 (単位：°C)	Temperature	一時間ごとの気温	観測地(東京)

「電力使用実績データ」の概要

　電力使用実績データは、東京電力パワーグリッド株式会社の「でんき予報」のページから、データをダウンロードしました。

東京電力パワーグリッド株式会社「でんき予報」ホームページ

　電力使用実績データの概要は、以下の通りです。

項目	カラム名	概要	特記
年月日	DATE	2017/01/01～2017/12/31	-
時刻 (単位：1時間)	TIME	00:00～23:00	-
実績 (単位：万kW	Power Consumption	時間単位の消費電力量	観測地(東京)

　【ご注意ください】

　ダウンロードしたデータを、そのまま利用すると、不要な項目や行があるため、分析・表示が正常に行えません。事前に、Excelなどの表計算ソフトを利用して、CSVファイルを加工してください。

　上記、二つのデータを、CSVファイルに、まとめて学習に使用します。

【余談】電気事業者ごとに異なるデータ・フォーマット

　日本の主な電気事業者は、以下の通りです。

名称	名称
北海道電力	関西電力
東北電力	中国電力
東京電力	四国電力
北陸電力	九州電力
中部電力	沖縄電力

参考・参照元：Wikipedia「日本の電力会社」より抜粋

　今回、全国の電力会社のオープンデータを利用しようと思い、数か所のデータをダウンロードしました。しかし電気事業者ごとに、データの内容や期間、ダウンロードできるCSVファイルの取得単位が、微妙に異なることを知りました。

　日本全域の電力消費量を、横断的に利用する場合、単位やフォーマットを合わせるための「前処理の前処理」が必要となりそうです。

　今回、調べた4事業者の違いを、ほんの一部ですが、ご紹介します。

CSVファイルの内容

　電気事業者ごとに、データの内容や、観測単位が異なっていました。詳細は、以下の通りです。

電力会社	1ファイル内に含まれるデータ	特記
北海道電力	1日分(1レコード/時)	-
東京電力	1年分(1レコード/時)	-
九州電力	1ヶ月分(1レコード/日)	(※1) 4:00-5:00 6:00-7:00 7:00-8:00 8:00-9:00 9:00-10:00 18:00-19:00
沖縄電力	1日分 (1レコード/時) または (1レコード/5分)	(※2)

(※1)　九州電力の場合「実績最大電力発生時間帯」の1時間が単位時間のため、毎日同じ時刻で計測しているデータではないようです。

(※2)　沖縄電力の場合、1ファイルの中に、1時間単位の計測データと、5分刻みの計測データが混在しているため、事前のデータ加工が必要になりそうです。

カラム(項目)

　カラム名や数も、事業者によって異なり、特に九州電力は、他の事業者と比べて、異なる点が多い印象でした。

電力会社	カラム名
北海道電力	DATE, TIME, 当日実績(万kW), 予測値(万kW), 使用率(%)
東京電力	DATE, TIME, 実績(万kW)
九州電力	DATE, ピーク時供給力(万kW), 予想最大電力(万kW), 予想使用率(%), 実績最大電力(万kW), 実績最大電力発生時間帯
沖縄電力	DATE, TIME, 当日実績(万kW), 予測値(万kW), 使用率(%)

CSVファイルのダウンロード単位

電力会社	ダウンロード単位	特記
北海道電力	3ヶ月分(約90ファイル)を圧縮 (計4圧縮ファイルで一年分)	-
東京電力	1ファイル/年	-
九州電力	1ファイル/月	-
沖縄電力	1ファイル/日	365回のダウンロードが必要

　沖縄電力の場合、1ファイルずつダウンロードするため、スクレイピングなどの手法も併せるなど検討が必要です。

　以上【余談】でした。「集約」の学習に戻ります。

[Python]sum( )関数を利用した合計値の算出

　参考資料「前処理大全」の「3-2 合計値の算出」(p.058)では、最も利用頻度の高い集約関数sum( )をご紹介しています。

　今回の記事では、Pythonの利用に焦点を当て、参考資料でご紹介されている集約構文を、以下に記載します。

　result = data_source.groupby(['column1', 'column2'])['column3'].sum( ).reset_index( )

　参考・参照元：「python_awesome.py(抜粋)」（p.060-061）を参考に作成

　【補足】

項目名	概要
result	戻り値
data_source	データ源泉
column1	グループ化する列(カラム)
column2	グループ化する列(カラム)
column3	集約対象の列(カラム)

　この例文では、column1とcolumn2でグループ化し、column3の列のデータを集計しています。末尾のreset_index( )関数は、集約した行に対してインデックスを振り直す役割を持ちます。

　また同ページには、集約したデータのカラム名を、リネイムする構文も記載されているため、ご紹介します。

　result.rename(columns={'old_column_name': 'new_column_name'}, inplace=True)

　参考・参照元：「python_awesome.py(抜粋)」（p.060-061）を参考に作成

　【補足】

項目名	概要
old_column_name	変更前のカラム名
new_column_name	変更後のカラム名
inplace=True	データを直接更新

　前回【前処理の学習-20】で学んだagg( )関数は、同時に複数のカラム(列)に対して、処理を行っていましたが、今回の集約構文は、集約処理が一つの場合を想定したものとされています。

　また、カラム名のリネイム処理も、rename( )関数を利用する方法と、data_source.columns = ['column_name1', 'column_name2', ....]といった、直接、カラム名を更新する方法もあり、著者は、状況に応じて使い分けることを勧めています。

今回のテストデータを使って学習してみる

　今回のテストデータを、あらためて記載します。「気象データ」と「電力使用実績データ」はともに、2017/01/01～12/31の東京で観測されたものです。

項目	カラム名	概要
年月日	DATE	2017/01/01～2017/12/31
時刻	TIME	00:00～23:00
気温	Temperature	一時間ごとの気温 (単位：℃)
電力使用実績	Power Consumption	一時間ごとの使用電力 (単位：MkW)

　残念ながら、合計値を見るだけでは、少し物足りないデータです。また集約対象が「気温」と「電力使用実績」と、一つ以上あるため、今回の学習では、以下の内容で進めていきます。

1. 24時間のデータを、日単位で集約し「気温」と「電力使用実績」の平均値を算出する。

2. 日単位のデータを、月単位で集約し「気温」と「電力使用実績」の平均値を算出する。

3. 月単位の「気温」と「電力使用実績」の平均値を使用して、年間の推移を可視化する。

　さっそく「分析」してみましょう。

データの読み込み

　今回使用する「気象データ」と「電力使用実績データ」は、事前にExcelでまとめて、UTF-8形式のCSVファイルとして作成し直しました。

[Windows7][Python][Jupyter Notebook]

#【前処理の学習-21】データを学ぶ ～集約～②

#Padasモジュール
import pandas as pd
from pandas import DataFrame as df

#CSVファイルの読み込み
df = pd.read_csv("./data/lesson21/lesson21_sample_data.csv")

　読み込んだデータを確認します。

#読み込んだデータを確認
df.head()

df.head( )の結果

　続けて、読み込んだデータの概要を確認します。

#読み込んだデータの統計情報
df.describe()

df.describe( )の結果

　この結果から、2017年一年間の東京の平均気温が、約15.9℃だったことが分かりました。

日単位で、データを集約

　ここから、集約を行います。はじめに1時間単位で取得されているデータを、日単位で集約して、1日の平均気温と電力使用実績の平均値を算出します。

#日単位でグループ化し、集約
result_days = df.groupby('DATE').agg({'Temperature':'mean', 'Power Consumption':'mean'}).reset_index()

#結果の確認
result_days.head()

日単位で集約し平均値を算出

　1日単位で、平均値が算出されていることが確認できました。なお、行が日付順に並んでいないことについて、ここでは注視しません。

データの一部を型変換(object型からdatetime型へ)

　つぎに、月単位でデータを集約したいのですが、カラム「DATE」の型が、Object型のため、集約処理が容易ではありません。そのため、対象のカラムを、Pandasのto_datetime( )関数を利用して、型キャスト(型変換)します。

　サンプルコードでは、型キャストする前後にinfo( )関数を使って、データの型を確認しています。

#型変換前の確認
result_days.info()

#カラム「DATE」を"オブジェクト型"から"datetime型"へ変換
result_days['DATE'] = pd.to_datetime(result_days['DATE'])

print() #空行

#型変換後の確認
result_days.info()

to_datetime( )を利用して型キャストした結果

　変換前後のカラム「DATE」を見ると"object"から"datetime"に変わっていることが確認できます。

月単位で、データを集約

　日付型に変換した「DATE」を利用して、月単位でデータを集約します。

#月単位でグループ化し、集約
result_month = result_days.groupby(result_days['DATE'].dt.month).agg({'Temperature':'mean', 'Power Consumption':'mean'}).reset_index()

#結果の確認(12ヶ月表示)
result_month.head(12)

月単位で集約し平均値を算出

　月単位で集約するには、"YYYY-MM-DD"(西暦-月-日)の形式から、"MM"(月)だけを取り出したいため、groupby( )関数の引数にresult_days['DATE'].dt.monthを指定します。

　集約した結果を見ると、まずまずの内容ではないでしょうか。ここで「Temperature」と「Power Consumption」の小数点以下の桁数が多いことに、気が付きます。次に、端数処理を行います。

端数処理

　小数点以下の桁数が多いと、分析の精度は高くなると思いますが、ここでは、以下の条件で端数処理を行います。

• 「気象データ」は、小数点第二位以下を、四捨五入する。

• 「電力使用実績」は、小数点第一位以下を、四捨五入する。

　端数処理は、round( )関数を利用します。

#集約データの端数処理
result_month = result_month.round({'Temperature':1, 'Power Consumption':0})

#結果の確認(12ヶ月表示)
result_month.head(12)

round( )関数の結果

　データが、すっきりして読みやすくなりました。最後は、集約データの可視化に挑戦します。

集約データの可視化

　今回の可視化では、DATUM STUDIO株式会社のブログ「楽屋」を参考にさせていただきました。参考ブログの詳細は、本記事末尾の【参考資料】をご覧ください。

#可視化(2軸グラフ)
import numpy as np
import matplotlib.pyplot as plt
from  matplotlib import cm
import matplotlib.ticker as ticker


#月間平均気温(2017年1月～12月　東京 単位：℃/月)
temp = pd.DataFrame(result_month, columns=['DATE', 'Temperature'])

#月間電気消費量(2017年1月～12月　東京 単位：MW/月)
power = pd.DataFrame(result_month, columns=['DATE', 'Power Consumption'])

plt.style.use('ggplot')

fig, ax1 = plt.subplots()
ax2 = ax1.twinx()

ax1.grid(True)
ax2.grid(False)

ax1.set_axisbelow(True)

color_temp = cm.Set1.colors[1]
color_power = cm.Set1.colors[7]

ax1.bar(power['DATE'], power['Power Consumption'], color=color_power, label='Power Consumption')
ax2.plot(temp['DATE'], temp['Temperature'], color=color_temp, label="Temperature")

ax2.spines['left'].set_color(color_power)
ax2.spines['right'].set_color(color_temp)

ax1.yaxis.set_major_formatter(ticker.FormatStrFormatter('%dMkW'))
ax2.yaxis.set_major_formatter(ticker.FormatStrFormatter('%d°C'))

power_max = 1.25 * max(power['Power Consumption'])
temp_max = 1.25 * max(temp['Temperature'])

ax1.set_ylim([0, power_max])
ax2.set_ylim([0, temp_max])


#参考・参照元：DATUM STUDIO株式会社　ブログ「楽屋」"Matplotlib-2軸グラフの書き方"を参考に作成

2017年東京の平均気温と電力使用実績平均の推移

　可視化したデータの精度については、いささか不安ですが、オープンデータの取得から集約・可視化まで、ひと通り行ってきました。

　まだまだ自力では、可視化まで辿りつけませんが、これからも、こつこつと学習するしか、ほかに道はありません。

【今回のまとめ】

　今回のテーマ「合計値の算出」からは、少し外れてしまいましたが、とても良い勉強になりました。次回は、もう少しテーマに合ったデータを、先に探してから学習をはじめたいと思います。

　今回は、以上です。

【参考資料】

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

www.data.jma.go.jp

　東京電力パワーグリッド「でんき予報」

www.tepco.co.jp

　Wikipedia「日本の電力会社」

日本の電力会社 - Wikipedia

DATUM STUDIO株式会社　ブログ「楽屋」"Matplotlib-2軸グラフの書き方"

Matplotlib-2軸グラフの書き方 | DATUM STUDIO株式会社

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

　前回までは、六回に渡りデータの「抽出」について学びました。

pimientito-handson-ml.hatenablog.com

　今回からデータの「集約」について学んでいきます。

【今回の目標到達点】

集約関数を使って、データの概要を掴む

【目次】

参考資料のご紹介

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

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

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

「データ集約」の概要

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

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

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

　さっそく参考資料「3-1 データ数、種類数の算出」（p.053～）を学習します。

今回のテストデータ

　今回のテストデータは、いままで使用してきた「地上気象観測時日別編集データ」から少し離れ、気象庁のオープンデータを、インターネットより、CSV形式でダウンロードしました。（詳しい情報は【参考資料】に記載した気象庁Webページをご覧ください）

過去の気象データ・ダウンロード Webページ（気象庁）

【ご注意ください】

　ダウンロードしたデータを、そのまま利用すると、不要な項目や行があるため、分析・表示が正常に行えません。事前に、Excelなどの表計算ソフトを利用して、CSVファイルを加工してください。

　オープンデータの加工については、もう少し知見が深まった際に、番外編として「前処理の前処理」としてご紹介したいと思います。

　今回使用するデータは、以下の通りです。

項目	カラム名	概要	特記
年月日(単位:日)	Date	2017/1/1～2017/12/31	-
地域(単位:市)	Region	さいたま市,越谷市, 熊谷市,所沢市,秩父市	市名はローマ字表記
平均気温(単位:℃)	AVG_Temp	１日の平均気温	-
最高気温(単位:℃)	MAX_Temp	１日の最高気温	-
最低気温(単位:℃)	MIN_Temp	１日の最低気温	-

　レコード数は、1,825行です。

　なお、可視化モジュールを利用する際、ラベルの一部で、日本語表記が、文字化けしてしまうことがあったため、カラム名を日本語から英語表記に変更して、データを加工しています。

　利用する可視化モジュールの仕様については、少しずつ学習し、その都度、本ブログに取り入れていく予定です。

[Python]groupby( )関数を利用した集約

　本節では、Pythonを利用した「集約」を中心に、学びます。

　参考資料で紹介されているPythonのgroupby( )関数を利用した集約構文は、以下の通りです。

　result = pandas.DataFrame.groupby(key_word).agg({column:function, column:function, ....})

　参考・参照元：「python_2_awesome.py(抜粋)」（p.057）を参考に作成

【groupby( )構文　補足事項】

項目名	概要
result	変数
key_word	グループ化する列（カラム）
column	集約対象の列（カラム）
function	集約関数

　また参考資料「前処理大全」では、戻り値resultには、インデックスが、key_wordで指定したカラムに変更されてしまうため、後続の処理のため、reset_index( )関数を利用して、適宜、インデックスを振り直したほうが良いと述べています。

　reset_index( )関数の構文は、以下の通りです。

　DataFrame.reset_index(level=None, drop=False, inplace=False, col_level=0, col_fill='' )

　参考・参照元：pandas 0.23.4 documentation「pandas.DataFrame.reset_index」より抜粋

　最後にgroupby( )関数で、集約・抽出したデータにカラム名を付けます。

　pandas.DataFrame.columns = [column_name, column_name, ......]

　参考・参照元：「python_2_awesome.py(抜粋)」（p.057）を参考に作成

agg( )関数

　上述したgroupby( )関数のサンプルコードに含まれているメソッドagg( )関数は、指定した軸（カラム）に対して、指定した関数を利用して集計を行います。

　SQLでいうところの、複数の集計結果をJOIN（集計結果の結合）する行為を、ひとつのコードで完結できるため、非常に効率的です。

その他のgroupby( )関数の集約関数（メソッド）

　groupby( )関数で、まとめたデータに対して、以下のような集約関数（メソッド）が利用できます。

関数名	説明
count	グループ内の欠損値以外の値の数
sum	欠損値以外の合計
mean	欠損値以外の平均
median	欠損値以外の算術中央値
std, var	バイアスの掛かっていない(n-1を分母とした) 標準偏差と分散
min, max	欠損値以外の最小値と最大値
prod	欠損値以外の積
first, last	欠損値以外の最初と最後の値

参考・参照元：「Pythonによるデータ分析入門（第２版）」表10-1「最適化済みのGroupByメソッド」(p.325)より抜粋

今回のテストデータを表示

　今回取得した気象データを使って、実際に集約を行います。

　はじめに、CSV形式のデータを読み込みます。

[Windows7][Python][Jupyter Notebook]

#【前処理の学習-20】データを学ぶ ～集約～①

#Padasモジュール
import pandas as pd
from pandas import DataFrame as df

#CSVファイルの読み込み
df = pd.read_csv("./lesson20_data_20160101-20180101.csv")

　次に、読み込んだデータを、おおまかに確認していきます。

#読み込んだデータの確認(行数)
df.count()

df.countの結果

　続けて、データの内容や、概要を確認します。

#読み込んだデータの先頭数行表示
df.head()

df.head( )の結果

#読み込んだデータの概要
df.describe()

df.describe( )の結果

　前回学んだデータの「可視化」も、行ってみます。

#読み込んだデータの可視化

import seaborn as sns

sns.lineplot(x="Date", y="AVG_Temp", hue="Region", data=df)

seaborn 「lineplot」の結果

　非常に「見づらい」プロットになってしまいました。まだまだ「見たいデータ」の選択や、「やりたいこと」の整理ができていない証拠です。可視化については、今後も引き続き学習していきます。

　最後に、groupby( )関数を使ったコーディングと、実行結果を確認します。今回は「地域」でグループ化し、１年間の「平均気温」「最高気温」「最低気温」を集約しました。

#groupby()関数による集約

result = df.groupby('Region').agg({'AVG_Temp': 'mean', 'MAX_Temp': 'max', 'MIN_Temp': 'min'})
print(result)

groupby( )の結果

　続けて、集約したデータにカラム名を付与します。

#表示整正
result.columns = ['AVG_Year', 'MAX_Year', 'MIN_Year']
print(result)

columnsの結果

　集約したデータを、pandasのプロット用メソッドを利用して、可視化しました。

#DataFrameメソッドを利用して可視化
result.plot.bar()

plot.bar( )の結果

　このグラフから、2017年（1月1日～12月31日）のさいたま市、越谷市、熊谷市、所沢市、秩父市の「平均気温」「最高気温」「最低気温」を、おおまかですが比較することができました。（同じ県内のため、大きな差異は見られませんでしたが。。。）

【今回のまとめ】

　今回は、Pythonを使って集約関数groupbyを学びました。SQLとは違い、比較的少ないコーディングで、集約が実現できた印象です。

　しかし、参考資料「前処理大全」の著者 本橋氏は、本項で、以下のように述べています。

結合を含めた集約を行うときは、中間データのサイズが大きくなるので、結合と集約をまとめて実行できるSQLを選択する方が良いでしょう。

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

　まだ経験則が足りないため「このときは、SQL」「こういう場合は、Python」と使い分けることができませんが、少しずつでも著者のいう「メリット」「デメリット」を理解できるよう、今後も学習を続けていきます。

　今回は、以上です。

【参考資料】

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

www.data.jma.go.jp

「Pythonによるデータ分析入門（第２版）」

Pythonによるデータ分析入門 第2版 ―NumPy、pandasを使ったデータ処理

pandas 0.23.4 documentation「pandas.DataFrame.reset_index」

pandas.DataFrame.reset_index — pandas 0.23.4 documentation