1. 概览
在本实验中，您将使用 BigQuery Studio 中 Python 笔记本中的 BigQuery DataFrame 来清理和分析爱荷华州酒类销售公共数据集。利用 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. 发现销售中的相关性
为什么某些邮政编码的销售额高于其他邮政编码？一种假设是，这可能是因为人口规模存在差异。人口较多的邮政编码地区的白酒销量可能会更高。
通过计算人口与白酒销售量之间的相关性来检验此假设。
与其他数据集联接
与人口数据集（例如 美国人口普查局的美国社区调查问卷邮政编码统计区调查问卷）联接。
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 从非结构化数据中获取数据洞见这门 Codelab，了解如何将 BigQuery DataFrames 与 Gemini 搭配使用。
而是使用更传统的自然语言处理软件包 NLTK 来处理这些数据。例如，一种名为“stemmer”的技术可以将复数名词和单数名词合并为同一值。
使用 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)
部署过程大约需要 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 函数的负载。您可以对数据库中的数百万行中的每一行应用词形变化函数，但这样做所需的费用会比分组后应用该函数高，并且可能需要增加配额。
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。真了不起！
后续步骤
- 将这些步骤应用于其他数据，例如 USA 名称数据库。
- 尝试在笔记本中生成 Python 代码。BigQuery Studio 中的 Python 笔记本由 Colab Enterprise 提供支持。提示：我发现让系统帮忙生成测试数据非常有用。
- 在 GitHub 上浏览 BigQuery DataFrames 示例笔记本。
- 创建在 BigQuery Studio 中运行笔记本的安排。
- 部署使用 BigQuery DataFrames 的远程函数，以将第三方 Python 软件包与 BigQuery 集成。