Informacje o tym ćwiczeniu (w Codelabs)
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
Zanim zaczniesz
Aby wykonać instrukcje w tym laboratorium kodu, musisz mieć projekt Google Cloud z włączonym BigQuery Studio i połączone konto rozliczeniowe.
- 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 są włączone płatności.
- 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.
- Otwórz BigQuery Studio w konsoli Google Cloud.
- Kliknij ▼ obok przycisku +.
- Wybierz Notatnik w Pythonie.
- Zamknij selektor szablonów.
- Aby utworzyć nową komórkę kodu, kliknij + Kod.
- Zainstaluj najnowszą wersję pakietu BigQuery DataFrames z komórki kodu.Wpisz następujące polecenie.
%pip install --upgrade bigframes --quiet
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:
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:
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:
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:
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:
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:
Średnią liczbę litrów zakupionego alkoholu można obliczyć na 2 sposoby:
- Ile alkoholu kupuje przeciętna osoba w Iowa?
- 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:
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)
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)
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,
)
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
- Zastosuj te czynności do innych danych, np. do bazy danych z imionami w USA.
- Spróbuj wygenerować kod Pythona w notatniku. Notatniki w Pythonie w BigQuery Studio są obsługiwane przez Colab Enterprise. Wskazówka: prośba o pomoc w generowaniu danych testowych może być bardzo przydatna.
- Zapoznaj się na GitHubie z przykładowymi notatnikami dla BigQuery DataFrames.
- utworzyć harmonogram wykonywania notatnika w BigQuery Studio.
- Wdróż funkcję zdalną z BigQuery DataFrames, aby zintegrować zewnętrzne pakiety Pythona z BigQuery.