Normalisation¶

Normalisation steps¶

After aggregating all input files, files2db applies a series of normalisation rules when the --normalize option is enabled. The rules are defined in the FieldsRules table and are applied sequentially to each field.

These steps are applied in the following order:

1. Drop all only NA values rows and columns
2. Remove all ascii characters and encode data in UTF-8
3. Process each field defined in FieldsRules:
   1. Split values (Sep)
   2. Clean values (DelMatch, DelIn, DelStart, DelEnd and StripFrom)
   3. Convert data type (DataType)
   4. Apply value mappings with ValuesMap table
   5. Validate values
   6. Split fields (SepPattern)

Normalisation rules¶

Separate modalities¶

This step allows to split multiple values in a field using the specified separator.

With Sep as ;

Clean the field¶

This step allows to clean the field using regular expression patterns and is case sensitive.

• DelMatch: remove values that completely match the pattern
• DelIn: remove every occurrence of the pattern within the value
• DelStart: remove the pattern when it occurs at the beginning of the value
• DelEnd: remove the pattern when it occurs at the end of the value
• StripFrom: remove the pattern and everything that follows it

With {pattern} as A:

Convert data type¶

This step allows to convert the data type of the field to one of the following:

• lower: string converted to lowercase
• UPPER: string converted to uppercase
• Title: string converted to title case (first letter of each word capitalized)
• date: date in the format DD.MM.YYYY
• int: integer number (e.g. "1" and "1.2" will become 1)
• float: floating-point number (e.g. "1.2" and "1,2" will become 1.2)
• string: remains a string
• bool: boolean (e.g. case insensitive "TRUE", "1" will become True while "false" and 0 will become False)

Value mapping¶

This step allows to apply value mappings defined in the ValuesMap table. For each Fieldprovided, the OriginalValues will be split by a comma (,) and each resulting modalities, if fully matched, will be replaced by the NewValue.

With ValuesMap as:

Then :

Validate values¶

This step allows to validate the field values using regular expression patterns. Values that do not match the pattern will generate an error in the error output file.

There is two validation possible:

• Contains: list of values separated by a comma (,). This test is case sensitive.
• Min and Max: for numerical values only.

Split fields¶

This step allows to split the field into multiple fields using a regular expression pattern as separator. This use named regex pattern to capture the new field. SepPattern has to be in the form (?P<NewFieldName>{pattern}), KeepLink is a boolean stating if the NewFieldName should be appended to the old Field or as a separate new field.

Simple example¶

With SepPattern = (?P<Int>\\d+)|(?P<String>[A-Za-z]+)

More complex example¶

A more complex example when you expect either 1 or 2 value, and if two then split it into left and right.

With SepPattern = ((?P<Left>[A-E])(\/*)(?P<Right>[A-E]))|(?P<Mono>[A-E])