Analyse exploratoire des ventes de boissons alcoolisées dans l'Iowa à l'aide du package BigQuery DataFrames

1. Présentation

Dans cet atelier, vous allez utiliser BigQuery DataFrames à partir d'un notebook Python dans BigQuery Studio pour nettoyer et analyser l'ensemble de données public sur les ventes de boissons alcoolisées dans l'Iowa. Exploitez les fonctionnalités de BigQuery ML et des fonctions distantes pour découvrir des insights.

Vous allez créer un notebook Python pour comparer les ventes dans différentes zones géographiques. Vous pouvez l'adapter à n'importe quelle donnée structurée.

Objectifs

Dans cet atelier, vous allez apprendre à effectuer les tâches suivantes :

  • Activer et utiliser des notebooks Python dans BigQuery Studio
  • Se connecter à BigQuery à l'aide du package BigQuery DataFrames
  • Créer une régression linéaire à l'aide de BigQuery ML
  • Effectuer des agrégations et des jointures complexes à l'aide d'une syntaxe semblable à celle de pandas

2. Conditions requises

  • Un navigateur tel que Chrome ou Firefox
  • Un projet Google Cloud avec facturation activée

Avant de commencer

Pour suivre les instructions de cet atelier de programmation, vous aurez besoin d'un projet Google Cloud dans lequel BigQuery Studio est activé et d'un compte de facturation associé.

  1. Dans la console Google Cloud, sur la page du sélecteur de projet, sélectionnez ou créez un projet Google Cloud.
  2. Assurez-vous que la facturation est activée pour votre projet Google Cloud. Découvrez comment vérifier si la facturation est activée sur un projet.
  3. Suivez les instructions pour activer BigQuery Studio pour la gestion des éléments.

Préparer BigQuery Studio

Créez un notebook vide et associez-le à un environnement d'exécution.

  1. Accédez à BigQuery Studio dans la console Google Cloud.
  2. Cliquez sur le bouton  à côté du bouton +.
  3. Sélectionnez Notebook Python.
  4. Fermez le sélecteur de modèle.
  5. Sélectionnez + Code pour créer une cellule de code.
  6. Installez la dernière version du package BigQuery DataFrames à partir de la cellule de code.Saisissez la commande suivante.
    %pip install --upgrade bigframes --quiet
    
    Cliquez sur le bouton Run cell (Exécuter la cellule) ou appuyez sur Maj+Entrée pour exécuter la cellule de code.

3. Lire un ensemble de données public

Initialisez le package BigQuery DataFrames en exécutant le code suivant dans une nouvelle cellule de code:

import bigframes.pandas as bpd

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

Remarque: dans ce tutoriel, nous utilisons le mode d'ordonnancement partiel expérimental, qui permet d'effectuer des requêtes plus efficaces lorsqu'il est utilisé avec un filtrage semblable à celui de pandas. Certaines fonctionnalités pandas qui nécessitent un tri ou un indice stricts peuvent ne pas fonctionner.

Vérifier la version de votre package bigframes avec

bpd.__version__

Ce tutoriel nécessite la version 1.27.0 ou ultérieure.

Ventes au détail d'alcools en Iowa

L'ensemble de données sur les ventes au détail de boissons alcoolisées dans l'Iowa est disponible sur BigQuery via le programme d'ensembles de données publics de Google Cloud. Cet ensemble de données contient tous les achats en gros de boissons alcoolisées dans l'État de l'Iowa par les détaillants pour la vente à des particuliers depuis le 1er janvier 2012. Les données sont collectées par la division des boissons alcoolisées du département du commerce de l'Iowa.

Dans BigQuery, interrogez bigquery-public-data.iowa_liquor_sales.sales pour analyser les ventes au détail d'alcools dans l'Iowa. Utilisez la méthode bigframes.pandas.read_gbq() pour créer un DataFrame à partir d'une chaîne de requête ou d'un ID de table.

Exécutez le code suivant dans une nouvelle cellule de code pour créer un DataFrame nommé "df":

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

Découvrir des informations de base sur un DataFrame

Utilisez la méthode DataFrame.peek() pour télécharger un petit échantillon des données.

Exécutez cette cellule:

df.peek()

Résultat attendu :

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

Remarque: head() nécessite un tri et est généralement moins efficace que peek() si vous souhaitez visualiser un échantillon de données.

Tout comme avec pandas, utilisez la propriété DataFrame.dtypes pour afficher toutes les colonnes disponibles et les types de données correspondants. Ils sont exposés de manière compatible avec pandas.

Exécutez cette cellule:

df.dtypes

Résultat attendu :

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

