Análise de dados exploratória das vendas de bebidas alcoólicas em Iowa usando o pacote BigQuery DataFrames

1. Visão geral

Neste laboratório, você vai usar o BigQuery DataFrames de um notebook Python no BigQuery Studio para limpar e analisar o conjunto de dados público de vendas de bebidas alcoólicas de Iowa. Use os recursos de função remota e do BigQuery ML para descobrir insights.

Você vai criar um notebook Python para comparar as vendas em diferentes áreas geográficas. Isso pode ser adaptado para funcionar em qualquer dado estruturado.

Objetivos

Neste laboratório, você aprenderá a fazer o seguinte:

Ativar e usar notebooks Python no BigQuery Studio
Conectar-se ao BigQuery usando o pacote BigQuery DataFrames
Criar uma regressão linear usando o BigQuery ML
Realizar agregações e junções complexas usando uma sintaxe semelhante ao pandas

2. Requisitos

Use um navegador, como o Chrome ou o Firefox.
Tenha um projeto do Google Cloud com o faturamento ativado.

Antes de começar

Para seguir as instruções deste codelab, você vai precisar de um projeto do Google Cloud com o BigQuery Studio ativado e uma conta de faturamento conectada.

No console do Google Cloud, na página de seletor de projetos, selecione ou crie um projeto do Google Cloud.
Verifique se o faturamento está ativado para o projeto do Google Cloud. Saiba como verificar se o faturamento está ativado em um projeto.
Siga as instruções para ativar o BigQuery Studio para gerenciamento de recursos.

Preparar o BigQuery Studio

Crie um notebook vazio e conecte-o a um ambiente de execução.

Acesse o BigQuery Studio no console do Google Cloud.
Clique em ▼ ao lado do botão +.
Selecione Notebook Python.
Feche o seletor de modelos.
Selecione + Código para criar uma célula de código.
Instale a versão mais recente do pacote BigQuery DataFrames na célula de código. Digite o seguinte comando:
```
%pip install --upgrade bigframes --quiet
```
Clique no botão Executar célula ou pressione Shift + Enter para executar a célula de código.

3. Ler um conjunto de dados público

Inicialize o pacote do BigQuery DataFrames executando o seguinte em uma nova célula de código:

import bigframes.pandas as bpd

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

Observação: neste tutorial, usamos o "modo de ordenação parcial" experimental, que permite consultas mais eficientes quando usado com filtragem semelhante ao pandas. Alguns recursos do pandas que exigem uma ordenação ou um índice estrito podem não funcionar.

Confira a versão do pacote bigframes com

bpd.__version__

Este tutorial requer a versão 1.27.0 ou mais recente.

Vendas de bebidas alcoólicas no varejo em Iowa

O conjunto de dados de vendas no varejo de bebidas alcoólicas de Iowa é fornecido no BigQuery pelo Programa de conjuntos de dados públicos do Google Cloud. Este conjunto de dados contém todas as compras de bebidas alcoólicas no atacado no estado de Iowa por varejistas para venda a pessoas físicas desde 1º de janeiro de 2012. Os dados são coletados pela Divisão de Bebidas Alcoólicas do Departamento de Comércio dos EUA em Iowa.

No BigQuery, consulte bigquery-public-data.iowa_liquor_sales.sales para analisar as vendas de bebidas alcoólicas no varejo de Iowa. Use o método bigframes.pandas.read_gbq() para criar um DataFrame com base em uma string de consulta ou um ID de tabela.

Execute o seguinte em uma nova célula de código para criar um DataFrame chamado "df":

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

Descobrir informações básicas sobre um DataFrame

Use o método DataFrame.peek() para baixar uma pequena amostra dos dados.

Execute esta célula:

df.peek()

Resposta esperada:

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

Observação: head() exige ordenação e geralmente é menos eficiente do que peek() se você quiser visualizar uma amostra de dados.

Assim como no pandas, use a propriedade DataFrame.dtypes para conferir todas as colunas disponíveis e os tipos de dados correspondentes. Eles são expostos de maneira compatível com pandas.

Execute esta célula:

df.dtypes

Resposta esperada:

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

O método DataFrame.describe() consulta algumas estatísticas básicas do DataFrame. Execute DataFrame.to_pandas() para fazer o download dessas estatísticas resumidas como um DataFrame do pandas.

Execute esta célula:

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

Resposta esperada:

	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. Visualizar e limpar os dados

O conjunto de dados de vendas varejistas de bebidas alcoólicas de Iowa fornece informações geográficas detalhadas, incluindo a localização das lojas. Use esses dados para identificar tendências e diferenças entre áreas geográficas.

Visualizar vendas por CEP

Há vários métodos de visualização integrados, como DataFrame.plot.hist(). Use esse método para comparar as vendas de bebidas alcoólicas por CEP.

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

Resposta esperada:

Histograma de volumes

Use um gráfico de barras para ver quais códigos postais venderam mais álcool.

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

Resposta esperada:

Gráfico de barras dos volumes de bebidas alcoólicas nos CEPs mais vendidos

Limpar os dados

Alguns CEPs têm um .0 no final. Talvez em algum lugar na coleta de dados, os CEPs tenham sido convertidos acidentalmente em valores de ponto flutuante. Use expressões regulares para limpar os CEPs e repita a análise.

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

Resposta esperada:

Gráfico de barras dos volumes de bebidas alcoólicas nos CEPs mais vendidos

5. Descobrir correlações nas vendas

Por que alguns CEPs vendem mais do que outros? Uma hipótese é que isso se deve a diferenças no tamanho da população. Um CEP com mais população provavelmente vai vender mais bebidas alcoólicas.

Teste essa hipótese calculando a correlação entre a população e o volume de vendas de bebidas alcoólicas.

Mesclar com outros conjuntos de dados

Faça uma junção com um conjunto de dados de população, como a pesquisa de área de tabulação de CEP da Pesquisa da Comunidade Americana do Bureau do Censo dos EUA.

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

A Pesquisa da Comunidade Americana identifica os estados por GEOID. No caso de áreas de tabulação de CEP, o GEOID é igual ao CEP.

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

Crie um gráfico de dispersão para comparar as populações das áreas de tabulação de CEP com os litros de álcool vendidos.

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

Resposta esperada:

Gráfico de dispersão das áreas de tabulação de CEPs por população e litros de bebidas alcoólicas vendidos

Calcular correlações

A tendência parece aproximadamente linear. Ajuste um modelo de regressão linear a isso para verificar se a população pode prever as vendas de bebidas alcoólicas.

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)

Verifique a qualidade do ajuste usando o método score.

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

Exemplo de resposta:

	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

Desenhe a linha de melhor ajuste chamando a função predict em um intervalo de valores de população.

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

Resposta esperada:

Gráfico de dispersão com uma linha de regressão ajustada

Como lidar com a heterocedasticidade

Os dados no gráfico anterior parecem ser heterocedásticos. A variância em torno da linha de melhor ajuste aumenta com a população.

Talvez a quantidade de álcool comprada por pessoa seja 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")
)

Resposta esperada:

Gráfico de dispersão de litros por população

Calcule a média de litros de álcool comprados de duas maneiras diferentes:

Qual é a quantidade média de álcool comprada por pessoa em Iowa?
Qual é a média em todos os CEPs da quantidade de álcool comprada por pessoa.

Em (1), ele reflete a quantidade de álcool comprada em todo o estado. Em (2), ele reflete o CEP médio, que não é necessariamente o mesmo que (1) porque diferentes CEPs têm populações 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

Resposta esperada: 87.997

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

Resposta esperada: 67.139

Represente essas médias, de forma semelhante ao que foi feito acima.

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

Resposta esperada:

Gráfico de dispersão de litros por população

Ainda há alguns CEPs que são outliers bastante grandes, especialmente em áreas com menos população. Fica como exercício levantar hipóteses sobre o motivo disso. Por exemplo, pode ser que alguns CEPs tenham baixa população, mas alto consumo, porque contêm a única loja de bebidas da área. Nesse caso, o cálculo com base na população dos CEPs vizinhos pode eliminar esses outliers.

6. Comparação entre os tipos de bebidas alcoólicas vendidas

Além dos dados geográficos, o banco de dados de vendas no varejo de bebidas alcoólicas de Iowa também contém informações detalhadas sobre o item vendido. Talvez, ao analisar esses dados, seja possível revelar diferenças de preferências entre áreas geográficas.

Ver categorias

Os itens são categorizados no banco de dados. Quantas categorias existem?

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

Resposta esperada: 103

Quais são as categorias mais procuradas por 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)

Gráfico de barras das principais categorias de bebidas vendidas

Como trabalhar com o tipo de dados ARRAY

Há várias categorias de uísque, rum, vodca e muito mais. Queria agrupar essas informações de alguma forma.

Comece dividindo os nomes das categorias em palavras separadas usando o método Series.str.split(). Para desagrupar a matriz criada, use o 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)

Palavras por contagem de categorias

category_parts.nunique()

Resposta esperada: 113

Analisando o gráfico acima, os dados ainda têm VODKA separado de VODKAS. É necessário mais agrupamento para reduzir as categorias a um conjunto menor.

7. Usar o NLTK com BigQuery DataFrames

Com apenas cerca de 100 categorias, seria possível escrever algumas heurísticas ou até mesmo criar manualmente um mapeamento da categoria para o tipo de bebida mais amplo. Outra opção é usar um modelo de linguagem grande, como o Gemini, para criar esse mapeamento. Teste o codelab Extrair insights de dados não estruturados usando DataFrames do BigQuery para usar o BigQuery DataFrames com o Gemini.

Em vez disso, use um pacote de processamento de linguagem natural mais tradicional, o NLTK, para processar esses dados. Uma tecnologia chamada "stemmer" pode mesclar substantivos no plural e no singular no mesmo valor, por exemplo.

Como usar o NLTK para derivar palavras

O pacote NLTK oferece métodos de processamento de linguagem natural acessíveis em Python. Instale o pacote para testar.

%pip install nltk

Em seguida, importe o pacote. Inspecione a versão. Ele será usado mais adiante no tutorial.

import nltk

nltk.__version__

Uma maneira de padronizar palavras é "derivar" a palavra. Isso remove todos os sufixos, como um "s" no final para plurais.

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)

Teste isso em algumas palavras.

stem("WHISKEY")

Resposta esperada: whiskey

stem("WHISKIES")

Resposta esperada: whiski

Infelizmente, isso não mapeou "uísques" para "uísque". Os stemmers não funcionam bem com plurais irregulares. Tente usar um lematizador, que usa técnicas mais sofisticadas para identificar a palavra base, chamada de "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())

Teste com algumas palavras.

lemmatize("WHISKIES")

Resposta esperada: whisky

lemmatize("WHISKY")

Resposta esperada: whisky

lemmatize("WHISKEY")

Resposta esperada: whiskey

Infelizmente, esse lematizador não mapeia "whiskey" para o mesmo lema de "whiskies". Como essa palavra é particularmente importante para o banco de dados de vendas de bebidas alcoólicas no varejo de Iowa, mapeie-a manualmente para a grafia americana usando um dicionário.

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)

Teste isso em algumas palavras.

lemmatize("WHISKIES")

Resposta esperada: whiskey

lemmatize("WHISKEY")

Resposta esperada: whiskey

Parabéns! Esse lematizador funciona bem para restringir as categorias. Para usar com o BigQuery, é necessário implantar na nuvem.

Configurar seu projeto para implantação de funções

Antes de implantar isso na nuvem para que o BigQuery possa acessar essa função, você precisa fazer uma configuração única.

Crie uma célula de código e substitua your-project-id pelo ID do projeto do Google Cloud que você está usando neste tutorial.

project_id = "your-project-id"

Crie uma conta de serviço sem permissões, já que essa função não precisa de acesso a nenhum recurso da nuvem.

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)

Resposta esperada: bigframes-no-permissions@your-project-id.iam.gserviceaccount.com

Crie um conjunto de dados do BigQuery para armazenar a função.

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)

Como implantar uma função remota

Ative a API Cloud Functions, se ainda não tiver feito isso.

!gcloud services enable cloudfunctions.googleapis.com

Agora, implante a função no conjunto de dados que você acabou de criar. Adicione um decorator @bpd.remote_function à função criada nas etapas 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)

A implantação leva cerca de dois minutos.

Como usar as funções remotas

Depois que a implantação for concluída, você poderá testar essa função.

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

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

Resposta esperada:

0	whiskey
1	whiskey
2	whiskey
3	vodka
4	vodka

dtype: string

8. Comparação do consumo de álcool por município

Agora que a função lemmatize está disponível, use-a para combinar categorias.

Encontrar a palavra que melhor resume a categoria

Primeiro, crie um DataFrame de todas as categorias no banco de dados.

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

Resposta esperada:

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

Em seguida, crie um DataFrame com todas as palavras das categorias, exceto algumas palavras de preenchimento, como pontuação e "item".

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

Resposta esperada:

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

Ao fazer a lematização depois do agrupamento, você reduz a carga na sua função do Cloud. É possível aplicar a função de lematização a cada uma das várias milhões de linhas no banco de dados, mas isso custaria mais do que aplicar depois do agrupamento e pode exigir aumentos de cota.

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

Resposta esperada:

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

Agora que as palavras foram lematizadas, selecione o lema que melhor resume a categoria. Como não há muitas palavras funcionais nas categorias, use a heurística de que, se uma palavra aparecer em várias outras categorias, provavelmente será melhor como uma palavra de resumo (por exemplo, uísque).

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

Resposta esperada:

	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

Agora que há um único lema resumindo cada categoria, mescle isso ao DataFrame original.

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

Resposta esperada:

	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

Comparar municípios

Compare as vendas em cada município para ver as diferenças.

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

Encontre o produto mais vendido (lema) em cada município.

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

Resposta esperada:

	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

Qual a diferença entre os condados?

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

Resposta esperada:

lemma	
american	1
liqueur	1
vodka	15
whiskey	83

dtype: Int64

Na maioria dos condados, o uísque é o produto mais procurado por volume, e a vodca é a mais popular em 15 condados. Compare isso com os tipos de bebidas alcoólicas mais populares em todo o estado.

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

Resposta esperada:

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

Uísque e vodca têm quase o mesmo volume, sendo a vodca um pouco mais alta que o uísque em todo o estado.

Comparação de proporções

O que há de exclusivo nas vendas de cada município? O que diferencia o município do restante do estado?

Use a medida h de Cohen para descobrir quais volumes de vendas de bebidas alcoólicas têm a maior diferença proporcional em relação ao que seria esperado com base na proporção de vendas em todo o 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"))
)

Agora que o h de Cohen foi medido para cada lema, encontre a maior diferença da proporção estadual em cada município.

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

Resposta esperada:

	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

Quanto maior o valor h de Cohen, maior a probabilidade de haver uma diferença estatisticamente significativa na quantidade desse tipo de álcool consumido em comparação com as médias estaduais. Para os valores positivos menores, a diferença no consumo é diferente da média estadual, mas pode ser devido a diferenças aleatórias.

Observação: o condado de EL PASO não parece ser um condado em Iowa. Isso pode indicar outra necessidade de revisão dos dados antes de depender totalmente desses resultados.

Visualização de condados

Faça uma junção com a tabela bigquery-public-data.geo_us_boundaries.counties para receber a área geográfica de cada município. Os nomes dos condados não são exclusivos nos Estados Unidos. Por isso, filtre para incluir apenas os condados de Iowa. O código FIPS de 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

Resposta esperada:

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

Use o GeoPandas para visualizar essas diferenças em um 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,
)

Um mapa do álcool mais diferente das proporções de volume de vendas em todo o estado em cada condado

9. Limpar

Se você criou um projeto na nuvem do Google para este tutorial, exclua-o para evitar cobranças extras por tabelas ou outros recursos criados.

Como alternativa, exclua as funções do Cloud, as contas de serviço e os conjuntos de dados criados para este tutorial.

10. Parabéns!

Você limpou e analisou dados estruturados usando DataFrames do BigQuery. Ao longo do caminho, você conheceu os conjuntos de dados públicos do Google Cloud, os notebooks Python no BigQuery Studio, o BigQuery ML, as funções remotas do BigQuery e o poder do BigQuery DataFrames. Ótimo trabalho!

Próximas etapas

Aplique essas etapas a outros dados, como o banco de dados de nomes dos EUA.
Tente gerar código Python no seu notebook. Os notebooks Python no BigQuery Studio são desenvolvidos pelo Colab Enterprise. Dica: acho muito útil pedir ajuda para gerar dados de teste.
Confira os notebooks de exemplo do BigQuery DataFrames no GitHub.
Crie um cronograma para executar um notebook no BigQuery Studio.
Implante uma função remota com o BigQuery DataFrames para integrar pacotes Python de terceiros ao BigQuery.