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:
Next... Step 6: Integrate Assay Data into a Study.
