Использование расширенных операций MySQL для анализа данных веб-парсера Python

Чтобы следовать этому руководству, у вас должно быть следующее:

Python 3.7 или новее.
Арктайп
Базовое понимание SQL.
Текстовый редактор.

Установка необходимых библиотек

Для этого урока необходимы следующие библиотеки:

numpy - фундаментальный пакет для научных вычислений с Python
pandas - библиотека, предоставляющая высокопроизводительные, простые в использовании структуры данных и инструменты анализа данных
request - единственная HTTP-библиотека без ГМО для Python, безопасная для употребления человеком. (люблю эту строку из официальных документов: D)
BeautifulSoup - библиотека Python для извлечения данных из файлов HTML и XML.

Чтобы установить библиотеки, необходимые для этого руководства, выполните следующие команды:

pip install numpy
pip install pandas
pip install requests
pip install bs4

Сборка Python Web Scraper

Теперь, когда у нас установлены все необходимые библиотеки, приступим к созданию нашего парсера.

Импорт библиотек Python

import numpy as np
import pandas as pd
import requests
from bs4 import BeautifulSoup
import json

Проведение исследования сайта

Первым шагом в любом проекте по очистке веб-страниц является исследование веб-страницы, которую вы хотите очистить, и изучение того, как она работает. Это очень важно для того, чтобы найти, где взять данные с сайта. В этом руководстве мы будем использовать http://understat.com.

На главной странице мы видим, что на сайте есть данные по шести европейским лигам. Однако мы будем извлекать данные только по пяти лучшим лигам (командам без РФПЛ).

Мы также можем заметить, что данные на сайте начинаются с 2014/2015 по 2020/2021. Давайте создадим переменные для обработки только необходимых нам данных.

# create urls for all seasons of all leagues
base_url = 'https://understat.com/league'
leagues = ['La_liga', 'EPL', 'Bundesliga', 'Serie_A', 'Ligue_1']
seasons = ['2016', '2017', '2018', '2019', '2020']

Следующий шаг - выяснить, где хранятся данные на веб-странице. Для этого откройте Инструменты разработчика в Chrome, перейдите на вкладку «Сеть», найдите файл данных (в данном примере 2018 г.) и выберите вкладку «Ответ». Вот что мы получим после выполнения запросов.

После просмотра содержимого веб-страницы мы обнаружили, что данные сохраняются под элементом «скрипт» в переменной teamsData и закодированы в формате JSON. В результате нам нужно отследить этот тег, извлечь из него JSON и преобразовать его в структуру данных, читаемую Python.

Расшифровка данных JSON с помощью Python

season_data = dict()
for season in seasons:
url = base_url+'/'+league+'/'+season
res = requests.get(url)
soup = BeautifulSoup(res.content, "lxml")
# Based on the structure of the webpage, I found that data is in the JSON variable, under <script> tags
scripts = soup.find_all('script')
string_with_json_obj = ''
# Find data for teams
for el in scripts:
if 'teamsData' in str(el):
string_with_json_obj = str(el).strip()
# strip unnecessary symbols and get only JSON data
ind_start = string_with_json_obj.index("('")+2
ind_end = string_with_json_obj.index("')")
json_data = string_with_json_obj[ind_start:ind_end]
json_data = json_data.encode('utf8').decode('unicode_escape')
#print(json_data)

После выполнения приведенного выше кода Python вы должны получить кучу данных, которые мы очистили.

Понимание данных скребка

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

Следовательно, мы можем сделать вывод, что в истории есть информация о каждом матче, сыгранном командой в своей лиге (игры Кубка лиги или Лиги чемпионов не включены).

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

# Get teams and their relevant ids and put them into separate dictionary
teams = {}
for id in data.keys():
teams[id] = data[id]['title']

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

columns = []
# Check the sample of values per each column
values = []
for id in data.keys():
columns = list(data[id]['history'][0].keys())
values = list(data[id]['history'][0].values())
break

Теперь давайте получим данные по всем командам. Раскомментируйте оператор печати в приведенном ниже коде, чтобы вывести данные на консоль.

# Getting data for all teams
dataframes = {}
for id, team in teams.items():
teams_data = []
for row in data[id]['history']:
teams_data.append(list(row.values()))
df = pd.DataFrame(teams_data, columns=columns)
dataframes[team] = df
# print('Added data for {}.'.format(team))

После того, как вы завершите этот код, у нас будет словарь DataFrames, в котором ключом будет имя команды, а значением - DataFrame, содержащий все игры команды.

Управление таблицей данных

Когда мы смотрим на содержимое DataFrame, мы видим, что такие показатели, как PPDA и OPPDA (разрешены ppda и ppda), представлены как общие суммы атакующих / защитных действий.

Однако они показаны как коэффициенты в исходной таблице. Давай уберем это.

for team, df in dataframes.items():
dataframes[team]['ppda_coef'] = dataframes[team]['ppda'].apply(lambda x: x['att']/x['def'] if x['def'] != 0 else 0)
dataframes[team]['oppda_coef'] = dataframes[team]['ppda_allowed'].apply(lambda x: x['att']/x['def'] if x['def'] != 0 else 0)

Теперь у нас есть все наши числа, но для каждой игры. Тотал для команды - это то, что нам нужно. Давайте посмотрим на столбцы, которые нам нужно сложить. Для этого мы вернулись к исходной таблице на веб-сайте и обнаружили, что все меры следует складывать вместе, и в конечном итоге в качестве средств остаются только PPDA и OPPDA. Во-первых, давайте определим столбцы, которые нам нужно суммировать и рассчитать.

cols_to_sum = ['xG', 'xGA', 'npxG', 'npxGA', 'deep', 'deep_allowed', 'scored', 'missed', 'xpts', 'wins', 'draws', 'loses', 'pts', 'npxGD']
cols_to_mean = ['ppda_coef', 'oppda_coef']

Наконец, давайте посчитаем итоги и средние значения.

for team, df in dataframes.items():
sum_data = pd.DataFrame(df[cols_to_sum].sum()).transpose()
mean_data = pd.DataFrame(df[cols_to_mean].mean()).transpose()
final_df = sum_data.join(mean_data)
final_df['team'] = team
final_df['matches'] = len(df)
frames.append(final_df)
full_stat = pd.concat(frames)
full_stat = full_stat[['team', 'matches', 'wins', 'draws', 'loses', 'scored', 'missed', 'pts', 'xG', 'npxG', 'xGA', 'npxGA', 'npxGD', 'ppda_coef', 'oppda_coef', 'deep', 'deep_allowed', 'xpts']]
full_stat.sort_values('pts', ascending=False, inplace=True)
full_stat.reset_index(inplace=True, drop=True)
full_stat['position'] = range(1,len(full_stat)+1)
full_stat['xG_diff'] = full_stat['xG'] - full_stat['scored']
full_stat['xGA_diff'] = full_stat['xGA'] - full_stat['missed']
full_stat['xpts_diff'] = full_stat['xpts'] - full_stat['pts']
cols_to_int = ['wins', 'draws', 'loses', 'scored', 'missed', 'pts', 'deep', 'deep_allowed']
full_stat[cols_to_int] = full_stat[cols_to_int].astype(int)

В приведенном выше коде мы переупорядочили столбцы для лучшей читаемости, отсортировали строки по точкам, сбросили индекс и добавили «положение» столбца.

Мы также добавили различия между ожидаемыми и реальными показателями.

Наконец, мы преобразовали числа с плавающей запятой в целые числа, где это необходимо.

Украшение окончательного вывода фрейма данных

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

python
col_order = ['position', 'team', 'matches', 'wins', 'draws', 'loses', 'scored', 'missed', 'pts', 'xG', 'xG_diff', 'npxG', 'xGA', 'xGA_diff', 'npxGA', 'npxGD', 'ppda_coef', 'oppda_coef', 'deep', 'deep_allowed', 'xpts', 'xpts_diff']
full_stat = full_stat[col_order]
full_stat = full_stat.set_index('position')
# print(full_stat.head(20))

Чтобы напечатать часть улучшенных данных, раскомментируйте оператор печати в приведенном выше коде.

Компиляция окончательного кода агрегатора данных Python

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

