BigQuery DataFrames पैकेज का इस्तेमाल करके, आयोवा में शराब की बिक्री के बारे में एक्सप्लोरेटरी डेटा का विश्लेषण

BigQuery DataFrames पैकेज का इस्तेमाल करके, आयोवा में शराब की बिक्री के बारे में एक्सप्लोरेटरी डेटा का विश्लेषण

इस कोडलैब (कोड बनाना सीखने के लिए ट्यूटोरियल) के बारे में जानकारी

subjectपिछली बार दिस॰ 4, 2024 को अपडेट किया गया
account_circleTim Swena ने लिखा

1. खास जानकारी

इस लैब में, आपको Iowa में शराब की बिक्री के सार्वजनिक डेटासेट को साफ़ करने और उसका विश्लेषण करने के लिए, BigQuery Studio में Python नोटबुक से BigQuery डेटाफ़्रेम का इस्तेमाल करना होगा. अहम जानकारी पाने के लिए, BigQuery ML और रिमोट फ़ंक्शन की सुविधाओं का इस्तेमाल करें.

आपको अलग-अलग भौगोलिक इलाकों में होने वाली बिक्री की तुलना करने के लिए, Python नोटबुक बनानी होगी. इसे किसी भी स्ट्रक्चर्ड डेटा पर काम करने के लिए अडजस्ट किया जा सकता है.

इस लैब में, आपको ये काम करने का तरीका पता चलेगा:

  • BigQuery Studio में Python नोटबुक चालू करना और उनका इस्तेमाल करना
  • BigQuery DataFrames पैकेज का इस्तेमाल करके, BigQuery से कनेक्ट करना
  • BigQuery ML का इस्तेमाल करके लीनियर रिग्रेशन बनाना
  • pandas जैसे सिंटैक्स का इस्तेमाल करके, जटिल एग्रीगेशन और जॉइन करें

2. ज़रूरी शर्तें

  • कोई ब्राउज़र, जैसे कि Chrome या Firefox
  • बिलिंग की सुविधा वाला Google Cloud प्रोजेक्ट

शुरू करने से पहले

इस कोडलैब में दिए गए निर्देशों का पालन करने के लिए, आपके पास BigQuery Studio की सुविधा वाला Google Cloud प्रोजेक्ट और उससे जुड़ा बिलिंग खाता होना चाहिए.

  1. Google Cloud Console में, प्रोजेक्ट चुनने वाले पेज पर, Google Cloud प्रोजेक्ट चुनें या बनाएं
  2. पक्का करें कि आपके Google Cloud प्रोजेक्ट के लिए बिलिंग की सुविधा चालू हो. किसी प्रोजेक्ट के लिए बिलिंग की सुविधा चालू है या नहीं, यह देखने का तरीका जानें
  3. एसेट मैनेजमेंट के लिए BigQuery Studio चालू करने के लिए, दिए गए निर्देशों का पालन करें.

BigQuery Studio को तैयार करना

कोई खाली नोटबुक बनाएं और उसे किसी रनटाइम से कनेक्ट करें.

  1. Google Cloud Console में, BigQuery Studio पर जाएं.
  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 जैसे फ़िल्टर का इस्तेमाल करके ज़्यादा असरदार क्वेरी की जा सकती हैं. ऐसा हो सकता है कि pandas की कुछ सुविधाएं काम न करें. इन सुविधाओं के लिए, डेटा को क्रम से लगाना या इंडेक्स करना ज़रूरी होता है.

bigframes पैकेज का वर्शन देखने के लिए,

bpd.__version__

इस ट्यूटोरियल के लिए, 1.27.0 या इसके बाद का वर्शन ज़रूरी है.

आयोवा में शराब की खुदरा बिक्री

आयोवा में शराब की खुदरा बिक्री का डेटासेट, Google Cloud के सार्वजनिक डेटासेट प्रोग्राम की मदद से BigQuery पर उपलब्ध कराया जाता है. इस डेटासेट में, 1 जनवरी, 2012 से अब तक, आयोवा राज्य में खुदरा दुकानदारों की ओर से, लोगों को बेचने के लिए थोक में खरीदी गई शराब की जानकारी शामिल है. डेटा को आयोवा डिपार्टमेंट ऑफ़ कॉमर्स के अल्कोहलिक बेवरेज डिवीज़न से इकट्ठा किया जाता है.

आयोवा में शराब की खुदरा बिक्री का विश्लेषण करने के लिए, BigQuery में bigquery-public-data.iowa_liquor_sales.sales क्वेरी करें. क्वेरी स्ट्रिंग या टेबल आईडी से DataFrame बनाने के लिए, bigframes.pandas.read_gbq() तरीके का इस्तेमाल करें.

