pandasでBigQueryのデータを操作する方法 (基本編)

記事タイトルとURLをコピーする

G-gen の杉村です。 Python のライブラリである pandas (パンダス) は、データ分析に用いられるツールとして有名です。

当記事では BigQuery から取得したデータを pandas で操作する方法をご紹介します。ごく基本的な内容ですが、コーディング時のメモとして、また Python による BigQuery データを扱う際の練習等にご利用ください。

基本編では簡単なテストデータを使いながら、 SQL での SELECT集計関数 + GROUP BY に相当する操作を確認します。

環境準備

gcloud コマンド

当記事の作業は、お使いの環境で gcloud コマンド が使えることが前提です。

インストールされていない場合、マニュアル Cloud SDK のインストール に従いインストールしてください。
インストールできたら gcloud init コマンドを実行し、プロジェクト情報等を設定します。

python および関連ライブラリ

当記事で利用する各種 SDK 、ライブラリの実行には Python 3.6 以上が必要です (2022 年 5 月現在) 。

Python や pip のインストール方法は、当記事では省略させていただきます。

必要なライブラリは、以下のコマンドでインストールしてください。インストールするのは pandas 、 BigQuery 等のための pandas のデータ型拡張ライブラリである db_dtypes 、 BigQuery 用 SDK である google-cloud-bigquery の 3 つです。

# pip にて必要なパッケージのインストール
pip install --upgrade pandas
pip install --upgrade db_dtypes
pip install --upgrade google-cloud-bigquery

テストデータの準備

今回は以下のような非常に簡単なカンマ区切りのデータを用意しました。
当記事に沿ってハンズオンする方は all_score.csv としてローカルにファイルとして保存してください。

class,name,subject,score
1,佐藤,国語,80
1,佐藤,数学,60
1,佐藤,英語,50
1,田中,国語,70
1,田中,数学,90
1,田中,英語,90
2,鈴木,国語,40
2,鈴木,数学,20
2,鈴木,英語,30
2,伊藤,国語,90
2,伊藤,数学,40
2,伊藤,英語,80

BigQuery データセットおよびテーブルの作成

テスト用のデータセットを作成します。ロケーションやデータセット名 ( ここでは my_test ) は適宜、任意の値に置き換えてください。

# テスト用のデータセット作成
bq mk --dataset --location=asia-northeast1 my_test

テスト用データを投入するテーブルを作成します。テーブル名等は適宜、任意の値に置き換えてください。
以下のコマンドを実行すると、テストデータの csv ファイルが BigQuery のテーブルとしてロード (読み込み) されます。

# テストデータのテーブル作成
bq --location=asia-northeast1 load \
--source_format=CSV \
--skip_leading_rows 1 \
my_test.all_score \
./all_score.csv \
class:INT64,name:STRING,subject:STRING,score:INT64

以下のようにコマンド実行すると、テーブルの中身がプレビューできます。

bq head my_test.all_score
$ bq head my_test.all_score
+-------+------+---------+-------+
| class | name | subject | score |
+-------+------+---------+-------+
|     1 | 佐藤 | 国語    |    80 |
|     1 | 佐藤 | 数学    |    60 |
|     1 | 佐藤 | 英語    |    50 |
|     1 | 田中 | 国語    |    70 |
|     1 | 田中 | 数学    |    90 |
|     1 | 田中 | 英語    |    90 |
|     2 | 鈴木 | 国語    |    40 |
|     2 | 鈴木 | 数学    |    20 |
|     2 | 鈴木 | 英語    |    30 |
|     2 | 伊藤 | 国語    |    90 |
|     2 | 伊藤 | 数学    |    40 |
|     2 | 伊藤 | 英語    |    80 |
+-------+------+---------+-------+

Python の実行

以下のコマンドを実行することで 自分の Google アカウントで認証 して、その権限で Python SDK から API を実行することができるようになります。
コマンドを実行するとブラウザが開くので、 Google アカウントを選択して認証を行ってください。

# 認証情報を取得して Python SDK から利用できるようにする
gcloud auth application-default login

このコマンドを実行すると ~/.config/gcloud/application_default_credentials.json に認証情報ファイルが生成されます。

なお上記を実行しない場合は サービスアカウント を発行して秘密鍵を含んだ認証情報 JSON ファイルをダウンロードし、以下のコマンドで認証情報ファイルを指定する必要があります。

export GOOGLE_APPLICATION_CREDENTIALS=${PATH_TO_CREDENTIAL_FILE}

上記のいずれかで認証方法を決めたら、 python を実行します。

# 環境の設定内容により python もしくは python3
python3 

データフレームの準備

パッケージのインポート

pip でインストール済みのパッケージをインポートします。

# 必要なパッケージのインポート
import pandas
import db_dtypes
from google.cloud import bigquery

BigQuery からデータを取得

BigQuery からデータを取得し データフレーム と呼ばれるオブジェクトとして格納します。

