missing_values_found¶
This rule checks if a mandatory column has been left empty. A value is considered missing if it is an empty string, or an empty string once trimmed.
Consider a mandatory column named geoLat in a sites table. The
following ODM dataset snippet would fail validation due to the first row
having an empty string for the geoLat column value.
Invalid Dataset ┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓ ┃ siteID ┃ geoLat ┃ ┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩ │ 1 │ │ │ 2 │ 1 │ └────────────────────────────────────────────────────────┴────────────────────────────────────────────────────────┘
In addition, column values that are an empty string when trimmed should also fail this validation rule. For example, the following ODM data snippet would also fail validation,
Invalid Dataset ┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓ ┃ siteID ┃ geoLat ┃ ┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩ │ 1 │ │ │ 2 │ 1 │ └────────────────────────────────────────────────────────┴────────────────────────────────────────────────────────┘
A missingness value states why a value is absent, which is a valid way
of populating a mandatory column. It therefore does not trigger this
rule. The following dataset snippet would pass validation, since NA is
a missingness value that geoLat is allowed to take on. Which
missingness values a column can take on is decided by the
missingness rule, which is also the rule that forbids
them in an identifier (primary key) column.
Valid Dataset ┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓ ┃ siteID ┃ geoLat ┃ ┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩ │ 1 │ 2 │ │ 2 │ NA │ └────────────────────────────────────────────────────────┴────────────────────────────────────────────────────────┘
Warning report¶
A value that is invalid due to this rule should generate a warning and not an error. The warning report should have the following fields
warningType: missing_values_found
tableName: The name of the table containing the missing values
columnName: The name of the mandatory column containing the missing values
invalidValue: The invalid value
rowNumber: The index of the table row with the error
row: The dictionary containing the row
validationRuleFields: The ODM data dictionary rows used to generate this rule
message: Mandatory column <column_name> in table <table_name> has a missing value in row <row_index>
The warning report object for the example ODM datasets in the previous section are shown below.
Example 1 warning report,
{ │ 'warnings': [ │ │ { │ │ │ 'warningType': 'missing_values_found', │ │ │ 'tableName': 'sites', │ │ │ 'columnName': 'geoLat', │ │ │ 'rowNumber': 1, │ │ │ 'row': { │ │ │ │ 'siteID': '1', │ │ │ │ 'geoLat': '' │ │ │ }, │ │ │ 'invalidValue': '', │ │ │ 'validationRuleFields': [ │ │ │ │ { │ │ │ │ │ 'partID': 'geoLat', │ │ │ │ │ 'sites': 'header', │ │ │ │ │ 'sitesRequired': 'mandatory' │ │ │ │ } │ │ │ ], │ │ │ 'message': 'missing_values_found rule triggered in table sites, column geoLat, row(s) 1: Empty string found' │ │ } │ ], │ 'errors': [] }
Example 2 warning report,
{ │ 'warnings': [ │ │ { │ │ │ 'warningType': 'missing_values_found', │ │ │ 'tableName': 'sites', │ │ │ 'columnName': 'geoLat', │ │ │ 'invalidValue': ' ', │ │ │ 'rowNumber': 1, │ │ │ 'row': { │ │ │ │ 'siteID': '1', │ │ │ │ 'geoLat': ' ' │ │ │ }, │ │ │ 'validationRuleFields': [ │ │ │ │ { │ │ │ │ │ 'partID': 'geoLat', │ │ │ │ │ 'sites': 'header', │ │ │ │ │ 'sitesRequired': 'mandatory' │ │ │ │ } │ │ │ ], │ │ │ 'message': 'missing_values_found rule triggered in table sites, column geoLat, row(s) 1: Missing value " "' │ │ } │ ], │ 'errors': [] }
Rule metadata¶
All the metadata for this rule is contained in the parts sheet in the data dictionary. The steps involved are:
Add this rule for each mandatory column
For example the ODM snippet below will be used to generate the validation schema for the examples above,
Parts v2 ┏━━━━━━━━━━┳━━━━━━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━┓ ┃ partID ┃ partType ┃ status ┃ sites ┃ sitesRequired ┃ missingnessSet ┃ firstReleased ┃ lastUpdated ┃ ┡━━━━━━━━━━╇━━━━━━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━┩ │ NA │ missingness │ active │ NA │ NA │ NA │ 2.0.0 │ 2.0.0 │ │ sites │ tables │ active │ NA │ NA │ NA │ 1.0.0 │ 2.0.0 │ │ geoLat │ attributes │ active │ header │ mandatory │ genMissingnessSet │ 1.0.0 │ 2.0.0 │ │ geoLong │ attributes │ active │ header │ optional │ genMissingnessSet │ 1.0.0 │ 2.0.0 │ └──────────┴──────────────┴─────────┴─────────┴────────────────┴────────────────────┴───────────────┴─────────────┘
we would add this rule only to the geoLat column in the sites table.
This rule would not be added to the geoLong column in the same
table.
Cerberus Schema¶
Cerberus does not have a rule that fits this one. We need to extend the
cerberus
validator
with a custom rule called emptyTrimmed for empty values, which also
handles trimming. Although the cerberus library has an empty rule,
that rule allows values that are “empty” once trimmed.
The cerberus schema for the ODM dictionary snippet above is shown below,
{ │ 'schemaVersion': '2.0.0', │ 'schema': { │ │ 'sites': { │ │ │ 'type': 'list', │ │ │ 'schema': { │ │ │ │ 'type': 'dict', │ │ │ │ 'schema': { │ │ │ │ │ 'geoLat': { │ │ │ │ │ │ 'emptyTrimmed': False, │ │ │ │ │ │ 'meta': [ │ │ │ │ │ │ │ { │ │ │ │ │ │ │ │ 'ruleID': 'missing_values_found', │ │ │ │ │ │ │ │ 'meta': [ │ │ │ │ │ │ │ │ │ { │ │ │ │ │ │ │ │ │ │ 'partID': 'geoLat', │ │ │ │ │ │ │ │ │ │ 'sites': 'header', │ │ │ │ │ │ │ │ │ │ 'sitesRequired': 'mandatory' │ │ │ │ │ │ │ │ │ } │ │ │ │ │ │ │ │ ] │ │ │ │ │ │ │ } │ │ │ │ │ │ ] │ │ │ │ │ } │ │ │ │ }, │ │ │ │ 'meta': [ │ │ │ │ │ { │ │ │ │ │ │ 'partID': 'sites', │ │ │ │ │ │ 'partType': 'tables' │ │ │ │ │ } │ │ │ │ ] │ │ │ } │ │ } │ } }
The meta field for this rule should have one entry: the row from the
parts sheet for the mandatory column. The entry should include the
partID and the <table_name>Required fields.
ODM Version 1¶
For version 1 validation schemas, we add this rule to only those version 2 columns which have a version 1 equivalent part.
For the ODM snippet below,
Parts v1 ┏━━━━━━━━━┳━━━━━━━━━━┳━━━━━━━━┳━━━━━━━━┳━━━━━━━━━━┳━━━━━━━━━━┳━━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━┓ ┃ partID ┃ partType ┃ status ┃ sites ┃ sitesRe… ┃ missing… ┃ version… ┃ versio… ┃ version… ┃ firstR… ┃ lastUpd… ┃ ┡━━━━━━━━━╇━━━━━━━━━━╇━━━━━━━━╇━━━━━━━━╇━━━━━━━━━━╇━━━━━━━━━━╇━━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━┩ │ NA │ missing… │ active │ NA │ NA │ NA │ NA │ NA │ NA │ 2.0.0 │ 2.0.0 │ │ sites │ tables │ active │ NA │ NA │ NA │ tables │ Site │ NA │ 1.0.0 │ 2.0.0 │ │ geoLat │ attribu… │ active │ header │ mandato… │ genMiss… │ variabl… │ Site │ latitude │ 1.0.0 │ 2.0.0 │ │ geoLong │ attribu… │ active │ header │ optional │ genMiss… │ variabl… │ Site │ longitu… │ 1.0.0 │ 2.0.0 │ └─────────┴──────────┴────────┴────────┴──────────┴──────────┴──────────┴─────────┴──────────┴─────────┴──────────┘
The corresponding cerberus schema would be,
{ │ 'schemaVersion': '1.0.0', │ 'schema': { │ │ 'Site': { │ │ │ 'type': 'list', │ │ │ 'schema': { │ │ │ │ 'type': 'dict', │ │ │ │ 'schema': { │ │ │ │ │ 'latitude': { │ │ │ │ │ │ 'emptyTrimmed': False, │ │ │ │ │ │ 'meta': [ │ │ │ │ │ │ │ { │ │ │ │ │ │ │ │ 'ruleID': 'missing_values_found', │ │ │ │ │ │ │ │ 'meta': [ │ │ │ │ │ │ │ │ │ { │ │ │ │ │ │ │ │ │ │ 'partID': 'geoLat', │ │ │ │ │ │ │ │ │ │ 'sites': 'header', │ │ │ │ │ │ │ │ │ │ 'sitesRequired': 'mandatory', │ │ │ │ │ │ │ │ │ │ 'version1Location': 'variables', │ │ │ │ │ │ │ │ │ │ 'version1Table': 'Site', │ │ │ │ │ │ │ │ │ │ 'version1Variable': 'latitude' │ │ │ │ │ │ │ │ │ } │ │ │ │ │ │ │ │ ] │ │ │ │ │ │ │ } │ │ │ │ │ │ ] │ │ │ │ │ } │ │ │ │ }, │ │ │ │ 'meta': [ │ │ │ │ │ { │ │ │ │ │ │ 'partID': 'sites', │ │ │ │ │ │ 'partType': 'tables', │ │ │ │ │ │ 'version1Location': 'tables', │ │ │ │ │ │ 'version1Table': 'Site' │ │ │ │ │ } │ │ │ │ ] │ │ │ } │ │ } │ } }
The meta field for version 1 includes everything in version 2 but should also include the following columns for the entry for the mandatory column,
version1Locationversion1Tableversion1Variable
