The Excel_plan() – batch scans using a spreadsheet#

Use a spreadsheet as a multi-sample batch scan tool.
We’ll need a spreadsheet with some things to be done and a plan that will read that spreadsheet and act on it.

This plan will:

  • ☒ use an Excel spreadsheet (for starters)

  • ☒ read a table from the spreadsheet file

  • ☒ take a single action from each row in the table

  • ☒ decide action based on a specific named column in the table

  • ☒ report all columns as metadata for the action

  • ☒ ignore empty rows

  • ☐ ignore any data outside of the table boundaries

Since the actions and parameters (args & kwargs) will be different in every implementation, this may prove difficult to generalize.given a spreadsheet (named sample_example.xlsx) with content as shown in the next figure:

``sample_example.xlsx``

Tip: Place the column labels on the fourth row of the spreadsheet, starting in the first column. The actions start on the next row. The first blank row indicates the end of the command table within the spreadsheet. Use as many columns as you need, one column per argument.

Here’s the demo, starting with the bluesky setup.

[1]:
# Import matplotlib and put it in interactive mode.
%matplotlib notebook
import matplotlib.pyplot as plt
plt.ion()

import pathlib

import databroker
cat = databroker.temp()

from bluesky import RunEngine
import bluesky.plans as bp
import bluesky.plan_stubs as bps
from bluesky.callbacks.best_effort import BestEffortCallback
from bluesky import SupplementalData
from bluesky.simulators import summarize_plan
from bluesky.suspenders import SuspendFloor

from ophyd.sim import motor1, motor2, motor3, SynGauss

import apstools.devices as APS_devices
import apstools.utils as APS_utils

RE = RunEngine({})
RE.subscribe(cat.v1.insert)
RE.subscribe(BestEffortCallback())
RE.preprocessors.append(SupplementalData())

shutter = APS_devices.SimulatedApsPssShutterWithStatus(name="shutter")
watch_for_shutter_close = SuspendFloor(shutter.pss_state, 1)

noisy_det = SynGauss('noisy_det', motor1, 'motor1', center=0, Imax=1,
                noise='uniform', sigma=0.9, noise_multiplier=0.1, labels={'detectors'})
noisy_det.kind = "hinted"

Excel plan and infrastructure#

[2]:
def beforeExcelPlan():
    """things to be done at the start of every Excel plan"""
    yield from bps.mv(
        shutter, "open",    # for example
    )


def afterExcelPlan():
    """things to be done at the end of every Excel plan"""
    yield from bps.mv(
        shutter, "close",   # for example
        motor1, 0,              # park the motors
        motor2, 0,
        motor3, 0,
    )


def common_step_scan(pos_X, pos_Y, thickness, scan_title, md={}):
    """
    run a step scan over a common range at given sample position
    """
    yield from bps.mv(
        motor2, pos_X,
        motor3, pos_Y,
    )
    md[motor2.name] = motor2.position
    md[motor3.name] = motor3.position
    md["shutter"] = shutter.state
    for k, v in md.items():
        print(f"{k}: {v}")

    yield from bps.install_suspender(watch_for_shutter_close)
    yield from bp.scan([noisy_det], motor1, -5, 5, 8, md=md)
    yield from bps.remove_suspender(watch_for_shutter_close)


def Excel_plan(xl_file, md={}):
    """
    example of reading a list of samples from Excel spreadsheet

    USAGE::

        summarize_plan(run_Excel_file("sample_example.xlsx"))
        RE(run_Excel_file("sample_example.xlsx"))
    """
    excel_file = pathlib.Path(xl_file)
    assert excel_file.exists()
    xl = APS_utils.ExcelDatabaseFileGeneric(str(excel_file))

    yield from beforeExcelPlan()
    for i, row in enumerate(xl.db.values()):
        # print(f"row={row}")

        # metadata
        # all parameters from this row go into the metadata
        # columns names are the keys in the metadata dictionary
        # make sure md keys are "clean"
        # also provide crossreference to original column names
        _md = {APS_utils.cleanupText(k): v for k, v in row.items()}
        _md["table_row"] = i+1
        _md["Excel_file"] = str(excel_file)
        _md["xl_file"] = xl_file
        _md["original_keys"] = {APS_utils.cleanupText(k): k for k in row.keys()}
        _md.update(md)      # overlay with user-supplied metadata

        scan_command = (row["Scan Type"] or "").lower()
        if scan_command == "step_scan":
            yield from common_step_scan(
                row["sx"],  # label must match cell string EXACTLY
                row["sy"],
                row["Thickness"],
                row["Sample Name"],
                # add all input as scan metadata, ensure the keys are clean
                md=_md,
                )
        elif scan_command == "some_other_action":
            pass  # TODO: suggestion
        else:
            print(f"no handling for table row {i+1}: {row}")
    yield from afterExcelPlan()


