การวิเคราะห์ข้อมูลเชิงสำรวจเกี่ยวกับยอดขายสุราในไอโอวาโดยใช้แพ็กเกจ DataFrames ของ BigQuery

เกี่ยวกับ Codelab นี้

อัปเดตล่าสุดเมื่อ ธ.ค. 4, 2024

เขียนโดย Tim Swena

1 ภาพรวม

ในชั้นเรียนนี้ คุณจะใช้ DataFrame ของ BigQuery จากโน้ตบุ๊ก Python ใน BigQuery Studio เพื่อล้างและวิเคราะห์ชุดข้อมูลสาธารณะเกี่ยวกับยอดขายเครื่องดื่มแอลกอฮอล์ในไอโอวา ใช้ความสามารถของ BigQuery ML และฟังก์ชันระยะไกลเพื่อค้นพบข้อมูลเชิงลึก

คุณจะต้องสร้างโน้ตบุ๊ก Python เพื่อเปรียบเทียบยอดขายในพื้นที่ทางภูมิศาสตร์ต่างๆ ซึ่งสามารถปรับให้ทำงานกับ Structured Data ใดก็ได้

วัตถุประสงค์

ในบทแนะนำนี้ คุณจะได้เรียนรู้วิธีทํางานต่อไปนี้

เปิดใช้งานและใช้โน้ตบุ๊ก Python ใน BigQuery Studio
เชื่อมต่อกับ BigQuery โดยใช้แพ็กเกจ BigQuery DataFrames
สร้างการถดถอยเชิงเส้นโดยใช้ BigQuery ML
ดำเนินการรวมและเข้าร่วมที่ซับซ้อนโดยใช้ไวยากรณ์ที่คล้ายกับ Pandas ซึ่งคุ้นเคย

2 ข้อกำหนด

เบราว์เซอร์ เช่น Chrome หรือ Firefox
โปรเจ็กต์ Google Cloud ที่เปิดใช้การเรียกเก็บเงิน

ก่อนเริ่มต้น

หากต้องการทําตามวิธีการในโค้ดแล็บนี้ คุณจะต้องมีโปรเจ็กต์ Google Cloud ที่เปิดใช้ BigQuery Studio และบัญชีการเรียกเก็บเงินที่เชื่อมต่อ

ในคอนโซล Google Cloud ให้เลือกหรือสร้างโปรเจ็กต์ Google Cloud ในหน้าตัวเลือกโปรเจ็กต์
ตรวจสอบว่าเปิดใช้การเรียกเก็บเงินสำหรับโปรเจ็กต์ Google Cloud แล้ว ดูวิธีตรวจสอบว่าเปิดใช้การเรียกเก็บเงินในโปรเจ็กต์หรือไม่
ทําตามวิธีการเพื่อเปิดใช้ BigQuery Studio สําหรับการจัดการชิ้นงาน

เตรียม BigQuery Studio

สร้างสมุดบันทึกเปล่าและเชื่อมต่อกับรันไทม์

ไปที่ BigQuery Studio ในคอนโซล Google Cloud
คลิก ▼ ข้างปุ่ม +
เลือกโน้ตบุ๊ก Python
ปิดตัวเลือกเทมเพลต
เลือก + โค้ดเพื่อสร้างเซลล์โค้ดใหม่
ติดตั้งแพ็กเกจ BigQuery DataFrames เวอร์ชันล่าสุดจากเซลล์โค้ด โดยพิมพ์คำสั่งต่อไปนี้
```
%pip install --upgrade bigframes --quiet
```
คลิกปุ่มเรียกใช้เซลล์หรือกด Shift + Enter เพื่อเรียกใช้เซลล์โค้ด

3 อ่านชุดข้อมูลสาธารณะ

เริ่มต้นใช้งานแพ็กเกจ BigQuery DataFrame โดยเรียกใช้โค้ดต่อไปนี้ในเซลล์โค้ดใหม่

import bigframes.pandas as bpd

bpd.options.bigquery.ordering_mode = "partial"
bpd.options.display.repr_mode = "deferred"

