Trang này được dịch bởi Cloud Translation API.

Phân tích dữ liệu khám phá về doanh số bán đồ uống có cồn ở Iowa bằng gói BigQuery DataFrames

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.
```
%pip install --upgrade bigframes --quiet
```
Nhấp vào nút Run cell (Chạy ô) hoặc nhấn tổ hợp phím Shift + Enter để chạy ô mã.

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:

Biểu đồ về số lượng

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:

Biểu đồ thanh về số lượng đồ uống có cồn ở những mã bưu chính bán chạy nhất

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:

Biểu đồ thanh về số lượng đồ uống có cồn ở những mã bưu chính bán chạy nhất

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:

Biểu đồ tán xạ các khu vực lập bảng theo mã bưu chính theo dân số và số lít rượu bán ra

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:

Biểu đồ tán xạ có đường phù hợp nhất

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:

Biểu đồ tán xạ về số lít trên mỗi dân số

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:

Biểu đồ tán xạ về số lít trên mỗi dân số

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)

Biểu đồ thanh về các danh mục rượu mạnh bán chạy nhất

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)

Số từ theo danh mục

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,
)

Bản đồ về loại đồ uống có cồn có tỷ lệ doanh số bán hàng khác biệt nhất so với toàn tiểu bang ở mỗi quận

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.