Използвайте разширени MySQL операции, за да анализирате данните на Python Web Scraper

За да следвате този урок, трябва да имате следното:

Python 3.7 или по-нова.
Arctype
Основно разбиране на SQL.
Текстов редактор.

Инсталиране на необходимите библиотеки

Библиотеките, необходими за този урок, са както следва:

numpy — основен пакет за научни изчисления с Python
pandas — библиотека, предоставяща високопроизводителни, лесни за използване структури от данни и инструменти за анализ на данни
requests — е единствената Non-GMO 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.

Виждаме на началната страница, че сайтът има данни за шест европейски лиги. Ние обаче ще извличаме данни само за първите 5 лиги (отбори с изключение на RFPL).

Можем също да забележим, че данните на сайта започват от 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) и изберете раздела „Отговор“. След като изпълним заявките, това ще получим.

След като прегледахме съдържанието на уеб страницата, открихме, че данните се записват под елемента „script“ в променливата 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 по-горе, трябва да получите куп данни, които сме изчистили.

Разбиране на данните на скрепера

Когато започнем да разглеждаме данните, разбираме, че това е речник от речници с три ключа: id, title и history. Идентификаторите също се използват като ключове в началния слой на речника.

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

След като прегледахме речника на първия слой, можем да започнем да съставяме списък с имена на екипи.

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

В кода по-горе пренаредихме колоните за по-добра четливост, сортирахме редове въз основа на точки, нулирахме индекса и добавихме колона „позиция“.

Добавихме и разликите между очакваните показатели и реалните показатели.

И накрая, ние преобразувахме числата с плаваща степен в цели числа, където е подходящо.

Разкрасяване на крайния изход на Dataframe

И накрая, нека разкрасим нашите данни, за да станат подобни на данните за сайта в изображението по-горе. За да направите това, изпълнете кода на 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')

Анализиране на данни от Scraper с MySQL

Сега, когато имаме чист CSV файл, съдържащ нашите футболни данни, нека създадем някои визуализации. Първо ще трябва да импортираме CSV файла в MySQL таблица.

Импортиране на CSV данни в MySQL

За да използваме извлечените от нас данни, трябва да импортираме CSV данните като таблица в нашата база данни. За да направите това, следвайте стъпките по-долу:

Стъпка 1
В менюто на базата данни щракнете върху иконата с три точки и изберете „Импортиране на таблица“. Кликнете върху „приемам“, за да приемете схемата.

Стъпка 2
Въведете името на таблицата като „soccer_data“, след което преименувайте първите две колони на „league“ и „year“. Оставете всички други настройки и щракнете върху бутона „Импортиране на CSV“, както се вижда на изображението по-долу.

След като следвате стъпките по-горе, таблицата „soccer_data“ трябва да бъде попълнена с данни от CSV файла, както се вижда на изображението по-долу.

Сега, след като импортирахме нашите данни, съхранени в CSV файл, можем да сравним различни данни и да ги визуализираме в диаграми с данни.

Използвайте Dynamic 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)

Стартирайте, преименувайте и запазете цялата заявка по-горе.

Визуализиране на топ 5 срещу останалите с кръгова диаграма

Отличен начин за визуализиране на заявката по-горе е използването на кръгова диаграма. Използвайте колоната 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 магия.

Разделяне на топ 3 отбора

Първо, нека разделим нашите три най-добри отбора с помощта на 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, и нашите отчети за случай на колона за първите три отбора, запазени в @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