หน้านี้ได้รับการแปลโดย Cloud Translation API

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

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