این صفحه به‌وسیله ‏Cloud Translation API‏ ترجمه شده است.

تجزیه و تحلیل داده های اکتشافی فروش مشروب آیووا با استفاده از بسته BigQuery DataFrames

1. بررسی اجمالی

در این آزمایشگاه، از BigQuery DataFrames از یک نوت بوک پایتون در BigQuery Studio برای تمیز کردن و تجزیه و تحلیل مجموعه داده عمومی فروش مشروب الکلی آیووا استفاده خواهید کرد. از BigQuery ML و قابلیت‌های عملکرد راه دور برای کشف اطلاعات استفاده کنید.

شما یک نوت بوک پایتون برای مقایسه فروش در مناطق جغرافیایی ایجاد خواهید کرد. این می تواند برای کار بر روی هر داده ساختاریافته سازگار شود.

اهداف

در این آزمایشگاه می آموزید که چگونه وظایف زیر را انجام دهید:

نوت بوک های پایتون را در BigQuery Studio فعال و استفاده کنید
با استفاده از بسته BigQuery DataFrames به BigQuery متصل شوید
با استفاده از BigQuery ML یک رگرسیون خطی ایجاد کنید
با استفاده از یک نحو آشنای پاندا مانند، ادغام های پیچیده و پیوستن ها را انجام دهید

2. الزامات

مرورگری مانند کروم یا فایرفاکس
یک پروژه Google Cloud با فعال کردن صورت‌حساب

قبل از شروع

برای پیروی از دستورالعمل‌های این لبه کد، به یک پروژه Google Cloud با فعال BigQuery Studio و یک حساب صورت‌حساب متصل نیاز دارید.

در Google Cloud Console ، در صفحه انتخاب پروژه، یک پروژه Google Cloud را انتخاب یا ایجاد کنید
مطمئن شوید که صورتحساب برای پروژه Google Cloud شما فعال است. با نحوه بررسی فعال بودن صورت‌حساب در پروژه آشنا شوید
دستورالعمل‌های فعال کردن BigQuery Studio را برای مدیریت دارایی دنبال کنید.

BigQuery Studio را آماده کنید

یک نوت بوک خالی بسازید و آن را به زمان اجرا متصل کنید.

در Google Cloud Console به BigQuery Studio بروید.
روی ▼ در کنار دکمه + کلیک کنید.
نوت بوک پایتون را انتخاب کنید.
انتخابگر قالب را ببندید.
برای ایجاد یک سلول کد جدید، + Code را انتخاب کنید.
آخرین نسخه بسته BigQuery DataFrames را از سلول کد نصب کنید. دستور زیر را تایپ کنید.
```
%pip install --upgrade bigframes --quiet
```
روی دکمه Run cell کلیک کنید یا Shift + Enter را فشار دهید تا سلول کد اجرا شود.

3. یک مجموعه داده عمومی را بخوانید

بسته BigQuery DataFrames را با اجرای موارد زیر در یک سلول کد جدید راه اندازی کنید:

import bigframes.pandas as bpd

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

توجه: در این آموزش، ما از "حالت سفارش جزئی" آزمایشی استفاده می‌کنیم که در صورت استفاده از فیلتر پاندا مانند، درخواست‌های کارآمدتر را امکان‌پذیر می‌کند. برخی از ویژگی‌های پاندا که نیاز به سفارش یا فهرست دقیق دارند ممکن است کار نکنند.

نسخه بسته 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() دارد.

درست مانند پانداها، از ویژگی DataFrame.dtypes برای مشاهده تمام ستون های موجود و انواع داده های مربوط به آنها استفاده کنید. اینها به روشی سازگار با پانداها در معرض دید قرار می گیرند.

این سلول را اجرا کنید:

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

خروجی مورد انتظار:

نمودار پراکندگی لیتر در هر جمعیت

میانگین لیتر الکل خریداری شده را به دو روش مختلف محاسبه کنید:

میانگین مقدار الکل خریداری شده برای هر نفر در آیووا چقدر است؟
میانگین مقدار الکل خریداری شده برای هر نفر در همه کدهای پستی چقدر است.