หมายเหตุ: ในบทแนะนํานี้ เราจะใช้ "โหมดการจัดเรียงบางส่วน" เวอร์ชันทดลอง ซึ่งช่วยให้การค้นหามีประสิทธิภาพมากขึ้นเมื่อใช้ร่วมกับการกรองแบบแพนด้า ฟีเจอร์บางอย่างของ Pandas ที่กำหนดให้ต้องจัดเรียงหรือมีดัชนีอย่างเคร่งครัดอาจไม่ทำงาน

ตรวจสอบเวอร์ชันแพ็กเกจ bigframes ด้วย

bpd.__version__

บทแนะนำนี้ต้องใช้เวอร์ชัน 1.27.0 ขึ้นไป

ยอดขายปลีกสุราในไอโอวา

ชุดข้อมูลการขายปลีกเครื่องดื่มแอลกอฮอล์ในไอโอวามีอยู่ใน BigQuery ผ่านโปรแกรมชุดข้อมูลสาธารณะของ Google Cloud ชุดข้อมูลนี้ประกอบด้วยการซื้อสุราแบบค้าส่งทั้งหมดในรัฐไอโอวาจากผู้ค้าปลีกเพื่อขายให้แก่บุคคลธรรมดานับตั้งแต่วันที่ 1 มกราคม 2012 ข้อมูลรวบรวมโดยแผนกเครื่องดื่มแอลกอฮอล์ภายในกระทรวงพาณิชย์ของรัฐไอโอวา

ใน BigQuery ให้ค้นหา bigquery-public-data.iowa_liquor_sales.sales เพื่อวิเคราะห์ยอดขายปลีกเหล้าในไอโอวา ใช้เมธอด bigframes.pandas.read_gbq() เพื่อสร้าง DataFrame จากสตริงการค้นหาหรือรหัสตาราง

เรียกใช้โค้ดต่อไปนี้ในเซลล์โค้ดใหม่เพื่อสร้าง DataFrame ชื่อ "df"

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() เพื่อดาวน์โหลดสถิติสรุปเหล่านี้เป็น DataFrame ของ Pandas

เรียกใช้เซลล์นี้:

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

ผลลัพธ์ที่คาดหวัง:

ผังกระจายของลิตรต่อประชากร

คำนวณปริมาณแอลกอฮอล์ที่ซื้อโดยเฉลี่ยได้ 2 วิธีดังนี้

ปริมาณเครื่องดื่มแอลกอฮอล์ที่ซื้อโดยเฉลี่ยต่อคนในไอโอวาคือเท่าใด
ปริมาณเครื่องดื่มแอลกอฮอล์ที่ซื้อต่อคนโดยเฉลี่ยจากรหัสไปรษณีย์ทั้งหมดคือเท่าใด

ใน (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 กับ DataFrame ของ BigQuery

เมื่อมีเพียงประมาณ 100 หมวดหมู่ คุณอาจเขียนวิธีการแก้ปัญหาแบบเฮอร์มิทิคส์หรือสร้างการแมปจากหมวดหมู่ไปยังประเภทเครื่องดื่มที่กว้างขึ้นด้วยตนเองได้ หรือจะใช้โมเดลภาษาขนาดใหญ่อย่าง Gemini เพื่อสร้างการแมปดังกล่าวก็ได้ ลองใช้ Codelab รับข้อมูลเชิงลึกจากข้อมูลที่ไม่เป็นโครงสร้างโดยใช้ DataFrame ของ BigQuery เพื่อใช้ DataFrame ของ BigQuery กับ 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)

ลองใช้กับคำ 2-3 คำ

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

ลองใช้กับคำ 2-3 คำ

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)

ลองใช้กับคำ 2-3 คำ

lemmatize("WHISKIES")

ผลลัพธ์ที่คาดหวัง: whiskey

lemmatize("WHISKEY")

ผลลัพธ์ที่คาดหวัง: whiskey

ยินดีด้วย เครื่องมือแยกคำนี้ควรทำงานได้ดีในการจำกัดหมวดหมู่ให้แคบลง หากต้องการใช้กับ BigQuery คุณต้องติดตั้งใช้งานในระบบคลาวด์

