はじめに
Dr.Sum、というDBがあります。ウイングアーク1st社が開発/販売している国産DWHで、エンタープライズに幅広くは利用されていませんが、一部の根強いファンがいるイメージがあります。
今回、縁あって、このDr.Sumを使った全社データ基盤の構築プロジェクトに携わりました。
その際に困ったことをちょっと書いておきます。実際の経験として、Dr.Sum、としていますが、その他のDBでも直面する問題かなという気がします。
やりたかったこと
タイトルどおりですが、データの名寄せを、具体的に言うと会社名の名寄せを行ないたかったのです。
プロジェクトでは異なるベンダの管理する異なるシステムをDr.Sumに集めて横串で可視化したり、機械学習の特徴量を算出するため、データを整形する必要がありました。
たとえば以下はとあるトランザクションデータです。
| コード | 会社名 |
|---|---|
| 0105 | ホワイトベース |
| 2000 | クインマンサ |
| コード | 会社名 |
|---|---|
| 10000 | 木馬（株） |
| 10000 | 木馬 |
| 03000 | (株)クィン・マンサ |
| 03000 | クィン・マンサ |
| 3000 | クィン・マンサ事業 |
| 3000 | 株式会社クィン・マンサ |
- 地球連邦では「クィン・マンサ」を「クインマンサ」としている
- ジオンでは「ホワイトベース」のことを通称の「木馬」と入力している
- ジオンでは「（株）」や「株式会社」などの法人格の入力があり揺れている
いちばん困ったこととして、異なるシステム間で互いのコード体系が同期されていないですね。
そうしたとき、一般的にはユーザー主導で変換マスタを作ることになると思います。それがいちばん確実です。
しかし今回はもろもろの事情でそれができずに、こちら側で方法を考える必要がありました。
やったこと
まずはわかりやすいところから、ジオンシステムのほうの法人格を除去し、また、「木馬」という名称を正式名称に変換します。
| コード | 会社名 |
|---|---|
| 10000 | ホワイトベース |
| 10000 | ホワイトベース |
| 3000 | クィン・マンサ |
| 3000 | クィン・マンサ |
| 3000 | クィン・マンサ事業 |
| 3000 | クィン・マンサ |
ここから、地球連邦のほうの「ホワイトベース」とジオンのほうの「ホワイトベース」、地球連邦のほうの「クインマンサ」とジオンのほうの「クィン・マンサ」および「クィン・マンサ事業」が同じものを指しているため、これらのコード変換表を用意することになります。
イメージとしては次のような表です。
| 地球連邦コード | 地球連邦会社名 | ジオンコード | ジオン会社名 |
|---|---|---|---|
| 0105 | ホワイトベース | 10000 | ホワイトベース |
| 2000 | クインマンサ | 3000 | クィン・マンサ |
| 2000 | クインマンサ | 3000 | クィン・マンサ事業 |
今回のサンプルデータの範囲だけで言えば手動で作っても問題ありませんが、実際にはユーザーが値を入力することで生成されるトランザクションデータですので、今後、「ホワイト・ベース」とか「クイン・マンサ」とかが入力された場合は逐一それを追加しなければならず、現実的ではありません。
そのため、会社名を見て少なくともある程度は自動で名寄せを行ない、コード変換マスタに反映させる必要があります。
文字列間の関連度を図りたい
最初、Dr.Sumが製品として用意しているSQL関数で対応できるかを調査しましたが、結果的には対応ができませんでした。比較する2つの文字列内に同じ文字がいくつ含まれているかはわかりますが、文字列の長さは可変ですからね……。
そこで採用することにしたのが、「レーベンシュタイン距離」です。
レーベンシュタイン距離については、このqiitaにもいくつか素晴らしい記事が存在しており、おおいに参考にさせていただきました。
今回はPythonで記述します。
Levenshtein
まずはLevenshteinライブラリをインストールします。
また、テーブルデータをDataframeで処理したいので、pandasも合わせてインストールします。
なお、Dr.SumがWindowsでしか動作しない関係上、実行環境も同じくWindowsです。
python -m pip install levenshtein
python -m pip install pandas
インストールできたら、処理を記述します。
このときの事前準備として、地球連邦の会社名とジオンの会社名の全組み合わせを持てるように、2つのトランザクションデータ（からなるマスタ）をCROSS JOINしておきます。
それでは、Dr.Sumから出力したCSVファイルをDataframeとして読み込みます。
import Levenshtein as ls
import pandas as pd
df = pd.DataFrame(pd.read_csv('C:/temp/CROSS_JOIN.csv',dtype = 'object'), columns=['地球連邦コード', '地球連邦会社名', 'ジオンコード', 'ジオン会社名'])
続いて、2カラム間、ここでは［地球連邦会社名］と［ジオン会社名］間のレーベンシュタイン距離を測り、それをカラム［Distance］としてDataframeに追加します。
df['distance'] = df.apply(lambda x : ls.distance(x['地球連邦会社名'], x['ジオン会社名']), axis=1)
print(df)
距離を算出できました。
［distance］を見てみると、距離の分布は[0,2,4,6,7,9]となっており、見た感じ「distance <= 4」という条件を適用して抽出し、distinctすることで有効なマスタ変換表を取り出せそうです。
しかし、それで本当にいいのでしょうか？
ratioで類似度を算出できる
ここで、地球連邦側とジオン側にそれぞれ次のコードと名称が追加されたとします。
※出力結果は長くなるのでdistinctしています。
| コード | 会社名 |
|---|---|
| 2001 | ジオ |
| 0112 | ザク |
| コード | 会社名 |
|---|---|
| 3107 | ジ・O |
| 0112 | ザク |
indexが27や30の行を見てください。レーベンシュタイン距離が「2」や「3」となっています。
レーベンシュタイン距離の細かな算出方法は別記事を参照いただければと思いますが、要は比較する文字列が短いと距離も短くなってしまうようです。
これでは、「distance <= 4」という条件を適用して抽出してしまった場合に、ジオとザクが同じものとして見做されてしまい都合が悪いです。
そこで、Levenshteinのdistanceではなくratioを使用します。
df['ratio'] = df.apply(lambda x : ls.ratio(x['地球連邦会社名'], x['ジオン会社名']), axis=1)
print(df.drop_duplicates())
どうでしょうか。先ほどのindex27や30はratioは「0」です。
あとは、どの程度の誤りを許容するかを踏まえながら、ratioに対する抽出条件をパラメータとして決定していくだけです。
今回はたとえば「ratio >= 0.4」とすると良い感じのマスタ変換表が取り出せます。
おわりに
ここまでで、当初の目的はいったんは果たせましたが、実際の運用においては、Python処理時に結果を抽出するのではなく、いったん全ての結果をCSVに出力し、Dr.Sumにインポートしたうえで、抽出を行ないました。
それは、ratioに対する「>= 0.4」という条件を、運用の中で調整していく必要があるからです。
前述したように、今後どういった会社名が入ってくるかわからない中で、たとえば同じ会社のratioが0.33という結果になることも将来的には十分にありえます。このときにPython側で0.4未満を一律に切り捨ててしまうとよろしくないので、Dr.SumでのETLの運用の中にこのマスタ変換表の真正性を追認できる仕組みを組み込むことにしました。
次回、記事を書くことがあれば、そのあたりのことを書きたいと思います。