import numpy as np
import pandas as pd

"""
Explanation: 2.2 DataFrame
Content:
  - 2.2.1 DataFrame Structure
  - 2.2.2 Working with Columns
  - 2.2.3 Working with Rows
  - 2.2.4 Conditional Selection
  - 2.2.5 Case Study: Olympic Games
A DataFrame is a two dimensional data structure with columns of potentially different data types. It can be considered like a table or a spreadsheet, similar to R's data.frame.
A DataFrame has both row index and column index.
End of explanation
"""


# Create a DataFrame from dictionary
data = {'Region':['Central','East','North','North-East','West'],
        'Area':[132.7,93.1,134.5,103.9,201.3],
       'Population':[939890,693500,531860,834450,903010]}
df = pd.DataFrame(data)
# Display df
df

# Note that the row index are assigned automatically and column index are arranged in alphabetical order.
# Rearrange columns
df = pd.DataFrame(data,columns=['Region','Population','Area'])
df

# Display columns names
df.columns

# Display all values
df.values

# Get the number of rows and columns of the dataframe i.e. its shape
df.shape

# Size of DataFrame = row x column
df.size

# Number of rows
len(df)

# Programming specific information of the dataframe
df.info()

# Statistical description of numerical columns
df.describe()

"""
Explanation: 2.1.1 DataFrame Structure
Initializing a Dataframe.
End of explanation
"""


# Rename a column label
df = df.rename(columns={'Population':'Pop'})
df

# Select a single column to series
A = df['Area'] # same answer as df.Area
A

# Select a single column to dataframe
B = df[['Area']]
B

# Select multiple columns to dataframe
C = df[['Area','Pop']]
C

# Change order of columns
D = df[['Region','Area','Pop']]
D

# Drop a column by label
E = df.drop('Area',axis=1)
E

# Create a new column 'Density' = 'Population'/'Area'
df['Density'] = df['Pop']/df['Area']
df

# Sort values
df.sort_values(by=['Pop'], ascending=False)

# Find index label for max/min values
df['Density'].idxmax()

"""
Explanation: 2.2.2 Working with columns
End of explanation
"""


# method 1: 
df['Region'][df['Density'].idxmax()]

# method 2: Change the index
df1 = df.set_index('Region')
df1['Density'].idxmax()

"""
Explanation: Exercise: Which region has the highest density? Can you get the answer without sorting?
End of explanation
"""


# Get all numerical summaries of a column
df['Pop'].describe()

"""
Explanation: There are many commonly used column-wide methods/attributes:
- df['col'].size
- df['col'].count()
- df['col'].sum()
- df['col'].max()
- df['col'].mean()
- df['col'].std()
End of explanation
"""


# Select multiple rows
df[2:4]

# Select the last row
df[-1:]

# Select all but last row
df[:-1]

# Select all even rows
df[::2]

# Select by .iloc
df.iloc[0:2,1:3]

# Select by .loc
df.loc[0:1,['Pop','Area']]

"""
Explanation: 2.2.3 Working with rows
End of explanation
"""


# Boolean masking
df['Pop']>800000

# Select rows by boolean masking
df[df['Pop']>800000]

# Boolean masking with ==
df['Region']=='Central'

# Select rows by boolean masking
df[df['Region']=='Central']

# Using .loc to find the Area of the Central region.
df.loc[df['Region']=='Central', 'Area']

# Multiple conditions (and: &) (or: |)
(df['Pop'] < 800000) & (df['Density']<8000)

# Select rows by multiple conditions
df[(df['Pop'] < 800000) & (df['Density']<8000)]

# Using .query method

df.query("Pop < 800000 & Density < 8000")

"""
Explanation: 2.2.4 Conditional Selection
End of explanation
"""


# Import data from Excel file
og = pd.read_excel('OlympicGames.xlsx')
# Display first 5 rows of dataframe
og.head()

"""
Explanation: 2.2.5 Case Study: Olympic Games
We can import data from a csv file by using pd.read_csv. 
https://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_csv.html
We can import data from an Excel file by using pd.read_excel.
https://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_excel.html
Source of data: https://en.wikipedia.org/wiki/All-time_Olympic_Games_medal_table
End of explanation
"""


og.shape

"""
Explanation: Q1: How many rows and columns are there in the dataframe?
End of explanation
"""


#og.isnull().sum()
og.info()

"""
Explanation: Q2: Are there any missing values in the dataframe?
End of explanation
"""


og['Total'] = og['Gold']+og['Silver']+og['Bronze']
og.head()

"""
Explanation: Q3: Create a new column for total number of Olympic medals.
End of explanation
"""


og[og['Country']=='Singapore']

"""
Explanation: Q4: Select the row where Country = Singapore.
End of explanation
"""


#og[og['Gold'] == og['Gold'].max()]['Country']
og['Country'][og['Gold'].idxmax()]

"""
Explanation: Q5: Which country has won the highest number of Gold medals?
End of explanation
"""


len(og.query("Games >= 25").loc[:, "Country"])

"""
Explanation: Q6: How many countries participated in at least 25 Olympic games (100 years)? Return an integer.
End of explanation
"""


og.sort_values(by='Total', ascending=False)[0:3]['Country'].values

"""
Explanation: Q7: Which are the top 3 countries with highest total Olympic medals? 
Challenge! Can you return a list of countries in one line of code?
End of explanation
"""


og['Country'][og[og['Gold']==0]['Total'].idxmax()]
og.Country[og.query("Gold == 0").Total.idxmax()]

"""
Explanation: Q8: Out of the countries which have not won any Gold medals, which country has won the highest number of medals?
Challenge! Can you return the name of the country in one line of code?
End of explanation
"""


