Analisi esplorativa dei dati sulle vendite di liquori in Iowa utilizzando il pacchetto BigQuery DataFrames

1. Panoramica

In questo lab utilizzerai BigQuery DataFrames da un notebook Python in BigQuery Studio per pulire e analizzare il set di dati pubblico sulle vendite di liquori in Iowa. Utilizza le funzionalità di BigQuery ML e delle funzioni remote per scoprire approfondimenti.

Creerai un notebook Python per confrontare le vendite nelle varie aree geografiche. Questo approccio può essere adattato per funzionare su qualsiasi dato strutturato.

Obiettivi

In questo lab imparerai a:

  • Attivare e utilizzare i notebook Python in BigQuery Studio
  • Connettiti a BigQuery utilizzando il pacchetto BigQuery DataFrames
  • Creare una regressione lineare utilizzando BigQuery ML
  • Esegui aggregazioni e unioni complesse utilizzando una sintassi simile a pandas

2. Requisiti

  • Un browser, ad esempio Chrome o Firefox
  • Un progetto Google Cloud con la fatturazione abilitata

Prima di iniziare

Per seguire le istruzioni di questo codelab, devi disporre di un progetto Google Cloud con BigQuery Studio abilitato e di un account di fatturazione collegato.

  1. Nella console Google Cloud, nella pagina di selezione del progetto, seleziona o crea un progetto Google Cloud
  2. Verifica che la fatturazione sia attivata per il tuo progetto Google Cloud. Scopri come verificare se la fatturazione è abilitata in un progetto
  3. Segui le istruzioni per abilitare BigQuery Studio per la gestione degli asset.

Prepara BigQuery Studio

Crea un blocco note vuoto e connettilo a un runtime.

  1. Vai a BigQuery Studio nella console Google Cloud.
  2. Fai clic sul pulsante accanto al pulsante +.
  3. Seleziona Blocco note Python.
  4. Chiudi il selettore dei modelli.
  5. Seleziona + Codice per creare una nuova cella di codice.
  6. Installa la versione più recente del pacchetto BigQuery DataFrames dalla cella di codice.Digita il seguente comando.
    %pip install --upgrade bigframes --quiet
    
    Fai clic sul pulsante Esegui cella o premi Maiusc + Invio per eseguire la cella di codice.

3. Leggere un set di dati pubblico

Inizializza il pacchetto BigQuery DataFrames eseguendo il seguente codice in una nuova cella di codice:

import bigframes.pandas as bpd

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

Nota: in questo tutorial utilizziamo la "modalità di ordinamento parziale" sperimentale, che consente query più efficienti se utilizzata con il filtro simile a pandas. Alcune funzionalità di pandas che richiedono un ordinamento o un indice rigoroso potrebbero non funzionare.

Controlla la versione del pacchetto bigframes con

bpd.__version__

Questo tutorial richiede la versione 1.27.0 o successive.

Vendite al dettaglio di alcolici in Iowa

Il set di dati sulle vendite al dettaglio di liquori dell'Iowa è fornito su BigQuery tramite il programma per i set di dati pubblici di Google Cloud. Questo set di dati contiene tutti gli acquisti all'ingrosso di alcolici nello stato dell'Iowa da parte di rivenditori per la vendita a privati dal 1° gennaio 2012. I dati vengono raccolti dalla divisione bevande alcoliche del Dipartimento del Commercio dell'Iowa.

In BigQuery, esegui una query su bigquery-public-data.iowa_liquor_sales.sales per analizzare le vendite al dettaglio di liquori in Iowa. Utilizza il metodo bigframes.pandas.read_gbq() per creare un DataFrame da una stringa di query o dall'ID tabella.

Esegui il seguente codice in una nuova cella di codice per creare un DataFrame denominato "df":

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

Scopri informazioni di base su un DataFrame

Utilizza il metodo DataFrame.peek() per scaricare un piccolo campione di dati.

Esegui questa cella:

df.peek()

Output previsto:

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

Nota: head() richiede l'ordinamento ed è in genere meno efficiente di peek() se vuoi visualizzare un campione di dati.

Come per pandas, utilizza la proprietà DataFrame.dtypes per visualizzare tutte le colonne disponibili e i relativi tipi di dati corrispondenti. Questi sono esposti in modo compatibile con pandas.

Esegui questa cella:

df.dtypes

Output previsto:

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

