SQLの正規表現を、pandas(python)のqueryメソッドのstr.containsを使ってまとめてみた

はじめに

　最近、プライベートでデータ分析のツールを作成している関係でpandasというpython外部ライブラリを活用している。が、いざ使って見ると、「pandas?なにそれかわいいの？」と動物のパンダ🐼を連想させるヤバい思考に行きつつある状況になる。
　これはまずいと感じ、投稿者はpandasを探し求める旅に出る。
　この記事は、pandasを飼いならすためにpandasをSQLっぽく考えるというデータサイエンス初学者に向けた記事となります。

そもそもpandasとは何か

　pandasとは、構造化された（表形式、多次元、潜在的に不均質）データと時系列データを簡単かつ直感的に操作できるように設計された高速で柔軟な表現力のあるデータ構造を提供するPythonパッケージで、実際的な実世界のデータ分析を行うための基本的な高レベルのビルドを行う事が可能なツールです。
　要は、表データをpythonを使っていい感じに処理して目的のデータ抽出するツールです。

実践

準備

今回は下記の表データ【customer】を用いてpandas攻略を行います。（表データはCSV形式）

※データは長いので冒頭部分のみ表示

参考データ：データサイエンス100本ノック
https://github.com/The-Japan-DataScientist-Society/100knocks-preprocess

また、今回はpandasを使用するため、コードは下記のように予め準備しておきます。

python

import pandas as pd
df_customer = pd.read_sql('customerのCSVファイルのパス', sep=',')

本題

タイトルにある通り、今回はSQLの正規表現をpandasを使って再現します


問題：顧客データフレーム（df_customer）から、ステータスコード（status_cd）の先頭がアルファベットのA〜Fで始まり、末尾が数字の1〜9で終わるデータを全項目抽出し、10件だけ表示せよ。

目的：正規表現を活用して、パターンマッチするレコード（行）を抽出する

解答

解答を記述するとこんな感じになります。

pandas(python)

df_customer.query("status_cd.str.contains('^[A-F].*[1-9]$', regex=True)", engine='python').head(10)

もしくは

pandas(python)

df_customer.query("status_cd.str.contains('^[A-F]', regex=True) & status_cd.str.contains('[1-9]$', regex=True)", engine='python').head(10)

解説

• pandasのqueryメソッドを活用する
  

まず、pandasのqueryメソッドを用いてSQLに当たるWHERE文を定義します

※補足：pandasのqueryメソッドに関する詳細の記事は下記を参考にしてみて下さい。
https://qiita.com/syuki-read/items/9a57b78f6a577c2fbefe

形式を表現するとこんな感じです。

pandas(python)

df_store.query('指定したい条件内容')

これをSQLで考えると、こんな感じになります。

SQL

select * from store where '条件内容';

FROM句：データベース内の指定する表を選択する
SELECT文：指定した表から抽出すべき列を選択する
WHERE文：指定した表から条件に合致するフィールドを選択し、そのフィールドを含むカラムを抽出する

• SQLの正規表現を、str.contains()で表現する
  

そしてここから、pandasのqueryメソッドのstr.contains()を用いて、（SQLの正規表現を）対象のカラム(status_cd)から条件に合致するレコードの抽出を行います。str.contains()を用いて、正規表現を表現します。

※補足：pandasのqueryメソッド関する詳細の記事は下記を参考にしてみて下さい。
https://qiita.com/syuki-read/items/39320736dd31f42d4893
https://qiita.com/syuki-read/items/9a57b78f6a577c2fbefe

データ分析を行う上で、正規表現は避けては通れない項目で、特にpythonではその傾向が強いです。代表的な正規表現のパターンは、下記のようなものがあります。正規表現をやり慣れていない場合、python標準モジュールreをimportして練習してみるのも１つの手段です。

メタ文字	説明	使用例	合致する文字列	合致しない文字列
.	任意の1文字	R.d	Red	Road
*	直前のパターンを0回以上繰り返す	Hey*	He,Hey,Heyyy	His,Him
+	直前のパターンを１回以上繰り返す	Yes+	Yes,Yesss	Ye
?	直前のパターンを0もしくは1回繰り返す	No?	N,No	Noo
^	文字列の先頭を指定する	^A.	AB	BA
$	文字列の末端を指定する	.A$	BA	AB
{数字}	直前のパターンを数字の分だけ繰り返し	7{3}	777	777,777,777
[文字,文字]	カッコ内の文字のいずれか１文字	[a,b,c]	a,b,c	d,e,f
[文字-文字]	カッコ内の文字の範囲からいずれか１文字	[a-e]	a,b,e	f,s,q
[数字-数字]	カッコ内の数字の範囲からいずれか１文字	[0-5]	2	7

