تحليل البيانات الاستكشافي لمبيعات الخمور في آيوا باستخدام حزمة BigQuery DataFrames

1. نظرة عامة

في هذا البرنامج التدريبي، ستستخدم إطارات بيانات BigQuery من دفتر ملاحظات Python في BigQuery Studio لتنظيف مجموعة البيانات العامة لمبيعات الخمور في آيوا وتحليلها. استفِد من إمكانات BigQuery ML والدوالّ البعيدة لاكتشاف الإحصاءات.

ستنشئ دفتر ملاحظات Python لمقارنة المبيعات في المناطق الجغرافية المختلفة. ويمكن تعديل هذا الإجراء للعمل على أي بيانات منظَّمة.

الأهداف

في هذه الميزة الاختبارية، ستتعرّف على كيفية تنفيذ المهام التالية:

  • تفعيل دفاتر ملاحظات Python واستخدامها في BigQuery Studio
  • الربط بخدمة BigQuery باستخدام حزمة BigQuery DataFrames
  • إنشاء انحدار خطي باستخدام BigQuery ML
  • تنفيذ عمليات تجميع ودمج معقّدة باستخدام بنية جملة مألوفة مثل pandas

2. المتطلبات

  • متصفّح، مثل Chrome أو Firefox
  • مشروع على Google Cloud تم تفعيل الفوترة فيه

قبل البدء

اتّباع التعليمات الواردة في هذا الدليل التعليمي حول رموز البرامج، ستحتاج إلى مشروع على Google Cloud تم تفعيل BigQuery Studio فيه وحساب فوترة مرتبط.

  1. في Google Cloud Console، اختَر مشروعًا على Google Cloud أو أنشِئه في صفحة أداة اختيار المشاريع.
  2. تأكَّد من تفعيل الفوترة لمشروعك على Google Cloud. تعرَّف على كيفية التحقّق مما إذا كانت الفوترة مفعَّلة في مشروع.
  3. اتّبِع التعليمات لتفعيل BigQuery Studio لإدارة مواد العرض.

إعداد BigQuery Studio

أنشئ دفتر ملاحظات فارغًا واربطه بوقت تشغيل.

  1. انتقِل إلى BigQuery Studio في Google Cloud Console.
  2. انقر على ▼ بجانب الزر +.
  3. اختَر دفتر ملاحظات Python.
  4. أغلِق أداة اختيار النماذج.
  5. انقر على + رمز لإنشاء خلية رمز جديدة.
  6. ثبِّت أحدث إصدار من حزمة BigQuery DataFrames من خلية الرمز البرمجي.اكتب الأمر التالي.
    %pip install --upgrade bigframes --quiet
    انقر على الزر تشغيل الخلية أو اضغط على Shift ‏+ Enter لتشغيل خلية الرمز البرمجي.

3- قراءة مجموعة بيانات عامة

يمكنك إعداد حزمة BigQuery DataFrames من خلال تنفيذ ما يلي في خلية رمز جديدة:

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() لإنشاء إطار بيانات من سلسلة طلب بحث أو رقم تعريف جدول.

شغِّل ما يلي في خلية رمز جديدة لإنشاء إطار بيانات باسم "df":

df = bpd.read_gbq_table("bigquery-public-data.iowa_liquor_sales.sales")

التعرّف على معلومات أساسية عن إطار البيانات

استخدِم طريقة 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.to_pandas() لتنزيل هذه الإحصاءات الموجزة كإطار بيانات 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")

يحدِّد استطلاع American Community Survey الولايات حسب مفتاح GEOID. في ما يتعلّق بمناطق تجميع الرموز البريدية، يكون مفتاح GEOID مساويًا للرمز البريدي.

volume_by_pop = volume_by_zip.join(
    census_acs.set_index("geo_id")
)

أنشئ رسمًا بيانيًا خطيًا للمقارنة بين أعداد السكان في منطقة ترميز الرمز البريدي وعدد لترات الكحول المُباعة.

(
    volume_by_pop[["volume_sold_liters", "total_pop"]]
    .to_pandas()
    .plot.scatter(x="total_pop", y="volume_sold_liters")
)

