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() 需要排序，如果您想直观呈现部分数据，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")
美国社区调查通过地理 ID 识别州。对于邮政编码统计区域，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 等大语言模型来创建此类映射。不妨尝试一下 Codelab：使用 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
遗憾的是，这并未将威士忌映射到与 whiskey 相同的词。词干提取器无法很好地处理不规则的复数形式。您可以尝试使用词形还原器，它会使用更复杂的技术来识别基本词（称为“词元”）。
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 Functions 函数的负载。可以对数据库中的数百万行中的每一行应用词形还原函数，但这样做比在分组后应用该函数成本更高，并且可能需要增加配额。
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's 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 DataFrames 清理并分析了结构化数据。在此过程中，您探索了 Google Cloud 的公共数据集、BigQuery Studio 中的 Python 笔记本、BigQuery ML、BigQuery 远程函数以及 BigQuery DataFrames 的强大功能。真了不起！
后续步骤
- 您可以将这些步骤应用于其他数据，例如 USA Names 数据库。
- 不妨尝试在笔记本中生成 Python 代码。BigQuery Studio 中的 Python 笔记本由 Colab Enterprise 提供支持。提示：我发现让 Gemini 帮忙生成测试数据非常有用。
- 在 GitHub 上探索 BigQuery DataFrames 的示例笔记本。
- 创建在 BigQuery Studio 中运行笔记本的调度。
- 部署使用 BigQuery DataFrames 的远程函数，以将第三方 Python 软件包与 BigQuery 集成。