Analisis data eksplorasi penjualan minuman keras Iowa menggunakan paket BigQuery DataFrames

1. Ringkasan

Di lab ini, Anda akan menggunakan BigQuery DataFrames dari notebook Python di BigQuery Studio untuk membersihkan dan menganalisis set data publik penjualan minuman keras Iowa. Manfaatkan kemampuan BigQuery ML dan fungsi jarak jauh untuk menemukan insight.

Anda akan membuat notebook Python untuk membandingkan penjualan di seluruh area geografis. Hal ini dapat disesuaikan agar berfungsi pada data terstruktur apa pun.

Tujuan

Di lab ini, Anda akan mempelajari cara melakukan tugas berikut:

  • Mengaktifkan dan menggunakan notebook Python di BigQuery Studio
  • Menghubungkan ke BigQuery menggunakan paket BigQuery DataFrames
  • Membuat regresi linear menggunakan BigQuery ML
  • Melakukan agregasi dan penggabungan yang kompleks menggunakan sintaksis seperti pandas yang sudah dikenal

2. Persyaratan

  • Browser, seperti Chrome atau Firefox
  • Project Google Cloud yang mengaktifkan penagihan

Sebelum memulai

Untuk mengikuti petunjuk dalam codelab ini, Anda memerlukan Project Google Cloud dengan BigQuery Studio yang diaktifkan dan akun penagihan yang terhubung.

  1. Di Konsol Google Cloud, di halaman pemilih project, pilih atau buat project Google Cloud
  2. Pastikan penagihan diaktifkan untuk project Google Cloud Anda. Pelajari cara memeriksa apakah penagihan telah diaktifkan pada suatu project
  3. Ikuti petunjuk untuk Mengaktifkan BigQuery Studio untuk pengelolaan aset.

Menyiapkan BigQuery Studio

Buat notebook kosong dan hubungkan ke runtime.

  1. Buka BigQuery Studio di Konsol Google Cloud.
  2. Klik ▼ di samping tombol +.
  3. Pilih Python notebook.
  4. Tutup pemilih template.
  5. Pilih + Kode untuk membuat sel kode baru.
  6. Instal paket BigQuery DataFrames versi terbaru dari sel kode.Ketik perintah berikut.
    %pip install --upgrade bigframes --quiet
    Klik tombol Run cell atau tekan Shift + Enter untuk menjalankan sel kode.

3. Membaca set data publik

Lakukan inisialisasi paket BigQuery DataFrames dengan menjalankan kode berikut di sel kode baru:

import bigframes.pandas as bpd

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

Catatan: dalam tutorial ini, kita menggunakan "mode pengurutan parsial" eksperimental, yang memungkinkan kueri yang lebih efisien saat digunakan dengan pemfilteran seperti pandas. Beberapa fitur pandas yang memerlukan pengurutan atau indeks yang ketat mungkin tidak berfungsi.

Periksa versi paket bigframes Anda dengan

bpd.__version__

Tutorial ini memerlukan versi 1.27.0 atau yang lebih baru.

Penjualan retail minuman keras Iowa

Set data penjualan retail minuman keras Iowa disediakan di BigQuery melalui program set data publik Google Cloud. Set data ini berisi setiap pembelian minuman keras grosir di Negara Bagian Iowa oleh retailer untuk dijual kepada individu sejak 1 Januari 2012. Data dikumpulkan oleh Divisi Minuman Beralkohol di dalam Departemen Perdagangan Iowa.

Di BigQuery, buat kueri bigquery-public-data.iowa_liquor_sales.sales untuk menganalisis penjualan retail minuman keras Iowa. Gunakan metode bigframes.pandas.read_gbq() untuk membuat DataFrame dari string kueri atau ID tabel.

Jalankan kode berikut di sel kode baru untuk membuat DataFrame bernama "df":

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

Menemukan informasi dasar tentang DataFrame

Gunakan metode DataFrame.peek() untuk mendownload sampel kecil data.

Jalankan sel ini:

df.peek()

Output yang diharapkan:

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	...

Catatan: head() memerlukan pengurutan dan umumnya kurang efisien daripada peek() jika Anda ingin memvisualisasikan sampel data.

Sama seperti pandas, gunakan properti DataFrame.dtypes untuk melihat semua kolom yang tersedia dan jenis data yang sesuai. Data ini ditampilkan dengan cara yang kompatibel dengan pandas.

Jalankan sel ini:

df.dtypes

Output yang diharapkan:

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

Metode DataFrame.describe() membuat kueri beberapa statistik dasar dari DataFrame. Jalankan DataFrame.to_pandas() untuk mendownload statistik ringkasan ini sebagai DataFrame pandas.

Jalankan sel ini:

df.describe("all").to_pandas()

Output yang diharapkan:

	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. Memvisualisasikan dan membersihkan data

Set data penjualan retail minuman keras Iowa memberikan informasi geografis yang terperinci, termasuk lokasi toko retail. Gunakan data ini untuk mengidentifikasi tren dan perbedaan di seluruh area geografis.

Memvisualisasikan penjualan per kode pos

Ada beberapa metode visualisasi bawaan seperti DataFrame.plot.hist(). Gunakan metode ini untuk membandingkan penjualan minuman keras menurut kode pos.

volume_by_zip = df.groupby("zip_code").agg({"volume_sold_liters": "sum"})
volume_by_zip.plot.hist(bins=20)

Output yang diharapkan:

Histogram volume

Gunakan diagram batang untuk melihat kode pos mana yang menjual alkohol terbanyak.

(
  volume_by_zip
  .sort_values("volume_sold_liters", ascending=False)
  .head(25)
  .to_pandas()
  .plot.bar(rot=80)
)

Output yang diharapkan:

Diagram batang volume alkohol di kode pos dengan penjualan teratas

Membersihkan data

Beberapa kode pos memiliki .0 di akhir. Mungkin di suatu tempat dalam pengumpulan data, kode pos tidak sengaja dikonversi menjadi nilai floating point. Gunakan ekspresi reguler untuk membersihkan kode pos dan mengulangi analisis.

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

Output yang diharapkan:

Diagram batang volume alkohol di kode pos dengan penjualan teratas

5. Menemukan korelasi dalam penjualan

Mengapa beberapa kode pos menjual lebih banyak daripada yang lain? Salah satu hipotesisnya adalah karena perbedaan ukuran populasi. Kode pos dengan populasi yang lebih banyak kemungkinan akan menjual lebih banyak minuman keras.

Uji hipotesis ini dengan menghitung korelasi antara populasi dan volume penjualan minuman keras.

Menggabungkan dengan set data lain

Gabungkan dengan set data populasi seperti survei area tabulasi kode pos Survei Komunitas Amerika dari Badan Sensus AS.

census_acs = bpd.read_gbq_table("bigquery-public-data.census_bureau_acs.zcta_2020_5yr")

American Community Survey mengidentifikasi negara bagian berdasarkan GEOID. Untuk area tabulasi kode pos, GEOID sama dengan kode pos.

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

Buat diagram pencar untuk membandingkan populasi area tabulasi kode pos dengan liter alkohol yang terjual.

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

Output yang diharapkan:

Diagram sebar area tabulasi kode pos menurut populasi dan liter minuman keras yang terjual

Menghitung korelasi

Tren ini terlihat kira-kira linear. Sesuaikan model regresi linear dengan hal ini untuk memeriksa seberapa baik populasi dapat memprediksi penjualan minuman keras.

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)

Periksa seberapa baik kecocokannya dengan menggunakan metode score.

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

Contoh output:

	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

Menggambar garis yang paling sesuai, tetapi memanggil fungsi predict pada rentang nilai populasi.

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

Output yang diharapkan:

Plot sebar dengan garis yang paling sesuai

Mengatasi heteroskedasitas

Data dalam diagram sebelumnya tampaknya heteroskedastis. Variansi di sekitar garis yang paling cocok akan bertambah seiring dengan populasi.

Mungkin jumlah alkohol yang dibeli per orang relatif konstan.

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

Output yang diharapkan:

Diagram sebar liter per populasi

Hitung rata-rata liter alkohol yang dibeli dengan dua cara berbeda:

  1. Berapa jumlah rata-rata alkohol yang dibeli per orang di Iowa?
  2. Berapa rata-rata jumlah alkohol yang dibeli per orang di semua kode pos.

Di (1), nilai ini mencerminkan jumlah alkohol yang dibeli di seluruh negara bagian. Di (2), kode pos rata-rata ditampilkan, yang tidak selalu sama dengan (1) karena kode pos yang berbeda memiliki populasi yang berbeda.

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

Output yang diharapkan: 87.997

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

Output yang diharapkan: 67.139

Buat plot rata-rata ini, mirip dengan di atas.

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

Output yang diharapkan:

Diagram sebar liter per populasi

Masih ada beberapa kode pos yang merupakan outlier yang cukup besar, terutama di area dengan populasi yang lebih sedikit. Anda dapat membuat hipotesis tentang penyebabnya. Misalnya, mungkin beberapa kode pos memiliki populasi rendah tetapi konsumsi tinggi karena hanya memiliki satu toko minuman keras di area tersebut. Jika demikian, penghitungan berdasarkan populasi kode pos di sekitarnya dapat mengecualikan outlier ini.

6. Membandingkan jenis minuman keras yang dijual

Selain data geografis, database penjualan retail minuman keras Iowa juga berisi informasi mendetail tentang item yang terjual. Mungkin dengan menganalisis hal ini, kita dapat mengungkapkan perbedaan selera di seluruh area geografis.

Jelajahi kategori

Item dikategorikan dalam database. Berapa banyak kategorinya?

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

Output yang diharapkan: 103

Manakah kategori yang paling populer berdasarkan volume?

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)

Diagram batang kategori minuman keras teratas yang terjual

Bekerja dengan jenis data ARRAY

Ada beberapa kategori untuk setiap wiski, rum, vodka, dan lainnya. Saya ingin mengelompokkan semuanya.

Mulai dengan memisahkan nama kategori menjadi kata-kata terpisah menggunakan metode Series.str.split(). Hapus array yang dibuat menggunakan metode 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)

Kata berdasarkan jumlah dari kategori

category_parts.nunique()

Output yang diharapkan: 113

Melihat diagram di atas, data masih memiliki VODKA yang terpisah dari VODKAS. Pengelompokan lainnya diperlukan untuk menciutkan kategori ke dalam kumpulan yang lebih kecil.

7. Menggunakan NLTK dengan BigQuery DataFrames

Dengan hanya sekitar 100 kategori, Anda dapat menulis beberapa heuristik atau bahkan membuat pemetaan dari kategori ke jenis minuman keras yang lebih luas secara manual. Atau, Anda dapat menggunakan model bahasa besar seperti Gemini untuk membuat pemetaan tersebut. Coba codelab Mendapatkan insight dari data tidak terstruktur menggunakan BigQuery DataFrames untuk menggunakan BigQuery DataFrames dengan Gemini.

Sebagai gantinya, gunakan paket natural language processing yang lebih tradisional, NLTK, untuk memproses data ini. Teknologi yang disebut "stemmer" dapat menggabungkan kata benda jamak dan tunggal menjadi nilai yang sama, misalnya.

Menggunakan NLTK untuk meruncingkan kata

Paket NLTK menyediakan metode natural language processing yang dapat diakses dari Python. Instal paket untuk mencobanya.

%pip install nltk

Selanjutnya, impor paket. Periksa versinya. Kode ini akan digunakan nanti dalam tutorial.

import nltk

nltk.__version__

Salah satu cara menstandarkan kata untuk "memotong" kata. Tindakan ini akan menghapus akhiran, seperti "s" di akhir untuk bentuk jamak.

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)

Coba lakukan ini pada beberapa kata.

stem("WHISKEY")

Output yang diharapkan: whiskey

stem("WHISKIES")

Output yang diharapkan: whiski

Sayangnya, hal ini tidak memetakan wiski ke yang sama dengan wiski. Stemmer tidak berfungsi dengan baik dengan bentuk jamak yang tidak beraturan. Coba lemmatizer, yang menggunakan teknik yang lebih canggih untuk mengidentifikasi kata dasar, yang disebut "lemma".

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

Coba lakukan ini pada beberapa kata.

lemmatize("WHISKIES")

Output yang diharapkan: whisky

lemmatize("WHISKY")

Output yang diharapkan: whisky

lemmatize("WHISKEY")

Output yang diharapkan: whiskey

Sayangnya, lemmatizer ini tidak memetakan "whisky" ke lemma yang sama dengan "whiskies". Karena kata ini sangat penting untuk database penjualan minuman keras retail Iowa, petakan secara manual ke ejaan Amerika menggunakan kamus.

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)

Coba lakukan ini pada beberapa kata.

lemmatize("WHISKIES")

Output yang diharapkan: whiskey

lemmatize("WHISKEY")

Output yang diharapkan: whiskey

Selamat! Lemmatizer ini akan berfungsi dengan baik untuk mempersempit kategori. Untuk menggunakannya dengan BigQuery, Anda harus men-deploynya ke cloud.

Menyiapkan project untuk deployment fungsi

Sebelum men-deploynya ke cloud agar BigQuery dapat mengakses fungsi ini, Anda harus melakukan beberapa penyiapan satu kali.

Buat sel kode baru dan ganti your-project-id dengan project ID Google Cloud yang Anda gunakan untuk tutorial ini.

project_id = "your-project-id"

Buat akun layanan tanpa izin apa pun, karena fungsi ini tidak memerlukan akses ke resource cloud apa pun.

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)

Output yang diharapkan: bigframes-no-permissions@your-project-id.iam.gserviceaccount.com

Buat set data BigQuery untuk menyimpan fungsi.

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)

Men-deploy fungsi jarak jauh

Aktifkan Cloud Functions API jika belum diaktifkan.

!gcloud services enable cloudfunctions.googleapis.com

Sekarang, deploy fungsi ke set data yang baru saja Anda buat. Tambahkan dekorator @bpd.remote_function ke fungsi yang Anda buat di langkah sebelumnya.

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

Deployment memerlukan waktu sekitar dua menit.

Menggunakan fungsi jarak jauh

Setelah deployment selesai, Anda dapat menguji fungsi ini.

lemmatize = bpd.read_gbq_function(f"{project_id}.functions.lemmatize")

words = bpd.Series(["whiskies", "whisky", "whiskey", "vodkas", "vodka"])
words.apply(lemmatize).to_pandas()

Output yang diharapkan:

0	whiskey
1	whiskey
2	whiskey
3	vodka
4	vodka

dtype: string

8. Membandingkan konsumsi alkohol menurut county

Setelah fungsi lemmatize tersedia, gunakan fungsi tersebut untuk menggabungkan kategori.

Menemukan kata yang paling tepat untuk meringkas kategori

Pertama, buat DataFrame dari semua kategori dalam database.

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

Output yang diharapkan:

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

Selanjutnya, buat DataFrame dari semua kata dalam kategori, kecuali beberapa kata pengisi seperti tanda baca dan "item".

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

Output yang diharapkan:

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

Perhatikan bahwa dengan melakukan lemmatisasi setelah pengelompokan, Anda mengurangi beban pada Cloud Functions. Anda dapat menerapkan fungsi lemmatize pada setiap dari beberapa juta baris dalam database, tetapi biayanya akan lebih mahal daripada menerapkannya setelah pengelompokan dan mungkin memerlukan penambahan kuota.

