day71/Day71ExtraCredit.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Extra Credit\n",
    "\n",
    "The PayScale dataset used in this lesson was from 2008 and looked at the prior 10 years. Notice how Finance ranked very high on post-degree earnings at the time. However, we all know there was a massive financial crash in that year. Perhaps things have changed. Can you use what you've learnt about web scraping in the prior lessons (e.g., Day 45) and share some updated information from PayScale's website in the comments below?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "PAGE_URL = \"https://www.payscale.com/college-salary-report/majors-that-pay-you-back/bachelors\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import data from the first HTML table\n",
    "table = pd.read_html(PAGE_URL)\n",
    "df = table[0].copy()\n",
    "df.columns = [\"Rank\", \"Major\", \"Degree Type\", \"Early Career Pay\", \"Mid-Career Pay\", \"% High Meaning\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Rank</th>\n",
       "      <th>Major</th>\n",
       "      <th>Degree Type</th>\n",
       "      <th>Early Career Pay</th>\n",
       "      <th>Mid-Career Pay</th>\n",
       "      <th>% High Meaning</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Rank:1</td>\n",
       "      <td>Major:Petroleum Engineering</td>\n",
       "      <td>Degree Type:Bachelors</td>\n",
       "      <td>Early Career Pay:$92,300</td>\n",
       "      <td>Mid-Career Pay:$182,000</td>\n",
       "      <td>% High Meaning:69%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Rank:2</td>\n",
       "      <td>Major:Electrical Engineering &amp; Computer Scienc...</td>\n",
       "      <td>Degree Type:Bachelors</td>\n",
       "      <td>Early Career Pay:$101,200</td>\n",
       "      <td>Mid-Career Pay:$152,300</td>\n",
       "      <td>% High Meaning:46%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Rank:3</td>\n",
       "      <td>Major:Applied Economics and Management</td>\n",
       "      <td>Degree Type:Bachelors</td>\n",
       "      <td>Early Career Pay:$60,900</td>\n",
       "      <td>Mid-Career Pay:$139,600</td>\n",
       "      <td>% High Meaning:67%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Rank:3</td>\n",
       "      <td>Major:Operations Research</td>\n",
       "      <td>Degree Type:Bachelors</td>\n",
       "      <td>Early Career Pay:$78,400</td>\n",
       "      <td>Mid-Career Pay:$139,600</td>\n",
       "      <td>% High Meaning:52%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Rank:5</td>\n",
       "      <td>Major:Public Accounting</td>\n",
       "      <td>Degree Type:Bachelors</td>\n",
       "      <td>Early Career Pay:$60,000</td>\n",
       "      <td>Mid-Career Pay:$138,800</td>\n",
       "      <td>% High Meaning:49%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Rank:6</td>\n",
       "      <td>Major:Chemical Engineering/Materials Science &amp;...</td>\n",
       "      <td>Degree Type:Bachelors</td>\n",
       "      <td>Early Career Pay:$74,500</td>\n",
       "      <td>Mid-Career Pay:$137,800</td>\n",
       "      <td>% High Meaning:60%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Rank:7</td>\n",
       "      <td>Major:Quantitative Business Analysis</td>\n",
       "      <td>Degree Type:Bachelors</td>\n",
       "      <td>Early Career Pay:$67,900</td>\n",
       "      <td>Mid-Career Pay:$136,200</td>\n",
       "      <td>% High Meaning:55%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Rank:8</td>\n",
       "      <td>Major:Pharmacy</td>\n",
       "      <td>Degree Type:Bachelors</td>\n",
       "      <td>Early Career Pay:$66,300</td>\n",
       "      <td>Mid-Career Pay:$133,200</td>\n",
       "      <td>% High Meaning:79%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Rank:9</td>\n",
       "      <td>Major:Aeronautics &amp; Astronautics</td>\n",
       "      <td>Degree Type:Bachelors</td>\n",
       "      <td>Early Career Pay:$74,000</td>\n",
       "      <td>Mid-Career Pay:$133,100</td>\n",
       "      <td>% High Meaning:60%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Rank:10</td>\n",
       "      <td>Major:Systems Engineering</td>\n",
       "      <td>Degree Type:Bachelors</td>\n",
       "      <td>Early Career Pay:$74,000</td>\n",
       "      <td>Mid-Career Pay:$132,900</td>\n",
       "      <td>% High Meaning:54%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Rank:11</td>\n",
       "      <td>Major:Electrical Power Engineering</td>\n",
       "      <td>Degree Type:Bachelors</td>\n",
       "      <td>Early Career Pay:$73,100</td>\n",
       "      <td>Mid-Career Pay:$130,700</td>\n",
       "      <td>% High Meaning:67%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Rank:12</td>\n",
       "      <td>Major:Actuarial Mathematics</td>\n",
       "      <td>Degree Type:Bachelors</td>\n",
       "      <td>Early Career Pay:$61,900</td>\n",
       "      <td>Mid-Career Pay:$130,500</td>\n",
       "      <td>% High Meaning:50%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Rank:13</td>\n",
       "      <td>Major:Aerospace Studies</td>\n",
       "      <td>Degree Type:Bachelors</td>\n",
       "      <td>Early Career Pay:$50,300</td>\n",
       "      <td>Mid-Career Pay:$130,300</td>\n",
       "      <td>% High Meaning:-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Rank:14</td>\n",
       "      <td>Major:Chemical Engineering</td>\n",
       "      <td>Degree Type:Bachelors</td>\n",
       "      <td>Early Career Pay:$73,500</td>\n",
       "      <td>Mid-Career Pay:$128,900</td>\n",
       "      <td>% High Meaning:56%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Rank:15</td>\n",
       "      <td>Major:Actuarial Science</td>\n",
       "      <td>Degree Type:Bachelors</td>\n",
       "      <td>Early Career Pay:$64,700</td>\n",
       "      <td>Mid-Career Pay:$128,700</td>\n",
       "      <td>% High Meaning:42%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Rank:16</td>\n",
       "      <td>Major:Information &amp; Computer Science</td>\n",
       "      <td>Degree Type:Bachelors</td>\n",
       "      <td>Early Career Pay:$70,300</td>\n",
       "      <td>Mid-Career Pay:$127,600</td>\n",
       "      <td>% High Meaning:60%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Rank:17</td>\n",
       "      <td>Major:Aeronautical Engineering</td>\n",
       "      <td>Degree Type:Bachelors</td>\n",
       "      <td>Early Career Pay:$71,600</td>\n",
       "      <td>Mid-Career Pay:$125,900</td>\n",
       "      <td>% High Meaning:64%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Rank:18</td>\n",
       "      <td>Major:Operations &amp; Information Systems Management</td>\n",
       "      <td>Degree Type:Bachelors</td>\n",
       "      <td>Early Career Pay:$63,800</td>\n",
       "      <td>Mid-Career Pay:$125,100</td>\n",
       "      <td>% High Meaning:31%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Rank:18</td>\n",
       "      <td>Major:Political Economy</td>\n",
       "      <td>Degree Type:Bachelors</td>\n",
       "      <td>Early Career Pay:$58,200</td>\n",
       "      <td>Mid-Career Pay:$125,100</td>\n",
       "      <td>% High Meaning:34%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Rank:20</td>\n",
       "      <td>Major:Marine Engineering</td>\n",
       "      <td>Degree Type:Bachelors</td>\n",
       "      <td>Early Career Pay:$74,700</td>\n",
       "      <td>Mid-Career Pay:$125,000</td>\n",
       "      <td>% High Meaning:64%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Rank:21</td>\n",
       "      <td>Major:Computer Systems Engineering</td>\n",
       "      <td>Degree Type:Bachelors</td>\n",
       "      <td>Early Career Pay:$74,600</td>\n",
       "      <td>Mid-Career Pay:$124,800</td>\n",
       "      <td>% High Meaning:49%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Rank:22</td>\n",
       "      <td>Major:Nuclear Engineering</td>\n",
       "      <td>Degree Type:Bachelors</td>\n",
       "      <td>Early Career Pay:$73,100</td>\n",
       "      <td>Mid-Career Pay:$124,500</td>\n",
       "      <td>% High Meaning:63%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Rank:23</td>\n",
       "      <td>Major:Computer Science (CS) &amp; Engineering</td>\n",
       "      <td>Degree Type:Bachelors</td>\n",
       "      <td>Early Career Pay:$74,000</td>\n",
       "      <td>Mid-Career Pay:$124,000</td>\n",
       "      <td>% High Meaning:44%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Rank:24</td>\n",
       "      <td>Major:Corporate Accounting &amp; Finance</td>\n",
       "      <td>Degree Type:Bachelors</td>\n",
       "      <td>Early Career Pay:$61,200</td>\n",
       "      <td>Mid-Career Pay:$123,600</td>\n",
       "      <td>% High Meaning:57%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Rank:25</td>\n",
       "      <td>Major:Electrical &amp; Computer Engineering (ECE)</td>\n",
       "      <td>Degree Type:Bachelors</td>\n",
       "      <td>Early Career Pay:$73,900</td>\n",
       "      <td>Mid-Career Pay:$123,300</td>\n",
       "      <td>% High Meaning:49%</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Rank                                              Major  \\\n",
       "0    Rank:1                        Major:Petroleum Engineering   \n",
       "1    Rank:2  Major:Electrical Engineering & Computer Scienc...   \n",
       "2    Rank:3             Major:Applied Economics and Management   \n",
       "3    Rank:3                          Major:Operations Research   \n",
       "4    Rank:5                            Major:Public Accounting   \n",
       "5    Rank:6  Major:Chemical Engineering/Materials Science &...   \n",
       "6    Rank:7               Major:Quantitative Business Analysis   \n",
       "7    Rank:8                                     Major:Pharmacy   \n",
       "8    Rank:9                   Major:Aeronautics & Astronautics   \n",
       "9   Rank:10                          Major:Systems Engineering   \n",
       "10  Rank:11                 Major:Electrical Power Engineering   \n",
       "11  Rank:12                        Major:Actuarial Mathematics   \n",
       "12  Rank:13                            Major:Aerospace Studies   \n",
       "13  Rank:14                         Major:Chemical Engineering   \n",
       "14  Rank:15                            Major:Actuarial Science   \n",
       "15  Rank:16               Major:Information & Computer Science   \n",
       "16  Rank:17                     Major:Aeronautical Engineering   \n",
       "17  Rank:18  Major:Operations & Information Systems Management   \n",
       "18  Rank:18                            Major:Political Economy   \n",
       "19  Rank:20                           Major:Marine Engineering   \n",
       "20  Rank:21                 Major:Computer Systems Engineering   \n",
       "21  Rank:22                          Major:Nuclear Engineering   \n",
       "22  Rank:23          Major:Computer Science (CS) & Engineering   \n",
       "23  Rank:24               Major:Corporate Accounting & Finance   \n",
       "24  Rank:25      Major:Electrical & Computer Engineering (ECE)   \n",
       "\n",
       "              Degree Type           Early Career Pay           Mid-Career Pay  \\\n",
       "0   Degree Type:Bachelors   Early Career Pay:$92,300  Mid-Career Pay:$182,000   \n",
       "1   Degree Type:Bachelors  Early Career Pay:$101,200  Mid-Career Pay:$152,300   \n",
       "2   Degree Type:Bachelors   Early Career Pay:$60,900  Mid-Career Pay:$139,600   \n",
       "3   Degree Type:Bachelors   Early Career Pay:$78,400  Mid-Career Pay:$139,600   \n",
       "4   Degree Type:Bachelors   Early Career Pay:$60,000  Mid-Career Pay:$138,800   \n",
       "5   Degree Type:Bachelors   Early Career Pay:$74,500  Mid-Career Pay:$137,800   \n",
       "6   Degree Type:Bachelors   Early Career Pay:$67,900  Mid-Career Pay:$136,200   \n",
       "7   Degree Type:Bachelors   Early Career Pay:$66,300  Mid-Career Pay:$133,200   \n",
       "8   Degree Type:Bachelors   Early Career Pay:$74,000  Mid-Career Pay:$133,100   \n",
       "9   Degree Type:Bachelors   Early Career Pay:$74,000  Mid-Career Pay:$132,900   \n",
       "10  Degree Type:Bachelors   Early Career Pay:$73,100  Mid-Career Pay:$130,700   \n",
       "11  Degree Type:Bachelors   Early Career Pay:$61,900  Mid-Career Pay:$130,500   \n",
       "12  Degree Type:Bachelors   Early Career Pay:$50,300  Mid-Career Pay:$130,300   \n",
       "13  Degree Type:Bachelors   Early Career Pay:$73,500  Mid-Career Pay:$128,900   \n",
       "14  Degree Type:Bachelors   Early Career Pay:$64,700  Mid-Career Pay:$128,700   \n",
       "15  Degree Type:Bachelors   Early Career Pay:$70,300  Mid-Career Pay:$127,600   \n",
       "16  Degree Type:Bachelors   Early Career Pay:$71,600  Mid-Career Pay:$125,900   \n",
       "17  Degree Type:Bachelors   Early Career Pay:$63,800  Mid-Career Pay:$125,100   \n",
       "18  Degree Type:Bachelors   Early Career Pay:$58,200  Mid-Career Pay:$125,100   \n",
       "19  Degree Type:Bachelors   Early Career Pay:$74,700  Mid-Career Pay:$125,000   \n",
       "20  Degree Type:Bachelors   Early Career Pay:$74,600  Mid-Career Pay:$124,800   \n",
       "21  Degree Type:Bachelors   Early Career Pay:$73,100  Mid-Career Pay:$124,500   \n",
       "22  Degree Type:Bachelors   Early Career Pay:$74,000  Mid-Career Pay:$124,000   \n",
       "23  Degree Type:Bachelors   Early Career Pay:$61,200  Mid-Career Pay:$123,600   \n",
       "24  Degree Type:Bachelors   Early Career Pay:$73,900  Mid-Career Pay:$123,300   \n",
       "\n",
       "        % High Meaning  \n",
       "0   % High Meaning:69%  \n",
       "1   % High Meaning:46%  \n",
       "2   % High Meaning:67%  \n",
       "3   % High Meaning:52%  \n",
       "4   % High Meaning:49%  \n",
       "5   % High Meaning:60%  \n",
       "6   % High Meaning:55%  \n",
       "7   % High Meaning:79%  \n",
       "8   % High Meaning:60%  \n",
       "9   % High Meaning:54%  \n",
       "10  % High Meaning:67%  \n",
       "11  % High Meaning:50%  \n",
       "12    % High Meaning:-  \n",
       "13  % High Meaning:56%  \n",
       "14  % High Meaning:42%  \n",
       "15  % High Meaning:60%  \n",
       "16  % High Meaning:64%  \n",
       "17  % High Meaning:31%  \n",
       "18  % High Meaning:34%  \n",
       "19  % High Meaning:64%  \n",
       "20  % High Meaning:49%  \n",
       "21  % High Meaning:63%  \n",
       "22  % High Meaning:44%  \n",
       "23  % High Meaning:57%  \n",
       "24  % High Meaning:49%  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check the created dataframe\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import and append data from HTML tables on pages 2-34\n",
    "for page in range(2, 35):\n",
    "    table = pd.read_html(f\"{PAGE_URL}/page/{page}\")\n",
    "    page_df = table[0].copy()\n",
    "    page_df.columns = [\"Rank\", \"Major\", \"Degree Type\", \"Early Career Pay\", \"Mid-Career Pay\", \"% High Meaning\"]\n",
    "    df = df.append(page_df, ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Rank</th>\n",
       "      <th>Major</th>\n",
       "      <th>Degree Type</th>\n",
       "      <th>Early Career Pay</th>\n",
       "      <th>Mid-Career Pay</th>\n",
       "      <th>% High Meaning</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>834</td>\n",
       "      <td>834</td>\n",
       "      <td>834</td>\n",
       "      <td>834</td>\n",
       "      <td>834</td>\n",
       "      <td>834</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique</th>\n",
       "      <td>501</td>\n",
       "      <td>834</td>\n",
       "      <td>1</td>\n",
       "      <td>318</td>\n",
       "      <td>501</td>\n",
       "      <td>69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>top</th>\n",
       "      <td>Rank:488</td>\n",
       "      <td>Major:Electronics</td>\n",
       "      <td>Degree Type:Bachelors</td>\n",
       "      <td>Early Career Pay:$43,900</td>\n",
       "      <td>Mid-Career Pay:$76,200</td>\n",
       "      <td>% High Meaning:-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>freq</th>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>834</td>\n",
       "      <td>9</td>\n",
       "      <td>6</td>\n",
       "      <td>51</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Rank              Major            Degree Type  \\\n",
       "count        834                834                    834   \n",
       "unique       501                834                      1   \n",
       "top     Rank:488  Major:Electronics  Degree Type:Bachelors   \n",
       "freq           6                  1                    834   \n",
       "\n",
       "                Early Career Pay          Mid-Career Pay    % High Meaning  \n",
       "count                        834                     834               834  \n",
       "unique                       318                     501                69  \n",
       "top     Early Career Pay:$43,900  Mid-Career Pay:$76,200  % High Meaning:-  \n",
       "freq                           9                       6                51  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# confirm that all rows were imported\n",
    "df.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Rank</th>\n",
       "      <th>Major</th>\n",
       "      <th>Degree Type</th>\n",
       "      <th>Early Career Pay</th>\n",
       "      <th>Mid-Career Pay</th>\n",
       "      <th>% High Meaning</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Petroleum Engineering</td>\n",
       "      <td>Bachelors</td>\n",
       "      <td>92300</td>\n",
       "      <td>182000</td>\n",
       "      <td>69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Electrical Engineering &amp; Computer Science (EECS)</td>\n",
       "      <td>Bachelors</td>\n",
       "      <td>101200</td>\n",
       "      <td>152300</td>\n",
       "      <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Applied Economics and Management</td>\n",
       "      <td>Bachelors</td>\n",
       "      <td>60900</td>\n",
       "      <td>139600</td>\n",
       "      <td>67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>Operations Research</td>\n",
       "      <td>Bachelors</td>\n",
       "      <td>78400</td>\n",
       "      <td>139600</td>\n",
       "      <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Public Accounting</td>\n",
       "      <td>Bachelors</td>\n",
       "      <td>60000</td>\n",
       "      <td>138800</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Rank                                             Major Degree Type  \\\n",
       "0    1                             Petroleum Engineering   Bachelors   \n",
       "1    2  Electrical Engineering & Computer Science (EECS)   Bachelors   \n",
       "2    3                  Applied Economics and Management   Bachelors   \n",
       "3    3                               Operations Research   Bachelors   \n",
       "4    5                                 Public Accounting   Bachelors   \n",
       "\n",
       "  Early Career Pay Mid-Career Pay % High Meaning  \n",
       "0            92300         182000             69  \n",
       "1           101200         152300             46  \n",
       "2            60900         139600             67  \n",
       "3            78400         139600             52  \n",
       "4            60000         138800             49  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# clean up unnecessary text in each column\n",
    "df[\"Rank\"] = df[\"Rank\"].str.replace(\"Rank:\", \"\")\n",
    "df[\"Major\"] = df[\"Major\"].str.replace(\"Major:\", \"\")\n",
    "df[\"Degree Type\"] = df[\"Degree Type\"].str.replace(\"Degree Type:\", \"\")\n",
    "# so $ gets treated as a character\n",
    "df[\"Early Career Pay\"] = df[\"Early Career Pay\"].str.replace(\"Early Career Pay:$\", \"\", regex=False)\n",
    "df[\"Early Career Pay\"] = df[\"Early Career Pay\"].str.replace(\",\", \"\")\n",
    "df[\"Mid-Career Pay\"] = df[\"Mid-Career Pay\"].str.replace(\"Mid-Career Pay:$\", \"\", regex=False)\n",
    "df[\"Mid-Career Pay\"] = df[\"Mid-Career Pay\"].str.replace(\",\", \"\")\n",
    "df[\"% High Meaning\"] = df[\"% High Meaning\"].str.replace(\"% High Meaning:\", \"\")\n",
    "df[\"% High Meaning\"] = df[\"% High Meaning\"].str.replace(\"%\", \"\")\n",
    "\n",
    "# confirm changes\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Rank                object\n",
       "Major               object\n",
       "Degree Type         object\n",
       "Early Career Pay    object\n",
       "Mid-Career Pay      object\n",
       "% High Meaning      object\n",
       "dtype: object"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check the data types for each column\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# convert data types\n",
    "df[\"Rank\"] = df[\"Rank\"].astype(int)\n",
    "df[\"Early Career Pay\"] = df[\"Early Career Pay\"].astype(int)\n",
    "df[\"Mid-Career Pay\"] = df[\"Mid-Career Pay\"].astype(int)\n",
    "# can't convert the \"% High Meaning\" column since it has \"-\" in some rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Rank                 int32\n",
       "Major               object\n",
       "Degree Type         object\n",
       "Early Career Pay     int32\n",
       "Mid-Career Pay       int32\n",
       "% High Meaning      object\n",
       "dtype: object"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# confirm changes\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Rank</th>\n",
       "      <th>Major</th>\n",
       "      <th>Degree Type</th>\n",
       "      <th>Early Career Pay</th>\n",
       "      <th>Mid-Career Pay</th>\n",
       "      <th>% High Meaning</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Petroleum Engineering</td>\n",
       "      <td>Bachelors</td>\n",
       "      <td>92300</td>\n",
       "      <td>182000</td>\n",
       "      <td>69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Electrical Engineering &amp; Computer Science (EECS)</td>\n",
       "      <td>Bachelors</td>\n",
       "      <td>101200</td>\n",
       "      <td>152300</td>\n",
       "      <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Applied Economics and Management</td>\n",
       "      <td>Bachelors</td>\n",
       "      <td>60900</td>\n",
       "      <td>139600</td>\n",
       "      <td>67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>Operations Research</td>\n",
       "      <td>Bachelors</td>\n",
       "      <td>78400</td>\n",
       "      <td>139600</td>\n",
       "      <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Public Accounting</td>\n",
       "      <td>Bachelors</td>\n",
       "      <td>60000</td>\n",
       "      <td>138800</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Rank                                             Major Degree Type  \\\n",
       "0     1                             Petroleum Engineering   Bachelors   \n",
       "1     2  Electrical Engineering & Computer Science (EECS)   Bachelors   \n",
       "2     3                  Applied Economics and Management   Bachelors   \n",
       "3     3                               Operations Research   Bachelors   \n",
       "4     5                                 Public Accounting   Bachelors   \n",
       "\n",
       "   Early Career Pay  Mid-Career Pay % High Meaning  \n",
       "0             92300          182000             69  \n",
       "1            101200          152300             46  \n",
       "2             60900          139600             67  \n",
       "3             78400          139600             52  \n",
       "4             60000          138800             49  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# cleanup complete, check one more time\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# save the dataframe into a CSV file\n",
    "df.to_csv(\"salaries_by_college_major_20210208.csv\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Major</th>\n",
       "      <th>Early Career Pay</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Electrical Engineering &amp; Computer Science (EECS)</td>\n",
       "      <td>101200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>Physician Assistant Studies</td>\n",
       "      <td>92900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Petroleum Engineering</td>\n",
       "      <td>92300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>Metallurgical Engineering</td>\n",
       "      <td>79100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Operations Research</td>\n",
       "      <td>78400</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                               Major  Early Career Pay\n",
       "1   Electrical Engineering & Computer Science (EECS)            101200\n",
       "74                       Physician Assistant Studies             92900\n",
       "0                              Petroleum Engineering             92300\n",
       "49                         Metallurgical Engineering             79100\n",
       "3                                Operations Research             78400"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# top 5 with the highest early career pay\n",
    "early_max = df.sort_values(\"Early Career Pay\", ascending=False)\n",
    "early_max[[\"Major\", \"Early Career Pay\"]].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Major</th>\n",
       "      <th>Mid-Career Pay</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Petroleum Engineering</td>\n",
       "      <td>182000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Electrical Engineering &amp; Computer Science (EECS)</td>\n",
       "      <td>152300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Applied Economics and Management</td>\n",
       "      <td>139600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Operations Research</td>\n",
       "      <td>139600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Public Accounting</td>\n",
       "      <td>138800</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                              Major  Mid-Career Pay\n",
       "0                             Petroleum Engineering          182000\n",
       "1  Electrical Engineering & Computer Science (EECS)          152300\n",
       "2                  Applied Economics and Management          139600\n",
       "3                               Operations Research          139600\n",
       "4                                 Public Accounting          138800"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# top 5 with the highest mid-career pay\n",
    "mid_max = df.sort_values(\"Mid-Career Pay\", ascending=False)\n",
    "mid_max[[\"Major\", \"Mid-Career Pay\"]].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Major</th>\n",
       "      <th>Early Career Pay</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>785</th>\n",
       "      <td>Rehabilitation Services</td>\n",
       "      <td>34100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>828</th>\n",
       "      <td>Child &amp; Family Studies</td>\n",
       "      <td>34100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>814</th>\n",
       "      <td>Voice &amp; Opera</td>\n",
       "      <td>32900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>571</th>\n",
       "      <td>Painting &amp; Printmaking</td>\n",
       "      <td>32800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>707</th>\n",
       "      <td>Developmental Psychology</td>\n",
       "      <td>31000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        Major  Early Career Pay\n",
       "785   Rehabilitation Services             34100\n",
       "828    Child & Family Studies             34100\n",
       "814             Voice & Opera             32900\n",
       "571    Painting & Printmaking             32800\n",
       "707  Developmental Psychology             31000"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# bottom 5 with the lowest early career pay\n",
    "early_min = df.sort_values(\"Early Career Pay\", ascending=False)\n",
    "early_min[[\"Major\", \"Early Career Pay\"]].tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Major</th>\n",
       "      <th>Mid-Career Pay</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>829</th>\n",
       "      <td>Early Childhood Education</td>\n",
       "      <td>43300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>830</th>\n",
       "      <td>Mental Health</td>\n",
       "      <td>42500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>831</th>\n",
       "      <td>Medical Assisting</td>\n",
       "      <td>42300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>832</th>\n",
       "      <td>Addictions Counseling</td>\n",
       "      <td>42200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>833</th>\n",
       "      <td>Metalsmithing</td>\n",
       "      <td>38400</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         Major  Mid-Career Pay\n",
       "829  Early Childhood Education           43300\n",
       "830              Mental Health           42500\n",
       "831          Medical Assisting           42300\n",
       "832      Addictions Counseling           42200\n",
       "833              Metalsmithing           38400"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# bottom 5 with the lowest mid-career pay\n",
    "mid_min = df.sort_values(\"Mid-Career Pay\", ascending=False)\n",
    "mid_min[[\"Major\", \"Mid-Career Pay\"]].tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "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.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}