1. 總覽
在本實驗室中，您將使用 BigQuery Studio 中的 Python 筆記本，透過 BigQuery DataFrames 清理及分析愛荷華州酒類銷售公開資料集。善用 BigQuery ML 和遠端函式功能，發掘洞見。
您將建立 Python 筆記本，比較不同地理區域的銷售量。這項功能可用於任何結構化資料。
目標
在本研究室中，您將瞭解如何執行下列工作：
- 在 BigQuery Studio 中啟用及使用 Python 筆記本
- 使用 BigQuery DataFrames 套件連結至 BigQuery
- 使用 BigQuery ML 建立線性迴歸
- 使用熟悉的 pandas 類型語法執行複雜的匯總和彙整作業
2. 需求條件
事前準備
如要按照本程式碼研究室中的操作說明進行操作，您需要具備已啟用 BigQuery Studio 的 Google Cloud 專案，以及已連結的帳單帳戶。
- 在 Google Cloud 控制台的專案選取器頁面中，選取或建立 Google Cloud 專案
- 請確認 Google Cloud 專案已啟用計費功能。瞭解如何檢查專案是否已啟用計費功能
- 按照操作說明啟用 BigQuery Studio 以便管理素材資源。
準備 BigQuery Studio
建立空白筆記本，並將其連線至執行階段。
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. 探索銷售中的相關性
為什麼某些郵遞區號的銷售量高於其他郵遞區號？其中一個假設是，這可能是由於人口規模的差異。人口較多的郵遞區號可能會賣出更多酒精飲料。
計算人口與酒類銷售量之間的相關性，以驗證這個假設。
與其他資料集彙整
彙整人口資料集，例如 美國人口普查局的美國社區問卷調查郵遞區號統計區調查。
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",
)
預期的輸出內容：
處理異方差
上圖中的資料似乎是異質的。最佳適配線周圍的變異數會隨著母體數量增加。
或許每人購買的酒精量相對固定。
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")
)
預期的輸出內容：
計算購買酒精飲料的平均公升數，方法如下：
- 愛荷華州每位消費者平均購買多少酒精飲料？
- 所有郵遞區內，每人購買的酒精飲料平均數量為何。
在 (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. 搭配使用 NLTK 和 BigQuery DataFrames
由於類別只有約 100 個，因此可以編寫一些推論法，甚至手動建立類別與更廣泛的酒類型別的對應關係。或者，您也可以使用 Gemini 等大型語言模型建立這類對應項目。請試試使用 BigQuery DataFrames 從非結構化資料中取得洞察資料程式碼研究室，瞭解如何搭配使用 BigQuery DataFrames 和 Gemini。
請改用傳統的自然語言處理套件 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 可以存取這個函式之前，您必須進行一些一次性的設定。
建立新的程式碼儲存格，並將 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)
部署作業大約需要兩分鐘。
使用遠端函式
部署完成後，您就可以測試這個函式。
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 Function 的負載。您可以對資料庫中的數百萬個資料列套用 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's 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's 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 整合。