{ "nbformat": 4, "nbformat_minor": 0, "metadata": { "colab": { "name": "data-analysis-v1.ipynb", "provenance": [], "collapsed_sections": [], "toc_visible": true }, "kernelspec": { "name": "python3", "display_name": "Python 3" } }, "cells": [ { "cell_type": "markdown", "metadata": { "id": "FG17p6IUVggd" }, "source": [ "# Introduction to Data Analytics\n", "\n", "For this session, we are using Pandas, which is a Python library for data analysis. You can [read more about Pandas](https://pandas.pydata.org/about/index.html).\n" ] }, { "cell_type": "markdown", "metadata": { "id": "ug5UIGca5-uu" }, "source": [ "\n", "\n", "## Part 1: Introducing the Data Frame\n", "\n", "In Pandas, data is held in a Dataframe, which is like a spreadsheet or database table\n", " * Each columns has a name\n", " * Each row contains data from one 'individual'\n", "\n", "In this part you will learn how to:\n", "\n", " 1. Import the Pandas library\n", " 1. Open a CSV file and create a dataframe\n", " 1. Have a look at the data in the dataframe\n", "\n", "\n", " ### Import the pandas library\n", " We use Python's 'import as' so that we have a short name. Since pandas is a very large library, it is probably not a good idea to use on import without using `as` to give a name.\n", "\n", " `import * from pandas`" ] }, { "cell_type": "code", "metadata": { "id": "LZdn4_nC7mBS" }, "source": [ "import pandas as pd" ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": { "id": "4jj3seHS7siq" }, "source": [ "### Task 1.1 Loading a CSV file\n", "The CSV file can be loaded from the PC you are using. Check you have downloaded it somewhere.\n", "\n", "The data is about the country of birth by Borough, age group and sex. It is from the 2011 census, so not up to date.\n" ] }, { "cell_type": "code", "metadata": { "id": "7hOKM8sm8bR1", "colab": { "resources": { "http://localhost:8080/nbextensions/google.colab/files.js": { "data": "Ly8gQ29weXJpZ2h0IDIwMTcgR29vZ2xlIExMQwovLwovLyBMaWNlbnNlZCB1bmRlciB0aGUgQXBhY2hlIExpY2Vuc2UsIFZlcnNpb24gMi4wICh0aGUgIkxpY2Vuc2UiKTsKLy8geW91IG1heSBub3QgdXNlIHRoaXMgZmlsZSBleGNlcHQgaW4gY29tcGxpYW5jZSB3aXRoIHRoZSBMaWNlbnNlLgovLyBZb3UgbWF5IG9idGFpbiBhIGNvcHkgb2YgdGhlIExpY2Vuc2UgYXQKLy8KLy8gICAgICBodHRwOi8vd3d3LmFwYWNoZS5vcmcvbGljZW5zZXMvTElDRU5TRS0yLjAKLy8KLy8gVW5sZXNzIHJlcXVpcmVkIGJ5IGFwcGxpY2FibGUgbGF3IG9yIGFncmVlZCB0byBpbiB3cml0aW5nLCBzb2Z0d2FyZQovLyBkaXN0cmlidXRlZCB1bmRlciB0aGUgTGljZW5zZSBpcyBkaXN0cmlidXRlZCBvbiBhbiAiQVMgSVMiIEJBU0lTLAovLyBXSVRIT1VUIFdBUlJBTlRJRVMgT1IgQ09ORElUSU9OUyBPRiBBTlkgS0lORCwgZWl0aGVyIGV4cHJlc3Mgb3IgaW1wbGllZC4KLy8gU2VlIHRoZSBMaWNlbnNlIGZvciB0aGUgc3BlY2lmaWMgbGFuZ3VhZ2UgZ292ZXJuaW5nIHBlcm1pc3Npb25zIGFuZAovLyBsaW1pdGF0aW9ucyB1bmRlciB0aGUgTGljZW5zZS4KCi8qKgogKiBAZmlsZW92ZXJ2aWV3IEhlbHBlcnMgZm9yIGdvb2dsZS5jb2xhYiBQeXRob24gbW9kdWxlLgogKi8KKGZ1bmN0aW9uKHNjb3BlKSB7CmZ1bmN0aW9uIHNwYW4odGV4dCwgc3R5bGVBdHRyaWJ1dGVzID0ge30pIHsKICBjb25zdCBlbGVtZW50ID0gZG9jdW1lbnQuY3JlYXRlRWxlbWVudCgnc3BhbicpOwogIGVsZW1lbnQudGV4dENvbnRlbnQgPSB0ZXh0OwogIGZvciAoY29uc3Qga2V5IG9mIE9iamVjdC5rZXlzKHN0eWxlQXR0cmlidXRlcykpIHsKICAgIGVsZW1lbnQuc3R5bGVba2V5XSA9IHN0eWxlQXR0cmlidXRlc1trZXldOwogIH0KICByZXR1cm4gZWxlbWVudDsKfQoKLy8gTWF4IG51bWJlciBvZiBieXRlcyB3aGljaCB3aWxsIGJlIHVwbG9hZGVkIGF0IGEgdGltZS4KY29uc3QgTUFYX1BBWUxPQURfU0laRSA9IDEwMCAqIDEwMjQ7CgpmdW5jdGlvbiBfdXBsb2FkRmlsZXMoaW5wdXRJZCwgb3V0cHV0SWQpIHsKICBjb25zdCBzdGVwcyA9IHVwbG9hZEZpbGVzU3RlcChpbnB1dElkLCBvdXRwdXRJZCk7CiAgY29uc3Qgb3V0cHV0RWxlbWVudCA9IGRvY3VtZW50LmdldEVsZW1lbnRCeUlkKG91dHB1dElkKTsKICAvLyBDYWNoZSBzdGVwcyBvbiB0aGUgb3V0cHV0RWxlbWVudCB0byBtYWtlIGl0IGF2YWlsYWJsZSBmb3IgdGhlIG5leHQgY2FsbAogIC8vIHRvIHVwbG9hZEZpbGVzQ29udGludWUgZnJvbSBQeXRob24uCiAgb3V0cHV0RWxlbWVudC5zdGVwcyA9IHN0ZXBzOwoKICByZXR1cm4gX3VwbG9hZEZpbGVzQ29udGludWUob3V0cHV0SWQpOwp9CgovLyBUaGlzIGlzIHJvdWdobHkgYW4gYXN5bmMgZ2VuZXJhdG9yIChub3Qgc3VwcG9ydGVkIGluIHRoZSBicm93c2VyIHlldCksCi8vIHdoZXJlIHRoZXJlIGFyZSBtdWx0aXBsZSBhc3luY2hyb25vdXMgc3RlcHMgYW5kIHRoZSBQeXRob24gc2lkZSBpcyBnb2luZwovLyB0byBwb2xsIGZvciBjb21wbGV0aW9uIG9mIGVhY2ggc3RlcC4KLy8gVGhpcyB1c2VzIGEgUHJvbWlzZSB0byBibG9jayB0aGUgcHl0aG9uIHNpZGUgb24gY29tcGxldGlvbiBvZiBlYWNoIHN0ZXAsCi8vIHRoZW4gcGFzc2VzIHRoZSByZXN1bHQgb2YgdGhlIHByZXZpb3VzIHN0ZXAgYXMgdGhlIGlucHV0IHRvIHRoZSBuZXh0IHN0ZXAuCmZ1bmN0aW9uIF91cGxvYWRGaWxlc0NvbnRpbnVlKG91dHB1dElkKSB7CiAgY29uc3Qgb3V0cHV0RWxlbWVudCA9IGRvY3VtZW50LmdldEVsZW1lbnRCeUlkKG91dHB1dElkKTsKICBjb25zdCBzdGVwcyA9IG91dHB1dEVsZW1lbnQuc3RlcHM7CgogIGNvbnN0IG5leHQgPSBzdGVwcy5uZXh0KG91dHB1dEVsZW1lbnQubGFzdFByb21pc2VWYWx1ZSk7CiAgcmV0dXJuIFByb21pc2UucmVzb2x2ZShuZXh0LnZhbHVlLnByb21pc2UpLnRoZW4oKHZhbHVlKSA9PiB7CiAgICAvLyBDYWNoZSB0aGUgbGFzdCBwcm9taXNlIHZhbHVlIHRvIG1ha2UgaXQgYXZhaWxhYmxlIHRvIHRoZSBuZXh0CiAgICAvLyBzdGVwIG9mIHRoZSBnZW5lcmF0b3IuCiAgICBvdXRwdXRFbGVtZW50Lmxhc3RQcm9taXNlVmFsdWUgPSB2YWx1ZTsKICAgIHJldHVybiBuZXh0LnZhbHVlLnJlc3BvbnNlOwogIH0pOwp9CgovKioKICogR2VuZXJhdG9yIGZ1bmN0aW9uIHdoaWNoIGlzIGNhbGxlZCBiZXR3ZWVuIGVhY2ggYXN5bmMgc3RlcCBvZiB0aGUgdXBsb2FkCiAqIHByb2Nlc3MuCiAqIEBwYXJhbSB7c3RyaW5nfSBpbnB1dElkIEVsZW1lbnQgSUQgb2YgdGhlIGlucHV0IGZpbGUgcGlja2VyIGVsZW1lbnQuCiAqIEBwYXJhbSB7c3RyaW5nfSBvdXRwdXRJZCBFbGVtZW50IElEIG9mIHRoZSBvdXRwdXQgZGlzcGxheS4KICogQHJldHVybiB7IUl0ZXJhYmxlPCFPYmplY3Q+fSBJdGVyYWJsZSBvZiBuZXh0IHN0ZXBzLgogKi8KZnVuY3Rpb24qIHVwbG9hZEZpbGVzU3RlcChpbnB1dElkLCBvdXRwdXRJZCkgewogIGNvbnN0IGlucHV0RWxlbWVudCA9IGRvY3VtZW50LmdldEVsZW1lbnRCeUlkKGlucHV0SWQpOwogIGlucHV0RWxlbWVudC5kaXNhYmxlZCA9IGZhbHNlOwoKICBjb25zdCBvdXRwdXRFbGVtZW50ID0gZG9jdW1lbnQuZ2V0RWxlbWVudEJ5SWQob3V0cHV0SWQpOwogIG91dHB1dEVsZW1lbnQuaW5uZXJIVE1MID0gJyc7CgogIGNvbnN0IHBpY2tlZFByb21pc2UgPSBuZXcgUHJvbWlzZSgocmVzb2x2ZSkgPT4gewogICAgaW5wdXRFbGVtZW50LmFkZEV2ZW50TGlzdGVuZXIoJ2NoYW5nZScsIChlKSA9PiB7CiAgICAgIHJlc29sdmUoZS50YXJnZXQuZmlsZXMpOwogICAgfSk7CiAgfSk7CgogIGNvbnN0IGNhbmNlbCA9IGRvY3VtZW50LmNyZWF0ZUVsZW1lbnQoJ2J1dHRvbicpOwogIGlucHV0RWxlbWVudC5wYXJlbnRFbGVtZW50LmFwcGVuZENoaWxkKGNhbmNlbCk7CiAgY2FuY2VsLnRleHRDb250ZW50ID0gJ0NhbmNlbCB1cGxvYWQnOwogIGNvbnN0IGNhbmNlbFByb21pc2UgPSBuZXcgUHJvbWlzZSgocmVzb2x2ZSkgPT4gewogICAgY2FuY2VsLm9uY2xpY2sgPSAoKSA9PiB7CiAgICAgIHJlc29sdmUobnVsbCk7CiAgICB9OwogIH0pOwoKICAvLyBXYWl0IGZvciB0aGUgdXNlciB0byBwaWNrIHRoZSBmaWxlcy4KICBjb25zdCBmaWxlcyA9IHlpZWxkIHsKICAgIHByb21pc2U6IFByb21pc2UucmFjZShbcGlja2VkUHJvbWlzZSwgY2FuY2VsUHJvbWlzZV0pLAogICAgcmVzcG9uc2U6IHsKICAgICAgYWN0aW9uOiAnc3RhcnRpbmcnLAogICAgfQogIH07CgogIGNhbmNlbC5yZW1vdmUoKTsKCiAgLy8gRGlzYWJsZSB0aGUgaW5wdXQgZWxlbWVudCBzaW5jZSBmdXJ0aGVyIHBpY2tzIGFyZSBub3QgYWxsb3dlZC4KICBpbnB1dEVsZW1lbnQuZGlzYWJsZWQgPSB0cnVlOwoKICBpZiAoIWZpbGVzKSB7CiAgICByZXR1cm4gewogICAgICByZXNwb25zZTogewogICAgICAgIGFjdGlvbjogJ2NvbXBsZXRlJywKICAgICAgfQogICAgfTsKICB9CgogIGZvciAoY29uc3QgZmlsZSBvZiBmaWxlcykgewogICAgY29uc3QgbGkgPSBkb2N1bWVudC5jcmVhdGVFbGVtZW50KCdsaScpOwogICAgbGkuYXBwZW5kKHNwYW4oZmlsZS5uYW1lLCB7Zm9udFdlaWdodDogJ2JvbGQnfSkpOwogICAgbGkuYXBwZW5kKHNwYW4oCiAgICAgICAgYCgke2ZpbGUudHlwZSB8fCAnbi9hJ30pIC0gJHtmaWxlLnNpemV9IGJ5dGVzLCBgICsKICAgICAgICBgbGFzdCBtb2RpZmllZDogJHsKICAgICAgICAgICAgZmlsZS5sYXN0TW9kaWZpZWREYXRlID8gZmlsZS5sYXN0TW9kaWZpZWREYXRlLnRvTG9jYWxlRGF0ZVN0cmluZygpIDoKICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgJ24vYSd9IC0gYCkpOwogICAgY29uc3QgcGVyY2VudCA9IHNwYW4oJzAlIGRvbmUnKTsKICAgIGxpLmFwcGVuZENoaWxkKHBlcmNlbnQpOwoKICAgIG91dHB1dEVsZW1lbnQuYXBwZW5kQ2hpbGQobGkpOwoKICAgIGNvbnN0IGZpbGVEYXRhUHJvbWlzZSA9IG5ldyBQcm9taXNlKChyZXNvbHZlKSA9PiB7CiAgICAgIGNvbnN0IHJlYWRlciA9IG5ldyBGaWxlUmVhZGVyKCk7CiAgICAgIHJlYWRlci5vbmxvYWQgPSAoZSkgPT4gewogICAgICAgIHJlc29sdmUoZS50YXJnZXQucmVzdWx0KTsKICAgICAgfTsKICAgICAgcmVhZGVyLnJlYWRBc0FycmF5QnVmZmVyKGZpbGUpOwogICAgfSk7CiAgICAvLyBXYWl0IGZvciB0aGUgZGF0YSB0byBiZSByZWFkeS4KICAgIGxldCBmaWxlRGF0YSA9IHlpZWxkIHsKICAgICAgcHJvbWlzZTogZmlsZURhdGFQcm9taXNlLAogICAgICByZXNwb25zZTogewogICAgICAgIGFjdGlvbjogJ2NvbnRpbnVlJywKICAgICAgfQogICAgfTsKCiAgICAvLyBVc2UgYSBjaHVua2VkIHNlbmRpbmcgdG8gYXZvaWQgbWVzc2FnZSBzaXplIGxpbWl0cy4gU2VlIGIvNjIxMTU2NjAuCiAgICBsZXQgcG9zaXRpb24gPSAwOwogICAgZG8gewogICAgICBjb25zdCBsZW5ndGggPSBNYXRoLm1pbihmaWxlRGF0YS5ieXRlTGVuZ3RoIC0gcG9zaXRpb24sIE1BWF9QQVlMT0FEX1NJWkUpOwogICAgICBjb25zdCBjaHVuayA9IG5ldyBVaW50OEFycmF5KGZpbGVEYXRhLCBwb3NpdGlvbiwgbGVuZ3RoKTsKICAgICAgcG9zaXRpb24gKz0gbGVuZ3RoOwoKICAgICAgY29uc3QgYmFzZTY0ID0gYnRvYShTdHJpbmcuZnJvbUNoYXJDb2RlLmFwcGx5KG51bGwsIGNodW5rKSk7CiAgICAgIHlpZWxkIHsKICAgICAgICByZXNwb25zZTogewogICAgICAgICAgYWN0aW9uOiAnYXBwZW5kJywKICAgICAgICAgIGZpbGU6IGZpbGUubmFtZSwKICAgICAgICAgIGRhdGE6IGJhc2U2NCwKICAgICAgICB9LAogICAgICB9OwoKICAgICAgbGV0IHBlcmNlbnREb25lID0gZmlsZURhdGEuYnl0ZUxlbmd0aCA9PT0gMCA/CiAgICAgICAgICAxMDAgOgogICAgICAgICAgTWF0aC5yb3VuZCgocG9zaXRpb24gLyBmaWxlRGF0YS5ieXRlTGVuZ3RoKSAqIDEwMCk7CiAgICAgIHBlcmNlbnQudGV4dENvbnRlbnQgPSBgJHtwZXJjZW50RG9uZX0lIGRvbmVgOwoKICAgIH0gd2hpbGUgKHBvc2l0aW9uIDwgZmlsZURhdGEuYnl0ZUxlbmd0aCk7CiAgfQoKICAvLyBBbGwgZG9uZS4KICB5aWVsZCB7CiAgICByZXNwb25zZTogewogICAgICBhY3Rpb246ICdjb21wbGV0ZScsCiAgICB9CiAgfTsKfQoKc2NvcGUuZ29vZ2xlID0gc2NvcGUuZ29vZ2xlIHx8IHt9OwpzY29wZS5nb29nbGUuY29sYWIgPSBzY29wZS5nb29nbGUuY29sYWIgfHwge307CnNjb3BlLmdvb2dsZS5jb2xhYi5fZmlsZXMgPSB7CiAgX3VwbG9hZEZpbGVzLAogIF91cGxvYWRGaWxlc0NvbnRpbnVlLAp9Owp9KShzZWxmKTsK", "ok": true, "headers": [ [ "content-type", "application/javascript" ] ], "status": 200, "status_text": "" } }, "base_uri": "https://localhost:8080/", "height": 73 }, "outputId": "456cd46a-9f98-48e2-ad52-fe4e7b88e52b" }, "source": [ "from google.colab import files\n", "uploaded = files.upload()" ], "execution_count": null, "outputs": [ { "output_type": "display_data", "data": { "text/plain": [ "" ], "text/html": [ "\n", " \n", " \n", " Upload widget is only available when the cell has been executed in the\n", " current browser session. Please rerun this cell to enable.\n", " \n", " " ] }, "metadata": {} }, { "output_type": "stream", "name": "stdout", "text": [ "Saving LondonCOB.csv to LondonCOB.csv\n" ] } ] }, { "cell_type": "markdown", "metadata": { "id": "x2gdD_l79QWR" }, "source": [ "The uploaded file can now be converted to a dataframe: note the name used here **must be the same as the name** of the uploaded file. \n", "\n", "The variable `df` below holds the dataframe. Writing `df` on a line on its own causes a summary of the dataframe to be printed. Fortunately, the system knows not to print too many lines." ] }, { "cell_type": "code", "metadata": { "id": "AyLp3VJ79l84" }, "source": [ "import io\n", "df = pd.read_csv(io.StringIO(uploaded['LondonCOB.csv'].decode('utf-8')))\n", "df" ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": { "id": "aNkAM5CwJCmn" }, "source": [ "This data is an example of ['narrow' or 'tall' data](https://en.wikipedia.org/wiki/Wide_and_narrow_data), with lots of rows (approximately 67,000). \n", " * Each column holds one 'variable'.\n", " * Each row holds data about one instance. \n", "\n", "However, it is not at all easy to understand the data in this format." ] }, { "cell_type": "markdown", "metadata": { "id": "UJYBsxLT_nZu" }, "source": [ "### Task 1.2 Unique Values in a Column\n", "To start exploring the data we have loaded, we can find out how many different values there are in each column. For example, the code below shows the different age values." ] }, { "cell_type": "code", "metadata": { "id": "Rey5MXbCAPrN" }, "source": [ "# age values\n", "df.Age.unique()" ], "execution_count": null, "outputs": [] }, { "cell_type": "code", "metadata": { "id": "oe2QwsEhAV53" }, "source": [ "# Add code for looking at the values of Area, BirthCountry and BirthRegion" ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": { "id": "9mIx_0Dw4wC5" }, "source": [ "### Answer some questions about the data\n", "\n", "1. Are all countries included?\n", "2. How do people from other countries appear in the data?\n", "3. Are all the 'Area' value London Boroughs?\n", "4. Why do you suppose other Areas are included?" ] }, { "cell_type": "markdown", "metadata": { "id": "2ttFI0ofXbIZ" }, "source": [ "## Part 2: Selecting, Transforming and Viewing Data\n", "\n", "In this section, we learn about selecting data from the dataframe. In excel, this is described a 'filter'. The concept is the same, but this word is not used here (there is a filter function in Pandas).\n", "\n", "We then introduce the Pivot table which transforms the data from narrow (tall) to wide" ] }, { "cell_type": "markdown", "metadata": { "id": "mo_KUjXa5pTB" }, "source": [ "### Task 2.1 Selecting Data\n", "\n", "The data for one Borough can be selected as shown below. Select some other subsets of the data:\n", "\n", "* Choose two Boroughs of interest to you (e.g. where you work and where you live)\n", "* Select data for one BirthRegion or one BirthCountry" ] }, { "cell_type": "code", "metadata": { "id": "iaBQ6N-fX_nT" }, "source": [ "th = df[(df['Area']=='Tower Hamlets')]\n", "th" ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": { "id": "msDHWsUNzx7U" }, "source": [ "**What no loops?** You might expect that we would need to loop through the code to accumulate the unique values. However, this is not the case. \n", "\n", "In fact, we will not be using the `while` keyword anywhere in this notebook." ] }, { "cell_type": "markdown", "metadata": { "id": "wPgjCGCw6ZhI" }, "source": [ "It is possible to use two conditions, as shown below. Note that the '&' operator (or '|' for or) is used to combine them. This is not the standard Python 'and' operator but is overloaded for the types being used here: the arguments are of type 'Series' and the effect is to intersect the two series.\n", "\n", "Uncomment the code in the cell below to see the type.\n" ] }, { "cell_type": "code", "metadata": { "id": "doRUWMTD7tZJ" }, "source": [ "# type(df['Area']=='Tower Hamlets')" ], "execution_count": null, "outputs": [] }, { "cell_type": "code", "metadata": { "id": "eR5jtWZz6fMq" }, "source": [ "th_men = df[(df['Area']=='Tower Hamlets') & (df['Sex'] == 'Males')]\n", "th_men" ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": { "id": "cEy72H4u9Aa-" }, "source": [ "To understand more about selection, you can look at the [user guide section](https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html). However, it is complex!\n", "\n", "Note that it is not very easy to extract a single number from the dataframe in this 'narrow' format as it is not 'indexed'. For an example, look at the code below, extracting a single value. \n", "\n", "We will return to this below." ] }, { "cell_type": "code", "metadata": { "id": "7czEtsLh9Fdr" }, "source": [ "singleRow = df[(df['Area']=='Tower Hamlets') & (df['Sex'] == 'Males') & (df['BirthCountry'] == 'Ghana') & (df['Age'] == 'Age 20 to 24')]\n", "singleRow['UsualResidents'] \n" ], "execution_count": null, "outputs": [] }, { "cell_type": "code", "metadata": { "id": "DEhwW5-kBF9i" }, "source": [ "singleRow['UsualResidents'] " ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": { "id": "P_Dg_sMF5dNT" }, "source": [ "### Task 2.2 The Pivot Table\n", "\n", "The Pivot table gives a summary of the data in a number of rows and columns:\n", "* The rows contain values for case. This is specified by the `index` argument. \n", "* The values shown are given by the `values` argument. Here it is the number of residents.\n", "* One or more columns can be created, analysing the values by one of the other columns. This is specified by the `columns` argument. \n", "* The result table has far fewer entries than the original dataframe. Values that are neither distinguished by a row value or a column must be **aggregated**. Here, we sum that numbers, specified in the `aggfunc` argument.\n", "\n", "The following example gives a breakdown number of people by Sex (two columns) and Birth Country (rows)." ] }, { "cell_type": "code", "metadata": { "id": "oqvyX_K9ZkDT" }, "source": [ "p = th.pivot_table(values=['UsualResidents'], index=['BirthCountry'], columns=['Sex'], aggfunc='sum')\n", "p" ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": { "id": "S8vvbykcMU6a" }, "source": [ "Create some other pivot table. The cell below shows another example; try this and invent some more. " ] }, { "cell_type": "code", "metadata": { "id": "yMMauxkcNHnB" }, "source": [ "# p1 = th.pivot_table(values=['UsualResidents'], index=['Age'], columns=['BirthRegion'], aggfunc='sum')\n", "# p1" ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": { "id": "c-H6P_t6Ceg_" }, "source": [ "### Task 2.3 Sorting the Pivot Table\n", "\n", "We can sort the pivot table using one of the columns. Try the following example and then sort the other ones you have created." ] }, { "cell_type": "code", "metadata": { "id": "jyQH9-_aYbPm" }, "source": [ "p=p.sort_values(by=('UsualResidents', 'Males'))\n", "p" ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": { "id": "HuoEHG9mCpSC" }, "source": [ "### Task 2.4 Plotting Data\n", "\n", "Data can be plotted. This uses the matplotlib library, which is also very complex. However, Pandas provides a simplified plot function so that we do not have to use matplotlib explicitly (*at least at first*).\n", "\n", "Plot function arguments include:\n", "* `kind`: different kinds of plot e.g. 'bar', 'barh' or 'pie'\n", "* `figsize`: written as (width, height)\n", "* `stacked`: true or false\n", "* `logy`: show log of the data\n", "* `subplots`: see example below\n", "* `title`: plot title\n", "\n", "[The Pandas guide on visualisation is useful](https://pandas.pydata.org/pandas-docs/stable/user_guide/visualization.html)\n" ] }, { "cell_type": "code", "metadata": { "id": "eSOvK1SCboZu" }, "source": [ "p.plot(kind=\"bar\",figsize=(15,20), logy=True) \n" ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": { "id": "8AZQX7fytcHj" }, "source": [ "The following plots show more features." ] }, { "cell_type": "code", "metadata": { "id": "ebG5GOT6NYbn" }, "source": [ "#p.plot(kind=\"pie\",figsize=(20,20), subplots=True) \n", "#p1.plot(kind='bar', stacked=True, figsize=(15,10))\n", "#p1.plot(kind='bar', subplots=True, figsize=(15,20))" ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": { "id": "l00s4dKgdShX" }, "source": [ "## Part 3: Adding and Deleting Data\n", "\n", "It is useful to be able to add (and delete) data. Adding new columns is often needed as part of an analysis to present the data in a new way. " ] }, { "cell_type": "markdown", "metadata": { "id": "5BA9_hZO1Us5" }, "source": [ "### Task 3.1 Selecting Rows\n", "\n", "Not all the areas are London Boroughs. We will discard the rows that are not for London Boroughs. The following steps are needed:\n", "\n", "1. We define a function that returns true if an area is a London Borough.\n", "1. We `apply` this function to the `Area` column. " ] }, { "cell_type": "code", "metadata": { "id": "gFC55S4_v0po" }, "source": [ "def isLondon(area):\n", " l = area in {'Barking and Dagenham', 'Barnet', 'Bexley', 'Brent', \n", " 'Bromley', 'Camden', 'City of London', 'Westminster', 'Croydon',\n", " 'Ealing', 'Enfield', 'Greenwich', 'Hackney', 'Hammersmith and Fulham',\n", " 'Haringey', 'Harrow', 'Havering', 'Hillingdon', 'Hounslow', 'Islington',\n", " 'Kensington and Chelsea', 'Kingston upon Thames', 'Lambeth', \n", " 'Lewisham', 'Merton', 'Newham', 'Redbridge', 'Richmond upon Thames', \n", " 'Southwark', 'Sutton', 'Tower Hamlets', 'Waltham Forest', 'Wandsworth'}\n", " return l\n", "\n", "#isLondon('Sutton')\n", "#isLondon('Wales')\n", "\n", "df_London = df.loc[df['Area'].apply(isLondon) == True]\n", "df_London" ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": { "id": "Ef22tBAZze3n" }, "source": [ "### Task 3.2 Calculating Totals\n", "\n", "A problem with our approach so far is that we looked at absolute numbers of people. But this cannot be used to compare the populations structure in different boroughs as they vary in absolute size.\n", "\n", "We can calculate the totals using a pivot table. " ] }, { "cell_type": "code", "metadata": { "id": "ZqmosHg2t68J" }, "source": [ "totals = df_London.pivot_table(values='UsualResidents', index=['Area'], aggfunc='sum')\n", "totals" ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": { "id": "m8Hyp-El2KA8" }, "source": [ "**Challenge** Plot these populations in sorted order" ] }, { "cell_type": "markdown", "metadata": { "id": "46cR1PLA2qtd" }, "source": [ "We can use the totals dataframe to access the total for one Borough. The following code shows how:" ] }, { "cell_type": "code", "metadata": { "id": "nUZrhlUpukeO" }, "source": [ "totals.loc['Redbridge']['UsualResidents']" ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": { "id": "VqUg9k-Z3HJt" }, "source": [ "### Task 3.3 Adding a New Column\n", "\n", "We will add a new column to the London dataframe with the population proportion. The steps are:\n", "\n", "1. Define a new function to do the calculation\n", "1. Apply it to every row\n" ] }, { "cell_type": "code", "metadata": { "id": "2IClnuTi3Z_7" }, "source": [ "def calcProportion(residents, borough):\n", " total = totals.loc[borough]['UsualResidents']\n", " return residents / total\n", "\n", "df_London['Proportion'] = df_London.apply(lambda df: calcProportion(df['UsualResidents'], df['Area']),axis=1)\n", "df_London\n" ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": { "id": "9HcfmbbwBcqK" }, "source": [ "We can use the new column is a pivot table" ] }, { "cell_type": "code", "metadata": { "id": "nSck1yXp9F2n" }, "source": [ "p4 = df_London.pivot_table(values='Proportion', index='Area', columns=['BirthRegion'], aggfunc='sum')\n", "p4" ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": { "id": "vLzp8rDWBmWs" }, "source": [ "Then we can plot a figure comparing the region of birth in different Boroughs." ] }, { "cell_type": "code", "metadata": { "id": "iQkYrB_e9n1y" }, "source": [ "p4.sort_values(by=['Europe']).sort_values(by=['Sutton'],axis=1, ascending=False).plot(\n", " kind='bar', stacked=True, figsize=(15,10))" ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": { "id": "_bfnFfTsB3Id" }, "source": [ "### Task 3.4 Challenge Problems\n", "\n", "Try some further analyses. For example:\n", "\n", "1. Add a column distinguishing inner and outer London Boroughs. [They are listed here](https://en.wikipedia.org/wiki/Inner_London) but watch out for different spelling.\n", "\n", "1. Look at the age structure of differnt Boroughs\n", "\n", "1. Change this around, to ask where do people of a certain age live? Now we want to normalise data (i.e. calculate proportions) using totals based on age group." ] }, { "cell_type": "code", "source": [ "" ], "metadata": { "id": "vG1xHjdGFgAt" }, "execution_count": null, "outputs": [] } ] }