<jupyter_start><jupyter_text>Team<jupyter_code>from bs4 import BeautifulSoup
import requests
import pandas as pd
import json
from sqlalchemy import create_engine
from geojson import Feature, FeatureCollection, Point
Coordinates = pd.read_csv('Resources/world_country_latitude_and_longitude_values.csv')
Coordinates
Team_data = pd.read_csv('Resources/Footballteams.csv')
Team_data['Country']=Team_data['Country'].apply(lambda x: x.strip())
Team_data
Team_data_Final = pd.merge(Team_data,Coordinates,on = "Country")
Team_data_Final
# columns used for constructing geojson object
features = Team_data_Final.apply(
    lambda row: Feature(geometry=Point((float(row['Longitude']), float(row['Latitude'])))),
    axis=1).tolist()

# all the other columns used as properties
properties = Team_data_Final.drop(['Latitude', 'Longitude'], axis=1).to_dict('records')

# whole geojson object
feature_collection = FeatureCollection(features=features, properties=properties)
with open('Team_Data.geojson', 'w', encoding='utf-8') as f:
    json.dump(feature_collection, f, ensure_ascii=False)
engine = create_engine('sqlite:///football_team_data.db')
Team_data.to_sql('premier_league_team_data', con=engine, if_exists='replace', index=False)
engine.execute("SELECT * FROM premier_league_team_data").fetchall()
Country_Rating = Team_data.groupby("Country").agg({"Rating":["sum"]})
Country_Goals = Team_data.groupby("Country").agg({"Goals":["sum"]})
Country_Shots = Team_data.groupby("Country").agg({"Shots pg":["sum"]})
Country_yellow_cards = Team_data.groupby("Country").agg({"yellow_cards":["sum"]})
Country_red_cards = Team_data.groupby("Country").agg({"red_cards":["sum"]})

Country_summary1 = pd.merge(Country_Rating,Country_Goals,on = "Country")
Country_summary2 = pd.merge(Country_summary1,Country_Shots,on = "Country")
Country_summary3 = pd.merge(Country_summary2,Country_yellow_cards,on = "Country")
Country_summary = pd.merge(Country_summary3,Country_red_cards,on = "Country")

#Reset the Index
Country_summary.reset_index(inplace=True)
#Assign name for the column variables
Country_summary.columns = ["Country","Rating","Goals","Shots","Yellow_Cards","Red_Cards"]
football_teams_summary = pd.merge(Country_summary,Coordinates,on = "Country")
football_teams_summary.to_csv(r'Country_summary.csv')
football_teams_summary

import csv, json

csvFilePath = "Country_summary.csv"  ## path to file
jsonFilePath = "Country_summary.json"   ## change to wanted file name

data = {}
with open(csvFilePath) as csvFile:
    csvReader = csv.DictReader(csvFile)
    for rows in csvReader:
        id = rows['Country']
        data[id] = rows

with open(jsonFilePath, 'w') as jsonFile:
    jsonFile.write(json.dumps(data, indent=4))
chelsea_players = pd.read_csv('Resources/chelsea_players.csv')
chelsea_players_Final = pd.merge(chelsea_players,Coordinates,how='left', left_on=['Nationality'], right_on=['Country'])

# columns used for constructing geojson object
features = chelsea_players_Final.apply(
    lambda row: Feature(geometry=Point((float(row['Longitude']), float(row['Latitude'])))),
    axis=1).tolist()

# all the other columns used as properties
properties = chelsea_players_Final.drop(['Latitude', 'Longitude'], axis=1).to_dict('records')

# whole geojson object
feature_collection = FeatureCollection(features=features, properties=properties)

with open('chelsea_players_Final.geojson', 'w', encoding='utf-8') as f:
    json.dump(feature_collection, f, ensure_ascii=False)