النتيجة المتوقّعة:

رسم بياني للنقاط المتفرقة يعرض مناطق تجميع الرموز البريدية حسب عدد السكان وعدد لترات الخمور المُباعة

احتساب الارتباطات

يبدو المؤشر خطيًا تقريبًا. يمكنك استخدام نموذج انحدار خطي للتحقّق من مدى قدرة عدد السكان على توقّع مبيعات الخمور.

from bigframes.ml.linear_model import LinearRegression

feature_columns = volume_by_pop[["total_pop"]]
label_columns = volume_by_pop[["volume_sold_liters"]]

# Create the linear model
model = LinearRegression()
model.fit(feature_columns, label_columns)

يمكنك التحقّق من مدى ملاءمة النموذج باستخدام طريقة score.

model.score(feature_columns, label_columns).to_pandas()

نموذج للناتج:

	mean_absolute_error	mean_squared_error	mean_squared_log_error	median_absolute_error	r2_score	explained_variance
0	245065.664095	224398167097.364288	5.595021	178196.31289	0.380096	0.380096

ارسم خط أفضل توافق، ولكن استخدِم الدالة predict على نطاق من قيم الكثافة السكانية.

import matplotlib.pyplot as pyplot
import numpy as np
import pandas as pd

line = pd.Series(np.arange(0, 50_000), name="total_pop")
predictions = model.predict(line).to_pandas()

zips = volume_by_pop[["volume_sold_liters", "total_pop"]].to_pandas()
pyplot.scatter(zips["total_pop"], zips["volume_sold_liters"])
pyplot.plot(
  line,
  predictions.sort_values("total_pop")["predicted_volume_sold_liters"],
  marker=None,
  color="red",
)

النتيجة المتوقّعة:

رسم بياني بالنقاط المبعثرة مع خط أقرب التقريب

معالجة التباين العشوائي

يبدو أنّ البيانات في الرسم البياني السابق غير متجانسة. يزداد التباين حول خط أفضل الملاءمة مع زيادة عدد القيم.

ربما تكون كمية الكحول التي يتم شراؤها لكل شخص ثابتة نسبيًا.

volume_per_pop = (
    volume_by_pop[volume_by_pop['total_pop'] > 0]
    .assign(liters_per_pop=lambda df: df["volume_sold_liters"] / df["total_pop"])
)

(
    volume_per_pop[["liters_per_pop", "total_pop"]]
    .to_pandas()
    .plot.scatter(x="total_pop", y="liters_per_pop")
)

النتيجة المتوقّعة:

رسم بياني خطي للتر لكل فرد

يمكنك احتساب متوسط ليترات الكحول التي تم شراؤها بطريقتَين مختلفتَين:

  1. ما هو متوسط كمية الكحول التي يتم شراؤها لكل شخص في آيوا؟
  2. ما هو متوسّط كمية الكحول التي يتم شراؤها لكل شخص في جميع الرموز البريدية؟

في (1)، يعرض مقدار الكحول الذي يتم شراؤه في الولاية بأكملها. في (2)، يعرض متوسط الرمز البريدي، والذي لن يكون بالضرورة مطابقًا للقيمة (1) لأنّ الرموز البريدية المختلفة تضم مجموعات سكانية مختلفة.

df = (
    bpd.read_gbq_table("bigquery-public-data.iowa_liquor_sales.sales")
    .assign(
        zip_code=lambda _: _["zip_code"].str.replace(".0", "")
    )
)
census_state = bpd.read_gbq(
    "bigquery-public-data.census_bureau_acs.state_2020_5yr",
    index_col="geo_id",
)

volume_per_pop_statewide = (
    df['volume_sold_liters'].sum()
    / census_state["total_pop"].loc['19']
)
volume_per_pop_statewide

النتيجة المتوقّعة: 87.997

average_per_zip = volume_per_pop["liters_per_pop"].mean()
average_per_zip

النتيجة المتوقّعة: 67.139

ارسم هذه المتوسطات، كما هو موضّح أعلاه.

import numpy as np
import pandas as pd
from matplotlib import pyplot

