01-Data Managing.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Management Notebook\n",
    "\n",
    "As we learnt in the first lecture, one of the big factors that has pushed the current AI wave is the availability of data.  So, we will learn how to handle numerical data in this workbook, moving later on to imaging data.\n",
    "\n",
    "In this tutorial, we will learn to load data (stored as comma separated values or .csv files), access, summarise, select, and manipulate data using NumPy and pandas."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## NumPy\n",
    "Numpy is the core library for scientific computing in Python. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### NumPy Arrays\n",
    "We will learn about arrays (focused on 1D and 2D arrays), and some tools for working with them. \n",
    "A numpy array is a grid of values, all of the same type. They are initialised from lists for example:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "array1d = np.array([1, 2, 3])           # this is a list of values - 1D\n",
    "array2d = np.array([[1,2,3],[4,5,6]])   # this is a list of lists - 2D"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can find the number of dimensions and size of these array using shape. Moreover, you can access the content using square brackets, either with a single number (for 1D arrays) or tuples:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print('Shape array1d ', array1d.shape)\n",
    "print('First value in array1d ', array1d[0])\n",
    "print('Last value in array1d ', array1d[-1])\n",
    "print('Values in array1d ', array1d)\n",
    "array1d[0] = 5\n",
    "print('Values in array1d after change:', array1d)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print('Shape array2d ', array2d.shape)\n",
    "print('Value at 0,0 position ', array2d[0, 0])\n",
    "print('Values in array2d:' )\n",
    "print(array2d)\n",
    "array2d[0, 0] = -1\n",
    "print('Values in array2d after change:' )\n",
    "print(array2d)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Slicing\n",
    "Slicing is the extraction of a part of an array in a specific range of elements by mentioning their indices. Let's create arrays with random values and slice them in different ways:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "a = np.array([0,1,2,3,4,5,6,7,8,9])\n",
    "print(a)\n",
    "print(a.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's get the values in the positions from 3 to 6. This is done like this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(a[3:7])  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You may notice that we want to get the values until the 6th position, but we have added a '7' in the indices.\n",
    "\n",
    "For any given a[i:j], Python assumes that you want all values, starting at the i-th position until (but not including) the value in the j-th position. \n",
    "\n",
    "You can also set up a step. This is written like this a[i:j:k]. In this case, Python assumes that you want the values, starting at the i-th position until (but not including) the value in the j-th position, but every k positions.  Let's see it in our example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(a[2:7:2])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can also use negative indices, or leave them empty to mean the default start or ending point:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(a[2:-1:3])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(a[2::2])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In 2D arrays, you need to use indices for each dimension."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "b = np.random.random((10,4))\n",
    "print(b)\n",
    "print(b.shape)\n",
    "print(b[:,2:4])\n",
    "print(b[7:9,:])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Boolean indexing\n",
    "Arrays can also be indexed using conditions (that create binary arrays). For example, let's create a array of random numbers and we can find all values that are larger than 0.7."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "rand1d = np.random.random((10,))\n",
    "print(rand1d)\n",
    "print(rand1d > 0.7)\n",
    "print(rand1d[rand1d>0.7])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Array math and other operations\n",
    "Basic mathematical functions operate elementwise on arrays.  Some examples include addition, substraction, multiplication, division and square root. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "x = np.array([[1,2],[3,4]])\n",
    "y = np.array([[5,6],[7,8]])\n",
    "\n",
    "print('x =', x)\n",
    "print('y =',y)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Elementwise addition --> the function produces an array as output\n",
    "z = x + y # or z = np.add(x, y)\n",
    "print(z) # or z = np.add(x, y)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Elementwise difference --> the function produces an array as output\n",
    "z = x - y # or z = np.subtract(x, y)\n",
    "print(z)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Elementwise product --> the function produces an array as output \n",
    "z = x * y # z = np.multiply(x, y)\n",
    "print(z)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Elementwise division --> the function produces an array as output\n",
    "z = x / y # z = np.divide(x, y)\n",
    "print(z)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are also functions that work within a single array, for example square root or sum."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Elementwise square root --> the function produces an array as output\n",
    "z = np.sqrt(x)\n",
    "print(z)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Sum of the elements --> the function produces an array as output\n",
    "summall = np.sum(x)\n",
    "print(summall)\n",
    "\n",
    "colsums = np.sum(x,axis=0)\n",
    "print(colsums)\n",
    "\n",
    "rowsums = np.sum(x,axis=1)\n",
    "print(rowsums)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Some functions can modify the content of the array, without returning a new array. For example put or place:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "rand1d = np.random.random((10,))\n",
    "print(rand1d)\n",
    "\n",
    "np.put(rand1d, [3,5,7], [1,2,3])\n",
    "print(rand1d)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "np.place(rand1d, rand1d>=1, [-1, -2, -3]) \n",
    "print(rand1d)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For more information, check the documentation of NumPy: https://numpy.org/doc/stable/reference/ "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pandas\n",
    "\n",
    "Python package for data manipulation and analysis, especially good for numerical tables and time series. Pandas stands for: \"panel data\", an econometrics term for longitudinal data sets or \"Python data analysis\"... you choose!  Pandas is built on NumPy's array, so many functions available in NumPy are accessible in Pandas.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's load the data using pandas. You can also open the file using excel or any spreadsheet processing package.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "# we are loading data from github. \n",
    "dataurl = 'https://github.com/rrr-uom-projects/MPiCRT-AI/raw/main/Data/titanic.csv' \n",
    "pax = pd.read_csv(dataurl, sep = ',')\n",
    "\n",
    "# You can also write the path if done locally, for example\n",
    "# dataloc = 'c:/temp/titanic.csv'\n",
    "# pax = pd.read_csv(dataloc,sep=',')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Main data structure: DataFrame and Series\n",
    "\n",
    "Pandas is built around data structures called _Series_ and _DataFrames_. \n",
    "pax above is a dataframe. We can check this easily:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(type(pax))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A DataFrame is a 2D data structure of rows and columns, similar to a spreadsheet. Each 'column' in the dataframe is stored as a 1D NumPy array, which also include an index (corresponding to the row number in spreadsheet). Keep in mind that indexes do not have to be numerical. \n",
    "\n",
    "Let's see how many rows/colums this dataframe has, and what columns we have. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(pax.shape)\n",
    "print(pax.columns)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "DataFrames keeps all series in a structure analogous to a Python dictionary, mapping column names (keys) to Series (values).  Let's select one series:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "a = pax['Name'] # or pax.Name\n",
    "print(type(a))\n",
    "print(a)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "An important aspect we learnt about Numpy arrays is that they can only hold the same type of data.  In contrast, a dataframe can contain several types, as each series is independent.  Let's see what type of data we have in the dataset, using the function info()."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(pax.info())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Do you see that the non-null counts differ... why is that?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Series operations\n",
    "Numerical series can be used arithmetically, e.g., series_3 = series_1 + series_2.  This operations aligns values with corresponding indices in series_1 and series_2, then add them together to produce new values in series_3.\n",
    "\n",
    "For example, we could add the age and fare into a new column... although with not much meaning:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "nonsensicalseries = pax.Age + pax.Fare\n",
    "print(nonsensicalseries)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can add a new series with the result of a mathematical operation very easily. For example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(pax.columns)\n",
    "pax['nonsense'] = pax['Age'] + pax['Fare']\n",
    "print(pax.columns)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How could we find the most expensive fare? \n",
    "Remember that the Series are based on NumPy!\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Try!\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also do all sort of String operations using the string functions within pandas.str (here some extra info: https://pandas.pydata.org/docs/user_guide/text.html).\n",
    "Let's try and find out how many passengers were called Mary and show the names:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# First we need to cast the type of the Name series to str. \n",
    "# From pax.info() we saw that Name has 891 non-null values, and it is type object\n",
    "pax['Name'] = pax['Name'].astype('string')\n",
    "print(pax.info())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Now, we can use the function count to count the number of time that paxname is within the values in pax.Name\n",
    "paxname = 'Mary' # Notice that this can also include regular expressions. Check for more info https://docs.python.org/3/library/re.html#module-re\n",
    "marycounts = pax['Name'].str.count(paxname)\n",
    "num = np.sum(marycounts)\n",
    "print(num)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# we can use marycounts to select the cells that had Mary in them:\n",
    "allmarys = pax['Name'][marycounts>0]\n",
    "print(allmarys)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also split strings 'easily'. For example, we could extract the surname of the names series.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "surnamefirstnames = pax['Name'].str.split(',')  # this splits the string by the token given (,)\n",
    "print(surnamefirstnames)                        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pax['Surname'] = surnamefirstnames.str.get(0)   # here we get the first bit of the divided sentence\n",
    "print(pax['Surname'][0:5])\n",
    "print(pax['Name'][0:5])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Accessing the data within the dataframe\n",
    "\n",
    "Selecting a specific cell can be done using two alternative:\n",
    "1. Selecting the series, then the index location,\n",
    "2. Using 'at'. In this case, the index goes before the series label."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(pax['Name'][100])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(pax.at[100,'Name'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can also select multiple columns for using 'loc', which creates a new dataframe with the selected data. For example, we can select Surname, age and fare for all passengers like this: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "selcols = ['Surname','age','fare'] # this should produce an error... any idea why?\n",
    "selpax = pax.loc[:,selcols]\n",
    "print(selpax)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We could also decide to only get these data for the passengers called Mary.  In this case, we can use marycounts, obtained above:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "marysdata = pax.loc[marycounts>0,selcols]\n",
    "print(marysdata)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can also access the dataframe, line by line. For this, we can use iterrows(). Let's do that for marysdata:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for index, row in marysdata.iterrows():\n",
    "    print(index, row['Surname'], row['Age'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What are those pesky NaN (or nan)?? "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can identify all the values that are NaN using the isnan() function of Numpy. For example in Age marysdata:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "allnansinMarys = np.isnan( marysdata['Age'] )\n",
    "print(allnansinMarys)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And then, you could replace those value for a value... For example you can find the mean value of all the ages that are not NaN, and replace them with that:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "meanageforknownMarys = np.nanmean(marysdata['Age']) # what does this function do?  check in internet compare it to np.mean\n",
    "print(meanageforknownMarys)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "marysdata.loc[allnansinMarys, 'Age'] = meanageforknownMarys\n",
    "print(marysdata)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "... and now you have imputed data for the first time!! Well done."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Categorical variables\n",
    "A categorical variable takes on a limited, and usually fixed, number of possible values. For example sex in the pax dataframe. \n",
    "We can check how many unique values we have, as well as summarise the number of times each value is in the series. For example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(pax['Sex'].nunique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(pax['Sex'].value_counts())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can use these type of variables when we want to group information, for example using the 'groupby()' function.  In this example we can calculate the mean age for males vs females:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(pax.groupby(['Sex'], observed=True)[['Age']].mean())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are many online resources that can be of use.  For example:\n",
    "- https://pandas.pydata.org/docs/reference/index.html\n",
    "- https://pandas.pydata.org/docs/user_guide/index.html"
   ]
  }
 ],
 "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.9.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}