EDAsale-types_detailed_analysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# EDA project - Andrea Schrader\n",
    "#### **Exploratory Data Analysis**\n",
    "\n",
    "**Dataset:**\"King County House Data\" on home sales in King County (USA) - CC0: Public domain  \n",
    "\n",
    "\n",
    "**Stakeholder choice**  \n",
    "    * Person chosen: Erin Robinson  \n",
    "    * Stakeholder type: Buyer  \n",
    "    * Stakeholder Description: Invest in poor neighborhood, buying & selling, costs back + little profit, socially responsible.  \n",
    "\n",
    "\n",
    "**Overarching topic focussed on in this analysis:**  \n",
    "Insights from multi-sale houses in King County as compared to single-sale houses for both: financial and social gain.  \n",
    "**Assumption**  \n",
    "    * The dataset is representative for other years as well. In the real world I would not base suggestions on a dataset which comprises one year only although the number of data points is high.  \n",
    "    * Poor neighborhoods are more likely locates around buildings with low prices.  \n",
    "    * People with financial problems are more likely to sell houses within a short time frame or speculants are acting within short time frames and are competitors for such houses.  \n",
    "    * Renovating houses in such areas or these already identified or even houses predicted to match that pattern leaves a positive social footprint especially when sticking roughly to the current prices of the market.  \n",
    "<br>\n",
    "**Questions**  \n",
    "> **1) Where are most houses with low prices located?**  \n",
    "> * Hypothesis: The further apart from the city center or in a specific area of the city, cheaper houses will cluster as well as in regions not so eat the far outskirt of the city.  \n",
    "> * Answer: In the outer areas of the city, especially in the south and not at the water.  \n",
    "\n",
    "> **2) How much money can be spent for renovations in comparably poor areas to ensure that costs are refunded and a little profit is achieved based according to the data?**\n",
    ">* Hypothesis: Houses that were sold again within one year are among the cheaper, smaller and older houses.  \n",
    ">* Answer: Most parts of the hypothesis are true. While the area for living was indeed smaller and multi-sale houses had less floors, the area of the lot was surprisingly larger. this implies that there might also be more room for extending the living area if allowed.  \n",
    "\n",
    "> **3) Are there additional risks due to the condition of these houses and can these be overcome by renovations?**  \n",
    "> * Hypothesis: These houses are in a comparably lower quality category and less renovations were conducted.  \n",
    "> * Answer: The quality of these houses is comparable to the median of the single-sale houses.  \n",
    "> With respect to the number of renovations conducted, the hypotheses is also not supported by the data. However, the dataset is small for renovations and errors might have occurred for the \"0\" values. One might consider to asl those collecting the data or, if not accessible,to suggest to conduct a new survey focussing on this aspect in case a stakeholder requests more secure recommendations based on this feature.\n",
    "\n",
    "\n",
    "**Should the class of multi-sale houses be kept in analysis pipelines in the future?**\n",
    "\n",
    "**YES**, this class of houses can be recommended to the stakeholder and can be used for future predictions for similar strategies.\n",
    "\n",
    "---\n",
    "\n",
    "## Documentation of the analysis conducted \n",
    "*May, 10th-12th 2022, presentation on May, 12th*\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# First I import the recommended and additional packages and change some options for plotting according to the suggestions\n",
    "\n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import statistics\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import plotly.express as px\n",
    "\n",
    "from matplotlib.ticker import PercentFormatter\n",
    "plt.rcParams.update({ \"figure.figsize\" : (8, 5),\"axes.facecolor\" : \"white\", \"axes.edgecolor\":  \"black\"})     # setting the figure size and some colors for matplotlib plots including axes\n",
    "plt.rcParams[\"figure.facecolor\"]= \"w\"\n",
    "pd.plotting.register_matplotlib_converters()\n",
    "pd.set_option('display.float_format', lambda x: '%.3f' % x)                                                  # change the decimal numbers to 3 for the displayed float format of panda DataFrames"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Reading in the data and first geographic exploration"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Reading in the data (kd = \"King county house Data\")\n",
    "kd = pd.read_csv('./data/King_County_House_prices_dataset.csv')\n",
    "# Get an idea about the geographic data (columns \"lat\" and \"long\" for the coordinates and \"zip\" for the zip code) \n",
    "# and price data (column \"price\" [$]) for the first plot below. Are there NaN entries? Are the min and max values unusual?\n",
    "kd.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Units of the given coordinates:\n",
    "\n",
    "\n",
    "<img style = \"float: left;\" src=\"https://upload.wikimedia.org/wikipedia/commons/thumb/8/83/Latitude_and_longitude_graticule_on_a_sphere.svg/600px-Latitude_and_longitude_graticule_on_a_sphere.svg.png\" alt=\"globe_coordinates\" width=\"200\"/>\n",
    " <br>\n",
    " As no units are given neither in the column names nor in the description, I derive the format from knowing in which region the houses are located and from classical descriptions of such coordinates in the geographic coordinate system (GCS):<br>\n",
    "\n",
    " * This is an example for a (lat,long) tupel: (φ,λ) = (47.5112, -122.257) presumably in decimal degrees (DD) [°]\n",
    " <br>\n",
    " <br>\n",
    " <br>\n",
    " <br>\n",
    " <br>\n",
    "Left: \"Diagram of the latitude (φ) and longitude (λ) angle measurements in the GCS.\" from https://en.wikipedia.org/wiki/Geographic_coordinate_system.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Conducting an initial geo plot to visualize the area of interest\n",
    "# The color indicates the year of renovation and we see immediately that there is either no data available for this feature or there was no renovation.\n",
    "# The first map gives us an idea about the topology of the region.\n",
    "# scatter_mapbox\n",
    "fig = px.scatter_mapbox(kd, lat=\"lat\", lon=\"long\",\n",
    "                        color='yr_renovated', \n",
    "                        #opacity=0.9,\n",
    "                        color_continuous_scale=px.colors.sequential.Viridis, \n",
    "                        #hover_name=\"yr_renovated\",\n",
    "                        #size='condition',\n",
    "                        size_max=7,\n",
    "                        zoom=8,\n",
    "                        height=800)\n",
    "fig.update_layout(mapbox_style=\"carto-darkmatter\")\n",
    "#fig.update_layout(margin={\"r\": 0, \"t\": 0, \"l\": 0, \"b\": 0})\n",
    "fig.show()\n",
    "# \"open-street-map\", \"carto-positron\", \"carto-darkmatter\", \"stamen-terrain\", \"stamen-toner\" or \"stamen-watercolor\"\n",
    "\n",
    "# A first code for this was shared by Ryan North in our daily review."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# A similar plot with open-street map, now the color indicates price as the target variable\n",
    "# without log2\n",
    "# scatter_mapbox\n",
    "fig = px.scatter_mapbox(kd, lat=\"lat\", lon=\"long\",\n",
    "                        color='price', \n",
    "                        #opacity=0.9,\n",
    "                        color_continuous_scale=px.colors.sequential.Greys_r, \n",
    "                        #hover_name=\"yr_renovated\",\n",
    "                        #size='condition',\n",
    "                        size_max=7,\n",
    "                        zoom=8,\n",
    "                        height=800)\n",
    "fig.update_layout(mapbox_style=\"open-street-map\")\n",
    "#fig.update_layout(margin={\"r\": 0, \"t\": 0, \"l\": 0, \"b\": 0})\n",
    "fig.show()\n",
    "# \"open-street-map\", \"carto-positron\", \"carto-darkmatter\", \"stamen-terrain\", \"stamen-toner\" or \"stamen-watercolor\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# A similar plot with open-street map, now the color indicates price as the target variable\n",
    "# with log2, for this the log of prices is added as a column \"log_prices\"\n",
    "kd['log_price'] = np.log2(kd['price']).tolist()\n",
    "# scatter_mapbox\n",
    "fig = px.scatter_mapbox(kd, lat=\"lat\", lon=\"long\",\n",
    "                        color='log_price', \n",
    "                        #opacity=0.9,\n",
    "                        color_continuous_scale=px.colors.sequential.Greys_r, \n",
    "                        #hover_name=\"yr_renovated\",\n",
    "                        #size='condition',\n",
    "                        size_max=7,\n",
    "                        zoom=8,\n",
    "                        height=800)\n",
    "fig.update_layout(mapbox_style=\"open-street-map\")\n",
    "#fig.update_layout(margin={\"r\": 0, \"t\": 0, \"l\": 0, \"b\": 0})\n",
    "fig.show()\n",
    "# \"open-street-map\", \"carto-positron\", \"carto-darkmatter\", \"stamen-terrain\", \"stamen-toner\" or \"stamen-watercolor\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Conclusions from the last three plots: \n",
    "The data set is very dense. However, only few houses have been renovated. For many houses this information is missing. The value \"0\" is given for most houses according to hovering over the map (see also some printed text statements below within this notebook). Houses in the south and in the hills are cheaper than those in the north. Houses in Bellevue and on Mercer Island are most expensive. Visualizing log2(prices) improves the visualization and allow for direct insights!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### What is the timespan during which the documented house sales occured?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# First inspection of the data \n",
    "# kd.shape\n",
    "# kd.head()\n",
    "# kd.tail()\n",
    "#kd.info()\n",
    "kd.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# This chunck of code provides the number of unique values or entries per column\n",
    "kd_nunique = []\n",
    "for i in range(0,(kd.shape[1])):\n",
    "    kd_nunique = kd_nunique + [kd.iloc[:,i].nunique()]\n",
    "kd_nunique = pd.DataFrame(zip(kd.columns.to_list(), kd_nunique))\n",
    "print(kd.shape)\n",
    "print(kd_nunique)\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### First inspection of the data structure and first ideas for an EDA of the class of \"multi_sale houses\"\n",
    "\n",
    "Please note: this notebook was iteratively extended. The EDA-analysis started here, however, the story for the stakeholder started with the extensions above.\n",
    "\n",
    "1. What is the shape? - 21597 rows and 21 columns\n",
    "    - 21597 home sales and 19 features, one home id and one price per sale\n",
    "    - 18  numeric data features (int64 or float64)\n",
    "    - 2   categorical data features (object)\n",
    "    - target variable: price\n",
    "    - features: \n",
    "        * id, date\n",
    "        * bedrooms, bathrooms, floors\n",
    "        * sqft_living, sqft_lot, sqft_above, sqft_basement, sqft_living15, sqft_lot15\n",
    "        * waterfront, view, zipcode, lat, long\n",
    "        * condition, grade, yr_built, yr_renovated"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2. Are there houses which are sold several times within the acquisition period?\n",
    "    - should only be recommended to buyers if there was an average gain when sold for the second time.\n",
    "    - other aspects of these houses need to be carefully inspected\n",
    "    - what are indicators to identify such houses on the current market (area, building year, price etc.)\n",
    "    - can be used to asses effects of renovation\n",
    "    - might be indicators of a poor neighborhood, with a sufficient gain and renovation this could have some social benefit along the financial gain for the buyer.\n",
    "    - might be worth a a short time investment for specific stakeholders. -> This can be tested using the unique method.\n",
    "    - example histories of multiple sales houses could be generated if n is not too low."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "3. What is the date range?\n",
    "    - 02-05-2014 - 27-05-2015"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# This is a new chunk of code added late to add the id_count to the master table.\n",
    "# Count the occurences of ids\n",
    "kd_id = pd.DataFrame(kd.id.value_counts())\n",
    "# Give a meaningfull name to the result column\n",
    "kd_id.columns = ['id_counts']\n",
    "\n",
    "# Create a dataframe from ids and id_counts\n",
    "kd_id_DF = pd.DataFrame({'id': kd_id.index.tolist(), 'id_counts': kd_id['id_counts'].reset_index().iloc[:,1]})\n",
    "\n",
    "# Join with kd_id\n",
    "kd =kd.set_index('id', drop=False).join(kd_id)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Are there homes which were sold more than twice within one year and if yes how often were these sold?\n",
    "\n",
    "# Count the occurences of ids\n",
    "kd_id = pd.DataFrame(kd.id.value_counts())\n",
    "# Give a meaningfull name to the result column\n",
    "kd_id.columns = ['id_counts']\n",
    "\n",
    "# To use the main table for subsetting the data, the index is adjusted to the id column entries and the id column is preserved: \n",
    "kd_multi = kd.set_index('id', drop=False )\n",
    "\n",
    "# I only kept rows for homes with at least two ids:\n",
    "kd_multi = kd_multi[kd_id['id_counts']>1]\n",
    "kd_sold_more_than_twice = kd_id[kd_id['id_counts']>2]\n",
    "\n",
    "print(f\"There are {len(kd_multi['id'].unique())} homes which were sold at least twice in 2014-2015.\")\n",
    "\n",
    "# Print the answer to the question in this cell:\n",
    "if len(kd_sold_more_than_twice) == 1:\n",
    "   print(f\"{len(kd_sold_more_than_twice)} home was sold more than twice. The id of this homes is {kd_sold_more_than_twice.index.tolist()[0]}.\")\n",
    "else:\n",
    "    print(f\"{len(kd_sold_more_than_twice)} homes were sold more than twice. The list of ids of these homes is: {kd_sold_more_than_twice.index.tolist()[:]}.\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(f\"There are {len(kd_multi['id'].unique())} multi-sale houses.\")\n",
    "#Was any of these homes renovated after 2013? \n",
    "for year in kd_multi[kd_multi['yr_renovated']>0]['yr_renovated']:\n",
    "    if year > 2013:\n",
    "        print(f\"Yes, id {kd_multi[kd_multi['yr_renovated'] == year]['id']} was renovated after 2013\")\n",
    "        break\n",
    "    else:\n",
    "        continue\n",
    "print(\"No, none of these homes was renovated in 2014 or 2015.\")\n",
    "\n",
    "#Was any of these homes renovated between 2008 and 2014?\n",
    "for year in kd_multi[kd_multi['yr_renovated']>0]['yr_renovated']:\n",
    "    if 2008 < year < 2013:\n",
    "        print(f\"Yes, id {kd_multi[kd_multi['yr_renovated'] == year]['id']} was renovated between 2008 and 2014.\")\n",
    "        break\n",
    "    else:\n",
    "        continue\n",
    "print(\"No, none of these homes was renovated between 2008 and 2014.\")\n",
    "\n",
    "#Was any of these homes renovated between 1994 and 2009?\n",
    "for year in kd_multi[kd_multi['yr_renovated']>0]['yr_renovated']:\n",
    "    if 1994 < year < 2009:\n",
    "        print(f\"Yes, id {kd_multi[kd_multi['yr_renovated'] == year]['id']} was renovated between 1994 and 2009.\")\n",
    "        break\n",
    "    else:\n",
    "        continue\n",
    "print(\"No, none of these homes was renovated between 1994 and 2009.\")\n",
    "\n",
    "# Is there a lot of missing data?\n",
    "print(f\"For {kd_multi['yr_renovated'].isnull().sum()} homes there is no information available on renovations.\")\n",
    "print(f\"Only for {int((kd_multi['yr_renovated']>0).sum()/2)} homes there is information other than '0' available on renovations.\")\n",
    "\n",
    "print(f\"The latest renovation of these houses was in {int(kd_multi['yr_renovated'].max())}.\")\n",
    "len(kd_multi['yr_renovated'].value_counts()>0)\n",
    "\n",
    "kd_multi[kd_multi['yr_renovated']>0]\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Only 4 houses out of (176-67) houses were renovated for the multi-sale houses. This corresponds to a ratio of 4/(176-64)=0.03571428571428571. About 1 out of 25 houses with information on renovation has been renovated."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "kd_single = kd.set_index('id', drop=False )\n",
    "kd_single.drop(kd_multi['id'], inplace=True, axis=0)\n",
    "#kd_multi['id'].unique().tolist()\n",
    "kd_single.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#What about the single-sale houses?\n",
    "print(f\"There are {len(kd_single['id'].unique())} single-sale houses.\")\n",
    "#Was any of these homes renovated after 2013? \n",
    "x = 0\n",
    "for year in kd_single[kd_single['yr_renovated']>0]['yr_renovated']:\n",
    "    if year > 2013:\n",
    "        print(f\"Yes, {len(kd_single[kd_single['yr_renovated'] > year]['id'].unique())} houses were renovated after 2013.\")\n",
    "        y=len(kd_single[kd_single['yr_renovated'] > year]['id']) * -1\n",
    "        x = 1\n",
    "        break\n",
    "    else:\n",
    "        continue\n",
    "if not x==1:\n",
    "    print(\"No, none of these homes was renovated in 2014 or 2015.\")\n",
    "\n",
    "#Was any of these homes renovated between 2008 and 2014?\n",
    "x=0\n",
    "for year in kd_single[kd_single['yr_renovated']>0]['yr_renovated']:\n",
    "    if 2008 < year < 2014:\n",
    "        print(f\"Yes, {len(kd_single[kd_single['yr_renovated'] > year]['id'].unique())+y} houses were renovated between 2008 and 2014.\")\n",
    "        z=(len(kd_single[kd_single['yr_renovated'] > year]['id'])+y)*-1\n",
    "        x=1\n",
    "        break\n",
    "    else:\n",
    "        continue\n",
    "if not x==1:\n",
    "    print(\"No, none of these homes was renovated between 2008 and 2014.\")\n",
    "\n",
    "#Was any of these homes renovated between 1994 and 2009?\n",
    "x=0\n",
    "for year in kd_single[kd_single['yr_renovated']>0]['yr_renovated']:\n",
    "    if 1994 < year < 2009:\n",
    "        print(f\"Yes, {len(kd_single[kd_single['yr_renovated'] > year]['id'].unique())+z} houses were renovated between 1994 and 2009.\\n{len(kd_single[kd_single['yr_renovated'] > year]['id'].unique())} houses were renovated in the last 20 years\")\n",
    "        x=1\n",
    "        break\n",
    "    else:\n",
    "        continue\n",
    "if not x==1:\n",
    "    print(\"No, none of these homes was renovated between 1994 and 2009.\")\n",
    "\n",
    "# Is there a lot of missing data?\n",
    "print(f\"For {kd_single['yr_renovated'].isnull().sum()} houses, there is no information available on renovations.\")\n",
    "# as all sales are per one house only (single) we do not have to devide by 2\n",
    "print(f\"Only for {int((kd_single['yr_renovated']>0).sum())} houses, there is meaningful information (other than '0' and NaN) available on renovations. These can be considered as houses with renovation.\")\n",
    "\n",
    "print(f\"The latest renovation of these houses was in {int(kd_single['yr_renovated'].max())}.\")\n",
    "len(kd_single['yr_renovated'].value_counts()>0)\n",
    "\n",
    "kd_single[kd_single['yr_renovated']>0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "736 houses out of (21244-3775) houses were renovated for the single-sale houses. This corresponds to a ratio of 736/(21244-3775)=0.042131776289426985. About 1 out of 25 houses with information on renovation has been renovated. When rounding these proportions are similar between both classes. Therefore, the multi-sale houses were not bough for renovation to increase the price and are still available for such an approach. Also, as these were already sold at least twice within a short period of time, these might be comparably easy to be sold in the future if there was even additional renovation."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# As renovation did not occur between two sale events within one year other reasons might be mere speculation or the owner had to sell the object \n",
    "# maybe for financial or other personal reason or the object was sold due to the death of the previous owner.\n",
    "# Also, it would be interesting to know if the multi-sale homes are expensive homes relative to their other characteristics. \n",
    "# However the opposite would also make sense as these houses were not renovated and sold rapidly which could also be an indicator of financial problems.\n",
    "# If the latter holds true, these houses might be indicators of comparably poorer neighborhoods."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Prices across all houses (sale ids were used here) are compared between both classes of sale:\n",
    "# Scatterplot of ids and price plotted on top of each other for simple visualization\n",
    "fig = sns.scatterplot(data= kd_single, x=kd_single['id'], y=kd_single['price'])\n",
    "fig = sns.scatterplot(data= kd_multi, x=kd_multi['id'], y=kd_multi['price'])\n",
    "fig.set(xticks=[])\n",
    "fig.set(xticklabels=[])\n",
    "fig.set_xlabel('Individual houses', size=14)\n",
    "fig.set_title('Comparison of house sale prices:\\n- single-sale vs. multi-sale class of sales -', pad = 25, size=16)\n",
    "fig.set_ylabel('Price [$] (in million)', size =14)\n",
    "plt.show()\n",
    "# Due to the nature of the data this plot is already sufficient to illustrate the trent."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Orange: multi_sale houses<br>\n",
    "Blue: single_sale houses<br>\n",
    "Multi-sale houses are cheaper than single-sale houses.<br>\n",
    "Where are the multi-sale houses located?\n",
    "What is their condition?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# plot the location of multi-sale houses with coloration of the prize and the condition shown by size.\n",
    "fig = px.scatter_mapbox(kd_multi, lat=\"lat\", lon=\"long\",\n",
    "                        color='price', \n",
    "                        #opacity=0.9,\n",
    "                        color_continuous_scale=px.colors.sequential.Greys, \n",
    "                        hover_name=\"grade\",\n",
    "                        size='condition',\n",
    "                       # size_max=7,\n",
    "                        zoom=8,\n",
    "                        height=800)\n",
    "fig.update_layout(mapbox_style=\"stamen-terrain\")\n",
    "#fig.update_layout(margin={\"r\": 0, \"t\": 0, \"l\": 0, \"b\": 0})\n",
    "fig.show()\n",
    "# \"open-street-map\", \"carto-positron\", \"carto-darkmatter\", \"stamen-terrain\", \"stamen-toner\" or \"stamen-watercolor\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Is the condition comparably low as compared to single-sale houses?\n",
    "# Creating a dataset of medians for each feature. However, categorical features would require counting.\n",
    "data = pd.concat([pd.DataFrame(kd_single.median().reset_index()),pd.DataFrame(kd_multi.median().reset_index())], axis=1).iloc[1:, 1:4]\n",
    "data.set_axis(['features_single','feature_name','features_multi'], axis=1, inplace =True)\n",
    "data\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "kd_multi.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**We can derive multiple information from the two tables above.**   \n",
    "* The median for both classes for the condition (3), grade (7) and bedrooms (3) is similar. This means on average, subtle renovations could be sufficient in most cases.   \n",
    "* The area for living and of the lot is similar to the neighbors within both classes.  \n",
    "\n",
    "**Potential indicators for a comparably poor area?**  \n",
    "* The price is lower and thehouses are older in the multi-sale class.  \n",
    "* The area for living as compared to the single-sale class is less which could be due to the number of floors (1, vs 1.5 for single-sale houses).  \n",
    "* Interestingly the lot area is larger for the multi-sale houses which could be a reason for selling and/or these are just an indicator that these are located in more rural areas of the city where there is more space per house available than in the city center, for example.  \n",
    "* None of the multi-sale houses is at the water front which is more central and where houses are also more expensive.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "kd_single.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "kd_multi.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Preparing the data for boxplots comparing single and multi sale homes' prices:\n",
    "#data = [kd_single['price'], kd_multi['price']]\n",
    "# log2 was initially calculated as still shown in the code below. As we now added a log_price column, we could also now access this column here.\n",
    "data = [np.log2(kd_single['price']).tolist(), np.log2(kd_multi['price']).tolist()]\n",
    "\n",
    "fig = plt.figure(figsize = (10,7))\n",
    "ax = fig.add_axes([0,0,1,1])\n",
    "bp = ax.boxplot(data)\n",
    "plt.show()\n",
    "\n",
    "# Adjust decimal to 3\n",
    "print(\"\\nDescriptive statistics for the single-sale homes:\\n\")\n",
    "print(kd_single['price'].describe())\n",
    "print(\"\\nDescriptive statistics for the multi-sale homes:\\n\")\n",
    "print(kd_multi['price'].describe())\n",
    "print(f\"\\nThe median price of single-sale homes' is: {int(kd_single['price'].median())}\")\n",
    "print(f\"The median price of multi-sale homes' is: {int(kd_multi['price'].median())}\")\n",
    "print(f\"The mode price of single-sale homes' prices is: {int(statistics.mode(kd_single['price']))}\")\n",
    "print(f\"The mode price of multi-sale homes' prices is: {int(statistics.mode(kd_multi['price']))}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Conclusions from the table above**\n",
    "* Often old houses are expensive with respect to renovation but also might be cheaper in prize. \n",
    "* Taken together this could be houses which are bought by people on comparable poorer neighborhoods or just got too expensive for their owners also in the middle class of incomes.\n",
    "* To test this, we inspect the respective boxplots.\n",
    "* These boxplots were also inspected for other conclusions within this notebook prior to deciding on the data to present."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Preparing the data for the boxplot comparing single and multi sale homes' prices:\n",
    "data = [kd_single['yr_built'].dropna(), kd_multi['yr_built'].dropna()]\n",
    "\n",
    "fig = plt.figure(figsize = (10,7))\n",
    "ax = fig.add_axes([0,0,1,1])\n",
    "bp = ax.boxplot(data)\n",
    "plt.show()\n",
    "\n",
    "print(\"\\nDescriptive statistics for the single-sale homes:\\n\")\n",
    "print(kd_single['yr_built'].describe())\n",
    "print(f\"\\nThe median building years of single-sale homes is: {int(kd_single['yr_built'].median())}\")\n",
    "print(f\"The mode of the building years of single-sale homes is: {statistics.mode(kd_single['yr_built'])}\")\n",
    "\n",
    "print(\"\\nDescriptive statistics for the single-sale homes is:\\n\")\n",
    "print(kd_multi['yr_built'].describe())\n",
    "print(f\"\\nThe median building years of multi-sale homes: {int(kd_multi['yr_built'].median())}\")\n",
    "print(f\"The mode of the building years of multi-sale homes is: {statistics.mode(kd_multi['yr_built'])}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Another ideas incubator box....\n",
    "# I could include a multi_sale id and split the two sales of one home_id into two events. \n",
    "# For each sale_id the price of the earliest sale is substracted from the latest sale and we plot the profit or loss, respectively by month of buying and month of sale for sale_id and by the time expired between buying and saling the home. \n",
    "# We also want to know if the groups of sale_ids for which a renovation occurred  within this period or within a period of five years before the home was bought show a different gain-loss pattern.\n",
    "#   -> This idea is not followed as the number of renovated homes within this group is too low (4 of 176)\n",
    "# Eventually we plot the houses within Seattle to see if the multi_sale homes are clustering in a certain area (center vs suburb or zip codes)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# With this chunk of code the respective unique entries and their writing can be inspected \n",
    "# either for all or for selected columns only without the loop and specific selection of the respective column of interest.\n",
    "# However, this is only usefull for a very first assessment of the data and to find spelling differences as compared to expected entries or similar.\n",
    "\n",
    "# for i in range(0,(kd.shape[1])):\n",
    "#    print(kd.iloc[:,i].unique())\n",
    "\n",
    "# For the question on the format of the data we can get the answer as follows:\n",
    "print(kd['date'].head())\n",
    "kd.date = pd.to_datetime(kd.date)\n",
    "kd['year'] = pd.to_datetime(kd.date).dt.year\n",
    "kd['month'] = pd.to_datetime(kd.date).dt.month\n",
    "kd['month_day'] = pd.DataFrame(kd['date'].dt.strftime('%m-%d'))\n",
    "kd.head()\n",
    "kd.info()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Back to the maps. Is there a pattern for the year of sale? \n",
    "\n",
    "kd['log_price'] = np.log2(kd['price']).tolist()\n",
    "# scatter_mapbox\n",
    "fig = px.scatter_mapbox(kd, lat=\"lat\", lon=\"long\",\n",
    "                        color='year', \n",
    "                        #opacity=0.9,\n",
    "                        color_continuous_scale=px.colors.sequential.Greys_r, \n",
    "                        #hover_name=\"yr_renovated\",\n",
    "                        #size='condition',\n",
    "                        size_max=7,\n",
    "                        zoom=8,\n",
    "                        height=800)\n",
    "fig.update_layout(mapbox_style=\"open-street-map\")\n",
    "#fig.update_layout(margin={\"r\": 0, \"t\": 0, \"l\": 0, \"b\": 0})\n",
    "fig.show()\n",
    "# \"open-street-map\", \"carto-positron\", \"carto-darkmatter\", \"stamen-terrain\", \"stamen-toner\" or \"stamen-watercolor\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "No, there is no obvious pattern  by the year.\n",
    "What about the month?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "kd['log_price'] = np.log2(kd['price']).tolist()\n",
    "# scatter_mapbox\n",
    "fig = px.scatter_mapbox(kd, lat=\"lat\", lon=\"long\",\n",
    "                        color='log_price', \n",
    "                        #opacity=0.9,\n",
    "                        color_continuous_scale=px.colors.sequential.Greys, \n",
    "                        #hover_name=\"yr_renovated\",\n",
    "                        #size='condition',\n",
    "                        size_max=7,\n",
    "                        zoom=8,\n",
    "                        height=800)\n",
    "fig.update_layout(mapbox_style=\"open-street-map\")\n",
    "#fig.update_layout(margin={\"r\": 0, \"t\": 0, \"l\": 0, \"b\": 0})\n",
    "fig.show()\n",
    "# \"open-street-map\", \"carto-positron\", \"carto-darkmatter\", \"stamen-terrain\", \"stamen-toner\" or \"stamen-watercolor\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "No, again nor obvious pattern. This means the data is well distributed across years and month. Now we can plot the price against the month_day for a high resolution and than month per year to get an idea about general changes of the price over the year. However, when narrowing down this dataset, it might be required to analyze this again for the subset of data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.bar(x= kd['month_day'], height= kd['price'])\n",
    "labels = kd['month'].unique()\n",
    "grouped_year = kd.groupby('year')\n",
    "price_month_2014 = grouped_year.get_group(2014)[['price', 'month']]\n",
    "price_month_2015 = grouped_year.get_group(2015)[['price', 'month']]\n",
    "\n",
    "#x: month_day\n",
    "#y: price [$]\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We see that there are from time to time specific objects with a high price, these might need a specific treatment, however, here we will concentrate on poor neighborhoods in which we assume the house prices to be at least below the median."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "price_month_2014['price'].plot(kind='hist')\n",
    "price_month_2014.groupby('month')['price'].plot(kind = 'hist')\n",
    "#x: price [$]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "price_month_2015['price'].plot(kind='hist')\n",
    "price_month_2015.groupby('month')['price'].plot(kind = 'hist')\n",
    "#x: price [$]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As the histograms from above are not really helpful, we do something similar with plotly also indicating where individual data points are in this right skewed distribution. Note that the counts per year are added and the count per year becomes visible when hovering over the plot only. As expected, The counts for 2014 are higher as there is data from May to December while for 2015 we only have data from January to May."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = kd\n",
    "fig = px.histogram(df, x=\"price\", color=\"year\", marginal=\"rug\", # can be `box`, `violin`\n",
    "                         hover_data=df.columns)\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# In this histogram, all data per month are added according to the color-coded order\n",
    "\n",
    "df = kd\n",
    "fig = px.histogram(df, x=\"price\", color=\"month\", marginal=\"rug\", # can be `box`, `violin`\n",
    "                         hover_data=df.columns)\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Eventually we compare May in 2014 with 2015, the only month with data from two years."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = kd.query('month==5')\n",
    "fig = px.histogram(df, x=\"price\", color=\"year\", marginal=\"rug\", # can be `box`, `violin`\n",
    "                         hover_data=df.columns)\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Median and mean price and other stats for 2014 and 2015 by month\n",
    "print('Prices in 2014')\n",
    "print(pd.concat([pd.DataFrame(price_month_2015.groupby('month').median()),pd.DataFrame(price_month_2015.groupby('month').mean())], axis=1, keys = ['prices_median','prices_mean']))\n",
    "print('Prices in 2015')\n",
    "print(pd.concat([pd.DataFrame(price_month_2014.groupby('month').median()),pd.DataFrame(price_month_2014.groupby('month').mean())], axis=1, keys = ['prices_median','prices_mean']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# The seaborn barplot plots the means and the standarddeviation\n",
    "sns.barplot(data=kd,x='month',y='price', hue='year')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Another plot for May 2014 and 2015**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = kd.query('month==5').sort_values('month_day')\n",
    "sns.barplot(data=df,x='month_day',y='price', hue='year')\n",
    "plt.xticks(rotation=45)\n",
    "plt.show()\n",
    "\n",
    "print(\"Statistic information for May 2014:\")\n",
    "\n",
    "print(df[df['year']==2014].describe())\n",
    "\n",
    "print(\"\\nStatistic information for May 2015:\")\n",
    "\n",
    "print(df[df['year']==2015].describe())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It might be that the data in 2015 between mid of May and end of May is incomplete. However, there is no strong trend between the years within the first half of May. We can assume that during 2014 there was no overall surprising shift in prices. When we look a bit more into the details, the mean, min and max point to an increase over time as expected as the population in this area is also constantly increasing which can be expected to result in an increase in demand. However, more data would be needed to support this trend which is not available for now. <br>\n",
    "<br>\n",
    "A better dataset would include data from more than one year. This would provide a safer basis for recommendations for the stakeholders."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Is the period exactly one year or is there an overlap in May?\n",
    "kd.date.min(), kd.date.max()\n",
    "\n",
    "# There are 25 days of overlap for May, so May is the only month with overlapping data from two years.\n",
    "# Statements relative to the timing are not very well supported as there might have been specific events in 2014 and/or 2015.\n",
    "# This needs to be underlined when suggesting a specific month to be optimal for selling or buying depending on the price."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Are there outliers? This is checked here with a box plot and can be selected by column number while exploring the data, select for which plots a log transformation is required.\n",
    "kd.iloc[:,2].plot.box(grid='True')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Most prices are below $ 1,000,000 as also seen on the geographic map initially."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Her log2 is plotted per selectde column as this is advisable for some features. Such columns were collected in the list log_columns\n",
    "log_columns = [2,3,4,5,6,12,19,20]\n",
    "np.log2(kd.iloc[:,2]).plot.box(grid='True')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Now get hands on question 2:\n",
    "* Get the gain loss per multi-sale house"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#multi-sales2 (2 sales within one year)\n",
    "kd_multi2 = kd.query('id_counts == 2').reset_index()\n",
    "kd_multi2 \n",
    "#px.scatter_3d(kd_multi2, x='id', y='date',z='price')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# get a function to get the gain or loss per sale\n",
    "def x(df):\n",
    "    i=1\n",
    "    df.iloc[i,'id']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def x(df,ID):\n",
    "    df_ID = df[df['id']==ID]\n",
    "    df_max = df_ID['date'].max()\n",
    "    df_min = df_ID['date'].min()\n",
    "    result = df_ID[df_ID['date']==df_max]['price'].iloc[0] - df_ID[df_ID['date']==df_min]['price'].iloc[0]\n",
    "    return result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "multi2_ID = kd_multi2['id'].unique().tolist()\n",
    "price_span_multi = [x(kd_multi2,ID) for ID in multi2_ID]\n",
    "\n",
    "# define a function for calculating the win per multi-sale house\n",
    "def x(df,ID):\n",
    "    df_ID = df[df['id']==ID]\n",
    "    df_max = df_ID['date'].max()\n",
    "    df_min = df_ID['date'].min()\n",
    "    result = df_ID[df_ID['date']==df_max]['price'].iloc[0] - df_ID[df_ID['date']==df_min]['price'].iloc[0]\n",
    "    return result\n",
    "\n",
    "# Create a dataframe from this\n",
    "kd_price_span_multi = pd.DataFrame({'id': multi2_ID, 'price_span_multi': price_span_multi})\n",
    "#and plot the win in $ per multi-sale house"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fig = sns.barplot(data=kd_price_span_multi, x='id',y='price_span_multi', color='black')\n",
    "fig.set(xticks=[])\n",
    "fig.set(xticklabels=[])\n",
    "fig.set(xlabel='Individual multi-sale houses')\n",
    "fig.set(title='Win from multi-sale houses [$]')\n",
    "fig.set(ylabel='Win [$]')\n",
    "plt.show()\n",
    "print(f\"Median win: {(kd_price_span_multi['price_span_multi'].median())}\")\n",
    "kd_price_span_multi['price_span_multi'].describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Answer\n",
    "Indeed, multi-sale houses are suitable for renovations. The median win is \\$122,300. Therefore, I would suggest to invest not more than 0.6*122300 in renovations. As the maximal loss was \\$40,000 and renovations can be expected to increase the value of the house, this is conservative in risk and allows for a positive social footprint when buying renovating and selling multiple of such houses.\n",
    "Note, also that multi-sale houses have a similar grade and as single sale houses. They usually have only one floor and less sqrt of living area as compared to single-sale houses. If more living space can be generated by the available amount of money this might be beneficial. The latter statements are extracted from a table generated above. See also below for a recap.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Comaprison of features between single- and multi-sale houses.\n",
    "# Categorical data can be removed as it is not infomrative here. In the future, a relative count result from this data type can be added to the analysis.\n",
    "print(\"Median per feature and sale-type:\")\n",
    "data = pd.concat([pd.DataFrame(kd_single.median().reset_index()),pd.DataFrame(kd_multi.median().reset_index())], axis=1).iloc[1:, 1:4]\n",
    "data.set_axis(['features_single','feature_name','features_multi'], axis=1, inplace =True)\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# **Outlook:**\n",
    "\n",
    "* It can be assumed that there is a general increase of house prices in the whole area. In particular in southern regions as grouped by zip code one could follow the development of prices over time (per month) using the full dataset as well as the multi-sale dataset. As the latter is comparably small, the full dataset should be preferred for conclusions.\n",
    "* Another positive aspect about knowing about these multi-sale houses is that it was possible to sell these again within one year and, therefore, these houses and those in their neighborhood are interesting objects to invest in. \n",
    "* More focus could be layed on the those neighborhoods which are poorer than the average multi-sale areas. However, being more confident about the recommendation for the stakeholder was prioritized above risk and therefore the available price increases were used as a basis. As this stakeholder works with comparably low gains, the risk should be minimized. \n",
    "* To support this stakeholder to succeed against competitors with less social strategies, one could do correlation analysis with feature vectors of numerical and count data for all houses, single-sale houses and multi-sale houses in the whole area and also in poorer areas as defined by 33% of the lowest prices from all houses in the datasets. This way one could try to identify houses that belong to the same class, located in at least comparably poorer neighborhoods and might be easily sold within a short time frame.\n",
    "* One has to admit that the medium price for a house in the discussed multi-sale class of sales is low but not very low ($340,000). A really poor neighborhood might be better defined by even going below this price."
   ]
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "268b01054c0ffe9e2159bc2da6070dcbdb37e0946ed89a21510c583c5c7d40a9"
  },
  "kernelspec": {
   "display_name": "Python 3.9.8 ('.venv': venv)",
   "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.9.8"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}