{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Edit Tabular Data\n",
    "Edit and compare tabular data using the Python `numpy` library."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this example, there is some existing tensile data for a material (AMS 6520, Plate, 1000°F).\n",
    "Some of that data is of particular interest and we want to separate it from the old data and\n",
    "put it in a record of its own."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Connect to MI\n",
    "Specify a database and table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import datetime\n",
    "import numpy as np\n",
    "import sys\n",
    "from GRANTA_MIScriptingToolkit import granta as mpy\n",
    "\n",
    "mi = mpy.connect('http://localhost/mi_servicelayer', autologon=True)\n",
    "dbase = mi.get_db(db_key='MI_Training')\n",
    "table = dbase.get_table('Tensile Statistical Data')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Locate existing data\n",
    "Find the record and attribute that contains the original data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "record = table.search_for_records_by_name('AMS 6520, Plate, 1000°F')[0]\n",
    "samples = record.attributes['Tensile test data used in this rollup']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Select some samples of interest."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "focus_samples = ['MTS-615731', 'MTS-615741', 'MTS-615771']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Get their **Record** objects from the tabular attribute containing the tensile test data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "focus_recs = [samples.linked_records[s][0] for s in focus_samples]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Get their row indices in the tabular data structure."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "focus_rows = [samples['Linking Value (Specimen ID)'].index(samp) for samp in focus_samples]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Extract and analyse the data\n",
    "Extract the sample data, and establish how different these particular samples are from the remainder of the set."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(142.74677022298178, 146.3169097900391)"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "focus_youngs = [samples[\"Young's Modulus (11-axis)\", i] for i in focus_rows]\n",
    "rmain_youngs = [samples[\"Young's Modulus (11-axis)\", i] for i in range(6) if i not in focus_rows]\n",
    "\n",
    "rmain_mean = np.mean(rmain_youngs)\n",
    "focus_mean = np.mean(focus_youngs)\n",
    "rmain_mean, focus_mean"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Percentage difference between the two means is: 2.470140535743884 %\n"
     ]
    }
   ],
   "source": [
    "print('Percentage difference between the two means is: '\n",
    "      '{} %'.format(100*abs(rmain_mean-focus_mean)/(0.5*(rmain_mean+focus_mean))))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create a new record and tabular attribute\n",
    "Create a new record for your data of interest."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "now = datetime.now().strftime(\"%c\")\n",
    "recordName = 'STK Example 9:{}'.format(now)\n",
    "new_rec = table.create_record(recordName, parent=record.parent, subsets={'Statistical Test Data'})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Select the tabular attribute to write to."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<TabularValue name: Tensile test data used in this rollup (not loaded)>"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_samples = new_rec.attributes['Tensile test data used in this rollup']\n",
    "new_samples"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Link the samples to the new tabular datum."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<TabularValue name: Tensile test data used in this rollup, shape: 9 x 3>"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "for focus_sample in focus_samples:\n",
    "    new_samples.add_row(linking_value=focus_sample)\n",
    "new_samples"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Set the new tabular attribute to update, and write the new record to MI."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_rec.set_attributes([new_samples])\n",
    "new_rec = mi.update([new_rec])[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Link to the original record\n",
    "The original record is linked to the records you just linked to the tabular data. Link the new record to the original, too.\n",
    "\n",
    "(Records must exist on the server to be linked together, which means the record must be pushed to the server before it can be linked to other records.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Tensile Test Data': {<Record long name:MTS-615731>,\n",
       "  <Record long name:MTS-615741>,\n",
       "  <Record long name:MTS-615771>}}"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_rec.set_links('Tensile Test Data', set(focus_recs))\n",
    "new_rec.links"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Update the new record's links on the server\n",
    "Like changes to data, changes to links also need to be pushed to the server. Unlike attributes, links do not need to be flagged for update (there is no equivalent to `set_attributes()` for links)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Record long name:STK Example 9:Tue Mar 23 16:15:42 2021>]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mi.update_links([new_rec])"
   ]
  }
 ],
 "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.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
