<jupyter_start><jupyter_text>Filter Python database to find better, cheaper players using FIFA’s ratingsProduced by : [FC Python](https://fcpython.com/blog/filter-python-database-to-find-better-cheaper-players-using-fifas-ratings)Data provided by : [Kaggle](https://www.kaggle.com/stefanoleone992/fifa-20-complete-player-datasetplayers_20.csv)<jupyter_code>#Import modules
import numpy as np
import pandas as pd 
# 2020 Data Loading
data = pd.read_csv("fifa-20-complete-player-dataset/players_20.csv")
data.head()
#Define function called club, that looks for a team name and return the selected columns
def club(teamName):
    
    return data[data['club'] == teamName][['short_name','wage_eur','value_eur','player_positions','overall','age']]


#Use the club function to find the team, and sort the squad by wage bill
club('Manchester United').sort_values("wage_eur", ascending = False)

#Extract DDG's information, just like we did with the team name before
DDG = data[data['short_name'] == 'De Gea'][['short_name','wage_eur','value_eur','player_positions','overall','age']]

#Assign DDG's wage, position, rating and age to variables
DDGWage = DDG['wage_eur'].item()
DDGPos = DDG['player_positions'].item()
DDGRating = DDG['overall'].item()
DDGAge = DDG['age'].item()

#Create a list of goalkeepers, matching DDG's position
longlist = data[data['player_positions'] == DDGPos][['short_name','wage_eur','value_eur','player_positions','overall','age']]

#Create a list of players that have a lower overall rank than DDG
removals = longlist[longlist['overall'] <= DDGRating].index
#Drop these players
longlist.drop(removals , inplace=True)

#Repeat above, but for players with a larger wage
removals = longlist[longlist['wage_eur'] > DDGWage].index
longlist.drop(removals , inplace=True)

#Repeat above, but for older players
removals = longlist[longlist['age'] >= DDGAge].index
longlist.drop(removals , inplace=True)

#Show me our potential replacements, sorted by lowest wages
longlist.sort_values("wage_eur")


def cheapReplacement(player, skillReduction = 0):
    
    #Get the replacee with the name provided in the argument
    replacee = data[data['short_name'] == player][['short_name','wage_eur','value_eur','player_positions','overall','age']]

    #Assign the relevant details of this player to variables
    replaceePos = replacee['player_positions'].item()
    replaceeWage = replacee['wage_eur'].item()
    replaceeAge = replacee['age'].item()
    replaceeOverall = replacee['overall'].item() - skillReduction
    
    #Create the longlist of players that share the position
    longlist = data[data['player_positions'] == replaceePos][['short_name','wage_eur','value_eur','player_positions','overall','age']]
    
    
    #Create list of players that do not meet the rating criteria and drop them from the longlist
    removals = longlist[longlist['overall'] <= replaceeOverall].index
    longlist.drop(removals , inplace=True)
    
    #Repeat for players with higher wages
    removals = longlist[longlist['wage_eur'] > replaceeWage].index
    longlist.drop(removals , inplace=True)

    #Repeat for older players
    removals = longlist[longlist['age'] >= replaceeAge].index
    longlist.drop(removals , inplace=True)
    
    #Display the players that meet the requirements
    return longlist.sort_values("wage_eur")<jupyter_output><empty_output><jupyter_text>* Fred's Cheap Replacement<jupyter_code>cheapReplacement('Fred')<jupyter_output>/home/abdoul-ma/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:7: FutureWarning: `item` has been deprecated and will be removed in a future version
  import sys
/home/abdoul-ma/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:8: FutureWarning: `item` has been deprecated and will be removed in a future version
  
/home/abdoul-ma/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:9: FutureWarning: `item` has been deprecated and will be removed in a future version
  if __name__ == '__main__':
/home/abdoul-ma/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:10: FutureWarning: `item` has been deprecated and will be removed in a future version
  # Remove the CWD from sys.path while we load stuff.<jupyter_text>* Lindelöf's Cheap Replacement<jupyter_code>cheapReplacement('<NAME>')<jupyter_output>/home/abdoul-ma/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:7: FutureWarning: `item` has been deprecated and will be removed in a future version
  import sys
/home/abdoul-ma/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:8: FutureWarning: `item` has been deprecated and will be removed in a future version
  
/home/abdoul-ma/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:9: FutureWarning: `item` has been deprecated and will be removed in a future version
  if __name__ == '__main__':
/home/abdoul-ma/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:10: FutureWarning: `item` has been deprecated and will be removed in a future version
  # Remove the CWD from sys.path while we load stuff.<jupyter_text>* Pogba's Cheap Reaplacement with a circumference of 8<jupyter_code>cheapReplacement('<NAME>', 8)<jupyter_output>/home/abdoul-ma/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:7: FutureWarning: `item` has been deprecated and will be removed in a future version
  import sys
/home/abdoul-ma/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:8: FutureWarning: `item` has been deprecated and will be removed in a future version
  
/home/abdoul-ma/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:9: FutureWarning: `item` has been deprecated and will be removed in a future version
  if __name__ == '__main__':
/home/abdoul-ma/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:10: FutureWarning: `item` has been deprecated and will be removed in a future version
  # Remove the CWD from sys.path while we load stuff.
{'fraction_non_alphanumeric': 0.11597633136094675, 'fraction_numerical': 0.011665257819103972, 'mean_word_length': 5.610055865921788, 'pattern_counts': {'":': 0, '<': 18, '<?xml version=': 0, '>': 20, 'https://': 2, 'lorem ipsum': 0, 'www.': 1, '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': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '282466', 'license': 'MIT', 'n_tokens_mistral': 2000, 'n_tokens_neox': 1912, 'n_words': 491, 'path': '.ipynb_checkpoints/Fifa_ratings-checkpoint.ipynb', 'repo_name': 'AbdoulMa/FIFA-20-Players-Database', 'size': '5922'}