Используйте возможности импорта Google Sheet, чтобы быть более продуктивным

В Соединенных Штатах спорт — это большой бизнес . Примерно 154 миллиона человек смотрят спортивные трансляции хотя бы раз в месяц. И ожидается, что эти цифры будут расти с каждым годом.

Со спортивным бумом кустарные отрасли, такие как фэнтези-спорт, DFS и сайты ставок, дали фанатам еще одну причину смотреть (и становиться более вовлеченными). Эти игры позволяют проницательным зрителям зарабатывать большие деньги, перехитрив других игроков (и букмекеров), чтобы выиграть прибыльные онлайн-соревнования.

Основной способ, которым фэнтези-геймеры получают преимущество, — это использование проприетарных алгоритмов для прогнозирования результатов игроков. Массирование статистики игроков позволяет геймерам находить малоизвестные статистические жемчужины, которые они могут использовать, чтобы выиграть больше соревнований.

Сегодня мы рассмотрим, как это можно сделать с помощью Google Sheets. Мы подготовимся к гипотетическому фэнтезийному драфту, импортировав статистику игроков с общедоступного веб-сайта. Затем мы воспользуемся мощью функций для преобразования, обработки, фильтрации и сортировки этих данных по своему вкусу.

идентификатор wpchtmlp = 15056

1. Идентифицируйте свой целевой источник данных в Интернете

Участники призовых лиг фэнтези-футбола знают, как важно оценивать и ранжировать игроков перед драфтом лиги. Игроки фэнтези обычно начинают со статистики за предыдущий сезон при составлении своего рейтинга игроков. Затем они корректируют свои рейтинги игроков на основе множества факторов:

● Возможные травмы

● Позиционные бои

● Прогнозы Fantasy Point

● Правила подсчета очков в лиге

● и т. д.

Для этого примера давайте представим, что мы создаем рейтинг игроков для позиции квотербека в лиге фэнтези-футбола. Я нашел необходимые данные, опубликованные на сайте Национальной футбольной лиги.

Наша цель — импортировать эти статистические данные в таблицы Google, чтобы создать собственный рейтинг для текущего сезона.

2. Импорт табличных данных в Google Таблицы

Для импорта данных мы будем использовать функцию IMPORTHTML. Документация Google Sheets для этой функции показывает, что нам нужно предоставить три входа:

1. URL страницы для изучения
2. Запрос, который мы хотим выполнить (либо «список», либо «таблица», в зависимости от целевой структурной разметки).
3. Индекс (начиная с 1), указывающий, какую таблицу из источника HTML следует импортировать.

Мы уже определили URL-адрес целевой страницы. Запрос, который мы хотим указать, представляет собой строку «таблица», поскольку информация хранится в таблице HTML.

Примечание. Если вы хотите импортировать маркированный (или нумерованный) список, укажите «список» вместо «таблица» в качестве вторичного параметра.

Наконец, нам нужно определить, где находится наша целевая таблица (последовательно) в исходном HTML-коде самой страницы. Для этого щелкните правой кнопкой мыши в любом месте окна браузера и выберите «Просмотреть исходный код». Затем вы можете найти строку «<table», чтобы определить правильный индекс.

Поскольку наша целевая таблица является первой (и единственной) таблицей на этой странице, наш индекс будет равен 1.

Вот наша исходная формула:

=ИМПОРТHTML("https://www.nfl.com/stats/player-stats/", "таблица", 1)

После того, как мы введем формулу в A1, вот как выглядят данные:

Отлично, это было легко! Но мы можем использовать дополнительные функции Google Таблиц, чтобы улучшить этот процесс. Я вижу несколько недостатков в нашем текущем процессе:

1. Нам не нужны ВСЕ столбцы из исходной таблицы. Я хочу ограничить свои данные только теми полями, которые мне нужны для создания рейтинга.
2. Я могу указать некоторые минимальные критерии, которым должен соответствовать игрок, прежде чем я их импортирую (у меня есть стандарты!).
3. Я бы хотел, чтобы была одна дополнительная колонка, в которой подытоживались бы результаты игрока за весь сезон.
4. Поскольку я оцениваю этих игроков, лучшие игроки должны быть наверху.

