# This Python 3 environment comes with many helpful analytics libraries installed
# It is defined by the kaggle/python Docker image: https://github.com/kaggle/docker-python
# For example, here's several helpful packages to load

import numpy as np # linear algebra
import pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)

# Input data files are available in the read-only "../input/" directory
# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory

import os
for dirname, _, filenames in os.walk('/kaggle/input'):
    for filename in filenames:
        print(os.path.join(dirname, filename))

# You can write up to 20GB to the current directory (/kaggle/working/) that gets preserved as output when you create a version using "Save & Run All" 
# You can also write temporary files to /kaggle/temp/, but they won't be saved outside of the current session
data = pd.read_csv('/kaggle/input/120-years-of-olympic-history-athletes-and-results/athlete_events.csv')

data.head()
data.dtypes
data.isna().sum()
data.Medal = data.Medal.fillna('Loser')

data.isnull().sum()
data.head()
data.Age = data.Age.fillna(data.Age.mean())

data.isna().sum()
data.Height = data.Height.fillna(data.Height.mean())

data.isna().sum()
data.Weight = data.Weight.fillna(data.Weight.mean())

data.isna().sum()
"""
1. Select all data for football in Summer olympics from 2004 to 2016

2. Select all gold medalists in Summer olympics for Netherlands after 1996
"""
data[(data.Sport=='Football') & (data.Year<=2016) & (data.Year >= 2004) & (data.Season == 'Summer')]
data[(data.Medal == 'Gold') & (data.Season == 'Summer') & (data.Team == 'Netherlands') & (data.Year > 1996)]
"""
3. Select all Gold medalists from China with age less than 24 in Summer Olympics

4. Select all football players from Argentina and Spain who won atleast Silver on or after 2004 olympics

5. Select players from India who won Bronze medals after 1988 olympics
"""
data[(data.Medal == "Gold") & (data.Age < 24) &(data.Season == 'Summer') & (data.Team == 'China')]
# data[((data.Medal == "Gold") | (data.Medal =='Silver'))]
data.head()
data[data.Team=='India'].groupby(['Sport','Sex'])#.count()
"""
1. Which Team won most gold medals in Basketball?
"""
data[(data.Medal == 'Gold') & (data.Sport == 'Basketball')].groupby('Team').count()['Medal'].sort_values(ascending=False)
"""
2. In which sport India won the most gold medals after independence

3. In which city, Australia won the most gold medals?

4. How many Iranian females have won a medal before 1992? and how many after?

5. Which gender have won most medals for Israel in Summer Olympics?

6. Who is the oldest Indian to win a medal?
"""
data[(data.Team == 'India') & (data.Year > 1947) & (data.Medal == 'Gold')].groupby('Sport').count()['Medal']
data[(data.Team == 'Australia') & (data.Medal == 'Gold')].groupby('City').count()['Medal'].sort_values(ascending=False)
data[(data.Team == 'Iran') & (data.Sex == 'F') & (data.Medal != 'Loser') & (data.Year > 1992)].count()[0]
data[(data.Season == 'Summer') & (data.Team == 'Israel') & (data.Medal != 'Loser')].groupby('Sex').count().iloc[:,0]
data[(data.Medal !='Loser') & (data.Team == 'India')].sort_values('Age',ascending=False).iloc[0,[1,3]]