{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Tutorial 2: Importing and Wrangling Data\n", "\n", "## File Locations\n", "\n", "When dealing with files on your computer, you need to tell python the 'path' to that file. Paths can be *relative* or *absolute*. An *absolute* path might looks like this:\n", "\n", " /users/myname/Desktop/Python_Tutorials/data/my_data.txt\n", " \n", "it always starts with a `/`, which represents the 'root' directory of your computer (i.e. the `C:\\\\` drive on Windows, or the the `/` directory on Mac/Linux). These paths are unambiguous, and you'll always be able to find that file on your computer (as long as you don't move the file). However, if someone else were to open your code on their own computer, the file path might break because the start of your path (`/users/myname/Desktop/Python_Tutorials`) might not be the same on their system (e.g. they might use `/users/theirname/Documents/Python_Tutorials`). This is where *relative* paths are useful.\n", "\n", "A *relative* path might look like this:\n", "\n", " data/my_data.txt\n", "\n", "If there is no `/` at the start, Python will look for this file in its *working directory*. The working directory is the location where python is running. Using *relative* paths means that any other person can run your code, as long as they start Python in the `Python_Tutorials` folder on their computer. \n", "\n", "**Relative imports are more robust and shareable. Use them!**\n", "\n", "**Tip:** If you ever run into a `FileNotFoundError`, first check that there are no spelling mistakes in the path (tab autocompletion is your friend here!), and then check that the file path is definitely correct.\n", "\n", "### Paths in Jupyter Notebooks\n", "\n", "When you start Jupyter Notebooks it is running in a location - it has a *working directory*. You can find out where it's running by typing:\n", "\n", " pwd\n", "\n", "(which stands for 'print working directory') in a cell, and evaluating it. This will print out the working directory. You can specify paths *relative* to this location.\n", "\n", "You can also change the working directory within jupyter, using:\n", "\n", " cd /path/to/new/working/directory\n", "\n", "(try running `pwd` again - it should have changed to your new location).\n", "\n", "However, this can be a little clumsy, and it's easy to forget to do this every time you start a new notebook. The 'best practice' approach is to select the correct directory *before* you start jupyter notebook. To do this, before you launch `jupyter notebook` from the Terminal (Mac/Linux) or Anaconda Prompt (Windows) you can do:\n", "\n", " cd /path/to/my/working/directory\n", " jupyter notebook\n", "\n", "This will start jupyter notebook in the correct directory to start with, and then all files can be specified *relative* to that location.\n", "\n", "## Basic Data Import Principles\n", "\n", " Explicit is better than implicit.\n", "\n", "A little time thinking about data structure and labelling maked everything easier. In practice, this means:\n", "\n", "- Use meaningful, unique column names.\n", "- Avoid special characters and spaces.\n", "- Avoid 'merged cells' to label groups of columns.\n", "\n", "In most cases, the best format for your data will be simple columns or matrices." ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [], "source": [ "import numpy as np\n", "import pandas as pd\n", "\n", "%matplotlib inline\n", "import matplotlib.pyplot as plt" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Sample_TypeAA_stdBB_std
Sample_No
1Cheese16.1548910.7508815.0475290.125502
2Cheese16.1400080.2667445.4268460.089972
3Cheese16.8513590.1783195.6295350.162741
4Cheese16.2054270.2401945.3891000.122976
5Cheese16.2400210.7205345.1325730.171547
\n", "
" ], "text/plain": [ " Sample_Type A A_std B B_std\n", "Sample_No \n", "1 Cheese 16.154891 0.750881 5.047529 0.125502\n", "2 Cheese 16.140008 0.266744 5.426846 0.089972\n", "3 Cheese 16.851359 0.178319 5.629535 0.162741\n", "4 Cheese 16.205427 0.240194 5.389100 0.122976\n", "5 Cheese 16.240021 0.720534 5.132573 0.171547" ] }, "execution_count": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "good = pd.read_excel('data/good_practice.xlsx', index_col=(0))\n", "good.head(5) # .head() just displays the top n lines - .tail() does similar for the end." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Imports with no issues, and it's very ovious what things are." ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Unnamed: 0_level_0AB
Sample no.Sample TypeValueStand. Dev.ValueStand. Dev.
1Cheese16.1548910.7508815.0475290.125502
2Cheese16.1400080.2667445.4268460.089972
3Cheese16.8513590.1783195.6295350.162741
4Cheese16.2054270.2401945.3891000.122976
5Cheese16.2400210.7205345.1325730.171547
\n", "
" ], "text/plain": [ " Unnamed: 0_level_0 A B \n", "Sample no. Sample Type Value Stand. Dev. Value Stand. Dev.\n", "1 Cheese 16.154891 0.750881 5.047529 0.125502\n", "2 Cheese 16.140008 0.266744 5.426846 0.089972\n", "3 Cheese 16.851359 0.178319 5.629535 0.162741\n", "4 Cheese 16.205427 0.240194 5.389100 0.122976\n", "5 Cheese 16.240021 0.720534 5.132573 0.171547" ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# bad = pd.read_excel('bad_practice.xlsx')\n", "bad = pd.read_excel('data/bad_practice.xlsx', header=(0,1))\n", "\n", "bad.head(5)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Possible to get it to import, but takes more effort, and can be more irritating to get into the right format." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Data Import: Examples\n", "\n", "We'll go through importing using `numpy`, `pandas` and `sqlite`.\n", "\n", "## `numpy` data import.\n", "\n", "`numpy` is the 'numeric python' library. It's used for basically all 'behind the scenes' computations in Python. Often it will be more convenient to use a higher-level library like `pandas` to interact with your data, but if you're doing something that pandas doesn't handle well (e.g. working with 2D arrays), or want to make something 'lightweight' that doesn't rely on a lot of packages, `numpy` might be a better choice.\n", "\n", "`numpy` is designed with computation, rather than data input/output in mind, so it has more limited import options than something like `pandas`.\n", "\n", "### Example: reading a text file\n", "\n", "To import an xy file, similar to that produced by a typical spectrum from Infrared/XANES/Raman spectrometer." ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [], "source": [ "spectrum = np.genfromtxt(\"data/Raman_sp1.txt\")" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(4012, 2)" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "spectrum.shape" ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "array([[4002.113281, 2104.73877 ],\n", " [4001.415039, 1993.774536],\n", " [4000.716797, 2043.55896 ],\n", " ...,\n", " [ 54.099609, 5738.841309],\n", " [ 52.744141, 5708.288574],\n", " [ 51.390625, 5509.776367]])" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "spectrum" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Aside: Indexing\n", "\n", "This is a 2D array. You can access specific parts of the array by 'indexing':\n", "\n", " spectrum[row, column]\n", "\n", "Where `row` and `column` are the numerical indices of the data you want. For example, `[0, 0]` would return the data in the first row and the first column:\n", "\n", "#### Tip: in Python, numbers start at `0`, not `1`!" ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "4002.113281" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "spectrum[0, 0]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "A few handy indexing tricks, which are applicable throughout `numpy` and `pandas`:\n", "\n", "- `:` using a colon in place of a number means 'all values'. i.e. `spectrum[:, 0]` would be all rows from the first column.\n", "- `n:` all locations from a particular index (`n`) to the end\n", "- `:n` all locations from the start up to a particular index (`n`)\n", "- `n:m` all locations between one locations (`n`) and another (`m`)\n", "- `-n` negative numbers can be used to select items from the *end* of the index. e.g. `[-1]` would be the final item in the array. This can be combined with the `:` operator, for example `[5:-5]` would select from the 5th item to 5 items from the end of the array.\n", "\n", "Let's see this in action:" ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" }, { "data": { "image/png": "iVBORw0KGgoAAAANSUhEUgAAAY0AAAEJCAYAAABohnsfAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDIuMi4wLCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvFvnyVgAAIABJREFUeJzt3Xl4FFXW+PHvAcIiIpu4BmVzdxA0IDqAjv4EFV9AUYHRUUEFlxnBZUYccAFlRGcUFWf0RdAXlwEUdMQFZVGUUQSDCERB2SUo+xqBhJDz+6Oqm+6ku1PpdHV3kvN5nnq66tZyT6qhTlfdqluiqhhjjDFeVEt1AMYYYyoOSxrGGGM8s6RhjDHGM0saxhhjPLOkYYwxxjNLGsYYYzyzpGGMMcYzSxrGGGM8s6RhjDHGsxqpDiDRjjzySG3WrFmqwzDGmApl4cKFW1W1SWnLVbqk0axZM7Kzs1MdhjHGVCgiss7LcnZ5yhhjjGeWNIwxxnhmScMYY4xnla5NwxhT9Rw4cIDc3Fz279+f6lDSXu3atcnMzCQjIyOu9S1pGGMqvNzcXOrVq0ezZs0QkVSHk7ZUlW3btpGbm0vz5s3j2oZdnjLGVHj79++ncePGljBKISI0bty4XGdkljSMMZWCJQxvyrufLGlE8O233zJv3rxUh2GMMWnHkkYEbdu25fzzz091GMaYCkRE+MMf/hCcLiwspEmTJlxxxRVxb7NZs2Zs3bo15jJ/+9vf4t5+PCxpGGNMAtStW5ecnBz27dsHwMyZMzn++ON9r9eSRop9/fXXqQ7BGFNBXXbZZXzwwQcATJw4kb59+wJQVFTESSedxJYtW4LTrVq1KnEWsW3bNrp06ULbtm0ZOHAgqhqc17NnT8455xzOOOMMxo4dC8CQIUPYt28fbdq04brrrou6XCLZLbfFvPbaa6kOwRhTDoMHD+bbb79N6DbbtGnDM888U+pyffr0YcSIEVxxxRUsWbKE/v37M3fuXKpVq8b111/PG2+8weDBg5k1axZnnXUWRx55ZNj6w4cPp2PHjjz00EN88MEHYQf9l19+mUaNGrFv3z7atWtHr169GDVqFM8//3zY3xtpucaNGydsX9iZRjFjxowJjk+aNCmFkRhjKprWrVuzdu1aJk6cyOWXXx42r3///rz66quAc2Dv169fifU///xzrr/+egC6detGw4YNg/Oee+45zjrrLDp06MD69etZsWJFxBi8LhcvO9OI4aGHHqJPnz6pDsMYUwZezgj81L17d+677z7mzJnDtm3bguVNmzbl6KOP5pNPPmH+/Pm88cYbEdePdEvsnDlzmDVrFvPmzeOwww7jwgsvjPishdflysPONIoJPY379ddfUxiJMaYi6t+/Pw899BC/+c1vSsy75ZZbuP7667n22mupXr16ifmdO3cOJpPp06ezY8cOAHbt2kXDhg057LDDWL58OV999VVwnYyMDA4cOFDqcoliSaOYFi1aBMd//vnnFEZijKmIMjMzGTRoUMR53bt3Jy8vL+KlKYCHH36Yzz//nLPPPpsZM2ZwwgknAHDppZdSWFhI69atefDBB+nQoUNwnQEDBtC6dWuuu+66mMslioS2zlcGWVlZWp6XMFWvXp2ioqLgdGXbP8ZURsuWLeO0005LdRilys7O5u6772bu3LkpjSPS/hKRhaqaVdq61qZRTGjCOOqoo1IYiTGmMhk1ahQvvPBC1LaMisIuT8Vw7rnnpjoEY0wlMWTIENatW0fHjh1THUq5WNIIUfxSVEFBQYoiMcaUlV1K9qa8+8mSRojAHQgAnTp1sqRhTAVRu3Zttm3bZomjFIH3adSuXTvubVibRojQpFFUVMR3332XwmiMMV5lZmaSm5sb7KbDRBd4c1+8fEkaInIKMDmkqAXwEPCqW94MWAtcq6o7xHma5VngcmAvcJOqfuNu60ZgmLudx1R1gh8xw6Gk8fjjj/PAAw8AkJeXx+GHH+5XlcaYBMjIyIj7TXSmbHy5PKWqP6hqG1VtA5yDkwjeAYYAs1X1JGC2Ow1wGXCSOwwAXgAQkUbAw8C5QHvgYRFpiE8CSSM0SezZs8ev6owxpsJJRpvGxcAqVV0H9AACZwoTgJ7ueA/gVXV8BTQQkWOBrsBMVd2uqjuAmcClfgUaSBqhL1wPvWRljDFVXTKSRh9gojt+tKr+AuB+Bh6EOB5YH7JOrlsWrdwXkZLGyJEj/arOGGMqHF+ThojUBLoDb5W2aIQyjVFevJ4BIpItItnlaQgL3C0VmjTGjh3LRRddFPc2jTGmMvH7TOMy4BtV3eROb3IvO+F+bnbLc4GmIetlAj/HKA+jqmNVNUtVs5o0aRJ3sKNGjXKCyc0NK//000/j3qYxxlQmfieNvhy6NAUwDbjRHb8ReDek/AZxdAB2uZevPga6iEhDtwG8i1vmi3feeQdwOiqM1AOlMcZUdb49pyEihwGXAANDikcBb4rIzcBPwDVu+Yc4t9uuxLnTqh+Aqm4XkUeBwDtYR6jqdr9iDlyeqlmzJnl5edSpU8evqowxpkLyLWmo6l6gcbGybTh3UxVfVoE7o2znZeBlP2IsLrQhvDxPTBpjTGVl3YiECCSNmjVrlph38ODBZIdjjDFpx5JGiEC/NaF3TwU8/vjjyQ7HGGPSjiWNEDfddFPY5/333x+ct3Tp0hREZIwx6cWSRojMzExEhKZNnbt8r7766uC8/Pz8VIVljDFpw5JGiAMHDoRdmsrKyqJaNWcXWTfpxhhjSSNM8aQB0L59ewAKCwtTEZIxxqQVSxohIiWNwJnGzJkzUxGSMcakFUsaISIljTPPPDNF0RhjTPqxpBEiUtJ49tlnUxSNMcakH0saISIljdAnw+0BP2NMVWdJI0SkpBHKers1xlR1ljRClJY0Ak+MG2NMVWVJI0RpSUMk0juhjDGm6rCkEaKoqCjmezTWrFmTxGiMMSb9WNIIUVRUFHwuI1TXrl0BGDBgQLJDMsaYtGJJI0S0pPHEE0+kIBpjjEk/ljRCREsaxx13XAqiMcaY9GNJI0S0pGFv8TPGGIcljRDRkkatWrVSEI0xxqQfSxohoiWNWLfhGmNMVWJJI0S0pBH6fMb69euTGZIxxqQVSxohoiWNUGPGjElSNMYYk34saYTwkjSsfcMYU5VZ0gjhJWk89thjSYrGGGPSjyWNEPPmzYv6hr5LLrkkOP7UU08lKyRjjEkrUtl6bs3KytLs7Oy41g00eEfaJ1u2bOGoo44KTle2/WaMqdpEZKGqZpW2nG9nGiLSQESmiMhyEVkmIueJSCMRmSkiK9zPhu6yIiLPichKEVkiImeHbOdGd/kVInKjX/EGtGjRImJ53bp1/a7aGGPSnp+Xp54FPlLVU4GzgGXAEGC2qp4EzHanAS4DTnKHAcALACLSCHgYOBdoDzwcSDR+aNCgAd26dYs4r2bNmn5Va4wxFYYvSUNEjgA6A+MBVLVAVXcCPYAJ7mITgJ7ueA/gVXV8BTQQkWOBrsBMVd2uqjuAmcClfsQMUFhYSI0aNSLOK16+b98+v8Iwxpi05deZRgtgC/CKiCwSkXEiUhc4WlV/AXA/A40ExwOhT83lumXRyn1x8ODBmO/TCLVy5Uq/wjDGmLTlV9KoAZwNvKCqbYFfOXQpKpJIr8TTGOXhK4sMEJFsEcnesmVLPPECTtKIdqYB8NprrwXHu3TpEnc9xhhTUfmVNHKBXFWd705PwUkim9zLTrifm0OWbxqyfibwc4zyMKo6VlWzVDWrSZMmcQddWFgY80wjtL1j48aNcddjjDEVlS9JQ1U3AutF5BS36GLge2AaELgD6kbgXXd8GnCDexdVB2CXe/nqY6CLiDR0G8C7uGV+xExRUVHMM42GDRvy5Zdf+lG9McZUCNGPkOX3J+ANEakJrAb64SSpN0XkZuAn4Bp32Q+By4GVwF53WVR1u4g8CnztLjdCVbf7EezBgwcBSm3TOO+88wA455xz/AjDGGPSmm9JQ1W/BSI9KHJxhGUVuDPKdl4GXk5sdCV5TRoBCxcuZOfOnTRo0MDPsIwxJq1YNyKuQNKIdXmquF27dvkVjjHGpCVLGq7CwkLA+5kGQH5+vl/hGGNMWrKk4YrnTGP//v1+hWOMMWnJkoYrmWca+/fvZ/Xq1XGta4wxqWRJw1XWhnAg7ttve/fuTcuWLYN1GmNMRWFJwxVP0hg+fHhcdU2bNg2AnJycuNY3xphUsaThClyeysjI8LzOjh07ylxPXl5ecLxNmzZ8+OGHZd6GMcakSqlJQ0Tau59Hi8gwETnD/7CSL5A0ytIQHo+JEyeGTd9www2+1meMMYnk5UzjSffzEWAVMM63aFIo3qTxww8/lOlsYe/evWHT27ZtK1N9xhiTSl6SRk0RqQZkqOpEoMDnmFKiLEnj448PdX916qmnRn1xU6js7Gw6derE4MGD4w/SGGNSzEvSmA3MAyaLSG2gUr59qCy33Hbp0oXOnTtHnLdr1y5GjBhR4s6odu3a8d///rf8gRpjTAp5SRpLVPVcVZ2pqvuBMX4HlQplfbiv+GWmwPr33HMPDz/8MK1ataKoqMjTtrKzs8Omf/zxRzZt2uRpXWOMSSYvSeP2YtO3+hFIqpW1TePkk08Om54wwXmL7c6dOwFYu3Yt8+bN48EHH6R169Yxt3XbbbeFTZ9yyilkZmZ6isMYY5Ip6hFSRG4FBgCniMgCnLfoFQFzkhNacrVt25ZNmzZRv359T8sPHTqUf//738Hpm2++maVLl4ZdlurYsaOnbX3//ffB8f/93/8FDiUxY4xJJ+L0Sh5jAZGeqvqfJMVTbllZWVr8co8fVJVq1eJ/zOWMM85ARMjJyaFWrVrBfqxEDr3htrTvxhhjEkVEFqpqpNdZhPFyLWaFiPwDaIj7zm5V7V/O+Cq80IN7PI499lg6depETk6O9ZZrjKkwvCSN14E/A+t9jqVKmThxIm+99VZwes2aNZx44okpjMgYY0rn5frKOlWdpao/BAbfo6og3nnnHerVq1fm9VSVI488kptvvjlY1qJFixK3+27f7subbY0xJm5ekkYtEZkpIn8XkSdF5MnSV6kaevbsyWmnnRb3+jVr1ow5//33349728YY4wcvSWMU8BjwPvCBOxjXJZdc4tu2b7zxRus+3RiTVrwkDY0wGNfw4cPL9EKlst4RVbyDQ2OMSSUvDeGXuZ8CnI3T99TnvkVUwVSvXp3mzZtTo0YNCgsLWbFiBZ988gkbNmxgxIgRYcsuWbKkxPonnHACP/30U9Tt2ytljTHppNSkoaoPhE6LSIV5ZiOZAt2KZGRk0KpVK8aPHx82f8OGDRx33HEl1vv4449LtIu0atWKlStXAv531W6MMWVR6hFJRE4PmWwKNPMtmgqs+MubAm0RmZmZ/Pvf/46YMMDpJbe4efPm0aRJE8CShjEmvXg5Iv3Z/VRgB/AH/8KpPAKdFXbr1o1OnTqVad0jjzwyOJ5OSaOgoIDly5eX2peWMabyKrUhXFX7AbcBjwJ/VdWlvkdVCXTr1o1q1aoxcODAUpcNXIqKZOrUqYkMK24zZsygVq1anHXWWaxduzbV4RhjUsTL615vAL4ARgJfiIidaXjQtGlTDh48SNu2bUtdtmXLllx88cUA3HpreCfCU6ZM4fbbb2fMmNT1SJ+fn0/Xrl2D06effnqMpY0xlZmXDgvnAZ1V9YCI1AQ+U9XzSt2wyFpgD3AQKFTVLBFpBEzGaRdZC1yrqjvE6cjpWeByYC9wk6p+427nRmCYu9nHVHVCrHqT1WFhouXm5vKXv/yF8ePHU6dOnYh9W6WqA8P33nuP7t27p0Usxhh/eO2w0Gs3rYeHfJalp77fqWqbkECGALNV9SScNwIOccsvA05yhwHACwBuknkYOBdoDzwsIg3LUH+FEWgwr1OnDgDPPvtsiiMyxpiSvCSNYcB0950aHwB/LUd9PYDAmcIEoGdI+avq+ApoICLHAl2Bmaq6XVV3ADOBS8tRf4WR6ktA27ZtY9KkSeTl5bFs2bIS8+1JdWOqJi/PaczGOSsoKwVmiIgC/6uqY4GjVfUXd7u/iMhR7rLHE96Lbq5bFq08jIgMwDlD4YQTTogj1PTz//7f/6N69ephB+f33nuP//mf/0lK/d27d+fLL7+MOr+goCB4VmSMqTq8NITPCBkXEZnucdu/VdWzcS493SkinWNVE6FMY5SHF6iOVdUsVc0KPN9QGUyZMiVsunv37iXeTe6XSGcXoQoKCpIShzEmvXi5PBXsilWd1s/DvGxYVX92PzcD7+C0SWxyLzvhfm52F8/FeXAwIBP4OUZ5lXDppSWvxA0ZMiTCkolXvJv24p577rmkxGGMSS9eksbPIjJIRM4WkbuAX0pbQUTqiki9wDjQBcgBpgE3uovdCLzrjk8DbnDPZDoAu9zLWB8DXUSkodsA3sUtqxJq165doqy0M4BE2Lt3L1u3bo25zEMPPeR7HMaY9OMlafTHuSR0C1AE3ORhnaOB/4rIYmAB8IGqfoTTzfolIrICuMSdBvgQWA2sBF4C7gBQ1e04DxV+7Q4j3LIq44orrgibnjVrlu91XnPNNb7XYYypmLw0hO8HynQtQlVXA2dFKN8GXByhXIE7o2zrZeDlstRfmURq2N+7dy+HHebpKmFcPvzwwxJlCxYsoH379r7VaYypGLw+p2FS5JZbbilR1q9fv6TH0a5dO7Zv387OnTtLbe8wxlReljTSXNu2bVFVOnbsGCx78803fatvw4YNJcreeecdABo2bEj9+vWDtwGvWrXKtziMMempTElDRI4QkX4iUt+vgExkyertNjMzM2y6qKiInj17hpUFnlY/66wSVyCNMZVcWc80itx1HihtQZNYga7WA/Ly8nyvs3fv3hH7wAp0wvjrr7/6HoMxJr14ebjvgsC4quap6nhVTc7DAiaoeLcd48aN873OSZMmRSw/4ogjfK/bGJOevJxpXC4i/xWRv4nIaaUvbvxQ/DbYatX8bY565plnos4LvXNr/fr1UZczxlQ+Xl7CdL+qdgRmAI+KyEL3Yb96/odnAu666y7fLweFdlHSsGH0zoRD+5yyFzIZU7V4uTxVU0SuwnmOojrwd5ynwj/wOTYTQkTCfuEvWLAg4XVkZR3qSv+006KfVIYmDevt1piqxcs1jhnAicAfVfVKVZ2kqm8Cr/sbmoll4sSJCd9maBcl7dq1i7pcaPLKzc1NeBzGmPTlJWm8raqjVXUTgIj0A6dnWV8jMxG1adMGcF4nmyqhZxp/+IO9/deYqiRq0hCRGm5ng71EpI6IHCYiRwDXJi88U9z06U7P9OvWrfOtDksExphoYp1pXAe8D7TGab/4AHgLa8tIqWOOOca3bZ955pk0atSIsWNLP4ns1KmTb3EYY9JX1MeMVXUCMEFEOrivYDVpRlUjPnwXr5ycHHr27BmxS/biZsyYYW/uM6YKinV56mF39F4ReTN0SFJsJorf//73AOzatStYtmHDBr777ru4t7l69WoA/vOf/3ha3ktiMcZUPrE6NHrR/bwvGYEY72bPdl7ZfvPNNzN16lTgUJ9RTi/zZbdx48Yyr/P73/+e+fPnx1WfMaZiinqmEbhbCmijquuAWsCDQPNkBGaiC1ySevvtt/nqq6/C3rL30UcflXl7RUVF/Pa3vy3zert372bVqlUl+sUyxlReXm65Hex+3g+Mx3m4z6RQr169guPnnXde2O23t9xyC199VbYmqHnz5gXH33jjDc/rvf/++yXWN8ZUbl6SRuB93wdVdR5gXZum2LBhw8Km9+/fHxzfsGED5513HgB79uzxdLmqsLAwOB5oLymLffv2lXkdY0zF5CVpvAK8C4wVkdrAWl8jMqXy8qrXH3/8kSOOOILx48eXumx52yW2b69Sr203pkrz0mHhC6p6kapmq+p+Vb0pCXGZGLzc6hroEmTatGmlLnv//ffHFUfgTqvevXvHtb4xpuIp9XVwInI/0AfYBwigqnq+34GZ6DIyMkpdJtBYPnPmzJjLTZ48Oe44zjzzzLjXNcZUTF4uT10JnK2q56vqeZYw0sPChQtjzu/RowfgtHeICMOGDQtruwh44YUXguPnn1+2r9ZexmRM1eMlaXwDHO93IKZs2rRpQ716h15pcvrpp8dcfuTIkfzjH/8oUR76jo4bbrihTDFY0jCm6vGSNM4FPhORbBH5WkQS/yIHU2bVqlVj0aJFYdOtW7eOuc4DD5R8tXto0mjcuHGZYqhVq1ZwPNJZjDGm8vHSEH6OqrZU1SxVbaeq7ZMRmCndiSeeGBz32geViAQvXQHk5eUFx6+66qq4Y/nnP/8Z97rGmIrDy5v7zhCRt0VkhohUF5EhyQjMlK5GjRps2bIFgH79+tGqVStP6wXuqNq6dWvYO77jee944JmQwYMHl7KkMaYy8HKU+CdwO5ChqgeBS7xu3E0yi0TkfXe6uYjMF5EVIjJZRGq65bXc6ZXu/GYh23jALf9BRLqW5Y+rCo488kjy8/MZPHgwr7zyClOnTmXHjh3cfffdMdcTEZo0aVLu+hOxDWNMxeHpp6XbD1Xg0eLqZdj+IGBZyPQTwGhVPQnYAdzslt8M7FDVVsBodzlE5HSc233PAC4F/iUiZam/SqhZsyYiwhFHHMFVV11FgwYNePrpp2nZsqXvdfft29f3Oowx6cNL0vhYRP4PyBSRscCHXjYsIplAN2CcOy3ARcAUd5EJQE93vIc7jTv/Ynf5HsAkVc1X1TXASsDaVDzy8jR4QLydDl522WVxrWeMqZi8NIQ/DvwDGAqMUdUnPW77GeAvQOBo1BjYqaqB22xyOXQr7/HAere+QmCXu3ywPMI6phQXXHAB993nrWf7eF/mVL26nfgZU5V4aQj/QFVzVPUtVV0qIhM8rHMFsFlVQ59Ai3RU0lLmxVontL4B7i3B2YGGYeN48sknefHFF+naNXpz0AcfxP8GX0saxlQtUbsREZF2OJeCWonIHSHLt/Cw3d8C3UXkcqA2cATOmUcDEanhnk1kAj+7y+cCTYFcEakB1Ae2h5QHhK4TpKpjgbEAWVlZ8b2FqJISEQYOHMjAgQOjnk20bx//Fb/QfrAKCgqoWbNm3NsyxqS/WGcaBUAeToP1r+6wFSj1sWFVfUBVM1W1GU5D9ieqeh3wKXC1u9iNOL3nAkxzp3Hnf6JOn97TgD7u3VXNgZMAe7gwTrfffjvdu3cPK+vduzeNGjVKyPZffvnlhGzHGJO+op5pqOpiYDEwwX2fRgMiXy4qi/uBSSLyGLAI56VOuJ+vichKnDOMPm4M37nvJP8eKATudG/7NXH417/+BUDz5s1Zu3Ytjz76aIl3c5THtm3bErYtY0x68tLL7fNAa5xLRYLTpuD5TT2qOgeY446vJsLdT6q6H7gmyvojgZFe6zOlW7ZsGSNHjuTee+9NyPYGDRrEs88+y/HH2z0KxlR2Xm65/Y2qdlbV36tqX1Ut+6vdTFqpXbs2jz76qKf3cnhx883O4zaHH354QrZnjElfnnq5FZHfikhdETlMREp/bZypUgKN39dcE/Fk0RhTiZR6eQpo4w4BivOQnjEAHHfcccHxvXv3enodrTGmYio1aajq75IRiKm4Qt/rsXjx4mAnhsaYyifWcxpvEeFBOgBVvda3iEyFtmHDhlSHYIzxUawzDW/9TxgT4pprrsF5xMYYUxnFek5jXTIDMcYYk/7K/tYdY4wxVZYlDZMQ77//fqpDMMYkgSUNkxD169dPdQjGmCSwpGESolmzZqkOwRiTBJY0TEJkZmYGx/fs2ZPCSIwxfrKkYRIuJycn1SEYY3xiScMk3LRp01IdgjHGJ5Y0TMIEXv06atQofvnllxRHY4zxgyUNkzATJhx6ffyOHTtSGIkxxi+WNEzCHH300cHx1atXpzASY4xfLGmYhLn44ouD43fccUcKIzHG+MWShkkYkUOvkF+/fn0KIzHG+MWShjHGGM8saZiEGjVqVKpDMMb4yJKGSaisrKzgeG5ubgojMcb4wZKGSahOnToFx5s2bZrCSIwxfrCkYRKqZs2aqQ7BGOMjSxrGVHH79+/n7bffTnUYpoKwpGFMFbR48WLWrXPe6HzvvffSq1cvvvjiixRHZSoCX5KGiNQWkQUislhEvhOR4W55cxGZLyIrRGSyiNR0y2u50yvd+c1CtvWAW/6DiHT1I16TWFOnTk11CBXCwYMHOXjwoG/bz8vLIzMzk88++wyARYsWsWHDBlatWkWbNm1o1qwZY8eOZfr06QD88Y9/9C0WU4moasIHQIDD3fEMYD7QAXgT6OOWvwjc7o7fAbzojvcBJrvjpwOLgVpAc2AVUD1W3eecc46a1AMU0HXr1qU6lLSwdu1aBXTixInBMkAbNGjgS32ff/65TpkyJfg9rFq1Kjgea/j666/1119/1SlTpuiSJUt8ic2kJyBbPRzfa/iUiBTIcycz3EGBi4Dfu+UTgEeAF4Ae7jjAFOB5cR4v7gFMUtV8YI2IrATaA/P8iNsk3t///nfGjBmT6jBSbs6cOQAMHTqUvXv3Uq2ac5K/c+dOAE4++WSGDBnCpk2b6Nu3b7nehPjpp59y0UUXhZUtXrzY07rt2rULm960aRO33XYbL7/8Mg0aNIg7JlOJeMks8QxAdeBbnOTxBHAksDJkflMgxx3PATJD5q1yl38euD6kfDxwdax67UwjPRDy67Wqmz59esxf98uXLw+bPuOMM+Kq5+DBg7pw4UK98sorPZ1VeBmuvfZaBfQf//hHgveKSTd4PNPwrSFcVQ+qahsgE+fs4LRIi7mfEmVetPIwIjJARLJFJHvLli3xhmx8kp+fn+oQopozZw6zZ8+Oa91vv/2W3r17U1hYGHO5Bx98MOb8U089NWx69+7d7NmzJ/BDybMnn3ySc845h3feeadM68Xy5ptvAjB+/HiWLFkC+N8WY9Kcl8xS3gF4GPgzsBWo4ZadB3zsjn8MnOeO13CXE+AB4IGQ7QSXizbYmUZ6IOTX6kknnZTqcKIKxFhUVBT3ujNnztQZM2bo8OHDVVX1ueeeU0C3bdumu3fvjvtX/v3336/79u0L1vfss8/qddddpwcOHNDdu3eHxZKZmZmws4sm2+mNAAAVKUlEQVRYw4oVK4Lj06ZN04MHD4bFMXDgQB07dmwc34RJNTyeafiVJJoADdzxOsBc4ArgLcIbwu9wx+8kvCH8TXf8DMIbwldjDeEVwh133JHSS1RFRUV64MCBUpcLxNeyZUvt3LmzDhs2TL/55hu99957S00kkQ6qH330UcIP1Ndcc01Yfb169VJAFyxYoE8//bRecsklSUkYZRlMxeM1aYiW8RTYCxFpjdPQXR3ntt43VXWEiLQAJgGNgEU47RX5IlIbeA1oC2zHSSyr3W0NBfoDhcBgVZ0eq+6srCzNzs5O+N9kykZVg4294FxyqVevXtLqHzlyJMOGDWPPnj0cfvjhUZcL7c69uB9++IGTTz6ZpUuXUqdOHZo3b86KFSvYtGkT1atXD+sypSI65phj2Lhxoy/bPnjwYNj3b9KfiCxU1axSF/SSWSrSYGca6YNivz7/8pe/lLic4ZcTTzxRAX3rrbcizi8sLEz5r/FUDg0bNgw20Ldp00ZPOeWUhG7//vvv1507d+q+ffu0oKDA03e2detWXbFiRSL/GZgyIJWXp1I5WNJIH4FnE4oP8+bN86W+bdu26fTp07VOnTph9W3cuDFsueeffz5lB2vVksk0mcMPP/ygX3zxRYl9oqrarl07X//ugHXr1unKlStL1N+kSZMSy5rksaRh0kKkA8iDDz6okydP1u+++y5s2eXLl+tnn32mqqrff/+9Llu2LOI2p0yZooMGDdKdO3cGy3bt2hX1gFW/fn3dsWOHbtiwQV966aWUHbCffvrpqPvEr6F69er64osv6v79+0v9rvw8+/ryyy/1xRdfDCsrKCjQ999/XwHduXNnxARjkseShkkL33zzjedfoIGyCy64IDg+btw4/eijj4LLFL8bafbs2Tp79mwdOnRoypKBlyH0SfBIf4uq03h/9dVXJ6zOkSNHlvn7+vjjj4Pr9+/fXxs2bKgdOnQodywiEnP+ueeeGxxP1iVME86ShkkLpf16/eijjzQ/P7/Ua+q//vpryg/88Q6LFi2Kun/uuusufffdd8PKhg8fHndd3bp10z179uif/vQn/fXXX+P6znJycvSVV14JK/vzn/+ctP3Vt29f3bFjh86aNUuXLVsWvIutoKBA9+7dG9ffFA9ABw4cWOpyGzdu1NzcXP3mm2/0mGOO0caNGychusSzpGHSxuTJk2MeJG699dZSDyRdunTx5QC1bt067du3r3755Zd6ww03xLWNjIyMqPO2bdtW5v1VVFSkX375ZdRtdurUKWJ58eSTSAUFBcE2h2jDsGHDPH2X8Qzbt2/X9u3bKxw6O33nnXcUnKQcuL169+7dunnz5oT8zYG6Y4l2WbQisqRh0sqePXt8OZiUZzjqqKNKxDlu3Dh99dVXtU+fPmGJ6vXXXw+Ov/baa5qXl6eAPvXUU3rw4MGI27/66qvLtc+Kb+/xxx8Pe3akS5cu2qBBA/3ss8909erV5arLi61bt+pTTz0VFtPw4cOD3ZaMHj06uF/8HurXr1+ibMaMGcHxnJyccv+90RLAVVddpY888kjM+CoiSxomLfl9MDnuuOP0b3/7m86dO1d79OgRdbnly5drfn5+qfFu3LhRt2zZoqqqt912m3br1i3icgsWLNAvvvhCN2zYoPPnz9ft27eXe1/97ne/U0Dvu+8+XbNmTbm3lyih+1DVOTOaNGlS8Nf+p59+mvIfBID+5z//CYt76NCh+vXXX6uq6sqVK/Wll17y9HcG7sZ75ZVXPD/hX/wmj4rAkoZJS/n5+b4eKEJFa0957733UvTXl03gOYopU6akOpQw8+bNi3pnm6qTRM466yy96667Up44NmzYoB9++GHY2aCq6nHHHadA8BmS3NzcYNcsM2fO1Dlz5kTc3pgxYzzVW79+ff+/iASzpGHS2jPPPBP3gaBt27b69ttva05OTsykoao6ZMiQsPmdO3dOwV8bv7Vr16Y6hHI5cOBAyhMHOGcdgfHQf3t169bVqVOnKjh3eC1ZsiQh9WVkZKR615eZJQ2T1goKCvT666+P+p/ujjvu0Pz8fD322GNLzNuxY0fYtgLlzz//fIl6Aget2rVra15eXrL+PBMi8P1cdNFFumTJEr3gggu0X79+OnnyZC0oKEh5QvFrqGgsaZgK4d577w37j7Zr164Sv64D82bMmKHjx48vsY3A/GgdDM6fP79EojHJs3nzZm3WrFnU72DcuHEpP8D7MYwePTrJe7p8LGmYCqGoqEifeOKJmL/Ovv32W/3nP/8ZdRsV9ZedOSTQxnDPPfek/GCfyOGRRx5J9a71zJKGqVBefPFFbd68eVzrLl261Dq6q+C2b9+uq1at0qKiIl23bp3m5eVpXl6eLl26VFU12N0IoBdeeKHedNNNCoeeNK9bt64OGzYs5Uki0lCnTp0S7z9JR16Thi9do6eSdY1uTOU0e/Zs2rRpQ+PGjYNlLVq0YM2aNaxcuZKWLVtSrVo10vGYlp2dzTnnnJPqMGLy2jW6dXhvjKkQLr744rCEEUm8r+4ti2uvvZbRo0ezdetWcnJymDFjBkuWLKF///7s3LnT9/pTzsvpSEUa7PKUMVVHx44dFdCffvopWHbgwAHt3bt3mS4h1a1bN2x63759ETvBfP3110uNKVodTZs29XNXlBseL0/ZmYYxpsKaOnUqEyZMoGnTpsGyGjVqMGnSJIYMGcLcuXM9bWf37t3k5+fTt29fli9fTu3atXnssccoKioC4Morr+Txxx+nT58+cce6fv36uNdNJ9amYYyp1FavXk3Lli0BWLJkCa1bt6ZXr15MnTo1uExRUVHUV/9u376devXqkZGR4am+yZMnc8stt5CXl1diXjofb722aVjSMMZUehMnTqRx48Z06dKFH3/8kRNPPJFatWpx4okn8tNPPyX8YL5582aOPvroEuXDhg3jsMMO44EHHkhofYlgScMYY0qxYcMGFi5cSPfu3RO+7WhnLgC7du3iiCOOSHid5WF3TxljTCmOP/54XxIGwIIFC6LOu/vuuxkxYgTPPPMMmzdvBpxLZCNGjGDr1q1MnDiR/Px8X+IqLzvTMMYYn8Q62yhu6tSp9OrVi/r167Nr1y46dOjAvHnzfIwunJ1pGGNMBdKrVy/AuXQF8NVXX8VcftasWbz00kuoKmvXruXHH3/0PUaAGkmpxRhjTJmtXr2aFi1aBKfz8/OpVasWH374Id26dQPglFNO4YILLgCSc3eWnWkYY4xPFi9eXK4zgJYtW3LPPffQrVs3RITatWvTqVOnYMIAkv4UurVpGGNMEuzZs4e7776b8ePH+1ZHfn4+NWvWjGtda9Mwxpg0Uq9ePcaNG+frJaSuXbv6tu0AX5KGiDQVkU9FZJmIfCcig9zyRiIyU0RWuJ8N3XIRkedEZKWILBGRs0O2daO7/AoRudGPeI0xJpnWrVvny3bnzJnjy3ZD+XWmUQjcq6qnAR2AO0XkdGAIMFtVTwJmu9MAlwEnucMA4AVwkgzwMHAu0B54OJBojDGmojrhhBNSHULcfEkaqvqLqn7jju8BlgHHAz2ACe5iE4Ce7ngP4FW3s8WvgAYicizQFZipqttVdQcwE7jUj5iNMSaZXnvtteD4unXreOyxx2jWrBkXX3xxCqMqne9tGiLSDGgLzAeOVtVfwEkswFHuYscDoV1A5rpl0cqL1zFARLJFJHvLli2J/hOMMSbhrrvuOv74xz+yaNEiTjjhBIYOHcqaNWuYNWtWqkOLydekISKHA1OBwaq6O9aiEco0Rnl4gepYVc1S1awmTZrEF6wxxiSRiDBmzBjatGlTYt6iRYvi2marVq3KG1apfEsaIpKBkzDeUNW33eJN7mUn3M/Nbnku0DRk9Uzg5xjlxhhTaYUmkkh3Wz355JMR18vJyfEtpgC/7p4SYDywTFWfDpk1DQjcAXUj8G5I+Q3uXVQdgF3u5auPgS4i0tBtAO/ilhljTKU2ZMiQ4C20o0ePDnuV7aBBg4LjeXl5/PWvf2XQoEHUqlXL97h8ebhPRDoCc4GlQJFb/Fecdo03gROAn4BrVHW7m2Sex2nk3gv0U9Vsd1v93XUBRqrqK7Hqtof7jDGVVWFhIYWFhdSuXZu5c+eyatUqbrrppoRs296nYYwxxjN7ItwYY0zCWdIwxhjjmSUNY4wxnlnSMMYY45klDWOMMZ5Z0jDGGOOZJQ1jjDGeWdIwxhjjWaV7uE9EtgBlecPJkcBWn8Ipj3SNC9I3tnSNC9I3tnSNC9I3tnSNC8oX24mqWmqPr5UuaZSViGR7eQoy2dI1Lkjf2NI1Lkjf2NI1Lkjf2NI1LkhObHZ5yhhjjGeWNIwxxnhmSQPGpjqAKNI1Lkjf2NI1Lkjf2NI1Lkjf2NI1LkhCbFW+TcMYY4x3dqZhjDHGsyqbNETkUhH5QURWisiQFNS/VkSWisi3IhJ44VQjEZkpIivcz4ZuuYjIc26sS0Tk7ATH8rKIbBaRnJCyMsciIje6y68QkRsj1ZWg2B4RkQ3uvvtWRC4PmfeAG9sPItI1pDyh37eINBWRT0VkmYh8JyKD3PKU7rcYcaXDPqstIgtEZLEb23C3vLmIzHf//skiUtMtr+VOr3TnNyst5gTH9X8isiZkn7Vxy5P6f8DdbnURWSQi77vTqdtnqlrlBqA6sApoAdQEFgOnJzmGtcCRxcqeBIa440OAJ9zxy4HpgAAdgPkJjqUzcDaQE28sQCNgtfvZ0B1v6FNsjwD3RVj2dPe7rAU0d7/j6n5838CxwNnueD3gR7f+lO63GHGlwz4T4HB3PAPnTZ4dcN7m2cctfxG43R2/A3jRHe8DTI4Vsw9x/R9wdYTlk/p/wN32PcC/gffd6ZTts6p6ptEeWKmqq1W1AJgE9EhxTODEMMEdnwD0DCl/VR1fAQ1E5NhEVaqqnwPbyxlLV2Cmqm5X1R3ATJzX9/oRWzQ9gEmqmq+qa4CVON91wr9vVf1FVb9xx/cAy4DjSfF+ixFXNMncZ6qqee5khjsocBEwxS0vvs8C+3IKcLGISIyYEx1XNEn9PyAimUA3YJw7LaRwn1XVpHE8sD5kOpfY/7H8oMAMEVkoIgPcsqNV9Rdw/vMDR7nlqYi3rLEkO8Y/upcGXg5cAkpVbO4lgLY4v1DTZr8ViwvSYJ+5l1m+BTbjHFRXATtVtTBCPcEY3Pm7gMZ+xFY8LlUN7LOR7j4bLSK1isdVrH6/vstngL8ARe50Y1K4z6pq0pAIZcm+jey3qno2cBlwp4h0jrFsOsQbEC2WZMb4AtASaAP8Ajzllic9NhE5HJgKDFbV3bEWTWZsEeJKi32mqgdVtQ2QifNL97QY9SQttuJxiciZwAPAqUA7nEtO9yc7LhG5AtisqgtDi2PU43tsVTVp5AJNQ6YzgZ+TGYCq/ux+bgbewfkPtClw2cn93Owunop4yxpL0mJU1U3uf/Ii4CUOnWYnNTYRycA5ML+hqm+7xSnfb5HiSpd9FqCqO4E5OG0CDUSkRoR6gjG48+vjXKr0LbaQuC51L/WpquYDr5CaffZboLuIrMW5RHgRzplH6vZZeRpnKuoA1MBppGrOoUa+M5JYf12gXsj4lzjXPv9OeCPqk+54N8Ib3hb4EFMzwhubyxQLzi+xNTgNgA3d8UY+xXZsyPjdONdqAc4gvLFvNU6DbsK/b/fvfxV4plh5SvdbjLjSYZ81ARq443WAucAVwFuEN+re4Y7fSXij7puxYvYhrmND9ukzwKhU/R9wt38hhxrCU7bPEnrgqUgDzh0QP+JcUx2a5LpbuF/gYuC7QP041x5nAyvcz0ZuuQD/dGNdCmQlOJ6JOJcsDuD8Irk5nliA/jgNbCuBfj7G9ppb9xJgGuEHxKFubD8Al/n1fQMdcU7vlwDfusPlqd5vMeJKh33WGljkxpADPBTy/2GB+/e/BdRyy2u70yvd+S1KiznBcX3i7rMc4HUO3WGV1P8DIdu+kENJI2X7zJ4IN8YY41lVbdMwxhgTB0saxhhjPLOkYYwxxjNLGsYYYzyzpGGMMcYzSxrGGGM8s6RhKhwRaSYiW0Rkjoh8JSLnpEFMc9yuO0LLLhWRK93xh9yuqi8K6WvMjzh6ikj3COWHi8g8EXkqwrzzRSRHRDYWK/9YRCb5FaupmGqUvogxaekzVb1aRM7HefL6mlQHVJyqfhQyeYWqtgcQkSfx4bWcIvI/wB+ADBE5oKrTQ2b/BlikqvdGWPU7nC4yPg8tVNWubqLLUNUDiY7XVEyWNExF1wC3MzYRuQSnk7nDgbdVdZSI3ITTbXSGu+yLON0rHA50ccvewOkqYzPQG6ePnn/j9M1zMnCPqs4KVCgirXCesM4HflTVwJnDY+4LeXJU9Q637sPdbZ8mInNwenY9xR1/WFU/K/4HiUgTnL6O6gHrVfV6EfkG5wnfDsC/gPOBs3C6KZkoIu3c2PvgXEH4PxEJ7eiuFvBrpB2oqrvceiPN3ueua0nDOBL5mLsNNiRjwOmLagtOl99bgdZu+WHupwBf4fQjdBPwklv+KDDaHR+NkzRqAjXcsqeBS9zt/4Dzo+oknAQUWv/NwB/d8Wru5xygszv+X5yO4m4KWS47ZP3sUv6+0cCVxba/GieZHYbT3fVROAnvC4/77A/AsFKWKREXTpcjp6X6O7chfQZr0zAV1Weqei5OIjjXLWsrIrNwDuAtOPQuiyXu54Zi4w1xOpmbIiKf4XRSd5w7P0ed9xGsd5cL9SaQKSKvAteHlC9yP3NxDujxOhWn0zzU6ZUWYIeqrlfVvcByVd2sTo+staJtJEBEbsVJRG+HlL3qtsNcVMrqbwCfiUjHeP4QU/nY5SlT0f0L+FJEXsFp27gLWI5zKSdwvSW0g7XQcQGuA2ao6r9E5Oko6xS/blOoqkMAROR7EXndwzqhgsuJSCNgv5sMApbhdIn9rohUcxNHtL+hVKr6kojkA72A792yGzyufh1woap+X5Y6TeVlZxqmQlOngXYWzgFxKjAZp0fSiNfvI5gN3C4i7wLHeFynu4jMFZEFwEchZwNe/SAiU0WkA867n88rNv9xYIB79vNKGbcdzU84l8xKEJHT3DO0k0VkloicFTL7CHddYwCsl1tjUklEXgD+pIde3elXPecCt6rqLWVcbyFwrt/xmYrDzjSMSSFVvT1JB+TFwFGRntOIRkQ+Aj63hGFC2ZmGMcYYz+xMwxhjjGeWNIwxxnhmScMYY4xnljSMMcZ4ZknDGGOMZ/8fMXkXxTxZOsQAAAAASUVORK5CYII=\n", "text/plain": [ "
" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "plt.plot(spectrum[:,0],spectrum[:,1],\"k-\",label=\"My data\")\n", "plt.xlabel(r\"Raman shift, cm^{-1}\")\n", "plt.ylabel(r\"Intensity, counts\")\n", "plt.legend()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## `pandas`, Excel and csv files\n", "\n", "Now let's suppose that we have a list of spectra with other meta-information in a general excel spreadsheet. \n", "\n", "We could use numpy to try importing it, but this is a bad call since this mix numbers and letters (strings), which numpy can't deal with. Instead, we can use Pandas, which can cope with this, and also deals with missing values.\n", "\n", "You can have different sheets and indicate which one you want to import by setting the sheet_name variable to the value you want.\n", "\n", "If getting in trouble, look at the error message as sometimes some packages such as xlreader can be missing... If you see an error due to a missing package, just try installing the missing package and it should solve the problem." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Excel interactions" ] }, { "cell_type": "code", "execution_count": 9, "metadata": { "scrolled": true }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
SampleCrystalPTIR_E//AIR_E//CRamanRedoxMg#
01023Fo1.51200IR_A_sp1IR_B_sp1Raman_sp10.10.80
11024Fo2.01300IR_A_sp2IR_B_sp2Raman_sp20.20.85
21025En1.51400IR_A_sp3IR_B_sp3Raman_sp30.30.90
31026Fo1.01500IR_A_sp4IR_B_sp4Raman_sp40.20.95
41027En2.01200IR_A_sp5IR_B_sp5Raman_sp50.10.80
\n", "
" ], "text/plain": [ " Sample Crystal P T IR_E//A IR_E//C Raman Redox Mg#\n", "0 1023 Fo 1.5 1200 IR_A_sp1 IR_B_sp1 Raman_sp1 0.1 0.80\n", "1 1024 Fo 2.0 1300 IR_A_sp2 IR_B_sp2 Raman_sp2 0.2 0.85\n", "2 1025 En 1.5 1400 IR_A_sp3 IR_B_sp3 Raman_sp3 0.3 0.90\n", "3 1026 Fo 1.0 1500 IR_A_sp4 IR_B_sp4 Raman_sp4 0.2 0.95\n", "4 1027 En 2.0 1200 IR_A_sp5 IR_B_sp5 Raman_sp5 0.1 0.80" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "db_pd = pd.read_excel(\"data/data_cll.xlsx\", sheet_name=\"data_cll\")\n", "db_pd.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Another way would be to import a csv file" ] }, { "cell_type": "code", "execution_count": 10, "metadata": { "scrolled": true }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
SampleCrystalPTIR_E//AIR_E//CRamanRedoxMg#
01023Fo1.51200IR_A_sp1IR_B_sp1Raman_sp10.10.80
11024Fo2.01300IR_A_sp2IR_B_sp2Raman_sp20.20.85
21025En1.51400IR_A_sp3IR_B_sp3Raman_sp30.30.90
31026Fo1.01500IR_A_sp4IR_B_sp4Raman_sp40.20.95
41027En2.01200IR_A_sp5IR_B_sp5Raman_sp50.10.80
\n", "
" ], "text/plain": [ " Sample Crystal P T IR_E//A IR_E//C Raman Redox Mg#\n", "0 1023 Fo 1.5 1200 IR_A_sp1 IR_B_sp1 Raman_sp1 0.1 0.80\n", "1 1024 Fo 2.0 1300 IR_A_sp2 IR_B_sp2 Raman_sp2 0.2 0.85\n", "2 1025 En 1.5 1400 IR_A_sp3 IR_B_sp3 Raman_sp3 0.3 0.90\n", "3 1026 Fo 1.0 1500 IR_A_sp4 IR_B_sp4 Raman_sp4 0.2 0.95\n", "4 1027 En 2.0 1200 IR_A_sp5 IR_B_sp5 Raman_sp5 0.1 0.80" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "db_csv = pd.read_csv(\"data/data_cll.csv\")\n", "db_csv.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### Tip: pandas can read[ a lot of different formats](https://pandas.pydata.org/pandas-docs/stable/io.html)." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Pandas Indexing\n", "\n", "Your data is a bit different from when it was in a numpy array now, because it has labels as well as numerical indices.\n", "\n", "You can use numerical indices if you want, by using the `.iloc[row, column]` operator:" ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "0 1023\n", "1 1024\n", "2 1025\n", "3 1026\n", "4 1027\n", "Name: Sample, dtype: int64" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "db_pd.iloc[:, 0].head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "...but this doesn't make use of all the useful labels you've created. To use the named labels, you use the `.loc[row, column]` operator:" ] }, { "cell_type": "code", "execution_count": 12, "metadata": { "scrolled": true }, "outputs": [ { "data": { "text/plain": [ "0 1200\n", "1 1300\n", "2 1400\n", "3 1500\n", "4 1200\n", "5 1300\n", "6 1400\n", "7 1500\n", "8 1200\n", "9 1300\n", "10 1400\n", "11 1500\n", "12 1200\n", "13 1300\n", "14 1400\n", "15 1500\n", "16 1200\n", "17 1300\n", "18 1400\n", "19 1500\n", "20 1200\n", "21 1300\n", "22 1400\n", "23 1500\n", "24 1200\n", "25 1300\n", "26 1400\n", "27 1500\n", "28 1200\n", "29 1300\n", "30 1400\n", "31 1500\n", "32 1200\n", "33 1300\n", "34 1400\n", "35 1500\n", "36 1200\n", "37 1300\n", "38 1400\n", "39 1500\n", "40 1200\n", "41 1300\n", "42 1400\n", "43 1500\n", "44 1200\n", "45 1300\n", "46 1400\n", "47 1500\n", "48 1300\n", "Name: T, dtype: int64" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "db_pd.loc[:, \"T\"]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Note the use of the `:` here (like in numpy) to specify that you're selecting all the rows.\n", "\n", "#### Logical Indexing\n", "\n", "It's also possible to create 'logical indices' - i.e. select all rows that satisfy a certain set of conditions.\n", "\n", "To do this, you need an array of `True` and `False` (`boolean`) values the same length as you DataFrame.\n", "\n", "For example, to select all experiments done at 1200 °C:" ] }, { "cell_type": "code", "execution_count": 27, "metadata": { "scrolled": true }, "outputs": [ { "data": { "text/plain": [ "0 True\n", "1 False\n", "2 False\n", "3 False\n", "4 True\n", "5 False\n", "6 False\n", "7 False\n", "8 True\n", "9 False\n", "10 False\n", "11 False\n", "12 True\n", "13 False\n", "14 False\n", "15 False\n", "16 True\n", "17 False\n", "18 False\n", "19 False\n", "20 True\n", "21 False\n", "22 False\n", "23 False\n", "24 True\n", "25 False\n", "26 False\n", "27 False\n", "28 True\n", "29 False\n", "30 False\n", "31 False\n", "32 True\n", "33 False\n", "34 False\n", "35 False\n", "36 True\n", "37 False\n", "38 False\n", "39 False\n", "40 True\n", "41 False\n", "42 False\n", "43 False\n", "44 True\n", "45 False\n", "46 False\n", "47 False\n", "48 False\n", "Name: T, dtype: bool" ] }, "execution_count": 27, "metadata": {}, "output_type": "execute_result" } ], "source": [ "db_pd.loc[:, 'T'] == 1200" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "You can now use this directly to select the rows where `T` is equal to `1200`:" ] }, { "cell_type": "code", "execution_count": 28, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
SampleCrystalPTIR_E//AIR_E//CRamanRedoxMg#
01023Fo1.51200IR_A_sp1IR_B_sp1Raman_sp10.10.8
41027En2.01200IR_A_sp5IR_B_sp5Raman_sp50.10.8
81031En1.51200IR_A_sp9IR_B_sp9Raman_sp90.70.8
121035Fo1.51200IR_A_sp13IR_B_sp13Raman_sp130.50.8
161039En2.01200IR_A_sp17IR_B_sp17Raman_sp170.60.8
201043Fo1.51200IR_A_sp21IR_B_sp21Raman_sp211.00.8
241047En1.51200IR_A_sp25IR_B_sp25Raman_sp250.30.8
281051Fo2.01200IR_A_sp29IR_B_sp29Raman_sp290.50.8
321055En1.51200IR_A_sp33IR_B_sp33Raman_sp330.00.8
361059Fo1.51200IR_A_sp37IR_B_sp37Raman_sp370.20.8
401063Fo2.01200IR_A_sp41IR_B_sp41Raman_sp410.30.8
441067Fo1.51200IR_A_sp45IR_B_sp45Raman_sp451.00.8
\n", "
" ], "text/plain": [ " Sample Crystal P T IR_E//A IR_E//C Raman Redox Mg#\n", "0 1023 Fo 1.5 1200 IR_A_sp1 IR_B_sp1 Raman_sp1 0.1 0.8\n", "4 1027 En 2.0 1200 IR_A_sp5 IR_B_sp5 Raman_sp5 0.1 0.8\n", "8 1031 En 1.5 1200 IR_A_sp9 IR_B_sp9 Raman_sp9 0.7 0.8\n", "12 1035 Fo 1.5 1200 IR_A_sp13 IR_B_sp13 Raman_sp13 0.5 0.8\n", "16 1039 En 2.0 1200 IR_A_sp17 IR_B_sp17 Raman_sp17 0.6 0.8\n", "20 1043 Fo 1.5 1200 IR_A_sp21 IR_B_sp21 Raman_sp21 1.0 0.8\n", "24 1047 En 1.5 1200 IR_A_sp25 IR_B_sp25 Raman_sp25 0.3 0.8\n", "28 1051 Fo 2.0 1200 IR_A_sp29 IR_B_sp29 Raman_sp29 0.5 0.8\n", "32 1055 En 1.5 1200 IR_A_sp33 IR_B_sp33 Raman_sp33 0.0 0.8\n", "36 1059 Fo 1.5 1200 IR_A_sp37 IR_B_sp37 Raman_sp37 0.2 0.8\n", "40 1063 Fo 2.0 1200 IR_A_sp41 IR_B_sp41 Raman_sp41 0.3 0.8\n", "44 1067 Fo 1.5 1200 IR_A_sp45 IR_B_sp45 Raman_sp45 1.0 0.8" ] }, "execution_count": 28, "metadata": {}, "output_type": "execute_result" } ], "source": [ "db_pd.loc[db_pd.loc[:,\"T\"]==1200,:]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "These `boolean arrays` can be combined in various ways.\n", "\n", "For example if I want all experiments done between 1.5 and 2.0 GPa:" ] }, { "cell_type": "code", "execution_count": 29, "metadata": { "scrolled": true }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
SampleCrystalPTIR_E//AIR_E//CRamanRedoxMg#
01023Fo1.51200IR_A_sp1IR_B_sp1Raman_sp10.10.80
11024Fo2.01300IR_A_sp2IR_B_sp2Raman_sp20.20.85
21025En1.51400IR_A_sp3IR_B_sp3Raman_sp30.30.90
41027En2.01200IR_A_sp5IR_B_sp5Raman_sp50.10.80
61029Fo1.51400IR_A_sp7IR_B_sp7Raman_sp70.50.90
71030Fo2.01500IR_A_sp8IR_B_sp8Raman_sp80.30.95
81031En1.51200IR_A_sp9IR_B_sp9Raman_sp90.70.80
101033En2.01400IR_A_sp11IR_B_sp11Raman_sp110.00.90
121035Fo1.51200IR_A_sp13IR_B_sp13Raman_sp130.50.80
131036Fo2.01300IR_A_sp14IR_B_sp14Raman_sp140.30.85
141037En1.51400IR_A_sp15IR_B_sp15Raman_sp150.20.90
161039En2.01200IR_A_sp17IR_B_sp17Raman_sp170.60.80
181041Fo1.51400IR_A_sp19IR_B_sp19Raman_sp190.30.90
191042Fo2.01500IR_A_sp20IR_B_sp20Raman_sp200.70.95
201043Fo1.51200IR_A_sp21IR_B_sp21Raman_sp211.00.80
221045En2.01400IR_A_sp23IR_B_sp23Raman_sp231.00.90
241047En1.51200IR_A_sp25IR_B_sp25Raman_sp250.30.80
251048Fo2.01300IR_A_sp26IR_B_sp26Raman_sp260.20.85
261049En1.51400IR_A_sp27IR_B_sp27Raman_sp270.10.90
281051Fo2.01200IR_A_sp29IR_B_sp29Raman_sp290.50.80
301053Fo1.51400IR_A_sp31IR_B_sp31Raman_sp310.70.90
311054En2.01500IR_A_sp32IR_B_sp32Raman_sp321.00.95
321055En1.51200IR_A_sp33IR_B_sp33Raman_sp330.00.80
341057Fo2.01400IR_A_sp35IR_B_sp35Raman_sp350.50.90
361059Fo1.51200IR_A_sp37IR_B_sp37Raman_sp370.20.80
371060En2.01300IR_A_sp38IR_B_sp38Raman_sp380.10.85
381061En1.51400IR_A_sp39IR_B_sp39Raman_sp390.60.90
401063Fo2.01200IR_A_sp41IR_B_sp41Raman_sp410.30.80
421065En1.51400IR_A_sp43IR_B_sp43Raman_sp431.00.90
431066En2.01500IR_A_sp44IR_B_sp44Raman_sp440.00.95
441067Fo1.51200IR_A_sp45IR_B_sp45Raman_sp451.00.80
461069Fo2.01400IR_A_sp47IR_B_sp47Raman_sp470.20.90
481071En1.51300IR_A_sp49IR_B_sp49Raman_sp490.71.00
\n", "
" ], "text/plain": [ " Sample Crystal P T IR_E//A IR_E//C Raman Redox Mg#\n", "0 1023 Fo 1.5 1200 IR_A_sp1 IR_B_sp1 Raman_sp1 0.1 0.80\n", "1 1024 Fo 2.0 1300 IR_A_sp2 IR_B_sp2 Raman_sp2 0.2 0.85\n", "2 1025 En 1.5 1400 IR_A_sp3 IR_B_sp3 Raman_sp3 0.3 0.90\n", "4 1027 En 2.0 1200 IR_A_sp5 IR_B_sp5 Raman_sp5 0.1 0.80\n", "6 1029 Fo 1.5 1400 IR_A_sp7 IR_B_sp7 Raman_sp7 0.5 0.90\n", "7 1030 Fo 2.0 1500 IR_A_sp8 IR_B_sp8 Raman_sp8 0.3 0.95\n", "8 1031 En 1.5 1200 IR_A_sp9 IR_B_sp9 Raman_sp9 0.7 0.80\n", "10 1033 En 2.0 1400 IR_A_sp11 IR_B_sp11 Raman_sp11 0.0 0.90\n", "12 1035 Fo 1.5 1200 IR_A_sp13 IR_B_sp13 Raman_sp13 0.5 0.80\n", "13 1036 Fo 2.0 1300 IR_A_sp14 IR_B_sp14 Raman_sp14 0.3 0.85\n", "14 1037 En 1.5 1400 IR_A_sp15 IR_B_sp15 Raman_sp15 0.2 0.90\n", "16 1039 En 2.0 1200 IR_A_sp17 IR_B_sp17 Raman_sp17 0.6 0.80\n", "18 1041 Fo 1.5 1400 IR_A_sp19 IR_B_sp19 Raman_sp19 0.3 0.90\n", "19 1042 Fo 2.0 1500 IR_A_sp20 IR_B_sp20 Raman_sp20 0.7 0.95\n", "20 1043 Fo 1.5 1200 IR_A_sp21 IR_B_sp21 Raman_sp21 1.0 0.80\n", "22 1045 En 2.0 1400 IR_A_sp23 IR_B_sp23 Raman_sp23 1.0 0.90\n", "24 1047 En 1.5 1200 IR_A_sp25 IR_B_sp25 Raman_sp25 0.3 0.80\n", "25 1048 Fo 2.0 1300 IR_A_sp26 IR_B_sp26 Raman_sp26 0.2 0.85\n", "26 1049 En 1.5 1400 IR_A_sp27 IR_B_sp27 Raman_sp27 0.1 0.90\n", "28 1051 Fo 2.0 1200 IR_A_sp29 IR_B_sp29 Raman_sp29 0.5 0.80\n", "30 1053 Fo 1.5 1400 IR_A_sp31 IR_B_sp31 Raman_sp31 0.7 0.90\n", "31 1054 En 2.0 1500 IR_A_sp32 IR_B_sp32 Raman_sp32 1.0 0.95\n", "32 1055 En 1.5 1200 IR_A_sp33 IR_B_sp33 Raman_sp33 0.0 0.80\n", "34 1057 Fo 2.0 1400 IR_A_sp35 IR_B_sp35 Raman_sp35 0.5 0.90\n", "36 1059 Fo 1.5 1200 IR_A_sp37 IR_B_sp37 Raman_sp37 0.2 0.80\n", "37 1060 En 2.0 1300 IR_A_sp38 IR_B_sp38 Raman_sp38 0.1 0.85\n", "38 1061 En 1.5 1400 IR_A_sp39 IR_B_sp39 Raman_sp39 0.6 0.90\n", "40 1063 Fo 2.0 1200 IR_A_sp41 IR_B_sp41 Raman_sp41 0.3 0.80\n", "42 1065 En 1.5 1400 IR_A_sp43 IR_B_sp43 Raman_sp43 1.0 0.90\n", "43 1066 En 2.0 1500 IR_A_sp44 IR_B_sp44 Raman_sp44 0.0 0.95\n", "44 1067 Fo 1.5 1200 IR_A_sp45 IR_B_sp45 Raman_sp45 1.0 0.80\n", "46 1069 Fo 2.0 1400 IR_A_sp47 IR_B_sp47 Raman_sp47 0.2 0.90\n", "48 1071 En 1.5 1300 IR_A_sp49 IR_B_sp49 Raman_sp49 0.7 1.00" ] }, "execution_count": 29, "metadata": {}, "output_type": "execute_result" } ], "source": [ "db_pd.loc[(db_pd.loc[:,\"P\"] >= 1.5) & (db_pd.loc[:,\"P\"] <= 2),:]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Of course, the result of those queries can be saved in new variables, which will be new Pandas Dataframes" ] }, { "cell_type": "code", "execution_count": 30, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "pandas.core.frame.DataFrame" ] }, "execution_count": 30, "metadata": {}, "output_type": "execute_result" } ], "source": [ "db_subset = db_pd.loc[(db_pd.loc[:,\"P\"]>=1.5)&(db_pd.loc[:,\"P\"]<=2),:]\n", "type(db_subset)" ] }, { "cell_type": "code", "execution_count": 31, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
SampleCrystalPTIR_E//AIR_E//CRamanRedoxMg#
01023Fo1.51200IR_A_sp1IR_B_sp1Raman_sp10.10.80
11024Fo2.01300IR_A_sp2IR_B_sp2Raman_sp20.20.85
21025En1.51400IR_A_sp3IR_B_sp3Raman_sp30.30.90
41027En2.01200IR_A_sp5IR_B_sp5Raman_sp50.10.80
61029Fo1.51400IR_A_sp7IR_B_sp7Raman_sp70.50.90
71030Fo2.01500IR_A_sp8IR_B_sp8Raman_sp80.30.95
81031En1.51200IR_A_sp9IR_B_sp9Raman_sp90.70.80
101033En2.01400IR_A_sp11IR_B_sp11Raman_sp110.00.90
121035Fo1.51200IR_A_sp13IR_B_sp13Raman_sp130.50.80
131036Fo2.01300IR_A_sp14IR_B_sp14Raman_sp140.30.85
141037En1.51400IR_A_sp15IR_B_sp15Raman_sp150.20.90
161039En2.01200IR_A_sp17IR_B_sp17Raman_sp170.60.80
181041Fo1.51400IR_A_sp19IR_B_sp19Raman_sp190.30.90
191042Fo2.01500IR_A_sp20IR_B_sp20Raman_sp200.70.95
201043Fo1.51200IR_A_sp21IR_B_sp21Raman_sp211.00.80
221045En2.01400IR_A_sp23IR_B_sp23Raman_sp231.00.90
241047En1.51200IR_A_sp25IR_B_sp25Raman_sp250.30.80
251048Fo2.01300IR_A_sp26IR_B_sp26Raman_sp260.20.85
261049En1.51400IR_A_sp27IR_B_sp27Raman_sp270.10.90
281051Fo2.01200IR_A_sp29IR_B_sp29Raman_sp290.50.80
301053Fo1.51400IR_A_sp31IR_B_sp31Raman_sp310.70.90
311054En2.01500IR_A_sp32IR_B_sp32Raman_sp321.00.95
321055En1.51200IR_A_sp33IR_B_sp33Raman_sp330.00.80
341057Fo2.01400IR_A_sp35IR_B_sp35Raman_sp350.50.90
361059Fo1.51200IR_A_sp37IR_B_sp37Raman_sp370.20.80
371060En2.01300IR_A_sp38IR_B_sp38Raman_sp380.10.85
381061En1.51400IR_A_sp39IR_B_sp39Raman_sp390.60.90
401063Fo2.01200IR_A_sp41IR_B_sp41Raman_sp410.30.80
421065En1.51400IR_A_sp43IR_B_sp43Raman_sp431.00.90
431066En2.01500IR_A_sp44IR_B_sp44Raman_sp440.00.95
441067Fo1.51200IR_A_sp45IR_B_sp45Raman_sp451.00.80
461069Fo2.01400IR_A_sp47IR_B_sp47Raman_sp470.20.90
481071En1.51300IR_A_sp49IR_B_sp49Raman_sp490.71.00
\n", "
" ], "text/plain": [ " Sample Crystal P T IR_E//A IR_E//C Raman Redox Mg#\n", "0 1023 Fo 1.5 1200 IR_A_sp1 IR_B_sp1 Raman_sp1 0.1 0.80\n", "1 1024 Fo 2.0 1300 IR_A_sp2 IR_B_sp2 Raman_sp2 0.2 0.85\n", "2 1025 En 1.5 1400 IR_A_sp3 IR_B_sp3 Raman_sp3 0.3 0.90\n", "4 1027 En 2.0 1200 IR_A_sp5 IR_B_sp5 Raman_sp5 0.1 0.80\n", "6 1029 Fo 1.5 1400 IR_A_sp7 IR_B_sp7 Raman_sp7 0.5 0.90\n", "7 1030 Fo 2.0 1500 IR_A_sp8 IR_B_sp8 Raman_sp8 0.3 0.95\n", "8 1031 En 1.5 1200 IR_A_sp9 IR_B_sp9 Raman_sp9 0.7 0.80\n", "10 1033 En 2.0 1400 IR_A_sp11 IR_B_sp11 Raman_sp11 0.0 0.90\n", "12 1035 Fo 1.5 1200 IR_A_sp13 IR_B_sp13 Raman_sp13 0.5 0.80\n", "13 1036 Fo 2.0 1300 IR_A_sp14 IR_B_sp14 Raman_sp14 0.3 0.85\n", "14 1037 En 1.5 1400 IR_A_sp15 IR_B_sp15 Raman_sp15 0.2 0.90\n", "16 1039 En 2.0 1200 IR_A_sp17 IR_B_sp17 Raman_sp17 0.6 0.80\n", "18 1041 Fo 1.5 1400 IR_A_sp19 IR_B_sp19 Raman_sp19 0.3 0.90\n", "19 1042 Fo 2.0 1500 IR_A_sp20 IR_B_sp20 Raman_sp20 0.7 0.95\n", "20 1043 Fo 1.5 1200 IR_A_sp21 IR_B_sp21 Raman_sp21 1.0 0.80\n", "22 1045 En 2.0 1400 IR_A_sp23 IR_B_sp23 Raman_sp23 1.0 0.90\n", "24 1047 En 1.5 1200 IR_A_sp25 IR_B_sp25 Raman_sp25 0.3 0.80\n", "25 1048 Fo 2.0 1300 IR_A_sp26 IR_B_sp26 Raman_sp26 0.2 0.85\n", "26 1049 En 1.5 1400 IR_A_sp27 IR_B_sp27 Raman_sp27 0.1 0.90\n", "28 1051 Fo 2.0 1200 IR_A_sp29 IR_B_sp29 Raman_sp29 0.5 0.80\n", "30 1053 Fo 1.5 1400 IR_A_sp31 IR_B_sp31 Raman_sp31 0.7 0.90\n", "31 1054 En 2.0 1500 IR_A_sp32 IR_B_sp32 Raman_sp32 1.0 0.95\n", "32 1055 En 1.5 1200 IR_A_sp33 IR_B_sp33 Raman_sp33 0.0 0.80\n", "34 1057 Fo 2.0 1400 IR_A_sp35 IR_B_sp35 Raman_sp35 0.5 0.90\n", "36 1059 Fo 1.5 1200 IR_A_sp37 IR_B_sp37 Raman_sp37 0.2 0.80\n", "37 1060 En 2.0 1300 IR_A_sp38 IR_B_sp38 Raman_sp38 0.1 0.85\n", "38 1061 En 1.5 1400 IR_A_sp39 IR_B_sp39 Raman_sp39 0.6 0.90\n", "40 1063 Fo 2.0 1200 IR_A_sp41 IR_B_sp41 Raman_sp41 0.3 0.80\n", "42 1065 En 1.5 1400 IR_A_sp43 IR_B_sp43 Raman_sp43 1.0 0.90\n", "43 1066 En 2.0 1500 IR_A_sp44 IR_B_sp44 Raman_sp44 0.0 0.95\n", "44 1067 Fo 1.5 1200 IR_A_sp45 IR_B_sp45 Raman_sp45 1.0 0.80\n", "46 1069 Fo 2.0 1400 IR_A_sp47 IR_B_sp47 Raman_sp47 0.2 0.90\n", "48 1071 En 1.5 1300 IR_A_sp49 IR_B_sp49 Raman_sp49 0.7 1.00" ] }, "execution_count": 31, "metadata": {}, "output_type": "execute_result" } ], "source": [ "db_subset" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Split/Apply/Combine" ] }, { "cell_type": "code", "execution_count": 32, "metadata": {}, "outputs": [], "source": [ "groups = db_pd.groupby(['P', 'T']) # group data by pressure and temperature" ] }, { "cell_type": "code", "execution_count": 33, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
SampleRedoxMg#
meanstdmeanstdmeanstd
PT
1.01300104814.5012310.57500.3370040.850.0
1500104814.9666300.46250.2774240.950.0
1.51200104514.9666300.47500.3918820.800.0
13001071NaN0.7000NaN1.00NaN
1400104514.5012310.46250.2973090.900.0
2.01200104515.4919330.37500.2217360.800.0
1300104215.4919330.20000.0816500.850.0
1400105115.4919330.42500.4349330.900.0
1500104815.4919330.50000.4396970.950.0
\n", "
" ], "text/plain": [ " Sample Redox Mg# \n", " mean std mean std mean std\n", "P T \n", "1.0 1300 1048 14.501231 0.5750 0.337004 0.85 0.0\n", " 1500 1048 14.966630 0.4625 0.277424 0.95 0.0\n", "1.5 1200 1045 14.966630 0.4750 0.391882 0.80 0.0\n", " 1300 1071 NaN 0.7000 NaN 1.00 NaN\n", " 1400 1045 14.501231 0.4625 0.297309 0.90 0.0\n", "2.0 1200 1045 15.491933 0.3750 0.221736 0.80 0.0\n", " 1300 1042 15.491933 0.2000 0.081650 0.85 0.0\n", " 1400 1051 15.491933 0.4250 0.434933 0.90 0.0\n", " 1500 1048 15.491933 0.5000 0.439697 0.95 0.0" ] }, "execution_count": 33, "metadata": {}, "output_type": "execute_result" } ], "source": [ "groups.aggregate([np.mean, np.std]) # calculate mean and standard deviation of groups" ] }, { "cell_type": "code", "execution_count": 34, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "(1.0, 1300)\n", " Sample Crystal P T IR_E//A IR_E//C Raman Redox Mg#\n", "5 1028 En 1.0 1300 IR_A_sp6 IR_B_sp6 Raman_sp6 0.6 0.85\n", "9 1032 Fo 1.0 1300 IR_A_sp10 IR_B_sp10 Raman_sp10 1.0 0.85\n", "17 1040 En 1.0 1300 IR_A_sp18 IR_B_sp18 Raman_sp18 0.5 0.85\n", "21 1044 En 1.0 1300 IR_A_sp22 IR_B_sp22 Raman_sp22 0.0 0.85\n", "29 1052 En 1.0 1300 IR_A_sp30 IR_B_sp30 Raman_sp30 0.3 0.85\n", "33 1056 Fo 1.0 1300 IR_A_sp34 IR_B_sp34 Raman_sp34 1.0 0.85\n", "41 1064 Fo 1.0 1300 IR_A_sp42 IR_B_sp42 Raman_sp42 0.7 0.85\n", "45 1068 En 1.0 1300 IR_A_sp46 IR_B_sp46 Raman_sp46 0.5 0.85\n", "\n", "(1.0, 1500)\n", " Sample Crystal P T IR_E//A IR_E//C Raman Redox Mg#\n", "3 1026 Fo 1.0 1500 IR_A_sp4 IR_B_sp4 Raman_sp4 0.2 0.95\n", "11 1034 En 1.0 1500 IR_A_sp12 IR_B_sp12 Raman_sp12 1.0 0.95\n", "15 1038 Fo 1.0 1500 IR_A_sp16 IR_B_sp16 Raman_sp16 0.1 0.95\n", "23 1046 Fo 1.0 1500 IR_A_sp24 IR_B_sp24 Raman_sp24 0.5 0.95\n", "27 1050 En 1.0 1500 IR_A_sp28 IR_B_sp28 Raman_sp28 0.6 0.95\n", "35 1058 En 1.0 1500 IR_A_sp36 IR_B_sp36 Raman_sp36 0.3 0.95\n", "39 1062 Fo 1.0 1500 IR_A_sp40 IR_B_sp40 Raman_sp40 0.5 0.95\n", "47 1070 En 1.0 1500 IR_A_sp48 IR_B_sp48 Raman_sp48 0.5 0.95\n", "\n", "(1.5, 1200)\n", " Sample Crystal P T IR_E//A IR_E//C Raman Redox Mg#\n", "0 1023 Fo 1.5 1200 IR_A_sp1 IR_B_sp1 Raman_sp1 0.1 0.8\n", "8 1031 En 1.5 1200 IR_A_sp9 IR_B_sp9 Raman_sp9 0.7 0.8\n", "12 1035 Fo 1.5 1200 IR_A_sp13 IR_B_sp13 Raman_sp13 0.5 0.8\n", "20 1043 Fo 1.5 1200 IR_A_sp21 IR_B_sp21 Raman_sp21 1.0 0.8\n", "24 1047 En 1.5 1200 IR_A_sp25 IR_B_sp25 Raman_sp25 0.3 0.8\n", "32 1055 En 1.5 1200 IR_A_sp33 IR_B_sp33 Raman_sp33 0.0 0.8\n", "36 1059 Fo 1.5 1200 IR_A_sp37 IR_B_sp37 Raman_sp37 0.2 0.8\n", "44 1067 Fo 1.5 1200 IR_A_sp45 IR_B_sp45 Raman_sp45 1.0 0.8\n", "\n", "(1.5, 1300)\n", " Sample Crystal P T IR_E//A IR_E//C Raman Redox Mg#\n", "48 1071 En 1.5 1300 IR_A_sp49 IR_B_sp49 Raman_sp49 0.7 1.0\n", "\n", "(1.5, 1400)\n", " Sample Crystal P T IR_E//A IR_E//C Raman Redox Mg#\n", "2 1025 En 1.5 1400 IR_A_sp3 IR_B_sp3 Raman_sp3 0.3 0.9\n", "6 1029 Fo 1.5 1400 IR_A_sp7 IR_B_sp7 Raman_sp7 0.5 0.9\n", "14 1037 En 1.5 1400 IR_A_sp15 IR_B_sp15 Raman_sp15 0.2 0.9\n", "18 1041 Fo 1.5 1400 IR_A_sp19 IR_B_sp19 Raman_sp19 0.3 0.9\n", "26 1049 En 1.5 1400 IR_A_sp27 IR_B_sp27 Raman_sp27 0.1 0.9\n", "30 1053 Fo 1.5 1400 IR_A_sp31 IR_B_sp31 Raman_sp31 0.7 0.9\n", "38 1061 En 1.5 1400 IR_A_sp39 IR_B_sp39 Raman_sp39 0.6 0.9\n", "42 1065 En 1.5 1400 IR_A_sp43 IR_B_sp43 Raman_sp43 1.0 0.9\n", "\n", "(2.0, 1200)\n", " Sample Crystal P T IR_E//A IR_E//C Raman Redox Mg#\n", "4 1027 En 2.0 1200 IR_A_sp5 IR_B_sp5 Raman_sp5 0.1 0.8\n", "16 1039 En 2.0 1200 IR_A_sp17 IR_B_sp17 Raman_sp17 0.6 0.8\n", "28 1051 Fo 2.0 1200 IR_A_sp29 IR_B_sp29 Raman_sp29 0.5 0.8\n", "40 1063 Fo 2.0 1200 IR_A_sp41 IR_B_sp41 Raman_sp41 0.3 0.8\n", "\n", "(2.0, 1300)\n", " Sample Crystal P T IR_E//A IR_E//C Raman Redox Mg#\n", "1 1024 Fo 2.0 1300 IR_A_sp2 IR_B_sp2 Raman_sp2 0.2 0.85\n", "13 1036 Fo 2.0 1300 IR_A_sp14 IR_B_sp14 Raman_sp14 0.3 0.85\n", "25 1048 Fo 2.0 1300 IR_A_sp26 IR_B_sp26 Raman_sp26 0.2 0.85\n", "37 1060 En 2.0 1300 IR_A_sp38 IR_B_sp38 Raman_sp38 0.1 0.85\n", "\n", "(2.0, 1400)\n", " Sample Crystal P T IR_E//A IR_E//C Raman Redox Mg#\n", "10 1033 En 2.0 1400 IR_A_sp11 IR_B_sp11 Raman_sp11 0.0 0.9\n", "22 1045 En 2.0 1400 IR_A_sp23 IR_B_sp23 Raman_sp23 1.0 0.9\n", "34 1057 Fo 2.0 1400 IR_A_sp35 IR_B_sp35 Raman_sp35 0.5 0.9\n", "46 1069 Fo 2.0 1400 IR_A_sp47 IR_B_sp47 Raman_sp47 0.2 0.9\n", "\n", "(2.0, 1500)\n", " Sample Crystal P T IR_E//A IR_E//C Raman Redox Mg#\n", "7 1030 Fo 2.0 1500 IR_A_sp8 IR_B_sp8 Raman_sp8 0.3 0.95\n", "19 1042 Fo 2.0 1500 IR_A_sp20 IR_B_sp20 Raman_sp20 0.7 0.95\n", "31 1054 En 2.0 1500 IR_A_sp32 IR_B_sp32 Raman_sp32 1.0 0.95\n", "43 1066 En 2.0 1500 IR_A_sp44 IR_B_sp44 Raman_sp44 0.0 0.95\n", "\n" ] } ], "source": [ "# or use the groups in a loop:\n", "for i, g in groups:\n", " # i is the 'row-index', g is the data subset\n", " print(i)\n", " print(g)\n", " print('')\n", " # you can put any function you like inside the loop,\n", " # making this a really powerful technique." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Saving to excel\n", "\n", "And I can save my new data subset in a new excel file!" ] }, { "cell_type": "code", "execution_count": 35, "metadata": {}, "outputs": [], "source": [ "db_subset.to_excel('data/data_subset.xlsx')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "or even in the existing excel spreadsheet with a new sheet name. \n", "\n", "Let's do this last thing. \n", "\n", "First, let's come back to what does pd.read_excel that we saw previously.\n", "\n", "The pd.read_excel command is an easy command, also called high-level command, to interact with excel files in one line.\n", "\n", "It actually is the compilation of four steps, which are quite common when programming languages interacts with files on your system:\n", "\n", " 1) the command first open a \"channel\" to your file;\n", " 2) with this channel, which is actually a Python object, it reads things from the file.\n", " 3) it saves reading results in a new Pandas DataFrame;\n", " 4) it closes the \"channel\".\n", "\n", "Those steps are very frequent when trying to read files from computers with any languages. For instance, np.genfromtxt() is also a high-level command that performs similar steps, but saving the results in a numpy array. If you want to understand this better, you can have a read of the Python help: https://docs.python.org/3/tutorial/inputoutput.html#reading-and-writing-files\n", "\n", "Turning back to Pandas, for saving your DataFrame in Excel, there is no high-level interface and we need to do those four steps manually.\n", "\n", "We first need to open an excel writer, indicate which DataFrame we want to add to this writer, save it, and close it:" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Saving to the existing file in a new sheet\n", "\n", "We cannot apply the last method to save in a new sheet, because it overwrites the existing file. Instead, we can use the following method, found on [stackexchange](https://stackoverflow.com/questions/20219254/how-to-write-to-an-existing-excel-file-without-overwriting-data-using-pandas?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa):" ] }, { "cell_type": "code", "execution_count": 36, "metadata": {}, "outputs": [], "source": [ "from openpyxl import load_workbook\n", "\n", "with pd.ExcelWriter('data/data_cll.xlsx', engine='openpyxl') as writer:\n", " writer.book = load_workbook('data/data_cll.xlsx')\n", " db_subset.to_excel(writer, \"subset\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Getting bored with spreadsheets? SQL!\n", "\n", ".xlsx and .xls spreadsheets are very nice as long as your dataset is limited. Once you start to have more than 50-60 lines, files become hard to read and difficult to maintain. Furthermore, it is difficult to query only specific portions of your data with excel spreadsheets.\n", "\n", "For large datasets, SQL databases can be a good way to go, particularly if you want to be able to ask for specific portions of your dataset easily. Another reason to consider the use of a SQL database is if you want to put your dataset on a server at RSES, and be able to access it anywhere in the word with a different computer by running queries from your Python/Matlab/R scripts.\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Importing our data saved as an SQLite database.\n", "\n", "Several SQL database formats exist, each one being specialised in some use. For the general user, two formats stand out: SQLite, as an easy and powerful local one, and MySQL for online databases.\n", "\n", "In this example, I will use SQLite with the `sqlite3` python library (`sqlalchemy` seems also needed). A good tool to manipulate easily the SQL table is to use the DB Browser for SQLite software, for instance.\n", "\n", "REading and writing things in SQL database is done with queries. Please consult documentation at this link:\n", "\n", "http://www.sqlitetutorial.net/\n", "\n", "for further information. \n", "\n", "As for writing data in spreadsheet, we need to connect to the database betfore anything. Let's open a connector:" ] }, { "cell_type": "code", "execution_count": 37, "metadata": {}, "outputs": [], "source": [ "import sqlite3" ] }, { "cell_type": "code", "execution_count": 40, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "" ] }, "execution_count": 40, "metadata": {}, "output_type": "execute_result" } ], "source": [ "conn= sqlite3.connect('data/data.db')\n", "conn" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The connection is open. Now we actually need to run queries to get our data from this database. To facilitate this task, let's use the Pandas tool to save results from SQL queries in Pandas dataframe.\n", "\n", "One thing we could do, is to read the entire SQL table we want, which is data_cll:" ] }, { "cell_type": "code", "execution_count": 41, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
SampleCrystalPTIR_E//AIR_E//CRamanRedoxMg#
01023Fo1.51200IR_A_sp1IR_B_sp1Raman_sp10.10.8
11024Fo21300IR_A_sp2IR_B_sp2Raman_sp20.20.85
21025En1.51400IR_A_sp3IR_B_sp3Raman_sp30.30.9
31026Fo11500IR_A_sp4IR_B_sp4Raman_sp40.20.95
41027En21200IR_A_sp5IR_B_sp5Raman_sp50.10.8
\n", "
" ], "text/plain": [ " Sample Crystal P T IR_E//A IR_E//C Raman Redox Mg#\n", "0 1023 Fo 1.5 1200 IR_A_sp1 IR_B_sp1 Raman_sp1 0.1 0.8\n", "1 1024 Fo 2 1300 IR_A_sp2 IR_B_sp2 Raman_sp2 0.2 0.85\n", "2 1025 En 1.5 1400 IR_A_sp3 IR_B_sp3 Raman_sp3 0.3 0.9\n", "3 1026 Fo 1 1500 IR_A_sp4 IR_B_sp4 Raman_sp4 0.2 0.95\n", "4 1027 En 2 1200 IR_A_sp5 IR_B_sp5 Raman_sp5 0.1 0.8" ] }, "execution_count": 41, "metadata": {}, "output_type": "execute_result" } ], "source": [ "db_sql_1 = pd.read_sql_query(\"SELECT * FROM data_cll\",conn)\n", "db_sql_1.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Interesting thing: we can run queries while importing data, which avoids any further processing in Python. For instance, to grab all experiments performed at 1.0 GPa:" ] }, { "cell_type": "code", "execution_count": 42, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
SampleCrystalPTIR_E//AIR_E//CRamanRedoxMg#
01026Fo11500IR_A_sp4IR_B_sp4Raman_sp40.20.95
11028En11300IR_A_sp6IR_B_sp6Raman_sp60.60.85
21032Fo11300IR_A_sp10IR_B_sp10Raman_sp1010.85
31034En11500IR_A_sp12IR_B_sp12Raman_sp1210.95
41038Fo11500IR_A_sp16IR_B_sp16Raman_sp160.10.95
\n", "
" ], "text/plain": [ " Sample Crystal P T IR_E//A IR_E//C Raman Redox Mg#\n", "0 1026 Fo 1 1500 IR_A_sp4 IR_B_sp4 Raman_sp4 0.2 0.95\n", "1 1028 En 1 1300 IR_A_sp6 IR_B_sp6 Raman_sp6 0.6 0.85\n", "2 1032 Fo 1 1300 IR_A_sp10 IR_B_sp10 Raman_sp10 1 0.85\n", "3 1034 En 1 1500 IR_A_sp12 IR_B_sp12 Raman_sp12 1 0.95\n", "4 1038 Fo 1 1500 IR_A_sp16 IR_B_sp16 Raman_sp16 0.1 0.95" ] }, "execution_count": 42, "metadata": {}, "output_type": "execute_result" } ], "source": [ "db_sql_1GPa = pd.read_sql_query(\"SELECT * FROM data_cll WHERE P == '1'\",conn) # we use '1' here as the column P contains text and not numbers... this is my bad, as idealy the columns with numbers should be set as number in the SQLite database...\n", "db_sql_1GPa.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The interesting thing is that we can run very complex queries in a quite easy way:" ] }, { "cell_type": "code", "execution_count": 43, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
SampleCrystalPTIR_E//AIR_E//CRamanRedoxMg#
01027En21200IR_A_sp5IR_B_sp5Raman_sp50.10.8
11039En21200IR_A_sp17IR_B_sp17Raman_sp170.60.8
\n", "
" ], "text/plain": [ " Sample Crystal P T IR_E//A IR_E//C Raman Redox Mg#\n", "0 1027 En 2 1200 IR_A_sp5 IR_B_sp5 Raman_sp5 0.1 0.8\n", "1 1039 En 2 1200 IR_A_sp17 IR_B_sp17 Raman_sp17 0.6 0.8" ] }, "execution_count": 43, "metadata": {}, "output_type": "execute_result" } ], "source": [ "db_sql_2GPa_1200deg_Fo = pd.read_sql_query(\"SELECT * FROM data_cll WHERE P == '2' AND T == '1200'AND Crystal == 'En'\",conn) # we use '1' here as the column P contains text and not numbers... this is my bad, as idealy the columns with numbers should be set as number in the SQLite database...\n", "db_sql_2GPa_1200deg_Fo" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### IMPORTANT: Once finish, ALWAYS close the connector." ] }, { "cell_type": "code", "execution_count": 44, "metadata": {}, "outputs": [], "source": [ "conn.close()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## There are other formats out there...\n", "\n", "- HDF5\n", "- NETCDF \n", "- pickle\n", "- json" ] } ], "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.4" } }, "nbformat": 4, "nbformat_minor": 2 }