Explorative Datenanalyse der Spirituosenverkäufe in Iowa mit dem BigQuery DataFrames-Paket

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

  • Ein Browser, z. B. Chrome oder Firefox
  • Google Cloud-Projekt mit aktivierter Abrechnung

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.

  1. Wählen Sie in der Google Cloud Console auf der Seite der Projektauswahl ein Google Cloud-Projekt aus oder erstellen Sie eines.
  2. Die Abrechnung für das Google Cloud-Projekt muss aktiviert sein. So prüfen Sie, ob die Abrechnung für ein Projekt aktiviert ist
  3. 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.

  1. Rufen Sie in der Google Cloud Console BigQuery Studio auf.
  2. Klicken Sie auf das Dreipunkt-Menü ▼ neben der Schaltfläche +.
  3. Wählen Sie Python-Notebook aus.
  4. Schließen Sie die Vorlagenauswahl.
  5. Wählen Sie + Code aus, um eine neue Codezelle zu erstellen.
  6. Installieren Sie die neueste Version des BigQuery DataFrames-Pakets über die Codezelle.Geben Sie dazu den folgenden Befehl ein.
    %pip install --upgrade bigframes --quiet
    Klicken Sie auf die Schaltfläche Zelle ausführen oder drücken Sie die Umschalttaste + Eingabetaste, um die Codezelle auszuführen.

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:

Histogramm der Umsätze

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:

Balkendiagramm mit den Alkoholmengen in den Postleitzahlen mit den meisten Verkäufen

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:

Balkendiagramm mit den Alkoholmengen in den Postleitzahlen mit den meisten Verkäufen

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:

Streudiagramm der Tabulationsgebiete nach Postleitzahl nach Bevölkerung und verkauften Litern Alkohol

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:

Streudiagramm mit einer Ausgleichsgerade

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:

Streudiagramm mit Litern pro Einwohner

Es gibt zwei Möglichkeiten, den durchschnittlichen Literverbrauch an Alkohol zu berechnen:

  1. Wie viel Alkohol wird durchschnittlich pro Person in Iowa gekauft?
  2. 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:

Streudiagramm mit Litern pro Einwohner

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)

Balkendiagramm der Top-Kategorien der verkauften Spirituosen

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)

Wörter nach Anzahl aus Kategorien

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

Eine Karte mit den alkoholischen Getränken, die sich in jedem County am stärksten von den Anteilen am landesweiten Verkaufsvolumen unterscheiden

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