line = pd.Series(np.arange(0, 50_000), name="total_pop")

zips = volume_per_pop[["liters_per_pop", "total_pop"]].to_pandas()
pyplot.scatter(zips["total_pop"], zips["liters_per_pop"])
pyplot.plot(line, np.full(line.shape, volume_per_pop_statewide), marker=None, color="magenta")
pyplot.plot(line, np.full(line.shape, average_per_zip), marker=None, color="red")

النتيجة المتوقّعة:

رسم بياني خطي للتر لكل فرد

لا تزال هناك بعض الرموز البريدية التي تُعدّ قيمًا شاذة كبيرة جدًا، خاصةً في المناطق التي يقلّ فيها عدد السكان. نترك لك مهمة وضع فرضيات حول سبب ذلك. على سبيل المثال، قد يكون عدد السكان في بعض الرموز البريدية منخفضًا ولكنّ معدّل الاستهلاك مرتفع لأنّها تحتوي على متجر الخمور الوحيد في المنطقة. إذا كان الأمر كذلك، قد يؤدي احتساب عدد السكان استنادًا إلى الرموز البريدية المحيطة إلى استبعاد هذه القيم الشاذة.

6- مقارنة أنواع الخمور المُباعة

بالإضافة إلى البيانات الجغرافية، تحتوي قاعدة بيانات مبيعات الخمور بالتجزئة في آيوا أيضًا على معلومات مفصّلة عن السلعة المُباعة. من خلال تحليل هذه البيانات، يمكننا ربما الكشف عن الاختلافات في الأذواق على مستوى المناطق الجغرافية.

عرض الفئات

يتم تصنيف العناصر في قاعدة البيانات. كم عدد الفئات؟

import bigframes.pandas as bpd

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

df = bpd.read_gbq_table("bigquery-public-data.iowa_liquor_sales.sales")
df.category_name.nunique()

النتيجة المتوقّعة: 103

ما هي الفئات الأكثر رواجًا حسب الحجم؟

counts = (
    df.groupby("category_name")
    .agg({"volume_sold_liters": "sum"})
    .sort_values(["volume_sold_liters"], ascending=False)
    .to_pandas()
)
counts.head(25).plot.bar(rot=80)

رسم بياني شريطي لأبرز فئات المشروبات الكحولية المُباعة

العمل مع نوع البيانات ARRAY

تتوفّر عدة فئات لكل من الويسكي والروم والفودكا وغيرها. أريد تجميع هذه العناصر معًا بطريقة ما.

ابدأ بتقسيم أسماء الفئات إلى كلمات منفصلة باستخدام الطريقة Series.str.split(). أزِل الصفيف الذي تم إنشاؤه باستخدام الطريقة explode().

category_parts = df.category_name.str.split(" ").explode()
counts = (
    category_parts
    .groupby(category_parts)
    .size()
    .sort_values(ascending=False)
    .to_pandas()
)
counts.head(25).plot.bar(rot=80)

الكلمات حسب العدد من الفئات

category_parts.nunique()

النتيجة المتوقّعة: 113

عند الاطّلاع على الرسم البياني أعلاه، لا تزال البيانات تعرض VODKA منفصلة عن VODKAS. يجب إجراء المزيد من التجميع لتصغير الفئات إلى مجموعة أصغر.

7- استخدام NLTK مع إطارات بيانات BigQuery

مع توفّر 100 فئة تقريبًا فقط، سيكون من الممكن كتابة بعض الأساليب الاستقرائية أو حتى إنشاء تعيين يدوي من الفئة إلى نوع الخمور الأوسع نطاقًا. بدلاً من ذلك، يمكن استخدام نموذج لغوي كبير مثل Gemini لإنشاء عملية ربط مماثلة. جرِّب دورة تعلم البرمجة الحصول على إحصاءات من البيانات غير المنظَّمة باستخدام إطارات بيانات BigQuery لاستخدام إطارات بيانات 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)

جرِّب ذلك مع بضع كلمات.

stem("WHISKEY")

النتيجة المتوقّعة: whiskey

stem("WHISKIES")

النتيجة المتوقّعة: whiski

