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

درباره این codelab

آخرین به‌روزرسانی: دسامبر ۴, ۲۰۲۴

نویسنده: Tim Swena

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

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 با DataFrames BigQuery

تنها با حدود 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 اجرا کنید.

گزارش اشتباه