Data_Cleaning_and_Feature_Engineering.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "ef164f61",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "LOAD DURATION:  0:00:00.731530\n"
     ]
    }
   ],
   "source": [
    "from datetime import datetime\n",
    "start_time = datetime.now()\n",
    "\n",
    "# Libraries to install\n",
    "# %pip install pandas-profiling\n",
    "\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "%matplotlib inline\n",
    "\n",
    "print('LOAD DURATION: ', datetime.now() - start_time) # load time about 30 seconds"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "7310a1b4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 4.33 s, sys: 444 ms, total: 4.77 s\n",
      "Wall time: 4.77 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "property_data = pd.read_csv('NY property data.csv.zip', compression=\"zip\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0f10aed9",
   "metadata": {},
   "source": [
    "## Exclusion"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "7f8d76e8",
   "metadata": {},
   "outputs": [],
   "source": [
    "excluded_owners = [\n",
    "    'PARKCHESTER PRESERVAT',\n",
    "    'PARKS AND RECREATION',\n",
    "    'DCAS',\n",
    "    'HOUSING PRESERVATION',\n",
    "    'CITY OF NEW YORK',\n",
    "    'DEPT OF ENVIRONMENTAL',\n",
    "    'BOARD OF EDUCATION',\n",
    "    \"NEW YORK CITY HOUSING\",\n",
    "    'CNY/NYCTA',\n",
    "    'NYC HOUSING PARTNERSH',\n",
    "    'DEPARTMENT OF BUSINES',\n",
    "    'DEPT OF TRANSPORTATIO',\n",
    "    'MTA/LIRR',\n",
    "    'PARCKHESTER PRESERVAT',\n",
    "    'MH RESIDENTIAL 1, LLC',\n",
    "    'LINCOLN PLAZA ASSOCIA',\n",
    "    'UNITED STATES OF AMER',\n",
    "    'U S GOVERNMENT OWNRD',\n",
    "    'THE CITY OF NEW YORK',\n",
    "    'NYS URBAN DEVELOPMENT',\n",
    "    'NYS DEPT OF ENVIRONME',\n",
    "    'CULTURAL AFFAIRS',\n",
    "    'DEPT OF GENERAL SERVI',\n",
    "    'DEPT RE-CITY OF NY'\n",
    "]\n",
    "\n",
    "property_data = (\n",
    "    property_data\n",
    "    .query(\"`OWNER` not in @excluded_owners\")\n",
    "    .reset_index(drop=True)\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "03ec8c4f",
   "metadata": {},
   "source": [
    "## Data Cleaning: Fill in Missing Values"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cd2faef9",
   "metadata": {},
   "source": [
    "1. Make a list of the owner names to remove. Remove all records with those owners. Make sure you get all this right.\n",
    "2. Fill in the missing ZIPs\n",
    "3. Fill in missing FULLVAL, AVLAND, AVTOT\n",
    "4. Fill in missing STORIES\n",
    "5. Fill in LTFRONT, LTDEPTH, BLDFRONT, BLDDEPTH"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4792bfbd",
   "metadata": {},
   "source": [
    "### Fill in Missing Zip"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "4dcb5e7e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "21772"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "missing_zips = np.where(pd.isnull(property_data['ZIP']))[0]\n",
    "len(missing_zips)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1cd4b7c0",
   "metadata": {},
   "source": [
    "If the zip on both the record before and after the record with the missing zip are the same, replace by that zip code"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "3f67739e",
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in range(len(missing_zips)):\n",
    "    if(property_data.loc[missing_zips[i]+1, 'ZIP'] == property_data.loc[missing_zips[i]-1,'ZIP']):\n",
    "        property_data.loc[missing_zips[i],'ZIP'] = property_data.loc[missing_zips[i]-1, 'ZIP']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "dcac21d2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "10245"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "missing_zips = np.where(pd.isnull(property_data['ZIP']))[0]\n",
    "len(missing_zips)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "66b30c1a",
   "metadata": {},
   "source": [
    "For the rest of the missing zips, I simply replace missing zips with the zip from record above it"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "4572a58c",
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in range(len(missing_zips)):\n",
    "    property_data.loc[missing_zips[i],'ZIP'] = property_data.loc[missing_zips[i]-1,'ZIP']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "ab0a7aa9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "missing_zips = np.where(pd.isnull(property_data['ZIP']))[0]\n",
    "len(missing_zips)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "20d8ec26",
   "metadata": {},
   "source": [
    "### Fill in Missing FULLVAL, AVLAND, AVTOT"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "80a2934f",
   "metadata": {},
   "source": [
    "Calculate means for AVTOT, AVLAND, FULLVAL by taxclass, avoiding records with zeros"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "c6233062",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "TAXCLASS\n",
      "1     5.700059e+05\n",
      "1A    3.352942e+05\n",
      "1B    3.738399e+05\n",
      "1C    7.615359e+05\n",
      "1D    2.233614e+07\n",
      "2     8.005832e+05\n",
      "2A    8.639066e+05\n",
      "2B    1.254525e+06\n",
      "2C    7.723493e+05\n",
      "3     8.510005e+04\n",
      "4     2.772747e+06\n",
      "Name: FULLVAL, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "property_data['FULLVAL'].replace('NaN',0)\n",
    "temp = property_data[property_data['FULLVAL']!=0]\n",
    "mean_fullval = temp.groupby('TAXCLASS')['FULLVAL'].mean()\n",
    "print(mean_fullval)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "34c00f91",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "TAXCLASS\n",
      "1      14901.860425\n",
      "1A      2247.472465\n",
      "1B      9185.394750\n",
      "1C      8225.658898\n",
      "1D    709303.793103\n",
      "2      88471.102578\n",
      "2A     31370.740197\n",
      "2B     54607.394911\n",
      "2C     25635.944099\n",
      "3      28135.636364\n",
      "4     444996.049088\n",
      "Name: AVLAND, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "property_data['AVLAND'].replace('NaN',0)\n",
    "temp_avland = property_data[property_data['AVLAND']!=0]\n",
    "mean_avland = temp_avland.groupby('TAXCLASS')['AVLAND'].mean()\n",
    "print(mean_avland)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "e00861c9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "TAXCLASS\n",
      "1     2.502549e+04\n",
      "1A    1.437036e+04\n",
      "1B    9.189080e+03\n",
      "1C    2.898447e+04\n",
      "1D    1.166866e+06\n",
      "2     3.602638e+05\n",
      "2A    7.963525e+04\n",
      "2B    1.790961e+05\n",
      "2C    1.170418e+05\n",
      "3     3.829505e+04\n",
      "4     1.294237e+06\n",
      "Name: AVTOT, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "property_data['AVTOT'].replace('NaN',0)\n",
    "temp_avtot = property_data[property_data['AVTOT']!=0]\n",
    "mean_avtot = temp_avtot.groupby('TAXCLASS')['AVTOT'].mean()\n",
    "print(mean_avtot)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f15e100a",
   "metadata": {},
   "source": [
    "Substitute decent values for AVTOT, AVLAND, FULLVAL from averages by taxclass"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "563c2ad5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 4.48 s, sys: 5.64 ms, total: 4.48 s\n",
      "Wall time: 4.36 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "for index in mean_fullval.index:\n",
    "    property_data.loc[(property_data['FULLVAL']==0)&(property_data['TAXCLASS']==index),'FULLVAL']=mean_fullval[index]\n",
    "    property_data.loc[(property_data['AVLAND']==0)&(property_data['TAXCLASS']==index),'AVLAND']=mean_avland[index]\n",
    "    property_data.loc[(property_data['AVTOT']==0)&(property_data['TAXCLASS']==index),'AVTOT']=mean_avtot[index]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5cf8e9bc",
   "metadata": {},
   "source": [
    "### Fill in Missing STORIES"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "436b74d4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "43968"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "temp = property_data[property_data['STORIES'].isnull()]\n",
    "len(temp)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "407b9663",
   "metadata": {},
   "source": [
    "In addition, there should not be a value zero for this field, so we treat zeros as missing values as well.\n",
    "I'm grouping by TAXCLASS, since that should be a pretty good description of the nature of the buildings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "78c90f53",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1B    19055\n",
       "4     17979\n",
       "2      3434\n",
       "3      2416\n",
       "1       877\n",
       "2C      138\n",
       "2B       34\n",
       "2A       30\n",
       "1A        5\n",
       "Name: TAXCLASS, dtype: int64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "temp['TAXCLASS'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "ac66e410",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "TAXCLASS\n",
      "1      2.111633\n",
      "1A     1.656867\n",
      "1B     4.000000\n",
      "1C     3.052748\n",
      "1D     1.068966\n",
      "2     16.310549\n",
      "2A     2.844032\n",
      "2B     4.001635\n",
      "2C     4.744977\n",
      "3      1.000000\n",
      "4      5.517257\n",
      "Name: STORIES, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "mean_stories = property_data.groupby('TAXCLASS')['STORIES'].mean()\n",
    "print(mean_stories)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "46412b18",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "property_data.query(\"STORIES == 0\").shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "8d5f4a6b",
   "metadata": {},
   "outputs": [],
   "source": [
    "property_data['STORIES']=property_data['STORIES'].fillna(value=0)\n",
    "for index in mean_stories.index:\n",
    "    property_data.loc[(property_data['STORIES']==0)&(property_data['TAXCLASS']==index),'STORIES']=mean_stories[index]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "6f99c812",
   "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>RECORD</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BBLE</th>\n",
       "      <td>1000010101</td>\n",
       "      <td>1000010201</td>\n",
       "      <td>1000041001</td>\n",
       "      <td>1000041002</td>\n",
       "      <td>1000041003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BLOCK</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LOT</th>\n",
       "      <td>101</td>\n",
       "      <td>201</td>\n",
       "      <td>1001</td>\n",
       "      <td>1002</td>\n",
       "      <td>1003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EASEMENT</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OWNER</th>\n",
       "      <td>U S GOVT LAND &amp; BLDGS</td>\n",
       "      <td>U S GOVT LAND &amp; BLDGS</td>\n",
       "      <td>TRZ HOLDINGS, LLC</td>\n",
       "      <td>TRZ HOLDINGS, LLC</td>\n",
       "      <td>TRZ HOLDINGS, LLC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BLDGCL</th>\n",
       "      <td>P7</td>\n",
       "      <td>Z9</td>\n",
       "      <td>R5</td>\n",
       "      <td>R5</td>\n",
       "      <td>R5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TAXCLASS</th>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LTFRONT</th>\n",
       "      <td>500</td>\n",
       "      <td>27</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LTDEPTH</th>\n",
       "      <td>1046</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EXT</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>STORIES</th>\n",
       "      <td>5.517257</td>\n",
       "      <td>5.517257</td>\n",
       "      <td>50.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FULLVAL</th>\n",
       "      <td>21400000.0</td>\n",
       "      <td>193800000.0</td>\n",
       "      <td>3944762.0</td>\n",
       "      <td>5700930.0</td>\n",
       "      <td>10600000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AVLAND</th>\n",
       "      <td>4225500.0</td>\n",
       "      <td>14310000.0</td>\n",
       "      <td>636093.0</td>\n",
       "      <td>919276.0</td>\n",
       "      <td>967500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AVTOT</th>\n",
       "      <td>9630000.0</td>\n",
       "      <td>87210000.0</td>\n",
       "      <td>1775143.0</td>\n",
       "      <td>2565419.0</td>\n",
       "      <td>4770000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EXLAND</th>\n",
       "      <td>4225500.0</td>\n",
       "      <td>14310000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EXTOT</th>\n",
       "      <td>9630000.0</td>\n",
       "      <td>87210000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EXCD1</th>\n",
       "      <td>4600.0</td>\n",
       "      <td>4600.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>STADDR</th>\n",
       "      <td>1 LIBERTY ISLAND</td>\n",
       "      <td>1 ELLIS ISLAND</td>\n",
       "      <td>1 WATER STREET</td>\n",
       "      <td>1 WATER STREET</td>\n",
       "      <td>1 WATER STREET</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZIP</th>\n",
       "      <td>10004.0</td>\n",
       "      <td>10004.0</td>\n",
       "      <td>10004.0</td>\n",
       "      <td>10004.0</td>\n",
       "      <td>10004.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EXMPTCL</th>\n",
       "      <td>X3</td>\n",
       "      <td>X3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BLDFRONT</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BLDDEPTH</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AVLAND2</th>\n",
       "      <td>3775500.0</td>\n",
       "      <td>11111400.0</td>\n",
       "      <td>636093.0</td>\n",
       "      <td>919276.0</td>\n",
       "      <td>967500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AVTOT2</th>\n",
       "      <td>8613000.0</td>\n",
       "      <td>80690400.0</td>\n",
       "      <td>2049290.0</td>\n",
       "      <td>2961617.0</td>\n",
       "      <td>5483912.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EXLAND2</th>\n",
       "      <td>3775500.0</td>\n",
       "      <td>11111400.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EXTOT2</th>\n",
       "      <td>8613000.0</td>\n",
       "      <td>80690400.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EXCD2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PERIOD</th>\n",
       "      <td>FINAL</td>\n",
       "      <td>FINAL</td>\n",
       "      <td>FINAL</td>\n",
       "      <td>FINAL</td>\n",
       "      <td>FINAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>YEAR</th>\n",
       "      <td>2010/11</td>\n",
       "      <td>2010/11</td>\n",
       "      <td>2010/11</td>\n",
       "      <td>2010/11</td>\n",
       "      <td>2010/11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VALTYPE</th>\n",
       "      <td>AC-TR</td>\n",
       "      <td>AC-TR</td>\n",
       "      <td>AC-TR</td>\n",
       "      <td>AC-TR</td>\n",
       "      <td>AC-TR</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              0                      1                  2  \\\n",
       "RECORD                        1                      2                  9   \n",
       "BBLE                 1000010101             1000010201         1000041001   \n",
       "B                             1                      1                  1   \n",
       "BLOCK                         1                      1                  4   \n",
       "LOT                         101                    201               1001   \n",
       "EASEMENT                    NaN                    NaN                NaN   \n",
       "OWNER     U S GOVT LAND & BLDGS  U S GOVT LAND & BLDGS  TRZ HOLDINGS, LLC   \n",
       "BLDGCL                       P7                     Z9                 R5   \n",
       "TAXCLASS                      4                      4                  4   \n",
       "LTFRONT                     500                     27                  0   \n",
       "LTDEPTH                    1046                      0                  0   \n",
       "EXT                         NaN                    NaN                NaN   \n",
       "STORIES                5.517257               5.517257               50.0   \n",
       "FULLVAL              21400000.0            193800000.0          3944762.0   \n",
       "AVLAND                4225500.0             14310000.0           636093.0   \n",
       "AVTOT                 9630000.0             87210000.0          1775143.0   \n",
       "EXLAND                4225500.0             14310000.0                0.0   \n",
       "EXTOT                 9630000.0             87210000.0                0.0   \n",
       "EXCD1                    4600.0                 4600.0                NaN   \n",
       "STADDR         1 LIBERTY ISLAND         1 ELLIS ISLAND     1 WATER STREET   \n",
       "ZIP                     10004.0                10004.0            10004.0   \n",
       "EXMPTCL                      X3                     X3                NaN   \n",
       "BLDFRONT                      0                      0                  0   \n",
       "BLDDEPTH                      0                      0                  0   \n",
       "AVLAND2               3775500.0             11111400.0           636093.0   \n",
       "AVTOT2                8613000.0             80690400.0          2049290.0   \n",
       "EXLAND2               3775500.0             11111400.0                NaN   \n",
       "EXTOT2                8613000.0             80690400.0                NaN   \n",
       "EXCD2                       NaN                    NaN                NaN   \n",
       "PERIOD                    FINAL                  FINAL              FINAL   \n",
       "YEAR                    2010/11                2010/11            2010/11   \n",
       "VALTYPE                   AC-TR                  AC-TR              AC-TR   \n",
       "\n",
       "                          3                  4  \n",
       "RECORD                   10                 11  \n",
       "BBLE             1000041002         1000041003  \n",
       "B                         1                  1  \n",
       "BLOCK                     4                  4  \n",
       "LOT                    1002               1003  \n",
       "EASEMENT                NaN                NaN  \n",
       "OWNER     TRZ HOLDINGS, LLC  TRZ HOLDINGS, LLC  \n",
       "BLDGCL                   R5                 R5  \n",
       "TAXCLASS                  4                  4  \n",
       "LTFRONT                   0                  0  \n",
       "LTDEPTH                   0                  0  \n",
       "EXT                     NaN                NaN  \n",
       "STORIES                50.0               50.0  \n",
       "FULLVAL           5700930.0         10600000.0  \n",
       "AVLAND             919276.0           967500.0  \n",
       "AVTOT             2565419.0          4770000.0  \n",
       "EXLAND                  0.0                0.0  \n",
       "EXTOT                   0.0                0.0  \n",
       "EXCD1                   NaN                NaN  \n",
       "STADDR       1 WATER STREET     1 WATER STREET  \n",
       "ZIP                 10004.0            10004.0  \n",
       "EXMPTCL                 NaN                NaN  \n",
       "BLDFRONT                  0                  0  \n",
       "BLDDEPTH                  0                  0  \n",
       "AVLAND2            919276.0           967500.0  \n",
       "AVTOT2            2961617.0          5483912.0  \n",
       "EXLAND2                 NaN                NaN  \n",
       "EXTOT2                  NaN                NaN  \n",
       "EXCD2                   NaN                NaN  \n",
       "PERIOD                FINAL              FINAL  \n",
       "YEAR                2010/11            2010/11  \n",
       "VALTYPE               AC-TR              AC-TR  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "property_data.head().transpose()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d016986f",
   "metadata": {},
   "source": [
    "### Fill in Missing LOT and Building Sizes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "a3dda438",
   "metadata": {},
   "outputs": [],
   "source": [
    "# since these 4 values do not have NAs, we need to replace 0s\n",
    "# calculate groupwise average. First replace the 0's and 1's by NAs so they are not counted in calculating mean\n",
    "#also treat the sizes of 1s as missing\n",
    "property_data.loc[property_data['LTFRONT']==0,'LTFRONT']=np.nan\n",
    "property_data.loc[property_data['LTDEPTH']==0,'LTDEPTH']=np.nan\n",
    "property_data.loc[property_data['BLDFRONT']==0,'BLDFRONT']=np.nan\n",
    "property_data.loc[property_data['BLDDEPTH']==0,'BLDDEPTH']=np.nan\n",
    "property_data.loc[property_data['LTFRONT']==1,'LTFRONT']=np.nan\n",
    "property_data.loc[property_data['LTDEPTH']==1,'LTDEPTH']=np.nan\n",
    "property_data.loc[property_data['BLDFRONT']==1,'BLDFRONT']=np.nan\n",
    "property_data.loc[property_data['BLDDEPTH']==1,'BLDDEPTH']=np.nan"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "e411b544",
   "metadata": {},
   "outputs": [],
   "source": [
    "# calculate the mean (mean function ignores NAs but not 0s hence we converted 0 to NA)\n",
    "mean_LTFRONT = property_data.groupby(property_data['TAXCLASS'])['LTFRONT'].mean()\n",
    "mean_LTDEPTH = property_data.groupby(property_data['TAXCLASS'])['LTDEPTH'].mean()\n",
    "mean_BLDFRONT = property_data.groupby(property_data['TAXCLASS'])['BLDFRONT'].mean()\n",
    "mean_BLDDEPTH = property_data.groupby(property_data['TAXCLASS'])['BLDDEPTH'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "c28d9d42",
   "metadata": {},
   "outputs": [],
   "source": [
    "# impute values\n",
    "for index in mean_LTFRONT.index:\n",
    "    property_data.loc[(property_data['LTFRONT'].isnull())&(property_data['TAXCLASS']==index),'LTFRONT']=mean_LTFRONT[index]\n",
    "    property_data.loc[(property_data['LTDEPTH'].isnull())&(property_data['TAXCLASS']==index),'LTDEPTH']=mean_LTDEPTH[index]\n",
    "    property_data.loc[(property_data['BLDFRONT'].isnull())&(property_data['TAXCLASS']==index),'BLDFRONT']=mean_BLDFRONT[index]\n",
    "    property_data.loc[(property_data['BLDDEPTH'].isnull())&(property_data['TAXCLASS']==index),'BLDDEPTH']=mean_BLDDEPTH[index]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a2510957",
   "metadata": {},
   "source": [
    "## Feature Engineering: Add New Variables\n",
    "S1 = LTFRONT * LTDEPTH\n",
    "S2 = BLDFRONT * BLDDEPTH\n",
    "S3 = S2 * STORIES"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "9841796c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# convert ZIP from float to integers\n",
    "# convert ZIP from float to integers\n",
    "property_data['ZIP']  = property_data['ZIP'].astype(str)\n",
    "property_data['zip3'] = property_data['ZIP'].str[:3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "d278e22b",
   "metadata": {},
   "outputs": [],
   "source": [
    "property_data['ltsize'] = property_data['LTFRONT'] * property_data['LTDEPTH']\n",
    "property_data['bldsize'] = property_data['BLDFRONT'] * property_data['BLDDEPTH']\n",
    "property_data['bldvol'] = property_data['bldsize'] * property_data['STORIES']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "59c6f7fe",
   "metadata": {},
   "outputs": [],
   "source": [
    "# make variables that are typical measures of how normal a property is\n",
    "property_data['r1'] = property_data['FULLVAL'] / property_data['ltsize']\n",
    "property_data['r2'] = property_data['FULLVAL'] / property_data['bldsize']\n",
    "property_data['r3'] = property_data['FULLVAL'] / property_data['bldvol']\n",
    "property_data['r4'] = property_data['AVLAND'] / property_data['ltsize']\n",
    "property_data['r5'] = property_data['AVLAND'] / property_data['bldsize']\n",
    "property_data['r6'] = property_data['AVLAND'] / property_data['bldvol']\n",
    "property_data['r7'] = property_data['AVTOT'] / property_data['ltsize']\n",
    "property_data['r8'] = property_data['AVTOT'] / property_data['bldsize']\n",
    "property_data['r9'] = property_data['AVTOT'] / property_data['bldvol']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "cb28f4dd",
   "metadata": {},
   "outputs": [],
   "source": [
    "ninevars = ['r1','r2','r3','r4','r5','r6','r7','r8','r9']\n",
    "zip5_mean = property_data.groupby('ZIP')[ninevars].mean()\n",
    "zip3_mean = property_data.groupby('zip3')[ninevars].mean()\n",
    "taxclass_mean = property_data.groupby('TAXCLASS')[ninevars].mean()\n",
    "borough_mean = property_data.groupby('B')[ninevars].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "288e44d2",
   "metadata": {},
   "outputs": [],
   "source": [
    "property_data = property_data.join(zip5_mean, on='ZIP', rsuffix='_zip5')\n",
    "property_data = property_data.join(zip3_mean, on='zip3', rsuffix='_zip3')\n",
    "property_data = property_data.join(taxclass_mean, on='TAXCLASS', rsuffix='_taxclass')\n",
    "property_data = property_data.join(borough_mean, on='B', rsuffix='_boro')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "d74a2e2b",
   "metadata": {},
   "outputs": [],
   "source": [
    "rsuffix = ['_zip5','_zip3','_taxclass','_boro']\n",
    "for var in ninevars:\n",
    "    for r in rsuffix:\n",
    "        property_data[str(var)+r] = property_data[var] / property_data[str(var)+r]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "78814108",
   "metadata": {},
   "outputs": [],
   "source": [
    "property_data.to_pickle(\"property_preprocessed.pickle\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "472870be",
   "metadata": {},
   "outputs": [],
   "source": [
    "cols = ['r1', 'r2', 'r3', 'r4', 'r5', 'r6', 'r7', 'r8', 'r9',\n",
    "       'r1_zip5', 'r2_zip5', 'r3_zip5', 'r4_zip5', 'r5_zip5', 'r6_zip5',\n",
    "       'r7_zip5', 'r8_zip5', 'r9_zip5', 'r1_zip3', 'r2_zip3', 'r3_zip3',\n",
    "       'r4_zip3', 'r5_zip3', 'r6_zip3', 'r7_zip3', 'r8_zip3', 'r9_zip3',\n",
    "       'r1_taxclass', 'r2_taxclass', 'r3_taxclass', 'r4_taxclass',\n",
    "       'r5_taxclass', 'r6_taxclass', 'r7_taxclass', 'r8_taxclass',\n",
    "       'r9_taxclass', 'r1_boro', 'r2_boro', 'r3_boro', 'r4_boro', 'r5_boro',\n",
    "       'r6_boro', 'r7_boro', 'r8_boro', 'r9_boro']\n",
    "\n",
    "{}\n",
    "property_data[cols].describe().round(2).transpose().to_csv('stats_on_vars.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "bf32581b",
   "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",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>r1</th>\n",
       "      <td>1046826.0</td>\n",
       "      <td>213.57</td>\n",
       "      <td>532.56</td>\n",
       "      <td>0.0</td>\n",
       "      <td>76.26</td>\n",
       "      <td>152.86</td>\n",
       "      <td>243.18</td>\n",
       "      <td>138637.34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r2</th>\n",
       "      <td>1046826.0</td>\n",
       "      <td>536.42</td>\n",
       "      <td>1027.44</td>\n",
       "      <td>0.0</td>\n",
       "      <td>211.82</td>\n",
       "      <td>500.00</td>\n",
       "      <td>683.82</td>\n",
       "      <td>310551.57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r3</th>\n",
       "      <td>1046826.0</td>\n",
       "      <td>244.08</td>\n",
       "      <td>498.82</td>\n",
       "      <td>0.0</td>\n",
       "      <td>82.50</td>\n",
       "      <td>239.06</td>\n",
       "      <td>336.23</td>\n",
       "      <td>227500.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r4</th>\n",
       "      <td>1046826.0</td>\n",
       "      <td>10.46</td>\n",
       "      <td>69.98</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.35</td>\n",
       "      <td>4.60</td>\n",
       "      <td>7.23</td>\n",
       "      <td>22249.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r5</th>\n",
       "      <td>1046826.0</td>\n",
       "      <td>22.82</td>\n",
       "      <td>378.51</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.20</td>\n",
       "      <td>14.98</td>\n",
       "      <td>20.29</td>\n",
       "      <td>334636.37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r6</th>\n",
       "      <td>1046826.0</td>\n",
       "      <td>10.04</td>\n",
       "      <td>178.33</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.02</td>\n",
       "      <td>7.15</td>\n",
       "      <td>10.04</td>\n",
       "      <td>111545.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r7</th>\n",
       "      <td>1046826.0</td>\n",
       "      <td>26.83</td>\n",
       "      <td>203.87</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.28</td>\n",
       "      <td>8.58</td>\n",
       "      <td>13.53</td>\n",
       "      <td>64711.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r8</th>\n",
       "      <td>1046826.0</td>\n",
       "      <td>50.46</td>\n",
       "      <td>932.20</td>\n",
       "      <td>0.0</td>\n",
       "      <td>17.47</td>\n",
       "      <td>26.98</td>\n",
       "      <td>36.00</td>\n",
       "      <td>871362.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r9</th>\n",
       "      <td>1046826.0</td>\n",
       "      <td>19.07</td>\n",
       "      <td>327.02</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.09</td>\n",
       "      <td>12.68</td>\n",
       "      <td>17.58</td>\n",
       "      <td>290454.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r1_zip5</th>\n",
       "      <td>1046826.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>2.16</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.46</td>\n",
       "      <td>0.92</td>\n",
       "      <td>1.23</td>\n",
       "      <td>747.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r2_zip5</th>\n",
       "      <td>1046826.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>2.13</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.44</td>\n",
       "      <td>0.96</td>\n",
       "      <td>1.26</td>\n",
       "      <td>658.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r3_zip5</th>\n",
       "      <td>1046826.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>2.73</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.39</td>\n",
       "      <td>0.93</td>\n",
       "      <td>1.24</td>\n",
       "      <td>956.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r4_zip5</th>\n",
       "      <td>1046826.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>5.13</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.28</td>\n",
       "      <td>0.75</td>\n",
       "      <td>1.10</td>\n",
       "      <td>2456.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r5_zip5</th>\n",
       "      <td>1046826.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>8.17</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.34</td>\n",
       "      <td>0.80</td>\n",
       "      <td>1.08</td>\n",
       "      <td>5004.51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r6_zip5</th>\n",
       "      <td>1046826.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>10.01</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.25</td>\n",
       "      <td>0.73</td>\n",
       "      <td>1.02</td>\n",
       "      <td>4200.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r7_zip5</th>\n",
       "      <td>1046826.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>5.81</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.29</td>\n",
       "      <td>0.63</td>\n",
       "      <td>0.95</td>\n",
       "      <td>3237.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r8_zip5</th>\n",
       "      <td>1046826.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>7.37</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.44</td>\n",
       "      <td>0.74</td>\n",
       "      <td>1.00</td>\n",
       "      <td>5527.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r9_zip5</th>\n",
       "      <td>1046826.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>7.91</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.40</td>\n",
       "      <td>0.71</td>\n",
       "      <td>0.96</td>\n",
       "      <td>4864.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r1_zip3</th>\n",
       "      <td>1046826.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>2.40</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.43</td>\n",
       "      <td>0.86</td>\n",
       "      <td>1.23</td>\n",
       "      <td>727.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r2_zip3</th>\n",
       "      <td>1046826.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>2.12</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.40</td>\n",
       "      <td>0.95</td>\n",
       "      <td>1.27</td>\n",
       "      <td>756.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r3_zip3</th>\n",
       "      <td>1046826.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>2.65</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.35</td>\n",
       "      <td>0.93</td>\n",
       "      <td>1.27</td>\n",
       "      <td>912.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r4_zip3</th>\n",
       "      <td>1046826.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>6.39</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.28</td>\n",
       "      <td>0.69</td>\n",
       "      <td>1.06</td>\n",
       "      <td>2051.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r5_zip3</th>\n",
       "      <td>1046826.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>17.12</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.30</td>\n",
       "      <td>0.77</td>\n",
       "      <td>1.05</td>\n",
       "      <td>15414.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r6_zip3</th>\n",
       "      <td>1046826.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>16.14</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.22</td>\n",
       "      <td>0.70</td>\n",
       "      <td>1.00</td>\n",
       "      <td>9790.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r7_zip3</th>\n",
       "      <td>1046826.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>7.31</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.26</td>\n",
       "      <td>0.54</td>\n",
       "      <td>0.83</td>\n",
       "      <td>2407.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r8_zip3</th>\n",
       "      <td>1046826.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>22.53</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.42</td>\n",
       "      <td>0.70</td>\n",
       "      <td>0.93</td>\n",
       "      <td>21881.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r9_zip3</th>\n",
       "      <td>1046826.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>16.31</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.39</td>\n",
       "      <td>0.68</td>\n",
       "      <td>0.93</td>\n",
       "      <td>14530.83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r1_taxclass</th>\n",
       "      <td>1046826.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>2.93</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.37</td>\n",
       "      <td>0.68</td>\n",
       "      <td>1.06</td>\n",
       "      <td>1119.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r2_taxclass</th>\n",
       "      <td>1046826.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>3.74</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.49</td>\n",
       "      <td>0.78</td>\n",
       "      <td>1.06</td>\n",
       "      <td>1525.68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r3_taxclass</th>\n",
       "      <td>1046826.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>3.48</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.49</td>\n",
       "      <td>0.81</td>\n",
       "      <td>1.11</td>\n",
       "      <td>923.91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r4_taxclass</th>\n",
       "      <td>1046826.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>3.32</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.32</td>\n",
       "      <td>0.71</td>\n",
       "      <td>1.10</td>\n",
       "      <td>1438.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r5_taxclass</th>\n",
       "      <td>1046826.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>6.44</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.33</td>\n",
       "      <td>0.82</td>\n",
       "      <td>1.12</td>\n",
       "      <td>4260.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r6_taxclass</th>\n",
       "      <td>1046826.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>6.31</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.30</td>\n",
       "      <td>0.82</td>\n",
       "      <td>1.14</td>\n",
       "      <td>3169.93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r7_taxclass</th>\n",
       "      <td>1046826.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>2.95</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.38</td>\n",
       "      <td>0.73</td>\n",
       "      <td>1.12</td>\n",
       "      <td>1286.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r8_taxclass</th>\n",
       "      <td>1046826.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>6.13</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.45</td>\n",
       "      <td>0.84</td>\n",
       "      <td>1.12</td>\n",
       "      <td>4680.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r9_taxclass</th>\n",
       "      <td>1046826.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>5.63</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.45</td>\n",
       "      <td>0.84</td>\n",
       "      <td>1.15</td>\n",
       "      <td>4204.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r1_boro</th>\n",
       "      <td>1046826.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>2.37</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.42</td>\n",
       "      <td>0.84</td>\n",
       "      <td>1.24</td>\n",
       "      <td>727.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r2_boro</th>\n",
       "      <td>1046826.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.40</td>\n",
       "      <td>0.95</td>\n",
       "      <td>1.27</td>\n",
       "      <td>756.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r3_boro</th>\n",
       "      <td>1046826.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>2.54</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.35</td>\n",
       "      <td>0.92</td>\n",
       "      <td>1.28</td>\n",
       "      <td>932.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r4_boro</th>\n",
       "      <td>1046826.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>6.31</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.27</td>\n",
       "      <td>0.67</td>\n",
       "      <td>1.06</td>\n",
       "      <td>2054.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r5_boro</th>\n",
       "      <td>1046826.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>17.15</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.30</td>\n",
       "      <td>0.77</td>\n",
       "      <td>1.05</td>\n",
       "      <td>15466.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r6_boro</th>\n",
       "      <td>1046826.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>16.04</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.22</td>\n",
       "      <td>0.70</td>\n",
       "      <td>1.00</td>\n",
       "      <td>9483.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r7_boro</th>\n",
       "      <td>1046826.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>7.28</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.26</td>\n",
       "      <td>0.51</td>\n",
       "      <td>0.81</td>\n",
       "      <td>2407.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r8_boro</th>\n",
       "      <td>1046826.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>21.93</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.41</td>\n",
       "      <td>0.70</td>\n",
       "      <td>0.93</td>\n",
       "      <td>21241.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r9_boro</th>\n",
       "      <td>1046826.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>15.73</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.39</td>\n",
       "      <td>0.67</td>\n",
       "      <td>0.94</td>\n",
       "      <td>13819.33</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 count    mean      std  min     25%     50%     75%  \\\n",
       "r1           1046826.0  213.57   532.56  0.0   76.26  152.86  243.18   \n",
       "r2           1046826.0  536.42  1027.44  0.0  211.82  500.00  683.82   \n",
       "r3           1046826.0  244.08   498.82  0.0   82.50  239.06  336.23   \n",
       "r4           1046826.0   10.46    69.98  0.0    2.35    4.60    7.23   \n",
       "r5           1046826.0   22.82   378.51  0.0    6.20   14.98   20.29   \n",
       "r6           1046826.0   10.04   178.33  0.0    2.02    7.15   10.04   \n",
       "r7           1046826.0   26.83   203.87  0.0    5.28    8.58   13.53   \n",
       "r8           1046826.0   50.46   932.20  0.0   17.47   26.98   36.00   \n",
       "r9           1046826.0   19.07   327.02  0.0    7.09   12.68   17.58   \n",
       "r1_zip5      1046826.0    1.00     2.16  0.0    0.46    0.92    1.23   \n",
       "r2_zip5      1046826.0    1.00     2.13  0.0    0.44    0.96    1.26   \n",
       "r3_zip5      1046826.0    1.00     2.73  0.0    0.39    0.93    1.24   \n",
       "r4_zip5      1046826.0    1.00     5.13  0.0    0.28    0.75    1.10   \n",
       "r5_zip5      1046826.0    1.00     8.17  0.0    0.34    0.80    1.08   \n",
       "r6_zip5      1046826.0    1.00    10.01  0.0    0.25    0.73    1.02   \n",
       "r7_zip5      1046826.0    1.00     5.81  0.0    0.29    0.63    0.95   \n",
       "r8_zip5      1046826.0    1.00     7.37  0.0    0.44    0.74    1.00   \n",
       "r9_zip5      1046826.0    1.00     7.91  0.0    0.40    0.71    0.96   \n",
       "r1_zip3      1046826.0    1.00     2.40  0.0    0.43    0.86    1.23   \n",
       "r2_zip3      1046826.0    1.00     2.12  0.0    0.40    0.95    1.27   \n",
       "r3_zip3      1046826.0    1.00     2.65  0.0    0.35    0.93    1.27   \n",
       "r4_zip3      1046826.0    1.00     6.39  0.0    0.28    0.69    1.06   \n",
       "r5_zip3      1046826.0    1.00    17.12  0.0    0.30    0.77    1.05   \n",
       "r6_zip3      1046826.0    1.00    16.14  0.0    0.22    0.70    1.00   \n",
       "r7_zip3      1046826.0    1.00     7.31  0.0    0.26    0.54    0.83   \n",
       "r8_zip3      1046826.0    1.00    22.53  0.0    0.42    0.70    0.93   \n",
       "r9_zip3      1046826.0    1.00    16.31  0.0    0.39    0.68    0.93   \n",
       "r1_taxclass  1046826.0    1.00     2.93  0.0    0.37    0.68    1.06   \n",
       "r2_taxclass  1046826.0    1.00     3.74  0.0    0.49    0.78    1.06   \n",
       "r3_taxclass  1046826.0    1.00     3.48  0.0    0.49    0.81    1.11   \n",
       "r4_taxclass  1046826.0    1.00     3.32  0.0    0.32    0.71    1.10   \n",
       "r5_taxclass  1046826.0    1.00     6.44  0.0    0.33    0.82    1.12   \n",
       "r6_taxclass  1046826.0    1.00     6.31  0.0    0.30    0.82    1.14   \n",
       "r7_taxclass  1046826.0    1.00     2.95  0.0    0.38    0.73    1.12   \n",
       "r8_taxclass  1046826.0    1.00     6.13  0.0    0.45    0.84    1.12   \n",
       "r9_taxclass  1046826.0    1.00     5.63  0.0    0.45    0.84    1.15   \n",
       "r1_boro      1046826.0    1.00     2.37  0.0    0.42    0.84    1.24   \n",
       "r2_boro      1046826.0    1.00     2.00  0.0    0.40    0.95    1.27   \n",
       "r3_boro      1046826.0    1.00     2.54  0.0    0.35    0.92    1.28   \n",
       "r4_boro      1046826.0    1.00     6.31  0.0    0.27    0.67    1.06   \n",
       "r5_boro      1046826.0    1.00    17.15  0.0    0.30    0.77    1.05   \n",
       "r6_boro      1046826.0    1.00    16.04  0.0    0.22    0.70    1.00   \n",
       "r7_boro      1046826.0    1.00     7.28  0.0    0.26    0.51    0.81   \n",
       "r8_boro      1046826.0    1.00    21.93  0.0    0.41    0.70    0.93   \n",
       "r9_boro      1046826.0    1.00    15.73  0.0    0.39    0.67    0.94   \n",
       "\n",
       "                   max  \n",
       "r1           138637.34  \n",
       "r2           310551.57  \n",
       "r3           227500.00  \n",
       "r4            22249.80  \n",
       "r5           334636.37  \n",
       "r6           111545.46  \n",
       "r7            64711.85  \n",
       "r8           871362.22  \n",
       "r9           290454.07  \n",
       "r1_zip5         747.76  \n",
       "r2_zip5         658.74  \n",
       "r3_zip5         956.75  \n",
       "r4_zip5        2456.16  \n",
       "r5_zip5        5004.51  \n",
       "r6_zip5        4200.70  \n",
       "r7_zip5        3237.54  \n",
       "r8_zip5        5527.75  \n",
       "r9_zip5        4864.74  \n",
       "r1_zip3         727.14  \n",
       "r2_zip3         756.25  \n",
       "r3_zip3         912.30  \n",
       "r4_zip3        2051.04  \n",
       "r5_zip3       15414.36  \n",
       "r6_zip3        9790.65  \n",
       "r7_zip3        2407.60  \n",
       "r8_zip3       21881.14  \n",
       "r9_zip3       14530.83  \n",
       "r1_taxclass    1119.59  \n",
       "r2_taxclass    1525.68  \n",
       "r3_taxclass     923.91  \n",
       "r4_taxclass    1438.08  \n",
       "r5_taxclass    4260.03  \n",
       "r6_taxclass    3169.93  \n",
       "r7_taxclass    1286.40  \n",
       "r8_taxclass    4680.65  \n",
       "r9_taxclass    4204.08  \n",
       "r1_boro         727.14  \n",
       "r2_boro         756.58  \n",
       "r3_boro         932.87  \n",
       "r4_boro        2054.62  \n",
       "r5_boro       15466.54  \n",
       "r6_boro        9483.22  \n",
       "r7_boro        2407.60  \n",
       "r8_boro       21241.27  \n",
       "r9_boro       13819.33  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "property_data[cols].describe().round(2).transpose()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f80b5afe",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}