لسوء الحظ، لم يؤدّ ذلك إلى ربط أنواع الويسكي بأنواع الويسكي نفسها. لا تعمل أدوات تحليل الجذور بشكل جيد مع الجموع غير المنتظمة. جرِّب استخدام أداة لتحليل الجذور، وهي أداة تستخدِم أساليب أكثر تعقيدًا لتحديد الكلمة الأساسية، والتي تُعرف باسم "الجذر".

def lemmatize(word: str) -> str:
    # https://stackoverflow.com/a/18400977/101923
    # https://www.nltk.org/api/nltk.stem.wordnet.html#module-nltk.stem.wordnet
    import nltk
    import nltk.stem.wordnet


    # Avoid failure if a NULL is passed in.
    if not word:
        return word

    nltk.download('wordnet')
    wnl = nltk.stem.wordnet.WordNetLemmatizer()
    return wnl.lemmatize(word.lower())

جرِّب ذلك مع بضع كلمات.

lemmatize("WHISKIES")

النتيجة المتوقّعة: whisky

lemmatize("WHISKY")

النتيجة المتوقّعة: whisky

lemmatize("WHISKEY")

النتيجة المتوقّعة: whiskey

لا يربط هذا المعرِّف اللفظي كلمة "ويسكي" بالبنية اللفظية نفسها التي ترتبط بها كلمة "ويسكيهات". بما أنّ هذه الكلمة مهمة بشكل خاص لقاعدة بيانات مبيعات الخمور بالتجزئة في آيوا، يمكنك ربطها يدويًا بالطريقة الأمريكية باستخدام قاموس.

def lemmatize(word: str) -> str:
    # https://stackoverflow.com/a/18400977/101923
    # https://www.nltk.org/api/nltk.stem.wordnet.html#module-nltk.stem.wordnet
    import nltk
    import nltk.stem.wordnet


    # Avoid failure if a NULL is passed in.
    if not word:
        return word

    nltk.download('wordnet')
    wnl = nltk.stem.wordnet.WordNetLemmatizer()
    lemma = wnl.lemmatize(word.lower())

    table = {
        "whisky": "whiskey",  # Use the American spelling.
    }
    return table.get(lemma, lemma)

جرِّب ذلك مع بضع كلمات.

lemmatize("WHISKIES")

النتيجة المتوقّعة: whiskey

lemmatize("WHISKEY")

النتيجة المتوقّعة: whiskey

تهانينا! من المفترض أن يعمل أداة تحليل الجذور هذه بشكل جيد لتضييق نطاق الفئات. لاستخدامه مع BigQuery، يجب نشره على السحابة الإلكترونية.

إعداد مشروعك لنشر الدوالّ

قبل نشر هذا الإجراء في السحابة الإلكترونية كي تتمكّن خدمة BigQuery من الوصول إلى هذه الوظيفة، عليك إجراء بعض عمليات الإعداد لمرة واحدة.

أنشئ خلية رمز جديدة واستبدِل your-project-id بمعرّف مشروع Google Cloud الذي تستخدمه في هذا الدليل التعليمي.

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)

من المفترض أن تستغرق عملية النشر دقيقتَين تقريبًا.

استخدام وظائف جهاز التحكّم عن بُعد

بعد اكتمال عملية النشر، يمكنك اختبار هذه الدالة.

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، استخدِمها لدمج الفئات.

العثور على الكلمة التي تلخّص الفئة على أفضل نحو

أولاً، أنشئ إطار بيانات لجميع الفئات في قاعدة البيانات.

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

بعد ذلك، أنشئ إطار بيانات يتضمّن كل الكلمات في الفئات، باستثناء بعض الكلمات التي تُستخدَم ككلمات تملأ الفراغ، مثل علامات الترقيم و "العنصر".

words = (
    categories.assign(
        words=categories['category_name']
        .str.lower()
        .str.split(" ")
    )
    .assign(num_words=lambda _: _['words'].str.len())
    .explode("words")
    .rename(columns={"words": "word"})
)
words = words[
    # Remove punctuation and "item", unless it's the only word
    (words['word'].str.isalnum() & ~(words['word'].str.startswith('item')))
    | (words['num_words'] == 1)
]
words.to_pandas()