※pythonの正規表現について、もっと細かい内容を知りたい方は下記のpython公式サイトをご活用して見て下さい。
https://docs.python.org/ja/3/library/re.html

今回の設問では「先頭の文字がA~F」、「間に一文字以上の任意の文字列が繰り返される」、「末端の文字が1~9」を満たす必要があるため正規表現を下記のように記述します。（python標準モジュールreで表現）

python

import re

search_pattern=r'^[A-F].*[1-9]$'
#正規表現を行うパターン

customer_date1='CS021313000114'
customer_date2='GS021313000114'
customer_date3='CS021313000110'
#正規対象のデータ

result1=re.search(search_pattern, customer_date1)
result2=re.search(search_pattern, customer_date2)
result3=re.search(search_pattern, customer_date3)

print(result1)
#re.Match object; span=(0, 14), match='CS021313000114'>
#全ての条件を満たすため、正規表現のパターンにマッチングされ出力される
print(result2)
#none
#先頭文字が条件に一致していないため、正規表現のパターンにマッチングせず出力されない
print(result3)
#none
#末端文字が条件に一致していないため、正規表現のパターンにマッチングせず出力されない

そして、これをpandasで表現をして見るとこんな感じになります。

pandas(python)

df_customer.query("status_cd.str.contains('^[A-F].*[1-9]$', regex=True)", engine='python').head(10)

ここでは、queryメソッドの（第一引数に位置する）str.containsの第一引数が

『customerテーブルのstatus_cd列の文字列で「先頭の文字がA~F」、「間に一文字以上の任意の文字列が繰り返される」、「末端の文字が1~9」の全てを満たす』

カラムを抽出しております。この時、str.containsの第二引数を「regex=True」とし、正規表現を行う事を許可するのを忘れないようにしましょう。
また、queryメソッド第二引数に「engine='python'」のお約束事をつけ忘れないように注意しましょう。

これをSQLで考えるとこんな感じになります。

SQL(PostgreSQL)

SELECT * FROM customer WHERE status_cd ~ '^[A-F].*[1-9]$' LIMIT 10

今回のSQLのパターンマッチの等号条件は、「（大文字小文字の区別を含めた）一致」なので、「~」を使用して等号を行います。

演算子	説明
~	正規表現に一致する。大文字小文字の区別あり。
~*	正規表現に一致する。大文字小文字の区別なし。
!~	正規表現に一致しない。大文字小文字の区別あり。
!~*	正規表現に一致しない。大文字小文字の区別なし。

そして、図を表すとこんな感じになります。

• 全体図
  全体をまとめると、こんな感じになります。

コラム

今回はstr.contains()でデータ抽出を行いましたが、文字列（str型）のデータ抽出は他にもこんなのあったりします。

• str.contains(): 特定の文字列を含む
• str.endswith(): 特定の文字列で終わる
• str.startswith(): 特定の文字列で始まる
• str.match(): 正規表現のパターンに一致する

データ抽出は状況によって変化するため、柔軟に使い分けていく事が大事です。

まとめ

今回はpandasを使って、SQLの正規表現をまとめてみました。pandasはデータベース及びSQLを意識したpythonライブラリなため、普段からSQLを意識するとかなり使いやすいツールだと改めて感じます。また、特定の命令文を指示する時、SQLがいくつものパターンがあるように、pandasにも多様なパターンが存在すると、pandasを活用して日々実感を持ちます。

終わりに

　今回、利活用したデータはデータサイエンス協会（DS協会）の「データサイエンス100本ノック」を参考にしております。こちらはJupyter notebookを使用しているので、より見やすいデータが抽出されます。
　この記事を読んで、「実際に実装してみたい！！」という方がおりましたら、下記にその実装に関する記事を上げているので、良かったそちらの記事を参考に是非実装してみて下さい。

データサイエンス初学者にむけた、データサイエンス100本ノックを実装する方法：
https://qiita.com/syuki-read/items/714fe66bf5c16b8a7407