ECSV array/object-valued column support

The ECSV format is, as far as I know, defined by APE6. That lists the permitted datatypes as bool, int8, int16, int32, int64, uint8, uint16, uint32, uint64, float16, float32, float64, float128, complex64, complex128, complex256, and string, and it says on the topic of array-valued columns (as it calls them "Multidimensional columns"):

Multidimensional columns are not supported in version 0.9 of the ECSV format.

None of the available text data formats supports multidimensional columns with more than one element per row. Although in many cases having such data would indicate using a binary storage format, there is utility in supporting this for cases where the column shape is "reasonable", perhaps with no more than about 10 elements.

(parenthetically: VOTable listed as one of the comparison formats earlier in the document does support array-valued columns, though it's not really a text format)

However, if you ask astropy.table to write a table with array-like columns (lists, tuples, numpy arrays) to ECSV format, it will output ECSV tables using the undocumented datatype object. Such columns produce string values when read back in. See the following code:

from astropy.table import Table

# Create table column data
column_float  = [10., 20., 30.]
column_string = ['xx', 'yy', 'zz']
column_list   = [[2.0, 3.0, 4.0], [5.0], [8.5, 1.1]]

# Create Astropy table and get info
table = Table([column_float, column_string, column_list],
              names=('num_col', 'txt_col', 'list_col'),)

# Write ecsv table with multiple elements per cell
table.write('tsimp.ecsv', format='ascii.ecsv', overwrite=True, delimiter=',')

# Read it back in
tr = Table.read('tsimp.ecsv', format='ascii.ecsv')
print([type(c) for c in tr[0]])
print(tr)

which writes the following output:

[<class 'numpy.float64'>, <class 'numpy.str_'>, <class 'str'>]
num_col txt_col     list_col   
------- ------- ---------------
   10.0      xx [2.0, 3.0, 4.0]
   20.0      yy           [5.0]
   30.0      zz      [8.5, 1.1]

and produces the following ECSV file:

# %ECSV 0.9
# ---
# datatype:
# - {name: num_col, datatype: float64}
# - {name: txt_col, datatype: string}
# - {name: list_col, datatype: object}
# delimiter: ','
# schema: astropy-2.0
num_col,txt_col,list_col
10.0,xx,"[2.0, 3.0, 4.0]"
20.0,yy,[5.0]
30.0,zz,"[8.5, 1.1]"

Is this intended behaviour? At present my (java STIL/TOPCAT) ECSV reader fails to read such tables because they have an unknown datatype object. I'd say that's an ECSV output bug given the documentation at APE6.

However, as it happens this got noticed during some discussion within DPAC (the Gaia data processing consortium) about whether array values could be stored in ECSV tables, which is an enhancement requested by some DPAC members for use with both Astropy and TOPCAT. The suggestion was that the existing behaviour could be extended with some additional metadata (new datatype or array flag) to provide array-valued column support in ECSV.

On a related topic, I see #11155, which is pursuing a different approach to serializing array values into ECSV. That may not be suitable for the Gaia use case, since (a) it only supports fixed-size array values and (b) from the metadata in the example file it seems to be intended as an astropy-specific convention.

So: fix object datatype output? Extend it to support array-valued columns? Or should I just patch my ECSV parser to cope with undocumented datatypes?

@mbtaylor

@mbtaylor - ~~outputting object there is a bug.~~ EDIT - actually I didn't look closely enough at the original example. That is expected behavior for an object column. The current behavior for an object column is just to store the repr of the object, so that should indeed be documented.

Great that ECSV is being considered for use in this case and am happy to work to support requirements for multidimensional column data. Can you give a little more detail about the requirements and any ideas you have about a proposed extension to ECSV (APE-6) that can reasonably handle such data in a text line-based format? The solution in #11155 was an attempt to rationalize N-d columns into 1-d columns that are manageable, but I'm open to other solutions.

[EDIT - thinking some more, I think #11155 is a good solution for bona fide numpy N-d columns, but your use case is actually about round-tripping object columns.]

About the fixed-size array requirement, it looks like you are talking about tables that have e.g. an object column with variable length list elements. Astropy Table pretty much treats any object column as an opaque column that might contain absolutely anything in each element.

5-minute hack as a proof of concept. Along with documenting the behavior for object columns and relying on Python repr for basic object types like list and dict, maybe this has some promise for this case? Using your example ECSV file:

(astropy) ➜  astropy git:(master) ✗ git diff
diff --git a/astropy/io/ascii/ecsv.py b/astropy/io/ascii/ecsv.py
index 6ba9613fb..f684e98d9 100644
--- a/astropy/io/ascii/ecsv.py
+++ b/astropy/io/ascii/ecsv.py
@@ -201,6 +201,15 @@ class EcsvOutputter(core.TableOutputter):
         # the input table.
         out = serialize._construct_mixins_from_columns(out)
 
+        for col in out.itercols():
+            if col.dtype.name == 'object':
+                from ast import literal_eval
+                for ii, val in enumerate(col):
+                    try:
+                        col[ii] = literal_eval(val)
+                    except Exception:  # Not clear what exceptions might occur
+                        pass
+
         return out

Example:

from astropy.io import ascii

txt = """\
# %ECSV 0.9
# ---
# datatype:
# - {name: num_col, datatype: float64}
# - {name: txt_col, datatype: string}
# - {name: list_col, datatype: object}
# delimiter: ','
# schema: astropy-2.0
num_col,txt_col,list_col
10.0,xx,"[2.0, 3.0, 4.0]"
20.0,yy,[5.0]
30.0,zz,"[8.5, 1.1]"
"""

dat = ascii.read(txt, format='ecsv')

In [2]: dat
Out[2]: 
<Table length=3>
num_col txt_col     list_col   
float64   str2       object    
------- ------- ---------------
   10.0      xx [2.0, 3.0, 4.0]
   20.0      yy           [5.0]
   30.0      zz      [8.5, 1.1]

In [3]: dat['list_col'][0]
Out[3]: [2.0, 3.0, 4.0]

In [4]: type(dat['list_col'][0])
Out[4]: list

Just looking at the ECSV spec again I wonder if adding a shape field to the column specifier, and providing a spec for handling multidimensional columns (which coincidentally matches #11155) would be a good way forward.

@taldcroft

@taldcroft , thanks for following this up.

Having the existing object datatype documented as per astropy/astropy-APEs#64 is welcome in that it allows parsers to know what to expect, so I can at least understand what's in the column and do something about it (most likely, skip that column on input). This addresses the first point I was making in my comment, that object was undocumented in APE6.

My other point was that DPAC would like to be able to use ECSV as a language-neutral format for storing array-valued columns alongside scalar-valued columns. The datatype: object option does not solve this because:

realistically only Python readers will be able to reliably decode it
it allows heterogeneous content in a column; much table processing relies on treating all values in a column as being of a known data type

To address this, I think it's necessary to declare a column as containing a particular array type (array of doubles, array of ints) rather than just calling it an object and being able to stick anything in there.

I think what's required is:

a convention in the column declaration for flagging a column as being a typed array
a rule for decoding typed column content into an array value

For (2), I expect that standard python list syntax(?) can be used.

For (1), adding a shape specifier as you suggest would do it I think:

datatype: double, shape: []

though that has the problem that (older) parsers not aware of this convention might ignore the shape specifier and misunderstand the content. An alternative could be to add the shape as a suffix to the datatype:

datatype: double[]

so that legacy parsers would at least know the column is not a scalar double.
To preserve backward compatibility even better, this could stay as a string column, but with an additional parsing hint, e.g.

datatype: string, arraytype: double[]

which means that legacy parsers would still be able to read the table, just not understand the content as well as parsers that understand this convention.

Other devils in the detail: multidimensional arrays? fixed array sizes? I would suggest to allow e.g. [], [3], [][], [3][5], [][5], ... (some of those options would cause problems for my table handling software, but probably that's my problem). That would mean that you have to declare the dimensionality of your arrays up front (e.g. you couldn't mix [9.5] and [[1.0,2.1],[99.,5.]]), but I think that's reasonable.

If you think that looks like a good plan but you'd like me to flesh it out more or draft some text to amend APE6, I'm happy to do so.

@mbtaylor

@mbtaylor - I see your point about the Python repr and being language neutral.

For columns that truly are object, which includes Python numpy arrays of variable length lists, what about specifying the serialization standard as YAML? AFAIK this can be safe by specifying that reading use a safe loader in the target language. So this is basically back compatible since the object type was previously not defined in the spec, but the default is presumably to fall back to string type. I think this makes sense for object type and provides significant flexibility with very little new code complexity.

For an array that is actually an homogenous array with N-d elements (e.g. representable as a typed numpy ndarray) there is a good question about the best representation. #11155 breaks that into single columns which get parsed as normal CSV, then the reader application is required to stitch those back together in the final N-d column. With some reasonable update to ECSV, I think that readers could easily understand this convention, and it is back-compatible because the actual data columns are still just typed scalars. But of course the other option of a YAML serialization of each N-d element is also possible. I suspect that performance is substantially worse using YAML to parse each element.

For the in-between, somewhat specialized case of a typed array of variable-length arrays, I'm not sure what is the best strategy. I will think about what you said some more.

Anyway, at this point I'm doing a little thinking out loud and all options are open. But now I must attend to my day job for a bit...

For object columns, I'd say anything that makes sense in python is fair enough, but they are not in general going to be readable from non-python environments. If the intention is to fall back to string semantics in that case, which is probably the best that can be done, that intention should be noted in the documentation.

For N-d arrays of fixed size, the #11155 approach may be OK, but the meta: !!omap entry in the existing example on that PR looks both complicated and python-specific.

By all means take some time to think about variable-length arrays. In case it helps to motivate it, the kind of use case we have in mind is a table where each row represents a source, and one column provides a time-series of samples (e.g. magnitude observations), where each source may have a different number of such samples.

but they are not in general going to be readable from non-python environments.

I'm probably missing something, but I had imagined that most languages with YAML library support can happily read YAML-encoded strings. Obviously support for certain data structures will vary based on the language, but if java can handle a list of lists then I would imagine the java YAML library can decode that. E.g. SnakeYAML claims it can parse every example in the YAML spec. Apologies for the language ignorance.

About the meta: !!omap bit, yes that is really an astropy-specific extension that makes the Python implementation easy, but I'm 👍 with defining a language-agnostic update to ECSV for this case.

OK your mention of "Python numpy arrays" in this context might have thrown me; maybe it's not a language-dependent issue after all, sorry. I have to admit I stopped reading the YAML spec once I'd got far enough to read/write files following the existing ECSV definition, so it looks like I don't understand the suggestion you're making. Could you sketch an example of what this might look like?

For the specific case of variable length arrays, a minimal example is what I showed above in #11368 (comment). In other words, that data structure is represented as an array of objects, where each one is encoded by YAML.

As I think harder, YAML may not be a great choice. Since it supports rich serialization of arbitrary objects, it quickly starts using language-specific constructs, e.g. a tuple turns into !!python/tuple [1, 2]. Relatedly YAML is also quite slow.

A better default choice might be JSON which is simpler and really fast. My profiling showed the Python implementation of JSON decoding can be 100 times faster than the stock PyYAML. JSON is also always safe.

One option would be to include an object_encoding keyword in the column specifier for object columns (along with name, datatype, etc) which could be JSON or string. The latter just means that the writer put a language-dependent string representation of the object there. The reader would leave it as a string. One could imagine more specific labels like python, which means in Python using repr(obj) to encode and ast.literal_eval(string) to decode. The JSON encoding would be required for data files that are intended for cross-language use.

@mbtaylor

@mbtaylor - for the case of a homogenous fixed N-d array column, I want to explain a little more about #11155 and see if this might work for you. In the current implementation here is the output for a length-3 column where each element is 2x3:

In [6]: t = Table()  
In [7]: t['nd'] = np.arange(18).reshape(3, 2, 3) 
In [8]: ascii.write(t, format='ecsv')
# %ECSV 0.9
# ---
# datatype:
# - {name: nd.0_0, datatype: int64}
# - {name: nd.0_1, datatype: int64}
# - {name: nd.0_2, datatype: int64}
# - {name: nd.1_0, datatype: int64}
# - {name: nd.1_1, datatype: int64}
# - {name: nd.1_2, datatype: int64}
# meta: !!omap
#   .... astropy-specific stuff you can ignore
# schema: astropy-2.0
nd.0_0 nd.0_1 nd.0_2 nd.1_0 nd.1_1 nd.1_2
0 1 2 3 4 5
6 7 8 9 10 11
12 13 14 15 16 17

The first feature that makes me happy is that it is still just plain columns and most CSV readers can parse this.

At this point there is a convention that a column that is named <name>.<i>_<j> is the i,j subarray of <name>. What if instead we had something like:

# datatype:
# - {name: nd.0_0, datatype: int64, subarray: [0, 0], name_nd: 'nd'}
# - {name: nd.0_1, datatype: int64, subarray: [0, 1], name_nd: 'nd'}
# - {name: nd.0_2, datatype: int64, subarray: [0, 2], name_nd: 'nd'}
# - {name: nd.1_0, datatype: int64, subarray: [1, 0], name_nd: 'nd'}
# - {name: nd.1_1, datatype: int64, subarray: [1, 1], name_nd: 'nd'}
# - {name: nd.1_2, datatype: int64, subarray: [1, 2], name_nd: 'nd'}

The additional subarray key in the column descriptor explicitly indicates that this column is element [i, j] of a multidimensional column. The name_nd key specifies the name of the final column into which this subarray element should be placed. The shape is inferred from the maximum subarray value. The actual names of the subarray columns could be arbitrary, though the current astropy implementation does depend on a naming convention. I might be able to fix that.

This should be back-compatible by just adding new keys to the column specifier. It also should be a fairly straightforward extension for existing code to see these new column keys and do the process of assembling the subarrays into the final N-d array.

Thoughts?

The subarray/name_nd business strikes me as somewhat inelegant on the grounds that the column specifications are tied together in ways that can go wrong: what does a parser do if the datatypes for related columns are not all the same? Or if there are gaps in the subarray elements? But if it was in the specification I should be able to implement support for it.

However, it doesn't cover the variable-length array case.

The object type (encoded using JSON or YAML or whatever) does let you store variable-length arrays, but it gives very little indication of what the content of the column is. For me a table is a collection of equal-length columns, each of which has homogeneous content of a declared type, and that lets you perform processing based on the column types. If a column is allowed to contain anything-that-can-be-serialized-in-JSON it gives you flexibility, but you pay for it in ease of processing. If you know that each entry in a column is an array of floating point values, then you can know up front that for each cell it will be possible to e.g. plot a time series or calculate the mean. If all you know is that it's an object, you can't do much apart from serve up the content to downstream software to make sense of.

FITS and VOTable for instance allow fixed-length or (in some cases with difficulty) variable-length arrays of primitives as column types, but not generic object stores. That's what I was hoping to be able to do in ECSV.

The subarray/name_nd business strikes me as somewhat inelegant on the grounds that the column specifications are tied together in ways that can go wrong

Agreed that it is not especially elegant and I thought about this. The issue is about maintaining the core ECVS principle of the column specifications, namely that each distinct column in the file is fully described by the column spec. So without breaking that core principle, there is really no way around having the possibility that the file is "corrupted" by having member columns of an N-d array be somehow inconsistent. The spec can address this and declare that it is an error to have inconsistent types or a missing element of an N-d column.

One way around this inelegance might be to add a new top-level data structure that defines multidimensional columns. This would specify the overall shape and the name / indices of component columns.

A final option is to punt on all this and always represent N-d columns using the JSON encoding. This is more consistent, so you can have a column with a defined shape and datatype (like float64). The downside is that the ECSV file now contains data columns that are essentially opaque to a non-specialized reader. Someone that reads in the file to Excel or with Pandas gets a column of strings with JSON representations of the data instead of a collection of individual columns with correctly-typed numbers.

I'm not sure which things to prioritize.

For me a table is a collection of equal-length columns, each of which has homogeneous content of a declared type

We're on the same page for truly homogenous content (anything representable by a typed N-d array). Now for the different case of what we call variable length arrays of primitives, things are more difficult. Coming from the Python perspective, the way this data structure is implemented is a numpy object array of Python list objects or numpy ndarrays. There is no straightforward way to ensure that the contents or dimensionality are consistent with a global type or shape specification. (Of course this is all possible, it is just somewhat expensive in Python because of lots of pure-Python loops, possibly recursive).

As a specific example, what if my Table column contains:

[1]
[1,2]
[[1, 2], [3, 4]]
What do we do with that? Serialize as object, I suppose.

Maybe the answer is limiting scope to the specific case of a variable-length list that is required to be 1-d. Then you might specify shape=variable_1d. Anything else would just be object. The types are another question. Native Python types include bool, float, int, complex, str. If the idea is to be able to specify the datatype precision (e.g. 32 bits), that might imply making the 1-d array object be a numpy array not a Python list when reading. This is a language-dependent detail, but it is a question I would need to answer. I'm curious what is the representation of such a data structure in Java and whether that is typed at the level of e.g. uint8.

With a more limited spec of variable 1-d, then this can be detected by the ECSV writer and validated by the reader.

My feeling is that limiting scope for variable-length arrays to 1-d would be reasonable; I think it would cover most of the use cases involving arrays, though not all. But I'll try to find out just what the DPAC requirements are here.

Within an array type, I'd expect to see the same type options as for scalars, the existing bool, int8, int16 etc already defined for datatype; it would seem a bit strange to me to have a different type vocabulary for array elements than for scalars. That would be natural for the way that I've implemented table access, and it also corresponds to the way that FITS and VOTable support array-valued columns.

But it sounds like that might be problematic for the python implementation. Obviously as far as this is intended to be a cross-platform format (which I appreciate is not the context in which it was originally introduced) it's not a great idea to drive the design on implementation-specific details. However, burdening a format that does what it does well with a set of features that are hard to implement on its main platform would also have its downsides.

@mbtaylor

My feeling is that limiting scope for variable-length arrays to 1-d would be reasonable; I think it would cover most of the use cases involving arrays, though not all. But I'll try to find out just what the DPAC requirements are here.

Good.

Within an array type, I'd expect to see the same type options as for scalars, the existing bool, int8, int16 etc already defined for datatype

This is reasonable, and I now see that this is exactly what astropy.io.fits does for variable-length arrays. See:
https://docs.astropy.org/en/stable/io/fits/usage/unfamiliar.html?highlight=variable%20length#example

Reading the bundled variable-length test FITS file with Table.read(filename) gives an object column of np.ndarray items. So we're on the same page and that will be the behavior for ECSV. For writing the ECSV representation will be the JSON serialization of each item.

But it sounds like that might be problematic for the python implementation.

Assuming we use an object array of np.ndarray, there is no problem for Python. I think in most cases that memory and item access performance for small arrays is worse for numpy than pure Python lists, but so be it.

Finally @mbtaylor, do you have an opinion about the remaining open question of how to represent the data in the situation of an homogenous multidimensional column? I'm starting to lean toward using the JSON serialization (same as for a variable length array) instead of exploding the column into many 1-d columns. This is more consistent and perhaps less hacky.

So the main outcome is roughly:

Add a shape parameter to the column specifier. The shape can either be a list for an homogenous array or something like [None] (??) for a 1-d variable array. Thinking about implementation, it is probably easy to support any dimension of arrays that are variable in the last axis, so shape could also be [2, 3, None].
Serialize each item with JSON (Python detail, convert np arrays to Python lists first)
Columns of object type that are not variable 1-d arrays will be written as object datatype using JSON to serialize the object. This does restrict the object to be something representable in JSON. Probably OK.

I'll try to come up with some examples to illustrate this all better (though you are welcome as well if you are motivated).

@mhvk

@mhvk - if you have any thoughts on this extension to ECSV now would be a good time. This might mean punting on #11155 and doing it differently.

@taldcroft

@taldcroft, that sounds great, I agree with all of those suggestions. I do prefer using the same serialization for fixed- and variable-length arrays, and JSON is a sensible choice.

@taldcroft

@taldcroft - I have not thought much about this, but the idea of a JSON encoding sounds reasonable, as it seems it would give readable output. I think if one were to always have quotes around the entries, it would be backward compatible as well. Or would you rely on the brackets being clear enough?

p.s. JSON encoding has been on the wishlist for a while - #4604, #5165...

I would expect that any JSON-encoded content has to be treated syntactically as a string and hence usually/always enclosed in quotes (with appropriate quoting for quotes within that string). That way the post-YAML part of the file will still be parseable as a CSV file, which I take to be an important characteristic of the ECSV format.

The current Python code is going to produce output that does not by default enclose the content in quotes unless it is required. For example see below. The point is that the spec will state clearly that any columns with defined shape will be JSON-encoded strings and applications should parse them accordingly.

In practice, the column values for a non-scalar entry will always be treated as strings by any CSV parser because it will always start with [.

# %ECSV 0.9
# ---
# datatype:
# - {name: num_col, datatype: float64}
# - {name: txt_col, datatype: string}
# - {name: var_array_col, datatype: float64, shape:[null]}
# delimiter: ','
# schema: astropy-2.0
num_col,txt_col,var_array_col
10.0,xx,"[2.0,3.0,4.0]"   <-- quotes required to escape the embedded commas
20.0,yy,[5.0]  <-- no quotes
30.0,zz,"[8.5,1.1]"

Note that in the case of a space-delimited ECSV file there would not be any quotes unless the values contained a space character.

We can of course decide that everything has quotes around it, but this definitely complicates the Python implementation because we rely on the standard library csv module to take care of quoting. The degree of complication would be TBD, but my first guess is that it would be pretty bad.

@mbtaylor

@mbtaylor @mhvk - are you agreed on a shape parameter that looks like the following:

shape=[2,3]: 2x3 fixed shape array
shape=[null]: 1-d variable array
shape=[2,3,null]: 2 x 3 x variable-length array

Here the null is the YAML representation of the language-specific concept of null, which in Python is None.

I think it is reasonably easy to handle any array which is variable in the last index. (Could also be variable in the first index, but somehow last seems better).

Yes, that looks perfect.

@taldcroft

@taldcroft - OK, I see the logic for not quoting single-item lists - as you say, this is understood in the csv context given the brackets. The shape argument looks fine.

I guess the one disadvantage compared to the scheme in #11555 is that standard csv readers will now read the multi-column as string. So, it will not be something you just load in a spreadsheet and do calculations on. But visually the bracket notation does seem considerably nicer, and as these files are meant to be easily readable by humans, that counts!

@mhvk

ping @mhvk, @taldcroft. I just ran into this while fiddling around on #10673. Not mission critical, but it would be nice. Though this is not my wheelhouse, I'm happy to help!

@nstarman

@nstarman - Thanks! Right now my plan is to get to this for the 4.3 release but I'll let you know by this week if that seems unrealistic and I might need some help. If you are looking for a useful challenge, another set of eyes on #11127 would be great. 😄

See #11569.

Navigation Menu

Repository navigation

ECSV array/object-valued column support #11368

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

ECSV array/object-valued column support #11368

Description

Activity

taldcroft commented on Mar 6, 2021

taldcroft commented on Mar 6, 2021

taldcroft commented on Mar 7, 2021

mbtaylor commented on Mar 8, 2021

taldcroft commented on Mar 8, 2021

mbtaylor commented on Mar 8, 2021

taldcroft commented on Mar 8, 2021

mbtaylor commented on Mar 8, 2021

taldcroft commented on Mar 8, 2021

taldcroft commented on Mar 8, 2021

mbtaylor commented on Mar 8, 2021

taldcroft commented on Mar 9, 2021

mbtaylor commented on Mar 10, 2021

taldcroft commented on Mar 11, 2021

taldcroft commented on Mar 11, 2021

mbtaylor commented on Mar 11, 2021

mhvk commented on Mar 14, 2021

mbtaylor commented on Mar 15, 2021

taldcroft commented on Mar 15, 2021

taldcroft commented on Mar 15, 2021

mbtaylor commented on Mar 15, 2021

mhvk commented on Mar 15, 2021

nstarman commented on Apr 11, 2021

taldcroft commented on Apr 11, 2021

taldcroft commented on Apr 17, 2021

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions