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

1. Przegląd

W tym module użyjesz BigQuery DataFrames z notatnika Pythona w BigQuery Studio, aby oczyścić i przeanalizować publiczny zbiór danych dotyczący sprzedaży alkoholu w stanie Iowa. Wykorzystaj możliwości BigQuery ML i funkcji zdalnych, aby odkrywać statystyki.

Utworzysz notatnik w Pythonie, aby porównać sprzedaż w różnych obszarach geograficznych. Można go dostosować do dowolnych danych strukturalnych.

Cele

Z tego modułu nauczysz się, jak:

Aktywowanie i używanie notatników w Pythonie w BigQuery Studio
Łączenie z BigQuery za pomocą pakietu BigQuery DataFrames
Tworzenie regresji liniowej za pomocą BigQuery ML
wykonywać złożone agregacje i złączenia przy użyciu znanej składni podobnej do składni biblioteki pandas,

2. Wymagania

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

Zanim zaczniesz

Aby wykonać instrukcje podane w tym samouczku, musisz mieć projekt Google Cloud z włączoną usługą BigQuery Studio i połączonym kontem rozliczeniowym.

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

Przygotowywanie BigQuery Studio

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

W konsoli Google Cloud otwórz BigQuery Studio.
Kliknij ▼ obok przycisku +.
Wybierz Notatnik w Pythonie.
Zamknij selektor szablonów.
Aby utworzyć nową komórkę z kodem, kliknij + Kod.
Zainstaluj najnowszą wersję pakietu BigQuery DataFrames z komórki kodu. Wpisz to polecenie:
```
%pip install --upgrade bigframes --quiet
```
Kliknij przycisk Uruchom komórkę lub naciśnij Shift + Enter, aby uruchomić komórkę kodu.

3. Odczytywanie publicznego zbioru danych

Zainicjuj pakiet BigQuery DataFrames, uruchamiając w nowej komórce kodu to polecenie:

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 porządkowania”, który w połączeniu z filtrowaniem podobnym do tego w bibliotece pandas umożliwia wykonywanie bardziej wydajnych zapytań. Niektóre funkcje biblioteki pandas, które wymagają ścisłego uporządkowania lub indeksowania, mogą nie działać.

Sprawdź wersję pakietu bigframes za pomocą

bpd.__version__

Ten samouczek wymaga wersji 1.27.0 lub nowszej.

Sprzedaż alkoholu w amerykańskim stanie Iowa

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

W BigQuery wyślij zapytanie do tabeli bigquery-public-data.iowa_liquor_sales.sales, aby przeanalizować sprzedaż detaliczną alkoholu w stanie Iowa. Użyj metody bigframes.pandas.read_gbq(), aby utworzyć obiekt DataFrame na podstawie ciągu zapytania lub identyfikatora tabeli.

Aby utworzyć ramkę danych o nazwie „df”, uruchom w nowej komórce kodu to polecenie:

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

Poznaj podstawowe informacje o obiekcie DataFrame

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: head() wymaga uporządkowania i jest zwykle mniej wydajne niż peek(), jeśli chcesz wizualizować próbkę danych.

Podobnie jak w przypadku biblioteki pandas, użyj właściwości DataFrame.dtypes, aby wyświetlić wszystkie dostępne kolumny i odpowiadające im typy danych. 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 obiektu DataFrame. Uruchom DataFrame.to_pandas(), aby pobrać te statystyki podsumowujące jako strukturę DataFrame biblioteki 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 amerykańskim stanie Iowa zawiera szczegółowe informacje geograficzne, w tym lokalizację sklepów detalicznych. Na podstawie tych danych możesz identyfikować trendy i różnice w poszczególnych 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 wolumenów

Użyj wykresu słupkowego, aby sprawdzić, 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 ilości alkoholu w najchętniej kupowanych kodach pocztowych

Oczyść dane

Niektóre kody pocztowe mają na końcu symbol .0. Być może na etapie zbierania 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 ilości alkoholu w najchętniej kupowanych kodach pocztowych

5. Odkrywanie korelacji w sprzedaży

Dlaczego w niektórych kodach pocztowych sprzedaż jest wyższa niż w innych? Jedna z hipotez głosi, że jest to spowodowane różnicami w wielkości populacji. Na obszarze o większej populacji prawdopodobnie sprzedaje się więcej alkoholu.

Sprawdź tę hipotezę, obliczając korelację między populacją a wielkością sprzedaży alkoholu.

Złączanie z innymi zbiorami danych

Połącz z zbiorem danych o populacji, np. z badaniem American Community Survey amerykańskiego biura ewidencji ludności dotyczącym obszarów kodów pocztowych.

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

American Community Survey identyfikuje stany za pomocą GEOID. W przypadku obszarów obliczania kodów pocztowych 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ć populację obszarów kodów pocztowych z liczbą litrów 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 statystycznych kodów pocztowych według populacji i litrów sprzedanego alkoholu

Obliczanie korelacji

Trend wygląda na mniej więcej liniowy. Dopasuj do tych danych model regresji liniowej, aby sprawdzić, jak dobrze liczba ludności może prognozować 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ź dopasowanie za pomocą 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ę najlepszego dopasowania, 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

Radzenie sobie z heteroskedastycznością

Dane na poprzednim wykresie wydają się heteroskedastyczne. Wariancja wokół linii najlepszego dopasowania rośnie wraz z liczebnością populacji.

Być może ilość alkoholu kupowanego na 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 populację

Oblicz średnią liczbę litrów kupionego alkoholu na 2 sposoby:

Jaka jest średnia ilość alkoholu kupowanego na osobę w stanie Iowa?
Jaka jest średnia ilość alkoholu kupowanego na osobę we wszystkich kodach pocztowych?

W przypadku (1) pokazuje, ile alkoholu kupiono 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óżną liczbę mieszkańców.

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

Oczekiwane dane wyjściowe: 87.997

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

Oczekiwane dane wyjściowe: 67.139

Wykreśl te średnie wartości, podobnie jak 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 populację

Niektóre kody pocztowe nadal są dość duże, zwłaszcza na obszarach o mniejszej populacji. Pozostawiamy Ci spekulacje na temat tego, dlaczego tak się dzieje. Może na przykład niektóre kody pocztowe mają małą populację, ale dużą konsumpcję, ponieważ znajduje się w nich jedyny sklep z alkoholem w okolicy. W takim przypadku obliczenia na podstawie populacji okolicznych kodów pocztowych mogą 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 też szczegółowe informacje o sprzedawanych produktach. Być może dzięki analizie tych danych uda nam się wykryć różnice w gustach w różnych obszarach geograficznych.

Odkrywaj kategorie

Produkty są podzielone na kategorie 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()

Oczekiwane dane wyjściowe: 103

Które kategorie są najpopularniejsze pod względem liczby?

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 sprzedawanych alkoholi

Praca z typem danych ARRAY

W każdej z kategorii (whisky, rum, wódka itp.) znajdziesz kilka rodzajów. Chciałbym je jakoś pogrupować.

Zacznij od podzielenia nazw kategorii na poszczególne słowa za pomocą metody Series.str.split(). Rozpakuj utworzoną tablicę 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 z kategorii

category_parts.nunique()

Oczekiwane dane wyjściowe: 113

Na wykresie powyżej widać, że dane nadal zawierają oddzielne wartości VODKA i VODKAS. Aby zwinąć kategorie do mniejszego zestawu, potrzebne jest dodatkowe grupowanie.

7. Używanie NLTK z BigQuery DataFrames

Przy zaledwie 100 kategoriach 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, aby utworzyć takie mapowanie. Wypróbuj codelab Uzyskiwanie statystyk z danych nieustrukturyzowanych za pomocą BigQuery DataFrames, aby używać BigQuery DataFrames z Gemini.

Zamiast tego użyj bardziej tradycyjnego pakietu do przetwarzania języka naturalnego NLTK, aby przetworzyć te dane. Technologia zwana „stemmerem” może na przykład łączyć rzeczowniki w liczbie pojedynczej i mnogiej w tę samą wartość.

Używanie NLTK do skracania słów

Pakiet NLTK udostępnia metody przetwarzania języka naturalnego, do których można uzyskać dostęp z poziomu Pythona. Zainstaluj pakiet, aby go wypróbować.

%pip install nltk

Następnie zaimportuj pakiet. Sprawdź wersję. Będzie on potrzebny w dalszej części samouczka.

import nltk

nltk.__version__

Jednym ze sposobów standaryzacji słów jest „rdzeń” słowa. Spowoduje to usunięcie wszystkich sufiksów, np. końcówki „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)

Spróbuj na kilku słowach.

stem("WHISKEY")

Oczekiwane dane wyjściowe: whiskey

stem("WHISKIES")

Oczekiwane dane wyjściowe: whiski

Niestety nie udało się przypisać słowa „whiskies” do słowa „whiskey”. Stemery nie działają dobrze w przypadku nieregularnych form liczby mnogiej. Spróbuj użyć lemmatyzatora, który wykorzystuje bardziej zaawansowane techniki do identyfikowania słowa podstawowego, zwanego „lematem”.

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

Spróbuj na kilku słowach.

lemmatize("WHISKIES")

Oczekiwane dane wyjściowe: whisky

lemmatize("WHISKY")

Oczekiwane dane wyjściowe: whisky

lemmatize("WHISKEY")

Oczekiwane dane wyjściowe: whiskey

Niestety ten lemmatyzator nie przypisuje słowu „whiskey” tego samego lematu co słowu „whiskies”. Ponieważ to słowo jest szczególnie ważne w bazie danych o sprzedaży alkoholu w amerykańskim stanie Iowa, ręcznie przypisz je do amerykańskiej pisowni za pomocą 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)

Spróbuj na kilku słowach.

lemmatize("WHISKIES")

Oczekiwane dane wyjściowe: whiskey

lemmatize("WHISKEY")

Oczekiwane dane wyjściowe: whiskey

Gratulacje! Ten lemmatyzator powinien dobrze sprawdzać się w zawężaniu kategorii. Aby używać go z BigQuery, musisz wdrożyć go w chmurze.

Konfigurowanie projektu na potrzeby wdrażania funkcji

Zanim wdrożysz tę funkcję w chmurze, aby BigQuery mógł z niej korzystać, musisz przeprowadzić jednorazową konfigurację.

Utwórz nową komórkę kodu i zastąp your-project-id identyfikatorem projektu Google Cloud, którego używasz w tym samouczku.

project_id = "your-project-id"

Utwórz konto usługi bez uprawnień, ponieważ ta funkcja nie potrzebuje 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)

Oczekiwane dane wyjściowe: bigframes-no-permissions@your-project-id.iam.gserviceaccount.com

Utwórz zbiór danych BigQuery, w którym będzie przechowywana funkcja.

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 przez siebie 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 zająć 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 w poszczególnych hrabstwach

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

Znajdowanie słowa, które najlepiej podsumowuje kategorię

Najpierw utwórz ramkę danych ze wszystkimi kategoriami 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 ramkę danych ze wszystkimi słowami w kategoriach z wyjątkiem kilku słów wypełniających, takich jak znaki interpunkcyjne i „produkt”.

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 lematyzacja po grupowaniu zmniejsza obciążenie funkcji w Cloud Functions. Funkcję lemmatyzacji można zastosować do każdego z kilku milionów wierszy w bazie danych, ale będzie to kosztować więcej 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

Po przeprowadzeniu lematyzacji musisz wybrać lemat, który najlepiej podsumowuje kategorię. W kategoriach nie ma zbyt wielu słów funkcyjnych, więc użyj heurystyki, która mówi, że jeśli słowo pojawia się w wielu innych kategoriach, prawdopodobnie lepiej nadaje się jako słowo podsumowujące (np. 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()

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 już pojedynczy lemat podsumowujący każdą kategorię, połącz go z oryginalną ramką danych.

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 hrabstw

Porównaj sprzedaż w poszczególnych hrabstwach, aby zobaczyć 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ź najczęściej sprzedawany produkt (lemat) w każdym hrabstwie.

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

Jak bardzo 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 najpopularniejszym produktem pod względem ilości jest whisky, a w 15 hrabstwach – 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ą niemal taką samą objętość, przy czym wódka jest nieco popularniejsza w całym stanie.

Porównywanie proporcji

Co wyróżnia sprzedaż w poszczególnych hrabstwach? Co odróżnia hrabstwo od reszty stanu?

Użyj miary h Cohena, aby sprawdzić, które ilości sprzedaży alkoholu różnią się najbardziej proporcjonalnie od 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"))
)

Po zmierzeniu wartości h Cohena dla każdego lematu znajdź największą różnicę w stosunku do proporcji w całym stanie w każdym hrabstwie.

# 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 jest wartość h Cohena, tym większe jest prawdopodobieństwo, że istnieje istotna statystycznie różnica w ilości spożywanego alkoholu danego rodzaju w porównaniu ze średnimi wartościami dla danego stanu. W przypadku mniejszych wartości dodatnich różnica w zużyciu jest inna niż średnia w całym stanie, ale może to wynikać z przypadkowych różnic.

Uwaga: hrabstwo EL PASO nie wydaje się być hrabstwem w stanie Iowa. Może to oznaczać, że przed pełnym poleganiem na tych wynikach należy jeszcze oczyścić dane.

Wizualizacja hrabstw

Połącz z bigquery-public-data.geo_us_boundaries.counties tabelą, aby uzyskać obszar geograficzny każdego hrabstwa. Nazwy hrabstw nie są unikalne w Stanach Zjednoczonych, więc odfiltruj dane, 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 GeoPandas, aby wizualizować 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 w poszczególnych hrabstwach najbardziej różni się od proporcji sprzedaży w całym stanie.

9. Czyszczenie danych

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

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

10. Gratulacje!

Oczyszczono i przeanalizowano dane strukturalne za pomocą BigQuery DataFrames. Po drodze poznasz publiczne zbiory danych Google Cloud, notatniki Pythona w BigQuery Studio, BigQuery ML, funkcje zdalne BigQuery i możliwości BigQuery DataFrames. Świetna robota!

Dalsze kroki

Zastosuj te kroki do innych danych, np. bazy danych z imionami w USA.
Spróbuj wygenerować kod w Pythonie w notatniku. Notatniki w Pythonie w BigQuery Studio są oparte na Colab Enterprise. Wskazówka: proszenie o pomoc w generowaniu danych testowych jest bardzo przydatne.
Zapoznaj się z przykładowymi notatnikami BigQuery DataFrames w GitHubie.
Utwórz harmonogram uruchamiania notatnika w BigQuery Studio.
Wdróż funkcję zdalną z BigQuery DataFrames, aby zintegrować pakiety Pythona innych firm z BigQuery.