Il metodo DataFrame.describe() esegue query su alcune statistiche di base del DataFrame. Esegui DataFrame.to_pandas() per scaricare queste statistiche di riepilogo come DataFrame Pandas.

Esegui questa cella:

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

Output previsto:

	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. Visualizza e pulisci i dati

Il set di dati sulle vendite al dettaglio di alcolici nell'Iowa fornisce informazioni geografiche dettagliate, tra cui la posizione dei negozi di vendita al dettaglio. Utilizza questi dati per identificare tendenze e differenze nelle aree geografiche.

Visualizzare le vendite per codice postale

Esistono diversi metodi di visualizzazione integrati, come DataFrame.plot.hist(). Utilizza questo metodo per confrontare le vendite di alcolici per codice postale.

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

Output previsto:

Istogramma dei volumi

Utilizza un grafico a barre per vedere quali codici postali hanno venduto più alcolici.

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

Output previsto:

Grafico a barre dei volumi di alcolici nei codici postali con le vendite più elevate

pulisci i dati

Alcuni codici postali terminano con .0. È possibile che durante la raccolta dei dati i codici postali siano stati convertiti accidentalmente in valori in virgola mobile. Utilizza le espressioni regolari per ripulire i codici postali e ripetere l'analisi.

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 previsto:

Grafico a barre dei volumi di alcolici nei codici postali con le vendite più elevate

5. Scoprire le correlazioni nelle vendite

Perché alcuni codici postali vendono più di altri? Un'ipotesi è che sia dovuto alle differenze di dimensioni della popolazione. Un codice postale con una popolazione più numerosa probabilmente venderà più liquori.

Verifica questa ipotesi calcolando la correlazione tra popolazione e volume di vendite di liquori.

Eseguire l'unione con altri set di dati

Esegui l'unione con un set di dati sulla popolazione, ad esempio il sondaggio sulle aree di tabulazione dei codici postali dell'American Community Survey dell'U.S. Census Bureau.

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

L'American Community Survey identifica gli stati tramite GEOID. Nel caso delle aree di tabulazione dei codici postali, GEOID corrisponde al codice postale.

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

Crea un grafico a dispersione per confrontare le popolazioni delle aree di tabulazione dei codici postali con i litri di alcolici venduti.

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

Output previsto:

Grafico a dispersione delle aree di tabulazione dei codici postali in base alla popolazione e ai litri di liquori venduti

Calcolare le correlazioni

La tendenza sembra approssimativamente lineare. Adatta un modello di regressione lineare per verificare l'efficacia della popolazione nel prevedere le vendite di liquori.

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)

Controlla l'accuratezza della definizione utilizzando il metodo score.

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

Esempio di 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

Disegna la linea di migliore approssimazione, ma chiama la funzione predict su un intervallo di valori della popolazione.

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 previsto:

Grafico a dispersione con una linea di migliore adattamento

Risolvere i problemi di eteroschedasticità

I dati nel grafico precedente sembrano essere eteroschedastici. La varianza intorno alla linea di miglior adattamento aumenta con la popolazione.

Forse la quantità di alcolici acquistata per persona è relativamente costante.

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 previsto:

Grafico a dispersione dei litri per popolazione

Calcola i litri medi di alcolici acquistati in due modi diversi:

  1. Qual è la quantità media di alcolici acquistati per persona in Iowa?
  2. Qual è la media per tutti i codici postali della quantità di alcolici acquistati per persona.

In (1), riflette la quantità di alcolici acquistati nell'intero stato. In (2), riflette il codice postale medio, che non sarà necessariamente uguale a (1) perché i codici postali diversi hanno popolazioni diverse.

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 previsto: 87.997

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

Output previsto: 67.139

Grafica queste medie, come indicato sopra.

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 previsto:

Grafico a dispersione dei litri per popolazione

Esistono ancora alcuni codici postali che rappresentano valori anomali piuttosto elevati, in particolare nelle aree con una popolazione meno numerosa. Spetta a te ipotizzare il motivo. Ad esempio, potrebbe essere che alcuni codici postali abbiano una popolazione ridotta, ma un consumo elevato perché contengono l'unico negozio di liquori della zona. In questo caso, il calcolo in base alla popolazione dei codici postali circostanti potrebbe anche eliminare questi valori anomali.

6. Confronto tra i tipi di alcolici venduti

Oltre ai dati geografici, il database delle vendite al dettaglio di liquori dell'Iowa contiene anche informazioni dettagliate sull'articolo venduto. Forse analizzandoli, possiamo scoprire le differenze nei gusti nelle varie aree geografiche.

