Fix header detection for tables with sparse numerical data

[sbesson]

Fixes #76

Reproducible scenario

First create a minimal dataset/image hierarchy e.g. as follows:

touch test1.fake test2.fake
dataset=$(omero obj new Dataset name=sparse_table)
omero import -T $dataset test1.fake test2.fake

CSV files with sparse string data such as the one below are correctly handled by the current HEAD of omero-metadata.

$ cat sparse_string_column.csv 
Image name,meas1,meas2,meas3,meas4
test1.fake,1.1,1,high,low
test2.fake,0.5,2,,low

The columns with missing values are mapped as s/StringCOlumn and the missing value are turned into empty strings where running omero metadata populate --file sparse_string_column.csv $dataset e.g.

CSV files with sparse numerical columns such as the one below currently fail during the population command:

$ cat sparse_numeric_column.csv
Image name,meas1,meas2,meas3,meas4
test1.fake,1.1,1.2,high,low
test2.fake,,2.1,,low

Here, the meas1 column is currently mapped into a d header type/DoubleColumn by the pandas detection logic, With the default omero metadata populate --file command, the table population fails withValueError: Empty Double or Long value. Use --allow_nan to convert to NaN`.

Proposed changes

Since the library already includes some logic allowing the user to control whether NaN values are allowed in the OMERO.table (introduced in #60), this PR proposes the following changes

• the MetadataControl.detect_headers API supports an extra keep_default_na argument (True by default). Its value is forwarded topandas.read_csvand determines controls how pandas should handle NA (missing) values and whether the column is detected asdvss` (see https://pandas.pydata.org/docs/reference/api/pandas.read_csv.html)
• unit tests are added to cover the new MetadataControl.detect_headers API with different combination of tables/arguments
• the populate command is updated to use the value of args.allow_nan (False by default) as pass it as keep_default_na to MetadataControl.detect_headers

fe73a17d2a71fd7d220c48891a2364110b59f4f1 adds a cosmetic change defining GNU-style aliases of the command-line arguments (--manual-header, --allow-nan) using hyphen as separator. The existing underscore separated flags are preserved.

Testing

With these changes, annotating of sparse CSV tables using the default header detection should be functional in all cases.

1. if the tabular data is dense or containing sparse string columns, the behavior of the command should be unchanged

2. if the tabular data is dense or containing sparse numerical columns,, the behavior of the command will depend on the --allow-nan flag

   omero metadata populate --file sparse_numeric_column.csv $data

   will detect the sparse numeric column as aStringColumn and store the missing values as empty strings

   omero metadata populate --file sparse_numeric_column.csv --allow-nan $data

   will detect the sparse numeric column as a DoubleColumn and store missing values as nan

[snoopycrimecop]

Conflicting PR. Removed from build OMERO-plugins-push#1169. See the console output for more details. Possible conflicts:

• PR #64 will-moore 'Support images_by_name for Screen and Plate'
  • test/integration/metadata/test_populate.py

--conflicts

[snoopycrimecop]

Conflicting PR. Removed from build OMERO-plugins-push#1176. See the console output for more details. Possible conflicts:

• PR #64 will-moore 'Support images_by_name for Screen and Plate'
  • test/integration/metadata/test_populate.py

--conflicts Conflict resolved in build OMERO-plugins-push#1179. See the console output for more details.

[sbesson]

Thanks @muhanadz for the review. 27050f2 should amend the README where most of the information about the library usage is captured at the moment.

[sbesson]

Thanks @muhanadz. The new column detection behavior is now released as omero-metadata 0.11.0 🎉