La méthode DataFrame.describe() interroge certaines statistiques de base du DataFrame. Exécutez DataFrame.to_pandas() pour télécharger ces statistiques récapitulatives sous la forme d'un DataFrame pandas.

Exécutez cette cellule:

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

Résultat attendu :

	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. Visualiser et nettoyer les données

L'ensemble de données sur les ventes de boissons alcoolisées dans l'Iowa fournit des informations géographiques précises, y compris l'emplacement des magasins. Utilisez ces données pour identifier les tendances et les différences entre les zones géographiques.

Visualiser les ventes par code postal

Plusieurs méthodes de visualisation intégrées sont disponibles, comme DataFrame.plot.hist(). Utilisez cette méthode pour comparer les ventes d'alcool par code postal.

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

Résultat attendu :

Histogramme des volumes

Utilisez un graphique à barres pour identifier les codes postaux qui ont vendu le plus d'alcool.

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

Résultat attendu :

Graphique à barres représentant les volumes d&#39;alcool dans les codes postaux les plus vendus

Nettoyer les données

Certains codes postaux se terminent par un .0. Il est possible que, lors de la collecte des données, les codes postaux aient été convertis accidentellement en valeurs à virgule flottante. Utilisez des expressions régulières pour nettoyer les codes postaux et répétez l'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)
)

Résultat attendu :

Graphique à barres représentant les volumes d&#39;alcool dans les codes postaux les plus vendus

5. Découvrir des corrélations dans les ventes

Pourquoi certains codes postaux enregistrent-ils plus de ventes que d'autres ? Une hypothèse est que cela est dû à des différences de taille de population. Un code postal plus peuplé vend probablement plus d'alcool.

Testez cette hypothèse en calculant la corrélation entre la population et le volume de ventes d'alcool.

Associer des ensembles de données

Associez-les à un ensemble de données sur la population, comme l'enquête sur la communauté américaine du Bureau du recensement des États-Unis (American Community Survey) sur les zones de tabulation des codes postaux.

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

L'American Community Survey identifie les États par GEOID. Dans le cas des zones de tabulation des codes postaux, le GEOID est égal au code postal.

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

Créez un graphique en nuage de points pour comparer la population de la zone de tabulation du code postal aux litres d'alcool vendus.

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

Résultat attendu :

Nuage de points représentant les zones de tabulation des codes postaux en fonction de la population et des litres d&#39;alcool vendus

Calculer les corrélations

La tendance semble à peu près linéaire. Ajustez un modèle de régression linéaire à ces données pour vérifier dans quelle mesure la population peut prédire les ventes d'alcool.

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)

Vérifiez la qualité de l'ajustement à l'aide de la méthode score.

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

Exemple de résultat :

	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

Tracer la ligne d'ajustement, mais en appelant la fonction predict sur une plage de valeurs de population.

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

Résultat attendu :

Nuage de points avec une droite d&#39;ajustement optimal

Traiter l'hétéroscédasticité

Les données du graphique précédent semblent être hétéroscédastiques. La variance autour de la droite d'ajustement optimal augmente avec la population.

Il est possible que la quantité d'alcool achetée par personne soit relativement constante.

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

Résultat attendu :

Graphique en nuage de points des litres par habitant

Calculez la quantité moyenne d'alcool achetée de deux manières différentes:

  1. Quelle est la quantité moyenne d'alcool achetée par personne dans l'Iowa ?
  2. Quelle est la moyenne pour tous les codes postaux de la quantité d'alcool achetée par personne ?

Dans (1), il reflète la quantité d'alcool achetée dans l'ensemble de l'État. Dans (2), il reflète le code postal moyen, qui ne sera pas nécessairement identique à (1), car les populations des différents codes postaux sont différentes.

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

Résultat attendu: 87.997

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

Résultat attendu: 67.139

Représentez ces moyennes, comme ci-dessus.

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

Résultat attendu :

Graphique en nuage de points des litres par habitant

Certains codes postaux présentent encore des écarts importants, en particulier dans les zones moins peuplées. À vous de formuler des hypothèses pour expliquer ce phénomène. Par exemple, il se peut que certains codes postaux présentent une faible population, mais une consommation élevée, car ils abritent le seul magasin d'alcool de la région. Si tel est le cas, le calcul basé sur la population des codes postaux environnants peut même éliminer ces valeurs aberrantes.

6. Comparaison des types d'alcool vendus

En plus des données géographiques, la base de données des ventes au détail d'alcool de l'Iowa contient également des informations détaillées sur l'article vendu. En les analysant, nous pourrons peut-être révéler des différences de goûts entre les zones géographiques.

