Eksploracyjna analiza danych o sprzedaży alkoholu w stanie Iowa za pomocą pakietu BigQuery DataFrames

1. Omówienie

W tym praktycznym module użyjesz obiektów DataFrame BigQuery z notebooka Pythona w BigQuery Studio, aby oczyścić i przeanalizować publiczny zbiór danych o sprzedaży alkoholu w stanie Iowa. Korzystaj z możliwości BigQuery ML i funkcji zdalnych, aby uzyskiwać statystyki.

Utworzysz notatnik Pythona, aby porównać sprzedaż w różnych obszarach geograficznych. Można go dostosować do pracy z dowolnymi uporządkowanymi danymi.

Cele

Z tego modułu nauczysz się, jak:

  • Aktywowanie i używanie notatników Pythona w BigQuery Studio
  • Łączenie z BigQuery za pomocą pakietu BigQuery DataFrames
  • Tworzenie regresji liniowej za pomocą BigQuery ML
  • wykonywanie złożonych agregacji i złączeń za pomocą znanej już Ci składni podobnej do tej w pandas;

2. Wymagania

  • przeglądarka, np. Chrome lub Firefox;
  • projekt Google Cloud z włączonymi płatnościami;

Zanim zaczniesz

Aby wykonać instrukcje w tym laboratorium kodu, musisz mieć projekt Google Cloud z włączonym BigQuery Studio i połączone konto rozliczeniowe.

  1. W konsoli Google Cloud na stronie selektora projektu wybierz lub utwórz projekt Google Cloud.
  2. Sprawdź, czy w projekcie Google Cloud włączone są płatności. Dowiedz się, jak sprawdzić, czy w projekcie są włączone płatności.
  3. Postępuj zgodnie z instrukcjami, aby włączyć BigQuery Studio do zarządzania zasobami.

Przygotowanie BigQuery Studio

Utwórz pusty notatnik i połącz go ze środowiskiem wykonawczym.

  1. Otwórz BigQuery Studio w konsoli Google Cloud.
  2. Kliknij ▼ obok przycisku +.
  3. Wybierz Notatnik w Pythonie.
  4. Zamknij selektor szablonów.
  5. Aby utworzyć nową komórkę kodu, kliknij + Kod.
  6. Zainstaluj najnowszą wersję pakietu BigQuery DataFrames z komórki kodu.Wpisz następujące polecenie.
    %pip install --upgrade bigframes --quiet
    Aby uruchomić komórkę kodu, kliknij przycisk Uruchom komórkę lub naciśnij Shift + Enter.

3. Czytanie publicznego zbioru danych

Zainicjuj pakiet BigQuery DataFrames, wykonując ten kod w nowej komórce kodu:

import bigframes.pandas as bpd

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

Uwaga: w tym samouczku używamy eksperymentalnego „trybu częściowego sortowania”, który umożliwia tworzenie bardziej wydajnych zapytań w połączeniu z filtracją w stylu pandas. Niektóre funkcje pakietu pandas, które wymagają ścisłej kolejności lub indeksu, mogą nie działać.

Sprawdź wersję pakietu bigframes za pomocą

bpd.__version__

Ten samouczek wymaga wersji 1.27.0 lub nowszej.

Sprzedaż detaliczna alkoholu w stanie Iowa

Zbiór danych o sprzedaży detalicznej napojów alkoholowych w stanie Iowa jest udostępniany w BigQuery w ramach programu Google Cloud dotyczącego publicznych zbiorów danych. Ten zbiór danych zawiera wszystkie zakupy hurtowe alkoholu w stanie Iowa przez sprzedawców na sprzedaż osobom fizycznym od 1 stycznia 2012 r. Dane są zbierane przez Wydział Napojów Alkoholowych w Departamencie Handlu w stanie Iowa.

W BigQuery utwórz zapytanie do tabeli bigquery-public-data.iowa_liquor_sales.sales, aby przeanalizować sprzedaż detaliczną napojów alkoholowych w stanie Iowa. Użyj metody bigframes.pandas.read_gbq(), aby utworzyć DataFrame z ciągu zapytania lub identyfikatora tabeli.