"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 से कुछ बुनियादी आंकड़ों के बारे में क्वेरी करता है. खास जानकारी वाले इन आंकड़ों को Pandas DataFrame के तौर पर डाउनलोड करने के लिए, DataFrame.to_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) में यह दिखाया गया है कि पूरे राज्य में कितनी शराब खरीदी गई. (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. BigQuery डेटाफ़्रेम के साथ NLTK का इस्तेमाल करना

सिर्फ़ 100 कैटगरी के लिए, कुछ अनुमान लगाए जा सकते हैं. इसके अलावा, कैटगरी से शराब के टाइप तक मैपिंग को मैन्युअल तरीके से भी बनाया जा सकता है. इसके अलावा, इस तरह की मैपिंग बनाने के लिए, Gemini जैसे लार्ज लैंग्वेज मॉडल का इस्तेमाल किया जा सकता है. Gemini के साथ BigQuery डेटाफ़्रेम का इस्तेमाल करने के लिए, BigQuery डेटाफ़्रेम का इस्तेमाल करके, अस्ट्रक्चर्ड डेटा से अहम जानकारी पाना कोडलैब आज़माएं.

इसके बजाय, इस डेटा को प्रोसेस करने के लिए, नैचुरल लैंग्वेज प्रोसेसिंग के पारंपरिक पैकेज, 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 फ़ंक्शन उपलब्ध है. इसका इस्तेमाल करके, कैटगरी को आपस में जोड़ें.

कैटगरी की खास जानकारी देने वाला शब्द ढूंढना

सबसे पहले, डेटाबेस में सभी कैटगरी का 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

इसके बाद, कैटगरी में मौजूद सभी शब्दों का डेटाफ़्रेम बनाएं. हालांकि, इसमें विराम चिह्न और "आइटम" जैसे कुछ शब्द शामिल न करें.

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 फ़ंक्शन पर लोड कम हो जाता है. डेटाबेस में मौजूद लाखों लाइनों में से हर पंक्ति पर, लेममाटाइज़ फ़ंक्शन लागू किया जा सकता है. हालांकि, ग्रुप करने के बाद इसे लागू करने की तुलना में, इसकी लागत ज़्यादा होगी. साथ ही, इसके लिए कोटा बढ़ाने की ज़रूरत पड़ सकती है.

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 मेज़र का इस्तेमाल करके पता लगाएं कि शराब की बिक्री के वॉल्यूम, राज्य में होने वाली बिक्री के अनुपात के हिसाब से, उम्मीद से कितने अलग हैं.

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 वैल्यू जितनी ज़्यादा होगी, इस बात की संभावना उतनी ही ज़्यादा होगी कि राज्य के औसत की तुलना में, उस तरह के अल्कोहल के सेवन में आंकड़ों के हिसाब से काफ़ी अंतर है. छोटी पॉज़िटिव वैल्यू के लिए, खपत में अंतर, पूरे राज्य के औसत से अलग होता है. हालांकि, ऐसा अलग-अलग वजहों से हो सकता है.

एक और बात: ऐसा लगता है कि एएल पासो काउंटी, आयोवा में मौजूद काउंटी नहीं है. इससे पता चलता है कि इन नतीजों पर पूरी तरह से भरोसा करने से पहले, डेटा को फिर से व्यवस्थित करने की ज़रूरत है.

काउंटी को विज़ुअलाइज़ करना

हर काउंटी का भौगोलिक क्षेत्र पाने के लिए, bigquery-public-data.geo_us_boundaries.counties टेबल से जॉइन करें. अमेरिका में सभी देशों के नाम यूनीक नहीं होते. इसलिए, फ़िल्टर का इस्तेमाल करके सिर्फ़ आयोवा की काउंटी शामिल करें. आयोवा का एफ़आईपीएस कोड ‘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. बधाई हो!

आपने BigQuery डेटाफ़्रेम का इस्तेमाल करके, स्ट्रक्चर्ड डेटा को क्लीन किया है और उसका विश्लेषण किया है. इस दौरान, आपने Google Cloud के सार्वजनिक डेटासेट, BigQuery Studio में Python नोटबुक, BigQuery ML, BigQuery के रिमोट फ़ंक्शन, और BigQuery डेटाफ़्रेम की सुविधाओं के बारे में जाना है. बहुत बढ़िया!

अगले चरण

bug_report गलती की शिकायत करें