# coding: utf-8

# ---

#

# _You are currently looking at **version 1.2** of this notebook. To download notebooks and datafiles, as well as get help on Jupyter notebooks in the Coursera platform, visit the [Jupyter Notebook FAQ](https://www.coursera.org/learn/python-data-analysis/resources/0dhYG) course resource._

#

# ---

# # Assignment 2 - Pandas Introduction

# All questions are weighted the same in this assignment.

# ## Part 1

# The following code loads the olympics dataset (olympics.csv), which was derrived from the Wikipedia entry on [All Time Olympic Games Medals](https://en.wikipedia.org/wiki/All-time_Olympic_Games_medal_table), and does some basic data cleaning.

#

# The columns are organized as # of Summer games, Summer medals, # of Winter games, Winter medals, total # number of games, total # of medals. Use this dataset to answer the questions below.

# In[1]:

import pandas as pd

import operator

from heapq import nlargest

df = pd.read_csv('olympics.csv', index_col=0, skiprows=1)

for col in df.columns:

if col[:2]=='01':

df.rename(columns={col:'Gold'+col[4:]}, inplace=True)

if col[:2]=='02':

df.rename(columns={col:'Silver'+col[4:]}, inplace=True)

if col[:2]=='03':

df.rename(columns={col:'Bronze'+col[4:]}, inplace=True)

if col[:1]=='№':

df.rename(columns={col:'#'+col[1:]}, inplace=True)

names_ids = df.index.str.split('\s\(') # split the index by '('

df.index = names_ids.str[0] # the [0] element is the country name (new index)

df['ID'] = names_ids.str[1].str[:3] # the [1] element is the abbreviation or ID (take first 3 characters from that)

df = df.drop('Totals')

df

# ### Question 0 (Example)

#

# What is the first country in df?

#

# *This function should return a Series.*

# In[4]:

# You should write your whole answer within the function provided. The autograder will call

# this function and compare the return value against the correct solution value

def answer_zero():

# This function returns the row for Afghanistan, which is a Series object. The assignment

# question description will tell you the general format the autograder is expecting

return df.iloc[0]

# You can examine what your function returns by calling it in the cell. If you have questions

# about the assignment formats, check out the discussion forums for any FAQs

answer_zero()

# ### Question 1

# Which country has won the most gold medals in summer games?

#

# *This function should return a single string value.*

# In[16]:

def answer_one():

df1 = df['Gold']

max_gold = df1.max()

#print(max_gold)

#print(df1[df1 == max_gold])

return df1[df1 == max_gold].index[0]

# return count[count==maxval].index[0]

answer_one()

# ### Question 2

# Which country had the biggest difference between their summer and winter gold medal counts?

#

# *This function should return a single string value.*

# In[27]:

def answer_two():

df1 = abs(df['Gold'] - df['Gold.1'])

#print(df1)

max_diff = df1.max()

#print(max_diff)

return df1[df1==max_diff].index[0]

answer_two()

# ### Question 3

# Which country has the biggest difference between their summer gold medal counts and winter gold medal counts relative to their total gold medal count?

#

# $$\frac{Summer~Gold - Winter~Gold}{Total~Gold}$$

#

# Only include countries that have won at least 1 gold in both summer and winter.

#

# *This function should return a single string value.*

# In[74]:

def answer_three():

df1 = df[(df['Gold'] > 0) & (df['Gold.1'] > 0)]

#ctry = df1[]

#print("mine")

#print(df1.head())

df2 = (abs(df1['Gold'] - df1['Gold.1'])/df1['Gold.2'])

#df['preTestScore'].idxmax()

return df2.idxmax()

answer_three()

# ### Question 4

# Write a function that creates a Series called "Points" which is a weighted value where each gold medal (`Gold.2`) counts for 3 points, silver medals (`Silver.2`) for 2 points, and bronze medals (`Bronze.2`) for 1 point. The function should return only the column (a Series object) which you created, with the country names as indices.

#

# *This function should return a Series named `Points` of length 146*

# In[4]:

def answer_four():

