こんにゃくの畑

NULL値をゼロに：pandasのfillna()とDuckDB SQL

はじめに

pandasでは、fillna()関数を使ってデータフレームのNULL値をゼロなど特定の値に置き換え、データの一貫性を保つ処理を行っています。

以下はpandasの欠損値をゼロで埋めるサンプルコードです。

import pandas as pd

# サンプルデータ
data = {
    'a_00': [10, 20, None],
    'a_01': [13, 26, 29],
    'a_02': [16, None, None],
    'a_03': [18, 19, 34]
}

df = pd.DataFrame(data)

# fillnaでNULLをゼロに置換
df = df.fillna(0)
print(df)

出力:

   a_00  a_01  a_02  a_03
0  10.0  13.0  16.0  18.0
1  20.0  26.0   0.0  19.0
2   0.0  29.0   0.0  34.0

ここでは、同様の処理を行うDuckDB SQLを紹介します。とくに、複数列のNULL処理を行う効率的な方法を示します。

SQLでNULLをゼロに置換

SQL標準のCOALESCE()関数は、第一引数のNULL値を第二引数の値に置き換えることができます。 以下のクエリでは、単一列のNULL値をゼロに置換します。

サンプルテーブルを準備します:

-- サンプルテーブルを作成
CREATE TABLE numbers (
  id INTEGER,
  a_00 INTEGER,
  a_01 INTEGER,
  a_02 INTEGER,
  a_03 INTEGER
);
-- データを挿入 (NULL値を含む)
INSERT INTO numbers
VALUES
  (1, 10, 13, 16, 18),
  (2, 20, 26, NULL, 19),
  (3, NULL, 29, NULL, 34);

COALESCE()関数を使ってNULLをゼロに置換します:

SELECT
  id,
  COALESCE(a_02, 0) AS a_02
FROM numbers;

出力:

id  a_02
--  ----
 1    16
 2     0
 3     0

複数列のNULLを効率的に処理：DuckDB のCOLUMNS()の活用

複数列を対象にNULL値を置換する場合、COALESCE()を個別に適用するとクエリが冗長になります。DuckDBでは、COLUMNS()を活用して複数列を一括処理できます。

サンプルテーブルの a_xx 列のNULL値の処理をおこなう場合は次のようなクエリを実行します。

SELECT
  id,
  -- 'a_\d{2}'はa_に2桁の数字が続く正規表現
  COALESCE(COLUMNS('a_\d{2}'), 0)
FROM numbers;

出力:

id  a_00  a_01  a_02  a_03
--  ----  ----  ----  ----
 1    10    13    16    18
 2    20    26     0    19
 3     0    29     0    34

COLUMNS() は正規表現やラムダ関数を引数として使用でき、上記のクエリでは正規表現'a_\d{2}'で一致するすべての列を指定しています。個別にカラム名を指定する必要がなくなり、複数列に対するNULL値の処理が簡素化されます。

まとめ

DuckDB SQLを用いたNULL値処理のクエリを示しました。pandasのfillna(0)と同等の操作を、COALESCE()を利用した基本例と、COLUMNS()を活用した複数列の効率的な処理で紹介しました。

この方法でゼロ以外の値に置き換えることも可能です。たとえば、-1に置き換える場合は次のように記述します。

SELECT
  id,
  COALESCE(COLUMNS('a_\d{2}'), -1)
FROM numbers;

また、COLUMNS() はカラム名を柔軟にマッチングできます。テーブルの特定列だけ処理を行うことも可能で、データ構造に応じた柔軟なクエリ設計が可能です。