1. Tổng quan
Trong lớp học này, bạn sẽ sử dụng BigQuery DataFrames từ một sổ tay Python trong BigQuery Studio để dọn dẹp và phân tích tập dữ liệu công khai về doanh số bán đồ uống có cồn ở Iowa. Sử dụng BigQuery ML và các chức năng từ xa để khám phá thông tin chi tiết.
Bạn sẽ tạo một sổ tay Python để so sánh doanh số bán hàng trên các khu vực địa lý. Bạn có thể điều chỉnh để phương thức này hoạt động trên mọi dữ liệu có cấu trúc.
Mục tiêu
Trong lớp học này, bạn sẽ tìm hiểu cách thực hiện các nhiệm vụ sau:
- Kích hoạt và sử dụng sổ tay Python trong BigQuery Studio
- Kết nối với BigQuery bằng gói BigQuery DataFrames
- Tạo hồi quy tuyến tính bằng BigQuery ML
- Thực hiện các phép tổng hợp và nối phức tạp bằng cú pháp quen thuộc giống như pandas
2. Yêu cầu
- Một trình duyệt, chẳng hạn như Chrome hoặc Firefox
- Một dự án Google Cloud đã bật tính năng thanh toán
Trước khi bắt đầu
Để làm theo hướng dẫn trong lớp học lập trình này, bạn cần có một Dự án Google Cloud đã bật BigQuery Studio và một tài khoản thanh toán được kết nối.
- Trong Google Cloud Console, trên trang bộ chọn dự án, hãy chọn hoặc tạo dự án trên Google Cloud
- Đảm bảo bạn đã bật tính năng thanh toán cho dự án trên Google Cloud. Tìm hiểu cách kiểm tra xem tính năng thanh toán có được bật trên dự án hay không
- Làm theo hướng dẫn để Bật BigQuery Studio để quản lý thành phần.
Chuẩn bị BigQuery Studio
Tạo một sổ ghi chú trống và kết nối sổ ghi chú đó với một môi trường thời gian chạy.
- Chuyển đến BigQuery Studio trong Google Cloud Console.
- Nhấp vào biểu tượng ▼ bên cạnh nút +.
- Chọn Sổ tay Python.
- Đóng bộ chọn mẫu.
- Chọn + Mã để tạo một ô mã mới.
- Cài đặt phiên bản mới nhất của gói BigQuery DataFrames từ ô mã.Nhập lệnh sau.
Nhấp vào nút Run cell (Chạy ô) hoặc nhấn tổ hợp phím Shift + Enter để chạy ô mã.%pip install --upgrade bigframes --quiet
3. Đọc tập dữ liệu công khai
Khởi chạy gói BigQuery DataFrames bằng cách chạy đoạn mã sau trong một ô mã mới:
import bigframes.pandas as bpd
bpd.options.bigquery.ordering_mode = "partial"
bpd.options.display.repr_mode = "deferred"
Lưu ý: trong hướng dẫn này, chúng ta sử dụng "chế độ sắp xếp một phần" thử nghiệm, cho phép truy vấn hiệu quả hơn khi sử dụng với bộ lọc giống như pandas. Một số tính năng của pandas yêu cầu thứ tự hoặc chỉ mục nghiêm ngặt có thể không hoạt động.
Kiểm tra phiên bản gói bigframes
bằng
bpd.__version__
Hướng dẫn này yêu cầu phiên bản 1.27.0 trở lên.
Doanh số bán lẻ rượu ở Iowa
Tập dữ liệu bán lẻ đồ uống có cồn ở Iowa được cung cấp trên BigQuery thông qua chương trình tập dữ liệu công khai của Google Cloud. Tập dữ liệu này chứa mọi giao dịch mua bán lẻ rượu tại tiểu bang Iowa để bán cho cá nhân kể từ ngày 1 tháng 1 năm 2012. Dữ liệu do Bộ phận đồ uống có cồn thuộc Sở Thương mại Iowa thu thập.
Trong BigQuery, hãy truy vấn bigquery-public-data.iowa_liquor_sales.sales để phân tích doanh số bán lẻ đồ uống có cồn ở Iowa. Sử dụng phương thức bigframes.pandas.read_gbq()
để tạo DataFrame từ một chuỗi truy vấn hoặc mã nhận dạng bảng.
Chạy mã sau trong một ô mã mới để tạo một DataFrame có tên "df":
df = bpd.read_gbq_table("bigquery-public-data.iowa_liquor_sales.sales")
Khám phá thông tin cơ bản về DataFrame
Sử dụng phương thức DataFrame.peek()
để tải một mẫu nhỏ dữ liệu xuống.
Chạy ô này:
df.peek()
Kết quả dự kiến:
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 ...
Lưu ý: head()
yêu cầu sắp xếp và thường kém hiệu quả hơn peek()
nếu bạn muốn trực quan hoá một mẫu dữ liệu.
Tương tự như với pandas, hãy sử dụng thuộc tính DataFrame.dtypes
để xem tất cả các cột có sẵn và loại dữ liệu tương ứng. Các thuộc tính này được hiển thị theo cách tương thích với pandas.
Chạy ô này:
df.dtypes
Kết quả dự kiến:
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
Phương thức DataFrame.describe()
truy vấn một số số liệu thống kê cơ bản từ DataFrame. Chạy DataFrame.to_pandas()
để tải các số liệu thống kê tóm tắt này xuống dưới dạng DataFrame của pandas.
Chạy ô này:
df.describe("all").to_pandas()
Kết quả dự kiến:
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. Trực quan hoá và làm sạch dữ liệu
Tập dữ liệu bán lẻ đồ uống có cồn ở Iowa cung cấp thông tin địa lý chi tiết, bao gồm cả vị trí của các cửa hàng bán lẻ. Sử dụng những dữ liệu này để xác định xu hướng và sự khác biệt giữa các khu vực địa lý.
Hình ảnh hoá doanh số bán hàng theo mã bưu chính
Có một số phương thức trực quan tích hợp sẵn như DataFrame.plot.hist(). Sử dụng phương thức này để so sánh doanh số bán rượu theo mã bưu chính.
volume_by_zip = df.groupby("zip_code").agg({"volume_sold_liters": "sum"})
volume_by_zip.plot.hist(bins=20)
Kết quả dự kiến:
Sử dụng biểu đồ thanh để xem những mã zip nào bán nhiều đồ uống có cồn nhất.
(
volume_by_zip
.sort_values("volume_sold_liters", ascending=False)
.head(25)
.to_pandas()
.plot.bar(rot=80)
)
Kết quả dự kiến:
Làm sạch dữ liệu
Một số mã zip có .0
ở cuối. Có thể trong quá trình thu thập dữ liệu, mã zip đã vô tình được chuyển đổi thành giá trị dấu phẩy động. Sử dụng biểu thức chính quy để dọn dẹp mã zip và lặp lại quá trình phân tích.
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)
)
Kết quả dự kiến:
5. Khám phá mối tương quan trong doanh số
Tại sao một số mã bưu chính bán được nhiều hơn các mã bưu chính khác? Một giả thuyết cho rằng điều này là do sự khác biệt về quy mô dân số. Mã bưu chính có nhiều dân số hơn có thể sẽ bán được nhiều rượu hơn.
Kiểm thử giả thuyết này bằng cách tính toán mối tương quan giữa dân số và khối lượng bán rượu.
Kết hợp với các tập dữ liệu khác
Kết hợp với một tập dữ liệu dân số, chẳng hạn như cuộc khảo sát khu vực lập bảng mã bưu chính của Cục Thống kê Dân số Hoa Kỳ.
census_acs = bpd.read_gbq_table("bigquery-public-data.census_bureau_acs.zcta_2020_5yr")
Cuộc khảo sát cộng đồng Hoa Kỳ xác định các tiểu bang theo GEOID. Trong trường hợp khu vực lập bảng mã bưu chính, GEOID bằng mã bưu chính.
volume_by_pop = volume_by_zip.join(
census_acs.set_index("geo_id")
)
Tạo biểu đồ tán xạ để so sánh dân số trong khu vực lập bảng theo mã bưu chính với số lít đồ uống có cồn đã bán.
(
volume_by_pop[["volume_sold_liters", "total_pop"]]
.to_pandas()
.plot.scatter(x="total_pop", y="volume_sold_liters")
)
Kết quả dự kiến:
Tính toán mối tương quan
Xu hướng này có vẻ gần như tuyến tính. Hãy điều chỉnh mô hình hồi quy tuyến tính cho dữ liệu này để kiểm tra mức độ chính xác của việc dự đoán doanh số bán rượu dựa trên dân số.
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)
Kiểm tra mức độ phù hợp bằng cách sử dụng phương thức score
.
model.score(feature_columns, label_columns).to_pandas()
Kết quả mẫu:
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
Vẽ đường phù hợp nhất nhưng gọi hàm predict
trên một dải giá trị dân số.
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",
)
Kết quả dự kiến:
Giải quyết vấn đề tính không đồng nhất
Dữ liệu trong biểu đồ trước có vẻ như không đồng biến. Phương sai xung quanh đường phù hợp nhất tăng lên theo số lượng mẫu.
Có thể lượng đồ uống có cồn mà mỗi người mua là tương đối không đổi.
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")
)
Kết quả dự kiến:
Tính số lít đồ uống có cồn trung bình được mua theo hai cách:
- Mỗi người ở Iowa mua trung bình bao nhiêu đồ uống có cồn?
- Số lượng đồ uống có cồn trung bình được mua trên mỗi người theo mã zip.
Trong (1), biến này phản ánh lượng đồ uống có cồn được mua trong toàn tiểu bang. Trong (2), giá trị này phản ánh mã zip trung bình, không nhất thiết phải giống với (1) vì các mã zip khác nhau có dân số khác nhau.
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
Kết quả dự kiến: 87.997
average_per_zip = volume_per_pop["liters_per_pop"].mean()
average_per_zip
Kết quả dự kiến: 67.139
Vẽ các giá trị trung bình này, tương tự như trên.
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")
Kết quả dự kiến:
Vẫn có một số mã bưu chính là giá trị ngoại lai khá lớn, đặc biệt là ở những khu vực có ít dân cư. Bạn có thể tự suy đoán lý do tại sao điều này xảy ra. Ví dụ: một số mã zip có dân số thấp nhưng mức tiêu thụ cao vì đó là nơi có cửa hàng bán rượu duy nhất trong khu vực. Nếu có, việc tính toán dựa trên dân số của các mã zip xung quanh có thể loại bỏ cả những giá trị ngoại lai này.
6. So sánh các loại rượu đã bán
Ngoài dữ liệu địa lý, cơ sở dữ liệu bán lẻ đồ uống có cồn của Iowa cũng chứa thông tin chi tiết về mặt hàng đã bán. Có lẽ bằng cách phân tích những yếu tố này, chúng ta có thể tiết lộ sự khác biệt về thị hiếu giữa các khu vực địa lý.
Khám phá các danh mục
Các mục được phân loại trong cơ sở dữ liệu. Có bao nhiêu danh mục?
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()
Kết quả dự kiến: 103
Những danh mục phổ biến nhất theo số lượng là gì?
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)
Làm việc với loại dữ liệu ARRAY
Có một số danh mục cho từng loại rượu whisky, rượu rum, vodka, v.v. Tôi muốn nhóm các phần tử này lại với nhau.
Bắt đầu bằng cách tách tên danh mục thành các từ riêng biệt bằng phương thức Series.str.split(). Huỷ lồng các mảng mà phương thức này tạo bằng cách sử dụng phương thức 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()
Kết quả dự kiến: 113
Khi xem biểu đồ ở trên, bạn vẫn thấy dữ liệu VODKA tách biệt với VODKAS. Cần thêm nhóm để thu gọn các danh mục thành một nhóm nhỏ hơn.
7. Sử dụng NLTK với BigQuery DataFrames
Với chỉ khoảng 100 danh mục, bạn có thể viết một số phương pháp phỏng đoán hoặc thậm chí tạo mối liên kết từ danh mục đến loại đồ uống có cồn rộng hơn theo cách thủ công. Ngoài ra, bạn có thể sử dụng một mô hình ngôn ngữ lớn như Gemini để tạo mối liên kết như vậy. Hãy thử tham gia lớp học lập trình Nhận thông tin chi tiết từ dữ liệu không có cấu trúc bằng BigQuery DataFrames để sử dụng BigQuery DataFrames với Gemini.
Thay vào đó, hãy sử dụng một gói xử lý ngôn ngữ tự nhiên truyền thống hơn, NLTK, để xử lý những dữ liệu này. Ví dụ: công nghệ có tên là "trình rút gọn" có thể hợp nhất danh từ số nhiều và số ít thành cùng một giá trị.
Sử dụng NLTK để tạo từ gốc
Gói NLTK cung cấp các phương thức xử lý ngôn ngữ tự nhiên có thể truy cập được từ Python. Cài đặt gói để dùng thử.
%pip install nltk
Tiếp theo, hãy nhập gói. Kiểm tra phiên bản. Chúng ta sẽ sử dụng lớp này sau trong hướng dẫn.
import nltk
nltk.__version__
Một cách chuẩn hoá từ để "gốc" từ đó. Thao tác này sẽ xoá mọi hậu tố, chẳng hạn như "s" ở cuối từ cho số nhiều.
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)
Hãy thử làm điều này với một vài từ.
stem("WHISKEY")
Kết quả dự kiến: whiskey
stem("WHISKIES")
Kết quả dự kiến: whiski
Rất tiếc, việc này không liên kết whisky với rượu whisky. Công cụ tạo gốc không hoạt động tốt với các từ số nhiều không quy tắc. Hãy thử một công cụ tạo từ gốc (lemmatizer). Công cụ này sử dụng các kỹ thuật phức tạp hơn để xác định từ gốc, còn gọi là "lemma".
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())
Hãy thử làm điều này với một vài từ.
lemmatize("WHISKIES")
Kết quả dự kiến: whisky
lemmatize("WHISKY")
Kết quả dự kiến: whisky
lemmatize("WHISKEY")
Kết quả dự kiến: whiskey
Rất tiếc, công cụ tạo từ gốc này không liên kết "whisky" với cùng một từ gốc như "whiskies". Vì từ này đặc biệt quan trọng đối với cơ sở dữ liệu bán lẻ rượu ở Iowa, hãy liên kết từ này với cách viết theo tiếng Mỹ theo cách thủ công bằng cách sử dụng từ điển.
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)
Hãy thử làm điều này với một vài từ.
lemmatize("WHISKIES")
Kết quả dự kiến: whiskey
lemmatize("WHISKEY")
Kết quả dự kiến: whiskey
Xin chúc mừng! Công cụ tạo từ gốc này sẽ hoạt động hiệu quả để thu hẹp các danh mục. Để sử dụng công cụ này với BigQuery, bạn phải triển khai công cụ đó lên đám mây.
Thiết lập dự án để triển khai hàm
Trước khi triển khai hàm này lên đám mây để BigQuery có thể truy cập vào hàm này, bạn cần thực hiện một số bước thiết lập một lần.
Tạo một ô mã mới và thay thế your-project-id
bằng mã dự án Google Cloud mà bạn đang sử dụng cho hướng dẫn này.
project_id = "your-project-id"
Tạo một tài khoản dịch vụ không có quyền nào, vì hàm này không cần quyền truy cập vào bất kỳ tài nguyên nào trên đám mây.
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)
Kết quả dự kiến: bigframes-no-permissions@your-project-id.iam.gserviceaccount.com
Tạo một tập dữ liệu BigQuery để lưu trữ hàm.
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)
Triển khai hàm từ xa
Bật API của Cloud Functions nếu bạn chưa bật.
!gcloud services enable cloudfunctions.googleapis.com
Bây giờ, hãy triển khai hàm của bạn cho tập dữ liệu mà bạn vừa tạo. Thêm một trình trang trí @bpd.remote_function
vào hàm mà bạn đã tạo ở các bước trước.
@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)
Quá trình triển khai sẽ mất khoảng 2 phút.
Sử dụng các hàm từ xa
Sau khi triển khai xong, bạn có thể kiểm thử hàm này.
lemmatize = bpd.read_gbq_function(f"{project_id}.functions.lemmatize")
words = bpd.Series(["whiskies", "whisky", "whiskey", "vodkas", "vodka"])
words.apply(lemmatize).to_pandas()
Kết quả dự kiến:
0 whiskey
1 whiskey
2 whiskey
3 vodka
4 vodka
dtype: string
8. So sánh mức tiêu thụ đồ uống có cồn theo hạt
Giờ đây, bạn có thể sử dụng hàm lemmatize
để kết hợp các danh mục.
Tìm từ để tóm tắt danh mục một cách tốt nhất
Trước tiên, hãy tạo một DataFrame của tất cả các danh mục trong cơ sở dữ liệu.
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()
Kết quả dự kiến:
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
Tiếp theo, hãy tạo một DataFrame gồm tất cả các từ trong danh mục, ngoại trừ một số từ đệm như dấu câu và "mặt hàng".
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()
Kết quả dự kiến:
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
Xin lưu ý rằng bằng cách tạo từ gốc sau khi nhóm, bạn đang giảm tải cho Hàm trên đám mây. Bạn có thể áp dụng hàm lemmatize trên mỗi hàng trong số hàng triệu hàng trong cơ sở dữ liệu, nhưng việc này sẽ tốn kém hơn so với việc áp dụng hàm này sau khi nhóm và có thể yêu cầu tăng hạn mức.
lemmas = words.assign(lemma=lambda _: _["word"].apply(lemmatize))
lemmas.to_pandas()
Kết quả dự kiến:
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
Giờ đây, khi các từ đã được từ hoá, bạn cần chọn từ gốc tóm tắt chính xác nhất danh mục. Vì không có nhiều từ chức năng trong các danh mục, hãy sử dụng phương pháp phỏng đoán rằng nếu một từ xuất hiện trong nhiều danh mục khác, thì từ đó có thể là từ tóm tắt tốt hơn (ví dụ: rượu whisky).
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()
Kết quả dự kiến:
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
Bây giờ, bạn có một định nghĩa duy nhất tóm tắt từng danh mục, hãy hợp nhất định nghĩa này vào DataFrame ban đầu.
df_with_lemma = df.merge(
categories_mapping,
on="category_name",
how="left"
)
df_with_lemma[df_with_lemma['category_name'].notnull()].peek()
Kết quả dự kiến:
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
So sánh các hạt
So sánh doanh số bán hàng ở mỗi quận để xem sự khác biệt.
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"))
)
Tìm sản phẩm bán chạy nhất (lemma) ở mỗi quận.
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()
Kết quả dự kiến:
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
Các quận này khác nhau như thế nào?
county_max_lemma.groupby("lemma").size().to_pandas()
Kết quả dự kiến:
lemma
american 1
liqueur 1
vodka 15
whiskey 83
dtype: Int64
Ở hầu hết các quận, rượu whisky là sản phẩm phổ biến nhất theo số lượng, trong khi vodka phổ biến nhất ở 15 quận. So sánh với các loại rượu phổ biến nhất trên toàn tiểu bang.
total_liters = (
df_with_lemma
.groupby("lemma")
.agg({"volume_sold_liters": "sum"})
.sort_values("volume_sold_liters", ascending=False)
)
total_liters.to_pandas()
Kết quả dự kiến:
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
...
Rượu whisky và vodka có gần như cùng một lượng tiêu thụ, trong đó vodka có lượng tiêu thụ cao hơn một chút so với rượu whisky trên toàn tiểu bang.
So sánh tỷ lệ
Doanh số bán hàng ở mỗi quận có gì đặc biệt? Điều gì khiến quận này khác với các quận còn lại của tiểu bang?
Sử dụng chỉ số h của Cohen để tìm ra những loại rượu có doanh số bán chênh lệch nhiều nhất theo tỷ lệ so với dự kiến dựa trên tỷ lệ doanh số bán trên toàn tiểu bang.
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"))
)
Giờ đây, khi h của Cohen đã được đo lường cho mỗi từ nguyên, hãy tìm sự khác biệt lớn nhất so với tỷ lệ trên toàn tiểu bang ở mỗi hạt.
# 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()
Kết quả dự kiến:
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
Giá trị h của Cohen càng lớn thì càng có nhiều khả năng có sự khác biệt có ý nghĩa thống kê về lượng loại đồ uống có cồn đó được tiêu thụ so với mức trung bình của tiểu bang. Đối với các giá trị dương nhỏ hơn, mức chênh lệch về mức tiêu thụ khác với mức trung bình trên toàn tiểu bang, nhưng có thể là do sự khác biệt ngẫu nhiên.
Lưu ý: Có vẻ như hạt EL PASO không phải là hạt ở Iowa. Điều này có thể cho thấy bạn cần phải dọn dẹp dữ liệu trước khi hoàn toàn dựa vào những kết quả này.
Trực quan hoá các hạt
Kết hợp với bảng bigquery-public-data.geo_us_boundaries.counties
để lấy khu vực địa lý cho từng quận. Tên hạt ở Hoa Kỳ có thể trùng nhau, vì vậy, hãy lọc để chỉ bao gồm các hạt ở Iowa. Mã FIPS của Iowa là "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
Kết quả dự kiến:
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
Sử dụng GeoPandas để trực quan hoá những điểm khác biệt này trên bản đồ.
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. Dọn dẹp
Nếu đã tạo một dự án Google Cloud mới cho hướng dẫn này, bạn có thể xoá dự án đó để tránh bị tính thêm phí cho các bảng hoặc tài nguyên khác đã tạo.
Ngoài ra, hãy xoá Chức năng đám mây, tài khoản dịch vụ và tập dữ liệu được tạo cho hướng dẫn này.
10. Xin chúc mừng!
Bạn đã dọn dẹp và phân tích dữ liệu có cấu trúc bằng BigQuery DataFrames. Trong quá trình này, bạn đã khám phá Tập dữ liệu công khai của Google Cloud, sổ tay Python trong BigQuery Studio, BigQuery ML, Hàm từ xa BigQuery và sức mạnh của BigQuery DataFrames. Thật tuyệt vời!
Các bước tiếp theo
- Áp dụng các bước này cho dữ liệu khác, chẳng hạn như cơ sở dữ liệu tên ở Hoa Kỳ.
- Hãy thử tạo mã Python trong sổ tay. Sổ tay Python trong BigQuery Studio được Colab Enterprise hỗ trợ. Gợi ý: Tôi thấy việc yêu cầu trợ giúp tạo dữ liệu kiểm thử khá hữu ích.
- Khám phá sổ tay mẫu cho BigQuery DataFrames trên GitHub.
- Tạo lịch biểu để chạy một sổ tay trong BigQuery Studio.
- Triển khai Hàm từ xa bằng BigQuery DataFrames để tích hợp các gói Python của bên thứ ba với BigQuery.