Tentang codelab ini
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
Sebelum memulai
Untuk mengikuti petunjuk dalam codelab ini, Anda memerlukan Project Google Cloud dengan BigQuery Studio yang diaktifkan dan akun penagihan yang terhubung.
- Di Konsol Google Cloud, di halaman pemilih project, pilih atau buat project Google Cloud
- Pastikan penagihan diaktifkan untuk project Google Cloud Anda. Pelajari cara memeriksa apakah penagihan telah diaktifkan pada suatu project
- Ikuti petunjuk untuk Mengaktifkan BigQuery Studio untuk pengelolaan aset.
Menyiapkan BigQuery Studio
Buat notebook kosong dan hubungkan ke runtime.
- Buka BigQuery Studio di Konsol Google Cloud.
- Klik ▼ di samping tombol +.
- Pilih Python notebook.
- Tutup pemilih template.
- Pilih + Kode untuk membuat sel kode baru.
- Instal paket BigQuery DataFrames versi terbaru dari sel kode.Ketik perintah berikut.
%pip install --upgrade bigframes --quiet
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:
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:
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:
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:
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:
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:
Hitung rata-rata liter alkohol yang dibeli dengan dua cara berbeda:
- Berapa jumlah rata-rata alkohol yang dibeli per orang di Iowa?
- 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:
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)
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)
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,
)
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
- Terapkan langkah-langkah ini ke data lain, seperti database nama Amerika Serikat.
- Coba buat kode Python di notebook. Notebook Python di BigQuery Studio didukung oleh Colab Enterprise. Petunjuk: Saya merasa meminta bantuan untuk membuat data pengujian cukup berguna.
- Jelajahi contoh notebook untuk BigQuery DataFrames di GitHub.
- Buat jadwal untuk menjalankan notebook di BigQuery Studio.
- Deploy Fungsi Jarak Jauh dengan BigQuery DataFrames untuk mengintegrasikan paket Python pihak ketiga dengan BigQuery.