FIFA DATA CLEANING.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "e4a17163",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from string import ascii_uppercase as alphabet\n",
    "import pickle"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "95152a4f",
   "metadata": {},
   "source": [
    "<h2> EXTRACTING FIFA 2022 GROUP TABLES </h2>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "3a578b20",
   "metadata": {},
   "outputs": [],
   "source": [
    "tables= pd.read_html(\"https://web.archive.org/web/20221115040351/https://en.wikipedia.org/wiki/2022_FIFA_World_Cup\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "38eb5c30",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Pos</th>\n",
       "      <th>Team</th>\n",
       "      <th>Pld</th>\n",
       "      <th>W</th>\n",
       "      <th>D</th>\n",
       "      <th>L</th>\n",
       "      <th>GF</th>\n",
       "      <th>GA</th>\n",
       "      <th>GD</th>\n",
       "      <th>Pts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Portugal</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Ghana</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Uruguay</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>South Korea</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Pos         Team  Pld  W  D  L  GF  GA  GD  Pts\n",
       "0    1     Portugal    0  0  0  0   0   0   0    0\n",
       "1    2        Ghana    0  0  0  0   0   0   0    0\n",
       "2    3      Uruguay    0  0  0  0   0   0   0    0\n",
       "3    4  South Korea    0  0  0  0   0   0   0    0"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tables[12] # 8 tables\n",
    "tables[19]\n",
    "tables[26]\n",
    "tables[61]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "4069b013",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Pos',\n",
       "       'Team.mw-parser-output .navbar{display:inline;font-size:88%;font-weight:normal}.mw-parser-output .navbar-collapse{float:left;text-align:left}.mw-parser-output .navbar-boxtext{word-spacing:0}.mw-parser-output .navbar ul{display:inline-block;white-space:nowrap;line-height:inherit}.mw-parser-output .navbar-brackets::before{margin-right:-0.125em;content:\"[ \"}.mw-parser-output .navbar-brackets::after{margin-left:-0.125em;content:\" ]\"}.mw-parser-output .navbar li{word-spacing:-0.125em}.mw-parser-output .navbar a>span,.mw-parser-output .navbar a>abbr{text-decoration:inherit}.mw-parser-output .navbar-mini abbr{font-variant:small-caps;border-bottom:none;text-decoration:none;cursor:inherit}.mw-parser-output .navbar-ct-full{font-size:114%;margin:0 7em}.mw-parser-output .navbar-ct-mini{font-size:114%;margin:0 4em}vte',\n",
       "       'Pld', 'W', 'D', 'L', 'GF', 'GA', 'GD', 'Pts', 'Qualification'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tables[12].columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 457,
   "id": "2ab32264",
   "metadata": {},
   "outputs": [],
   "source": [
    "dict_table={}\n",
    "for letter,i in zip(alphabet,range (12,62,7)):\n",
    "    df_table=tables[i]\n",
    "    df_table.rename(columns={df_table.columns[1]: \"Team\"}, inplace=True)\n",
    "    df_table.pop(\"Qualification\")\n",
    "    dict_table[f\"Group {letter}\"]=df_table\n",
    "    \n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 461,
   "id": "31d43f30",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Pos</th>\n",
       "      <th>Team</th>\n",
       "      <th>Pld</th>\n",
       "      <th>W</th>\n",
       "      <th>D</th>\n",
       "      <th>L</th>\n",
       "      <th>GF</th>\n",
       "      <th>GA</th>\n",
       "      <th>GD</th>\n",
       "      <th>Pts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Brazil</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Serbia</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Switzerland</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Cameroon</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Pos         Team  Pld  W  D  L  GF  GA  GD  Pts\n",
       "0    1       Brazil    0  0  0  0   0   0   0    0\n",
       "1    2       Serbia    0  0  0  0   0   0   0    0\n",
       "2    3  Switzerland    0  0  0  0   0   0   0    0\n",
       "3    4     Cameroon    0  0  0  0   0   0   0    0"
      ]
     },
     "execution_count": 461,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dict_table[\"Group G\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "f35e9c04",
   "metadata": {},
   "outputs": [],
   "source": [
    "with open(\"dict_table\",\"wb\") as output:\n",
    "    pickle.dump(dict_table,output)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "a80a5bdd",
   "metadata": {},
   "outputs": [],
   "source": [
    "years=[]\n",
    "for i  in range(1930,2019,4):\n",
    "    years.append(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "9d238af1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[1930,\n",
       " 1934,\n",
       " 1938,\n",
       " 1950,\n",
       " 1954,\n",
       " 1958,\n",
       " 1962,\n",
       " 1966,\n",
       " 1970,\n",
       " 1974,\n",
       " 1978,\n",
       " 1982,\n",
       " 1986,\n",
       " 1990,\n",
       " 1994,\n",
       " 1998,\n",
       " 2002,\n",
       " 2006,\n",
       " 2010,\n",
       " 2014,\n",
       " 2018]"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "years"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "03f3cbe5",
   "metadata": {},
   "source": [
    "<h1> WEB SCRAPING MATCH RESULTS FROM 1930-2018</h1>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "95fbd24f",
   "metadata": {},
   "outputs": [],
   "source": [
    "from bs4 import BeautifulSoup\n",
    "import requests"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "id": "a85b32ee",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_matches(year):\n",
    "    web=f\"https://en.wikipedia.org/wiki/{year}_FIFA_World_Cup\"\n",
    "    response = requests.get(web)\n",
    "    content = response.text\n",
    "    soup = BeautifulSoup(content, \"lxml\")\n",
    "\n",
    "    matches=soup.find_all(\"div\",class_=\"footballbox\")\n",
    "\n",
    "    home_teams = []\n",
    "    score = []\n",
    "    away_teams = []\n",
    "\n",
    "    for match in matches:\n",
    "        home_team = match.find(\"th\", class_=\"fhome\").get_text()\n",
    "        match_score = match.find(\"th\", class_=\"fscore\").get_text()\n",
    "        away_team = match.find(\"th\", class_=\"faway\").get_text()\n",
    "\n",
    "        home_teams.append(home_team)\n",
    "        score.append(match_score)\n",
    "        away_teams.append(away_team)\n",
    "\n",
    "    football = {'Home': home_teams , 'Score': score , 'Away': away_teams}\n",
    "    df_football = pd.DataFrame(football)\n",
    "    df_football['Year'] = year\n",
    "    return df_football\n",
    "    \n",
    "fifa = [get_matches(year) for year in years] \n",
    "\n",
    "df_f = pd.concat(fifa, ignore_index = True)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "89d224f8",
   "metadata": {},
   "source": [
    "<h2>GETTING MISSING MATCH DATA FOR YEAR 1990 </h2>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "id": "07c9842e",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_missing_matches(year):\n",
    "    web=\"https://en.wikipedia.org/wiki/1990_FIFA_World_Cup\"\n",
    "    response = requests.get(web)\n",
    "    content = response.text\n",
    "    soup = BeautifulSoup(content, \"lxml\")\n",
    "\n",
    "    matches=soup.find_all(\"tr\", attrs={\"style\":\"font-size:90%\"})\n",
    "\n",
    "    home_teams = []\n",
    "    score = []\n",
    "    away_teams = []\n",
    "\n",
    "\n",
    "    for match in matches:\n",
    "        home_team = match.find(\"a\").get_text()\n",
    "        match_score = match.find(\"b\").get_text()\n",
    "        away_team = match.find(\"span\", attrs={\"style\":\"white-space:nowrap\"}).get_text()\n",
    "\n",
    "\n",
    "        home_teams.append(home_team)\n",
    "        score.append(match_score)\n",
    "        away_teams.append(away_team)\n",
    "\n",
    "    football = {'Home': home_teams, 'Score': score , 'Away': away_teams}\n",
    "    df_football = pd.DataFrame(football)\n",
    "    df_football['Year'] = year\n",
    "    return df_football\n",
    "\n",
    "missing_fifa_data = get_missing_matches(1990)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7575f0d3",
   "metadata": {},
   "source": [
    "<h1>WEB SCRAPING MATCH FIXTURES FOR FIFA 2022 </h1>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "id": "1653b8b5",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_fixtures(year):\n",
    "    web = \"https://web.archive.org/web/20221115040351/https://en.wikipedia.org/wiki/2022_FIFA_World_Cup\"\n",
    "    response = requests.get(web)\n",
    "    content = response.text\n",
    "    soup = BeautifulSoup(content, \"lxml\")\n",
    "\n",
    "    matches=soup.find_all(\"div\",class_=\"footballbox\")\n",
    "\n",
    "    home_22 = []\n",
    "    score_2022 = []\n",
    "    away_22 = []\n",
    "\n",
    "    for match in matches:\n",
    "        home_team_2022 = match.find(\"th\", class_=\"fhome\").get_text()\n",
    "        match_score_2022 = match.find(\"th\", class_=\"fscore\").get_text()\n",
    "        away_team_2022 = match.find(\"th\", class_=\"faway\").get_text()\n",
    "\n",
    "        home_22.append(home_team_2022)\n",
    "        score_2022.append(match_score_2022)\n",
    "        away_22.append(away_team_2022)\n",
    "\n",
    "    fixtures = {'Home': home_22 , 'Score': score_2022 , 'Away': away_22}\n",
    "    df_fixtures = pd.DataFrame(fixtures)\n",
    "    return df_fixtures\n",
    "\n",
    "df_fixtures_2022 = get_fixtures(2022)\n",
    "df_fixtures_2022.to_csv(\"FIFA 2022 FIXTURES.csv\", index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "id": "29a2405e",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_fifa = pd.concat([df_f, missing_fifa_data], ignore_index = True)  # To concatenate 2 dataframes pass them as a list\n",
    "df_fifa.to_csv('FIFA HISTORICAL DATA.csv', index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ee91a99f",
   "metadata": {},
   "source": [
    "<h1> CLEANING DATA FOR ANALYSIS </h1>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "id": "ac144a02",
   "metadata": {},
   "outputs": [],
   "source": [
    "# CLEANING DATA REMOVING SPACES\n",
    "\n",
    "df_fixtures_2022['Home'] = df_fixtures_2022['Home'].str.strip()\n",
    "df_fixtures_2022['Away'] = df_fixtures_2022['Away'].str.strip()\n",
    "\n",
    "# SORTING THE FIFA DATA VALUES ACCORDING TO YEAR\n",
    "\n",
    "df_fifa.sort_values('Year', inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 279,
   "id": "4415b83a",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# REPLACING STRINGS THAT CONTAIN \"After extra time\"\n",
    "\n",
    "df_fifa['Score'] = df_fifa['Score'].str.replace('(a.e.t)','', regex = True)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 298,
   "id": "b0e8e51c",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Home</th>\n",
       "      <th>Score</th>\n",
       "      <th>Away</th>\n",
       "      <th>Year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [Home, Score, Away, Year]\n",
       "Index: []"
      ]
     },
     "execution_count": 298,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# DROPPING THE WALKOVER MATCH BETWEEN SWEDEN AND AUSTRIA IN 1938\n",
    "\n",
    "df_fifa[df_fifa['Score'].str.contains('a')]\n",
    "df_fifa.drop(index=37, inplace= True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 299,
   "id": "01c6382d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# CLEANING WHITE SPACES FOR FIFA DATASET\n",
    "\n",
    "df_fifa['Home'] = df_fifa['Home'].str.strip()\n",
    "df_fifa['Away'] = df_fifa['Away'].str.strip()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 310,
   "id": "0c7fc6be",
   "metadata": {},
   "outputs": [],
   "source": [
    "# SEPERATING SCORE INTO HOME AND AWAY GOALS\n",
    "\n",
    "df_fifa[['Home Goals', 'Away Goals']] = df_fifa['Score'].str.split('–', expand = True)\n",
    "df_fifa = df_fifa.drop('Score', axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 326,
   "id": "d4eb721d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Home Team</th>\n",
       "      <th>Away Teams</th>\n",
       "      <th>Year</th>\n",
       "      <th>Home Goals</th>\n",
       "      <th>Away Goals</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>France</td>\n",
       "      <td>Mexico</td>\n",
       "      <td>1930</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Uruguay</td>\n",
       "      <td>Argentina</td>\n",
       "      <td>1930</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Uruguay</td>\n",
       "      <td>Yugoslavia</td>\n",
       "      <td>1930</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Argentina</td>\n",
       "      <td>United States</td>\n",
       "      <td>1930</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Paraguay</td>\n",
       "      <td>Belgium</td>\n",
       "      <td>1930</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>829</th>\n",
       "      <td>Serbia</td>\n",
       "      <td>Brazil</td>\n",
       "      <td>2018</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>828</th>\n",
       "      <td>Serbia</td>\n",
       "      <td>Switzerland</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>827</th>\n",
       "      <td>Brazil</td>\n",
       "      <td>Costa Rica</td>\n",
       "      <td>2018</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>825</th>\n",
       "      <td>Costa Rica</td>\n",
       "      <td>Serbia</td>\n",
       "      <td>2018</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>812</th>\n",
       "      <td>Spain</td>\n",
       "      <td>Morocco</td>\n",
       "      <td>2018</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>900 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Home Team     Away Teams  Year Home Goals Away Goals\n",
       "0        France         Mexico  1930          4          1\n",
       "17      Uruguay      Argentina  1930          4          2\n",
       "16      Uruguay     Yugoslavia  1930          6          1\n",
       "15    Argentina  United States  1930          6          1\n",
       "14     Paraguay        Belgium  1930          1          0\n",
       "..          ...            ...   ...        ...        ...\n",
       "829      Serbia         Brazil  2018          0          2\n",
       "828      Serbia    Switzerland  2018          1          2\n",
       "827      Brazil     Costa Rica  2018          2          0\n",
       "825  Costa Rica         Serbia  2018          0          1\n",
       "812       Spain        Morocco  2018          2          2\n",
       "\n",
       "[900 rows x 5 columns]"
      ]
     },
     "execution_count": 326,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_fifa.rename(columns = {'Home':'Home Team', 'Away': 'Away Teams'}, inplace= True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 342,
   "id": "d1cecea3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Home Team     object\n",
       "Away Teams    object\n",
       "Year           int64\n",
       "Home Goals    object\n",
       "Away Goals    object\n",
       "dtype: object"
      ]
     },
     "execution_count": 342,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_fifa.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 349,
   "id": "2d55a556",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_fifa['Total Goals'] = df_fifa['Home Goals'] + df_fifa['Away Goals']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 354,
   "id": "6607951b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_fifa = df_fifa.drop(\"Total Goals\", axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 355,
   "id": "62e4f084",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Home Team</th>\n",
       "      <th>Away Teams</th>\n",
       "      <th>Year</th>\n",
       "      <th>Home Goals</th>\n",
       "      <th>Away Goals</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>France</td>\n",
       "      <td>Mexico</td>\n",
       "      <td>1930</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Uruguay</td>\n",
       "      <td>Argentina</td>\n",
       "      <td>1930</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Uruguay</td>\n",
       "      <td>Yugoslavia</td>\n",
       "      <td>1930</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Argentina</td>\n",
       "      <td>United States</td>\n",
       "      <td>1930</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Paraguay</td>\n",
       "      <td>Belgium</td>\n",
       "      <td>1930</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>829</th>\n",
       "      <td>Serbia</td>\n",
       "      <td>Brazil</td>\n",
       "      <td>2018</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>828</th>\n",
       "      <td>Serbia</td>\n",
       "      <td>Switzerland</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>827</th>\n",
       "      <td>Brazil</td>\n",
       "      <td>Costa Rica</td>\n",
       "      <td>2018</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>825</th>\n",
       "      <td>Costa Rica</td>\n",
       "      <td>Serbia</td>\n",
       "      <td>2018</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>812</th>\n",
       "      <td>Spain</td>\n",
       "      <td>Morocco</td>\n",
       "      <td>2018</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>900 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Home Team     Away Teams  Year Home Goals Away Goals\n",
       "0        France         Mexico  1930          4          1\n",
       "17      Uruguay      Argentina  1930          4          2\n",
       "16      Uruguay     Yugoslavia  1930          6          1\n",
       "15    Argentina  United States  1930          6          1\n",
       "14     Paraguay        Belgium  1930          1          0\n",
       "..          ...            ...   ...        ...        ...\n",
       "829      Serbia         Brazil  2018          0          2\n",
       "828      Serbia    Switzerland  2018          1          2\n",
       "827      Brazil     Costa Rica  2018          2          0\n",
       "825  Costa Rica         Serbia  2018          0          1\n",
       "812       Spain        Morocco  2018          2          2\n",
       "\n",
       "[900 rows x 5 columns]"
      ]
     },
     "execution_count": 355,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_fifa"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ae9528c5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# REMOVING UNWANTED STRINGS FROM THE DATASET FOR CONVERSION\n",
    "\n",
    "df_fifa[df_fifa['Away Goals'].str.contains(\"0(.)\")]\n",
    "df_fifa['Away Goals'] = df_fifa['Away Goals'].str.replace('0(.)','0', regex = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 451,
   "id": "6f2dbec4",
   "metadata": {},
   "outputs": [],
   "source": [
    "# CHANGING THE DATA TYPE OF COLUMNS\n",
    "\n",
    "df_fifa['Home Goals'] = pd.to_numeric(df_fifa['Home Goals'], downcast = 'integer', errors='raise')\n",
    "df_fifa['Away Goals'] = pd.to_numeric(df_fifa['Away Goals'], downcast = 'integer', errors='raise')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 453,
   "id": "a53d1677",
   "metadata": {},
   "outputs": [],
   "source": [
    "# CREATING TOTAL GOALS COLUMN\n",
    "\n",
    "df_fifa['Total Goals'] = df_fifa['Home Goals'] + df_fifa['Away Goals']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 454,
   "id": "9070e4b9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Home Team</th>\n",
       "      <th>Away Teams</th>\n",
       "      <th>Year</th>\n",
       "      <th>Home Goals</th>\n",
       "      <th>Away Goals</th>\n",
       "      <th>Total Goals</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>France</td>\n",
       "      <td>Mexico</td>\n",
       "      <td>1930</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Uruguay</td>\n",
       "      <td>Argentina</td>\n",
       "      <td>1930</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Uruguay</td>\n",
       "      <td>Yugoslavia</td>\n",
       "      <td>1930</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Argentina</td>\n",
       "      <td>United States</td>\n",
       "      <td>1930</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Paraguay</td>\n",
       "      <td>Belgium</td>\n",
       "      <td>1930</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>829</th>\n",
       "      <td>Serbia</td>\n",
       "      <td>Brazil</td>\n",
       "      <td>2018</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>828</th>\n",
       "      <td>Serbia</td>\n",
       "      <td>Switzerland</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>827</th>\n",
       "      <td>Brazil</td>\n",
       "      <td>Costa Rica</td>\n",
       "      <td>2018</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>825</th>\n",
       "      <td>Costa Rica</td>\n",
       "      <td>Serbia</td>\n",
       "      <td>2018</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>812</th>\n",
       "      <td>Spain</td>\n",
       "      <td>Morocco</td>\n",
       "      <td>2018</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>900 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Home Team     Away Teams  Year  Home Goals  Away Goals  Total Goals\n",
       "0        France         Mexico  1930           4           1            5\n",
       "17      Uruguay      Argentina  1930           4           2            6\n",
       "16      Uruguay     Yugoslavia  1930           6           1            7\n",
       "15    Argentina  United States  1930           6           1            7\n",
       "14     Paraguay        Belgium  1930           1           0            1\n",
       "..          ...            ...   ...         ...         ...          ...\n",
       "829      Serbia         Brazil  2018           0           2            2\n",
       "828      Serbia    Switzerland  2018           1           2            3\n",
       "827      Brazil     Costa Rica  2018           2           0            2\n",
       "825  Costa Rica         Serbia  2018           0           1            1\n",
       "812       Spain        Morocco  2018           2           2            4\n",
       "\n",
       "[900 rows x 6 columns]"
      ]
     },
     "execution_count": 454,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_fifa"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 455,
   "id": "6bafa440",
   "metadata": {},
   "outputs": [],
   "source": [
    "# EXPORTING CLEAN DATA \n",
    "\n",
    "df_fifa.to_csv('FIFA HISTORICAL DATA CLEANED.csv', index = False)\n",
    "df_fixtures_2022.to_csv(\"FIFA 2022 FIXTURES CLEANED.csv\", index = False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}