06 - Pandas essential - instructor -with answer.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pandas library"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This library is again based on the [numpy]() library that we discuss in a previous lesson. \n",
    "It provides python with a new object which allows us to work with \"relational\" or \"labeled\" data in an easy way.\n",
    "It provides a similar access to data coming from Database or Spreadsheet as the one that you can find in languages like R.\n",
    "\n",
    "Pandas is designed more for datascience than pure numerical analysis but the tools can be combined together.\n",
    "\n",
    "The library is providing input/output tools with which it is possible to open/save data from MS Excel, CSV or HDF5 files.\n",
    "\n",
    "As usual we are going to import the library..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will later plot some data so we are importing the library matplotlib"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%matplotlib inline\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And we will move the current directory to where our data files are located:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cd data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%more gapminder_gdp_europe.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pandas.read_csv?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Inside this directory you will find a file called *gapminder_gdp_europe.csv* that is from the software carpentry project. This file has one column which contains the name of the countries and the other one contains the value of GDP for a certain year. \n",
    "\n",
    "Using the 'index_col' parameter, we will tell pandas to use the columns 'country' as our row labels."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = pandas.read_csv('gapminder_gdp_europe.csv', \n",
    "                       index_col='country')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can check the type of this object:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "type(data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We now have a new object type called DataFrame which contains our data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If it seems to be an array-like object but be aware that you cannot access the elements as with numpy array:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "data[0,0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Accessing elements, rows and columns using iloc and loc"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To get access to the first row, first columns element, we can use the numpy indexes using the method **iloc** followed by the position (starting by 0 as usual in python):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(data.iloc[0,0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "or we can use the keys with the method **loc**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(data.loc[\"Albania\", \"gdpPercap_1952\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It is possible to obtain all the information related to a country (i.e. a row):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(data.loc[\"Albania\", :])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "or per GDP per year (i.e. per columns:)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(data.loc[:, \"gdpPercap_1952\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It is possible to slice it:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(data.loc['Italy':'Poland', 'gdpPercap_1962':'gdpPercap_1972'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And we can do some numerical operations like finding the maximum of GDP for a slice:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(data.loc['Italy':'Poland', 'gdpPercap_1962':'gdpPercap_1972'].max())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "or the minimum value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(data.loc['Italy':'Poland', 'gdpPercap_1962':'gdpPercap_1972'].min())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can create variable which will contains a subset of the data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "subset = data.loc['Italy':'Poland', 'gdpPercap_1962':'gdpPercap_1972']\n",
    "print('Subset of data:\\n', subset)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can select items using certain criteria, in this case: if the GDP is greater than 10,000. \n",
    "\n",
    "One way to do this is to create a mask (similar to the numpy masked array) and we use it to only print the items which validate the condition"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mask = subset > 10000\n",
    "print(subset[mask])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can ask pandas to provide a statistical description of the data using the method **describe**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(subset[subset > 10000].describe())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using the pandas plot function, it is possible to plot the change of GDP for a specific country directly from the selected column:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.loc['Sweden'].plot()\n",
    "plt.xticks(rotation=90)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you want to analyse the data with a histogram where every columns will represent a country. You can transpose the data. Here we are transposing for the first three countries:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data[0:3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data[0:3].T"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And we then plot the histogram. \n",
    "\n",
    "For people use to R, it is possible to adjust the properties of the plot to be similar to the **ggpplot** package use in R:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.style.use('ggplot')\n",
    "data[0:3].T.plot(kind='bar')\n",
    "plt.xticks(rotation=90)\n",
    "plt.ylabel('GDP per capita')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div style='background:#B1E0A8; padding:10px 10px 10px 10px;'>\n",
    "<H2> Challenges </H2>\n",
    "<li>\n",
    "Create two variables 'gdp_sweden' and 'gdp_iceland' which contain the gdp for the Sweden and Iceland respectively.\n",
    "</li>\n",
    "<li>\n",
    "Plot the change in GDP per year.\n",
    "</li>\n",
    " <ol>\n",
    " </div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "gdp_sweden = data.loc['Sweden']\n",
    "gdp_iceland = data.loc['Iceland']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "years = data.columns.str.strip('gdpPercap_')\n",
    "plt.plot(years, gdp_sweden, 'b-', label='Sweden')\n",
    "plt.plot(years, gdp_iceland, 'g-', label='Iceland')\n",
    "\n",
    "# Create legend.\n",
    "plt.legend(loc='upper left')\n",
    "plt.xlabel('Year')\n",
    "plt.ylabel('GDP per capita ($)')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div style='background:#B1E0A8; padding:10px 10px 10px 10px;'>\n",
    "<H2> Challenges </H2>\n",
    "\n",
    " <ol>\n",
    " <li>Read the data of the file 'agelist.txt' using *python* only (not pandas) and create a dictionary which contains this data (the key should be the name)\n",
    " </li>\n",
    " <li>\n",
    " Read the same file, this time using *pandas* and create a dataframe df with the data.\n",
    " Hint: you cannot use read_csv function but pandas does provide a fixed formed reader (read_fwf)\n",
    " </li>\n",
    " \n",
    " </div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('data/agelist.txt') as f:\n",
    "    data = f.readlines()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = data[1:]\n",
    "d = {}\n",
    "for i, line in enumerate(data):\n",
    "    line = line.strip().split()\n",
    "    d[line[0]] = int(line[1])\n",
    "print(d)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas \n",
    "df = pandas.read_fwf('data/agelist.txt')  # fixed width formatted"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Name']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df['Name'] == 'Bob']['Age']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Name']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = pandas.read_fwf('agelist.txt', index_col='Name')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2"
   ]
  }
 ],
 "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.6.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}