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 DataFrames do BigQuery em 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 do BigQuery ML e das funções remotas para descobrir insights.

Você vai criar um notebook Python para comparar as vendas em áreas geográficas. Isso pode ser adaptado para funcionar com 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 neste codelab, você vai precisar de um projeto do Google Cloud com o BigQuery Studio ativado e uma conta de faturamento conectada.

  1. No console do Google Cloud, na página do seletor de projetos, selecione ou crie um projeto do Google Cloud.
  2. Verifique se o faturamento está ativado para seu projeto do Google Cloud. Saiba como verificar se o faturamento está ativado em um projeto.
  3. 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.

  1. Acesse o BigQuery Studio no console do Google Cloud.
  2. Clique no ▼ ao lado do botão +.
  3. Selecione Notebook Python.
  4. Feche o seletor de modelos.
  5. Selecione + Código para criar uma nova célula de código.
  6. Instale a versão mais recente do pacote BigQuery DataFrames na célula de código.Digite o comando a seguir.
    %pip install --upgrade bigframes --quiet
    Clique no botão Run cell ou pressione Shift + Enter para executar a célula de código.

3. Ler um conjunto de dados público

Inicie o pacote 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 a filtragem semelhante ao pandas. Alguns recursos do pandas que exigem uma ordenação ou um índice rígido podem não funcionar.

Verifique 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 de bebidas alcoólicas a varejo 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 no atacado de bebidas alcoólicas no estado de Iowa por varejistas para venda a indivíduos desde 1º de janeiro de 2012. Os dados são coletados pela Divisão de Bebidas Alcoólicas do Departamento de Comércio de Iowa (EUA).

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 fazer o download de uma pequena amostra dos dados.

Execute esta célula:

df.peek()

Saída 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

Saída 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()

Saída 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 de bebidas alcoólicas no varejo de Iowa fornece informações geográficas detalhadas, incluindo onde as lojas estão localizadas. Use esses dados para identificar tendências e diferenças entre as áreas geográficas.

Visualizar vendas por CEP

Existem 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 código postal.

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

Saída esperada:

Histograma de volumes

Use um gráfico de barras para saber 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)
)

Saída esperada:

Gráfico de barras dos volumes de álcool nos CEPs com mais vendas

Limpar os dados

Alguns CEPs têm um .0 no final. Talvez em algum lugar da coleta de dados, os códigos postais tenham sido convertidos acidentalmente em valores de ponto flutuante. Use expressões regulares para limpar os códigos postais 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)
)

Saída esperada:

Gráfico de barras dos volumes de álcool nos CEPs com mais vendas

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 vende mais bebidas alcoólicas.

Para testar essa hipótese, calcule 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 mesclagem com um conjunto de dados de população, como a pesquisa da área de tabulação de CEPs do US Census Bureau's American Community Survey.

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

A Pesquisa Comunitária 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 diagrama de dispersão para comparar as populações da área de tabulação do 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")
)

Saída 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 linear. Ajuste um modelo de regressão linear a isso para verificar como 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, mas 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",
)

Saída esperada:

Gráfico de dispersão com uma linha de ajuste

Como lidar com a heteroscedasticidade

Os dados no gráfico anterior parecem ser heteroscedásticos. A variância em torno da linha de 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")
)

Saída esperada:

Diagrama de dispersão de litros por população

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

  1. Qual é a quantidade média de álcool comprada por pessoa em Iowa?
  2. Qual é a média de todos os códigos postais 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 CEPs diferentes 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

Saída esperada: 87.997

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

Saída esperada: 67.139

Mostre essas médias, de forma semelhante à 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")

Saída esperada:

Diagrama de dispersão de litros por população

Ainda há alguns CEPs que são outliers muito grandes, especialmente em áreas com menos população. É um exercício para você levantar hipóteses sobre o motivo disso. Por exemplo, pode ser que alguns CEPs tenham baixa população, mas alto consumo, porque eles contêm a única loja de bebidas alcoólicas na área. Se for o caso, calcular com base na população dos códigos postais vizinhos pode até mesmo eliminar esses valores fora da curva.

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