Aby utworzyć obiekt DataFrame o nazwie „df”, uruchom w nowej komórce kodu ten kod:

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

Poznawanie podstawowych informacji o ramce danych

Aby pobrać małą próbkę danych, użyj metody DataFrame.peek().

Uruchom tę komórkę:

df.peek()

Oczekiwane dane wyjściowe:

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

Uwaga: funkcja head() wymaga uporządkowania danych i jest zazwyczaj mniej wydajna niż funkcja peek(), jeśli chcesz wizualizować próbkę danych.

Podobnie jak w przypadku pakietu pandas, aby wyświetlić wszystkie dostępne kolumny i odpowiadające im typy danych, użyj właściwości DataFrame.dtypes. Są one udostępniane w sposób zgodny z biblioteką pandas.

Uruchom tę komórkę:

df.dtypes

Oczekiwane dane wyjściowe:

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

Metoda DataFrame.describe() wysyła zapytanie o podstawowe statystyki z ramy danych. Uruchom DataFrame.to_pandas(), aby pobrać te statystyki podsumowania jako strukturę DataFrame w bibliotece pandas.

Uruchom tę komórkę:

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

Oczekiwane dane wyjściowe:

	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. Wizualizowanie i oczyszczanie danych

Zbiór danych o sprzedaży alkoholu w sklepach detalicznych w stanie Iowa zawiera szczegółowe informacje geograficzne, m.in. o lokalizacji sklepów. Na podstawie tych danych możesz identyfikować trendy i różnice w różnych obszarach geograficznych.

Wizualizacja sprzedaży według kodu pocztowego

Dostępnych jest kilka wbudowanych metod wizualizacji, np. DataFrame.plot.hist(). Użyj tej metody, aby porównać sprzedaż alkoholu według kodu pocztowego.

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

Oczekiwane dane wyjściowe:

Histogram wolumenu

Wykres słupkowy pokazuje, w których kodach pocztowych sprzedano najwięcej alkoholu.

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

Oczekiwane dane wyjściowe:

Wykres słupkowy przedstawiający wolumen alkoholu w przypadku kodów pocztowych z największą sprzedażą

Oczyść dane

Niektóre kody pocztowe mają końcówkę .0. Być może gdzieś w zbiorze danych kody pocztowe zostały przypadkowo przekonwertowane na wartości zmiennoprzecinkowe. Użyj wyrażeń regularnych, aby oczyścić kody pocztowe i powtórz analizę.

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

Oczekiwane dane wyjściowe:

Wykres słupkowy przedstawiający wolumen alkoholu w przypadku kodów pocztowych z największą sprzedażą

5. Odkrywanie korelacji w sprzedaży

Dlaczego niektóre kody pocztowe sprzedają się lepiej niż inne? Jedna z hipotez mówi, że wynika to z różnic w wielkości populacji. Kod pocztowy z większą liczbą ludności prawdopodobnie sprzeda więcej alkoholu.

Przetestuj tę hipotezę, obliczając korelację między liczbą ludności a objętością sprzedaży alkoholu.

Złączanie z innymi zbiorami danych

Złącz z danymi o populacji, np. z tabulacją obszarów kodów pocztowych w ramach amerykańskiego spisu ludności (American Community Survey).

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

Badanie American Community Survey określa stany za pomocą identyfikatorów GEOID. W przypadku obszarów tabulatury kodu pocztowego identyfikator GEOID jest równy kodowi pocztowemu.

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

Utwórz wykres punktowy, aby porównać liczbę ludności w obszarze spisu ludności z kodem pocztowym z ilością sprzedanego alkoholu.

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

Oczekiwane dane wyjściowe:

Wykres punktowy obszarów podziału kodów pocztowych według liczby ludności i sprzedanych litrów alkoholu

Obliczanie korelacji

Trend wygląda na w przybliżeniu liniowy. Dopasuj do tego modelu regresji liniowej, aby sprawdzić, jak dobrze populacja może przewidywać sprzedaż alkoholu.

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)

Sprawdź, jak dobrze pasuje, używając metody score.

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

Przykładowe dane wyjściowe:

	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

Narysuj linię najlepiej pasującą do danych, ale wywołując funkcję predict w przypadku zakresu wartości populacji.

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