Давайте посмотрим, как мы можем достичь этих целей.

3. Ограничьте количество столбцов, которые вы хотите импортировать

Нет смысла загромождать нашу электронную таблицу ненужными данными. К счастью, Google Таблицы позволяют импортировать только те столбцы, которые нам нужны.

Глядя на столбцы в нашей целевой таблице, меня интересуют только несколько столбцов.

1. Игрок (столбец 1)

2. Пройденные ярды (столбец 2)

3. ТД (столбец 7)

4. INT (столбец 8)

Эти столбцы — все данные, которые мне нужны, чтобы помочь мне ранжировать этих игроков на предстоящий сезон.

Чтобы ограничить импортируемые столбцы, нам нужно обернуть нашу функцию IMPORTHTML функцией запроса. Функция запроса принимает два параметра:

1. Данные, представляющие диапазон ячеек, для которых выполняется запрос.
2. Запрос для выполнения, написанный на языке запросов Google Visualization API.

Для параметра данных мы будем использовать всю уже созданную функцию IMPORTHTML. Для запроса мы предоставим запрос, нацеленный на столбцы, которые мы хотим включить в наш импорт.

=query(IMPORTHTML("https://www.nfl.com/stats/player-stats/", "таблица", 1), "выберите Col1, Col2, Col7, Col8")

Теперь наши импортированные данные выглядят так, без лишних столбцов!

Итак, теперь мы можем успешно импортировать желаемый набор данных, а также ограничить количество столбцов, которые будут отображаться на наших окончательных листах. Но есть еще над чем поработать.

4. Фильтрация импортированных строк на основе пользовательских критериев

В своем рейтинге я хочу учитывать только тех квотербеков, которые бросали не менее 3000 ярдов. В более общем случае я хочу отфильтровать импортированные строки на основе критериев в определенном столбце.

Чтобы создать фильтр, мы настроим нашу существующую функцию запроса, включив в нее предложение WHERE. В частности, мы укажем, что хотим импортировать только те строки, где значение в Col2 больше или равно 3000.

=query(IMPORTHTML("https://www.nfl.com/stats/player-stats/", "table", 1), "выберите Col1, Col2, Col7, Col8, где Col2 >= 3000")

Теперь, когда мы изучаем набор результатов, мы видим, что Ламар Джексон (единственный игрок с передачей менее 3000 ярдов) опущен. Удачи в следующем году!

Примечание: Ламар Джексон — жеребец, и мы обычно включаем его в наш рейтинг. Это теоретически, ребята!

5. Сгенерируйте вывод Fantasy Point с помощью вычисляемого поля

Мы успешно импортировали необработанную статистику квотербеков, обрезали столбцы по своему вкусу и отфильтровали некоторых нежелательных игроков. Это отличное начало для создания нашего пользовательского рейтинга.

Но необработанная статистика не так полезна, когда дело доходит до фэнтези-спорта. Что нам действительно нужно сделать, так это рассчитать вывод очков фантазии для каждого игрока. Это гораздо лучший показатель успеха.

И, конечно же, мы могли бы вручную добавить еще один столбец, выполнив этот расчет вручную на нашем листе. Но давайте вместо этого упростим процесс, выполнив вычисления в процессе импорта.

Разработка формулы для расчета очков фэнтези

Итак, по какой формуле мы рассчитываем количество фэнтезийных очков для квотербеков?

Оказывается, эта формула будет варьироваться от лиги к лиге в зависимости от ваших конкретных настроек подсчета очков. Но для этого урока мы будем использовать формулу, аналогичную стандартной системе подсчета очков:

Fantasy Points = (Pass Yds / 25) + (TD * 6) – (INT * 2)

Настройка запроса для включения вычисляемого поля

Чтобы интегрировать вычисляемое поле, мы изменим наш существующий запрос, включив в него вычисляемый столбец. В частности, мы хотим добавить вычисление очков фантазии в параметр запроса функции запроса.

=query(IMPORTHTML("https://www.nfl.com/stats/player-stats/", "table", 1), "выбрать Col1, Col2, Col7, Col8, (Col2/25)+(Col7* 6)-(Col8*2), где Col2 >= 3000")