Além dos dados geográficos, o banco de dados de vendas de bebidas alcoólicas de varejo de Iowa também contém informações detalhadas sobre o item vendido. Talvez, ao analisar esses dados, possamos revelar diferenças nos gostos em diferentes á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()

Saída esperada: 103

Quais são as categorias mais populares 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 alcoólicas vendidas

Como trabalhar com o tipo de dados ARRAY

Há várias categorias de uísque, rum, vodca e muito mais. Gostaria de agrupar esses itens de alguma forma.

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

Saída esperada: 113

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

7. Como usar o NLTK com os DataFrames do BigQuery

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

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

Como usar o NLTK para extrair palavras-raiz

O pacote NLTK (em inglês) oferece métodos de processamento de linguagem natural que podem ser acessados pelo Python. Instale o pacote para testá-lo.

%pip install nltk

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

import nltk

nltk.__version__

Uma forma de padronizar palavras para "originar" a palavra. Isso remove todos os sufixos, como um "s" 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 com algumas palavras.

stem("WHISKEY")

Saída esperada: whiskey

stem("WHISKIES")

Saída esperada: whiski

Infelizmente, isso não mapeou os uísques da mesma forma que o uísque. Os stemmers não funcionam bem com plurais irregulares. Experimente 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 isso com algumas palavras.

lemmatize("WHISKIES")

Saída esperada: whisky

lemmatize("WHISKY")

Saída esperada: whisky

lemmatize("WHISKEY")

Saída 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 ortografia 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 com algumas palavras.

lemmatize("WHISKIES")

Saída esperada: whiskey

lemmatize("WHISKEY")

Saída esperada: whiskey

Parabéns! Esse lematizador deve funcionar bem para restringir as categorias. Para usar o BigQuery, é necessário implantar o banco de dados na nuvem.

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

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

Crie uma nova 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 recursos 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)

Saída 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 sua função no conjunto de dados que você acabou de criar. Adicione um decorador @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 deve levar 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()

Saída 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()

Saída 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 de todas as palavras nas 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()

Saída 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 lemmatizar após o agrupamento, você reduz a carga na sua função do Cloud. É possível aplicar a função lemmatize em cada um dos milhões de linhas no banco de dados, mas isso custaria mais do que a aplicação após o agrupamento e pode exigir aumentos de cota.

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

Saída 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, você precisa selecionar 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 aparece em várias outras categorias, ela provavelmente é melhor como uma palavra de resumo (por exemplo, "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()

Saída 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 que resume cada categoria, mescle-o 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()

Saída 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

Comparação de municípios

Compare as vendas em cada condado para saber quais são 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 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()

Saída 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()

Saída esperada:

lemma	
american	1
liqueur	1
vodka	15
whiskey	83

dtype: Int64

Na maioria dos condados, o uísque é o produto mais popular em volume, e a vodka é a mais procurada 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()

Saída 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
...

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

Comparação de proporções

O que é único nas vendas de cada condado? O que diferencia o condado do resto do estado?

Use a medida h de Cohen para descobrir quais volumes de vendas de bebidas alcoólicas diferem mais proporcionalmente do 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 a medida h de Cohen foi calculada para cada lema, encontre a maior diferença em relação à proporção estadual em 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()

Saída 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 de 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 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 de Iowa. Isso pode indicar outra necessidade de limpeza de dados antes de depender totalmente desses resultados.

Visualizar condados

Junte-se à tabela bigquery-public-data.geo_us_boundaries.counties para receber a área geográfica de cada condado. Os nomes dos condados não são exclusivos nos Estados Unidos. Portanto, 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

Saída 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 que é mais diferente das proporções de volume de vendas em todo o estado em cada condado

9. Limpar

Se você criou um projeto do Google Cloud para este tutorial, exclua-o para evitar cobranças adicionais de 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. No 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