Parcourir les catégories

Les éléments sont catégorisés dans la base de données. Combien de catégories y a-t-il ?

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

Résultat attendu: 103

Quelles sont les catégories les plus populaires en termes de 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)

Graphique à barres des principales catégories d&#39;alcools vendus

Utiliser le type de données ARRAY

Il existe plusieurs catégories de whisky, de rhum, de vodka, etc. J'aimerais les regrouper d'une manière ou d'une autre.

Commencez par séparer les noms de catégorie en mots à l'aide de la méthode Series.str.split(). Désimbriquez le tableau créé à l'aide de la méthode 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)

Nombre de mots par catégorie

category_parts.nunique()

Résultat attendu: 113

En examinant le graphique ci-dessus, on voit que les données VODKA sont toujours séparées de VODKAS. Vous devez regrouper davantage de catégories pour les réduire à un ensemble plus petit.

7. Utiliser NLTK avec BigQuery DataFrames

Avec seulement une centaine de catégories, il est possible d'écrire des heuristiques ou même de créer manuellement un mappage de la catégorie au type de boisson alcoolisée plus large. Vous pouvez également utiliser un grand modèle de langage tel que Gemini pour créer une telle mise en correspondance. Essayez l'atelier de programmation Obtenir des insights à partir de données non structurées à l'aide de BigQuery DataFrames pour utiliser BigQuery DataFrames avec Gemini.

Utilisez plutôt un package de traitement du langage naturel plus traditionnel, NLTK, pour traiter ces données. Une technologie appelée "stemmer" peut fusionner les noms au pluriel et au singulier dans la même valeur, par exemple.

Utiliser NLTK pour extraire les racines des mots

Le package NLTK fournit des méthodes de traitement du langage naturel accessibles depuis Python. Installez le package pour l'essayer.

%pip install nltk

Importez ensuite le package. Inspectez la version. Vous en aurez besoin plus tard dans ce tutoriel.

import nltk

nltk.__version__

Une méthode de standardisation des mots consiste à "raciner" le mot. Cette opération supprime tous les suffixes, comme le "s" à la fin des noms au pluriel.

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)

Essayez-le avec quelques mots.

stem("WHISKEY")

Résultat attendu: whiskey

stem("WHISKIES")

Résultat attendu: whiski

Malheureusement, cela n'a pas permis de faire correspondre les whiskies au whisky. Les outils d'extraction de la racine ne fonctionnent pas bien avec les pluriels irréguliers. Essayez un lemmatiseur, qui utilise des techniques plus sophistiquées pour identifier le mot de base, appelé "lemme".

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

Essayez-le avec quelques mots.

lemmatize("WHISKIES")

Résultat attendu: whisky

lemmatize("WHISKY")

Résultat attendu: whisky

lemmatize("WHISKEY")

Résultat attendu: whiskey

Malheureusement, ce lemmatiseur ne mappe pas "whisky" au même lemme que "whiskies". Étant donné que ce mot est particulièrement important pour la base de données sur les ventes de boissons alcoolisées au détail dans l'Iowa, mappez-le manuellement à l'orthographe américaine à l'aide d'un dictionnaire.

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)

Essayez-le avec quelques mots.

lemmatize("WHISKIES")

Résultat attendu: whiskey

lemmatize("WHISKEY")

Résultat attendu: whiskey

Félicitations ! Ce lemmatiseur devrait être efficace pour affiner les catégories. Pour l'utiliser avec BigQuery, vous devez le déployer dans le cloud.

Configurer votre projet pour le déploiement de fonctions

Avant de déployer cette fonction dans le cloud afin que BigQuery puisse y accéder, vous devez effectuer une configuration unique.

Créez une cellule de code et remplacez your-project-id par l'ID du projet Google Cloud que vous utilisez pour ce tutoriel.

project_id = "your-project-id"

Créez un compte de service sans autorisations, car cette fonction n'a pas besoin d'accéder à des ressources 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)

Résultat attendu: bigframes-no-permissions@your-project-id.iam.gserviceaccount.com

Créez un ensemble de données BigQuery pour contenir la fonction.

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)

Déployer une fonction distante

Activez l'API Cloud Functions si ce n'est pas déjà fait.

!gcloud services enable cloudfunctions.googleapis.com

Déployez maintenant votre fonction dans l'ensemble de données que vous venez de créer. Ajoutez un décorateur @bpd.remote_function à la fonction que vous avez créée lors des étapes précédentes.

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

Le déploiement devrait prendre environ deux minutes.

Utiliser les fonctions à distance

Une fois le déploiement terminé, vous pouvez tester cette fonction.

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

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

