15-problem-begin_data_indexing_and_selection.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The data in the csv file was extracted from a [spreadheet](https://collegecost.ed.gov/wwwroot/documents/CATClists2014.xlsx) made available by the [U.S. Department of Education College Affordability and Transparency Center](https://collegecost.ed.gov/).\n",
    "It contains 2014-2015 tuition and fees for universities and colleges in the USA. The data is loaded into the data frame `data`"
   ]
  },
  {
   "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>Sector</th>\n",
       "      <th>Sector name</th>\n",
       "      <th>UnitID</th>\n",
       "      <th>OPEID</th>\n",
       "      <th>State</th>\n",
       "      <th>2014-15 Tuition and fees</th>\n",
       "      <th>List A: High tuition and fee indicator</th>\n",
       "      <th>List E: Low tuition and fee indicator</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Name of institution</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>University of Pittsburgh-Pittsburgh Campus</th>\n",
       "      <td>1</td>\n",
       "      <td>4-year, public</td>\n",
       "      <td>215293</td>\n",
       "      <td>337900</td>\n",
       "      <td>PA</td>\n",
       "      <td>17772.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>College of William and Mary</th>\n",
       "      <td>1</td>\n",
       "      <td>4-year, public</td>\n",
       "      <td>231624</td>\n",
       "      <td>370500</td>\n",
       "      <td>VA</td>\n",
       "      <td>17656.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pennsylvania State University-Main Campus</th>\n",
       "      <td>1</td>\n",
       "      <td>4-year, public</td>\n",
       "      <td>214777</td>\n",
       "      <td>332900</td>\n",
       "      <td>PA</td>\n",
       "      <td>17502.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Colorado School of Mines</th>\n",
       "      <td>1</td>\n",
       "      <td>4-year, public</td>\n",
       "      <td>126775</td>\n",
       "      <td>134800</td>\n",
       "      <td>CO</td>\n",
       "      <td>16918.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>University of New Hampshire-Main Campus</th>\n",
       "      <td>1</td>\n",
       "      <td>4-year, public</td>\n",
       "      <td>183044</td>\n",
       "      <td>258900</td>\n",
       "      <td>NH</td>\n",
       "      <td>16552.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            Sector     Sector name  UnitID  \\\n",
       "Name of institution                                                          \n",
       "University of Pittsburgh-Pittsburgh Campus       1  4-year, public  215293   \n",
       "College of William and Mary                      1  4-year, public  231624   \n",
       "Pennsylvania State University-Main Campus        1  4-year, public  214777   \n",
       "Colorado School of Mines                         1  4-year, public  126775   \n",
       "University of New Hampshire-Main Campus          1  4-year, public  183044   \n",
       "\n",
       "                                             OPEID State  \\\n",
       "Name of institution                                        \n",
       "University of Pittsburgh-Pittsburgh Campus  337900    PA   \n",
       "College of William and Mary                 370500    VA   \n",
       "Pennsylvania State University-Main Campus   332900    PA   \n",
       "Colorado School of Mines                    134800    CO   \n",
       "University of New Hampshire-Main Campus     258900    NH   \n",
       "\n",
       "                                            2014-15 Tuition and fees  \\\n",
       "Name of institution                                                    \n",
       "University of Pittsburgh-Pittsburgh Campus                   17772.0   \n",
       "College of William and Mary                                  17656.0   \n",
       "Pennsylvania State University-Main Campus                    17502.0   \n",
       "Colorado School of Mines                                     16918.0   \n",
       "University of New Hampshire-Main Campus                      16552.0   \n",
       "\n",
       "                                            List A: High tuition and fee indicator  \\\n",
       "Name of institution                                                                  \n",
       "University of Pittsburgh-Pittsburgh Campus                                       1   \n",
       "College of William and Mary                                                      1   \n",
       "Pennsylvania State University-Main Campus                                        1   \n",
       "Colorado School of Mines                                                         1   \n",
       "University of New Hampshire-Main Campus                                          1   \n",
       "\n",
       "                                            List E: Low tuition and fee indicator  \n",
       "Name of institution                                                                \n",
       "University of Pittsburgh-Pittsburgh Campus                                      0  \n",
       "College of William and Mary                                                     0  \n",
       "Pennsylvania State University-Main Campus                                       0  \n",
       "Colorado School of Mines                                                        0  \n",
       "University of New Hampshire-Main Campus                                         0  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = pd.read_csv(\"data/college_tuition.csv\").set_index(\"Name of institution\")\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Sector                                      int64\n",
       "Sector name                                object\n",
       "UnitID                                      int64\n",
       "OPEID                                       int64\n",
       "Name of institution                        object\n",
       "State                                      object\n",
       "2014-15 Tuition and fees                  float64\n",
       "List A: High tuition and fee indicator      int64\n",
       "List E: Low tuition and fee indicator       int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Indexing and selection with Series\n",
    "\n",
    "First we are going to focus on one column in the data frame and extract it into the series `tuition`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Name of institution\n",
       "University of Pittsburgh-Pittsburgh Campus    17772.0\n",
       "College of William and Mary                   17656.0\n",
       "Pennsylvania State University-Main Campus     17502.0\n",
       "Colorado School of Mines                      16918.0\n",
       "University of New Hampshire-Main Campus       16552.0\n",
       "                                               ...   \n",
       "Industrial Technical College                   6480.0\n",
       "American Educational College                   6422.0\n",
       "Future-Tech Institute                          6279.0\n",
       "Rosslyn Training Academy of Cosmetology        6139.0\n",
       "InterAmerican Technical Institute              2550.0\n",
       "Name: 2014-15 Tuition and fees, Length: 4140, dtype: float64"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tuition = data[\"2014-15 Tuition and fees\"]\n",
    "tuition"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Select the first five entries in the series\n",
    "- Select the last five entries in the series\n",
    "- Select every tenth entry (10% sampling)\n",
    "- What is the tuition of the `'University of Georgia'`?\n",
    "- Select the subseries containing the tuition fees of `'Harvard University'` and `'Massachusetts Institute of Technology'`.\n",
    "- Select the subseries containing tuitions greater than `50000`\n",
    "- Select the subseries containing tuition <= `50000` but greater than `40000`\n",
    "- (Challenge) Select the tuition of institutes of technology, that is colleges with names ending with `Institute of Technology`\n",
    "- (Challenge) What is the cheapest college in `Georgia`? **Hint** you can use the function `sort_values` to sort the series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Indexing and selection with DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>Sector</th>\n",
       "      <th>Sector name</th>\n",
       "      <th>UnitID</th>\n",
       "      <th>OPEID</th>\n",
       "      <th>State</th>\n",
       "      <th>2014-15 Tuition and fees</th>\n",
       "      <th>List A: High tuition and fee indicator</th>\n",
       "      <th>List E: Low tuition and fee indicator</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Name of institution</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>University of Pittsburgh-Pittsburgh Campus</th>\n",
       "      <td>1</td>\n",
       "      <td>4-year, public</td>\n",
       "      <td>215293</td>\n",
       "      <td>337900</td>\n",
       "      <td>PA</td>\n",
       "      <td>17772.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>College of William and Mary</th>\n",
       "      <td>1</td>\n",
       "      <td>4-year, public</td>\n",
       "      <td>231624</td>\n",
       "      <td>370500</td>\n",
       "      <td>VA</td>\n",
       "      <td>17656.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pennsylvania State University-Main Campus</th>\n",
       "      <td>1</td>\n",
       "      <td>4-year, public</td>\n",
       "      <td>214777</td>\n",
       "      <td>332900</td>\n",
       "      <td>PA</td>\n",
       "      <td>17502.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Colorado School of Mines</th>\n",
       "      <td>1</td>\n",
       "      <td>4-year, public</td>\n",
       "      <td>126775</td>\n",
       "      <td>134800</td>\n",
       "      <td>CO</td>\n",
       "      <td>16918.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>University of New Hampshire-Main Campus</th>\n",
       "      <td>1</td>\n",
       "      <td>4-year, public</td>\n",
       "      <td>183044</td>\n",
       "      <td>258900</td>\n",
       "      <td>NH</td>\n",
       "      <td>16552.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            Sector     Sector name  UnitID  \\\n",
       "Name of institution                                                          \n",
       "University of Pittsburgh-Pittsburgh Campus       1  4-year, public  215293   \n",
       "College of William and Mary                      1  4-year, public  231624   \n",
       "Pennsylvania State University-Main Campus        1  4-year, public  214777   \n",
       "Colorado School of Mines                         1  4-year, public  126775   \n",
       "University of New Hampshire-Main Campus          1  4-year, public  183044   \n",
       "\n",
       "                                             OPEID State  \\\n",
       "Name of institution                                        \n",
       "University of Pittsburgh-Pittsburgh Campus  337900    PA   \n",
       "College of William and Mary                 370500    VA   \n",
       "Pennsylvania State University-Main Campus   332900    PA   \n",
       "Colorado School of Mines                    134800    CO   \n",
       "University of New Hampshire-Main Campus     258900    NH   \n",
       "\n",
       "                                            2014-15 Tuition and fees  \\\n",
       "Name of institution                                                    \n",
       "University of Pittsburgh-Pittsburgh Campus                   17772.0   \n",
       "College of William and Mary                                  17656.0   \n",
       "Pennsylvania State University-Main Campus                    17502.0   \n",
       "Colorado School of Mines                                     16918.0   \n",
       "University of New Hampshire-Main Campus                      16552.0   \n",
       "\n",
       "                                            List A: High tuition and fee indicator  \\\n",
       "Name of institution                                                                  \n",
       "University of Pittsburgh-Pittsburgh Campus                                       1   \n",
       "College of William and Mary                                                      1   \n",
       "Pennsylvania State University-Main Campus                                        1   \n",
       "Colorado School of Mines                                                         1   \n",
       "University of New Hampshire-Main Campus                                          1   \n",
       "\n",
       "                                            List E: Low tuition and fee indicator  \n",
       "Name of institution                                                                \n",
       "University of Pittsburgh-Pittsburgh Campus                                      0  \n",
       "College of William and Mary                                                     0  \n",
       "Pennsylvania State University-Main Campus                                       0  \n",
       "Colorado School of Mines                                                        0  \n",
       "University of New Hampshire-Main Campus                                         0  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Extract `State` and `Sector name` in a new data frame\n",
    "- Extract the row pertaining to the `'University of Georgia'`\n",
    "- What is the data type of the extract row?\n",
    "- Extract two rows pertaning to 'Harvard University' and 'Massachusetts Institute of Technology'.\n",
    "- What is the data type of the extracted rows\n",
    "- Extract the `State` of 'Harvard University' and 'Massachusetts Institute of Technology'.\n",
    "- What is the data type of what you extracted?\n",
    "- What is the tuition fee for institutions in `GA`?\n",
    "- What is the average tuition fee for institutions in `GA`?\n",
    "- Extract the last two columns\n",
    "- Extract the last first five rows and last two columns\n",
    "- Sample 10% of the data using slicing\n",
    "- (Challenge) In which state is the institution of the highest tuition fee located?\n",
    "- (Challenge) What state has the highest average tuition fee?"
   ]
  },
  {
   "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}