Oczekiwane dane wyjściowe:

Wykres punktowy z linią trendu

Rozwiązywanie problemu heteroskedastyczności

Dane na poprzednim wykresie wydają się być heterocedystyczne. Wariancja wokół linii najlepszego dopasowania rośnie wraz z liczbą osób.

Być może ilość alkoholu kupowana przez jedną osobę jest stosunkowo stała.

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

Oczekiwane dane wyjściowe:

Wykres punktowy litrów na osobę

Średnią liczbę litrów zakupionego alkoholu można obliczyć na 2 sposoby:

  1. Ile alkoholu kupuje przeciętna osoba w Iowa?
  2. Ile wynosi średnia ilość alkoholu kupowana przez osobę we wszystkich kodach pocztowych.

W przypadku (1) odzwierciedla ona ilość alkoholu kupowanego w całym stanie. W przypadku (2) odzwierciedla on średni kod pocztowy, który niekoniecznie będzie taki sam jak w przypadku (1), ponieważ różne kody pocztowe mają różne populacje.

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

Oczekiwany wynik: 87.997

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

Oczekiwany wynik: 67.139

Wykresuj te średnie w sposób podobny do opisanego powyżej.

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

Oczekiwane dane wyjściowe:

Wykres punktowy litrów na osobę

Nadal istnieją kody pocztowe, które są dość dużymi wartościami odstapiającymi, zwłaszcza w obszarach o mniejszej liczbie ludności. Możesz się domyślić, dlaczego tak jest. Być może niektóre kody pocztowe mają niską liczbę ludności, ale wysokie spożycie, ponieważ zawierają jedyny sklep z alkoholem w danym regionie. Jeśli tak, obliczenie na podstawie populacji z okolicznych kodów pocztowych może wyrównać te wartości odstające.

6. Porównanie rodzajów sprzedawanego alkoholu

Oprócz danych geograficznych baza danych o sprzedaży detalicznej alkoholu w stanie Iowa zawiera również szczegółowe informacje o sprzedawanych produktach. Być może dzięki ich analizie uda się nam odkryć różnice w preferencjach w różnych obszarach geograficznych.

Odkrywaj kategorie

Produkty są kategoryzowane w bazie danych. Ile jest kategorii?

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

Oczekiwany wynik: 103

Które kategorie są najpopularniejsze pod względem liczby wyświetleń?

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)

Wykres słupkowy przedstawiający najpopularniejsze kategorie alkoholu

Praca z typem danych TABELA

Istnieje kilka kategorii whisky, rumu, wódki itp. Chcę je jakoś pogrupować.

Zacznij od podzielenia nazw kategorii na osobne słowa za pomocą metody Series.str.split(). Rozwiń tablicę utworzoną za pomocą metody 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)

Słowa według liczby w kategoriach

category_parts.nunique()

Oczekiwany wynik: 113

Na wykresie powyżej widać, że dane dotyczące VODKA są nadal oddzielone od danych dotyczących VODKAS. Aby zwijać kategorie w mniejszy zbiór, musisz utworzyć więcej grup.

7. Korzystanie z pakietu NLTK w przypadku obiektów BigQuery DataFrames

Przy zaledwie około 100 kategorii można napisać pewne heurystyki lub nawet ręcznie utworzyć mapowanie kategorii na szerszy typ alkoholu. Można też użyć dużego modelu językowego, takiego jak Gemini, do utworzenia takiego mapowania. Aby używać BigQuery DataFrames z Gemini, skorzystaj z Codelab Pozyskiwanie informacji z nieuporządkowanych danych za pomocą BigQuery DataFrames.

Zamiast tego do przetwarzania tych danych użyj bardziej tradycyjnego pakietu do przetwarzania języka naturalnego, np. NLTK. Technologia zwana „stemmingiem” może łączyć rzeczowniki w liczbie mnogiej i pojedynczej w jedną wartość.

Używanie pakietu NLTK do tworzenia podstawy słowotwórczej

Pakiet NLTK udostępnia metody przetwarzania języka naturalnego, które można wywołać z Pythona. Zainstaluj pakiet, aby go wypróbować.