Consultare le categorie

Gli elementi sono classificati nel database. Quante categorie sono disponibili?

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 previsto: 103

Quali sono le categorie più apprezzate in base al 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)

Grafico a barre delle principali categorie di liquori venduti

Utilizzo del tipo di dati ARRAY

Esistono diverse categorie di whisky, rum, vodka e altro ancora. Vorrei raggrupparli in qualche modo.

Inizia dividendo i nomi delle categorie in parole separate utilizzando il metodo Series.str.split(). Estrai l'array creato utilizzando il metodo 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)

Parole per conteggio dalle categorie

category_parts.nunique()

Output previsto: 113

Se guardi il grafico qui sopra, i dati mostrano ancora VODKA separato da VODKAS. È necessario un maggiore raggruppamento per comprimere le categorie in un insieme più piccolo.

7. Utilizzo di NLTK con BigQuery DataFrames

Con solo circa 100 categorie, sarebbe possibile scrivere alcune regole di euristica o addirittura creare manualmente una mappatura dalla categoria al tipo di liquore più ampio. In alternativa, è possibile utilizzare un modello linguistico di grandi dimensioni come Gemini per creare una mappatura di questo tipo. Prova il codelab Ottieni approfondimenti dai dati non strutturati utilizzando BigQuery DataFrames per utilizzare BigQuery DataFrames con Gemini.

Utilizza invece un pacchetto di elaborazione del linguaggio naturale più tradizionale, NLTK, per elaborare questi dati. La tecnologia chiamata "stemmer" può unire, ad esempio, i sostantivi plurali e singolari nello stesso valore.

Utilizzo di NLTK per l'analisi morfologica delle parole

Il pacchetto NLTK fornisce metodi di elaborazione del linguaggio naturale accessibili da Python. Installa il pacchetto per provarlo.

%pip install nltk

Poi importa il pacchetto. Controlla la versione. Verrà utilizzato più avanti nel tutorial.

import nltk

nltk.__version__

Un modo per standardizzare le parole per "estrarre" la radice della parola. In questo modo vengono rimossi eventuali suffissi, come la "s" finale per i plurali.

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)

Prova a farlo con alcune parole.

stem("WHISKEY")

Output previsto: whiskey

stem("WHISKIES")

Output previsto: whiski

Purtroppo, i whisky non sono stati associati allo stesso modo. Gli stemmer non funzionano bene con i plurali irregolari. Prova un programma di lemmatizzazione, che utilizza tecniche più sofisticate per identificare la parola base, chiamata "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())

Prova a farlo con alcune parole.

lemmatize("WHISKIES")

Output previsto: whisky

lemmatize("WHISKY")

Output previsto: whisky

lemmatize("WHISKEY")

Output previsto: whiskey

Purtroppo, questo lemmatizzatore non mappa "whisky" allo stesso lemma di "whiskies". Poiché questa parola è particolarmente importante per il database delle vendite al dettaglio di alcolici dell'Iowa, mappala manualmente all'ortografia americana utilizzando un dizionario.

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)

Prova a farlo con alcune parole.

lemmatize("WHISKIES")

Output previsto: whiskey

lemmatize("WHISKEY")

Output previsto: whiskey

Complimenti! Questo lemmatizzatore dovrebbe essere adatto per restringere le categorie. Per utilizzarlo con BigQuery, devi eseguirlo nel cloud.

Configura il progetto per il deployment delle funzioni

Prima di eseguire il deployment nel cloud in modo che BigQuery possa accedere a questa funzione, devi eseguire una configurazione una tantum.

Crea una nuova cella di codice e sostituisci your-project-id con l'ID progetto Google Cloud che utilizzi per questo tutorial.

project_id = "your-project-id"

Crea un account di servizio senza autorizzazioni, poiché questa funzione non ha bisogno di accedere a risorse cloud.

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 previsto: bigframes-no-permissions@your-project-id.iam.gserviceaccount.com

Crea un set di dati BigQuery per contenere la funzione.

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)

Eseguire il deployment di una funzione remota

Abilita l'API Cloud Functions, se non è ancora abilitata.

!gcloud services enable cloudfunctions.googleapis.com

Ora esegui il deployment della funzione nel set di dati che hai appena creato. Aggiungi un decoratore @bpd.remote_function alla funzione creata nei passaggi precedenti.

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

Il deployment dovrebbe richiedere circa due minuti.

