1. Descripción general
En este lab, usarás BigQuery DataFrames desde un notebook de Python en BigQuery Studio para limpiar y analizar el conjunto de datos públicos de ventas de licor de Iowa. Aprovecha las capacidades de BigQuery ML y las funciones remotas para descubrir estadísticas.
Crearás un notebook de Python para comparar las ventas en diferentes áreas geográficas. Se puede adaptar para que funcione con cualquier dato estructurado.
Objetivos
En este lab, aprenderás a realizar las siguientes tareas:
- Activa y usa notebooks de Python en BigQuery Studio
- Conéctate a BigQuery con el paquete de BigQuery DataFrames
- Crear una regresión lineal con BigQuery ML
- Realiza agregaciones y uniones complejas con una sintaxis familiar similar a la de pandas
2. Requisitos
Antes de comenzar
Para seguir las instrucciones de este codelab, necesitarás un proyecto de Google Cloud con BigQuery Studio habilitado y una cuenta de facturación conectada.
- En la página del selector de proyectos de la consola de Google Cloud, selecciona o crea un proyecto de Google Cloud.
- Asegúrate de que la facturación esté habilitada para tu proyecto de Google Cloud. Obtén información para verificar si la facturación está habilitada en un proyecto.
- Sigue las instrucciones para habilitar BigQuery Studio para la administración de recursos.
Prepara BigQuery Studio
Crea un notebook vacío y conéctalo a un entorno de ejecución.
- Ve a BigQuery Studio en la consola de Google Cloud.
- Haz clic en ▼ junto al botón +.
- Selecciona Notebook de Python.
- Cierra el selector de plantillas.
- Selecciona + Código para crear una celda de código nueva.
- Instala la versión más reciente del paquete de BigQuery DataFrames desde la celda de código.Escribe el siguiente comando.
%pip install --upgrade bigframes --quiet
3. Cómo leer un conjunto de datos públicos
Inicializa el paquete de BigQuery DataFrames ejecutando lo siguiente en una celda de código nueva:
import bigframes.pandas as bpd
bpd.options.bigquery.ordering_mode = "partial"
bpd.options.display.repr_mode = "deferred"
Nota: En este instructivo, usamos el "modo de ordenamiento parcial" experimental, que permite realizar consultas más eficientes cuando se usa con filtros similares a los de pandas. Es posible que no funcionen algunas funciones de pandas que requieren un orden o un índice estrictos.
Verifica la versión de tu paquete bigframes con el siguiente comando:
bpd.__version__
Este instructivo requiere la versión 1.27.0 o posterior.
Ventas minoristas de bebidas alcohólicas en Iowa
El conjunto de datos de ventas minoristas de licor de Iowa se proporciona en BigQuery a través del programa de conjuntos de datos públicos de Google Cloud. Este conjunto de datos contiene todas las compras mayoristas de licores en el estado de Iowa por parte de minoristas para la venta a particulares desde el 1 de enero de 2012. La División de Bebidas Alcohólicas de la Secretaría de Comercio de Iowa recopila los datos.
En BigQuery, consulta bigquery-public-data.iowa_liquor_sales.sales para analizar las ventas minoristas de licor en Iowa. Usa el método bigframes.pandas.read_gbq() para crear un DataFrame a partir de una cadena de consulta o un ID de tabla.
Ejecuta lo siguiente en una celda de código nueva para crear un DataFrame llamado "df":
df = bpd.read_gbq_table("bigquery-public-data.iowa_liquor_sales.sales")
Descubre información básica sobre un DataFrame
Usa el método DataFrame.peek() para descargar una pequeña muestra de los datos.
Ejecuta esta celda:
df.peek()
Resultado esperado:
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() requiere ordenamiento y, por lo general, es menos eficiente que peek() si deseas visualizar una muestra de datos.
Al igual que con pandas, usa la propiedad DataFrame.dtypes para ver todas las columnas disponibles y sus tipos de datos correspondientes. Se exponen de una manera compatible con pandas.
Ejecuta esta celda:
df.dtypes
Resultado esperado:
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
El método DataFrame.describe() consulta algunas estadísticas básicas del DataFrame. Ejecuta DataFrame.to_pandas() para descargar estas estadísticas de resumen como un DataFrame de Pandas.
Ejecuta esta celda:
df.describe("all").to_pandas()
Resultado esperado:
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. Visualiza y limpia los datos
El conjunto de datos de ventas minoristas de bebidas alcohólicas de Iowa proporciona información geográfica detallada, incluida la ubicación de las tiendas minoristas. Usa estos datos para identificar tendencias y diferencias en las distintas áreas geográficas.
Visualiza las ventas por código postal
Existen varios métodos de visualización integrados, como DataFrame.plot.hist(). Usa este método para comparar las ventas de licor por código postal.
volume_by_zip = df.groupby("zip_code").agg({"volume_sold_liters": "sum"})
volume_by_zip.plot.hist(bins=20)
Resultado esperado:
Usa un gráfico de barras para ver qué códigos postales vendieron más alcohol.
(
volume_by_zip
.sort_values("volume_sold_liters", ascending=False)
.head(25)
.to_pandas()
.plot.bar(rot=80)
)
Resultado esperado:
Limpia los datos
Algunos códigos postales tienen un .0 al final. Es posible que, en algún punto de la recopilación de datos, los códigos postales se hayan convertido accidentalmente en valores de punto flotante. Usa expresiones regulares para limpiar los códigos postales y repetir el análisis.
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)
)
Resultado esperado:
5. Descubre correlaciones en las ventas
¿Por qué algunos códigos postales venden más que otros? Una hipótesis es que se debe a las diferencias en el tamaño de la población. Es probable que en un código postal con más población se venda más licor.
Para probar esta hipótesis, calcula la correlación entre la población y el volumen de ventas de bebidas alcohólicas.
Unir con otros conjuntos de datos
Unir con un conjunto de datos de población, como la encuesta de áreas de tabulación de códigos postales de la Encuesta sobre la comunidad estadounidense de la Oficina del Censo de EE.UU.
census_acs = bpd.read_gbq_table("bigquery-public-data.census_bureau_acs.zcta_2020_5yr")
La Encuesta sobre la Comunidad Estadounidense identifica los estados por GEOID. En el caso de las áreas de tabulación de códigos postales, el GEOID es igual al código postal.
volume_by_pop = volume_by_zip.join(
census_acs.set_index("geo_id")
)
Crea un diagrama de dispersión para comparar las poblaciones de las áreas de tabulación de códigos postales con los litros de alcohol vendidos.
(
volume_by_pop[["volume_sold_liters", "total_pop"]]
.to_pandas()
.plot.scatter(x="total_pop", y="volume_sold_liters")
)
Resultado esperado:
Cómo calcular correlaciones
La tendencia parece aproximadamente lineal. Ajusta un modelo de regresión lineal a estos datos para verificar qué tan bien la población puede predecir las ventas de licor.
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)
Verifica qué tan bueno es el ajuste con el método score.
model.score(feature_columns, label_columns).to_pandas()
Resultado de muestra:
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
Dibuja la línea de mejor ajuste llamando a la función predict en un rango de valores de población.
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",
)
Resultado esperado:
Cómo abordar la heteroscedasticidad
Los datos del gráfico anterior parecen ser heterocedásticos. La varianza alrededor de la línea de mejor ajuste aumenta con la población.
Quizás la cantidad de alcohol que se compra por persona es relativamente 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")
)
Resultado esperado:
Calcula el promedio de litros de alcohol comprados de dos maneras diferentes:
- ¿Cuál es la cantidad promedio de alcohol que compra cada persona en Iowa?
- Es el promedio de la cantidad de alcohol comprada por persona en todos los códigos postales.
En (1), se refleja la cantidad de alcohol que se compra en todo el estado. En (2), se refleja el código postal promedio, que no necesariamente será el mismo que en (1) porque los diferentes códigos postales tienen poblaciones diferentes.
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
Resultado esperado: 87.997
average_per_zip = volume_per_pop["liters_per_pop"].mean()
average_per_zip
Resultado esperado: 67.139
Representa estos promedios en un gráfico, de forma similar a lo que se hizo anteriormente.
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")
Resultado esperado:
Aún hay algunos códigos postales que son valores atípicos bastante grandes, especialmente en áreas con menos población. Se deja como ejercicio hipotetizar por qué sucede esto. Por ejemplo, podría ser que algunos códigos postales tengan una población baja, pero un consumo alto porque contienen la única licorería de la zona. Si es así, calcular en función de la población de los códigos postales circundantes puede incluso compensar estos valores atípicos.
6. Comparación de los tipos de bebidas alcohólicas vendidas
Además de los datos geográficos, la base de datos de ventas minoristas de licores de Iowa también contiene información detallada sobre el artículo vendido. Quizás, al analizarlos, podamos revelar diferencias en los gustos entre las distintas áreas geográficas.
Explorar categorías
Los elementos están categorizados en la base de datos. ¿Cuántas categorías hay?
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()
Resultado esperado: 103
¿Cuáles son las categorías más populares por volumen?
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)
Trabaja con el tipo de datos ARRAY
Existen varias categorías de whisky, ron, vodka y mucho más. Me gustaría agruparlos de alguna manera.
Comienza por dividir los nombres de las categorías en palabras separadas con el método Series.str.split(). Anula el anidamiento del array que se crea con el método explode().
category_parts = df.category_name.str.split(" ").explode()
counts = (
category_parts
.groupby(category_parts)
.size()
.sort_values(ascending=False)
.to_pandas()
)
counts.head(25).plot.bar(rot=80)
category_parts.nunique()
Resultado esperado: 113
Si observas el gráfico anterior, los datos aún tienen VODKA separado de VODKAS. Se necesita más agrupación para reducir las categorías a un conjunto más pequeño.
7. Usa NLTK con BigQuery DataFrames
Con solo alrededor de 100 categorías, sería factible escribir algunas heurísticas o incluso crear manualmente una asignación de la categoría al tipo de licor más amplio. Como alternativa, se podría usar un modelo de lenguaje grande, como Gemini, para crear esa asignación. Prueba el codelab Obtén estadísticas a partir de datos no estructurados con BigQuery DataFrames para usar BigQuery DataFrames con Gemini.
En su lugar, usa un paquete de procesamiento de lenguaje natural más tradicional, NLTK, para procesar estos datos. Por ejemplo, la tecnología llamada "reductor" puede combinar sustantivos en plural y singular en el mismo valor.
Cómo usar NLTK para reducir palabras a su raíz
El paquete NLTK proporciona métodos de procesamiento de lenguaje natural a los que se puede acceder desde Python. Instala el paquete para probarlo.
%pip install nltk
A continuación, importa el paquete. Inspecciona la versión. Se usará más adelante en el instructivo.
import nltk
nltk.__version__
Una forma de estandarizar las palabras para obtener su "raíz". Esto quita cualquier sufijo, como una "s" final para los plurales.
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)
Prueba con algunas palabras.
stem("WHISKEY")
Resultado esperado: whiskey
stem("WHISKIES")
Resultado esperado: whiski
Lamentablemente, no se asignaron los whiskies a la misma categoría que el whisky. Los derivadores no funcionan bien con los plurales irregulares. Prueba un lematizador, que usa técnicas más sofisticadas para identificar la palabra base, llamada "lema".
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())
Prueba con algunas palabras.
lemmatize("WHISKIES")
Resultado esperado: whisky
lemmatize("WHISKY")
Resultado esperado: whisky
lemmatize("WHISKEY")
Resultado esperado: whiskey
Lamentablemente, este lematizador no asigna "whiskey" al mismo lema que "whiskies". Dado que esta palabra es particularmente importante para la base de datos de ventas minoristas de licores de Iowa, se asigna manualmente a la ortografía estadounidense con un diccionario.
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)
Prueba con algunas palabras.
lemmatize("WHISKIES")
Resultado esperado: whiskey
lemmatize("WHISKEY")
Resultado esperado: whiskey
¡Felicitaciones! Este lematizador debería funcionar bien para reducir las categorías. Para usarlo con BigQuery, debes implementarlo en la nube.
Configura tu proyecto para la implementación de funciones
Antes de implementar esto en la nube para que BigQuery pueda acceder a esta función, deberás realizar una configuración única.
Crea una celda de código nueva y reemplaza your-project-id por el ID del proyecto de Google Cloud que usas para este instructivo.
project_id = "your-project-id"
Crea una cuenta de servicio sin permisos, ya que esta función no necesita acceder a ningún recurso de la nube.
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)
Resultado esperado: bigframes-no-permissions@your-project-id.iam.gserviceaccount.com
Crea un conjunto de datos de BigQuery para contener la función.
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)
Implementa una función remota
Habilita la API de Cloud Functions si aún no lo hiciste.
!gcloud services enable cloudfunctions.googleapis.com
Ahora, implementa tu función en el conjunto de datos que acabas de crear. Agrega un decorador @bpd.remote_function a la función que creaste en los pasos anteriores.
@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)
La implementación debería tardar unos dos minutos.
Usa las funciones remotas
Una vez que se complete la implementación, podrás probar esta función.
lemmatize = bpd.read_gbq_function(f"{project_id}.functions.lemmatize")
words = bpd.Series(["whiskies", "whisky", "whiskey", "vodkas", "vodka"])
words.apply(lemmatize).to_pandas()
Resultado esperado:
0 whiskey
1 whiskey
2 whiskey
3 vodka
4 vodka
dtype: string
8. Comparación del consumo de alcohol por condado
Ahora que la función lemmatize está disponible, úsala para combinar categorías.
Cómo encontrar la palabra que mejor resume la categoría
Primero, crea un DataFrame de todas las categorías de la base de datos.
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()
Resultado esperado:
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 continuación, crea un DataFrame de todas las palabras de las categorías, excepto algunas palabras de relleno, como signos de puntuación y "artículo".
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()
Resultado esperado:
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
Ten en cuenta que, al lematizar después de agrupar, reduces la carga en tu Cloud Function. Es posible aplicar la función de lematización a cada uno de los varios millones de filas de la base de datos, pero costaría más que aplicarla después de agrupar y podría requerir aumentos de cuota.
lemmas = words.assign(lemma=lambda _: _["word"].apply(lemmatize))
lemmas.to_pandas()
Resultado esperado:
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
Ahora que las palabras se lematizaron, debes seleccionar el lema que mejor resuma la categoría. Dado que no hay muchas palabras funcionales en las categorías, usa la heurística de que, si una palabra aparece en varias otras categorías, es probable que sea mejor como palabra de resumen (p.ej., 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()
Resultado esperado:
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
Ahora que hay un solo lema que resume cada categoría, combínalo con el DataFrame original.
df_with_lemma = df.merge(
categories_mapping,
on="category_name",
how="left"
)
df_with_lemma[df_with_lemma['category_name'].notnull()].peek()
Resultado esperado:
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
Comparación de condados
Compara las ventas en cada condado para ver qué diferencias hay.
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"))
)
Encuentra el producto (lema) más vendido en cada condado.
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()
Resultado esperado:
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
¿Qué tan diferentes son los condados entre sí?
county_max_lemma.groupby("lemma").size().to_pandas()
Resultado esperado:
lemma
american 1
liqueur 1
vodka 15
whiskey 83
dtype: Int64
En la mayoría de los condados, el whisky es el producto más popular por volumen, mientras que el vodka es el más popular en 15 condados. Compara esto con los tipos de licor más populares en todo el estado.
total_liters = (
df_with_lemma
.groupby("lemma")
.agg({"volume_sold_liters": "sum"})
.sort_values("volume_sold_liters", ascending=False)
)
total_liters.to_pandas()
Resultado esperado:
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
...
El whisky y el vodka tienen casi el mismo volumen, y el vodka es un poco más alto que el whisky en todo el estado.
Comparación de proporciones
¿Qué tienen de particular las ventas en cada condado? ¿Qué diferencia al condado del resto del estado?
Usa la medida h de Cohen para determinar qué volúmenes de ventas de licor difieren más proporcionalmente de lo que se esperaría según la proporción de ventas en todo el estado.
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"))
)
Ahora que se midió la h de Cohen para cada lema, busca la diferencia más grande con respecto a la proporción estatal en cada condado.
# 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()
Resultado esperado:
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
Cuanto mayor sea el valor de h de Cohen, más probable será que haya una diferencia estadísticamente significativa en la cantidad de ese tipo de alcohol que se consume en comparación con los promedios estatales. En el caso de los valores positivos más pequeños, la diferencia en el consumo es diferente del promedio estatal, pero puede deberse a diferencias aleatorias.
Nota aparte: El condado de EL PASO no parece ser un condado de Iowa, lo que puede indicar otra necesidad de limpieza de datos antes de depender por completo de estos resultados.
Visualiza condados
Une con la tabla bigquery-public-data.geo_us_boundaries.counties para obtener el área geográfica de cada condado. Los nombres de los condados no son únicos en todo Estados Unidos, por lo que debes filtrar para incluir solo los condados de Iowa. El código FIPS de Iowa es "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
Resultado esperado:
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
Usa GeoPandas para visualizar estas diferencias en un mapa.
import geopandas
counties_plus = geopandas.GeoDataFrame(counties_plus, geometry="county_geom")
# https://stackoverflow.com/a/42214156/101923
ax = counties_plus.plot(figsize=(14, 14))
counties_plus.apply(
lambda row: ax.annotate(
text=row['lemma'],
xy=row['county_geom'].centroid.coords[0],
ha='center'
),
axis=1,
)
9. Limpia
Si creaste un proyecto nuevo de Google Cloud para este instructivo, puedes borrarlo para evitar cargos adicionales por las tablas o los demás recursos creados.
Como alternativa, borra las funciones de Cloud Functions, las cuentas de servicio y los conjuntos de datos que creaste para este instructivo.
10. ¡Felicitaciones!
Limpiaste y analizaste datos estructurados con marcos de datos de BigQuery. En el camino, exploraste los conjuntos de datos públicos de Google Cloud, los notebooks de Python en BigQuery Studio, BigQuery ML, las funciones remotas de BigQuery y el poder de BigQuery DataFrames. ¡Gran trabajo!
Próximos pasos
- Aplica estos pasos a otros datos, como la base de datos de nombres de EE.UU..
- Intenta generar código de Python en tu notebook. Los notebooks de Python en BigQuery Studio cuentan con la tecnología de Colab Enterprise. Sugerencia: Me resulta muy útil pedir ayuda para generar datos de prueba.
- Explora los notebooks de muestra para BigQuery DataFrames en GitHub.
- Crea una programación para ejecutar un notebook en BigQuery Studio.
- Implementa una función remota con BigQuery DataFrames para integrar paquetes de Python de terceros en BigQuery.