Ability to preserve missingValues in dump

[cschloer]

Passing this on to you @roll as I think it will be a pretty big change across multiple repos that will require your codebase knowledge. See previous discussion here: BCODMO/frictionless-usecases#32 as well as previous PRs here: frictionlessdata/tableschema-py#260, #175, datahq/dataflows#119.

Sometimes missingValues have meaning beyond "no measurement taken", and sometimes one field will have multiple different kinds of missingValues (such as no measurement taken, measurement below threshold, etc). It's very important that we be able to preserve that data, and up until now we are usually just keeping the field as a string type even though it is clearly a number. It would be great if there was an option to dump_to_path, or a different kind of missingValue parameter, that would show the missingValues properly after the dump.

Solution thoughts:

• update all processors to check if the value is in the missingValues parameter before doing any kind of processing. If in the missingValue list, continue. [probably way too much work]
• create some kind of data type that is interpreted as None by all of the processors but dump_to_path can see it and extract the original value. During the load step instead of setting the fields where there are missingValues to None, set the field to this object (and store the original data in it).

(this is highest priority for us right now I believe)

[roll]

@cschloer
Yea.. something to think about... Not sure, for now, what's the best option

[roll]

I'll try to propose something

[roll]

@cschloer
BTH is it 100% neede to have this data in the output files? Because it breaks the idea of a data package concept (I agree with Adam here).

Can we collect this data and export differently:

• as a resource fields e.g. collected_missing_values: [value, row, column]
• as an additional resource
• etc

?

[cschloer]

I'll check in with the rest of the team and get back to you!

[roll]

@cschloer
Also the option, I think already mentioned by @akariv:

Raw data:

measurements
1
2
problem1
4
problem2
5

Cleaned data:

measurement (number), measurement_note (string)
1, -
2, -
null, problem1
4, -
null, problem2
5, -

It's also kinda conceptually correct because output data will be strongly typed (e.g. it's possible to load into a SQL database)

So in high-level, to summarize, I think the best options are:

• convert missing values into metadata OR
• convert missing values into extra columns/resources

[cschloer]

We're still discussing on our end and probably won't have a response on a preferred solution until Monday, so best to keep this in "waiting for response"

[roll]

@cschloer
So regarding another option proposed by @akariv:

Consider we have a processor called handle_commented_numbers which parse a column containing numbers and comments (and marked as number/commented or like this) into this type:

from decimal import Decimal

class CommentedDecimal(Decimal):

    def __new__(cls, value=0):
        try:
            self = Decimal.__new__(cls, value)
            self.comment = None
        except Exception:
            # check if it's in the missing_values
            if not isinstance(value, str):
                raise
            self = Decimal.__new__(cls, 0)
            self.comment = value
        return self

    def __str__(self):
        if self.comment is None:
            return super().__str__()
        return self.comment

print(CommentedDecimal('3'))
# 3
print(CommentedDecimal('problem1'))
# problem1
print(CommentedDecimal('3') + CommentedDecimal('problem1') + 1) # 3 + 0 + 1
# 4

Then in other processors, you can use this column as a number (missing values will be 0) and on a dump stage, we don't need to change anything because a dumper will use the str(value) which returns a number OR a comment.

PR.
This approach will require moving parsing from the load processor.

[roll]

I think the last option is exactly what was described in the initial issue. Another question is that proper processing (converting missing values into metadata or other columns/resources) align better with ETL-concepts in my eyes.

So a lot of options to consider. Take your time definitely 😃

[cschloer]

So that looks great - but is there any reason that couldn't happen in the load step with a new commentedValue or ignoreValue parameter? And then there's a datatype like this for every type (number, integer, datetime, etc)?

Also I think it would be important that it not resolve as 0 if it's a number but rather as None like it currently does with missingValues. Important when comparing to a value that is actually 0, or other things that mess up when it actually has a value.

[roll]

@cschloer
I've investigated all these options more closely and a lot of them have failed some of the checks:

• serializability (I guess we run into a rabbit hole trying to subclass native types)
• addressability (another rabbit hole to follow the metadata if it's detached)

TBH at the moment, I don't see a better option than to have a processor that splits data+comments column into two columns: data + comments. It was described by this example:

Raw data

measurements
1
2
problem1
4
problem2
5

Cleaned data

measurement (number), measurement_note (string)
1, -
2, -
null, problem1
4, -
null, problem2
5, -

Can it work for BCO-DMO?

Actually, I feel now that it's the only proper solution for this problem from the data processing point of view.

[roll]

@cschloer
Also, it's easy to modify this solution to one-column based:

Raw data

measurements
1
2
problem1
4
problem2
5

Cleaned data

measurement (object)
{value: 1}
{value: 2}
{value: null, error: problem1}
{value: 4}
{value: null, error: problem2}
{value: 5}

And we even can have a pre-dump processor converting it back to strings as it was in the source

[cschloer]

With the one column solution, how do intermediate processors handle the values? Do they all need to be rewritten to handle a column of type { value: '', error: '' }, or am I misunderstanding?

[roll]

@cschloer (cc @akariv)
I've created the first prototype - datahq/dataflows#124

raw data

col1,col2
1,1
err1,2
3,3
4,err2
5,5
mis1,mis2
7,7

pipeline

extractmv:
  title: extractmv
  pipeline:

  - run: load
    parameters:
      format: csv
      from: extractmv.csv
      override_schema:
        missingValues: ['err1', 'err2', 'mis1', 'mis2']
      extract_missing_values:
        target: notes

  - run: dump_to_path
    parameters:
      out-path: 'output'
      pretty-descriptor: true

cleaned data

col1,col2,notes
1,1,{}
,2,"{""col1"": ""err1""}"
3,3,{}
4,,"{""col2"": ""err2""}"
5,5,{}
,,"{""col1"": ""mis1"", ""col2"": ""mis2""}"
7,7,{}

cleaned metadata

{
  "bytes": 149,
  "count_of_rows": 7,
  "hash": "3ca24e4cb672bda65cadbc3f1bee0950",
  "name": "_",
  "profile": "data-package",
  "resources": [
    {
      "bytes": 149,
      "dialect": {
        "caseSensitiveHeader": false,
        "delimiter": ",",
        "doubleQuote": true,
        "header": true,
        "lineTerminator": "\r\n",
        "quoteChar": "\"",
        "skipInitialSpace": false
      },
      "dpp:streamedFrom": "extractmv.csv",
      "dpp:streaming": true,
      "encoding": "utf-8",
      "format": "csv",
      "hash": "6d430994867785f148e9d022995a2ad1",
      "name": "extractmv",
      "path": "extractmv.csv",
      "profile": "tabular-data-resource",
      "schema": {
        "fields": [
          {
            "format": "default",
            "name": "col1",
            "type": "string"
          },
          {
            "format": "default",
            "name": "col2",
            "type": "string"
          },
          {
            "format": "default",
            "name": "notes",
            "type": "object",
            "values": [
              "err1",
              "err2",
              "mis1",
              "mis2"
            ]
          }
        ],
        "missingValues": [
          "err1",
          "err2",
          "mis1",
          "mis2"
        ]
      }
    }
  ]
}

I think a dumper can merge missing values back to their original columns but as metadata linked to a row it could be more useful for further processing in my opinion. For example, a UI can use this field to show some additional information on top of an empty cell (but having knowledge that this values is missing)

[cschloer]

This looks cool! My main concern is the interaction with processors that rename fields. I know there are none in the standard processor library, but in our custom processor library we have a rename fields processor. I suppose it's possible for the rename_fields processor itself to find the "notes" field and update the key names to the new fields but that also seems like a rabbit hole... Furthermore, what happens when you join two resources? Or concatenate two resources with different missingValues (I guess the schema would need to change the notes field to be the union of the previous two note fields)? I feel like there are some complications here that will rapidly make it more and more difficult to implement.

Can you clarify again the problem of subclassing native types? That feels like the only solution that is robust enough to survive a whole pipeline of processing, and also allows the user to choose at the end if they want to dump the dataset with the missingValues or not.

Just had a talk with Amber and Adam about this. Adam is going to reach out to some people in the Ocean sciences community to figure out what exactly they want for datasets like this in order for us to come up with the solution that works best for their needs. I think it's best to hold off on a complete implementation until we hear back from them.