%pip install nltk

Następnie zaimportuj pakiet. Sprawdź wersję. Użyjesz go w dalszej części samouczka.

import nltk

nltk.__version__

Jednym ze sposobów standaryzacji słów jest ich „odszczupywanie”. Spowoduje to usunięcie wszystkich przyrostków, np. „s” w przypadku liczby mnogiej.

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)

Wypróbuj to na kilku słowach.

stem("WHISKEY")

Oczekiwany wynik: whiskey

stem("WHISKIES")

Oczekiwany wynik: whiski

Niestety nie udało się zmapować whisky na whisky. Rozdzielacze nie działają dobrze w przypadku nieregularnych form liczby mnogiej. Użyj lemmatizera, który wykorzystuje bardziej zaawansowane techniki do identyfikowania słowa podstawowego, zwanego „lemma”.

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

Wypróbuj to na kilku słowach.

lemmatize("WHISKIES")

Oczekiwany wynik: whisky

lemmatize("WHISKY")

Oczekiwany wynik: whisky

lemmatize("WHISKEY")

Oczekiwany wynik: whiskey

Niestety ten lemmatizer nie mapuje słowa „whiskey” do tego samego lemmatu co słowo „whiskies”. Ponieważ to słowo jest szczególnie ważne w przypadku bazy danych o sprzedaży alkoholu w stanie Iowa, ręcznie zastosuj do niego pisownię amerykańską, korzystając z słownika.

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)

Wypróbuj to na kilku słowach.

lemmatize("WHISKIES")

Oczekiwany wynik: whiskey

lemmatize("WHISKEY")

Oczekiwany wynik: whiskey

Gratulacje! Ten lematyzator powinien dobrze działać w przypadku zawężania kategorii. Aby korzystać z niego w BigQuery, musisz go wdrożyć w chmurze.

Konfigurowanie projektu na potrzeby wdrażania funkcji

Zanim wdrożysz tę funkcję w chmurze, aby BigQuery mogła uzyskać do niej dostęp, musisz wykonać jednorazową konfigurację.

Utwórz nową komórkę kodu i zamień tekst your-project-id na identyfikator projektu Google Cloud, którego używasz w tym samouczku.

project_id = "your-project-id"

Utwórz konto usługi bez żadnych uprawnień, ponieważ ta funkcja nie wymaga dostępu do żadnych zasobów w chmurze.

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)

Oczekiwany wynik: bigframes-no-permissions@your-project-id.iam.gserviceaccount.com

Utwórz zbiór danych BigQuery, który będzie przechowywać funkcję.

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)

Wdrażanie funkcji zdalnej

Włącz interfejs Cloud Functions API, jeśli nie jest jeszcze włączony.

!gcloud services enable cloudfunctions.googleapis.com

Teraz wdróż funkcję w utworzonym właśnie zbiorze danych. Dodaj dekorator @bpd.remote_function do funkcji utworzonej w poprzednich krokach.

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

Wdrożenie powinno potrwać około 2 minut.

Korzystanie z funkcji pilota

Po zakończeniu wdrażania możesz przetestować tę funkcję.

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

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

Oczekiwane dane wyjściowe:

0	whiskey
1	whiskey
2	whiskey
3	vodka
4	vodka

dtype: string

8. Porównywanie spożycia alkoholu według powiatu

Teraz, gdy funkcja lemmatize jest dostępna, możesz jej używać do łączenia kategorii.

znalezienie słowa, które najlepiej podsumowuje kategorię;

Najpierw utwórz DataFrame wszystkich kategorii w bazie danych.

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

Oczekiwane dane wyjściowe:

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

Następnie utwórz DataFrame ze wszystkimi słowami w kategoriach, z wyjątkiem kilku słów zastępczych, takich jak znaki interpunkcyjne i „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()

Oczekiwane dane wyjściowe:

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

Pamiętaj, że dzięki lemmatyzacji po zgrupowaniu zmniejszasz obciążenie funkcji w Cloud Functions. Funkcję lemmatize można zastosować do każdego z kilku milionów wierszy w bazie danych, ale będzie to droższe niż zastosowanie jej po zgrupowaniu i może wymagać zwiększenia limitu.

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