summarize_plan(Excel_plan("sample_example.xlsx"))
shutter -> open
motor2 -> 5.07
motor3 -> 8.3
Scan_Type: step_scan
sx: 5.07
sy: 8.3
Thickness: 0
Sample_Name: Water Blank
remarks: deionized
code_number: None
table_row: 1
Excel_file: sample_example.xlsx
xl_file: sample_example.xlsx
original_keys: {'Scan_Type': 'Scan Type', 'sx': 'sx', 'sy': 'sy', 'Thickness': 'Thickness', 'Sample_Name': 'Sample Name', 'remarks': 'remarks', 'code_number': 'code number'}
motor2: 0
motor3: 0
shutter: close
=================================== Open Run ===================================
motor1 -> -5.0
  Read ['noisy_det', 'motor1']
motor1 -> -3.571428571428571
  Read ['noisy_det', 'motor1']
motor1 -> -2.142857142857143
  Read ['noisy_det', 'motor1']
motor1 -> -0.7142857142857144
  Read ['noisy_det', 'motor1']
motor1 -> 0.7142857142857144
  Read ['noisy_det', 'motor1']
motor1 -> 2.1428571428571432
  Read ['noisy_det', 'motor1']
motor1 -> 3.571428571428571
  Read ['noisy_det', 'motor1']
motor1 -> 5.0
  Read ['noisy_det', 'motor1']
================================== Close Run ===================================
no handling for table row 2: OrderedDict([('Scan Type', 'other_scan'), ('sx', 5.07), ('sy', 8.3), ('Thickness', 0), ('Sample Name', 'Water Blank'), ('remarks', 'deionized'), ('code number', None)])
no handling for table row 3: OrderedDict([('Scan Type', 'this will be ignored (and also the next blank row will be ignored)'), ('sx', None), ('sy', None), ('Thickness', None), ('Sample Name', None), ('remarks', None), ('code number', None)])
shutter -> close
motor1 -> 0
motor2 -> 0
motor3 -> 0
[3]:
RE(Excel_plan("sample_example.xlsx"))
Scan_Type: step_scan
sx: 5.07
sy: 8.3
Thickness: 0
Sample_Name: Water Blank
remarks: deionized
code_number: None
table_row: 1
Excel_file: sample_example.xlsx
xl_file: sample_example.xlsx
original_keys: {'Scan_Type': 'Scan Type', 'sx': 'sx', 'sy': 'sy', 'Thickness': 'Thickness', 'Sample_Name': 'Sample Name', 'remarks': 'remarks', 'code_number': 'code number'}
motor2: 5.07
motor3: 8.3
shutter: open


Transient Scan ID: 1     Time: 2022-07-06 00:27:29
Persistent Unique Scan ID: 'a6ea8a82-aff2-4621-a197-1a3bca524e51'
New stream: 'primary'
+-----------+------------+------------+------------+
|   seq_num |       time |     motor1 |  noisy_det |
+-----------+------------+------------+------------+
|         1 | 00:27:29.7 |     -5.000 |      0.017 |
|         2 | 00:27:29.7 |     -3.571 |      0.021 |
|         3 | 00:27:29.7 |     -2.143 |      0.115 |
|         4 | 00:27:29.7 |     -0.714 |      0.658 |
|         5 | 00:27:29.7 |      0.714 |      0.697 |
|         6 | 00:27:29.7 |      2.143 |      0.155 |
|         7 | 00:27:29.7 |      3.571 |     -0.086 |
|         8 | 00:27:29.7 |      5.000 |      0.089 |
+-----------+------------+------------+------------+
generator scan ['a6ea8a82'] (scan num: 1)



