Streamlining Formulation Compound & Ingredient Management

Overview

This guide provides computational biologists with a streamlined workflow for managing formulation compounds and ingredients.

It focuses on scenarios where wet lab scientists can execute experiments while Python scripts automate quality control (QC) checks, process ingredient data, perform analysis on instrument outputs, and visualize results directly within the experiment page.

The examples assume familiarity with Python, CSV file handling, and the basics of formulation workflows.

Example data model:

How it looks in the GUI - link here.

Getting Started

Before diving into creation workflows, let us go through the workflow for the wet lab scientist.

The wet lab scientist receives an order for a new formulation
They will then create an experiment page out of a templatized SOP
This templatized SOP will outline the steps the wet lab scientist needs to follow for the formulation.
1. In this example, the SOP will include three labsheets, "Formulation Labsheet", "Ingredients Table", "Materials Manager", where the wet lab scientist will need to log the formulation recipe as well as other relevant metadata including metadata on the ingredients and materials used.
The wet lab scientist can then run a script that will perform QC checks on the values for the ingredients and flags deviations outside of the acceptable QC Range
After the wet lab scientist has verified that the formulation is ready, they will run another script that will send the relevant information to the LC-MS system for testing.
The LC-MS system will then generate raw data which will be processed and analyzed, then will update the LC-MS labsheet with the relevant data as well as upload any relevant charts to the experiment page

Setting up the scripts

1. Getting the experiment data and performing QC Checks

For this script we will be getting the data in the experiment page and performing QC checks on the "Ingredients Table". We will need the hrid (Human-Readable ID) of the experiment page. This can be found in the "Copy Page ID" option in the experiment page. If this script is run directly in the experiment page using Jupyter Scripts, this will be automatically filled in.

First thing we will need to do is fetch the experiment page. This will provide all the metadata for the page, including any ingredients that are added into the embedded labsheet

Code Example:

import requests
url = "https://cloudlab.scispot.io/labspace/experiment/fetch"
params = {
          "hrid" : "jeffrey/new-Experiment-11-26-24/13:59:33",
         }

headers = {
    "Content-Type": "application/json",
    "Authorization" : f"Bearer {YOUR_API_TOKEN}"
}

experiment_response = requests.get(url, headers=headers, params=params)
print(experiment_response.json()['labsheets'])

Example Output:

{
    "labsheets": [
        {
            "name": "Formulation Labsheet",
            "uuid": "c342416c-3516-44ff-82fa-7ef0fbcde146",
            "rows": [
                [
                    "d7762d21-30b5-49ae-a899-1e01f65afe2c",
                    "F002",
                    "102564325",
                    "Generate Reports",
                    "",
                    "C1CNP(=O)(OC1)N(CCCl)CCCl",
                    "InChI=1S/C7H15Cl2N2O2P/c8-2-5-11(6-3-9)14(12)10-4-1-7-13-14/h1-7H2,(H,10,12)",
                    "",
                    "30",
                    [],
                    ...
                ]
            ],
            "headers": [
                "uuid",
                "Formulation ID",
                "Batch Details",
                "Stages",
                "Reports",
                "SMILES",
                "inchi",
                "Structure",
                "Total Volume_mL",
                ...
            ],
            "position": 1
        },
        {
            "name": "Ingredient Table",
            "uuid": "9bd5cb7d-dd33-446a-90d8-ec0eaa6d7d05",
            "rows": [
                [
                    "6b6adfe5-a6d2-4876-9638-af29ab60822b",
                    "I006",
                    "ddH2O",
                    [
                        {
                            "label": "F001",
                            "labsheet": "Formulation Labsheet",
                            "uuid": "a37e2b25-e133-49fe-b642-c4b5ec264076"
                        }
                    ],
                    "13.5 mL",
                    "12.02 mL",
                    "Within Range",
                    "",
                    "150",
                    "",
                    "ebaca009-efe9-4a58-9faa-0531276c5a1f, dcb90fed-c8b9-460a-9f4a-3c21e709c4d8, 8efe4562-24af-402d-b9a2-4c52ab0134cc"
                ],
                [
                    "ca222549-9c66-4016-a525-865b9832ac64",
                    "I005",
                    "NaHCO3",
                    ...
            ],
            "headers": [
                "uuid",
                "Ingredient ID",
                "Ingredient Name",
                "Formulation",
                "Amount Needed",
                ...
            ],
            "position": 2
        },
        {
            "name": "A. Materials Manager",
            "uuid": "1c4fe5d2-e91a-4bc6-a785-99d8e1bfe0c7",
            "rows": [
                [
                    "986a91f8-be1c-45b5-a223-9d4a1271774c",
                    "Glacial Acetic Acid",
                    "Chemicals",
                    "",
                    "25",
                    "25",
                    "",
                    "",
                    ...
            ],
            "headers": [
                "uuid",
                "Item Name",
                "Category",
                "file",
                "Threshold Quantity",
                "Reorder Quantity",
                "Quantity Left",
                "Quantity consumed",
                "Total Price_ Consumed Item",
                "Project ",
                ...
            ],
            "position": 3
        }
}

Note: The output has been shortened

Next we get the labsheet that we are interested in, in this case it is the Ingredients Table. We can easily turn it into a pandas dataframe and then perform any QC checks that are needed

Code Example:

import pandas as pd

# Get the labsheet we are interested in
i_table = [d for d in data if d.get("name") == "Ingredient Table"]
df = pd.DataFrame(i_table["rows"], columns=i_table["headers"])

# Define thresholds
lower_threshold = 0.95  # 5% below the amount needed
upper_threshold = 1.05  # 5% above the amount needed

# Custom function to determine QC Check
def qc_check(row):
    amount_needed = row["Amount Needed"]
    data = row["Data from Instrument"]
    
    if lower_threshold * amount_needed <= data <= upper_threshold * amount_needed:
        return "Within Range"
    elif data < lower_threshold * amount_needed or data > upper_threshold * amount_needed:
        return "QC Deviated"
    else:
        return "Reaching QC Limits"

# Apply the QC Check logic
df["QC Check"] = df.apply(qc_check, axis=1)

Now we can then push this updated dataframe back into the Ingredient Labsheet

Code Example:

url = "https://cloudlab.scispot.io/labsheets/update-rows-by-id"

data_dict = df.to_dict('records')

params = {
    "labsheet": "Instrument Outputs",
    "rows" : data_dict
}


headers = {
    "Content-Type": "application/json",
    "Authorization": f"Bearer {token}"
}


response = requests.post(url, headers=headers, json=params)
print(response.json())

Now the data in the ingredients labsheet will update with the revelant QC Checks

2. Processing Data for LC-MS Instrument

For this script, the user has uploaded the raw LC-MS data into the experiment. We can then get this file analyze and process the data to a labsheet friendly format. This data will then be added into the "LC-MS Data" labsheet. We can also create charts from this data and upload it back into the experiment page

Like the script before we will first fetch the relevant metadata from the experiment. But this time we will be focused on the files

Code Example:

url = "https://cloudlab.scispot.io/labspace/experiment/fetch"
params = {
          "hrid" : "jeffrey/new-Experiment-11-26-24/13:59:33",
         }

headers = {
    "Content-Type": "application/json",
    "Authorization" : f"Bearer {YOUR_API_TOKEN}"
}

experiment_response = requests.get(url, headers=headers, params=params)
print(experiment_response.json())

Example Output:

{'uuid': 'be6a2709-aa2c-4b96-88f5-9b9a404df593', 
 'name': 'My Experiment', 'hrid': 'JeffreyChingLok.Ng/new-Experiment-08-27-24/22:58:20', 
'parent': 'My Labspace', 
'status': 'preparing', 
'created_at': '2024-08-28T02:58:34.012Z', 
'created_by': '[email protected]', 
'last_updated': '2025-01-03T21:38:42.485Z', 
'description': '', 
'content': '<div id="start-mark" /><section><h5 class="editor-heading-class">Objective</h5><p>Enter your objective of the experiment here</p></section><p></p><section><h5 class="editor-heading-class">Materials</h5><p>Enter all your materials by entering \\Labsheets here</p></section><embed-file uuid="5ad857cc-3452-415b-a410-4b193f1d3c6d" name="output.zip" type="zip" caption="" alt_text="" creator="CAD0AC6F-E97C-4E48-966E-2F737877A6AE" creation_date="2025-01-03T21:38:36.145Z"></embed-file>...', 'labsheets': [...], 
'sheets': [], 
'protocols': [], 
'manifests': [], 
'files': [{'position': 1, 'uuid': '5ad857cc-3452-415b-a410-4b193f1d3c6d', 'name': 'output_instrument.csv'}], 
'success': True}

This API call provides the full metadata and content of the experiment page. We are only interested in the instrument file here for processing. Now we will obtain the file content through the API by using the uuid we obtained above

Code Example:

url = "https://new.scispot.io/download/file"
file_uuid = response.json()['files'][0]['uuid']
params = {
    "uuid": file_uuid,
}


headers = {
    "Authorization": f"Bearer {YOUR_API_TOKEN}"
}


file_response = requests.get(url,headers=headers, params=params)


print(file_response.status_code)

With this we can then easily convert this into a pandas dataframe to do further processing, using the following

Code Example:

from io import BytesIO
import pandas as pd

csv_file = BytesIO(file_response.content)
df = pd.read_csv(csv_file)
''' FURTHER PROCESSING HERE '''

Now after we have processed the data, we would then want to push this processed data back into our labsheet.

Code Example:

rows = df.values.tolist()
url = "https://cloudlab.scispot.io/labsheets/add-rows"


params = {
    "labsheet": "Instrument Outputs",
    "rows" : rows
}


headers = {
    "Content-Type": "application/json",
    "Authorization": f"Bearer {token}"
}


response = requests.post(url, headers=headers, json=params)
print(response.json())

We can also plot and image and upload it to Scispot

Code Example:

import matplotlib.pyplot as plt


plt.plot(df['Absorbance'], df['Concentration'], marker='o')
plt.xlabel('Absorbance')
plt.ylabel('Concentration')
plt.title('Abosorbance vs Concentration')

buf = io.BytesIO()
plt.savefig(buf, format='png')
buf.seek(0)
plt.close()

url = "https://new.scispot.io/files/store"

headers = {
    "Authorization": f"Bearer {token}"
}

files = {
    "files": ("output.png", buf, "image/png"),
}

image_response = requests.post(url, headers=headers, files=files)

print(image_response.json())

Example Output:

[{'fileName': 'output.png', 'fileId': '4ad48825-7d65-45a7-85ca-75c93aed9e15', 'status': 'success', 'message': 'File uploaded successfully'}]

We can now use the fileId to upload this image back to the experiment page.

Code Example:

image = image_response.json()[0]
url = "https://cloudlab.scispot.io/labspace/experiment/write"


params = {
  "contentToHead": {"uuid" : image["fileId"],
                    "name" : "output"},
  "hrid": "jeffrey/new-Experiment-11-26-24/13:59:33",
  "contentType" : "image"
}


headers = {
    "Content-Type": "application/json",
    "Authorization": f"Bearer {token}"
}


response = requests.post(url, headers=headers, json=params)
print(response.json())

The script is now complete. In this workflow you have seen getting metadata from an experiment. Downloading a instrument file that the web lab scientist has uploaded. Processing the instrument file, then updating the labsheet with the data that was processed. Finally plotting a graph and uploading the graph directly back into the experiment page.