sample_midterm1.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SI 330: Sample Midterm\n",
    "\n",
    "\n",
    "You have 80 minutes to complete this exam."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Question 1: Data Cleaning\n",
    "Listed below are all the 36 known earthquakes with an estimated magnitude of 8.5 or higher since the year 1500. But like most stuff from Wikipedia, it's in need of some data cleaning in order to be useful."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Part A\n",
    "We need to transform this wikipedia dataset into a well-formated table with the following columns:\n",
    "1. **year**: The year of each earthquake (1960, 1964, 2004, etc.)\n",
    "2. **month**: The month of each earthquake (May, March, December, etc.)\n",
    "3. **day**: The day of each earthquake (22, 27, 26, etc.)\n",
    "4. **magnitude**: The magnitude of each earthquake. If the magnitude has a range format (9.4-9.6), compute the average magnitude (9.5). Citation numbers ([2], [3], etc.) should also be removed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>Date</th>\n",
       "      <th>Location</th>\n",
       "      <th>Event</th>\n",
       "      <th>Magnitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>May 22, 1960</td>\n",
       "      <td>Valdivia, Chile</td>\n",
       "      <td>1960 Valdivia earthquake</td>\n",
       "      <td>9.4–9.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>March 27, 1964</td>\n",
       "      <td>Prince William Sound, Alaska, United States</td>\n",
       "      <td>1964 Alaska earthquake</td>\n",
       "      <td>9.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>December 26, 2004</td>\n",
       "      <td>Indian Ocean, Sumatra, Indonesia</td>\n",
       "      <td>2004 Indian Ocean earthquake</td>\n",
       "      <td>9.1–9.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>March 11, 2011</td>\n",
       "      <td>Pacific Ocean, Tōhoku region, Japan</td>\n",
       "      <td>2011 Tōhoku earthquake</td>\n",
       "      <td>9.1[3]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>November 4, 1952</td>\n",
       "      <td>Kamchatka, Russian SFSR, Soviet Union</td>\n",
       "      <td>1952 Kamchatka earthquakes</td>\n",
       "      <td>9.0[4]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Rank               Date                                     Location  \\\n",
       "0     1       May 22, 1960                              Valdivia, Chile   \n",
       "1     2     March 27, 1964  Prince William Sound, Alaska, United States   \n",
       "2     3  December 26, 2004             Indian Ocean, Sumatra, Indonesia   \n",
       "3     4     March 11, 2011          Pacific Ocean, Tōhoku region, Japan   \n",
       "4     5   November 4, 1952        Kamchatka, Russian SFSR, Soviet Union   \n",
       "\n",
       "                          Event Magnitude  \n",
       "0      1960 Valdivia earthquake   9.4–9.6  \n",
       "1        1964 Alaska earthquake       9.2  \n",
       "2  2004 Indian Ocean earthquake   9.1–9.3  \n",
       "3        2011 Tōhoku earthquake    9.1[3]  \n",
       "4    1952 Kamchatka earthquakes    9.0[4]  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "#data = pd.read_html(\"https://en.wikipedia.org/wiki/Lists_of_earthquakes\")[2]\n",
    "#data.to_csv(\"sample_midterm1_data.csv\", index=False)\n",
    "data = pd.read_csv(\"sample_midterm1_data.csv\")\n",
    "data.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 191,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Your code here\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Question 2: Data Manipulation\n",
    "Amy wanted a new bed frame from Ikea. Before going to the nearby store, she browsed online to find some Ikea bed frames that she quite liked. When she's at the store, some bed frames were actually out of stock, and she was only able to find part of her selections. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 184,
   "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>name</th>\n",
       "      <th>material</th>\n",
       "      <th>size</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NORDLI</td>\n",
       "      <td>wood</td>\n",
       "      <td>queen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>KOPARDAL</td>\n",
       "      <td>metal</td>\n",
       "      <td>queen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NEIDEN</td>\n",
       "      <td>wood</td>\n",
       "      <td>full</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>TARVA</td>\n",
       "      <td>wood</td>\n",
       "      <td>queen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>SLATTUM</td>\n",
       "      <td>metal</td>\n",
       "      <td>full</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>BJÖRKSNÄS</td>\n",
       "      <td>wood</td>\n",
       "      <td>king</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>ASKVOLL</td>\n",
       "      <td>wood</td>\n",
       "      <td>full</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        name material   size\n",
       "0     NORDLI     wood  queen\n",
       "1   KOPARDAL    metal  queen\n",
       "2     NEIDEN     wood   full\n",
       "3      TARVA     wood  queen\n",
       "4    SLATTUM    metal   full\n",
       "5  BJÖRKSNÄS     wood   king\n",
       "6    ASKVOLL     wood   full"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>name</th>\n",
       "      <th>price</th>\n",
       "      <th>color</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NEIDEN</td>\n",
       "      <td>99</td>\n",
       "      <td>natural</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NORDLI</td>\n",
       "      <td>399</td>\n",
       "      <td>white/black</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BJÖRKSNÄS</td>\n",
       "      <td>549</td>\n",
       "      <td>natural</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>TARVA</td>\n",
       "      <td>149</td>\n",
       "      <td>natural</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>KOPARDAL</td>\n",
       "      <td>279</td>\n",
       "      <td>dark_grey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>SLATTUM</td>\n",
       "      <td>129</td>\n",
       "      <td>grey</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        name  price        color\n",
       "0     NEIDEN     99      natural\n",
       "1     NORDLI    399  white/black\n",
       "2  BJÖRKSNÄS    549      natural\n",
       "3      TARVA    149      natural\n",
       "4   KOPARDAL    279    dark_grey\n",
       "5    SLATTUM    129         grey"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "online_df=pd.DataFrame([[\"NORDLI\",\"wood\",\"queen\"],\n",
    "        [\"KOPARDAL\",\"metal\",\"queen\"],\n",
    "        [\"NEIDEN\",\"wood\",\"full\"],\n",
    "        [\"TARVA\",\"wood\",\"queen\"],\n",
    "        [\"SLATTUM\",\"metal\",\"full\"],\n",
    "        [\"BJÖRKSNÄS\",\"wood\",\"king\"],\n",
    "        [\"ASKVOLL\",\"wood\",\"full\"]], columns=[\"name\",\"material\",\"size\"])\n",
    "\n",
    "store_df=pd.DataFrame([[\"NEIDEN\",99,\"natural\"],\n",
    "          [\"NORDLI\",399,\"white/black\"],\n",
    "          [\"BJÖRKSNÄS\",549,\"natural\"],\n",
    "          [\"TARVA\",149,\"natural\"],\n",
    "          [\"KOPARDAL\",279, \"dark_grey\"],\n",
    "          [\"SLATTUM\",129,\"grey\"]], columns=[\"name\",\"price\",\"color\"])\n",
    "\n",
    "display(online_df)\n",
    "display(store_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Part A\n",
    "Write a function to return the mean and standard deviations (from `numpy`) of the average price of a bed frame by size. Only include bed frames that Amy found in store."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 185,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Your code here\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Part B\n",
    "On average, what is the most expensive and least expensive size/material combination? Build a dataframe where each column is labeled as to a material type and the values of the cells are average price of different bed frame sizes (so row index values should be bedframe size)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 193,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Your code here"
   ]
  }
 ],
 "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}