01-Intro-to-Importing-Data-Tables-Graphs.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Introduction to Importing Data, Using Tables and Creating Graphs "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## The Jupyter Notebook\n",
    "\n",
    "*If you run into errors, check the [common errors](https://docs.google.com/document/d/1-LUvfYYI5UtjYiZerCGIBNgzkaJHNxl4530tgh37uYs/edit?usp=sharing) Google doc first.*\n",
    "\n",
    "First of all, note that this page is divided into what are called \"cells\". For example, the following cell is a \"code cell\" where you will write your code. You'll see a `In [ ]:` next to each cell for code, which is a counter for the cells you have run. You can navigate cells by clicking on them or by using the up and down arrows. Cells will be highlighted as you navigate them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# this is a code cell"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Executing cells\n",
    "\n",
    "<p></p>\n",
    "\n",
    "<div class=\"alert alert-info\">\n",
    "You can execute cells with <b><code>Ctrl-Enter</code></b> (which will run the cell and keep the same cell selected), or <b><code>Shift-Enter</code></b> (which will run the cell and then select the next cell).\n",
    "</div>\n",
    "\n",
    "Try running the following cell and see what it prints out:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "print(\"Hello world!\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "10 + 10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "(10 + 10) / 5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now run this cell to `import` some code we'll use today, nothing will `print` out, don't worry!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "from datascience import *\n",
    "%matplotlib inline \n",
    "plt.style.use(\"fivethirtyeight\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Importing\n",
    "\n",
    "In data analytics, there is almost always a file holding your data that already exists. There are thousands of databases online that contain information on topics from all domains. In general, to import data from a file, we write:\n",
    "\n",
    "```python\n",
    "Table.read_table(\"file_name\")\n",
    "```\n",
    "\n",
    "Most often, these file names end in `.csv` to show the data format. `.csv` format is popular for spreadsheets and can be imported/exported from programs such as Microsoft Excel, OpenOffice Calc, or Google spreadsheets. \n",
    " \n",
    "An example is shown below using [U.S. Census data](http://www2.census.gov/programs-surveys/popest/datasets/2010-2015/national/asrh/nc-est2015-agesex-res.csv). "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "Table.read_table(\"http://www2.census.gov/programs-surveys/popest/datasets/2010-2015/national/asrh/nc-est2015-agesex-res.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "That's a lot of information. As you can see from the labels on top, this table shows Biological Sex (0=total, 1=male, 2=female), Age,  2010 Census Information, and predictions for U.S. population for the next five years. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Using Tables\n",
    "\n",
    "We can make criteria to cut down tables. Accessing only the rows, columns, or values specfic to our purpose makes information easier understood. Analysis and conclusions can be made when data is more digestible. \n",
    "\n",
    "We need to access the census table above and name it for further use. We assign the table to a variable so that we can reference it later!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "census_data = Table.read_table(\"http://www2.census.gov/programs-surveys/popest/datasets/2010-2015/national/asrh/nc-est2015-agesex-res.csv\")\n",
    "census_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This notebook can calculate how large this table is with two functions: num_rows and num_columns. The general form for these functions are table.num_rows and table.num_columns. \n",
    "\n",
    "Let's use these on the table above. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "census_data.num_rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "census_data.num_columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "That's a 306 x 10 table! We can first start to cut down this table using only some columns. Let's only include biological sex, age and the estimated base for 2010 census data. \n",
    "\n",
    "There are two methods to make a table with select columns included. We could either use the 'select' function or the 'drop' function. \n",
    "\n",
    "- `select` can create a new table with only the columns indicated in the parameters \n",
    "- `drop` can create a new table with columns NOT indicated in the parameters\n",
    "\n",
    "\n",
    "Here's an example of two equal codes: (keep in mind that we assign each new table to a new variable, to make organization easier). "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "select_census_data = census_data.select(\"SEX\", \"AGE\", \"ESTIMATESBASE2010\")\n",
    "select_census_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "drop_census_data = census_data.drop(\"CENSUS2010POP\",\"POPESTIMATE2010\",\"POPESTIMATE2011\",\"POPESTIMATE2012\",\"POPESTIMATE2013\",\"POPESTIMATE2014\",\"POPESTIMATE2015\")\n",
    "drop_census_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As you can see underneath the table, there are still 296 rows omitted! Our next step is to only include non-gendered data AKA data where SEX=0, neither male or female specific. \n",
    "\n",
    "To do this, we need to use a new function `where`. The general form of this function is:\n",
    "\n",
    "```python\n",
    "table_name.where(column_name, predicate)\n",
    "```\n",
    "\n",
    "To cut our table down to only include `sex=0`, we may use the predicate `are.equal_to()`. Note that we are assigning the new table to a new variable. We are referencing the table stored in an older variable (`select_census_data`), and modifying it. That modification is what is stored in the new variable. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "new_census_data = select_census_data.where(\"SEX\", are.equal_to(0))\n",
    "new_census_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are still 92 rows omitted! Let's take every 10th entry to cut this table down a little more. \n",
    "\n",
    "To do this we need to use the `take` function. The `take` function creates a new table with rows from the original table whose indices(row number) are given. In Python, indices start at 0! \n",
    "\n",
    "Here's taking every 10th entry. Inside of the take parentheses is a Python list of numbers from 0 to 90, increasing by 10s. This indicates exactly which rows we want to keep (every 10th row)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "census_10_year = new_census_data.take([0,10,20,30,40,50,60,70,80,90])\n",
    "census_10_year"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that sex is all the same, we can drop that column. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "final_census_table = census_10_year.drop(\"SEX\")\n",
    "final_census_table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "### Tables Essentials!\n",
    "\n",
    "For your reference, here's a table of useful `Table` functions:\n",
    "\n",
    "|Name|Example|Purpose|\n",
    "|-|-|-|\n",
    "|`Table`|`Table()`|Create an empty table, usually to extend with data|\n",
    "|`Table.read_table`|`Table.read_table(\"my_data.csv\")`|Create a table from a data file|\n",
    "|`with_columns`|`tbl = Table().with_columns(\"N\", np.arange(5), \"2*N\", np.arange(0, 10, 2))`|Create a copy of a table with more columns|\n",
    "|`column`|`tbl.column(\"N\")`|Create an array containing the elements of a column|\n",
    "|`sort`|`tbl.sort(\"N\")`|Create a copy of a table sorted by the values in a column|\n",
    "|`where`|`tbl.where(\"N\", are.above(2))`|Create a copy of a table with only the rows that match some *predicate*|\n",
    "|`num_rows`|`tbl.num_rows`|Compute the number of rows in a table|\n",
    "|`num_columns`|`tbl.num_columns`|Compute the number of columns in a table|\n",
    "|`select`|`tbl.select(\"N\")`|Create a copy of a table with only some of the columns|\n",
    "|`drop`|`tbl.drop(\"2*N\")`|Create a copy of a table without some of the columns|\n",
    "|`take`|`tbl.take(np.arange(0, 6, 2))`|Create a copy of the table with only the rows whose indices are in the given array|\n",
    "|`join`|`tbl1.join(\"shared_column_name\", tbl2)`|Join together two tables with a common column name\n",
    "|`are.equal_to()`|`tbl.where(\"SEX\", are.equal_to(0))`|find values equal to that indicated|\n",
    "|`are.not_equal_to()`|`tbl.where(\"SEX\", are.not_equal_to(0))` | find values not including the one indicated|\n",
    "|`are.above()`| `tbl.where(\"AGE\", are.above(30))` | find values greater to that indicated|\n",
    "|`are.below()`| `tbl.where(\"AGE\", are.below(40))` | find values less than that indicated |\n",
    "|`are.between()`| `tbl.where(\"SEX\", are.between(18, 60))` | find values between the two indicated |\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Visualizations \n",
    "\n",
    "Now that we have a manageable table we can start making visualizations! Due to the numerical nature of the census table above, let's first try a scatter plot. \n",
    "\n",
    "To create a scatter plot, we need to use the `scatter()` function. The general form is:\n",
    "\n",
    "```python\n",
    "table.scatter(\"column for x axis\", \"column for y axis\")\n",
    "```\n",
    "\n",
    "An example is shown below:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "final_census_table.scatter(\"AGE\", \"ESTIMATESBASE2010\") "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With this data, we can also make a line plot. To do this, we need to use the `plot()` function. This works a lot like `scatter()` where the general form is:\n",
    "\n",
    "```python\n",
    "table.plot(\"x column\", \"y column\")\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "final_census_table.plot(\"AGE\", \"ESTIMATESBASE2010\") "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Though a bar may be better. Bar graphs follow the same formula as scatter plots and line graphs above, with the general form:\n",
    "\n",
    "```python\n",
    "table.bar(\"x axis\", \"y axis\")\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "final_census_table.bar(\"AGE\", \"ESTIMATESBASE2010\") "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can also use the functino `barh()` instead of `bar()` in order to flip the bar graph horizontally. Sometimes, this makes for a cleaner visualization."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## Merging Tables\n",
    "\n",
    "We are going to cover one more topic briefly that you will need to use in your project. We are going to look into how to merge two tables that have common information. This technique will be very valuable when the time comes for you to do your own analysis with your own data sets. \n",
    "\n",
    "We are going to read in a table with information about psychologists. We will call this new table `psych1`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "psych1 = Table.read_table(\"example-data/psych1.csv\")\n",
    "psych1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Adding rows\n",
    "\n",
    "You may have another `table` that has the exact same columns and you just want to add the rows to what you already have. Let's read in another short `table` with a couple more psychologists:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "psych2 = Table.read_table(\"example-data/psych2.csv\")\n",
    "psych2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Great! We see that this second table has the same columns as the first one. Merging these two tables would allow us to consolidate our information. We are going to use the \"append\" method to append the second table onto the first!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "psych_merged = Table.copy(psych1)  # copying over the new_psych table to a new variable for the merged table\n",
    "psych_merged.append(psych2)\n",
    "psych_merged"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As you can see, we have succesfully merged these two tables together! Now, let us try merging an additional column onto the original `new_psych` table. \n",
    "\n",
    "### Adding columns\n",
    "\n",
    "Let's pretend that we suddenly have access to the favorite foods of each psychologist. Wow! We definitely want to include that information in our table. This means that we need to merge in a new column to the initial table. Let us print the table first, and then we'll get the other column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "psych_merged"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we are going to create to read in our new information."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "psych_foods = Table.read_table('example-data/favorite_food.csv')\n",
    "psych_foods"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Luckily, we are going to assume that each row is in the right order in the column. We are going to use a similar process as before to merge this column! The method we now use is `append_column`. Otherwise, the format stays the same!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "psych_merged_with_food = Table.copy(psych_merged)  # copying over the new_psych table to a new variable \n",
    "psych_merged_with_food.append_column(\"Favorite Food\", psych_foods['Favorite Food'])\n",
    "psych_merged_with_food"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As you can see, we've successfully merged a column to our table too!\n",
    "\n",
    "### Joining on columns\n",
    "\n",
    "Suppose now that you have more information on the these psycologists (another column), but the rows aren't in the right order so you can't just `append_column`. Luckily, you have their names. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "psych_birthplaces = Table.read_table(\"example-data/birthplaces.csv\")\n",
    "psych_birthplaces"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Awesome! We love more data, but how can I merge this back into our existing information? We can't just add a column because the order is different.\n",
    "\n",
    "***Solution***: We can use the `join` method and tell it which column the two tables have in common, and it will match the data to the correct row:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "final_table = psych_merged_with_food.join(\"Psychologist\", psych_birthplaces)\n",
    "final_table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "That's super cool!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## SUMMARY \n",
    "\n",
    "### You've learned a lot in this module! Let's look back on the key parts. \n",
    "\n",
    "- To import data from a .csv/.txt file, we write `Table.read_table(\"file_name\")`.\n",
    "\n",
    "- To create our own table, we write `Table( ).with_columns(\"Column Name\", array_name, . . .)` . \n",
    "\n",
    "- To count number of rows, we use `table_name.num_rows`.\n",
    "\n",
    "- To count number of columns, we use `table_name.num_columns`.\n",
    "\n",
    "- To create a new table with only the columns indicated in the parameters, we use `table_name.select(\"COLUMN NAME\", ...)`. \n",
    "\n",
    "- To create a new table without the columns indicated in the parameters, we use `table_name.drop(\"COLUMN NAME\", ...)`. \n",
    "\n",
    "- To create a table with only certain values, we can use `table_name.where(column_name, predicate)`.\n",
    "\n",
    "- To create a new table with indicated rows from the original table, we use `table_name.take([index 1, index 2, . . . ])`. Remember in Python indices start at 0!\n",
    "\n",
    "- To create a scatter plot, we use `table.scatter(column for x axis, column for y axis)`.\n",
    "\n",
    "- To create a line plot, we use `table.plot(x column, y column)`.\n",
    "\n",
    "- To make a bar graphs, we can use either `table.bar(x column, y column)` or `table.barh(x column, y column)`. \n",
    "\n",
    "- To make a histogram, we use `table.hist(x axis, bins(optional), unit(optional))`. \n",
    "\n",
    "- To merge tables, we use either `append` or `append_column`.\n",
    "\n",
    "- To merge two tables with a common column name we use the `join` method.\n",
    "\n",
    "---\n",
    "\n",
    "With just some simple code, we were able to do an incredible amount of data analysis! Play around with the examples until you feel comfortable with the content of this notebook. We will be using notebooks to analyze your own data sets in the future! Please ask if you have questions!\n",
    "\n",
    "\n",
    "If you need help, please consult the [Data Peers](https://data.berkeley.edu/education/data-science-community)!"
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "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.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}