lemmas = words.assign(lemma=lambda _: _["word"].apply(lemmatize))
lemmas.to_pandas()

Output yang diharapkan:

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

Setelah kata dilemmatisasi, Anda perlu memilih lemma yang paling meringkas kategori. Karena tidak ada banyak kata fungsi dalam kategori, gunakan heuristik bahwa jika kata muncul dalam beberapa kategori lain, kata tersebut mungkin lebih baik sebagai kata ringkasan (misalnya, wiski).

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

Output yang diharapkan:

	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

Setelah ada satu lemma yang merangkum setiap kategori, gabungkan lemma ini ke DataFrame asli.

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

Output yang diharapkan:

	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

Membandingkan wilayah

Bandingkan penjualan di setiap kabupaten untuk melihat perbedaannya.

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

Temukan produk (lemma) yang paling banyak terjual di setiap county.

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

Output yang diharapkan:

	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

Seberapa berbeda setiap county satu sama lain?

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

Output yang diharapkan:

lemma	
american	1
liqueur	1
vodka	15
whiskey	83

dtype: Int64

Di sebagian besar county, wiski adalah produk paling populer berdasarkan volume, dengan vodka paling populer di 15 county. Bandingkan dengan jenis minuman keras yang paling populer di seluruh negara bagian.

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

Output yang diharapkan:

	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
...

Volume wiski dan vodka hampir sama, dengan vodka sedikit lebih tinggi daripada wiski di seluruh negara bagian.

Membandingkan proporsi

Apa yang unik dari penjualan di setiap wilayah? Apa yang membuat county ini berbeda dari negara bagian lainnya?

Gunakan pengukuran h Cohen untuk menemukan volume penjualan minuman keras yang paling berbeda secara proporsional dari yang diperkirakan berdasarkan proporsi penjualan di seluruh negara bagian.

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

Setelah h Cohen diukur untuk setiap lemma, temukan perbedaan terbesar dari proporsi seluruh negara bagian di setiap county.

# 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()

Output yang diharapkan:

	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

Makin besar nilai h Cohen, makin besar kemungkinan adanya perbedaan yang signifikan secara statistik dalam jumlah jenis alkohol yang dikonsumsi dibandingkan dengan rata-rata negara bagian. Untuk nilai positif yang lebih kecil, perbedaan konsumsi berbeda dengan rata-rata seluruh negara bagian, tetapi mungkin disebabkan oleh perbedaan acak.

Catatan: EL PASO county tampaknya bukan county di Iowa. Hal ini mungkin menunjukkan kebutuhan lain untuk pembersihan data sebelum sepenuhnya bergantung pada hasil ini.

Memvisualisasikan wilayah

Gabungkan dengan tabel bigquery-public-data.geo_us_boundaries.counties untuk mendapatkan area geografis untuk setiap county. Nama county tidak unik di seluruh Amerika Serikat, jadi filter untuk hanya menyertakan county dari Iowa. Kode FIPS untuk Iowa adalah '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

Output yang diharapkan:

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

Gunakan GeoPandas untuk memvisualisasikan perbedaan ini pada peta.

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

Peta alkohol yang paling berbeda dari proporsi volume penjualan di seluruh negara bagian di setiap county

9. Pembersihan

Jika telah membuat project Google Cloud baru untuk tutorial ini, Anda dapat menghapusnya untuk mencegah tagihan tambahan untuk tabel atau resource lain yang dibuat.

Atau, hapus Cloud Functions, akun layanan, dan set data yang dibuat untuk tutorial ini.

10. Selamat!

Anda telah membersihkan dan menganalisis data terstruktur menggunakan DataFrame BigQuery. Selama prosesnya, Anda telah menjelajahi Set Data Publik Google Cloud, notebook Python di BigQuery Studio, BigQuery ML, BigQuery Remote Functions, dan kecanggihan BigQuery DataFrames. Hebat!

Langkah berikutnya