import numpy as np
import pandas as pd
import requests
from bs4 import BeautifulSoup
import json
# create urls for all seasons of all leagues
base_url = 'https://understat.com/league'
leagues = ['La_liga', 'EPL', 'Bundesliga', 'Serie_A', 'Ligue_1']
seasons = ['2016', '2017', '2018', '2019', '2020']
full_data = dict()
for league in leagues:
season_data = dict()
for season in seasons:
url = base_url+'/'+league+'/'+season
res = requests.get(url)
soup = BeautifulSoup(res.content, "lxml")
# Based on the structure of the webpage, I found that data is in the JSON variable, under <script> tags
scripts = soup.find_all('script')
string_with_json_obj = ''
# Find data for teams
for el in scripts:
if 'teamsData' in str(el):
string_with_json_obj = str(el).strip()
# print(string_with_json_obj)
# strip unnecessary symbols and get only JSON data
ind_start = string_with_json_obj.index("('")+2
ind_end = string_with_json_obj.index("')")
json_data = string_with_json_obj[ind_start:ind_end]
json_data = json_data.encode('utf8').decode('unicode_escape')
# convert JSON data into Python dictionary
data = json.loads(json_data)
# Get teams and their relevant ids and put them into separate dictionary
teams = {}
for id in data.keys():
teams[id] = data[id]['title']
# EDA to get a feeling of how the JSON is structured
# Column names are all the same, so we just use first element
columns = []
# Check the sample of values per each column
values = []
for id in data.keys():
columns = list(data[id]['history'][0].keys())
values = list(data[id]['history'][0].values())
break
# Getting data for all teams
dataframes = {}
for id, team in teams.items():
teams_data = []
for row in data[id]['history']:
teams_data.append(list(row.values()))
df = pd.DataFrame(teams_data, columns=columns)
dataframes[team] = df
# print('Added data for {}.'.format(team))
for team, df in dataframes.items():
dataframes[team]['ppda_coef'] = dataframes[team]['ppda'].apply(lambda x: x['att']/x['def'] if x['def'] != 0 else 0)
dataframes[team]['oppda_coef'] = dataframes[team]['ppda_allowed'].apply(lambda x: x['att']/x['def'] if x['def'] != 0 else 0)
cols_to_sum = ['xG', 'xGA', 'npxG', 'npxGA', 'deep', 'deep_allowed', 'scored', 'missed', 'xpts', 'wins', 'draws', 'loses', 'pts', 'npxGD']
cols_to_mean = ['ppda_coef', 'oppda_coef']
frames = []
for team, df in dataframes.items():
sum_data = pd.DataFrame(df[cols_to_sum].sum()).transpose()
mean_data = pd.DataFrame(df[cols_to_mean].mean()).transpose()
final_df = sum_data.join(mean_data)
final_df['team'] = team
final_df['matches'] = len(df)
frames.append(final_df)
full_stat = pd.concat(frames)
full_stat = full_stat[['team', 'matches', 'wins', 'draws', 'loses', 'scored', 'missed', 'pts', 'xG', 'npxG', 'xGA', 'npxGA', 'npxGD', 'ppda_coef', 'oppda_coef', 'deep', 'deep_allowed', 'xpts']]
full_stat.sort_values('pts', ascending=False, inplace=True)
full_stat.reset_index(inplace=True, drop=True)
full_stat['position'] = range(1,len(full_stat)+1)
full_stat['xG_diff'] = full_stat['xG'] - full_stat['scored']
full_stat['xGA_diff'] = full_stat['xGA'] - full_stat['missed']
full_stat['xpts_diff'] = full_stat['xpts'] - full_stat['pts']
cols_to_int = ['wins', 'draws', 'loses', 'scored', 'missed', 'pts', 'deep', 'deep_allowed']
full_stat[cols_to_int] = full_stat[cols_to_int].astype(int)
col_order = ['position', 'team', 'matches', 'wins', 'draws', 'loses', 'scored', 'missed', 'pts', 'xG', 'xG_diff', 'npxG', 'xGA', 'xGA_diff', 'npxGA', 'npxGD', 'ppda_coef', 'oppda_coef', 'deep', 'deep_allowed', 'xpts', 'xpts_diff']
full_stat = full_stat[col_order]
full_stat = full_stat.set_index('position')
# print(full_stat.head(20))
season_data[season] = full_stat
df_season = pd.concat(season_data)
full_data[league] = df_season

Чтобы проанализировать наши данные в Arctype, нам нужно экспортировать данные в файл CSV. Для этого скопируйте и вставьте приведенный ниже код.

python
data = pd.concat(full_data)
data.to_csv('understat.com.csv')

Анализ данных парсера с MySQL

Теперь, когда у нас есть чистый CSV-файл, содержащий данные о футболе, давайте создадим несколько визуализаций. Во-первых, нам нужно импортировать CSV-файл в таблицу MySQL.

Импорт данных CSV в MySQL

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

Шаг 1
В меню базы данных щелкните значок с тремя точками и выберите «Импортировать таблицу». Нажмите «принять», чтобы принять схему.

Шаг 2
Введите имя таблицы как «soccer_data», затем переименуйте первые два столбца в «лига» и «год». Оставьте все остальные настройки и нажмите кнопку «Импортировать CSV», как показано на изображении ниже.

После выполнения описанных выше шагов таблица soccer_data должна быть заполнена данными из файла CSV, как показано на изображении ниже.

Теперь, когда мы импортировали наши данные, хранящиеся в файле CSV, мы можем сравнивать различные данные и визуализировать их на диаграммах данных.

