With this set I need to find which team has scored the most goals in non-friendly games since 2010 (or line 31992).
I started by isolating non-friendly games with:
conditions = [df['tournament'] != ('Friendly')]
values = ['FIFAEVENT']
df['FE'] = np.select(conditions, values)
Don't know how to proceed from here tbh. Any help or suggestions is greatly appreciated.
Dataset : https://www.kaggle.com/martj42/international-football-results-from-1872-to-2017
You can try this:
import pandas as pd
import datetime
df = pd.read_csv('../input/international-football-results-from-1872-to-2017/results.csv')
df['date'] = pd.to_datetime(df['date']).dt.date
df_sub = df[(df.date>datetime.date(2009,12,31))&(df.tournament!='Friendly')] #Selecting dates from 2010 and excluding friendly matches
unique_teams = list(df_sub.home_team.unique())
unique_teams.extend(list(df_sub.away_team.unique()))
unique_teams = set(unique_teams) # finding the unique set of teams
goals = []
for team in unique_teams:
home_goal = df_sub[df_sub['home_team']==team]['home_score'].sum()
away_goal = df_sub[df_sub['away_team']==team]['away_score'].sum()
goals.append(home_goal+away_goal) # calculate and append total goal for each team
df_most_goals = pd.DataFrame(data={'teams':list(unique_teams),'total_goals':goals}) #creating a dataframe with team and the total goals scored
df_most_goals = df_most_goals.sort_values(by='total_goals',ascending=False) #Sorting based on descending order
df_most_goals = df_most_goals.reset_index(drop=True)
print(df_most_goals.head()) # printing top5 teams scored most number of goals after 2010