Chapter 10 - Data Analysis with Pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Analysis with Python (Pandas)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src=\"https://viezeliedjes.files.wordpress.com/2014/09/kbmarskramersceptisch.jpg?w=982&h=760\" align=\"right\" width=250/>In the Data Science community, Python is well known for its excellent data manipulation capabilities (e.g. matrix handling). In this chapter, we will review a number of vital external libraries in this respect (numpy, pandas), which allow scholars to load, manipulate and analyze tabular data. As example data, we will work with data from the [Meertens Tune Collection](http://www.liederenbank.nl/mtc/). We will work with the MTC-FS dataset which consists of 4120 digitally encoded vocal folk songs both from *Onder de groene linde* (2503) and from various related written sources (1617).\n",
    "\n",
    "In this chapter, we will show you some of the basic plotting functionality available in Python. The plots can be embedded in the notebooks by executing the following cell:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The default colors of matplotlib are not the most pretty ones. We can change that using:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import matplotlib\n",
    "matplotlib.style.use('ggplot')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This chapter will introduce you to the basics of the Python Data Analysis Library **Pandas**. The library is very well documented and contains about all the fuctionality you need to work with tabular data and time-indexed data. We begin with importing the library into the Python workspace:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reading Data from a csv / excel file "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pandas provides a simple method to read CSV files. To read the metadata file of the Meertens Tune Collection, execute the following cell:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df = pandas.read_csv(\"data/MTC-FS.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`pandas.read_csv` returns a `DataFrame` object, which is one of the most important data structures in Pandas. Let's have a look at this data structure:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The IPython notebooks work seamlessly with Pandas and display a nicely formatted HTML table. We can access the first, say, 5 rows of this table using a slicing operation:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df[:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "An equivalent, yet slightly better understandable method is to use the `.head` method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "By default `.head` returns the first 5 rows of a DataFrame. The method takes one optional argument `n` with which you can specify how many rows to show:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df.head(n=10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `pandas.read_csv` method takes many optional arguments that allow you to parse all kinds of CSV files (e.g. with different encodings, missing values, different separators etc.). One of the columns in our dataframe contains the date of the recordings. By default, Pandas will parse those columns as string objects. We can specify which columns should be parsed as dates using the following statement:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df = pandas.read_csv(\"data/MTC-FS.csv\", parse_dates=['date_of_recording'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Remember, to quickly access the documentation of a method or function, you can use a question mark, as in:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "pandas.read_csv?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since we will be using many functions such as `.read_csv` it would be convenient not having to type `pandas` all the time. Python allows you to import libraries using an alias, as in:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can use `pd.read_csv` instead of `pandas.read_csv`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = pd.read_csv(\"data/MTC-FS.csv\", parse_dates=['date_of_recording'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Accessing Rows & Columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pandas DataFrames work much like regular Python dictionaries. You can retrieve the contents of a column as follows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df['tunefamily']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If the column name does not contain any spaces, Pandas will turn the column name into an attribute of the DataFrame which can be accessed as follows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df.tunefamily"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To access a particular row of a DataFrame, Pandas specifies to use the method `.ix`, as in:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df.ix[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Q1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Write some code that shows the contents of another column. Tip: you can use `df.columns` to see which column names are available. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# insert your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We have seen the method `.head`. What would be the equivalent method to show the *n* last rows of a DataFrame? Hint: think of a dog. Try printing the *n=20* last rows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# insert your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Selecting Multiple Columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pandas allows you to conveniently select multiple columns in a DataFrame. The syntax is the same as with a single column, but here we provide a list of the columns we want to retrieve:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df[['tunefamily', 'title']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "These objects are reduced DataFrames that behave exactly the same as the orginal DataFrame. We can slice them as follows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df[['tunefamily', 'title']][:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Or we first slice the DataFrame and then select the columns we're interested in:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df[:5][['tunefamily', 'title']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Counting discrete variables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Most columns in the MTC dataset are categorical. Pandas provides the convenient method `.value_counts` which returns how often a particular value of a column occurs. Let's see that in action:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df.tunefamily.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As you can see, the column tunefamily contains many duplicates. We would like to obtain a frequency distribution of the tune families in the collection. This can be easily done using:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df.tunefamily.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To print only the *n* most frequent tune families, we can use:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df.tunefamily.value_counts()[:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It gets even better. We can plot these frequency distributions by simply adding `.plot`!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df.tunefamily.value_counts()[:10].plot(kind='bar')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Q2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Make a bar plot showing the 10 most frequent places where recordings have been made."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# insert your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Make a density plot of the place name frequency distribution. Tip: search for 'density' at: http://pandas.pydata.org/pandas-docs/stable/visualization.html"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# insert you code here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The method `.value_counts` returns an object which is called a `Series` in pandas. DataFrames consist of columns which are Series objects. `Series` have many methods available, such as the `.plot` method. Other methods are: `.sum`, `.mean`, `.median` etc:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "tune_counts = df.tunefamily.value_counts()\n",
    "print(tune_counts.sum())\n",
    "print(tune_counts.mean())\n",
    "print(tune_counts.median())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To get a quick summary of a Series object, use the method `.describe`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "tune_counts.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Q3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the next cell, place your cursor behind the dot and hit TAB to retrieve an overview of the many methods available."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "tune_counts."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Write a line of code which computes the cumulative sum of `tune_counts` and plots it as a line plot. A cumulative sum is a series of partial sums over a given sequence, rather than just the final number in that series (which would be the regular sum)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# insert your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data selection under conditions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Often we are interested in particular rows of a dataframe that satisfy particular conditions. We might be interested in all recordings that were made in Groningen for example, or all recordings before 1980. In the previous chapters you have written a lot of code that involved looping over an iterable and depending on whether some condition held, append each item in that iterable to a new list. In Pandas people generally avoid looping and prefer to use so-called vectorized operations that take care of the looping internally.\n",
    "\n",
    "Say we are interested in all records of which the tune family is equal to `Die schuintamboers 1`. Normally we would write a loop, such as:\n",
    "\n",
    "    results = []\n",
    "    for row in df:\n",
    "        if row.tunefamily == 'Drie schuintamboers 1':\n",
    "            results.append(row)\n",
    "    print(results)\n",
    "\n",
    "(Note that the above code will not actually work, because of the way DataFrames are structured and looped over, but it shows a standard looping approach). In Pandas you can achieve the same thing (and better!) by using the following single line:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "df[df.tunefamily == 'Drie schuintamboers 1']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's break that statement down. The inner statement `df.tunefamily == 'Drie schuintamboers 1'` tests for each item in the column `tunefamily` whether it is equal to `'Drie schuintamboers 1'`. When we execute that seperately, Python returns a Series object with boolean values that are either `True` (if it was a match) or `False` (if the item didn't match):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df.tunefamily == 'Drie schuintamboers 1'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When we *index* our `df` with this Series object, we get just the rows where the condition holds."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Q4"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Write a line of code that returns a new dataframe in which all recordings are from Groningen."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# insert your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What is the tunefamily that is most often attested in Amsterdam? (Tip: remember the value_counts method you can call on (filtered) DataFrames. Also, Series objects have a method called `argmax` that returns the argument corresponding to a maximum value.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# insert your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can easily select data on the basis of multiple conditions. Here is an example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "in_Assen = df.place_of_recording == 'Assen'\n",
    "is_Suze = df.tunefamily == 'Suze mien lam'\n",
    "df[in_Assen & is_Suze]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Just to show you that the returned object is another DataFrame, we select two columns to construct a reduced DataFrame:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df[in_Assen & is_Suze][['filename', 'place_of_recording']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Q5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When we look at all values in `df.place_of_recording` we notice that many values are missing (NaN values). "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df.place_of_recording"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can use the method `.isnull` (e.g. on a column) to select all items that are NaN. Similarly, the method `.notnull` is to select all items that are not NaN. Write a line of code that returns a new DataFrame in which for all records the place of recording is known."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# insert your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## TimeSeries"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pandas is a library with tremendous functionality for tabular data. Another key feature of the library is the way it can work with time series. The MTC contains a column `date_of_recording` which provides for most but not all items the date at which the recording was made. In the following analyses, we would like to work with data for which the date of recording is known. We create the corresponding DataFrame as follows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df = df[df.date_of_recording.notnull()]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To conveniently work with time series, we set the index of the dataframe (i.e. the labels of the rows) to hold the contents of the column `date_of_recording`. Note that after this operation, the column `date_of_recording` is no longer part of the dataframe but only as an index."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df = df.set_index(\"date_of_recording\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The Index of our new `df` is now of the type `DatetimeIndex`. (If not, the CSV was not read in with the argument `parse_dates=['date_of_recording']`)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "type(df.index)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This data structure provides a vast amount of methods and functions to manipulate and index the data. For example, we can retrieve the year of each recording as follows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df.index.year"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Or the day:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df.index.day"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Or the week:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df.index.week"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Q6"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Write a single line of code which returns a new DataFrame consisting of all recordings made at January first (independent of the year). Tip: type `df.index.` followed by TAB to scan through an overview of all the available methods."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# insert your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Add a column to a Dataframe"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pandas makes it really easy to add new columns to a DataFrame. In the following cell we add a column containing the week number at which the recording was made."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df['week'] = df.index.week"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df.week[:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Q7"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Add a column `year` to the DataFrame which contains for each record the year of recording."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# insert your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Grouping data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Often we would like to group our data on the basis of some criterium and perform operations on those groups. We could, for example, group all recordings on the basis of where they were recorded. Pandas provides the convenient (though sometimes challenging) method `.groupby`. It works as follows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "grouped = df.groupby('place_of_recording')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "grouped"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A `DataFrameGroupBy` object consists of several smaller DataFrames per group. We grouped the original dataframe on the key `place_of_recording` and therefore the `grouped` object contains a DataFrame for each unique place of recording. \n",
    "\n",
    "We can use the method `.size` to assess the size (i.e. number of records) of each group:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "grouped.size()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When we add the method `.sort_values` and specify that we want the sorting to be done in descending order we obtain the following Series:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "grouped.size().sort_values(ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Q8"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Just to recap, compute the max, min, mean, and median number of items per group."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# insert your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Grouped objects are extremely helpful when we want to compute a frequency distribution for each unique item of a particular column. Say we would like to investigate the frequency distribution of tune families per place of recording. Given that we have created a grouped object with `place_of_recording` as key, all we need to do is the following:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "grouped.tunefamily.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's make it slightly more complex. In the previous example we created a grouped object using a single key. We can also specify multiple keys, as in:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "grouped = df.groupby(['place_of_recording', 'singer_id_s'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This produces the following data structure:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "grouped.tunefamily.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Q9"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Make a plot that visualizes for each year how many recordings were made. Tip: first create a grouped object."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# insert your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## String operations"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "One of my favourite functionalities of Pandas is the way it allows you to do vectorized string operations. These operations can be useful if you want to select particular string cells in your dataframe or when you want to manipulate / correct the data. Let's have a look at some of the string data in the MTC:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "firstlines = df.firstline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "firstlines[:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, `firstlines` is a Series corresponding to the firstline column, which contains strings. We can access the string methods of a column with `.str`. In the following cell, place your cursor after the last dot hit TAB:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "firstlines.str."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As you can see, almost all regular string operations are available. The neat thing is that if you call a method like `.upper`, the method will be automatically applied to all items in the series (this is referred to as vectorized operations). Let's see that in action:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "firstlines.str.upper().head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The method `.contains` is particularly useful if you want to extract all strings that contain a particular substring. We could, for example, search for the string `boom` to extract all records that mention a tree in the first lines:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "firstlines.str.contains('boom').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The method `.contains` returns a boolean series which we can use to index the dataframe:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df[firstlines.str.contains('boom')]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To give another example, let's search for all records of which the first line mentions the word `zomer` (summer):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "is_summer = firstlines.str.contains('zomer')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "is_summer.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As you can see, this returns a boolean Series object. Note that the original `DatetimeIndex` is still in place. We can use that to conveniently plot all occurrences over the years:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "is_summer.plot()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When we index `firstlines` with `is_summer` we retrieve a new Series object consisting of all records that mention the word `zomer` in their first line:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "summer_lines = firstlines[is_summer]\n",
    "summer_lines"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Q10"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Write some code that computes in which quarter of the year most songs about summer are recorded. Tip: use the `.index` attribute and a `Counter` object from the `collections` library."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# insert your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercises"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You now should have a basic understanding of some important concepts in the Pandas library. The library contains many more interesting functions and methods and it takes quite some time to master them all. The best way to become more confident in using the library is by working with your own data and try to do some basic analyses. \n",
    "\n",
    "The following exercises are to give some more practice. Some problems will be tough, and you cannot always rely on the information provided in this chapter. This is something you'll have to get used to. Don't worry, visit the documentation website (see http://pandas.pydata.org/pandas-docs/version/0.17.0/), Google for particular problems or ask questions on the Slack channel. Asking for help is all part of learning a new library. \n",
    "\n",
    "In the following exercises we begin with working on the Dative alternation dataset of Bresnan et al. (2007). In English, the recipient can be realized either as an Noun Phrase (NP), *John gave Mary the book*, or as a Prepositional Phrase (PP), *John gave the book to Mary*. Bresnan et al. (2007) studied several factors that influence the realization of the dative. The complete dataset of Bresnan et al. (2007) is included in the `data` directory under `data/dative.csv`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Use Pandas to read the dataset as a DataFrame:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "dative = # insert your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Inspect the first 10 lines of the dataframe:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# insert your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The dataset provides for each entry the verb that was used. Create a Series object that counts how often each verb is used:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# insert your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Plot the ten most frequent verbs as a pie chart:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# insert your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The realization of the recipient (NP or PP) is stored in the column `RealizationOfRecipient`. How many NP realizations are there and how many PP realizations?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# insert your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The recipients can be either animate or inanimate. How many PP realizations are inanimate? Tip: use `pd.crosstab`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# insert your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Make a single bar plot that visualizes for both PP and NP realizations of the recipient how often they are animate or inanimate. Tip: use the crosstab method of the previous exercise."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# insert your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Sometimes it is convenient to sort a dataframe on a particular column. Write some code that sorts the `dative` dataframe on the realization of the recipient."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# insert your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now sort the data on the length of the recipient in **descending** order:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# insert your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Write some code that prints the mean length of the recipient for both animate and inanimate recipients."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# insert your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Make a boxplot that visualizes for each realization of the recipient (i.e. NP or PP) the length of the theme. Tip: make use of the method `dative.boxplot`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# insert your code here"
   ]
  }
 ],
 "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.5.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}