{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Loading Sample Metadata from Excel Spreadsheet\n",
    "\n",
    "## Configuration\n",
    "\n",
    "This code would normally go in a script automatically run at startup. The user would not have to worry about this."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%matplotlib widget\n",
    "%run startup.py\n",
    "\n",
    "# Set up simulated hardware.\n",
    "from ophyd.sim import SynGauss, SynAxis\n",
    "motor = SynAxis(name='motor')\n",
    "det = SynGauss('det', motor, 'motor', center=0, Imax=1,\n",
    "               noise='uniform', sigma=1, noise_multiplier=0.1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Generate an Excel spreadsheet we can use.\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "SAMPLE_MAP = {'sample1': {'name': 'AS-21_Spent', 'pos': 252},\n",
    "              'sample2': {'name': 'AS-21', 'pos': 259},\n",
    "              'sample3': {'name': 'AS-4-1_Spent', 'pos': 267},\n",
    "              'sample4': {'name': '30CoCeO2', 'pos': 276},\n",
    "              'sample5': {'name': '8CoCeO2', 'pos': 282},\n",
    "              'sample6': {'name': '2CoCeO2', 'pos': 290},\n",
    "}\n",
    "\n",
    "pd.DataFrame.from_dict(SAMPLE_MAP, orient='index').to_excel('samples.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Acquisition\n",
    "\n",
    "### Load an Excel spreadsheet with sample names and positions in Python as a DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "samples = pd.read_excel('samples.xlsx')\n",
    "samples"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Define a custom plan that loop over the samples"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def multi_sample_count(detectors, samples, position_motor,\n",
    "                       md=None, **kwargs):\n",
    "    \"For a table (DataFrame) of samples, move to the sample location and 'count'.\"\n",
    "    dets = list(detectors) + [position_motor]\n",
    "    for i, sample in samples.iterrows():\n",
    "        # Combine sample metadata with any user-specified metadata.\n",
    "        _md = dict(sample)\n",
    "        _md.update(md or {})\n",
    "\n",
    "        # Extract the sample position from the table and move the motor there.\n",
    "        pos = sample['pos']\n",
    "        yield from mv(position_motor, pos)\n",
    "\n",
    "        # Count.\n",
    "        yield from count(dets, md=_md, **kwargs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "RE(multi_sample_count([det], samples, motor))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Access the saved data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# All of the data\n",
    "db.get_table(db())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Filter by 'name' metadata (originally extracted from spreadsheet)\n",
    "db.get_table(db(name='AS-21'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# another example\n",
    "db.get_table(db(name='30CoCeO2'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercises\n",
    "\n",
    "1. Write a variation on ``multi_sample_count`` named ``multi_sample_scan`` that scans `motor` from -1 to 1 relative to the sample position.\n",
    "2. Use ``plan_name='multi_sample_scan'`` to narrow search results to scans (excluding previous data from counts).\n",
    "3. Add a parameter to ``multi_sample_count`` that is a list of sample names. Then make it skip any rows in the spreadsheet that with sample names not in that whitelist."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}