-
Notifications
You must be signed in to change notification settings - Fork 3
CLI Manual
terminal command and options:
Usage: qctool csv <options> <csv file> <schema json>
This command produces a validation report for <csv file>.
The report file is stored in the same folder where <csv file> is located.
<schema json> file MUST be compliant with frirctionless data table-
schema specs(https://specs.frictionlessdata.io/table-schema/) or with
Data Catalogue json format.
Options:
--clean Flag for performing data cleaning.The cleaned file will
be saved in the report folder.
-m, --metadata [dc|qc] Select "dc" for Data Catalogue spec json
or "qc" for frictionless spec json.
-r, --report [xls|pdf] Select the report file format.
-o, --outlier FLOAT outlier threshold in standard deviations.
--help Show this message and exit.outlier threshold input field is related with the outlier detection for numerical variables of the incoming dataset. The way that the Data Quality Control tool handles the outlier detection of a certain numerical variable, is that first calculates the mean and the standard deviation based on the valid values of that column and then calculates the upper and the lower limit by the formula: upper_limit = mean + outlier threshold * standard deviation, lower_limit = mean - outlier threshold * standard deviation. If any value is outside those limits then it is considered as an outlier.
The report file will be saved in the folder where the incoming dataset file is located.
The Data Validation report has 2 major report types:
- Report with dataset's overall statistics and rows percentages about data completion and validation.
- Report for each dateset column with statistics about the values, validation results and suggestions for correcting the invalid data.
If Data Cleaning has been performed, then both reports are slightly different from the original ones, containing additional information about the data cleaning operation.
Here are the templates of those 2 types of reports with explanations for each subsection
This QC Report is created on: Date we run the data validation
Version of Data Quality Control Tool: version
File path: the file path location of the dataset csv file
Total number of rows: integer, Total number of columns: integer
Is metadata JSON file with table schema provided? Yes/No
Data Cleaning performed? Yes/No
Number of rows with invalid values: the total number of rows having at least one column with invalid data
Rows Data Completeness Overall Statistics Table with distribution of number of rows against number of filled columns per row
Rows Data Validation Overall Statistics Table with distribution of number of rows against number of filled -with valid data- columns per row
| Type | Text/Integer/Numerical/Nominal/Date |
| Total number of rows | column size in rows |
| Number of rows with data | how many filled values the column has |
| Completion percentage | The % ration of (filled values/column size) |
| Number of rows with constrain violation | how many rows contain a value that violates a specified value restriction (like min, max, enum etc) which is described in the dateset schema |
| Number of rows with datatype violation | how many rows contain a value that its type differs from the type specified for this particular column in the dataset's schema. |
| Data Cleansing applied? | Yes/No |
The statistics vary depending on the column datatype.
| statistic field | explanation |
|---|---|
| Mode | most frequent value in the column |
| Number of occurrences for the most frequent value (mode) | |
| Minimum value | |
| Maximum value | |
| 25% of records are below this value(limit value of the first quartile) | the middle number between the smallest number and the median of the set of values |
| 50% of records are below this value (median) | the median of the set of values / 50% of the data lies below this point |
| 75% of records are below this value (limit value of the third quartile) | the middle value between the median and the highest value of the set of values |
| statistic field | explanation |
|---|---|
| Mean | the expected value or average |
| Standard deviation | measure of the amount of variation of a set of values |
| Minimum value | |
| Maximum value | |
| 25% of records are below this value(limit value of the first quartile) | the middle number between the smallest number and the median of the set of values |
| 50% of records are below this value (median) | the median of the set of values / 50% of the data lies below this point |
| 75% of records are below this value (limit value of the third quartile) | the middle value between the median and the highest value of the set of values |
| Outlier upper bound | mean + 3 * standard deviation |
| Outlier lower bound | mean - 3 * standard deviation |
| Total number of outliers (outside 3 std.dev) | |
| Rows with outliers | list of [row number, value] |
| statistic field | explanation |
|---|---|
| Mode | most frequent value in the column |
| Number of occurrences for the most frequent value (mode) | |
| Minimum value | |
| Maximum value |
| statistic field | explanation |
|---|---|
| Count of unique values (for text variables) | |
| Most frequent value | most frequent value in the column |
| Number of occurrences for most frequent value | |
| 5 most frequent values | |
| 5 least frequent values |
| statistic field | explanation |
|---|---|
| Most frequent value | most frequent value in the column |
| Number of occurrences for most frequent value | |
| List of category values | |
| Number of categories |
If there are datatype violations and the tool succeeds to find corrections for some of them, then the suggested corrections are presented in a table with the following structure.
| invalid value | proposed correction |
|---|
If there are constraint violations and the tool succeeds to find corrections for some of them, then the suggested corrections are presented in a table with the following structure.
| invalid value | proposed correction |
|---|
The invalid values that the tool has not been able to propose any corrections, will be replaced with null. Those invalid values are presented in this section in a list.
The tool can perform data validation and cleaning to the following datatypes:
- numerical
- integer
- date
- nominal
We categorize the violations into two types:
- Constraint violations
- Datatype violations
Types of Constraint violations that are supported currently by the tool are:
- minimum (for date, integer, numerical)
- maximum (for date, integer, numerical)
- enum (list of enumerations for nominal datatypes)
Datatype violation is the case when a value in a column, has a different datatype from the one that has been declared for that column in the dataset's schema json file.
Below we can see the constraint violations per datatype that currently are supported by the tool, and the corresponding suggestions for the data cleaning operation. Also, in another table, we can see the datatype violations per datatype and the corresponding suggestions for the data cleaning operation.
| Constraint | Suggested replacement |
|---|---|
| Minimum | -> Null |
| Maximum | -> Null |
| wrong Datatype | Suggested replacement |
|---|---|
| date | -> Null |
| text | -> Null |
| Constraint | Suggested replacement |
|---|---|
| Minimum | -> Null |
| Maximum | -> Null |
| wrong Datatype | Suggested replacement |
|---|---|
| numerical(float) | -> integer(numerical) |
| date | -> Null |
| text | -> Null |
| Constraint | Suggested replacement |
|---|---|
| Minimum | -> Null |
| Maximum | -> Null |
| wrong Datatype | Suggested replacement |
|---|---|
| numerical | -> Null |
| integer | -> Null |
| text | try infer(Date) else Null |
| Constraint | Suggested replacement |
|---|---|
| enum | try spell-correction* else Null |
*We calculate the levenshtein distances between the given mis-pelled value with all the enumerations declared in the data schema. We suggest as corrected value the enumeration with distance smaller or equal to 3.
| wrong Datatype | Suggested replacement |
|---|---|
| numerical | -> Null |
| integer | -> Null |
The report has 3 major sections:
- Section with general information about the tool and execution parameters.
- Section with General and Validation Statistics
- Section with of MRI protocols
Here is the template of the MRI Sequences Report along with some comments and explanations.
This Report is created on: * Date the report is created* Version of Data Quality Control Tool: Version of the tool Main folder path where DICOM files are stored: folder path of the Dicom Root Folder Total subfolders scanned: number of subfolders that the tool has scanned and read the including dcm files, note there is an assumption of one subfolder per patient
Total number of patients with valid MRI sequences: Total number of valid MRI sequences: Total number of invalid MRI sequences: Total number of invalid DICOM (.dcm) files:
Here, all the distinct MRI protocols (of all valid and invalid Sequences) are listed in a table. Those protocols are retrieved from the SeriesDescription Dicom tag.