Overview

It can be useful to transform data columns during the process of importing data to LabKey Server. For example, you can add a column that is calculated from several other columns in the dataset. For this simple example, we add a column that contains a randomly selected value.

Topics:

  • Set up the Perl Scripting Engine
  • Set up a New Assay Design
  • Import Data and Observe the Data Transformation
Additional documentation:

Set up the Perl Scripting Engine

Before you can run transform scripts, you need to set up the appropriate scripting engine. You only need to set up a scripting engine once per type of script (e.g., R or perl). You will need a copy of Perl running on your instrument to set up the engine.

  • Select the Admin > Site > Admin Console.
  • Click Views and Scripting.
  • Click Add > New Perl Engine.
    • Name: Perl Scripting Engine
    • Language: Perl
    • Language Version: Leave this blank
    • File Extension: pl
    • Program Path: Provide the path to the perl program on your instrument, including the name of the program. For example, on labkey.org, this is "/usr/bin/perl". A typical path on Windows is "C:\perl\bin\perl.exe".
    • Program Command: Leave this blank
    • Output File Name: Leave this blank
    • Enabled: Confirm this is checked.
    • Click "Submit"

Locate the Transform Script

To add a transform script to an assay design, you will need to enter a full path to it on your local machine. For example, if you are completing the assay tutorial on a local windows machine and uploaded the LabKeyDemoFiles package, you might find our sample perl script using a path like:

C:/Program Files (x86)/LabKey Server/files/home/Assay Tutorial/@files/LabKeyDemoFiles/Assays/Generic/GenericAssay_Transform.pl

You can also download GenericAssay_Transform.pl to the location of your choice now.

Set Up a New Assay Design

We create a new assay design in order to avoid losing the original GenericAssay design. Using this transform script requires adding a column to the design.

Copy the GenericAssay Design.

  • Navigate to the Assay Tutorial project page.
  • Click GenericAssay in the Assay List section.
  • Select Manage Assay Design > Copy Assay Design.
  • Click Copy to Current Folder.
Set Assay Properties
  • Set these properties in the Assay Properties section.
    • Name: GenericAssay_Transformed
    • Transform Script: Click Add Script and enter the full path to the perl transform script, GenericAssay_Transform.pl, on your local machine.
Add a Data Field.
  • In the Data Fields section:
    • Add a field named "Animal" of type "string."
  • Click "Save and Close."

Import data and observe the transformed column

Start Import

  • On the portal page, in the Files section, locate the file LabKeyDemoFiles/AssayData/Generic/GenericAssay_Run4.xls.
  • Click the Import Data button.
  • Select Use GenericAssay_Transformed and click Import.
Enter Batch Properties.
  • OperatorEmail: john@company.com
  • Instrument: ABI QSTAR
  • Click Next.
Enter Run Properties
  • Assay ID: Enter "Run4"
  • Comment: Enter whatever you wish.
  • Instrument setting: 24 (or any integer)
  • Press Save and Finish.

The transform script is run during data import and adds a column called "Animal" that contains a randomly selected type of animal. The transform script used here is arbitrary; you could easily alter the script to do something different. You can see the resulting dataset here in the demo on labkey.org and in the following screenshot:

Related Topics

Discussion

previousnext
 
expand all collapse all