May 18 - Clean Halifax's messy housing data with Python.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# May 18 - Clean Halifax's messy housing data with Python\n",
    "*© 2022 Colin Conrad*\n",
    "\n",
    "Welcome to the second week of SummerPy! Last time we explored how to get started with your Jupyter environment. This week, we will work towards something useful: data processing with CSV files. If you are like me (i.e. don't have a formal technical undergrad) you may feel that this week's exercise covers a lot of ground. That's because it does... the concepts that we will explore are essentially what you would teach in a first year, first semester Python course. I chose this approach because I wanted to teach you something useful right away, and after all, there are no grades here; be sure to check out some of the extra materials such as [Sweigart (2020)](https://automatetheboringstuff.com/) if you need extra material. \n",
    "\n",
    "**This week, we will achieve the following objectives:**\n",
    "- Get a csv file into Python\n",
    "- Navigate your data by processing `lists`\n",
    "- Clean data using `if` statements\n",
    "- Clean all of your data using `for` loops\n",
    "- Write your cleaned csv file\n",
    "\n",
    "By the end of this session you should have some idea of how Python is valuable for data validation and processing."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Case: Canada Mortgage and Housing Corporation\n",
    "The [Canada Mortgage and Housing Corporation (CMHC)](https://www.youtube.com/watch?v=vy19rwKFGYk#action=share) is a crown corporation with a mandate to assist housing for Canadians. Founded shortly after the second world war, its purpose was once to find housing for veterans. Now it provides programs for making mortgages more affordable and enforces policies designed to make rent more accessible. The CMHC also provides data on rental affordability, including [information on the average price of rent](https://www03.cmhc-schl.gc.ca/hmip-pimh/en#TableMapChart/0580/3/Halifax%20CMA) for various regions in Halifax through its information portal.\n",
    "\n",
    "Like most information portals, CMHC's is built to make the most important information accessible. This has the unfortunate consequence of also making it difficult to retrieve the data that you need. To retrieve rental price data, you must first select your appropriate data boundary and then select `Primary Rental Market` and `Average Rent ($)`. You can then download data in csv format, similar to the `2021_10_Halifax_Rental.csv` provided to you if you wish.\n",
    "\n",
    "The data provided by CMHC is not formatted in a way that is conducive to data science. They provide data in rows and columns, complete with notes at the bottom of the file. It would be desirable to prepare the data in a way that is appropriate for analysis. If we were able to do it in Python, we could sort through hundreds of CSV spreadsheets, such as the rental data for October of each preceding year. Finally, if you are interested in learning more about the CMHC data collection methods, you can [learn more here](https://www.cmhc-schl.gc.ca/en/professionals/housing-markets-data-and-research/housing-research/surveys/methods/methodology-rental-market-survey). "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Objective 1: Get the data into Python \n",
    "Before we can get started with data science earnestly, we need to know more about one last core Python feature: **libraries**. One of the main advantages of Python versus many other programming languages is that it is high level and highly portable. This is to say that you can do a lot with Python in a few lines of code. One of the main things that makes this possible are Python's libraries.\n",
    "\n",
    "In programming, a library is a collection of pre-defined routines that you can import into your code without writing them. These greatly accelerate the time that it takes to finish a programming task, and in some cases, save you years of work. Just like libraries designed for humans, Python programming libraries are generally curated by groups of people who ensure that the library is usable. As a free and open source programming language, experienced developers will often create and curate libraries for free, sometimes at great expense of their time!\n",
    "\n",
    "[Python's csv library](https://docs.python.org/3/library/csv.html) is a fantastic resource for reading and writing csv files. This one takes a little getting used to, so it is better to simply give an example of its basic use and then explain it. The following cell gives code for reading and `print`ing the 2020 file that you downloaded from CMHC."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import csv # this is how you tell Python that we want to use the CSV library\n",
    "\n",
    "with open('data/2020_10_Halifax_Rental.csv', newline='') as csvfile: # tells Python which file to read\n",
    "    housing_reader = csv.reader(csvfile, delimiter=',') # draws on the reader object to read the file\n",
    "    for row in housing_reader: # this is a loop; more on this later\n",
    "        print(row) # we saw this last week"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Magic, right?** Well, not quite, but it's pretty cool. There are a lot of steps here that we didn't dig into yet, but we will explore while doing today's exercise. To start understanding this code however, I recommend opening the `data` folder that comes with the GitHub repository. In that folder you will find `data/2020_10_Halifax_Rental.csv`. Try opening that file in Excel.\n",
    "\n",
    "You should notice that the printed output above loosely mimics the data that Excel interpreted. This is because Python is looking at the same data, though it expressed it using `lists`, one of Python's default data structures. Let's dive a bit more into lists, so that we can understand how the `csv` library works.\n",
    "\n",
    "## Challenge Question 1 ##\n",
    "If you opened the `data/2020_10_Halifax_Rental.csv` file in Excel, you might have noticed that we provided the 2021 data as well. Can you modify the `csv` code above to work with the 2021 data instead?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import csv # this is how you tell Python that we want to use the CSV library\n",
    "\n",
    "with open('data/2021_10_Halifax_Rental.csv', newline='') as csvfile: # tells Python which file to read\n",
    "    housing_reader = csv.reader(csvfile, delimiter=',') # draws on the reader object to read the file\n",
    "    for row in housing_reader: # this is a loop; more on this later\n",
    "        print(row) # we saw this last week"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Objective 2: Navigate your data by processing \n",
    "Lists are very important for researchers and data scientists who use Python. Lists are data structures which consist of a series of values organized in a systematic way. For example, if we wanted to list the rental values for the South End of Halifax, as given in the 2020 CMHC rental data, we could create a list such as peninsula_south below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "peninsula_south = [930, 1269, 1756, 2029, 1416] #bach, 1bdr, 2bdr, 3bdr, total"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In some ways, lists are like the strings which we investigated last week. Lists are organized with indexes, similarly to the sequence of characters in strings. For example, if we wanted to retrieve a value for the first item in our list, we could retrieve it using the index. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "peninsula_south[0] # retrieves the second value from the list"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Lists can also be subdivided using an index range, similarly to strings. For example, if we had the string `\"The data scientist solved the problem\"` and wanted to only retrieve the values `\"The\"`, we could specify the range `[0:3]`. Likewise, to retrieve values from a list, we can specify the range of items, such as with the code below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "phrase = \"The data scientist\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "phrase[0:4]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "peninsula_south[0:4] # returns a subdivided list of only the first three values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Lists can also contain any type of data, such as strings, integers, floats ... even other lists! In this respect, they are very different. The code below demonstrates how lists can contain different types of values. Try printing the various list values by executing the cell."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "list_example = [\"Strings are cool!\", 42, 42.56, [\"Seriously, they are cool!\", \"You can even add lists within lists!\"]]\n",
    "\n",
    "for l in list_example:\n",
    "    print(l)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Revisiting the CSV library\n",
    "Now that we know something about lists, we can start to understand the magic of the `csv` library. The `csv.reader` tool essentially reads each row of a CSV file and brings it into our Python environment. Once it is in Python, we can move each row into a list. Let's try to use the `csv` library again, but this time with a list rather than the `print()` function. The code below specifies a list called `my_data` and then attempts to use it to save each row of the `csv`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "my_data = [] # specify the row\n",
    "\n",
    "with open('data/2020_10_Halifax_Rental.csv', newline='') as csvfile:\n",
    "    housing_reader = csv.reader(csvfile, delimiter=',')\n",
    "    for row in housing_reader: # this is a loop; more on this later\n",
    "        my_data = row # makes my_row equal to the current row in reader"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "At first glance, it seems like this worked. Well... at least it didn't crash. We can test our code by `print()`ing the contents. The code below will give us the content of our list. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(my_data) # only the final row is saved"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is close, but not quite what we were looking for. If this worked as intended, we should see the full CSV file printed; we instead only saw the final line of the file. Upon closer inspection, you will find that the code was writing over each row\n",
    "\n",
    "An alternative approach is to use the `.append()` method. This is a feature of Python's lists which places each new iteration to the end of a list. The code below leverages this function to save the whole spreadsheet."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "my_data = [] # specify the row\n",
    "\n",
    "with open('data/2020_10_Halifax_Rental.csv', newline='') as csvfile: # these are the same as before\n",
    "    housing_reader = csv.reader(csvfile, delimiter=',')\n",
    "    for row in housing_reader: \n",
    "        my_data.append(row) # this is different. The .append() adds each new row to a list. We now have a two dimensonal list "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can now use this to print the list. Run the code below to see the result."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(my_data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is a list of lists! We can use this to navigate specific rows of the CSV file, like the code below. Pretty handy, huh? There are still a few things that we need to dig into before this will make complete sense, though this is a cool dive into the good stuff."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(my_data[3])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Challenge Question 2\n",
    "Last week we learned that we can navigate lists by using a shorthand notation with the `:`; for example `my_data[0:3]`. What would you need to write to retrieve only the rows that contained numerical data? Write the notation below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# insert code here\n",
    "print(my_data[4:13])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Objective 3: Clean your data using if statements\n",
    "So far, so good. However, the dataset that we imported is still very messy. For example, only some of the rows are especially helpful to us, and the most useful rows include markers (e.g. `a`) in the file which are used to describe the data. It would be much more usable if we found a way to remove those. Let's find a way to clean up this data.\n",
    "\n",
    "While most people in this session are new to programming, many of you will be familiar with flow charts. If we wanted to express our data cleaning processes as a flowchart, we could illustrate it as expressed below. Two flow control tools that will allow us to leverage this are `if` conditional statements, and `for` loops. Let's unpack these two concepts.\n",
    "\n",
    "\n",
    "<img src=\"img/cleaner_data.png\">\n",
    "\n",
    "### Conditional statements\n",
    "Conditional statements (expressed as `if` in Python) are lines of code that only execute if the condition is met. These work pretty similarly to Excel, in the sense that an `if` statement checks the condition immediately to the right of it. \n",
    "\n",
    "One example that is relevant here could be strong validation, similarly to what we saw last week. As you may recall, we can use the `len()` function to find the length of a string. We could use this to create a simple string validation script to figure out whether a string contains Dalhousie's full name, or its short form. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "len(\"Dalhousie University\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "university_name = \"DAL\" # try changing this to \"Dal\" and see what happens\n",
    "\n",
    "if len(university_name) < 4: # checks if the number of characters in university_name is less than 4\n",
    "    print(\"short form\")\n",
    "else: # for all other conditions\n",
    "    print(\"long form\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Similarly, we could apply conditionals to only include lists that are of a predefined length. The script below includes an `if` statement that only includes rows that have a length greater than two. This will help us remove the descriptive rows. This is a highly useful skill with a wide range of applications. Consider applying it beyond in the challenge question."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "my_data = [] # specify the row\n",
    "\n",
    "with open('data/2020_10_Halifax_Rental.csv', newline='') as csvfile: # these are the same as before\n",
    "    housing_reader = csv.reader(csvfile, delimiter=',')\n",
    "    for row in housing_reader: \n",
    "        if len(row) > 2: # only include rows that have a length greater than 2\n",
    "            my_data.append(row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(my_data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Challenge Question 3\n",
    "If statements are highly versatile, in the sense that you can use them for a lot more than length checks. Write a script that only includes data if it belongs to `Peninsula South`. Could you refine this further to include other rows that denote Peninsula values?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# insert code here\n",
    "\n",
    "my_data = [] # specify the row\n",
    "\n",
    "with open('data/2020_10_Halifax_Rental.csv', newline='') as csvfile: # these are the same as before\n",
    "    housing_reader = csv.reader(csvfile, delimiter=',')\n",
    "    for row in housing_reader: \n",
    "        if row[0] == \"Peninsula South\":\n",
    "            my_data.append(row)\n",
    "            \n",
    "print(my_data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Objective 4: Clean all of your data using for loops\n",
    "As mentioned above, the other basic flow control tool that can help us here is a loop. In Python, loops are usually expressed in one of two ways: a `while` loop and a `for` loop. A `while` loop is a loop that is constrained by a single condition, and when it is met, the loop ends. We will not cover it in this exercise, though I encourage you to dig more into it. The other type of loop is a `for` loop, which can iterate across all of the items in a list or string. This is very helpful in a programming language like Python that is often used for data validation.\n",
    "\n",
    "To see a `for` loop in action, consider the code below. It will check through and print the characters in the `university_name` string until it completes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "university_name = \"Dal\"\n",
    "\n",
    "for ch in university_name:\n",
    "    print(ch)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we apply the `for` loop to our data, we can similarly print all of our rows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for row in my_data:\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can probably see where this is going. The CSV code that we have been using so far leverages the `for` loops to iterate through each of the rows in the CSV file. We can use the same technique to similarly iterate through each row, just like in our flow chart. The script below thus:\n",
    "- Opens the spreadsheet\n",
    "- Checks each row for length\n",
    "- Checks each item in the row for length\n",
    "- Saves the validated rows in a new list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "my_data = [] # specify the row\n",
    "\n",
    "with open('data/2020_10_Halifax_Rental.csv', newline='') as csvfile: # these are the same as before\n",
    "    housing_reader = csv.reader(csvfile, delimiter=',')\n",
    "    for row in housing_reader: \n",
    "        if len(row) > 2: # only include rows that have a length greater than 2\n",
    "            clean_row = [] # create a placeholder for the cleaned row\n",
    "            for row_item in row: # check each row item\n",
    "                if len(row_item) > 2:\n",
    "                    clean_row.append(row_item) # only append the items that have a length greater than 2\n",
    "            my_data.append(clean_row)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can observe the data by simply calling the list. The result is a clean spreadsheet that is more usable for analysis."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "my_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Challenge Question 4\n",
    "While this list is very usable, there is still one challenge: the commas. How would you modify the script to remove the commas? I left some code to help you get started."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# complete the code below\n",
    "# learn more here https://www.w3schools.com/python/ref_string_replace.asp\n",
    "\n",
    "for r in my_data:\n",
    "    for i in r:\n",
    "        # insert code here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Objective 5: Write a csv file\n",
    "There is one last thing we have to do before calling it. While we have successfully developed a working cleaning script, it would be very useful to export it back to CSV. The `csv` library once again comes to the rescue. After reading through the [CSV documentation](https://docs.python.org/3/library/csv.html), it is clear that we can use the `writer` to create a new CSV file with the cleaned data. The following script will create a new CSV file. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('data/2020_10_Halifax_Rental_clean.csv', 'w', newline='') as csvfile:\n",
    "    writer = csv.writer(csvfile, delimiter=' ')\n",
    "    for r in my_data: # this is our cleaned data file\n",
    "        writer.writerow(r) \n",
    "        \n",
    "# be sure to check your data folder!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And there you have it! Basic data processing in Python. It's a lot to cover, though I encourage you to read up on these various features of Python, because this skill is super-handy. Catch you in two weeks-- we will start with Pandas, an even easier approach to doing data science!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# References\n",
    "Canada Mortgage and Housing Corporation (15 May 2022). Housing Market Information Portal. Retrieved from: https://www03.cmhc-schl.gc.ca/hmip-pimh/en#TableMapChart/0580/3/Halifax%20CMA"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}