データフレームとは、 表形式のデータを扱うためのオブジェクト であり、行と列を持つことができる形式です。

# クライアント インスタンス生成
client = bigquery.Client()

# クエリ実行とデータフレーム取得
query_str = """
    SELECT
        class, name, subject, score
    FROM
        my_test.all_score
"""

df = client.query(query_str).to_dataframe()

最後の行でクエリ文字列を実行 (Google Cloud に対して API を実行) し、 データフレーム として変数 df に格納しています。

もし権限エラーが出た場合は、Google アカウントが対象プロジェクト (対象データセット) にて BigQuery ジョブユーザー + BigQuery データ閲覧者 + BigQuery 読み取りセッション ユーザー などのロールと紐付いているかご確認ください。検証環境であればプロジェクトに対する BigQuery 管理者オーナー でも問題ないでしょう。

ここからはデータフレームの操作を試してみます。

データフレームのプレビュー・射影・フィルタ

プレビュー

print 等で中身をプレビューすることができます。
量が多い場合は、中盤が自動的に省略して表示されます。

>>> print(df)
    class name subject  score
0       1   佐藤      国語     80
1       1   佐藤      数学     60
2       1   佐藤      英語     50
3       1   田中      国語     70
4       1   田中      数学     90
5       1   田中      英語     90
6       2   鈴木      国語     40
7       2   鈴木      数学     20
8       2   鈴木      英語     30
9       2   伊藤      国語     90
10      2   伊藤      数学     40
11      2   伊藤      英語     80

射影 (SELECT)

以下のようにカラム名を指定することでデータフレームを射影 (SELECT) することができます。

>>> df['name']
0     佐藤
1     佐藤
2     佐藤
3     田中
4     田中
5     田中
6     鈴木
7     鈴木
8     鈴木
9     伊藤
10    伊藤
11    伊藤
Name: name, dtype: object

複数列を指定する際は角括弧を二重に重ねます。

>>> df[['class', 'name']]
    class name
0       1   佐藤
1       1   佐藤
2       1   佐藤
3       1   田中
4       1   田中
5       1   田中
6       2   鈴木
7       2   鈴木
8       2   鈴木
9       2   伊藤
10      2   伊藤
11      2   伊藤

フィルタ (SELECT 〜 WHERE 〜)

角括弧の中に条件文を記載することでフィルタすることが可能です。

>>> df[(df.score > 50)]
    class name subject  score
0       1   佐藤      国語     80
1       1   佐藤      数学     60
3       1   田中      国語     70
4       1   田中      数学     90
5       1   田中      英語     90
9       2   伊藤      国語     90
11      2   伊藤      英語     80

以下のように & でつなげることで複数条件を指定できます。

>>> df[(df.score > 50) & (df.subject == "国語")]
   class name subject  score
0      1   佐藤      国語     80
3      1   田中      国語     70
9      2   伊藤      国語     90

フィルタしたデータフレームに角括弧を続けることで射影が可能です。

>>> df[(df.score > 50) & (df.subject == "国語")]['name']
0    佐藤
3    田中
9    伊藤
Name: name, dtype: object

なお単一のカラムを射影することで得られたオブジェクトは Series 型 です。データフレーム (DataFrame) が 2 次元の表を扱う形式であるのに対して Series は 1 次元を扱う形式です。
以下のように for に渡すことで一つ一つ処理することも可能です。

>>> series = df[(df.score > 50) & (df.subject == "国語")]['name']
>>> for row in series:
...     print(row)
... 
佐藤
田中
伊藤

一意の行を抽出 (SELECT DISTINCT)

以下のようにすることでデータフレームに対して、 SQL で言うところの SELECT DISTINCT を行うことができます。

>>> df[~df.duplicated(subset=['class', 'name'])][['class', 'name']]
   class name
0      1   佐藤
3      1   田中
6      2   鈴木
9      2   伊藤

なぜこんなに長いのか、分解していくと理解することができます。

まず df.duplicated(subset=['class', 'name']) だけを実行すると class と name の組み合わせで 前の行で出てきたことのある組み合わせを持つ行 のインデックスが True になります。
つまり False は初めて現れる組み合わせの行ということです。

>>> df.duplicated(subset=['class', 'name'])
0     False
1      True
2      True
3     False
4      True
5      True
6     False
7      True
8      True
9     False
10     True
11     True
dtype: bool

先頭に ~ をつけると True/False が逆転します。

>>> ~df.duplicated(subset=['class', 'name'])
0      True
1     False
2     False
3      True
4     False
5     False
6      True
7     False
8     False
9      True
10    False
11    False
dtype: bool

df[] の中に先の文を入れると、 True の行だけを抽出してくれます。

>>> df[~df.duplicated(subset=['class', 'name'])]
   class name subject  score
0      1   佐藤      国語     80
3      1   田中      国語     70
6      2   鈴木      国語     40
9      2   伊藤      国語     90

