前回までは、六回に渡りデータの「集約」について学びました。
pimientito-handson-ml.hatenablog.com
今回からデータの「結合」について学んでいきます。
【今回の目標到達点】
データテーブルとマスタテーブルの結合を学ぶ
【目次】
参考資料のご紹介
はじめに、現在、主に参考とさせていただいている書籍をご紹介します。
「前処理大全 データ分析のためのSQL/R/Python実践テクニック」本橋智光氏著(技術評論社)
![前処理大全[データ分析のためのSQL/R/Python実践テクニック] 前処理大全[データ分析のためのSQL/R/Python実践テクニック]](https://images-fe.ssl-images-amazon.com/images/I/61D0XQc0fwL._SL160_.jpg)
前処理大全[データ分析のためのSQL/R/Python実践テクニック]
- 作者: 本橋智光
- 出版社/メーカー: 技術評論社
- 発売日: 2018/04/13
- メディア: 大型本
- この商品を含むブログ (1件) を見る
「データ結合」の概要
参考資料「前処理大全」の「第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」の問い合わせ履歴データを使用して、テストデータを作成します。
「311 SERVICE REQUESTS」データセットの概要を、簡単にご説明します。
このデータセットは、ボストン市の公共設備や公共サービスに関する問合せ受付センター「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'」は存在していませんでしたが、今回のデータセットには、欠測率は低いものの、複数の項目に欠測値が含まれていました。
資料などを片手に、欠測値データの性質を確認し、今回は、難しい数式(代入法)を用いずに、学習が進められそうです。判断理由は、以下の通りです。
判断理由
欠測しているデータが「ID」や「画像へのリンク」の類のため。
一部を除き、欠測している比率が、各要素で5%未満程度のため。
「欠測値」の処理は、いったん保留になり、ホッとしたのも束の間、今回のデータセットでは、いくつかの項目で「揺らぎ」も存在しました。
「揺らぎ」を持つデータとは、同じ値にも関わらず、表記(フォーマット)が異なることで、別の値と評価されてしまうデータのことを指します。今回のデータセットに存在する「揺らぎ」の一例を、以下に記載します。
公共事業地区 | 公共事業地区 | 警察地区 | 警察地区 | |
---|---|---|---|---|
1A | 6 | A1 | D4 | |
1B | 06 | A7 | D-4 | |
1C | 7 | A-7 | D14 | |
2 | 07 | A15 | D-14 | |
02 | 8 | A-15 | E5 | |
3 | 08 | B2 | E-5 | |
03 | 9 | B-2 | E13 | |
4 | 09 | B3 | E-13 | |
04 | 10A | B-3 | E18 | |
5 | 10B | C6 | E-18 | |
05 | C-6 | |||
C11 | ||||
C-11 |
人間の目では「同じデータ(意味)だろう。」と思えるものでも、コンピュータには「別のデータ」に見えてしまいます。そのため、コンピュータに理解できるようにデータを整備する必要があります。
分析に適したデータへ整備することを「データクレンジング」と呼び、今回のデータセットでも、分析する内容によって、適宜、データクレンジングを行なう必要があります。
なお今回のデータセットには「欠測値」「揺らぎ」のほかに、付属資料「datadictionary-crmvaluecodeindex.pdf」には定義されていない項目や値が含まれていることもあり、データ分析初学者には、なかなかハードルの高いデータセットを引き当ててしまいました。
しかし、ボストン市の公共サービスに関する情報は、wikipediaなどでも、丁寧に説明されていることもあり、比較的、データの補完はしやすい印象です。
次の項目から、実際に今回の学習用テストデータを作成します。
テストデータの加工(前処理の前処理)
CSVデータの読み込み
1.WebからダウンロードしたCSV「311 SERVICE REQUESTS」を読み込みます。
[Windows7][Python][Jupyter Notebook]
#【前処理の学習-26】データを学ぶ ~結合~① #1.サンプルデータの読み込み #Pandasモジュール import pandas as pd from pandas import DataFrame as df_sample_data #CSVファイルの読み込み(Boston 311 Service Requests) df_sample_data = pd.read_csv("./data/lesson26_sample_data.csv", encoding='utf-8')
2.続いて読み込んだデータセットを格納するDBテーブル「tbl_311_requests」を定義します。
[Windows7][Python/SQL][Jupyter Notebook]
#2.DBテーブル作成 #標準モジュール import sys #コード実行時の履歴確認 import traceback #Pandasモジュール import pandas as pd #postgreSQL用Python DB API「psycopg」 import psycopg2 #変数の初期化 con = None #----- パラメータ ----- #DBテーブル名 tbl_name = 'tbl_311_requests' #CREATE文(カラムリスト 29カラム) clmn_list_create = 'case_enquiry_id varchar, open_dt varchar, target_dt varchar, \ closed_dt varchar, ontime varchar, case_status varchar, closure_reason varchar, \ case_title varchar, subject varchar, reason varchar, type varchar, queue varchar, \ department varchar, submittedphoto varchar, closedphoto varchar, location varchar, \ fire_district varchar, pwd_district varchar, city_council_district varchar, \ police_district varchar, neighborhood varchar, neighborhood_services_district varchar, \ ward varchar, precinct varchar, location_street_name varchar, location_zipcode varchar, \ latitude varchar, longitude varchar, source varchar, ' #CREATE文(主キー) clmn_P_Key = 'case_enquiry_id' #----- SQL ----- #CREATE TABLE sql_create = 'CREATE TABLE ' + tbl_name + ' (' + clmn_list_create + ' PRIMARY KEY(' + clmn_P_Key + '));' #データベース接続パラメータ #passwordは、伏字(*)に変換しています。 db_connection_parameter = "host='localhost' dbname='pimientito_ml' user='pimientito' password='*****'" try: #DBテーブル作成(テーブル作成後、コメントアウト) with psycopg2.connect(db_connection_parameter) as con: con.autocommit = False #コンソールオブジェクト生成 with con.cursor() as cur: #テストテーブル作成 cur.execute(sql_create) con.commit() except psycopg2.DatabaseError as e: if con: con.rollback() print('Error: %s' % e) traceback.print_exc() sys.exit(1) except Exception as e: if con: con.rollback() print('Exception Error: %s' % e) traceback.print_exc() sys.exit(1) finally: con.commit() if con: cur.close() con.close() print('処理が完了しました.')
CSVファイルから読み込んだデータセットには、日付や座標などの数値データが存在しますが、筆者は、DBへ取込む際には、すべて文字列型(varchar)で格納し、データを使用する際、必要に応じて型変換を行っています。
事前にデータ型を定義する方法と、使用時に型変換を行なう方法の、どちらが最善なのかは、今後の学習を進めていくなかで、学んでいきたいと思います。
3.定義したDBテーブルへ、CSVファイルから読み込んだデータセットを挿入します。
[Windows7][Python/SQL][Jupyter Notebook]
#3.サンプルデータ挿入 #標準モジュール import sys #コード実行時の履歴確認 import traceback #Pandasモジュール import pandas as pd #SQL INSERT文のフォーマットに合わせるために使用 from pandas import DataFrame as df_cast_data #postgreSQL用Python DB API「psycopg」 import psycopg2 #変数の初期化 con = None #----- パラメータ ----- #DBテーブル名 tbl_name = 'tbl_311_requests' #----- SQL ----- #INSERT sql_insert = 'INSERT INTO ' + tbl_name + ' VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,\ %s,%s,%s,%s,%s,%s,%s,%s,%s,%s,\ %s,%s,%s,%s,%s,%s,%s,%s,%s) ' #データベース接続パラメータ #passwordは、伏字(*)に変換しています。 db_connection_parameter = "host='localhost' dbname='pimientito_ml' user='pimientito' password='*****'" try: #DBテーブルへデータ挿入 with psycopg2.connect(db_connection_parameter) as con: con.autocommit = False #コンソールオブジェクト生成 with con.cursor() as cur: #リスト型配列 arry_row = [] #DataFrameのデータを抽出 for row in df_sample_data.values: #抽出したデータを、DataFrame型(一行単位)に変換 df_cast_data = (row[0], row[1], row[2], row[3], row[4], \ row[5], row[6], row[7], row[8], row[9], \ row[10], row[11], row[12], row[13], row[14], \ row[15], row[16], row[17], row[18], row[19], \ row[20], row[21], row[22], row[23], row[24], \ row[25], row[26], row[27], row[28]) #リスト型配列arry_rowへ追加 arry_row.append(df_cast_data) #DBテーブルへデータを挿入 cur.executemany(sql_insert,arry_row) con.commit() except psycopg2.DatabaseError as e: if con: con.rollback() print('Error: %s' % e) traceback.print_exc() sys.exit(1) except Exception as e: if con: con.rollback() print('Exception Error: %s' % e) traceback.print_exc() sys.exit(1) finally: con.commit() if con: cur.close() con.close() print('処理が完了しました.')
4.今回は、市民からのリクエストに対応する「部署」のマスタテーブル「tbl_departments」を作成します。テーブル構成は、以下の通りです。
id | name |
---|---|
ANML | Animal Control |
BHA_ | Boston Housing Authority |
BPD_ | Boston Police Department |
BPS_ | Boston Public Schools |
BTDT | Transportation Department |
BWSC | Water and Sewer Commission |
CAFF | unknown |
CHT_ | City Hall Truck |
DISB | Disability Commission |
DND_ | Department of Neighborhood Development |
GEN_ | unknown |
HS_D | Disabilities/ADA |
HS_O | Housing Office of Civil Rights |
HS_V | Veterans Call log |
HS_W | Women’s Commission |
HS_Y | Youthline |
INFO | Information Channel(Not a department) |
ISD | Inspectional Services |
No Q | No queue assigned(Not a department) |
ONS_ | Office for National Statistics |
PARK | Parks |
PROP | Property Management |
PWDx | Public Works |
Temp | Temporary |
コードインデクスに記載されていない部署があり、Webなどで調べた結果、不明な部署名については「unkown」としました。
「departments」テーブルに挿入するデータは、事前にCSVファイルを作成し読み込ませます。具体的なコーディングは、以下の通りです。
[Windows7][Python/SQL][Jupyter Notebook]
#4.マスタテーブル「Department」の作成 #標準モジュール import sys #コード実行時の履歴確認 import traceback #Pandasモジュール import pandas as pd from pandas import DataFrame as df_csv_data #SQL INSERT文のフォーマットに合わせるために使用 from pandas import DataFrame as df_cast_data #postgreSQL用Python DB API「psycopg」 import psycopg2 #変数の初期化 con = None #----- パラメータ ----- #DBテーブル名 tbl_name = 'tbl_departments' #CREATE文(カラムリスト 29カラム) clmn_list_create = 'id varchar, name varchar, ' #CREATE文(主キー) clmn_P_Key = 'id' #----- SQL ----- #CREATE TABLE sql_create = 'CREATE TABLE ' + tbl_name + ' (' + clmn_list_create + ' PRIMARY KEY(' + clmn_P_Key + '));' #INSERT sql_insert = 'INSERT INTO ' + tbl_name + ' VALUES (%s,%s) ' #データベース接続パラメータ #passwordは、伏字(*)に変換しています。 db_connection_parameter = "host='localhost' dbname='pimientito_ml' user='pimientito' password='*****'" #CSVファイルの読み込み(CSVの1行目(0番目)は、カラム名のため、スキップします) df_csv_data = pd.read_csv("./data/lesson26_departments_data.csv", encoding='utf-8', skiprows=0) try: #DBテーブル作成(テーブル作成後、コメントアウト) with psycopg2.connect(db_connection_parameter) as con: con.autocommit = False #コンソールオブジェクト生成 with con.cursor() as cur: #テストテーブル作成 cur.execute(sql_create) con.commit() #リスト型配列 arry_row = [] #DataFrameのデータを抽出 for row in df_csv_data.values: #抽出したデータを、DataFrame型(一行単位)に変換 df_cast_data = (row[0], row[1]) #リスト型配列arry_rowへ追加 arry_row.append(df_cast_data) #DBテーブルへデータを挿入 cur.executemany(sql_insert,arry_row) con.commit() except psycopg2.DatabaseError as e: if con: con.rollback() print('Error: %s' % e) traceback.print_exc() sys.exit(1) except Exception as e: if con: con.rollback() print('Exception Error: %s' % e) traceback.print_exc() sys.exit(1) finally: con.commit() if con: cur.close() con.close() print('処理が完了しました.')
以上で、今回のテストデータ作成は完了しました。続いて、テストデータを使って学習を進めます。
テストデータを使って学習
今回のテストデータでは、前述した「欠測値」「揺らぎ」を含まない要素を使い、SQLで学習します。最終的に抽出するデータの構成は、以下の通りです。
カラム名 | テーブル |
---|---|
department | tbl_311_requests.department tbl_departments.id |
name | tbl_department.name |
source | tbl_311_requests |
count | count(request) group by department |
[SQL]マスタテーブルの結合
マスタテーブルの結合を行う前に、抽出する対象を、以下の条件で絞り込みます。
- 311への問い合わせのうち、部署ごとの問い合わせ件数の上位3位までを抽出する。
データ件数のランキング付けについては、前回学習しました。その時に学んだRANK( )
関数を使って、上位3位以内の部署を確認します。
pimientito-handson-ml.hatenablog.com
SQL文は、以下の通りです。(例外処理の部分は割愛しています)
[Windows7][Python/SQL][Jupyter Notebook]
#5. リクエスト(問合せ)件数の上位部署を確認 #標準モジュール import sys #コード実行時の履歴確認 import traceback #Pandasモジュール import pandas as pd from pandas import DataFrame as df_result #postgreSQL用Python DB API「psycopg」 import psycopg2 #変数の初期化 con = None #----- パラメータ ----- #DBテーブル名 tbl_name = 'tbl_311_requests' #----- SQL ----- #SELECT sql_select_ranking = 'SELECT department, COUNT(*) AS requests_count, \ RANK() OVER (ORDER BY COUNT(*) DESC) AS ranking \ FROM tbl_311_requests GROUP BY department;' #データベース接続パラメータ #passwordは、伏字(*)に変換しています。 db_connection_parameter = "host='localhost' dbname='pimientito_ml' \ user='pimientito' password='*****'" try: #PostgreSQLへ接続 with psycopg2.connect(db_connection_parameter) as con: con.autocommit = False #コンソールオブジェクト生成 with con.cursor() as cur: #SELECT文実行 df_result = pd.read_sql_query(sql_select_ranking, con)
ランキング結果を確認します。
#結果の確認
df_result
ランキング結果を確認したところ、以下の部署の問い合わせ件数が多いことが分かりました。
部署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)
それぞれ抽出した結果は、以下の通りです。
続いて、抽出したデータの可視化に挑戦します。
「可視化」に挑戦
可視化の目的
今回抽出したデータでは、311へのリクエストが、どのようなツールを経由して、各担当部署へ通知されたのかを、可視化して理解を深めたいと思います。リクエストツールは、以下の一覧の通りです。
source |
---|
Citizens Connect App |
City Worker App |
Constituent Call |
Employee Generated |
Maximo Integration |
Self Service |
[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)の可視化の結果は、以下の通りです。
以下、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)
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)
隣接する要素が重なって、図として見づらい場合、変数explode
で、互いの距離を微調整します。
可視化することで、今回のデータから、以下のような推測が導かれるのではないでしょうか。
ボストン市提供の専用アプリが浸透しており、十分な成果を上げている。
コールセンターの役割も、非常に重要で、問い合わせ内容によっては、まだまだ電話による応対が必要である。(人的リソースの確保は重要で、雇用機会が一定数保たれているかもしれない)
問合せや苦情に関する分野において、Twitterは、あまり有効的な手段ではない。
※ 本推測は、あくまで筆者の私見の域を超えるものではありません。より正確な分析をするためには、使用しているデータの正規分布を取ったり、母集団(1,433,899レコード)から、妥当なサンプリングを行うなど、広範囲な統計学の知識や、それに伴う確率論などの学習が必須です。また検定と呼ばれるスキルも身に付けなければ、推測の妥当性を立証することができません。
今回のまとめ
今回の学習では、データテーブルと、マスタテーブルの結合について学びました。
データベースを業務で利用する場合、多くのデータテーブルや、マスタテーブルを、複雑に結合して、必要なデータを生成するということは、頻繁に行われると思います。
参考資料のなかで、著者も言及していましたが、アウトプットのデータに合わせて、コーディングを行なってしまうと、無為に不要な処理を多く含んでしまう恐れがあるため、できる限りデータ生成の工程を整理し、最短で、効率的な手順を検討しなければならないことを、あらためて考えさせられた学習でした。
効率的な処理やコーディングを行うには、まだまだ広範囲に学習が必要です。
今回は、以上です。
【参考資料】
Welcome to ANALYZE BOSTON
311 Service Requests
matplotlib example 「Basic pie chart」
本ブログに関するお問い合わせ先
筆者自身の「機械学習」の学習のため、参考にさせていただいている資料や、Web情報に対して、情報元の権利を侵害しないよう、できる限り、細心の注意を払って、ブログの更新に努めておりますが、万が一、関係各所の方々に、不利益が生じる恐れがある場合、大変お手数ですが、下記の「お問い合わせ先」まで、ご一報いただけますようお願いいたします。
なお、本ブログへの誹謗・中傷や、記事内容についてのご質問やご指摘につきましては、お答えできないこともございますので、あらかじめご理解・ご了承いただけますよう、何卒よろしくお願いいたします。
お問い合わせ先:otoiawase@handson-t-arte.com