النتيجة المتوقّعة:

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

يُرجى العلم أنّه من خلال تحليل الجذر بعد التجميع، يمكنك تقليل الضغط على Cloud 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

الآن بعد أن أصبح لديك صيغة مفردة تلخص كل فئة، يمكنك دمجها مع إطار البيانات الأصلي.

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

النتيجة المتوقّعة:

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

مقارنة المقاطعات

قارِن المبيعات في كل مقاطعة لمعرفة الاختلافات.

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

العثور على المنتج (البنية الأساسية) الأكثر مبيعًا في كل مقاطعة

county_max = (
    county_lemma
    .reset_index(drop=False)
    .groupby("county")
    .agg({"volume_sold_int64": "max"})
)

county_max_lemma = county_lemma[
    county_lemma["volume_sold_int64"] == county_max["volume_sold_int64"]
]

county_max_lemma.to_pandas()

النتيجة المتوقّعة:

	volume_sold_liters	volume_sold_int64
county	lemma		
SCOTT	vodka	6044393.1	6044393
APPANOOSE	whiskey	292490.44	292490
HAMILTON	whiskey	329118.92	329118
...	...	...	...
WORTH	whiskey	100542.85	100542
MITCHELL	vodka	158791.94	158791
RINGGOLD	whiskey	65107.8	65107
101 rows × 2 columns

ما هي أوجه الاختلاف بين المقاطعات؟

county_max_lemma.groupby("lemma").size().to_pandas()

النتيجة المتوقّعة:

lemma	
american	1
liqueur	1
vodka	15
whiskey	83

dtype: Int64

في معظم المقاطعات، يُعدّ الويسكي المنتج الأكثر رواجًا من حيث الحجم، بينما يُعدّ الفودكا الأكثر رواجًا في 15 مقاطعة. قارِن ذلك بأنواع الخمور الأكثر رواجًا على مستوى الولاية.

total_liters = (
    df_with_lemma
    .groupby("lemma")
    .agg({"volume_sold_liters": "sum"})
    .sort_values("volume_sold_liters", ascending=False)
)
total_liters.to_pandas()

النتيجة المتوقّعة:

	volume_sold_liters
lemma	
vodka	85356422.950001
whiskey	85112339.980001
rum	33891011.72
american	19994259.64
imported	14985636.61
tequila	12357782.37
cocktails/rtd	7406769.87
...

يُستهلك الويسكي والفودكا بكميات متقاربة تقريبًا، مع أنّ الفودكا تُستهلك بكميات أكبر قليلاً من الويسكي على مستوى الولاية.

مقارنة النِسب

ما هي الخصائص الفريدة للمبيعات في كل مقاطعة؟ ما الذي يميّز المقاطعة عن بقية الولاية؟

استخدِم مقياس 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، زاد احتمال وجود فرق ذي دلالة إحصائية في كمية هذا النوع من الكحول المستهلكة مقارنةً بمتوسّطات الولاية. بالنسبة إلى القيم الموجبة الأصغر، يختلف الفرق في الاستهلاك عن المتوسط على مستوى الولاية، ولكن قد يرجع ذلك إلى اختلافات عشوائية.

ملاحظة: يبدو أنّ مقاطعة إل باسو ليست مقاطعة في آيوا، ما قد يشير إلى الحاجة إلى تنظيف البيانات مرة أخرى قبل الاعتماد على هذه النتائج بالكامل.

عرض المقاطعات

يمكنك الانضمام إلى جدول 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 وحسابات الخدمات ومجموعات البيانات التي تم إنشاؤها لهذا الدليل التعليمي.

10. تهانينا!

لقد نظّفت البيانات المنظَّمة وحلّلتها باستخدام إطارات بيانات BigQuery. خلال هذه الرحلة، استكشَفت مجموعات البيانات المتاحة للجميع في Google Cloud، ودفاتر ملاحظات Python في BigQuery Studio، وBigQuery ML، وBigQuery Remote Functions، ومدى فعالية BigQuery DataFrames. أحسنت!

الخطوات التالية