この結果はデータフレームであり、角括弧をつけて必要な列だけを射影することができます (最初の実行結果です) 。

>>> df[~df.duplicated(subset=['class', 'name'])][['class', 'name']]
   class name
0      1   佐藤
3      1   田中
6      2   鈴木
9      2   伊藤

集計 (最大値・最小値・平均値)

データフレーム全体に対する集計

データフレームに対して .max() .min() .mean() メソッドを用いることでそれぞれ最大値、最小値、平均値を取得できます。

>>> df.max()
class       2
name       鈴木
subject    英語
score      90
dtype: object
>>> df.min()
class       1
name       伊藤
subject    国語
score      20
dtype: object
>>> df.mean()
<stdin>:1: FutureWarning: Dropping of nuisance columns in DataFrame reductions (with 'numeric_only=None') is deprecated; in a future version this will raise TypeError.  Select only valid columns before calling the reduction.
class     1.500000
score    61.666667
dtype: float64
>>> 
>>> # 警告が出ないよう数値の列のみに適用。先のコマンドは、将来的にエラーとなる
>>> df[['class','score']].mean()
class     1.500000
score    61.666667
dtype: float64

必要な列の値のみ欲しい場合、以下のようにして得られます。
1つ目のコマンドと 2 つ目のコマンドは同じ結果となっていますが、データ量が多い場合は後者のほうが、先に射影したデータフレームに対して集計を行うためパフォーマンスが良くなる可能性が考えられます。

>>> df.max()['score']
90
>>>
>>> df['score'].max()
90

特定カラムごとの集計 (GROUP BY)

以下のようにして特定カラムで group by して集計することが可能です。

>>> df_grouped = df.groupby(['class', 'subject'])
>>> df_grouped.max()
              name  score
class subject            
1     国語        田中     80
      数学        田中     90
      英語        田中     90
2     国語        鈴木     90
      数学        鈴木     40
      英語        鈴木     80

この結果ですと、関係のない name も出力されてしまっています。以下のようにして必要な値のみを取得できます。

>>> df_grouped.max()['score']
class  subject
1      国語         80
       数学         90
       英語         90
2      国語         90
       数学         40
       英語         80
Name: score, dtype: Int64
>>> df_grouped.max()['score'][1]
subject
国語    80
数学    90
英語    90
Name: score, dtype: Int64
>>> df_grouped.max()['score'][1]['国語']
80

各行に対して操作

データフレームは for 等に渡すことができますが、データフレームは列方向にデータを持つオブジェクトです (カラムナ) ので、これだと順に列名が渡されます 。

>>> for column in df:
...     print(column)
... 
class
name
subject
score

これは多くの場合、やりたいことと異なるはずです。各行に操作を行いたい場合、以下のように itertuples() メソッドが利用できます。

>>> for row in df.itertuples():
...     print(row)
... 
Pandas(Index=0, _1=1, name='佐藤', subject='国語', score=80)
Pandas(Index=1, _1=1, name='佐藤', subject='数学', score=60)
Pandas(Index=2, _1=1, name='佐藤', subject='英語', score=50)
Pandas(Index=3, _1=1, name='田中', subject='国語', score=70)
Pandas(Index=4, _1=1, name='田中', subject='数学', score=90)
Pandas(Index=5, _1=1, name='田中', subject='英語', score=90)
Pandas(Index=6, _1=2, name='鈴木', subject='国語', score=40)
Pandas(Index=7, _1=2, name='鈴木', subject='数学', score=20)
Pandas(Index=8, _1=2, name='鈴木', subject='英語', score=30)
Pandas(Index=9, _1=2, name='伊藤', subject='国語', score=90)
Pandas(Index=10, _1=2, name='伊藤', subject='数学', score=40)
Pandas(Index=11, _1=2, name='伊藤', subject='英語', score=80)

ここで取り出して変数 row に入っている値は namedtuples という型です。
値は [n] または .(列名) で取り出すことができます。

>>> for row in df.itertuples():
...     print(row[3] + " ←これらは同じ意味→ " + row.subject)
... 
国語 ←これらは同じ意味→ 国語
数学 ←これらは同じ意味→ 数学
英語 ←これらは同じ意味→ 英語
国語 ←これらは同じ意味→ 国語
数学 ←これらは同じ意味→ 数学
英語 ←これらは同じ意味→ 英語
国語 ←これらは同じ意味→ 国語
数学 ←これらは同じ意味→ 数学
英語 ←これらは同じ意味→ 英語
国語 ←これらは同じ意味→ 国語
数学 ←これらは同じ意味→ 数学
英語 ←これらは同じ意味→ 英語

杉村 勇馬 (記事一覧) (Facebook)

クラウドソリューション部 部長

元・警察官という経歴を持つ現・エンジニア。クラウド管理運用やネットワークに知見。AWS 12冠、Google Cloud認定資格11冠。

2022年6月現在、ハマっているものはピーナッツくんの新しいアルバム Walk Through the Stars 。