image_output_1.png
import numpy as np

import pandas as pd

df = pd.read_csv('../input/fifa-world-cup-2022/international_matches.csv')

pd.set_option('display.max_columns', None)

df.describe().T

num_cols = list(df.select_dtypes(include=['int64', 'float64']).columns)
cat_cols = list(df.select_dtypes(include=['object']).columns)

columns_contains_null = [col for col in df.columns if df[col].isnull().any()]

fifa2022_teams = ['Qatar', 'Ecuador', 'Senegal', 'Netherlands', 'England', 'IR Iran', 'USA', 'Wales', 'Argentina', 'Saudi Arabia', 'Mexico', 'Poland', 'France', 'Australia', 'Denmark', 'Tunisia', 'Spain', 'Costa Rica', 'Germany', 'Japan', 'Belgium', 'Canada', 'Morocco', 'Croatia', 'Brazil', 'Serbia', 'Switzerland', 'Cameroon', 'Portugal', 'Ghana', 'Uruguay', 'Korea Republic']

ranks = []
for x in fifa2022_teams:
    rank_df = df[(df['home_team'] == x) | (df['away_team'] == x)].sort_values(['date', 'home_team_fifa_rank', 'away_team_fifa_rank'], ascending=[False, True, True]).iloc[0]
    if rank_df['home_team'] == x:
        rank = rank_df['home_team_fifa_rank']
    else:
        rank = rank_df['away_team_fifa_rank']
    ranks.append(rank)
team_ranks = pd.DataFrame({'Team': fifa2022_teams, 'Rank': ranks}).sort_values('Rank').reset_index(drop=True)
team_ranks.index += 1
team_ranks

goalkeeper_score = []
for x in fifa2022_teams:
    gk_score = np.round((df[df['home_team'] == x]['home_team_goalkeeper_score'].mean() + df[df['away_team'] == x]['away_team_goalkeeper_score'].mean()) / 2, 2)
    goalkeeper_score.append(gk_score)
goalkeeper_scores = pd.DataFrame({'Team': fifa2022_teams, 'Gk score': goalkeeper_score}).sort_values('Gk score', ascending=False).reset_index(drop=True)
goalkeeper_scores.index += 1
goalkeeper_scores

defence_score = []
for x in fifa2022_teams:
    df_score = np.round((df[df['home_team'] == x]['home_team_mean_defense_score'].mean() + df[df['away_team'] == x]['away_team_mean_defense_score'].mean()) / 2, 2)
    defence_score.append(df_score)
defence_scores = pd.DataFrame({'Team': fifa2022_teams, 'Df score': defence_score}).sort_values('Df score', ascending=False).reset_index(drop=True)
defence_scores.index += 1
defence_scores

offence_score = []
for x in fifa2022_teams:
    of_score = np.round((df[df['home_team'] == x]['home_team_mean_offense_score'].mean() + df[df['away_team'] == x]['away_team_mean_offense_score'].mean()) / 2, 2)
    offence_score.append(of_score)
offence_scores = pd.DataFrame({'Team': fifa2022_teams, 'Of score': offence_score}).sort_values('Of score', ascending=False).reset_index(drop=True)
offence_scores.index += 1
offence_scores

midfield_score = []
for x in fifa2022_teams:
    md_score = np.round((df[df['home_team'] == x]['home_team_mean_midfield_score'].mean() + df[df['away_team'] == x]['away_team_mean_midfield_score'].mean()) / 2, 2)
    midfield_score.append(md_score)
midfield_scores = pd.DataFrame({'Team': fifa2022_teams, 'Md score': midfield_score}).sort_values('Md score', ascending=False).reset_index(drop=True)
midfield_scores.index += 1
midfield_scores

hwins, hdraws, hloses = ([], [], [])
awins, adraws, aloses = ([], [], [])
for team in fifa2022_teams:
    home_win = df[df['home_team'] == team][df['home_team_result'] == 'Win'].shape[0]
    home_draw = df[df['home_team'] == team][df['home_team_result'] == 'Draw'].shape[0]
    home_lose = df[df['home_team'] == team][df['home_team_result'] == 'Lose'].shape[0]
    away_win = df[df['away_team'] == team][df['home_team_result'] == 'Lose'].shape[0]
    away_draw = df[df['away_team'] == team][df['home_team_result'] == 'Draw'].shape[0]
    away_lose = df[df['away_team'] == team][df['home_team_result'] == 'Win'].shape[0]
    hwins.append(home_win)
    hdraws.append(home_draw)
    hloses.append(home_lose)
    awins.append(away_win)
    adraws.append(away_draw)
    aloses.append(away_lose)
    wins = np.add(hwins, awins)
    draws = np.add(hdraws, adraws)
    loses = np.add(hloses, aloses)
win_draw_lose = pd.DataFrame({'Team': fifa2022_teams, 'Win': wins, 'Draw': draws, 'Lose': loses, 'Home win': hwins, 'Home draw': hdraws, 'Home lose': hloses, 'Away win': awins, 'Away draw': adraws, 'Away lose': aloses})
win_draw_lose.insert(1, 'Total', win_draw_lose['Win'] + win_draw_lose['Draw'] + win_draw_lose['Lose'])
win_draw_lose.insert(8, 'Total Home', win_draw_lose['Home win'] + win_draw_lose['Home draw'] + win_draw_lose['Home lose'])
win_draw_lose.insert(12, 'Total Away', win_draw_lose['Away win'] + win_draw_lose['Away draw'] + win_draw_lose['Away lose'])
win_draw_lose['Win %'] = np.around(100 * win_draw_lose['Win'] / win_draw_lose['Total'], 2)
win_draw_lose['Draw %'] = np.round(100 * win_draw_lose['Draw'] / win_draw_lose['Total'], 2)
win_draw_lose['Lose %'] = np.round(100 * win_draw_lose['Lose'] / win_draw_lose['Total'], 2)
win_draw_lose['Home Win %'] = np.round(100 * win_draw_lose['Home win'] / win_draw_lose['Total Home'], 2)
win_draw_lose['Home Draw %'] = np.round(100 * win_draw_lose['Home draw'] / win_draw_lose['Total Home'], 2)
win_draw_lose['Home Lose %'] = np.round(100 * win_draw_lose['Home lose'] / win_draw_lose['Total Home'], 2)
win_draw_lose['Away Win %'] = np.round(100 * win_draw_lose['Away win'] / win_draw_lose['Total Away'], 2)
win_draw_lose['Away Draw %'] = np.round(100 * win_draw_lose['Away draw'] / win_draw_lose['Total Away'], 2)
win_draw_lose['Away Lose %'] = np.round(100 * win_draw_lose['Away lose'] / win_draw_lose['Total Away'], 2)
win_draw_lose = win_draw_lose.sort_values('Win %', ascending=False).reset_index(drop=True)
win_draw_lose.index += 1
win_draw_lose.style.set_properties(**{'background-color': 'gray', 'color': 'yellow'}, subset=['Home win', 'Home Win %', 'Win %'])