Points = (df['Gold.2']*3 + df['Silver.2']*2 + df['Bronze.2'])

return Points

df1 = answer_four()

print(df1)

# ## Part 2

# For the next set of questions, we will be using census data from the [United States Census Bureau](http://www.census.gov). Counties are political and geographic subdivisions of states in the United States. This dataset contains population data for counties and states in the US from 2010 to 2015. [See this document](https://www2.census.gov/programs-surveys/popest/technical-documentation/file-layouts/2010-2015/co-est2015-alldata.pdf) for a description of the variable names.

#

# The census dataset (census.csv) should be loaded as census_df. Answer questions using this as appropriate.

#

# ### Question 5

# Which state has the most counties in it? (hint: consider the sumlevel key carefully! You'll need this for future questions too...)

#

# *This function should return a single string value.*

# In[35]:

census_df = pd.read_csv('census.csv')

census_df.head()

# In[6]:

def answer_five():

count = census_df['STNAME'].value_counts()

maxval = count.max()

return count[count==maxval].index[0]

df1 = answer_five()

df1

# ### Question 6

# **Only looking at the three most populous counties for each state**, what are the three most populous states (in order of highest population to lowest population)? Use `CENSUS2010POP`.

#

# *This function should return a list of string values.*

# In[71]:

def answer_six():

states = census_df['STNAME'].unique()

cnt = len(states)

#print(cnt)

df1 = census_df[census_df['SUMLEV'] == 50]

state_population_count = {}

for i in range(cnt):

df = df1[(df1['STNAME'] == states[i]) & df1['CENSUS2010POP']]

top_three = df.nlargest(3,['CENSUS2010POP'])

#print(states[i])

#print(top_three['CENSUS2010POP'].sum())

state_population_count[states[i]] = top_three['CENSUS2010POP'].sum()

#print(df)

#sorted_d = sorted(state_population_count.items(), key=operator.itemgetter(1), reverse = True)

ThreeHighest = nlargest(3, state_population_count, key = state_population_count.get)

#print(sorted_d)

return ThreeHighest

x = answer_six()

x

# ### Question 7

# Which county has had the largest absolute change in population within the period 2010-2015? (Hint: population values are stored in columns POPESTIMATE2010 through POPESTIMATE2015, you need to consider all six columns.)

#

# e.g. If County Population in the 5 year period is 100, 120, 80, 105, 100, 130, then its largest change in the period would be |130-80| = 50.

#

# *This function should return a single string value.*

# In[73]:

def answer_seven():

df = census_df[census_df['SUMLEV'] == 50]

df1 = df[['POPESTIMATE2010','POPESTIMATE2011','POPESTIMATE2012','POPESTIMATE2013','POPESTIMATE2014','POPESTIMATE2015']]

#print(df1.head())

county = census_df['CTYNAME']

df_max = df1.max(axis=1)

df_min = df1.min(axis=1)

#print(df_max.head())

#print(df_min.head())

df_diff = df_max - df_min

max_diff = df_diff.max()

#print(max_diff)

index_of_max_pop_county = df_diff[df_diff == max_diff].index[0]

#print(index_of_max_pop_county)

#print(county[index_of_max_pop_county])

#print(df_diff[index_of_max_pop_county])

return county[index_of_max_pop_county]

x = answer_seven()

x

# ### Question 8

# In this datafile, the United States is broken up into four regions using the "REGION" column.

#

# Create a query that finds the counties that belong to regions 1 or 2, whose name starts with 'Washington', and whose POPESTIMATE2015 was greater than their POPESTIMATE 2014.

#

# *This function should return a 5x2 DataFrame with the columns = ['STNAME', 'CTYNAME'] and the same index ID as the census_df (sorted ascending by index).*

# In[41]:

def answer_eight():

df = census_df[((census_df['REGION'] == 1) | (census_df['REGION'] == 2)) & (census_df['CTYNAME'] == "Washington County") & (census_df['POPESTIMATE2015']>census_df['POPESTIMATE2014'])]

ans = df[['STNAME','CTYNAME']]

return ans

x = answer_eight()

x