Utilizzare le funzioni del telecomando

Al termine del deployment, puoi testare questa funzione.

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

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

Output previsto:

0	whiskey
1	whiskey
2	whiskey
3	vodka
4	vodka

dtype: string

8. Confronto del consumo di alcolici per contea

Ora che la funzione lemmatize è disponibile, utilizzala per combinare le categorie.

Trovare la parola che riassume al meglio la categoria

Innanzitutto, crea un DataFrame di tutte le categorie nel 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 previsto:

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

A questo punto, crea un DataFrame di tutte le parole nelle categorie, ad eccezione di alcune parole riempitive come la punteggiatura e "articolo".

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 previsto:

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

Tieni presente che, eseguendo la lemmatizzazione dopo il raggruppamento, riduci il carico sulla funzione Cloud. È possibile applicare la funzione lemmatize a ciascuna delle diverse milioni di righe del database, ma il costo sarebbe superiore a quello dell'applicazione dopo il raggruppamento e potrebbe richiedere aumenti della quota.

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

Output previsto:

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

Ora che le parole sono state lemmatizzate, devi selezionare il lemma che riassume meglio la categoria. Poiché le categorie non contengono molte parole funzionali, utilizza l'euristica che se una parola compare in più altre categorie, è probabile che sia migliore come parola di sintesi (ad es. 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()

Output previsto:

	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

Ora che esiste un singolo lemma che riassume ogni categoria, uniscilo al DataFrame originale.

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

Output previsto:

	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

Confronto tra contee

Confronta le vendite in ogni contea per vedere quali sono le differenze.

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

Trova il prodotto (lemma) più venduto in ogni contea.

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 previsto:

	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

Quanto sono diverse le contee?

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

Output previsto:

lemma	
american	1
liqueur	1
vodka	15
whiskey	83

dtype: Int64

Nella maggior parte dei paesi, il whisky è il prodotto più apprezzato in termini di volume, mentre la vodka è la più popolare in 15 paesi. Confronta questi dati con i tipi di liquori più apprezzati a livello statale.

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

Output previsto:

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

Il volume di whisky e vodka è quasi lo stesso, con la vodka leggermente superiore al whisky a livello statale.

Confrontare le proporzioni

Quali sono le caratteristiche uniche delle vendite in ogni contea? Cosa rende la contea diversa dal resto dello stato?

Utilizza l'indice di Cohen per trovare i volumi di vendita di liquori che differiscono maggiormente proporzionalmente rispetto a quanto previsto in base alla proporzione delle vendite a livello statale.

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

Ora che il coefficiente di Cohen è stato misurato per ogni lemma, trova la differenza maggiore rispetto alla proporzione a livello statale in ogni contea.

# 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 previsto:

	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

Maggiore è il valore di h di Cohen, maggiore è la probabilità che esista una differenza statisticamente significativa nella quantità di quel tipo di alcolici consumati rispetto alle medie statali. Per i valori positivi più piccoli, la differenza di consumo è diversa dalla media a livello statale, ma potrebbe essere dovuta a differenze casuali.

A parte: la contea di EL PASO non sembra essere una contea dell'Iowa, il che potrebbe indicare un'altra necessità di pulizia dei dati prima di fare affidamento completo su questi risultati.

Visualizzazione delle contee

Esegui l'unione con la tabella bigquery-public-data.geo_us_boundaries.counties per ottenere l'area geografica di ogni contea. I nomi delle contee non sono univoci negli Stati Uniti, quindi applica un filtro per includere solo le contee dell'Iowa. Il codice FIPS per l'Iowa è "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 previsto:

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

Utilizza GeoPandas per visualizzare queste differenze su una mappa.

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

Una mappa dell&#39;alcol più diverso dalle proporzioni del volume di vendita a livello statale in ogni contea

9. Esegui la pulizia

Se hai creato un nuovo progetto Google Cloud per questo tutorial, puoi eliminarlo per evitare addebiti aggiuntivi per le tabelle o altre risorse create.

In alternativa, elimina le Funzioni Cloud, gli account di servizio e i set di dati creati per questo tutorial.

10. Complimenti!

Hai pulito e analizzato i dati strutturati utilizzando BigQuery DataFrames. Lungo il percorso, hai esplorato i set di dati pubblici di Google Cloud, i notebook Python in BigQuery Studio, BigQuery ML, le funzioni remote di BigQuery e la potenza di BigQuery DataFrames. Ottimo lavoro.

Passaggi successivi