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.
- No console do Google Cloud, na página do seletor de projetos, selecione ou crie um projeto do Google Cloud.
- Verifique se o faturamento está ativado para seu 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 no ▼ ao lado do botão +.
- Selecione Notebook Python.
- Feche o seletor de modelos.
- Selecione + Código para criar uma nova célula de código.
- Instale a versão mais recente do pacote BigQuery DataFrames na célula de código.Digite o comando a seguir.
Clique no botão Run cell ou pressione Shift + Enter para executar a célula de código.%pip install --upgrade bigframes --quiet
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:
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:
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:
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:
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:
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:
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 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:
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)
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)
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,
)
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
- 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 fornecidos pelo Colab Enterprise. Dica: acho que pedir ajuda para gerar dados de teste é muito útil.
- Confira os notebooks de exemplo para DataFrames do BigQuery no GitHub.
- Crie uma programação para executar um notebook no BigQuery Studio.
- Implante uma função remota com DataFrames do BigQuery para integrar pacotes de terceiros do Python ao BigQuery.