【BigQuery】他の行を参照して欠損値を埋める方法のベストプラクティス

Question

前提

• BigQuery上のテーブルに次のような欠損値が大量にあるとします。

id	列1	列2	列3	列4
1	日本ハム	ガッツ	小笠原道大	1997
2	北海道日本ハム	ガッツ	小笠原道大	2004
3	巨人	ガッツ	null	2007
4	東北楽天	神の子	田中将大	2007
5	東北楽天	神の子	null	2020

実現したいこと

• これをクレンジング処理によって、このように埋めたいです。

id	列1	列2	列3	列4
1	日ハム	ガッツ	小笠原道大	1997
2	北海道日本ハム	ガッツ	小笠原道大	2004
3	巨人	ガッツ	小笠原道大	2007
4	東北楽天	神の子	田中将大	2007
5	東北楽天	神の子	田中将大	2020

• 欠損値はいずれも列3のみに生じており、いずれの行も列2と列3の値は一対一に対応しています。
• 従って欠損値を埋めるには、欠損行と列2の値が同じかつ、列3がnullでない行をテーブルから検索して、列3の値を取得すればokです。
• そのような行が複数ある場合は、列4の値が新しい方の行を採用することとします。
• 列2の値が同じ行がテーブルにない場合、欠損値はnullのままにします。

試したこと

• pandas-gbqを使って上記の方針でpythonスクリプトを作り、要件を満たす実装を作りました。(列3がnullの行をSELECTしてdfに格納し、一行ずつ上記の処理を行ってからUPDATEするというものです。)
• 個人的には結構よくある欠損地埋めのパターンと思っているのですが、検索しても同様の問題の解決策などを見つけることができませんでした。
• ↑の例はシンプルなパターンですが、列2の値が同じ行がテーブルにない場合は他の列で順々に同じ処理をしていく実装や、一つの列だけでなく、複数の列が同じ行を検索する場合の実装などのパターンもあり、誰かライブラリ作ってくれと思っていたところでした。
• このような場合のベストプラクティスなどがあれば教えていただきたいと思い、質問させていただきます。(BigQueryに限らず、csv、dataframe一般についての方法でも大丈夫です)

Comments

[meg_]

> pandas-gbqを使って上記の方針でpythonスクリプトを作り、要件を満たす実装を作りました。
上記方法での問題点は何でしょうか？

[Jonathan_Sf]

特に問題点はないのですが、こういう時普通はどうやって対処するのか、を知りたかったのです。

[meg_]

BigQueryをお使いということはビッグデータを扱っているのでしょう。おそらくpandas等の処理ではビッグデータを扱うのは無理かと思います。BigQueryに詳しくないのでベストプラクティスについての回答は出来ませんが、ビッグデータとそれ以外とでは実装も異なるかと思っています。（以前Sparkのコーディングに苦労した経験からの知見です）

[68user]

望ましいのは、正規化して「氏名」と「ニックネーム」カラムを持つ「選手テーブル」を作ることではないでしょうか。それが無理であるなら、UPDATE 文 1回の実行で更新できそうな気がします (試してませんが)

Answer 1

欠損値はいずれも列3のみに生じており、いずれの行も列2と列3の値は一対一に対応しています

Pandas の場合。

import pandas as pd
import io

csv_data = '''
id,列1,列2,列3,列4
1,日本ハム,ガッツ,小笠原道大,1997
2,北海道日本ハム,ガッツ,小笠原道大,2004
3,巨人,ガッツ,,2007
4,東北楽天,神の子,田中将大,2007
5,東北楽天,神の子,,2020
'''
df = pd.read_csv(io.StringIO(csv_data))

#
df['列3'] = df.groupby('列2', group_keys=False)['列3'].apply(lambda x: x.ffill().bfill())
print(df)

id	列1	列2	列3	列4
1	日本ハム	ガッツ	小笠原道大	1997
2	北海道日本ハム	ガッツ	小笠原道大	2004
3	巨人	ガッツ	小笠原道大	2007
4	東北楽天	神の子	田中将大	2007
5	東北楽天	神の子	田中将大	2020