Data_Cleaning_and_Preparation.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Establishing the Contex"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Before we begin to clean the data, it is important we become more familiar with the contex of the data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Baltimore City in 1937 "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- All of the accessible forms for Baltimore are dated May 4, 1937.\n",
    "- According to the [1940's Census](https://www.census.gov/prod/www/decennial.html.png\" width), the population of Baltimore City was 859,100.\n",
    "    - 73.5% Native-born white\n",
    "    - 19.3% African American\n",
    "    - 7.1% Foreign-born white \n",
    "- Different types of houses in Baltimore City 1930s-1940s\n",
    "    - Row homes\n",
    "    - Single family attached/detached (Frame houses)\n",
    "<img src= \"rowhomes.png\" alt=\"row_homes\" title=\"Baltimore Row Homes\" align = \"right\" width=600>\n",
    "<img src= \"framehouse.png\" alt=\"frame_house\" title=\"Baltimore Frame House\" align = \"left\" width=600>\n",
    "- The 1940's census was the first census to begin collecting housing information.\n",
    "- The local goverment at this time was becoming very interested in real estate, urban development, and \"slum removal\".\n",
    "- The Housing Authority of Baltimore City (HABC) was established in December 13, 1937 to provide federally-funded public housing programs to Baltmore's low-income residents. However, this housing was initially largely segregated due to racial tensions."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Area Descriptions\n",
    "- Area Descriptions are documents that provide detailed descriptions of each colored section on the security grade map\n",
    "- They are organized by their `Grade` and their `Area Number`\n",
    "- They do not explain how field agents were to integrate all the different characteristics into a single grade \n",
    "- Template of this form may vary depending on city\n",
    "- Some forms contain a lot of missing values \n",
    "- Historians debate on the purpose of area descriptions and whether they were created during the lending process and why they would be created after the HOLC stopped lending \n",
    "<img src=\"Baltimore_AD_002.png\" width=600 align=right>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Excercise 1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1. Visit the [Mapping Inequality site](https://dsl.richmond.edu/panorama/redlining/#loc=11/39.2930/-76.6330&opacity=0.8&city=baltimore-md) and explore the Baltimore residential security map\n",
    "2. What date was the map prepared? Who prepared it?\n",
    "3. What areas are industrial?\n",
    "4. What areas are underdeveloped? What color are they shaded?\n",
    "5. What fields are curated? What fields are not curated?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. Importing the Data and Creating a Data Frame"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The fields of each record are characteristics of a graded section indicated by the security grade map seen above. These fields have been transcribed and stored in a __comma separated value (csv)__ format.\n",
    "\n",
    "Using the pandas library, we can create a data frame from the csv. A __data frame__ is a table where every row represents a single unique object, called an __entity__, and every column represents characteristics of these objects, called __attributes__."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "ename": "SyntaxError",
     "evalue": "invalid syntax (2886008940.py, line 7)",
     "output_type": "error",
     "traceback": [
      "\u001b[0;36m  Cell \u001b[0;32mIn [2], line 7\u001b[0;36m\u001b[0m\n\u001b[0;31m    df = pd.\u001b[0m\n\u001b[0m               ^\u001b[0m\n\u001b[0;31mSyntaxError\u001b[0m\u001b[0;31m:\u001b[0m invalid syntax\n"
     ]
    }
   ],
   "source": [
    "# loads the pandas library \n",
    "import pandas as pd\n",
    "import warnings\n",
    "warnings.simplefilter(action='ignore', category=FutureWarning)  # Ignore Pandas future warnings\n",
    "\n",
    "# creates data frame named df by reading in the Baltimore csv\n",
    "df = pd.read_csv(\"AD_Data_BaltimoreProject.csv\")\n",
    "df.head(n=3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that we have 26 attributes. We can all of the atrributes and their data types calling `dtypes` on our dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Form                                object\n",
       "State                               object\n",
       "City                                object\n",
       "Security_Grade                      object\n",
       "Area_Number                          int64\n",
       "Terrain_Description                 object\n",
       "Favorable_Influences                object\n",
       "Detrimental_Influences              object\n",
       "INHABITANTS_Type                    object\n",
       "INHABITANTS_Annual_Income           object\n",
       "INHABITANTS_Foreignborn             object\n",
       "INHABITANTS_F                      float64\n",
       "INHABITANTS_Negro                   object\n",
       "INHABITANTS_N                       object\n",
       "INHABITANTS_Infiltration            object\n",
       "INHABITANTS_Relief_Families         object\n",
       "INHABITANTS_Population_Increase     object\n",
       "INHABITANTS_Population_Decrease     object\n",
       "INHABITANTS_Population_Static       object\n",
       "BUILDINGS_Types                     object\n",
       "BUILDINGS_Construction              object\n",
       "BUILDINGS_Age                       object\n",
       "BUILDINGS_Repair                    object\n",
       "Ten_Fifteen_Desirability            object\n",
       "Remarks                             object\n",
       "Date                                object\n",
       "dtype: object"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The data type `object` indicates that the value is a string in Python. Since we are not going to use the `Form` column, we are going to drop it. Notice that on the form `INHABITANTS_F` is a percent, so we want to convert it to a decimal value to be easier to work with later. What other attribute is a percentage?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "# drops a single column\n",
    "df.drop(['Form'], axis=1)\n",
    "\n",
    "# iterates through this attribute and replaces the current value with decimal form\n",
    "for value in df['INHABITANTS_F']:\n",
    "    if value != 'NaN':\n",
    "        df['INHABITANTS_F'] = df['INHABITANTS_F'].replace(value, value/100)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. Handling Text Values with Similar Meanings "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After obtaining our data, we need to clean it to prepare for data analysis. This includes handling similiar text values and making sure a column of interest contains a single attribute."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `INHABITANTS_Foreignborn` varaible indicates whether there are foreigners in a neighborhood section. Observe that different forms used 'No' or 'None' to indicate there are no foreigners and 'Small' , 'Very few' , and 'Mixture' are various ways of indicating there were foreigners. `NaN` indicates the value in the form is missing."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0           No\n",
       "1         None\n",
       "2         None\n",
       "3         None\n",
       "4         None\n",
       "5          NaN\n",
       "6           No\n",
       "7           No\n",
       "8           No\n",
       "9        Small\n",
       "10    Very few\n",
       "11          No\n",
       "12          No\n",
       "13          No\n",
       "14          No\n",
       "15     Mixture\n",
       "Name: INHABITANTS_Foreignborn, dtype: object"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# selects rows with indices between 0 to 15 included of the 'INHABITANTS_Foreignborn' column\n",
    "df.loc[0:15,'INHABITANTS_Foreignborn']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since columns with categorized values are easier to analyze, we are going to transform this column to indicate whether there are foreigners or not. In Python, `None` is a special keyword that indicates the cell has a null value. Therefore, we will alter all the 'None' values to 'No' and every other value that indicates there are to 'Yes'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0      No\n",
       "1      No\n",
       "2      No\n",
       "3      No\n",
       "4      No\n",
       "5     Yes\n",
       "6      No\n",
       "7      No\n",
       "8      No\n",
       "9     Yes\n",
       "10    Yes\n",
       "11     No\n",
       "12     No\n",
       "13     No\n",
       "14     No\n",
       "15    Yes\n",
       "Name: INHABITANTS_Foreignborn, dtype: object"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# replaces the values of 'None' with 'No'\n",
    "df['INHABITANTS_Foreignborn'] = df['INHABITANTS_Foreignborn'].replace('None', 'No')\n",
    "\n",
    "# replaces all other values with 'Yes'\n",
    "for value in df['INHABITANTS_Foreignborn']:\n",
    "    if value != 'No' and value != 'NaN':\n",
    "        df['INHABITANTS_Foreignborn'] = df['INHABITANTS_Foreignborn'].replace(value, 'Yes')\n",
    "\n",
    "df.loc[0:15,'INHABITANTS_Foreignborn']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`Detrimental_Influences`  are descriptions of a region's undesiriable characteristics. Notice in this case that there are multiple variations of the meaning of 'No' to be replaced. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0                                          None\n",
       "1                                          None\n",
       "2                              Distance to City\n",
       "3                                          None\n",
       "4                                          None\n",
       "5                                           NaN\n",
       "6                                           No.\n",
       "7                                          None\n",
       "8     Few streets of property in poor condition\n",
       "9                                          None\n",
       "10                                         None\n",
       "11                      Built on filled ground.\n",
       "12                                         None\n",
       "13                                         None\n",
       "14                   Distance to center of city\n",
       "15                                         None\n",
       "Name: Detrimental_Influences, dtype: object"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[0:15,'Detrimental_Influences']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To handle the multiple words to be replaced we can use an __array__, a list that holds a fixed number of values of the same type, to store several strings. Then we can replace each occurence in `Detrimental_Influences` by iterating through the array and using the `replace()` function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# declares an array called 'no_words' containing strings that all mean 'No'\n",
    "no_words = ['No.','no', 'none', 'None']\n",
    "\n",
    "# interates through no_words and replaces each occurence in Detrimental_Influences\n",
    "for word in no_words:\n",
    "    df['Detrimental_Influences'] = df['Detrimental_Influences'].replace( word , 'No')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0                                            No\n",
       "1                                            No\n",
       "2                              Distance to City\n",
       "3                                            No\n",
       "4                                            No\n",
       "5                                           NaN\n",
       "6                                            No\n",
       "7                                            No\n",
       "8     Few streets of property in poor condition\n",
       "9                                            No\n",
       "10                                           No\n",
       "11                      Built on filled ground.\n",
       "12                                           No\n",
       "13                                           No\n",
       "14                   Distance to center of city\n",
       "15                                           No\n",
       "Name: Detrimental_Influences, dtype: object"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[0:15,'Detrimental_Influences']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We need to do these same operations for `INHABITANTS_Negro`, whether residents are African American, and `INHABITANTS_Infiltration`, which refers to if the area is being infiltrated. __Functions__ are procedures that perform specific tasks. In Python, we can create our own functions or used built-in functions such as `replace()`. __Parameters__ are variables that are defined in the function definition. __Arguments__ are the values passed into a function.\n",
    "\n",
    "The `replace_values` function has three parmeters : `attribute`, `new_word`, and `words_to_replace`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# this function iterates through an array of words to be replaced and replaces each occurence in the attribute \n",
    "# with the new word\n",
    "def replace_values(attribute, new_word, words_to_replace):\n",
    "    for word in words_to_replace:\n",
    "        df[attribute] = df[attribute].replace(word, new_word) \n",
    "\n",
    "\n",
    "replace_values('INHABITANTS_Negro', 'No', no_words)\n",
    "replace_values('INHABITANTS_Infiltration', 'No', no_words)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0                  No\n",
       "1                  No\n",
       "2                  No\n",
       "3                  No\n",
       "4                  No\n",
       "5                 NaN\n",
       "6                  No\n",
       "7                  No\n",
       "8                  No\n",
       "9                 NaN\n",
       "10                 No\n",
       "11                 No\n",
       "12                 No\n",
       "13                 No\n",
       "14                 No\n",
       "15    People from C-1\n",
       "Name: INHABITANTS_Infiltration, dtype: object"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[0:15,'INHABITANTS_Infiltration']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that we do the same procedure for words meaning 'small', however we could have made these columns have two categories, 'yes' or 'no', and indicate in our data analysis the alterations to our data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "# Various words meaning small \n",
    "small_words = ['Nominal','nominal', 'Small','small','Minimum', 'minimum','minimal', 'Very few']\n",
    "\n",
    "replace_values('INHABITANTS_Negro', 'Nominal', small_words)\n",
    "replace_values('INHABITANTS_Infiltration', 'Nominal', small_words)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0                  No\n",
       "1                  No\n",
       "2                  No\n",
       "3                  No\n",
       "4                  No\n",
       "5                 NaN\n",
       "6                  No\n",
       "7                  No\n",
       "8                  No\n",
       "9                 NaN\n",
       "10                 No\n",
       "11                 No\n",
       "12                 No\n",
       "13                 No\n",
       "14                 No\n",
       "15    People from C-1\n",
       "Name: INHABITANTS_Infiltration, dtype: object"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[0:15,'INHABITANTS_Infiltration']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0      No\n",
       "1      No\n",
       "2      No\n",
       "3      No\n",
       "4      No\n",
       "5     NaN\n",
       "6      No\n",
       "7      No\n",
       "8      No\n",
       "9      No\n",
       "10     No\n",
       "11     No\n",
       "12     No\n",
       "13     No\n",
       "14     No\n",
       "15     No\n",
       "Name: INHABITANTS_Negro, dtype: object"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[0:15,'INHABITANTS_Negro']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. String Parsing to Extract Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Some of the cities have corresponding suburbs followed by a dash. We can extract the suburbs and create a new column using  `str.split()`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "16                                Baltimore \n",
       "17                       Baltimore - Dundalk\n",
       "18                                 Baltimore\n",
       "19                  Baltimore - Metropolitan\n",
       "20                                 Baltimore\n",
       "21                  Baltimore - Metropolitan\n",
       "22                                 Baltimore\n",
       "23                   Baltimore - Sub. Burton\n",
       "24    Baltimore - Sub. Mt. Washington Summit\n",
       "25                Baltimore - Sub. Villahove\n",
       "26            Baltimore - Sub. Colonial Park\n",
       "27        Baltimore - Sub. Linthicum Heights\n",
       "28                                 Baltimore\n",
       "29                                 Baltimore\n",
       "30                                 Baltimore\n",
       "Name: City, dtype: object"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[16:30,'City']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " A new data frame seperate is created with two columns seperated based on `str.split`, where `n=1` indicates the string is split once. We create two new attributes in the original data frame, `City_clean` and `Suburb`. Then we drop the old `City` city column and rename our new `City` attribute."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# creates a new data frame that splits the values in the orginal data frame on the '-' character \n",
    "newdf = df[\"City\"].str.split('-', n = 1, expand = True) \n",
    "newdf.head()\n",
    "\n",
    "# creates two new columns \n",
    "df['City_clean'] = newdf[0]\n",
    "df['Suburb'] = newdf[1]\n",
    "\n",
    "# removes the old column\n",
    "df.drop([\"City\"], axis = 1, inplace = True) \n",
    "\n",
    "# renames as 'City'\n",
    "df.rename(index=str, columns={\"City_clean\": \"City\"});"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "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>Suburb</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Dundalk</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Metropolitan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Metropolitan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Sub. Burton</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Sub. Mt. Washington Summit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Sub. Villahove</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Sub. Colonial Park</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Sub. Linthicum Heights</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         Suburb\n",
       "16                         None\n",
       "17                      Dundalk\n",
       "18                         None\n",
       "19                 Metropolitan\n",
       "20                         None\n",
       "21                 Metropolitan\n",
       "22                         None\n",
       "23                  Sub. Burton\n",
       "24   Sub. Mt. Washington Summit\n",
       "25               Sub. Villahove\n",
       "26           Sub. Colonial Park\n",
       "27       Sub. Linthicum Heights\n",
       "28                         None\n",
       "29                         None\n",
       "30                         None"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[16:30,['Suburb']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `BUILDINGS_Age` attribute represents the range of the age of the buildings in a region. Notice the range is given is given as a string of text. For purpose of analysis, we would like to extract the upper end of the range and create a column of numeric values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0      1 to 10 years\n",
       "1           12 years\n",
       "2      1 to 20 years\n",
       "3           10 years\n",
       "4      1 to 20 years\n",
       "5                NaN\n",
       "6           25 years\n",
       "7     15 to 25 years\n",
       "8           15 years\n",
       "9      5 to 25 years\n",
       "10          20 years\n",
       "11     5 to 10 years\n",
       "12          10 years\n",
       "13          25 years\n",
       "14     1 to 20 years\n",
       "15     6 to 25 years\n",
       "Name: BUILDINGS_Age, dtype: object"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[0:15,'BUILDINGS_Age']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Observe that the numeric value at the end of the range is always the last number followed by the string `years`. We use specific string patterns called __regular expressions (regex)__ and `str.extract()` to create a new attribute called `max_building_age`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "df['max_building_age'] = df['BUILDINGS_Age'].str.extract('(\\d+)(?!.*\\d)', expand=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The regular expression in this example is `(\\d+)(?!.*\\d)` and was tested using [Pythex](https://pythex.org/), a regex editor. We could have imported `re` , Python's regular expression library, to perform the same task, however this notebook consistenly uses `str()` functions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "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>BUILDINGS_Age</th>\n",
       "      <th>max_building_age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1 to 10 years</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12 years</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1 to 20 years</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10 years</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1 to 20 years</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>25 years</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>15 to 25 years</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>15 years</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>5 to 25 years</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>20 years</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>5 to 10 years</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>10 years</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>25 years</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>1 to 20 years</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>6 to 25 years</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     BUILDINGS_Age max_building_age\n",
       "0    1 to 10 years               10\n",
       "1         12 years               12\n",
       "2    1 to 20 years               20\n",
       "3         10 years               10\n",
       "4    1 to 20 years               20\n",
       "5              NaN              NaN\n",
       "6         25 years               25\n",
       "7   15 to 25 years               25\n",
       "8         15 years               15\n",
       "9    5 to 25 years               25\n",
       "10        20 years               20\n",
       "11   5 to 10 years               10\n",
       "12        10 years               10\n",
       "13        25 years               25\n",
       "14   1 to 20 years               20\n",
       "15   6 to 25 years               25"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[0:15,['BUILDINGS_Age','max_building_age']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `Date`, the date indicated on the form, has three attributes with the pattern given by **month day, year**. As previously stated, we would like to have a single attribute per column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "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>Date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>May 4,1937</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>May 4,1937</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>May 4,1937</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>May 4,1937</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date\n",
       "0  May 4,1937\n",
       "1  May 4,1937\n",
       "2  May 4,1937\n",
       "3  May 4,1937"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[0:3,['Date']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can seperate this column into three columns:  `Month`, `Day`, and `Year` using `str.extract()` and regex patterns\n",
    "`(\\d\\d\\d\\d)` to indicate four digit year, `(\\d)` to indicate a single digit day, and ` [A-Z]\\w{0,}` to indicate __aplhanumeric__ text to extract the month. Note that regex can also be used to reformat the date column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "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>Date</th>\n",
       "      <th>Month</th>\n",
       "      <th>Day</th>\n",
       "      <th>Year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>May 4,1937</td>\n",
       "      <td>May</td>\n",
       "      <td>4</td>\n",
       "      <td>1937</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>May 4,1937</td>\n",
       "      <td>May</td>\n",
       "      <td>4</td>\n",
       "      <td>1937</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>May 4,1937</td>\n",
       "      <td>May</td>\n",
       "      <td>4</td>\n",
       "      <td>1937</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>May 4,1937</td>\n",
       "      <td>May</td>\n",
       "      <td>4</td>\n",
       "      <td>1937</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date Month Day  Year\n",
       "0  May 4,1937   May   4  1937\n",
       "1  May 4,1937   May   4  1937\n",
       "2  May 4,1937   May   4  1937\n",
       "3  May 4,1937   May   4  1937"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# extracts the year \n",
    "df['Year'] = df['Date'].str.extract('(\\d\\d\\d\\d)', expand=True)\n",
    "\n",
    "# extracts day  \n",
    "df['Day'] = df['Date'].str.extract('(\\d)', expand=True)\n",
    "\n",
    "# extracts month \n",
    "df['Month'] = df['Date'].str.extract('([A-Z]\\w{0,})', expand=True)\n",
    "\n",
    "df.loc[0:3,['Date','Month','Day','Year']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Similar to `BUILDINGS_Age`, `INHABITANTS_Annual_Income`, the range of income of residents in a section, is a string of text and we would like to extract the numeric value at the end of the range."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "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>INHABITANTS_Annual_Income</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>$3000 - 5,000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>over $5000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3500 - 7000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>over $5000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>$3,500 - $10,000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>over $4000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>over $5000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  INHABITANTS_Annual_Income\n",
       "0             $3000 - 5,000\n",
       "1                over $5000\n",
       "2               3500 - 7000\n",
       "3                over $5000\n",
       "4          $3,500 - $10,000\n",
       "5                       NaN\n",
       "6                over $4000\n",
       "7                over $5000"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[0:7,['INHABITANTS_Annual_Income']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Observe that some values are located after '$', some inlcude '-', and others include ','.Since the pattern to extract the last numeric value varies, we use `str.replace()`, `str.extract`, and regex in a sequence of operations to create a new column `max_annual_income`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-34-f14c5f79d783>:2: FutureWarning: The default value of regex will change from True to False in a future version.\n",
      "  df['max_annual_income'] = df['INHABITANTS_Annual_Income'].str.replace('[^\\d(,)]',' ')\n"
     ]
    }
   ],
   "source": [
    "# replaces anything that is not a digit or ',' with empty space\n",
    "df['max_annual_income'] = df['INHABITANTS_Annual_Income'].str.replace('[^\\d(,)]',' ')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "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>max_annual_income</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3000   5,000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3500   7000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3,500    10,000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>4000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>5000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  max_annual_income\n",
       "0      3000   5,000\n",
       "1              5000\n",
       "2       3500   7000\n",
       "3              5000\n",
       "4   3,500    10,000\n",
       "5               NaN\n",
       "6              4000\n",
       "7              5000"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[0:7,['max_annual_income']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We did not include the delimeter in the previous `str.replace()` operation because their positions would have been replaced by an empty space. Now we want to remove delimeters."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-36-3eaf5e901346>:2: FutureWarning: The default value of regex will change from True to False in a future version.\n",
      "  df['max_annual_income'] = df['max_annual_income'].str.replace('[(,)]','')\n"
     ]
    }
   ],
   "source": [
    "# removes delimeter by replacing ',' with ''\n",
    "df['max_annual_income'] = df['max_annual_income'].str.replace('[(,)]','')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "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>max_annual_income</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3000   5000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3500   7000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3500    10000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>4000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>5000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  max_annual_income\n",
       "0       3000   5000\n",
       "1              5000\n",
       "2       3500   7000\n",
       "3              5000\n",
       "4     3500    10000\n",
       "5               NaN\n",
       "6              4000\n",
       "7              5000"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[0:7,['max_annual_income']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, we can extract the last number."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "# extracts the second or last number \n",
    "df['max_annual_income'] = df['max_annual_income'].str.extract('(\\d+)(?!.*\\d)', expand=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "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>max_annual_income</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>4000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>5000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  max_annual_income\n",
       "0              5000\n",
       "1              5000\n",
       "2              7000\n",
       "3              5000\n",
       "4             10000\n",
       "5               NaN\n",
       "6              4000\n",
       "7              5000"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[0:7,['max_annual_income']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercise 2\n",
    "> 1. Can you identify another way to handle No/No./None and it's variations?\n",
    "2. Are there any patterns in the date column?\n",
    "3. Columns that had values such as 'Mixture' and 'Few' were altered to Yes. How might this influence further analysis?\n",
    "4. Extract the lower range of the `BUILDINGS_Age`.\n",
    "4. Would it be better to use `median` income?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### DataFrame to CSV"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We need to convert our dataframe to another csv to use in the next section of this series. The new csv should be available in your local repository once running the line below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv(r'clean_baltimore_data.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "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>Unnamed: 0</th>\n",
       "      <th>Form</th>\n",
       "      <th>State</th>\n",
       "      <th>Security_Grade</th>\n",
       "      <th>Area_Number</th>\n",
       "      <th>Terrain_Description</th>\n",
       "      <th>Favorable_Influences</th>\n",
       "      <th>Detrimental_Influences</th>\n",
       "      <th>INHABITANTS_Type</th>\n",
       "      <th>INHABITANTS_Annual_Income</th>\n",
       "      <th>...</th>\n",
       "      <th>Ten_Fifteen_Desirability</th>\n",
       "      <th>Remarks</th>\n",
       "      <th>Date</th>\n",
       "      <th>City_clean</th>\n",
       "      <th>Suburb</th>\n",
       "      <th>max_building_age</th>\n",
       "      <th>Year</th>\n",
       "      <th>Day</th>\n",
       "      <th>Month</th>\n",
       "      <th>max_annual_income</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>NS FORM-8 6-1-37</td>\n",
       "      <td>Maryland</td>\n",
       "      <td>A</td>\n",
       "      <td>2</td>\n",
       "      <td>Rolling</td>\n",
       "      <td>Fairly new suburban area of homogeneous charac...</td>\n",
       "      <td>No</td>\n",
       "      <td>Substantial Middle Class</td>\n",
       "      <td>$3000 - 5,000</td>\n",
       "      <td>...</td>\n",
       "      <td>Upward</td>\n",
       "      <td>A recent development with much room for expans...</td>\n",
       "      <td>May 4,1937</td>\n",
       "      <td>Baltimore</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1937.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>May</td>\n",
       "      <td>5000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>NS FORM-8 6-1-37</td>\n",
       "      <td>Maryland</td>\n",
       "      <td>A</td>\n",
       "      <td>1</td>\n",
       "      <td>Undulating</td>\n",
       "      <td>Very nicely planned residential area of medium...</td>\n",
       "      <td>No</td>\n",
       "      <td>Executives, Professional Men</td>\n",
       "      <td>over $5000</td>\n",
       "      <td>...</td>\n",
       "      <td>Upward</td>\n",
       "      <td>Mostly fee properties. A few homes valued at $...</td>\n",
       "      <td>May 4,1937</td>\n",
       "      <td>Baltimore</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12.0</td>\n",
       "      <td>1937.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>May</td>\n",
       "      <td>5000.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 33 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0              Form     State Security_Grade  Area_Number  \\\n",
       "0           0  NS FORM-8 6-1-37  Maryland              A            2   \n",
       "1           1  NS FORM-8 6-1-37  Maryland              A            1   \n",
       "\n",
       "  Terrain_Description                               Favorable_Influences  \\\n",
       "0             Rolling  Fairly new suburban area of homogeneous charac...   \n",
       "1         Undulating   Very nicely planned residential area of medium...   \n",
       "\n",
       "  Detrimental_Influences              INHABITANTS_Type  \\\n",
       "0                     No      Substantial Middle Class   \n",
       "1                     No  Executives, Professional Men   \n",
       "\n",
       "  INHABITANTS_Annual_Income  ... Ten_Fifteen_Desirability  \\\n",
       "0             $3000 - 5,000  ...                   Upward   \n",
       "1                over $5000  ...                   Upward   \n",
       "\n",
       "                                             Remarks        Date  City_clean  \\\n",
       "0  A recent development with much room for expans...  May 4,1937  Baltimore    \n",
       "1  Mostly fee properties. A few homes valued at $...  May 4,1937  Baltimore    \n",
       "\n",
       "  Suburb max_building_age    Year  Day Month max_annual_income  \n",
       "0    NaN             10.0  1937.0  4.0   May            5000.0  \n",
       "1    NaN             12.0  1937.0  4.0   May            5000.0  \n",
       "\n",
       "[2 rows x 33 columns]"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "clean_df = pd.read_csv(\"clean_baltimore_data.csv\")\n",
    "clean_df.head(n=2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Next Step\n",
    "\n",
    "Continue to the [Data Manipulation](Data_Manipulation.ipynb) portion of this module."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.12 (main, Nov 20 2023, 15:14:05) [GCC 11.4.0]"
  },
  "vscode": {
   "interpreter": {
    "hash": "916dbcbb3f70747c44a77c7bcd40155683ae19c65e1c03b4aa3499c5328201f1"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}