CollaboratoR Example
collaborator_example.RmdOverview
This walks through an example of a collection of example Google Sheets needed for collaborative data entry, how to configure the software (the pieces of infoomation you need), and how to automatically download and validate the data.
Example Data
This walk-through will use a small collection of short files from what’s known as “Fisher’s Iris data” or “Anderson’s Iris data”: a famous data set of measurments of flower sepals and petals collected by Edgar Anderson on the Gaspé Peninsula in Quebec (Anderson 1936).
As of this writing, there is a public folder in Google Docs/Workspace with these files
If this is no longer availableon Google (nothing is forever!), in our
example data repository, as a zip file containing XLSX formatted versions of these sheets that you may upload to your own Google Drive shared folder.
While this data set is small, they are split by species in the sheets to simulate collaborative data.
Getting started: setting up data sheets for entry
This package was designed to assist with a workflow that used spreadsheets for the following functions. After designing the goals and basic process/protocol for your study, to use spreadshseets to collect data and use this package, one should include the following types of sheets in the table below:
| Element | Description/Function | example sheet names | example sheet description |
|---|---|---|---|
| data entry protocol | (distinct from the data collection protocol, this is a detailed
description how data extract from field data or for a meta analysis, not specific to this package |
Iris data entry protocol | ficitious protocol for how measurements may be collaboratively entered for validation |
| data specification | list of variables included in data entry sheets with data types,
i.e. the ‘data dictionary.’ All variables here should be represented
in the data entry template |
measurements_spec | List of Sepal and petal measurements with grouping and entry information |
| data sheet template | this is not required but helpful. a blank spreadsheet with columns
as defined in the data specification. |
iris_template | example blank data sheet, to be copied and saved for per-species data entry |
| data sheets | copies of template filled with data, each row identifies who entered these data, and data/tasks is split on a logical grouping depending on your protocol/process. | iris_setosa, iris_versicolor, iris_virginica | per-species sepal and petal measurment data for this example |
| data sheet list | a table of all data sheets ready for validation with URLs for CollaboratoR to read from | id_urls | listing of data sheets above with Google Sheets URLs |
| group lookup values | columns holding identifying variables used groupming must be entered correctly. A list of the acceptable values for that grouping variable is useful for both the data entry template and for the validation step. An example categorical grouping column may be “species” (as in our example). CollaboratoR currently can only provide this function for one column. | species | a single column list of species included in this example project, used for both validation steps, and for limiting data in the data entry template spreadsheet |
Example Sheets setup
The folder for this example project has the following sheets in it
- Iris data entry protocol: fictitious protocol for how measurements may be collaboratively entered for validation.
- id_urls : table of data sheets and their associated google urls
- iris_template : example blank data sheet to use for new entries
- iris_setosa, iris_versicolor, iris_virginica : data sheets by species
- measurements_spec : the ‘data dictionary’ list of variables in data sheets with descriptions
- species : a single column list of species included in this data project, used for both validation steps, and for limiting data in the data entry template spreadsheet
Getting Started:
Write your data entry protocol
This example has a very simple data entry protocol “Iris data entry protocol” which does not include any information about field collection. The data entry protocol for a meta analysis is typically much more complex related to how data is extracted and how studies are selected and filtered out. See (ref)
Metadata
The main benefit of this package is that you can write a basic data dictionary describing the data sheet columns, which are used for collaboration
Per the ‘workflow’ vignette, this table must have columns as follows:
| Column | Description |
|---|---|
| col_name | exact name of column as it will appear in the datasheet |
| col_description | human reable description based on your data collection protocol |
| col_type | name of the datatype, see datatype list below |
| example | human readable example for guidance |
In this example the ” measurements_spec” Google sheet contains this information
| col_name | col_description | col_type | example |
|---|---|---|---|
| Species | full binomial / species name of observed plant | character | Iris setosa |
| Sepal.L. | Sepal length (cm) | double | 5.1 |
| Sepal.W. | Sepal width (cm) | double | 3.5 |
| Petal.L. | Petal length (cm) | double | 1.4 |
| Petal.W. | Petal width (cm) | double | 0.2 |
| Group.Id. | grouping variable (genus name) | factor | setosa |
| Entered.By. | initials of the person entering this row of data | character | ESA |
Species Look-up List
The CollaboratoR package can also check a column against a list. In this small example the column “Species” can have one of three entries. The sheet also called “species” is simply list of correct values for this column.
While not part of the CollaboratoR package, one can also use these lists in the data sheet templates.
Enter data into tabular form
The protocol should be clear how to split up the data into logical
groups across spreadsheets. To add more data to the project, copy the
template (if you’ve made one) and name the spreadsheet for the group you
are entering. In this example, copy the template sheet
iris_template and save as for example,
iris_setosa
Another option for grouping in this example could be by sampling site (if we knew it) Sampling site is not included as a column in our example but it could be. In that case sheets would be a mix of species which would also be ok. The reason to split the data across sheets is to split up the work (data extraction and data entry).
Add complete data sheets to list for validation
The package requires a list of sheets to download and read-in. In our
example that list is in a sheet creatively called
data sheet list
A sheet does not have to all the data in it to be included on this list, you just need to prepare it for validation.
Prepare an R project and git repository
The core of this workflow is not the google sheets, but an R project folder where you use CollaboratoR to pull in data entered into google sheets, validate and analyze.
We’ve prepared an example project on Github for these data:
The essential files are in this repository along with example data. To work through this tutorial you will have to make a similar repository and complete the configuration for Google APIs and optionally Github. In this R project folder you will need to install this package and it’s dependencies from github. Using the renv package is a great choice as it isolates projects that need different packages
For example: renv::install()
Configure Google Sheets for Automatic download
The vignette Google Sheets API setup using Google Cloud describes in detail how to set this. To work this example will need to create a Google cloud API to access these data sheets and update the project configuration
About Validation and Setup
As described in the workflow vignette, this package does two levels of validation: structural and constraints.
Initiate automated validation and report
In your R project,
- read data files (from list of URIs in CSV or Google Sheets)
- data format against schema (from column spec)
- category columns are checked against lists (i.e. species lists)
- data values against rules (from YAML file)
- report errors printed for user
save to git
- those sheets that passed validation are committed to git
TO DO - include actual data validation report - introduce both numeric and formatting errors to generate validation report items - histogram etc of EDA simple charts - to show outliers (e.g. errors)
References
Edgar Anderson (1936). “The species problem in Iris”. Annals of the Missouri Botanical Garden. 23 (3): 457–509. Bibcode:1936AnMBG..23..457A. doi:10.2307/2394164. JSTOR 2394164.