{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "_You are currently looking at **version 1.0** 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._\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Assignment 2 - Pandas Introduction\n",
    "All questions are weighted the same in this assignment.\n",
    "## Part 1\n",
    "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. Use this dataset to answer the questions below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "nbgrader": {
     "grade": false,
     "grade_id": "1",
     "locked": false,
     "solution": false
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.read_csv('olympics.csv', index_col=0, skiprows=1)\n",
    "\n",
    "for col in df.columns:\n",
    "    if col[:2]=='01':\n",
    "        df.rename(columns={col:'Gold'+col[4:]}, inplace=True)\n",
    "    if col[:2]=='02':\n",
    "        df.rename(columns={col:'Silver'+col[4:]}, inplace=True)\n",
    "    if col[:2]=='03':\n",
    "        df.rename(columns={col:'Bronze'+col[4:]}, inplace=True)\n",
    "    if col[:1]=='№':\n",
    "        df.rename(columns={col:'#'+col[1:]}, inplace=True)\n",
    "\n",
    "names_ids = df.index.str.split('\\s\\(') # split the index by '('\n",
    "\n",
    "df.index = names_ids.str[0] # the [0] element is the country name (new index) \n",
    "df['ID'] = names_ids.str[1].str[:3] # the [1] element is the abbreviation or ID (take first 3 characters from that)\n",
    "\n",
    "df = df.drop('Totals')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Question 0 (Example)\n",
    "\n",
    "What is the first country in df?\n",
    "\n",
    "*This function should return a Series.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# You should write your whole answer within the function provided. The autograder will call\n",
    "# this function and compare the return value against the correct solution value\n",
    "def answer_zero():\n",
    "    # This function returns the row for Afghanistan, which is a Series object. The assignment\n",
    "    # question description will tell you the general format the autograder is expecting\n",
    "    return df.iloc[0]\n",
    "\n",
    "# You can examine what your function returns by calling it in the cell. If you have questions\n",
    "# about the assignment formats, check out the discussion forums for any FAQs\n",
    "#answer_zero() "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Question 1\n",
    "Which country has won the most gold medals in summer games?\n",
    "\n",
    "*This function should return a single string value.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "nbgrader": {
     "grade": false,
     "locked": false,
     "solution": false
    }
   },
   "outputs": [],
   "source": [
    "one_df = df.copy()\n",
    "one_df['country'] = one_df.index\n",
    "one_df = one_df.set_index('Gold')\n",
    "one_df = one_df.reset_index()\n",
    "\n",
    "def answer_one():\n",
    "    most_summer_gold = max(one_df['Gold'])\n",
    "    gold_df = one_df.where(one_df['Gold'] == most_summer_gold)\n",
    "    gold_df = gold_df.dropna()\n",
    "    #country = gold_mydf['country']\n",
    "    return gold_df['country'].iloc[0]\n",
    "answer_one()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Question 2\n",
    "Which country had the biggest difference between their summer and winter gold medal counts?\n",
    "\n",
    "*This function should return a single string value.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "two_df = df.copy()\n",
    "two_df['country'] = two_df.index\n",
    "two_df = two_df.set_index('Gold')\n",
    "two_df = two_df.reset_index()\n",
    "\n",
    "def answer_two():\n",
    "    summer_gold = two_df['Gold']\n",
    "    winter_gold = two_df['Gold.1']\n",
    "    abs_diff = abs(summer_gold - winter_gold)\n",
    "    two_df['diff'] = abs_diff\n",
    "    max_abs = max(two_df['diff'])\n",
    "    max_abs\n",
    "    max_abs_df = two_df.where(two_df['diff'] == max_abs)\n",
    "    max_abs_df = max_abs_df.dropna()\n",
    "    max_abs_df\n",
    "    return max_abs_df['country'].iloc[0]    \n",
    "answer_two()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Question 3\n",
    "Which country has the biggest difference between their summer and winter gold medal counts relative to their total gold medal count? Only include countries that have won at least 1 gold in both summer and winter.\n",
    "\n",
    "*This function should return a single string value.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "three_df = df.copy()\n",
    "three_df = three_df.where(three_df['Gold'] > 0)\n",
    "three_df = three_df.dropna()\n",
    "\n",
    "def answer_three():  \n",
    "    for i, j in three_df.iterrows():\n",
    "        summ = three_df.loc[i, 'Gold.2'] - three_df.loc[i, 'Gold']\n",
    "        wint = three_df.loc[i, 'Gold.2'] - three_df.loc[i, 'Gold.1']\n",
    "        three_df.loc[i, 'summ'] = summ\n",
    "        three_df.loc[i, 'wint'] = wint\n",
    "        \n",
    "    max_diff = max(max(three_df['summ']), max(three_df['wint']))\n",
    "    check = (three_df['summ'] == max_diff) | (three_df['wint'] == max_diff)\n",
    "    j = three_df.where(check == True).dropna()\n",
    "    return j.index[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Question 4\n",
    "Write a function to update the dataframe to include a new column called \"Points\" which is a weighted value where each gold medal counts for 3 points, silver medals for 2 points, and bronze mdeals for 1 point. The function should return only the column (a Series object) which you created.\n",
    "\n",
    "*This function should return a Series named `Points` of length 146*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "four_df = df.copy()\n",
    "\n",
    "def answer_four():    \n",
    "    for i, j in four_df.iterrows():\n",
    "        point = (3 * four_df.loc[i, 'Gold.2']) + (2 * four_df.loc[i, 'Silver.2']) + (1 * four_df.loc[i, 'Bronze.2'])\n",
    "        four_df.loc[i, 'Points'] = point\n",
    "    return four_df['Points'].astype(int)\n",
    "answer_four()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Part 2\n",
    "For the next set of questions, we will be using census data from the [United States Census Bureau](http://www.census.gov/popest/data/counties/totals/2015/CO-EST2015-alldata.html). 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](http://www.census.gov/popest/data/counties/totals/2015/files/CO-EST2015-alldata.pdf) for a description of the variable names.\n",
    "\n",
    "The census dataset (census.csv) should be loaded as census_df. Answer questions using this as appropriate.\n",
    "\n",
    "### Question 5\n",
    "Which state has the most counties in it? (hint: consider the sumlevel key carefully! You'll need this for future questions too...)\n",
    "\n",
    "*This function should return a single string value.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from operator import itemgetter \n",
    "census_df = pd.read_csv('census.csv')\n",
    "census_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "five_df = census_df.copy()\n",
    "columns_to_keep = ['STNAME',\n",
    "                   'CTYNAME',\n",
    "                   'STATE',\n",
    "                   'DIVISION',\n",
    "                   'COUNTY',\n",
    "                   'SUMLEV',\n",
    "                   'REGION',\n",
    "                   'POPESTIMATE2010',\n",
    "                   'POPESTIMATE2011',\n",
    "                   'POPESTIMATE2012',\n",
    "                   'POPESTIMATE2013',\n",
    "                   'POPESTIMATE2014',\n",
    "                   'POPESTIMATE2015']\n",
    "five_df = five_df[columns_to_keep]\n",
    "\n",
    "five_df = five_df[five_df['SUMLEV'] == 50]\n",
    "five_df = five_df.set_index(['STNAME', 'CTYNAME'])\n",
    "\n",
    "\n",
    "def answer_five():\n",
    "    k = ((five_df.loc[state]['STATE'].count(), state) for state in five_df.index.levels[0])\n",
    "    print(type(k))\n",
    "    sorted_k = sorted(k, key = itemgetter(0), reverse=True)\n",
    "    return sorted_k[0][1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Question 6\n",
    "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)?\n",
    "\n",
    "*This function should return a list of string values.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "six_df = census_df.copy()\n",
    "columns_to_keep = ['STNAME',\n",
    "                   'CTYNAME',\n",
    "                   'STATE',\n",
    "                   'DIVISION',\n",
    "                   'COUNTY',\n",
    "                   'SUMLEV',\n",
    "                   'REGION',\n",
    "                   'POPESTIMATE2010',\n",
    "                   'POPESTIMATE2011',\n",
    "                   'POPESTIMATE2012',\n",
    "                   'POPESTIMATE2013',\n",
    "                   'POPESTIMATE2014',\n",
    "                   'POPESTIMATE2015']\n",
    "six_df = six_df[columns_to_keep]\n",
    "six_df = six_df[six_df['SUMLEV'] == 50]\n",
    "six_df = six_df.set_index(['STNAME', 'CTYNAME'])\n",
    "\n",
    "def answer_six():\n",
    "    s = ((state, sum_3_most_pop(state)) for state in six_df.index.levels[0])\n",
    "    sorted_s = sorted(s, key = itemgetter(1), reverse=True)\n",
    "    return [j[0] for j in sorted_s[:3]]\n",
    "\n",
    "def sum_3_most_pop(state):\n",
    "    sorted_state = six_df.loc[state].sort(columns='POPESTIMATE2015', axis = 0, ascending=False)\n",
    "    return sum(sorted_state['POPESTIMATE2015'][:3])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Question 7\n",
    "Which county has had the largest change in population within the five year period (hint: population values are stored in columns POPESTIMATE2010 through POPESTIMATE2015, you need to consider all five columns)?\n",
    "\n",
    "*This function should return a single string value.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "seven_df = census_df.copy()\n",
    "seven_df = seven_df[seven_df['SUMLEV'] == 50]\n",
    "col_to_keep = ['CTYNAME', 'POPESTIMATE2010', 'POPESTIMATE2011', 'POPESTIMATE2012',\n",
    "               'POPESTIMATE2013', 'POPESTIMATE2014', 'POPESTIMATE2015']\n",
    "seven_df = seven_df[col_to_keep]\n",
    "seven_df['10-11'] = abs(seven_df['POPESTIMATE2010'] - seven_df['POPESTIMATE2011'])\n",
    "seven_df['11-12'] = abs(seven_df['POPESTIMATE2011'] - seven_df['POPESTIMATE2012'])\n",
    "seven_df['12-13'] = abs(seven_df['POPESTIMATE2012'] - seven_df['POPESTIMATE2013'])\n",
    "seven_df['13-14'] = abs(seven_df['POPESTIMATE2013'] - seven_df['POPESTIMATE2014'])\n",
    "seven_df['14-15'] = abs(seven_df['POPESTIMATE2014'] - seven_df['POPESTIMATE2015'])\n",
    "\n",
    "def answer_seven():\n",
    "    max_pop_change = max(max(seven_df['10-11']), max(seven_df['11-12']), max(seven_df['12-13']), \n",
    "                     max(seven_df['13-14']), max(seven_df['14-15']))\n",
    "    \n",
    "    check = (seven_df['10-11']==max_pop_change) | (seven_df['11-12']==max_pop_change) | (seven_df['12-13']==max_pop_change) | \\\n",
    "            (seven_df['13-14']==max_pop_change)| (seven_df['14-15']==max_pop_change)\n",
    "\n",
    "    j = seven_df.where(check == True).dropna()\n",
    "    return j['CTYNAME'].iloc[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#seven_df.where(seven_df['10-11'] == 95115).dropna()\n",
    "#k = seven_df[seven_df['10-11'] == 95115].index.tolist()\n",
    "#seven_df.loc[2667]['CTYNAME']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Question 8\n",
    "In this datafile, the United States is broken up into four regions using the \"REGION\" column. \n",
    "\n",
    "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.\n",
    "\n",
    "*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).*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "eight_df = census_df.copy()\n",
    "eight_df.head()\n",
    "\n",
    "def answer_eight():\n",
    "    eight_df = census_df.copy()\n",
    "    eight_df = eight_df.where((eight_df['REGION'] == 1) | (eight_df['REGION'] == 2))\n",
    "    eight_df = eight_df.where(eight_df['POPESTIMATE2015'] > eight_df['POPESTIMATE2014'])\n",
    "    eight_df  = eight_df.where(eight_df['CTYNAME'].str.startswith('Washington'))\n",
    "    columns_to_keep = ['STNAME', 'CTYNAME']\n",
    "    eight_df = eight_df[columns_to_keep]\n",
    "    return eight_df.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "coursera": {
   "course_slug": "python-data-analysis",
   "graded_item_id": "tHmgx",
   "launcher_item_id": "Um6Bz",
   "part_id": "OQsnr"
  },
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}