1. Обзор
В этом лабораторном занятии вы будете использовать кадры данных BigQuery из блокнота Python в BigQuery Studio для очистки и анализа общедоступного набора данных о продажах спиртных напитков в Айове. Используйте возможности BigQuery ML и удаленных функций для получения ценной информации.
Вы создадите блокнот Python для сравнения продаж в разных географических регионах. Его можно адаптировать для работы с любыми структурированными данными.
Цели
В ходе этой лабораторной работы вы научитесь выполнять следующие задачи:
- Активируйте и используйте блокноты Python в BigQuery Studio.
- Подключитесь к BigQuery с помощью пакета BigQuery DataFrames.
- Создайте линейную регрессию с помощью BigQuery ML.
- Выполняйте сложные агрегации и соединения, используя знакомый синтаксис, подобный пандам.
2. Требования
Прежде чем начать
Чтобы следовать инструкциям в этой лаборатории кода, вам понадобится проект Google Cloud с включенной BigQuery Studio и подключенный платежный аккаунт.
- В консоли Google Cloud на странице выбора проекта выберите или создайте проект Google Cloud.
- Убедитесь, что для вашего проекта Google Cloud включена оплата. Узнайте, как проверить, включена ли оплата в проекте.
- Следуйте инструкциям, чтобы включить BigQuery Studio для управления активами .
Подготовьте BigQuery Studio
Создайте пустой блокнот и подключите его к среде выполнения.
- Перейдите в BigQuery Studio в Google Cloud Console.
- Нажмите ▼ рядом с кнопкой + .
- Выберите блокнот Python .
- Закройте селектор шаблонов.
- Выберите + Код , чтобы создать новую ячейку кода.
- Установите последнюю версию пакета BigQuery DataFrames из ячейки кода. Введите следующую команду.
%pip install --upgrade bigframes --quiet
3. Прочтите общедоступный набор данных
Инициализируйте пакет BigQuery DataFrames, выполнив следующую команду в новой ячейке кода:
import bigframes.pandas as bpd
bpd.options.bigquery.ordering_mode = "partial"
bpd.options.display.repr_mode = "deferred"
Примечание. В этом руководстве мы используем экспериментальный «режим частичного упорядочения», который позволяет выполнять более эффективные запросы при использовании с фильтрацией, подобной пандам. Некоторые функции pandas, требующие строгого порядка или индексации, могут не работать.
Проверьте версию пакета bigframes с помощью
bpd.__version__
Для этого руководства требуется версия 1.27.0 или более поздняя.
Розничные продажи спиртных напитков в Айове
Набор данных о розничных продажах спиртных напитков в Айове предоставляется в BigQuery через программу общедоступных наборов данных Google Cloud . Этот набор данных содержит все оптовые закупки спиртных напитков в штате Айова розничными торговцами для продажи частным лицам с 1 января 2012 года. Данные собираются Отделом алкогольных напитков Министерства торговли Айовы.
В BigQuery запросите bigquery-public-data.iowa_liquor_sales.sales, чтобы проанализировать розничные продажи спиртных напитков в Айове. Используйте метод bigframes.pandas.read_gbq() , чтобы создать DataFrame из строки запроса или идентификатора таблицы.
Выполните следующую команду в новой ячейке кода, чтобы создать DataFrame с именем «df»:
df = bpd.read_gbq_table("bigquery-public-data.iowa_liquor_sales.sales")
Откройте для себя основную информацию о DataFrame
Используйте метод DataFrame.peek() , чтобы загрузить небольшой образец данных.
Запустите эту ячейку:
df.peek()
Ожидаемый результат:
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 ...
Примечание. head() требует упорядочивания и, как правило, менее эффективна, чем peek() если вы хотите визуализировать выборку данных.
Как и в случае с пандами, используйте свойство DataFrame.dtypes чтобы просмотреть все доступные столбцы и соответствующие им типы данных. Они предоставляются совместимым с pandas способом.
Запустите эту ячейку:
df.dtypes
Ожидаемый результат:
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
Метод DataFrame.describe() запрашивает некоторую базовую статистику из DataFrame. Запустите DataFrame.to_pandas() чтобы загрузить эту сводную статистику в виде DataFrame pandas.
Запустите эту ячейку:
df.describe("all").to_pandas()
Ожидаемый результат:
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. Визуализируйте и очистите данные
Набор данных о розничных продажах спиртных напитков в Айове предоставляет подробную географическую информацию, в том числе о том, где расположены розничные магазины. Используйте эти данные для выявления тенденций и различий между географическими регионами.
Визуализируйте продажи по почтовому индексу
Существует несколько встроенных методов визуализации, таких как DataFrame.plot.hist() . Используйте этот метод для сравнения продаж спиртных напитков по почтовому индексу.
volume_by_zip = df.groupby("zip_code").agg({"volume_sold_liters": "sum"})
volume_by_zip.plot.hist(bins=20)
Ожидаемый результат:
Используйте гистограмму, чтобы увидеть, в каких точках продаж продается больше всего алкоголя.
(
volume_by_zip
.sort_values("volume_sold_liters", ascending=False)
.head(25)
.to_pandas()
.plot.bar(rot=80)
)
Ожидаемый результат:
Очистить данные
Некоторые почтовые индексы заканчиваются .0 . Возможно, где-то в процессе сбора данных почтовые индексы были случайно преобразованы в значения с плавающей запятой. Используйте регулярные выражения для очистки почтовых индексов и повторите анализ.
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)
)
Ожидаемый результат:
5. Обнаружьте корреляции в продажах
Почему некоторые почтовые индексы продаются больше, чем другие? Одна из гипотез заключается в том, что это связано с различиями в размерах населения. В почтовом индексе с большим населением, скорее всего, будет продаваться больше спиртных напитков.
Проверьте эту гипотезу, рассчитав корреляцию между населением и объемом продаж спиртных напитков.
Присоединяйтесь к другим наборам данных
Присоединяйтесь к набору данных о населении, такому как исследование области табулирования почтовых индексов , проведенное Бюро переписи населения США .
census_acs = bpd.read_gbq_table("bigquery-public-data.census_bureau_acs.zcta_2020_5yr")
Исследование американского сообщества определяет штаты по GEOID. В случае областей таблицы почтовых индексов GEOID равен почтовому индексу.
volume_by_pop = volume_by_zip.join(
census_acs.set_index("geo_id")
)
Создайте диаграмму рассеяния, чтобы сравнить численность населения области табуляции почтовых индексов с количеством проданных литров алкоголя.
(
volume_by_pop[["volume_sold_liters", "total_pop"]]
.to_pandas()
.plot.scatter(x="total_pop", y="volume_sold_liters")
)
Ожидаемый результат:
Вычислить корреляции
Тенденция выглядит примерно линейной. Подберите к этому модель линейной регрессии, чтобы проверить, насколько хорошо население может прогнозировать продажи спиртных напитков.
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)
Проверьте, насколько хорошо подходит, используя метод score .
model.score(feature_columns, label_columns).to_pandas()
Пример вывода:
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
Нарисуйте линию наилучшего соответствия, но вызовите функцию predict для диапазона значений совокупности.
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",
)
Ожидаемый результат:
Решение проблемы гетероскедастичности
Данные на предыдущей диаграмме кажутся гетероскедастическими. Отклонение вокруг линии наилучшего соответствия растет вместе с численностью населения.
Возможно, количество покупаемого алкоголя на человека относительно постоянно.
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")
)
Ожидаемый результат:
Рассчитайте среднее количество литров купленного алкоголя двумя разными способами:
- Какое среднее количество алкоголя покупается на человека в Айове?
- Каково среднее количество приобретенного алкоголя на человека по всем почтовым индексам?
В (1) отражается, сколько алкоголя покупается во всем штате. В (2) он отражает средний почтовый индекс, который не обязательно будет таким же, как (1), поскольку разные почтовые индексы имеют разное население.
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
Ожидаемый результат: 87.997
average_per_zip = volume_per_pop["liters_per_pop"].mean()
average_per_zip
Ожидаемый результат: 67.139
Постройте эти средние значения, как указано выше.
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")
Ожидаемый результат:
Есть еще некоторые почтовые индексы, которые довольно сильно выделяются, особенно в районах с меньшим населением. Остается в качестве упражнения выдвинуть гипотезу, почему это так. Например, может случиться так, что в некоторых почтовых индексах мало населения, но высокий уровень потребления, поскольку в этом районе находится единственный винный магазин. Если это так, расчет на основе численности населения окружающих почтовых индексов может сгладить эти выбросы.
6. Сравнение видов продаваемых спиртных напитков
Помимо географических данных, база данных розничных продаж спиртных напитков штата Айова также содержит подробную информацию о проданном товаре. Возможно, проанализировав их, мы сможем выявить различия во вкусах в разных географических регионах.
Исследуйте категории
В базе данных элементы распределены по категориям. Сколько категорий существует?
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()
Ожидаемый результат: 103
Какие категории наиболее популярны по объему?
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)
Работа с типом данных ARRAY.
Есть несколько категорий: виски, ром, водка и многое другое. Я хотел бы как-то сгруппировать их вместе.
Начните с разделения названий категорий на отдельные слова с помощью метода Series.str.split() . Отмените вложение созданного массива с помощью метода 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()
Ожидаемый результат: 113
Глядя на диаграмму выше, видно, что в данных по-прежнему ВОДКА отделена от ВОДКИ. Чтобы свернуть категории в меньший набор, необходима дополнительная группировка.
7. Использование NLTK с кадрами данных BigQuery
Имея всего около 100 категорий, было бы возможно написать некоторую эвристику или даже вручную создать сопоставление категории с более широким типом спиртных напитков. В качестве альтернативы для создания такого отображения можно использовать большую языковую модель, такую как Gemini. Попробуйте кодовую лабораторию . Получите ценную информацию из неструктурированных данных с помощью BigQuery DataFrames , чтобы использовать BigQuery DataFrames с Gemini.
Вместо этого для обработки этих данных используйте более традиционный пакет обработки естественного языка NLTK. Например, технология под названием «стеммер» может объединять существительные во множественном и единственном числе в одно и то же значение.
Использование NLTK для определения слов
Пакет NLTK предоставляет методы обработки естественного языка, доступные из Python. Установите пакет, чтобы опробовать его.
%pip install nltk
Затем импортируйте пакет. Проверьте версию. Он будет использоваться позже в уроке.
import nltk
nltk.__version__
Один из способов стандартизации слов, чтобы «ограничить» слово. При этом удаляются любые суффиксы, например, завершающая буква «s» для множественного числа.
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)
Попробуйте это на нескольких словах.
stem("WHISKEY")
Ожидаемый результат: whiskey
stem("WHISKIES")
Ожидаемый результат: whiski
К сожалению, это не отождествляло виски с виски. Стеммеры плохо работают с неправильными формами множественного числа. Попробуйте лемматизатор, который использует более сложные методы для определения основного слова, называемого «леммой».
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())
Попробуйте это на нескольких словах.
lemmatize("WHISKIES")
Ожидаемый результат: whisky
lemmatize("WHISKY")
Ожидаемый результат: whisky
lemmatize("WHISKEY")
Ожидаемый результат: whiskey
К сожалению, этот лемматизатор не сопоставляет «виски» с той же леммой, что и «виски». Поскольку это слово особенно важно для базы данных розничных продаж спиртных напитков штата Айова, вручную сопоставьте его с американским написанием с помощью словаря.
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)
Попробуйте это на нескольких словах.
lemmatize("WHISKIES")
Ожидаемый результат: whiskey
lemmatize("WHISKEY")
Ожидаемый результат: whiskey
Поздравляю! Этот лемматизатор должен хорошо работать для сужения категорий. Чтобы использовать его с BigQuery, необходимо развернуть его в облаке.
Настройте свой проект для развертывания функций
Прежде чем развернуть это в облаке, чтобы BigQuery мог получить доступ к этой функции, вам необходимо выполнить однократную настройку.
Создайте новую ячейку кода и замените your-project-id на идентификатор проекта Google Cloud, который вы используете для этого руководства.
project_id = "your-project-id"
Создайте учетную запись службы без каких-либо разрешений, поскольку для этой функции не требуется доступ к каким-либо облачным ресурсам.
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)
Ожидаемый результат: bigframes-no-permissions@your-project-id.iam.gserviceaccount.com .
Создайте набор данных BigQuery для хранения функции.
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)
Развертывание удаленной функции
Включите API облачных функций, если он еще не включен.
!gcloud services enable cloudfunctions.googleapis.com
Теперь разверните свою функцию в только что созданном наборе данных. Добавьте декоратор @bpd.remote_function к функции, созданной на предыдущих шагах.
@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)
Развертывание должно занять около двух минут.
Использование удаленных функций
После завершения развертывания вы можете протестировать эту функцию.
lemmatize = bpd.read_gbq_function(f"{project_id}.functions.lemmatize")
words = bpd.Series(["whiskies", "whisky", "whiskey", "vodkas", "vodka"])
words.apply(lemmatize).to_pandas()
Ожидаемый результат:
0 whiskey
1 whiskey
2 whiskey
3 vodka
4 vodka
dtype: string
8. Сравнение потребления алкоголя по округам
Теперь, когда доступна функция lemmatize , используйте ее для объединения категорий.
Найдите слово, которое лучше всего характеризует категорию
Сначала создайте DataFrame всех категорий в базе данных.
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()
Ожидаемый результат:
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
Затем создайте DataFrame для всех слов в категориях, за исключением нескольких слов-заполнителей, таких как знаки препинания и «элемент».
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()
Ожидаемый результат:
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
Обратите внимание: лемматизируя после группировки, вы снижаете нагрузку на свою облачную функцию. Можно применить функцию лемматизации к каждой из нескольких миллионов строк в базе данных, но это будет стоить дороже, чем ее применение после группировки, и может потребовать увеличения квоты.
lemmas = words.assign(lemma=lambda _: _["word"].apply(lemmatize))
lemmas.to_pandas()
Ожидаемый результат:
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
Теперь, когда слова лемматизированы, вам нужно выбрать лемму, которая лучше всего обобщает категорию. Поскольку в категориях не так много служебных слов, используйте эвристику, согласно которой, если слово встречается в нескольких других категориях, оно, скорее всего, будет лучше в качестве обобщающего слова (например, виски).
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()
Ожидаемый результат:
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
Теперь, когда есть одна лемма, обобщающая каждую категорию, объедините ее с исходным DataFrame.
df_with_lemma = df.merge(
categories_mapping,
on="category_name",
how="left"
)
df_with_lemma[df_with_lemma['category_name'].notnull()].peek()
Ожидаемый результат:
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
Сравнение округов
Сравните продажи в каждом округе, чтобы увидеть, в чем различия.
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"))
)
Найдите самый продаваемый товар (лемма) в каждом округе.
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()
Ожидаемый результат:
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
Насколько округа отличаются друг от друга?
county_max_lemma.groupby("lemma").size().to_pandas()
Ожидаемый результат:
lemma
american 1
liqueur 1
vodka 15
whiskey 83
dtype: Int64
В большинстве округов виски является самым популярным продуктом по объему, а водка наиболее популярна в 15 округах. Сравните это с самыми популярными видами спиртных напитков по всему штату.
total_liters = (
df_with_lemma
.groupby("lemma")
.agg({"volume_sold_liters": "sum"})
.sort_values("volume_sold_liters", ascending=False)
)
total_liters.to_pandas()
Ожидаемый результат:
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
...
Виски и водка имеют почти одинаковый объем, причем водки немного больше, чем виски по всему штату.
Сравнение пропорций
Что уникального в продажах в каждом округе? Чем этот округ отличается от остального штата?
Используйте показатель Коэна h, чтобы определить, какие объемы продаж спиртных напитков наиболее пропорционально отличаются от ожидаемых, исходя из доли продаж в масштабе штата.
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"))
)
Теперь, когда h Коэна измерен для каждой леммы, найдите наибольшее отличие от пропорции по всему штату в каждом округе.
# 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()
Ожидаемый результат:
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
Чем больше значение h Коэна, тем более вероятно, что существует статистически значимая разница в количестве потребляемого этого типа алкоголя по сравнению со средними показателями по штату. Для меньших положительных значений разница в потреблении отличается от среднего показателя по штату, но это может быть связано со случайными различиями.
Небольшое отступление: округ ЭЛЬ-ПАСО, похоже, не является округом Айовы, это может указывать на еще одну необходимость очистки данных, прежде чем полностью зависеть от этих результатов.
Визуализация округов
Присоединитесь к таблице bigquery-public-data.geo_us_boundaries.counties чтобы получить географическую область для каждого округа. Названия округов не уникальны в Соединенных Штатах, поэтому отфильтруйте их, чтобы включить только округа из Айовы. Код FIPS для Айовы — «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
Ожидаемый результат:
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
Используйте GeoPandas, чтобы визуализировать эти различия на карте.
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. Очистка
Если вы создали новый проект Google Cloud для этого руководства, вы можете удалить его , чтобы предотвратить дополнительную плату за созданные таблицы или другие ресурсы.
Либо удалите облачные функции, учетные записи служб и наборы данных, созданные для этого руководства.
10. Поздравляем!
Вы очистили и проанализировали структурированные данные с помощью BigQuery DataFrames. Попутно вы изучили общедоступные наборы данных Google Cloud, блокноты Python в BigQuery Studio, BigQuery ML, удаленные функции BigQuery и возможности BigQuery DataFrames. Фантастическая работа!
Следующие шаги
- Примените эти шаги к другим данным, например к базе данных названий США .
- Попробуйте сгенерировать код Python в своем блокноте . Блокноты Python в BigQuery Studio работают на платформе Colab Enterprise. Подсказка: я считаю, что просьба о помощи в создании тестовых данных весьма полезна.
- Изучите примеры блокнотов для BigQuery DataFrames на GitHub.
- Создайте расписание для запуска блокнота в BigQuery Studio .
- Разверните удаленную функцию с помощью BigQuery DataFrames , чтобы интегрировать сторонние пакеты Python с BigQuery.