1. 概要
このラボでは、BigQuery Studio の Python ノートブックから BigQuery DataFrames を使用して、アイオワ州の酒類販売の一般公開データセットをクリーンアップして分析します。BigQuery ML とリモート関数の機能を活用して分析情報を発見する。
Python ノートブックを作成して、地域別の売上を比較します。これは、任意の構造化データで動作するように調整できます。
目標
このラボでは、次のタスクの実行方法について学びます。
- BigQuery Studio で Python ノートブックを有効にして使用する
- BigQuery DataFrames パッケージを使用して BigQuery に接続する
- BigQuery ML を使用して線形回帰を作成する
- 使い慣れた pandas のような構文を使用して、複雑な集計と結合を実行する
2. 必要なもの
始める前に
この Codelab の手順を行うには、BigQuery Studio が有効になっている Google Cloud プロジェクトと、接続された請求先アカウントが必要です。
- Google Cloud コンソールのプロジェクト選択ページで、Google Cloud プロジェクトを選択または作成します。
- Google Cloud プロジェクトに対して課金が有効になっていることを確認します。詳しくは、プロジェクトで課金が有効になっているかどうかを確認する方法をご覧ください。
- 手順に沿って、アセット管理に関する BigQuery Studio を有効にする。
BigQuery Studio を準備する
空のノートブックを作成してランタイムに接続します。
- Google Cloud コンソールで BigQuery Studio に移動します。
- [+] ボタンの横にある [▼] をクリックします。
- [Python ノートブック] を選択します。
- テンプレート セレクタを閉じます。
- [+ コード] を選択して、新しいコードセルを作成します。
- コードセルから BigQuery DataFrames パッケージの最新バージョンをインストールします。次のコマンドを入力します。
%pip install --upgrade bigframes --quiet
3. 一般公開データセットを読み取る
新しいコードセルで次のコードを実行して、BigQuery DataFrames パッケージを初期化します。
import bigframes.pandas as bpd
bpd.options.bigquery.ordering_mode = "partial"
bpd.options.display.repr_mode = "deferred"
注: このチュートリアルでは、試験運用版の「部分並べ替えモード」を使用します。このモードでは、pandas のようなフィルタリングと組み合わせることで、より効率的なクエリを実行できます。厳密な順序付けやインデックスが必要な pandas の機能は動作しない場合があります。
bigframes パッケージのバージョンを確認するには、
bpd.__version__
このチュートリアルではバージョン 1.27.0 以降が必要です。
アイオワ州の酒類小売販売
アイオワ州の酒類小売販売データセットは、Google Cloud の一般公開データセット プログラムを通じて BigQuery で提供されています。このデータセットには、2012 年 1 月 1 日以降、アイオワ州で小売業者が個人向けに販売するために卸売で購入したすべての酒類が含まれています。データは、アイオワ州商務局のアルコール飲料課によって収集されます。
BigQuery で bigquery-public-data.iowa_liquor_sales.sales にクエリを実行して、アイオワ州の酒類小売販売を分析します。bigframes.pandas.read_gbq() メソッドを使用して、クエリ文字列またはテーブル ID から DataFrame を作成します。
新しいコードセルで次のコードを実行して、「df」という名前の DataFrame を作成します。
df = bpd.read_gbq_table("bigquery-public-data.iowa_liquor_sales.sales")
DataFrame の基本情報を確認する
DataFrame.peek() メソッドを使用して、データの小さなサンプルをダウンロードします。
このセルを実行します。
df.peek()
想定される出力:
index invoice_and_item_number date store_number store_name ...
0 RINV-04620300080 2023-04-28 10197 SUNSHINE FOODS / HAWARDEN
1 RINV-04864800097 2023-09-25 2621 HY-VEE FOOD STORE #3 / SIOUX CITY
2 RINV-05057200028 2023-12-28 4255 FAREWAY STORES #058 / ORANGE CITY
3 ...
注: head() では並べ替えが必要であり、データのサンプルを可視化する場合、通常は peek() よりも効率が低くなります。
pandas と同様に、DataFrame.dtypes プロパティを使用して、使用可能なすべての列とそれに対応するデータ型を確認します。これらは pandas と互換性のある方法で公開されます。
このセルを実行します。
df.dtypes
想定される出力:
invoice_and_item_number string[pyarrow]
date date32[day][pyarrow]
store_number string[pyarrow]
store_name string[pyarrow]
address string[pyarrow]
city string[pyarrow]
zip_code string[pyarrow]
store_location geometry
county_number string[pyarrow]
county string[pyarrow]
category string[pyarrow]
category_name string[pyarrow]
vendor_number string[pyarrow]
vendor_name string[pyarrow]
item_number string[pyarrow]
item_description string[pyarrow]
pack Int64
bottle_volume_ml Int64
state_bottle_cost Float64
state_bottle_retail Float64
bottles_sold Int64
sale_dollars Float64
volume_sold_liters Float64
volume_sold_gallons Float64
dtype: object
DataFrame.describe() メソッドは、DataFrame から基本的な統計情報をクエリします。DataFrame.to_pandas() を実行して、これらの要約統計量を pandas DataFrame としてダウンロードします。
このセルを実行します。
df.describe("all").to_pandas()
想定される出力:
invoice_and_item_number date store_number store_name ...
nunique 30305765 <NA> 3158 3353 ...
std <NA> <NA> <NA> <NA> ...
mean <NA> <NA> <NA> <NA> ...
75% <NA> <NA> <NA> <NA> ...
25% <NA> <NA> <NA> <NA> ...
count 30305765 <NA> 30305765 30305765 ...
min <NA> <NA> <NA> <NA> ...
50% <NA> <NA> <NA> <NA> ...
max <NA> <NA> <NA> <NA> ...
9 rows × 24 columns
4. データを可視化してクリーニングする
アイオワ州の酒類小売販売データセットには、小売店の場所など、きめ細かい地理情報が含まれています。これらのデータを使用して、地域ごとの傾向や違いを特定します。
郵便番号別の売上を可視化する
DataFrame.plot.hist() など、いくつかの可視化メソッドが組み込まれています。このメソッドを使用して、郵便番号別の酒類販売を比較します。
volume_by_zip = df.groupby("zip_code").agg({"volume_sold_liters": "sum"})
volume_by_zip.plot.hist(bins=20)
想定される出力:
棒グラフを使用して、アルコールの販売数が最も多い郵便番号を確認します。
(
volume_by_zip
.sort_values("volume_sold_liters", ascending=False)
.head(25)
.to_pandas()
.plot.bar(rot=80)
)
想定される出力:
データをクリーニングする
一部の郵便番号には末尾に .0 が付いています。データ収集のどこかで、郵便番号が誤って浮動小数点値に変換された可能性があります。正規表現を使用して郵便番号をクリーンアップし、分析を繰り返す。
df = (
bpd.read_gbq_table("bigquery-public-data.iowa_liquor_sales.sales")
.assign(
zip_code=lambda _: _["zip_code"].str.replace(".0", "")
)
)
volume_by_zip = df.groupby("zip_code").agg({"volume_sold_liters": "sum"})
(
volume_by_zip
.sort_values("volume_sold_liters", ascending=False)
.head(25)
.to_pandas()
.plot.bar(rot=80)
)
想定される出力:
5. 売り上げの相関関係を特定する
一部の郵便番号で販売が他より多いのは何故ですか？1 つの仮説は、人口規模の違いが原因であるというものです。人口が多い郵便番号では、酒類の販売量も増える傾向があります。
この仮説を検証するには、人口と酒類の販売量の相関を計算します。
他のデータセットと結合する
米国国勢調査局の全米コミュニティ調査の郵便番号集計エリア調査などの人口データセットと結合します。
census_acs = bpd.read_gbq_table("bigquery-public-data.census_bureau_acs.zcta_2020_5yr")
米国コミュニティ調査では、州を GEOID で識別します。郵便番号集計エリアの場合、GEOID は郵便番号と同じです。
volume_by_pop = volume_by_zip.join(
census_acs.set_index("geo_id")
)
散布図を作成して、郵便番号集計エリアの人口と販売されたアルコールのリットルを比較します。
(
volume_by_pop[["volume_sold_liters", "total_pop"]]
.to_pandas()
.plot.scatter(x="total_pop", y="volume_sold_liters")
)
想定される出力:
相関を計算する
傾向はほぼ直線的です。これに線形回帰モデルを適合させて、人口が酒類の売上をどの程度予測できるかを確認します。
from bigframes.ml.linear_model import LinearRegression
feature_columns = volume_by_pop[["total_pop"]]
label_columns = volume_by_pop[["volume_sold_liters"]]
# Create the linear model
model = LinearRegression()
model.fit(feature_columns, label_columns)
score メソッドを使用して、適合度を確認します。
model.score(feature_columns, label_columns).to_pandas()
出力例:
mean_absolute_error mean_squared_error mean_squared_log_error median_absolute_error r2_score explained_variance
0 245065.664095 224398167097.364288 5.595021 178196.31289 0.380096 0.380096
最適化曲線を描画しますが、母集団の値の範囲で predict 関数を呼び出します。
import matplotlib.pyplot as pyplot
import numpy as np
import pandas as pd
line = pd.Series(np.arange(0, 50_000), name="total_pop")
predictions = model.predict(line).to_pandas()
zips = volume_by_pop[["volume_sold_liters", "total_pop"]].to_pandas()
pyplot.scatter(zips["total_pop"], zips["volume_sold_liters"])
pyplot.plot(
line,
predictions.sort_values("total_pop")["predicted_volume_sold_liters"],
marker=None,
color="red",
)
想定される出力:
異方性に対処する
上のグラフのデータは異方分散のようです。近似直線の周りの分散は、母集団とともに増加します。
1 人あたりの購入するアルコールの量は比較的一定である可能性があります。
volume_per_pop = (
volume_by_pop[volume_by_pop['total_pop'] > 0]
.assign(liters_per_pop=lambda df: df["volume_sold_liters"] / df["total_pop"])
)
(
volume_per_pop[["liters_per_pop", "total_pop"]]
.to_pandas()
.plot.scatter(x="total_pop", y="liters_per_pop")
)
想定される出力:
購入されたアルコールの平均リットルを 2 つの方法で計算します。
- アイオワ州で 1 人あたり購入されるアルコールの平均量はどれくらいですか？
- すべての郵便番号における 1 人あたりの購入アルコール量の平均はいくらですか。
（1）では、州全体で購入されたアルコールの量が反映されます。（2）は郵便番号ごとの平均値を反映していますが、郵便番号によって人口が異なるため、必ずしも（1）と同じとは限りません。
df = (
bpd.read_gbq_table("bigquery-public-data.iowa_liquor_sales.sales")
.assign(
zip_code=lambda _: _["zip_code"].str.replace(".0", "")
)
)
census_state = bpd.read_gbq(
"bigquery-public-data.census_bureau_acs.state_2020_5yr",
index_col="geo_id",
)
volume_per_pop_statewide = (
df['volume_sold_liters'].sum()
/ census_state["total_pop"].loc['19']
)
volume_per_pop_statewide
想定される出力: 87.997
average_per_zip = volume_per_pop["liters_per_pop"].mean()
average_per_zip
想定される出力: 67.139
上記と同様に、これらの平均値をプロットします。
import numpy as np
import pandas as pd
from matplotlib import pyplot
line = pd.Series(np.arange(0, 50_000), name="total_pop")
zips = volume_per_pop[["liters_per_pop", "total_pop"]].to_pandas()
pyplot.scatter(zips["total_pop"], zips["liters_per_pop"])
pyplot.plot(line, np.full(line.shape, volume_per_pop_statewide), marker=None, color="magenta")
pyplot.plot(line, np.full(line.shape, average_per_zip), marker=None, color="red")
想定される出力:
特に人口の少ない地域では、かなり大きな外れ値となる郵便番号がまだあります。なぜこのような結果になったのかを推測してみてください。たとえば、一部の郵便番号では人口は少ないものの、地域唯一の酒屋があるため消費量が多いという可能性があります。その場合、周辺の郵便番号の人口に基づいて計算すると、これらの外れ値も除外される可能性があります。
6. 販売される酒類の種類の比較
アイオワ州の酒類小売販売データベースには、地理情報に加えて、販売された商品に関する詳細な情報も含まれています。これらのデータを分析することで、地域ごとの好みの違いを明らかにできるかもしれません。
カテゴリを探索
アイテムはデータベースで分類されます。カテゴリはいくつありますか？
import bigframes.pandas as bpd
bpd.options.bigquery.ordering_mode = "partial"
bpd.options.display.repr_mode = "deferred"
df = bpd.read_gbq_table("bigquery-public-data.iowa_liquor_sales.sales")
df.category_name.nunique()
想定される出力: 103
視聴回数が多いカテゴリはどれですか？
counts = (
df.groupby("category_name")
.agg({"volume_sold_liters": "sum"})
.sort_values(["volume_sold_liters"], ascending=False)
.to_pandas()
)
counts.head(25).plot.bar(rot=80)
ARRAY データ型の操作
ウィスキー、ラム、ウォッカなど、それぞれに複数のカテゴリがあります。これらをグループ化したいのですが。
まず、Series.str.split() メソッドを使用して、カテゴリ名を個別の単語に分割します。explode() メソッドを使用して、作成された配列のネストを解除します。
category_parts = df.category_name.str.split(" ").explode()
counts = (
category_parts
.groupby(category_parts)
.size()
.sort_values(ascending=False)
.to_pandas()
)
counts.head(25).plot.bar(rot=80)
category_parts.nunique()
想定される出力: 113
上のグラフを見ると、データでは VODKA と VODKAS がまだ別々になっています。カテゴリを小さなセットにまとめるには、さらにグループ化が必要です。
7. BigQuery DataFrames で NLTK を使用する
カテゴリが 100 個程度であれば、ヒューリスティックを記述したり、カテゴリからより広範な酒類の種類にマッピングを手動で作成したりすることも可能です。または、Gemini などの大規模言語モデルを使用して、このようなマッピングを作成することもできます。BigQuery DataFrames を使用して非構造化データから分析情報を取得する Codelab で、Gemini で BigQuery DataFrames を使用する方法を学びましょう。
代わりに、より従来の自然言語処理パッケージである NLTK を使用して、これらのデータを処理します。たとえば、「ステムマシン」と呼ばれる技術では、複数形と単数形の名詞を同じ値に統合できます。
NLTK を使用して単語の語幹を抽出する
NLTK パッケージには、Python からアクセスできる自然言語処理メソッドが用意されています。パッケージをインストールして試します。
%pip install nltk
次に、パッケージをインポートします。バージョンを検査します。これはチュートリアルの後半で使用します。
import nltk
nltk.__version__
単語を標準化する方法の一つとして、単語を「語幹」に変換する方法があります。これにより、複数形の末尾の「s」などの接尾辞が削除されます。
def stem(word: str) -> str:
# https://www.nltk.org/howto/stem.html
import nltk.stem.snowball
# Avoid failure if a NULL is passed in.
if not word:
return word
stemmer = nltk.stem.snowball.SnowballStemmer("english")
return stemmer.stem(word)
いくつかの単語で試してみましょう。
stem("WHISKEY")
想定される出力: whiskey
stem("WHISKIES")
想定される出力: whiski
残念ながら、この変更により、ウィスキーはウィスキーと同じにマッピングされませんでした。ステマは、不規則な複数形には適していません。より高度な手法を使用して「語幹」と呼ばれる基本単語を特定する、形態素解析ツールを試してみてください。
def lemmatize(word: str) -> str:
# https://stackoverflow.com/a/18400977/101923
# https://www.nltk.org/api/nltk.stem.wordnet.html#module-nltk.stem.wordnet
import nltk
import nltk.stem.wordnet
# Avoid failure if a NULL is passed in.
if not word:
return word
nltk.download('wordnet')
wnl = nltk.stem.wordnet.WordNetLemmatizer()
return wnl.lemmatize(word.lower())
いくつかの単語で試してみましょう。
lemmatize("WHISKIES")
想定される出力: whisky
lemmatize("WHISKY")
想定される出力: whisky
lemmatize("WHISKEY")
想定される出力: whiskey
残念ながら、このレマタイザーは「whiskey」を「whiskies」と同じレマにマッピングしません。この単語はアイオワ州の酒類小売販売データベースで特に重要であるため、辞書を使用して、この単語をアメリカ式のスペルに手動でマッピングします。
def lemmatize(word: str) -> str:
# https://stackoverflow.com/a/18400977/101923
# https://www.nltk.org/api/nltk.stem.wordnet.html#module-nltk.stem.wordnet
import nltk
import nltk.stem.wordnet
# Avoid failure if a NULL is passed in.
if not word:
return word
nltk.download('wordnet')
wnl = nltk.stem.wordnet.WordNetLemmatizer()
lemma = wnl.lemmatize(word.lower())
table = {
"whisky": "whiskey", # Use the American spelling.
}
return table.get(lemma, lemma)
いくつかの単語で試してみましょう。
lemmatize("WHISKIES")
想定される出力: whiskey
lemmatize("WHISKEY")
想定される出力: whiskey
おめでとうございます。このレマタイザーは、カテゴリの絞り込みに適しています。BigQuery で使用するには、クラウドにデプロイする必要があります。
関数のデプロイ用にプロジェクトを設定する
BigQuery がこの関数にアクセスできるようにクラウドにデプロイする前に、1 回限りの設定を行う必要があります。
新しいコードセルを作成し、your-project-id をこのチュートリアルで使用する Google Cloud プロジェクト ID に置き換えます。
project_id = "your-project-id"
この関数はクラウド リソースにアクセスする必要がないため、権限のないサービス アカウントを作成します。
from google.cloud import iam_admin_v1
from google.cloud.iam_admin_v1 import types
iam_admin_client = iam_admin_v1.IAMClient()
request = types.CreateServiceAccountRequest()
account_id = "bigframes-no-permissions"
request.account_id = account_id
request.name = f"projects/{project_id}"
display_name = "bigframes remote function (no permissions)"
service_account = types.ServiceAccount()
service_account.display_name = display_name
request.service_account = service_account
account = iam_admin_client.create_service_account(request=request)
print(account.email)
想定される出力: bigframes-no-permissions@your-project-id.iam.gserviceaccount.com
関数を保持する BigQuery データセットを作成します。
from google.cloud import bigquery
bqclient = bigquery.Client(project=project_id)
dataset = bigquery.Dataset(f"{project_id}.functions")
bqclient.create_dataset(dataset, exists_ok=True)
リモート関数のデプロイ
Cloud Functions API がまだ有効になっていない場合は有効にします。
!gcloud services enable cloudfunctions.googleapis.com
次に、作成したデータセットに関数をデプロイします。前の手順で作成した関数に @bpd.remote_function デコレーターを追加します。
@bpd.remote_function(
dataset=f"{project_id}.functions",
name="lemmatize",
# TODO: Replace this with your version of nltk.
packages=["nltk==3.9.1"],
cloud_function_service_account=f"bigframes-no-permissions@{project_id}.iam.gserviceaccount.com",
cloud_function_ingress_settings="internal-only",
)
def lemmatize(word: str) -> str:
# https://stackoverflow.com/a/18400977/101923
# https://www.nltk.org/api/nltk.stem.wordnet.html#module-nltk.stem.wordnet
import nltk
import nltk.stem.wordnet
# Avoid failure if a NULL is passed in.
if not word:
return word
nltk.download('wordnet')
wnl = nltk.stem.wordnet.WordNetLemmatizer()
lemma = wnl.lemmatize(word.lower())
table = {
"whisky": "whiskey", # Use the American spelling.
}
return table.get(lemma, lemma)
デプロイには約 2 分かかります。
リモート関数の使用
デプロイが完了したら、この関数をテストできます。
lemmatize = bpd.read_gbq_function(f"{project_id}.functions.lemmatize")
words = bpd.Series(["whiskies", "whisky", "whiskey", "vodkas", "vodka"])
words.apply(lemmatize).to_pandas()
想定される出力:
0 whiskey
1 whiskey
2 whiskey
3 vodka
4 vodka
dtype: string
8. アルコール消費量を郡別に比較する
lemmatize 関数を作成したので、これを使用してカテゴリを結合します。
カテゴリを最も適切に要約する単語を見つける
まず、データベース内のすべてのカテゴリの DataFrame を作成します。
df = bpd.read_gbq_table("bigquery-public-data.iowa_liquor_sales.sales")
categories = (
df['category_name']
.groupby(df['category_name'])
.size()
.to_frame()
.rename(columns={"category_name": "total_orders"})
.reset_index(drop=False)
)
categories.to_pandas()
想定される出力:
category_name total_orders
0 100 PROOF VODKA 99124
1 100% AGAVE TEQUILA 724374
2 AGED DARK RUM 59433
3 AMARETTO - IMPORTED 102
4 AMERICAN ALCOHOL 24351
... ... ...
98 WATERMELON SCHNAPPS 17844
99 WHISKEY LIQUEUR 1442732
100 WHITE CREME DE CACAO 7213
101 WHITE CREME DE MENTHE 2459
102 WHITE RUM 436553
103 rows × 2 columns
次に、カテゴリ内のすべての単語の DataFrame を作成します。ただし、句読点や「アイテム」などの一部の代名詞は除きます。
words = (
categories.assign(
words=categories['category_name']
.str.lower()
.str.split(" ")
)
.assign(num_words=lambda _: _['words'].str.len())
.explode("words")
.rename(columns={"words": "word"})
)
words = words[
# Remove punctuation and "item", unless it's the only word
(words['word'].str.isalnum() & ~(words['word'].str.startswith('item')))
| (words['num_words'] == 1)
]
words.to_pandas()
想定される出力:
category_name total_orders word num_words
0 100 PROOF VODKA 99124 100 3
1 100 PROOF VODKA 99124 proof 3
2 100 PROOF VODKA 99124 vodka 3
... ... ... ... ...
252 WHITE RUM 436553 white 2
253 WHITE RUM 436553 rum 2
254 rows × 4 columns
グループ化後にレマタイズすることで、Cloud Functions の負荷を軽減できます。データベース内の数百万行の各行に lemmatize 関数を適用することは可能ですが、グループ化後に適用するよりも費用が高く、割り当ての増加が必要になる場合があります。
lemmas = words.assign(lemma=lambda _: _["word"].apply(lemmatize))
lemmas.to_pandas()
想定される出力:
category_name total_orders word num_words lemma
0 100 PROOF VODKA 99124 100 3 100
1 100 PROOF VODKA 99124 proof 3 proof
2 100 PROOF VODKA 99124 vodka 3 vodka
... ... ... ... ... ...
252 WHITE RUM 436553 white 2 white
253 WHITE RUM 436553 rum 2 rum
254 rows × 5 columns
単語が形態素解析されたので、カテゴリを最もよく要約する形態素を選択する必要があります。カテゴリには機能語があまりないため、ある単語が他の複数のカテゴリに出現する場合は、要約語として適している可能性が高いというヒューリスティクスを使用します（例: ウイスキー）。
lemma_counts = (
lemmas
.groupby("lemma", as_index=False)
.agg({"total_orders": "sum"})
.rename(columns={"total_orders": "total_orders_with_lemma"})
)
categories_with_lemma_counts = lemmas.merge(lemma_counts, on="lemma")
max_lemma_count = (
categories_with_lemma_counts
.groupby("category_name", as_index=False)
.agg({"total_orders_with_lemma": "max"})
.rename(columns={"total_orders_with_lemma": "max_lemma_count"})
)
categories_with_max = categories_with_lemma_counts.merge(
max_lemma_count,
on="category_name"
)
categories_mapping = categories_with_max[
categories_with_max['total_orders_with_lemma'] == categories_with_max['max_lemma_count']
].groupby("category_name", as_index=False).max()
categories_mapping.to_pandas()
想定される出力:
category_name total_orders word num_words lemma total_orders_with_lemma max_lemma_count
0 100 PROOF VODKA 99124 vodka 3 vodka 7575769 7575769
1 100% AGAVE TEQUILA 724374 tequila 3 tequila 1601092 1601092
2 AGED DARK RUM 59433 rum 3 rum 3226633 3226633
... ... ... ... ... ... ... ...
100 WHITE CREME DE CACAO 7213 white 4 white 446225 446225
101 WHITE CREME DE MENTHE 2459 white 4 white 446225 446225
102 WHITE RUM 436553 rum 2 rum 3226633 3226633
103 rows × 7 columns
各カテゴリを要約する単一の語幹が作成されたので、これを元の DataFrame に結合します。
df_with_lemma = df.merge(
categories_mapping,
on="category_name",
how="left"
)
df_with_lemma[df_with_lemma['category_name'].notnull()].peek()
想定される出力:
invoice_and_item_number ... lemma total_orders_with_lemma max_lemma_count
0 S30989000030 ... vodka 7575769 7575769
1 S30538800106 ... vodka 7575769 7575769
2 S30601200013 ... vodka 7575769 7575769
3 S30527200047 ... vodka 7575769 7575769
4 S30833600058 ... vodka 7575769 7575769
5 rows × 30 columns
郡の比較
各郡の売上を比較して、違いを確認します。
county_lemma = (
df_with_lemma
.groupby(["county", "lemma"])
.agg({"volume_sold_liters": "sum"})
# Cast to an integer for more deterministic equality comparisons.
.assign(volume_sold_int64=lambda _: _['volume_sold_liters'].astype("Int64"))
)
各郡で最も売れている商品（レマ）を探します。
county_max = (
county_lemma
.reset_index(drop=False)
.groupby("county")
.agg({"volume_sold_int64": "max"})
)
county_max_lemma = county_lemma[
county_lemma["volume_sold_int64"] == county_max["volume_sold_int64"]
]
county_max_lemma.to_pandas()
想定される出力:
volume_sold_liters volume_sold_int64
county lemma
SCOTT vodka 6044393.1 6044393
APPANOOSE whiskey 292490.44 292490
HAMILTON whiskey 329118.92 329118
... ... ... ...
WORTH whiskey 100542.85 100542
MITCHELL vodka 158791.94 158791
RINGGOLD whiskey 65107.8 65107
101 rows × 2 columns
各郡の違いは何ですか？
county_max_lemma.groupby("lemma").size().to_pandas()
想定される出力:
lemma
american 1
liqueur 1
vodka 15
whiskey 83
dtype: Int64
ほとんどの郡では、販売量で最も人気があるのはウイスキーですが、15 の郡ではウォッカが最も人気があります。州内で最も人気のある酒類の種類と比較します。
total_liters = (
df_with_lemma
.groupby("lemma")
.agg({"volume_sold_liters": "sum"})
.sort_values("volume_sold_liters", ascending=False)
)
total_liters.to_pandas()
想定される出力:
volume_sold_liters
lemma
vodka 85356422.950001
whiskey 85112339.980001
rum 33891011.72
american 19994259.64
imported 14985636.61
tequila 12357782.37
cocktails/rtd 7406769.87
...
ウィスキーとウォッカの販売量はほぼ同じで、州全体ではウォッカの方がウィスキーより少し多くなります。
比率の比較
各郡の売り上げの特徴は何ですか？この郡は州の他の地域とどう違うのですか？
Cohen の h 指標を使用して、州全体での販売の割合に基づいて予想される値と最も比例的に異なる酒類の販売量を確認します。
import numpy as np
total_proportions = total_liters / total_liters.sum()
total_phi = 2 * np.arcsin(np.sqrt(total_proportions))
county_liters = df_with_lemma.groupby(["county", "lemma"]).agg({"volume_sold_liters": "sum"})
county_totals = df_with_lemma.groupby(["county"]).agg({"volume_sold_liters": "sum"})
county_proportions = county_liters / county_totals
county_phi = 2 * np.arcsin(np.sqrt(county_proportions))
cohens_h = (
(county_phi - total_phi)
.rename(columns={"volume_sold_liters": "cohens_h"})
.assign(cohens_h_int=lambda _: (_['cohens_h'] * 1_000_000).astype("Int64"))
)
各レマの Cohen の h を測定したところで、各郡の州全体の割合との差が最も大きいものを探します。
# Note: one might want to use the absolute value here if interested in counties
# that drink _less_ of a particular liquor than expected.
largest_per_county = cohens_h.groupby("county").agg({"cohens_h_int": "max"})
counties = cohens_h[cohens_h['cohens_h_int'] == largest_per_county["cohens_h_int"]]
counties.sort_values('cohens_h', ascending=False).to_pandas()
想定される出力:
cohens_h cohens_h_int
county lemma
EL PASO liqueur 1.289667 1289667
ADAMS whiskey 0.373591 373590
IDA whiskey 0.306481 306481
OSCEOLA whiskey 0.295524 295523
PALO ALTO whiskey 0.293697 293696
... ... ... ...
MUSCATINE rum 0.053757 53757
MARION rum 0.053427 53427
MITCHELL vodka 0.048212 48212
WEBSTER rum 0.044896 44895
CERRO GORDO cocktails/rtd 0.027496 27495
100 rows × 2 columns
Cohen の h 値が大きいほど、その種類のアルコールの消費量が州の平均と比較して統計的に有意な差がある可能性が高くなります。正の値が小さい場合、消費量の差は州全体の平均と異なりますが、これは偶発的な差異による可能性があります。
余談ですが、EL PASO 郡はアイオワ州の郡ではないようです。この結果を完全に信頼する前に、データのクリーンアップが必要になる可能性があります。
郡の可視化
bigquery-public-data.geo_us_boundaries.counties テーブルと結合して、各郡の地理的領域を取得します。郡の名前は米国全体で一意ではないため、アイオワ州の郡のみを表示するようにフィルタします。アイオワの FIPS コードは「19」です。
counties_geo = (
bpd.read_gbq("bigquery-public-data.geo_us_boundaries.counties")
.assign(county=lambda _: _['county_name'].str.upper())
)
counties_plus = (
counties
.reset_index(drop=False)
.merge(counties_geo[counties_geo['state_fips_code'] == '19'], on="county", how="left")
.dropna(subset=["county_geom"])
.to_pandas()
)
counties_plus
想定される出力:
county lemma cohens_h cohens_h_int geo_id state_fips_code ...
0 ALLAMAKEE american 0.087931 87930 19005 19 ...
1 BLACK HAWK american 0.106256 106256 19013 19 ...
2 WINNESHIEK american 0.093101 93101 19191 19 ...
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
96 CLINTON tequila 0.075708 75707 19045 19 ...
97 POLK tequila 0.087438 87438 19153 19 ...
98 LEE schnapps 0.064663 64663 19111 19 ...
99 rows × 23 columns
GeoPandas を使用して、これらの違いを地図上に可視化します。
import geopandas
counties_plus = geopandas.GeoDataFrame(counties_plus, geometry="county_geom")
# https://stackoverflow.com/a/42214156/101923
ax = counties_plus.plot(figsize=(14, 14))
counties_plus.apply(
lambda row: ax.annotate(
text=row['lemma'],
xy=row['county_geom'].centroid.coords[0],
ha='center'
),
axis=1,
)
9. クリーンアップ
このチュートリアル用に新しい Google Cloud プロジェクトを作成した場合は、作成したテーブルやその他のリソースの追加料金が発生しないように、プロジェクトを削除できます。
または、このチュートリアルで作成した Cloud Functions、サービス アカウント、データセットを削除します。
10. 完了
BigQuery DataFrame を使用して構造化データをクリーニングし、分析しました。このチュートリアルでは、Google Cloud の公開データセット、BigQuery Studio の Python ノートブック、BigQuery ML、BigQuery リモート関数、BigQuery DataFrames の強力な機能を学習しました。お疲れさまでした。
次のステップ
- 米国の名前データベースなど、他のデータにもこの手順を適用します。
- ノートブックで Python コードを生成する方法を試してみてください。BigQuery Studio の Python ノートブックは Colab Enterprise をベースにしています。ヒント: テストデータの生成をサポートしてもらうことは非常に有用です。
- GitHub で BigQuery DataFrames のサンプル ノートブックを確認する。
- BigQuery Studio でノートブックを実行するスケジュールを作成します。
- BigQuery DataFrames でリモート関数をデプロイして、サードパーティの Python パッケージを BigQuery と統合します。