Data processing.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "source": [
    "The order of viewing this project could be:\n",
    "\n",
    "•\tData processing. ipynb\n",
    "\n",
    "•\tDecision Point1.ipynb\n",
    "\n",
    "•\tDecision Point2.ipynb\n",
    "\n",
    "•\tDecision Point3.ipynb\n",
    "\n",
    "The notebooks contain sufficient explanations intent and insights in the analysis.\n",
    "Please reach out directly at shiva.sync@gmail.com for any issues.\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "## Assumptions and Considerations:\n",
    "\n",
    "1)\tI have restricted this Data Analysis to 2-bedroom housing properties in New York.\n",
    "\n",
    "2)\tThe houses with room type “Private Room” in Airbnb data in a 2-bedroom setting apartment has a price for only 1 night’s stay in room. This has been multiplied by 2 for such properties as if rental management can potentially earn double rent of this property as it has 2 bedrooms. However, the overall rental price per night may not be exactly double as the other room may be smaller or have lesser or more appeal than the room in the listing.\n",
    "\n",
    "3)\tOccupancy for the Airbnb listings have been assumed to be 75% throughout the year\n",
    "\n",
    "4)\tThe value of each property is assumed to increase/decrease every year by a fixed  rate.  The calculation of this rate has been discussed section 2.\n",
    "\n",
    "\n",
    "## Metrics Created:\n",
    "\n",
    "### Annual_Rent: \n",
    "\n",
    "This is annual rent collected from a property in Airbnb assuming 75% occupancy using rent price per night from Zillow data. This value is assumed to be fixed.\n",
    "\n",
    "### Payback_Period: \n",
    "\n",
    "This is the number of years required to earn back the initial cost of investment from annual rental income from property assuming 75% occupancy throughout the year.\n",
    "\n",
    "\n",
    "### Occupancy:\n",
    "\n",
    "0.75*365 (Assumed occupancy of all properties in Airbnb)\n",
    "\n",
    "### Rate: \n",
    "So, we all are aware that properties value appreciate/depreciate depending upon the neighborhood and locality. In the Zillow data we have the average housing prices for a zip code for as far back as 1996 but these columns have lot of missing values. However, we do have complete data from 06-2007. The difference between in prices in 06-2007 and 06-2017 divided by 10 has been taken as an average rate by which a property rises in that zip code. Although a naïve approach, it still captures the essence of appreciation/depreciation of property by zip codes over the years and would be useful in adding a location specific factor in Return on Investment calculations.\n",
    "\n",
    "\\begin{equation*}\n",
    "\\frac{Property Value in 2017 -  Property Value in 2007}{Property Value in 2007 * Difference in Years}\n",
    "\\end{equation*}\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "## Roi_Equity: \n",
    "\n",
    "This metric is return on investment in 10 years assuming rent appreciates at a constant rate without including the equity value of the property.\n",
    "\n",
    "\n",
    "\\begin{equation*}\n",
    "\\frac{Price of Property*(1+\\frac{Rate}{100})^{10}- Current Price of the property}{Current Price of the property}\n",
    "\\end{equation*}\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "## Roi: \n",
    "\n",
    "This metric takes into account the return on investment through \n",
    " equity appreciation i.e. the increase in the price of the house is also taken into account to calculate roi. This has been calculated for 10 years as well.\n",
    "\n",
    "It is calculated as:\n",
    "\n",
    "\n",
    "A=Annual Rent in 10 years\n",
    "\n",
    "\\begin{equation*}\n",
    "Annual Rent*10\n",
    "\\end{equation*}\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "B= Increase in cost of property in 10 Years\n",
    "\n",
    "\n",
    "\n",
    "\\begin{equation*}\n",
    "Price of Property*(1+\\frac{Rate}{100})^{10}- Current Price of the property\n",
    "\\end{equation*}\n",
    "\n",
    "\n",
    "\n",
    "Roi= (A+B)/Price of Property\n",
    "\n",
    "\\begin{equation*}\n",
    "\\frac{(A+B)}{Price Of Property}\n",
    "\\end{equation*}\n",
    "\n",
    "## Usefulness of these metrics:\n",
    "\n",
    "### Payback_Period : \n",
    "\n",
    "This tells us how fast we can recover investments from our property.\n",
    "This may be one of the conservative estimates to evaluate expected profits from a property.\n",
    "\n",
    "\n",
    "\n",
    "### Roi_equity : \n",
    "\n",
    "This metric is a good metric to assess profitability from a property considering only appreciation in price of property.\n",
    "Because of variable rates of appreciation for each zipcode this metric gives a good estimate how location of zipcode determines ROI of a property \n",
    "\n",
    "\n",
    "### Roi : \n",
    "\n",
    "This metric would be useful to assess overall value of investment keeping in mind the increase in value of asset and also opens the value analysis  in case rental Property management decides to sell the current asset after 10 years. \n",
    "It is expected that Roi_without would be very similar in results in ROI_10  as it has high equity appreciation has same rate of increase as rate of rentals but ROI_10 . But some interesting insights can be drawn from differences in behavior of these two metrics for zipcodes.\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "source": [
    "**This is the file in which we create the required the dataframes as pickles for a feed to our visualzation as well as to do quality checks on the data.**\n",
    "\n",
    "\n",
    "# Key Points:\n",
    "\n",
    "1) select columns of interests in our analysis\n",
    "\n",
    "2) Handling of missing values in the Data and doing necessary imputations.\n",
    "\n",
    "3) Subsetting the data further as per needs for visualization\n",
    "\n",
    "# Methodology:\n",
    "\n",
    "## Airbnb Data:\n",
    "The biggest concern for our analysis was imputations for missing zipcodes.Once we have data for zipcodes the city and state can easily be inferred from zipcode value.\n",
    "We used pygeocoder a library built on google maps api to impute missing zipcodes by using latitudes and longitudes.\n",
    "Luckily no missing values on lat and long.\n",
    "After imputation only 2 properties still had missing values as their coordinates were not too specific for api to give a zipcode.\n",
    "They were ignored and removed.\n",
    "\n",
    "\n",
    "\n",
    "Once we imputed zipcode a quality check was done on zipcodes to ensure proper string length and then a range of zipcodes between 10000 and 20000 were selected as properties in NY.\n",
    "\n",
    "Next we noticed 22 missing values for bedrooms which were removed from the dataset as their was no way to estimate the number of bedrooms.\n",
    "\n",
    "finally we subset the 2 bedrooms properties in NY CITY.\n",
    "\n",
    "\n",
    "This data set was then aggregated to be used for decision point 1 .\n",
    "\n",
    "**\n",
    "Note: the function impute zip will not work as api credential has been removed.The code will still work as the results from the imputation have been pickled.**\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [],
   "source": [
    "##### Reading datasets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "\n",
    "zillow_data=pd.read_csv(\"Zip_Zhvi_2bedroom.csv\")\n",
    "\n",
    "#airbnb_data=pd.read_csv(\"listings.csv\")\n",
    "# airbnb_data.shape\n",
    "# airbnb=airbnb_data[[\"id\",\"city\",\"state\",\"zipcode\",\"room_type\",\"bedrooms\",\"latitude\",\"longitude\",\"price\",\"availability_365\"]]\n",
    "#airbnb.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [],
   "source": [
    "# from pygeocoder import Geocoder\n",
    "# import pandas as pd\n",
    "# import numpy as np\n",
    "# from preprocessing import impute_zip\n",
    "# # data=impute_zip(airbnb)\n",
    "# data.to_pickle(\"final_frame.pkl\")\n",
    "airbnb=pd.read_pickle(\"pickles/final_frame.pkl\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id                   0\n",
       "city                62\n",
       "state                6\n",
       "zipcode              2\n",
       "room_type            0\n",
       "bedrooms            22\n",
       "latitude             0\n",
       "longitude            0\n",
       "price                0\n",
       "availability_365     0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "airbnb.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "airbnb[\"zipcode\"].isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "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>id</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>zipcode</th>\n",
       "      <th>room_type</th>\n",
       "      <th>bedrooms</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>price</th>\n",
       "      <th>availability_365</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4062</th>\n",
       "      <td>2582812</td>\n",
       "      <td>Brooklyn</td>\n",
       "      <td>NY</td>\n",
       "      <td>11249\\n11249</td>\n",
       "      <td>Private room</td>\n",
       "      <td>1.0</td>\n",
       "      <td>40.71997</td>\n",
       "      <td>-73.95945</td>\n",
       "      <td>$60.00</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9112</th>\n",
       "      <td>6973292</td>\n",
       "      <td>New York</td>\n",
       "      <td>NY</td>\n",
       "      <td>11103-3233</td>\n",
       "      <td>Private room</td>\n",
       "      <td>1.0</td>\n",
       "      <td>40.76583</td>\n",
       "      <td>-73.91086</td>\n",
       "      <td>$59.00</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10057</th>\n",
       "      <td>7724136</td>\n",
       "      <td>New York</td>\n",
       "      <td>NY</td>\n",
       "      <td>10002-2289</td>\n",
       "      <td>Private room</td>\n",
       "      <td>1.0</td>\n",
       "      <td>40.72092</td>\n",
       "      <td>-73.98849</td>\n",
       "      <td>$85.00</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18482</th>\n",
       "      <td>14551508</td>\n",
       "      <td>Ridgewood</td>\n",
       "      <td>NY</td>\n",
       "      <td>11385-2308</td>\n",
       "      <td>Private room</td>\n",
       "      <td>1.0</td>\n",
       "      <td>40.70645</td>\n",
       "      <td>-73.90578</td>\n",
       "      <td>$45.00</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26300</th>\n",
       "      <td>20968166</td>\n",
       "      <td>Springfield Gardens</td>\n",
       "      <td>NY</td>\n",
       "      <td>11413-3220</td>\n",
       "      <td>Entire home/apt</td>\n",
       "      <td>1.0</td>\n",
       "      <td>40.66648</td>\n",
       "      <td>-73.76286</td>\n",
       "      <td>$90.00</td>\n",
       "      <td>281</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31255</th>\n",
       "      <td>24278511</td>\n",
       "      <td>Arverne</td>\n",
       "      <td>NY</td>\n",
       "      <td>11692-2304</td>\n",
       "      <td>Private room</td>\n",
       "      <td>1.0</td>\n",
       "      <td>40.58906</td>\n",
       "      <td>-73.79503</td>\n",
       "      <td>$95.00</td>\n",
       "      <td>364</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             id                 city state       zipcode        room_type  \\\n",
       "4062    2582812             Brooklyn    NY  11249\\n11249     Private room   \n",
       "9112    6973292             New York    NY    11103-3233     Private room   \n",
       "10057   7724136             New York    NY    10002-2289     Private room   \n",
       "18482  14551508           Ridgewood     NY    11385-2308     Private room   \n",
       "26300  20968166  Springfield Gardens    NY    11413-3220  Entire home/apt   \n",
       "31255  24278511              Arverne    NY    11692-2304     Private room   \n",
       "\n",
       "       bedrooms  latitude  longitude   price  availability_365  \n",
       "4062        1.0  40.71997  -73.95945  $60.00                 0  \n",
       "9112        1.0  40.76583  -73.91086  $59.00                 0  \n",
       "10057       1.0  40.72092  -73.98849  $85.00                 0  \n",
       "18482       1.0  40.70645  -73.90578  $45.00                 0  \n",
       "26300       1.0  40.66648  -73.76286  $90.00               281  \n",
       "31255       1.0  40.58906  -73.79503  $95.00               364  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "airbnb[(airbnb.zipcode.str.len() > 5)|(airbnb.zipcode.str.len() < 5)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(48895, 10)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "airbnb.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "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>id</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>zipcode</th>\n",
       "      <th>room_type</th>\n",
       "      <th>bedrooms</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>price</th>\n",
       "      <th>availability_365</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2736</th>\n",
       "      <td>1476238</td>\n",
       "      <td>New York</td>\n",
       "      <td>NY</td>\n",
       "      <td>None</td>\n",
       "      <td>Entire home/apt</td>\n",
       "      <td>1.0</td>\n",
       "      <td>40.76084</td>\n",
       "      <td>-73.96908</td>\n",
       "      <td>$300.00</td>\n",
       "      <td>98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4743</th>\n",
       "      <td>3351584</td>\n",
       "      <td>New York</td>\n",
       "      <td>NY</td>\n",
       "      <td>None</td>\n",
       "      <td>Private room</td>\n",
       "      <td>1.0</td>\n",
       "      <td>40.83473</td>\n",
       "      <td>-73.93706</td>\n",
       "      <td>$80.00</td>\n",
       "      <td>351</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           id      city state zipcode        room_type  bedrooms  latitude  \\\n",
       "2736  1476238  New York    NY    None  Entire home/apt       1.0  40.76084   \n",
       "4743  3351584  New York    NY    None     Private room       1.0  40.83473   \n",
       "\n",
       "      longitude    price  availability_365  \n",
       "2736  -73.96908  $300.00                98  \n",
       "4743  -73.93706   $80.00               351  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "airbnb[airbnb[\"zipcode\"].isnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from preprocessing import zipcode_check\n",
    "airbnb=zipcode_check(airbnb)\n",
    "airbnb.zipcode.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [],
   "source": [
    "airbnb[\"zipcode\"]=airbnb[\"zipcode\"].astype(int)\n",
    "airbnb_ny=airbnb[(airbnb[\"zipcode\"]>=10000)&(airbnb[\"zipcode\"]<=20000)].reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(48887, 10)"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "airbnb.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id                   0\n",
       "city                62\n",
       "state                6\n",
       "zipcode              0\n",
       "room_type            0\n",
       "bedrooms            22\n",
       "latitude             0\n",
       "longitude            0\n",
       "price                0\n",
       "availability_365     0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "airbnb.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "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>id</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>zipcode</th>\n",
       "      <th>room_type</th>\n",
       "      <th>bedrooms</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>price</th>\n",
       "      <th>availability_365</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5022</td>\n",
       "      <td>New York</td>\n",
       "      <td>NY</td>\n",
       "      <td>10029</td>\n",
       "      <td>Entire home/apt</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.79851</td>\n",
       "      <td>-73.94399</td>\n",
       "      <td>$80.00</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>6848</td>\n",
       "      <td>Brooklyn</td>\n",
       "      <td>NY</td>\n",
       "      <td>11211</td>\n",
       "      <td>Entire home/apt</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.70837</td>\n",
       "      <td>-73.95352</td>\n",
       "      <td>$140.00</td>\n",
       "      <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>7801</td>\n",
       "      <td>Brooklyn</td>\n",
       "      <td>NY</td>\n",
       "      <td>11211</td>\n",
       "      <td>Entire home/apt</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.71842</td>\n",
       "      <td>-73.95718</td>\n",
       "      <td>$299.00</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>9357</td>\n",
       "      <td>New York</td>\n",
       "      <td>NY</td>\n",
       "      <td>10019</td>\n",
       "      <td>Entire home/apt</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.76715</td>\n",
       "      <td>-73.98533</td>\n",
       "      <td>$150.00</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>27883</td>\n",
       "      <td>New York</td>\n",
       "      <td>NY</td>\n",
       "      <td>10009</td>\n",
       "      <td>Entire home/apt</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.72245</td>\n",
       "      <td>-73.98527</td>\n",
       "      <td>$100.00</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        id      city state  zipcode        room_type  bedrooms  latitude  \\\n",
       "4     5022  New York    NY    10029  Entire home/apt       NaN  40.79851   \n",
       "15    6848  Brooklyn    NY    11211  Entire home/apt       NaN  40.70837   \n",
       "20    7801  Brooklyn    NY    11211  Entire home/apt       NaN  40.71842   \n",
       "27    9357  New York    NY    10019  Entire home/apt       NaN  40.76715   \n",
       "123  27883  New York    NY    10009  Entire home/apt       NaN  40.72245   \n",
       "\n",
       "     longitude    price  availability_365  \n",
       "4    -73.94399   $80.00                 0  \n",
       "15   -73.95352  $140.00                46  \n",
       "20   -73.95718  $299.00                 0  \n",
       "27   -73.98533  $150.00                75  \n",
       "123  -73.98527  $100.00                 0  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "airbnb[airbnb[\"bedrooms\"].isnull()].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id                  0\n",
       "city                9\n",
       "state               1\n",
       "zipcode             0\n",
       "room_type           0\n",
       "bedrooms            0\n",
       "latitude            0\n",
       "longitude           0\n",
       "price               0\n",
       "availability_365    0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "airbnb_ny=airbnb_ny.loc[airbnb_ny[\"bedrooms\"]==2].reset_index(drop=True)\n",
    "airbnb_ny.to_pickle(\"pickles/airbnb_ny.pkl\")\n",
    "airbnb_ny.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "source": [
    "## Zillow Data :\n",
    "\n",
    "This dataset was filtered for properties in NY as no missing values were present on city column.\n",
    "Then we selected the columns to be used for analysis.\n",
    "\n",
    "We  took the column 2016-06 as current selling price of the property.\n",
    "In this filtered data set we did not find any missing values.\n",
    "\n",
    "However missing values were present in certain price years but we did not use them in our analysis.\n",
    "While calculating rate, the fathest two columns were considered which had complete data in their columns.\n",
    "\n",
    "\n",
    "**\n",
    "Note: the function impute zip will not work as api credential has been removed.The code will still work as the results from the imputation have been pickled.**\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "zillow_data.City.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [],
   "source": [
    "## subsetting for city\n",
    "from preprocessing import citysubset\n",
    "zillow_data_ny=citysubset(zillow_data,\"New York\")\n",
    "#zillow_data_ny.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## calculating rate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "from metrics import rate\n",
    "zillow_data_ny[\"rate\"]=rate(zillow_data_ny,10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_zill=zillow_data_ny.iloc[:,0:7]\n",
    "data_zill[\"rate\"]=zillow_data_ny[\"rate\"]\n",
    "data_zill[\"2017-06\"]=zillow_data_ny[\"2017-06\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "RegionID      0\n",
       "RegionName    0\n",
       "City          0\n",
       "State         0\n",
       "Metro         0\n",
       "CountyName    0\n",
       "SizeRank      0\n",
       "rate          0\n",
       "2017-06       0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_zill.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_zill[\"RegionName\"]=data_zill[\"RegionName\"].astype(\"str\")\n",
    "#data_zill=data_zill[data_zill[\"City\"]==\"New York\"].reset_index(drop=True)\n",
    "data_zill.rename(columns={\"2017-06\":\"sell_price\"},inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Finding County for each zipcode in airbnb_ny. Since airbnb has more rows than zillow data on individual property it was decided to use seperate counties files to merge with airbnb data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "county=pd.read_csv('https://query.data.world/s/buotxun7n3q6twzd6havwil7h5nqpb')\n",
    "data1=airbnb_ny.merge(county,how=\"left\",left_on=\"zipcode\",right_on=\"ZIP\")\n",
    "data1[\"price\"]=pd.to_numeric(data1[\"price\"].str.replace(\"[^\\d\\.]\",\"\"))\n",
    "data1.loc[data1[\"room_type\"]==\"Private room\",\"price\"]=2*data1.loc[data1[\"room_type\"]==\"Private room\",\"price\"]\n",
    "\n",
    "data1_county=data1.groupby([\"COUNTYNAME\",\"room_type\"],as_index=False).mean()\n",
    "data1_county.to_pickle(\"pickles/airbnb_county.pkl\")\n",
    "data2=data1.groupby([\"COUNTYNAME\",\"zipcode\",\"room_type\"],as_index=False).mean()\n",
    "data2.to_pickle(\"pickles/airbnb_zip_county.pkl\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Merging airbnb and zillow data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "data1[\"zipcode\"]=data1[\"zipcode\"].astype(str)\n",
    "air_zill=data1.reset_index(drop=True).merge(data_zill,how=\"inner\",left_on=\"zipcode\",right_on=\"RegionName\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Calculating occupancy and payback rate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "air_zill[\"Occupancy\"]=0.75*365\n",
    "air_zill[\"annual_rent\"]=air_zill[\"price\"]*air_zill[\"Occupancy\"]\n",
    "air_zill[\"payback_period\"]=air_zill[\"sell_price\"]/air_zill[\"annual_rent\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Calculating Roi's"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "from metrics import roi_equity,roi\n",
    "air_zill[\"roi\"]=roi(air_zill,10)\n",
    "air_zill[\"roi_equity\"]=roi_equity(air_zill,10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "data3=air_zill.groupby([\"zipcode\",\"CountyName\"],as_index=False).mean()\n",
    "data3.to_pickle(\"pickles/air_zillgroup.pkl\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "env",
   "language": "python",
   "name": "env"
  },
  "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.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}