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) ... 国語 ←これらは同じ意味→ 国語 数学 ←これらは同じ意味→ 数学 英語 ←これらは同じ意味→ 英語 国語 ←これらは同じ意味→ 国語 数学 ←これらは同じ意味→ 数学 英語 ←これらは同じ意味→ 英語 国語 ←これらは同じ意味→ 国語 数学 ←これらは同じ意味→ 数学 英語 ←これらは同じ意味→ 英語 国語 ←これらは同じ意味→ 国語 数学 ←これらは同じ意味→ 数学 英語 ←これらは同じ意味→ 英語
杉村 勇馬 (記事一覧)
執行役員 CTO / クラウドソリューション部 部長
元警察官という経歴を持つ現 IT エンジニア。クラウド管理・運用やネットワークに知見。AWS 12資格、Google Cloud認定資格11資格。X (旧 Twitter) では Google Cloud や AWS のアップデート情報をつぶやいています。
Follow @y_sugi_it