ZCI_SS_Day1_02_Pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr><hr>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Science Summer School - Split '16 </center>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Day 1 - Python for data analysis fundamentals \n",
    "### *Numpy, Pandas, Matplotlib*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "(c) 2016 Damir Pintar"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*version: 0.1* \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`kernel: Python 2.7`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr> <hr>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Part 2 - Pandas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "*Pandas* is an open-source library which aims to facilitate data analysis tasks in the Python programming language. The name *pandas* stands for \"Python Data Analysis Software\", although the name actually originates from \"panel data\" a term commonly used in economics for multidimensional datasets."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "While *Numpy* offers plenty of tools for manipulating numerical vectors of matrices, it is not very well suited for dealing with more complex data formats. *Pandas* library provides data structures specifically aimed at facilitating analysis of tabular data where columns depict attributes and rows describe observations - similar to  *DataFrame* objects used in programming language R, tables in a relational database, or perhaps typical Excel sheets. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In our brief *Pandas* introduction we will focus on two flagship objects in the Pandas Library:\n",
    "- `Series` and\n",
    "- `Data Frame`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Most of our work in *Pandas* will revolve around manipulating objects of the above classes. Hence, let's briefly describe what these objects are all about, and then show how to deal with them in practice.'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It's easiest to consider `Series` as a *Numpy* array which allows *indexing*. Essentially it trades *efficiency* for *flexibility*. While it's relatively easy to convert between *Pandas*' `Series` and *Numpy*'s `Array`, in practice you will probably stick with `Series` unless you require computational efficiency or want to perform some lower-level operations."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`Data Frame` on the other hand can be viewed as a data structure which allow storing data in tabular form, each column describing a certain attribute belonging to a certain domain, and row depiciting a tuple or observation. You can also see a `Data Frame` as a collection of `Series` objects."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Or, to put simply, `Data Frame` is our dataset in a tabular form, and each column is a `Series`. Analyzing our data often boils down to manipulating our `Data Frame` objects in various ways by reshaping them, transforming, slicing, aggregating and using as input for various functions and methods."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pandas Series"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First, let's import the *Pandas* package using the common convetion of abbreviating it as *pd*:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can create a `Series` object in a similar fashion as we did with *Numpy* `Array`, by calling a default constructor (`Series`) and providing a list argument:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "a = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]\n",
    "\n",
    "# create a Numpy Array called np_a and a Pandas Series called pd_s from the above list\n",
    "###\n",
    "###\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# print out np_a\n",
    "###\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# print out pd_s\n",
    "###\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice one change between `Series` and `Array` - first, the information is being printed out in a more informative fashion, with even the data type provided. More importantly, *Pandas* has automatically asigned indexes to our `Series` - in this case integers which correspond to default indexing used by Python lists as `Array`s. However, if you try to treat it as if it were a *Numpy* array, you might soon run into certain problems."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# slice pd_s from 2nd to 4th element and store it in a variable called pd_slice\n",
    "###\n",
    "\n",
    "\n",
    "# print out pd_slice\n",
    "###\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# print out the first element of pd_slice\n",
    "###\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Did you try to get the first element of `pd_slice` by indexing it with 0? If you did, you might have been suprised by an unexpected error. What might be the cause of it?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here, we have stumbled upon a slightly controversial issue in *Pandas* `Series` indexing. By design, `Series` recognizes three types of slicing: **boolean-based**, **integer-based** and **label-based**. Integer-based indexing means we are asking for the elements regarding their positions - i.e. integer locations - in a Series. Label-based indexing means we identify our elements by labels (or keys). Label-based indexing often takes priority, and things may get confusing when (as in our case) labels and integer locations seem interchangeable. When we used a range of integers, *Pandas* supposed that we wanted to use integer-based indexing (as we did), but when we asked for one element, *Pandas* incorrectly assumed we were asking for an entry with a specific key.  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To avoid this issue, we may opt to use `Series` methods called `iloc` and `loc`, which are hardcoded to use integer-based and label-based indexing respectively."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It *is* possible to use integer-based location indexing though, but you need to use the `iloc` method of the `Series` object:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# print the first element of pd_slice using the iloc method.\n",
    "###\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# now print the same element using the loc method\n",
    "###\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Things may get slightly more confusing when we reveal that the range operator `:` also works just fine for label-based indexing!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#print pd_s to remember how it looks\n",
    "###\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# print 3rd, 4th and 5th element of pd_s using the iloc method\n",
    "###\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# print 3rd, 4th and 5th element of pd_s using the loc method\n",
    "###\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If the last exercise left you with a feeling something is not quite right, that's actually true. There is a slight inconsistency in label-based indexing compared to Python's usual syntax - **label-based indexing is inclusive from both ends**! Even though this might be considered somewhat unpythonic and more like something we would expect to see in a less strict language like `R`, it is actually an agreed upon compromise since otherwise the user would be inconvinienced to always search for the label of the row under the one he wants to actually retrieve, or use the clumsy `+1` semantics which doesn't really make sense when applied to labels."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's make our life easier by assigning our own index to the `Series`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "\n",
    "pd_s = pd.Series([1,2,3,4,5], index = ['a', 'b', 'c', 'd', 'e'])\n",
    "\n",
    "# print the element with the index 'c' from pd_s.\n",
    "###\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# print all elements from the one indexed with 'b' to the one with indexed 'e'\n",
    "# use list range syntax\n",
    "###\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice that in the last exercise you could have used the `loc` method, but it wasn't necessary; *Numpy* realized you wanted to use label-based indexing simply by checking out the index arguments. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Even though the `Series` behaves a lot like a dictionary, there are still scenarios where we can treat it just as if we are dealing with a *Numpy* array. One example of this is using relational operators. Let's try to filter `Series` elements by using similar methods we used in the *Numpy* lecture:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# see what happens if you print the following expression:  pd_s > 2\n",
    "###\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# now try to print all elements from pd_s which are larger then 2\n",
    "###\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As you can see, instead of actual indexes we can provide a `boolean Series` which will then slice our `Series` in an expected fashion. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Also similar to arrays, we can perform arithmetical operations with scalars and get the expected results."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# multiply pd_s by 10 and print out the result\n",
    "###\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Performing arithmetic operations between `Series` objects will work similar to *Numpy* arrays, i.e. it will be executed in vectorized fashion. However there is one crucial difference - *Pandas* will take into account `Series` indexing and will align two objects accordingly."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "pd_a = pd.Series([1, 2, 3, 4, 5], index = ('a', 'b', 'c', 'd', 'e'))\n",
    "pd_b = pd.Series([1, 2, 3, 4, 5], index = ('c', 'e', 'd', 'f', 'b'))\n",
    "\n",
    "\n",
    "# print the result of pd_a + pd_b w and explain the result\n",
    "###\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As you see, *Pandas* will first align rows by indexes and then perform the operation. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If one or both `Series` operands contain indexes which aren't present in the other operand, we get `NaN` as the result. Sometimes this isn't what we want. *Pandas* allows us to \"fill in\" the default value for these cases, but then we cannot use the operator (since we don't have a way to input additional parameters). This is why *Pandas* offers special methods of`Series` objects - such as `add` or `multiply` (`mul`) -  which perform the same thing as the operator but offer us a chance to input additional parameters."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# add pd_a and pd_b using the DataFrame method called add. Add a fill_value parameter set to 0\n",
    "# simply print the result\n",
    "###\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# multiply pd_a and pd_b using the DataFrame method multiply (or mul). Use appropriate fill in value to avoid NaNs.\n",
    "# print out the result\n",
    "###\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Pandas* offers a huge selection of attributes functions which offer various ways to manipulate `Series` elements, execute various unary or binary operations or compute values. You may find it worthwhile to check the official API documentation for Series at <a href =\"#1\"> [1] </a>. Amongst other things, you can try:\n",
    "- `shape` - (attribute) returns dimensions of underlying data\n",
    "- `index` - (attribute) returns an array of Indexes\n",
    "- `values` - (attribute) returns an array of values\n",
    "- `size` - (attribute) returns the number of elements\n",
    "- `sort_values` - sorts Series by values\n",
    "- `sort_index` - sorts Series by indexes\n",
    "- `add, sub, mul, div, mod, pow` - performs arithmetic operations\n",
    "- `min, max, mean, median, describe` - calculates statistics\n",
    "- `isnull, notnull, eq, lt, gt` - returns boolean Series based on chosen condition\n",
    "- `unique` - returns unique values \n",
    "- `str.*` - a family of functions involving string manipulation\n",
    "- etc."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's experiment with a few of these methods:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    " #initializing a Series first - notice how index keys do not need to be unique!\n",
    "pd_a = pd.Series([4, 2, 9, 11, 4, 18, 2, 10], index = ['b', 'a', 'd', 'c', 'a', 'e', 'f', 'c'])\n",
    "\n",
    "# print just the indexes of the above series\n",
    "###\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# print just the values\n",
    "###\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# print the number of elements\n",
    "###\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# print the series sorted by indexes\n",
    "###\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# print the series sorted by values\n",
    "###\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# print the unique values of the Series\n",
    "###\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# print the arithmetic mean of the series\n",
    "###\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# print all the statistics by using the \"describe\" method\n",
    "###\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, we are not constrained to provided functions. We can apply any function we want to a `Series` object by using the `apply` method of a `Series` object and providing the function as a parameter (and `args` as a set of additional parameters, if needed)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# initialize a Series containing three floating point numbers\n",
    "import math\n",
    "pd_series = pd.Series([1./3, math.pi, 0.123456789])\n",
    "\n",
    "# print a Series of natural logarithms of the elements of pd_series\n",
    "# use np.log function from the numpy package\n",
    "###\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# round the elements of pd_series elements to 2 decimals using numpy's round function \n",
    "# remember that you can put positional arguments in an 'args' list \n",
    "# or keyword arguments directly\n",
    "# print out the results\n",
    "###\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# you can use custom functions too!\n",
    "# apply custom square function to the series by using lambda x: x ** 2 as a function argument\n",
    "###\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr><hr>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pandas DataFrame"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "`DataFrame` is a data structure most commonly used for storing 2-dimensional tabular data with columns of potentially different types. It is easiest to compare it to a table in a relational database, Excel spreadsheet, or a `data.frame` object in the `R` programming language. It can also be thought of as a collection of `Series` objects whih all share the same index (\"row indices\"), but are also indexed by the `DataFrame` itself externally (\"column indices\"). If you prefer Python lingo, if `Series` objects are like a dictionary, `DataFrame` is like a dictionary of dictionaries (in fact, a dictionary of dictionaries is one type of arguments the `DataFrame` accepts by default!)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Constructing a DataFrame"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You most commonly create `DataFrame` objects in one of the two ways:\n",
    "- programmaticaly using one of the default constructors\n",
    "- by reading data from a file (.TXT, .CSV etc.)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Both of these ways have plenty of available options. For clarity, we will here demonstrate two most common ones. For additional options, be sure to check the official documentation at <A href = \"http://pandas.pydata.org/pandas-docs/stable/api.html#series\">[1]</a>."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# creating a DataFrame programmatically\n",
    "zipcode = [10000, 51000, 21000, 31000, 20000]\n",
    "dataset = { 'name': ['Zagreb', 'Rijeka', 'Split', 'Osijek', 'Dubrovnik'], \n",
    "           'avg_salary': [6359, 5418, 5170, 4892, 5348], \n",
    "           'population': [790017, 128384, 167121, 84104, 28434], \n",
    "           'tax': [18, 15, 10, 13, 10]}\n",
    "    \n",
    "\n",
    "# and send this dictionary to the DataFrame constructor\n",
    "cities_df = pd.DataFrame(dataset, index = zipcode, columns = ['name', 'avg_salary', 'population', 'tax'])\n",
    "cities_df.index.name = 'zipcode'   #otherwise it will stay nameless\n",
    "\n",
    "# try printing out the above data frame\n",
    "###\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We have first created a list of indexes, and then a dictionary holding our data columns. Then we have used one of the appropriate `DataFrame` constructors which takes exactly the format of arguments that we have prepared. The `columns` parameter wasn't strictly necessary, but since Python dictionaries do not have defined ordering, the order of columns would ultimately be completely arbitrary which usually isn't what we want. Additionally, you might have noticed that we explicitly stated the index name by setting the `index.name` attribute of the `DataFrame` object. This is not really required, but when we are using a data column for a key, we usually prefer it having its own header."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "NOTE: if you explicitly used the `print` function to print out the data frame, revisit the code segment again and print out `cities_df` using autoprint (just put the name of the variable as the last command in the code block). You will see that as an added bonus, Jupyter Notebook prints out dataframes as HTML tables. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There is a myriad of other ways to construct a `DataFrame` programmatically. We avoid showing these for reasons of brevity, but be free to check out the official API documentation on <a href = \"#1\"> [1] </a> for a particular method you may find convenient."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's try to create the same data frame by reading it from the CSV file. Ensure that there is a `CroCities.csv` file in the home directory of this notebook. Open it with a text editor (*Notepad* on Windows, *vi* or similar on Unix) and analyze its structure. It should look similar to the following: "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**CroCities.csv**\n",
    "\n",
    "`zipcode,name,avg_salary,population,tax\n",
    "10000,Zagreb,6359,790017,18 \n",
    "51000,Rijeka,5418,128384,15\n",
    "21000,Split,5170,167121,10\n",
    "31000,Osijek,4892,84104,13 \n",
    "20000,Dubrovnik,5348,28434,10`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's create our data frame:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# creating a dataframe by reading from a CSV file\n",
    "\n",
    "cities_df = pd.read_csv('CroCities.csv', index_col = 'zipcode')\n",
    "\n",
    "# print out the above data frame\n",
    "###\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As you saw, this was pretty painless. In practice, you will want to check the `read_csv` API for all available options so your imported data frame contains correct data in the type or form that you require. Things you need to watch out for is the separator symbol (default is `,`, set your own with a `sep` attribute if needed), weather the file has a header or not (control it with `header` parameter which takes a binary argument) and, if needed, set your own column names (with a 'columns' parameter), set character encoding etc. In our case we have explicitly stated that `zipcode` is our index column, otherwise *Pandas* would have provided its own key (a usual range of integer values). "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you want to read data from another source, such as an Excel file, relational database, JSON file, HTML file etc. check out the official `DataFrame` API at <a href = \"#1\"> [1] </a> for available functions. *Pandas* offers plenty of options out-of-the-box and there is a great chance that the format you need has its own accompanying *Pandas* function. If not, rememeber that you can almost always use a CSV file as a proxy between your data source and *Pandas*."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Selecting rows / colums"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "One of the most common things you will do with a data frame is filtering out specific rows and columns based on a certain criteria. *Pandas* is very flexible with what you can do with your data frames, however you need to understand the funcionalities it offers first."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First of all, remember that a `DataFrame` is basically a dictionary of `Series` objects. If you want to extract a specific `Series` from a `DataFrame`, just reference it by name in a typical dictionary fashion (i.e. df['name']), or - even simpler - use regular attribute syntax (df.name). If you want more columns, put their names in a list and send it as an argument."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# print the name column from cities_df using dictionary syntax, print it out on the screen\n",
    "###\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#  print the population column using attribute syntax\n",
    "###\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# print the name and tax columns together by using dictionary syntax with a list of keys as an argument. \n",
    "#Notice the result is not a Series anymore, but a DataFrame\n",
    "###\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Similar to indexing issues we have encountered with the `Series` object, we also need to be aware of certain indexing inconsistencies when using `DataFrame`s. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When we use a **single-argument label-based** indexing with a `DataFrame` object, it will default to selecting **columns**. This is what you were doing in the last exercise. However if you put a **boolean list** or an **integer list** as a single indexing argument, you will be selecting **rows**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# print the second and third row from cities_df\n",
    "###\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#print all information about the cities with the average salary greater then 5200 Kn\n",
    "###\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The reason why *Pandas* designer implemented this slightly confusing feature is simply because this type of row-wise selection is extremely common, so the inconsistency is a decided compromise between the ease of use and the \"cleanliness\" of the API. Again, this is a common feature of the `R` programming language, but not something that we usually see in Python."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For a more 'pythonic' approach, you should use the `iloc` and `loc` functions, which you have met when dealing with indexing `Series` objects. They will expect two arguments - first for rows, second for columns, and the functions will expect integer-based and label-based indexing respectively. Interestingly, both functions will readily accept boolean lists for any othe arguments. Also, if you want to mix integer- and label- based indexing (for example you want to index rows by their integer order but columns by their label), you can use the `ix` function. This function is a hybrid of `iloc` and `loc` and it will try to determine what type of indexing you want by the arguments you have provided. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# print the 3rd and 4th row and the 1st and 2nd column from cities_df\n",
    "###\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# print the name and tax of cities where the population is under 200000\n",
    "###\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# print the name of all cities with tax less then 15 (percent) and population higher then 100000\n",
    "###\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Adding rows / columns to a DataFrame"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Adding columns to a data frame is simple - you most commonly perform it using dictionary syntax for adding a new key (df['new'] = 'value'). Your new column can be based on operations performed on existing columns, or you can add a completely new column with its own indexes. The indexes do not need to conform completely with the existing data - in case of missing indexes or new additions, *Pandas* will reshape the data frame accordingly."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# create a new boolean column in cities_df called is_tax_high which will show whether the tax is over 13 percent\n",
    "# use dictionary sintax  (a['key'] = values)\n",
    "###\n",
    "\n",
    "# create a new column in cities_df called \"area\" which will store city areas in km2\n",
    "# imagine you are only aware of the following data: Zagreb has 641 km2, Osijek 169 km2, Dubrovnik 22 km2\n",
    "###\n",
    "\n",
    "# print cities_df\n",
    "###\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you made a mistake, go up and reload `cities_df` again. Alternatively, if you only want to get rid of some columns, simply delete them with Python's `del` function. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Adding rows usually boils down to \"concatenating\" two `DataFrame` objects using the `append` method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# we are creating a new data frame with a few more cities for our dataset\n",
    "# notice we are missing some columns!\n",
    "more_cities_zipcode = [44000, 33000]\n",
    "more_cities_data = {'name': ['Sisak', 'Virovitica'], \n",
    "           'population': [44322, 14688], \n",
    "           'tax': [7, 10],\n",
    "           'area': [423, 179]}\n",
    "more_cities_df = pd.DataFrame(more_cities_data, index = more_cities_zipcode)\n",
    "more_cities_df.index.name = 'zipcode'  \n",
    "\n",
    "# append more_cities_df to cities_df. Store it in a variable called new_cities_df\n",
    "# use the append method\n",
    "###\n",
    "\n",
    "# print out new_cities_df\n",
    "###\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It seems all data is there - but the columns have reordered themselves! Don't worry, we can fix this easily - let's just select all data from our data frame, but put columns in proper order. We can put the result of that selection back to `new_cities_df`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Rorder new_cities_df so columns follow this order: name, population, avg_salary, tax, is_tax_high, area\n",
    "###\n",
    "\n",
    "# print new_cities_df again\n",
    "###\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you did everything right, your final `new_cities_df` data frame needs to look like this:\n",
    "\n",
    "\n",
    "`zipcode        name  population  avg_salary  tax is_tax_high area                                                      \n",
    "10000        Zagreb      790017      6359.0   18      True  641.0\n",
    "51000        Rijeka      128384      5418.0   15      True    NaN\n",
    "21000         Split      167121      5170.0   10     False    NaN\n",
    "31000        Osijek       84104      4892.0   13     False  169.0\n",
    "20000     Dubrovnik       28434      5348.0   10     False   22.0\n",
    "44000         Sisak       44322         NaN    7       Nan  423.0\n",
    "33000    Virovitica       14688         NaN   10       Nan  179.0`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We like the order now, but the data frame seems to be infested with missing values. Let's learn how to deal with them in the next chapter."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr><hr>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Dealing with missing values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Missing values are very common in real world datasets. When a value is missing it can mean a lot of things - maybe there was a mistake in the original data which caused a parsing problem, maybe this data was never collected, was witheld or perhaps is not even applicable for this particular observation.  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When we notice that we have missing values in our datasets, we need to carefully consider the strategy of dealing with such values. The problem with them is that they can skew or invalidate our calculations, or cause further havoc when we further manipulate our data. Missing values in expressions commonly cause more missing values, and even if computations are programmed in such a way they ignore the missing values and only take into account observations or atribute values which are actually present, the result may not be representative of what we wanted to calculate in the first place."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Handling of missing values is actually a very complex issue, with many potential strategies at disposal, some simple, some more advanced. Here, we will focus only on a few of the basic methods, but readers are encouraged to explore and research the more advanced techniques, especially if they expect that handling missing values would be a common task they will have to confront in the future."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Some of the most basic guidelines are:\n",
    "- always check for the missing data. Is there any missing data? In which rows / columns? How prevalent is it?\n",
    "- try to discover the reasons why the data is missing. Is it missing at random or are there specific patterns?\n",
    "- should you ignore the missing data, remove it, or change it to another value?\n",
    " - if you are ignoring missing data - are the functions you plan to use able to gracefully accept missing data without errors? Will the results be repesentative?\n",
    " - if you are removing missing data - should you remove rows, or attributes? Is this removal affecting the nature of the dataset? Is the data you are removing telling you some important information which you need to explore further?\n",
    " - if you are replacing missing data with other values - which values do you choose? Should it be 0, a mean or median of this attribute, a mean or median of only a subset of observations which the observation with missing data belongs to, or something else?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Without delving too deep into these questions, let's move onto exact methods you require to even start getting dirty with missing data. Let's go back to our `new_cities_df` data frame which, as you remember, has some missing data itself."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's first learn how to spot if missing data even exists. For that we can use a data frame method called `isnull`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# try out the isnull method on new_cities_df\n",
    "###\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We received a boolean data frame as a result. Now even though we can explore it visually and check for cells which have `True`, this is not really practical for larger datasets. One neat trick we can do is simply calculate a sum of all elements for this dataframe. *Numpy* has a function for that called `DataFrame.sum`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# print out the sum of new_citis_df.isnull()\n",
    "###\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we clearly see which columns have missing values so we can start planning our strategy."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "One of the simplest things we can do is simply remove all rows with missing values from them. A data frame method for that is called `dropna()`. If we only want to remove rows with missing values in for a subset of columns, we can use the `subset` argument and state a list of column labels which we want removed if they contain missing values. There are other arguments to consider which you can check in the documentation. For now, let's simply annihilate all missing values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# drop all missing values from new_cities_df and store it in a new variable: new_cities_df_scrubbed\n",
    "###\n",
    "\n",
    "# print out new_cities_df_scrubbed\n",
    "###\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This method is ok if we have a large dataset with a very few rows which have missing values, especially if we checked those rows and deemed them truly insignificant. For the `new_cities_df` however this operation was devastating - we lost more than half of our rows."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's think of an alternate strategy. Print out `new_cities_df` again and try to establish a strategy for dealing with missing values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# print out new_cities_df\n",
    "###\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Suppose this was our chosen strategy:\n",
    "- column `is_tax_high` is derived from `tax` column which doesn't have missing values; this means we can simply recalculate it\n",
    "- we can calculate the median of the `avg_salary` column and impute those values instead of the missing ones \n",
    "- we can try to find out city areas from an alternate data source - in this example googling them is simple and easy enough, even though in practice we would probably need to converse with domain experts for the availability of data we're missing"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's fix those missing values!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# copy the new_cities_df data frame into a new variable called new_cities_df_clean\n",
    "# use the copy method of a DataFrame object\n",
    "###\n",
    "\n",
    "# print out new_cities_df_clean\n",
    "###\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# recalculate the is_tex_high column\n",
    "###\n",
    "\n",
    "# print out new_cities_df_clean\n",
    "###\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# suppose we procurred additional data for the area column: area of Rijeka is 44km2, and Split 79 km2\n",
    "# update those values in the data frame \n",
    "###\n",
    "###\n",
    "\n",
    "# print out new_cities_df_clean\n",
    "###\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# finally, change the missing values in the avg_salary column \n",
    "# first calculate the median of this column with the help of Numpy's \"nanmedian\" method (median which ignores NaNs)\n",
    "# you can round the result to the nearest integer using Numpy's \"round\" method\n",
    "# call this value `median_salary`\n",
    "###\n",
    "\n",
    "# put indexes of cells where avg_salary is NaN in a boolean array called missing_salaries\n",
    "###\n",
    "\n",
    "# finally, using the loc method impute the median into appropriate cells\n",
    "###\n",
    "\n",
    "# print out new_cities_df_clean\n",
    "###\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There. We have rid ourselves of missing values completely. Be aware though that the fix involving the salaries might bites us in the long run; we have effectively narrowed down the distribution of salary values as well as entered potentially wrong values for these observations (which would be especially problematic if these observations have values which are in fact very far away from the median). The most important thing to remember is that handling missing values should always be performed with care and forethought, and even what the perceived best solution can still be a compromise of sorts which might effect the results of our analysis in the long run. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr><hr>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Sorting, grouping and aggregation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For our last segment, we will briefly demonstrate how to do two very common tasks when dealing with data frames - sorting by a certain key and aggregating values from our data frame, which may or may not involve spliting our data frame first into logical groupings of observations.\n",
    "\n",
    "The easiest way to sort a data frame is to use the `sort_values` method of the `DataFrame` object. This method takes a number of arguments out of which we can make do with just two: `by`, which takes a column name (or a list of column names) by which we want to sort, and `ascending` which takes a boolean value (or a list of boolean values) which determine should the sorting by a specific column be done in an ascending or descending fashion."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# print the sorted variant of the new_cities_df_clean data frame\n",
    "# sort first by tax (ascending) and then average salary (descending)\n",
    "\n",
    "###\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Grouping and aggregation is a process more commonly known as S-A-C: *Split - Apply - Combine*. It basically means:\n",
    "- *Split* - cut up the data frame based on some criteria\n",
    "- *Apply* - perform an operation on each of the sections gained by splitting\n",
    "- *Combine* - collect all results from the previous step in one resulting data frame"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Users of SQL language will immediately understand this principle since it is one of very common operations done on relational tables, using GROUP BY clause and aggregate functions."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Our `new_cities_df` is pretty small, but still usable to demonstrate grouping and aggregating. To achieve this, we will first add another column called `size`, which will contain a categorical variable with the following values:\n",
    "- \"Small\" - if the city has a population of less than 40,000     \n",
    "- \"Medium\" - if the city has a population between 40,000 and 100,000 (inclusive range from both ends)\n",
    "- \"Large\" - if the city has a population of more than 100,000   "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's create this column first."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# create the column called `size` in new_cities_df_clean and fill it with values according to the above criteria\n",
    "###\n",
    "###\n",
    "###\n",
    "\n",
    "# print out new_cities_df_clean\n",
    "###\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we will create a so-called `group by` object by calling the `DataFrame` method `groupby` and providing the column we want to group on."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# call the method groupby on new_cities_df_clean data frame\n",
    "# provide column 'size' as argument\n",
    "# store this object as a variable called size_groupby\n",
    "\n",
    "###\n",
    "\n",
    "# print out the attribute `groups` from this object\n",
    "###\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see that this object basically consists of groupings of indexes based on our grouping criteria."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we are free to perform aggregation (the apply-combine steps of the S-A-C principle). We simply call one of the provided methods of the `groupby` object, optionally stating which columns we are interested in. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# find out how many members each group has by calling the count method of the group by object\n",
    "# and selecting just the first column of the result\n",
    "###\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# calculate the average area of cities grouped by size by calling the mean method of the groupby object\n",
    "# and selecting the 'area' column of the result\n",
    "\n",
    "###\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With this we conclude our brief introduction to *Pandas*. In our final notebook we will give a quick presentation of how to perform exploratory data analysis on larger datasets using `pandas` and `matplotlib` packages."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr> <hr> <hr>\n",
    "## <font color = \"blue\">Exercises"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "Instead of proiding our own exercises, we will use the opportunity to point to a venture similar to previously mentioned \"100 Numpy exercises\", called **100 Pandas exercises**, available here: <a href = \"#5\">[5]</a>. While this seems to be a work in progress (and the number of exercises isn't even near one hundred yet), the exercises there are a great way to repeat some of the above mentioned concepts and learn someting new.  Also, do not forget to check out *Part 3*, which expands on the subject of *Pandas* DataFrames."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr> <hr> <hr>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Additional resources"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a name=\"1\"></a><A href = \"http://pandas.pydata.org/pandas-docs/stable/api.html#series\">[1]</a> *Series and Data Frame API Reference*, official Pandas documentation, last accessed 2016/09/06"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a name=\"2\"></a><a href = \"http://synesthesiam.com/posts/an-introduction-to-pandas.html\">[2]</a> *An Introduction to Pandas* by Michael Hansen, last accessed 2016/09/06"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a name=\"3\"></a><a href = \"http://www.gregreda.com/2013/10/26/intro-to-pandas-data-structures/\">[3]</a> *Intro to Pandas data structures* by Greg Reda, last accessed 2016/09/07"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a name=\"4\"></a><a href = \"http://pandas.pydata.org/pandas-docs/stable/10min.html\">[4]</a> *10 minutes to Pandas*, official *Pandas* 0.18.1 documentation, last accessed 2016/09/07"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a name=\"5\"></a><a href = \"https://github.com/ajcr/100-pandas-puzzles\">[5]</a> *100 Pandas exercises*, last accessed 2016/09/22</a>"
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python [Root]",
   "language": "python",
   "name": "Python [Root]"
  },
  "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.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}