no handling for table row 2: OrderedDict([('Scan Type', 'other_scan'), ('sx', 5.07), ('sy', 8.3), ('Thickness', 0), ('Sample Name', 'Water Blank'), ('remarks', 'deionized'), ('code number', None)])
no handling for table row 3: OrderedDict([('Scan Type', 'this will be ignored (and also the next blank row will be ignored)'), ('sx', None), ('sy', None), ('Thickness', None), ('Sample Name', None), ('remarks', None), ('code number', None)])
[3]:
('a6ea8a82-aff2-4621-a197-1a3bca524e51',)

Show what was collected by accessing the most recent run from the catalog.

[4]:
run = cat[-1]
run
[4]:
Start
code_number None
detectors
noisy_det
Excel_file sample_example.xlsx
hints
dimensions
[['motor1'], 'primary']
motor2 5.07
motor3 8.3
motors
motor1
num_intervals 7
num_points 8
original_keys
code_number code number
remarks remarks
Sample_Name Sample Name
Scan_Type Scan Type
sx sx
sy sy
Thickness Thickness
plan_args
args
SynAxis(prefix='', name='motor1', read_attrs=['readback', 'setpoint'], configuration_attrs=['velocity', 'acceleration'])
-5
5
detectors
SynGauss(prefix='', name='noisy_det', read_attrs=['val'], configuration_attrs=['Imax', 'center', 'sigma', 'noise', 'noise_multiplier'])
num 8
per_step None
plan_name scan
plan_pattern inner_product
plan_pattern_args
args
SynAxis(prefix='', name='motor1', read_attrs=['readback', 'setpoint'], configuration_attrs=['velocity', 'acceleration'])
-5
5
num 8
plan_pattern_module bluesky.plan_patterns
plan_type generator
remarks deionized
Sample_Name Water Blank
scan_id 1
Scan_Type step_scan
shutter open
sx 5.07
sy 8.3
table_row 1
Thickness 0
time a second ago (2022-07-06T00:27:29.644067)
uid a6ea8a82-aff2-4621-a197-1a3bca524e51
versions
bluesky 1.8.3
ophyd 1.6.4
xl_file sample_example.xlsx
Stop
exit_status success
num_events
primary 8
reason
run_start a6ea8a82-aff2-4621-a197-1a3bca524e51
time a second ago (2022-07-06T00:27:29.781108)
uid 128c5ecf-2c21-497e-b7f1-6b74614bfb45
Descriptors
primary
configuration
motor1
data
motor1_acceleration 1
motor1_velocity 1
data_keys
motor1_acceleration
dtype integer
shape
source SIM:motor1_acceleration
motor1_velocity
dtype integer
shape
source SIM:motor1_velocity
timestamps
motor1_acceleration 1657085249.0746343
motor1_velocity 1657085249.0746002
noisy_det
data
noisy_det_center 0
noisy_det_Imax 1
noisy_det_noise uniform
noisy_det_noise_multiplier 0.1
noisy_det_sigma 0.9
data_keys
noisy_det_center
dtype integer
shape
source SIM:noisy_det_center
noisy_det_Imax
dtype integer
shape
source SIM:noisy_det_Imax
noisy_det_noise
dtype integer
enum_strs
none
poisson
uniform
shape
source SIM:noisy_det_noise
noisy_det_noise_multiplier
dtype number
shape
source SIM:noisy_det_noise_multiplier
noisy_det_sigma
dtype number
shape
source SIM:noisy_det_sigma
timestamps
noisy_det_center 1657085249.298179
noisy_det_Imax 1657085249.2981858
noisy_det_noise 1657085249.298202
noisy_det_noise_multiplier 1657085249.2982059
noisy_det_sigma 1657085249.2981968
data_keys
motor1
dtype number
object_name motor1
precision 3
shape
source SIM:motor1
motor1_setpoint
dtype number
object_name motor1
precision 3
shape
source SIM:motor1_setpoint
noisy_det
dtype number
object_name noisy_det
precision 3
shape
source SIM:noisy_det
hints
motor1
fields
motor1
noisy_det
fields
noisy_det
name primary
object_keys
motor1
motor1
motor1_setpoint
noisy_det
noisy_det
run_start a6ea8a82-aff2-4621-a197-1a3bca524e51
time a second ago (2022-07-06T00:27:29.654973)
uid 9fc2e538-dfde-48a0-a70a-b6ad3ac3c493