Oczekiwane dane wyjściowe:

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

Teraz, gdy słowa zostały zlemmatyzowane, musisz wybrać lemmat, który najlepiej podsumowuje kategorię. W kategoriach nie ma zbyt wielu słów funkcyjnych, dlatego zastosuj heurystyk, że jeśli słowo pojawia się w wielu innych kategoriach, prawdopodobnie lepiej nadaje się na słowo podsumowujące (np. whiskey).

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

Oczekiwane dane wyjściowe:

	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

Teraz, gdy mamy jedną lemmę podsumowującą każdą kategorię, scal ją z pierwotnym DataFrame.

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

Oczekiwane dane wyjściowe:

	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

Porównywanie powiatów

Porównaj sprzedaż w poszczególnych powiatach, aby zobaczyć, jakie są różnice.

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

Znajdź najlepiej sprzedający się produkt (lemma) w każdym kraju.

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

Oczekiwane dane wyjściowe:

	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

Czym różnią się od siebie poszczególne hrabstwa?

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

Oczekiwane dane wyjściowe:

lemma	
american	1
liqueur	1
vodka	15
whiskey	83

dtype: Int64

W większości hrabstw whisky jest najpopularniejszym produktem pod względem objętości, a w 15 hrabstwach najpopularniejsza jest wódka. Porównaj to z najpopularniejszymi rodzajami alkoholu w całym stanie.

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

Oczekiwane dane wyjściowe:

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

Whisky i wódka mają prawie taką samą objętość, przy czym wódka jest nieco mocniejsza niż whisky w całym stanie.

Porównywanie proporcji

Co jest wyjątkowego w sprzedaży w każdym hrabstwie? Co wyróżnia ten powiat na tle reszty stanu?

Użyj wskaźnika h według Cohena, aby sprawdzić, które wolumeny sprzedaży alkoholu różnią się najbardziej proporcjonalnie od wartości oczekiwanych na podstawie proporcji sprzedaży w całym stanie.

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

Teraz, gdy dla każdego leksymu zmierzono współczynnik h Cohena, znajdź w każdym hrabstwie największą różnicę w porównaniu z proporcją w całym stanie.

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

Oczekiwane dane wyjściowe:

	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

Im większa wartość współczynnika h Cohena, tym większe prawdopodobieństwo, że ilość spożywanego alkoholu danego typu różni się istotnie statystycznie od średniej w danym stanie. W przypadku mniejszych wartości dodatnich różnica w konsumpcji różni się od średniej w całym stanie, ale może być spowodowana przypadkowymi różnicami.

Uwaga: hrabstwo El Paso nie jest hrabstwem w stanie Iowa, co może oznaczać, że przed pełnym poleganiem na tych wynikach należy jeszcze wyczyścić dane.

Wizualizacja hrabstw

Złącz z tabelą bigquery-public-data.geo_us_boundaries.counties, aby uzyskać obszar geograficzny dla każdego powiatu. Nazwy hrabstw nie są unikalne w Stanach Zjednoczonych, dlatego filtruj tak, aby uwzględnić tylko hrabstwa z Iowa. Kod FIPS stanu Iowa to „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

Oczekiwane dane wyjściowe:

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

Użyj pakietu GeoPandas, aby zwizualizować te różnice na mapie.

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

Mapa alkoholu, który najbardziej różni się od proporcji sprzedaży w poszczególnych stanach

9. Czyszczenie danych

Jeśli na potrzeby tego samouczka został przez Ciebie utworzony nowy projekt Google Cloud, możesz go usunąć, aby uniknąć dodatkowych opłat za utworzone tabele lub inne zasoby.

Możesz też usunąć funkcje w Cloud Functions, konta usług i zbiory danych utworzone w ramach tego samouczka.

10. Gratulacje!

Oczyszczone i przeanalizowane dane uporządkowane za pomocą ramek danych BigQuery. W trakcie tej podróży poznaliśmy publiczne zbiory danych Google Cloud, notatniki Pythona w BigQuery Studio, uczenie maszynowe BigQuery, funkcje zdalne BigQuery oraz możliwości BigQuery DataFrames. Świetna robota!

Dalsze kroki