C4_Pandas_Housing_Student-Tahseen.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Pandas Data Cleaning and Exploratory Data Analysis (EDA)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Upload Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "housing_header = [\"HomeID\", \"HomeAge\", \"HomeSqft\", \"LotSize\", \"BedRooms\", \n",
    "                  \"HighSchoolAPI\", \"ProxFwy\", \"CarGarage\", \"ZipCode\", \"HomePriceK\"]\n",
    "df = pd.read_csv(\"fixed-housing-data.csv\",names=housing_header)\n",
    "#what does this do?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>HomeID</th>\n",
       "      <th>HomeAge</th>\n",
       "      <th>HomeSqft</th>\n",
       "      <th>LotSize</th>\n",
       "      <th>BedRooms</th>\n",
       "      <th>HighSchoolAPI</th>\n",
       "      <th>ProxFwy</th>\n",
       "      <th>CarGarage</th>\n",
       "      <th>ZipCode</th>\n",
       "      <th>HomePriceK</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "      <td>1757</td>\n",
       "      <td>6056</td>\n",
       "      <td>2</td>\n",
       "      <td>899</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>94085</td>\n",
       "      <td>894</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>1563</td>\n",
       "      <td>6085</td>\n",
       "      <td>2</td>\n",
       "      <td>959</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>94085</td>\n",
       "      <td>861</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>14</td>\n",
       "      <td>1344</td>\n",
       "      <td>6089</td>\n",
       "      <td>2</td>\n",
       "      <td>865</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>94085</td>\n",
       "      <td>831</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>14</td>\n",
       "      <td>1215</td>\n",
       "      <td>6129</td>\n",
       "      <td>3</td>\n",
       "      <td>959</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>94085</td>\n",
       "      <td>809</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>24</td>\n",
       "      <td>1866</td>\n",
       "      <td>6141</td>\n",
       "      <td>3</td>\n",
       "      <td>877</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>94085</td>\n",
       "      <td>890</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   HomeID  HomeAge  HomeSqft  LotSize  BedRooms  HighSchoolAPI  ProxFwy  \\\n",
       "0       1       24      1757     6056         2            899        3   \n",
       "1       2       10      1563     6085         2            959        4   \n",
       "2       3       14      1344     6089         2            865        4   \n",
       "3       4       14      1215     6129         3            959        4   \n",
       "4       5       24      1866     6141         3            877        4   \n",
       "\n",
       "   CarGarage  ZipCode  HomePriceK  \n",
       "0          3    94085         894  \n",
       "1          3    94085         861  \n",
       "2          3    94085         831  \n",
       "3          2    94085         809  \n",
       "4          1    94085         890  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Why did we only want to display the first 5 rows of the dataframe?\n",
    "\n",
    "What if we wanted to see the size of this dataframe?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "100"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# number of rows\n",
    "len(df)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(100, 10)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# shape of df (rows, columns)\n",
    "df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Change Column Name(s)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Why would we want to change the column names?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>HomeID</th>\n",
       "      <th>HomeAge</th>\n",
       "      <th>HomeSqft</th>\n",
       "      <th>LotSize</th>\n",
       "      <th>BedRooms</th>\n",
       "      <th>SchoolAPI</th>\n",
       "      <th>ProxFwy</th>\n",
       "      <th>CarGarage</th>\n",
       "      <th>ZipCode</th>\n",
       "      <th>HomePriceK</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "      <td>1757</td>\n",
       "      <td>6056</td>\n",
       "      <td>2</td>\n",
       "      <td>899</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>94085</td>\n",
       "      <td>894</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>1563</td>\n",
       "      <td>6085</td>\n",
       "      <td>2</td>\n",
       "      <td>959</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>94085</td>\n",
       "      <td>861</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>14</td>\n",
       "      <td>1344</td>\n",
       "      <td>6089</td>\n",
       "      <td>2</td>\n",
       "      <td>865</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>94085</td>\n",
       "      <td>831</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>14</td>\n",
       "      <td>1215</td>\n",
       "      <td>6129</td>\n",
       "      <td>3</td>\n",
       "      <td>959</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>94085</td>\n",
       "      <td>809</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>24</td>\n",
       "      <td>1866</td>\n",
       "      <td>6141</td>\n",
       "      <td>3</td>\n",
       "      <td>877</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>94085</td>\n",
       "      <td>890</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   HomeID  HomeAge  HomeSqft  LotSize  BedRooms  SchoolAPI  ProxFwy  \\\n",
       "0       1       24      1757     6056         2        899        3   \n",
       "1       2       10      1563     6085         2        959        4   \n",
       "2       3       14      1344     6089         2        865        4   \n",
       "3       4       14      1215     6129         3        959        4   \n",
       "4       5       24      1866     6141         3        877        4   \n",
       "\n",
       "   CarGarage  ZipCode  HomePriceK  \n",
       "0          3    94085         894  \n",
       "1          3    94085         861  \n",
       "2          3    94085         831  \n",
       "3          2    94085         809  \n",
       "4          1    94085         890  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.rename(columns={'HighSchoolAPI': 'SchoolAPI'})\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create New Columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What is new information about \"Block_Location\" that we can actually use and save?\n",
    "\n",
    "Let's create new columns for the information we extracted from those values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>HomeID</th>\n",
       "      <th>HomeAge</th>\n",
       "      <th>HomeSqft</th>\n",
       "      <th>LotSize</th>\n",
       "      <th>BedRooms</th>\n",
       "      <th>SchoolAPI</th>\n",
       "      <th>ProxFwy</th>\n",
       "      <th>CarGarage</th>\n",
       "      <th>ZipCode</th>\n",
       "      <th>HomePriceK</th>\n",
       "      <th>Price2019</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "      <td>1757</td>\n",
       "      <td>6056</td>\n",
       "      <td>2</td>\n",
       "      <td>899</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>94085</td>\n",
       "      <td>894</td>\n",
       "      <td>929.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>1563</td>\n",
       "      <td>6085</td>\n",
       "      <td>2</td>\n",
       "      <td>959</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>94085</td>\n",
       "      <td>861</td>\n",
       "      <td>895.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>14</td>\n",
       "      <td>1344</td>\n",
       "      <td>6089</td>\n",
       "      <td>2</td>\n",
       "      <td>865</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>94085</td>\n",
       "      <td>831</td>\n",
       "      <td>864.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>14</td>\n",
       "      <td>1215</td>\n",
       "      <td>6129</td>\n",
       "      <td>3</td>\n",
       "      <td>959</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>94085</td>\n",
       "      <td>809</td>\n",
       "      <td>841.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>24</td>\n",
       "      <td>1866</td>\n",
       "      <td>6141</td>\n",
       "      <td>3</td>\n",
       "      <td>877</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>94085</td>\n",
       "      <td>890</td>\n",
       "      <td>925.60</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   HomeID  HomeAge  HomeSqft  LotSize  BedRooms  SchoolAPI  ProxFwy  \\\n",
       "0       1       24      1757     6056         2        899        3   \n",
       "1       2       10      1563     6085         2        959        4   \n",
       "2       3       14      1344     6089         2        865        4   \n",
       "3       4       14      1215     6129         3        959        4   \n",
       "4       5       24      1866     6141         3        877        4   \n",
       "\n",
       "   CarGarage  ZipCode  HomePriceK  Price2019  \n",
       "0          3    94085         894     929.76  \n",
       "1          3    94085         861     895.44  \n",
       "2          3    94085         831     864.24  \n",
       "3          2    94085         809     841.36  \n",
       "4          1    94085         890     925.60  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "prices_2019 = [(price * 1.04) for price in df[\"HomePriceK\"]]\n",
    "df[\"Price2019\"] = prices_2019\n",
    "#Check if it worked\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Drop Columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>HomeID</th>\n",
       "      <th>HomeAge</th>\n",
       "      <th>HomeSqft</th>\n",
       "      <th>LotSize</th>\n",
       "      <th>BedRooms</th>\n",
       "      <th>SchoolAPI</th>\n",
       "      <th>CarGarage</th>\n",
       "      <th>ZipCode</th>\n",
       "      <th>HomePriceK</th>\n",
       "      <th>Price2019</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "      <td>1757</td>\n",
       "      <td>6056</td>\n",
       "      <td>2</td>\n",
       "      <td>899</td>\n",
       "      <td>3</td>\n",
       "      <td>94085</td>\n",
       "      <td>894</td>\n",
       "      <td>929.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>1563</td>\n",
       "      <td>6085</td>\n",
       "      <td>2</td>\n",
       "      <td>959</td>\n",
       "      <td>3</td>\n",
       "      <td>94085</td>\n",
       "      <td>861</td>\n",
       "      <td>895.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>14</td>\n",
       "      <td>1344</td>\n",
       "      <td>6089</td>\n",
       "      <td>2</td>\n",
       "      <td>865</td>\n",
       "      <td>3</td>\n",
       "      <td>94085</td>\n",
       "      <td>831</td>\n",
       "      <td>864.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>14</td>\n",
       "      <td>1215</td>\n",
       "      <td>6129</td>\n",
       "      <td>3</td>\n",
       "      <td>959</td>\n",
       "      <td>2</td>\n",
       "      <td>94085</td>\n",
       "      <td>809</td>\n",
       "      <td>841.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>24</td>\n",
       "      <td>1866</td>\n",
       "      <td>6141</td>\n",
       "      <td>3</td>\n",
       "      <td>877</td>\n",
       "      <td>1</td>\n",
       "      <td>94085</td>\n",
       "      <td>890</td>\n",
       "      <td>925.60</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   HomeID  HomeAge  HomeSqft  LotSize  BedRooms  SchoolAPI  CarGarage  \\\n",
       "0       1       24      1757     6056         2        899          3   \n",
       "1       2       10      1563     6085         2        959          3   \n",
       "2       3       14      1344     6089         2        865          3   \n",
       "3       4       14      1215     6129         3        959          2   \n",
       "4       5       24      1866     6141         3        877          1   \n",
       "\n",
       "   ZipCode  HomePriceK  Price2019  \n",
       "0    94085         894     929.76  \n",
       "1    94085         861     895.44  \n",
       "2    94085         831     864.24  \n",
       "3    94085         809     841.36  \n",
       "4    94085         890     925.60  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.drop(\"ProxFwy\", axis = 1)\n",
    "#Check if it dropped\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([94085, 95051, 94087, 95014])"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.ZipCode.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([3, 2, 1, 0])"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"CarGarage\"].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# EXPLORATORY DATA ANALYSIS\n",
    "\n",
    "<h3>\"Exploratory data analysis or 'EDA' is a <b>critical</b> beginning step in analyzing the data from an experiment.</h3>\n",
    "\n",
    "<b>Here are the main reasons we use EDA:</b>\n",
    "<ul>\n",
    "• detection of mistakes<br><br>\n",
    "• checking of assumptions<br><br>\n",
    "• preliminary selection of appropriate models<br><br>\n",
    "• determining relationships among the explanatory variables, and<br><br>\n",
    "• assessing the direction and rough size of relationships between explanatory and outcome variables.\"</ul>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Now what?\n",
    "\n",
    "We have cleaned our data to the best of our ability based on the initial look. Now let's try to look at the <b>relationships</b> between different values. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>HomeID</th>\n",
       "      <th>HomeAge</th>\n",
       "      <th>HomeSqft</th>\n",
       "      <th>LotSize</th>\n",
       "      <th>BedRooms</th>\n",
       "      <th>SchoolAPI</th>\n",
       "      <th>CarGarage</th>\n",
       "      <th>ZipCode</th>\n",
       "      <th>HomePriceK</th>\n",
       "      <th>Price2019</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "      <td>1757</td>\n",
       "      <td>6056</td>\n",
       "      <td>2</td>\n",
       "      <td>899</td>\n",
       "      <td>3</td>\n",
       "      <td>94085</td>\n",
       "      <td>894</td>\n",
       "      <td>929.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>1563</td>\n",
       "      <td>6085</td>\n",
       "      <td>2</td>\n",
       "      <td>959</td>\n",
       "      <td>3</td>\n",
       "      <td>94085</td>\n",
       "      <td>861</td>\n",
       "      <td>895.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>14</td>\n",
       "      <td>1344</td>\n",
       "      <td>6089</td>\n",
       "      <td>2</td>\n",
       "      <td>865</td>\n",
       "      <td>3</td>\n",
       "      <td>94085</td>\n",
       "      <td>831</td>\n",
       "      <td>864.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>14</td>\n",
       "      <td>1215</td>\n",
       "      <td>6129</td>\n",
       "      <td>3</td>\n",
       "      <td>959</td>\n",
       "      <td>2</td>\n",
       "      <td>94085</td>\n",
       "      <td>809</td>\n",
       "      <td>841.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>24</td>\n",
       "      <td>1866</td>\n",
       "      <td>6141</td>\n",
       "      <td>3</td>\n",
       "      <td>877</td>\n",
       "      <td>1</td>\n",
       "      <td>94085</td>\n",
       "      <td>890</td>\n",
       "      <td>925.60</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   HomeID  HomeAge  HomeSqft  LotSize  BedRooms  SchoolAPI  CarGarage  \\\n",
       "0       1       24      1757     6056         2        899          3   \n",
       "1       2       10      1563     6085         2        959          3   \n",
       "2       3       14      1344     6089         2        865          3   \n",
       "3       4       14      1215     6129         3        959          2   \n",
       "4       5       24      1866     6141         3        877          1   \n",
       "\n",
       "   ZipCode  HomePriceK  Price2019  \n",
       "0    94085         894     929.76  \n",
       "1    94085         861     895.44  \n",
       "2    94085         831     864.24  \n",
       "3    94085         809     841.36  \n",
       "4    94085         890     925.60  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's look at the different types of offenses that were called in. We know that using the .unique() function will return all the unique values in the column, but what if we wanted to also <b>count</b> the different times each unique value appeared?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "95051    25\n",
       "95014    25\n",
       "94087    25\n",
       "94085    25\n",
       "Name: ZipCode, dtype: int64"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.ZipCode.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    31\n",
       "1    18\n",
       "2    19\n",
       "3    32\n",
       "Name: CarGarage, dtype: int64"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.CarGarage.value_counts().sort_index()\n",
    "#print(df1.so())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Why is \"LARCENY\" a higher occurence in the \"EVENTDESC\" column, if when we looked into the \"OFFENSE\" column, \"BURGLARY - VEHICLE\" is first? Let's look into this a little more.\n",
    "\n",
    "\n",
    "## GroupBy "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ZipCode  CarGarage\n",
      "94085    0             4\n",
      "         1             7\n",
      "         2             6\n",
      "         3             8\n",
      "94087    0             9\n",
      "         1             7\n",
      "         2             4\n",
      "         3             5\n",
      "95014    0             9\n",
      "         1             2\n",
      "         2             3\n",
      "         3            11\n",
      "95051    0             9\n",
      "         1             2\n",
      "         2             6\n",
      "         3             8\n",
      "Name: CarGarage, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "df1 = df.groupby(\"ZipCode\").CarGarage.value_counts().sort_index()\n",
    "#Try to remove to_frame() and see\n",
    "#Try to display in sorted order of Car Garage. Use sort_index()\n",
    "print(df1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## More about GROUP BY\n",
    "\"This grouped variable is now a GroupBy object. It has not actually computed anything yet except for some intermediate data about the group key df['key1']. The idea is that this object has all of the information needed to then apply some operation to each of the groups.\" - Python for Data Analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pandas.core.groupby.groupby.DataFrameGroupBy object at 0x10bfebc50>"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Use list() to show what a grouping looks like\n",
    "\n",
    "df.groupby(\"ZipCode\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Descriptive statistics by group"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{94085: Int64Index([ 0,  1,  2,  3,  4,  5,  6,  7,  8,  9, 10, 11, 12, 13, 14, 16, 17,\n",
       "             19, 20, 21, 22, 24, 25, 26, 28],\n",
       "            dtype='int64'),\n",
       " 94087: Int64Index([40, 41, 45, 47, 48, 53, 55, 56, 57, 59, 60, 61, 62, 63, 64, 66, 67,\n",
       "             68, 69, 71, 74, 75, 77, 78, 79],\n",
       "            dtype='int64'),\n",
       " 95014: Int64Index([65, 70, 72, 73, 76, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91,\n",
       "             92, 93, 94, 95, 96, 97, 98, 99],\n",
       "            dtype='int64'),\n",
       " 95051: Int64Index([15, 18, 23, 27, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 42, 43,\n",
       "             44, 46, 49, 50, 51, 52, 54, 58],\n",
       "            dtype='int64')}"
      ]
     },
     "execution_count": 104,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#returns a dict of your groups\n",
    "df.groupby(\"ZipCode\").groups"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "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>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>min</th>\n",
       "      <th>25%</th>\n",
       "      <th>50%</th>\n",
       "      <th>75%</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZipCode</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>94085</th>\n",
       "      <td>25.0</td>\n",
       "      <td>6531.48</td>\n",
       "      <td>366.680279</td>\n",
       "      <td>6056.0</td>\n",
       "      <td>6183.0</td>\n",
       "      <td>6514.0</td>\n",
       "      <td>6870.0</td>\n",
       "      <td>7098.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94087</th>\n",
       "      <td>25.0</td>\n",
       "      <td>8279.68</td>\n",
       "      <td>467.047439</td>\n",
       "      <td>7426.0</td>\n",
       "      <td>7958.0</td>\n",
       "      <td>8348.0</td>\n",
       "      <td>8585.0</td>\n",
       "      <td>8974.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95014</th>\n",
       "      <td>25.0</td>\n",
       "      <td>9145.28</td>\n",
       "      <td>275.174266</td>\n",
       "      <td>8446.0</td>\n",
       "      <td>9095.0</td>\n",
       "      <td>9211.0</td>\n",
       "      <td>9337.0</td>\n",
       "      <td>9476.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95051</th>\n",
       "      <td>25.0</td>\n",
       "      <td>7405.56</td>\n",
       "      <td>359.942134</td>\n",
       "      <td>6680.0</td>\n",
       "      <td>7181.0</td>\n",
       "      <td>7339.0</td>\n",
       "      <td>7693.0</td>\n",
       "      <td>8096.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         count     mean         std     min     25%     50%     75%     max\n",
       "ZipCode                                                                    \n",
       "94085     25.0  6531.48  366.680279  6056.0  6183.0  6514.0  6870.0  7098.0\n",
       "94087     25.0  8279.68  467.047439  7426.0  7958.0  8348.0  8585.0  8974.0\n",
       "95014     25.0  9145.28  275.174266  8446.0  9095.0  9211.0  9337.0  9476.0\n",
       "95051     25.0  7405.56  359.942134  6680.0  7181.0  7339.0  7693.0  8096.0"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(\"ZipCode\").LotSize.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "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>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>min</th>\n",
       "      <th>25%</th>\n",
       "      <th>50%</th>\n",
       "      <th>75%</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZipCode</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>94085</th>\n",
       "      <td>25.0</td>\n",
       "      <td>907.00</td>\n",
       "      <td>38.223030</td>\n",
       "      <td>851.0</td>\n",
       "      <td>877.0</td>\n",
       "      <td>904.0</td>\n",
       "      <td>935.0</td>\n",
       "      <td>966.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94087</th>\n",
       "      <td>25.0</td>\n",
       "      <td>899.24</td>\n",
       "      <td>33.121343</td>\n",
       "      <td>850.0</td>\n",
       "      <td>876.0</td>\n",
       "      <td>890.0</td>\n",
       "      <td>927.0</td>\n",
       "      <td>962.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95014</th>\n",
       "      <td>25.0</td>\n",
       "      <td>894.80</td>\n",
       "      <td>32.430695</td>\n",
       "      <td>850.0</td>\n",
       "      <td>862.0</td>\n",
       "      <td>889.0</td>\n",
       "      <td>924.0</td>\n",
       "      <td>942.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95051</th>\n",
       "      <td>25.0</td>\n",
       "      <td>916.68</td>\n",
       "      <td>39.359158</td>\n",
       "      <td>853.0</td>\n",
       "      <td>891.0</td>\n",
       "      <td>918.0</td>\n",
       "      <td>949.0</td>\n",
       "      <td>975.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         count    mean        std    min    25%    50%    75%    max\n",
       "ZipCode                                                             \n",
       "94085     25.0  907.00  38.223030  851.0  877.0  904.0  935.0  966.0\n",
       "94087     25.0  899.24  33.121343  850.0  876.0  890.0  927.0  962.0\n",
       "95014     25.0  894.80  32.430695  850.0  862.0  889.0  924.0  942.0\n",
       "95051     25.0  916.68  39.359158  853.0  891.0  918.0  949.0  975.0"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(\"ZipCode\").SchoolAPI.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get Columns + Index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['HomeID', 'HomeAge', 'HomeSqft', 'LotSize', 'BedRooms', 'SchoolAPI',\n",
       "       'CarGarage', 'ZipCode', 'HomePriceK', 'Price2019'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['HomeID',\n",
       " 'HomeAge',\n",
       " 'HomeSqft',\n",
       " 'LotSize',\n",
       " 'BedRooms',\n",
       " 'SchoolAPI',\n",
       " 'CarGarage',\n",
       " 'ZipCode',\n",
       " 'HomePriceK',\n",
       " 'Price2019']"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(df.columns)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <font color = \"red\">Pandas HW 1</font>\n",
    "\n",
    "Could there be any relationship between \"Price per lot size Sqft\" and \"Price per home Sqft\"? What can be the takeaway message from the data we have? Try out different functions to see if there is any significance?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Price/Lot size sqft by Zipcodes:\n",
      "         count      mean       std     min     25%     50%     75%     max\n",
      "ZipCode                                                                   \n",
      "94085     25.0  135.8696  5.861388  125.97  131.59  132.95  141.02  147.62\n",
      "94087     25.0  139.3828  6.342052  131.48  135.12  138.13  142.01  154.70\n",
      "95014     25.0  138.1952  4.021741  130.34  134.78  139.18  141.96  143.09\n",
      "95051     25.0  138.2388  4.039974  131.42  136.04  138.60  141.44  146.08\n",
      "\n",
      "\n",
      "Price/Home size sqft by Zipcodes:\n",
      "         count      mean         std     min     25%     50%     75%     max\n",
      "ZipCode                                                                     \n",
      "94085     25.0  558.0148   87.981158  432.97  497.82  550.86  618.30  746.93\n",
      "94087     25.0  746.1456   98.434066  579.14  677.05  755.58  821.66  930.40\n",
      "95014     25.0  800.9448  114.728785  629.89  723.96  784.62  882.92  978.66\n",
      "95051     25.0  630.4940  104.803343  480.04  542.49  602.09  714.66  833.20\n"
     ]
    }
   ],
   "source": [
    "# Your code here ...\n",
    "#Calc price/lot sqft and price/home sqft - add another column and then for each zip code do a desc\n",
    "\n",
    "df[\"PricePerLot\"]=round(df[\"HomePriceK\"]*1000/df[\"LotSize\"],2)\n",
    "print(\"Price/Lot size sqft by Zipcodes:\")\n",
    "print(df.groupby(\"ZipCode\").PricePerLot.describe())\n",
    "print(\"\\n\")\n",
    "\n",
    "df[\"PricePerHome\"]=round(df[\"HomePriceK\"]*1000/df[\"HomeSqft\"],2)\n",
    "print(\"Price/Home size sqft by Zipcodes:\")\n",
    "print(df.groupby(\"ZipCode\").PricePerHome.describe())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <font color = \"red\">Pandas HW 2</font>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What other data column for the zip codes could make the analysis more precise?\n",
    "\n",
    "Median houshold income, population, population density??? Inlude one or more new data columns and re-visit your conclusions from HW 1.\n",
    "\n",
    "Are these home prices driven by factors for which we have the data?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "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>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>min</th>\n",
       "      <th>25%</th>\n",
       "      <th>50%</th>\n",
       "      <th>75%</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MedianIncome</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>101051.0</th>\n",
       "      <td>25.0</td>\n",
       "      <td>885.96</td>\n",
       "      <td>34.408671</td>\n",
       "      <td>809.0</td>\n",
       "      <td>865.0</td>\n",
       "      <td>894.0</td>\n",
       "      <td>912.0</td>\n",
       "      <td>934.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106527.0</th>\n",
       "      <td>25.0</td>\n",
       "      <td>1023.20</td>\n",
       "      <td>46.984927</td>\n",
       "      <td>942.0</td>\n",
       "      <td>991.0</td>\n",
       "      <td>1030.0</td>\n",
       "      <td>1068.0</td>\n",
       "      <td>1097.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129668.0</th>\n",
       "      <td>25.0</td>\n",
       "      <td>1151.48</td>\n",
       "      <td>28.133788</td>\n",
       "      <td>1103.0</td>\n",
       "      <td>1128.0</td>\n",
       "      <td>1150.0</td>\n",
       "      <td>1179.0</td>\n",
       "      <td>1190.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141917.0</th>\n",
       "      <td>25.0</td>\n",
       "      <td>1263.32</td>\n",
       "      <td>38.518091</td>\n",
       "      <td>1194.0</td>\n",
       "      <td>1240.0</td>\n",
       "      <td>1269.0</td>\n",
       "      <td>1288.0</td>\n",
       "      <td>1336.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              count     mean        std     min     25%     50%     75%  \\\n",
       "MedianIncome                                                              \n",
       "101051.0       25.0   885.96  34.408671   809.0   865.0   894.0   912.0   \n",
       "106527.0       25.0  1023.20  46.984927   942.0   991.0  1030.0  1068.0   \n",
       "129668.0       25.0  1151.48  28.133788  1103.0  1128.0  1150.0  1179.0   \n",
       "141917.0       25.0  1263.32  38.518091  1194.0  1240.0  1269.0  1288.0   \n",
       "\n",
       "                 max  \n",
       "MedianIncome          \n",
       "101051.0       934.0  \n",
       "106527.0      1097.0  \n",
       "129668.0      1190.0  \n",
       "141917.0      1336.0  "
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# your code here ...\n",
    "#From internet find Median household income,population..add a column and do the analysis\n",
    "\n",
    "\n",
    "df.loc[df.ZipCode==94087,\"MedianIncome\"]=129668\n",
    "df.loc[df.ZipCode==95014,\"MedianIncome\"]=141917\n",
    "df.loc[df.ZipCode==95051,\"MedianIncome\"]=106527\n",
    "df.loc[df.ZipCode==94085,\"MedianIncome\"]=101051\n",
    "\n",
    "\n",
    "df.groupby(\"MedianIncome\").HomePriceK.describe()"
   ]
  },
  {
   "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.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}