Informationen zu diesem Codelab
1. Übersicht
In diesem Lab verwenden Sie BigQuery DataFrames aus einem Python-Notebook in BigQuery Studio, um das öffentliche Dataset „Iowa Liquor Sales“ zu bereinigen und zu analysieren. Nutzen Sie BigQuery ML und Remote-Funktionen, um Statistiken zu ermitteln.
Sie erstellen ein Python-Notebook, um den Umsatz in verschiedenen Regionen zu vergleichen. Dieser kann für alle strukturierten Daten angepasst werden.
Zielsetzungen
Aufgaben in diesem Lab:
- Python-Notebooks in BigQuery Studio aktivieren und verwenden
- Mit dem BigQuery DataFrames-Paket eine Verbindung zu BigQuery herstellen
- Lineare Regression mit BigQuery ML erstellen
- Komplexe Aggregationen und Joins mit einer vertrauten Pandas-ähnlichen Syntax ausführen
2. Voraussetzungen
Hinweis
Um der Anleitung in diesem Codelab zu folgen, benötigen Sie ein Google Cloud-Projekt mit aktiviertem BigQuery Studio und einem verknüpften Abrechnungskonto.
- Wählen Sie in der Google Cloud Console auf der Seite der Projektauswahl ein Google Cloud-Projekt aus oder erstellen Sie eines.
- Die Abrechnung für das Google Cloud-Projekt muss aktiviert sein. So prüfen Sie, ob die Abrechnung für ein Projekt aktiviert ist
- Folgen Sie der Anleitung unter BigQuery Studio für die Asset-Verwaltung aktivieren.
BigQuery Studio vorbereiten
Erstellen Sie ein leeres Notebook und verbinden Sie es mit einer Laufzeit.
- Rufen Sie in der Google Cloud Console BigQuery Studio auf.
- Klicken Sie auf das Dreipunkt-Menü ▼ neben der Schaltfläche +.
- Wählen Sie Python-Notebook aus.
- Schließen Sie die Vorlagenauswahl.
- Wählen Sie + Code aus, um eine neue Codezelle zu erstellen.
- Installieren Sie die neueste Version des BigQuery DataFrames-Pakets über die Codezelle.Geben Sie dazu den folgenden Befehl ein.
%pip install --upgrade bigframes --quiet
3. Öffentliches Dataset lesen
Initialisieren Sie das BigQuery DataFrames-Paket, indem Sie Folgendes in einer neuen Codezelle ausführen:
import bigframes.pandas as bpd
bpd.options.bigquery.ordering_mode = "partial"
bpd.options.display.repr_mode = "deferred"
Hinweis: In dieser Anleitung verwenden wir den experimentellen „Teilsortierungsmodus“, der effizientere Abfragen ermöglicht, wenn er mit einem Pandas-ähnlichen Filter verwendet wird. Einige Pandas-Funktionen, für die eine strenge Sortierung oder ein Index erforderlich ist, funktionieren möglicherweise nicht.
Prüfe die bigframes-Paketversion mit
bpd.__version__
Für diese Anleitung ist Version 1.27.0 oder höher erforderlich.
Einzelhandelsumsätze mit Spirituosen in Iowa
Der Dataset zu den Einzelhandelsumsätzen mit Spirituosen in Iowa wird in BigQuery über das Programm für öffentliche Datasets in Google Cloud bereitgestellt. Dieser Datensatz enthält alle Großhandelskäufe von Spirituosen im US-Bundesstaat Iowa durch Einzelhändler zum Verkauf an Einzelpersonen seit dem 1. Januar 2012. Die Daten werden von der Abteilung für alkoholische Getränke des Handelsministeriums von Iowa erhoben.
Stellen Sie in BigQuery eine Abfrage für bigquery-public-data.iowa_liquor_sales.sales, um den Einzelhandelsumsatz für Spirituosen in Iowa zu analysieren. Mit der Methode bigframes.pandas.read_gbq() können Sie einen DataFrame aus einem Abfragestring oder einer Tabellen-ID erstellen.
Führen Sie den folgenden Code in einer neuen Codezelle aus, um einen DataFrame mit dem Namen „df“ zu erstellen:
df = bpd.read_gbq_table("bigquery-public-data.iowa_liquor_sales.sales")
Grundlegende Informationen zu einem DataFrame
Verwenden Sie die Methode DataFrame.peek(), um eine kleine Stichprobe der Daten herunterzuladen.
Führen Sie diese Zelle aus:
df.peek()
Erwartete Ausgabe:
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 ...
Hinweis: Für head() ist eine Sortierung erforderlich und es ist im Allgemeinen weniger effizient als peek(), wenn Sie eine Stichprobe von Daten visualisieren möchten.
Genau wie bei Pandas können Sie mit der Property DataFrame.dtypes alle verfügbaren Spalten und ihre zugehörigen Datentypen aufrufen. Sie werden in einer pandas-kompatiblen Weise bereitgestellt.
Führen Sie diese Zelle aus:
df.dtypes
Erwartete Ausgabe:
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
Mit der Methode DataFrame.describe() werden einige grundlegende Statistiken aus dem DataFrame abgefragt. Führen Sie DataFrame.to_pandas() aus, um diese Zusammenfassungsstatistiken als Pandas DataFrame herunterzuladen.
Führen Sie diese Zelle aus:
df.describe("all").to_pandas()
Erwartete Ausgabe:
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. Daten visualisieren und bereinigen
Das Dataset „Iowa Liquor Retail Sales“ (Einzelhandelsverkauf von Spirituosen in Iowa) enthält detaillierte geografische Informationen, einschließlich des Standorts der Einzelhändler. Anhand dieser Daten können Sie Trends und Unterschiede zwischen geografischen Gebieten ermitteln.
Umsätze nach Postleitzahl visualisieren
Es gibt mehrere vordefinierte Visualisierungsmethoden wie DataFrame.plot.hist(). Mit dieser Methode können Sie den Alkoholverkauf nach Postleitzahl vergleichen.
volume_by_zip = df.groupby("zip_code").agg({"volume_sold_liters": "sum"})
volume_by_zip.plot.hist(bins=20)
Erwartete Ausgabe:
Mit einem Balkendiagramm können Sie sehen, in welchen Postleitzahlenbereichen der meiste Alkohol verkauft wurde.
(
volume_by_zip
.sort_values("volume_sold_liters", ascending=False)
.head(25)
.to_pandas()
.plot.bar(rot=80)
)
Erwartete Ausgabe:
Daten bereinigen
Einige Postleitzahlen enden auf .0. Möglicherweise wurden die Postleitzahlen irgendwo in der Datenerhebung versehentlich in Gleitkommawerte umgewandelt. Verwenden Sie reguläre Ausdrücke, um die Postleitzahlen zu bereinigen, und wiederholen Sie die Analyse.
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)
)
Erwartete Ausgabe:
5. Korrelationen bei Verkäufen ermitteln
Warum werden in einigen Postleitzahlen mehr Produkte verkauft als in anderen? Eine Hypothese besagt, dass es an Unterschieden bei der Bevölkerungsgröße liegt. In einer Postleitzahl mit mehr Einwohnern wird wahrscheinlich mehr Alkohol verkauft.
Testen Sie diese Hypothese, indem Sie die Korrelation zwischen Bevölkerung und Alkoholumsatz berechnen.
Zusammenführen mit anderen Datasets
Führen Sie eine Zusammenführung mit einem Bevölkerungs-Dataset wie der Postleitzahlen-Tabellenfläche-Umfrage der American Community Survey des US Census Bureau durch.
census_acs = bpd.read_gbq_table("bigquery-public-data.census_bureau_acs.zcta_2020_5yr")
Im American Community Survey werden Bundesstaaten anhand des GEOID identifiziert. Bei Tabulationen nach Postleitzahl entspricht das GEOID der Postleitzahl.
volume_by_pop = volume_by_zip.join(
census_acs.set_index("geo_id")
)
Erstellen Sie ein Streudiagramm, um die Bevölkerungszahlen der Zählungsgebiete nach Postleitzahl mit den verkauften Litern Alkohol zu vergleichen.
(
volume_by_pop[["volume_sold_liters", "total_pop"]]
.to_pandas()
.plot.scatter(x="total_pop", y="volume_sold_liters")
)
Erwartete Ausgabe:
Korrelationen berechnen
Der Trend ist ungefähr linear. Passen Sie ein lineares Regressionsmodell an, um zu prüfen, wie gut die Bevölkerung den Alkoholverkauf vorhersagen kann.
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)
Mit der Methode score können Sie die Passform prüfen.
model.score(feature_columns, label_columns).to_pandas()
Beispielausgabe:
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
Zeichnen Sie die beste Anpassungslinie, indem Sie die Funktion predict auf einen Bereich von Populationswerten anwenden.
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",
)
Erwartete Ausgabe:
Heteroskedastizität beheben
Die Daten im vorherigen Diagramm sind offenbar heteroskedastisch. Die Abweichung von der Ausgleichsgerade nimmt mit der Grundgesamtheit zu.
Vielleicht ist die Menge des gekauften Alkohols pro Person relativ konstant.
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")
)
Erwartete Ausgabe:
Es gibt zwei Möglichkeiten, den durchschnittlichen Literverbrauch an Alkohol zu berechnen:
- Wie viel Alkohol wird durchschnittlich pro Person in Iowa gekauft?
- Wie hoch ist der durchschnittliche Alkoholverbrauch pro Person in allen Postleitzahlen?
In (1) spiegelt es wider, wie viel Alkohol im gesamten Bundesstaat gekauft wird. In (2) entspricht sie der durchschnittlichen Postleitzahl, die nicht unbedingt mit (1) übereinstimmt, da verschiedene Postleitzahlen unterschiedliche Bevölkerungszahlen haben.
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
Erwartete Ausgabe: 87.997
average_per_zip = volume_per_pop["liters_per_pop"].mean()
average_per_zip
Erwartete Ausgabe: 67.139
Plotten Sie diese Durchschnittswerte ähnlich wie oben.
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")
Erwartete Ausgabe:
Es gibt aber immer noch einige Postleitzahlen mit sehr großen Abweichungen, insbesondere in Gebieten mit weniger Einwohnern. Es bleibt Ihnen überlassen, eine Hypothese dazu aufzustellen. Es könnte beispielsweise sein, dass in einigen Postleitzahlengebieten nur wenige Menschen leben, aber der Alkoholkonsum hoch ist, weil sich dort der einzige Spirituosenladen in der Region befindet. In diesem Fall können diese Ausreißer durch eine Berechnung auf Grundlage der Bevölkerungszahl der umliegenden Postleitzahlen ausgeglichen werden.
6. Vergleich der verkauften Spirituosenarten
Neben geografischen Daten enthält die Datenbank für den Einzelhandel mit Spirituosen in Iowa auch detaillierte Informationen zum verkauften Artikel. Vielleicht können wir durch die Analyse dieser Daten Unterschiede in den Geschmäckern in verschiedenen Regionen feststellen.
Kategorien entdecken
Artikel werden in der Datenbank kategorisiert. Wie viele Kategorien gibt es?
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()
Erwartete Ausgabe: 103
Welche Kategorien sind nach Volumen am beliebtesten?
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)
Mit dem ARRAY-Datentyp arbeiten
Es gibt mehrere Kategorien für Whisky, Rum, Wodka und mehr. Ich möchte diese irgendwie gruppieren.
Trennen Sie zuerst die Kategorienamen mithilfe der Methode Series.str.split() in einzelne Wörter. Entfernen Sie mit der Methode explode() die Verschachtelung des Arrays.
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()
Erwartete Ausgabe: 113
Im Diagramm oben sind VODKA und VODKAS weiterhin getrennt. Es ist mehr Gruppierung erforderlich, um Kategorien in einen kleineren Satz zusammenzufassen.
7. NLTK mit BigQuery DataFrames verwenden
Bei nur etwa 100 Kategorien wäre es möglich, einige Heuristiken zu formulieren oder sogar manuell eine Zuordnung von der Kategorie zum breiteren Alkoholtyp zu erstellen. Alternativ kann auch ein Large Language Model wie Gemini verwendet werden, um eine solche Zuordnung zu erstellen. Im Codelab Mit BigQuery DataFrames Informationen aus unstrukturierten Daten gewinnen erfahren Sie, wie Sie BigQuery DataFrames mit Gemini verwenden.
Verwenden Sie stattdessen ein traditionelleres Natural Language Processing-Paket, NLTK, um diese Daten zu verarbeiten. Mit einer Technologie namens „Stemmer“ können beispielsweise Plural- und Singularformen von Substantiven zu einem einzigen Wert zusammengeführt werden.
NLTK zum Stammen von Wörtern verwenden
Das NLTK-Paket bietet Methoden zur Verarbeitung natürlicher Sprache, auf die über Python zugegriffen werden kann. Installieren Sie das Paket, um es auszuprobieren.
%pip install nltk
Importieren Sie als Nächstes das Paket. Prüfen Sie die Version. Sie wird später in der Anleitung verwendet.
import nltk
nltk.__version__
Eine Möglichkeit, Wörter zu standardisieren, um den Stamm des Wortes zu erhalten. Dadurch werden alle Suffixe entfernt, z. B. das abschließende „s“ für den Plural.
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)
Probieren Sie das an einigen Wörtern aus.
stem("WHISKEY")
Erwartete Ausgabe: whiskey
stem("WHISKIES")
Erwartete Ausgabe: whiski
Leider wurden Whiskys nicht als Whisky zugeordnet. Stammformen funktionieren nicht gut mit unregelmäßigen Pluralformen. Verwenden Sie einen Lemmatisierer, der mithilfe ausgefeilterer Verfahren das Grundwort, das sogenannte Lemma, ermittelt.
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())
Probieren Sie das an einigen Wörtern aus.
lemmatize("WHISKIES")
Erwartete Ausgabe: whisky
lemmatize("WHISKY")
Erwartete Ausgabe: whisky
lemmatize("WHISKEY")
Erwartete Ausgabe: whiskey
Leider ordnet dieser Lemmatisierer „Whiskey“ nicht demselben Lemma wie „Whiskies“ zu. Da dieses Wort für die Datenbank für den Einzelhandelsverkauf von Spirituosen in Iowa besonders wichtig ist, ordnen Sie es mithilfe eines Wörterbuchs manuell der amerikanischen Schreibweise zu.
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)
Probieren Sie das an einigen Wörtern aus.
lemmatize("WHISKIES")
Erwartete Ausgabe: whiskey
lemmatize("WHISKEY")
Erwartete Ausgabe: whiskey
Glückwunsch! Dieser Lemmatizer sollte gut geeignet sein, um die Kategorien einzugrenzen. Wenn Sie es mit BigQuery verwenden möchten, müssen Sie es in der Cloud bereitstellen.
Projekt für die Funktionsbereitstellung einrichten
Bevor Sie die Funktion in der Cloud bereitstellen, damit BigQuery darauf zugreifen kann, müssen Sie eine einmalige Einrichtung vornehmen.
Erstellen Sie eine neue Codezelle und ersetzen Sie your-project-id durch die Google Cloud-Projekt-ID, die Sie für diese Anleitung verwenden.
project_id = "your-project-id"
Erstellen Sie ein Dienstkonto ohne Berechtigungen, da diese Funktion keinen Zugriff auf Cloud-Ressourcen benötigt.
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)
Erwartete Ausgabe: bigframes-no-permissions@your-project-id.iam.gserviceaccount.com
Erstellen Sie ein BigQuery-Dataset, das die Funktion enthält.
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)
Remote-Funktion bereitstellen
Aktivieren Sie die Cloud Functions API, falls noch nicht geschehen.
!gcloud services enable cloudfunctions.googleapis.com
Stellen Sie die Funktion jetzt in dem gerade erstellten Datenpool bereit. Fügen Sie der Funktion, die Sie in den vorherigen Schritten erstellt haben, einen @bpd.remote_function-Decorator hinzu.
@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)
Die Bereitstellung sollte etwa zwei Minuten dauern.
Remote-Funktionen verwenden
Sobald die Bereitstellung abgeschlossen ist, können Sie diese Funktion testen.
lemmatize = bpd.read_gbq_function(f"{project_id}.functions.lemmatize")
words = bpd.Series(["whiskies", "whisky", "whiskey", "vodkas", "vodka"])
words.apply(lemmatize).to_pandas()
Erwartete Ausgabe:
0 whiskey
1 whiskey
2 whiskey
3 vodka
4 vodka
dtype: string
8. Alkoholkonsum nach Landkreis vergleichen
Mit der Funktion lemmatize können Sie jetzt Kategorien kombinieren.
Das Wort finden, das die Kategorie am besten zusammenfasst
Erstellen Sie zuerst einen DataFrame mit allen Kategorien in der Datenbank.
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()
Erwartete Ausgabe:
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
Erstellen Sie als Nächstes einen DataFrame mit allen Wörtern in den Kategorien, mit Ausnahme einiger Füllwörter wie Satzzeichen und „Artikel“.
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()
Erwartete Ausgabe:
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
Wenn Sie nach der Gruppierung lematisieren, reduzieren Sie die Auslastung Ihrer Cloud-Funktion. Es ist möglich, die Funktion „lemmatize“ auf jede der mehreren Millionen Zeilen in der Datenbank anzuwenden. Das würde jedoch mehr kosten als die Anwendung nach der Gruppierung und möglicherweise eine Kontingenterhöhung erfordern.
lemmas = words.assign(lemma=lambda _: _["word"].apply(lemmatize))
lemmas.to_pandas()
Erwartete Ausgabe:
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
Nachdem die Wörter lemmatisiert wurden, müssen Sie das Lemma auswählen, das die Kategorie am besten zusammenfasst. Da es in den Kategorien nicht viele Funktionswörter gibt, gilt die Heuristik, dass ein Wort, das in mehreren anderen Kategorien vorkommt, wahrscheinlich besser als zusammenfassendes Wort geeignet ist (z.B. Whisky).
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()
Erwartete Ausgabe:
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
Da es jetzt für jede Kategorie ein einzelnes Lemma gibt, das sie zusammenfasst, können Sie es mit dem ursprünglichen DataFrame zusammenführen.
df_with_lemma = df.merge(
categories_mapping,
on="category_name",
how="left"
)
df_with_lemma[df_with_lemma['category_name'].notnull()].peek()
Erwartete Ausgabe:
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
Landkreise vergleichen
Vergleichen Sie die Umsätze in den einzelnen Landkreisen, um Unterschiede zu erkennen.
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"))
)
Das meistverkaufte Produkt (Lemma) in jedem Landkreis ermitteln
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()
Erwartete Ausgabe:
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
Wie unterscheiden sich die Landkreise voneinander?
county_max_lemma.groupby("lemma").size().to_pandas()
Erwartete Ausgabe:
lemma
american 1
liqueur 1
vodka 15
whiskey 83
dtype: Int64
In den meisten Landkreisen ist Whiskey das beliebteste Produkt nach Volumen, in 15 Landkreisen ist es Wodka. Vergleichen Sie dies mit den beliebtesten Spirituosenarten im Bundesstaat.
total_liters = (
df_with_lemma
.groupby("lemma")
.agg({"volume_sold_liters": "sum"})
.sort_values("volume_sold_liters", ascending=False)
)
total_liters.to_pandas()
Erwartete Ausgabe:
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
...
Whiskey und Wodka haben fast das gleiche Volumen, wobei Wodka landesweit etwas höher liegt als Whiskey.
Proportionen vergleichen
Was ist bei den Verkäufen in den einzelnen Landkreisen besonders? Worin unterscheidet sich der Landkreis vom Rest des Bundesstaats?
Mit dem Cohen's h-Maß können Sie ermitteln, bei welchen Spirituosenverkäufen sich das Volumen am stärksten von dem unterscheidet, was auf Grundlage des Anteils der Verkäufe im Bundesstaat zu erwarten wäre.
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"))
)
Nachdem der Cohen's h-Wert für jedes Lemma gemessen wurde, ermitteln Sie in jedem County die größte Abweichung vom landesweiten Anteil.
# 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()
Erwartete Ausgabe:
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
Je höher der Wert von Cohen's h ist, desto wahrscheinlicher ist es, dass es im Vergleich zum landesweiten Durchschnitt einen statistisch signifikanten Unterschied bei der Menge dieser Alkoholsorte gibt. Bei den kleineren positiven Werten weicht der Unterschied bei der Nutzung vom landesweiten Durchschnitt ab. Dies kann jedoch auf zufällige Unterschiede zurückzuführen sein.
Nebenbei: EL PASO County ist offenbar kein County in Iowa. Möglicherweise müssen die Daten noch einmal bereinigt werden, bevor Sie sich voll und ganz auf diese Ergebnisse verlassen können.
Landkreise visualisieren
Führen Sie eine Verbindung mit der Tabelle bigquery-public-data.geo_us_boundaries.counties her, um das geografische Gebiet für jeden Landkreis zu erhalten. Landkreisnamen sind in den USA nicht eindeutig. Filtern Sie daher so, dass nur Landkreise aus Iowa berücksichtigt werden. Der FIPS-Code für Iowa lautet „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
Erwartete Ausgabe:
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
Mit GeoPandas können Sie diese Unterschiede auf einer Karte visualisieren.
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. Bereinigen
Wenn Sie für diese Anleitung ein neues Google Cloud-Projekt erstellt haben, können Sie es löschen, um zusätzliche Kosten für erstellte Tabellen oder andere Ressourcen zu vermeiden.
Alternativ können Sie die für diese Anleitung erstellten Cloud Functions, Dienstkonten und Datensätze löschen.
10. Glückwunsch!
Sie haben strukturierte Daten mit BigQuery DataFrames bereinigt und analysiert. Dabei haben Sie die öffentlichen Datasets von Google Cloud, Python-Notebooks in BigQuery Studio, BigQuery ML, BigQuery-Remote-Funktionen und die Vorteile von BigQuery DataFrames kennengelernt. Fantastisch!
Nächste Schritte
- Sie können diese Schritte auch auf andere Daten anwenden, z. B. auf die Datenbank mit US-amerikanischen Namen.
- Versuchen Sie, Python-Code in Ihrem Notebook zu generieren. Python-Notebooks in BigQuery Studio basieren auf Colab Enterprise. Hinweis: Ich finde es sehr hilfreich, um Hilfe beim Erstellen von Testdaten zu bitten.
- Beispiel-Notebooks für BigQuery DataFrames auf GitHub
- Erstellen Sie einen Zeitplan zum Ausführen eines Notebooks in BigQuery Studio.
- Sie können eine Remote-Funktion mit BigQuery DataFrames bereitstellen, um Python-Pakete von Drittanbietern in BigQuery zu integrieren.