chelsea_players_Final
manchester_players = pd.read_csv('Resources/manchester_players.csv')
psg_players = pd.read_csv('Resources/psg_players.csv')
madrid_players = pd.read_csv('Resources/real_madrid_players.csv')
chelsea_players
manchester_players = pd.read_csv('Resources/manchester_players.csv')
manchester_players_Final = pd.merge(manchester_players,Coordinates,how='left', left_on=['Nationality'], right_on=['Country'])

# columns used for constructing geojson object
features = manchester_players_Final.apply(
    lambda row: Feature(geometry=Point((float(row['Longitude']), float(row['Latitude'])))),
    axis=1).tolist()

# all the other columns used as properties
properties = manchester_players_Final.drop(['Latitude', 'Longitude'], axis=1).to_dict('records')

# whole geojson object
feature_collection = FeatureCollection(features=features, properties=properties)

with open('manchester_players_Final.geojson', 'w', encoding='utf-8') as f:
    json.dump(feature_collection, f, ensure_ascii=False)

manchester_players_Final
psg_players = pd.read_csv('Resources/psg_players.csv')
psg_players_Final = pd.merge(psg_players,Coordinates,how='left', left_on=['Nationality'], right_on=['Country'])

# columns used for constructing geojson object
features = psg_players_Final.apply(
    lambda row: Feature(geometry=Point((float(row['Longitude']), float(row['Latitude'])))),
    axis=1).tolist()

# all the other columns used as properties
properties = psg_players_Final.drop(['Latitude', 'Longitude'], axis=1).to_dict('records')

# whole geojson object
feature_collection = FeatureCollection(features=features, properties=properties)

with open('psg_players_Final.geojson', 'w', encoding='utf-8') as f:
    json.dump(feature_collection, f, ensure_ascii=False)

psg_players_Final
madrid_players = pd.read_csv('Resources/real_madrid_players.csv')
madrid_players_Final = pd.merge(madrid_players,Coordinates,how='left', left_on=['Nationality'], right_on=['Country'])

# columns used for constructing geojson object
features = madrid_players_Final.apply(
    lambda row: Feature(geometry=Point((float(row['Longitude']), float(row['Latitude'])))),
    axis=1).tolist()

# all the other columns used as properties
properties = madrid_players_Final.drop(['Latitude', 'Longitude'], axis=1).to_dict('records')

# whole geojson object
feature_collection = FeatureCollection(features=features, properties=properties)

with open('manchester_players_Final.geojson', 'w', encoding='utf-8') as f:
    json.dump(feature_collection, f, ensure_ascii=False)

madrid_players
chelsea_group = chelsea_players.groupby("Nationality").agg({"Nationality":["count"]})
#nationality_group.reset_index(inplace=True)
#nationality_group.columns = ["Nationality","Count"]
#Sorted_nationalities = nationality_group.sort_values(by = "Count", ascending = False)
#Sorted_nationalities
manchester_players
psg_players
madrid_players
frames = [chelsea_players, manchester_players, psg_players,madrid_players]
all_nationalities = pd.concat(frames)
all_nationalities
nationality_group = all_nationalities.groupby(["Club","Nationality"]).agg({"Nationality":["count"]})
nationality_group.reset_index(inplace=True)
nationality_group.columns = ["Club","Nationality","Count"]
Sorted_nationalities = nationality_group.sort_values(by = "Club", ascending = False)
Sorted_nationalities
html_table = nationality_group.to_html()
html_table
nationality_group.to_html('Data_table.html')<jupyter_output><empty_output><jupyter_text>Foreign Player Minutes<jupyter_code>from bs4 import BeautifulSoup
import requests
import pandas as pd
from sqlalchemy import create_engine
# Premier League
league = "premier-league"
abbr = "GB1"
r = requests.get(f"https://www.transfermarkt.co.uk/{league}/legionaereeinsaetze/wettbewerb/{abbr}/saison_id/2020/altersklasse/alle/option/spiele/plus/1", 
                 headers= {"User-Agent":"Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:74.0) Gecko/20100101 Firefox/74.0"})
