Python(pandas)を使ってテーブル間の差分を取り出す
2018年7月20日追記:このやり方全然スマートではないので修正版を書きます。
同じ列(カラム)を持っているCSVファイルが複数あり、その差分を取りたいときの手法をメモっておきます。一年前の売上と比較したいとか、前年度の従業員リストと比較したい場合なんかに使えるかと。
使うのはPython環境と pandas です。pandas が入ってない場合は pip を使ってインストールしてください。Anaconda や WinPython をインストールしていれば最初から入ってます。
CSV(テキストデータ)ファイルの読み込み
pandas の read_csv を使ってCSVファイルを読み込みます。
import pandas as pd df01 = pd_readcsv('sample01.csv', encoding='Shift-JIS') df02 = pd_readcsv('sample02.csv', encoding='Shift-JIS')
Windows環境で作成したCSVファイルを使っているので、eocoding='Shift-JIS'で文字コードを指定しています。Mac環境、Linux環境であればこのオプションはいらないです。
また、使用しているCSVファイルは以下のようなテーブルです。
sample01.csv
ID | カテゴリ | 数量 |
---|---|---|
1 | A | 200 |
2 | A | 300 |
3 | B | 400 |
4 | C | 230 |
5 | A | 210 |
6 | D | 120 |
7 | E | 90 |
8 | B | 80 |
9 | A | 220 |
10 | D | 190 |
sample02.csv
ID | カテゴリ | 数量 |
---|---|---|
1 | B | 200 |
2 | B | 290 |
3 | A | 400 |
4 | C | 230 |
5 | C | 210 |
6 | D | 180 |
7 | A | 90 |
8 | E | 120 |
9 | A | 290 |
10 | D | 190 |
IDは一意でカテゴリ、数量がちょっとづつ違うという2種類のテーブルです。
比較のための文字列を生成する
pandas の isin を使って差分が発生している箇所を見つけ出します。
isin は同じものが含まれている列はTrueを返し、違っていればFalseを返します。比較対象を作成するために、applyを使ってIDとカテゴリ(比較対象の列)を結合した新しい列を生成します。
df01['比較用の列'] = df01[['ID', 'カテゴリ']].apply(lambda x: '{}_{}'.format(x[0], x[1]), axis=1) df02['比較用の列'] = df02[['ID', 'カテゴリ']].apply(lambda x: '{}_{}'.format(x[0], x[1]), axis=1)
これで準備が出来ました。この手順を飛ばしてisinを使い、「カテゴリ」だけを比較すると空のDataFrameが返ってきます。
isinはカテゴリに含まれる属性がそれぞれのテーブルに存在するか否かを返してくるので空になります。df01のカラムに含まれる値['A', 'B', 'C', 'D', 'E'] と df02の値['A', 'B', 'C', 'D', 'E']を比較し、新しい属性値が無いので空。
'F’という新しい属性値があれば、その部分の行だけを抽出してきます。既存の属性には無いものを探すときはカラム指定でisinかけると良いかもしれません。ノイズ探しとか。
isinメソッドを使った比較
上で作成した比較用の列をisinメソッドに食わせます。
df01[~df01['比較用の列'].isin(df02['比較用の列'])]
これでdf01には存在しておらず、df02には存在している行が出てきます。~df01['比較用の列']の部分で「df01の比較用の列カラムには"存在しない"属性と、df02の比較用の列カラムに入っている属性を比較しています。
DataFrameのIDの部分だけを取り出したい場合は末尾に ['ID'] をつけます。
df01[~df01['比較用の列'].isin(df02['比較用の列'])]['ID']
差分だけを別のDataFrameに格納しておくと便利です。
df_diff = df01[~df01['比較用の列'].isin(df02['比較用の列'])]['ID']
リスト形式で取り出す
DataFrameで比較しただけではその後の処理に使いにくいので、これをPythonのリストに格納してSQLやfor文で使いやすくしましょう。IDの部分だけを取り出して、valuesを噛ませるとnumpyの配列(array)になります。
df_diff['ID'].values
ここに更にtolistを噛ませます。
df_diff['ID'].values.tolist()
ここまでくればSQLに投げ込んだり、forを回して対象を書き換えたりが簡単にできるようになりますね。
とはいえ、もう少しスマートなやり方がありそうな気がしています。より良いやり方を知っている方がいたら、教えていただけると助かります。