Теперь наша электронная таблица содержит общую производительность фэнтези для каждого игрока (гораздо более полезная метрика).

Но этот новый заголовок столбца выглядит очень странно. Было бы намного чище, если бы мы могли применить к столбцу пользовательскую метку (например, «Очки фантазии»). l. Оказывается, это возможно, хотя синтаксис немного странный.

Что нам нужно сделать, так это добавить больше данных в конец нашего запроса:

1. Ключевое слово "этикетка"

2. Повторить рассчитанное поле

3. Заголовок желаемого столбца в виде строки

=query(IMPORTHTML("https://www.nfl.com/stats/player-stats/", "table", 1), "выбрать Col1, Col2, Col7, Col8, (Col2/25)+(Col7* 6)-(Col8*2), где Col2 >= 3000 метка (Col2/25)+(Col7*6)-(Col8*2) 'Fantasy Points'”)

Вот это больше похоже!

Эта колонка Fantasy Points выглядит намного лучше! Но я не думаю, что мне нужна десятичная часть этих чисел.

Для меня десятичные цифры добавляют ненужный фоновый шум. Итак, давайте посмотрим, сможем ли мы округлить эти цифры.

Для этого мы применим к нашему запросу пользовательский формат. В частности, нам нужно указать:

1. Ключевое слово "ФОРМАТ"

2. Повторить рассчитанное поле

3. Пользовательский формат номера (в нашем случае «#»)

=query(IMPORTHTML("https://www.nfl.com/stats/player-stats/", "table", 1), "выбрать Col1, Col2, Col7, Col8, (Col2/25)+(Col7* 6)-(Col8*2), где Col2 >= 3000 label (Col2/25)+(Col7*6)-(Col8*2) ФОРМАТ 'Fantasy Points' (Col2/25)+(Col7*6)-(Col8 *2) '#'”)

Теперь наш стол выглядит чище.

Мы прошли долгий путь и упростили процесс сбора данных. Но у нас есть еще один шаг, чтобы завершить этот урок.

Поскольку это упражнение направлено на создание рейтинга для этих игроков, было бы полезно отсортировать их при импорте данных.

Давайте поработаем над этим дальше.

6. Порядок строк на основе вычисляемого столбца

Мы снова обратимся к нашему запросу, чтобы указать начальный порядок строк для наших данных. Мы хотим отсортировать наши строки по вычисляемому столбцу («Очки фантазии»). Но мы хотим сделать это в порядке убывания.

Сортировку можно выполнить с помощью предложения ORDER BY DESC. Опять же (и, к сожалению), нам нужно будет сослаться на наше полное вычисляемое поле.

ПРИМЕЧАНИЕ. Вы не можете использовать заголовок столбца вычисляемого поля в предложении ORDER BY, потому что в порядке операций SQL псевдоним не применяется до тех пор, пока после ORDER BY.

Вот наша окончательная формула:

=query(IMPORTHTML("https://www.nfl.com/stats/player-stats/", "table", 1), "выбрать Col1, Col2, Col7, Col8, (Col2/25)+(Col7* 6)-(Col8*2), где Col2 >= 3000 ORDER BY (Col2/25)+(Col7*6)-(Col8*2) Метка DESC (Col2/25)+(Col7*6)-(Col8*2 ) ФОРМАТ 'Fantasy Points' (Col2/25)+(Col7*6)-(Col8*2) '#'”)

Наш окончательный набор данных теперь упорядочен с лучшими игроками в первую очередь (в соответствии с особыми правилами подсчета очков нашей лиги).

Функции импорта данных в Google Sheets просты, но эффективны

Независимо от того, являетесь ли вы специалистом по статистике, студентом или исследователем, универсальность функций импорта данных в Google Sheets значительно экономит время. Используя функции, подробно описанные в этой статье, вы теперь можете импортировать, фильтровать, сортировать и в дальнейшем обрабатывать любые данные, которые вы можете найти в Интернете.

И с более чем 6 миллиардами веб-страниц (и продолжает расти), похоже, вы будете очень заняты.