soup = BeautifulSoup(r.content, "html.parser")
results = soup.find("table",class_="items")
premier_leauge_minutes = pd.read_html(str(results))[0]
premier_leauge_minutes.drop(columns=['wappen',"% minutes foreign players"],inplace=True)
premier_leauge_minutes.columns=['Club', 'Players used', 'Non-foreigners played',
       'Used foreign players', '% minutes non-foreigners',
       '% minutes foreign players']
premier_leauge_minutes = premier_leauge_minutes[:20]
premier_leauge_minutes = premier_leauge_minutes.sort_values(by="% minutes foreign players", ascending=False)
premier_leauge_minutes
engine = create_engine('sqlite:///football.db')
premier_leauge_minutes.to_sql('premier_league_foreign_minutes', con=engine, if_exists='replace', index=False)
engine.execute("SELECT * FROM premier_league_foreign_minutes").fetchall()
# Ligue 1
league = "ligue-1"
abbr = "FR1"

r = requests.get(f"https://www.transfermarkt.co.uk/{league}/legionaereeinsaetze/wettbewerb/{abbr}/saison_id/2020/altersklasse/alle/option/spiele/plus/1", 
                 headers= {"User-Agent":"Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:74.0) Gecko/20100101 Firefox/74.0"})
soup = BeautifulSoup(r.content, "html.parser")
results = soup.find("table",class_="items")
ligue_one_minutes = pd.read_html(str(results))[0]
ligue_one_minutes.drop(columns=['wappen',"% minutes foreign players"],inplace=True)
ligue_one_minutes.columns=['Club', 'Players used', 'Non-foreigners played',
       'Used foreign players', '% minutes non-foreigners',
       '% minutes foreign players']
ligue_one_minutes = ligue_one_minutes[:20]
ligue_one_minutes = ligue_one_minutes.sort_values(by="% minutes foreign players", ascending=False)
ligue_one_minutes
ligue_one_minutes.to_sql('ligue__one_foreign_minutes', con=engine, if_exists='replace', index=False)
engine.execute("SELECT * FROM ligue__one_foreign_minutes").fetchall()
# La Liga
league = "laliga"
abbr = "ES1"
r = requests.get(f"https://www.transfermarkt.co.uk/{league}/legionaereeinsaetze/wettbewerb/{abbr}/saison_id/2020/altersklasse/alle/option/spiele/plus/1", 
                 headers= {"User-Agent":"Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:74.0) Gecko/20100101 Firefox/74.0"})
soup = BeautifulSoup(r.content, "html.parser")
results = soup.find("table",class_="items")
laliga_minutes = pd.read_html(str(results))[0]
laliga_minutes.drop(columns=['wappen',"% minutes foreign players"],inplace=True)
laliga_minutes.columns=['Club', 'Players used', 'Non-foreigners played',
       'Used foreign players', '% minutes non-foreigners',
       '% minutes foreign players']
laliga_minutes = laliga_minutes[:20]
laliga_minutes = laliga_minutes.sort_values(by="% minutes foreign players", ascending=False)
laliga_minutes
laliga_minutes.to_sql('laliga_foreign_minutes', con=engine, if_exists='replace', index=False)
engine.execute("SELECT * FROM laliga_foreign_minutes").fetchall()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13866847300174182, 'fraction_numerical': 0.011805689955486743, 'mean_word_length': 7.98695652173913, 'pattern_counts': {'":': 10, '<': 9, '<?xml version=': 0, '>': 9, 'https://': 3, 'lorem ipsum': 0, 'www.': 3, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '428607', 'license': 'MIT', 'n_tokens_mistral': 3555, 'n_tokens_neox': 3364, 'n_words': 578, 'path': 'Teams.ipynb', 'repo_name': 'KenyanBoy/HML-Project', 'size': '10334'}