Использование динамического SQL для создания сводной таблицы и гистограммы

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

Создание сводной таблицы количества ударов по цели

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

SELECT
	year,
	SUM(
		CASE 
			WHEN year = '2020' THEN (scored + missed) / scored 
			ELSE NULL
		END
	) AS `2020 Season`
FROM 
	soccer_data
WHERE
	league = 'Bundesliga'
GROUP BY
	team
ORDER BY
	team;

Таким образом, соотношение бросков к голу каждой команды в сезоне 2020 года выводится в столбце под названием 2020 Season. Но что, если мы хотим, чтобы пять отдельных столбцов делали одно и то же в течение пяти сезонов? Конечно, мы можем определить каждый из них вручную или можем использовать GROUP CONCAT() и пользовательские переменные, чтобы делать это динамически. Единственный динамический компонент нашего запроса - это столбцы сезона в нашем операторе SELECT, поэтому давайте начнем с SELECT вставки этой строки запроса в переменную (@sql).

SELECT
  GROUP_CONCAT(
    DISTINCT CONCAT(
      'SUM(case when year = ''',
      year,
      ''' then (scored + missed) / scored ELSE NULL END) AS `',
      year,
      ' Season`'
    )
    ORDER BY
      year ASC
  ) INTO @sql
FROM
  soccer_data;

Здесь DISTINCT CONCAT() генерирует определение SUM(CASE WHEN year=...) столбца для каждого отдельного значения в year столбце нашей таблицы. Если вы хотите увидеть точный результат, просто добавьте SELECT @sql; на новой строке и выполните запрос.

Теперь, когда у нас есть динамическая часть нашей строки запроса, нам просто нужно добавить все остальное вокруг нее, например:

SET
  @sql = CONCAT(
    'WITH pivot_data AS (SELECT team, ',
    @sql,
    'FROM understat_com 
	WHERE league=''Bundesliga'' 
	GROUP BY team 
	ORDER BY team) 
	SELECT * 
	FROM pivot_data 
	WHERE `2019 Season` IS NOT NULL 
	AND `2020 Season` IS NOT NULL;'
  );

Наконец, нам просто нужно подготовить оператор SQL из строки в @sql и выполнить его:

PREPARE stmt FROM @sql;
EXECUTE stmt;

Запустите, переименуйте и сохраните весь запрос выше.

Визуализация отношения количества голов к выстрелу с помощью гистограммы

Отличный способ визуализировать приведенный выше запрос - использовать гистограмму. Используйте столбец team для оси x и каждый из наших Season столбцов для оси y. В результате должна получиться гистограмма, подобная приведенной ниже:

Создайте круговую диаграмму «Топ-5 по сравнению с остальными» с помощью CTE

Мы будем анализировать данные по одной лиге за разные годы, чтобы сравнить общие победы пяти лучших команд с остальной лигой. Идеальная лига для проведения этого анализа - это «Серия А», поскольку в ней зафиксировано много побед.

Как разделить 5 лучших команд с помощью оператора WITH

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

Имея это в виду, мы сначала сосредоточимся на строках для пяти лучших команд:

WITH top5 AS(
	SELECT 
		team,
		SUM(wins) as wins 
	FROM 
		soccer_data
	WHERE 
		league='Serie_A'
	GROUP BY 
		1
	ORDER BY 
		2 DESC
	LIMIT 5
)
SELECT * FROM top5

Здесь мы используем предложение WITH для создания общего табличного выражения (CTE) с именем top5 с названием команды и общим количеством побед для 5 лучших команд. Затем мы выбираем все в top5.

Теперь, когда у нас есть пять лучших команд, давайте с помощью UNION добавим остальные:

UNION
SELECT
	'Other' as team,
	SUM(wins) as wins
FROM
	soccer_data
WHERE
	league='Serie_A'
	AND team NOT IN (SELECT team FROM top5)

Запустите, переименуйте и сохраните весь запрос выше.

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

Отличный способ визуализировать приведенный выше запрос - использовать круговую диаграмму. Используйте столбец team для "категории" и wins для "значений". После добавления столбцов у нас должна получиться круговая диаграмма, как на изображении ниже. Как видите, на пятерку лучших команд приходится около 50% побед в лиге Серии А:

Сравнение соотношений выигрышей и проигрышей с CTE и динамическим SQL

Для этого запроса мы будем использовать CTE и динамический SQL, чтобы сравнить соотношение побед и поражений трех лучших команд в Серии А со всеми остальными командами. Нам нужно, чтобы наш набор результатов выглядел примерно так:

Основная логика запроса должна выглядеть примерно так:

SELECT 
	year, 
	MAX(CASE 
		WHEN team = 'team1' THEN wins / losses
		ELSE NULL
	END) AS `team 1`
	AVG(CASE
		WHEN team NOT in ('team1','team2','team3') THEN wins / losses
		ELSE NULL
	END) AS `other`
FROM 
	soccer_stats
WHERE
	league = 'Serie_A'
GROUP BY 
	team

Конечно, это не сработает без некоторой магии MySQL.

Разделение трех лучших команд

Перво-наперво, давайте разделим наши три лучшие команды с помощью CTE:

WITH top3 AS(
  SELECT
    team,
    AVG(wins / loses) as wins_to_losses
  FROM
    soccer_data
  WHERE
    league = 'Serie_A'
  GROUP BY
    team
  ORDER BY
    2 DESC
  LIMIT
    3
)

Создание динамических строк SQL внутри CTE

Поскольку каждой из этих команд потребуется отдельный столбец, нам потребуется использовать динамический SQL для генерации некоторых специальных операторов CASE. Нам также потребуется сгенерировать оператор CASE для нашего столбца «Другое». Для этого мы будем использовать динамический SQL внутри CTE:

variable_definitions AS(
	SELECT
	(
		GROUP_CONCAT(
			CONCAT(
				'''', 
				team, 
				''''
			)
		)
	) as team_names,
    (
		GROUP_CONCAT(
			DISTINCT CONCAT(
			'MAX(case when team = ''',
			team,
			''' then wins / loses ELSE NULL END) AS `',
			team,
			'`'
			)
		)
	) as column_definitions
	FROM top3
)

Затем возьмем строки team_names и column_definitions и INSERT их в переменные:

SELECT
  team_names,
  column_definitions 
INTO 
  @teams,
  @sql
FROM
  variable_definitions;

На этом этапе у нас должен быть список трех лучших команд в строковом формате, сохраненный в @teams, а наши операторы столбцов case для трех лучших команд сохранены в @sql. Нам просто нужно построить окончательный запрос:

SET
  @sql = CONCAT(
    'SELECT year, ',
    @sql,
    ', AVG(CASE WHEN team NOT IN (',
    @teams,
    ') THEN wins / loses ELSE NULL END) AS `Others` ',
    'FROM soccer_data WHERE league = ''Serie_A'' GROUP BY year;'
  );
 prepare stmt FROM @sql;
 EXECUTE stmt;

Вы можете найти полный запрос внизу этой статьи.

Визуализация соотношения выигрышей и проигрышей с помощью диаграммы с областями

Отличный способ визуализировать приведенный выше запрос - использовать диаграмму с областями. Чтобы создать эту диаграмму с областями, используйте year для оси x и всех остальных столбцов для оси y. Ваша диаграмма должна выглядеть примерно так:

Поскольку мы используем динамический SQL, мы можем легко добавить больше групповых столбцов, изменив LIMIT 3 в top3 CTE на LIMIT 5:

Заключение

В этой статье вы узнали, как извлекать спортивные данные с помощью Python с веб-сайта и использовать расширенные операции MySQL для их анализа и визуализации с помощью Arctype. Кроме того, вы увидели, насколько легко запускать SQL-запросы к базе данных с помощью Arctype, и получили возможность изучить некоторые из его основных функций и возможностей.

Исходный код скрипта python, файл CSV и другие данные доступны на Github. Если у вас есть вопросы, напишите мне в Twitter: @ LordChuks3.

Последний запрос SQL:

WITH top3 AS(
  SELECT
    team,
    AVG(wins / loses) as wins_to_losses
  FROM
    soccer_data
  WHERE
    league = 'Serie_A'
  GROUP BY
    team
  ORDER BY
    2 DESC
  LIMIT
    3
), variable_definitions AS(
  SELECT
    (GROUP_CONCAT(CONCAT('''', team, ''''))) as team_names,
    (
      GROUP_CONCAT(
        DISTINCT CONCAT(
          'MAX(case when team = ''',
          team,
          ''' then wins / loses ELSE NULL END) AS `',
          team,
          '`'
        )
      )
    ) as column_definitions
  FROM
    top3
)
SELECT
  team_names,
  column_definitions 
INTO 
  @teams,
  @sql
FROM
  variable_definitions;
SET
  @sql = CONCAT(
    'SELECT year, ',
    @sql,
    ', AVG(CASE WHEN team NOT IN (',
    @teams,
    ') THEN wins / loses ELSE NULL END) AS `Others` ',
    'FROM soccer_data WHERE league = ''Serie_A'' GROUP BY year;'
  );
 prepare stmt FROM @sql;
 EXECUTE stmt;

Больше контента на plainenglish.io