Laboratory data can be organized and analyzed using LabKey Assay folders. This topic walks you through the basics using a simple Excel spreadsheet standing in for typically more complex instrument output.
This topic is intended to be used alongside a LabKey Trial Server
. You should have another browser window open to view the Example Project > Laboratory Data
The "Laboratory Data" folder is an Assay
folder. You'll see four web parts in our example:
- 1. Learn About LabKey Assay Folders: a panel of descriptive information (not part of a default Assay folder).
- 2. Assay List: a list of assays defined in the folder. Here we have "Blood Test Data."
- 3. Blood Test Data Results: A query web part showing a grid of data.
- 4. Files: a file repository where you can browse files in this container or upload new ones.
The folder includes a simple assay design created from the "General Purpose Assay Type" (GPAT)
representing some blood test results. A single run (spreadsheet) of data has been imported using it. Continue below to try out the assay tools.
Try It Now
Using an Assay List
web part, you can browse existing assay designs and as an administrator, add new ones. An assay design tells the server how to interpret uploaded data. Click the name of the design in this web part to see the run(s) that have been uploaded.
- Click the name Blood Test Data in the Assay List to see the "Runs" imported using it.
- In this case, a single run (spreadsheet) "BloodTest_Run1.xls" has been imported. Click the Assay ID (in this case the file name) to see the results.
This example shows blood data for 3 participants over a few different dates. White blood counts (WBC), mean corpuscular volume (MCV), and hemoglobin (HGB) levels have been collected in an Excel spreadsheet.
To see the upload process, we can simulate importing the existing run. We will cancel the import before any reimporting actually happens.
- Click Re-Import Run above the grid.
- The first page shows:
- Assay Properties: These are fixed properties, like the assay design name, that are read only for all imports using this design.
- Batch Properties: These properties will apply to a set of files uploaded together. For example, here they include specifying how the data is identified (the Participant/Visit setting) and selecting a target study to use if any data is to be copied. Select /Example Project/Research Study (Research Study).
- Click Next.
- On the next page, notice the batch properties are now read only.
- Below them are Run Properties to specify; these could be entered differently for each run in the assay, such as the "Assay ID". If not provided, the name of the file is used, as in our example.
- Here you also provide the data from the assay run, either by uploading a data file (or reusing the one we already uploaded) or entering your own new data.
- Click Show Expected Data Fields. You will see the list of fields expected to be in the spreadsheet. When you design the assay, you specify the name, type, and other properties of expected columns.
- Click Download Spreadsheet Template to generate a blank template spreadsheet. It will be named "data_<datetimestamp>.xls". You could open and populate it in Excel, then upload it to this server as a new run. Save this template file and use it when you get to the Files section below.
- Since we are just reviewing the process right now, click Cancel after reviewing this page.
Next, review what the assay design itself looks like. You need to be an administrator to perform this step.
- From the Blood Test Data Runs page, select Manage Assay Design > Edit assay design to open it.
The sections correspond to how a user will set properties as they import a run.
- Assay Properties: Values that do not vary per run or batch.
- Batch Fields: Values set once for each batch of runs.
- Run Fields: Values set per run; in this case none are defined, but as you saw when importing the file, some built in things like "Assay ID" are defined per run.
- Data Fields: This is the heart of the assay design and where you would identify what columns are expected in the spreadsheet, what labels to use, what their datatype is. If you click a row, you can also set other properties of each column--including things like whether a value can be used as a "measure" in reporting, as shown here for the WBC field.
- Review the contents of this page, but make no changes and click Cancel.
- Return to the main folder page by clicking the Laboratory Data link near the top of the page.
Blood Test Data Results
The assay results are displayed in a web part on the main page. Explore some general features of LabKey data grids here:Column Header Menus
: Each column header has a menu of options to apply to the grid based on the values in that column.
- Click the header of the WBC column and select Sort Descending.
- Notice the (sort) icon that appears in the column header.
- Click the header of the Participant ID column and select Filter... to open a popup.
- Use the checkboxes to "Choose Values". For this example, uncheck the box for "202".
- The "Choose Values is only available when there are a limited number of distinct values.
- Switching to the "Choose Filters" tab in this popup would let you use filter expressions instead.
- Click OK to apply the filter.
- Notice the (filter) icon shown in the column header. There is also a new entry in the filter panel above the grid - you can clear this filter by clicking the for the "Participant <> 202" filter.
- Click the header of the MCV column and select Summary Statistics... to open a popup.
- Select the statistics you would like to show (the values are previewed in the popup.)
- Premium Feature: Many summary statistics shown are only available with premium editions of LabKey Server. Learn more here.
- In this case, Mean and Standard Deviation might be the most interesting. Click Apply.
- Notice the new row at the bottom of the grid showing the statistics we selected for this column.
The view of the grid now includes a sort, a filter, and a summary statistic.
Notice the message "This grid view has been modified" was added when you added the summary statistic. The grid you are now seeing is not the original default. You have not changed anything about the underlying imported data table, merely how you see it in this grid.
To save this changed grid view, making it persistent and sharable with others, follow these steps:
Visualizations and Reports
- Click Save next to the grid modification message.
- Select Named and give the new grid a name (such as "Grid with Statistics").
- Check the box for "Make this grid view available to all users."
- Click Save.
- Notice that the grid now has Grid With Statistics shown above it.
- To switch between grid views, use the (Grid Views) menu in the grid header. Switch between "default" and "Grid With Statistics" to see the difference.
- Return to the "default" view before proceeding with this walkthrough.
There is not enough data in this small spreadsheet to make meaningful visualizations or reports, but you can see how the options work:
- Column Visualizations: Each column header menu has a set of visualizations available that can be displayed directly in the grid view. Options vary based on the data type. For example, from the "WBC" column header menu, choose Box and Whisker.
- Quick Charts: Choose Quick Chart from the header menu of any column to see a quick best guess visualization of the data from that column. The default is typically a box and whisker plot.
- While similar to the column chart above, this chart creates a stand alone chart that is named and saved separately from the grid view.
- It also opens in the "Chart Wizard", described next, which offers many customization options.
- Note that a chart like this is backed by the "live" data in the source table. If you change the underlying data (either by editing or by adding additional rows to the table) the chart will also update automatically.
- Plot Editor: More types of charts and many more configuration options are available using the common plot editor, the "Chart Wizard".
- Return to the grid view if you navigated away.
- Select (Charts) > Create Chart above the grid.
- Select the type of chart you want using the options along the left edge.
Learn more about creating and customizing each chart type in the documentation. You can experiment with this small data set or use the more complex data when Exploring LabKey Studies
web part lists the single assay run as well as the log file from when it was uploaded.
To explore the abilities of the file browser, we'll create some new assay data to import.
- Open the template you downloaded earlier. It is an Excel file named "data_<datetimestamp>.xls"
- Enter some values. Some participant ID values to use are 101, 202, 303, 404, 505, and 606, though any integer values will be accepted. You can ignore the VisitID column and enter any dates you like. The WBC and MCV columns are integers, HGB is a double. The more rows of data you enter, the more "new" results you will have available to "analyze."
- Save the spreadsheet with the name "data_run2.xls" if you want to match our instructions.
- You can also click here to download ours if you want your data to match our screenshots: data_run2.xls
- Return to the Example Project > Laboratory Data folder.
- Drag and drop the "data_run2.xls" file into the Files web part to upload it.
- Select it using the checkbox and click Import Data.
- Scroll down to find the "Import Text or Excel Assay" category.
- By default, the "Blood Test Data" assay design we predefined is selected. Leave this choice, but notice you also have the option to create a new "General Purpose" assay design instead.
- Click Import.
- You will see the Assay Properties and be able to enter Batch Properties; select "Example Project/Research Study" as the Target Study.
- Click Next.
- On the Run Properties and Data File page, notice your "data_run2.xls" file listed as the Run Data. You don't need to make any changes on this page.
- Click Save and Finish.
- Now you will see the runs grid, with your new run added to our original sample.
- Click the file name to see the data you created.
- Notice the (Grid Views) menu includes the custom grid view "Grid with Statistics" we created earlier - select it.
- Notice the grid also shows a filter "Run = 2" (the specific run number may vary) because you clicked the run name to view only results from that run. Click the for the filter to delete it.
- You now see the combined results from both runs.
- If you saved any visualizations earlier using the original spreadsheet of data, view them now from the (Charts/Reports) menu, and notice they have been updated to include your additional data.
What Else Can I Do?
Define a New Assay
LabKey can help you create a new assay design from a spreadsheet of your own. Choose something with a few columns of different types, or simply add a few more columns to the "data_run2.xls" spreadsheet you used earlier. If you have existing instrument data that is in tabular format, you can also use that to complete this walkthrough.
Note that LabKey does have a few reserved names in the assay framework, including "container, created, createdBy, modified, and modifiedBy", so if your spreadsheet contains these columns you may encounter mapping errors if you try to create a new assay from it. There are ways to work around this issue, but for this getting-started tutorial, try renaming the columns or using another sample spreadsheet.
Here we use the name "mydata.xls" to represent your own spreadsheet of data.
- Navigate to the main page of the Laboratory Data folder.
- Drag and drop your "mydata.xls" file to the Files web part.
- Select the file and click Import Data.
- In the popup, choose "Create New General Assay Design" and click Import.
- Give your new assay design a Name, such as "MyData".
- Notice the default Location is the current project. Select instead the "Current Folder (Laboratory Data)".
- The Columns for Assay Data section shows the columns imported from your spreadsheet and the data types the server has inferred from the contents of the first few rows of your spreadsheet.
- If you see a column here that you do not want to import, simply uncheck it. If you want to edit column properties, you can do that using the (triangle) button next to the column name.
- You can change the inferred data types as necessary. For example, if you have a column that happens to contain whole number values, the server will infer it is of type "Integer". If you want it to be "Number (Double)" instead, select that type after clicking the (caret) button next to the type. If a column happens to be empty in the first few rows, the server will guess "Text (String)" but you can change that as well.
- Column Mapping below these inferrals is where you would map things like Participant ID and Date information. For instance, if you have a column called "When" that contains the date information, you can tell the server that here. It is not required that you provide mappings at all.
- When you are satisfied with how the server will interpret your spreadsheet, scroll back up and click Begin Import.
- You will be simultaneously creating the assay design (for this and future uses) and importing this single run. Enter batch properties if needed, then click Next.
- Enter run properties if needed, including an Assay ID if you want to use a name other than your file name.
- Click Show Expected Data Fields to review how your data will be structured. Notice that if you didn't provide mappings, new columns will be created for Specimen, Participant, Visit ID, and Date.
- Click Save and Finish.
- You now see your new run in a grid, and the new assay design has been created.
- Click the filename (in the Assay ID column) to see your data.
- Click column headers to sort, filter, or create quick visualizations.
- Select Manage Assay Design > Edit assay design to review the assay design.
- Click Laboratory Data to return to the main folder page.
- Notice your new assay design is listed in the Assay List.
- You can now upload and import additional spreadsheets with the same structure using it. When you import .xls files in the future, your own assay design will be one of the options for importing it.
Understand Copy to Study
You may have noticed the Copied to Research Study
column in the Blood Test Data Results
web part. The sample assay design includes copying data automatically to the Example Project/Research Study
folder on your Trial Server.
"Copying" data to a study does not literally copy the data. What is created is a dynamic link so that the assay data can be integrated with other data in the study about the same participants. When data changes in the original container, the "copied" set in the study is also updated.
Click the View Copy-to-Study History
link in the Blood Test Data Results
You will see at least one copy event from the original copy that occurred when our sample data was copied during the startup of your server (shown as being created by the "team lead"). If you followed the above steps and uploaded a second "data_run2.xls" spreadsheet, it will also be listed, created by you.
- Click View Results to see the result rows.
- Click one of the links in the Copied to Research Study column. You see what appears to be the same grid of data, but notice by checking the project menu or looking at the URL that you are now in the "Research Study" folder. You will also see the tabs present in a study folder.
- Click the Clinical and Assay Data tab.
- Under Assay Data you will see the "Blood Test Data" copied dataset. In the topic Exploring LabKey Studies we will explore how that data can now be connected to other participant information.
- Using the project menu, return to the Laboratory Data folder.
Other tutorials using "Assay" folders that you can run on your LabKey Trial Server:
To avoid overwriting this Example Project content with new tutorial content, you could create a new "Tutorials" project to work in. See Exploring Project Creation
for a walkthrough.
Explore More on your LabKey Trial Server