Résultat attendu :

0	whiskey
1	whiskey
2	whiskey
3	vodka
4	vodka

dtype: string

8. Comparer la consommation d'alcool par comté

Maintenant que la fonction lemmatize est disponible, utilisez-la pour combiner des catégories.

Trouver le mot qui résume le mieux la catégorie

Commencez par créer un DataFrame de toutes les catégories de la base de données.

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

Résultat attendu :

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

Ensuite, créez un DataFrame de tous les mots des catégories, à l'exception de quelques mots de remplissage tels que les signes de ponctuation et "article".

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

Résultat attendu :

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

Notez qu'en lemmatisant après le regroupement, vous réduisez la charge sur votre fonction Cloud. Il est possible d'appliquer la fonction lemmatize à chacun des millions de lignes de la base de données, mais cela coûterait plus cher que de l'appliquer après le regroupement et pourrait nécessiter une augmentation des quotas.

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

Résultat attendu :

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

Maintenant que les mots ont été lemmatisés, vous devez sélectionner le lemme qui résume le mieux la catégorie. Étant donné qu'il n'y a pas beaucoup de mots fonctionnels dans les catégories, utilisez l'heuristique suivante : si un mot apparaît dans plusieurs autres catégories, il est probablement plus adapté en tant que mot récapitulatif (par exemple, "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()

Résultat attendu :

	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

Maintenant qu'il existe un seul lemme résumant chaque catégorie, fusionnez-le avec le DataFrame d'origine.

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

Résultat attendu :

	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

Comparer des comtés

Comparez les ventes dans chaque comté pour identifier les différences.

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

Trouvez le produit (lemme) le plus vendu dans chaque comté.

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

Résultat attendu :

	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

En quoi les comtés sont-ils différents les uns des autres ?

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

Résultat attendu :

lemma	
american	1
liqueur	1
vodka	15
whiskey	83

dtype: Int64

Dans la plupart des comtés, le whisky est le produit le plus populaire en termes de volume, tandis que la vodka est la plus populaire dans 15 comtés. Comparez-le aux types d'alcool les plus populaires dans l'État.

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

Résultat attendu :

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

Le whisky et la vodka représentent presque le même volume, la vodka étant un peu plus élevée que le whisky dans l'ensemble de l'État.

Comparer les proportions

Quelle est la particularité des ventes dans chaque comté ? En quoi le comté est-il différent du reste de l'État ?

Utilisez la mesure h de Cohen pour identifier les volumes de ventes d'alcool qui diffèrent le plus proportionnellement de ce qui était attendu en fonction de la proportion des ventes dans l'État.

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

Maintenant que l'h de Cohen a été mesuré pour chaque lemme, identifiez la plus grande différence par rapport à la proportion à l'échelle de l'État dans chaque comté.

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

Résultat attendu :

	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

Plus la valeur de Cohen's h est élevée, plus il est probable qu'il existe une différence statistiquement pertinente entre la quantité de ce type d'alcool consommée et les moyennes nationales. Pour les valeurs positives plus faibles, la différence de consommation est différente de la moyenne à l'échelle de l'État, mais cela peut être dû à des différences aléatoires.

Remarque: Le comté EL PASO ne semble pas être un comté de l'Iowa. Cela peut indiquer que vous devez encore nettoyer les données avant de vous fier entièrement à ces résultats.

Visualiser les comtés

Associez-vous au tableau bigquery-public-data.geo_us_boundaries.counties pour obtenir la zone géographique de chaque comté. Les noms de comtés ne sont pas uniques aux États-Unis. Filtrez donc pour n'inclure que les comtés de l'Iowa. Le code FIPS de l'Iowa est "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

Résultat attendu :

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

Utilisez GeoPandas pour visualiser ces différences sur une carte.

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

Carte de l&#39;alcool qui diffère le plus des proportions de volume de ventes à l&#39;échelle de l&#39;État dans chaque comté

9. Effectuer un nettoyage

Si vous avez créé un projet Google Cloud pour ce tutoriel, vous pouvez le supprimer pour éviter que des frais supplémentaires ne soient facturés pour les tables ou d'autres ressources créées.

Vous pouvez également supprimer les fonctions Cloud, les comptes de service et les ensembles de données créés pour ce tutoriel.

10. Félicitations !

Vous avez nettoyé et analysé des données structurées à l'aide de DataFrames BigQuery. Au cours de ce parcours, vous avez découvert les ensembles de données publics de Google Cloud, les notebooks Python dans BigQuery Studio, BigQuery ML, les fonctions distantes BigQuery et la puissance de BigQuery DataFrames. Bravo !

Étapes suivantes