eJ09.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 9. Sorting, filtering and frequency analysis\n",
    "\n",
    "In this lecture we demonstrate:\n",
    "1. how to sort the rows of the table;\n",
    "2. how to filter out the rows of a table which satisfy some criterion; and\n",
    "3. how to perform frequency analysis on the rows of a table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9.1. Sorting\n",
    "\n",
    "Recall that to sort a sequence means to order its members in an increasing (or decreasing) way. To sort the rows of a table means to rearrange the rows so that the values in a particular column are sorted. We shall now show how to quickly sort tables using the _pandas_ library. Let us first load the library:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Consider the example we have already analyzed in previous lectures:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "students = [[\"Anne\",    \"f\", 13, 46, 160],\n",
    "            [\"Ben\",     \"m\", 14, 52, 165],\n",
    "            [\"Colin\",   \"m\", 13, 47, 157],\n",
    "            [\"Diana\",   \"f\", 15, 54, 165],\n",
    "            [\"Ethan\",   \"m\", 15, 56, 163],\n",
    "            [\"Fred\",    \"m\", 13, 45, 159],\n",
    "            [\"Gloria\",  \"f\", 14, 49, 161],\n",
    "            [\"Hellen\",  \"f\", 15, 52, 164],\n",
    "            [\"Ian\",     \"m\", 15, 57, 167],\n",
    "            [\"Jane\",    \"f\", 13, 45, 158],\n",
    "            [\"Kate\",    \"f\", 14, 51, 162]]\n",
    "students_df = pd.DataFrame(students)\n",
    "students_df.columns=[\"Name\", \"Sex\", \"Age\", \"Weight\", \"Height\"]\n",
    "students_ix=students_df.set_index(\"Name\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The table looks like this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "students_ix"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The function `sort_values` sorts the rows of a table so that the values in a particular column are sorted. The name of the column that we want sorted is sometimes called the _sorting criterion_. We specify the sorting criterion using the option `by` like this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "students_by_height = students_ix.sort_values(by=\"Height\")\n",
    "students_by_height"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The default _sorting order_ is _ascending_, which means that the rows of the table will be rearranged so that the values in the column \"Height\" increase. If we wish to sort the table so that the first row contains the highest student we have to invoke the function with an additional option `ascending=False`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "students_by_height = students_ix.sort_values(by=\"Height\", ascending=False)\n",
    "students_by_height"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, let us visualize the height and the weight of the students in the sorted table (note that the table is sorted by height, but that does not mean that the remaining columns are also sorted):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "plt.figure(figsize=(10,5))\n",
    "plt.bar(students_by_height.index, students_by_height[\"Height\"], label=\"Height\")\n",
    "plt.bar(students_by_height.index, students_by_height[\"Weight\"], label=\"Weight\")\n",
    "plt.title(\"The height and the weight of students in the group\")\n",
    "plt.legend()\n",
    "plt.show()\n",
    "plt.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9.2. Filtering data\n",
    "\n",
    "Quite often we have to go through a table and single out the rows that satisfy some criterion. For example, if we wish to focus on the data about females only, we can filter out these rows and package them into a new table like this:\n",
    "\n",
    "    girls = students_ix[students_ix.Sex == \"f\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "girls = students_ix[students_ix.Sex == \"f\"]\n",
    "girls"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This will sigle out students with more than 50 kg:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "over_50kg = students_ix[students_ix.Weight > 50]\n",
    "over_50kg"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also combine criteria. For example if want to focus on boys with not more than 55 kg, we have to combine two criteria:\n",
    "\n",
    "    Weight <= 55  and  Sex == \"m\".\n",
    "\n",
    "The `pandas` library uses the symbol `&` to combine criteria in cases both have to be satisfied:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "boys_55kg = students_ix[(students_ix.Weight <= 55) & (students_ix.Sex == \"m\")]\n",
    "boys_55kg"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let us visualize the height and weight of these boys:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.figure(figsize=(6,6))\n",
    "plt.bar(boys_55kg.index, boys_55kg[\"Height\"], label=\"Height\")\n",
    "plt.bar(boys_55kg.index, boys_55kg[\"Weight\"], label=\"Weight\")\n",
    "plt.title(\"Boys with not more than 55 kg\")\n",
    "plt.legend()\n",
    "plt.show()\n",
    "plt.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The file _data/LongestRiversOfEurope.csv_ contains the list of the tlongest 25 rivers of Europe. This table has five columns:\n",
    "\n",
    "- River = name of the river,\n",
    "- Length = length of the river in km,\n",
    "- Countries = a string with all the countries the river flows through,\n",
    "- Mouth = the sea/ocean/lake the river flows into, and\n",
    "- Via = \"(directly)\" if the river flows directly into the sea/ocean/lake; otherwise the name of the river through which it flows into the sea/ocean/lake.\n",
    "\n",
    "Let us load the file into a DataFrame, index it by column \"River\" and display the first few lines of the table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "rivers = pd.read_csv(\"data/LongestRiversOfEurope.csv\").set_index(\"River\")\n",
    "rivers.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The list of countries the river flows through is represented by a string. If we wish to filter out all the rivers flowing through, say, Germany, we have to check whether the string stored in `Countries` contains \"Germany\" as a substring:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "rivers[rivers.Countries.str.contains(\"Germany\")]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9.3. Frequency analysis\n",
    "\n",
    "Recall that _frequency analysis_ boils down to counting how many times each value occurs in the sequence. The `pandas` library has a handy function `value_counts` that can to that for us. For example, let us count boys and girls in the above table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "students_ix[\"Sex\"].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So `value_counts` found that in this table there are 6 rows having \"f\" in column \"Sex\" and 5 rows having \"m\" in column \"Sex\". Therefore, there are 6 girls and 5 boys in the group.\n",
    "\n",
    "When it comes to the age of students in the group we have the following situation:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "students_ix[\"Age\"].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are 4 students aged 15, 4 students aged 13 and 3 students aged 14.\n",
    "\n",
    "We can put the outcome of the `value_counts` function in a variable for further reference:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "freq = students_ix[\"Sex\"].value_counts()\n",
    "freq"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The we can easily get access to the items that have been identified in the column \"Sex\" and their frequences:\n",
    "\n",
    "    freq.index\n",
    "    \n",
    "gives the list of items, while\n",
    "    \n",
    "    freq.values\n",
    "\n",
    "gives their frequences."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"Items identified in the column:\", freq.index)\n",
    "print(\"Their frequences:\", freq.values)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The structure of the group according to the gender can be visualized like this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "freq = students_ix[\"Sex\"].value_counts()\n",
    "plt.figure(figsize=(6,6))\n",
    "plt.pie(freq.values, labels=freq.index)\n",
    "plt.title(\"The structure of the group by gender\")\n",
    "plt.show()\n",
    "plt.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Analogously, we can visualize the structure of the group by age:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "freq = students_ix[\"Age\"].value_counts()\n",
    "plt.figure(figsize=(6,6))\n",
    "plt.pie(freq.values, labels=freq.index)\n",
    "plt.title(\"The structure of the group by age\")\n",
    "plt.show()\n",
    "plt.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As a final example let us find the distribution of countries by continents. At the following URL\n",
    "\n",
    "    https://raw.githubusercontent.com/cs109/2014_data/master/countries.csv\n",
    "    \n",
    "you can find a publicly available list of all the countries in the world. Let us start by loading the list into a DataFrame:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "countries = pd.read_csv(\"https://raw.githubusercontent.com/cs109/2014_data/master/countries.csv\")\n",
    "countries.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The number of countries per continen can now be easily determined:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "countries[\"Region\"].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "and visualized:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "by_continents = countries[\"Region\"].value_counts()\n",
    "plt.figure(figsize=(8,8))\n",
    "plt.pie(by_continents.values, labels=by_continents.index)\n",
    "plt.title(\"The distributiuon of countries by continents\")\n",
    "plt.show()\n",
    "plt.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9.4. Exercises"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise 1.** The file _data/LongestRiversOfEurope.csv_ contains the list of the tlongest 25 rivers of Europe. This table has five columns:\n",
    "\n",
    "- River = name of the river,\n",
    "- Length = length of the river in km,\n",
    "- Countries = a string with all the countries the river flows through,\n",
    "- Mouth = the sea/ocean/lake the river flows into, and\n",
    "- Via = \"(directly)\" if the river flows directly into the sea/ocean/lake; otherwise the name of the river through which it flows into the sea/ocean/lake.\n",
    "\n",
    "_(a)_ Load the file into a DataFrame, index it by the column \"River\" and display the first few lines of the table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " \n",
    "_(b)_ Sort the table by length so that the longest river in Europe is on the top."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "_(c)_ How many of the longest 25 rivers of Europe flow through Russia, how many through Germany, and how many through Liechtenstein?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "_(d)_ For a new table that consists only of those rivers that flow directly into a sea/ocean/lake, get rid of the row \"Via\" and write the table into the file _data/FLowDirectly.csv_"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise 2.** Here are nutritive facts about some sea food:\n",
    "\n",
    "| Product (100g) | Nutritive value (kcal) | Carbohydrates (g) | Proteins (g) | Fats (g) |\n",
    "|--|--|--|--|--|\n",
    "|Tuna|116|0|26|1|\n",
    "|Hake|88|0|17.2|0.8|\n",
    "|Trout|119|0|18|5|\n",
    "|Salmon|116|0|20|3.5|\n",
    "|Mackerel|205|0|19|14|\n",
    "|Sardines|135|0|18|5|\n",
    "|Hеrring|158|0|18|9|\n",
    "|Cod|82|0|18|0.7|\n",
    "|Catfish|95|0|16.4|2.8|\n",
    "|Carp|127|0|17.6|5.6|\n",
    "|Gilthead|115|0|16.5|5.5|\n",
    "|Eel|184|0|18.4|11.7|\n",
    "|Shrimp|106|1|20|2|\n",
    "|Mussels|86|4|12|2|\n",
    "|Prawns|71|1|13|1|\n",
    "|Squid|92|3|15.6|1.3|\n",
    "|Octopus|81|0|16.4|0.9|\n",
    "|Lobster|112|0|20|1.5|\n",
    "\n",
    "This table can be represented as a list like this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sea_food = [\n",
    "  [\"Tuna\", 116, 0, 26, 1],\n",
    "  [\"Hake\", 88, 0, 17.2, 0.8],\n",
    "  [\"Trout\", 119, 0, 18, 5],\n",
    "  [\"Salmon\", 116, 0, 20, 3.5],\n",
    "  [\"Mackerel\", 205, 0, 19, 14],\n",
    "  [\"Sardines\", 135, 0, 18, 5],\n",
    "  [\"Hеrring\", 158, 0, 18, 9],\n",
    "  [\"Cod\", 82, 0, 18, 0.7],\n",
    "  [\"Catfish\", 95, 0, 16.4, 2.8],\n",
    "  [\"Carp\", 127, 0, 17.6, 5.6],\n",
    "  [\"Gilthead\", 115, 0, 16.5, 5.5],\n",
    "  [\"Eel\", 184, 0, 18.4, 11.7],\n",
    "  [\"Shrimp\", 106, 1, 20, 2],\n",
    "  [\"Mussels\", 86, 4, 12, 2],\n",
    "  [\"Prawns\", 71, 1, 13, 1],\n",
    "  [\"Squid\", 92, 3, 15.6, 1.3],\n",
    "  [\"Octopus\", 81, 0, 16.4, 0.9],\n",
    "  [\"Lobster\", 112, 0, 20, 1.5]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "_(a)_ Turn this list into a DataFrame, name the colums appropriately and index the table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "_(b)_ Sort the table by nutritive value and visualize the data using a bar chart."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "_(c)_ Make the frequency analysis of this table according to the amount of carbohydrates and visualize you result by a pie chart."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "_(d)_ Put all the sea food with no carbohydrates and less that 10 g of fats per 100 g into a new table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise 3.** At a PE lecture the students were practising long jumps. Each student had three tries and the data is collected in _LongJump.csv_ available in the folder _data_. The first row of the file is the header.\n",
    "\n",
    "_(a)_ Load this file into a _DataFrame_."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "_(b)_ Add a new column \"Max\" to the table and for each student compute the best jump."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "_(c)_ Sort the table by \"Max\" and display the first five rows of the sorted table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "_(d)_ SIngle out those students who have made at least one foul jump. Foul jumps are recorded as 0."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise 4.** The following list contains some basic information about a group of students: last name, first name, student ID, the class they attend and the average of their marks:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "student = [\n",
    "    [\"Peterson\",    \"Peter\",   \"0308003800019\", \"m\", 8, 4.52],\n",
    "    [\"Janesdottir\", \"Jane\",    \"1210003805026\", \"f\", 8, 5.00],\n",
    "    [\"Annesdottir\", \"Anne\",    \"1105004805019\", \"f\", 7, 4.11],\n",
    "    [\"Verasdottir\", \"Vera\",    \"2901005705011\", \"f\", 6, 5.00],\n",
    "    [\"Georgsson\",   \"George\",  \"1504005700012\", \"m\", 6, 3.12],\n",
    "    [\"Michaelson\",  \"Michael\", \"1506004400056\", \"m\", 7, 2.51],\n",
    "    [\"Michaelson\",  \"Peter\",   \"1506004400057\", \"m\", 7, 2.48],\n",
    "    [\"Smith\",       \"Nathan\",  \"2109003800046\", \"m\", 8, 3.58],\n",
    "    [\"Smith\",       \"Jane\",    \"2109003805021\", \"f\", 8, 4.21]\n",
    "]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "_(a)_ Turn this into a _DataFrame_."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "_(b)_ Visualize the gender structure of the group by a pie chart."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "_(c)_ Visualize the age structure of the group by a pie chart (base your analysis on the class the student attends)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "_(d)_ Which column is the best choice for an index column?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "_(e)_ Put all the students of the 8th grade into a new table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "_(f)_ Put all the boys whose average mark is less that 4.50 into a new table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Zadatak 6.** At the following URL:\n",
    "\n",
    "    https://raw.githubusercontent.com/resbaz/r-novice-gapminder-files/master/data/gapminder-FiveYearData.csv\n",
    "\n",
    "you can find a publicly available table with the list of all the countries in the world and some parameters of the economic development for each country. The table has the six columns:\n",
    "\n",
    "* country\n",
    "* year = the year in which the economic parameters were computed\n",
    "* pop = population\n",
    "* continent\n",
    "* lifeExp = life expextancy\n",
    "* gdpPercap = GDP per capitem\n",
    "\n",
    "_(a)_ Load this table into a _DataFrame_."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "_(b)_ Put all the data that refers to your country into a new table (use a filtering criterion like this: `table[table.country == \"Serbia\"]`) and write it to a file."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "_(c)_ Display the change of the life expectancy of your country in time by a line chart."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "_(d)_ Display the change in GDP in your country by a bar chart."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}