ตั้งค่าโปรเจ็กต์สําหรับการทําให้ฟังก์ชันใช้งานได้

ก่อนที่คุณจะนำข้อมูลนี้ไปใช้งานในระบบคลาวด์เพื่อให้ BigQuery เข้าถึงฟังก์ชันนี้ได้ คุณจะต้องทำการตั้งค่าแบบครั้งเดียว

สร้างเซลล์โค้ดใหม่และแทนที่ your-project-id ด้วยรหัสโปรเจ็กต์ Google Cloud ที่คุณใช้สำหรับบทแนะนำนี้

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

โปรดทราบว่าการทำ Lemmatization หลังจากการจัดกลุ่มจะช่วยลดภาระงานใน Cloud Function คุณสามารถใช้ฟังก์ชัน lemmatize กับแต่ละแถวหลายล้านแถวในฐานข้อมูลได้ แต่วิธีนี้จะใช้ทรัพยากรมากกว่าการใช้หลังจากการจัดกลุ่มและอาจต้องเพิ่มโควต้า

lemmas = words.assign(lemma=lambda _: _["word"].apply(lemmatize))
lemmas.to_pandas()

ผลลัพธ์ที่คาดหวัง:

category_name   total_orders    word    num_words       lemma
0       100 PROOF VODKA 99124   100     3       100
1       100 PROOF VODKA 99124   proof   3       proof
2       100 PROOF VODKA 99124   vodka   3       vodka
...     ...     ...     ...     ...     ...
252     WHITE RUM       436553  white   2       white
253     WHITE RUM       436553  rum     2       rum
254 rows × 5 columns

เมื่อเปลี่ยนคำเป็นคำพื้นฐานแล้ว คุณจะต้องเลือกคำพื้นฐานที่สรุปหมวดหมู่ได้ดีที่สุด เนื่องจากมีคำหน้าที่อยู่ในหมวดหมู่ไม่มากนัก ให้ใช้วิธีการเฮิวริสติกที่ว่าหากคำหนึ่งปรากฏในหมวดหมู่อื่นๆ หลายหมวดหมู่ ก็มีความเป็นไปได้ว่าคำนั้นจะเป็นคำสรุปที่ดีกว่า (เช่น วิสกี้)

lemma_counts = (
    lemmas
    .groupby("lemma", as_index=False)
    .agg({"total_orders": "sum"})
    .rename(columns={"total_orders": "total_orders_with_lemma"})
)

categories_with_lemma_counts = lemmas.merge(lemma_counts, on="lemma")

max_lemma_count = (
    categories_with_lemma_counts
    .groupby("category_name", as_index=False)
    .agg({"total_orders_with_lemma": "max"})
    .rename(columns={"total_orders_with_lemma": "max_lemma_count"})
)

categories_with_max = categories_with_lemma_counts.merge(
    max_lemma_count,
    on="category_name"
)

categories_mapping = categories_with_max[
    categories_with_max['total_orders_with_lemma'] == categories_with_max['max_lemma_count']
].groupby("category_name", as_index=False).max()
categories_mapping.to_pandas()

ผลลัพธ์ที่คาดหวัง:

        category_name   total_orders    word    num_words       lemma   total_orders_with_lemma max_lemma_count
0       100 PROOF VODKA 99124   vodka   3       vodka   7575769 7575769
1       100% AGAVE TEQUILA      724374  tequila 3       tequila 1601092 1601092
2       AGED DARK RUM   59433   rum     3       rum     3226633 3226633
...     ...     ...     ...     ...     ...     ...     ...
100     WHITE CREME DE CACAO    7213    white   4       white   446225  446225
101     WHITE CREME DE MENTHE   2459    white   4       white   446225  446225
102     WHITE RUM       436553  rum     2       rum     3226633 3226633
103 rows × 7 columns

เมื่อเรามีคําที่ตรงกันทั้งหมดรายการเดียวที่สรุปแต่ละหมวดหมู่แล้ว ให้ผสานข้อมูลนี้กับ DataFrame เดิม

df_with_lemma = df.merge(
    categories_mapping,
    on="category_name",
    how="left"
)
df_with_lemma[df_with_lemma['category_name'].notnull()].peek()