در (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 DataFrames

تنها با حدود 100 دسته، نوشتن برخی اکتشافی ها یا حتی ایجاد نقشه دستی از دسته به نوع مشروب وسیع تر امکان پذیر است. از طرف دیگر، می توان از یک مدل زبان بزرگ مانند Gemini برای ایجاد چنین نقشه برداری استفاده کرد. برای استفاده از BigQuery DataFrames با Gemini از داده‌های بدون ساختار با استفاده از BigQuery DataFrames استفاده کنید .

در عوض، از یک بسته پردازش زبان طبیعی سنتی تر، NLTK، برای پردازش این داده ها استفاده کنید. به عنوان مثال، فناوری به نام "Stemmer" می تواند اسامی جمع و مفرد را در یک مقدار ادغام کند.

استفاده از NLTK برای ریشه کردن کلمات

بسته NLTK روش های پردازش زبان طبیعی را ارائه می دهد که از پایتون قابل دسترسی هستند. بسته را نصب کنید تا آن را امتحان کنید.

%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

متأسفانه، این نقشه ویسکی را به همان ویسکی نشان نداد. ساقه ها با جمع های نامنظم خوب کار نمی کنند. یک lemmatizer را امتحان کنید، که از تکنیک های پیچیده تری برای شناسایی کلمه پایه استفاده می کند که "لما" نامیده می شود.

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

متأسفانه، این lemmatizer «ویسکی» را به همان لم «ویسکی» نگاشت نمی کند. از آنجایی که این کلمه برای پایگاه داده فروش مشروب خرده فروشی آیووا اهمیت ویژه ای دارد، با استفاده از یک فرهنگ لغت، آن را به صورت دستی به املای آمریکایی ترسیم کنید.

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

تبریک میگم این lemmatizer باید برای محدود کردن دسته ها به خوبی کار کند. برای استفاده از آن با 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 در دسترس است، از آن برای ترکیب دسته ها استفاده کنید.

یافتن کلمه ای برای خلاصه کردن بهترین دسته

ابتدا یک 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

توجه داشته باشید که با کلمه نویسی پس از گروه بندی، بار عملکرد Cloud خود را کاهش می دهید. اعمال تابع 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"))
)

بیشترین فروش محصول (لما) را در هر شهرستان پیدا کنید.

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 کوهن برای یافتن اینکه کدام حجم فروش مشروب بیشترین تفاوت را با آنچه که بر اساس نسبت فروش در سراسر کشور انتظار می رود متفاوت است، استفاده کنید.

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 کوهن برای هر لم اندازه گیری شده است، بزرگترین تفاوت را از نسبت سراسری در هر شهرستان بیابید.

# 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 کوهن بزرگتر باشد، احتمال اینکه تفاوت آماری معنی داری در میزان مصرف آن نوع الکل در مقایسه با میانگین ایالتی وجود داشته باشد بیشتر است. برای مقادیر مثبت کوچکتر، تفاوت در مصرف با میانگین کل کشور متفاوت است، اما ممکن است به دلیل تفاوت های تصادفی باشد.

نکته: به نظر نمی رسد شهرستان 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، حساب‌های سرویس، و مجموعه داده‌های ایجاد شده برای این آموزش را حذف کنید.

10. تبریک می گویم!

شما داده های ساخت یافته را با استفاده از BigQuery DataFrames تمیز و تجزیه و تحلیل کرده اید. در طول مسیر مجموعه داده های عمومی Google Cloud، نوت بوک های Python در BigQuery Studio، BigQuery ML، BigQuery Remote Functions و قدرت BigQuery DataFrames را کاوش کرده اید. کار خارق العاده!

مراحل بعدی

این مراحل را روی داده‌های دیگر مانند پایگاه داده نام‌های ایالات متحده اعمال کنید.
سعی کنید کد پایتون را در نوت بوک خود ایجاد کنید . نوت‌بوک‌های پایتون در BigQuery Studio توسط Colab Enterprise پشتیبانی می‌شوند. نکته: به نظر من درخواست کمک برای تولید داده های آزمایشی بسیار مفید است.
نمونه نوت بوک های BigQuery DataFrames را در GitHub کاوش کنید.
برنامه ای برای اجرای یک نوت بوک در BigQuery Studio ایجاد کنید.
برای ادغام بسته‌های Python شخص ثالث با BigQuery، یک تابع از راه دور را با BigQuery DataFrames اجرا کنید.