Data-flo
Data-floSource CodeCGPS
  • INTRODUCTION
    • What is Data-flo
    • Getting Started - Sign In
    • Privacy and Terms Of Service
    • Contact - Help & reporting errors
    • Change log
  • USING DATA-FLO
    • Data-flo site navigation
      • Transformations Page
      • Run Page
      • Canvas
    • Data
      • Bringing data in to Data-flo
      • Getting data out of Data-flo
      • Data Types
        • Boolean
        • Datatable
        • File
        • Graph
        • List
        • Map
        • Number
        • Text
    • Regular Expressions (RegEx)
    • Adaptors overview
      • Components of an adaptor
      • Binding types
        • Bind to Data-flo input
        • Bind to value
        • Bind to another transformation
    • Specific adaptors
      • add-column
      • append-to-list
      • calculate-time-difference
      • change-column-case
      • columns-concatenation
      • concatenate-text
      • create-microreact-project
      • csv-file-to-datatable
      • csv-to-datatable
      • datatable-columns
      • datatable-to-csv-file
      • datatable-to-graph
      • datatable-to-list
      • datatable-to-map
      • datatable-to-sqlite-file
      • date-to-text
      • dbf-file
      • dot-to-graph
      • download-file
      • dropbox-file
      • epicollect-project
      • extend-datatable
      • figshare-file
      • file-to-text
      • filter-blank-values
      • filter-columns
      • filter-list
      • filter-rows
      • filter-rows-numerically
      • force-directed-layout
      • format-date-column
      • forward-geocoding
      • ftp-file
      • gather-rows
      • google-drive-file
      • google-spreadsheet
      • graph-to-dot
      • join-datatables
      • list-to-datatable
      • lookup-map-value
      • merge-datatables
      • merge-lists
      • microreact-project
      • mysql-database
      • newick-leaf-labels
      • oracle-database
      • postgress-database
      • prepend-to-list
      • remove-columns
      • remove-duplicate-rows
      • rename-columns
      • replace-blank-values
      • replace-column-values
      • replace-text
      • replace-text-in-list
      • replace-value
      • reverse-geocoding
      • row-column-value
      • s3-file
      • select-columns
      • send-email-message
      • slice-datatable
      • slice-list
      • smb-file
      • sort-datatable
      • sort-list
      • split-column
      • split-datatable-rows
      • split-list
      • split-text
      • spread-rows
      • spreadsheet-file
      • sql-server-database
      • sqlite-database
      • sum-rows
      • text-template
      • text-to-file
      • unique-list-items
      • update-epicollect-entries
      • update-microreact-project
      • update-smb-file
      • upload-file-to-google-drive
      • upload-files-to-google-drive
      • url-builder
      • yaml-to-json
    • Building a data-flo
      • Debugging mode
      • Show detailed errors on Run Page
      • Permissions - Access Control
    • Tips & Tricks
  • TUTORIALS
    • Prep outbreak data for Microreact
    • Common use cases, solved
      • Fixing datatable headers
      • Select, remove, rename, reorder columns
      • Data in separate files
      • There's no single-column unique row ID (primary key)
      • Ensure non-dates stay non-dates
      • Connect directly to a database
      • Access files on a drive
Powered by GitBook
On this page
  • One column is separate from main data
  • Problem: Table 1 has plate layout, Table 2 has plate data
  • Solution: Create data map, Extend datatable using map for values argument
  • One shared column, many different columns
  • Problem: Lab data and Epi data in two files
  • Solution: Join datatables, keep multiple columns from both files
  • Multiple files with the same columns
  • Problem: Files have same columns, different rows
  • Solution: Import each file, then merge them

Was this helpful?

  1. TUTORIALS
  2. Common use cases, solved

Data in separate files

Example scenarios and solutions

PreviousSelect, remove, rename, reorder columnsNextThere's no single-column unique row ID (primary key)

Last updated 2 years ago

Was this helpful?

One column is separate from main data

Problem: Table 1 has plate layout, Table 2 has plate data

Example scenario: In a laboratory, there is a list of samples to test in a 96-well plate. There is a spreadsheet tracking which sample went into which well location.

The 96-well plate gets loaded onto the plate reader, which produces a data file with well number and measurement reading.

The plate reader measurements are useful when they are associated back to the samples. It's possible to associate them because both spreadsheets contain well location information.

  • Sample list: Sample ID, Well Location

  • Plate reader data: Well location and all the readings associated with that well.

Goal: associate the plate reader data with the sample IDs. Since Well Number is the same in both sheets, this is your Key in the Key-Value pair, and Sample is the Value.

Solution: Create data map, Extend datatable using map for values argument

While this problem can also be solved using , it is a simple single-column mapping that provides a good example of how the map data type can be useful. In this case,

One shared column, many different columns

When only one column is missing from the desired datatable, it can often be pulled in via a map (see Solution 1). When multiple columns need to be pulled from a second file, however, a larger JOIN is required.

Problem: Lab data and Epi data in two files

Making data-driven decisions requires information contained in many columns from LIMS (laboratory information management system) and many columns from a infectious disease surveillance system. The columns are different, but there is a unique ID that is common between the files.

Solution: Join datatables, keep multiple columns from both files

Multiple files with the same columns

Problem: Files have same columns, different rows

Examples scenario: GISAID limits the number of rows that can be downloaded at once, so multiple smaller files get created instead of a single file containing all the rows.

Solution: Import each file, then merge them

This is a simple use case for the adaptor. One datatable becomes the "main" datatable, and columns from the "other" datatable are pulled in via matching on the unique ID shared between the two datasets.

If the rows in two files could be all in the same spreadsheet without altering the column names or data types, you may want to copy-paste them into a single file. Data-flo can do that for you using the adaptor.

This is a simple use case for the adaptor. If there are more than two tables, the adaptor can be used multiple times, adding new rows each time.

join-datatables
merge-datatables
merge-datatables
Join-datatables
Data-flo - Run: Sample wells and well metrics - Data-flo
Example data-flo: Using the sample positions as Extend-datatable values map
Logo
The data-flo pulling sample location into metrics file