Google Colab上でSQL使ってアンダーサンプリングしてみた

Pythonを使わない、SQLでやるアンダーサンプリング

#Colaboratory

#SQL

クルトン

2023.10.04

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

皆さん、こんにちは。クルトンです！

アンダーサンプリングには、Pythonのモジュールを使う事も可能なのですが、SQLでテーブル操作をしている時にお手軽な確認方法を知りたいと考えやってみました。

SQLの実行方法

実行環境はGoogle Colabで、ランタイムはCPUです。お手軽に実行できますので、実際に実行してみると理解が捗るかもしれません。

まずは必要なモジュールとデータをダウンロードします。

# IPython用のモジュールをダウンロード
!pip install jupysql duckdb-engine

# データ（csvファイル）のダウンロード
!wget https://raw.githubusercontent.com/mwaskom/seaborn-data/master/penguins.csv

ここでダウンロードしたpenguins.csvは、ペンギンの種類やどこの島に生息しているかなどのデータが記録されているテーブルデータになります。

次に、Colab上でSQLが使えるように、データ読み込みとDBの設定をします。

%load_ext sql

%sql duckdb://

これで、データの用意とSQLクエリが使えるようになりました。かなりお手軽ですね。

Colabのセル上でSQLを実行するには、 %%sqlと書いて、その直下にSQL文を書くと良いです。例えば以下のように書きます。

%%sql
SELECT
  *
FROM
  penguins.csv

SQLを実行すると次の画像のように、実行結果が表示されます。 fig1

また、以下のように書いて、実行結果を変数に入れることも可能です。

%%sql 変数名 <<

実際に変数に入れてデータを確認する例は次のようになります。

%%sql all_result <<
SELECT
  *
FROM
  penguins.csv

実行結果を入れたall_resultは、typeがsql.run.resultset.ResultSetです。 変数名.DataFrame()でpandasのDataFrame型に変換できますので、Pythonの処理に移行することも可能です。 fig2

アンダーサンプリングしてみた

次のSQLを実行して、ペンギンの種別ごとのデータ数を確認しました。

# ペンギンの種類を確認
%%sql
SELECT
  species ,count(*) as species_count
FROM
  penguins.csv
GROUP BY
  species

以下が実行結果です。

Running query in 'duckdb://'
species species_count
Adelie  152
Chinstrap   68
Gentoo  124

どうやらChinstrapの数が少ないようです。今回は他のAdelieとGentooのデータ数を、Chinstrapに合わせるためにアンダーサンプリングしてみます。

ちなみにペンギンの種類を日本語名でいうと以下のようになります。

Adelie アデリーペンギン
Chinstrap ヒゲペンギン
Gentoo ジェンツーペンギン

では、早速ですが、アンダーサンプリングを次のSQLで実行してみましょう。

%%sql
WITH tbl AS(
  SELECT
    *, ROW_NUMBER() OVER(PARTITION BY species ORDER BY RANDOM()) as random_sampling
  FROM
      penguins.csv
  WHERE
    species='Adelie'
  UNION ALL
  SELECT
    *, ROW_NUMBER() OVER(PARTITION BY species ORDER BY RANDOM()) as random_sampling
  FROM
      penguins.csv
  WHERE
    species='Chinstrap'
  UNION ALL
  SELECT
    *, ROW_NUMBER() OVER(PARTITION BY species ORDER BY RANDOM()) as random_sampling
  FROM
      penguins.csv
  WHERE
    species='Gentoo'
)
, all_cnt_table AS(
  SELECT
    species ,count(*) as species_count
  FROM
    penguins.csv
  GROUP BY
    species
)
, min_cnt_table AS(
  SELECT
    MIN(species_count) as min_count
  FROM
    all_cnt_table
)
SELECT
  species
  ,island
  ,bill_length_mm
  ,bill_depth_mm
  ,flipper_length_mm
  ,body_mass_g
  ,sex
FROM
  tbl, min_cnt_table
WHERE
  random_sampling <= min_count

上記の内容を変数に入れてみると、次の画像のように、204行のデータ(1種類68個のデータを3種類分の合計204個のデータ)が表示される事からもアンダーサンプリング出来ている事を確認できます。 fig3

アンダーサンプリングに使ったSQLを少し、分解して説明します。

次のSQL文では、データの並び順をランダムにして番号を割り振ります。

SELECT
    *, ROW_NUMBER() OVER(PARTITION BY species ORDER BY RANDOM()) as random_sampling
FROM
    penguins.csv
WHERE
    species='Adelie'

実行結果は以下のようになります。

Running query in 'duckdb://'
species island  bill_length_mm  bill_depth_mm   flipper_length_mm   body_mass_g sex random_sampling
Adelie  Torgersen   34.4    18.4    184 3325    FEMALE  1
Adelie  Dream   37.0    16.5    185 3400    FEMALE  2
Adelie  Dream   36.8    18.5    193 3500    FEMALE  3
Adelie  Dream   38.9    18.8    190 3600    FEMALE  4
Adelie  Biscoe  37.6    17.0    185 3600    FEMALE  5
Adelie  Dream   39.8    19.1    184 4650    MALE    6
Adelie  Dream   40.2    17.1    193 3400    FEMALE  7
Adelie  Dream   38.8    20.0    190 3950    MALE    8
Adelie  Dream   35.7    18.0    202 3550    FEMALE  9
Adelie  Biscoe  42.0    19.5    200 4050    MALE    10

右端にrandom_samplingというカラムがあり、上から順番に値が割り振られています。値が割り振られるデータがランダムです。実際に複数回実行してみると、結果が異なるのが確認できます。また、この段階ではその種別の全てのデータが含まれています。(Adelieだと152個のデータ全てがあります。)

このランダムに値を割り振るというのを、種別ごとにしています。

割り振られた値がどこで使われるかというと、WITH句が終わった後のSELECT文です。

SELECT
  species
  ,island
  ,bill_length_mm
  ,bill_depth_mm
  ,flipper_length_mm
  ,body_mass_g
  ,sex
FROM
  tbl, min_cnt_table
WHERE
  random_sampling <= min_count

WHERE句で、min_countとしている数値以下のデータであるなら表示するとしています。

ここで、min_countというのは68になります。WITH句内において、種別毎のデータ数をカウントし、一番小さい値を取り出したものになります。実装内容はアンダーサンプリングを実行していた上述しているSQL文(all_cnt_tableとmin_cnt_table)をご覧ください。

以上で、アンダーサンプリングをSQLで実行できました！