Lab 8 - Making big(ger) data eas(ier) with data frames.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Week 8 - Making big(ger) data eas(ier) with data frames\n",
    "*© 2020 Colin Conrad*\n",
    "\n",
    "Welcome to Week 8 of INFO 6270! Last week we explored social media data. This week we are going to explore one of the most powerful tools in the data scientists' toolbox: the *dataframe*. The pandas dataframe is a tool which makes it easier to navigate and analyze large datasets. Built upon numpy and other dependencies, this tool is among the most essential resources for conducting analysis on larger datasets. We will also use this tool in all subsequent lab, so be sure to explore this one closely!\n",
    "\n",
    "**This week, we will achieve the following objectives:**\n",
    "- Turn your dataset into a dataframe and start querying!\n",
    "- Collect descriptive statistics from your dataframe\n",
    "- Make changes to your dataframe\n",
    "- Identify opportunities to use numpy"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Case: Airbnb\n",
    "It's pretty likely that you know something about [Airbnb](https://www.airbnb.ca/). Airbnb has been called the [world's largest hotel chain](https://www.bizjournals.com/sanfrancisco/news/2017/08/11/airbnb-surpasses-ihg-wyn-hilton-marriott-listings.html), while owning no hotels themselves. As a crowdsourcing platform, users can list their properties and rent them out to short-term renters using the Airbnb app. Though the company is not yet 12 years old as of 2020, it is among the world's most valuable privately owned companies with a market valuation of over [$35 billion](https://www.vox.com/2019/3/19/18272274/airbnb-valuation-common-stock-hoteltonight).\n",
    "\n",
    "Airbnb is not without controversy. Airbnb has been identified by the [Economic Policy Institute](https://www.epi.org/publication/the-economic-costs-and-benefits-of-airbnb-no-reason-for-local-policymakers-to-let-airbnb-bypass-tax-or-regulatory-obligations/) as an important factor in rising rent an property prices, despite often escaping tax and regulation. The company [regularly releases their application data publicly](http://insideairbnb.com/get-the-data.html). Though we cannot investigate this phenomenon in one lab, this is a useful resource for learning about data science tools."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Objective 1: Turn your dataset into a dataframe and start querying!\n",
    "As discussed in class, numpy and pandas are two distinct tools which are like peas in a pod. Numpy is a tool for transforming your data into a multi-dimensional array, sort of like a hyper-efficient Python list. The pandas (PANel + DAta) library transforms our data into numerical tables (a.k.a. data frames) which are easier to calculate and sort through. We will start with Pandas because this is the tool that will be most useful for most of you.\n",
    "\n",
    "To transform a csv file into a pandas object we need to import the pandas library. We can then import a csv file by using pandas' built-in read_csv feature."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd # import pandas \n",
    "\n",
    "import numpy as np # import numpy\n",
    "\n",
    "nyc = pd.read_csv('data/w8_nyc.csv') # command pandas to import the data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Dataframe head\n",
    "Once our data frame has been imported we can apply a few methods that can generate knowledge about the dataset. The `head()` method gives us a summary of the first five items in the dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "nyc.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Dataframe series\n",
    "\n",
    "Data frames are easily navigable compared to lists or dictionaries. If we want to retrieve all of the data from a column in the dataframe, we can call that column similarly to calling a method. The code below will give us the values for `neighbourhood_group` from the whole dataset, but will give us only the first and last values when printed. This is super-handy!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "nyc.neighbourhood_group"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### A transposed dataframe\n",
    "\n",
    "Some things that are somewhat cumbersome with lists and dictionaries are also very simple with pandas. For instance, if we wish to transpose our data (make the rows columns and the columns rows) we can use the `.T` method. This can be helpful when making calculations across entities."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "nyc.T"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Sort values\n",
    "In addition, dataframes can be easily sorted, much like SQL. The following code will sort the data by price starting with the highest values. I wonder who seriously believes that they can rent an apartment for $10 000 per night?! It must be fancy!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "nyc.sort_values(by='price', ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Subsetting the data\n",
    "Perhaps the coolest feature of a dataframe is that it facilitates efficient queries and to retrieve subsets of the data. In pandas, a subset is declared by writing square brackets following the data frame-- for instance, `nyc['neighbourhood_group']` would return the values of neighborhood. However, we can also use this to conduct Boolean searches as well. For instance, if we wanted to retrieve only the values where `neighbourhood_group == Brooklyn` we could write a query as follows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "nyc[nyc.neighbourhood_group == 'Brooklyn']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Sorting subsets\n",
    "\n",
    "Similarly, to before, if we wanted to list the values from Brooklyn according to price, we can create a new data frame which is equal to this subset and sort it by price."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "brooklyn = nyc[nyc.neighbourhood_group == 'Brooklyn']\n",
    "\n",
    "brooklyn.sort_values(by='price', ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Sort by date-time\n",
    "Pretty cool! Another feature of pandas is that it recognizes common data types which are not recognized as distinct types by Python itself. For example, pandas dataframes are optimized to recognize date and time formats. If we want to sort a search by `last_review`, for instance, we could conduct a similar query as with `price`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "recent_brooklyn = nyc[(nyc.neighbourhood_group == 'Brooklyn')]\n",
    "\n",
    "recent_brooklyn.sort_values(by='last_review', ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Query using two conditions\n",
    "\n",
    "Queries can also be more complex. If we wish to choose a subset of data which is constrained by two conditions, we can include both conditions by using the `&` operator. The following query will retrieve the values that match `Brooklyn` which also have a `last_review` equal to `2019-08-06`, the date that I seem to have retrieved this data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "recent_brooklyn = nyc[(nyc.neighbourhood_group == 'Brooklyn') & \n",
    "                      (nyc.last_review == '2019-08-06')]\n",
    "\n",
    "recent_brooklyn.sort_values(by='price', ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Querying using two conditions, one of which is an OR\n",
    "\n",
    "Finally, we can also create nested queries. The following query searches for values which match `Brooklyn` but have a last_review in the two days prior."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "recent_brooklyn = nyc[(nyc.neighbourhood_group == 'Brooklyn') & \n",
    "                      ((nyc.last_review == '2019-08-06') | (nyc.last_review == '2019-08-05'))]\n",
    "\n",
    "recent_brooklyn.sort_values(by='price', ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Challenge Question 1 (2 points)\n",
    "Using the `nyc` dataframe, conduct a query which retrieves the following:\n",
    "- Rentals only from the `Queens` neighborhood\n",
    "- Rentals with either more than 100 reviews or more than 5 reviews per month\n",
    "- Rentals with a price of less than 200\n",
    "- Rentals which are the `Entire home/apt` room type\n",
    "\n",
    "Sort your findings by order of price, starting with the lowest price."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# insert code here!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Objective 2: Collect descriptive statistics from your dataframe\n",
    "One of the most handy features of pandas dataframes is that they come with a few built-in methods for conducting descriptive analysis. For example, the `.describe()` method will give summary of statistical measures of a given dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "nyc.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Describe a column\n",
    "This is good, but perhaps too much to be useful. Instead, we could choose to apply `.describe()` to a single column. This will give us more manageable information."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "nyc.price.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Calculate the mean price\n",
    "In addition, dataframes also have functions for calculating specific statistics such as mean, median and mode. To calculate the mean value of a column we can write the line below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "nyc.price.mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Calculate the sum\n",
    "Alternatively, if we wanted to find the sum of a column (e.g. the total number of reviews) we can use the sum function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "nyc.number_of_reviews.sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Calculate number of unique values\n",
    "Finally, there are a few other methods which are handy. For instance, the `.nunique()` method will tell use the number of unique values in a dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "nyc.host_id.nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Challenge Question 2 (2 points)\n",
    "Write code that calculates the median price for the property category of `'Entire home/apt'`. **Hint**: (This tutorial site)[https://www.tutorialspoint.com/python_pandas/python_pandas_descriptive_statistics.htm] has a pretty good summary of dataframe functions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# insert code here!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Challenge Question 3 (1 point)\n",
    "Write code which finds the neighborhood (*not* neighbourhood_group) with the most listings. You can probably do this in one line, though if you choose to use a more complex function, you are welcome to do so!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# insert code here!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Challenge Question 4 (1 point)\n",
    "The `availability_365` column represents the number of days in the past year that the property was available to rent through the Airbnb app. Retrieve the number of listings in New York which were available every day of the previous year."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# insert code here!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Objective 3: Make changes to your dataframe\n",
    "In addition to being navigable, dataframes are also relatively easy to change. For instance, if we wanted to insert a column, we could use the `.insert()` method. According to the [documentation](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.insert.html), this method requires four pieces of information: \n",
    "- Where to insert it\n",
    "- The name of the column\n",
    "- The value to be inserted\n",
    "- Whether to allow duplicates\n",
    "\n",
    "The code below inserts the value \"Airbnb\" in a column named `dataset`. This would be handy if we acquired our data from more than one source."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "nyc.insert(2, \"dataset\", \"Airbnb\", True)\n",
    "nyc"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Deleting data in python\n",
    "This said, given that our data came from a single source, we have no need for this. To drop a column, we could choose to use the del keyword, which deletes objects stored in python. Note that this keyword is not unique to pandas and can be used for virtually anything in python."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "del nyc['dataset']\n",
    "nyc"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### The drop method\n",
    "The proper way to drop a column in pandas however is to use the `.drop()` method. This method is used to drop rows or columns from a pandas dataframe. For instance, if we wished to drop the first entry we could use the following:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mod_nyc = nyc.drop([0, 1]) # create a new dataframe which has the first two values dropped\n",
    "\n",
    "mod_nyc.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pandas drops rows by default so we only needed to provide the indexes to make it happen. Alternatively, to drop columns we need to provide a label and an `axis=1` value to tell pandas to search for the column with said label. If we wished to remove the host names (say, in order to better preserve privacy) we could specify the following."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mod_nyc = nyc.drop(labels='host_name', axis=1) # create a new dataframe which has the first two values dropped\n",
    "\n",
    "mod_nyc.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Entering new columns\n",
    "We can also add new columns to our dataframe. To create a new column, you can add the column values using a key/value format. The code below creates a new column called `reviews_to_avaliability_ratio` which calculates the number of reviews relative to the listing availability."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "nyc['reviews_to_avaliability_ratio'] = nyc['number_of_reviews']/nyc['availability_365']\n",
    "\n",
    "nyc.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Challenge Question 5 (2 point)\n",
    "One measure which might be interesting in this dataset is the ratio of price to number of reviews. Create a new column called `price_to_review_ratio` which calculates the price divided by the reviews. Once you have added this column, provide code which prints the median value of this number."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# insert code here!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Objective 4: Identify opportunities to use numpy\n",
    "Another tool related to pandas which is worth mentioning is numpy. I had originally anticipated to cover this tool in depth, though decided that a focus on pandas would be more appropriate for this course. Numpy is a library for building multideminsional arrays and is one of the dependencies of the pandas library (in other words, pandas is built on top of numpy).\n",
    "\n",
    "The architecture that numpy relies on is the `ndarray`, an efficient multidimensional array data structure. You have actually seen multidimensional arrays before, in the context of nested lists. Let's start by converting our pandas dataframe into a numpy array so that you can see this in action."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "nyc_np = nyc.to_numpy()\n",
    "\n",
    "nyc_np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Iterating through numpy arrays\n",
    "From our perspective, the strength of numpy arrays are that they are like lists but overcome most of the annoying little things that frustrate us when programming with lists. Just like before, to retrieve the first value of a numpy array we call the 0 index."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "nyc_np[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Similarly, we would be able to write functions that iterate through the array like before. The function below uses a for loop to identify the number of listings in either Brooklyn or Queens."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "counter = 0\n",
    "\n",
    "for entry in nyc_np:\n",
    "    if entry[4] == \"Brooklyn\":\n",
    "        counter += 1\n",
    "    elif entry[4] == \"Queens\":\n",
    "        counter += 1\n",
    "\n",
    "print(\"The number of listings in Brooklyn and Queens is \" + str(counter))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This said, numpy is simply better for most things. You may recall that during the previous step we calculated the reviews-to-availability ratio. Some of the values in the calculation divided by zero, which is mathematically impossible. For example, value 3 is one such value."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "nyc_np[3][16]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Normally in python, dividing by zero throws an error. If you execute the code below, for instance, you will receive an error notification."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "test = 1/0"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "However, numpy assigns the value of `inf` in these situations so that you do not break your code. This is a very handy feature!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "test = np.divide(1,0)\n",
    "print(test)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The value of `np.inf` is a special value which can be matched logically. This allows us to create some pretty good code for cleaning data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "if nyc_np[3][16] == np.inf:\n",
    "    print(\"True!\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Challenge Question 6 (2 points)\n",
    "This final challenge will reuqire you to put some of these pieces together. Create code which calculates the mean price-to-rating ratio in Brooklyn and Queens. You will most certainly run into values called `inf` (infinite) and `nan` (not-a-number). Numpy has functions to help with these values; consider reading the docs on `isnan()` [here](https://docs.scipy.org/doc/numpy/reference/generated/numpy.isnan.html)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# insert code here!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## References\n",
    "\n",
    "Pandas (2020). 10 minutes to pandas. Retrieved from: https://pandas.pydata.org/pandas-docs/stable/getting_started/10min.html\n",
    "\n",
    "Pandas (2020). Cookbook. Retrieved from: https://pandas.pydata.org/pandas-docs/stable/user_guide/cookbook.html#cookbook"
   ]
  }
 ],
 "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.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}