ผลลัพธ์ที่คาดหวัง:

        invoice_and_item_number ...     lemma   total_orders_with_lemma max_lemma_count
0       S30989000030    ...     vodka   7575769 7575769
1       S30538800106    ...     vodka   7575769 7575769
2       S30601200013    ...     vodka   7575769 7575769
3       S30527200047    ...     vodka   7575769 7575769
4       S30833600058    ...     vodka   7575769 7575769
5 rows × 30 columns

การเปรียบเทียบเขต

เปรียบเทียบยอดขายในแต่ละเขตเพื่อดูความแตกต่าง

county_lemma = (
    df_with_lemma
    .groupby(["county", "lemma"])
    .agg({"volume_sold_liters": "sum"})
    # Cast to an integer for more deterministic equality comparisons.
    .assign(volume_sold_int64=lambda _: _['volume_sold_liters'].astype("Int64"))
)

ค้นหาผลิตภัณฑ์ (Lemma) ที่ขายดีที่สุดในแต่ละเขต

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
...

วิสกี้และวอดก้ามีปริมาณเกือบเท่ากัน โดยวอดก้ามีปริมาณสูงกว่าวิสกี้เล็กน้อยในรัฐ

การเปรียบเทียบสัดส่วน

ยอดขายในแต่ละเขตมีความโดดเด่นอย่างไร อะไรทำให้เขตแตกต่างจากส่วนอื่นๆ ของรัฐ

ใช้ตัววัด h ของ Cohen เพื่อดูว่าปริมาณการขายเครื่องดื่มแอลกอฮอล์ใดแตกต่างไปตามสัดส่วนมากที่สุดจากสิ่งที่คาดไว้ตามสัดส่วนการขายทั่วรัฐ

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

เมื่อวัด h ของ Cohen สําหรับคําแต่ละคําแล้ว ให้หาความแตกต่างที่ใหญ่ที่สุดจากสัดส่วนระดับรัฐในแต่ละเขต

# 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

ยิ่งค่า h ของ Cohen มีค่ามากเท่าใด ก็ยิ่งมีความเป็นไปได้สูงว่าปริมาณการบริโภคเครื่องดื่มแอลกอฮอล์ประเภทนั้นๆ เมื่อเทียบกับค่าเฉลี่ยของรัฐจะแตกต่างกันอย่างมีนัยสำคัญทางสถิติ สําหรับค่าบวกที่น้อยกว่า ความแตกต่างของปริมาณการใช้จะแตกต่างจากค่าเฉลี่ยระดับรัฐ แต่อาจเกิดจากความแตกต่างแบบสุ่ม

หมายเหตุ: ดูเหมือนว่าเขต 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 ยินดีด้วย

คุณได้ล้างและวิเคราะห์ Structured Data โดยใช้ DataFrame ของ BigQuery คุณได้สำรวจชุดข้อมูลสาธารณะของ Google Cloud, โน้ตบุ๊ค Python ใน BigQuery Studio, BigQuery ML, BigQuery Remote Functions และความสามารถของ BigQuery DataFrame ไปพร้อมๆ กัน ยอดเยี่ยมมาก

ขั้นตอนถัดไป

ใช้ขั้นตอนเหล่านี้กับข้อมูลอื่นๆ เช่น ฐานข้อมูลชื่อในสหรัฐอเมริกา
ลองสร้างโค้ด Python ในโน้ตบุ๊ก สมุดบันทึก Python ใน BigQuery Studio ทำงานด้วย Colab Enterprise เคล็ดลับ: เราพบว่าการขอความช่วยเหลือในการสร้างข้อมูลทดสอบมีประโยชน์มาก
สำรวจโน้ตบุ๊กตัวอย่างสําหรับ DataFrame ของ BigQuery ใน GitHub
สร้างกำหนดเวลาเพื่อเรียกใช้โน้ตบุ๊กใน BigQuery Studio
ติดตั้งใช้งานฟังก์ชันระยะไกลด้วย DataFrame ของ BigQuery เพื่อผสานรวมแพ็กเกจ Python ของบุคคลที่สามกับ BigQuery

รายงานความผิดพลาด